示例#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 TestExportData()
        {
            var t = new Table("dbo", "Status");
            t.Columns.Add(new Column("id", "int", false, null));
            t.Columns.Add(new Column("code", "char", 1, false, null));
            t.Columns.Add(new Column("description", "varchar", 20, false, null));
            t.Columns.Find("id").Identity = new Identity(1, 1);
            t.Constraints.Add(new Constraint("PK_Status", "PRIMARY KEY", "id"));

            string conn = TestHelper.GetConnString("TESTDB");
            DBHelper.DropDb(conn);
            DBHelper.CreateDb(conn);
            SqlConnection.ClearAllPools();
            DBHelper.ExecBatchSql(conn, t.ScriptCreate());

            string dataIn =
                @"1	R	Ready
            2	P	Processing
            3	F	Frozen
            ";

            t.ImportData(conn, dataIn);

            string dataOut = t.ExportData(conn);
            Assert.AreEqual(dataIn, dataOut);
        }
示例#3
0
        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.Constraints.Add(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.Constraints.Add(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", "");
        }
示例#4
0
        public ForeignKey(Table table, string name, string columns, Table refTable, string refColumns, string onUpdate,
			string onDelete)
        {
            Table = table;
            Name = name;
            Columns = new List<string>(columns.Split(','));
            RefTable = refTable;
            RefColumns = new List<string>(refColumns.Split(','));
            OnUpdate = onUpdate;
            OnDelete = onDelete;
        }
示例#5
0
 public ForeignKey(Table table, string name, string columns, Table refTable, string refColumns, string onUpdate,
     string onDelete)
 {
     Table = new TableInfo(table.Owner, table.Name);
     Name = name;
     Columns = new List<string>(columns.Split(','));
     RefTable = new TableInfo(refTable.Owner, refTable.Name);
     RefColumns = new List<string>(refColumns.Split(','));
     OnUpdate = onUpdate;
     OnDelete = onDelete;
 }
示例#6
0
        public void CanIgnoreTable()
        {
            var db = new Database();
            var table = new Table("dbo", "IgnoredTableWithConstraints");
            var constraint = new Constraint("TestConstraint", "", "");
            table.Constraints.Add(constraint);
            db.DataTables.Add(table);

            db.Ignore(new[] { "IgnoredTableWithConstraints" });

            db.Tables.Any().Should().BeFalse();
        }
示例#7
0
        public void CanIgnoreForeignKeysOfIgnoredTable()
        {
            var db = new Database();
            var table = new Table("dbo", "IgnoredTableWithForeignKey");
            var refTable = new Table("dbo", "RefTable");
            db.DataTables.Add(table);
            db.ForeignKeys.Add(new ForeignKey(table,"","", refTable, ""));

            db.Ignore(new[] { "IgnoredTableWithForeignKey" });

            db.Tables.Any().Should().BeFalse();
            db.ForeignKeys.Any().Should().BeFalse();
        }
示例#8
0
文件: Table.cs 项目: aeagle/schemazen
        public TableDiff Compare(Table t)
        {
            var diff = new TableDiff();
            diff.Owner = t.Owner;
            diff.Name = t.Name;

            //get additions and compare mutual columns
            foreach (Column c in Columns.Items) {
                Column c2 = t.Columns.Find(c.Name);
                if (c2 == null) {
                    diff.ColumnsAdded.Add(c);
                } else {
                    //compare mutual columns
                    ColumnDiff cDiff = c.Compare(c2);
                    if (cDiff.IsDiff) {
                        diff.ColumnsDiff.Add(cDiff);
                    }
                }
            }

            //get deletions
            foreach (Column c in t.Columns.Items.Where(c => Columns.Find(c.Name) == null)) {
                diff.ColumnsDropped.Add(c);
            }

            //get added and compare mutual constraints
            foreach (Constraint c in Constraints) {
                Constraint c2 = t.FindConstraint(c.Name);
                if (c2 == null) {
                    diff.ConstraintsAdded.Add(c);
                } else {
                    if (c.Script() != c2.Script()) {
                        diff.ConstraintsChanged.Add(c);
                    }
                }
            }
            //get deleted constraints
            foreach (Constraint c in t.Constraints.Where(c => FindConstraint(c.Name) == null)) {
                diff.ConstraintsDeleted.Add(c);
            }

            return diff;
        }
示例#9
0
        public void TestCompare()
        {
            var t1 = new Table("dbo", "Test");
            var t2 = new Table("dbo", "Test");
            TableDiff diff = default(TableDiff);

            //test equal
            t1.Columns.Add(new Column("first", "varchar", 30, false, null));
            t2.Columns.Add(new Column("first", "varchar", 30, false, null));
            t1.Constraints.Add(new Constraint("PK_Test", "PRIMARY KEY", "first"));
            t2.Constraints.Add(new Constraint("PK_Test", "PRIMARY KEY", "first"));

            diff = t1.Compare(t2, new CompareConfig());
            Assert.IsNotNull(diff);
            Assert.IsFalse(diff.IsDiff);

            //test add
            t1.Columns.Add(new Column("second", "varchar", 30, false, null));
            diff = t1.Compare(t2, new CompareConfig());
            Assert.IsTrue(diff.IsDiff);
            Assert.AreEqual(1, diff.ColumnsAdded.Count);

            //test delete
            diff = t2.Compare(t1, new CompareConfig());
            Assert.IsTrue(diff.IsDiff);
            Assert.AreEqual(1, diff.ColumnsDroped.Count);

            //test diff
            t1.Columns[0].Length = 20;
            diff = t1.Compare(t2, new CompareConfig());
            Assert.IsTrue(diff.IsDiff);
            Assert.AreEqual(1, diff.ColumnsDiff.Count);

            Console.WriteLine("--- create ----");
            Console.Write(t1.ScriptCreate());

            Console.WriteLine("--- migrate up ---");
            Console.Write(t1.Compare(t2, new CompareConfig()).Script());

            Console.WriteLine("--- migrate down ---");
            Console.Write(t2.Compare(t1, new CompareConfig()).Script());
        }
示例#10
0
        public void TestScript()
        {
            var t = new Table("dbo", "Address");
            t.Columns.Add(new Column("id", "int", false, null));
            t.Columns.Add(new Column("street", "varchar", 50, false, null));
            t.Columns.Add(new Column("city", "varchar", 50, false, null));
            t.Columns.Add(new Column("state", "char", 2, false, null));
            t.Columns.Add(new Column("zip", "char", 5, false, null));
            t.Constraints.Add(new Constraint("PK_Address", "PRIMARY KEY", "id"));

            var getAddress = new Routine("dbo", "GetAddress");
            getAddress.Text = @"
            CREATE PROCEDURE [dbo].[GetAddress]
            @id int
            AS
            select * from Address where id = @id
            ";

            TestHelper.ExecSql(t.ScriptCreate(), "");
            TestHelper.ExecBatchSql(getAddress.ScriptCreate(null) + "\nGO", "");
            TestHelper.ExecSql("drop table [dbo].[Address]", "");
            TestHelper.ExecSql("drop procedure [dbo].[GetAddress]", "");
        }
示例#11
0
 private static string MakeFileName(Table t)
 {
     return MakeFileName(t.Owner, t.Name);
 }
示例#12
0
 public void TestScriptNonSupportedColumn()
 {
     var t = new Table("dbo", "bla");
     t.Columns.Add(new Column("a", "madeuptype", true, null));
     t.ScriptCreate();
 }
示例#13
0
        public TableDiff Compare(Table otherTable, CompareConfig compareConfig)
        {
            var diff = new TableDiff();
            diff.Owner = otherTable.Owner;
            diff.Name = otherTable.Name;

            CompareColumns(otherTable, compareConfig, diff);
            CompareConstraints(otherTable, compareConfig, diff);

            return diff;
        }
示例#14
0
 public ForeignKey(Table table, string name, string columns, Table refTable, string refColumns)
     : this(table, name, columns, refTable, refColumns, "", "")
 {
 }
示例#15
0
        public void TestScript()
        {
            var db = new Database("TEST_TEMP");
            var t1 = new Table("dbo", "t1");
            t1.Columns.Add(new Column("col1", "int", false, null));
            t1.Columns.Add(new Column("col2", "int", false, null));
            t1.Constraints.Add(new Constraint("pk_t1", "PRIMARY KEY", "col1,col2"));
            t1.FindConstraint("pk_t1").Clustered = true;

            var t2 = new Table("dbo", "t2");
            t2.Columns.Add(new Column("col1", "int", false, null));
            t2.Columns.Add(new Column("col2", "int", false, null));
            t2.Columns.Add(new Column("col3", "int", false, null));
            t2.Constraints.Add(new Constraint("pk_t2", "PRIMARY KEY", "col1"));
            t2.FindConstraint("pk_t2").Clustered = true;
            t2.Constraints.Add(new Constraint("IX_col3", "UNIQUE", "col3"));

            db.ForeignKeys.Add(new ForeignKey(t2, "fk_t2_t1", "col2,col3", t1, "col1,col2"));

            db.Tables.Add(t1);
            db.Tables.Add(t2);

            TestHelper.DropDb("TEST_TEMP");
            SqlConnection.ClearAllPools();
            TestHelper.ExecBatchSql(db.ScriptCreate(), "master");

            var db2 = new Database();
            db2.Connection = TestHelper.GetConnString("TEST_TEMP");
            db2.Load();

            TestHelper.DropDb("TEST_TEMP");

            foreach (Table t in db.Tables) {
                Assert.IsNotNull(db2.FindTable(t.Name, t.Owner));
                Assert.IsFalse(db2.FindTable(t.Name, t.Owner).Compare(t).IsDiff);
            }
        }
示例#16
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);
        }
