versaplexd/vxdbschema.cs

   1 using System;
   2 using System.Collections;
   3 using System.Collections.Generic;
   4 using System.Data;
   5 using System.Data.Common;
   6 using System.Data.SqlClient;
   7 using System.Linq;
   8 using System.Text;
   9 using System.Text.RegularExpressions;
  10 using Wv;
  11 using Wv.Extensions;
  12
  13 // An ISchemaBackend that uses a direct database connection as a backing
  14 // store.
  15 [WvMoniker]
  16 internal class VxDbSchema : ISchemaBackend
  17 {
  18     static WvLog log = new WvLog("VxDbSchema", WvLog.L.Debug4);
  19
  20     public static void wvmoniker_register()
  21     {
  22         WvMoniker<ISchemaBackend>.register("dbi",
  23                   (string m, object o) => new VxDbSchema(WvDbi.create(m)));
  24     }
  25
  26     static string[] ProcedureTypes = new string[] {
  27 //            "CheckCnst",
  28 //            "Constraint",
  29 //            "Default",
  30 //            "DefaultCnst",
  31 //            "Executed",
  32             "ScalarFunction",
  33             "TableFunction",
  34 //            "InlineFunction",
  35 //            "ExtendedProc",
  36 //            "ForeignKey",
  37 //            "MSShipped",
  38 //            "PrimaryKey",
  39             "Procedure",
  40             "ReplProc",
  41 //            "Rule",
  42 //            "SystemTable",
  43 //            "Table",
  44             "Trigger",
  45 //            "UniqueCnst",
  46             "View",
  47 //            "OwnerId"
  48         };
  49
  50     WvDbi dbi;
  51
  52     public VxDbSchema(WvDbi _dbi)
  53     {
  54         dbi = _dbi;
  55         dbi.execute("set quoted_identifier off");
  56         dbi.execute("set ansi_nulls on");
  57     }
  58
  59     public void Dispose()
  60     {
  61         using (dbi)
  62         {
  63             dbi = null;
  64         }
  65     }
  66
  67     //
  68     // The ISchema interface
  69     //
  70
  71     public VxSchemaErrors Put(VxSchema schema, VxSchemaChecksums sums,
  72         VxPutOpts opts)
  73     {
  74         log.print("Put\n");
  75         bool no_retry = (opts & VxPutOpts.NoRetry) != 0;
  76         int old_err_count = -1;
  77         IEnumerable<string> keys = schema.Keys;
  78         VxSchemaErrors errs = new VxSchemaErrors();
  79
  80         // Sometimes we'll get schema elements in the wrong order, so retry
  81         // until the number of errors stops decreasing.
  82         while (errs.Count != old_err_count)
  83         {
  84             log.print("Calling Put on {0} entries\n",
  85                 old_err_count == -1 ? schema.Count : errs.Count);
  86             old_err_count = errs.Count;
  87             errs.Clear();
  88
  89             List<string> tables = new List<string>();
  90             List<string> nontables = new List<string>();
  91             foreach (string key in keys)
  92             {
  93                 if (schema[key].type == "Table")
  94                     tables.Add(key);
  95                 else
  96                     nontables.Add(key);
  97             }
  98
  99             errs.Add(PutSchemaTables(tables, schema, sums, opts));
 100             foreach (string key in nontables)
 101             {
 102                 log.print("Calling PutSchema on {0}\n", key);
 103                 VxSchemaError e = PutSchemaElement(schema[key], opts);
 104                 if (e != null)
 105                     errs.Add(key, e);
 106             }
 107             // If we only had one schema element, retrying it isn't going to
 108             // fix anything.  We retry to fix ordering problems.
 109             if (no_retry || errs.Count == 0 || schema.Count == 1)
 110                 break;
 111
 112             log.print("Got {0} errors, old_errs={1}, retrying\n",
 113                 errs.Count, old_err_count);
 114
 115             keys = errs.Keys.ToList();
 116         }
 117         return errs;
 118     }
 119
 120     // Escape the schema element names supplied, to make sure they don't have
 121     // evil characters.
 122     private static string EscapeSchemaElementName(string name)
 123     {
 124         // Replace any nasty non-ASCII characters with an !
 125         string escaped = Regex.Replace(name, "[^\\p{IsBasicLatin}]", "!");
 126
 127         // Escape quote marks
 128         return escaped.Replace("'", "''");
 129     }
 130
 131     public VxSchema Get(IEnumerable<string> keys)
 132     {
 133         log.print("Get\n");
 134         List<string> all_names = new List<string>();
 135         List<string> proc_names = new List<string>();
 136         List<string> xml_names = new List<string>();
 137         List<string> tab_names = new List<string>();
 138         // FIXME: This variable is unused.  Get rid of it, and perhaps throw
 139         // an error if we see an index show up.
 140         List<string> idx_names = new List<string>();
 141
 142         foreach (string key in keys)
 143         {
 144             string fullname = EscapeSchemaElementName(key);
 145             log.print("CallGetSchema: Read name " + fullname);
 146             all_names.Add(fullname);
 147
 148             string[] parts = fullname.Split(new char[] {'/'}, 2);
 149             if (parts.Length == 2)
 150             {
 151                 string type = parts[0];
 152                 string name = parts[1];
 153                 if (type == "Table")
 154                     tab_names.Add(name);
 155                 else if (type == "Index")
 156                     idx_names.Add(name);
 157                 else if (type == "XMLSchema")
 158                     xml_names.Add(name);
 159                 else
 160                     proc_names.Add(name);
 161             }
 162             else
 163             {
 164                 // No type given, just try them all
 165                 proc_names.Add(fullname);
 166                 xml_names.Add(fullname);
 167                 tab_names.Add(fullname);
 168                 idx_names.Add(fullname);
 169             }
 170         }
 171
 172         VxSchema schema = new VxSchema();
 173
 174         if (proc_names.Count > 0 || all_names.Count == 0)
 175         {
 176             foreach (string type in ProcedureTypes)
 177             {
 178                 RetrieveProcSchemas(schema, proc_names, type, 0);
 179                 RetrieveProcSchemas(schema, proc_names, type, 1);
 180             }
 181         }
 182
 183         if (xml_names.Count > 0 || all_names.Count == 0)
 184             RetrieveXmlSchemas(schema, xml_names);
 185
 186         if (tab_names.Count > 0 || all_names.Count == 0)
 187             RetrieveTableSchema(schema, tab_names);
 188
 189         return schema;
 190     }
 191
 192     public VxSchemaChecksums GetChecksums()
 193     {
 194         log.print("GetChecksums\n");
 195         VxSchemaChecksums sums = new VxSchemaChecksums();
 196
 197         foreach (string type in ProcedureTypes)
 198         {
 199             try
 200             {
 201                 if (type == "Procedure")
 202                 {
 203                     // Set up self test
 204                     DbiExec("create procedure schemamatic_checksum_test " +
 205                         "as print 'hello' ");
 206                 }
 207
 208                 GetProcChecksums(sums, type, 0);
 209
 210                 if (type == "Procedure")
 211                 {
 212                     // Self-test the checksum feature.  If mssql's checksum
 213                     // algorithm changes, we don't want to pretend our checksum
 214                     // list makes any sense!
 215                     string test_csum = "Procedure/schemamatic_checksum_test";
 216                     ulong got_csum = 0;
 217                     if (sums.ContainsKey(test_csum))
 218                         got_csum = sums[test_csum].checksums.First();
 219                     ulong want_csum = 0x173d6ee8;
 220                     if (want_csum != got_csum)
 221                     {
 222                         throw new Exception(String.Format(
 223                             "checksum_test_mismatch! {0} != {1}",
 224                             got_csum, want_csum));
 225                     }
 226                     sums.Remove(test_csum);
 227                 }
 228             }
 229             finally
 230             {
 231                 if (type == "Procedure")
 232                 {
 233                     DbiExec("drop procedure schemamatic_checksum_test");
 234                 }
 235             }
 236
 237             GetProcChecksums(sums, type, 1);
 238         }
 239
 240         // Do tables separately
 241         GetTableChecksums(sums);
 242
 243         // Do indexes separately
 244         AddIndexChecksumsToTables(sums);
 245
 246         // Do XML schema collections separately (FIXME: only if SQL2005)
 247         GetXmlSchemaChecksums(sums);
 248
 249         return sums;
 250     }
 251
 252     // Deletes the named objects in the database.
 253     public VxSchemaErrors DropSchema(IEnumerable<string> keys)
 254     {
 255         return DropSchema(keys.ToArray());
 256     }
 257
 258     // Deletes the named objects in the database.
 259     public VxSchemaErrors DropSchema(params string[] keys)
 260     {
 261         VxSchemaErrors errs = new VxSchemaErrors();
 262         foreach (string key in keys)
 263         {
 264             VxSchemaError e = DropSchemaElement(key);
 265             if (e != null)
 266                 errs.Add(key, e);
 267         }
 268
 269         return errs;
 270     }
 271
 272     //
 273     // Non-ISchemaBackend methods
 274     //
 275
 276     public VxSchemaError DropSchemaElement(string key)
 277     {
 278         log.print("DropSchemaElement({0})\n", key);
 279         if (key == null)
 280             return null;
 281
 282         string type, name;
 283         VxSchema.ParseKey(key, out type, out name);
 284         if (type == null || name == null)
 285             return new VxSchemaError(key, "Malformed key: " + key, -1);
 286
 287         string query = GetDropCommand(type, name);
 288
 289         try {
 290             DbiExec(query);
 291         } catch (VxSqlException e) {
 292             log.print("Got error dropping {0}: {1} ({2})\n", key,
 293                 e.Message, e.Number);
 294             return new VxSchemaError(key, e);
 295         }
 296
 297         return null;
 298     }
 299
 300     // Translate SqlExceptions from dbi.execute into VxSqlExceptions
 301     private int DbiExec(string query, params string[] args)
 302     {
 303         try
 304         {
 305             return dbi.execute(query, args);
 306         }
 307         catch (DbException e)
 308         {
 309             throw new VxSqlException(e.Message, e);
 310         }
 311     }
 312
 313     // Roll back the given transaction, but eat SQL error 3903, where the
 314     // rollback fails because it's already been rolled back.
 315     // Returns true if the transaction was actually rolled back.
 316     // Returns false (or throws some other exception) if the rollback failed.
 317     private bool DbiExecRollback(string trans_name)
 318     {
 319         try
 320         {
 321             dbi.execute("ROLLBACK TRANSACTION " + trans_name);
 322             return true;
 323         }
 324         catch (SqlException e)
 325         {
 326             log.print("Caught rollback exception: {0} ({1})\n",
 327                 e.Message, e.Number);
 328             // Eat any "The Rollback Transaction request has no
 329             // corresponding Begin Transaction." errors - some errors
 330             // will automatically roll us back, some won't.
 331             if (e.Number != 3903)
 332                 throw;
 333         }
 334         return false;
 335     }
 336
 337     // Translate SqlExceptions from dbi.select into VxSqlExceptions.
 338     private IEnumerable<WvSqlRow> DbiSelect(string query,
 339         params object[] bound_vars)
 340     {
 341         log.print(WvLog.L.Debug5, "DbiSelect({0}...)\n", query.shorten(60));
 342         try
 343         {
 344             return dbi.select(query, bound_vars).ToArray();
 345         }
 346         catch (DbException e)
 347         {
 348             throw new VxSqlException(e.Message, e);
 349         }
 350     }
 351
 352     private static string GetDropCommand(string type, string name)
 353     {
 354         if (type.EndsWith("Function"))
 355             type = "Function";
 356         else if (type == "XMLSchema")
 357             type = "XML Schema Collection";
 358         else if (type == "Index")
 359         {
 360             string[] parts = name.Split(new char[] {'/'}, 2);
 361             if (parts.Length == 2)
 362             {
 363                 string tabname = parts[0];
 364                 string idxname = parts[1];
 365                 return String.Format(
 366                     @"declare @x int;
 367                       select @x = is_primary_key
 368                           from sys.indexes
 369                           where object_name(object_id) = '{0}'
 370                             and name = '{1}';
 371                       if @x = 1
 372                           ALTER TABLE [{0}] DROP CONSTRAINT [{1}];
 373                       else
 374                           DROP {2} [{0}].[{1}]",
 375                     tabname, idxname, type);
 376             }
 377             else
 378                 throw new ArgumentException(String.Format(
 379                     "Invalid index name '{0}'!", name));
 380         }
 381
 382         return String.Format("DROP {0} [{1}]", type, name);
 383     }
 384
 385     private VxSchemaErrors PutSchemaTables(List<string> tables,
 386         VxSchema newschema, VxSchemaChecksums newsums, VxPutOpts opts)
 387     {
 388         VxSchema curschema = Get(tables);
 389         VxSchemaErrors errs = new VxSchemaErrors();
 390
 391         foreach (string key in tables)
 392         {
 393             log.print("Putting table {0}\n", key);
 394             string curtype = curschema.ContainsKey(key) ?
 395                 curschema[key].type : "Table";
 396             string newtype = newschema.ContainsKey(key) ?
 397                 newschema[key].type : "Table";
 398
 399             if (newtype != "Table" || curtype != "Table")
 400                 throw new ArgumentException("PutSchemaTables called on " +
 401                     "non-table element '" + key + "'.");
 402
 403             // Check for the easy cases, an all-new table or table deletion
 404             if (!curschema.ContainsKey(key))
 405             {
 406                 // New table, let PutSchemaElement handle it like before.
 407                 VxSchemaError e = PutSchemaElement(newschema[key], opts);
 408                 if (e != null)
 409                     errs.Add(key, e);
 410                 continue;
 411             }
 412             if (!newschema.ContainsKey(key))
 413             {
 414                 // Deleted table, let DropSchemaElement deal with it.
 415                 VxSchemaError e = DropSchemaElement(key);
 416                 if (e != null)
 417                     errs.Add(key, e);
 418                 continue;
 419             }
 420
 421             // An existing table has been modified.
 422
 423             VxSchemaTable newtable;
 424             VxSchemaTable curtable;
 425             if (newschema[key] is VxSchemaTable)
 426                 newtable = (VxSchemaTable)newschema[key];
 427             else
 428                 newtable = new VxSchemaTable(newschema[key]);
 429
 430             if (curschema[key] is VxSchemaTable)
 431                 curtable = (VxSchemaTable)curschema[key];
 432             else
 433                 curtable = new VxSchemaTable(curschema[key]);
 434
 435             VxSchemaErrors put_table_errs = null;
 436             put_table_errs = PutSchemaTable(curtable, newtable, opts);
 437
 438             // If anything goes wrong updating a table in destructive mode,
 439             // drop and re-add it.  We want to be sure the schema is updated
 440             // exactly.
 441             bool destructive = (opts & VxPutOpts.Destructive) != 0;
 442             if (destructive && put_table_errs.Count > 0)
 443             {
 444                 put_table_errs = null;
 445
 446                 log.print("Couldn't cleanly modify table '{0}'.  Dropping " +
 447                     "and re-adding it.\n", newtable.name);
 448                 VxSchemaError e = PutSchemaElement(newschema[key], opts);
 449
 450                 if (e != null)
 451                     errs.Add(key, e);
 452             }
 453
 454             if (put_table_errs != null && put_table_errs.Count > 0)
 455                 errs.Add(put_table_errs);
 456         }
 457
 458         return errs;
 459     }
 460
 461     private VxSchemaError PutSchemaTableIndex(string key, VxSchemaTable table,
 462         VxSchemaTableElement elem)
 463     {
 464         string query = "";
 465         if (elem.elemtype == "primary-key")
 466             query = table.PrimaryKeyToSql(elem);
 467         else if (elem.elemtype == "index")
 468             query = table.IndexToSql(elem);
 469         else
 470             return new VxSchemaError(key, wv.fmt(
 471                 "Unknown table element '{0}'.", elem.elemtype), -1);
 472
 473         try
 474         {
 475             if (query != "")
 476                 dbi.execute(query);
 477         }
 478         catch (SqlException e)
 479         {
 480             return new VxSchemaError(key, e);
 481         }
 482
 483         return null;
 484     }
 485
 486     // Create a new table element that allows nulls
 487     private VxSchemaTableElement GetNullableColumn(VxSchemaTableElement elem)
 488     {
 489         var nullable = new VxSchemaTableElement(elem.elemtype);
 490         foreach (var kvp in elem.parameters)
 491             if (kvp.Key == "null")
 492                 nullable.AddParam("null", "1");
 493             else
 494                 nullable.AddParam(kvp.Key, kvp.Value);
 495
 496         return nullable;
 497     }
 498
 499     private void DropTableColumn(VxSchemaTable table, VxSchemaTableElement col)
 500     {
 501         string colname = col.GetParam("name");
 502         if (col.HasDefault())
 503         {
 504             string defquery = wv.fmt("ALTER TABLE [{0}] " +
 505                 "DROP CONSTRAINT {1}",
 506                 table.name, table.GetDefaultDefaultName(colname));
 507
 508             dbi.execute(defquery);
 509         }
 510
 511         string query = wv.fmt("ALTER TABLE [{0}] DROP COLUMN [{1}]",
 512             table.name, colname);
 513
 514         dbi.execute(query);
 515     }
 516
 517     private VxSchemaErrors ApplyChangedColumn(VxSchemaTable table,
 518         VxSchemaTableElement oldelem, VxSchemaTableElement newelem,
 519         VxSchemaError expected_err, VxPutOpts opts)
 520     {
 521         VxSchemaErrors errs = new VxSchemaErrors();
 522         log.print("Altering {0}\n", newelem.ToString());
 523
 524         bool destructive = (opts & VxPutOpts.Destructive) != 0;
 525         string colname = newelem.GetParam("name");
 526
 527         // Remove any old default constraint; even if it doesn't change, it
 528         // can get in the way of modifying the column.  We'll add it again
 529         // later if needed.
 530         if (oldelem.HasDefault())
 531         {
 532             string defquery = wv.fmt("ALTER TABLE [{0}] DROP CONSTRAINT {1}",
 533                 table.name, table.GetDefaultDefaultName(colname));
 534
 535             log.print("Executing {0}\n", defquery);
 536
 537             dbi.execute(defquery);
 538         }
 539
 540         bool did_default_constraint = false;
 541
 542         // Don't try to alter the table if we know it won't work.
 543         if (expected_err == null)
 544         {
 545             string query = wv.fmt("ALTER TABLE [{0}] ALTER COLUMN {1}",
 546                 table.name, table.ColumnToSql(newelem, false));
 547
 548             log.print("Executing {0}\n", query);
 549
 550             dbi.execute(query);
 551         }
 552         else
 553         {
 554             // Some table attributes can't be changed by ALTER TABLE,
 555             // such as changing identity values, or data type changes that
 556             // would truncate data.  If the client has set the Destructive
 557             // flag though, we can try to drop and re-add the column.
 558             if (destructive)
 559             {
 560                 log.print("Alter column would fail, dropping and adding.\n");
 561                 log.print("Expected error message: {0} ({1})\n",
 562                     expected_err.msg, expected_err.errnum);
 563                 string delquery = wv.fmt("ALTER TABLE [{0}] " +
 564                     "DROP COLUMN [{1}]",
 565                     table.name, colname);
 566                 // We need to include the default value here (the second
 567                 // parameter to ColumnToSql), otherwise adding a column to a
 568                 // table with data in it might not work.
 569                 string addquery = wv.fmt("ALTER TABLE [{0}] ADD {1}",
 570                     table.name, table.ColumnToSql(newelem, true));
 571
 572                 log.print("Executing {0}\n", delquery);
 573                 dbi.execute(delquery);
 574                 log.print("Executing {0}\n", addquery);
 575                 dbi.execute(addquery);
 576                 did_default_constraint = true;
 577             }
 578             else
 579             {
 580                 // Error 515: Can't modify a column because it contains nulls
 581                 // and the column requires non-nulls.
 582                 if (expected_err.errnum == 515)
 583                 {
 584                     log.print("Couldn't modify column due to null " +
 585                         "restriction.  Making column nullable.\n");
 586                     var nullable = GetNullableColumn(newelem);
 587
 588                     string query = wv.fmt("ALTER TABLE [{0}] ALTER COLUMN {1}",
 589                         table.name, table.ColumnToSql(nullable, false));
 590
 591                     log.print("Executing {0}\n", query);
 592
 593                     dbi.execute(query);
 594                 }
 595                 else
 596                 {
 597                     log.print("Can't alter table and destructive flag " +
 598                         "not set.  Giving up.\n");
 599                     string key = table.key;
 600                     string errmsg = wv.fmt("Refusing to drop and re-add " +
 601                             "column [{0}] when the destructive option " +
 602                             "is not set.  Error when altering was: '{1}'",
 603                             colname, expected_err.msg);
 604                     errs.Add(key, new VxSchemaError(key, errmsg, -1));
 605                 }
 606             }
 607         }
 608
 609         // No errors so far, let's try to add the new default values if we
 610         // didn't do it already.
 611         // FIXME: Check for actual errors, don't care about warnings.
 612         if (errs.Count == 0 && newelem.HasDefault() && !did_default_constraint)
 613         {
 614             string defquery = wv.fmt("ALTER TABLE [{0}] ADD CONSTRAINT {1} " +
 615                 "DEFAULT {2} FOR {3}",
 616                 table.name, table.GetDefaultDefaultName(colname),
 617                 newelem.GetParam("default"), colname);
 618
 619             log.print("Executing {0}\n", defquery);
 620
 621             dbi.execute(defquery);
 622         }
 623
 624         if (errs.Count != 0)
 625             log.print("Altering column had errors: " + errs.ToString());
 626
 627         return errs;
 628     }
 629
 630     private VxSchemaErrors PutSchemaTable(VxSchemaTable curtable,
 631         VxSchemaTable newtable, VxPutOpts opts)
 632     {
 633         bool destructive = (opts & VxPutOpts.Destructive) != 0;
 634
 635         string tabname = newtable.name;
 636         string key = newtable.key;
 637
 638         var diff = VxSchemaTable.GetDiff(curtable, newtable);
 639
 640         var coladd = new List<VxSchemaTableElement>();
 641         var coldel = new List<VxSchemaTableElement>();
 642         var colchanged = new List<VxSchemaTableElement>();
 643         var otheradd = new List<VxSchemaTableElement>();
 644         var otherdel = new List<VxSchemaTableElement>();
 645         foreach (var kvp in diff)
 646         {
 647             VxSchemaTableElement elem = kvp.Key;
 648             VxDiffType difftype = kvp.Value;
 649             if (elem.elemtype == "primary-key" || elem.elemtype == "index")
 650             {
 651                 if (difftype == VxDiffType.Add)
 652                     otheradd.Add(elem);
 653                 else if (difftype == VxDiffType.Remove)
 654                     otherdel.Add(elem);
 655                 else if (difftype == VxDiffType.Change)
 656                 {
 657                     // We don't want to bother trying to change indexes or
 658                     // primary keys; it's easier to just delete and re-add
 659                     // them.
 660                     otherdel.Add(curtable[elem.GetElemKey()]);
 661                     otheradd.Add(elem);
 662                 }
 663             }
 664             else
 665             {
 666                 if (difftype == VxDiffType.Add)
 667                     coladd.Add(elem);
 668                 else if (difftype == VxDiffType.Remove)
 669                     coldel.Add(elem);
 670                 else if (difftype == VxDiffType.Change)
 671                     colchanged.Add(elem);
 672             }
 673         }
 674
 675         var errs = new VxSchemaErrors();
 676
 677         // Might as well check this sooner rather than later.
 678         if (!destructive && coldel.Count > 0)
 679         {
 680             List<string> colstrs = new List<string>();
 681             foreach (var elem in coldel)
 682                 colstrs.Add(elem.GetParam("name"));
 683             // Sorting this is mostly unnecessary, except it makes life a lot
 684             // nicer in the unit tests.
 685             colstrs.Sort();
 686
 687             string errmsg = wv.fmt("Refusing to drop columns ([{0}]) " +
 688                     "when the destructive option is not set.",
 689                     colstrs.Join("], ["));
 690             errs.Add(key, new VxSchemaError(key, errmsg, -1));
 691             goto done;
 692         }
 693
 694         // Perform any needed column changes.
 695         // Note: we call dbi.execute directly, instead of DbiExec, as we're
 696         // running SQL we generated ourselves so we shouldn't blame any
 697         // errors on the client's SQL.  We'll catch the DbExceptions and
 698         // turn them into VxSchemaErrors.
 699
 700         var deleted_indexes = new List<VxSchemaTableElement>();
 701         var added_columns = new List<VxSchemaTableElement>();
 702
 703         bool transaction_started = false;
 704         bool transaction_resolved = false;
 705         try
 706         {
 707             // Delete any to-delete indexes first, to get them out of the way.
 708             // Indexes are easy to deal with, they don't cause data loss.
 709             // Note: we can't do this inside the transaction, MSSQL doesn't
 710             // let you change columns that used to be covered by the dropped
 711             // indexes.  Instead we'll drop the indexes outside the
 712             // transaction, and restore them by hand if there's an error.
 713             foreach (var elem in otherdel)
 714             {
 715                 log.print("Dropping {0}\n", elem.ToString());
 716                 string idxname = elem.GetParam("name");
 717
 718                 // Use the default primary key name if none was specified.
 719                 if (elem.elemtype == "primary-key" && idxname.e())
 720                     idxname = curtable.GetDefaultPKName();
 721
 722                 var err = DropSchemaElement("Index/" + tabname + "/" + idxname);
 723                 if (err != null)
 724                 {
 725                     errs.Add(key, err);
 726                     goto done;
 727                 }
 728
 729                 deleted_indexes.Add(elem);
 730             }
 731
 732             // If an ALTER TABLE query fails inside a transaction, the
 733             // transaction is automatically rolled back, even if you start
 734             // an inner transaction first.  This makes error handling
 735             // annoying.  So before we start the real transaction, try to make
 736             // the column changes in a test transaction that we'll always roll
 737             // back to see if they'd fail.
 738             var ErrWhenAltering = new Dictionary<string, VxSchemaError>();
 739             foreach (var elem in colchanged)
 740             {
 741                 VxSchemaError err = null;
 742                 log.print("Doing a trial run of modifying {0}\n",
 743                     elem.GetElemKey());
 744                 dbi.execute("BEGIN TRANSACTION coltest");
 745                 try
 746                 {
 747                     // Try to change the column the easy way, without dropping
 748                     // or adding anything and without any expected errors.
 749                     var change_errs = ApplyChangedColumn(newtable,
 750                         curtable[elem.GetElemKey()], elem, null, VxPutOpts.None);
 751                     if (change_errs.Count > 0)
 752                         err = change_errs[newtable.key][0];
 753                 }
 754                 catch (SqlException e)
 755                 {
 756                     // OK, the easy way doesn't work.  Remember the error for
 757                     // when we do it for real.
 758                     log.print("Caught exception in trial run: {0} ({1})\n",
 759                         e.Message, e.Number);
 760                     err = new VxSchemaError(key, e);
 761                 }
 762
 763                 log.print("Rolling back, err='{0}'\n",
 764                     err == null ? "" : err.ToString());
 765
 766                 DbiExecRollback("coltest");
 767
 768                 ErrWhenAltering.Add(elem.GetElemKey(), err);
 769             }
 770             log.print("About to begin real transaction\n");
 771
 772             // Add new columns before deleting old ones; MSSQL won't let a
 773             // table have no data columns in it, even temporarily.
 774             // Do this outside the transaction since failures here will
 775             // automatically cause a rollback, even if we handle them.
 776             // It's easy enough for us to roll back by hand if needed.
 777             foreach (var elem in coladd)
 778             {
 779                 log.print("Adding {0}\n", elem.ToString());
 780                 string add_format = "ALTER TABLE [{0}] ADD {1}\n";
 781                 string query = wv.fmt(add_format,
 782                     tabname, newtable.ColumnToSql(elem, true));
 783
 784                 try
 785                 {
 786                     dbi.execute(query);
 787                 }
 788                 catch (SqlException e)
 789                 {
 790                     // Error 4901: adding a column on a non-empty table failed
 791                     // due to neither having a default nor being nullable.
 792                     // Don't try anything special in destructive mode, just
 793                     // fail and nuke the table.
 794                     if (!destructive && e.Number == 4901)
 795                     {
 796                         log.print("Couldn't add a new non-nullable column " +
 797                             "without a default.  Making column nullable.\n");
 798                         var nullable = GetNullableColumn(elem);
 799
 800                         string nullquery = wv.fmt(add_format,
 801                             tabname, newtable.ColumnToSql(nullable, true));
 802
 803                         log.print("Executing {0}", nullquery);
 804                         dbi.execute(nullquery);
 805                     }
 806                     else
 807                         throw;
 808                 }
 809                 added_columns.Add(elem);
 810             }
 811
 812             transaction_started = true;
 813             dbi.execute("BEGIN TRANSACTION TableUpdate");
 814
 815             foreach (var elem in coldel)
 816             {
 817                 log.print("Dropping {0}\n", elem.ToString());
 818                 DropTableColumn(newtable, elem);
 819             }
 820
 821             foreach (var elem in colchanged)
 822             {
 823                 var expected_err = ErrWhenAltering[elem.GetElemKey()];
 824                 var change_errs = ApplyChangedColumn(newtable,
 825                     curtable[elem.GetElemKey()], elem, expected_err, opts);
 826
 827                 if (change_errs != null && change_errs.Count > 0)
 828                 {
 829                     errs.Add(change_errs);
 830                     goto done;
 831                 }
 832             }
 833
 834             // Now that all the columns are finalized, add in any new indices.
 835             foreach (var elem in otheradd)
 836             {
 837                 log.print("Adding {0}\n", elem.ToString());
 838                 VxSchemaError err = PutSchemaTableIndex(key, curtable, elem);
 839                 if (err != null)
 840                 {
 841                     errs.Add(key, err);
 842                     goto done;
 843                 }
 844             }
 845
 846             log.print("All changes made, committing transaction.\n");
 847
 848             dbi.execute("COMMIT TRANSACTION TableUpdate");
 849             transaction_resolved = true;
 850         }
 851         catch (SqlException e)
 852         {
 853             var err = new VxSchemaError(key, e);
 854             log.print("Caught exception: {0}\n", err.ToString());
 855             errs.Add(key, err);
 856         }
 857         finally
 858         {
 859             if (transaction_started && !transaction_resolved)
 860             {
 861                 log.print("Transaction failed, rolling back.\n");
 862                 if (transaction_started)
 863                     DbiExecRollback("TableUpdate");
 864
 865                 foreach (var elem in added_columns)
 866                 {
 867                     log.print("Restoring {0}\n", elem.ToString());
 868                     try
 869                     {
 870                         DropTableColumn(newtable, elem);
 871                     }
 872                     catch (SqlException e)
 873                     {
 874                         log.print("Caught error clearing column: {0}\n",
 875                             e.Message);
 876                     }
 877                 }
 878
 879                 foreach (var elem in deleted_indexes)
 880                 {
 881                     log.print("Restoring index {0}\n", elem.ToString());
 882                     var err = PutSchemaTableIndex(key, curtable, elem);
 883                     if (err != null)
 884                         errs.Add(key, err);
 885                 }
 886             }
 887         }
 888
 889         // Check for null entries in columns that are supposed to be non-null
 890         if (errs.Count == 0)
 891         {
 892             foreach (var elem in newtable)
 893             {
 894                 string nullity = elem.GetParam("null");
 895                 if (elem.elemtype == "column" && nullity.ne() && nullity != "1")
 896                 {
 897                     string colname = elem.GetParam("name");
 898                     string query = wv.fmt("SELECT count(*) FROM [{0}] " +
 899                         "WHERE [{1}] IS NULL",
 900                         tabname, colname);
 901
 902                     int num_nulls = -1;
 903                     try
 904                     {
 905                         num_nulls = dbi.select_one(query);
 906                     }
 907                     catch (SqlException e)
 908                     {
 909                         string errmsg = wv.fmt(
 910                             "Couldn't figure out if '{0}' has nulls: {1}",
 911                             colname, e.Message);
 912                         log.print(errmsg + "\n");
 913                         errs.Add(key, new VxSchemaError(
 914                             key, errmsg, -1, WvLog.L.Warning));
 915                     }
 916
 917                     if (num_nulls > 0)
 918                     {
 919                         string errmsg = wv.fmt("Column '{0}' was requested " +
 920                                 "to be non-null but has {1} null elements.",
 921                                 colname, num_nulls);
 922                         log.print(errmsg + "\n");
 923                         errs.Add(key, new VxSchemaError(
 924                             key, errmsg, -1, WvLog.L.Warning));
 925                     }
 926                 }
 927             }
 928         }
 929
 930     done:
 931         return errs;
 932     }
 933
 934     // Replaces the named object in the database.  elem.text is a verbatim
 935     // hunk of text returned earlier by GetSchema.  'destructive' says whether
 936     // or not to perform potentially destructive operations while making the
 937     // change, e.g. dropping a table so we can re-add it with the right
 938     // columns.
 939     private VxSchemaError PutSchemaElement(VxSchemaElement elem, VxPutOpts opts)
 940     {
 941         try
 942         {
 943             bool destructive = (opts & VxPutOpts.Destructive) != 0;
 944             if (destructive || elem.type != "Table")
 945             {
 946                 try {
 947                     DropSchema(elem.key);
 948                 } catch (VxSqlException e) {
 949                     // Check if it's a "didn't exist" error, rethrow if not.
 950                     // SQL Error 3701 means "can't drop sensible item because
 951                     // it doesn't exist or you don't have permission."
 952                     // SQL Error 15151 means "can't drop XML Schema collection
 953                     // because it doesn't exist or you don't have permission."
 954                     if (!e.ContainsSqlError(3701) && !e.ContainsSqlError(15151))
 955                         throw;
 956                 }
 957             }
 958
 959             if (elem.text.ne())
 960             {
 961                 log.print("Putting element: {0}...\n",
 962                           elem.ToSql().shorten(60));
 963                 DbiExec(elem.ToSql());
 964             }
 965         }
 966         catch (VxRequestException e)
 967         {
 968             log.print("Got error from {0}: {1}\n", elem.key, e.Message);
 969             return new VxSchemaError(elem.key, e);
 970         }
 971
 972         return null;
 973     }
 974
 975     // Functions used for GetSchemaChecksums
 976
 977     void GetProcChecksums(VxSchemaChecksums sums,
 978             string type, int encrypted)
 979     {
 980         string encrypt_str = encrypted > 0 ? "-Encrypted" : "";
 981
 982         log.print("Indexing: {0}{1}\n", type, encrypt_str);
 983
 984         string query = @"
 985             select convert(varchar(128), object_name(id)) name,
 986                      convert(int, colid) colid,
 987                      convert(varchar(3900), text) text
 988                 into #checksum_calc
 989                 from syscomments
 990                 where objectproperty(id, 'Is" + type + @"') = 1
 991                     and encrypted = @col0
 992                     and object_name(id) like '%'
 993             select name, convert(varbinary(8), getchecksum(text))
 994                 from #checksum_calc
 995                 order by name, colid
 996             drop table #checksum_calc";
 997
 998
 999         foreach (WvSqlRow row in DbiSelect(query, encrypted))
1000         {
1001             string name = row[0];
1002
1003             // Ignore dt_* functions and sys* views
1004             if (name.StartsWith("dt_") || name.StartsWith("sys"))
1005                 continue;
1006
1007             ulong checksum = 0;
1008             foreach (byte b in (byte[])row[1])
1009             {
1010                 checksum <<= 8;
1011                 checksum |= b;
1012             }
1013
1014             // Fix characters not allowed in filenames
1015             name.Replace('/', '!');
1016             name.Replace('\n', '!');
1017             string key = String.Format("{0}{1}/{2}", type, encrypt_str, name);
1018
1019             log.print("name={0}, checksum={1}, key={2}\n", name, checksum, key);
1020             sums.AddSum(key, checksum);
1021         }
1022     }
1023
1024     void GetTableChecksums(VxSchemaChecksums sums)
1025     {
1026         log.print("Indexing: Tables\n");
1027
1028         // The weird "replace" in defval is because different versions of
1029         // mssql (SQL7 vs. SQL2005, at least) add different numbers of parens
1030         // around the default values.  Weird, but it messes up the checksums,
1031         // so we just remove all the parens altogether.
1032         string query = @"
1033             select convert(varchar(128), t.name) tabname,
1034                convert(varchar(128), c.name) colname,
1035                convert(varchar(64), typ.name) typename,
1036                convert(int, c.length) len,
1037                convert(int, c.xprec) xprec,
1038                convert(int, c.xscale) xscale,
1039                convert(varchar(128),
1040                    replace(replace(def.text, '(', ''), ')', ''))
1041                    defval,
1042                convert(int, c.isnullable) nullable,
1043                convert(int, columnproperty(t.id, c.name, 'IsIdentity')) isident,
1044                convert(int, ident_seed(t.name)) ident_seed,
1045                convert(int, ident_incr(t.name)) ident_incr
1046               into #checksum_calc
1047               from sysobjects t
1048               join syscolumns c on t.id = c.id
1049               join systypes typ on c.xtype = typ.xtype
1050               left join syscomments def on def.id = c.cdefault
1051               where t.xtype = 'U'
1052                 and typ.name <> 'sysname'
1053               order by tabname, c.colorder, colname, typ.status
1054            select tabname, convert(varbinary(8), getchecksum(tabname))
1055                from #checksum_calc
1056            drop table #checksum_calc";
1057
1058         foreach (WvSqlRow row in DbiSelect(query))
1059         {
1060             string name = row[0];
1061             ulong checksum = 0;
1062             foreach (byte b in (byte[])row[1])
1063             {
1064                 checksum <<= 8;
1065                 checksum |= b;
1066             }
1067
1068             // Tasks_#* should be ignored
1069             if (name.StartsWith("Tasks_#"))
1070                 continue;
1071
1072             string key = String.Format("Table/{0}", name);
1073
1074             log.print("name={0}, checksum={1}, key={2}\n", name, checksum, key);
1075             sums.AddSum(key, checksum);
1076         }
1077     }
1078
1079     void AddIndexChecksumsToTables(VxSchemaChecksums sums)
1080     {
1081         string query = @"
1082             select
1083                convert(varchar(128), object_name(i.object_id)) tabname,
1084                convert(varchar(128), i.name) idxname,
1085                convert(int, i.type) idxtype,
1086                convert(int, i.is_unique) idxunique,
1087                convert(int, i.is_primary_key) idxprimary,
1088                convert(varchar(128), c.name) colname,
1089                convert(int, ic.index_column_id) colid,
1090                convert(int, ic.is_descending_key) coldesc
1091               into #checksum_calc
1092               from sys.indexes i
1093               join sys.index_columns ic
1094                  on ic.object_id = i.object_id
1095                  and ic.index_id = i.index_id
1096               join sys.columns c
1097                  on c.object_id = i.object_id
1098                  and c.column_id = ic.column_id
1099               where object_name(i.object_id) not like 'sys%'
1100                 and object_name(i.object_id) not like 'queue_%'
1101               order by i.name, i.object_id, ic.index_column_id
1102
1103             select
1104                tabname, idxname, colid,
1105                convert(varbinary(8), getchecksum(idxname))
1106               from #checksum_calc
1107             drop table #checksum_calc";
1108
1109         foreach (WvSqlRow row in DbiSelect(query))
1110         {
1111             string tablename = row[0];
1112             string indexname = row[1];
1113             ulong checksum = 0;
1114             foreach (byte b in (byte[])row[3])
1115             {
1116                 checksum <<= 8;
1117                 checksum |= b;
1118             }
1119
1120             string key = String.Format("Table/{0}", tablename);
1121
1122             log.print("tablename={0}, indexname={1}, checksum={2}, colid={3}\n",
1123                 tablename, indexname, checksum, (int)row[2]);
1124             sums.AddSum(key, checksum);
1125         }
1126     }
1127
1128     void GetXmlSchemaChecksums(VxSchemaChecksums sums)
1129     {
1130         string query = @"
1131             select sch.name owner,
1132                xsc.name sch,
1133                cast(XML_Schema_Namespace(sch.name,xsc.name)
1134                     as nvarchar(max)) contents
1135               into #checksum_calc
1136               from sys.xml_schema_collections xsc
1137               join sys.schemas sch on xsc.schema_id = sch.schema_id
1138               where sch.name <> 'sys'
1139               order by sch.name, xsc.name
1140
1141             select sch, convert(varbinary(8), checksum(contents))
1142                 from #checksum_calc
1143             drop table #checksum_calc";
1144
1145         foreach (WvSqlRow row in DbiSelect(query))
1146         {
1147             string schemaname = row[0];
1148             ulong checksum = 0;
1149             foreach (byte b in (byte[])row[1])
1150             {
1151                 checksum <<= 8;
1152                 checksum |= b;
1153             }
1154
1155             string key = String.Format("XMLSchema/{0}", schemaname);
1156
1157             log.print("schemaname={0}, checksum={1}, key={2}\n",
1158                 schemaname, checksum, key);
1159             sums.AddSum(key, checksum);
1160         }
1161     }
1162
1163     // Functions used for GetSchema
1164
1165     static string RetrieveProcSchemasQuery(string type, int encrypted,
1166         bool countonly, List<string> names)
1167     {
1168         string name_q = names.Count > 0
1169             ? " and object_name(id) in ('" + names.Join("','") + "')"
1170             : "";
1171
1172         string textcol = encrypted > 0 ? "ctext" : "text";
1173         string cols = countonly
1174             ? "count(*)"
1175             : "object_name(id), colid, " + textcol + " ";
1176
1177         return "select " + cols + " from syscomments " +
1178             "where objectproperty(id, 'Is" + type + "') = 1 " +
1179                 "and encrypted = " + encrypted + name_q;
1180     }
1181
1182     void RetrieveProcSchemas(VxSchema schema, List<string> names,
1183         string type, int encrypted)
1184     {
1185         string query = RetrieveProcSchemasQuery(type, encrypted, false, names);
1186         log.print(WvLog.L.Debug3, "Query={0}\n", query);
1187
1188         foreach (WvSqlRow row in DbiSelect(query))
1189         {
1190             string name = row[0];
1191             //short colid = row[1];
1192             string text;
1193             // FIXME: Retrieving encrypted data is kind of broken anyway.
1194             if (encrypted > 0)
1195                 text = row[2];//.ToHex();
1196             else
1197                 text = row[2];
1198
1199
1200             // Skip dt_* functions and sys_* views
1201             if (name.StartsWith("dt_") || name.StartsWith("sys_"))
1202                 continue;
1203
1204             // Fix characters not allowed in filenames
1205             name.Replace('/', '!');
1206             name.Replace('\n', '!');
1207
1208             schema.Add(type, name, text, encrypted > 0);
1209         }
1210     }
1211
1212     // Adds the indexes for each table in "names" to the table elements.
1213     void AddIndexesToTables(VxSchema schema, List<string> names)
1214     {
1215         string tabnames = (names.Count > 0) ?
1216             "and (object_name(i.object_id) in ('" +
1217                 names.Join("','") + "'))"
1218             : "";
1219
1220         string query = @"
1221           select
1222            convert(varchar(128), object_name(i.object_id)) tabname,
1223            convert(varchar(128), i.name) idxname,
1224            convert(int, i.type) idxtype,
1225            convert(int, i.is_unique) idxunique,
1226            convert(int, i.is_primary_key) idxprimary,
1227            convert(varchar(128), c.name) colname,
1228            convert(int, ic.index_column_id) colid,
1229            convert(int, ic.is_descending_key) coldesc
1230           from sys.indexes i
1231           join sys.index_columns ic
1232              on ic.object_id = i.object_id
1233              and ic.index_id = i.index_id
1234           join sys.columns c
1235              on c.object_id = i.object_id
1236              and c.column_id = ic.column_id
1237           where object_name(i.object_id) not like 'sys%'
1238             and object_name(i.object_id) not like 'queue_%' " +
1239             tabnames +
1240           @" order by i.name, i.object_id, ic.index_column_id";
1241
1242         log.print("Adding index information for {0}\n",
1243             names.Count > 0 ? names.Join(",") : "all tables");
1244
1245         WvSqlRow[] data = DbiSelect(query).ToArray();
1246
1247         int old_colid = 0;
1248         List<string> cols = new List<string>();
1249         // FIXME: use foreach
1250         for (int ii = 0; ii < data.Length; ii++)
1251         {
1252             WvSqlRow row = data[ii];
1253
1254             string tabname = row[0];
1255             string idxname = row[1];
1256             int idxtype = row[2];
1257             int idxunique = row[3];
1258             int idxprimary = row[4];
1259             string colname = row[5];
1260             int colid = row[6];
1261             int coldesc = row[7];
1262
1263             // Check that we're getting the rows in order.
1264             wv.assert(colid == old_colid + 1 || colid == 1);
1265             old_colid = colid;
1266
1267             cols.Add(coldesc == 0 ? colname : colname + " DESC");
1268
1269             WvSqlRow nextrow = ((ii+1) < data.Length) ? data[ii+1] : null;
1270             string next_tabname = (nextrow != null) ? (string)nextrow[0] : null;
1271             string next_idxname = (nextrow != null) ? (string)nextrow[1] : null;
1272
1273             // If we've finished reading the columns for this index, add the
1274             // index to the schema.  Note: depends on the statement's ORDER BY.
1275             if (tabname != next_tabname || idxname != next_idxname)
1276             {
1277                 VxSchemaTable table;
1278                 string tabkey = "Table/" + tabname;
1279                 if (schema.ContainsKey(tabkey))
1280                 {
1281                     table = (VxSchemaTable)schema[tabkey];
1282                     log.print("Found table, idxtype={0}, cols={1}\n",
1283                         idxtype, cols.Join(","));
1284
1285                     if (idxprimary != 0)
1286                         table.AddPrimaryKey(idxname, idxtype, cols.ToArray());
1287                     else
1288                         table.AddIndex(idxname, idxunique, idxtype,
1289                             cols.ToArray());
1290                 }
1291                 else
1292                     throw new ArgumentException(
1293                         "Schema is missing table '" + tabkey + "'!");
1294
1295                 cols.Clear();
1296             }
1297         }
1298
1299         return;
1300     }
1301
1302     static string XmlSchemasQuery(int count, List<string> names)
1303     {
1304         int start = count * 4000;
1305
1306         string namestr = (names.Count > 0) ?
1307             "and xsc.name in ('" + names.Join("','") + "')"
1308             : "";
1309
1310         string query = @"select sch.name owner,
1311            xsc.name sch,
1312            cast(substring(
1313                  cast(XML_Schema_Namespace(sch.name,xsc.name) as varchar(max)),
1314                  " + start + @", 4000)
1315             as varchar(4000)) contents
1316           from sys.xml_schema_collections xsc
1317           join sys.schemas sch on xsc.schema_id = sch.schema_id
1318           where sch.name <> 'sys'" +
1319             namestr +
1320           @" order by sch.name, xsc.name";
1321
1322         return query;
1323     }
1324
1325     void RetrieveXmlSchemas(VxSchema schema, List<string> names)
1326     {
1327         bool do_again = true;
1328         for (int count = 0; do_again; count++)
1329         {
1330             do_again = false;
1331             string query = XmlSchemasQuery(count, names);
1332
1333             foreach (WvSqlRow row in DbiSelect(query))
1334             {
1335                 string owner = row[0];
1336                 string name = row[1];
1337                 string contents = row[2];
1338
1339                 if (contents.e())
1340                     continue;
1341
1342                 do_again = true;
1343
1344                 if (count == 0)
1345                     schema.Add("XMLSchema", name, String.Format(
1346                         "\nCREATE XML SCHEMA COLLECTION [{0}].[{1}] AS '",
1347                         owner, name), false);
1348
1349                 schema.Add("XMLSchema", name, contents, false);
1350             }
1351         }
1352
1353         // Close the quotes on all the XMLSchemas
1354         foreach (KeyValuePair<string, VxSchemaElement> p in schema)
1355         {
1356             if (p.Value.type == "XMLSchema")
1357                 p.Value.text += "'\n";
1358         }
1359     }
1360
1361     // Removes any matching enclosing parens from around a string.
1362     // E.g. "foo" => "foo", "(foo)" => "foo", "((foo))" => "foo",
1363     // "((2)-(1))" => "(2)-(1)"
1364     public static string StripMatchingParens(string s)
1365     {
1366         WvLog log = new WvLog("StripMatchingParens", WvLog.L.Debug5);
1367         int len = s.Length;
1368
1369         // Count the initial and trailing number of parens
1370         int init_parens = 0;
1371         while (init_parens < len && s[init_parens] == '(')
1372             init_parens++;
1373
1374         int trailing_parens = 0;
1375         while (trailing_parens < len && s[len - trailing_parens - 1] == ')')
1376             trailing_parens++;
1377
1378         // No leading or trailing parens means there can't possibly be any
1379         // matching parens.
1380         if (init_parens == 0 || trailing_parens == 0)
1381             return s;
1382
1383         // Count all the parens in between the leading and trailing ones.
1384         bool is_escaped = false;
1385         int paren_count = init_parens;
1386         int min_parens = init_parens;
1387         for (int i = init_parens; i < s.Length - trailing_parens; i++)
1388         {
1389             if (s[i] == '(' && !is_escaped)
1390                 paren_count++;
1391             else if (s[i] == ')' && !is_escaped)
1392                 paren_count--;
1393             else if (s[i] == '\'')
1394                 is_escaped = !is_escaped;
1395
1396             if (paren_count < min_parens)
1397                 min_parens = paren_count;
1398         }
1399
1400         // The minimum number of outstanding parens found while iterating over
1401         // the string is the number of parens to strip.  Unless there aren't
1402         // enough trailing parens to match the leading ones, of course.
1403         min_parens = Math.Min(min_parens, trailing_parens);
1404         log.print("Trimming {0} parens\n", min_parens);
1405         return s.Substring(min_parens, len - 2*min_parens);
1406     }
1407
1408     void RetrieveTableSchema(VxSchema schema, List<string> names)
1409     {
1410         string tablenames = (names.Count > 0
1411             ? "and t.name in ('" + names.Join("','") + "')"
1412             : "");
1413
1414         string query = @"select t.name tabname,
1415            c.name colname,
1416            typ.name typename,
1417            c.length len,
1418            c.xprec xprec,
1419            c.xscale xscale,
1420            def.text defval,
1421            c.isnullable nullable,
1422            columnproperty(t.id, c.name, 'IsIdentity') isident,
1423            ident_seed(t.name) ident_seed, ident_incr(t.name) ident_incr
1424           from sysobjects t
1425           join syscolumns c on t.id = c.id
1426           join systypes typ on c.xtype = typ.xtype
1427           left join syscomments def on def.id = c.cdefault
1428           where t.xtype = 'U'
1429             and typ.name <> 'sysname' " +
1430             tablenames + @"
1431           order by tabname, c.colorder, typ.status";
1432
1433         VxSchemaTable table = null;
1434         foreach (WvSqlRow row in DbiSelect(query))
1435         {
1436             string tabname = row[0];
1437             string colname = row[1];
1438             string typename = row[2];
1439             short len = row[3];
1440             byte xprec = row[4];
1441             byte xscale = row[5];
1442             string defval = row[6].IsNull ? (string)null : row[6];
1443             int isnullable = row[7];
1444             int isident = row[8];
1445             string ident_seed = row[9];
1446             string ident_incr = row[10];
1447
1448             if (table != null && tabname != table.name)
1449             {
1450                 schema.Add(table.key, table);
1451                 table = null;
1452             }
1453
1454             if (isident == 0)
1455                 ident_seed = ident_incr = null;
1456
1457             string lenstr = "";
1458             string precstr = null;
1459             string scalestr = null;
1460             if (typename.EndsWith("nvarchar") || typename.EndsWith("nchar"))
1461             {
1462                 if (len == -1)
1463                     lenstr = "max";
1464                 else
1465                 {
1466                     len /= 2;
1467                     lenstr = len.ToString();
1468                 }
1469             }
1470             else if (typename.EndsWith("char") || typename.EndsWith("binary"))
1471             {
1472                 lenstr = (len == -1 ? "max" : len.ToString());
1473             }
1474             else if (typename.EndsWith("decimal") ||
1475                 typename.EndsWith("numeric") || typename.EndsWith("real"))
1476             {
1477                 precstr = xprec.ToString();
1478                 scalestr = xscale.ToString();
1479             }
1480
1481             if (defval.ne())
1482             {
1483                 // MSSQL returns default values wrapped in an irritatingly
1484                 // variable number of ()s
1485                 defval = StripMatchingParens(defval);
1486             }
1487
1488             if (table == null)
1489                 table = new VxSchemaTable(tabname);
1490
1491             table.AddColumn(colname, typename, isnullable, lenstr,
1492                 defval, precstr, scalestr, isident, ident_seed, ident_incr);
1493         }
1494
1495         if (table != null)
1496         {
1497             log.print("Adding table {0}\n", table.key);
1498             schema.Add(table.key, table);
1499         }
1500
1501         AddIndexesToTables(schema, names);
1502     }
1503
1504     // Returns a blob of text that can be used with PutSchemaData to fill
1505     // the given table.
1506     public string GetSchemaData(string tablename, int seqnum, string where)
1507     {
1508         log.print("GetSchemaData({0},{1},{2})\n", tablename, seqnum, where);
1509
1510         string ident_query = @"select
1511             columnproperty(t.id, c.name, 'IsIdentity') isident
1512           from sysobjects t
1513           join syscolumns c on t.id = c.id
1514           where t.xtype = 'U'
1515             and t.name = '" + tablename + "'";
1516
1517         bool has_ident = false;
1518         foreach (WvSqlRow row in dbi.select(ident_query))
1519         {
1520             int isident = row[0];
1521             if (isident > 0)
1522             {
1523                 has_ident = true;
1524                 break;
1525             }
1526         }
1527
1528         string query = "SELECT * FROM " + tablename;
1529
1530         if (where != null && where.Length > 0)
1531             query += " WHERE " + where;
1532
1533         bool did_preamble = false;
1534         string prefix = "";
1535         System.Type[] types = null;
1536
1537         StringBuilder result = new StringBuilder();
1538         List<string> values = new List<string>();
1539
1540         foreach (WvSqlRow row in DbiSelect(query))
1541         {
1542             if (!did_preamble)
1543             {
1544                 // Read the column name and type information for the query.
1545                 // We only need to do this once.
1546                 List<string> cols = new List<string>();
1547                 types = new System.Type[row.Length];
1548
1549                 WvColInfo[] columns = row.columns.ToArray();
1550                 for (int ii = 0; ii < columns.Length; ii++)
1551                 {
1552                     WvColInfo col = columns[ii];
1553                     cols.Add("[" + col.name + "]");
1554                     types[ii] = col.type;
1555                 }
1556
1557                 prefix = String.Format("INSERT INTO {0} ({1}) VALUES (",
1558                     tablename, cols.Join(","));
1559
1560                 did_preamble = true;
1561             }
1562
1563             values.Clear();
1564             int colnum = 0;
1565             foreach (WvAutoCast elem in row)
1566             {
1567                 if (elem.IsNull)
1568                     values.Add("NULL");
1569                 else if (types[colnum] == typeof(System.String) ||
1570                     types[colnum] == typeof(System.DateTime))
1571                 {
1572                     string str;
1573                     // The default formatting is locale-dependent, and stupid.
1574                     if (types[colnum] == typeof(System.DateTime))
1575                     {
1576                         str = ((DateTime)elem).ToString(
1577                             "yyyy-MM-dd HH:mm:ss.fff");
1578                     }
1579                     else
1580                         str = (string)elem;
1581
1582                     // Double-quote chars for SQL safety
1583                     string esc = str.Replace("'", "''");
1584                     values.Add("'" + esc + "'");
1585                 }
1586                 else
1587                     values.Add(elem);
1588
1589                 colnum++;
1590             }
1591             result.Append(prefix + values.Join(",") + ");\n");
1592         }
1593
1594         if (has_ident)
1595         {
1596             return "SET IDENTITY_INSERT [" + tablename + "] ON;\n" +
1597                 result.ToString() +
1598                 "SET IDENTITY_INSERT [" + tablename + "] OFF;\n";
1599         }
1600         return result.ToString();
1601     }
1602
1603     // Delete all rows from the given table and replace them with the given
1604     // data.  text is an opaque hunk of text returned from GetSchemaData.
1605     public void PutSchemaData(string tablename, string text, int seqnum)
1606     {
1607         log.print("Calling PutSchemaData on {0}\n", tablename);
1608         // There may be extra static files in the DATA/ directory that
1609         // Schemamatic didn't create and don't have an official table name,
1610         // but that we still want to run.  So if the tablename is empty,
1611         // don't do anything fancy but still run the query.
1612         if (tablename.ne())
1613             DbiExec(String.Format("DELETE FROM [{0}]", tablename));
1614
1615         log.print("text size: {0}\n", text.Length);
1616         if (text.Length > 50000)
1617         {
1618             string[] parts = text.Split("\nINSERT ");
1619             log.print("Split into {0} parts.\n", parts.Length);
1620
1621             log.print("Part 1...\n");
1622             DbiExec(parts[0]);
1623
1624             int count = 1;
1625             var sb = new StringBuilder();
1626             for (int i = 1; i < parts.Length; i++)
1627             {
1628                 sb.Append("\nINSERT ");
1629                 sb.Append(parts[i]);
1630                 if (sb.Length > 50000)
1631                 {
1632                     log.print("Part {0}...\n", ++count);
1633                     DbiExec(sb.ToString());
1634                     sb = new StringBuilder();
1635                 }
1636             }
1637             if (sb.Length > 0)
1638             {
1639                 log.print("Part {0}...\n", ++count);
1640                 DbiExec(sb.ToString());
1641             }
1642         }
1643         else
1644             DbiExec(text);
1645     }
1646 }
1647