using System;
+using System.Collections.Generic;
using System.Data;
using System.IO;
+using System.Text;
using Mono.Data.Sqlite;
using NUnit.Framework;
[Test]
+ [Category ("NotWorking")]
public void GetSchemaTableTest ()
{
_conn.ConnectionString = _connectionString;
}
}
}
+
+ [Test]
+ public void TimestampTest ()
+ {
+ _conn.ConnectionString = _connectionString;
+ using (_conn) {
+ _conn.Open ();
+ var cmd = (SqliteCommand) _conn.CreateCommand ();
+ cmd.CommandText = @"CREATE TABLE IF NOT EXISTS TestNullableDateTime (nullable TIMESTAMP NULL, dummy int); INSERT INTO TestNullableDateTime (nullable, dummy) VALUES (124123, 2);";
+ cmd.ExecuteNonQuery ();
+
+ var query = "SELECT * FROM TestNullableDateTime;";
+ cmd = (SqliteCommand) _conn.CreateCommand ();
+ cmd.CommandText = query;
+ cmd.CommandType = CommandType.Text;
+
+ using (var reader = cmd.ExecuteReader ()) {
+ try {
+ var dt = reader ["nullable"];
+ Assert.Fail ("Expected: FormatException");
+ } catch (FormatException ex) {
+ // expected this one
+ }
+ }
+ }
+
+ _conn.ConnectionString = _connectionString + ",DateTimeFormat=UnixEpoch";
+ using (_conn) {
+ _conn.Open ();
+ var cmd = (SqliteCommand) _conn.CreateCommand ();
+ cmd.CommandText = @"CREATE TABLE IF NOT EXISTS TestNullableDateTime (nullable TIMESTAMP NULL, dummy int); INSERT INTO TestNullableDateTime (nullable, dummy) VALUES (124123, 2);";
+ cmd.ExecuteNonQuery ();
+
+ var query = "SELECT * FROM TestNullableDateTime;";
+ cmd = (SqliteCommand) _conn.CreateCommand ();
+ cmd.CommandText = query;
+ cmd.CommandType = CommandType.Text;
+
+ using (var reader = cmd.ExecuteReader ()) {
+ // this should succeed now
+ var dt = reader ["nullable"];
+ }
+ }
+ }
+
+ [Test]
+ public void CloseConnectionTest ()
+ {
+ // When this test fails it may confuse nunit a bit, causing it to show strange
+ // exceptions, since it leaks file handles (and nunit tries to open files,
+ // which it doesn't expect to fail).
+
+ // For the same reason a lot of other tests will fail when this one fails.
+
+ _conn.ConnectionString = _connectionString;
+ using (_conn) {
+ _conn.Open ();
+ using (var cmd = (SqliteCommand) _conn.CreateCommand ()) {
+ cmd.CommandText = @"CREATE TABLE IF NOT EXISTS TestNullableDateTime (nullable TIMESTAMP NULL, dummy int); INSERT INTO TestNullableDateTime (nullable, dummy) VALUES (124123, 2);";
+ cmd.ExecuteNonQuery ();
+ }
+ }
+
+ for (int i = 0; i < 1000; i++) {
+ _conn.ConnectionString = _connectionString;
+ using (_conn) {
+ _conn.Open ();
+ using (var cmd = (SqliteCommand) _conn.CreateCommand ()) {
+ cmd.CommandText = "SELECT * FROM TestNullableDateTime;";
+ cmd.CommandType = CommandType.Text;
+
+ using (var reader = cmd.ExecuteReader (CommandBehavior.CloseConnection)) {
+ reader.Read ();
+ }
+ }
+ }
+ }
+ }
+
+ void AddParameter (global::System.Data.Common.DbCommand cm, string name, object value)
+ {
+ var param = cm.CreateParameter ();
+ param.ParameterName = ":" + name;
+ param.Value = value;
+ cm.Parameters.Add (param);
+ }
+
+ class D
+ {
+ public string Sql;
+ public Type Expected;
+ public object Value;
+ }
+
+ [Test]
+ public void TestDataTypes ()
+ {
+ SqliteParameter param;
+
+ var data = new List<D> ()
+ {
+ new D () { Sql = "DATETIME", Expected = typeof (DateTime), Value = DateTime.Now },
+ new D () { Sql = "GUIDBLOB NOT NULL", Expected = typeof (Guid), Value = new byte [] { 3, 14, 15 } },
+ new D () { Sql = "BOOLEAN", Expected = typeof (bool), Value = true },
+ new D () { Sql = "INT32", Expected = typeof (long), Value = 1 },
+ new D () { Sql = "INT32 NOT NULL", Expected = typeof (long), Value = 2 },
+ new D () { Sql = "UINT1", Expected = typeof (long), Value = 3 },
+
+ // these map to the INTEGER affinity
+ new D () { Sql = "INT", Expected = typeof (int), Value = 4 },
+ new D () { Sql = "INTEGER", Expected = typeof (long), Value = 5 },
+ new D () { Sql = "TINYINT", Expected = typeof (byte), Value = 6 },
+ new D () { Sql = "SMALLINT", Expected = typeof (short), Value = 7 },
+ new D () { Sql = "MEDIUMINT", Expected = typeof (long), Value = 8 },
+ new D () { Sql = "BIGINT", Expected = typeof (long), Value = 9 },
+ new D () { Sql = "UNSIGNED BIG INT", Expected = typeof (long), Value = 0 },
+ new D () { Sql = "INT2", Expected = typeof (long), Value = 1 },
+ new D () { Sql = "INT4", Expected = typeof (long), Value = 2 },
+
+ // these map to the TEXT affinity
+ new D () { Sql = "CHARACTER(20)", Expected = typeof (string), Value = "a" },
+ new D () { Sql = "VARCHAR(255)", Expected = typeof (string), Value = "b" },
+ new D () { Sql = "VARYING CHARACTER(255)", Expected = typeof (string), Value = "c" },
+ new D () { Sql = "NCHAR(55)", Expected = typeof (string), Value = "d" },
+ new D () { Sql = "NATIVE CHARACTER(70)", Expected = typeof (string), Value = "e" },
+ new D () { Sql = "NVARCHAR(100)", Expected = typeof (string), Value = "f" },
+ new D () { Sql = "TEXT", Expected = typeof (string), Value = "g" },
+ new D () { Sql = "CLOB", Expected = typeof (string), Value = "h" },
+
+ // these map to the NONE affinity
+ new D () { Sql = "BLOB", Expected = typeof (byte[]), Value = new byte [] { 3, 14, 15 } },
+ new D () { Sql = "", Expected = typeof (object), Value = null },
+
+ // these map to the REAL affinity
+ new D () { Sql = "REAL", Expected = typeof (float), Value = 3.2 },
+ new D () { Sql = "DOUBLE", Expected = typeof (double), Value = 4.2 },
+ new D () { Sql = "DOUBLE PRECISION", Expected = typeof (double), Value = 5.2 },
+ new D () { Sql = "FLOAT", Expected = typeof (double), Value = 6.2 },
+
+ // these map to the NUMERIC affinity
+ new D () { Sql = "NUMERIC", Expected = typeof (decimal), Value = 3.2 },
+ new D () { Sql = "DECIMAL(10,5)", Expected = typeof (decimal), Value = null },
+ new D () { Sql = "BOOLEAN", Expected = typeof (bool), Value = true },
+ new D () { Sql = "DATE", Expected = typeof (DateTime), Value = DateTime.Now },
+ new D () { Sql = "DATETIME", Expected = typeof (DateTime), Value = DateTime.Now },
+
+
+ };
+
+ _conn.ConnectionString = _connectionString;
+ using (_conn) {
+ _conn.Open();
+
+ using (var cm = _conn.CreateCommand ()) {
+ var sql = new StringBuilder ();
+ var args = new StringBuilder ();
+ var vals = new StringBuilder ();
+ sql.AppendLine ("DROP TABLE TEST;");
+ sql.Append ("CREATE TABLE TEST (");
+
+ bool comma = false;
+ for (int i = 0; i < data.Count; i++) {
+ if (comma) {
+ sql.Append (", ");
+ args.Append (", ");
+ vals.Append (", ");
+ }
+ comma = true;
+ sql.AppendFormat ("F{0} {1}", i, data [i].Sql);
+ args.AppendFormat ("F{0}", i);
+ vals.AppendFormat (":F{0}", i);
+ AddParameter (cm, "F" + i.ToString (), data [i].Value);
+ }
+ sql.AppendLine (");");
+ sql.Append ("INSERT INTO TEST (");
+ sql.Append (args.ToString ());
+ sql.Append (") VALUES (");
+ sql.Append (vals.ToString ());
+ sql.Append (");");
+
+ cm.CommandText = sql.ToString ();
+
+ cm.ExecuteNonQuery ();
+ }
+
+ using (var cm = _conn.CreateCommand ()) {
+ cm.CommandText = "SELECT * FROM TEST";
+ using (var dr = cm.ExecuteReader ()) {
+ dr.Read ();
+
+ for (int i = 0; i < data.Count; i++) {
+ string tn = data [i].Sql.Replace (" NOT NULL", "");
+ int index = dr.GetOrdinal ("F" + i.ToString ());
+ Assert.AreEqual (tn, dr.GetDataTypeName (index), "F" + i.ToString () + " (" + data [i].Sql + ")");
+ Assert.AreEqual (data [i].Expected.FullName, dr.GetFieldType (index).ToString (), "F" + i.ToString () + " (" + data [i].Sql + ")");
+ }
+ }
+ }
+ }
+ }
}
}