Fluent DDL Builder

Obsolete

The fluent builder is a helper that makes easier the writing of Create/Alter table scripts. Basically you're using C# instead of Sql. It is NOT an object to table mapper and it will never be. It's designed to support most common DDL features so that it's usable in the majority of real life usage cases.

Supported databases

• SqlServer
• MySql
• Postgres
• Sqlite - partially, only create table scripts

Generating a Create Table script

The manual way

var db= SqlFuDao.GetConnection();
var builder= db.DatabaseTools().GetCreateTableBuilder("MessageQueue", IfTableExists.DropIt);
builder.Columns
            .Add("Id", DbType.Int64, isNullable: false, autoIncrement: true).AsPrimaryKey("PK_store")
            .Add("MessageId", DbType.Guid, isNullable: false).AsUnique("UK_MessageQueueStore")
            .Add("Name",DbType.AnsiString,"200",false)
            .Add("Body", DbType.Binary)
            .Add("QueuedAt", DbType.DateTime, isNullable: false)
            .Add("ShouldRunAt", DbType.DateTime, isNullable: false)
            .Add("CompletedAt", DbType.DateTime)
            .Add("FailCount", DbType.Int16, isNullable: false, defaultValue: "0")
            .Add("Hash", DbType.AnsiStringFixedLength, "40", false).AsUnique("UC_MessageHash")
            .Add("Priority",DbType.String,"10").IfDatabaseIs(DbEngine.MySql)
                 .RedefineColumnAs("enum('low','normal','high')")
            .Add("IgnoreIt",DbType.Boolean)
                 ;
//customize primary key for a specific rdbms
builder.Constraints["PK_store"]
                .IfDatabaseIs(DbEngine.SqlServer)
                .PrimaryKeyOptions(SqlServerOptions.PrimaryKeyIs.Clustered());

//customize table for a specific rdbms
builder.TableOptionsFor(DbEngine.MySql, MySqlOptions.Table.EngineIs(TableEngineType.InnoDb));

//create unique index
builder.Indexes.AddIndexOn("Name", true);

//show generated script
Console.WriteLine(builder.GetSql());

//execute generated script
builder.ExecuteDDL();

db.Dispose();

Some guidelines

• If size is not specified, it's assumed to be maximum size.
• By default, every column is nullable.
• Constraint and index names are optional, however is good practice to have them, especially if you want to customize things for a specific rdbms.
• It isn't required to add the constraints when adding the columns. You can add them later via the [builder].Constraints property. Same with indexes.
• Some rdbms specific options are supported, as shown in the example above.
• Don't forget this is a way of writing DDL scripts using C#, it's not a mapper.

Customize script for different rdbms

Usually, the builder knows how to deal with each of the supported rdbms to generate a valid script. However, sometimes you want to have more control when dealing with a certain db or to activate specific options (in the example, we set the option that for MySql the table engine should be InnoDb).

The supported db specific options are available as "[DbEngine]Options". If you can't find an option, it means it isn't supported.

You can customize items in a couple of ways:

• Columns
  • RedefineAs - Tell the builder to use the provided definition for that column. The definition must not contain the column name.
  • SetColumnOptions - Use the [DbEngine]Options class to access supported options.
• Constraints
  • Redefine - The builder will use the provided definition. You must provide the complete definition including constraint name.
  • AddConstraint - Adds a new constraint only for that db. Same rules for definition as for Redefine.
  • PrimaryKeyOptions - Use the [DbEngine]Options class to access supported options.
• Index
  • AddCustom - Adds a new index only for that db. Full definition must be provided.
  • RedefineAs - The builder will use the provided definition. You must provide the complete index definition.
  • SetOptions - Use the [DbEngine]Options class to access supported options.

Automatic Way

You can create the script directly from a POCO, then customize it further

[Table("Users",CreationOptions=IfTableExists.DropIt)]
[PrimaryKey("Id",AutoIncrement = true,Name="PK_Users")]
[Index("Email",Name="ix_email",IsUnique=true)]
public class User
{
    public int Id {get;set;}

    [ColumnOptions(Size = "50")]
    public string Name {get;set;}
    public string Email {get;set;}
    public DateTime RegisteredAt { get; set; }
    public Guid? InternalId {get;set;}

    [RedefineFor(DbEngine.MySql, "enum('Member','Admin')")]  
    [ColumnOptions(DefaultValue = "Member",Size = "50")]
    public string Rights {get;set;}
    public MyEnum Something {get;set;}

   [ForeignKey("ParentTable","ParentColumn",OnDelete = ForeignKeyRelationCascade.Cascade)]
    public int FkExample {get;set;} 
}

var builder=db.DatabaseTools().GetCreateTableBuilder<User>();

//customize it further
builder.TableOptionsFor(DbEngine.MySql,MySqlOptions.Table.EngineIs(TableEngineType.InnoDb));
builder.ExecuteDDL();

Things to know

• When creating the script from a POCO , columns are not Nullable by default. This is obvious for value types like int, however for strings, if you want to be nullable you have to use the [ColumnOptions] attribute and set the IsNullable property to true.
• Enums are considered to be the underlying type (Int32 for 99% of cases). If you want to be treated as strings, use the [InsertAsString] attribute
• You can set how many [Index] attributes you need, but only one [PrimaryKey] . You can create multi column pk or index.

Generating an Alter Table script

You can add/modify/drop columns and you can add/drop constraints and indexes.

Note: Sqlite provider doesn't support this type of builder.

var builder=db.DatabaseTools().GetAlterTableBuilder("users");

//write alter column, some examples
builder.Columns.Change("Name")
    .RenameTo("Name1")
    .SetDefault("test")
    .DropDefault()
    .SetNullable(true)
    .SetType(DbType.Int32)
    .SetCollation("CI_AI_Latin_General");

//adding new columns
builder.Columns.Add("Another",DbType.Int32);

//drop column
builder.Columns.Drop("Name").WithoutOptions();

//dropping column with db specific options
builder.Columns.Drop("Name")
    .WithOptionsForDatabase(DbEngine.PostgreSQL,
                            PostgresOptions.Drop.Column(DropOptionType.Cascade));

//dropping named constraints
builder.Constraints.Drop("uk_email").WithoutOptions();

//drop primary key
builder.Constraints.DropPrimaryKey().WithoutOptions();

//drop index
builder.Indexes.Drop("ix_email").WithoutOptions();

builder.ExecuteDDL();