public override void BuildColumnModifySql(DbObjectChange change, DbColumnInfo column, DbScriptOptions options = DbScriptOptions.None)
{
var colSpec = GetColumnSpec(column, options);
var tbl = column.Table;
var scriptType = options.IsSet(DbScriptOptions.CompleteColumnSetup) ? DbScriptType.ColumnSetupComplete : DbScriptType.ColumnModify;
change.AddScript(scriptType, "ALTER TABLE {0} MODIFY COLUMN {1};", tbl.FullName, colSpec);
}
protected override string GetColumnSpec(DbColumnInfo column, DbScriptOptions options)
{
var typeStr = column.TypeInfo.DbTypeSpec;
var nullable = options.IsSet(DbScriptOptions.ForceNull) || column.Flags.IsSet(DbColumnFlags.Nullable);
var nullStr = nullable ? "NULL" : "NOT NULL";
var strAutoInc = string.Empty;
bool isNew = options.IsSet(DbScriptOptions.NewColumn);
if (isNew && column.Flags.IsSet(DbColumnFlags.Identity))
{
// MySql requires that auto-incr column is supported by a key - either a primary key, or an index
var strKeyType = column.Flags.IsSet(DbColumnFlags.PrimaryKey) ? "PRIMARY KEY" : "KEY";
strAutoInc = $"AUTO_INCREMENT, {strKeyType}({column.ColumnNameQuoted})";
}
string defaultStr = null;
//Default constraint can be set only on new columns
if (!string.IsNullOrWhiteSpace(column.DefaultExpression) && options.IsSet(DbScriptOptions.NewColumn))
{
defaultStr = "DEFAULT " + column.DefaultExpression;
}
var spec = $" {column.ColumnNameQuoted} {typeStr} {nullStr} {strAutoInc}";
return(spec);
}
//ALTER TABLE employees ALTER COLUMN [employee_name] nvarchar(100) Null;
public virtual void BuildColumnModifySql(DbObjectChange change, DbColumnInfo column, DbScriptOptions options = DbScriptOptions.None)
{
var scriptType = options.IsSet(DbScriptOptions.CompleteColumnSetup) ? DbScriptType.ColumnSetupComplete : DbScriptType.ColumnModify;
if(ShouldResetNullsToDefault(column)) {
BuildColumnSetDefaultValuesSql(change, column);
scriptType = DbScriptType.ColumnSetupComplete;
}
var colSpec = GetColumnSpec(column);
change.AddScript(scriptType,
"ALTER TABLE {0} ALTER COLUMN {1};", column.Table.FullName, colSpec);
}
public override void BuildColumnModifySql(DbObjectChange change, DbColumnInfo column, DbScriptOptions options = DbScriptOptions.None)
{
//SqlCe does not allow any modification of 'ntext'/memo columns
var dbType = column.TypeInfo.VendorDbType.DbType;
bool isNText = column.TypeInfo.Size < 0 && (dbType == DbType.String || dbType == DbType.Binary);
if(isNText) {
change.NotSupported("Modifying ntext column not supported in SqlCE. Column: {0}.{1}", column.Table.TableName, column.ColumnName);
return;
}
base.BuildColumnModifySql(change, column, options);
}
public override void BuildColumnModifySql(DbObjectChange change, DbColumnInfo column, DbScriptOptions options = DbScriptOptions.None)
{
if(ShouldResetNullsToDefault(column))
BuildColumnSetDefaultValuesSql(change, column);
// In Pg you modify column one aspect at a time; setting TYPE and Nullable requires 2 calls
change.AddScript(DbScriptType.ColumnModify, "ALTER TABLE {0} ALTER COLUMN \"{1}\" TYPE {2};",
column.Table.FullName, column.ColumnName, column.TypeInfo.SqlTypeSpec);
var nullStr = column.Flags.IsSet(DbColumnFlags.Nullable) ? "DROP NOT NULL" : "SET NOT NULL";
change.AddScript(DbScriptType.ColumnSetupComplete, "ALTER TABLE {0} ALTER COLUMN \"{1}\" {2};",
column.Table.FullName, column.ColumnName, nullStr);
}
protected override string GetColumnSpec(DbColumnInfo column, DbScriptOptions options = DbScriptOptions.None)
{
// See https://www.sqlite.org/autoinc.html
// auto-inc columns is always INT64, but s
if (column.Flags.IsSet(DbColumnFlags.Identity))
{
return(column.ColumnName + " INTEGER PRIMARY KEY NOT NULL"); //
}
var spec = base.GetColumnSpec(column, options);
return(spec);
}
protected virtual string GetColumnSpec(DbColumnInfo column, DbScriptOptions options = DbScriptOptions.None)
{
var typeStr = column.TypeInfo.DbTypeSpec;
var nullable = options.IsSet(DbScriptOptions.ForceNull) || column.Flags.IsSet(DbColumnFlags.Nullable);
var nullStr = nullable ? "NULL" : "NOT NULL";
string defaultStr = null;
if (!string.IsNullOrWhiteSpace(column.DefaultExpression))
{
defaultStr = "DEFAULT " + column.DefaultExpression;
}
var spec = $" {column.ColumnNameQuoted} {typeStr} {defaultStr} {nullStr}";
return(spec);
}
protected virtual string GetColumnSpec(DbColumnInfo column, DbScriptOptions options = DbScriptOptions.None)
{
var typeStr = column.TypeInfo.SqlTypeSpec;
var nullable = options.IsSet(DbScriptOptions.ForceNull) || column.Flags.IsSet(DbColumnFlags.Nullable);
var nullStr = nullable ? "NULL" : "NOT NULL";
string defaultStr = null;
if (!string.IsNullOrWhiteSpace(column.DefaultExpression))
{
defaultStr = "DEFAULT " + column.DefaultExpression;
}
var spec = string.Format(" \"{0}\" {1} {2} {3}", column.ColumnName, typeStr, defaultStr, nullStr);
return(spec);
}
}//method
protected override string GetColumnSpec(DbColumnInfo column, DbScriptOptions options)
{
if (column.Flags.IsSet(DbColumnFlags.Identity))
{
if (column.TypeInfo.SqlTypeSpec == "bigint")
{
return(string.Format(@"""{0}"" BIGSERIAL ", column.ColumnName));
}
else
{
return(string.Format(@"""{0}"" SERIAL ", column.ColumnName));
}
}
return(base.GetColumnSpec(column, options));
}
}//method
protected override string GetColumnSpec(DbColumnInfo column, DbScriptOptions options)
{
if (column.Flags.IsSet(DbColumnFlags.Identity))
{
if (column.TypeInfo.DbTypeSpec == "bigint")
{
return($"{column.ColumnNameQuoted} BIGSERIAL ");
}
else
{
return($"{column.ColumnNameQuoted} SERIAL ");
}
}
return(base.GetColumnSpec(column, options));
}
public override void BuildColumnAddSql(DbObjectChange change, DbColumnInfo column, DbScriptOptions options)
{
var colSpec = GetColumnSpec(column, options);
if(!column.Flags.IsSet(DbColumnFlags.Nullable)) {
var dft = column.TypeInfo.VendorDbType.DefaultColumnInit;
if (string.IsNullOrWhiteSpace(dft))
dft = column.TypeInfo.ToLiteral(new byte[] {0});
colSpec += " DEFAULT " + dft;
}
//workaround for unit test with renaming table - ignore rename, use old table
var tbl = column.Table;
if (tbl.Peer != null)
tbl = tbl.Peer; //use old table name
change.AddScript(DbScriptType.ColumnAdd, "ALTER TABLE {0} ADD {1};", tbl.FullName, colSpec);
}
protected override string GetColumnSpec(DbColumnInfo column, DbScriptOptions options = DbScriptOptions.None)
{
var typeStr = column.TypeInfo.SqlTypeSpec;
var nullable = options.IsSet(DbScriptOptions.ForceNull) || column.Flags.IsSet(DbColumnFlags.Nullable);
var nullStr = nullable ? "NULL" : "NOT NULL";
var idStr = string.Empty;
bool isNew = column.Peer == null;
if(isNew && column.Flags.IsSet(DbColumnFlags.Identity)) {
idStr = "IDENTITY(1,1)";
}
string defaultStr = null;
//Default constraint can be set only on new columns in SQL server
if(!string.IsNullOrWhiteSpace(column.DefaultExpression) && isNew)
defaultStr = "DEFAULT " + column.DefaultExpression;
var spec = string.Format(" \"{0}\" {1} {2} {3} {4}", column.ColumnName, typeStr, idStr, defaultStr, nullStr);
return spec;
}
protected override string GetColumnSpec(DbColumnInfo column, DbScriptOptions options)
{
var typeStr = column.TypeInfo.SqlTypeSpec;
var nullable = options.IsSet(DbScriptOptions.ForceNull) || column.Flags.IsSet(DbColumnFlags.Nullable);
var nullStr = nullable ? "NULL" : "NOT NULL";
var strAutoInc = string.Empty;
bool isNew = column.Peer == null;
if(isNew && column.Flags.IsSet(DbColumnFlags.Identity)) {
// MySql requires that auto-incr column is supported by a key - either a primary key, or an index
var strKeyType = column.Flags.IsSet(DbColumnFlags.PrimaryKey) ? "PRIMARY KEY" : "KEY";
strAutoInc = string.Format("AUTO_INCREMENT, {0}(\"{1}\")", strKeyType, column.ColumnName);
}
string defaultStr = null;
//Default constraint can be set only on new columns
if(!string.IsNullOrWhiteSpace(column.DefaultExpression) && options.IsSet(DbScriptOptions.NewColumn))
defaultStr = "DEFAULT " + column.DefaultExpression;
var spec = string.Format(@" ""{0}"" {1} {2} {3}", column.ColumnName, typeStr, nullStr, strAutoInc);
return spec;
}
protected override string GetColumnSpec(DbColumnInfo column, DbScriptOptions options = DbScriptOptions.None)
{
var typeStr = column.TypeInfo.DbTypeSpec;
var nullable = options.IsSet(DbScriptOptions.ForceNull) || column.Flags.IsSet(DbColumnFlags.Nullable);
var nullStr = nullable ? "NULL" : "NOT NULL";
var idStr = string.Empty;
bool isNew = column.Peer == null;
if (isNew && column.Flags.IsSet(DbColumnFlags.Identity))
{
idStr = "IDENTITY(1,1)";
}
string defaultStr = null;
//Default constraint can be set only on new columns in SQL server
if (!string.IsNullOrWhiteSpace(column.DefaultExpression) && isNew)
{
defaultStr = "DEFAULT " + column.DefaultExpression;
}
var spec = $" {column.ColumnNameQuoted} {typeStr} {idStr} {defaultStr} {nullStr}";
return(spec);
}
//not supported
public override void BuildColumnModifySql(DbObjectChange change, DbColumnInfo column, DbScriptOptions options = DbScriptOptions.None)
{
}
protected override string GetColumnSpec(DbColumnInfo column, DbScriptOptions options)
{
if(column.Flags.IsSet(DbColumnFlags.Identity)) {
if(column.TypeInfo.SqlTypeSpec == "bigint")
return string.Format(@"""{0}"" BIGSERIAL ", column.ColumnName);
else
return string.Format(@"""{0}"" SERIAL ", column.ColumnName);
}
return base.GetColumnSpec(column, options);
}
public override void BuildColumnModifySql(DbObjectChange change, DbColumnInfo column, DbScriptOptions options = DbScriptOptions.None)
{
if (ShouldResetNullsToDefault(column))
{
BuildColumnSetDefaultValuesSql(change, column);
}
// In Pg you modify column one aspect at a time; setting TYPE and Nullable requires 2 calls
change.AddScript(DbScriptType.ColumnModify, "ALTER TABLE {0} ALTER COLUMN \"{1}\" TYPE {2};",
column.Table.FullName, column.ColumnName, column.TypeInfo.SqlTypeSpec);
var nullStr = column.Flags.IsSet(DbColumnFlags.Nullable) ? "DROP NOT NULL" : "SET NOT NULL";
change.AddScript(DbScriptType.ColumnSetupComplete, "ALTER TABLE {0} ALTER COLUMN \"{1}\" {2};",
column.Table.FullName, column.ColumnName, nullStr);
}
public override void BuildColumnModifySql(DbObjectChange change, DbColumnInfo column, DbScriptOptions options = DbScriptOptions.None)
{
var colSpec = GetColumnSpec(column, options);
var tbl = column.Table;
var scriptType = options.IsSet(DbScriptOptions.CompleteColumnSetup) ? DbScriptType.ColumnSetupComplete : DbScriptType.ColumnModify;
change.AddScript(scriptType, $"ALTER TABLE {tbl.FullName} MODIFY COLUMN {colSpec};");
}
public static bool IsSet(this DbScriptOptions flags, DbScriptOptions flag)
{
return((flags & flag) != 0);
}
//ALTER TABLE employees ALTER COLUMN [employee_name] nvarchar(100) Null;
public virtual void BuildColumnModifySql(DbObjectChange change, DbColumnInfo column, DbScriptOptions options = DbScriptOptions.None)
{
var scriptType = options.IsSet(DbScriptOptions.CompleteColumnSetup) ? DbScriptType.ColumnSetupComplete : DbScriptType.ColumnModify;
if (ShouldResetNullsToDefault(column))
{
BuildColumnSetDefaultValuesSql(change, column);
scriptType = DbScriptType.ColumnSetupComplete;
}
var colSpec = GetColumnSpec(column);
change.AddScript(scriptType, $"ALTER TABLE {column.Table.FullName} ALTER COLUMN {colSpec};");
}
protected override string GetColumnSpec(DbColumnInfo column, DbScriptOptions options = DbScriptOptions.None)
{
// See https://www.sqlite.org/autoinc.html
// auto-inc columns is always INT64, but s
if(column.Flags.IsSet(DbColumnFlags.Identity))
return column.ColumnName + " INTEGER PRIMARY KEY NOT NULL"; //
var spec = base.GetColumnSpec(column, options);
return spec;
}
public override void BuildColumnModifySql(DbObjectChange change, DbColumnInfo column, DbScriptOptions options = DbScriptOptions.None)
{
//SqlCe does not allow any modification of 'ntext'/memo columns
var dbType = column.TypeInfo.DbType;
bool isNText = column.TypeInfo.Size < 0 && (dbType == DbType.String || dbType == DbType.Binary);
if (isNText)
{
change.NotSupported("Modifying ntext column not supported in SqlCE. Column: {0}.{1}", column.Table.TableName, column.ColumnName);
return;
}
base.BuildColumnModifySql(change, column, options);
}
// ALTER TABLE employees ADD [employee_pwd] nvarchar(20) Null;
// All columns are added as nullable, to allow for existing rows be filled with nulls
// Then extra step columnInit sets default values for types (zeros), and then column is modified to NOT NULL
public virtual void BuildColumnAddSql(DbObjectChange change, DbColumnInfo column, DbScriptOptions options)
{
var colSpec = GetColumnSpec(column, options);
change.AddScript(DbScriptType.ColumnAdd, $"ALTER TABLE {column.Table.FullName} ADD {colSpec};");
}
//not supported
public override void BuildColumnModifySql(DbObjectChange change, DbColumnInfo column, DbScriptOptions options = DbScriptOptions.None)
{
}
protected virtual string GetColumnSpec(DbColumnInfo column, DbScriptOptions options = DbScriptOptions.None)
{
var typeStr = column.TypeInfo.SqlTypeSpec;
var nullable = options.IsSet(DbScriptOptions.ForceNull) || column.Flags.IsSet(DbColumnFlags.Nullable);
var nullStr = nullable ? "NULL" : "NOT NULL";
string defaultStr = null;
if(!string.IsNullOrWhiteSpace(column.DefaultExpression))
defaultStr = "DEFAULT " + column.DefaultExpression;
var spec = string.Format(" \"{0}\" {1} {2} {3}", column.ColumnName, typeStr, defaultStr, nullStr);
return spec;
}
// ALTER TABLE employees ADD [employee_pwd] nvarchar(20) Null;
// All columns are added as nullable, to allow for existing rows be filled with nulls
// Then extra step columnInit sets default values for types (zeros), and then column is modified to NOT NULL
public virtual void BuildColumnAddSql(DbObjectChange change, DbColumnInfo column, DbScriptOptions options)
{
var colSpec = GetColumnSpec(column, options);
change.AddScript(DbScriptType.ColumnAdd, "ALTER TABLE {0} ADD {1};", column.Table.FullName, colSpec);
}
public override void BuildColumnAddSql(DbObjectChange change, DbColumnInfo column, DbScriptOptions options)
{
var colSpec = GetColumnSpec(column, options);
if (!column.Flags.IsSet(DbColumnFlags.Nullable))
{
var dft = column.TypeInfo.VendorDbType.DefaultColumnInit;
if (string.IsNullOrWhiteSpace(dft))
{
dft = column.TypeInfo.ToLiteral(new byte[] { 0 });
}
colSpec += " DEFAULT " + dft;
}
//workaround for unit test with renaming table - ignore rename, use old table
var tbl = column.Table;
if (tbl.Peer != null)
{
tbl = tbl.Peer; //use old table name
}
change.AddScript(DbScriptType.ColumnAdd, "ALTER TABLE {0} ADD {1};", tbl.FullName, colSpec);
}
public static bool IsSet(this DbScriptOptions flags, DbScriptOptions flag)
{
return (flags & flag) != 0;
}