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 using System;
  13 using System.Data;
  14 using System.IO;
  15 using System.Text;
  16 using Mono.Data.Sqlite;
  17 using NUnit.Framework;
  18
  19 namespace MonoTests.Mono.Data.Sqlite {
  20
  21         [TestFixture]
  22         public class SqliteiOS82BugTests {
  23                 readonly static string dbPath = Path.Combine (Environment.GetFolderPath (Environment.SpecialFolder.Personal), "adodemo.db3");
  24                 readonly static string connectionString = "Data Source=" + dbPath;
  25                 static SqliteConnection cnn = new SqliteConnection (connectionString);
  26
  27                 [SetUp]
  28                 public void Create()
  29                 {
  30                         try {
  31                                 if(File.Exists(dbPath)) {
  32                                         cnn.Dispose();
  33                                         // We want to start with a fresh db for each full run
  34                                         // The database is created on the first open()
  35                                         File.Delete(dbPath);
  36                                 }
  37                         }
  38                         catch(Exception e) {
  39                                 throw e;
  40                         }
  41
  42                         try {
  43                                 using (var createCommand = new SqliteCommand ("CREATE TABLE Company (RecordId int, Name text);", cnn))
  44                                 using (var insertCommand = new SqliteCommand ("INSERT INTO Company VALUES (1, 'Test CO')", cnn)) {
  45                                         cnn.Open();
  46                                         createCommand.ExecuteNonQuery();
  47                                         insertCommand.ExecuteNonQuery();
  48                                 }
  49                         }
  50                         catch(Exception e) {
  51                                 Console.WriteLine (e);
  52                                 throw new AssertionException ("Create table failed", e);
  53                         }
  54                         finally {
  55                                 cnn.Close();
  56                         }
  57                 }
  58
  59                 // Ref: https://bugzilla.xamarin.com/show_bug.cgi?id=27864
  60                 // 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
  61                 // The pruppose of this test is to know when apple fixes this. Expected test behaivour is as follows.
  62                 // If iOS 8.2+ Test will pass as long as apple does not fix the bug
  63                 // If iOS < 8.2 Test will pass as it does work as expected
  64                 // 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
  65                 [Test]
  66                 public void SqliteSelectTestBug27864()
  67                 {
  68                         try {
  69                                 var cmdText = "SELECT " +
  70                                         "2 AS SortOrder, " +
  71                                         "com.Name AS 'Company.Name' " +
  72                                         "FROM " +
  73                                         "company com " +
  74                                         "UNION " +
  75                                         "SELECT " +
  76                                         "0 AS SortOrder, " +
  77                                         "com.Name AS 'Company.Name' " +
  78                                         "FROM " +
  79                                         "Company com " +
  80                                         "ORDER BY " +
  81                                         "com.Name, " +
  82                                         "SortOrder COLLATE NOCASE";
  83
  84                                 using (cnn)
  85                                 using (var cmd = new SqliteCommand (cmdText, cnn)) {
  86                                         cnn.Open();
  87                                         using (SqliteDataReader dr = cmd.ExecuteReader()) {
  88                                                 var i = 0;
  89                                                 while (dr.Read()) {
  90                                                         Assert.AreEqual(dr["SortOrder"], i);
  91                                                         Assert.AreEqual(dr["Company.Name"], "Test CO");
  92                                                         i += 2;
  93                                                 }
  94                                                 Assert.IsTrue(dr.FieldCount>0, i.ToString ());
  95                                         }
  96                                 }
  97                         } catch (SqliteException ex) {
  98 #if MONOTOUCH
  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;
 104 #endif
 105                         }
 106                 }
 107         }
 108 }