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

   1 // SqliteDataAdapterUnitTests.cs - NUnit Test Cases for Mono.Data.Sqlite.SqliteDataAdapter
   2 //
   3 // Author(s):   Thomas Zoechling <thomas.zoechling@gmx.at>
   4
   5
   6 using System;
   7 using System.Data;
   8 using System.IO;
   9 using System.Text;
  10 using Mono.Data.Sqlite;
  11 using NUnit.Framework;
  12
  13 namespace MonoTests.Mono.Data.Sqlite
  14 {
  15
  16         [TestFixture]
  17         public class SqliteCommandUnitTests
  18         {
  19                 readonly static string _uri = "SqliteTest.db";
  20                 readonly static string _connectionString = "URI=file://" + _uri + ", version=3";
  21                 static SqliteConnection _conn = new SqliteConnection (_connectionString);
  22                 readonly static string stringvalue = "my keyboard is better than yours : äöüß";
  23
  24                 public SqliteCommandUnitTests()
  25                 {
  26                 }
  27
  28                 [SetUp]
  29                 public void Create()
  30                 {
  31                         try
  32                         {
  33                                 if(File.Exists(_uri))
  34                                 {
  35                                         _conn.Dispose();
  36                                         // We want to start with a fresh db for each full run
  37                                         // The database is created on the first open()
  38                                         File.Delete(_uri);
  39
  40                                 }
  41                         }
  42                         catch(Exception e)
  43                         {
  44                                 throw e;
  45                         }
  46
  47                         SqliteCommand createCommand = new SqliteCommand("CREATE TABLE t1(t  TEXT,  f FLOAT, i INTEGER, b TEXT);",_conn);
  48                         SqliteCommand insertCommand = new SqliteCommand("INSERT INTO t1  (t, f, i, b ) VALUES('" + stringvalue + "',123,123,'123')",_conn);
  49
  50                         try
  51                         {
  52                                 _conn.Open();
  53                                 createCommand.ExecuteNonQuery();
  54                                 insertCommand.ExecuteNonQuery();
  55                         }
  56                         catch(Exception e)
  57                         {
  58                                 Console.WriteLine (e);
  59                                 throw new AssertionException("Create table failed",e);
  60                         }
  61                         finally
  62                         {
  63                                 _conn.Close();
  64                         }
  65                 }
  66
  67                 [Test]
  68                 public void Select()
  69                 {
  70                         SqliteCommand simpleSelect = new SqliteCommand("SELECT * FROM t1;  ", _conn); // check trailing spaces
  71                         using(_conn)
  72                         {
  73                                 _conn.Open();
  74                                 SqliteDataReader dr = simpleSelect.ExecuteReader();
  75                                 while(dr.Read())
  76                                 {
  77                                         string test = dr[0].ToString();
  78                                         Assert.AreEqual(dr["T"], stringvalue); // also checks case-insensitive column
  79                                         Assert.AreEqual(dr["F"], 123);
  80                                         Assert.AreEqual(dr["I"], 123);
  81                                         Assert.AreEqual(dr["B"], "123");
  82                                 }
  83                                 Assert.IsTrue(dr.FieldCount>0);
  84                         }
  85                 }
  86
  87                 [Test]
  88                 public void Delete()
  89                 {
  90                         SqliteCommand insCmd = new SqliteCommand("INSERT INTO t1 VALUES ('todelete',0.1,0,'')",_conn);
  91                         SqliteCommand delCmd = new SqliteCommand("DELETE FROM t1 WHERE t = 'todelete'",_conn);
  92                         using(_conn)
  93                         {
  94                                 _conn.Open();
  95                                 int insReturn = insCmd.ExecuteNonQuery();
  96                                 int delReturn = delCmd.ExecuteNonQuery();
  97
  98                                 Assert.IsTrue(insReturn == delReturn);
  99                         }
 100                 }
 101
 102                 [Test]
 103                 public void Insert()
 104                 {
 105                         SqliteCommand insCmd = new SqliteCommand("INSERT INTO t1 VALUES ('inserted',0.1,0,'')",_conn);
 106                         using(_conn)
 107                         {
 108                                 _conn.Open();
 109                                 int insReturn = insCmd.ExecuteNonQuery();
 110                                 Assert.IsTrue(insReturn == 1);
 111                         }
 112                 }
 113
 114                 [Test]
 115                 public void Update()
 116                 {
 117                         SqliteCommand insCmd = new SqliteCommand("INSERT INTO t1 VALUES ('toupdate',0.1,0,'')",_conn);
 118                         SqliteCommand updCmd = new SqliteCommand("UPDATE t1 SET t = 'updated' ,f = 2.0, i = 2, b = '' WHERE t = 'toupdate'",_conn);
 119                         using(_conn)
 120                         {
 121                                 _conn.Open();
 122                                 insCmd.ExecuteNonQuery();
 123                                 Assert.IsTrue(updCmd.ExecuteNonQuery() == 1);
 124                         }
 125                 }
 126
 127
 128                 [Test]
 129                 public void ScalarReturn()
 130                 {
 131                         // This should return the 1 line that got inserted in CreateTable() Test
 132                         SqliteCommand cmd = new SqliteCommand("SELECT COUNT(*) FROM t1 WHERE  t LIKE '%äöüß'",_conn);
 133                         using(_conn)
 134                         {
 135                                 _conn.Open();
 136                                 Assert.AreEqual(1, Convert.ToInt32(cmd.ExecuteScalar()));
 137                         }
 138                 }
 139
 140                 [Test]
 141                 public void InsertWithTransaction()
 142                 {
 143                         _conn.Open();
 144                         SqliteTransaction t = _conn.BeginTransaction() as SqliteTransaction;
 145                         SqliteCommand  c1 = new SqliteCommand("INSERT INTO t1 VALUES ('a',0.1,0,'0')",_conn,t);
 146                         SqliteCommand  c2 = new SqliteCommand("INSERT INTO t1 VALUES ('b',1.2,0,'0')",_conn,t);
 147                         SqliteCommand  c3 = new SqliteCommand("INSERT INTO t1 VALUES ('c',0.3,1,'0')",_conn,t);
 148                         SqliteCommand  c4 = new SqliteCommand("INSERT INTO t1 VALUES ('d',0.4,0,'1')",_conn,t);
 149                         using(_conn)
 150                         {
 151                                 try
 152                                 {
 153                                         c1.ExecuteNonQuery();
 154                                         c2.ExecuteNonQuery();
 155                                         c3.ExecuteNonQuery();
 156                                         c4.ExecuteNonQuery();
 157                                         t.Commit();
 158                                 }
 159                                 catch(Exception e)
 160                                 {
 161                                         t.Rollback();
 162                                         throw new AssertionException("Sqlite Commands failed", e);
 163                                 }
 164                         }
 165                 }
 166
 167                 [Test]
 168 #if NET_2_0
 169                 [ExpectedException(typeof(SqliteException))]
 170 #else
 171                 [ExpectedException(typeof(SqliteSyntaxException))]
 172 #endif
 173                 public void InsertWithFailingTransaction()
 174                 {
 175                         _conn.Open();
 176                         SqliteTransaction t = _conn.BeginTransaction() as SqliteTransaction;
 177                         SqliteCommand  c1 = new SqliteCommand("INSERT INTO t1 VALUES ('1','0','0','0')",_conn,t);
 178                         SqliteCommand  c2 = new SqliteCommand("INSERT INTO t1 VALUES ('0','1','0','0')",_conn,t);
 179                         SqliteCommand  c3 = new SqliteCommand("INSERT INTO t1 VALUES ('x',?,'x',?,'x',?,'x')",_conn,t);
 180                         SqliteCommand  c4 = new SqliteCommand("INSERT INTO t1 VALUES ('0','0','0','1')",_conn,t);
 181                         using(_conn)
 182                         {
 183                                 try
 184                                 {
 185                                         c1.ExecuteNonQuery();
 186                                         c2.ExecuteNonQuery();
 187                                         c3.ExecuteNonQuery();
 188                                         c4.ExecuteNonQuery();
 189                                         t.Commit();
 190                                 }
 191                                 catch(Exception e)
 192                                 {
 193                                         t.Rollback();
 194                                         throw e;
 195                                 }
 196                         }
 197                 }
 198         }
 199 }