//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 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 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 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 fullTableRef = key.Table.FullName; var pkFields = key.KeyColumns.GetSqlNameList(); // PK name is always 'PRIMARY' change.AddScript(DbScriptType.PrimaryKeyAdd, "ALTER TABLE {0} ADD CONSTRAINT PRIMARY KEY ({1});", fullTableRef, pkFields); }
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); }
//not supported; all we can do is nullify it; so if it is a FK it no longer holds target refs public override 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 if (column.Flags.IsSet(DbColumnFlags.Nullable) && column.Flags.IsSet(DbColumnFlags.ForeignKey)) { change.AddScript(DbScriptType.ColumnInit, "UPDATE {0} SET \"{1}\" = NULL;", tableName, column.ColumnName); } }
public override void BuildStoredProcDropSql(DbObjectChange change, DbCommandInfo command) { if(command.CustomTag == null) { //try to recover it var inpParams = command.Parameters.Where(p => p.Direction != ParameterDirection.Output); command.CustomTag = string.Join(", ", inpParams.Select(p => p.TypeInfo.SqlTypeSpec)); } var funcRef = string.Format(@"{0}.""{1}""({2})", command.Schema, command.CommandName, command.CustomTag); change.AddScript(DbScriptType.RoutineDrop, "DROP FUNCTION {0};", funcRef); }
//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 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 override void BuildIndexDropSql(DbObjectChange change, DbKeyInfo key) { change.AddScript(DbScriptType.IndexDrop, "DROP INDEX \"{0}\".\"{1}\";", key.Table.Schema, key.Name); }
public override void BuildColumnRenameSql(DbObjectChange change, DbColumnInfo oldColumn, DbColumnInfo newColumn) { change.AddScript(DbScriptType.ColumnRename, "ALTER TABLE {0} RENAME COLUMN \"{1}\" TO \"{2}\";", newColumn.Table.FullName, oldColumn.ColumnName, newColumn.ColumnName); }
public virtual 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)); var columnSpecs = string.Join("," + Environment.NewLine, specs); change.AddScript(DbScriptType.TableAdd, SqlTemplate, table.FullName, columnSpecs); }
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 virtual void BuildTableRenameSql(DbObjectChange change, DbTableInfo oldTable, DbTableInfo newTable) { //Syntax for MySql and almost like Postgres change.AddScript(DbScriptType.TableRename, "ALTER TABLE {0} RENAME TO \"{1}\" ;", oldTable.FullName, newTable.FullName); }
public override void BuildTableRenameSql(DbObjectChange change, DbTableInfo oldTable, DbTableInfo newTable) { change.AddScript(DbScriptType.TableRename, "ALTER TABLE {0} RENAME TO \"{1}\" ;", oldTable.FullName, newTable.TableName); }
public override void BuildSequenceDropSql(DbObjectChange change, DbSequenceInfo sequence) { // PG creates sequences for identity columns, these should not be dropped explicitly; we do sequence drop after table drop, so we add check for existense change.AddScript(DbScriptType.SequenceDrop, "DROP SEQUENCE IF EXISTS {0}", sequence.FullName); }
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 virtual void BuildTableConstraintDropSql(DbObjectChange change, DbKeyInfo key) { change.AddScript(DbScriptType.TableConstraintDrop, "ALTER TABLE {0} DROP CONSTRAINT \"{1}\";", key.Table.FullName, key.Name); }
//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 {0} DROP COLUMN \"{1}\"", tableName, column.ColumnName); }
//Helper methods protected virtual void BuildColumnRenameSqlWithAddDrop(DbObjectChange change, DbColumnInfo oldColumn, DbColumnInfo newColumn) { //Add new column BuildColumnAddSql(change, newColumn, DbScriptOptions.ForceNull); // copy data change.AddScript(DbScriptType.ColumnCopyValues, "UPDATE {0} SET \"{1}\" = \"{2}\";", oldColumn.Table.FullName, newColumn.ColumnName, oldColumn.ColumnName); // finalize added column BuildColumnModifySql(change, newColumn, DbScriptOptions.CompleteColumnSetup); //drop old column BuildColumnDropSql(change, oldColumn); }
// 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 virtual void BuildViewDropSql(DbObjectChange change, DbTableInfo view) { change.AddScript(DbScriptType.ViewDrop, "DROP VIEW {0}", view.FullName); }
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); }
public override void BuildPrimaryKeyAddSql(DbObjectChange change, DbKeyInfo key) { base.BuildPrimaryKeyAddSql(change, key); if(key.KeyType.IsSet(KeyType.Clustered)) change.AddScript(DbScriptType.PrimaryKeyAdd, "ALTER TABLE {0} CLUSTER ON \"{1}\";", key.Table.FullName, key.Name); }
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 virtual void BuildDatabaseAddSql(DbObjectChange change, string name) { change.AddScript(DbScriptType.DatabaseAdd, "CREATE DATABASE \"{0}\"", name); }
//Dropping objects ----------------------------------------------------------------------------------------------------- public virtual void BuildDatabaseDropSql(DbObjectChange change, string name) { change.AddScript(DbScriptType.DatabaseDrop, "DROP DATABASE \"{0}\";", name); }
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 BuildViewAddSql(DbObjectChange change, DbTableInfo view) { const string createViewTemplate = @"CREATE {0} VIEW {1} AS {2}; COMMENT ON {0} VIEW {1} IS '{3}'; "; //notice - no ';' at the end, SQL must have it already // For indexed views add 'MATERIALIZED' attribute var matz = view.IsMaterializedView ? "MATERIALIZED" : string.Empty; //var escapedSql = view.ViewSql.Replace("'", "''"); change.AddScript(DbScriptType.ViewAdd, createViewTemplate, matz, view.FullName, view.ViewSql, view.ViewHash); }
public virtual void BuildTableDropSql(DbObjectChange change, DbTableInfo table) { change.AddScript(DbScriptType.TableDrop, "DROP TABLE {0}", table.FullName); }