Exemplo n.º 1
0
 public override int Run(string[] remainingArguments)
 {
     var sourceDb = new Database();
     var targetDb = new Database();
     sourceDb.Connection = _source;
     targetDb.Connection = _target;
     sourceDb.Load();
     targetDb.Load();
     DatabaseDiff diff = sourceDb.Compare(targetDb);
     if (diff.IsDiff) {
         Console.WriteLine("Databases are different.");
         if (!string.IsNullOrEmpty(_outDiff)) {
             if (!_overwrite && File.Exists(_outDiff)) {
                 if (!ConsoleQuestion.AskYN(string.Format("{0} already exists - do you want to replace it", _outDiff))) {
                     return 1;
                 }
             }
             File.WriteAllText(_outDiff, diff.Script());
             Console.WriteLine("Script to make the databases identical has been created at {0}", Path.GetFullPath(_outDiff));
         }
         return 1;
     }
     Console.WriteLine("Databases are identical.");
     return 0;
 }
Exemplo n.º 2
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);
        }
Exemplo n.º 3
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();
        }
Exemplo n.º 4
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();
        }
Exemplo n.º 5
0
        public override int Run(string[] remainingArguments)
        {
            var sourceDb = new Database();
            sourceDb.Connection = _source;
            sourceDb.Load(_ignore);

            var serializer = new XmlSerializer(typeof(Database));
            using (var stream = new StreamWriter(_target, false))
            {
                serializer.Serialize(stream, sourceDb);
            }

            return 0;
        }
