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