//ALTER TABLE employees DROP COLUMN "employee_num"; public override void BuildColumnDropSql(DbObjectChange change, DbColumnInfo column) { if (!string.IsNullOrEmpty(column.DefaultExpression)) { change.AddScript(DbScriptType.ColumnModify, "ALTER TABLE {0} DROP CONSTRAINT \"{1}\";", column.Table.FullName, column.DefaultConstraintName); } //Note: the column drop comes after table-rename, so it might be table is already renamed, and we have to get its new name var tableName = column.Table.Peer.FullName; //new name if renamed change.AddScript(DbScriptType.ColumnDrop, "ALTER TABLE {0} DROP COLUMN \"{1}\";", tableName, column.ColumnName); }
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 {column.Table.FullName} ALTER COLUMN {column.ColumnNameQuoted} TYPE {column.TypeInfo.DbTypeSpec};"); var nullStr = column.Flags.IsSet(DbColumnFlags.Nullable) ? " DROP NOT NULL" : " SET NOT NULL"; change.AddScript(DbScriptType.ColumnSetupComplete, $"ALTER TABLE {column.Table.FullName} ALTER COLUMN {column.ColumnNameQuoted}{nullStr};"); }
public override void BuildCustomTypeAddSql(DbObjectChange change, DbCustomTypeInfo typeInfo) { var sqlCreateTemplate = "CREATE TYPE {0} AS TABLE ([Value] Sql_Variant);"; var sqlGrantTemplate = "Grant EXECUTE on TYPE::{0} to {1};"; change.AddScript(DbScriptType.CustomTypeAdd, sqlCreateTemplate, typeInfo.FullName); if (!string.IsNullOrWhiteSpace(Settings.GrantExecReadToRole)) { change.AddScript(DbScriptType.CustomTypeAdd, sqlGrantTemplate, typeInfo.FullName, Settings.GrantExecReadToRole); } if (!string.IsNullOrWhiteSpace(Settings.GrantExecWriteToRole) && Settings.GrantExecWriteToRole != Settings.GrantExecReadToRole) { change.AddScript(DbScriptType.CustomTypeAdd, sqlGrantTemplate, typeInfo.FullName, Settings.GrantExecWriteToRole); } }
public override void BuildPrimaryKeyAddSql(DbObjectChange change, DbKeyInfo key) { // PK for Identity (Auto-increment) columns is created when table/ID columns is created if (key.KeyColumns[0].Column.Flags.IsSet(DbColumnFlags.Identity)) { change.AddScript(DbScriptType.PrimaryKeyAdd, "-- PrimaryKeyAdd empty action"); return; } var tn = key.Table.FullName; var pkFields = key.KeyColumns.GetSqlNameList(); // PK name is always 'PRIMARY' change.AddScript(DbScriptType.PrimaryKeyAdd, $"ALTER TABLE {tn} ADD CONSTRAINT PRIMARY KEY ({pkFields});"); }
public override void BuildSequenceAddSql(DbObjectChange change, DbSequenceInfo sequence) { const string sqlCreateTemplate = "CREATE Sequence {0} AS {1} START WITH {2} INCREMENT BY {3};"; const string sqlGrantTemplate = "Grant UPDATE on {0} to {1};"; change.AddScript(DbScriptType.SequenceAdd, sqlCreateTemplate, sequence.FullName, sequence.DbType.SqlTypeSpec, sequence.StartValue, sequence.Increment); //Grant permission to UPDATE var updateRole = this.Settings.GrantExecWriteToRole; if (!string.IsNullOrWhiteSpace(updateRole)) { change.AddScript(DbScriptType.Grant, sqlGrantTemplate, sequence.FullName, updateRole); } }
public override void BuildIndexAddSql(DbObjectChange change, DbKeyInfo key) { var driver = this.Settings.Driver; var unique = key.KeyType.IsSet(KeyType.Unique) ? "UNIQUE" : string.Empty; string clustered = GetClusteredExpression(key); string indexFields; if (driver.Supports(DbFeatures.OrderedColumnsInIndexes)) { indexFields = key.KeyColumns.GetSqlNameListWithOrderSpec(); } else { indexFields = key.KeyColumns.GetSqlNameList(); } var qKeyName = QuoteName(key.Name); string includeList = (key.IncludeColumns.Count == 0) ? string.Empty : "INCLUDE (" + key.IncludeColumns.GetSqlNameList() + ")"; string wherePred = (key.Filter == null) ? string.Empty: "WHERE " + key.Filter.DefaultSql; var script = $@" CREATE {unique} {clustered} INDEX {qKeyName} ON {key.Table.FullName} ( {indexFields} ) {includeList} {wherePred} ;"; var phase = key.KeyType.IsSet(KeyType.Clustered) ? ApplyPhase.Early : ApplyPhase.Default; change.AddScript(DbScriptType.IndexAdd, phase, script); }
public virtual void BuildColumnSetDefaultValuesSql(DbObjectChange change, DbColumnInfo column) { var fullTableRef = column.Table.FullName; change.AddScript(DbScriptType.ColumnInit, "UPDATE {0} SET \"{1}\" = {2} WHERE \"{1}\" IS NULL;", fullTableRef, column.ColumnName, column.TypeInfo.InitExpression); }
public override void BuildIndexAddSql(DbObjectChange change, DbKeyInfo key) { const string CreateIndexTemplate = @" CREATE {0} {1} INDEX {2} ON {3} ( {4} ) {5} {6} "; var unique = key.KeyType.IsSet(KeyType.Unique) ? "UNIQUE" : string.Empty; string clustered = GetClusteredExpression(key); var indexFields = key.KeyColumns.GetSqlNameListWithOrderSpec(); var qKeyName = '"' + key.Name + '"'; string includeList = string.Empty; if (key.IncludeColumns.Count > 0) { includeList = "INCLUDE (" + key.IncludeColumns.GetSqlNameList() + ")"; } string wherePred = string.Empty; if (!string.IsNullOrWhiteSpace(key.Filter)) { wherePred = "WHERE " + key.Filter; } var phase = key.KeyType.IsSet(KeyType.Clustered) ? ApplyPhase.Early : ApplyPhase.Default; change.AddScript(DbScriptType.IndexAdd, phase, CreateIndexTemplate, unique, clustered, qKeyName, key.Table.FullName, indexFields, includeList, wherePred); }
public override void BuildColumnSetDefaultValuesSql(DbObjectChange change, DbColumnInfo column) { const string sqlUpdateValues = "UPDATE {0} SET \"{1}\" = {2} WHERE \"{1}\" IS NULL;"; var fullTableRef = column.Table.FullName; change.AddScript(DbScriptType.ColumnInit, sqlUpdateValues, fullTableRef, column.ColumnName, column.TypeInfo.InitExpression); }
public override void BuildViewAddSql(DbObjectChange change, DbTableInfo view) { const string sqlTemplate = @"CREATE VIEW {0} {1} AS {2}"; //notice - no ';' at the end, SQL must have it already // For materialized views add 'With SCHEMABINDING' attribute var attrs = view.IsMaterializedView ? "WITH SCHEMABINDING" : string.Empty; change.AddScript(DbScriptType.ViewAdd, sqlTemplate, view.FullName, attrs, view.ViewSql); const string GrantSelectTemplate = "GRANT SELECT ON {0} TO [{1}];"; if (!string.IsNullOrWhiteSpace(Settings.GrantExecReadToRole)) { change.AddScript(DbScriptType.Grant, GrantSelectTemplate, view.FullName, Settings.GrantExecReadToRole); } }
public override void BuildIndexDropSql(DbObjectChange change, DbKeyInfo key) { //for indexes on DB views clustered index must be dropped last and created first var applyPhase = key.KeyType.IsSet(KeyType.Clustered) ? ApplyPhase.Late : ApplyPhase.Default; change.AddScript(DbScriptType.IndexDrop, applyPhase, "DROP INDEX \"{0}\" ON {1};", key.Name, key.Table.FullName); }
public override void BuildPrimaryKeyAddSql(DbObjectChange change, DbKeyInfo key) { var pkFields = key.KeyColumns.GetSqlNameList(); var clustered = GetClusteredExpression(key); change.AddScript(DbScriptType.PrimaryKeyAdd, "ALTER TABLE {0} ADD CONSTRAINT \"{1}\" PRIMARY KEY {2} ({3});", key.Table.FullName, key.Name, clustered, pkFields); }
public override void BuildViewDropSql(DbObjectChange change, DbTableInfo view) { var matzed = view.IsMaterializedView ? "MATERIALIZED" : string.Empty; change.AddScript(DbScriptType.ViewDrop, "DROP {0} VIEW {1};", matzed, view.FullName); //base.BuildViewDropSql(change, view); }
public override void BuildSequenceAddSql(DbObjectChange change, DbSequenceInfo sequence) { var start = (sequence.StartValue < 1) ? 1 : sequence.StartValue; const string sqlTemplate = "CREATE Sequence {0} START WITH {1} INCREMENT BY {2};"; change.AddScript(DbScriptType.SequenceAdd, sqlTemplate, sequence.FullName, start, sequence.Increment); }
public override void BuildSequenceAddSql(DbObjectChange change, DbSequenceInfo sequence) { var start = (sequence.StartValue < 1) ? 1 : sequence.StartValue; change.AddScript(DbScriptType.SequenceAdd, $"CREATE Sequence {sequence.FullName} START WITH {start} INCREMENT BY {sequence.Increment};"); }
public override void BuildTableAddSql(DbObjectChange change, DbTableInfo table) { const string SqlTemplate = @"CREATE TABLE {0} (" + "\r\n {1} \r\n); "; var specs = table.Columns.Select(c => GetColumnSpec(c)).ToList(); //Until now it was the same as default impl method in base class. Now we need to add Primary key and Foreign key constraints //Primary Key var pk = table.PrimaryKey; var col0 = pk.KeyColumns[0].Column; //Identity Primary Key is taken care of in GetColumnSpec // Note: looks like we need to declare identity PK in GetColumnSpec - SQLite is quite tricky in this way if (!col0.Flags.IsSet(DbColumnFlags.Identity)) { var strKeyCols = pk.KeyColumns.GetSqlNameList(); var pkSpec = string.Format("PRIMARY KEY({0})", strKeyCols); specs.Add(pkSpec); } //Foreign keys (ref constraints foreach (var refC in table.RefConstraints) { var strKeyCols = refC.FromKey.KeyColumns.GetSqlNameList(); //find target table var strPkKeyCols = refC.ToKey.KeyColumns.GetSqlNameList(); var fkSpec = string.Format("FOREIGN KEY({0}) REFERENCES {1}({2})", strKeyCols, refC.ToKey.Table.TableName, strPkKeyCols); if (refC.CascadeDelete) { fkSpec += " ON DELETE CASCADE"; } specs.Add(fkSpec); } //Build final Table statement var columnSpecs = string.Join("," + Environment.NewLine, specs); change.AddScript(DbScriptType.TableAdd, SqlTemplate, table.FullName, columnSpecs); }
public virtual void BuildIndexAddSql(DbObjectChange change, DbKeyInfo key) { const string CreateIndexTemplate = @" CREATE {0} INDEX {1} ON {2} ( {3} ) {4} {5} "; var driver = this.Settings.Driver; var unique = key.KeyType.IsSet(KeyType.Unique) ? "UNIQUE" : string.Empty; string indexFields; if (driver.Supports(DbFeatures.OrderedColumnsInIndexes)) { indexFields = key.KeyColumns.GetSqlNameListWithOrderSpec(); } else { indexFields = key.KeyColumns.GetSqlNameList(); } var qKeyName = '"' + key.Name + '"'; string includeList = string.Empty; if (key.IncludeColumns.Count > 0 && driver.Supports(DbFeatures.IncludeColumnsInIndexes)) { includeList = "INCLUDE (" + key.IncludeColumns.GetSqlNameList() + ")"; } string wherePred = string.Empty; if (!string.IsNullOrWhiteSpace(key.Filter) && driver.Supports(DbFeatures.FilterInIndexes)) { wherePred = "WHERE " + key.Filter; } change.AddScript(DbScriptType.IndexAdd, CreateIndexTemplate, unique, qKeyName, key.Table.FullName, indexFields, includeList, wherePred); }
public override void BuildTableConstraintDropSql(DbObjectChange change, DbKeyInfo key) { if (key.KeyType == Entities.Model.KeyType.PrimaryKey) { change.AddScript(DbScriptType.RefConstraintDrop, "ALTER TABLE {0} DROP PRIMARY KEY;", key.Table.FullName); } }
public override void BuildSequenceAddSql(DbObjectChange change, DbSequenceInfo sequence) { const string sqlCreateTemplate = "CREATE Sequence {0} AS {1} START WITH {2} INCREMENT BY {3};"; const string sqlGrantTemplate = "Grant UPDATE on {0} to {1};"; var typeName = GetIntDbTypeName(sequence.Definition.DataType); change.AddScript(DbScriptType.SequenceAdd, sqlCreateTemplate, sequence.FullName, typeName, sequence.StartValue, sequence.Increment); //Grant permission to UPDATE var updateRole = this.Settings.GetCustomSetting(MsSqlDbDriver.SettingsKeyGrantExecWriteRole, "public"); if (!string.IsNullOrWhiteSpace(updateRole)) { change.AddScript(DbScriptType.Grant, sqlGrantTemplate, sequence.FullName, updateRole); } }
public override void BuildRefConstraintDropSql(DbObjectChange change, DbRefConstraintInfo dbRefConstraint) { var fromKey = dbRefConstraint.FromKey; var kn = QuoteName(fromKey.Name); change.AddScript(DbScriptType.RefConstraintDrop, $"ALTER TABLE {fromKey.Table.FullName} DROP FOREIGN KEY {kn};"); }
public override void BuildColumnRenameSql(DbObjectChange change, DbColumnInfo oldColumn, DbColumnInfo newColumn) { var colSpec = GetColumnSpec(newColumn, DbScriptOptions.None); var tn = newColumn.Table.FullName; change.AddScript(DbScriptType.ColumnRename, $"ALTER TABLE {tn} CHANGE COLUMN {oldColumn.ColumnNameQuoted} {colSpec};"); }
//ALTER TABLE employees DROP COLUMN [employee_pwd]; public virtual void BuildColumnDropSql(DbObjectChange change, DbColumnInfo column) { //Note: the column drop comes after table-rename, so it might be table is already renamed, and we have to get its new name var tableName = column.Table.Peer.FullName; //new name if renamed change.AddScript(DbScriptType.ColumnDrop, $"ALTER TABLE {tableName} DROP COLUMN {column.ColumnNameQuoted}"); }
public override void BuildViewAddSql(DbObjectChange change, DbTableInfo view) { // For materialized views add 'With SCHEMABINDING' attribute var attrs = view.IsMaterializedView ? "WITH SCHEMABINDING" : string.Empty; //notice - no ';' at the end, SQL must have it already var script = $@"CREATE VIEW {view.FullName} {attrs} AS {view.ViewSql}"; change.AddScript(DbScriptType.ViewAdd, script); //Grant Select var execReadRole = Settings.GetCustomSetting(MsSqlDbDriver.SettingsKeyGrantExecReadRole, "public"); if (!string.IsNullOrWhiteSpace(execReadRole)) { change.AddScript(DbScriptType.Grant, $"GRANT SELECT ON {view.FullName} TO [{execReadRole}];"); } }
public override void BuildCustomTypeDropSql(DbObjectChange change, DbCustomTypeInfo typeInfo) { // We drop only Vita_* automatic types if (typeInfo.Name.StartsWith("Vita_")) { change.AddScript(DbScriptType.CustomTypeDrop, $"DROP TYPE {typeInfo.FullName};"); } }
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 virtual void BuildColumnSetDefaultValuesSql(DbObjectChange change, DbColumnInfo column) { var fullTableRef = column.Table.FullName; var cn = column.ColumnNameQuoted; var init = column.TypeInfo.TypeDef.ColumnInit; change.AddScript(DbScriptType.ColumnInit, $"UPDATE {fullTableRef} SET {cn} = {init} WHERE {cn} IS NULL;"); }
// Inject hash value, some servers preserve it, some don't public virtual void BuildViewAddSql(DbObjectChange change, DbTableInfo view) { var viewAddSql = $@"CREATE VIEW {view.FullName} AS {view.ViewSql}"; change.AddScript(DbScriptType.ViewAdd, viewAddSql); }
public virtual void BuildPrimaryKeyAddSql(DbObjectChange change, DbKeyInfo key) { var pkFields = key.KeyColumns.GetSqlNameList(); var tname = key.Table.FullName; var keyName = QuoteName(key.Name); change.AddScript(DbScriptType.PrimaryKeyAdd, $"ALTER TABLE {tname} ADD CONSTRAINT {keyName} PRIMARY KEY ({pkFields})"); }
public override void BuildPrimaryKeyAddSql(DbObjectChange change, DbKeyInfo key) { var pkFields = key.KeyColumns.GetSqlNameList(); var clustered = GetClusteredExpression(key); var qn = QuoteName(key.Name); change.AddScript(DbScriptType.PrimaryKeyAdd, $"ALTER TABLE {key.Table.FullName} ADD CONSTRAINT {qn} PRIMARY KEY {clustered} ({pkFields});"); }
public virtual void BuildViewAddSql(DbObjectChange change, DbTableInfo view) { const string sqlTemplate = @"CREATE VIEW {0} AS {1}"; //no ';' at the end, SQL must have it already change.AddScript(DbScriptType.ViewAdd, sqlTemplate, view.FullName, view.ViewSql); }