public void TestScriptToDirOnlyCreatesNecessaryFolders()
{
var db = new Database("TestEmptyDB");
db.Connection = ConfigHelper.TestDB.Replace("database=TESTDB", "database=" + db.Name);
db.ExecCreate(true);
db.Dir = db.Name;
db.Load();
if (Directory.Exists(db.Dir)) // if the directory exists, delete it to make it a fair test
{
Directory.Delete(db.Dir, true);
}
db.ScriptToDir();
Assert.AreEqual(0, db.Assemblies.Count);
Assert.AreEqual(0, db.DataTables.Count);
Assert.AreEqual(0, db.ForeignKeys.Count);
Assert.AreEqual(0, db.Routines.Count);
Assert.AreEqual(0, db.Schemas.Count);
Assert.AreEqual(0, db.Synonyms.Count);
Assert.AreEqual(0, db.Tables.Count);
Assert.AreEqual(0, db.TableTypes.Count);
Assert.AreEqual(0, db.Users.Count);
Assert.AreEqual(0, db.ViewIndexes.Count);
Assert.IsTrue(Directory.Exists(db.Name));
Assert.IsTrue(File.Exists(db.Name + "\\props.sql"));
//Assert.IsFalse(File.Exists(db.Name + "\\schemas.sql"));
Assert.IsFalse(Directory.Exists(db.Name + "\\assemblies"));
Assert.IsFalse(Directory.Exists(db.Name + "\\data"));
Assert.IsFalse(Directory.Exists(db.Name + "\\foreign_keys"));
foreach (var routineType in Enum.GetNames(typeof(Routine.RoutineKind)))
{
var dir = routineType.ToLower() + "s";
Assert.IsFalse(Directory.Exists(db.Name + "\\" + dir));
}
Assert.IsFalse(Directory.Exists(db.Name + "\\synonyms"));
Assert.IsFalse(Directory.Exists(db.Name + "\\tables"));
Assert.IsFalse(Directory.Exists(db.Name + "\\table_types"));
Assert.IsFalse(Directory.Exists(db.Name + "\\users"));
}
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.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 });
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 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(loc);
db.TableTypes.Add(tt_codedesc);
db.ForeignKeys.Add(fk_policy_formType);
db.ForeignKeys.Add(fk_location_policy);
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.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) {
Assert.IsTrue(File.Exists(db.Name + "\\data\\" + t.Name + ".tsv"));
}
foreach (var t in db.Tables) {
Assert.IsTrue(File.Exists(db.Name + "\\tables\\" + t.Name + ".sql"));
}
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);
}
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);
}
public void TestScriptViewInsteadOfTrigger() {
var setupSQL1 = @"
CREATE TABLE [dbo].[t1]
(
a INT NOT NULL,
CONSTRAINT [PK] PRIMARY KEY (a)
)
";
var setupSQL2 = @"
CREATE VIEW [dbo].[v1] AS
SELECT * FROM t1
";
var setupSQL3 = @"
CREATE TRIGGER [dbo].[TR_v1] ON [dbo].[v1] INSTEAD OF DELETE AS
DELETE FROM [dbo].[t1] FROM [dbo].[t1] INNER JOIN DELETED ON DELETED.a = [dbo].[t1].a
";
var db = new Database("TestScriptViewInsteadOfTrigger");
db.Connection = ConfigHelper.TestDB.Replace("database=TESTDB", "database=" + db.Name);
db.ExecCreate(true);
DBHelper.ExecSql(db.Connection, setupSQL1);
DBHelper.ExecSql(db.Connection, setupSQL2);
DBHelper.ExecSql(db.Connection, setupSQL3);
db.Dir = db.Name;
db.Load();
// Required in order to expose the exception
db.ScriptToDir();
var triggers = db.Routines.Where(x => x.RoutineType == Routine.RoutineKind.Trigger).ToList();
Assert.AreEqual(1, triggers.Count());
Assert.AreEqual("TR_v1", triggers[0].Name);
Assert.IsTrue(File.Exists(db.Name + "\\triggers\\TR_v1.sql"));
}
public void TestScriptFKSameName() {
var setupSQL = @"
CREATE SCHEMA [s2] AUTHORIZATION [dbo]
CREATE TABLE [dbo].[t1a]
(
a INT NOT NULL,
CONSTRAINT [PK_1a] PRIMARY KEY (a)
)
CREATE TABLE [dbo].[t1b]
(
a INT NOT NULL,
CONSTRAINT [FKName] FOREIGN KEY ([a]) REFERENCES [dbo].[t1a] ([a])
)
CREATE TABLE [s2].[t2a]
(
a INT NOT NULL,
CONSTRAINT [PK_2a] PRIMARY KEY (a)
)
CREATE TABLE [s2].[t2b]
(
a INT NOT NULL,
CONSTRAINT [FKName] FOREIGN KEY ([a]) REFERENCES [s2].[t2a] ([a])
)
";
var db = new Database("TestScriptFKSameName");
db.Connection = ConfigHelper.TestDB.Replace("database=TESTDB", "database=" + db.Name);
db.ExecCreate(true);
DBHelper.ExecSql(db.Connection, setupSQL);
db.Dir = db.Name;
db.Load();
// Required in order to expose the exception
db.ScriptToDir();
Assert.AreEqual(2, db.ForeignKeys.Count());
Assert.AreEqual(db.ForeignKeys[0].Name, db.ForeignKeys[1].Name);
Assert.AreNotEqual(db.ForeignKeys[0].Table.Owner, db.ForeignKeys[1].Table.Owner);
}
public void TestScriptTableTypePrimaryKey() {
var setupSQL1 = @"
CREATE TYPE [dbo].[MyTableType] AS TABLE(
[ID] [int] NOT NULL,
[Value] [varchar](50) NOT NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)
";
var db = new Database("TestScriptTableTypePrimaryKey");
db.Connection = ConfigHelper.TestDB.Replace("database=TESTDB", "database=" + db.Name);
db.ExecCreate(true);
DBHelper.ExecSql(db.Connection, setupSQL1);
db.Dir = db.Name;
db.Load();
db.ScriptToDir();
Assert.AreEqual(1, db.TableTypes.Count());
Assert.AreEqual(1, db.TableTypes[0].PrimaryKey.Columns.Count);
Assert.AreEqual("ID", db.TableTypes[0].PrimaryKey.Columns[0]);
Assert.AreEqual(50, db.TableTypes[0].Columns.Items[1].Length);
Assert.AreEqual("MyTableType", db.TableTypes[0].Name);
Assert.IsTrue(File.Exists(db.Name + "\\table_types\\TYPE_MyTableType.sql"));
Assert.IsTrue(File.ReadAllText(db.Name + "\\table_types\\TYPE_MyTableType.sql").Contains("PRIMARY KEY"));
}
public void TestScriptTableType() {
var setupSQL1 = @"
CREATE TYPE [dbo].[MyTableType] AS TABLE(
[ID] [nvarchar](250) NULL,
[Value] [numeric](5, 1) NULL
)
";
var db = new Database("TestScriptTableType");
db.Connection = ConfigHelper.TestDB.Replace("database=TESTDB", "database=" + db.Name);
db.ExecCreate(true);
DBHelper.ExecSql(db.Connection, setupSQL1);
db.Dir = db.Name;
db.Load();
db.ScriptToDir();
Assert.AreEqual(1, db.TableTypes.Count());
Assert.AreEqual(250, db.TableTypes[0].Columns.Items[0].Length);
Assert.AreEqual(1, db.TableTypes[0].Columns.Items[1].Scale);
Assert.AreEqual(5, db.TableTypes[0].Columns.Items[1].Precision);
Assert.AreEqual("MyTableType", db.TableTypes[0].Name);
Assert.IsTrue(File.Exists(db.Name + "\\table_types\\TYPE_MyTableType.sql"));
}
