protected virtual void LoadReferentialConstraints()
{
var data = GetReferentialConstraints();
foreach (InfoRow row in data.Rows)
{
//Load names for Foreign key and Unique key
var fkSchema = row.GetAsString("CONSTRAINT_SCHEMA");
var toSchema = row.GetAsString("UNIQUE_CONSTRAINT_SCHEMA");
if (!IncludeSchema(toSchema) || !IncludeSchema(fkSchema))
{
continue;
}
var fkTableName = row.GetAsString("C_TABLE");
var fkName = row.GetAsString("CONSTRAINT_NAME");
var fromKey = FindKey(fkSchema, fkTableName, fkName);
Util.Check(fromKey != null, "Key {0} for table {1} not found.", fkName, fkTableName);
var toTableName = row.GetAsString("U_TABLE");
var toTable = Model.GetTable(toSchema, toTableName);
if (toTable == null)
{
continue; // target table is being deleted, ignore this constraint
}
var toKey = toTable.PrimaryKey;
bool cascadeDelete = row.GetAsString("DELETE_RULE") == "CASCADE";
var refConstraint = new DbRefConstraintInfo(Model, fromKey, toKey, cascadeDelete); //will be added to list by constr
// set FK flag
foreach (var kCol in fromKey.KeyColumns)
{
kCol.Column.Flags |= DbColumnFlags.ForeignKey;
}
}
}
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};");
}
public override void BuildRefConstraintAddSql(DbObjectChange change, DbRefConstraintInfo refConstraint)
{
var srcTable = refConstraint.FromKey.Table;
var targetTable = refConstraint.ToKey.Table;
var srcCols = refConstraint.FromKey.KeyColumns.GetSqlNameList();
var targetCols = refConstraint.ToKey.KeyColumns.GetSqlNameList();
bool cascade = refConstraint.OwnerReference.FromMember.Flags.IsSet(EntityMemberFlags.CascadeDelete);
var onDeleteClause = cascade ? "ON DELETE CASCADE" : string.Empty;
var cn = QuoteName(refConstraint.FromKey.Name);
var script = $@"ALTER TABLE {srcTable.FullName}
ADD CONSTRAINT {cn} FOREIGN KEY ({srcCols}) REFERENCES {targetTable.FullName} ({targetCols}) {onDeleteClause};";
change.AddScript(DbScriptType.RefConstraintAdd, script);
}
private DbRefConstraintInfo FindOldRefConstraint(DbTableInfo oldTable, DbRefConstraintInfo newRefConstraint)
{
var newFrom = newRefConstraint.FromKey;
var newTo = newRefConstraint.ToKey;
foreach (var oldRc in oldTable.RefConstraints)
{
if (oldRc.FromKey.Peer == newFrom && oldRc.ToKey.Peer == newTo && oldRc.CascadeDelete == newRefConstraint.CascadeDelete)
{
return(oldRc);
}
}
return(null);
}
public virtual void BuildRefConstraintAddSql(DbObjectChange change, DbRefConstraintInfo refConstraint)
{
const string sqlTemplate = "ALTER TABLE {0} ADD CONSTRAINT \"{1}\" FOREIGN KEY ({2}) REFERENCES {3} ({4}) {5};";
var srcTable = refConstraint.FromKey.Table;
var targetTable = refConstraint.ToKey.Table;
var fullSrcTableRef = srcTable.FullName;
var fullTargetTableRef = targetTable.FullName;
var srcCols = refConstraint.FromKey.KeyColumns.GetSqlNameList();
var targetCols = refConstraint.ToKey.KeyColumns.GetSqlNameList();
bool cascade = refConstraint.OwnerReference.FromMember.Flags.IsSet(EntityMemberFlags.CascadeDelete);
var onDeleteClause = cascade ? "ON DELETE CASCADE" : string.Empty;
change.AddScript(DbScriptType.RefConstraintAdd, sqlTemplate, fullSrcTableRef, refConstraint.FromKey.Name, srcCols, fullTargetTableRef, targetCols, onDeleteClause);
}
protected virtual void LoadReferentialConstraints()
{
var data = GetReferentialConstraintsExt();
foreach (DbRow row in data.Rows)
{
//Load names for Foreign key and Unique key
var fkSchema = row.GetAsString("CONSTRAINT_SCHEMA");
var toSchema = row.GetAsString("UNIQUE_CONSTRAINT_SCHEMA");
if (!IncludeSchema(toSchema) || !IncludeSchema(fkSchema))
{
continue;
}
var fkTableName = row.GetAsString("C_TABLE");
var fkName = row.GetAsString("CONSTRAINT_NAME");
var fromKey = FindKey(fkSchema, fkTableName, fkName);
Util.Check(fromKey != null, "Key {0} for table {1} not found.", fkName, fkTableName);
var toTableName = row.GetAsString("U_TABLE");
var toTable = Model.GetTable(toSchema, toTableName);
Util.Check(toTable != null, "Target table {0}.{1} not found in DB Model.", toSchema, toTableName);
var toKey = toTable.PrimaryKey;
/*
* if (toKey == null) {
* //catch special case - foreign key to non-key set of fields
* if (fromKey != null && fromKey.KeyType.IsSet(KeyType.ForeignKey))
* fromKey.NotSupported = true;
* continue; //it is not ref constraint
* }
*/
bool cascadeDelete = row.GetAsString("DELETE_RULE") == "CASCADE";
var refConstraint = new DbRefConstraintInfo(Model, fromKey, toKey, cascadeDelete);
fromKey.Table.RefConstraints.Add(refConstraint);
}
}
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
private bool RefConstraintKeysChanged(DbRefConstraintInfo constraint)
{
return(_changedKeys.Contains(constraint.FromKey) || _changedKeys.Contains(constraint.ToKey));
}
}//method
public virtual void BuildRefConstraintDropSql(DbObjectChange change, DbRefConstraintInfo dbRefConstraint)
{
BuildTableConstraintDropSql(change, dbRefConstraint.FromKey);
}
//not supported
public override void BuildRefConstraintDropSql(DbObjectChange change, DbRefConstraintInfo dbRefConstraint)
{
}
public override void BuildRefConstraintAddSql(DbObjectChange change, DbRefConstraintInfo refConstraint)
{
}
public override void BuildRefConstraintDropSql(DbObjectChange change, DbRefConstraintInfo dbRefConstraint)
{
var fromKey = dbRefConstraint.FromKey;
change.AddScript(DbScriptType.RefConstraintDrop, "ALTER TABLE {0} DROP FOREIGN KEY {1};", fromKey.Table.FullName, fromKey.Name);
}
