} //method private DbKeyInfo FindOldKey(DbTableInfo oldTable, DbKeyInfo newKey) { // raw match by type, and column list foreach (var oldKey in oldTable.Keys) { // we cannot match just by name: for SQLite, PK has fixed name: sqlite_autoindex_<table>_1 // also Oracle - max key len is 30, not enough to include all table/col names // if(NamesMatch(oldKey.Name, newKey.Name)) return oldKey; if (oldKey.KeyType != newKey.KeyType || oldKey.KeyColumns.Count != newKey.KeyColumns.Count) { continue; } //check cols bool match = true; for (int i = 0; i < oldKey.KeyColumns.Count; i++) { if (oldKey.KeyColumns[i].Column != newKey.KeyColumns[i].Column.Peer) { match = false; break; } } if (match) { return(oldKey); } } return(null); }
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 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 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 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 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); } }
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});"); }
protected virtual void LoadIndexes() { var data = GetIndexes(); foreach (DbRow row in data.Rows) { var schema = row.GetAsString("TABLE_SCHEMA"); if (!IncludeSchema(schema)) { continue; } var tableName = row.GetAsString("TABLE_NAME"); var table = Model.GetTable(schema, tableName); if (table == null) { continue; } var indexName = row.GetAsString("INDEX_NAME"); //indexName might be null for SQL Server (type_desc="HEAP") - just ignore these if (string.IsNullOrWhiteSpace(indexName)) { continue; } //primary keys are added through table constraints, so skip them here - except mark them as clustered var isPk = IsTrueOrNonZero(row, "primary_key"); bool isClustered = Driver.Supports(DbFeatures.ClusteredIndexes) && IsTrueOrNonZero(row, "clustered"); if (isPk && isClustered) { table.PrimaryKey.KeyType = KeyType.ClusteredPrimaryKey; } if (isPk) { continue; //PKs are added through constraints } var isUnique = IsTrueOrNonZero(row, "unique"); // Find existing, or create a new one var key = table.Keys.FindByName(indexName); //If key not exists yet, create it if (key == null) { key = new DbKeyInfo(indexName, table, KeyType.Index); } else { key.KeyType |= KeyType.Index; } if (isClustered) { key.KeyType |= KeyType.ClusteredIndex; } if (isUnique) { key.KeyType |= KeyType.UniqueIndex; } key.Filter = row.GetAsString("FILTER_CONDITION"); }//while }
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};"); } }
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); } }
public override void OnDbModelConstructed(DbModel dbModel) { foreach (var table in dbModel.Tables) { // Names of PK constraints in MySql is 'PRIMARY', cannot be changed // PK is always used as clustered index, but we consider clustered indexes as not supported in MySql if (table.PrimaryKey != null) { table.PrimaryKey.Name = "PRIMARY"; } // All foreign keys have a supporting index; if there's no matching index already, it is created automatically. foreach (var key in table.Keys) { if (key.KeyType.IsSet(KeyType.ForeignKey)) { var supportingIndex = DbModelHelper.FindMatchingIndex(key, table.Keys); if (supportingIndex == null) //if no supporting index, then mark this key as an index { key.KeyType |= KeyType.Index; } } //Drop descending flag - see notes at the top of the file // MySql supports ordered columns in indexes, but there's no way to get this information // when loading index columns from the database - at least I don't know any // so we set all column direction to ASC after construction DbModel foreach (var kc in key.KeyColumns) { kc.Desc = false; } } // auto_increment (identity) columns must be associated with key (PK or index). For auto-inc columns that are NOT PKs we create artificial index var autoIncCols = table.Columns.Where(c => c.Flags.IsSet(DbColumnFlags.Identity)); foreach (var col in autoIncCols) { if (col.Flags.IsSet(DbColumnFlags.PrimaryKey)) { continue; } var ind = new DbKeyInfo(col.ColumnName, col.Table, KeyType.Index); //added automatically to table.Keys list ind.KeyColumns.Add(new DbKeyColumnInfo(col)); } } // Remove double-quotes from StoredProcedure names - MySqlDriver does not like it foreach (var cmd in dbModel.Commands) { cmd.FullCommandName = cmd.FullCommandName.Replace("\"", ""); } base.OnDbModelConstructed(dbModel); }
protected virtual string BuildJoinClause(DbKeyInfo key1, DbKeyInfo key2, string table1Alias, string table2Alias) { var prefix1 = string.IsNullOrEmpty(table1Alias) ? string.Empty : table1Alias + "."; var prefix2 = string.IsNullOrEmpty(table2Alias) ? string.Empty : table2Alias + "."; var count = key1.KeyColumns.Count; var parts = new string[count]; for (int i = 0; i < count; i++) { parts[i] = string.Format("{0}\"{1}\" = {2}\"{3}\"", prefix1, key1.KeyColumns[i].Column.ColumnName, prefix2, key2.KeyColumns[i].Column.ColumnName); } return(string.Join(" AND ", parts)); }
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});"); }
protected virtual void LoadTableConstraints() { var data = GetTableConstraints(); foreach (InfoRow row in data.Rows) { var schema = row.GetAsString("TABLE_SCHEMA"); if (!IncludeSchema(schema)) { continue; } var tableName = row.GetAsString("TABLE_NAME"); var table = Model.GetTable(schema, tableName); var constrName = row.GetAsString("CONSTRAINT_NAME"); var constrTypeStr = row.GetAsString("CONSTRAINT_TYPE"); if (table == null) { continue; } KeyType keyType; switch (constrTypeStr.Trim()) { case "PRIMARY KEY": keyType = KeyType.PrimaryKey; break; case "FOREIGN KEY": keyType = KeyType.ForeignKey; break; default: continue; //skip this constraint } var dbKey = new DbKeyInfo(constrName, table, keyType); if (keyType == KeyType.PrimaryKey) { table.PrimaryKey = dbKey; } } //sanity check - all tables must have PK foreach (var table in Model.Tables) { if (table.PrimaryKey == null && table.Kind == EntityKind.Table) { //Just to have a line for a breakpoint System.Diagnostics.Debug.WriteLine("DBModelLoader warning: Table without PK:" + table.TableName); } } }
private bool DbKeysMatch(DbKeyInfo oldKey, DbKeyInfo newKey) { if (oldKey.KeyType != newKey.KeyType || oldKey.KeyColumns.Count != newKey.KeyColumns.Count) { return(false); } //check column-by-column match for (int i = 0; i < oldKey.KeyColumns.Count; i++) { var oldKeyCol = oldKey.KeyColumns[i]; var newKeyCol = newKey.KeyColumns[i]; if (oldKeyCol.Column.Peer != newKeyCol.Column) { return(false); } if (_supportsOrderInIndexes && oldKeyCol.Desc != newKeyCol.Desc) { return(false); } } // check filter and included columns if (_newModel.Driver.Supports(DbFeatures.FilterInIndexes) && (NormalizeIndexFilter(oldKey.Filter) != NormalizeIndexFilter(newKey.Filter))) { return(false); } if (_newModel.Driver.Supports(DbFeatures.IncludeColumnsInIndexes)) { //compare lists - first counts, then columns; note that columns might be in a different order if (oldKey.IncludeColumns.Count != newKey.IncludeColumns.Count) { return(false); } foreach (var oldIncCol in oldKey.IncludeColumns) { if (oldIncCol.Peer == null || !newKey.IncludeColumns.Contains(oldIncCol.Peer)) { return(false); } } }//if return(true); }
private DbKeyInfo FindOldKey(DbTableInfo oldTable, DbKeyInfo newKey) { // just for easier debugging, preselect keys matching by type and column count var similarOldKeys = oldTable.Keys.Where(k => k.KeyType == newKey.KeyType && k.KeyColumns.Count == newKey.KeyColumns.Count).ToList(); foreach (var oldKey in similarOldKeys) { // If we have duplicating keys (ex: Northwind database, OrdersTable, key CustomerID, CustomerOrders), then lets try match them in pairs, to accurately report differences if (oldKey.Peer == newKey) { return(oldKey); } if (oldKey.Peer != null) { continue; } if (DbKeysMatch(oldKey, newKey)) { return(oldKey); } } return(null); }
public virtual void BuildIndexAddSql(DbObjectChange change, DbKeyInfo key) { 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 = QuoteName(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 (key.Filter != null && driver.Supports(DbFeatures.FilterInIndexes)) { wherePred = "WHERE " + key.Filter.DefaultSql; } var script = $@" CREATE {unique} INDEX {qKeyName} ON {key.Table.FullName} ( {indexFields} ) {includeList} {wherePred} "; change.AddScript(DbScriptType.IndexAdd, script); }
private string GetClusteredExpression(DbKeyInfo key) { var clustered = key.KeyType.IsSet(KeyType.Clustered) ? "CLUSTERED" : "NONCLUSTERED"; return(clustered); }
private bool IsPureIndex(DbKeyInfo key) { bool isPkOrFk = key.KeyType.IsSet(KeyType.PrimaryKey | KeyType.ForeignKey); return(!isPkOrFk); }
public override void BuildTableConstraintDropSql(DbObjectChange change, DbKeyInfo key) { }
//Default impl adds table name to the statement: "DROP INDEX <IndexName> ON <tableName>"; SQLite does not use table name public override void BuildIndexDropSql(DbObjectChange change, DbKeyInfo key) { change.AddScript(DbScriptType.IndexDrop, "DROP INDEX \"{0}\"", key.Name); }
public override void BuildIndexDropSql(DbObjectChange change, DbKeyInfo key) { change.AddScript(DbScriptType.IndexDrop, "DROP INDEX {0}.\"{1}\";", key.Table.FullName, key.Name); }
//Default impl adds table name to the statement: "DROP INDEX <IndexName> ON <tableName>"; SQLite does not use table name public override void BuildIndexDropSql(DbObjectChange change, DbKeyInfo key) { var kn = QuoteName(key.Name); change.AddScript(DbScriptType.IndexDrop, $"DROP INDEX {kn}"); }
public virtual void BuildTableConstraintDropSql(DbObjectChange change, DbKeyInfo key) { var qn = QuoteName(key.Name); change.AddScript(DbScriptType.TableConstraintDrop, $"ALTER TABLE {key.Table.FullName} DROP CONSTRAINT {qn};"); }
public virtual void BuildIndexDropSql(DbObjectChange change, DbKeyInfo key) { var qn = QuoteName(key.Name); change.AddScript(DbScriptType.IndexDrop, $"DROP INDEX {qn} ON {key.Table.FullName};"); }
public override DbModel LoadModel() { Model = new DbModel(Settings.ModelConfig); //tables/views, columns var tblTables = ExecuteSelect("select type, tbl_name, sql from sqlite_master where type='table' OR type='view';"); foreach (var tblRow in tblTables.Rows) { var tblName = tblRow.GetAsString("tbl_name"); if (tblName.StartsWith("sqlite_")) { continue; // sqlite_sequence and other sys tables - ignore } var isView = tblRow.GetAsString("type") == "view"; var objType = isView ? DbObjectType.View : DbObjectType.Table; var tableSql = tblRow.GetAsString("sql"); var tbl = new DbTableInfo(this.Model, string.Empty, tblName, null, objType); if (isView) { //do not load columns, it will fail tbl.ViewSql = tableSql; continue; } //table columns // We detect PKs from the list of indexes. But, for tables with auto-inc columns there's no index // However col is marked as PK in table_info listing. So we collect these columns and use them later // if PK has not been set var pkMarkedCols = new List <DbColumnInfo>(); var tblCols = ExecuteSelect("PRAGMA table_info('{0}');", tblName); foreach (var colRow in tblCols.Rows) { var colName = colRow.GetAsString("name"); var typeName = colRow.GetAsString("type"); var notNull = colRow.GetAsInt("notnull"); var dftValue = colRow.GetAsString("dflt_value"); var typeInfo = GetSqliteTypeInfo(typeName, nullable: notNull == 0, dft: dftValue); if (typeInfo == null) { typeInfo = GetSqliteTypeInfo("text", nullable: notNull == 0, dft: dftValue); //default to text! } /* * { * LogError( * "Failed to find TypeInfo for SQL data type [{0}]. Table(view) {1}, column {2}.", typeName, tblName, colName); * continue; * } */ var isNullable = notNull == 0; var colInfo = new DbColumnInfo(tbl, colName, typeInfo, isNullable); // check PK flag, save the column if the flag is set bool isPk = colRow.GetAsInt("pk") == 1; if (isPk) { pkMarkedCols.Add(colInfo); } }// foreach colRow // indexes, PKs var tblIndexes = ExecuteSelect("PRAGMA index_list('{0}')", tblName); foreach (var indRow in tblIndexes.Rows) { var indName = indRow.GetAsString("name"); var origin = indRow.GetAsString("origin"); var unique = indRow.GetAsInt("unique"); var keyType = KeyType.Index; if (origin == "pk") { keyType = KeyType.PrimaryKey; } else if (unique == 1) { keyType |= KeyType.Unique; } var indCols = ExecuteSelect("PRAGMA index_info('{0}')", indName); var indKey = new DbKeyInfo(indName, tbl, keyType); foreach (var colRow in indCols.Rows) { var colName = colRow.GetAsString("name"); var col = tbl.Columns.FindByName(colName); Util.Check(col != null, "Building index {0}, table {1}: column {2} not found.", indName, tblName, colName); indKey.KeyColumns.Add(new DbKeyColumnInfo(col)); }//foreach colRow if (keyType.IsSet(KeyType.PrimaryKey)) { tbl.PrimaryKey = indKey; } }//foreach indRow // check PK; if not detected, it is identity (auto-inc) col (no pk index in this case) if (tbl.Kind == EntityKind.Table && tbl.PrimaryKey == null) { Util.Check(pkMarkedCols.Count > 0, "Primary key not found on table {0}.", tblName); CreateAutoIncPrimaryKey(tbl, pkMarkedCols); } }// foreach tblRow // FKs - we need to do it in another loop, after all cols are created. foreach (var tbl in Model.Tables) { if (tbl.Kind == EntityKind.View) { continue; } var tname = tbl.TableName; var tblFKs = ExecuteSelect("PRAGMA foreign_key_list('{0}')", tname); //System.Diagnostics.Trace.WriteLine($"Table: {tname}, FKs: {tblFKs.Rows.Count}"); var lastKeyId = -1; DbRefConstraintInfo constr = null; foreach (var fkColRow in tblFKs.Rows) { var fromColName = fkColRow.GetAsString("from"); var toTableName = fkColRow.GetAsString("table"); var toColName = fkColRow.GetAsString("to"); var fromCol = tbl.Columns.FindByName(fromColName); Util.Check(fromCol != null, "Loading FKs, table {0}, error: column {1} not found.", tname, fromColName); var toTable = FindTable(toTableName); Util.Check(toTable != null, "Loading FKs, table {0}, error: target table {1} not found.", tname, toTableName); var toCol = toTable.Columns.FindByName(toColName); // if keyId is the same as previous one, then col belongs to the same (composite) key var keyId = fkColRow.GetAsInt("id"); if (keyId != lastKeyId) { bool cascadeDelete = fkColRow.GetAsString("on_delete") == "CASCADE"; var keyFrom = new DbKeyInfo("FK_" + tname + "_" + fromColName, tbl, KeyType.ForeignKey); constr = new DbRefConstraintInfo(this.Model, keyFrom, toTable.PrimaryKey, cascadeDelete); lastKeyId = keyId; } constr.FromKey.KeyColumns.Add(new DbKeyColumnInfo(fromCol)); } //foreach fkColRow } //foreach tbl return(Model); } //method
public virtual void BuildTableConstraintDropSql(DbObjectChange change, DbKeyInfo key) { change.AddScript(DbScriptType.TableConstraintDrop, "ALTER TABLE {0} DROP CONSTRAINT \"{1}\";", key.Table.FullName, key.Name); }