versaplexd/t/sqlsucker.test.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
   8 using System;
   9 using System.Data;
  10 using System.Data.SqlClient;
  11 using System.Data.SqlTypes;
  12 using System.IO;
  13 using NUnit.Framework;
  14 using Wv.Test;
  15 using Wv.Utils;
  16
  17 namespace SqlSucker.Test
  18 {
  19
  20 [TestFixture]
  21 public class SqlSuckerTest
  22 {
  23     private const string Server = "amsdev";
  24     private const string User = "asta";
  25     private const string Password = "m!ddle-tear";
  26     private const string Database = "adrian_test";
  27
  28     // A file full of "lorem ipsum dolor" text
  29     private const string lipsum_file = "lipsum.txt";
  30     // A UTF-8 test file
  31     private const string unicode_file = "UTF-8-demo.txt";
  32     // A random file of binary goop
  33     private const string goop_file = "random.bin";
  34     // THTBACS image
  35     private const string image_file = "thtbacs.tiff";
  36
  37     SqlConnection con;
  38     SqlCommand cmd;
  39
  40     bool Connect(SqlConnection connection)
  41     {
  42         connection.Open();
  43
  44         return true;
  45     }
  46
  47     bool Exec(string query)
  48     {
  49         Console.WriteLine(" + Exec SQL Query: {0}", query);
  50
  51         using (SqlCommand execCmd = new SqlCommand(query, con)) {
  52             execCmd.ExecuteNonQuery();
  53         }
  54
  55         return true;
  56     }
  57
  58     bool Scalar(string query, out object result)
  59     {
  60         Console.WriteLine(" + Scalar SQL Query: {0}", query);
  61
  62         using (SqlCommand execCmd = new SqlCommand(query, con)) {
  63             result = execCmd.ExecuteScalar();
  64         }
  65
  66         return true;
  67     }
  68
  69     bool Reader(string query, out SqlDataReader result)
  70     {
  71         Console.WriteLine(" + Reader SQL Query: {0}", query);
  72
  73         using (SqlCommand execCmd = new SqlCommand(query, con)) {
  74             result = execCmd.ExecuteReader();
  75         }
  76
  77         return true;
  78     }
  79
  80     bool SetupOutputTable(string name)
  81     {
  82         Exec(string.Format(
  83                     "CREATE TABLE [{0}] (_ int)",
  84                     name.Replace("]","]]")));
  85         return true;
  86     }
  87
  88     bool RunSucker(string table, string query)
  89     {
  90         return RunSucker(table, query, false);
  91     }
  92
  93     bool RunSucker(string table, string query, bool ordered)
  94     {
  95         Exec(string.Format(
  96                     "EXEC SqlSucker '{0}', '{1}', {2}",
  97                     table.Replace("'", "''"),
  98                     query.Replace("'", "''"),
  99                     ordered));
 100         return true;
 101     }
 102
 103     bool Insert(string table, params object [] param)
 104     {
 105         Console.WriteLine(" + Insert to {0} ({1})", table, String.Join(", ",
 106                     wv.stringify(param)));
 107
 108         System.Text.StringBuilder query = new System.Text.StringBuilder();
 109         query.AppendFormat("INSERT INTO [{0}] VALUES (",
 110                 table.Replace("]","]]"));
 111
 112         using (SqlCommand insCmd = con.CreateCommand()) {
 113             for (int i=0; i < param.Length; i++) {
 114                 if (i > 0)
 115                     query.Append(", ");
 116
 117                 if (param[i] is DBNull) {
 118                     query.Append("NULL");
 119                 } else {
 120                     string paramName = string.Format("@col{0}", i);
 121
 122                     query.Append(paramName);
 123                     insCmd.Parameters.Add(new SqlParameter(paramName,
 124                                 param[i]));
 125                 }
 126             }
 127
 128             query.Append(")");
 129             insCmd.CommandText = query.ToString();
 130
 131             insCmd.ExecuteNonQuery();
 132         }
 133
 134         return true;
 135     }
 136
 137     string read_lipsum()
 138     {
 139         WVASSERT(File.Exists(lipsum_file));
 140
 141         using (StreamReader sr = new StreamReader(lipsum_file)) {
 142             return sr.ReadToEnd();
 143         }
 144     }
 145
 146     string read_unicode()
 147     {
 148         WVASSERT(File.Exists(unicode_file));
 149
 150         using (StreamReader sr = new StreamReader(unicode_file)) {
 151             return sr.ReadToEnd();
 152         }
 153     }
 154
 155     Byte [] read_goop()
 156     {
 157         WVASSERT(File.Exists(goop_file));
 158
 159         using (FileStream f = new FileStream(goop_file, FileMode.Open,
 160                     FileAccess.Read))
 161         using (BinaryReader sr = new BinaryReader(f)) {
 162             return sr.ReadBytes((int)Math.Min(f.Length, Int32.MaxValue));
 163         }
 164     }
 165
 166     Byte [] read_image()
 167     {
 168         WVASSERT(File.Exists(image_file));
 169
 170         using (FileStream f = new FileStream(image_file, FileMode.Open,
 171                     FileAccess.Read))
 172         using (BinaryReader sr = new BinaryReader(f)) {
 173             return sr.ReadBytes((int)Math.Min(f.Length, Int32.MaxValue));
 174         }
 175     }
 176
 177     long GetInt64(SqlDataReader reader, int colnum) {
 178         // For some reason, it won't just up-convert int32 to int64
 179         if (reader.GetFieldType(colnum) == typeof(System.Int32)) {
 180             return reader.GetInt32(colnum);
 181         } else if (reader.GetFieldType(colnum) == typeof(System.Int64)) {
 182             return reader.GetInt64(colnum);
 183         } else if (reader.GetFieldType(colnum) == typeof(System.Decimal)) {
 184             return (long)reader.GetDecimal(colnum);
 185         } else {
 186             // Unknown type
 187             bool unknown_type_in_result = true;
 188             WVFAIL(unknown_type_in_result);
 189
 190             return -1;
 191         }
 192     }
 193
 194     [SetUp]
 195     public void init()
 196     {
 197         con = new SqlConnection(string.Format(
 198                     "Server={0};UID={1};Password={2};Database={3}",
 199                     Server, User, Password, Database));
 200
 201         WVASSERT(Connect(con));
 202
 203         WVASSERT(SetupOutputTable("#suckout"));
 204
 205         cmd = con.CreateCommand();
 206     }
 207
 208     [TearDown]
 209     public void cleanup()
 210     {
 211         if (cmd != null)
 212             cmd.Dispose();
 213         cmd = null;
 214
 215         if (con != null) {
 216             try {
 217                 WVASSERT(Exec("DROP TABLE #suckout"));
 218             } catch { }
 219
 220             con.Dispose();
 221         }
 222         con = null;
 223     }
 224
 225     [Test, Category("Data"), Category("Sanity")]
 226     public void EmptyTable()
 227     {
 228         // Check that an empty table stays empty
 229
 230         WVASSERT(Exec("CREATE TABLE #test1 (testcol int not null)"));
 231
 232         WVASSERT(RunSucker("#suckout", "SELECT * FROM #test1"));
 233
 234         object result;
 235         WVASSERT(Scalar("SELECT COUNT(*) FROM #suckout", out result));
 236         WVPASSEQ((int)result, 0);
 237
 238         SqlDataReader reader;
 239         WVASSERT(Reader("SELECT * FROM #suckout", out reader));
 240         using (reader)
 241         using (DataTable schema = reader.GetSchemaTable()) {
 242             WVPASSEQ(reader.FieldCount, 1);
 243             WVPASSEQ(schema.Rows.Count, 1);
 244
 245             DataRow schemaRow = schema.Rows[0];
 246             WVPASSEQ((string)schemaRow["ColumnName"], "testcol");
 247             WVPASSEQ(schemaRow["DataType"], typeof(System.Int32));
 248
 249             WVFAIL(reader.Read());
 250             WVFAIL(reader.NextResult());
 251         }
 252     }
 253
 254     [Test, Category("Running"), Category("Errors")]
 255     public void NonexistantTable()
 256     {
 257         // Check that a nonexistant output table throws an error
 258
 259         WVASSERT(Exec("CREATE TABLE #test1 (testcol int not null)"));
 260
 261         try {
 262             WVEXCEPT(RunSucker("#nonexistant", "SELECT * FROM #test1"));
 263         } catch (NUnit.Framework.AssertionException e) {
 264             throw e;
 265         } catch (System.Exception e) {
 266             WVPASS(e is SqlException);
 267         }
 268
 269         // The only way to get here is for the test to pass (otherwise an
 270         // exception has been generated somewhere), as WVEXCEPT() always throws
 271         // something.
 272     }
 273
 274     [Test, Category("Running"), Category("Errors")]
 275     public void BadSchemaTable()
 276     {
 277         // Check that an output table with bad schema throws an error
 278
 279         WVASSERT(Exec("CREATE TABLE #test1 (testcol int not null)"));
 280
 281         string[] schemas = {
 282             "_ int, a int",
 283             "a int",
 284             "a int, _ int"
 285         };
 286
 287         foreach (string s in schemas) {
 288             WVASSERT(Exec(string.Format("CREATE TABLE #badschema ({0})", s)));
 289
 290             try {
 291                 WVEXCEPT(RunSucker("#badschema", "SELECT * FROM #test1"));
 292             } catch (NUnit.Framework.AssertionException e) {
 293                 throw e;
 294             } catch (System.Exception e) {
 295                 WVPASS(e is SqlException);
 296             }
 297
 298             WVASSERT(Exec("DROP TABLE #badschema"));
 299         }
 300     }
 301
 302     [Test, Category("Running"), Category("Pedantic")]
 303     public void OutputNotEmpty()
 304     {
 305         // Check that if an output table is non-empty that its contents are
 306         // truncated (i.e. there are no extra rows afterwards)
 307
 308         WVASSERT(Exec("CREATE TABLE #test1 (testcol int not null)"));
 309
 310         WVASSERT(Insert("#suckout", 1));
 311         WVASSERT(Insert("#suckout", 2));
 312
 313         object result;
 314
 315         WVASSERT(Scalar("SELECT COUNT(*) FROM #suckout", out result));
 316         WVPASSEQ((int)result, 2);
 317
 318         WVASSERT(RunSucker("#suckout", "SELECT * FROM #test1"));
 319
 320         WVASSERT(Scalar("SELECT COUNT(*) FROM #suckout", out result));
 321         WVPASSEQ((int)result, 0);
 322     }
 323
 324     [Test, Category("Schema"), Category("Sanity")]
 325     public void ColumnTypes()
 326     {
 327         // Check that column types are copied correctly to the output table
 328
 329         string[] colTypes = {
 330             // Pulled from the SQL Server management gui app's dropdown list in
 331             // the table design screen
 332             "bigint", "binary(50)", "bit", "char(10)", "datetime",
 333             "decimal(18, 0)", "float", "image", "int", "money", "nchar(10)",
 334             "ntext", "numeric(18, 0)", "nvarchar(50)", "nvarchar(MAX)", "real",
 335             "smalldatetime", "smallint", "smallmoney", "text",
 336             "timestamp", "tinyint", "uniqueidentifier", "varbinary(50)",
 337             "varbinary(MAX)", "varchar(50)", "varchar(MAX)", "xml",
 338             // "sql_variant", // this is problematic, so it is unsupported
 339
 340             // Plus a few more to mix up the parameters a bit, and providing
 341             // edge cases
 342             "numeric(1, 0)", "numeric(38, 38)", "numeric(1, 1)",
 343             "numeric(38, 0)", "nvarchar(4000)", "nvarchar(1)",
 344             "varchar(8000)", "varchar(1)", "char(1)", "char(8000)",
 345             "nchar(1)", "nchar(4000)", "decimal(1, 0)", "decimal(38, 38)",
 346             "decimal(1, 1)", "decimal(38, 0)", "binary(1)", "binary(8000)"
 347         };
 348
 349         foreach (String colType in colTypes) {
 350             WVASSERT(Exec(string.Format("CREATE TABLE #test1 (testcol {0})",
 351                             colType)));
 352             // This makes sure it runs the prepare statement
 353             WVASSERT(Insert("#test1", DBNull.Value));
 354
 355             WVASSERT(SetupOutputTable("#test1out"));
 356
 357             WVASSERT(RunSucker("#test1out", "SELECT * FROM #test1"));
 358
 359             SqlDataReader reader;
 360             DataTable[] schemas = new DataTable[2];
 361
 362             WVASSERT(Reader("SELECT * FROM #test1", out reader));
 363             using (reader)
 364                 schemas[0] = reader.GetSchemaTable();
 365
 366             WVASSERT(Reader("SELECT * FROM #test1out", out reader));
 367             using (reader)
 368                 schemas[1] = reader.GetSchemaTable();
 369
 370             WVPASSEQ(schemas[0].Rows.Count, schemas[1].Rows.Count);
 371
 372             for (int colNum = 0; colNum < schemas[0].Rows.Count; colNum++) {
 373                 DataRow[] colInfo = {
 374                     schemas[0].Rows[colNum],
 375                     schemas[1].Rows[colNum]
 376                 };
 377
 378                 WVPASSEQ((IComparable)colInfo[0]["ColumnName"],
 379                         (IComparable)colInfo[1]["ColumnName"]);
 380                 WVPASSEQ((IComparable)colInfo[0]["ColumnOrdinal"],
 381                         (IComparable)colInfo[1]["ColumnOrdinal"]);
 382                 WVPASSEQ((IComparable)colInfo[0]["ColumnSize"],
 383                         (IComparable)colInfo[1]["ColumnSize"]);
 384                 WVPASSEQ((IComparable)colInfo[0]["NumericPrecision"],
 385                         (IComparable)colInfo[1]["NumericPrecision"]);
 386                 WVPASSEQ((IComparable)colInfo[0]["NumericScale"],
 387                         (IComparable)colInfo[1]["NumericScale"]);
 388                 // This one shouldn't be casted to IComparable or it doesn't
 389                 // work
 390                 WVPASSEQ(colInfo[0]["DataType"], colInfo[1]["DataType"]);
 391                 // Timestamp gets converted into a varbinary(8), so there's
 392                 // some discrepancy here. Ignore it (other tests make sure
 393                 // that timestamp is handled properly).
 394                 if (colType != "timestamp") {
 395                     WVPASSEQ((IComparable)colInfo[0]["ProviderType"],
 396                             (IComparable)colInfo[1]["ProviderType"]);
 397                 }
 398                 WVPASSEQ((IComparable)colInfo[0]["IsLong"],
 399                         (IComparable)colInfo[1]["IsLong"]);
 400             }
 401
 402             WVASSERT(Exec("DROP TABLE #test1out"));
 403             WVASSERT(Exec("DROP TABLE #test1"));
 404         }
 405     }
 406
 407     [Test, Category("Schema"), Category("Errors")]
 408     public void EmptyColumnName()
 409     {
 410         // Check that a query with a missing column name throws an error
 411
 412         try {
 413             WVEXCEPT(RunSucker("#suckout", "SELECT 1"));
 414         } catch (NUnit.Framework.AssertionException e) {
 415             throw e;
 416         } catch (System.Exception e) {
 417             WVPASS(e is SqlException);
 418         }
 419
 420         // The failed run shouldn't have modified #suckout, so this should work
 421         WVASSERT(RunSucker("#suckout", "SELECT 1 as foo"));
 422
 423         SqlDataReader reader;
 424         WVASSERT(Reader("SELECT * FROM #suckout", out reader));
 425         using (reader)
 426         using (DataTable schema = reader.GetSchemaTable()) {
 427             WVPASSEQ(reader.FieldCount, 1);
 428             WVPASSEQ(schema.Rows.Count, 1);
 429
 430             DataRow schemaRow = schema.Rows[0];
 431             WVPASSEQ((string)schemaRow["ColumnName"], "foo");
 432
 433             WVPASS(reader.Read());
 434             WVPASSEQ((int)reader["foo"], 1);
 435
 436             WVFAIL(reader.Read());
 437             WVFAIL(reader.NextResult());
 438         }
 439     }
 440
 441     [Test, Category("Data")]
 442     public void RowOrdering()
 443     {
 444         // Make sure that data comes out in the right order when ordering is
 445         // requested from SqlSucker
 446
 447         // If these are all prime then the permutation is guaranteed to work
 448         // without any duplicates (I think it actually works as long as numElems
 449         // is coprime with the other two, but making them all prime is safe)
 450         const int numElems = 101;
 451         const int prime1 = 47;
 452         const int prime2 = 53;
 453
 454         WVASSERT(Exec("CREATE TABLE #test1 (seq int NOT NULL, "
 455                     + "num int NOT NULL)"));
 456
 457         // j will be a permutation of 0..numElems without resorting to random
 458         // numbers, while making sure that we're not inserting in sorted order.
 459         for (int i=0, j=0; i < numElems; i++, j = (i*prime1) % numElems) {
 460             // This inserts 0..numElems into seq (in a permuted order), with
 461             // 0..numElems in num, but permuted in a different order.
 462             Insert("#test1", j, (j*prime2) % numElems);
 463         }
 464
 465         WVASSERT(RunSucker("#suckout",
 466                     "SELECT num from #test1 ORDER BY seq DESC", true));
 467
 468         SqlDataReader reader;
 469         WVASSERT(Reader("SELECT num FROM #suckout ORDER BY _ DESC",
 470                     out reader));
 471
 472         using (reader) {
 473             for (int i=0; i < numElems; i++) {
 474                 WVASSERT(reader.Read());
 475                 WVPASSEQ((int)reader["num"], (i*prime2) % numElems);
 476             }
 477
 478             WVFAIL(reader.Read());
 479         }
 480
 481         WVASSERT(Exec("DROP TABLE #test1"));
 482     }
 483
 484     [Test, Category("Schema")]
 485     public void ColumnOrdering()
 486     {
 487         // Make a bunch of columns and check that they come back in the right
 488         // order
 489
 490         // For an explanation about the permutation stuff here, see the
 491         // RowOrdering test, above
 492         const int numCols = 101;
 493         const int numSelected = 83;
 494         const int prime1 = 47;
 495         const int prime2 = 53;
 496
 497         System.Text.StringBuilder query = new System.Text.StringBuilder(
 498                 "CREATE TABLE #test1 (");
 499
 500         for (int i=0, j=0; i < numCols; i++, j = (i*prime1) % numCols) {
 501             if (i > 0)
 502                 query.Append(", ");
 503
 504             query.AppendFormat("col{0} int", j);
 505         }
 506
 507         query.Append(")");
 508
 509         WVASSERT(Exec(query.ToString()));
 510
 511         query = new System.Text.StringBuilder("SELECT ");
 512
 513         // Don't select all of them, in case that makes a difference. But still
 514         // select from the entire range (as opposed to the first few), so still
 515         // mod by numCols instead of numSelected.
 516         for (int i=0, j=0; i < numSelected; i++, j = (i*prime2) % numCols) {
 517             if (i > 0)
 518                 query.Append(", ");
 519
 520             query.AppendFormat("col{0}", j);
 521         }
 522         query.Append(" FROM #test1");
 523
 524         WVASSERT(RunSucker("#suckout", query.ToString()));
 525
 526         SqlDataReader reader;
 527         WVASSERT(Reader("SELECT * FROM #suckout", out reader));
 528
 529         using (reader) {
 530             WVPASSEQ(reader.FieldCount, numSelected);
 531
 532             for (int i=0; i < numSelected; i++) {
 533                 WVPASSEQ((string)reader.GetName(i),
 534                         string.Format("col{0}", (i*prime2) % numCols));
 535             }
 536
 537             WVFAIL(reader.Read());
 538         }
 539
 540         WVASSERT(Exec("DROP TABLE #test1"));
 541     }
 542
 543     [Test, Category("Data")]
 544     public void VerifyIntegers()
 545     {
 546         // bigint, int, smallint, tinyint
 547         // Insert 6 rows: max, 10, 0, -10, min, nulls (except tinyint is
 548         // unsigned so it has 0 again instead of -10)
 549         // Then check that they were copied correctly
 550         // Assume that the schema of the output table is correct (tested
 551         // elsewhere)
 552
 553         WVASSERT(Exec("CREATE TABLE #test1 (bi bigint, i int, si smallint, "
 554                     + "ti tinyint, roworder int not null)"));
 555
 556         WVASSERT(Insert("#test1", Int64.MaxValue, Int32.MaxValue,
 557                     Int16.MaxValue, Byte.MaxValue, 1));
 558         WVASSERT(Insert("#test1", 10, 10, 10, 10, 2));
 559         WVASSERT(Insert("#test1", 0, 0, 0, 0, 3));
 560         WVASSERT(Insert("#test1", -10, -10, -10, 0, 4));
 561         WVASSERT(Insert("#test1", Int64.MinValue, Int32.MinValue,
 562                     Int16.MinValue, Byte.MinValue, 5));
 563         WVASSERT(Insert("#test1", DBNull.Value, DBNull.Value, DBNull.Value,
 564                     DBNull.Value, 6));
 565
 566         WVASSERT(RunSucker("#suckout", "SELECT * FROM #test1"));
 567
 568         WVASSERT(Exec("DROP TABLE #test1"));
 569
 570         SqlDataReader reader;
 571         WVASSERT(Reader("SELECT bi,i,si,ti FROM #suckout ORDER BY roworder",
 572                     out reader));
 573
 574         using (reader) {
 575             WVASSERT(reader.Read());
 576             WVPASSEQ(reader.GetInt64(0), Int64.MaxValue);
 577             WVPASSEQ(reader.GetInt32(1), Int32.MaxValue);
 578             WVPASSEQ(reader.GetInt16(2), Int16.MaxValue);
 579             WVPASSEQ(reader.GetByte(3), Byte.MaxValue);
 580
 581             WVASSERT(reader.Read());
 582             WVPASSEQ(reader.GetInt64(0), 10);
 583             WVPASSEQ(reader.GetInt32(1), 10);
 584             WVPASSEQ(reader.GetInt16(2), 10);
 585             WVPASSEQ(reader.GetByte(3), 10);
 586
 587             WVASSERT(reader.Read());
 588             WVPASSEQ(reader.GetInt64(0), 0);
 589             WVPASSEQ(reader.GetInt32(1), 0);
 590             WVPASSEQ(reader.GetInt16(2), 0);
 591             WVPASSEQ(reader.GetByte(3), 0);
 592
 593             WVASSERT(reader.Read());
 594             WVPASSEQ(reader.GetInt64(0), -10);
 595             WVPASSEQ(reader.GetInt32(1), -10);
 596             WVPASSEQ(reader.GetInt16(2), -10);
 597             WVPASSEQ(reader.GetByte(3), 0);
 598
 599             WVASSERT(reader.Read());
 600             WVPASSEQ(reader.GetInt64(0), Int64.MinValue);
 601             WVPASSEQ(reader.GetInt32(1), Int32.MinValue);
 602             WVPASSEQ(reader.GetInt16(2), Int16.MinValue);
 603             WVPASSEQ(reader.GetByte(3), Byte.MinValue);
 604
 605             WVASSERT(reader.Read());
 606             WVPASS(reader.IsDBNull(0));
 607             WVPASS(reader.IsDBNull(1));
 608             WVPASS(reader.IsDBNull(2));
 609             WVPASS(reader.IsDBNull(3));
 610
 611             WVFAIL(reader.Read());
 612         }
 613     }
 614
 615     [Test, Category("Data")]
 616     public void VerifyBinary()
 617     {
 618         // binary, varbinary (not max)
 619
 620         // This must be sorted
 621         int [] sizes = { 1, 10, 50, 255, 4000, 8000 };
 622
 623         string [] types = { "binary", "varbinary" };
 624         int [] typemax = { 8000, 8000 };
 625         int [] charsize = { 1, 1 };
 626         bool [] varsize = { false, true };
 627
 628         Byte [] binary_goop = read_goop();
 629
 630         WVASSERT(binary_goop.Length >= sizes[sizes.Length-1]);
 631
 632         for (int i=0; i < types.Length; i++) {
 633             for (int j=0; j < sizes.Length && sizes[j] <= typemax[i]; j++) {
 634                 WVASSERT(Exec(string.Format("CREATE TABLE #test1 "
 635                                 + "(data {0}({1}), roworder int not null)",
 636                                 types[i], sizes[j])));
 637
 638                 for (int k=0; k <= j; k++) {
 639                     Byte [] data = new byte[sizes[k]];
 640                     Array.Copy(binary_goop, data, sizes[k]);
 641
 642                     WVASSERT(Insert("#test1", new SqlBinary(data), k));
 643                 }
 644
 645                 WVASSERT(Insert("#test1", DBNull.Value, j+1));
 646
 647                 WVASSERT(SetupOutputTable("#test1out"));
 648
 649                 WVASSERT(RunSucker("#test1out",
 650                             "SELECT * FROM #test1 ORDER BY roworder", true));
 651
 652                 WVASSERT(Exec("DROP TABLE #test1"));
 653
 654                 SqlDataReader reader;
 655                 WVASSERT(Reader("SELECT LEN(data), DATALENGTH(data), data FROM "
 656                             + "#test1out ORDER BY _",
 657                             out reader));
 658
 659                 using (reader) {
 660                     for (int k=0; k <= j; k++) {
 661                         Byte [] data = new byte[sizes[k]];
 662                         Array.Copy(binary_goop, data, sizes[k]);
 663
 664                         WVASSERT(reader.Read());
 665
 666                         int len = sizes[varsize[i] ? k : j];
 667                         WVPASSEQ(GetInt64(reader, 0), len);
 668
 669                         int datalen = sizes[varsize[i] ? k : j]*charsize[i];
 670                         WVPASSEQ(GetInt64(reader, 1), datalen);
 671
 672                         WVPASSEQ(reader.GetSqlBinary(2), new SqlBinary(data));
 673                     }
 674
 675                     WVASSERT(reader.Read());
 676                     WVPASS(reader.IsDBNull(2));
 677
 678                     WVFAIL(reader.Read());
 679                 }
 680
 681                 WVASSERT(Exec("DROP TABLE #test1out"));
 682             }
 683         }
 684     }
 685
 686     [Test, Category("Data")]
 687     public void VerifyBit()
 688     {
 689         // bit
 690         // Insert 3 rows: true, false, null
 691         // Then check that they were copied correctly
 692         // Assume that the schema of the output table is correct (tested
 693         // elsewhere)
 694
 695         WVASSERT(Exec("CREATE TABLE #test1 (b bit, roworder int not null)"));
 696
 697         WVASSERT(Insert("#test1", true, 1));
 698         WVASSERT(Insert("#test1", false, 2));
 699         WVASSERT(Insert("#test1", DBNull.Value, 3));
 700
 701         WVASSERT(RunSucker("#suckout", "SELECT * FROM #test1"));
 702
 703         WVASSERT(Exec("DROP TABLE #test1"));
 704
 705         SqlDataReader reader;
 706         WVASSERT(Reader("SELECT b FROM #suckout ORDER BY roworder",
 707                     out reader));
 708
 709         using (reader) {
 710             WVASSERT(reader.Read());
 711             WVPASSEQ(reader.GetBoolean(0), true);
 712
 713             WVASSERT(reader.Read());
 714             WVPASSEQ(reader.GetBoolean(0), false);
 715
 716             WVASSERT(reader.Read());
 717             WVPASS(reader.IsDBNull(0));
 718
 719             WVFAIL(reader.Read());
 720         }
 721     }
 722
 723     [Test, Category("Data")]
 724     public void VerifyChar()
 725     {
 726         // char, nchar, varchar (in-row or max), nvarchar (in-row or max),
 727         // text, ntext
 728         // This doesn't try to use any non-ascii characters. There is a separate
 729         // test for that.
 730
 731         // This must be sorted
 732         int [] sizes = { 1, 10, 50, 255, 4000, 8000, 8040, 8192, 16080, 16384,
 733             24120, 32160, 32768, 50157 };
 734
 735         string [] types = { "char", "varchar", "nchar", "nvarchar", "text",
 736             "ntext", "varchar(max)", "nvarchar(max)" };
 737         int [] typemax = { 8000, 8000, 4000, 4000, Int32.MaxValue,
 738             Int32.MaxValue/2, Int32.MaxValue, Int32.MaxValue/2 };
 739         int [] charsize = { 1, 1, 2, 2, 1, 2, 1, 2 };
 740         bool [] varsize = { false, true, false, true, true, true, true, true };
 741         bool [] sizeparam = { true, true, true, true, false, false, false,
 742             false };
 743         bool [] lenok = { true, true, true, true, false, false, true, true };
 744
 745         string lipsum_text = read_lipsum();
 746
 747         WVASSERT(lipsum_text.Length >= sizes[sizes.Length-1]);
 748
 749         for (int i=0; i < types.Length; i++) {
 750             for (int j=0; j < sizes.Length && sizes[j] <= typemax[i]; j++) {
 751                 if (sizeparam[i]) {
 752                     WVASSERT(Exec(string.Format("CREATE TABLE #test1 "
 753                                     + "(data {0}({1}), roworder int not null)",
 754                                     types[i], sizes[j])));
 755                 } else {
 756                     WVASSERT(Exec(string.Format("CREATE TABLE #test1 "
 757                                     + "(data {0}, roworder int not null)",
 758                                     types[i])));
 759                     j = sizes.Length-1;
 760                 }
 761
 762                 for (int k=0; k <= j; k++) {
 763                     WVASSERT(Exec(string.Format(
 764                                     "INSERT INTO #test1 VALUES ('{0}', {1})",
 765                                     lipsum_text.Substring(0,
 766                                         sizes[k]).Replace("'", "''"), k)));
 767                     /* This doesn't work because it truncates to 4000 chars
 768                      * regardless of if it's a nchar/nvarchar or plain
 769                      * char/varchar.
 770                     WVASSERT(Insert("#test1",
 771                                 new SqlString(
 772                                     lipsum_text.Substring(0, sizes[k])), k));
 773                                     */
 774                 }
 775
 776                 WVASSERT(Insert("#test1", DBNull.Value, j+1));
 777
 778                 WVASSERT(SetupOutputTable("#test1out"));
 779
 780                 WVASSERT(RunSucker("#test1out",
 781                             "SELECT * FROM #test1 ORDER BY roworder", true));
 782
 783                 WVASSERT(Exec("DROP TABLE #test1"));
 784
 785                 SqlDataReader reader;
 786
 787                 if (lenok[i]) {
 788                     WVASSERT(Reader("SELECT LEN(data), DATALENGTH(data), data "
 789                                 + "FROM #test1out ORDER BY _",
 790                                 out reader));
 791                 } else {
 792                     WVASSERT(Reader("SELECT -1, "
 793                                 + "DATALENGTH(data), data FROM #test1out "
 794                                 + "ORDER BY _",
 795                                 out reader));
 796                 }
 797
 798                 using (reader) {
 799                     for (int k=0; k <= j; k++) {
 800                         WVASSERT(reader.Read());
 801
 802                         if (lenok[i])
 803                             WVPASSEQ(GetInt64(reader, 0), sizes[k]);
 804
 805                         WVPASSEQ(GetInt64(reader, 1),
 806                                 sizes[varsize[i] ? k : j]*charsize[i]);
 807                         WVPASSEQ(reader.GetString(2).Substring(0, sizes[k]),
 808                                 lipsum_text.Substring(0, sizes[k]));
 809                     }
 810
 811                     WVASSERT(reader.Read());
 812                     WVPASS(reader.IsDBNull(2));
 813
 814                     WVFAIL(reader.Read());
 815                 }
 816
 817                 WVASSERT(Exec("DROP TABLE #test1out"));
 818             }
 819         }
 820     }
 821
 822     [Test, Category("Data")]
 823     public void VerifyDateTime()
 824     {
 825         // datetime, smalldatetime
 826         // Insert 7 rows: max, a date in the future, now, a date in the past,
 827         // datetime epoch, min, null
 828         //
 829         // Then check that they were copied correctly
 830         // Assume that the schema of the output table is correct (tested
 831         // elsewhere)
 832         //
 833         // The actual dates don't really matter, but:
 834         // - The date in the past is adewhurst's birthday (approximately, PST)
 835         // - The date in the future is 1 second past the signed 32-bit overflow
 836         //   of seconds since the unix epoch (UTC)
 837         // - The datetime epoch is January 1 1900 at midnight
 838         //
 839         // Other notes:
 840         // - The min/max values of SqlDateTime are supposed to correspond to the
 841         //   min/max values of the SQL Server datetime type, except Mono doesn't
 842         //   quite have the semantics right (at least, not as of 1.2.3.1, but
 843         //   new versions may be better), so the min/max values are hard-coded
 844         //   in instead.
 845         // - All smalldatetime values are rounded down to the nearest minute,
 846         //   since it only has per-minute granularity
 847
 848         SqlDateTime epoch = new SqlDateTime(0, 0);
 849         SqlDateTime smallMin = epoch;
 850         SqlDateTime smallMax = new SqlDateTime(2079, 6, 6, 23, 59, 0, 0);
 851
 852         SqlDateTime dtMin = new SqlDateTime(1753, 1, 1, 0, 0, 0, 0);
 853         // This is wrong, but mono seems to have trouble with the fractional
 854         // parts.
 855         SqlDateTime dtMax = new SqlDateTime(9999, 12, 31, 23, 59, 59, 0);
 856
 857         SqlDateTime pastDate = new SqlDateTime(1984, 12, 2, 3, 0, 0, 0);
 858         SqlDateTime pastDateSmall = new SqlDateTime(1984, 12, 2, 3, 0, 0, 0);
 859         SqlDateTime futureDate = new SqlDateTime(2038, 6, 19, 3, 14, 8, 0);
 860         SqlDateTime futureDateSmall = new SqlDateTime(2038, 6, 19, 3, 14, 0, 0);
 861
 862         // Mono has difficulties converting DateTime to SqlDateTime directly, so
 863         // take it down to per-second precision, which works reliably
 864         DateTime now = DateTime.Now;
 865         SqlDateTime sqlNow = new SqlDateTime(now.Year, now.Month, now.Day,
 866                 now.Hour, now.Minute, now.Second);
 867         SqlDateTime sqlNowSmall = new SqlDateTime(now.Year, now.Month, now.Day,
 868                 now.Hour, now.Minute, 0);
 869
 870         WVASSERT(Exec("CREATE TABLE #test1 (dt datetime, sdt smalldatetime, "
 871                     + "roworder int not null)"));
 872
 873         WVASSERT(Insert("#test1", dtMin, smallMin, 1));
 874         WVASSERT(Insert("#test1", epoch, epoch, 2));
 875         WVASSERT(Insert("#test1", pastDate, pastDateSmall, 3));
 876         WVASSERT(Insert("#test1", sqlNow, sqlNowSmall, 4));
 877         WVASSERT(Insert("#test1", futureDate, futureDateSmall, 5));
 878         WVASSERT(Insert("#test1", dtMax, smallMax, 6));
 879         WVASSERT(Insert("#test1", DBNull.Value, DBNull.Value, 7));
 880
 881         WVASSERT(RunSucker("#suckout", "SELECT * FROM #test1"));
 882
 883         WVASSERT(Exec("DROP TABLE #test1"));
 884
 885         SqlDataReader reader;
 886         WVASSERT(Reader("SELECT dt, sdt FROM #suckout ORDER BY roworder",
 887                     out reader));
 888
 889         using (reader) {
 890             WVASSERT(reader.Read());
 891             WVPASSEQ(reader.GetSqlDateTime(0), dtMin);
 892             WVPASSEQ(reader.GetSqlDateTime(1), smallMin);
 893
 894             WVASSERT(reader.Read());
 895             WVPASSEQ(reader.GetSqlDateTime(0), epoch);
 896             WVPASSEQ(reader.GetSqlDateTime(1), epoch);
 897
 898             WVASSERT(reader.Read());
 899             WVPASSEQ(reader.GetSqlDateTime(0), pastDate);
 900             WVPASSEQ(reader.GetSqlDateTime(1), pastDateSmall);
 901
 902             WVASSERT(reader.Read());
 903             WVPASSEQ(reader.GetSqlDateTime(0), sqlNow);
 904             WVPASSEQ(reader.GetSqlDateTime(1), sqlNowSmall);
 905
 906             WVASSERT(reader.Read());
 907             WVPASSEQ(reader.GetSqlDateTime(0), futureDate);
 908             WVPASSEQ(reader.GetSqlDateTime(1), futureDateSmall);
 909
 910             WVASSERT(reader.Read());
 911             WVPASSEQ(reader.GetSqlDateTime(0), dtMax);
 912             WVPASSEQ(reader.GetSqlDateTime(1), smallMax);
 913
 914             WVASSERT(reader.Read());
 915             WVPASS(reader.IsDBNull(0));
 916             WVPASS(reader.IsDBNull(1));
 917
 918             WVFAIL(reader.Read());
 919         }
 920     }
 921
 922     [Test, Category("Data")]
 923     public void VerifyDecimal()
 924     {
 925         // decimal(38,0), decimal(38,38), decimal(18,0), decimal(1,0),
 926         // decimal(1,1), numeric as same types
 927         // Insert 6 rows: max, something positive, 0, something negative, min,
 928         // nulls
 929         // Then check that they were copied correctly
 930         // Assume that the schema of the output table is correct (tested
 931         // elsewhere)
 932
 933         Byte [,] sizes = {
 934             // {precision, scale}
 935             {38, 0},
 936             {38, 38},
 937             {18, 0},
 938             {1, 0},
 939             {1, 1}
 940         };
 941
 942         // Construct all of the things we will insert
 943         // These are all strings because attempting to use the SqlDecimal class
 944         // just leads to no end of problems. Even Microsoft's .NET
 945         // implementation seems to have issues with the max/min value ones.
 946         object [,] values = {
 947             {
 948                 "99999999999999999999999999999999999999",
 949                 "0.99999999999999999999999999999999999999",
 950                 "999999999999999999",
 951                 "9",
 952                 "0.9"
 953             }, {
 954                 "123456",
 955                 "0.12345600000000000000000000000000000000",
 956                 "123456",
 957                 "1",
 958                 "0.1"
 959             }, {
 960                 /*
 961                  * The "zero" data set actually makes Mono's TDS library croak.
 962                  * But that's not a SqlSucker bug. The other data sets should
 963                  * give reasonable confidence in SqlSucker anyway.
 964                 "0",
 965                 "0.00000000000000000000000000000000000000",
 966                 "0",
 967                 "0",
 968                 "0.0"
 969             }, {
 970                 */
 971                 "-654321",
 972                 "-0.65432100000000000000000000000000000000",
 973                 "-654321",
 974                 "-1",
 975                 "-0.1"
 976             }, {
 977                 "-99999999999999999999999999999999999999",
 978                 "-0.99999999999999999999999999999999999999",
 979                 "-999999999999999999",
 980                 "-9",
 981                 "-0.9"
 982             }, {
 983                 DBNull.Value,
 984                 DBNull.Value,
 985                 DBNull.Value,
 986                 DBNull.Value,
 987                 DBNull.Value
 988             }
 989         };
 990
 991         // Make sure that the data is specified correctly here
 992         WVPASSEQ(sizes.GetLength(0), values.GetLength(1));
 993
 994         // Make the table we're going to create
 995         System.Text.StringBuilder schema = new System.Text.StringBuilder(
 996                 "CREATE TABLE #test1 (");
 997
 998         // Make one of each decimal and numeric column. These are in fact
 999         // identical, but since either may show up in real-world tables, testing
1000         // both is a good plan
1001         for (int i=0; i < sizes.GetLength(0); i++) {
1002             schema.AppendFormat("d{0}_{1} decimal({0},{1}), "
1003                     + "n{0}_{1} numeric({0},{1}), ", sizes[i,0], sizes[i,1]);
1004         }
1005
1006         schema.Append("roworder int not null)");
1007
1008         WVASSERT(Exec(schema.ToString()));
1009
1010         // Now insert them
1011         object [] insertParams = new object[2*values.GetLength(1)+1];
1012
1013         for (int i=0; i < values.GetLength(0); i++) {
1014             insertParams[insertParams.Length-1] = i;
1015             for (int j=0; j < insertParams.Length-1; j++) {
1016                 insertParams[j] = values[i,j/2];
1017             }
1018             WVASSERT(Insert("#test1", insertParams));
1019         }
1020
1021         WVASSERT(RunSucker("#suckout", "SELECT * FROM #test1"));
1022
1023         WVASSERT(Exec("DROP TABLE #test1"));
1024
1025         SqlDataReader reader;
1026         WVASSERT(Reader("SELECT * FROM #suckout ORDER BY roworder",
1027                     out reader));
1028
1029         using (reader) {
1030             for (int i=0; i < values.GetLength(0); i++) {
1031                 WVASSERT(reader.Read());
1032
1033                 for (int j=0; j < insertParams.Length-1; j++) {
1034                     if (values[i,j/2] is DBNull) {
1035                         WVPASS(reader.IsDBNull(j));
1036                     } else {
1037                         // The preprocessor doesn't like the comma in the array
1038                         // subscripts
1039                         string val = (string)values[i,j/2];
1040                         string fromdb = reader.GetSqlDecimal(j).ToString();
1041
1042                         // Mono produces ".1" and "-.1"
1043                         // Microsoft .NET produces "0.1" and "-0.1"
1044                         // Deal with that here.
1045                         if (val[0] == '0' && fromdb[0] == '.') {
1046                             WVPASSEQ(fromdb, val.Substring(1));
1047                         } else if (val[0] == '-' && val[1] == '0'
1048                                 && fromdb[0] == '-' && fromdb[1] == '.') {
1049                             WVPASSEQ(fromdb, "-" + val.Substring(2));
1050                         } else {
1051                             WVPASSEQ(fromdb, val);
1052                         }
1053                     }
1054                 }
1055             }
1056
1057             WVFAIL(reader.Read());
1058         }
1059     }
1060
1061     [Test, Category("Data")]
1062     public void VerifyFloat()
1063     {
1064         // float(53), float(24), real
1065         // Insert 8 rows: max, something positive, smallest positive, 0,
1066         // smallest negative, something negative, min, nulls
1067         // Then check that they were copied correctly
1068         // Assume that the schema of the output table is correct (tested
1069         // elsewhere)
1070         //
1071         // Specifically, infinity, -infinity and NaN are not tested here because
1072         // SQL Server appears to reject them as values for float columns
1073
1074         // Construct all of the things we will insert
1075         object [,] values = {
1076             {
1077                 /* Can't use SqlDouble.MaxValue et al. because there are
1078                  * rounding issues in Mono somewhere that make it reject the
1079                  * exact maximum value. These numbers come from the SQL Server
1080                  * 2005 reference for the float data type
1081                 SqlDouble.MaxValue,
1082                 SqlSingle.MaxValue,
1083                 SqlSingle.MaxValue */
1084                 1.79E+308d,
1085                 3.40E+38f,
1086                 3.40E+38f
1087             }, {
1088                 /* Mono has problems with sending Math.E in a way that is
1089                  * roundtrip-able
1090                 (double)Math.E,
1091                 (float)Math.E,
1092                 (float)Math.E */
1093                 2.71828182845905d,
1094                 2.718282f,
1095                 2.718282f
1096             }, {
1097                 /* Can't use Double.Epsilon or Single.Epsilon because SQL server
1098                  * complains, even on the Microsoft .NET implementation
1099                  * These numbers come from the SQL Server 2005 reference for the
1100                  * float data type
1101                 Double.Epsilon,
1102                 Single.Epsilon,
1103                 Single.Epsilon */
1104                 2.23E-308d,
1105                 1.18E-38f,
1106                 1.18E-38f
1107             }, {
1108                 0.0d,
1109                 0.0f,
1110                 0.0f
1111             }, {
1112                 /*
1113                 -Double.Epsilon,
1114                 -Single.Epsilon,
1115                 -Single.Epsilon */
1116                 -2.23E-308d,
1117                 -1.18E-38f,
1118                 -1.18E-38f
1119             }, {
1120                 -127.001d,
1121                 -1270.01f,
1122                 -12700.1f
1123             }, {
1124                 /*
1125                 SqlDouble.MinValue,
1126                 SqlSingle.MinValue,
1127                 SqlSingle.MinValue */
1128                 -1.79E+308d,
1129                 -3.40E+38f,
1130                 -3.40E+38f
1131             }, {
1132                 DBNull.Value,
1133                 DBNull.Value,
1134                 DBNull.Value
1135             }
1136         };
1137
1138         WVASSERT(Exec("CREATE TABLE #test1 (f53 float(53), f24 float(24), "
1139                     + "r real, roworder int not null)"));
1140
1141         // Now insert them
1142         object [] insertParams = new object[values.GetLength(1)+1];
1143
1144         for (int i=0; i < values.GetLength(0); i++) {
1145             insertParams[insertParams.Length-1] = i;
1146             for (int j=0; j < insertParams.Length-1; j++) {
1147                 insertParams[j] = values[i,j];
1148             }
1149             WVASSERT(Insert("#test1", insertParams));
1150         }
1151
1152         WVASSERT(RunSucker("#suckout", "SELECT * FROM #test1"));
1153
1154         WVASSERT(Exec("DROP TABLE #test1"));
1155
1156         SqlDataReader reader;
1157         WVASSERT(Reader("SELECT * FROM #suckout ORDER BY roworder",
1158                     out reader));
1159
1160         using (reader) {
1161             for (int i=0; i < values.GetLength(0); i++) {
1162                 WVASSERT(reader.Read());
1163
1164                 for (int j=0; j < insertParams.Length-1; j++) {
1165                     // The preprocessor doesn't like the comma in the array
1166                     // subscripts
1167                     object val = values[i,j];
1168
1169                     if (val is DBNull) {
1170                         WVPASS(reader.IsDBNull(j));
1171                     } else if (val is double) {
1172                         WVPASSEQ(reader.GetDouble(j), (double)val);
1173                     } else if (val is float) {
1174                         WVPASSEQ(reader.GetFloat(j), (float)val);
1175                     } else {
1176                         // If we get here, a data type was used in the values
1177                         // array that's not handled by one of the above cases
1178                         bool test_is_broken = true;
1179                         WVFAIL(test_is_broken);
1180                     }
1181                 }
1182             }
1183
1184             WVFAIL(reader.Read());
1185         }
1186     }
1187
1188     [Test, Category("Data")]
1189     public void VerifyMoney()
1190     {
1191         // money, smallmoney
1192         // Insert 6 rows: max, a positive amount, 0, a negative amount, min,
1193         // null
1194         // Then check that they were copied correctly
1195         // Assume that the schema of the output table is correct (tested
1196         // elsewhere)
1197
1198         WVASSERT(Exec("CREATE TABLE #test1 (m money, sm smallmoney, "
1199                     + "roworder int not null)"));
1200
1201         WVASSERT(Insert("#test1", SqlMoney.MaxValue, 214748.3647m, 1));
1202         WVASSERT(Insert("#test1", 1337.42m, 1337.42m, 2));
1203         WVASSERT(Insert("#test1", 0.0m, 0.0m, 3));
1204         WVASSERT(Insert("#test1", -3.141m, -3.141m, 5));
1205         WVASSERT(Insert("#test1", SqlMoney.MinValue, -214748.3648m, 6));
1206         WVASSERT(Insert("#test1", DBNull.Value, DBNull.Value, 7));
1207
1208         WVASSERT(RunSucker("#suckout", "SELECT * FROM #test1"));
1209
1210         WVASSERT(Exec("DROP TABLE #test1"));
1211
1212         SqlDataReader reader;
1213         // Cast the return type because Mono doesn't properly handle negative
1214         // money amounts
1215         WVASSERT(Reader("SELECT CAST(m as decimal(20,4)),"
1216                     + "CAST(sm as decimal(20,4)) "
1217                     + "FROM #suckout ORDER BY roworder", out reader));
1218
1219         using (reader) {
1220             WVASSERT(reader.Read());
1221             WVPASSEQ(reader.GetDecimal(0), SqlMoney.MaxValue.ToDecimal());
1222             WVPASSEQ(reader.GetDecimal(1), 214748.3647m);
1223
1224             WVASSERT(reader.Read());
1225             WVPASSEQ(reader.GetDecimal(0), 1337.42m);
1226             WVPASSEQ(reader.GetDecimal(1), 1337.42m);
1227
1228             WVASSERT(reader.Read());
1229             WVPASSEQ(reader.GetDecimal(0), 0m);
1230             WVPASSEQ(reader.GetDecimal(1), 0m);
1231
1232             WVASSERT(reader.Read());
1233             WVPASSEQ(reader.GetDecimal(0), -3.141m);
1234             WVPASSEQ(reader.GetDecimal(1), -3.141m);
1235
1236             WVASSERT(reader.Read());
1237             WVPASSEQ(reader.GetDecimal(0), SqlMoney.MinValue.ToDecimal());
1238             WVPASSEQ(reader.GetDecimal(1), -214748.3648m);
1239
1240             WVASSERT(reader.Read());
1241             WVPASS(reader.IsDBNull(0));
1242             WVPASS(reader.IsDBNull(1));
1243
1244             WVFAIL(reader.Read());
1245         }
1246     }
1247
1248     [Test, Category("Data")]
1249     public void VerifyTimestamp()
1250     {
1251         // Create a table with a timestamp column, create a bunch of rows in a
1252         // particular order, then check that they match up after copying
1253
1254         // This permutation strategy is discussed in the RowOrdering test
1255         const int numElems = 101;
1256         const int prime1 = 47;
1257
1258         WVASSERT(Exec("CREATE TABLE #test1 (ts timestamp, "
1259                     + "roworder int not null)"));
1260
1261         for (int i=0, j=0; i < numElems; i++, j = (i*prime1) % numElems) {
1262             Insert("#test1", DBNull.Value, j);
1263         }
1264
1265         SqlDataReader reader;
1266         WVASSERT(Reader("SELECT ts,roworder FROM #test1 ORDER BY roworder",
1267                     out reader));
1268
1269         SqlBinary [] tsdata = new SqlBinary[numElems];
1270
1271         using (reader) {
1272             for (int i=0; i < numElems; i++) {
1273                 WVASSERT(reader.Read());
1274                 WVPASSEQ(reader.GetInt32(1), i);
1275                 tsdata[i] = reader.GetSqlBinary(0);
1276             }
1277
1278             WVFAIL(reader.Read());
1279         }
1280
1281         WVASSERT(RunSucker("#suckout",
1282                     "SELECT ts,roworder from #test1 ORDER BY ts", true));
1283
1284         WVASSERT(Exec("DROP TABLE #test1"));
1285
1286         WVASSERT(Reader("SELECT ts,roworder FROM #suckout ORDER BY _",
1287                     out reader));
1288
1289         using (reader) {
1290             for (int i=0, j=0; i < numElems; i++, j = (i*prime1) % numElems) {
1291                 WVASSERT(reader.Read());
1292                 WVPASSEQ(reader.GetInt32(1), j);
1293                 WVPASSEQ(reader.GetSqlBinary(0), tsdata[j]);
1294             }
1295
1296             WVFAIL(reader.Read());
1297         }
1298     }
1299
1300     [Test, Category("Data")]
1301     public void VerifyUniqueIdentifier()
1302     {
1303         // uniqueidentifier
1304         // Insert 2 rows: a valid number, null
1305         // Then check that they were copied correctly
1306         // Assume that the schema of the output table is correct (tested
1307         // elsewhere)
1308
1309         SqlGuid guid = new SqlGuid("6F9619FF-8B86-D011-B42D-00C04FC964FF");
1310
1311         WVASSERT(Exec("CREATE TABLE #test1 (u uniqueidentifier, "
1312                     + "roworder int not null)"));
1313
1314         WVASSERT(Insert("#test1", guid, 1));
1315         WVASSERT(Insert("#test1", DBNull.Value, 2));
1316
1317         WVASSERT(RunSucker("#suckout",
1318                     "SELECT u from #test1 ORDER BY roworder", true));
1319
1320         WVASSERT(Exec("DROP TABLE #test1"));
1321
1322         SqlDataReader reader;
1323         WVASSERT(Reader("SELECT u FROM #suckout ORDER BY _",
1324                     out reader));
1325
1326         using (reader) {
1327             WVASSERT(reader.Read());
1328             WVPASSEQ(reader.GetSqlGuid(0), guid);
1329
1330             WVASSERT(reader.Read());
1331             WVPASS(reader.IsDBNull(0));
1332
1333             WVFAIL(reader.Read());
1334         }
1335     }
1336
1337     [Test, Category("Data")]
1338     public void VerifyVarBinaryMax()
1339     {
1340         // varbinary(max), image
1341
1342         // This must be sorted
1343         long [] sizes = { 1, 10, 50, 255, 4000, 8000, 8040, 8192, 16080, 16384,
1344             24120, 32160, 32768, 40200, 65536, 131072, 262144, 524288, 1048576,
1345             2097152, 3076506 };
1346
1347         string [] types = { "varbinary(max)", "image" };
1348
1349         Byte [] image_data = read_image();
1350
1351         WVASSERT(image_data.Length >= sizes[sizes.Length-1]);
1352
1353         foreach (string type in types) {
1354             WVASSERT(Exec(string.Format("CREATE TABLE #test1 "
1355                             + "(data {0}, roworder int not null)", type)));
1356
1357             for (int k=0; k < sizes.Length; k++) {
1358                 Byte [] data = new byte[sizes[k]];
1359                 Array.Copy(image_data, data, sizes[k]);
1360
1361                 WVASSERT(Insert("#test1", new SqlBinary(data), k));
1362             }
1363
1364             WVASSERT(Insert("#test1", DBNull.Value, sizes.Length));
1365
1366             WVASSERT(SetupOutputTable("#test1out"));
1367
1368             WVASSERT(RunSucker("#test1out",
1369                         "SELECT * FROM #test1 ORDER BY roworder", true));
1370
1371             WVASSERT(Exec("DROP TABLE #test1"));
1372
1373             SqlDataReader reader;
1374             WVASSERT(Reader("SELECT DATALENGTH(data), data FROM "
1375                         + "#test1out ORDER BY _",
1376                         out reader));
1377
1378             using (reader) {
1379                 for (int k=0; k < sizes.Length; k++) {
1380                     Byte [] data = new byte[sizes[k]];
1381                     Array.Copy(image_data, data, sizes[k]);
1382
1383                     WVASSERT(reader.Read());
1384
1385                     WVPASSEQ(GetInt64(reader, 0), sizes[k]);
1386                     WVPASSEQ(reader.GetSqlBinary(1), new SqlBinary(data));
1387                 }
1388
1389                 WVASSERT(reader.Read());
1390                 WVPASS(reader.IsDBNull(1));
1391
1392                 WVFAIL(reader.Read());
1393             }
1394
1395             WVASSERT(Exec("DROP TABLE #test1out"));
1396         }
1397     }
1398
1399     [Test, Category("Data")]
1400     public void VerifyXML()
1401     {
1402         // xml
1403         // Insert 2 rows: some sample XML, null
1404         // Then check that they were copied correctly
1405         // Assume that the schema of the output table is correct (tested
1406         // elsewhere)
1407         // This isn't very exhaustive, so improvements are welcome.
1408         // This was going to use SqlXml instead of using a string, but Mono
1409         // doesn't support that very well.
1410
1411         // This MUST not have any extra whitespace, as it will be stripped by
1412         // some SQL parser and won't be reproduced when it comes back out.
1413         // This is the style that Microsoft's .NET returns
1414         string xml =
1415             "<outside><!--hi--><element1 />Text<element2 type=\"pretty\" />"
1416             + "</outside>";
1417         // This is the style that Mono returns
1418         string altxml =
1419             "<outside><!--hi--><element1/>Text<element2 type=\"pretty\"/>"
1420             + "</outside>";
1421
1422         WVASSERT(Exec("CREATE TABLE #test1 (x xml, "
1423                     + "roworder int not null)"));
1424
1425         WVASSERT(Insert("#test1", xml, 1));
1426         WVASSERT(Insert("#test1", DBNull.Value, 2));
1427
1428         WVASSERT(RunSucker("#suckout",
1429                     "SELECT x from #test1 ORDER BY roworder", true));
1430
1431         WVASSERT(Exec("DROP TABLE #test1"));
1432
1433         SqlDataReader reader;
1434         WVASSERT(Reader("SELECT x FROM #suckout ORDER BY _",
1435                     out reader));
1436
1437         using (reader) {
1438             WVASSERT(reader.Read());
1439             // Sigh.
1440             if (reader.GetString(0) == altxml) {
1441                 WVPASSEQ(reader.GetString(0), altxml);
1442             } else {
1443                 WVPASSEQ(reader.GetString(0), xml);
1444             }
1445
1446             WVASSERT(reader.Read());
1447             WVPASS(reader.IsDBNull(0));
1448
1449             WVFAIL(reader.Read());
1450         }
1451     }
1452
1453     [Test, Category("Data")]
1454     public void Unicode()
1455     {
1456         // nchar, nvarchar (in-row or max), ntext
1457         // Using lots of non-ascii characters
1458
1459         string unicode_text = read_unicode();
1460
1461         int [] sizes = { 4000, unicode_text.Length };
1462         WVASSERT(unicode_text.Length >= sizes[0]);
1463
1464         string [] types = { "nchar", "nvarchar", "ntext", "nvarchar(max)" };
1465         int [] typemax = { 4000, 4000, Int32.MaxValue/2, Int32.MaxValue/2 };
1466         int [] charsize = { 2, 2, 2, 2 };
1467         bool [] varsize = { false, true, true, true };
1468         bool [] sizeparam = { true, true, false, false };
1469         bool [] lenok = { true, true, false, true };
1470
1471         for (int i=0; i < types.Length; i++) {
1472             for (int j=0; j < sizes.Length && sizes[j] <= typemax[i]; j++) {
1473                 if (sizeparam[i]) {
1474                     WVASSERT(Exec(string.Format("CREATE TABLE #test1 "
1475                                     + "(data {0}({1}), roworder int not null)",
1476                                     types[i], sizes[j])));
1477                 } else {
1478                     WVASSERT(Exec(string.Format("CREATE TABLE #test1 "
1479                                     + "(data {0}, roworder int not null)",
1480                                     types[i])));
1481                     j = sizes.Length-1;
1482                 }
1483
1484                 for (int k=0; k <= j; k++) {
1485                     WVASSERT(Exec(string.Format(
1486                                     "INSERT INTO #test1 VALUES (N'{0}', {1})",
1487                                     unicode_text.Substring(0,
1488                                         sizes[k]).Replace("'", "''"), k)));
1489                 }
1490
1491                 WVASSERT(SetupOutputTable("#test1out"));
1492
1493                 WVASSERT(RunSucker("#test1out",
1494                             "SELECT * FROM #test1 ORDER BY roworder", true));
1495
1496                 WVASSERT(Exec("DROP TABLE #test1"));
1497
1498                 SqlDataReader reader;
1499
1500                 if (lenok[i]) {
1501                     WVASSERT(Reader("SELECT LEN(data), DATALENGTH(data), data "
1502                                 + "FROM #test1out ORDER BY _",
1503                                 out reader));
1504                 } else {
1505                     WVASSERT(Reader("SELECT -1, "
1506                                 + "DATALENGTH(data), data FROM #test1out "
1507                                 + "ORDER BY _",
1508                                 out reader));
1509                 }
1510
1511                 using (reader) {
1512                     for (int k=0; k <= j; k++) {
1513                         WVASSERT(reader.Read());
1514
1515                         if (lenok[i])
1516                             WVPASSEQ(GetInt64(reader, 0), sizes[k]);
1517
1518                         WVPASSEQ(GetInt64(reader, 1),
1519                                 sizes[varsize[i] ? k : j]*charsize[i]);
1520                         WVPASSEQ(reader.GetString(2).Substring(0, sizes[k]),
1521                                 unicode_text.Substring(0, sizes[k]));
1522                     }
1523
1524                     WVFAIL(reader.Read());
1525                 }
1526
1527                 WVASSERT(Exec("DROP TABLE #test1out"));
1528             }
1529         }
1530     }
1531
1532     [Test, Category("Running")]
1533     public void Recursion()
1534     {
1535         // Check that SqlSucker can be called recursively
1536
1537         // This will need redesigning to recurse past 25 levels because we run
1538         // out of alphabet (primes list will need to be extended too)
1539         // No, there is no particular reason that this has to use primes.
1540         // Also, the default recursion depth of 32 limits recurse_lvl to 9
1541         const int recurse_lvl = 9;
1542         char colname_base = 'A';
1543         int [] primes = { 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43,
1544             47, 53, 59, 61, 67, 71, 73, 79, 83, 89, 97, 101 };
1545
1546         WVASSERT(Exec(string.Format("CREATE PROCEDURE #sucker_test0\n"
1547             + "AS BEGIN\n"
1548             + "SELECT {0} as {1}\n"
1549             + "END", primes[0], colname_base)));
1550
1551         System.Text.StringBuilder colnames = new System.Text.StringBuilder();
1552
1553         for (int i=1; i <= recurse_lvl; i++) {
1554             if (i > 1) {
1555                 colnames.Append(", ");
1556             }
1557             colnames.AppendFormat("{0}", (char)(colname_base + i - 1));
1558
1559             WVASSERT(Exec(string.Format("CREATE PROCEDURE #sucker_test{0}\n"
1560                     + "AS BEGIN\n"
1561                     + "CREATE TABLE #out{1} (_ int)\n"
1562                     + "EXEC SqlSucker '#out{1}','EXEC #sucker_test{1}',false\n"
1563                     + "SELECT {2}, {3} as {4} FROM #out{1}\n"
1564                     + "DROP TABLE #out{1}\n"
1565                     + "END", i, i-1, colnames, primes[i],
1566                     (char)(colname_base + i))));
1567
1568         }
1569
1570         WVASSERT(RunSucker("#suckout",
1571                     string.Format("EXEC #sucker_test{0}", recurse_lvl)));
1572
1573         for (int i=recurse_lvl; i >= 0; i--) {
1574             WVASSERT(Exec(string.Format("DROP PROCEDURE #sucker_test{0}", i)));
1575         }
1576
1577         SqlDataReader reader;
1578         WVASSERT(Reader("SELECT * FROM #suckout", out reader));
1579         using (reader) {
1580             WVPASSEQ(reader.FieldCount, recurse_lvl+1);
1581
1582             for (int i=0; i <= recurse_lvl; i++) {
1583                 WVPASSEQ(reader.GetName(i),
1584                         string.Format("{0}", (char)(colname_base + i)));
1585             }
1586
1587             WVASSERT(reader.Read());
1588             for (int i=0; i <= recurse_lvl; i++) {
1589                 WVPASSEQ(reader.GetInt32(i), primes[i]);
1590             }
1591
1592             WVFAIL(reader.Read());
1593         }
1594     }
1595 }
1596
1597 }
1598