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

   1 //
   2 // Unit test for bug https://bugzilla.xamarin.com/show_bug.cgi?id=27864
   3 //
   4 // Authors:
   5 //      Thomas Zoechling <thomas.zoechling@gmx.at>
   6 //      Alex Soto <alex.soto@xamarin.com>
   7 //
   8 //
   9 // Copyright 2015 Xamarin Inc.
  10 //
  11
  12 #if MONOTOUCH
  13 using System;
  14 using System.Data;
  15 using System.IO;
  16 using System.Text;
  17 using Mono.Data.Sqlite;
  18 using NUnit.Framework;
  19
  20 namespace MonoTests.Mono.Data.Sqlite {
  21
  22         [TestFixture]
  23         public class SqliteiOS82BugTests {
  24                 readonly static string dbPath = Path.Combine (Environment.GetFolderPath (Environment.SpecialFolder.Personal), "adodemo.db3");
  25                 readonly static string connectionString = "Data Source=" + dbPath;
  26                 static SqliteConnection cnn = new SqliteConnection (connectionString);
  27
  28                 [SetUp]
  29                 public void Create()
  30                 {
  31                         try {
  32                                 if(File.Exists(dbPath)) {
  33                                         cnn.Dispose();
  34                                         // We want to start with a fresh db for each full run
  35                                         // The database is created on the first open()
  36                                         File.Delete(dbPath);
  37                                 }
  38                         }
  39                         catch(Exception e) {
  40                                 throw e;
  41                         }
  42
  43                         try {
  44                                 using (var createCommand = new SqliteCommand ("CREATE TABLE Company (RecordId int, Name text);", cnn))
  45                                 using (var insertCommand = new SqliteCommand ("INSERT INTO Company VALUES (1, 'Test CO')", cnn)) {
  46                                         cnn.Open();
  47                                         createCommand.ExecuteNonQuery();
  48                                         insertCommand.ExecuteNonQuery();
  49                                 }
  50                         }
  51                         catch(Exception e) {
  52                                 Console.WriteLine (e);
  53                                 throw new AssertionException ("Create table failed", e);
  54                         }
  55                         finally {
  56                                 cnn.Close();
  57                         }
  58                 }
  59
  60                 // Ref: https://bugzilla.xamarin.com/show_bug.cgi?id=27864
  61                 // As of iOS 8.2 Apple updated Sqlite and broke queries that used to work pre iOS 8.2 like the select command in this test
  62                 // The pruppose of this test is to know when apple fixes this. Expected test behaivour is as follows.
  63                 // If iOS 8.2+ Test will pass as long as apple does not fix the bug
  64                 // If iOS < 8.2 Test will pass as it does work as expected
  65                 // If iOS 8.2+ and apple fixed bug 27864 test will fail and we will need to remove the fail check on lines 105 and 106
  66                 [Test]
  67                 public void SqliteSelectTestBug27864()
  68                 {
  69                         try {
  70                                 var cmdText = "SELECT " +
  71                                         "2 AS SortOrder, " +
  72                                         "com.Name AS 'Company.Name' " +
  73                                         "FROM " +
  74                                         "company com " +
  75                                         "UNION " +
  76                                         "SELECT " +
  77                                         "0 AS SortOrder, " +
  78                                         "com.Name AS 'Company.Name' " +
  79                                         "FROM " +
  80                                         "Company com " +
  81                                         "ORDER BY " +
  82                                         "com.Name, " +
  83                                         "SortOrder COLLATE NOCASE";
  84
  85                                 using (cnn)
  86                                 using (var cmd = new SqliteCommand (cmdText, cnn)) {
  87                                         cnn.Open();
  88                                         using (SqliteDataReader dr = cmd.ExecuteReader()) {
  89                                                 var i = 0;
  90                                                 while (dr.Read()) {
  91                                                         Assert.AreEqual(dr["SortOrder"], i);
  92                                                         Assert.AreEqual(dr["Company.Name"], "Test CO");
  93                                                         i += 2;
  94                                                 }
  95                                                 Assert.IsTrue(dr.FieldCount>0, i.ToString ());
  96                                         }
  97                                 }
  98                         } catch (SqliteException ex) {
  99                                 // Expected Exception from iOS 8.2 (broken) to 9.0 (fixed)
 100                                 if (BCL.Tests.TestRuntime.CheckSystemVersion (8,2) && !BCL.Tests.TestRuntime.CheckSystemVersion (9,0))
 101                                         Assert.That (ex.Message.Contains ("no such column: com.Name"));
 102                                 else
 103                                         throw new AssertionException ("Unexpected Sqlite Error", ex); // This should not happen
 104                         }
 105                 }
 106         }
 107 }
 108 #endif