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); }