GDFDataTools 0.6.0-preview.3

Introduction

GDFDAO is a C# Framework used to abstract SQL databases accesses.

Processes

This section will explain how the GDFDAO works. GDFDAO is a C# framework responsible for managing data and their structure in database as seamlessly as possible. It is important to have a basic understanding of SQL databases to use this framework.

Main concept

GDFDAO uses C# types (class or struct) as data structures to be stored as SQL tables. GDFDAO parses the type to determine the structure of the SQL table (columns and constraints).

The DAO associates C# code and SQL structures as follows: | C# | Sql | |-----------------|-----------------| | Class | Table | | Property | Columns | | Classe instance | Row |

GDFDAO uses reflection in order to extract the valid informations from a type to structure it into a SQL table. The DAO will cache as much of the structure as possible in order to improve performances. Each and every interaction with the database is made explicitly by the developer with the exception of data structure creation. The framework tries to be as easy to use as possible but does not try to hide what is been done in the database.

Initialisation

By default, creating a DAO does not create any interaction with the database. Meaning that there is no SQL table creation or connection testing at this point. It is due to GDFDAO not knowing which type to convert into SQL table at startup. Instead of parsing every single type of the program, the DAO waits for the developer to ask to store a type in database and hot creates it when needed. It is called SQL checking.

SQL checking checks the following:

• If the table exists
  • It creates it if it does not
• If the table is of the right structure
  • If all the columns exists
  • If some columns are still used
  • If columns are of the right SQL type
  • If the constraints (indexes and uniques) are of the right name and on the right columns.
• If the structure is not valid, the DAO will try to fix it
  • It will try to retrieve as many data as possible
  • If an error occur, it will not loose any data rollback to the previous SQL table structure

By default, the name of the SQL table is the name of the class (case sensitive). By default, the name of a column is the name of the property (case sensitive).

SQL checking is done only when a feature requires it. Such features are:

• Count
• Select
• Update
• Insert
• Delete

Table exists

Checking if a table exists is pivotal for the DAO. As such, it is important for the implementation of this feature to be foolproof.

Checking if a table exists must not automatically create the table if it does not.

Truncate

Truncating a table can be necessary in some cases.

Truncating a table must not automatically create the table if it does not exist. But it returns whether or not it exists.

Drop

Dropping a table can be necessary in some cases.

Dropping a table must not automatically create the table if it does not exist. But it returns whether or not it exists.

Now

Now is an advanced feature used to synchronize time between servers using a database as a time reference.

This method does not change the time of the current machine. It just retrieves the current time of the database.

It can also be used to check database connexion since it performs a quick and easy call for the database to handle.

Count, Select, Insert, Update, Delete

As explained previously, these methods must perform a SQL check.

It also caches as much information as possible to speed up the next calls the the method.

Supported features

Supported RDBMS

Here is the list of supported and tested RDBMS: | RDBMS | GDFDAO class | |---------|---------------| | MariaDb | GDFDAOMariaDb | | MySQL | GDFDAOMySQL |

Data structures

GDFDAO translates C# classes and structures into SQL tables. This is done by parsing types and their properties then converting them into SQL tables and columns. GDFDAO helps developers creating rich SQL tables by implementing tools and attributes.

Supported types

Only non abstract or static types are supported. Only public properties with a managed type are parsed.

Default managed types

Basic	Special
bool	enumerations
sbyte	byte[]
byte	Guid
char	DateOnly
short	TimeOnly
ushort	DateTimeOffset
int	Nullable<>
uint
long
ulong
float
double
decimal
string
DateTime

Flavors

Flavors are ways to match a C# type to a SQL type. It allows developers to save a C# type in different ways depending on the use case. It also allows to support custom property types.

Attributes

By using attributes on classes and properties, developers can add SQL information to help the declaration of tables and columns.

Class attributes

Attribute	Description
[GDFDbTable(name)]	Overrides the default table name.

Property attributes

Attribute	Description
[GDFDbKey(autoIncrement)]	Define a primary key.
[GDFDbDefault(value)]	Set a SQL DEFAULT value.
[GDFDbLength(length)]	Set the length of a string.
[GDFDbNotNull]	Set a reference type as NOT NULL in database.
[GDFDbAccess(select, update, insert)]	Set whether the property should be used in the different SQL operations.
[GDFDbFlavor(dbType)]	Define how to save a C# type in database using DAO flavors.
[GDFDbIndex(name, autoGenerated)]	Define an index for the column.
[GDFDbUnique(name, autoGenerated)]	Define a column with a unique constraint.

Tutorial

Prerequisites

To use GDFDAO you need:

• To be working on a C# project
• To have a supported database working with a valid user.
  • User permissions depend on the RDBMS.
  • This example will be using MariaDb.
• To have an idea of the data structure you want to save.

Create database credentials

Any framework working with a SQL database needs to be able to connect to it. GDFDAO uses a class to describe the database credentials called GDFDatabaseCredentials.

The minimum amount of data to fill in the credentials depends on the RDBMS.