示例#17
0
        private void CompareConstraints(Table otherTable, CompareConfig compareConfig, TableDiff diff)
        {
            Action<Constraint, Constraint> checkIfConstraintChanged = (c, c2) => {
                if(!c.HasSameProperties(c2, compareConfig)) {
                    diff.ConstraintsChanged.Add(c);
                };
            };

            Func<Constraint, Constraint> getOtherConstraint = (c) => {
                var c2 = otherTable.FindConstraint(c.Name);
                if(compareConfig.IgnoreConstraintsNameMismatch && c2 == null)
                    return  otherTable.FindSimilarConstraint(c);

                return c2;
            };

            CheckSource(compareConfig.ConstraintsCompareMethod,
                Constraints,
                getOtherConstraint,
                c => diff.ConstraintsAdded.Add(c),
                checkIfConstraintChanged);

            Func<Constraint, bool> constraintExistsOnlyInTaget = (c) => {
                var c2 = FindConstraint(c.Name);
                if (compareConfig.IgnoreConstraintsNameMismatch && c2 == null)
                    c2 = FindSimilarConstraint(c);

                return c2 == null;
            };
            CheckTarget(compareConfig.ConstraintsCompareMethod,
                otherTable.Constraints,
                constraintExistsOnlyInTaget,
                c => diff.ConstraintsDeleted.Add(c));
        }
