public void TestMultiplePrimaryKeys()
{
string sch1 = "column: name=f1,type=int,null=0\n" +
"column: name=f2,type=int,null=0\n";
string pk1 = sch1 + "primary-key: column=f1\n";
string pk2 = pk1 + "primary-key: column=f2\n";
string expected_sql1 = "CREATE TABLE [testtable] (" +
"\n\t[f1] [int] NOT NULL,\n\t[f2] [int] NOT NULL);\n\n\n";
string expected_pksql1 = "CREATE TABLE [testtable] (\n" +
"\t[f1] [int] NOT NULL,\n\t[f2] [int] NOT NULL);\n\n" +
"ALTER TABLE [testtable] ADD CONSTRAINT [PK_testtable] " +
"PRIMARY KEY NONCLUSTERED\n" +
"\t(f1);\n\n\n";
VxSchemaTable table = new VxSchemaTable("testtable");
table.text = sch1;
log.print("Expected sch1: " + expected_sql1 + "\n");
log.print("Actual sch1: " + table.ToSql() + "\n");
WVPASSEQ(table.text, sch1);
WVPASSEQ(table.ToSql(), expected_sql1);
table.text = pk1;
WVPASSEQ(table.text, pk1);
log.print("Expected PK1: " + expected_pksql1 + "\n");
log.print("Actual PK1: " + table.ToSql() + "\n");
WVPASSEQ(table.ToSql(), expected_pksql1);
try {
WVEXCEPT(table.text = pk2);
} catch (VxBadSchemaException e) {
WVPASSEQ(e.Message, "Duplicate table entry 'primary-key' found.");
log.print(e.ToString() + "\n");
}
}
public void TestToSql()
{
string tab1schema = "column: name=f1,type=int,null=0\n" +
"column: name=f2,type=money,null=0\n" +
"column: name=f3,type=varchar,null=0,length=80\n" +
"column: name=f4,type=varchar,null=1,length=max,default='Default Value'\n" +
"column: name=f5,type=decimal,null=1,precision=3,scale=2\n" +
"column: name=f6,type=bigint,null=0,identity_seed=4,identity_incr=5\n" +
"primary-key: column=f1,column=f2,clustered=1\n" +
"index: name=idx1,column=f3,column=f4,unique=1,clustered=1\n" +
"index: name=idx2,column=f5,unique=0\n";
VxSchemaTable table = new VxSchemaTable("testtable", tab1schema);
string sql = table.ToSql();
string expected = "CREATE TABLE [testtable] (\n" +
"\t[f1] [int] NOT NULL,\n" +
"\t[f2] [money] NOT NULL,\n" +
"\t[f3] [varchar] (80) NOT NULL,\n" +
"\t[f4] [varchar] (max) CONSTRAINT testtable_f4_default DEFAULT 'Default Value' NULL,\n" +
"\t[f5] [decimal] (3,2) NULL,\n" +
"\t[f6] [bigint] NOT NULL IDENTITY (4,5));\n\n" +
"ALTER TABLE [testtable] ADD CONSTRAINT [PK_testtable] " +
"PRIMARY KEY CLUSTERED\n" +
"\t(f1, f2);\n\n" +
"CREATE UNIQUE CLUSTERED INDEX [idx1] ON [testtable] \n\t(f3, f4);\n" +
"CREATE INDEX [idx2] ON [testtable] \n\t(f5);\n";
log.print("Returned SQL: " + sql + "\n");
log.print("Expected: " + expected + "\n");
WVPASSEQ(sql, expected);
}
private VxSchemaErrors PutSchemaTable(VxSchemaTable curtable,
VxSchemaTable newtable, VxPutOpts opts)
{
bool destructive = (opts & VxPutOpts.Destructive) != 0;
string tabname = newtable.name;
string key = newtable.key;
var diff = VxSchemaTable.GetDiff(curtable, newtable);
var coladd = new List<VxSchemaTableElement>();
var coldel = new List<VxSchemaTableElement>();
var colchanged = new List<VxSchemaTableElement>();
var otheradd = new List<VxSchemaTableElement>();
var otherdel = new List<VxSchemaTableElement>();
foreach (var kvp in diff)
{
VxSchemaTableElement elem = kvp.Key;
VxDiffType difftype = kvp.Value;
if (elem.elemtype == "primary-key" || elem.elemtype == "index")
{
if (difftype == VxDiffType.Add)
otheradd.Add(elem);
else if (difftype == VxDiffType.Remove)
otherdel.Add(elem);
else if (difftype == VxDiffType.Change)
{
// We don't want to bother trying to change indexes or
// primary keys; it's easier to just delete and re-add
// them.
otherdel.Add(curtable[elem.GetElemKey()]);
otheradd.Add(elem);
}
}
else
{
if (difftype == VxDiffType.Add)
coladd.Add(elem);
else if (difftype == VxDiffType.Remove)
coldel.Add(elem);
else if (difftype == VxDiffType.Change)
colchanged.Add(elem);
}
}
var errs = new VxSchemaErrors();
// Might as well check this sooner rather than later.
if (!destructive && coldel.Count > 0)
{
List<string> colstrs = new List<string>();
foreach (var elem in coldel)
colstrs.Add(elem.GetParam("name"));
// Sorting this is mostly unnecessary, except it makes life a lot
// nicer in the unit tests.
colstrs.Sort();
string errmsg = wv.fmt("Refusing to drop columns ([{0}]) " +
"when the destructive option is not set.",
colstrs.join("], ["));
errs.Add(key, new VxSchemaError(key, errmsg, -1));
goto done;
}
// Perform any needed column changes.
// Note: we call dbi.execute directly, instead of DbiExec, as we're
// running SQL we generated ourselves so we shouldn't blame any
// errors on the client's SQL. We'll catch the DbExceptions and
// turn them into VxSchemaErrors.
var deleted_indexes = new List<VxSchemaTableElement>();
var added_columns = new List<VxSchemaTableElement>();
bool transaction_started = false;
bool transaction_resolved = false;
try
{
// Delete any to-delete indexes first, to get them out of the way.
// Indexes are easy to deal with, they don't cause data loss.
// Note: we can't do this inside the transaction, MSSQL doesn't
// let you change columns that used to be covered by the dropped
// indexes. Instead we'll drop the indexes outside the
// transaction, and restore them by hand if there's an error.
foreach (var elem in otherdel)
{
log.print("Dropping {0}\n", elem.ToString());
string idxname = elem.GetParam("name");
// Use the default primary key name if none was specified.
if (elem.elemtype == "primary-key" && idxname.e())
idxname = curtable.GetDefaultPKName();
var err = DropSchemaElement("Index/" + tabname + "/" + idxname);
if (err != null)
{
errs.Add(key, err);
goto done;
}
deleted_indexes.Add(elem);
}
// If an ALTER TABLE query fails inside a transaction, the
// transaction is automatically rolled back, even if you start
// an inner transaction first. This makes error handling
// annoying. So before we start the real transaction, try to make
// the column changes in a test transaction that we'll always roll
// back to see if they'd fail.
var ErrWhenAltering = new Dictionary<string, VxSchemaError>();
foreach (var elem in colchanged)
{
VxSchemaError err = null;
log.print("Doing a trial run of modifying {0}\n",
elem.GetElemKey());
dbi.execute("BEGIN TRANSACTION coltest");
try
{
// Try to change the column the easy way, without dropping
// or adding anything and without any expected errors.
var change_errs = ApplyChangedColumn(newtable,
curtable[elem.GetElemKey()], elem, null, VxPutOpts.None);
if (change_errs.Count > 0)
err = change_errs[newtable.key][0];
}
catch (SqlException e)
{
// OK, the easy way doesn't work. Remember the error for
// when we do it for real.
log.print("Caught exception in trial run: {0} ({1})\n",
e.Message, e.Number);
err = new VxSchemaError(key, e);
}
log.print("Rolling back, err='{0}'\n",
err == null ? "" : err.ToString());
DbiExecRollback("coltest");
ErrWhenAltering.Add(elem.GetElemKey(), err);
}
log.print("About to begin real transaction\n");
// Add new columns before deleting old ones; MSSQL won't let a
// table have no data columns in it, even temporarily.
// Do this outside the transaction since failures here will
// automatically cause a rollback, even if we handle them.
// It's easy enough for us to roll back by hand if needed.
foreach (var elem in coladd)
{
log.print("Adding {0}\n", elem.ToString());
string add_format = "ALTER TABLE [{0}] ADD {1}\n";
string query = wv.fmt(add_format,
tabname, newtable.ColumnToSql(elem, true));
try
{
dbi.execute(query);
}
catch (SqlException e)
{
// Error 4901: adding a column on a non-empty table failed
// due to neither having a default nor being nullable.
// Don't try anything special in destructive mode, just
// fail and nuke the table.
if (!destructive && e.Number == 4901)
{
log.print("Couldn't add a new non-nullable column " +
"without a default. Making column nullable.\n");
var nullable = GetNullableColumn(elem);
string nullquery = wv.fmt(add_format,
tabname, newtable.ColumnToSql(nullable, true));
log.print("Executing {0}", nullquery);
dbi.execute(nullquery);
}
else
throw;
}
added_columns.Add(elem);
}
transaction_started = true;
dbi.execute("BEGIN TRANSACTION TableUpdate");
foreach (var elem in coldel)
{
log.print("Dropping {0}\n", elem.ToString());
DropTableColumn(newtable, elem);
}
foreach (var elem in colchanged)
{
var expected_err = ErrWhenAltering[elem.GetElemKey()];
var change_errs = ApplyChangedColumn(newtable,
curtable[elem.GetElemKey()], elem, expected_err, opts);
if (change_errs != null && change_errs.Count > 0)
{
errs.Add(change_errs);
goto done;
}
}
// Now that all the columns are finalized, add in any new indices.
foreach (var elem in otheradd)
{
log.print("Adding {0}\n", elem.ToString());
VxSchemaError err = PutSchemaTableIndex(key, curtable, elem);
if (err != null)
{
errs.Add(key, err);
goto done;
}
}
log.print("All changes made, committing transaction.\n");
dbi.execute("COMMIT TRANSACTION TableUpdate");
transaction_resolved = true;
}
catch (SqlException e)
{
var err = new VxSchemaError(key, e);
log.print("Caught exception: {0}\n", err.ToString());
errs.Add(key, err);
}
finally
{
if (transaction_started && !transaction_resolved)
{
log.print("Transaction failed, rolling back.\n");
if (transaction_started)
DbiExecRollback("TableUpdate");
foreach (var elem in added_columns)
{
log.print("Restoring {0}\n", elem.ToString());
try
{
DropTableColumn(newtable, elem);
}
catch (SqlException e)
{
log.print("Caught error clearing column: {0}\n",
e.Message);
}
}
foreach (var elem in deleted_indexes)
{
log.print("Restoring index {0}\n", elem.ToString());
var err = PutSchemaTableIndex(key, curtable, elem);
if (err != null)
errs.Add(key, err);
}
}
}
// Check for null entries in columns that are supposed to be non-null
if (errs.Count == 0)
{
foreach (var elem in newtable)
{
string nullity = elem.GetParam("null");
if (elem.elemtype == "column" && nullity.ne() && nullity != "1")
{
string colname = elem.GetParam("name");
string query = wv.fmt("SELECT count(*) FROM [{0}] " +
"WHERE [{1}] IS NULL",
tabname, colname);
int num_nulls = -1;
try
{
num_nulls = dbi.select_one(query);
}
catch (SqlException e)
{
string errmsg = wv.fmt(
"Couldn't figure out if '{0}' has nulls: {1}",
colname, e.Message);
log.print(errmsg + "\n");
errs.Add(key, new VxSchemaError(
key, errmsg, -1, WvLog.L.Warning));
}
if (num_nulls > 0)
{
string errmsg = wv.fmt("Column '{0}' was requested " +
"to be non-null but has {1} null elements.",
colname, num_nulls);
log.print(errmsg + "\n");
errs.Add(key, new VxSchemaError(
key, errmsg, -1, WvLog.L.Warning));
}
}
}
}
done:
return errs;
}
private VxSchemaErrors ApplyChangedColumn(VxSchemaTable table,
VxSchemaTableElement oldelem, VxSchemaTableElement newelem,
VxSchemaError expected_err, VxPutOpts opts)
{
VxSchemaErrors errs = new VxSchemaErrors();
log.print("Altering {0}\n", newelem.ToString());
bool destructive = (opts & VxPutOpts.Destructive) != 0;
string colname = newelem.GetParam("name");
// Remove any old default constraint; even if it doesn't change, it
// can get in the way of modifying the column. We'll add it again
// later if needed.
if (oldelem.HasDefault())
{
string defquery = wv.fmt("ALTER TABLE [{0}] DROP CONSTRAINT {1}",
table.name, table.GetDefaultDefaultName(colname));
log.print("Executing {0}\n", defquery);
dbi.execute(defquery);
}
bool did_default_constraint = false;
// Don't try to alter the table if we know it won't work.
if (expected_err == null)
{
string query = wv.fmt("ALTER TABLE [{0}] ALTER COLUMN {1}",
table.name, table.ColumnToSql(newelem, false));
log.print("Executing {0}\n", query);
dbi.execute(query);
}
else
{
// Some table attributes can't be changed by ALTER TABLE,
// such as changing identity values, or data type changes that
// would truncate data. If the client has set the Destructive
// flag though, we can try to drop and re-add the column.
if (destructive)
{
log.print("Alter column would fail, dropping and adding.\n");
log.print("Expected error message: {0} ({1})\n",
expected_err.msg, expected_err.errnum);
string delquery = wv.fmt("ALTER TABLE [{0}] " +
"DROP COLUMN [{1}]",
table.name, colname);
// We need to include the default value here (the second
// parameter to ColumnToSql), otherwise adding a column to a
// table with data in it might not work.
string addquery = wv.fmt("ALTER TABLE [{0}] ADD {1}",
table.name, table.ColumnToSql(newelem, true));
log.print("Executing {0}\n", delquery);
dbi.execute(delquery);
log.print("Executing {0}\n", addquery);
dbi.execute(addquery);
did_default_constraint = true;
}
else
{
// Error 515: Can't modify a column because it contains nulls
// and the column requires non-nulls.
if (expected_err.errnum == 515)
{
log.print("Couldn't modify column due to null " +
"restriction. Making column nullable.\n");
var nullable = GetNullableColumn(newelem);
string query = wv.fmt("ALTER TABLE [{0}] ALTER COLUMN {1}",
table.name, table.ColumnToSql(nullable, false));
log.print("Executing {0}\n", query);
dbi.execute(query);
}
else
{
log.print("Can't alter table and destructive flag " +
"not set. Giving up.\n");
string key = table.key;
string errmsg = wv.fmt("Refusing to drop and re-add " +
"column [{0}] when the destructive option " +
"is not set. Error when altering was: '{1}'",
colname, expected_err.msg);
errs.Add(key, new VxSchemaError(key, errmsg, -1));
}
}
}
// No errors so far, let's try to add the new default values if we
// didn't do it already.
// FIXME: Check for actual errors, don't care about warnings.
if (errs.Count == 0 && newelem.HasDefault() && !did_default_constraint)
{
string defquery = wv.fmt("ALTER TABLE [{0}] ADD CONSTRAINT {1} " +
"DEFAULT {2} FOR {3}",
table.name, table.GetDefaultDefaultName(colname),
newelem.GetParam("default"), colname);
log.print("Executing {0}\n", defquery);
dbi.execute(defquery);
}
if (errs.Count != 0)
log.print("Altering column had errors: " + errs.ToString());
return errs;
}
private void DropTableColumn(VxSchemaTable table, VxSchemaTableElement col)
{
string colname = col.GetParam("name");
if (col.HasDefault())
{
string defquery = wv.fmt("ALTER TABLE [{0}] " +
"DROP CONSTRAINT {1}",
table.name, table.GetDefaultDefaultName(colname));
dbi.execute(defquery);
}
string query = wv.fmt("ALTER TABLE [{0}] DROP COLUMN [{1}]",
table.name, colname);
dbi.execute(query);
}
private VxSchemaError PutSchemaTableIndex(string key, VxSchemaTable table,
VxSchemaTableElement elem)
{
string query = "";
if (elem.elemtype == "primary-key")
query = table.PrimaryKeyToSql(elem);
else if (elem.elemtype == "index")
query = table.IndexToSql(elem);
else
return new VxSchemaError(key, wv.fmt(
"Unknown table element '{0}'.", elem.elemtype), -1);
try
{
if (query != "")
dbi.execute(query);
}
catch (SqlException e)
{
return new VxSchemaError(key, e);
}
return null;
}
private VxSchemaErrors PutSchemaTables(List<string> tables,
VxSchema newschema, VxSchemaChecksums newsums, VxPutOpts opts)
{
VxSchema curschema = Get(tables);
VxSchemaErrors errs = new VxSchemaErrors();
foreach (string key in tables)
{
log.print("Putting table {0}\n", key);
string curtype = curschema.ContainsKey(key) ?
curschema[key].type : "Table";
string newtype = newschema.ContainsKey(key) ?
newschema[key].type : "Table";
if (newtype != "Table" || curtype != "Table")
throw new ArgumentException("PutSchemaTables called on " +
"non-table element '" + key + "'.");
// Check for the easy cases, an all-new table or table deletion
if (!curschema.ContainsKey(key))
{
// New table, let PutSchemaElement handle it like before.
VxSchemaError e = PutSchemaElement(newschema[key], opts);
if (e != null)
errs.Add(key, e);
continue;
}
if (!newschema.ContainsKey(key))
{
// Deleted table, let DropSchemaElement deal with it.
VxSchemaError e = DropSchemaElement(key);
if (e != null)
errs.Add(key, e);
continue;
}
// An existing table has been modified.
VxSchemaTable newtable;
VxSchemaTable curtable;
if (newschema[key] is VxSchemaTable)
newtable = (VxSchemaTable)newschema[key];
else
newtable = new VxSchemaTable(newschema[key]);
if (curschema[key] is VxSchemaTable)
curtable = (VxSchemaTable)curschema[key];
else
curtable = new VxSchemaTable(curschema[key]);
VxSchemaErrors put_table_errs = null;
put_table_errs = PutSchemaTable(curtable, newtable, opts);
// If anything goes wrong updating a table in destructive mode,
// drop and re-add it. We want to be sure the schema is updated
// exactly.
bool destructive = (opts & VxPutOpts.Destructive) != 0;
if (destructive && put_table_errs.Count > 0)
{
put_table_errs = null;
log.print("Couldn't cleanly modify table '{0}'. Dropping " +
"and re-adding it.\n", newtable.name);
VxSchemaError e = PutSchemaElement(newschema[key], opts);
if (e != null)
errs.Add(key, e);
}
if (put_table_errs != null && put_table_errs.Count > 0)
errs.Add(put_table_errs);
}
return errs;
}
void RetrieveTableSchema(VxSchema schema, List<string> names)
{
string tablenames = (names.Count > 0
? "and t.name in ('" + names.join("','") + "')"
: "");
string query = @"select t.name tabname,
c.name colname,
typ.name typename,
c.length len,
c.xprec xprec,
c.xscale xscale,
def.text defval,
c.isnullable nullable,
columnproperty(t.id, c.name, 'IsIdentity') isident,
ident_seed(t.name) ident_seed, ident_incr(t.name) ident_incr
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' " +
tablenames + @"
order by tabname, c.colorder, typ.status";
VxSchemaTable table = null;
foreach (WvSqlRow row in DbiSelect(query))
{
string tabname = row[0];
string colname = row[1];
string typename = row[2];
short len = row[3];
byte xprec = row[4];
byte xscale = row[5];
string defval = row[6].IsNull ? (string)null : row[6];
int isnullable = row[7];
int isident = row[8];
string ident_seed = row[9];
string ident_incr = row[10];
if (table != null && tabname != table.name)
{
schema.Add(table.key, table);
table = null;
}
if (isident == 0)
ident_seed = ident_incr = null;
string lenstr = "";
string precstr = null;
string scalestr = null;
if (typename.EndsWith("nvarchar") || typename.EndsWith("nchar"))
{
if (len == -1)
lenstr = "max";
else
{
len /= 2;
lenstr = len.ToString();
}
}
else if (typename.EndsWith("char") || typename.EndsWith("binary"))
{
lenstr = (len == -1 ? "max" : len.ToString());
}
else if (typename.EndsWith("decimal") ||
typename.EndsWith("numeric") || typename.EndsWith("real"))
{
precstr = xprec.ToString();
scalestr = xscale.ToString();
}
if (defval.ne())
{
// MSSQL returns default values wrapped in an irritatingly
// variable number of ()s
defval = StripMatchingParens(defval);
}
if (table == null)
table = new VxSchemaTable(tabname);
table.AddColumn(colname, typename, isnullable, lenstr,
defval, precstr, scalestr, isident, ident_seed, ident_incr);
}
if (table != null)
{
log.print("Adding table {0}\n", table.key);
schema.Add(table.key, table);
}
AddIndexesToTables(schema, names);
}
public void TestTextParsing()
{
string tab1schema = "column: name=f1,type=int,null=0\n" +
"column: name=f2,type=money,null=1\n" +
"column: name=f3,type=varchar,null=0,length=80\n" +
"column: name=f4,type=varchar,null=1,length=max,default='Default Value'\n" +
"column: name=f5,type=decimal,null=1,precision=3,scale=2\n" +
"column: name=f6,type=bigint,null=0,identity_seed=4,identity_incr=5\n" +
"primary-key: column=f1,column=f2,clustered=1\n" +
"index: name=idx1,column=f3,column=f4,unique=1\n";
VxSchemaTable table = new VxSchemaTable("testtable");
table.text = tab1schema;
// Check that it gets parsed as expected.
var iter = table.GetEnumerator();
iter.Reset();
WVPASS(iter.MoveNext());
WVPASSEQ(iter.Current.elemtype, "column");
WVPASSEQ(iter.Current.parameters[0].Key, "name");
WVPASSEQ(iter.Current.parameters[0].Value, "f1");
WVPASSEQ(iter.Current.parameters[1].Key, "type");
WVPASSEQ(iter.Current.parameters[1].Value, "int");
WVPASSEQ(iter.Current.parameters[2].Key, "null");
WVPASSEQ(iter.Current.parameters[2].Value, "0");
WVPASSEQ(iter.Current.parameters.Count, 3);
WVPASS(iter.MoveNext());
WVPASSEQ(iter.Current.elemtype, "column");
WVPASSEQ(iter.Current.parameters[0].Key, "name");
WVPASSEQ(iter.Current.parameters[0].Value, "f2");
WVPASSEQ(iter.Current.parameters[1].Key, "type");
WVPASSEQ(iter.Current.parameters[1].Value, "money");
WVPASSEQ(iter.Current.parameters[2].Key, "null");
WVPASSEQ(iter.Current.parameters[2].Value, "1");
WVPASSEQ(iter.Current.parameters.Count, 3);
WVPASS(iter.MoveNext());
WVPASSEQ(iter.Current.elemtype, "column");
WVPASSEQ(iter.Current.parameters[0].Key, "name");
WVPASSEQ(iter.Current.parameters[0].Value, "f3");
WVPASSEQ(iter.Current.parameters[1].Key, "type");
WVPASSEQ(iter.Current.parameters[1].Value, "varchar");
WVPASSEQ(iter.Current.parameters[2].Key, "null");
WVPASSEQ(iter.Current.parameters[2].Value, "0");
WVPASSEQ(iter.Current.parameters[3].Key, "length");
WVPASSEQ(iter.Current.parameters[3].Value, "80");
WVPASSEQ(iter.Current.parameters.Count, 4);
WVPASS(iter.MoveNext());
WVPASSEQ(iter.Current.elemtype, "column");
WVPASSEQ(iter.Current.parameters[0].Key, "name");
WVPASSEQ(iter.Current.parameters[0].Value, "f4");
WVPASSEQ(iter.Current.parameters[1].Key, "type");
WVPASSEQ(iter.Current.parameters[1].Value, "varchar");
WVPASSEQ(iter.Current.parameters[2].Key, "null");
WVPASSEQ(iter.Current.parameters[2].Value, "1");
WVPASSEQ(iter.Current.parameters[3].Key, "length");
WVPASSEQ(iter.Current.parameters[3].Value, "max");
WVPASSEQ(iter.Current.parameters[4].Key, "default");
WVPASSEQ(iter.Current.parameters[4].Value, "'Default Value'");
WVPASSEQ(iter.Current.parameters.Count, 5);
WVPASS(iter.MoveNext());
WVPASSEQ(iter.Current.elemtype, "column");
WVPASSEQ(iter.Current.parameters[0].Key, "name");
WVPASSEQ(iter.Current.parameters[0].Value, "f5");
WVPASSEQ(iter.Current.parameters[1].Key, "type");
WVPASSEQ(iter.Current.parameters[1].Value, "decimal");
WVPASSEQ(iter.Current.parameters[2].Key, "null");
WVPASSEQ(iter.Current.parameters[2].Value, "1");
WVPASSEQ(iter.Current.parameters[3].Key, "precision");
WVPASSEQ(iter.Current.parameters[3].Value, "3");
WVPASSEQ(iter.Current.parameters[4].Key, "scale");
WVPASSEQ(iter.Current.parameters[4].Value, "2");
WVPASSEQ(iter.Current.parameters.Count, 5);
WVPASS(iter.MoveNext());
WVPASSEQ(iter.Current.elemtype, "column");
WVPASSEQ(iter.Current.parameters[0].Key, "name");
WVPASSEQ(iter.Current.parameters[0].Value, "f6");
WVPASSEQ(iter.Current.parameters[1].Key, "type");
WVPASSEQ(iter.Current.parameters[1].Value, "bigint");
WVPASSEQ(iter.Current.parameters[2].Key, "null");
WVPASSEQ(iter.Current.parameters[2].Value, "0");
WVPASSEQ(iter.Current.parameters[3].Key, "identity_seed");
WVPASSEQ(iter.Current.parameters[3].Value, "4");
WVPASSEQ(iter.Current.parameters[4].Key, "identity_incr");
WVPASSEQ(iter.Current.parameters[4].Value, "5");
WVPASSEQ(iter.Current.parameters.Count, 5);
WVPASS(iter.MoveNext());
WVPASSEQ(iter.Current.elemtype, "primary-key");
WVPASSEQ(iter.Current.parameters[0].Key, "column");
WVPASSEQ(iter.Current.parameters[0].Value, "f1");
WVPASSEQ(iter.Current.parameters[1].Key, "column");
WVPASSEQ(iter.Current.parameters[1].Value, "f2");
WVPASSEQ(iter.Current.parameters[2].Key, "clustered");
WVPASSEQ(iter.Current.parameters[2].Value, "1");
WVPASSEQ(iter.Current.parameters.Count, 3);
WVPASS(iter.MoveNext());
WVPASSEQ(iter.Current.elemtype, "index");
WVPASSEQ(iter.Current.parameters[0].Key, "name");
WVPASSEQ(iter.Current.parameters[0].Value, "idx1");
WVPASSEQ(iter.Current.parameters[1].Key, "column");
WVPASSEQ(iter.Current.parameters[1].Value, "f3");
WVPASSEQ(iter.Current.parameters[2].Key, "column");
WVPASSEQ(iter.Current.parameters[2].Value, "f4");
WVPASSEQ(iter.Current.parameters[3].Key, "unique");
WVPASSEQ(iter.Current.parameters[3].Value, "1");
WVPASSEQ(iter.Current.parameters.Count, 4);
// Check that we get back what we put in.
WVPASSEQ(table.text, tab1schema);
}
public void TestPKNames()
{
string sch1 = "column: name=f1,type=int,null=0\n" +
"primary-key: column=f1\n";
string sch2 = "column: name=f1,type=int,null=0\n" +
"primary-key: name=mypkname,column=f1\n";
string expected_sql1 = "CREATE TABLE [testtable] (\n" +
"\t[f1] [int] NOT NULL);\n\n" +
"ALTER TABLE [testtable] ADD CONSTRAINT [PK_testtable] " +
"PRIMARY KEY NONCLUSTERED\n" +
"\t(f1);\n\n\n";
string expected_sql2 = "CREATE TABLE [testtable] (\n" +
"\t[f1] [int] NOT NULL);\n\n" +
"ALTER TABLE [testtable] ADD CONSTRAINT [mypkname] " +
"PRIMARY KEY NONCLUSTERED\n" +
"\t(f1);\n\n\n";
VxSchemaTable table = new VxSchemaTable("testtable");
table.text = sch1;
log.print("Expected sql1: " + expected_sql1 + "\n");
log.print("Actual sql1: " + table.ToSql() + "\n");
WVPASSEQ(table.text, sch1);
WVPASSEQ(table.ToSql(), expected_sql1);
table.text = sch2;
WVPASSEQ(table.text, sch2);
log.print("Expected sql2: " + expected_sql2 + "\n");
log.print("Actual sql2: " + table.ToSql() + "\n");
WVPASSEQ(table.ToSql(), expected_sql2);
}
