mcs/class/Mono.Data.Sqlite/Test/SqliteDataReaderTest.cs

   1 // SqliteDataReaderTest.cs - NUnit Test Cases for SqliteDataReader
   2 //
   3 // Authors:
   4 //   Sureshkumar T <tsureshkumar@novell.com>
   5 //
   6
   7 //
   8 // Copyright (C) 2004 Novell, Inc (http://www.novell.com)
   9 //
  10 // Permission is hereby granted, free of charge, to any person obtaining
  11 // a copy of this software and associated documentation files (the
  12 // "Software"), to deal in the Software without restriction, including
  13 // without limitation the rights to use, copy, modify, merge, publish,
  14 // distribute, sublicense, and/or sell copies of the Software, and to
  15 // permit persons to whom the Software is furnished to do so, subject to
  16 // the following conditions:
  17 //
  18 // The above copyright notice and this permission notice shall be
  19 // included in all copies or substantial portions of the Software.
  20 //
  21 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  22 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  23 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  24 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  25 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  26 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  27 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  28 //
  29
  30
  31 using System;
  32 using System.Collections.Generic;
  33 using System.Data;
  34 using System.IO;
  35 using System.Text;
  36 using Mono.Data.Sqlite;
  37
  38 using NUnit.Framework;
  39
  40 namespace MonoTests.Mono.Data.Sqlite
  41 {
  42         [TestFixture]
  43         public class SqliteDataReaderTest
  44         {
  45                 readonly static string _uri = "./test.db";
  46                 readonly static string _connectionString = "URI=file://" + _uri + ", version=3";
  47                 SqliteConnection _conn = new SqliteConnection ();
  48
  49                 [TestFixtureSetUp]
  50                 public void FixtureSetUp ()
  51                 {
  52                         if (! File.Exists (_uri) || new FileInfo (_uri).Length == 0) {
  53                                 // ignore all tests
  54                                 Assert.Ignore ("#000 ignoring all fixtures. No database present");
  55                         }
  56                 }
  57
  58
  59                 [Test]
  60                 [Category ("NotWorking")]
  61                 public void GetSchemaTableTest ()
  62                 {
  63                         _conn.ConnectionString = _connectionString;
  64                         SqliteDataReader reader = null;
  65                         using (_conn) {
  66                                 _conn.Open ();
  67                                 SqliteCommand cmd = (SqliteCommand) _conn.CreateCommand ();
  68                                 cmd.CommandText = "select * from test";
  69                                 reader = cmd.ExecuteReader ();
  70                                 try {
  71                                         DataTable dt = reader.GetSchemaTable ();
  72                                         Assert.IsNotNull (dt, "#GS1 should return valid table");
  73                                         Assert.IsTrue (dt.Rows.Count > 0, "#GS2 should return with rows ;-)");
  74                                 } finally {
  75                                         if (reader != null && !reader.IsClosed)
  76                                                 reader.Close ();
  77                                         _conn.Close ();
  78                                 }
  79                         }
  80                 }
  81
  82                 [Test]
  83                 public void TypeOfNullInResultTest ()
  84                 {
  85                         _conn.ConnectionString = _connectionString;
  86                         SqliteDataReader reader = null;
  87                         using (_conn) {
  88                                 _conn.Open ();
  89                                 SqliteCommand cmd = (SqliteCommand) _conn.CreateCommand ();
  90                                 cmd.CommandText = "select null from test";
  91                                 reader = cmd.ExecuteReader ();
  92                                 try {
  93                                         Assert.IsTrue (reader.Read());
  94                                         Assert.IsNotNull (reader.GetFieldType (0));
  95                                 } finally {
  96                                         if (reader != null && !reader.IsClosed)
  97                                                 reader.Close ();
  98                                         _conn.Close ();
  99                                 }
 100                         }
 101                 }
 102
 103                 [Test]
 104                 public void TimestampTest ()
 105                 {
 106                         _conn.ConnectionString = _connectionString;
 107                         using (_conn) {
 108                                 _conn.Open ();
 109                                 var cmd = (SqliteCommand) _conn.CreateCommand ();
 110                                 cmd.CommandText = @"CREATE TABLE IF NOT EXISTS TestNullableDateTime (nullable TIMESTAMP NULL, dummy int); INSERT INTO TestNullableDateTime (nullable, dummy) VALUES (124123, 2);";
 111                                 cmd.ExecuteNonQuery ();
 112
 113                                 var query = "SELECT * FROM TestNullableDateTime;";
 114                                 cmd = (SqliteCommand) _conn.CreateCommand ();
 115                                 cmd.CommandText = query;
 116                                 cmd.CommandType = CommandType.Text;
 117
 118                                 using (var reader = cmd.ExecuteReader ()) {
 119                                         try {
 120                                                 var dt = reader ["nullable"];
 121                                                 Assert.Fail ("Expected: FormatException");
 122                                         } catch (FormatException ex) {
 123                                                 // expected this one
 124                                         }
 125                                 }
 126                         }
 127
 128                         _conn.ConnectionString = _connectionString + ",DateTimeFormat=UnixEpoch";
 129                         using (_conn) {
 130                                 _conn.Open ();
 131                                 var cmd = (SqliteCommand) _conn.CreateCommand ();
 132                                 cmd.CommandText = @"CREATE TABLE IF NOT EXISTS TestNullableDateTime (nullable TIMESTAMP NULL, dummy int); INSERT INTO TestNullableDateTime (nullable, dummy) VALUES (124123, 2);";
 133                                 cmd.ExecuteNonQuery ();
 134
 135                                 var query = "SELECT * FROM TestNullableDateTime;";
 136                                 cmd = (SqliteCommand) _conn.CreateCommand ();
 137                                 cmd.CommandText = query;
 138                                 cmd.CommandType = CommandType.Text;
 139
 140                                 using (var reader = cmd.ExecuteReader ()) {
 141                                         // this should succeed now
 142                                         var dt = reader ["nullable"];
 143                                 }
 144                         }
 145                 }
 146
 147                 [Test]
 148                 public void CloseConnectionTest ()
 149                 {
 150                         // When this test fails it may confuse nunit a bit, causing it to show strange
 151                         // exceptions, since it leaks file handles (and nunit tries to open files,
 152                         // which it doesn't expect to fail).
 153
 154                         // For the same reason a lot of other tests will fail when this one fails.
 155
 156                         _conn.ConnectionString = _connectionString;
 157                         using (_conn) {
 158                                 _conn.Open ();
 159                                 using (var cmd = (SqliteCommand) _conn.CreateCommand ()) {
 160                                         cmd.CommandText = @"CREATE TABLE IF NOT EXISTS TestNullableDateTime (nullable TIMESTAMP NULL, dummy int); INSERT INTO TestNullableDateTime (nullable, dummy) VALUES (124123, 2);";
 161                                         cmd.ExecuteNonQuery ();
 162                                 }
 163                         }
 164
 165                         for (int i = 0; i < 1000; i++) {
 166                                 _conn.ConnectionString = _connectionString;
 167                                 using (_conn) {
 168                                         _conn.Open ();
 169                                         using (var cmd = (SqliteCommand) _conn.CreateCommand ()) {
 170                                                 cmd.CommandText = "SELECT * FROM TestNullableDateTime;";
 171                                                 cmd.CommandType = CommandType.Text;
 172
 173                                                 using (var reader = cmd.ExecuteReader (CommandBehavior.CloseConnection)) {
 174                                                         reader.Read ();
 175                                                 }
 176                                         }
 177                                 }
 178                         }
 179                 }
 180
 181                 void AddParameter (System.Data.Common.DbCommand cm, string name, object value)
 182                 {
 183                         var param = cm.CreateParameter ();
 184                         param.ParameterName = ":" + name;
 185                         param.Value = value;
 186                         cm.Parameters.Add (param);
 187                 }
 188
 189                 class D
 190                 {
 191                         public string Sql;
 192                         public Type Expected;
 193                         public object Value;
 194                 }
 195
 196                 [Test]
 197                 public void TestDataTypes ()
 198                 {
 199                         SqliteParameter param;
 200
 201                         var data = new List<D> ()
 202                         {
 203                                 new D () { Sql = "DATETIME",            Expected = typeof (DateTime),   Value = DateTime.Now              },
 204                                 new D () { Sql = "GUIDBLOB NOT NULL",   Expected = typeof (Guid),       Value = new byte [] { 3, 14, 15 } },
 205                                 new D () { Sql = "BOOLEAN",             Expected = typeof (bool),       Value = true                      },
 206                                 new D () { Sql = "INT32",               Expected = typeof (long),       Value = 1                         },
 207                                 new D () { Sql = "INT32 NOT NULL",      Expected = typeof (long),       Value = 2                         },
 208                                 new D () { Sql = "UINT1",               Expected = typeof (long),       Value = 3                         },
 209
 210                                 // these map to the INTEGER affinity
 211                                 new D () { Sql = "INT",                 Expected = typeof (int),       Value = 4  },
 212                                 new D () { Sql = "INTEGER",             Expected = typeof (long),      Value = 5 },
 213                                 new D () { Sql = "TINYINT",             Expected = typeof (byte),      Value = 6 },
 214                                 new D () { Sql = "SMALLINT",            Expected = typeof (short),     Value = 7 },
 215                                 new D () { Sql = "MEDIUMINT",           Expected = typeof (long),      Value = 8 },
 216                                 new D () { Sql = "BIGINT",              Expected = typeof (long),      Value = 9 },
 217                                 new D () { Sql = "UNSIGNED BIG INT",    Expected = typeof (long),      Value = 0 },
 218                                 new D () { Sql = "INT2",                Expected = typeof (long),      Value = 1 },
 219                                 new D () { Sql = "INT4",                Expected = typeof (long),      Value = 2 },
 220
 221                                 // these map to the TEXT affinity
 222                                 new D () { Sql = "CHARACTER(20)",          Expected = typeof (string),       Value = "a" },
 223                                 new D () { Sql = "VARCHAR(255)",           Expected = typeof (string),       Value = "b" },
 224                                 new D () { Sql = "VARYING CHARACTER(255)", Expected = typeof (string),       Value = "c" },
 225                                 new D () { Sql = "NCHAR(55)",              Expected = typeof (string),       Value = "d" },
 226                                 new D () { Sql = "NATIVE CHARACTER(70)",   Expected = typeof (string),       Value = "e" },
 227                                 new D () { Sql = "NVARCHAR(100)",          Expected = typeof (string),       Value = "f" },
 228                                 new D () { Sql = "TEXT",                   Expected = typeof (string),       Value = "g" },
 229                                 new D () { Sql = "CLOB",                   Expected = typeof (string),       Value = "h" },
 230
 231                                 // these map to the NONE affinity
 232                                 new D () { Sql = "BLOB",           Expected = typeof (byte[]),       Value = new byte [] { 3, 14, 15 } },
 233                                 new D () { Sql = "",               Expected = typeof (object),       Value = null                      },
 234
 235                                 // these map to the REAL affinity
 236                                 new D () { Sql = "REAL",               Expected = typeof (float),        Value = 3.2 },
 237                                 new D () { Sql = "DOUBLE",             Expected = typeof (double),       Value = 4.2 },
 238                                 new D () { Sql = "DOUBLE PRECISION",   Expected = typeof (double),       Value = 5.2 },
 239                                 new D () { Sql = "FLOAT",              Expected = typeof (double),       Value = 6.2 },
 240
 241                                 // these map to the NUMERIC affinity
 242                                 new D () { Sql = "NUMERIC",            Expected = typeof (decimal),        Value = 3.2          },
 243                                 new D () { Sql = "DECIMAL(10,5)",      Expected = typeof (decimal),        Value = null         },
 244                                 new D () { Sql = "BOOLEAN",            Expected = typeof (bool),           Value = true         },
 245                                 new D () { Sql = "DATE",               Expected = typeof (DateTime),       Value = DateTime.Now },
 246                                 new D () { Sql = "DATETIME",           Expected = typeof (DateTime),       Value = DateTime.Now },
 247
 248
 249                         };
 250
 251                         _conn.ConnectionString = _connectionString;
 252                         using (_conn) {
 253                                 _conn.Open();
 254
 255                                 using (var cm = _conn.CreateCommand ()) {
 256                                         var sql = new StringBuilder ();
 257                                         var args = new StringBuilder ();
 258                                         var vals = new StringBuilder ();
 259                                         sql.AppendLine ("DROP TABLE TEST;");
 260                                         sql.Append ("CREATE TABLE TEST (");
 261
 262                                         bool comma = false;
 263                                         for (int i = 0; i < data.Count; i++) {
 264                                                 if (comma) {
 265                                                         sql.Append (", ");
 266                                                         args.Append (", ");
 267                                                         vals.Append (", ");
 268                                                 }
 269                                                 comma = true;
 270                                                 sql.AppendFormat ("F{0} {1}", i, data [i].Sql);
 271                                                 args.AppendFormat ("F{0}", i);
 272                                                 vals.AppendFormat (":F{0}", i);
 273                                                 AddParameter (cm, "F" + i.ToString (), data [i].Value);
 274                                         }
 275                                         sql.AppendLine (");");
 276                                         sql.Append ("INSERT INTO TEST (");
 277                                         sql.Append (args.ToString ());
 278                                         sql.Append (") VALUES (");
 279                                         sql.Append (vals.ToString ());
 280                                         sql.Append (");");
 281
 282                                         cm.CommandText = sql.ToString ();
 283
 284                                         cm.ExecuteNonQuery ();
 285                                 }
 286
 287                                 using (var cm = _conn.CreateCommand ()) {
 288                                         cm.CommandText = "SELECT * FROM TEST";
 289                                         using (var dr = cm.ExecuteReader ()) {
 290                                                 dr.Read ();
 291
 292                                                 for (int i = 0; i < data.Count; i++) {
 293                                                         string tn = data [i].Sql.Replace (" NOT NULL", "");
 294                                                         int index = dr.GetOrdinal ("F" + i.ToString ());
 295                                                         Assert.AreEqual (tn, dr.GetDataTypeName (index), "F" + i.ToString () + " (" + data [i].Sql + ")");
 296                                                         Assert.AreEqual (data [i].Expected.FullName, dr.GetFieldType (index).ToString (), "F" + i.ToString () + " (" + data [i].Sql + ")");
 297                                                 }
 298                                         }
 299                                 }
 300                         }
 301                 }
 302         }
 303 }