Exemplo n.º 6
0
        public string ScriptCreate(Database db)
        {
            string login = PasswordHash == null ? string.Empty : string.Format(@"IF SUSER_ID('{0}') IS NULL
                BEGIN CREATE LOGIN {0} WITH PASSWORD = {1} HASHED END
            ", Name, "0x" + new SoapHexBinary(PasswordHash));

            return login +
                   string.Format("CREATE USER {0} {1} {2}{3}", Name, PasswordHash == null ? "WITHOUT LOGIN" : "FOR LOGIN " + Name,
                       string.IsNullOrEmpty(DefaultSchema) ? string.Empty : "WITH DEFAULT_SCHEMA = ", DefaultSchema)
                   + "\r\n" +
                   string.Join("\r\n",
                       DatabaseRoles.Select(
                           r => string.Format("/*ALTER ROLE {0} ADD MEMBER {1}*/ exec sp_addrolemember '{0}', '{1}'", r, Name))
                           .ToArray());
        }
Exemplo n.º 7
0
        public void TestCollate()
        {
            string pathToSchema = ConfigHelper.TestSchemaDir + "/SANDBOX3_GBL.SQL";
            TestHelper.DropDb("TEST_SOURCE");

            //create the db from sql script
            TestHelper.ExecSql("CREATE DATABASE TEST_SOURCE", "");
            TestHelper.ExecBatchSql(File.ReadAllText(pathToSchema), "TEST_SOURCE");

            //load the model from newly created db and check collation
            var copy = new Database("TEST_COPY");
            copy.Connection = TestHelper.GetConnString("TEST_SOURCE");
            copy.Load();

            Assert.AreEqual("SQL_Latin1_General_CP1_CI_AS", copy.FindProp("COLLATE").Value);
        }
Exemplo n.º 8
0
 private static void HandleDataTables(Database db, string tableNames)
 {
     foreach (string value in tableNames.Split(',')) {
         string schema = "dbo";
         string name = value;
         if (value.Contains(".")) {
             schema = value.Split('.')[0];
             name = value.Split('.')[1];
         }
         Table t = db.FindTable(name, schema);
         if (t == null) {
             Console.WriteLine(
                 "warning: could not find data table {0}.{1}",
                 schema, name);
         }
         if (db.DataTables.Contains(t)) continue;
         db.DataTables.Add(t);
     }
 }
Exemplo n.º 9
0
 public string ScriptCreate(Database db)
 {
     string script = "";
     bool defaultQuotedId = !QuotedId;
     if (db != null && db.FindProp("QUOTED_IDENTIFIER") != null) {
         defaultQuotedId = db.FindProp("QUOTED_IDENTIFIER").Value == "ON";
     }
     if (defaultQuotedId != QuotedId) {
         script = string.Format(@"SET QUOTED_IDENTIFIER {0} {1}GO{1}",
             (QuotedId ? "ON" : "OFF"), Environment.NewLine);
     }
     bool defaultAnsiNulls = !AnsiNull;
     if (db != null && db.FindProp("ANSI_NULLS") != null) {
         defaultAnsiNulls = db.FindProp("ANSI_NULLS").Value == "ON";
     }
     if (defaultAnsiNulls != AnsiNull) {
         script = string.Format(@"SET ANSI_NULLS {0} {1}GO{1}",
             (AnsiNull ? "ON" : "OFF"), Environment.NewLine);
     }
     return script + Text;
 }
Exemplo n.º 10
0
        public static void TestCopySchema(string pathToSchemaScript)
        {
            TestHelper.DropDb("TEST_SOURCE");
            TestHelper.DropDb("TEST_COPY");

            //create the db from sql script
            TestHelper.ExecSql("CREATE DATABASE TEST_SOURCE", "");
            TestHelper.ExecBatchSql(File.ReadAllText(pathToSchemaScript), "TEST_SOURCE");

            //load the model from newly created db and create a copy
            var copy = new Database("TEST_COPY");
            copy.Connection = TestHelper.GetConnString("TEST_SOURCE");
            copy.Load();
            SqlConnection.ClearAllPools();
            TestHelper.ExecBatchSql(copy.ScriptCreate(), "master");

            //compare the dbs to make sure they are the same
            var source = new Database("TEST_SOURCE");
            source.Connection = TestHelper.GetConnString("TEST_SOURCE");
            source.Load();
            copy.Load();
            TestCompare(source, copy);
        }
Exemplo n.º 11
0
        public DatabaseDiff Compare(Database otherDb, CompareConfig compareConfig = null)
        {
            compareConfig = compareConfig ?? new CompareConfig();

            var diff = new DatabaseDiff();
            diff.Db = otherDb;

            if (!compareConfig.IgnoreProps) {
                //compare database properties
                foreach (DbProp p in Props) {
                    DbProp p2 = otherDb.FindProp(p.Name);
                    if (p.Script() != p2.Script()) {
                        diff.PropsChanged.Add(p);
                    }
                }
            }

            CompareTables(otherDb, compareConfig, diff);
            CompareRoutines(otherDb, compareConfig, diff);
            CompareForeignKeys(otherDb, compareConfig, diff);

            return diff;
        }
Exemplo n.º 12
0
        public void TestDiffScript()
        {
            TestHelper.DropDb("TEST_SOURCE");
            TestHelper.DropDb("TEST_COPY");

            //create the dbs from sql script
            string script = File.ReadAllText(ConfigHelper.TestSchemaDir + "\\BOP_QUOTE.sql");
            TestHelper.ExecSql("CREATE DATABASE TEST_SOURCE", "");
            TestHelper.ExecBatchSql(script, "TEST_SOURCE");

            script = File.ReadAllText(ConfigHelper.TestSchemaDir + "\\BOP_QUOTE_2.sql");
            TestHelper.ExecSql("CREATE DATABASE TEST_COPY", "");
            TestHelper.ExecBatchSql(script, "TEST_COPY");

            var source = new Database("TEST_SOURCE");
            source.Connection = TestHelper.GetConnString("TEST_SOURCE");
            source.Load();

            var copy = new Database("TEST_COPY");
            copy.Connection = TestHelper.GetConnString("TEST_COPY");
            copy.Load();

            //execute migration script to make SOURCE the same as COPY
            DatabaseDiff diff = copy.Compare(source);
            TestHelper.ExecBatchSql(diff.Script(), "TEST_SOURCE");

            //compare the dbs to make sure they are the same
            string cmd = string.Format("/c {0}\\SQLDBDiffConsole.exe {1} {2} {0}\\{3}", ConfigHelper.SqlDbDiffPath,
                "localhost\\SQLEXPRESS TEST_COPY   NULL NULL Y", "localhost\\SQLEXPRESS TEST_SOURCE NULL NULL Y",
                "SqlDbDiff.XML CompareResult.txt null");
            var proc = new Process();
            proc.StartInfo.FileName = "cmd.exe";
            proc.StartInfo.Arguments = cmd;
            proc.StartInfo.WindowStyle = ProcessWindowStyle.Normal;
            proc.Start();
            proc.WaitForExit();

            Assert.AreEqual("no difference", File.ReadAllLines("CompareResult.txt")[0]);
        }
Exemplo n.º 13
0
        public void TestFindTableRegEx()
        {
            var db = new Database();
            db.Tables.Add(new Table("dbo", "cmicDeductible"));
            db.Tables.Add(new Table("dbo", "cmicZipCode"));
            db.Tables.Add(new Table("dbo", "cmicState"));
            db.Tables.Add(new Table("dbo", "Policy"));
            db.Tables.Add(new Table("dbo", "Location"));
            db.Tables.Add(new Table("dbo", "Rate"));

            Assert.AreEqual(3, db.FindTablesRegEx("^cmic").Count);
            Assert.AreEqual(1, db.FindTablesRegEx("Location").Count);
        }
Exemplo n.º 14
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);
            }
        }
