예제 #1
0
        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);
        }
예제 #2
0
        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};");
        }
예제 #3
0
        //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}");
        }
예제 #4
0
 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);
     }
 }
예제 #5
0
        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);
        }
예제 #6
0
        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};");
        }
예제 #7
0
        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);
        }
예제 #8
0
        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);
        }
예제 #9
0
        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);
        }
예제 #10
0
        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);
        }
예제 #11
0
        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};");
        }
예제 #12
0
        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);
        }
예제 #13
0
        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);
        }
예제 #14
0
        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);
        }
예제 #15
0
        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);
        }
예제 #16
0
        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})");
        }
예제 #17
0
        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;");
        }
예제 #18
0
        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);
        }
예제 #19
0
        // 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);
        }
예제 #20
0
        }//method

        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);
        }//method
예제 #21
0
 public override void BuildIndexAddSql(DbObjectChange change, DbKeyInfo key)
 {
     base.BuildIndexAddSql(change, key);
     if (key.KeyType.IsSet(KeyType.Clustered))
     {
         change.AddScript(DbScriptType.IndexAdd, "ALTER TABLE {0} CLUSTER ON \"{1}\";", key.Table.FullName, key.Name);
     }
 }
예제 #22
0
        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});");
        }
예제 #23
0
 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};");
     }
 }
예제 #24
0
        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};");
        }
예제 #25
0
 public override void BuildIndexAddSql(DbObjectChange change, DbKeyInfo key)
 {
     base.BuildIndexAddSql(change, key);
     if (key.KeyType.IsSet(KeyType.Clustered))
     {
         var kn = QuoteName(key.Name);
         change.AddScript(DbScriptType.IndexAdd, $"ALTER TABLE {key.Table.FullName} CLUSTER ON {kn};");
     }
 }
예제 #26
0
        public override void BuildColumnRenameSql(DbObjectChange change, DbColumnInfo oldColumn, DbColumnInfo newColumn)
        {
            var sch        = oldColumn.Table.Schema;
            var tn         = oldColumn.Table.TableName;
            var oldColName = oldColumn.ColumnName;
            var newColName = newColumn.ColumnName;

            change.AddScript(DbScriptType.ColumnRename, $"EXEC SYS.SP_RENAME '{sch}.{tn}.{oldColName}' , '{newColName}', 'COLUMN'");
        }
예제 #27
0
        //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);
            }
        }
예제 #28
0
        //Drop table - supported, but will fail if there's an old foreign key to this table. Dropping FKs is not supported,
        // so we do not delete the table if there are foreign keys
        public override void BuildTableDropSql(DbObjectChange change, DbTableInfo table)
        {
            var refs = table.GetIncomingReferences();

            if (refs.Count > 0)
            {
                return;
            }
            base.BuildTableDropSql(change, table);
        }
예제 #29
0
 public override void BuildTableConstraintDropSql(DbObjectChange change, DbKeyInfo key)
 {
     if (key.KeyType == KeyType.PrimaryKey)
     {
         change.AddScript(DbScriptType.RefConstraintDrop, $"ALTER TABLE {key.Table.FullName} DROP PRIMARY KEY;");
     }
     else
     {
         base.BuildTableConstraintDropSql(change, key);
     }
 }
예제 #30
0
 //Helper methods
 protected virtual void BuildColumnRenameSqlWithAddDrop(DbObjectChange change, DbColumnInfo oldColumn, DbColumnInfo newColumn)
 {
     //Add new column
     BuildColumnAddSql(change, newColumn, DbScriptOptions.ForceNull | DbScriptOptions.NewColumn);
     // copy data
     change.AddScript(DbScriptType.ColumnCopyValues, $"UPDATE {oldColumn.Table.FullName} SET {newColumn.ColumnNameQuoted} = {oldColumn.ColumnNameQuoted};");
     // finalize added column
     BuildColumnModifySql(change, newColumn, DbScriptOptions.CompleteColumnSetup);
     //drop old column
     BuildColumnDropSql(change, oldColumn);
 }