protected virtual string formatColumn(TableMigratorInfo table, ColumnMigratorInfo column)
{
var isPrimaryKeyKeywordInline = shouldFormatPrimaryKeyInlinse(table);
var columnDbTypeString = formatColumnDataTypeString(column.Type, column.DbTypeParameter);
var isPrimaryKey = table.PrimaryKey?.Split(',')?.Contains(column.ColumnName, new StringComparer()) ?? false;
StringBuilder builder = new StringBuilder(databaseType.EscapeSqlIdentifier(column.ColumnName));
builder.Append(" ");
builder.Append(columnDbTypeString);
builder.Append(" ");
builder.Append(column.AllowNull ? "NULL" : "NOT NULL");
if (isPrimaryKey)
{
if (isPrimaryKeyKeywordInline)
{
builder.Append(" PRIMARY KEY");
}
if (table.AutoIncrement)
{
builder.Append(" ");
builder.Append(formatAutoIncrement());
}
}
return(builder.ToString());
}
private static Sql[] getAutoIncrementQuery(TableMigratorInfo table)
{
return(new Sql[0]);
var generatorTableName = Regex.Replace(table.TableName, "[^A-Za-z]", "_");
var generatorName = $"gen_{generatorTableName}_id";
//var createGenerator = $"CREATE GENERATOR {generatorName};";
//var setGenerator = $"SET SEQUENCE {generatorName} TO 0;";
// var createTrigger = $@"set term !! ;
//CREATE TRIGGER {generatorTableName}_BI FOR {table.TableName}
//ACTIVE BEFORE INSERT POSITION 0
//AS
//BEGIN
//if (NEW.ID is NULL) then NEW.ID = GEN_ID({generatorName}, 1);
//END!!
//set term ; !!";
//return new[] { new Sql(createGenerator), new Sql(setGenerator), new Sql(createTrigger) };
var createGenerator = $"CREATE SEQUENCE {generatorName};";
var createTrigger = $@"set term !! ;
CREATE TRIGGER {generatorTableName}_BI FOR {table.TableName}
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
NEW.ID = next value for {generatorName};
END!!
set term ; !!";
return(new[] { new Sql(createGenerator), new Sql(createTrigger) });
}
public void Can_parse_table_info_extended()
{
var tableInfo = TableMigratorInfo.FromPoco(typeof(ExtendedTestClass));
Assert.AreEqual("testClass", tableInfo.TableName);
Assert.IsTrue(tableInfo.PrimaryKey.Equals("Id,Name", StringComparison.InvariantCultureIgnoreCase));
Assert.IsFalse(tableInfo.AutoIncrement);
}
protected virtual string formatPrimaryKeys(TableMigratorInfo tableInfo)
{
var keys = tableInfo.PrimaryKey.Split(',');
if (tableInfo.AutoIncrement)
{
return("");
}
return($"PRIMARY KEY ({String.Join(",", keys.Select(databaseType.EscapeSqlIdentifier))})");
}
public virtual Sql[] CreateTable(TableMigratorInfo table, ColumnMigratorInfo[] columns)
{
var isPrimaryKeyKeywordInline = shouldFormatPrimaryKeyInlinse(table);
var columnString = String.Join(", ", columns.Select(x => formatColumn(table, x)));
if (!isPrimaryKeyKeywordInline && !String.IsNullOrWhiteSpace(table.PrimaryKey))
{
columnString += $", ";
columnString += formatPrimaryKeys(table);
}
return(columnString.Length != 0 ? new[] { new Sql($"CREATE TABLE {databaseType.EscapeTableName(table.TableName)} ({columnString})") } : throw new Exception("Cannot create table without columns"));
}
public override Sql[] CreateTable(TableMigratorInfo table, ColumnMigratorInfo[] columns)
{
var isPrimaryKeyKeywordInline = shouldFormatPrimaryKeyInlinse(table);
var columnString = String.Join(",\n", columns.Select(x => formatColumn(table, x)));
if (!isPrimaryKeyKeywordInline && !String.IsNullOrWhiteSpace(table.PrimaryKey))
{
columnString += $", ";
columnString += formatPrimaryKeys(table);
}
if (columnString.Length != 0)
{
var primaryQuery = new[] {
new Sql($"CREATE TABLE {databaseType.EscapeTableName(table.TableName)} (\n{columnString} )")
};
return((table.AutoIncrement) ? getAutoIncrementQuery(table).Concat(primaryQuery).ToArray() : primaryQuery);
}
throw new Exception("Cannot create table without columns");
}
public override Sql[] CreateTable(TableMigratorInfo table, ColumnMigratorInfo[] columns)
{
return(base.CreateTable(table, columns));
}
protected virtual bool shouldFormatPrimaryKeyInlinse(TableMigratorInfo table)
{
return((table.PrimaryKey?.Split(',')?.Length ?? 0) <= 1 || table.AutoIncrement);
}
public CreateTableQueryProvider(Database Database, IMigratorDatabaseType MigratorSqlSyntaxProvider, TableMigratorInfo Table) : base(Database, MigratorSqlSyntaxProvider, Table)
{
}
protected override string formatPrimaryKeys(TableMigratorInfo tableInfo)
{
var keys = tableInfo.PrimaryKey.Split(',');
return($"CONSTRAINT PK PRIMARY KEY ({String.Join(",", keys.Select(databaseType.EscapeSqlIdentifier))})");
}