Exemplo n.º 15
0
        public DatabaseDiff Compare(Database db)
        {
            var diff = new DatabaseDiff();
            diff.Db = db;

            //compare database properties
            foreach (DbProp p in Props)
            {
                DbProp p2 = db.FindProp(p.Name);
                if (p.Script() != p2.Script())
                {
                    diff.PropsChanged.Add(p);
                }
            }

            //get tables added and changed
            foreach (Table t in Tables)
            {
                Table t2 = db.FindTable(t.Name, t.Owner);
                if (t2 == null)
                {
                    diff.TablesAdded.Add(t);
                }
                else
                {
                    //compare mutual tables
                    TableDiff tDiff = t.Compare(t2);
                    if (tDiff.IsDiff)
                    {
                        diff.TablesDiff.Add(tDiff);
                    }
                }
            }
            //get deleted tables
            foreach (Table t in db.Tables)
            {
                if (FindTable(t.Name, t.Owner) == null)
                {
                    diff.TablesDeleted.Add(t);
                }
            }

            //get procs added and changed
            foreach (Routine r in Routines)
            {
                Routine r2 = db.FindRoutine(r.Name, r.Schema);
                if (r2 == null)
                {
                    diff.RoutinesAdded.Add(r);
                }
                else
                {
                    //compare mutual procs
                    if (r.Text != r2.Text)
                    {
                        diff.RoutinesDiff.Add(r);
                    }
                }
            }
            //get procs deleted
            foreach (Routine r in db.Routines)
            {
                if (FindRoutine(r.Name, r.Schema) == null)
                {
                    diff.RoutinesDeleted.Add(r);
                }
            }

            //get added and compare mutual foreign keys
            foreach (ForeignKey fk in ForeignKeys)
            {
                ForeignKey fk2 = db.FindForeignKey(fk.Name);
                if (fk2 == null)
                {
                    diff.ForeignKeysAdded.Add(fk);
                }
                else
                {
                    if (fk.ScriptCreate() != fk2.ScriptCreate())
                    {
                        diff.ForeignKeysDiff.Add(fk);
                    }
                }
            }
            //get deleted foreign keys
            foreach (ForeignKey fk in db.ForeignKeys)
            {
                if (FindForeignKey(fk.Name) == null)
                {
                    diff.ForeignKeysDeleted.Add(fk);
                }
            }

            return diff;
        }
Exemplo n.º 16
0
        private void CompareTables(Database otherDb, CompareConfig compareConfig, DatabaseDiff diff)
        {
            Action<Table, Table> checkIfTableChanged = (t, t2) => {
                //compare mutual tables
                TableDiff tDiff = t.Compare(t2, compareConfig);
                if (tDiff.IsDiff) {
                    diff.TablesDiff.Add(tDiff);
                }
            };

            CheckSource(compareConfig.TablesCompareMethod,
                Tables,
                t => otherDb.FindTable(t.Name, t.Owner),
                t => diff.TablesAdded.Add(t),
                checkIfTableChanged);

            //get deleted tables
            CheckTarget(compareConfig.TablesCompareMethod,
                otherDb.Tables,
                t => FindTable(t.Name, t.Owner) == null,
                t => diff.TablesDeleted.Add(t));
        }