示例#18
0
        public void TestScript()
        {
            //create a table with all known types, script it, and execute the script
            var t = new Table("dbo", "AllTypesTest");
            t.Columns.Add(new Column("a", "bigint", false, null));
            t.Columns.Add(new Column("b", "binary", 50, false, null));
            t.Columns.Add(new Column("c", "bit", false, null));
            t.Columns.Add(new Column("d", "char", 10, false, null));
            t.Columns.Add(new Column("e", "datetime", false, null));
            t.Columns.Add(new Column("f", "decimal", 18, 0, false, null));
            t.Columns.Add(new Column("g", "float", false, null));
            t.Columns.Add(new Column("h", "image", false, null));
            t.Columns.Add(new Column("i", "int", false, null));
            t.Columns.Add(new Column("j", "money", false, null));
            t.Columns.Add(new Column("k", "nchar", 10, false, null));
            t.Columns.Add(new Column("l", "ntext", false, null));
            t.Columns.Add(new Column("m", "numeric", 18, 0, false, null));
            t.Columns.Add(new Column("n", "nvarchar", 50, false, null));
            t.Columns.Add(new Column("o", "nvarchar", -1, false, null));
            t.Columns.Add(new Column("p", "real", false, null));
            t.Columns.Add(new Column("q", "smalldatetime", false, null));
            t.Columns.Add(new Column("r", "smallint", false, null));
            t.Columns.Add(new Column("s", "smallmoney", false, null));
            t.Columns.Add(new Column("t", "sql_variant", false, null));
            t.Columns.Add(new Column("u", "text", false, null));
            t.Columns.Add(new Column("v", "timestamp", false, null));
            t.Columns.Add(new Column("w", "tinyint", false, null));
            t.Columns.Add(new Column("x", "uniqueidentifier", false, null));
            t.Columns.Add(new Column("y", "varbinary", 50, false, null));
            t.Columns.Add(new Column("z", "varbinary", -1, false, null));
            t.Columns.Add(new Column("aa", "varchar", 50, true, new Default("DF_AllTypesTest_aa", "'asdf'")));
            t.Columns.Add(new Column("bb", "varchar", -1, true, null));
            t.Columns.Add(new Column("cc", "xml", true, null));

            Console.WriteLine(t.ScriptCreate());
            TestHelper.ExecSql(t.ScriptCreate(), "");
            TestHelper.ExecSql("drop table [dbo].[AllTypesTest]", "");
        }
示例#19
0
        private void CompareColumns(Table otherTable, CompareConfig compareConfig, TableDiff diff)
        {
            Action<Column, Column> checkIfColumnChanged = (c, c2) => {
                //compare mutual columns
                ColumnDiff cDiff = c.Compare(c2, compareConfig);
                if (cDiff.IsDiff) {
                    diff.ColumnsDiff.Add(cDiff);
                }
            };

            CheckSource(compareConfig.ColumnsCompareMethod,
                Columns,
                c => otherTable.Columns.Find(c.Name),
                c => diff.ColumnsAdded.Add(c),
                checkIfColumnChanged);

            CheckTarget(compareConfig.RoutinesCompareMethod,
                otherTable.Columns,
                c => Columns.Find(c.Name) == null,
                c => diff.ColumnsDroped.Add(c));
        }