void GetXmlSchemaChecksums(VxSchemaChecksums sums)
{
string query = @"
select sch.name owner,
xsc.name sch,
cast(XML_Schema_Namespace(sch.name,xsc.name)
as nvarchar(max)) contents
into #checksum_calc
from sys.xml_schema_collections xsc
join sys.schemas sch on xsc.schema_id = sch.schema_id
where sch.name <> 'sys'
order by sch.name, xsc.name
select sch, convert(varbinary(8), checksum(contents))
from #checksum_calc
drop table #checksum_calc";
foreach (WvSqlRow row in DbiSelect(query))
{
string schemaname = row[0];
ulong checksum = 0;
foreach (byte b in (byte[])row[1])
{
checksum <<= 8;
checksum |= b;
}
string key = String.Format("XMLSchema/{0}", schemaname);
log.print("schemaname={0}, checksum={1}, key={2}\n",
schemaname, checksum, key);
sums.AddSum(key, checksum);
}
}
// Functions used for GetSchemaChecksums
void GetProcChecksums(VxSchemaChecksums sums,
string type, int encrypted)
{
string encrypt_str = encrypted > 0 ? "-Encrypted" : "";
log.print("Indexing: {0}{1}\n", type, encrypt_str);
string query = @"
select convert(varchar(128), object_name(id)) name,
convert(int, colid) colid,
convert(varchar(3900), text) text
into #checksum_calc
from syscomments
where objectproperty(id, 'Is" + type + @"') = 1
and encrypted = @col0
and object_name(id) like '%'
select name, convert(varbinary(8), getchecksum(text))
from #checksum_calc
order by name, colid
drop table #checksum_calc";
foreach (WvSqlRow row in DbiSelect(query, encrypted))
{
string name = row[0];
// Ignore dt_* functions and sys* views
if (name.StartsWith("dt_") || name.StartsWith("sys"))
continue;
ulong checksum = 0;
foreach (byte b in (byte[])row[1])
{
checksum <<= 8;
checksum |= b;
}
// Fix characters not allowed in filenames
name.Replace('/', '!');
name.Replace('\n', '!');
string key = String.Format("{0}{1}/{2}", type, encrypt_str, name);
log.print("name={0}, checksum={1}, key={2}\n", name, checksum, key);
sums.AddSum(key, checksum);
}
}
void GetTableChecksums(VxSchemaChecksums sums)
{
log.print("Indexing: Tables\n");
// The weird "replace" in defval is because different versions of
// mssql (SQL7 vs. SQL2005, at least) add different numbers of parens
// around the default values. Weird, but it messes up the checksums,
// so we just remove all the parens altogether.
string query = @"
select convert(varchar(128), t.name) tabname,
convert(varchar(128), c.name) colname,
convert(varchar(64), typ.name) typename,
convert(int, c.length) len,
convert(int, c.xprec) xprec,
convert(int, c.xscale) xscale,
convert(varchar(128),
replace(replace(def.text, '(', ''), ')', ''))
defval,
convert(int, c.isnullable) nullable,
convert(int, columnproperty(t.id, c.name, 'IsIdentity')) isident,
convert(int, ident_seed(t.name)) ident_seed,
convert(int, ident_incr(t.name)) ident_incr
into #checksum_calc
from sysobjects t
join syscolumns c on t.id = c.id
join systypes typ on c.xtype = typ.xtype
and c.xusertype = typ.xusertype
left join syscomments def on def.id = c.cdefault
where t.xtype = 'U'
and typ.name <> 'sysname'
order by tabname, c.colorder, colname, typ.status
select tabname, convert(varbinary(8), getchecksum(tabname))
from #checksum_calc
drop table #checksum_calc";
foreach (WvSqlRow row in DbiSelect(query))
{
string name = row[0];
ulong checksum = 0;
foreach (byte b in (byte[])row[1])
{
checksum <<= 8;
checksum |= b;
}
// Tasks_#* should be ignored
if (name.StartsWith("Tasks_#"))
continue;
string key = String.Format("Table/{0}", name);
log.print("name={0}, checksum={1}, key={2}\n", name, checksum, key);
sums.AddSum(key, checksum);
}
}
void AddIndexChecksumsToTables(VxSchemaChecksums sums)
{
string query = @"
select
convert(varchar(128), object_name(i.object_id)) tabname,
convert(varchar(128), i.name) idxname,
convert(int, i.type) idxtype,
convert(int, i.is_unique) idxunique,
convert(int, i.is_primary_key) idxprimary,
convert(varchar(128), c.name) colname,
convert(int, ic.index_column_id) colid,
convert(int, ic.is_descending_key) coldesc
into #checksum_calc
from sys.indexes i
join sys.index_columns ic
on ic.object_id = i.object_id
and ic.index_id = i.index_id
join sys.columns c
on c.object_id = i.object_id
and c.column_id = ic.column_id
where object_name(i.object_id) not like 'sys%'
and object_name(i.object_id) not like 'queue_%'
order by i.name, i.object_id, ic.index_column_id
select
tabname, idxname, colid,
convert(varbinary(8), getchecksum(idxname))
from #checksum_calc
drop table #checksum_calc";
foreach (WvSqlRow row in DbiSelect(query))
{
string tablename = row[0];
string indexname = row[1];
ulong checksum = 0;
foreach (byte b in (byte[])row[3])
{
checksum <<= 8;
checksum |= b;
}
string key = String.Format("Table/{0}", tablename);
log.print("tablename={0}, indexname={1}, checksum={2}, colid={3}\n",
tablename, indexname, checksum, (int)row[2]);
sums.AddSum(key, checksum);
}
}
public void TestSubmoduleExceptions()
{
VxSchema schema1 = new VxSchema();
VxSchemaChecksums sums1 = new VxSchemaChecksums();
VxSchema schema2 = new VxSchema();
VxSchemaChecksums sums2 = new VxSchemaChecksums();
schema1.Add("Procedure", "Func1", "Random contents", false);
sums1.AddSum("Procedure/Func1", 1);
schema2.Add("Procedure", "Func1", "Random contents 2", false);
sums2.AddSum("Procedure/Func1", 2);
string tmpdir = GetTempDir();
try
{
Directory.CreateDirectory(tmpdir);
VxDiskSchema disk = new VxDiskSchema(tmpdir);
disk.Put(schema2, sums2, VxPutOpts.None);
try {
WVEXCEPT(VxDiskSchema.AddFromDir(tmpdir, schema1, sums1))
} catch (System.ArgumentException e) {
WVPASSEQ(e.Message, "Conflicting schema key: Procedure/Func1");
}
}
finally
{
Directory.Delete(tmpdir, true);
}
WVPASS(!Directory.Exists(tmpdir));
}
public void TestSubmoduleSupport()
{
VxSchema schema1 = new VxSchema();
VxSchemaChecksums sums1 = new VxSchemaChecksums();
VxSchema schema2 = new VxSchema();
VxSchemaChecksums sums2 = new VxSchemaChecksums();
schema1.Add("Table", "Tab1", "column: name=random\n", false);
sums1.AddSum("Table/Tab1", 1);
schema2.Add("Table", "Tab2", "column: name=ignored\n", false);
sums2.AddSum("Table/Tab2", 2);
string tmpdir = GetTempDir();
try
{
Directory.CreateDirectory(tmpdir);
VxDiskSchema disk = new VxDiskSchema(tmpdir);
disk.Put(schema2, sums2, VxPutOpts.None);
VxDiskSchema.AddFromDir(tmpdir, schema1, sums1);
WVPASSEQ(sums1["Table/Tab1"].GetSumString(), "0x00000001");
WVPASSEQ(schema1["Table/Tab1"].name, "Tab1");
WVPASSEQ(schema1["Table/Tab1"].type, "Table");
WVPASSEQ(schema1["Table/Tab1"].text, "column: name=random\n");
WVPASSEQ(schema1["Table/Tab1"].encrypted, false);
WVPASSEQ(sums1["Table/Tab2"].GetSumString(), "0x00000002");
WVPASSEQ(schema1["Table/Tab2"].name, "Tab2");
WVPASSEQ(schema1["Table/Tab2"].type, "Table");
WVPASSEQ(schema1["Table/Tab2"].text, "column: name=ignored\n");
WVPASSEQ(schema1["Table/Tab2"].encrypted, false);
}
finally
{
Directory.Delete(tmpdir, true);
}
WVPASS(!Directory.Exists(tmpdir));
}
public void TestDropSchemaFromDisk()
{
string tmpdir = GetTempDir();
try
{
Directory.CreateDirectory(tmpdir);
VxDiskSchema backend = new VxDiskSchema(tmpdir);
VxSchema schema = new VxSchema();
schema.Add("Table", "Foo", "column: name=foo,type=int\n", false);
schema.Add("Table", "Bar", "column: name=bar,type=int\n", false);
schema.Add("Procedure", "Func1", "Func1 contents", false);
schema.Add("ScalarFunction", "Func2", "Func2 contents", false);
VxSchemaChecksums sums = new VxSchemaChecksums();
sums.AddSum("Table/Foo", 1);
sums.AddSum("Table/Bar", 2);
sums.AddSum("Procedure/Func1", 3);
sums.AddSum("ScalarFunction/Func2", 5);
backend.Put(schema, sums, VxPutOpts.None);
WVPASS(File.Exists(Path.Combine(tmpdir, "Table/Foo")));
WVPASS(File.Exists(Path.Combine(tmpdir, "Table/Bar")));
WVPASS(File.Exists(Path.Combine(tmpdir, "Procedure/Func1")));
WVPASS(File.Exists(Path.Combine(tmpdir, "ScalarFunction/Func2")));
VxSchema newschema = backend.Get(null);
VxSchemaChecksums newsums = backend.GetChecksums();
WVPASSEQ(newschema.Count, schema.Count);
WVPASSEQ(newsums.Count, sums.Count);
WVPASS(newschema.ContainsKey("Table/Foo"));
WVPASS(newschema.ContainsKey("Table/Bar"));
WVPASS(newschema.ContainsKey("Procedure/Func1"));
WVPASS(newschema.ContainsKey("ScalarFunction/Func2"));
string[] todrop = { "Table/Foo" };
backend.DropSchema(todrop);
WVPASS(!File.Exists(Path.Combine(tmpdir, "Table/Foo")))
WVPASS(File.Exists(Path.Combine(tmpdir, "Table/Bar")))
WVPASS(File.Exists(Path.Combine(tmpdir, "Procedure/Func1")))
WVPASS(File.Exists(Path.Combine(tmpdir, "ScalarFunction/Func2")))
newschema = backend.Get(null);
newsums = backend.GetChecksums();
WVPASSEQ(newschema.Count, 3);
WVPASSEQ(newsums.Count, 3);
WVPASS(!newschema.ContainsKey("Table/Foo"));
WVPASS(newschema.ContainsKey("Table/Bar"));
WVPASS(newschema.ContainsKey("Procedure/Func1"));
WVPASS(newschema.ContainsKey("ScalarFunction/Func2"));
todrop = new string[] { "Procedure/Func1", "ScalarFunction/Func2" };
backend.DropSchema(todrop);
WVPASS(!File.Exists(Path.Combine(tmpdir, "Table/Foo")))
WVPASS(File.Exists(Path.Combine(tmpdir, "Table/Bar")))
WVPASS(!File.Exists(Path.Combine(tmpdir, "Procedure/Func1")))
WVPASS(!File.Exists(Path.Combine(tmpdir, "ScalarFunction/Func2")))
newschema = backend.Get(null);
newsums = backend.GetChecksums();
WVPASSEQ(newschema.Count, 1);
WVPASSEQ(newsums.Count, 1);
WVPASS(!newschema.ContainsKey("Table/Foo"));
WVPASS(newschema.ContainsKey("Table/Bar"));
WVPASS(!newschema.ContainsKey("Procedure/Func1"));
WVPASS(!newschema.ContainsKey("ScalarFunction/Func2"));
}
finally
{
Directory.Delete(tmpdir, true);
}
WVPASS(!Directory.Exists(tmpdir));
}
public void TestApplySchemaDiff(ISchemaBackend backend)
{
log.print("In TestApplySchemaDiff({0})\n", backend.GetType().ToString());
SchemaCreator sc = new SchemaCreator(this);
sc.Create();
string msg2 = "Hello, world, this used to be Func1!";
string func1q2 = "create procedure Func1 as select '" + msg2 + "'\n";
VxSchema origschema = dbus.Get();
VxSchemaChecksums origsums = dbus.GetChecksums();
VxSchema newschema = new VxSchema(origschema);
VxSchemaChecksums newsums = new VxSchemaChecksums(origsums);
// Don't bother putting the data again if we're talking to dbus:
// we already snuck it in the back door.
if (backend != dbus)
backend.Put(origschema, origsums, VxPutOpts.None);
VxSchemaChecksums diffsums = new VxSchemaChecksums(newsums);
// Make some changes to create an interesting diff.
// Change the text and sums of Func1, schedule TestSchema for
// deletion, and act like Tab2 is new.
newschema["Procedure/Func1"].text = func1q2;
newsums.AddSum("Procedure/Func1", 123);
newsums.Remove("XMLSchema/TestSchema");
origsums.Remove("Table/Tab2");
WVASSERT(VxExec("drop table Tab2"));
VxSchemaDiff diff = new VxSchemaDiff(origsums, newsums);
using (IEnumerator<KeyValuePair<string,VxDiffType>> iter =
diff.GetEnumerator())
{
WVPASS(iter.MoveNext());
WVPASSEQ(iter.Current.Key, "XMLSchema/TestSchema");
WVPASSEQ((char)iter.Current.Value, (char)VxDiffType.Remove);
WVPASS(iter.MoveNext());
WVPASSEQ(iter.Current.Key, "Table/Tab2");
WVPASSEQ((char)iter.Current.Value, (char)VxDiffType.Add);
WVPASS(iter.MoveNext());
WVPASSEQ(iter.Current.Key, "Procedure/Func1");
WVPASSEQ((char)iter.Current.Value, (char)VxDiffType.Change);
WVFAIL(iter.MoveNext());
}
VxSchema diffschema = newschema.GetDiffElements(diff);
WVPASSEQ(diffschema["XMLSchema/TestSchema"].type, "XMLSchema");
WVPASSEQ(diffschema["XMLSchema/TestSchema"].name, "TestSchema");
WVPASSEQ(diffschema["XMLSchema/TestSchema"].text, "");
WVPASSEQ(diffschema["Table/Tab2"].type, "Table");
WVPASSEQ(diffschema["Table/Tab2"].name, "Tab2");
WVPASSEQ(diffschema["Table/Tab2"].text, sc.tab2sch);
WVPASSEQ(diffschema["Procedure/Func1"].type, "Procedure");
WVPASSEQ(diffschema["Procedure/Func1"].name, "Func1");
WVPASSEQ(diffschema["Procedure/Func1"].text, func1q2);
VxSchemaErrors errs = backend.Put(diffschema, diffsums, VxPutOpts.None);
WVPASSEQ(errs.Count, 0);
VxSchema updated = backend.Get(null);
WVASSERT(!updated.ContainsKey("XMLSchema/TestSchema"));
WVPASSEQ(updated["Table/Tab1"].text, newschema["Table/Tab1"].text);
WVPASSEQ(updated["Table/Tab2"].text, newschema["Table/Tab2"].text);
WVPASSEQ(updated["Procedure/Func1"].text,
newschema["Procedure/Func1"].text);
sc.Cleanup();
}
public void TestChecksumDiff()
{
VxSchemaChecksums srcsums = new VxSchemaChecksums();
VxSchemaChecksums goalsums = new VxSchemaChecksums();
VxSchemaChecksums emptysums = new VxSchemaChecksums();
srcsums.AddSum("XMLSchema/secondxml", 2);
srcsums.AddSum("XMLSchema/firstxml", 1);
srcsums.AddSum("Procedure/ConflictProc", 3);
srcsums.AddSum("Table/HarmonyTable", 6);
goalsums.AddSum("Table/NewTable", 3);
goalsums.AddSum("Procedure/NewFunc", 4);
goalsums.AddSum("Procedure/ConflictProc", 5);
goalsums.AddSum("Table/HarmonyTable", 6);
VxSchemaDiff diff = new VxSchemaDiff(srcsums, goalsums);
string expected = "- XMLSchema/firstxml\n" +
"- XMLSchema/secondxml\n" +
"+ Table/NewTable\n" +
"* Procedure/ConflictProc\n" +
"+ Procedure/NewFunc\n";
WVPASSEQ(diff.ToString(), expected);
// Check that the internal order matches the string's order.
WVPASSEQ(diff.ElementAt(0).Key, "XMLSchema/firstxml");
WVPASSEQ(diff.ElementAt(1).Key, "XMLSchema/secondxml");
WVPASSEQ(diff.ElementAt(2).Key, "Table/NewTable");
WVPASSEQ(diff.ElementAt(3).Key, "Procedure/ConflictProc");
WVPASSEQ(diff.ElementAt(4).Key, "Procedure/NewFunc");
// Check that a comparison with an empty set of sums returns the other
// side, sorted.
diff = new VxSchemaDiff(srcsums, emptysums);
expected = "- XMLSchema/firstxml\n" +
"- XMLSchema/secondxml\n" +
"- Table/HarmonyTable\n" +
"- Procedure/ConflictProc\n";
WVPASSEQ(diff.ToString(), expected);
diff = new VxSchemaDiff(emptysums, goalsums);
expected = "+ Table/HarmonyTable\n" +
"+ Table/NewTable\n" +
"+ Procedure/ConflictProc\n" +
"+ Procedure/NewFunc\n";
WVPASSEQ(diff.ToString(), expected);
}
