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 = Path.Combine (Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "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                         try
  48                         {
  49                                 using (SqliteCommand createCommand = new SqliteCommand("CREATE TABLE t1(t  TEXT,  f FLOAT, i INTEGER, b TEXT);", _conn))
  50                                 using (SqliteCommand insertCommand = new SqliteCommand("INSERT INTO t1  (t, f, i, b ) VALUES('" + stringvalue + "',123,123,'123')", _conn))
  51                                 {
  52                                         _conn.Open();
  53                                         createCommand.ExecuteNonQuery();
  54                                         insertCommand.ExecuteNonQuery();
  55                                 }
  56                         }
  57                         catch(Exception e)
  58                         {
  59                                 Console.WriteLine (e);
  60                                 throw new AssertionException("Create table failed",e);
  61                         }
  62                         finally
  63                         {
  64                                 _conn.Close();
  65                         }
  66                 }
  67
  68                 [Test]
  69                 public void Select()
  70                 {
  71                         using (_conn)
  72                         using (SqliteCommand simpleSelect = new SqliteCommand("SELECT * FROM t1;  ", _conn)) // check trailing spaces
  73                         {
  74                                 _conn.Open();
  75                                 using (SqliteDataReader dr = simpleSelect.ExecuteReader())
  76                                 {
  77                                         while (dr.Read())
  78                                         {
  79                                                 string test = dr[0].ToString();
  80                                                 Assert.AreEqual(dr["T"], stringvalue); // also checks case-insensitive column
  81                                                 Assert.AreEqual(dr["F"], 123);
  82                                                 Assert.AreEqual(dr["I"], 123);
  83                                                 Assert.AreEqual(dr["B"], "123");
  84                                         }
  85                                         Assert.IsTrue(dr.FieldCount>0);
  86                                 }
  87                         }
  88                 }
  89
  90                 [Test]
  91                 public void Delete()
  92                 {
  93                         using (_conn)
  94                         using (SqliteCommand insCmd = new SqliteCommand("INSERT INTO t1 VALUES ('todelete',0.1,0,'')", _conn))
  95                         using (SqliteCommand delCmd = new SqliteCommand("DELETE FROM t1 WHERE t = 'todelete'", _conn))
  96                         {
  97                                 _conn.Open();
  98                                 int insReturn = insCmd.ExecuteNonQuery();
  99                                 int delReturn = delCmd.ExecuteNonQuery();
 100
 101                                 Assert.IsTrue(insReturn == delReturn);
 102                         }
 103                 }
 104
 105                 [Test]
 106                 public void Insert()
 107                 {
 108                         using (_conn)
 109                         using (SqliteCommand insCmd = new SqliteCommand("INSERT INTO t1 VALUES ('inserted',0.1,0,'')", _conn))
 110                         {
 111                                 _conn.Open();
 112                                 int insReturn = insCmd.ExecuteNonQuery();
 113                                 Assert.IsTrue(insReturn == 1);
 114                         }
 115                 }
 116
 117                 [Test]
 118                 public void Update()
 119                 {
 120                         using (_conn)
 121                         using (SqliteCommand insCmd = new SqliteCommand("INSERT INTO t1 VALUES ('toupdate',0.1,0,'')", _conn))
 122                         using (SqliteCommand updCmd = new SqliteCommand("UPDATE t1 SET t = 'updated' ,f = 2.0, i = 2, b = '' WHERE t = 'toupdate'", _conn))
 123                         {
 124                                 _conn.Open();
 125                                 insCmd.ExecuteNonQuery();
 126                                 Assert.IsTrue(updCmd.ExecuteNonQuery() == 1);
 127                         }
 128                 }
 129
 130
 131                 [Test]
 132                 public void ScalarReturn()
 133                 {
 134                         // This should return the 1 line that got inserted in CreateTable() Test
 135                         using (_conn)
 136                         using (SqliteCommand cmd = new SqliteCommand("SELECT COUNT(*) FROM t1 WHERE  t LIKE '%äöüß'", _conn))
 137                         {
 138                                 _conn.Open();
 139                                 Assert.AreEqual(1, Convert.ToInt32(cmd.ExecuteScalar()));
 140                         }
 141                 }
 142
 143                 [Test]
 144                 public void InsertWithTransaction()
 145                 {
 146                         _conn.Open();
 147                         using (_conn)
 148                         using (SqliteTransaction t = _conn.BeginTransaction() as SqliteTransaction)
 149                         using (SqliteCommand c1 = new SqliteCommand("INSERT INTO t1 VALUES ('a',0.1,0,'0')", _conn, t))
 150                         using (SqliteCommand c2 = new SqliteCommand("INSERT INTO t1 VALUES ('b',1.2,0,'0')", _conn, t))
 151                         using (SqliteCommand c3 = new SqliteCommand("INSERT INTO t1 VALUES ('c',0.3,1,'0')", _conn, t))
 152                         using (SqliteCommand c4 = new SqliteCommand("INSERT INTO t1 VALUES ('d',0.4,0,'1')", _conn, t))
 153                         {
 154                                 try
 155                                 {
 156                                         c1.ExecuteNonQuery();
 157                                         c2.ExecuteNonQuery();
 158                                         c3.ExecuteNonQuery();
 159                                         c4.ExecuteNonQuery();
 160                                         t.Commit();
 161                                 }
 162                                 catch(Exception e)
 163                                 {
 164                                         t.Rollback();
 165                                         throw new AssertionException("Sqlite Commands failed", e);
 166                                 }
 167                         }
 168                 }
 169
 170                 [Test]
 171 #if NET_2_0
 172                 [ExpectedException(typeof(SqliteException))]
 173 #else
 174                 [ExpectedException(typeof(SqliteSyntaxException))]
 175 #endif
 176                 public void InsertWithFailingTransaction()
 177                 {
 178                         _conn.Open();
 179                         using (_conn)
 180                         using (SqliteTransaction t = _conn.BeginTransaction() as SqliteTransaction)
 181                         using (SqliteCommand c1 = new SqliteCommand("INSERT INTO t1 VALUES ('1','0','0','0')", _conn, t))
 182                         using (SqliteCommand c2 = new SqliteCommand("INSERT INTO t1 VALUES ('0','1','0','0')", _conn, t))
 183                         using (SqliteCommand c3 = new SqliteCommand("INSERT INTO t1 VALUES ('x',?,'x',?,'x',?,'x')", _conn, t))
 184                         using (SqliteCommand c4 = new SqliteCommand("INSERT INTO t1 VALUES ('0','0','0','1')", _conn, t))
 185                         {
 186                                 try
 187                                 {
 188                                         c1.ExecuteNonQuery();
 189                                         c2.ExecuteNonQuery();
 190                                         c3.ExecuteNonQuery();
 191                                         c4.ExecuteNonQuery();
 192                                         t.Commit();
 193                                 }
 194                                 catch(Exception e)
 195                                 {
 196                                         t.Rollback();
 197                                         throw e;
 198                                 }
 199                         }
 200                 }
 201         }
 202 }