versaplexd/vxdbschema.cs

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