Exemplo n.º 17
0
        private void CompareRoutines(Database otherDb, CompareConfig compareConfig, DatabaseDiff diff)
        {
            Action<Routine, Routine> checkIfRoutineChanged = (r, r2) => {
                if (compareConfig.IgnoreRoutinesTextMismatch) {
                    return;
                }

                //compare mutual procs
                if (r.Text.Replace("\r\n", "\n") != r2.Text.Replace("\r\n", "\n")) {
                    diff.RoutinesDiff.Add(r);
                }
            };

            CheckSource(compareConfig.RoutinesCompareMethod,
                Routines,
                r => otherDb.FindRoutine(r.Name, r.Schema),
                r => diff.RoutinesAdded.Add(r),
                checkIfRoutineChanged);

            //get procs deleted in source db or added in target db
            CheckTarget(compareConfig.RoutinesCompareMethod,
                otherDb.Routines,
                r => FindRoutine(r.Name, r.Schema) == null,
                r => diff.RoutinesDeleted.Add(r));
        }
Exemplo n.º 18
0
        private void CompareForeignKeys(Database otherDb, CompareConfig compareConfig, DatabaseDiff diff)
        {
            Action<ForeignKey, ForeignKey> checkIfFkChanged = (fk1, fk2) => {
                if (fk1.ScriptCreate() != fk2.ScriptCreate()) {
                    diff.ForeignKeysDiff.Add(fk1);
                }
            };

            CheckSource(compareConfig.ForeignKeysCompareMethod,
                ForeignKeys,
                fk => otherDb.FindForeignKey(fk.Name),
                fk => diff.ForeignKeysAdded.Add(fk),
                checkIfFkChanged);

            //get deleted foreign keys
            CheckTarget(compareConfig.ForeignKeysCompareMethod, otherDb.ForeignKeys,
                fk => FindForeignKey(fk.Name) == null, fk => diff.ForeignKeysDeleted.Add(fk));
        }
Exemplo n.º 19
0
        public void TestScriptDeletedProc()
        {
            var source = new Database();
            source.Routines.Add(new Routine("dbo", "test"));
            source.FindRoutine("test", "dbo").RoutineType = Routine.RoutineKind.Procedure;
            source.FindRoutine("test", "dbo").Text = @"
            create procedure [dbo].[test]
            as
            select * from Table1
            ";

            var target = new Database();
            string scriptUp = target.Compare(source).Script();
            string scriptDown = source.Compare(target).Script();
            Assert.IsTrue(scriptUp.ToLower().Contains("drop procedure [dbo].[test]"));
            Assert.IsTrue(scriptDown.ToLower().Contains("create procedure [dbo].[test]"));
        }
