versaplexd/t/putschema.t.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 #include "wvtest.cs.h"
   7 // Test the Schemamatic functions that deal with Put-ing schema elements
   8
   9 using System;
  10 using System.IO;
  11 using System.Linq;
  12 using System.Text;
  13 using System.Collections.Generic;
  14 using System.Security.Cryptography;
  15 using Wv;
  16 using Wv.Extensions;
  17 using Wv.Test;
  18
  19 [TestFixture]
  20 class PutSchemaTests : SchemamaticTester
  21 {
  22     VxDbusSchema dbus;
  23     WvLog log;
  24
  25     public PutSchemaTests()
  26     {
  27         dbus = new VxDbusSchema(bus);
  28         log = new WvLog("Schemamatic Tests");
  29     }
  30
  31     public void CheckTable(VxSchema schema, string tabname, string schemastr)
  32     {
  33         string key = "Table/" + tabname;
  34         WVPASSEQ(schema[key].type, "Table");
  35         WVPASSEQ(schema[key].name, tabname);
  36         WVPASSEQ(schema[key].text, schemastr);
  37         WVPASSEQ(schema[key].encrypted, false);
  38     }
  39
  40     public void TestTableUpdate(string tabname, string tabschema)
  41     {
  42         TestTableUpdate(tabname, tabschema, VxPutOpts.None, null);
  43     }
  44
  45     public void TestTableUpdate(string tabname, string tabschema,
  46         VxPutOpts opts)
  47     {
  48         TestTableUpdate(tabname, tabschema, opts, null);
  49     }
  50
  51     public void TestTableUpdate(string tabname, string tabschema,
  52         VxPutOpts opts, string expected_warning)
  53     {
  54         VxSchema schema = new VxSchema();
  55         schema.Add("Table", tabname, tabschema, false);
  56
  57         VxSchemaErrors errs = dbus.Put(schema, null, opts);
  58
  59         log.print("Received errors: {0}\n", errs.ToString());
  60         int errcount = 0;
  61         int warncount = 0;
  62         foreach (var errlist in errs)
  63             foreach (var err in errlist.Value)
  64             {
  65                 log.print("Got error, err.level={0}\n", err.level);
  66                 if (err.level == WvLog.L.Error)
  67                     errcount++;
  68                 else if (err.level == WvLog.L.Warning)
  69                     warncount++;
  70             }
  71         WVPASSEQ(errcount, 0);
  72         WVPASSEQ(warncount, expected_warning.ne() ? 1 : 0);
  73         WVPASSEQ(errs.Count, errcount + warncount);
  74
  75         if (expected_warning.ne())
  76         {
  77             string key = "Table/" + tabname;
  78             WVPASSEQ(errs[key][0].key, key);
  79             WVPASSEQ(errs[key][0].msg, expected_warning);
  80             WVPASSEQ(errs[key][0].errnum, -1);
  81             WVPASSEQ((int)errs[key][0].level, (int)WvLog.L.Warning);
  82             WVPASSEQ(errs[key].Count, 1);
  83         }
  84
  85         schema = dbus.Get("Table/" + tabname);
  86         WVPASSEQ(schema.Count, 1);
  87         CheckTable(schema, tabname, tabschema);
  88     }
  89
  90     public void TestTableUpdateError(string tabname, string tabschema,
  91         string errmsg, string oldval)
  92     {
  93         TestTableUpdateError(tabname, tabschema, errmsg, oldval,
  94             VxPutOpts.None, -1, WvLog.L.Error);
  95     }
  96
  97     public void TestTableUpdateError(string tabname, string tabschema,
  98         string errmsg, string oldval, VxPutOpts opts)
  99     {
 100         TestTableUpdateError(tabname, tabschema, errmsg, oldval, opts, -1,
 101             WvLog.L.Error);
 102     }
 103
 104     public void TestTableUpdateError(string tabname, string tabschema,
 105         string errmsg, string oldval, VxPutOpts opts, int errno)
 106     {
 107         TestTableUpdateError(tabname, tabschema, errmsg, oldval, opts, errno,
 108             WvLog.L.Error);
 109     }
 110
 111     public void TestTableUpdateError(string tabname, string tabschema,
 112         string errmsg, string oldval, VxPutOpts opts, int errno, WvLog.L level)
 113     {
 114         string key = "Table/" + tabname;
 115         VxSchema schema = new VxSchema();
 116         schema.Add("Table", tabname, tabschema, false);
 117
 118         VxSchemaErrors errs = dbus.Put(schema, null, opts);
 119         log.print("Received errors: {0}\n", errs.ToString());
 120
 121         WVPASSEQ(errs.Count, 1);
 122         WVPASSEQ(errs[key][0].key, key);
 123         log.print("Expected error prefix: [{0}]\n", errmsg);
 124         WVPASS(errs[key][0].msg.StartsWith(errmsg));
 125         WVPASSEQ(errs[key][0].errnum, errno);
 126         WVPASSEQ((int)errs[key][0].level, (int)level);
 127         WVPASSEQ(errs[key].Count, 1);
 128
 129         // Ensure that we didn't break what was already there.
 130         schema = dbus.Get(key);
 131         if (oldval.ne())
 132         {
 133             WVPASSEQ(schema.Count, 1);
 134             CheckTable(schema, tabname, oldval);
 135         }
 136         else
 137             WVPASSEQ(schema.Count, 0);
 138     }
 139
 140     [Test, Category("Schemamatic"), Category("PutSchema")]
 141     public void TestChangingIndexes()
 142     {
 143         try { VxExec("drop table TestTable"); } catch { }
 144
 145         WVPASS(1);
 146         string testschema = "column: name=f1,type=int,null=0\n" +
 147                 "column: name=f2,type=money,null=0\n" +
 148                 "column: name=f3,type=varchar,null=1,length=80\n" +
 149                 "index: column=f2,column=f3 DESC,name=Idx1,unique=1,clustered=2\n" +
 150                 "primary-key: column=f1,column=f2,clustered=1\n";
 151         TestTableUpdate("TestTable", testschema);
 152
 153         // Change the index: line slightly.
 154         WVPASS(2);
 155         string testschema2 = "column: name=f1,type=int,null=0\n" +
 156                 "column: name=f2,type=money,null=0\n" +
 157                 "column: name=f3,type=varchar,null=1,length=80\n" +
 158                 "index: column=f1,column=f3 DESC,name=Idx1,unique=1,clustered=2\n" +
 159                 "primary-key: column=f1,column=f2,clustered=1\n";
 160         TestTableUpdate("TestTable", testschema2);
 161
 162         // No index at all
 163         WVPASS(3);
 164         string testschema3 = "column: name=f1,type=int,null=0\n" +
 165                 "column: name=f2,type=money,null=0\n" +
 166                 "column: name=f3,type=varchar,null=1,length=80\n" +
 167                 "primary-key: column=f1,column=f2,clustered=1\n";
 168         TestTableUpdate("TestTable", testschema3);
 169
 170         // Add the index back, and another for good measure
 171         WVPASS(4);
 172         string testschema4 = "column: name=f1,type=int,null=0\n" +
 173                 "column: name=f2,type=money,null=0\n" +
 174                 "column: name=f3,type=varchar,null=1,length=80\n" +
 175                 "index: column=f1,column=f3 DESC,name=Idx1,unique=1,clustered=2\n" +
 176                 "index: column=f2,column=f3,name=Idx2\n" +
 177                 "primary-key: column=f1,column=f2,clustered=1\n";
 178         VxSchema schema = new VxSchema();
 179         schema.Add("Table", "TestTable", testschema4, false);
 180
 181         VxSchemaErrors errs = dbus.Put(schema, null, VxPutOpts.None);
 182         log.print("Received errors: {0}\n", errs.ToString());
 183         WVPASSEQ(errs.Count, 0);
 184
 185         // Check that we get the default unique=0 and clustered=2 parameters.
 186         testschema4 = testschema4.Replace("Idx2", "Idx2,unique=0,clustered=2");
 187
 188         schema = dbus.Get("Table/TestTable");
 189         WVPASSEQ(schema.Count, 1);
 190         CheckTable(schema, "TestTable", testschema4);
 191
 192         // Check that duplicate index names give errors.
 193         // Note: VxSchemaTable now checks for this, which makes it hard to
 194         // actually test that the server rejects these.  It's pretty safe to
 195         // assume that the server would have just as much trouble creating one
 196         // as we would though, and that the exception would make its way back.
 197         WVPASS(5);
 198         string testschema5 = "column: name=f1,type=int,null=0\n" +
 199                 "column: name=f2,type=money,null=0\n" +
 200                 "column: name=f3,type=varchar,null=1,length=80\n" +
 201                 "index: column=f1,column=f3 DESC,name=Idx1,unique=1,clustered=2\n" +
 202                 "index: column=f2,column=f3,name=Idx1,unique=1,clustered=2\n" +
 203                 "primary-key: column=f1,column=f2,clustered=1\n";
 204         string errmsg = "Duplicate table entry 'index: Idx1' found.";
 205         schema = new VxSchema();
 206         try
 207         {
 208             WVEXCEPT(schema.Add("Table", "TestTable", testschema5, false));
 209         }
 210         catch (VxBadSchemaException e)
 211         {
 212             WVPASSEQ(e.Message, errmsg);
 213             log.print(e.ToString() + "\n");
 214         }
 215
 216         // Try renaming an index.
 217         // Note that indexes are returned alphabetically, and the default name
 218         // for a primary key is PK_TableName, so the renamed index will show
 219         // up after the primary key.
 220         WVPASS(6);
 221         string testschema6 = "column: name=f1,type=int,null=0\n" +
 222                 "column: name=f2,type=money,null=0\n" +
 223                 "column: name=f3,type=varchar,null=1,length=80\n" +
 224                 "primary-key: column=f1,column=f2,clustered=1\n" +
 225                 "index: column=f1,column=f3 DESC,name=RenamedIndex," +
 226                     "unique=1,clustered=2\n";
 227         TestTableUpdate("TestTable", testschema6);
 228
 229         try { VxExec("drop table TestTable"); } catch { }
 230     }
 231
 232     [Test, Category("Schemamatic"), Category("PutSchema")]
 233     public void TestChangingPrimaryKeys()
 234     {
 235         try { VxExec("drop table TestTable"); } catch { }
 236
 237         WVPASS(1);
 238         string testschema = "column: name=f1,type=int,null=0\n" +
 239                 "column: name=f2,type=money,null=0\n" +
 240                 "column: name=f3,type=varchar,null=1,length=80\n" +
 241                 "index: column=f2,column=f3 DESC,name=Idx1,unique=1,clustered=2\n" +
 242                 "primary-key: column=f1,column=f2,clustered=1\n";
 243         TestTableUpdate("TestTable", testschema);
 244
 245         // Change the primary-key: line: try specifying a name, changing the
 246         // columns, and omitting the optional "clustered" parameter.
 247         WVPASS(2);
 248         string testschema2 = "column: name=f1,type=int,null=0\n" +
 249                 "column: name=f2,type=money,null=0\n" +
 250                 "column: name=f3,type=varchar,null=1,length=80\n" +
 251                 "index: column=f1,column=f3 DESC,name=Idx1,unique=1,clustered=2\n" +
 252                 "primary-key: name=TestPK,column=f1\n";
 253         VxSchema schema = new VxSchema();
 254         schema.Add("Table", "TestTable", testschema2, false);
 255         VxSchemaErrors errs = dbus.Put(schema, null, VxPutOpts.None);
 256
 257         // We'll get the default clustered value added back
 258         testschema2 = testschema2.Replace("primary-key: name=TestPK,column=f1",
 259             "primary-key: name=TestPK,column=f1,clustered=2");
 260
 261         log.print("Received errors: {0}\n", errs.ToString());
 262         WVPASSEQ(errs.Count, 0);
 263
 264         schema = dbus.Get("Table/TestTable");
 265         WVPASSEQ(schema.Count, 1);
 266         CheckTable(schema, "TestTable", testschema2);
 267
 268         // Remove the primary-key: line.
 269         WVPASS(3);
 270         string testschema3 = "column: name=f1,type=int,null=0\n" +
 271                 "column: name=f2,type=money,null=0\n" +
 272                 "column: name=f3,type=varchar,null=1,length=80\n" +
 273                 "index: column=f1,column=f3 DESC,name=Idx1,unique=1,clustered=2\n";
 274         TestTableUpdate("TestTable", testschema3);
 275
 276         // Try to add two primary keys
 277         // Note: VxSchemaTable now checks for this, which makes it hard to
 278         // actually test that the server rejects these.  It's pretty safe to
 279         // assume that the server would have just as much trouble creating one
 280         // as we would though, and that the exception would make its way back.
 281         WVPASS(4);
 282         string testschema4 = "column: name=f1,type=int,null=0\n" +
 283                 "column: name=f2,type=money,null=0\n" +
 284                 "column: name=f3,type=varchar,null=1,length=80\n" +
 285                 "index: column=f1,column=f3 DESC,name=Idx1,unique=1,clustered=2\n" +
 286                 "primary-key: column=f1,clustered=1\n" +
 287                 "primary-key: column=f2,clustered=1\n";
 288         string errmsg = "Duplicate table entry 'primary-key' found.";
 289         schema = new VxSchema();
 290         try
 291         {
 292             WVEXCEPT(schema.Add("Table", "TestTable", testschema4, false));
 293         }
 294         catch (VxBadSchemaException e)
 295         {
 296             WVPASSEQ(e.Message, errmsg);
 297             log.print(e.ToString() + "\n");
 298         }
 299
 300         try { VxExec("drop table TestTable"); } catch { }
 301     }
 302
 303     [Test, Category("Schemamatic"), Category("PutSchema")]
 304     public void TestChangingEmptyColumns()
 305     {
 306         try { VxExec("drop table TestTable"); } catch { }
 307
 308         WVPASS(1);
 309         string schema1 = "column: name=f1,type=int,null=0\n" +
 310                 "column: name=f2,type=money,null=0\n" +
 311                 "column: name=f3,type=varchar,null=1,length=80\n" +
 312                 "index: column=f2,column=f3 DESC,name=Idx1,unique=1,clustered=2\n" +
 313                 "primary-key: column=f1,column=f2,clustered=1\n";
 314         TestTableUpdate("TestTable", schema1);
 315
 316         // Add a new column
 317         WVPASS(2);
 318         string schema2 = "column: name=f1,type=int,null=0\n" +
 319                 "column: name=f2,type=money,null=0\n" +
 320                 "column: name=f3,type=varchar,null=1,length=80\n" +
 321                 "column: name=f4,type=tinyint,null=0\n" +
 322                 "index: column=f2,column=f3 DESC,name=Idx1,unique=1,clustered=2\n" +
 323                 "primary-key: column=f1,column=f2,clustered=1\n";
 324         TestTableUpdate("TestTable", schema2);
 325
 326         // Check that adding an identity attribute doesn't work without
 327         // specifying the destructive option.
 328         WVPASS(3);
 329         string schema3 = "column: name=f1,type=int,null=0\n" +
 330                 "column: name=f2,type=money,null=0\n" +
 331                 "column: name=f3,type=varchar,null=1,length=80\n" +
 332                 "column: name=f4,type=tinyint,null=0,identity_seed=4,identity_incr=5\n" +
 333                 "index: column=f2,column=f3 DESC,name=Idx1,unique=1,clustered=2\n" +
 334                 "primary-key: column=f1,column=f2,clustered=1\n";
 335         string errmsg = "Refusing to drop and re-add column [f4] " +
 336             "when the destructive option is not set.  Error when altering " +
 337             "was: 'Incorrect syntax near the keyword 'IDENTITY'.'";
 338         TestTableUpdateError("TestTable", schema3, errmsg, schema2);
 339
 340         // Just try lightly changing a column, change the nullity on f4
 341         WVPASS(5);
 342         string schema5 = "column: name=f1,type=int,null=0\n" +
 343                 "column: name=f2,type=money,null=0\n" +
 344                 "column: name=f3,type=varchar,null=1,length=80\n" +
 345                 "column: name=f4,type=tinyint,null=1\n" +
 346                 "index: column=f2,column=f3 DESC,name=Idx1,unique=1,clustered=2\n" +
 347                 "primary-key: column=f1,column=f2,clustered=1\n";
 348         TestTableUpdate("TestTable", schema5, VxPutOpts.Destructive);
 349
 350         // Try changing the column type and some attributes of f4
 351         WVPASS(6);
 352         string schema6 = "column: name=f1,type=int,null=0\n" +
 353                 "column: name=f2,type=money,null=0\n" +
 354                 "column: name=f3,type=varchar,null=1,length=80\n" +
 355                 "column: name=f4,type=bigint,null=0\n" +
 356                 "index: column=f2,column=f3 DESC,name=Idx1,unique=1,clustered=2\n" +
 357                 "primary-key: column=f1,column=f2,clustered=1\n";
 358         TestTableUpdate("TestTable", schema6);
 359
 360         // Try renaming f4 without specifying Destructive.
 361         WVPASS(7);
 362         string schema7 = "column: name=f1,type=int,null=0\n" +
 363                 "column: name=f2,type=money,null=0\n" +
 364                 "column: name=f3,type=varchar,null=1,length=80\n" +
 365                 "column: name=f4renamed,type=bigint,null=0\n" +
 366                 "index: column=f2,column=f3 DESC,name=Idx1,unique=1,clustered=2\n" +
 367                 "primary-key: column=f1,column=f2,clustered=1\n";
 368         errmsg = "Refusing to drop columns ([f4]) " +
 369             "when the destructive option is not set.";
 370         TestTableUpdateError("TestTable", schema7, errmsg, schema6);
 371
 372         // Try to drop f4 without specifying Destructive.
 373         WVPASS(8);
 374         string schema8 = "column: name=f1,type=int,null=0\n" +
 375                 "column: name=f2,type=money,null=0\n" +
 376                 "column: name=f3,type=varchar,null=1,length=80\n" +
 377                 "index: column=f2,column=f3 DESC,name=Idx1,unique=1,clustered=2\n" +
 378                 "primary-key: column=f1,column=f2,clustered=1\n";
 379         errmsg = "Refusing to drop columns ([f4]) " +
 380             "when the destructive option is not set.";
 381         TestTableUpdateError("TestTable", schema8, errmsg, schema6);
 382
 383         // Actually rename f4.
 384         WVPASS(9);
 385         string schema9 = "column: name=f1,type=int,null=0\n" +
 386                 "column: name=f2,type=money,null=0\n" +
 387                 "column: name=f3,type=varchar,null=1,length=80\n" +
 388                 "column: name=f4renamed,type=bigint,null=0\n" +
 389                 "index: column=f2,column=f3 DESC,name=Idx1,unique=1,clustered=2\n" +
 390                 "primary-key: column=f1,column=f2,clustered=1\n";
 391         TestTableUpdate("TestTable", schema9, VxPutOpts.Destructive);
 392
 393         // Actually drop f4 (f4renamed at this point).
 394         WVPASS(10);
 395         string schema10 = "column: name=f1,type=int,null=0\n" +
 396                 "column: name=f2,type=money,null=0\n" +
 397                 "column: name=f3,type=varchar,null=1,length=80\n" +
 398                 "index: column=f2,column=f3 DESC,name=Idx1,unique=1,clustered=2\n" +
 399                 "primary-key: column=f1,column=f2,clustered=1\n";
 400         TestTableUpdate("TestTable", schema10, VxPutOpts.Destructive);
 401
 402         // Get rid of all the columns, except for one that gets renamed.
 403         WVPASS(11);
 404         string schema11 = "column: name=f1renamed,type=int,null=0\n" +
 405                 "primary-key: column=f1renamed,clustered=1\n";
 406         TestTableUpdate("TestTable", schema11, VxPutOpts.Destructive);
 407
 408         // Try to get rid of all the columns.
 409         WVPASS(12);
 410         string schema12 = "primary-key: column=f1renamed,clustered=1\n";
 411         errmsg = "No columns in schema.";
 412         TestTableUpdateError("TestTable", schema12, errmsg, null,
 413             VxPutOpts.Destructive);
 414
 415         // Try to get rid of all the columns, and rename the remaining index.
 416         WVPASS(13);
 417         string schema13 = "primary-key: name=NewPK,column=f1renamed,clustered=1\n";
 418         errmsg = "No columns in schema.";
 419         TestTableUpdateError("TestTable", schema13, errmsg, null,
 420             VxPutOpts.Destructive);
 421
 422         try { VxExec("drop table TestTable"); } catch { }
 423     }
 424
 425     [Test, Category("Schemamatic"), Category("PutSchema")]
 426     public void TestChangingColumnsWithData()
 427     {
 428         try { VxExec("drop table TestTable"); } catch { }
 429
 430         WVPASS(1);
 431         string schema1 = "column: name=f1,type=int,null=0\n" +
 432                 "column: name=f2,type=money,null=0\n" +
 433                 "column: name=f3,type=varchar,null=1,length=80\n" +
 434                 "index: column=f2,column=f3 DESC,name=Idx1,unique=1,clustered=2\n" +
 435                 "primary-key: column=f1,column=f2,clustered=1\n";
 436         TestTableUpdate("TestTable", schema1);
 437
 438         // Insert a number big enough to overflow a tinyint, and a string long
 439         // enough to overflow a varchar(10).
 440         string test_varchar =
 441             "I am a varchar(80).  See? 12345678901234567890";
 442         WVASSERT(VxExec(wv.fmt("INSERT INTO [TestTable] VALUES (" +
 443             1024*1024*1024 + ",1234.56,'{0}')", test_varchar)));
 444
 445         WVPASSEQ(Scalar("select count(*) from TestTable"), 1);
 446         WVPASSEQ((int)Scalar("select f1 from TestTable"), 1024*1024*1024);
 447         WVPASSEQ(Scalar("select f2 from TestTable"), 1234.56);
 448         WVPASSEQ(Scalar("select f3 from TestTable"), test_varchar);
 449
 450         // First check that you can't change a column out from under a
 451         // primary key constraint, without the Destructive option.
 452         WVPASS(2);
 453         string schema2 = "column: name=f1,type=tinyint,null=0\n" +
 454                 "column: name=f2,type=money,null=0\n" +
 455                 "column: name=f3,type=varchar,null=1,length=80\n" +
 456                 "index: column=f2,column=f3 DESC,name=Idx1,unique=1,clustered=2\n" +
 457                 "primary-key: column=f1,column=f2,clustered=1\n";
 458         string errmsg = "Refusing to drop and re-add column [f1] when " +
 459             "the destructive option is not set.  Error when altering was: " +
 460             "'The object 'PK_TestTable' is dependent on column 'f1'.";
 461         TestTableUpdateError("TestTable", schema2, errmsg, schema1);
 462
 463         WVPASSEQ(Scalar("select count(*) from TestTable"), 1);
 464         WVPASSEQ((int)Scalar("select f1 from TestTable"), 1024*1024*1024);
 465         WVPASSEQ(Scalar("select f2 from TestTable"), 1234.56);
 466         WVPASSEQ(Scalar("select f3 from TestTable"), test_varchar);
 467
 468         // The Destructive operation destroys such puny limitations...
 469         WVPASS(3);
 470         string schema3 = "column: name=f1,type=tinyint,null=0\n" +
 471                 "column: name=f2,type=money,null=0\n" +
 472                 "column: name=f3,type=varchar,null=1,length=80\n" +
 473                 "index: column=f2,column=f3 DESC,name=Idx1,unique=1,clustered=2\n" +
 474                 "primary-key: column=f1,column=f2,clustered=1\n";
 475         TestTableUpdate("TestTable", schema3, VxPutOpts.Destructive);
 476
 477         // ... but also destroys the data.
 478         WVPASSEQ(Scalar("select count(*) from TestTable"), 0);
 479
 480         // Restore the original schema and data
 481         WVPASS(3.5);
 482         TestTableUpdate("TestTable", schema1, VxPutOpts.Destructive);
 483         WVASSERT(VxExec(wv.fmt("INSERT INTO [TestTable] VALUES (" +
 484             1024*1024*1024 + ",1234.56,'{0}')", test_varchar)));
 485
 486         WVPASSEQ(Scalar("select count(*) from TestTable"), 1);
 487         WVPASSEQ((int)Scalar("select f1 from TestTable"), 1024*1024*1024);
 488         WVPASSEQ(Scalar("select f2 from TestTable"), 1234.56);
 489         WVPASSEQ(Scalar("select f3 from TestTable"), test_varchar);
 490
 491         // Now try truncating columns with no indexes in the way.
 492         WVPASS(4);
 493         string schema4 = "column: name=f1,type=tinyint,null=0\n" +
 494                 "column: name=f2,type=money,null=0\n" +
 495                 "column: name=f3,type=varchar,null=1,length=80\n";
 496         errmsg = "Refusing to drop and re-add column [f1] when the " +
 497             "destructive option is not set.  Error when altering was: " +
 498             "'Arithmetic overflow error for data type tinyint, " +
 499             "value = 1073741824.";
 500         TestTableUpdateError("TestTable", schema4, errmsg, schema1);
 501
 502         WVPASS(5);
 503         string schema5 = "column: name=f1,type=int,null=0\n" +
 504                 "column: name=f2,type=money,null=0\n" +
 505                 "column: name=f3,type=varchar,null=1,length=10\n";
 506         errmsg = "Refusing to drop and re-add column [f3] when the " +
 507             "destructive option is not set.  Error when altering was: " +
 508             "'String or binary data would be truncated.";
 509         TestTableUpdateError("TestTable", schema5, errmsg, schema1);
 510
 511         WVPASSEQ(Scalar("select count(*) from TestTable"), 1);
 512         WVPASSEQ((int)Scalar("select f1 from TestTable"), 1024*1024*1024);
 513         WVPASSEQ(Scalar("select f2 from TestTable"), 1234.56);
 514         WVPASSEQ(Scalar("select f3 from TestTable"), test_varchar);
 515
 516         // Oops, that pesky destructive flag wasn't set.  Set it now, and
 517         // change both columns at once.
 518         WVPASS(6);
 519         string schema6 = "column: name=f2,type=money,null=0\n" +
 520                 "column: name=f1,type=tinyint,null=0,default=123\n" +
 521                 "column: name=f3,type=varchar,null=1,length=10\n";
 522         TestTableUpdate("TestTable", schema6, VxPutOpts.Destructive);
 523
 524         WVPASSEQ(Scalar("select count(*) from TestTable"), 1);
 525         WVPASSEQ((int)Scalar("select f1 from TestTable"), 123);
 526         WVPASSEQ(Scalar("select f2 from TestTable"), 1234.56);
 527         WVPASS(Scalar("select f3 from TestTable").IsNull);
 528
 529         // Try to drop the nullity on a column that contains a null value.
 530         // Returns a warning and doesn't actually change the schema.
 531         WVPASS(7);
 532         string schema7 = "column: name=f2,type=money,null=0\n" +
 533                 "column: name=f1,type=tinyint,null=0,default=0\n" +
 534                 "column: name=f3,type=varchar,null=0,length=10\n";
 535         string schema7_returned = "column: name=f2,type=money,null=0\n" +
 536                 "column: name=f1,type=tinyint,null=0,default=0\n" +
 537                 "column: name=f3,type=varchar,null=1,length=10\n";
 538         errmsg = "Column 'f3' was requested to be non-null but has " +
 539             "1 null elements.";
 540         TestTableUpdateError("TestTable", schema7, errmsg, schema7_returned,
 541             VxPutOpts.None, -1, WvLog.L.Warning);
 542
 543         WVPASSEQ(Scalar("select count(*) from TestTable"), 1);
 544         WVPASSEQ((int)Scalar("select f1 from TestTable"), 123);
 545         WVPASSEQ(Scalar("select f2 from TestTable"), 1234.56);
 546         WVPASS(Scalar("select f3 from TestTable").IsNull);
 547
 548         // The destructive options works and doesn't whine, but nukes the data.
 549         WVPASS(7.5);
 550         TestTableUpdate("TestTable", schema7, VxPutOpts.Destructive);
 551         WVPASSEQ(Scalar("select count(*) from TestTable"), 0);
 552
 553         // Restore the data
 554         TestTableUpdate("TestTable", schema6, VxPutOpts.None);
 555         WVASSERT(VxExec("INSERT INTO [TestTable] ([f1],[f2],[f3]) " +
 556             "VALUES (123,1234.56,NULL)"));
 557
 558         WVPASSEQ(Scalar("select count(*) from TestTable"), 1);
 559         WVPASSEQ((int)Scalar("select f1 from TestTable"), 123);
 560         WVPASSEQ(Scalar("select f2 from TestTable"), 1234.56);
 561         WVPASS(Scalar("select f3 from TestTable").IsNull);
 562
 563         // Try to add a new column that doesn't allow null.  Should give a
 564         // warning, but otherwise work.
 565         // Note: the added column shows up before the changed column.
 566         WVPASS(8);
 567         string schema8 = "column: name=f2,type=money,null=0\n" +
 568                 "column: name=f1,type=tinyint,null=0,default=0\n" +
 569                 "column: name=f3,type=varchar,null=1,length=10\n" +
 570                 "column: name=f4,type=int,null=0\n";
 571         string schema8_returned = "column: name=f2,type=money,null=0\n" +
 572                 "column: name=f1,type=tinyint,null=0,default=0\n" +
 573                 "column: name=f3,type=varchar,null=1,length=10\n" +
 574                 "column: name=f4,type=int,null=1\n";
 575         errmsg = "Column 'f4' was requested to be non-null but has " +
 576             "1 null elements.";
 577         TestTableUpdateError("TestTable", schema8, errmsg,
 578             schema8_returned, VxPutOpts.None, -1, WvLog.L.Warning);
 579
 580         WVPASSEQ(Scalar("select count(*) from TestTable"), 1);
 581         WVPASSEQ((int)Scalar("select f1 from TestTable"), 123);
 582         WVPASSEQ(Scalar("select f2 from TestTable"), 1234.56);
 583         WVPASS(Scalar("select f3 from TestTable").IsNull);
 584         WVPASS(Scalar("select f4 from TestTable").IsNull);
 585
 586         // Try to add a new column that doesn't explicitly mention
 587         // nullability.  We'll still get whining about f4 if we try to drop
 588         // the nullity again.
 589         WVPASS(9);
 590         string schema9 = "column: name=f2,type=money,null=0\n" +
 591                 "column: name=f1,type=tinyint,null=0,default=0\n" +
 592                 "column: name=f3,type=varchar,null=1,length=10\n" +
 593                 "column: name=f4,type=int,null=0\n" +
 594                 "column: name=f5,type=int\n";
 595         string schema9_returned = "column: name=f2,type=money,null=0\n" +
 596                 "column: name=f1,type=tinyint,null=0,default=0\n" +
 597                 "column: name=f3,type=varchar,null=1,length=10\n" +
 598                 "column: name=f4,type=int,null=1\n" +
 599                 "column: name=f5,type=int,null=1\n";
 600         errmsg = "Column 'f4' was requested to be non-null but has " +
 601             "1 null elements.";
 602         TestTableUpdateError("TestTable", schema9, errmsg, schema9_returned,
 603             VxPutOpts.None, -1, WvLog.L.Warning);
 604
 605         WVPASSEQ(Scalar("select count(*) from TestTable"), 1);
 606         WVPASSEQ((int)Scalar("select f1 from TestTable"), 123);
 607         WVPASSEQ(Scalar("select f2 from TestTable"), 1234.56);
 608         WVPASS(Scalar("select f3 from TestTable").IsNull);
 609         WVPASS(Scalar("select f4 from TestTable").IsNull);
 610         WVPASS(Scalar("select f5 from TestTable").IsNull);
 611
 612         try { VxExec("drop table TestTable"); } catch { }
 613     }
 614
 615     [Test, Category("Schemamatic"), Category("PutSchema")]
 616     public void TestColumnInsertOrder()
 617     {
 618         string tabname = "TestTable";
 619         try { VxExec("drop table " + tabname); } catch { }
 620
 621         // Make sure that table columns get added in the order we give them,
 622         // not alphabetical or something.
 623         WVPASS(1);
 624         string schema1 = "column: name=c,type=int,null=0\n" +
 625                 "column: name=d,type=int,null=0\n" +
 626                 "column: name=b,type=int,null=0\n";
 627         TestTableUpdate(tabname, schema1);
 628
 629         // Modified columns go to the end if we had to drop and add them.
 630         WVPASS(2);
 631         string schema2_sent = "column: name=c,type=tinyint,null=0,identity_seed=1,identity_incr=1\n" +
 632                 "column: name=d,type=int,null=0\n" +
 633                 "column: name=b,type=int,null=0\n";
 634         string schema2_returned = "column: name=d,type=int,null=0\n" +
 635                 "column: name=b,type=int,null=0\n" +
 636                 "column: name=c,type=tinyint,null=0,identity_seed=1,identity_incr=1\n";
 637
 638         VxSchema schema = new VxSchema();
 639         schema.Add("Table", tabname, schema2_sent, false);
 640
 641         VxSchemaErrors errs = dbus.Put(schema, null, VxPutOpts.Destructive);
 642
 643         log.print("Received errors: {0}\n", errs.ToString());
 644         WVPASSEQ(errs.Count, 0);
 645
 646         schema = dbus.Get("Table/" + tabname);
 647         WVPASSEQ(schema.Count, 1);
 648         CheckTable(schema, tabname, schema2_returned);
 649
 650         // New columns go to the end too, but stay in order.
 651         WVPASS(3);
 652         string schema3_sent = "column: name=e,type=int,null=0\n" +
 653                 "column: name=a,type=int,null=0\n" +
 654                 "column: name=c,type=tinyint,null=0,identity_seed=1,identity_incr=1\n" +
 655                 "column: name=d,type=int,null=0\n" +
 656                 "column: name=b,type=int,null=0\n";
 657         string schema3_returned = "column: name=d,type=int,null=0\n" +
 658                 "column: name=b,type=int,null=0\n" +
 659                 "column: name=c,type=tinyint,null=0,identity_seed=1,identity_incr=1\n" +
 660                 "column: name=e,type=int,null=0\n" +
 661                 "column: name=a,type=int,null=0\n";
 662
 663         schema = new VxSchema();
 664         schema.Add("Table", tabname, schema3_sent, false);
 665
 666         errs = dbus.Put(schema, null, VxPutOpts.Destructive);
 667
 668         log.print("Received errors: {0}\n", errs.ToString());
 669         WVPASSEQ(errs.Count, 0);
 670
 671         schema = dbus.Get("Table/" + tabname);
 672         WVPASSEQ(schema.Count, 1);
 673         CheckTable(schema, tabname, schema3_returned);
 674
 675         // Changing the columns completely inserts the new ones in order
 676         WVPASS(4);
 677         string schema4 = "column: name=cc,type=int,null=0\n" +
 678                 "column: name=bb,type=int,null=0\n" +
 679                 "column: name=dd,type=int,null=0\n";
 680         TestTableUpdate(tabname, schema4, VxPutOpts.Destructive);
 681
 682         try { VxExec("drop table " + tabname); } catch { }
 683     }
 684
 685     [Test, Category("Schemamatic"), Category("PutSchema")]
 686     public void TestDefaultConstraints()
 687     {
 688         string tabname = "TestTable";
 689         try { VxExec("drop table " + tabname); } catch { }
 690
 691         // Check that we can add columns with default values
 692         WVPASS(1);
 693         string schema1 = "column: name=f1,type=int,null=1,default=1\n";
 694         TestTableUpdate(tabname, schema1);
 695
 696         WVASSERT(VxExec("INSERT INTO [TestTable] VALUES (2)"));
 697
 698         // Check that we can drop default values
 699         WVPASS(2);
 700         string schema2 = "column: name=f1,type=int,null=0\n";
 701         TestTableUpdate(tabname, schema2);
 702
 703         // Check that we can add new default values
 704         WVPASS(3);
 705         string schema3 = "column: name=f1,type=int,null=0,default=3\n";
 706         TestTableUpdate(tabname, schema3);
 707
 708         WVASSERT(VxExec("INSERT INTO [TestTable] VALUES (DEFAULT)"));
 709         object count;
 710         WVASSERT(Scalar("SELECT COUNT(*) FROM [TestTable] WHERE [f1] = 3",
 711             out count));
 712         WVPASSEQ((int)count, 1);
 713
 714         // Check that we can drop columns with default values
 715         WVPASS(4);
 716         string schema4 = "column: name=f2,type=int,null=0,default=4\n";
 717         TestTableUpdate(tabname, schema4, VxPutOpts.Destructive);
 718
 719         // When we added the new column, it gave its default value to both
 720         // existing rows.
 721         WVASSERT(Scalar("SELECT COUNT(*) FROM [TestTable] WHERE [f2] = 4",
 722             out count));
 723         WVPASSEQ((int)count, 2);
 724
 725         try { VxExec("drop table " + tabname); } catch { }
 726     }
 727
 728     [Test, Category("Schemamatic"), Category("PutSchema")]
 729     public void TestAddingNonNullColumns()
 730     {
 731         try { VxExec("drop table TestTable"); } catch { }
 732
 733         string tabname = "TestTable";
 734
 735         WVPASS(1);
 736         string schema1 = "column: name=f1,type=int,null=0\n";
 737         TestTableUpdate(tabname, schema1);
 738
 739         WVASSERT(VxExec(wv.fmt("INSERT INTO [{0}] VALUES (123)", tabname)));
 740
 741         // Check that nullability gets added without the Destructive option.
 742         WVPASS(2);
 743         string schema2 = "column: name=f1,type=int,null=0\n" +
 744                 "column: name=f2,type=int,null=0\n";
 745         string schema2_returned = "column: name=f1,type=int,null=0\n" +
 746                 "column: name=f2,type=int,null=1\n";
 747         string errmsg = "Column 'f2' was requested " +
 748             "to be non-null but has 1 null elements.";
 749         TestTableUpdateError(tabname, schema2, errmsg, schema2_returned,
 750             VxPutOpts.None, -1, WvLog.L.Warning);
 751
 752         WVPASSEQ(Scalar("select count(*) from TestTable"), 1);
 753         WVPASSEQ((int)Scalar("select f1 from TestTable"), 123);
 754         WVPASS(Scalar("select f2 from TestTable").IsNull);
 755
 756         // Check that the Destructive option forces an exact table update.
 757         TestTableUpdate(tabname, schema2, VxPutOpts.Destructive);
 758
 759         try { VxExec("drop table TestTable"); } catch { }
 760     }
 761
 762     [Test, Category("Schemamatic"), Category("PutSchema")]
 763     public void TestTableChecksumSorting()
 764     {
 765         try { VxExec("drop table TestTable"); } catch { }
 766
 767         WVPASS(1);
 768         string schema1 = "column: name=f1,type=int,null=0\n" +
 769                 "column: name=f2,type=money,null=0\n" +
 770                 "column: name=f3,type=varchar,null=1,length=80\n";
 771         TestTableUpdate("TestTable", schema1);
 772
 773         VxSchemaChecksums origsums = dbus.GetChecksums();
 774
 775         WVPASS(2);
 776         string schema2 = "column: name=f1,type=int,null=0\n" +
 777                 "column: name=f3,type=varchar,null=1,length=80\n";
 778         TestTableUpdate("TestTable", schema2, VxPutOpts.Destructive);
 779
 780         WVPASS(3);
 781         string schema3 = "column: name=f1,type=int,null=0\n" +
 782                 "column: name=f3,type=varchar,null=1,length=80\n" +
 783                 "column: name=f2,type=money,null=0\n";
 784         TestTableUpdate("TestTable", schema3, VxPutOpts.Destructive);
 785
 786         VxSchemaChecksums newsums = dbus.GetChecksums();
 787
 788         // Test that the checksums match even if the column order changes -
 789         // avoids spurious checksum mismatches when you try to add a column
 790         // in the middle of the table (and MSSQL makes you put it at the end).
 791         WVPASSEQ(origsums["Table/TestTable"].GetSumString(),
 792             newsums["Table/TestTable"].GetSumString());
 793
 794         try { VxExec("drop table TestTable"); } catch { }
 795     }
 796
 797     public static void Main()
 798     {
 799         WvTest.DoMain();
 800     }
 801 }