public void TestScript() { var person = new Table("dbo", "Person"); person.Columns.Add(new Column("id", "int", false, null)); person.Columns.Add(new Column("name", "varchar", 50, false, null)); person.Columns.Find("id").Identity = new Identity(1, 1); person.AddConstraint(new Constraint("PK_Person", "PRIMARY KEY", "id")); var address = new Table("dbo", "Address"); address.Columns.Add(new Column("id", "int", false, null)); address.Columns.Add(new Column("personId", "int", false, null)); address.Columns.Add(new Column("street", "varchar", 50, false, null)); address.Columns.Add(new Column("city", "varchar", 50, false, null)); address.Columns.Add(new Column("state", "char", 2, false, null)); address.Columns.Add(new Column("zip", "varchar", 5, false, null)); address.Columns.Find("id").Identity = new Identity(1, 1); address.AddConstraint(new Constraint("PK_Address", "PRIMARY KEY", "id")); var fk = new ForeignKey(address, "FK_Address_Person", "personId", person, "id", "", "CASCADE"); TestHelper.ExecSql(person.ScriptCreate(), ""); TestHelper.ExecSql(address.ScriptCreate(), ""); TestHelper.ExecSql(fk.ScriptCreate(), ""); TestHelper.ExecSql("drop table Address", ""); TestHelper.ExecSql("drop table Person", ""); }
public void TestMultiColumnKey() { var t1 = new Table("dbo", "t1"); t1.Columns.Add(new Column("c2", "varchar", 10, false, null)); t1.Columns.Add(new Column("c1", "int", false, null)); t1.AddConstraint(new Constraint("pk_t1", "PRIMARY KEY", "c1,c2")); var t2 = new Table("dbo", "t2"); t2.Columns.Add(new Column("c1", "int", false, null)); t2.Columns.Add(new Column("c2", "varchar", 10, false, null)); t2.Columns.Add(new Column("c3", "int", false, null)); var fk = new ForeignKey(t2, "fk_test", "c3,c2", t1, "c1,c2"); var db = new Database("TESTDB"); db.Tables.Add(t1); db.Tables.Add(t2); db.ForeignKeys.Add(fk); db.Connection = TestHelper.GetConnString("TESTDB"); db.ExecCreate(true); db.Load(); Assert.AreEqual("c3", db.FindForeignKey("fk_test", "dbo").Columns[0]); Assert.AreEqual("c2", db.FindForeignKey("fk_test", "dbo").Columns[1]); Assert.AreEqual("c1", db.FindForeignKey("fk_test", "dbo").RefColumns[0]); Assert.AreEqual("c2", db.FindForeignKey("fk_test", "dbo").RefColumns[1]); db.ExecCreate(true); }
private void LoadForeignKeys(SqlCommand cm) { //get foreign keys cm.CommandText = @" select TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE = 'FOREIGN KEY'"; using (var dr = cm.ExecuteReader()) { while (dr.Read()) { var t = FindTable((string) dr["TABLE_NAME"], (string) dr["TABLE_SCHEMA"]); var fk = new ForeignKey((string) dr["CONSTRAINT_NAME"]); fk.Table = t; ForeignKeys.Add(fk); } } //get foreign key props cm.CommandText = @" select CONSTRAINT_NAME, OBJECT_SCHEMA_NAME(fk.parent_object_id) as TABLE_SCHEMA, UPDATE_RULE, DELETE_RULE, fk.is_disabled from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc inner join sys.foreign_keys fk on rc.CONSTRAINT_NAME = fk.name and rc.CONSTRAINT_SCHEMA = OBJECT_SCHEMA_NAME(fk.parent_object_id)"; using (var dr = cm.ExecuteReader()) { while (dr.Read()) { var fk = FindForeignKey((string) dr["CONSTRAINT_NAME"], (string)dr["TABLE_SCHEMA"]); fk.OnUpdate = (string) dr["UPDATE_RULE"]; fk.OnDelete = (string) dr["DELETE_RULE"]; fk.Check = !(bool) dr["is_disabled"]; } } //get foreign key columns and ref table cm.CommandText = @" select fk.name as CONSTRAINT_NAME, OBJECT_SCHEMA_NAME(fk.parent_object_id) as TABLE_SCHEMA, c1.name as COLUMN_NAME, OBJECT_SCHEMA_NAME(fk.referenced_object_id) as REF_TABLE_SCHEMA, OBJECT_NAME(fk.referenced_object_id) as REF_TABLE_NAME, c2.name as REF_COLUMN_NAME from sys.foreign_keys fk inner join sys.foreign_key_columns fkc on fkc.constraint_object_id = fk.object_id inner join sys.columns c1 on fkc.parent_column_id = c1.column_id and fkc.parent_object_id = c1.object_id inner join sys.columns c2 on fkc.referenced_column_id = c2.column_id and fkc.referenced_object_id = c2.object_id order by fk.name, fkc.constraint_column_id "; using (var dr = cm.ExecuteReader()) { while (dr.Read()) { var fk = FindForeignKey((string) dr["CONSTRAINT_NAME"], (string)dr["TABLE_SCHEMA"]); if (fk == null) { continue; } fk.Columns.Add((string) dr["COLUMN_NAME"]); fk.RefColumns.Add((string) dr["REF_COLUMN_NAME"]); if (fk.RefTable == null) { fk.RefTable = FindTable((string) dr["REF_TABLE_NAME"], (string) dr["REF_TABLE_SCHEMA"]); } } } }
public void TestScriptToDir() { var policy = new Table("dbo", "Policy"); policy.Columns.Add(new Column("id", "int", false, null) {Position = 1}); policy.Columns.Add(new Column("form", "tinyint", false, null) {Position = 2}); policy.AddConstraint(new Constraint("PK_Policy", "PRIMARY KEY", "id") { Clustered = true, Unique = true }); policy.Columns.Items[0].Identity = new Identity(1, 1); var loc = new Table("dbo", "Location"); loc.Columns.Add(new Column("id", "int", false, null) {Position = 1}); loc.Columns.Add(new Column("policyId", "int", false, null) {Position = 2}); loc.Columns.Add(new Column("storage", "bit", false, null) {Position = 3}); loc.Columns.Add(new Column("category", "int", false, null) { Position = 4 }); loc.AddConstraint(new Constraint("PK_Location", "PRIMARY KEY", "id") { Clustered = true, Unique = true }); loc.Columns.Items[0].Identity = new Identity(1, 1); var formType = new Table("dbo", "FormType"); formType.Columns.Add(new Column("code", "tinyint", false, null) {Position = 1}); formType.Columns.Add(new Column("desc", "varchar", 10, false, null) {Position = 2}); formType.AddConstraint(new Constraint("PK_FormType", "PRIMARY KEY", "code") { Clustered = true, Unique = true }); formType.AddConstraint(Constraint.CreateCheckedConstraint("CK_FormType", false, "([code]<(5))")); var categoryType = new Table("dbo", "CategoryType"); categoryType.Columns.Add(new Column("id", "int", false, null) { Position = 1 }); categoryType.Columns.Add(new Column("Category", "varchar", 10, false, null) { Position = 2 }); categoryType.AddConstraint(new Constraint("PK_CategoryType", "PRIMARY KEY", "id") { Clustered = true, Unique = true }); var emptyTable = new Table("dbo", "EmptyTable"); emptyTable.Columns.Add(new Column("code", "tinyint", false, null) {Position = 1}); emptyTable.AddConstraint(new Constraint("PK_EmptyTable", "PRIMARY KEY", "code") {Clustered = true, Unique = true}); var fk_policy_formType = new ForeignKey("FK_Policy_FormType"); fk_policy_formType.Table = policy; fk_policy_formType.Columns.Add("form"); fk_policy_formType.RefTable = formType; fk_policy_formType.RefColumns.Add("code"); fk_policy_formType.OnUpdate = "NO ACTION"; fk_policy_formType.OnDelete = "NO ACTION"; var fk_location_policy = new ForeignKey("FK_Location_Policy"); fk_location_policy.Table = loc; fk_location_policy.Columns.Add("policyId"); fk_location_policy.RefTable = policy; fk_location_policy.RefColumns.Add("id"); fk_location_policy.OnUpdate = "NO ACTION"; fk_location_policy.OnDelete = "CASCADE"; var fk_location_category = new ForeignKey("FK_Location_category"); fk_location_category.Table = loc; fk_location_category.Columns.Add("category"); fk_location_category.RefTable = categoryType; fk_location_category.RefColumns.Add("id"); fk_location_category.OnUpdate = "NO ACTION"; fk_location_category.OnDelete = "CASCADE"; var tt_codedesc = new Table("dbo", "CodeDesc"); tt_codedesc.IsType = true; tt_codedesc.Columns.Add(new Column("code", "tinyint", false, null) { Position = 1 }); tt_codedesc.Columns.Add(new Column("desc", "varchar", 10, false, null) { Position = 2 }); tt_codedesc.AddConstraint(new Constraint("PK_CodeDesc", "PRIMARY KEY", "code")); var db = new Database("ScriptToDirTest"); db.Tables.Add(policy); db.Tables.Add(formType); db.Tables.Add(categoryType); db.Tables.Add(emptyTable); db.Tables.Add(loc); db.TableTypes.Add(tt_codedesc); db.ForeignKeys.Add(fk_policy_formType); db.ForeignKeys.Add(fk_location_policy); db.ForeignKeys.Add(fk_location_category); db.FindProp("COMPATIBILITY_LEVEL").Value = "110"; db.FindProp("COLLATE").Value = "SQL_Latin1_General_CP1_CI_AS"; db.FindProp("AUTO_CLOSE").Value = "OFF"; db.FindProp("AUTO_SHRINK").Value = "ON"; db.FindProp("ALLOW_SNAPSHOT_ISOLATION").Value = "ON"; db.FindProp("READ_COMMITTED_SNAPSHOT").Value = "OFF"; db.FindProp("RECOVERY").Value = "SIMPLE"; db.FindProp("PAGE_VERIFY").Value = "CHECKSUM"; db.FindProp("AUTO_CREATE_STATISTICS").Value = "ON"; db.FindProp("AUTO_UPDATE_STATISTICS").Value = "ON"; db.FindProp("AUTO_UPDATE_STATISTICS_ASYNC").Value = "ON"; db.FindProp("ANSI_NULL_DEFAULT").Value = "ON"; db.FindProp("ANSI_NULLS").Value = "ON"; db.FindProp("ANSI_PADDING").Value = "ON"; db.FindProp("ANSI_WARNINGS").Value = "ON"; db.FindProp("ARITHABORT").Value = "ON"; db.FindProp("CONCAT_NULL_YIELDS_NULL").Value = "ON"; db.FindProp("NUMERIC_ROUNDABORT").Value = "ON"; db.FindProp("QUOTED_IDENTIFIER").Value = "ON"; db.FindProp("RECURSIVE_TRIGGERS").Value = "ON"; db.FindProp("CURSOR_CLOSE_ON_COMMIT").Value = "ON"; db.FindProp("CURSOR_DEFAULT").Value = "LOCAL"; db.FindProp("TRUSTWORTHY").Value = "ON"; db.FindProp("DB_CHAINING").Value = "ON"; db.FindProp("PARAMETERIZATION").Value = "FORCED"; db.FindProp("DATE_CORRELATION_OPTIMIZATION").Value = "ON"; db.Connection = ConfigHelper.TestDB.Replace("database=TESTDB", "database=" + db.Name); db.ExecCreate(true); DBHelper.ExecSql(db.Connection, " insert into formType ([code], [desc]) values (1, 'DP-1')\n" + "insert into formType ([code], [desc]) values (2, 'DP-2')\n" + "insert into formType ([code], [desc]) values (3, 'DP-3')"); db.DataTables.Add(formType); db.DataTables.Add(emptyTable); db.Dir = db.Name; if (Directory.Exists(db.Dir)) Directory.Delete(db.Dir, true); db.ScriptToDir(); Assert.IsTrue(Directory.Exists(db.Name)); Assert.IsTrue(Directory.Exists(db.Name + "\\data")); Assert.IsTrue(Directory.Exists(db.Name + "\\tables")); Assert.IsTrue(Directory.Exists(db.Name + "\\foreign_keys")); foreach (var t in db.DataTables) { if (t.Name == "EmptyTable") { Assert.IsFalse(File.Exists(db.Name + "\\data\\" + t.Name + ".tsv")); } else { Assert.IsTrue(File.Exists(db.Name + "\\data\\" + t.Name + ".tsv")); } } foreach (var t in db.Tables) { var tblFile = db.Name + "\\tables\\" + t.Name + ".sql"; Assert.IsTrue(File.Exists(tblFile)); // Test that the constraints are ordered in the file string script = File.ReadAllText(tblFile); int cindex = -1; foreach (var ckobject in t.Constraints.OrderBy(x => x.Name)) { var thisindex = script.IndexOf(ckobject.ScriptCreate()); Assert.Greater(thisindex, cindex, "Constraints are not ordered."); cindex = thisindex; } } foreach (var t in db.TableTypes) { Assert.IsTrue(File.Exists(db.Name + "\\table_types\\TYPE_" + t.Name + ".sql")); } foreach (var expected in db.ForeignKeys.Select(fk => db.Name + "\\foreign_keys\\" + fk.Table.Name + ".sql")) { Assert.IsTrue(File.Exists(expected), "File does not exist" + expected); } // Test that the foreign keys are ordered in the file foreach (var t in db.Tables) { var fksFile = db.Name + "\\foreign_keys\\" + t.Name + ".sql"; if (File.Exists(fksFile)) { string script = File.ReadAllText(fksFile); int fkindex = -1; foreach (var fkobject in db.ForeignKeys.Where(x => x.Table == t).OrderBy(x => x.Name)) { var thisindex = script.IndexOf(fkobject.ScriptCreate()); Assert.Greater(thisindex, fkindex, "Foreign keys are not ordered."); fkindex = thisindex; } } } var copy = new Database("ScriptToDirTestCopy"); copy.Dir = db.Dir; copy.Connection = ConfigHelper.TestDB.Replace("database=TESTDB", "database=" + copy.Name); copy.CreateFromDir(true); copy.Load(); TestCompare(db, copy); }