Exemplo n.º 20
0
        public DatabaseDiff Compare(Database db)
        {
            var diff = new DatabaseDiff();
            diff.Db = db;

            //compare database properties
            foreach (DbProp p in from p in Props
                let p2 = db.FindProp(p.Name)
                where p.Script() != p2.Script()
                select p) {
                diff.PropsChanged.Add(p);
            }

            //get tables added and changed
            foreach (Table t in Tables) {
                Table t2 = db.FindTable(t.Name, t.Owner);
                if (t2 == null) {
                    diff.TablesAdded.Add(t);
                } else {
                    //compare mutual tables
                    TableDiff tDiff = t.Compare(t2);
                    if (tDiff.IsDiff) {
                        diff.TablesDiff.Add(tDiff);
                    }
                }
            }
            //get deleted tables
            foreach (Table t in db.Tables.Where(t => FindTable(t.Name, t.Owner) == null)) {
                diff.TablesDeleted.Add(t);
            }

            //get procs added and changed
            foreach (Routine r in Routines) {
                Routine r2 = db.FindRoutine(r.Name, r.Schema);
                if (r2 == null) {
                    diff.RoutinesAdded.Add(r);
                } else {
                    //compare mutual procs
                    if (r.Text != r2.Text) {
                        diff.RoutinesDiff.Add(r);
                    }
                }
            }
            //get procs deleted
            foreach (Routine r in db.Routines.Where(r => FindRoutine(r.Name, r.Schema) == null)) {
                diff.RoutinesDeleted.Add(r);
            }

            //get added and compare mutual foreign keys
            foreach (ForeignKey fk in ForeignKeys) {
                ForeignKey fk2 = db.FindForeignKey(fk.Name);
                if (fk2 == null) {
                    diff.ForeignKeysAdded.Add(fk);
                } else {
                    if (fk.ScriptCreate() != fk2.ScriptCreate()) {
                        diff.ForeignKeysDiff.Add(fk);
                    }
                }
            }
            //get deleted foreign keys
            foreach (ForeignKey fk in db.ForeignKeys.Where(fk => FindForeignKey(fk.Name) == null)) {
                diff.ForeignKeysDeleted.Add(fk);
            }

            //get added and compare mutual assemblies
            foreach (SqlAssembly a in Assemblies) {
                SqlAssembly a2 = db.FindAssembly(a.Name);
                if (a2 == null) {
                    diff.AssembliesAdded.Add(a);
                } else {
                    if (a.ScriptCreate(this) != a2.ScriptCreate(db)) {
                        diff.AssembliesDiff.Add(a);
                    }
                }
            }
            //get deleted assemblies
            foreach (SqlAssembly a in db.Assemblies.Where(a => FindAssembly(a.Name) == null)) {
                diff.AssembliesDeleted.Add(a);
            }

            //get added and compare mutual users
            foreach (SqlUser u in Users) {
                SqlUser u2 = db.FindUser(u.Name);
                if (u2 == null) {
                    diff.UsersAdded.Add(u);
                } else {
                    if (u.ScriptCreate(this) != u2.ScriptCreate(db)) {
                        diff.UsersDiff.Add(u);
                    }
                }
            }
            //get deleted users
            foreach (SqlUser u in db.Users.Where(u => FindUser(u.Name) == null)) {
                diff.UsersDeleted.Add(u);
            }

            //get added and compare view indexes
            foreach (Constraint c in ViewIndexes) {
                Constraint c2 = db.FindViewIndex(c.Name);
                if (c2 == null) {
                    diff.ViewIndexesAdded.Add(c);
                } else {
                    if (c.Script() != c2.Script()) {
                        diff.ViewIndexesDiff.Add(c);
                    }
                }
            }
            //get deleted view indexes
            foreach (Constraint c in db.ViewIndexes.Where(c => FindViewIndex(c.Name) == null)) {
                diff.ViewIndexesDeleted.Add(c);
            }

            return diff;
        }
Exemplo n.º 21
0
        private Database GetDatabase(string connectionString, string filePath)
        {
            Database db;
            if (!string.IsNullOrEmpty(connectionString)) {
                db = new Database();
                db.Connection = connectionString;
                db.Load();

                return db;
            }

            var serializer = new XmlSerializer(typeof(Database));
            using (var stream = new StreamReader(filePath, false)) {
                return (Database)serializer.Deserialize(stream);
            }
        }
Exemplo n.º 22
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);
        }
Exemplo n.º 23
0
        private static void TestCompare(Database source, Database copy)
        {
            //compare the dbs to make sure they are the same
            Assert.IsFalse(source.Compare(copy).IsDiff);

            // get a second opinion
            // if you ever find your license key
            /*
                        var cmd = string.Format("/c {0}\\SQLDBDiffConsole.exe {1} {2} {0}\\{3}",
                            ConfigHelper.SqlDbDiffPath,
                            "localhost\\SQLEXPRESS " + copy.Name + " NULL NULL Y",
                            "localhost\\SQLEXPRESS " + source.Name + " NULL NULL Y",
                            "SqlDbDiff.XML CompareResult.txt null");

                        Console.WriteLine(cmd);
                        var proc = new Process();
                        proc.StartInfo.FileName = "cmd.exe";
                        proc.StartInfo.Arguments = cmd;
                        proc.StartInfo.WindowStyle = ProcessWindowStyle.Normal;
                        proc.Start();
                        proc.WaitForExit();
                        Assert.AreEqual("no difference", File.ReadAllLines("CompareResult.txt")[0]);
            */
        }