Here are the main informations you need to fill for GDFDAO to work with MariaDb: | Field | Description | |--|--| | Server | The ip address or domain name of the database. | | Port (optional) | The port to connect to. If not set, will use default. | | Database | The name of the database to connect to. | | User | The login of the SQL user. | | Password | The password of the SQL user. |

public class C {
    public void M() {
	    GDFDatabaseCredentials credetials = new GDFDatabaseCredentials {
		    Server = "127.0.0.1",
		    Port = 3306,
		    Database = "GDFUnitTests",
		    User = "User",
		    Password = "123456789"
		};
    }
}

Create a GDFDAO instance

To create the DAO, just instantiate a DAO class.

public class C {
    public void M() {
	    GDFDatabaseCredentials credetials = new GDFDatabaseCredentials {
		    Server = "127.0.0.1",
		    Port = 3306,
		    Database = "GDFUnitTests",
		    User = "User",
		    Password = "123456789"
		};

		IGDFDAO dao = new GDFDAOMariaDb(credetials);
    }
}

Create a class to store in database

Once the dao is initialized, you can use it as you want. First, you need to create a class to be stored in database.

public class Person {
	[GDFDbKey(true)]
	public ulong Id { get; set; } // The primary key. Has an auto increment.

	[GDFDbLength(128)]
	[GDFDbNotNull]
	public string FirstName { get; set; } = ""; // Mandatory field of maximum 128 characters.

	[GDFDbLength(128)]
	public string? MiddleName { get; set; } // Field of maximum 128 characters.

	[GDFDbLength(128)]
	[GDFDbNotNull]
	public string LastName { get; set; } = ""; // Mandatory field of maximum 128 characters.

	[GDFDbUnique]
	[GDFDbIndex]
	[GDFDbLength(256)]
	[GDFDbNotNull]
	public string Username { get; set; } = ""; // Unique mandatory field of maximum 256 characters with an index.

	[GDFDbDefault(0)]
	public sbyte Age { get; set; } // Unique mandatory field with a default value of 0.
}

public class C {
    public void M() {
	    GDFDatabaseCredentials credetials = new GDFDatabaseCredentials {
		    Server = "127.0.0.1",
		    Port = 3306,
		    Database = "GDFUnitTests",
		    User = "User",
		    Password = "123456789"
		};

		IGDFDAO dao = new GDFDAOMariaDb(credetials);
    }
}

Use the DAO

The last thing to do is to use the DAO as intended. Here is an example of everything you can do.

public class Person {
	...
}

public class C {
    public void M() {
	    GDFDatabaseCredentials credetials = new GDFDatabaseCredentials {
			...
		};

		IGDFDAO dao = new GDFDAOMariaDb(credetials);
		
		DateTime now = dao.Now();
		// Get the current database time.
		// Can also be used to check connection state.

		Console.WriteLine(dao.TableExists<Person>());
		// > returns False

		Console.WriteLine(dao.Count<Person>());
		// > returns 0

		Console.WriteLine(dao.TableExists<Person>());
		// > returns True

		Person person = new Person
		{
		    FirstName = "John",
		    MiddleName = "Gregory",
		    LastName = "Doe",
		    Username = "jgDoe",
		    Age = 30
		};
		Console.WriteLine(dao.Insert(person));
		// > returns 1 as the number of modified lines.

		Console.WriteLine(dao.Count<Person>());
		// > returns 1

		List<Person> persons = dao.Select<Person>().ToList();
		Console.WriteLine(persons[0].MiddleName);
		// > returns Gregory

		persons[0].Age = 31;
		Console.WriteLine(dao.Update(persons[0]));
		// > returns 1 as the number of modified lines.

		persons = dao.Select<Person>(GDFDAOWhere.Comparison(
		    nameof(Person.Username),
		    GDFDAOComparisonOperator.Equal,
		    GDFDAOWhere.Value("jgDoe")
		)).ToList();
		Console.WriteLine(persons[0].MiddleName);
		// > returns Gregory

		persons = dao.Select<Person>(GDFDAOWhere.Comparison(
		    nameof(Person.Age),
		    GDFDAOComparisonOperator.GreaterThan,
		    GDFDAOWhere.Value(31)
		)).ToList();
		Console.WriteLine(persons.Count);
		// > returns 0

		Console.WriteLine(dao.Truncate<Person>());
		// > returns True

		Console.WriteLine(dao.Count<Person>());
		// > returns 0

		Console.WriteLine(dao.Drop<Person>());
		// > returns True

		Console.WriteLine(dao.TableExists<Person>());
		// > returns False
    }
}

Create a custom GDFDAO

Developers might want to create their own DAO in order to support a new RDBMS or to extend the default DAO.

IGDFDAO

This interface describes the default features that need to be implemented for a data type to be a valid DAO.

If your custom DAO do not implement this interface, it will not be considered valid.

GDFDAO

A flexible abstract implementation of the IGDFDAO interface. It implements all the features and logic that are common amongst all RDBMS.

