예제 #1
0
        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.Constraints.Add(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").Columns[0]);
            Assert.AreEqual("c2", db.FindForeignKey("fk_test").Columns[1]);
            Assert.AreEqual("c1", db.FindForeignKey("fk_test").RefColumns[0]);
            Assert.AreEqual("c2", db.FindForeignKey("fk_test").RefColumns[1]);

            db.ExecCreate(true);
        }
예제 #2
0
        public void TestScriptToDir()
        {
            var policy = new Table("dbo", "Policy");
            policy.Columns.Add(new Column("id", "int", false, null));
            policy.Columns.Add(new Column("form", "tinyint", false, null));
            policy.Constraints.Add(new Constraint("PK_Policy", "PRIMARY KEY", "id"));
            policy.Constraints[0].Clustered = true;
            policy.Constraints[0].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));
            loc.Columns.Add(new Column("policyId", "int", false, null));
            loc.Columns.Add(new Column("storage", "bit", false, null));
            loc.Constraints.Add(new Constraint("PK_Location", "PRIMARY KEY", "id"));
            loc.Constraints[0].Clustered = true;
            loc.Constraints[0].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));
            formType.Columns.Add(new Column("desc", "varchar", 10, false, null));
            formType.Constraints.Add(new Constraint("PK_FormType", "PRIMARY KEY", "code"));
            formType.Constraints[0].Clustered = 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 db = new Database("ScriptToDirTest");
            db.Tables.Add(policy);
            db.Tables.Add(formType);
            db.Tables.Add(loc);
            db.ForeignKeys.Add(fk_policy_formType);
            db.ForeignKeys.Add(fk_location_policy);
            db.FindProp("COMPATIBILITY_LEVEL").Value = "120";
            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.Dir = db.Name;
            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 (Table t in db.DataTables) {
                Assert.IsTrue(File.Exists(db.Name + "\\data\\" + t.Name + ".tsv"));
            }
            foreach (Table t in db.Tables) {
                Assert.IsTrue(File.Exists(db.Name + "\\tables\\" + t.Name + ".sql"));
            }
            foreach (string expected in db.ForeignKeys.Select(fk => db.Name + "\\foreign_keys\\" + fk.Table.Name + ".sql")) {
                Assert.IsTrue(File.Exists(expected), "File does not exist" + expected);
            }

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