1 # 2006 November 08

     2 #

     3 # The author disclaims copyright to this source code.  In place of

     4 # a legal notice, here is a blessing:

     5 #

     6 #    May you do good and not evil.

     7 #    May you find forgiveness for yourself and forgive others.

     8 #    May you share freely, never taking more than you give.

     9 #

    10 #***********************************************************************

    11 # This file implements regression tests for SQLite library.  

    12 #

    13 # This is a copy of the capi3.test file that has been adapted to

    14 # test the new sqlite3_prepare_v2 interface.

    15 #

    16 # $Id: capi3c.test,v 1.19 2008/05/05 16:56:35 drh Exp $

    17 #

    18 

    19 set testdir [file dirname $argv0]

    20 source $testdir/tester.tcl

    21 

    22 # Return the UTF-16 representation of the supplied UTF-8 string $str.

    23 # If $nt is true, append two 0x00 bytes as a nul terminator.

    24 proc utf16 {str {nt 1}} {

    25   set r [encoding convertto unicode $str]

    26   if {$nt} {

    27     append r "\x00\x00"

    28   }

    29   return $r

    30 }

    31 

    32 # Return the UTF-8 representation of the supplied UTF-16 string $str. 

    33 proc utf8 {str} {

    34   # If $str ends in two 0x00 0x00 bytes, knock these off before

    35   # converting to UTF-8 using TCL.

    36   binary scan $str \c* vals

    37   if {[lindex $vals end]==0 && [lindex $vals end-1]==0} {

    38     set str [binary format \c* [lrange $vals 0 end-2]]

    39   }

    40 

    41   set r [encoding convertfrom unicode $str]

    42   return $r

    43 }

    44 

    45 # These tests complement those in capi2.test. They are organized

    46 # as follows:

    47 #

    48 # capi3c-1.*: Test sqlite3_prepare_v2 

    49 # capi3c-2.*: Test sqlite3_prepare16_v2 

    50 # capi3c-3.*: Test sqlite3_open

    51 # capi3c-4.*: Test sqlite3_open16

    52 # capi3c-5.*: Test the various sqlite3_result_* APIs

    53 # capi3c-6.*: Test that sqlite3_close fails if there are outstanding VMs.

    54 #

    55 

    56 set DB [sqlite3_connection_pointer db]

    57 

    58 do_test capi3c-1.0 {

    59   sqlite3_get_autocommit $DB

    60 } 1

    61 do_test capi3c-1.1 {

    62   set STMT [sqlite3_prepare_v2 $DB {SELECT name FROM sqlite_master} -1 TAIL]

    63   sqlite3_finalize $STMT

    64   set TAIL

    65 } {}

    66 do_test capi3c-1.2 {

    67   sqlite3_errcode $DB

    68 } {SQLITE_OK}

    69 do_test capi3c-1.3 {

    70   sqlite3_errmsg $DB

    71 } {not an error}

    72 do_test capi3c-1.4 {

    73   set sql {SELECT name FROM sqlite_master;SELECT 10}

    74   set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL]

    75   sqlite3_finalize $STMT

    76   set TAIL

    77 } {SELECT 10}

    78 do_test capi3c-1.5 {

    79   set sql {SELECT namex FROM sqlite_master}

    80   catch {

    81     set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL]

    82   }

    83 } {1}

    84 do_test capi3c-1.6 {

    85   sqlite3_errcode $DB

    86 } {SQLITE_ERROR}

    87 do_test capi3c-1.7 {

    88   sqlite3_errmsg $DB

    89 } {no such column: namex}

    90 

    91 

    92 ifcapable {utf16} {

    93   do_test capi3c-2.1 {

    94     set sql16 [utf16 {SELECT name FROM sqlite_master}]

    95     set STMT [sqlite3_prepare16_v2  $DB $sql16 -1 ::TAIL]

    96     sqlite3_finalize $STMT

    97     utf8 $::TAIL

    98   } {}

    99   do_test capi3c-2.2 {

   100     set sql [utf16 {SELECT name FROM sqlite_master;SELECT 10}]

   101     set STMT [sqlite3_prepare16_v2  $DB $sql -1 TAIL]

   102     sqlite3_finalize $STMT

   103     utf8 $TAIL

   104   } {SELECT 10}

   105   do_test capi3c-2.3 {

   106     set sql [utf16 {SELECT namex FROM sqlite_master}]

   107     catch {

   108       set STMT [sqlite3_prepare16_v2  $DB $sql -1 TAIL]

   109     }

   110   } {1}

   111   do_test capi3c-2.4 {

   112     sqlite3_errcode $DB

   113   } {SQLITE_ERROR}

   114   do_test capi3c-2.5 {

   115     sqlite3_errmsg $DB

   116   } {no such column: namex}

   117 

   118   ifcapable schema_pragmas {

   119     do_test capi3c-2.6 {

   120       execsql {CREATE TABLE tablename(x)}

   121       set sql16 [utf16 {PRAGMA table_info("TableName")}]

   122       set STMT [sqlite3_prepare16_v2  $DB $sql16 -1 TAIL]

   123       sqlite3_step $STMT

   124     } SQLITE_ROW

   125     do_test capi3c-2.7 {

   126       sqlite3_step $STMT

   127     } SQLITE_DONE

   128     do_test capi3c-2.8 {

   129       sqlite3_finalize $STMT

   130     } SQLITE_OK

   131   }

   132 

   133 } ;# endif utf16

   134 

   135 # rename sqlite3_open sqlite3_open_old

   136 # proc sqlite3_open {fname options} {sqlite3_open_new $fname $options}

   137 

   138 do_test capi3c-3.1 {

   139   set db2 [sqlite3_open test.db {}]

   140   sqlite3_errcode $db2

   141 } {SQLITE_OK}

   142 # FIX ME: Should test the db handle works.

   143 do_test capi3c-3.2 {

   144   sqlite3_close $db2

   145 } {SQLITE_OK}

   146 # Symbian OS: '/' in the file name replaced with '\\'

   147 do_test capi3c-3.3 {

   148   catch {

   149     set db2 [sqlite3_open \\bogus\\path\\test.db {}]

   150   }

   151   sqlite3_errcode $db2

   152 } {SQLITE_CANTOPEN}

   153 do_test capi3c-3.4 {

   154   sqlite3_errmsg $db2

   155 } {unable to open database file}

   156 do_test capi3c-3.5 {

   157   sqlite3_close $db2

   158 } {SQLITE_OK}

   159 do_test capi3c-3.6.1-misuse {

   160   sqlite3_close $db2

   161 } {SQLITE_MISUSE}

   162 do_test capi3c-3.6.2-misuse {

   163   sqlite3_errmsg $db2

   164 } {library routine called out of sequence}

   165 ifcapable {utf16} {

   166   do_test capi3c-3.6.3-misuse {

   167     utf8 [sqlite3_errmsg16 $db2]

   168   } {library routine called out of sequence}

   169 }

   170 

   171 # rename sqlite3_open ""

   172 # rename sqlite3_open_old sqlite3_open

   173 

   174 ifcapable {utf16} {

   175 do_test capi3c-4.1 {

   176   set db2 [sqlite3_open16 [utf16 test.db] {}]

   177   sqlite3_errcode $db2

   178 } {SQLITE_OK}

   179 # FIX ME: Should test the db handle works.

   180 do_test capi3c-4.2 {

   181   sqlite3_close $db2

   182 } {SQLITE_OK}

   183 # Symbian OS: '/' in the file name replaced with '\\'

   184 do_test capi3c-4.3 {

   185   catch {

   186     set db2 [sqlite3_open16 [utf16 \\bogus\\path\\test.db] {}]

   187   }

   188   sqlite3_errcode $db2

   189 } {SQLITE_CANTOPEN}

   190 do_test capi3c-4.4 {

   191   utf8 [sqlite3_errmsg16 $db2]

   192 } {unable to open database file}

   193 do_test capi3c-4.5 {

   194   sqlite3_close $db2

   195 } {SQLITE_OK}

   196 } ;# utf16

   197 

   198 # This proc is used to test the following API calls:

   199 #

   200 # sqlite3_column_count

   201 # sqlite3_column_name

   202 # sqlite3_column_name16

   203 # sqlite3_column_decltype

   204 # sqlite3_column_decltype16

   205 #

   206 # $STMT is a compiled SQL statement. $test is a prefix

   207 # to use for test names within this proc. $names is a list

   208 # of the column names that should be returned by $STMT.

   209 # $decltypes is a list of column declaration types for $STMT.

   210 #

   211 # Example:

   212 #

   213 # set STMT [sqlite3_prepare_v2 "SELECT 1, 2, 2;" -1 DUMMY]

   214 # check_header test1.1 {1 2 3} {"" "" ""}

   215 #

   216 proc check_header {STMT test names decltypes} {

   217 

   218   # Use the return value of sqlite3_column_count() to build

   219   # a list of column indexes. i.e. If sqlite3_column_count

   220   # is 3, build the list {0 1 2}.

   221   set ::idxlist [list]

   222   set ::numcols [sqlite3_column_count $STMT]

   223   for {set i 0} {$i < $::numcols} {incr i} {lappend ::idxlist $i}

   224 

   225   # Column names in UTF-8

   226   do_test $test.1 {

   227     set cnamelist [list]

   228     foreach i $idxlist {lappend cnamelist [sqlite3_column_name $STMT $i]} 

   229     set cnamelist

   230   } $names

   231 

   232   # Column names in UTF-16

   233   ifcapable {utf16} {

   234     do_test $test.2 {

   235       set cnamelist [list]

   236       foreach i $idxlist {

   237         lappend cnamelist [utf8 [sqlite3_column_name16 $STMT $i]]

   238       }

   239       set cnamelist

   240     } $names

   241   }

   242 

   243   # Column names in UTF-8

   244   do_test $test.3 {

   245     set cnamelist [list]

   246     foreach i $idxlist {lappend cnamelist [sqlite3_column_name $STMT $i]} 

   247     set cnamelist

   248   } $names

   249 

   250   # Column names in UTF-16

   251   ifcapable {utf16} {

   252     do_test $test.4 {

   253       set cnamelist [list]

   254       foreach i $idxlist {

   255         lappend cnamelist [utf8 [sqlite3_column_name16 $STMT $i]]

   256       }

   257       set cnamelist

   258     } $names

   259   }

   260 

   261   # Column names in UTF-8

   262   do_test $test.5 {

   263     set cnamelist [list]

   264     foreach i $idxlist {lappend cnamelist [sqlite3_column_decltype $STMT $i]} 

   265     set cnamelist

   266   } $decltypes

   267 

   268   # Column declaration types in UTF-16

   269   ifcapable {utf16} {

   270     do_test $test.6 {

   271       set cnamelist [list]

   272       foreach i $idxlist {

   273         lappend cnamelist [utf8 [sqlite3_column_decltype16 $STMT $i]]

   274       }

   275       set cnamelist

   276     } $decltypes

   277   }

   278 

   279 

   280   # Test some out of range conditions:

   281   ifcapable {utf16} {

   282     do_test $test.7 {

   283       list \

   284         [sqlite3_column_name $STMT -1] \

   285         [sqlite3_column_name16 $STMT -1] \

   286         [sqlite3_column_decltype $STMT -1] \

   287         [sqlite3_column_decltype16 $STMT -1] \

   288         [sqlite3_column_name $STMT $numcols] \

   289         [sqlite3_column_name16 $STMT $numcols] \

   290         [sqlite3_column_decltype $STMT $numcols] \

   291         [sqlite3_column_decltype16 $STMT $numcols]

   292     } {{} {} {} {} {} {} {} {}}

   293   }

   294 } 

   295 

   296 # This proc is used to test the following API calls:

   297 #

   298 # sqlite3_column_origin_name

   299 # sqlite3_column_origin_name16

   300 # sqlite3_column_table_name

   301 # sqlite3_column_table_name16

   302 # sqlite3_column_database_name

   303 # sqlite3_column_database_name16

   304 #

   305 # $STMT is a compiled SQL statement. $test is a prefix

   306 # to use for test names within this proc. $names is a list

   307 # of the column names that should be returned by $STMT.

   308 # $decltypes is a list of column declaration types for $STMT.

   309 #

   310 # Example:

   311 #

   312 # set STMT [sqlite3_prepare_v2 "SELECT 1, 2, 2;" -1 DUMMY]

   313 # check_header test1.1 {1 2 3} {"" "" ""}

   314 #

   315 proc check_origin_header {STMT test dbs tables cols} {

   316   # If sqlite3_column_origin_name() and friends are not compiled into

   317   # this build, this proc is a no-op.

   318 ifcapable columnmetadata {

   319 

   320     # Use the return value of sqlite3_column_count() to build

   321     # a list of column indexes. i.e. If sqlite3_column_count

   322     # is 3, build the list {0 1 2}.

   323     set ::idxlist [list]

   324     set ::numcols [sqlite3_column_count $STMT]

   325     for {set i 0} {$i < $::numcols} {incr i} {lappend ::idxlist $i}

   326   

   327     # Database names in UTF-8

   328     do_test $test.8 {

   329       set cnamelist [list]

   330       foreach i $idxlist {

   331         lappend cnamelist [sqlite3_column_database_name $STMT $i]

   332       } 

   333       set cnamelist

   334     } $dbs

   335   

   336     # Database names in UTF-16

   337     ifcapable {utf16} {

   338       do_test $test.9 {

   339         set cnamelist [list]

   340         foreach i $idxlist {

   341           lappend cnamelist [utf8 [sqlite3_column_database_name16 $STMT $i]]

   342         }

   343         set cnamelist

   344       } $dbs

   345     }

   346   

   347     # Table names in UTF-8

   348     do_test $test.10 {

   349       set cnamelist [list]

   350       foreach i $idxlist {

   351         lappend cnamelist [sqlite3_column_table_name $STMT $i]

   352       } 

   353       set cnamelist

   354     } $tables

   355   

   356     # Table names in UTF-16

   357     ifcapable {utf16} {

   358       do_test $test.11 {

   359         set cnamelist [list]

   360         foreach i $idxlist {

   361           lappend cnamelist [utf8 [sqlite3_column_table_name16 $STMT $i]]

   362         }

   363         set cnamelist

   364       } $tables

   365     }

   366   

   367     # Origin names in UTF-8

   368     do_test $test.12 {

   369       set cnamelist [list]

   370       foreach i $idxlist {

   371         lappend cnamelist [sqlite3_column_origin_name $STMT $i]

   372       } 

   373       set cnamelist

   374     } $cols

   375   

   376     # Origin declaration types in UTF-16

   377     ifcapable {utf16} {

   378       do_test $test.13 {

   379         set cnamelist [list]

   380         foreach i $idxlist {

   381           lappend cnamelist [utf8 [sqlite3_column_origin_name16 $STMT $i]]

   382         }

   383         set cnamelist

   384       } $cols

   385     }

   386   }

   387 }

   388 

   389 # This proc is used to test the following APIs:

   390 #

   391 # sqlite3_data_count

   392 # sqlite3_column_type

   393 # sqlite3_column_int

   394 # sqlite3_column_text

   395 # sqlite3_column_text16

   396 # sqlite3_column_double

   397 #

   398 # $STMT is a compiled SQL statement for which the previous call 

   399 # to sqlite3_step returned SQLITE_ROW. $test is a prefix to use 

   400 # for test names within this proc. $types is a list of the 

   401 # manifest types for the current row. $ints, $doubles and $strings

   402 # are lists of the integer, real and string representations of

   403 # the values in the current row.

   404 #

   405 # Example:

   406 #

   407 # set STMT [sqlite3_prepare_v2 "SELECT 'hello', 1.1, NULL" -1 DUMMY]

   408 # sqlite3_step $STMT

   409 # check_data test1.2 {TEXT REAL NULL} {0 1 0} {0 1.1 0} {hello 1.1 {}}

   410 #

   411 proc check_data {STMT test types ints doubles strings} {

   412 

   413   # Use the return value of sqlite3_column_count() to build

   414   # a list of column indexes. i.e. If sqlite3_column_count

   415   # is 3, build the list {0 1 2}.

   416   set ::idxlist [list]

   417   set numcols [sqlite3_data_count $STMT]

   418   for {set i 0} {$i < $numcols} {incr i} {lappend ::idxlist $i}

   419 

   420 # types

   421 do_test $test.1 {

   422   set types [list]

   423   foreach i $idxlist {lappend types [sqlite3_column_type $STMT $i]}

   424   set types

   425 } $types

   426 

   427 # Integers

   428 do_test $test.2 {

   429   set ints [list]

   430   foreach i $idxlist {lappend ints [sqlite3_column_int64 $STMT $i]}

   431   set ints

   432 } $ints

   433 

   434 # bytes

   435 set lens [list]

   436 foreach i $::idxlist {

   437   lappend lens [string length [lindex $strings $i]]

   438 }

   439 do_test $test.3 {

   440   set bytes [list]

   441   set lens [list]

   442   foreach i $idxlist {

   443     lappend bytes [sqlite3_column_bytes $STMT $i]

   444   }

   445   set bytes

   446 } $lens

   447 

   448 # bytes16

   449 ifcapable {utf16} {

   450   set lens [list]

   451   foreach i $::idxlist {

   452     lappend lens [expr 2 * [string length [lindex $strings $i]]]

   453   }

   454   do_test $test.4 {

   455     set bytes [list]

   456     set lens [list]

   457     foreach i $idxlist {

   458       lappend bytes [sqlite3_column_bytes16 $STMT $i]

   459     }

   460     set bytes

   461   } $lens

   462 }

   463 

   464 # Blob

   465 do_test $test.5 {

   466   set utf8 [list]

   467   foreach i $idxlist {lappend utf8 [sqlite3_column_blob $STMT $i]}

   468   set utf8

   469 } $strings

   470 

   471 # UTF-8

   472 do_test $test.6 {

   473   set utf8 [list]

   474   foreach i $idxlist {lappend utf8 [sqlite3_column_text $STMT $i]}

   475   set utf8

   476 } $strings

   477 

   478 # Floats

   479 do_test $test.7 {

   480   set utf8 [list]

   481   foreach i $idxlist {lappend utf8 [sqlite3_column_double $STMT $i]}

   482   set utf8

   483 } $doubles

   484 

   485 # UTF-16

   486 ifcapable {utf16} {

   487   do_test $test.8 {

   488     set utf8 [list]

   489     foreach i $idxlist {lappend utf8 [utf8 [sqlite3_column_text16 $STMT $i]]}

   490     set utf8

   491   } $strings

   492 }

   493 

   494 # Integers

   495 do_test $test.9 {

   496   set ints [list]

   497   foreach i $idxlist {lappend ints [sqlite3_column_int $STMT $i]}

   498   set ints

   499 } $ints

   500 

   501 # Floats

   502 do_test $test.10 {

   503   set utf8 [list]

   504   foreach i $idxlist {lappend utf8 [sqlite3_column_double $STMT $i]}

   505   set utf8

   506 } $doubles

   507 

   508 # UTF-8

   509 do_test $test.11 {

   510   set utf8 [list]

   511   foreach i $idxlist {lappend utf8 [sqlite3_column_text $STMT $i]}

   512   set utf8

   513 } $strings

   514 

   515 # Types

   516 do_test $test.12 {

   517   set types [list]

   518   foreach i $idxlist {lappend types [sqlite3_column_type $STMT $i]}

   519   set types

   520 } $types

   521 

   522 # Test that an out of range request returns the equivalent of NULL

   523 do_test $test.13 {

   524   sqlite3_column_int $STMT -1

   525 } {0}

   526 do_test $test.13 {

   527   sqlite3_column_text $STMT -1

   528 } {}

   529 

   530 }

   531 

   532 ifcapable !floatingpoint {

   533   finish_test

   534   return

   535 }

   536 

   537 do_test capi3c-5.0 {

   538   execsql {

   539     CREATE TABLE t1(a VARINT, b BLOB, c VARCHAR(16));

   540     INSERT INTO t1 VALUES(1, 2, 3);

   541     INSERT INTO t1 VALUES('one', 'two', NULL);

   542     INSERT INTO t1 VALUES(1.2, 1.3, 1.4);

   543   }

   544   set sql "SELECT * FROM t1"

   545   set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL]

   546   sqlite3_column_count $STMT

   547 } 3

   548 

   549 check_header $STMT capi3c-5.1 {a b c} {VARINT BLOB VARCHAR(16)}

   550 check_origin_header $STMT capi3c-5.1 {main main main} {t1 t1 t1} {a b c}

   551 do_test capi3c-5.2 {

   552   sqlite3_step $STMT

   553 } SQLITE_ROW

   554 

   555 check_header $STMT capi3c-5.3 {a b c} {VARINT BLOB VARCHAR(16)}

   556 check_origin_header $STMT capi3c-5.3 {main main main} {t1 t1 t1} {a b c}

   557 check_data $STMT capi3c-5.4 {INTEGER INTEGER TEXT} {1 2 3} {1.0 2.0 3.0} {1 2 3}

   558 

   559 do_test capi3c-5.5 {

   560   sqlite3_step $STMT

   561 } SQLITE_ROW

   562 

   563 check_header $STMT capi3c-5.6 {a b c} {VARINT BLOB VARCHAR(16)}

   564 check_origin_header $STMT capi3c-5.6 {main main main} {t1 t1 t1} {a b c}

   565 check_data $STMT capi3c-5.7 {TEXT TEXT NULL} {0 0 0} {0.0 0.0 0.0} {one two {}}

   566 

   567 do_test capi3c-5.8 {

   568   sqlite3_step $STMT

   569 } SQLITE_ROW

   570 

   571 check_header $STMT capi3c-5.9 {a b c} {VARINT BLOB VARCHAR(16)}

   572 check_origin_header $STMT capi3c-5.9 {main main main} {t1 t1 t1} {a b c}

   573 check_data $STMT capi3c-5.10 {FLOAT FLOAT TEXT} {1 1 1} {1.2 1.3 1.4} {1.2 1.3 1.4}

   574 

   575 do_test capi3c-5.11 {

   576   sqlite3_step $STMT

   577 } SQLITE_DONE

   578 

   579 do_test capi3c-5.12 {

   580   sqlite3_finalize $STMT

   581 } SQLITE_OK

   582 

   583 do_test capi3c-5.20 {

   584   set sql "SELECT a, sum(b), max(c) FROM t1 GROUP BY a"

   585   set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL]

   586   sqlite3_column_count $STMT

   587 } 3

   588 

   589 check_header $STMT capi3c-5.21 {a sum(b) max(c)} {VARINT {} {}}

   590 check_origin_header $STMT capi3c-5.22 {main {} {}} {t1 {} {}} {a {} {}}

   591 do_test capi3c-5.23 {

   592   sqlite3_finalize $STMT

   593 } SQLITE_OK

   594 

   595 

   596 set ::ENC [execsql {pragma encoding}]

   597 db close

   598 

   599 do_test capi3c-6.0 {

   600   sqlite3 db test.db

   601   set DB [sqlite3_connection_pointer db]

   602   sqlite3_key $DB xyzzy

   603   set sql {SELECT a FROM t1 order by rowid}

   604   set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL]

   605   expr 0

   606 } {0}

   607 do_test capi3c-6.1 {

   608   db cache flush

   609   sqlite3_close $DB

   610 } {SQLITE_BUSY}

   611 do_test capi3c-6.2 {

   612   sqlite3_step $STMT

   613 } {SQLITE_ROW}

   614 check_data $STMT capi3c-6.3 {INTEGER} {1} {1.0} {1}

   615 do_test capi3c-6.3 {

   616   sqlite3_finalize $STMT

   617 } {SQLITE_OK}

   618 do_test capi3c-6.4 {

   619   db cache flush

   620   sqlite3_close $DB

   621 } {SQLITE_OK}

   622 do_test capi3c-6.99-misuse {

   623   db close

   624 } {}

   625 

   626 # This procedure sets the value of the file-format in file 'test.db'

   627 # to $newval. Also, the schema cookie is incremented.

   628 # 

   629 proc set_file_format {newval} {

   630   hexio_write test.db 44 [hexio_render_int32 $newval]

   631   set schemacookie [hexio_get_int [hexio_read test.db 40 4]]

   632   incr schemacookie

   633   hexio_write test.db 40 [hexio_render_int32 $schemacookie]

   634   return {}

   635 }

   636 

   637 # This procedure returns the value of the file-format in file 'test.db'.

   638 # 

   639 proc get_file_format {{fname test.db}} {

   640   return [hexio_get_int [hexio_read $fname 44 4]]

   641 }

   642 

   643 if {![sqlite3 -has-codec]} {

   644   # Test what happens when the library encounters a newer file format.

   645   do_test capi3c-7.1 {

   646     set_file_format 5

   647   } {}

   648   do_test capi3c-7.2 {

   649     sqlite3 db test.db

   650     catchsql {

   651       SELECT * FROM sqlite_master;

   652     }

   653   } {1 {unsupported file format}}

   654   db close

   655 }

   656 

   657 if {![sqlite3 -has-codec]} {

   658   # Now test that the library correctly handles bogus entries in the

   659   # sqlite_master table (schema corruption).

   660   do_test capi3c-8.1 {

   661     file delete -force test.db test.db-journal

   662     sqlite3 db test.db

   663     execsql {

   664       CREATE TABLE t1(a);

   665     }

   666     db close

   667   } {}

   668   do_test capi3c-8.2 {

   669     sqlite3 db test.db

   670     execsql {

   671       PRAGMA writable_schema=ON;

   672       INSERT INTO sqlite_master VALUES(NULL,NULL,NULL,NULL,NULL);

   673     }

   674     db close

   675   } {}

   676   do_test capi3c-8.3 {

   677     sqlite3 db test.db

   678     catchsql {

   679       SELECT * FROM sqlite_master;

   680     }

   681   } {1 {malformed database schema (?)}}

   682   do_test capi3c-8.4 {

   683     # Build a 5-field row record. The first field is a string 'table', and

   684     # subsequent fields are all NULL.

   685     db close

   686     file delete -force test.db test.db-journal

   687     sqlite3 db test.db

   688     execsql {

   689       CREATE TABLE t1(a);

   690       PRAGMA writable_schema=ON;

   691       INSERT INTO sqlite_master VALUES('table',NULL,NULL,NULL,NULL);

   692     }

   693     db close

   694   } {};

   695   do_test capi3c-8.5 {

   696     sqlite3 db test.db

   697     catchsql {

   698       SELECT * FROM sqlite_master;

   699     }

   700   } {1 {malformed database schema (?)}}

   701   db close

   702 }

   703 file delete -force test.db

   704 file delete -force test.db-journal

   705 

   706 

   707 # Test the english language string equivalents for sqlite error codes

   708 set code2english [list \

   709 SQLITE_OK         {not an error} \

   710 SQLITE_ERROR      {SQL logic error or missing database} \

   711 SQLITE_PERM       {access permission denied} \

   712 SQLITE_ABORT      {callback requested query abort} \

   713 SQLITE_BUSY       {database is locked} \

   714 SQLITE_LOCKED     {database table is locked} \

   715 SQLITE_NOMEM      {out of memory} \

   716 SQLITE_READONLY   {attempt to write a readonly database} \

   717 SQLITE_INTERRUPT  {interrupted} \

   718 SQLITE_IOERR      {disk I/O error} \

   719 SQLITE_CORRUPT    {database disk image is malformed} \

   720 SQLITE_FULL       {database or disk is full} \

   721 SQLITE_CANTOPEN   {unable to open database file} \

   722 SQLITE_EMPTY      {table contains no data} \

   723 SQLITE_SCHEMA     {database schema has changed} \

   724 SQLITE_CONSTRAINT {constraint failed} \

   725 SQLITE_MISMATCH   {datatype mismatch} \

   726 SQLITE_MISUSE     {library routine called out of sequence} \

   727 SQLITE_NOLFS      {large file support is disabled} \

   728 SQLITE_AUTH       {authorization denied} \

   729 SQLITE_FORMAT     {auxiliary database format error} \

   730 SQLITE_RANGE      {bind or column index out of range} \

   731 SQLITE_NOTADB     {file is encrypted or is not a database} \

   732 unknownerror      {unknown error} \

   733 ]

   734 

   735 set test_number 1

   736 foreach {code english} $code2english {

   737   do_test capi3c-9.$test_number "sqlite3_test_errstr $code" $english

   738   incr test_number

   739 }

   740 

   741 # Test the error message when a "real" out of memory occurs.

   742 ifcapable memdebug {

   743   do_test capi3c-10-1 {

   744     sqlite3 db test.db

   745     set DB [sqlite3_connection_pointer db]

   746     sqlite3_memdebug_fail 0

   747     catchsql {

   748       select * from sqlite_master;

   749     }

   750   } {1 {out of memory}}

   751   do_test capi3c-10-2 {

   752     sqlite3_errmsg $::DB

   753   } {out of memory}

   754   ifcapable {utf16} {

   755     do_test capi3c-10-3 {

   756       utf8 [sqlite3_errmsg16 $::DB]

   757     } {out of memory}

   758   }

   759   db close

   760   sqlite3_memdebug_fail -1

   761 }

   762 

   763 # The following tests - capi3c-11.* - test that a COMMIT or ROLLBACK

   764 # statement issued while there are still outstanding VMs that are part of

   765 # the transaction fails.

   766 sqlite3 db test.db

   767 set DB [sqlite3_connection_pointer db]

   768 sqlite_register_test_function $DB func

   769 do_test capi3c-11.1 {

   770   execsql {

   771     BEGIN;

   772     CREATE TABLE t1(a, b);

   773     INSERT INTO t1 VALUES(1, 'int');

   774     INSERT INTO t1 VALUES(2, 'notatype');

   775   }

   776 } {}

   777 do_test capi3c-11.1.1 {

   778   sqlite3_get_autocommit $DB

   779 } 0

   780 do_test capi3c-11.2 {

   781   set STMT [sqlite3_prepare_v2 $DB "SELECT func(b, a) FROM t1" -1 TAIL]

   782   sqlite3_step $STMT

   783 } {SQLITE_ROW}

   784 do_test capi3c-11.3 {

   785   catchsql {

   786     COMMIT;

   787   }

   788 } {1 {cannot commit transaction - SQL statements in progress}}

   789 do_test capi3c-11.3.1 {

   790   sqlite3_get_autocommit $DB

   791 } 0

   792 do_test capi3c-11.4 {

   793   sqlite3_step $STMT

   794 } {SQLITE_ERROR}

   795 do_test capi3c-11.5 {

   796   sqlite3_finalize $STMT

   797 } {SQLITE_ERROR}

   798 do_test capi3c-11.6 {

   799   catchsql {

   800     SELECT * FROM t1;

   801   }

   802 } {0 {1 int 2 notatype}}

   803 do_test capi3c-11.6.1 {

   804   sqlite3_get_autocommit $DB

   805 } 0

   806 do_test capi3c-11.7 {

   807   catchsql {

   808     COMMIT;

   809   }

   810 } {0 {}}

   811 do_test capi3c-11.7.1 {

   812   sqlite3_get_autocommit $DB

   813 } 1

   814 do_test capi3c-11.8 {

   815   execsql {

   816     CREATE TABLE t2(a);

   817     INSERT INTO t2 VALUES(1);

   818     INSERT INTO t2 VALUES(2);

   819     BEGIN;

   820     INSERT INTO t2 VALUES(3);

   821   }

   822 } {}

   823 do_test capi3c-11.8.1 {

   824   sqlite3_get_autocommit $DB

   825 } 0

   826 do_test capi3c-11.9 {

   827   set STMT [sqlite3_prepare_v2 $DB "SELECT a FROM t2" -1 TAIL]

   828   sqlite3_step $STMT

   829 } {SQLITE_ROW}

   830 do_test capi3c-11.9.1 {

   831   sqlite3_get_autocommit $DB

   832 } 0

   833 do_test capi3c-11.9.2 {

   834   catchsql {

   835     ROLLBACK;

   836   }

   837 } {1 {cannot rollback transaction - SQL statements in progress}}

   838 do_test capi3c-11.9.3 {

   839   sqlite3_get_autocommit $DB

   840 } 0

   841 do_test capi3c-11.10 {

   842   sqlite3_step $STMT

   843 } {SQLITE_ROW}

   844 do_test capi3c-11.11 {

   845   sqlite3_step $STMT

   846 } {SQLITE_ROW}

   847 do_test capi3c-11.12 {

   848   sqlite3_step $STMT

   849 } {SQLITE_DONE}

   850 do_test capi3c-11.13 {

   851   sqlite3_finalize $STMT

   852 } {SQLITE_OK}

   853 do_test capi3c-11.14 {

   854   execsql {

   855     SELECT a FROM t2;

   856   }

   857 } {1 2 3}

   858 do_test capi3c-11.14.1 {

   859   sqlite3_get_autocommit $DB

   860 } 0

   861 do_test capi3c-11.15 {

   862   catchsql {

   863     ROLLBACK;

   864   }

   865 } {0 {}}

   866 do_test capi3c-11.15.1 {

   867   sqlite3_get_autocommit $DB

   868 } 1

   869 do_test capi3c-11.16 {

   870   execsql {

   871     SELECT a FROM t2;

   872   }

   873 } {1 2}

   874 

   875 # Sanity check on the definition of 'outstanding VM'. This means any VM

   876 # that has had sqlite3_step() called more recently than sqlite3_finalize() or

   877 # sqlite3_reset(). So a VM that has just been prepared or reset does not

   878 # count as an active VM.

   879 do_test capi3c-11.17 {

   880   execsql {

   881     BEGIN;

   882   }

   883 } {}

   884 do_test capi3c-11.18 {

   885   set STMT [sqlite3_prepare_v2 $DB "SELECT a FROM t1" -1 TAIL]

   886   catchsql {

   887     COMMIT;

   888   }

   889 } {0 {}}

   890 do_test capi3c-11.19 {

   891   sqlite3_step $STMT

   892 } {SQLITE_ROW}

   893 do_test capi3c-11.20 {

   894   catchsql {

   895     BEGIN;

   896     COMMIT;

   897   }

   898 } {1 {cannot commit transaction - SQL statements in progress}}

   899 do_test capi3c-11.20 {

   900   sqlite3_reset $STMT

   901   catchsql {

   902     COMMIT;

   903   }

   904 } {0 {}}

   905 do_test capi3c-11.21 {

   906   sqlite3_finalize $STMT

   907 } {SQLITE_OK}

   908 

   909 # The following tests - capi3c-12.* - check that its Ok to start a

   910 # transaction while other VMs are active, and that its Ok to execute

   911 # atomic updates in the same situation 

   912 #

   913 do_test capi3c-12.1 {

   914   set STMT [sqlite3_prepare_v2 $DB "SELECT a FROM t2" -1 TAIL]

   915   sqlite3_step $STMT

   916 } {SQLITE_ROW}

   917 do_test capi3c-12.2 {

   918   catchsql {

   919     INSERT INTO t1 VALUES(3, NULL);

   920   }

   921 } {0 {}}

   922 do_test capi3c-12.3 {

   923   catchsql {

   924     INSERT INTO t2 VALUES(4);

   925   }

   926 } {0 {}}

   927 do_test capi3c-12.4 {

   928   catchsql {

   929     BEGIN;

   930     INSERT INTO t1 VALUES(4, NULL);

   931   }

   932 } {0 {}}

   933 do_test capi3c-12.5 {

   934   sqlite3_step $STMT

   935 } {SQLITE_ROW}

   936 do_test capi3c-12.5.1 {

   937   sqlite3_step $STMT

   938 } {SQLITE_ROW}

   939 do_test capi3c-12.6 {

   940   sqlite3_step $STMT

   941 } {SQLITE_DONE}

   942 do_test capi3c-12.7 {

   943   sqlite3_finalize $STMT

   944 } {SQLITE_OK}

   945 do_test capi3c-12.8 {

   946   execsql {

   947     COMMIT;

   948     SELECT a FROM t1;

   949   }

   950 } {1 2 3 4}

   951 

   952 # Test cases capi3c-13.* test the sqlite3_clear_bindings() and 

   953 # sqlite3_sleep APIs.

   954 #

   955 if {[llength [info commands sqlite3_clear_bindings]]>0} {

   956   do_test capi3c-13.1 {

   957     execsql {

   958       DELETE FROM t1;

   959     }

   960     set STMT [sqlite3_prepare_v2 $DB "INSERT INTO t1 VALUES(?, ?)" -1 TAIL]

   961     sqlite3_step $STMT

   962   } {SQLITE_DONE}

   963   do_test capi3c-13.2 {

   964     sqlite3_reset $STMT

   965     sqlite3_bind_text $STMT 1 hello 5

   966     sqlite3_bind_text $STMT 2 world 5

   967     sqlite3_step $STMT

   968   } {SQLITE_DONE}

   969   do_test capi3c-13.3 {

   970     sqlite3_reset $STMT

   971     sqlite3_clear_bindings $STMT

   972     sqlite3_step $STMT

   973   } {SQLITE_DONE}

   974   do_test capi3c-13-4 {

   975     sqlite3_finalize $STMT

   976     execsql {

   977       SELECT * FROM t1;

   978     }

   979   } {{} {} hello world {} {}}

   980 }

   981 if {[llength [info commands sqlite3_sleep]]>0} {

   982   do_test capi3c-13-5 {

   983     set ms [sqlite3_sleep 80]

   984     expr {$ms==80 || $ms==1000}

   985   } {1}

   986 }

   987 

   988 # Ticket #1219:  Make sure binding APIs can handle a NULL pointer.

   989 #

   990 do_test capi3c-14.1 {

   991   set rc [catch {sqlite3_bind_text 0 1 hello 5} msg]

   992   lappend rc $msg

   993 } {1 SQLITE_MISUSE}

   994 

   995 # Ticket #1650:  Honor the nBytes parameter to sqlite3_prepare.

   996 #

   997 do_test capi3c-15.1 {

   998   set sql {SELECT * FROM t2}

   999   set nbytes [string length $sql]

  1000   append sql { WHERE a==1}

  1001   set STMT [sqlite3_prepare_v2 $DB $sql $nbytes TAIL]

  1002   sqlite3_step $STMT

  1003   sqlite3_column_int $STMT 0

  1004 } {1}

  1005 do_test capi3c-15.2 {

  1006   sqlite3_step $STMT

  1007   sqlite3_column_int $STMT 0

  1008 } {2}

  1009 do_test capi3c-15.3 {

  1010   sqlite3_finalize $STMT

  1011 } {SQLITE_OK}

  1012 

  1013 # Make sure code is always generated even if an IF EXISTS or 

  1014 # IF NOT EXISTS clause is present that the table does not or

  1015 # does exists.  That way we will always have a prepared statement

  1016 # to expire when the schema changes.

  1017 #

  1018 do_test capi3c-16.1 {

  1019   set sql {DROP TABLE IF EXISTS t3}

  1020   set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL]

  1021   sqlite3_finalize $STMT

  1022   expr {$STMT!=""}

  1023 } {1}

  1024 do_test capi3c-16.2 {

  1025   set sql {CREATE TABLE IF NOT EXISTS t1(x,y)}

  1026   set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL]

  1027   sqlite3_finalize $STMT

  1028   expr {$STMT!=""}

  1029 } {1}

  1030 

  1031 # But still we do not generate code if there is no SQL

  1032 #

  1033 do_test capi3c-16.3 {

  1034   set STMT [sqlite3_prepare_v2 $DB {} -1 TAIL]

  1035   sqlite3_finalize $STMT

  1036   expr {$STMT==""}

  1037 } {1}

  1038 do_test capi3c-16.4 {

  1039   set STMT [sqlite3_prepare_v2 $DB {;} -1 TAIL]

  1040   sqlite3_finalize $STMT

  1041   expr {$STMT==""}

  1042 } {1}

  1043 

  1044 # Ticket #2154.

  1045 #

  1046 do_test capi3c-17.1 {

  1047   set STMT [sqlite3_prepare_v2 $DB {SELECT max(a) FROM t2} -1 TAIL]

  1048   sqlite3_step $STMT

  1049 } SQLITE_ROW

  1050 do_test capi3c-17.2 {

  1051   sqlite3_column_int $STMT 0

  1052 } 4

  1053 do_test capi3c-17.3 {

  1054   sqlite3_step $STMT

  1055 } SQLITE_DONE

  1056 do_test capi3c-17.4 {

  1057   sqlite3_reset $STMT

  1058   db eval {CREATE INDEX i2 ON t2(a)}

  1059   sqlite3_step $STMT

  1060 } SQLITE_ROW

  1061 do_test capi3c-17.5 {

  1062   sqlite3_column_int $STMT 0

  1063 } 4

  1064 do_test capi3c-17.6 {

  1065   sqlite3_step $STMT

  1066 } SQLITE_DONE

  1067 do_test capi3c-17.7 {

  1068   sqlite3_reset $STMT

  1069   db eval {DROP INDEX i2}

  1070   sqlite3_step $STMT

  1071 } SQLITE_ROW

  1072 do_test capi3c-17.8 {

  1073   sqlite3_column_int $STMT 0

  1074 } 4

  1075 do_test capi3c-17.9 {

  1076   sqlite3_step $STMT

  1077 } SQLITE_DONE

  1078 do_test capi3c-17.10 {

  1079   sqlite3_finalize $STMT

  1080   set STMT [sqlite3_prepare_v2 $DB {SELECT b FROM t1 WHERE a=?} -1 TAIL]

  1081   sqlite3_bind_int $STMT 1 2

  1082   db eval {

  1083     DELETE FROM t1;

  1084     INSERT INTO t1 VALUES(1,'one');

  1085     INSERT INTO t1 VALUES(2,'two');

  1086     INSERT INTO t1 VALUES(3,'three');

  1087     INSERT INTO t1 VALUES(4,'four');

  1088   }

  1089   sqlite3_step $STMT

  1090 } SQLITE_ROW

  1091 do_test capi3c-17.11 {

  1092   sqlite3_column_text $STMT 0

  1093 } two

  1094 do_test capi3c-17.12 {

  1095   sqlite3_step $STMT

  1096 } SQLITE_DONE

  1097 do_test capi3c-17.13 {

  1098   sqlite3_reset $STMT

  1099   db eval {CREATE INDEX i1 ON t1(a)}

  1100   sqlite3_step $STMT

  1101 } SQLITE_ROW

  1102 do_test capi3c-17.14 {

  1103   sqlite3_column_text $STMT 0

  1104 } two

  1105 do_test capi3c-17.15 {

  1106   sqlite3_step $STMT

  1107 } SQLITE_DONE

  1108 do_test capi3c-17.16 {

  1109   sqlite3_reset $STMT

  1110   db eval {DROP INDEX i1}

  1111   sqlite3_step $STMT

  1112 } SQLITE_ROW

  1113 do_test capi3c-17.17 {

  1114   sqlite3_column_text $STMT 0

  1115 } two

  1116 do_test capi3c-17.18 {

  1117   sqlite3_step $STMT

  1118 } SQLITE_DONE

  1119 do_test capi3c-17.99 {

  1120   sqlite3_finalize $STMT

  1121 } SQLITE_OK

  1122 

  1123 # On the mailing list it has been reported that finalizing after

  1124 # an SQLITE_BUSY return leads to a segfault.  Here we test that case.

  1125 #

  1126 do_test capi3c-18.1 {

  1127   sqlite3 db2 test.db

  1128   set STMT [sqlite3_prepare_v2 $DB {SELECT max(a) FROM t1} -1 TAIL]

  1129   sqlite3_step $STMT

  1130 } SQLITE_ROW

  1131 do_test capi3c-18.2 {

  1132   sqlite3_column_int $STMT 0

  1133 } 4

  1134 do_test capi3c-18.3 {

  1135   sqlite3_reset $STMT