If you wish to reuse the logic of the default DAO for your own RDBMS, you can inherit from that class and override the abstract methods.

If you wish to fully rewrite the logic of the DAO, it is preferable not to inherit from this class.

Formats

Formats are a set of classes and interfaces which define a SQL request format for each features of the DAO.

IGDFDAOTableFormat

This interface defines every SQL request string regarding the definition of table structure (DDL requests). The default implementation is the class GDFDAODefaultTableFormat.

IGDFDAORequestFormat

This interface defines every SQL request string regarding data query and manipulation in tables (DQL & DML requests). The default implementation is the class GDFDAODefaultRequestFormat.

IGDFDAOConditionFormat

This interface defines every SQL request string regarding the construction of conditions in where clauses. The default implementation is the class GDFDAODefaultConditionFormat.

Flavors

Flavors are ways to translate a C# type into a SQL data type. Each C# types can have several flavors, meaning that a the developer can choose how the type will be converted and stored in SQL tables.

Flavors can be used to:

• Add custom ways to store types in SQL tables
• Add custom C# types to be stored in SQL tables
• Define SQL type formats when creating a DAO for a new RDBMS

IGDFDAOFlavors

This interface stores all the valid flavors for the DAO. The default implementation is the class GDFDAODefaultFlavors.

In this interface, you can add a flavor for a GDFDAOFlavorHash (which is a pair of C# type and GDFDbType corresponding).

Each and every mandatory type must be defined in the flavors.

IGDFDAOFlavor

This interface defines the SQL data type and convert data from a code type to a SQL type and reverse.

You can implement this interface for every flavor you want to create, add or define. Then add it in the IGDFDAOFlavors instance for the DAO to be able to use them for the desired properties.

Unit Tests

When creating a custom DAO, it is crucial to make sure it works as intended. If you want to be sure it supports the same amount of features than other DAO, you can inherit from the unit test classes DAOBaseTests.

it is an abstract class that will require you to create two DAO:

• A valid one to execute the main tests.
• An invalid one to make sur it works as intended when connection is not valid.

Limits

As of now GDFDAO has a set of known limits that the developer has to work with.

Name collisions

A name collision occurs when the same name is used for two different things.

It is not possible to have a name collision for column names as they are always escaped. So it is not possible to have two columns of the same name or have a column name in conflict with a SQL keyword.

Table name

When it comes to table names, there are a few possible collisions.

Colliding with another table name

By using the [GDFDbTable] attribute, you can set the name of the SQL table to other table names. It is not a bad practice itself if you want to create advanced type conversion from SQL to C#. However, it can lead to unexpected behavior on the SQL side of things so be extremely cautious when using such a feature.

It is recommended to always make sure that the name of a type does not overlap with another type you created.

Colliding with the database name

By creating a type of the wrong name or using the [GDFDbTable] attribute, you can set the name of the SQL table to the one of a database.

Table calls are extremely basic in GDFDAO and are not escaped (for now) so they can be misinterpreted by the RDBMS for a database call instead of a table call.

Colliding with a keyword

By creating a type of the wrong name or using the [GDFDbTable] attribute, you can set the name of the SQL table to the one of a SQL keyword.

Table calls are extremely basic in GDFDAO and are not escaped (for now) so they can be misinterpreted by the RDBMS for a keyword instead of a table call.

Keywords are RDBMS dependent. Even though there are common ones, they can differ from an RDBMS to another but also from a RDBMS version to another.

Secondary keys

GDFDAO does not support secondary keys and table relationships in general. This feature is not meant to be supported as of now and not in any roadmap.

Automatic values retrieval

When inserting a new row, automatic values are values that are filled automatically by the database when not specified in the request. They can be:

• Auto increments values
• Default values
• Current_Timestamp
• Procedure calls
• ...

As of now, those values are not retrieved when inserting a new row in database. It can be a huge hassle to manage data which the values in code are not consistent with values in database so it will be added in an update.

Renaming

As of now, it is impossible to rename a table or a column in database. It can (and will) be done in a later update.

Improvements

There are a number of improvement that can be done in the future to further develop the tool into an unavoidable one.

SQL Group methods

Being able to use SQL group methods is an improvement that has a lot of advantages in terms of performance.

Transactions

It requires a certain amount of testing, but having an implementation of a transaction system for the DAO is always a good feature.

Table locks

Table locks can be the last improvement in order to create a truly thread safe DAO. It will probably be used only for SQL checking but might make the whole data structure management safer.

Renaming

Being able to rename SQL tables and columns is a welcomed feature for a lot of reasons. It will require a few R&D to be sure it is full functional and safe to use.

Requests column selection

Being able to manually SELECT or UPDATE columns is an interesting feature to implement when it coms to data management. It would allow a grater freedom for requests and performances.

Automatic data retrieval

Not being able to know the value of columns automatically inserted in an SQL table is a big oversight depending on the way the DAO is used. It might be mandatory to add it in the future.

Secure table names

Having a safer way to call and table names that will limit name collisions is not a mandatory but welcome feature.