System.Data.SQLite
Artifact Content
Not logged in

Artifact 02cc0eca4f13055a3c4c991ecd3e2daa81020821:


     1  ###############################################################################
     2  #
     3  # tkt-343d392b51.eagle --
     4  #
     5  # Written by Joe Mistachkin.
     6  # Released to the public domain, use at your own risk!
     7  #
     8  ###############################################################################
     9  
    10  package require Eagle
    11  package require Eagle.Library
    12  package require Eagle.Test
    13  
    14  runTestPrologue
    15  
    16  ###############################################################################
    17  
    18  package require System.Data.SQLite.Test
    19  runSQLiteTestPrologue
    20  
    21  ###############################################################################
    22  
    23  set dateTimeFormats [list "" Ticks ISO8601 JulianDay UnixEpoch]
    24  
    25  for {set i 1} {$i < 5} {incr i} {
    26    set dateTimeFormat [lindex $dateTimeFormats $i]
    27  
    28    runTest {test [appendArgs tkt-343d392b51-1. $i] [subst {DateTime\
    29        binding $dateTimeFormat format}] -setup {
    30      setupDb [set fileName [appendArgs tkt-343d392b51-1. $i .db]] "" \
    31          $dateTimeFormat Utc
    32  
    33      set dateTime "4 October, 2011 3:27:50 PM GMT"
    34    } -body {
    35      sql execute $db "CREATE TABLE t1(x DATETIME);"
    36  
    37      set paramDateTime1 [clock format [clock scan $dateTime] -format \
    38          [getDateTimeFormat] -gmt true]
    39  
    40      switch -exact -- $dateTimeFormat {
    41        Ticks {
    42          set paramDateTime1 [object invoke \
    43              -create -alias DateTime Parse $paramDateTime1]
    44  
    45          set paramDateTime1 [$paramDateTime1 ToUniversalTime.Ticks]
    46          set paramDateTime2 $paramDateTime1
    47        }
    48        ISO8601 {
    49          set paramDateTime2 [appendArgs ' $paramDateTime1 ']
    50        }
    51        JulianDay {
    52          set paramDateTime1 [object invoke \
    53              -create -alias DateTime Parse $paramDateTime1]
    54  
    55          set paramDateTime1 [$paramDateTime1 -create -alias ToUniversalTime]
    56  
    57          set paramDateTime1 [expr {[$paramDateTime1 ToOADate] + \
    58              [object invoke -flags +NonPublic System.Data.SQLite.SQLiteConvert \
    59              OleAutomationEpochAsJulianDay]}]
    60  
    61          set paramDateTime2 $paramDateTime1
    62        }
    63        UnixEpoch {
    64          set paramDateTime1 [clock scan $dateTime]
    65          set paramDateTime2 $paramDateTime1
    66        }
    67      }
    68  
    69      sql execute $db [appendArgs "INSERT INTO t1 (x) VALUES(" $paramDateTime2 \
    70          ");"]
    71  
    72      list [sql execute -verbatim -execute reader -format list -datetimeformat \
    73          [getDateTimeFormat] $db "SELECT x FROM t1 WHERE x = ?;" \
    74          [list param1 String $paramDateTime1]] \
    75          [sql execute -verbatim -execute reader -format list -datetimeformat \
    76          [getDateTimeFormat] $db "SELECT x FROM t1 WHERE x = ?;" \
    77          [list param1 DateTime $paramDateTime1]]
    78    } -cleanup {
    79      cleanupDb $fileName
    80  
    81      unset -nocomplain paramDateTime2 paramDateTime1 dateTime db fileName
    82    } -constraints {eagle command.object threadCulture.en_US monoBug28\
    83  command.sql compile.DATA SQLite System.Data.SQLite} -result \
    84  {{{2011-10-04 15:27:50Z}} {{2011-10-04 15:27:50Z}}}}
    85  }
    86  
    87  ###############################################################################
    88  
    89  unset -nocomplain dateTimeFormat i dateTimeFormats
    90  
    91  ###############################################################################
    92  
    93  runTest {test tkt-343d392b51-2.1 {SQLiteDataAdapter update fail} -setup {
    94    setupDb [set fileName tkt-343d392b51-2.1.db]
    95    set otherFileName tkt-343d392b51-2.1-otherDb.db
    96  } -body {
    97    set id [object invoke Interpreter.GetActive NextId]
    98    set dataSource [file join [getDatabaseDirectory] $fileName]
    99    set otherDataSource [file join [getDatabaseDirectory] $otherFileName]
   100    set otherDbName otherDb
   101    set otherTable [appendArgs $otherDbName .t1]
   102  
   103    set sql(inserts) ""
   104    set sql(1) [subst { \
   105      ATTACH DATABASE '${otherDataSource}' AS ${otherDbName}; \
   106      CREATE TABLE ${otherTable}(x INTEGER PRIMARY KEY, y DATETIME); \
   107      [for {set i 1} {$i < 3} {incr i} {
   108        append sql(inserts) [appendArgs \
   109            "INSERT INTO " ${otherTable} " (x, y) VALUES(" $i ", '" \
   110            [clock format $i -format [getDateTimeFormat]] "'); "]
   111      }; return [expr {[info exists sql(inserts)] ? $sql(inserts) : ""}]] \
   112    }]
   113  
   114    set sql(2) [subst { \
   115      SELECT x, y FROM ${otherTable} ORDER BY x; \
   116    }]
   117  
   118    unset -nocomplain results errors
   119  
   120    set code [compileCSharpWith [subst {
   121      using System;
   122      using System.Data;
   123      using System.Data.SQLite;
   124  
   125      namespace _Dynamic${id}
   126      {
   127        public static class Test${id}
   128        {
   129          public static void Main()
   130          {
   131            using (SQLiteConnection connection = new SQLiteConnection(
   132                "Data Source=${dataSource};[getTestProperties]"))
   133            {
   134              connection.Open();
   135  
   136              using (SQLiteCommand command = connection.CreateCommand())
   137              {
   138                command.CommandText = "${sql(1)}";
   139                command.ExecuteNonQuery();
   140              }
   141  
   142              using (SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(
   143                  "${sql(2)}", connection))
   144              {
   145                using (DataSet dataSet = new DataSet())
   146                {
   147                  dataAdapter.Fill(dataSet, "${otherTable}");
   148  
   149                  DataTable dataTable = dataSet.Tables\["${otherTable}"\];
   150  
   151                  dataTable.Columns\["x"\].Unique = true;
   152                  dataTable.PrimaryKey = new DataColumn\[\] {
   153                    dataTable.Columns\["x"\]
   154                  };
   155  
   156                  [expr {[isMono] ? "#pragma warning disable 219" : ""}]
   157                  SQLiteCommandBuilder commandBuilder =
   158                      new SQLiteCommandBuilder(dataAdapter);
   159                  [expr {[isMono] ? "#pragma warning restore 219" : ""}]
   160  
   161                  foreach (DataRow dataRow in dataTable.Rows)
   162                  {
   163                    //
   164                    // NOTE: Update even rows and delete odd rows.
   165                    //
   166                    if ((long)dataRow\["x"\] % 2 == 0)
   167                      dataRow\["y"\] =
   168                          DateTime.UtcNow.ToString("[getDateTimeFormat]");
   169                    else
   170                      dataRow.Delete();
   171                  }
   172  
   173                  dataAdapter.Update(dataTable); // DBConcurrencyException (?)
   174                }
   175              }
   176            }
   177          }
   178        }
   179      }
   180    }] true false true results errors System.Data.SQLite.dll]
   181  
   182    list $code $results \
   183        [expr {[info exists errors] ? $errors : ""}] \
   184        [expr {$code eq "Ok" ? [catch {
   185          object invoke _Dynamic${id}.Test${id} Main
   186        } result] : [set result ""]}] $result
   187  } -cleanup {
   188    cleanupDb $fileName; unset -nocomplain db fileName
   189    cleanupDb $otherFileName
   190  
   191    unset -nocomplain result code results errors i sql otherTable otherDbName \
   192        otherDataSource dataSource id otherFileName
   193  } -constraints {eagle command.object monoBug28 command.sql compile.DATA SQLite\

   194  System.Data.SQLite compileCSharp} -match glob -result {*\
195 System.Data.DBConcurrencyException: *}} 196 197 ############################################################################### 198 199 runTest {test tkt-343d392b51-2.2 {SQLiteDataAdapter update success} -setup { 200 setupDb [set fileName tkt-343d392b51-2.2.db] "" JulianDay 201 set otherFileName tkt-343d392b51-2.2-otherDb.db 202 } -body { 203 set id [object invoke Interpreter.GetActive NextId] 204 set dataSource [file join [getDatabaseDirectory] $fileName] 205 set otherDataSource [file join [getDatabaseDirectory] $otherFileName] 206 set otherDbName otherDb 207 set otherTable [appendArgs $otherDbName .t1] 208 209 set sql(inserts) "" 210 set sql(1) [subst { \ 211 ATTACH DATABASE '${otherDataSource}' AS ${otherDbName}; \ 212 CREATE TABLE ${otherTable}(x INTEGER PRIMARY KEY, y DATETIME); \ 213 [for {set i 1} {$i < 3} {incr i} { 214 append sql(inserts) [appendArgs \ 215 "INSERT INTO " ${otherTable} " (x, y) VALUES(" $i ", JULIANDAY('" \ 216 [clock format $i -format [getDateTimeFormat]] "')); "] 217 }; return [expr {[info exists sql(inserts)] ? $sql(inserts) : ""}]] \ 218 }] 219 220 set sql(2) [subst { \ 221 SELECT x, y FROM ${otherTable} ORDER BY x; \ 222 }] 223 224 unset -nocomplain results errors 225 226 set code [compileCSharpWith [subst { 227 using System; 228 using System.Data; 229 using System.Data.SQLite; 230 231 namespace _Dynamic${id} 232 { 233 public static class Test${id} 234 { 235 public static void Main() 236 { 237 using (SQLiteConnection connection = new SQLiteConnection( 238 "Data Source=${dataSource};DateTimeFormat=JulianDay;" + 239 "[getTestProperties]")) 240 { 241 connection.Open(); 242 243 using (SQLiteCommand command = connection.CreateCommand()) 244 { 245 command.CommandText = "${sql(1)}"; 246 command.ExecuteNonQuery(); 247 } 248 249 using (SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter( 250 "${sql(2)}", connection)) 251 { 252 using (DataSet dataSet = new DataSet()) 253 { 254 dataAdapter.Fill(dataSet, "${otherTable}"); 255 256 DataTable dataTable = dataSet.Tables\["${otherTable}"\]; 257 258 dataTable.Columns\["x"\].Unique = true; 259 dataTable.PrimaryKey = new DataColumn\[\] { 260 dataTable.Columns\["x"\] 261 }; 262 263 [expr {[isMono] ? "#pragma warning disable 219" : ""}] 264 SQLiteCommandBuilder commandBuilder = 265 new SQLiteCommandBuilder(dataAdapter); 266 [expr {[isMono] ? "#pragma warning restore 219" : ""}] 267 268 foreach (DataRow dataRow in dataTable.Rows) 269 { 270 // 271 // NOTE: Update even rows and delete odd rows. 272 // 273 if ((long)dataRow\["x"\] % 2 == 0) 274 dataRow\["y"\] = 275 DateTime.UtcNow.ToString("[getDateTimeFormat]"); 276 else 277 dataRow.Delete(); 278 } 279 280 dataAdapter.Update(dataTable); // DBConcurrencyException (?) 281 } 282 } 283 } 284 } 285 } 286 } 287 }] true false true results errors System.Data.SQLite.dll] 288 289 list $code $results \ 290 [expr {[info exists errors] ? $errors : ""}] \ 291 [expr {$code eq "Ok" ? [catch { 292 object invoke _Dynamic${id}.Test${id} Main 293 } result] : [set result ""]}] $result 294 } -cleanup { 295 cleanupDb $fileName; unset -nocomplain db fileName 296 cleanupDb $otherFileName 297
298 unset -nocomplain result code results errors i sql otherTable otherDbName \ 299 otherDataSource dataSource id otherFileName 300 } -constraints {eagle command.object monoBug28 command.sql compile.DATA SQLite\ 301 System.Data.SQLite compileCSharp} -match regexp -result {^Ok\ 302 System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 \{\}$}} 303 304 ############################################################################### 305 306 runTest {test tkt-343d392b51-3.1 {attached database, same table name} -setup { 307 setupDb [set fileName tkt-343d392b51-3.1.db] 308 set otherFileName tkt-343d392b51-3.1-otherDb.db 309 } -body { 310 set otherDataSource [file join [getDatabaseDirectory] $otherFileName] 311 set otherDbName otherDb 312 set otherTable [appendArgs $otherDbName .t1] 313 314 set sql(inserts) "" 315 set sql(1) [subst { \ 316 CREATE TABLE t1(x INTEGER PRIMARY KEY); \ 317 ATTACH DATABASE '${otherDataSource}' AS ${otherDbName}; \ 318 CREATE TABLE ${otherTable}(x INTEGER PRIMARY KEY); \ 319 [for {set i 1} {$i < 3} {incr i} { 320 append sql(inserts) [appendArgs \ 321 "INSERT INTO t1 (x) VALUES(" $i "); "] 322 323 append sql(inserts) [appendArgs \ 324 "INSERT INTO " ${otherTable} " (x) VALUES(" [expr {$i * 2}] "); "] 325 }; return [expr {[info exists sql(inserts)] ? $sql(inserts) : ""}]] \ 326 }] 327 328 sql execute $db $sql(1) 329 330 list [sql execute -execute reader -format list $db "SELECT x FROM t1;"] \ 331 [sql execute -execute reader -format list $db [appendArgs \ 332 "SELECT x FROM " ${otherTable} ";"]] 333 } -cleanup { 334 cleanupDb $fileName; unset -nocomplain db fileName 335 cleanupDb $otherFileName 336 337 unset -nocomplain i sql otherTable otherDbName otherDataSource otherFileName 338 } -constraints \ 339 {eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -result \ 340 {{1 2} {2 4}}} 341 342 ############################################################################### 343 344 runTest {test tkt-343d392b51-3.2 {adapter, attached db, table names} -setup { 345 setupDb [set fileName tkt-343d392b51-3.2.db] 346 set otherFileName tkt-343d392b51-3.2-otherDb.db 347 } -body { 348 set id [object invoke Interpreter.GetActive NextId] 349 set dataSource [file join [getDatabaseDirectory] $fileName] 350 set otherDataSource [file join [getDatabaseDirectory] $otherFileName] 351 set otherDbName otherDb 352 set otherTable [appendArgs $otherDbName .t1] 353 354 set sql(inserts) "" 355 set sql(1) [subst { \ 356 CREATE TABLE t1(x INTEGER PRIMARY KEY); \ 357 ATTACH DATABASE '${otherDataSource}' AS ${otherDbName}; \ 358 CREATE TABLE ${otherTable}(x INTEGER PRIMARY KEY); \ 359 [for {set i 1} {$i < 3} {incr i} { 360 append sql(inserts) [appendArgs \ 361 "INSERT INTO t1 (x) VALUES(" $i ");"] 362 append sql(inserts) [appendArgs \ 363 "INSERT INTO " ${otherTable} " (x) VALUES(" [expr {$i * 2}] "); "] 364 }; return [expr {[info exists sql(inserts)] ? $sql(inserts) : ""}]] \ 365 }] 366 367 set sql(2) [subst { \ 368 SELECT x FROM ${otherTable} ORDER BY x; \ 369 }] 370 371 unset -nocomplain results errors 372 373 set code [compileCSharpWith [subst { 374 using System.Data; 375 using System.Data.SQLite; 376 377 namespace _Dynamic${id} 378 { 379 public static class Test${id} 380 { 381 public static void Main() 382 { 383 using (SQLiteConnection connection = new SQLiteConnection( 384 "Data Source=${dataSource};[getTestProperties]")) 385 { 386 connection.Open(); 387 388 using (SQLiteCommand command = connection.CreateCommand()) 389 { 390 command.CommandText = "${sql(1)}"; 391 command.ExecuteNonQuery(); 392 } 393 394 using (SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter( 395 "${sql(2)}", connection)) 396 { 397 using (DataSet dataSet = new DataSet()) 398 { 399 dataAdapter.Fill(dataSet, "${otherTable}"); 400 401 DataTable dataTable = dataSet.Tables\["${otherTable}"\]; 402 403 dataTable.Columns\["x"\].Unique = true; 404 dataTable.PrimaryKey = new DataColumn\[\] { 405 dataTable.Columns\["x"\] 406 }; 407 408 [expr {[isMono] ? "#pragma warning disable 219" : ""}] 409 SQLiteCommandBuilder commandBuilder = 410 new SQLiteCommandBuilder(dataAdapter); 411 [expr {[isMono] ? "#pragma warning restore 219" : ""}] 412 413 foreach (DataRow dataRow in dataTable.Rows) 414 dataRow.Delete(); 415 416 dataAdapter.Update(dataTable); // DBConcurrencyException (?) 417 } 418 } 419 } 420 } 421 } 422 } 423 }] true false true results errors System.Data.SQLite.dll] 424 425 list $code $results \ 426 [expr {[info exists errors] ? $errors : ""}] \ 427 [expr {$code eq "Ok" ? [catch { 428 object invoke _Dynamic${id}.Test${id} Main 429 } result] : [set result ""]}] $result 430 } -cleanup { 431 cleanupDb $fileName; unset -nocomplain db fileName 432 cleanupDb $otherFileName 433 434 unset -nocomplain result code results errors i sql otherTable otherDbName \ 435 otherDataSource dataSource id otherFileName 436 } -constraints {eagle command.object monoBug28 monoBug211 command.sql\ 437 compile.DATA SQLite System.Data.SQLite compileCSharp} -match regexp -result \ 438 {^Ok System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 \{\}$}} 439 440 ############################################################################### 441 442 runSQLiteTestEpilogue 443 runTestEpilogue