} //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);
}
