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 static DbKeyInfo FindMatchingIndex(DbKeyInfo foreignKey, IList<DbKeyInfo> keys)
{
foreach(var key in keys) {
if(!key.KeyType.IsSet(KeyType.Index | KeyType.PrimaryKey) || key.KeyColumns.Count < foreignKey.KeyColumns.Count)
continue;
//Check columns are the same
bool match = true;
for(int i = 0; i < foreignKey.KeyColumns.Count; i++) {
if(foreignKey.KeyColumns[i].Column != key.KeyColumns[i].Column)
match = false;
}
if(match)
return key;
}
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 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);
}
private void CreateTableKeys()
{
var createIndexesOnForeignKeys = _driver.Supports(DbFeatures.NoIndexOnForeignKeys)
&& _config.Options.IsSet(DbOptions.AutoIndexForeignKeys);
var supportsClustedIndex = _driver.Supports(DbFeatures.ClusteredIndexes);
var supportsIndexedViews = _driver.Supports(DbFeatures.MaterializedViews);
foreach (var table in _dbModel.Tables) {
if (table.Kind == EntityKind.View && !supportsIndexedViews)
continue;
var entity = table.Entity;
_tableKeyIndex = 0; //counter used to create unique index names
foreach (var entityKey in entity.Keys) {
if (table.Kind == EntityKind.View && entityKey.KeyType == KeyType.ForeignKey)
continue; //views do not have foreign keys; view entities can reference other entities, but this relationship is not represented in database
var keyCols = new List<DbKeyColumnInfo>();
//Find columns and add them to the key
foreach (var keyMember in entityKey.ExpandedKeyMembers) {
var col = table.Columns.First(c => c.Member == keyMember.Member);
keyCols.Add(new DbKeyColumnInfo(col, keyMember));
}//foreach member
var inclCols = new List<DbColumnInfo>();
foreach(var incMember in entityKey.ExpandedIncludeMembers) {
var col = table.Columns.First(c => c.Member == incMember);
inclCols.Add(col);
}//foreach member
//Create DBKey
var keyType = entityKey.KeyType;
if (!supportsClustedIndex)
keyType &= ~KeyType.Clustered;
var keyName = ConstructDbKeyName(table, keyType, keyCols, entityKey.Name);
var dbKey = new DbKeyInfo(keyName, table, keyType, entityKey);
dbKey.KeyColumns.AddRange(keyCols);
dbKey.IncludeColumns.AddRange(inclCols);
//check filter
if(!string.IsNullOrWhiteSpace(entityKey.Filter))
dbKey.Filter = ProcessKeyFilter(entityKey.Filter, table);
//Assign PK if it is PK
if (keyType.IsSet(KeyType.PrimaryKey))
table.PrimaryKey = dbKey;
if (keyType.IsSet(KeyType.Clustered))
foreach (var keycol in dbKey.KeyColumns)
keycol.Column.Flags |= DbColumnFlags.ClusteredIndex;
}//foreach key
// Check primary key columns, mark them as no-update
if (table.Kind == EntityKind.View)
continue;
foreach (var keyCol in table.PrimaryKey.KeyColumns)
keyCol.Column.Flags |= DbColumnFlags.NoUpdate | DbColumnFlags.PrimaryKey;
//Create indexes on foreign keys
if (createIndexesOnForeignKeys) {
//copy FKeys into a separate list, to avoid trouble when adding indexes to collection that we are iterating
var foreignKeys = table.Keys.Where(k => k.KeyType.IsSet(KeyType.ForeignKey)).ToList();
foreach (var key in foreignKeys) {
//Check if there is already index with the same starting columns
var matchingIndex = FindMatchingIndexForForeignKey(key);
if (matchingIndex != null)
continue;
// construct index name, strip "IX_" prefix and replace it with "IX_FK_"
var indexName = "IX_FK_" + ConstructDbKeyName(table, KeyType.Index, key.KeyColumns, key.Name).Substring(3);
var dbIndex = new DbKeyInfo(indexName, table, KeyType.Index);
dbIndex.KeyColumns.AddRange(key.KeyColumns);
}
}//if createIndexes
}//foreach table
CheckErrors();
}
public virtual void BuildPrimaryKeyAddSql(DbObjectChange change, DbKeyInfo key)
{
var pkFields = key.KeyColumns.GetSqlNameList();
change.AddScript(DbScriptType.PrimaryKeyAdd, "ALTER TABLE {0} ADD CONSTRAINT \"{1}\" PRIMARY KEY ({2});", key.Table.FullName, key.Name, pkFields);
}
public virtual void BuildTableConstraintDropSql(DbObjectChange change, DbKeyInfo key)
{
change.AddScript(DbScriptType.TableConstraintDrop, "ALTER TABLE {0} DROP CONSTRAINT \"{1}\";", key.Table.FullName, key.Name);
}
public DbRefConstraintInfo(DbModel dbModel, DbKeyInfo fromKey, DbKeyInfo toKey, bool cascadeDelete, EntityReferenceInfo ownerRef = null)
: base(dbModel, fromKey.Schema, DbObjectType.RefConstraint, ownerRef)
{
Util.Check(fromKey != null, "fromKey may not be null.");
Util.Check(toKey != null, "toKey may not be null.");
FromKey = fromKey;
ToKey = toKey;
OwnerReference = ownerRef;
CascadeDelete = cascadeDelete;
base.GlobalName = DbModelHelper.GetGlobalName(fromKey.Table.Schema, fromKey.Name);
}
private bool IsPureIndex(DbKeyInfo key)
{
bool isPkOrFk = key.KeyType.IsSet(KeyType.PrimaryKey | KeyType.ForeignKey);
return !isPkOrFk;
}
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 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);
}
protected virtual void LoadTableConstaints()
{
var data = GetTableConstraints();
foreach (DataRow 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);
}
}
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);
}
protected virtual void LoadIndexes()
{
var data = GetIndexes();
foreach (DataRow 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
}
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 bool KeyChanged(DbKeyInfo keyInfo)
{
if(keyInfo.Peer == null)
return true;
var supportsOrder = _newModel.Driver.Supports(DbFeatures.OrderedColumnsInIndexes);
var supportsInclude = _newModel.Driver.Supports(DbFeatures.IncludeColumnsInIndexes);
// Check column counts
if(keyInfo.KeyColumns.Count != keyInfo.Peer.KeyColumns.Count)
return true;
if(supportsInclude && keyInfo.IncludeColumns.Count != keyInfo.Peer.IncludeColumns.Count)
return true;
//Check if any column changed
if(keyInfo.KeyColumns.Any(kc => _changedColumns.Contains(kc.Column)))
return true;
if(supportsInclude)
if(keyInfo.IncludeColumns.Any(c => _changedColumns.Contains(c)))
return true;
// compare individual columns match
for(int i = 0; i < keyInfo.KeyColumns.Count; i++) {
var oldKeyCol = keyInfo.KeyColumns[i];
var newKeyCol = keyInfo.Peer.KeyColumns[i];
if(oldKeyCol.Column != newKeyCol.Column.Peer)
return true;
if(supportsOrder)
if(oldKeyCol.Desc != newKeyCol.Desc)
return true;
}//for i
//check included columns list match
if(supportsInclude)
for(int i = 0; i < keyInfo.IncludeColumns.Count; i++) {
var oldCol = keyInfo.IncludeColumns[i];
var newCol = keyInfo.Peer.IncludeColumns[i];
if(oldCol.Peer != newCol)
return true;
}
// if everyting matched, key did not change
return false;
}
// Finds an existing index that can be used to support (speed-up) the foreign key. It can be index, or primary key
// If no index is found, system would create an extra index for the foreign key (when corresponding option is set)
private DbKeyInfo FindMatchingIndexForForeignKey(DbKeyInfo key)
{
var table = key.Table;
foreach (var index in key.Table.Keys) {
//We are interested only in indexes or primary keys (or clustered primary keys)
if (!index.KeyType.IsSet(KeyType.Index | KeyType.PrimaryKey))
continue;
if (index.KeyColumns.Count < key.KeyColumns.Count) //it is not a match if it has fewer columns
continue;
bool match = true;
for (int i = 0; i < key.KeyColumns.Count; i++)
match &= (key.KeyColumns[i].Column == index.KeyColumns[i].Column);
if (match)
return index;
}//foreach
return null;
}
private string GetClusteredExpression(DbKeyInfo key)
{
var clustered = key.KeyType.IsSet(KeyType.Clustered) ? "CLUSTERED" : "NONCLUSTERED";
return clustered;
}
public override void BuildIndexDropSql(DbObjectChange change, DbKeyInfo key)
{
change.AddScript(DbScriptType.IndexDrop, "DROP INDEX \"{0}\".\"{1}\";", key.Table.Schema, key.Name);
}
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);
}
}
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 virtual void BuildIndexDropSql(DbObjectChange change, DbKeyInfo key)
{
change.AddScript(DbScriptType.IndexDrop, "DROP INDEX \"{0}\" ON {1};", key.Name, key.Table.FullName);
}
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);
}
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;
}
