1 # 2003 January 29

     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.  The

    12 # focus of this script testing the callback-free C/C++ API.

    13 #

    14 # $Id: capi3.test,v 1.67 2008/07/12 15:55:55 danielk1977 Exp $

    15 #

    16 

    17 set testdir [file dirname $argv0]

    18 source $testdir/tester.tcl

    19 

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

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

    22 proc utf16 {str {nt 1}} {

    23   set r [encoding convertto unicode $str]

    24   if {$nt} {

    25     append r "\x00\x00"

    26   }

    27   return $r

    28 }

    29 

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

    31 proc utf8 {str} {

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

    33   # converting to UTF-8 using TCL.

    34   binary scan $str \c* vals

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

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

    37   }

    38 

    39   set r [encoding convertfrom unicode $str]

    40   return $r

    41 }

    42 

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

    44 # as follows:

    45 #

    46 # capi3-1.*: Test sqlite3_prepare 

    47 # capi3-2.*: Test sqlite3_prepare16

    48 # capi3-3.*: Test sqlite3_open

    49 # capi3-4.*: Test sqlite3_open16

    50 # capi3-5.*: Test the various sqlite3_result_* APIs

    51 # capi3-6.*: Test that sqlite3_close fails if there are outstanding VMs.

    52 #

    53 

    54 set DB [sqlite3_connection_pointer db]

    55 

    56 do_test capi3-1.0 {

    57   sqlite3_get_autocommit $DB

    58 } 1

    59 do_test capi3-1.1 {

    60   set STMT [sqlite3_prepare $DB {SELECT name FROM sqlite_master} -1 TAIL]

    61   sqlite3_finalize $STMT

    62   set TAIL

    63 } {}

    64 do_test capi3-1.2 {

    65   sqlite3_errcode $DB

    66 } {SQLITE_OK}

    67 do_test capi3-1.3 {

    68   sqlite3_errmsg $DB

    69 } {not an error}

    70 do_test capi3-1.4 {

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

    72   set STMT [sqlite3_prepare $DB $sql -1 TAIL]

    73   sqlite3_finalize $STMT

    74   set TAIL

    75 } {SELECT 10}

    76 do_test capi3-1.5 {

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

    78   set STMT [sqlite3_prepare $DB $sql [string length $sql] TAIL]

    79   sqlite3_finalize $STMT

    80   set TAIL

    81 } {SELECT 10}

    82 do_test capi3-1.6 {

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

    84   set STMT [sqlite3_prepare $DB $sql [expr [string length $sql]+1] TAIL]

    85   sqlite3_finalize $STMT

    86   set TAIL

    87 } {SELECT 10}

    88 

    89 do_test capi3-1.7 {

    90   set sql {SELECT namex FROM sqlite_master}

    91   catch {

    92     set STMT [sqlite3_prepare $DB $sql -1 TAIL]

    93   }

    94 } {1}

    95 do_test capi3-1.8 {

    96   sqlite3_errcode $DB

    97 } {SQLITE_ERROR}

    98 do_test capi3-1.9 {

    99   sqlite3_errmsg $DB

   100 } {no such column: namex}

   101 

   102 ifcapable {utf16} {

   103   do_test capi3-2.1 {

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

   105     set STMT [sqlite3_prepare16 $DB $sql16 -1 ::TAIL]

   106     sqlite3_finalize $STMT

   107     utf8 $::TAIL

   108   } {}

   109   do_test capi3-2.2 {

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

   111     set STMT [sqlite3_prepare16 $DB $sql -1 TAIL]

   112     sqlite3_finalize $STMT

   113     utf8 $TAIL

   114   } {SELECT 10}

   115   do_test capi3-2.3 {

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

   117     catch {

   118       set STMT [sqlite3_prepare16 $DB $sql -1 TAIL]

   119     }

   120   } {1}

   121   do_test capi3-2.4 {

   122     sqlite3_errcode $DB

   123   } {SQLITE_ERROR}

   124   do_test capi3-2.5 {

   125     sqlite3_errmsg $DB

   126   } {no such column: namex}

   127 

   128   ifcapable schema_pragmas {

   129     do_test capi3-2.6 {

   130       execsql {CREATE TABLE tablename(x)}

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

   132       set STMT [sqlite3_prepare16 $DB $sql16 -1 TAIL]

   133       sqlite3_step $STMT

   134     } SQLITE_ROW

   135     do_test capi3-2.7 {

   136       sqlite3_step $STMT

   137     } SQLITE_DONE

   138     do_test capi3-2.8 {

   139       sqlite3_finalize $STMT

   140     } SQLITE_OK

   141   }

   142 

   143 } ;# endif utf16

   144 

   145 # rename sqlite3_open sqlite3_open_old

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

   147 

   148 do_test capi3-3.1 {

   149   set db2 [sqlite3_open test.db {}]

   150   sqlite3_errcode $db2

   151 } {SQLITE_OK}

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

   153 do_test capi3-3.2 {

   154   sqlite3_close $db2

   155 } {SQLITE_OK}

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

   157 do_test capi3-3.3 {

   158   catch {

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

   160   }

   161   sqlite3_errcode $db2

   162 } {SQLITE_CANTOPEN}

   163 do_test capi3-3.4 {

   164   sqlite3_errmsg $db2

   165 } {unable to open database file}

   166 do_test capi3-3.5 {

   167   sqlite3_close $db2

   168 } {SQLITE_OK}

   169 do_test capi3-3.6.1-misuse {

   170   sqlite3_close $db2

   171 } {SQLITE_MISUSE}

   172 do_test capi3-3.6.2-misuse {

   173   sqlite3_errmsg $db2

   174 } {library routine called out of sequence}

   175 ifcapable {utf16} {

   176   do_test capi3-3.6.3-misuse {

   177     utf8 [sqlite3_errmsg16 $db2]

   178   } {library routine called out of sequence}

   179 }

   180 

   181 do_test capi3-3.7 {

   182   set db2 [sqlite3_open]

   183   sqlite3_errcode $db2

   184 } {SQLITE_OK}

   185 do_test capi3-3.8 {

   186   sqlite3_close $db2

   187 } {SQLITE_OK}

   188 

   189 # rename sqlite3_open ""

   190 # rename sqlite3_open_old sqlite3_open

   191 

   192 ifcapable {utf16} {

   193 do_test capi3-4.1 {

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

   195   sqlite3_errcode $db2

   196 } {SQLITE_OK}

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

   198 do_test capi3-4.2 {

   199   sqlite3_close $db2

   200 } {SQLITE_OK}

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

   202 do_test capi3-4.3 {

   203   catch {

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

   205   }

   206   sqlite3_errcode $db2

   207 } {SQLITE_CANTOPEN}

   208 do_test capi3-4.4 {

   209   utf8 [sqlite3_errmsg16 $db2]

   210 } {unable to open database file}

   211 do_test capi3-4.5 {

   212   sqlite3_close $db2

   213 } {SQLITE_OK}

   214 } ;# utf16

   215 

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

   217 #

   218 # sqlite3_column_count

   219 # sqlite3_column_name

   220 # sqlite3_column_name16

   221 # sqlite3_column_decltype

   222 # sqlite3_column_decltype16

   223 #

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

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

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

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

   228 #

   229 # Example:

   230 #

   231 # set STMT [sqlite3_prepare "SELECT 1, 2, 2;" -1 DUMMY]

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

   233 #

   234 proc check_header {STMT test names decltypes} {

   235 

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

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

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

   239   set ::idxlist [list]

   240   set ::numcols [sqlite3_column_count $STMT]

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

   242 

   243   # Column names in UTF-8

   244   do_test $test.1 {

   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.2 {

   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.3 {

   263     set cnamelist [list]

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

   265     set cnamelist

   266   } $names

   267 

   268   # Column names in UTF-16

   269   ifcapable {utf16} {

   270     do_test $test.4 {

   271       set cnamelist [list]

   272       foreach i $idxlist {

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

   274       }

   275       set cnamelist

   276     } $names

   277   }

   278 

   279   # Column names in UTF-8

   280   do_test $test.5 {

   281     set cnamelist [list]

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

   283     set cnamelist

   284   } $decltypes

   285 

   286   # Column declaration types in UTF-16

   287   ifcapable {utf16} {

   288     do_test $test.6 {

   289       set cnamelist [list]

   290       foreach i $idxlist {

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

   292       }

   293       set cnamelist

   294     } $decltypes

   295   }

   296 

   297 

   298   # Test some out of range conditions:

   299   ifcapable {utf16} {

   300     do_test $test.7 {

   301       list \

   302         [sqlite3_column_name $STMT -1] \

   303         [sqlite3_column_name16 $STMT -1] \

   304         [sqlite3_column_decltype $STMT -1] \

   305         [sqlite3_column_decltype16 $STMT -1] \

   306         [sqlite3_column_name $STMT $numcols] \

   307         [sqlite3_column_name16 $STMT $numcols] \

   308         [sqlite3_column_decltype $STMT $numcols] \

   309         [sqlite3_column_decltype16 $STMT $numcols]

   310     } {{} {} {} {} {} {} {} {}}

   311   }

   312 } 

   313 

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

   315 #

   316 # sqlite3_column_origin_name

   317 # sqlite3_column_origin_name16

   318 # sqlite3_column_table_name

   319 # sqlite3_column_table_name16

   320 # sqlite3_column_database_name

   321 # sqlite3_column_database_name16

   322 #

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

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

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

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

   327 #

   328 # Example:

   329 #

   330 # set STMT [sqlite3_prepare "SELECT 1, 2, 2;" -1 DUMMY]

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

   332 #

   333 proc check_origin_header {STMT test dbs tables cols} {

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

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

   336   ifcapable columnmetadata {

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

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

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

   340     set ::idxlist [list]

   341     set ::numcols [sqlite3_column_count $STMT]

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

   343   

   344     # Database names in UTF-8

   345     do_test $test.8 {

   346       set cnamelist [list]

   347       foreach i $idxlist {

   348         lappend cnamelist [sqlite3_column_database_name $STMT $i]

   349       } 

   350       set cnamelist

   351     } $dbs

   352   

   353     # Database names in UTF-16

   354     ifcapable {utf16} {

   355       do_test $test.9 {

   356         set cnamelist [list]

   357         foreach i $idxlist {

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

   359         }

   360         set cnamelist

   361       } $dbs

   362     }

   363   

   364     # Table names in UTF-8

   365     do_test $test.10 {

   366       set cnamelist [list]

   367       foreach i $idxlist {

   368         lappend cnamelist [sqlite3_column_table_name $STMT $i]

   369       } 

   370       set cnamelist

   371     } $tables

   372   

   373     # Table names in UTF-16

   374     ifcapable {utf16} {

   375       do_test $test.11 {

   376         set cnamelist [list]

   377         foreach i $idxlist {

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

   379         }

   380         set cnamelist

   381       } $tables

   382     }

   383   

   384     # Origin names in UTF-8

   385     do_test $test.12 {

   386       set cnamelist [list]

   387       foreach i $idxlist {

   388         lappend cnamelist [sqlite3_column_origin_name $STMT $i]

   389       } 

   390       set cnamelist

   391     } $cols

   392   

   393     # Origin declaration types in UTF-16

   394     ifcapable {utf16} {

   395       do_test $test.13 {

   396         set cnamelist [list]

   397         foreach i $idxlist {

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

   399         }

   400         set cnamelist

   401       } $cols

   402     }

   403   }

   404 }

   405 

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

   407 #

   408 # sqlite3_data_count

   409 # sqlite3_column_type

   410 # sqlite3_column_int

   411 # sqlite3_column_text

   412 # sqlite3_column_text16

   413 # sqlite3_column_double

   414 #

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

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

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

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

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

   420 # the values in the current row.

   421 #

   422 # Example:

   423 #

   424 # set STMT [sqlite3_prepare "SELECT 'hello', 1.1, NULL" -1 DUMMY]

   425 # sqlite3_step $STMT

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

   427 #

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

   429 

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

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

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

   433   set ::idxlist [list]

   434   set numcols [sqlite3_data_count $STMT]

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

   436 

   437 # types

   438 do_test $test.1 {

   439   set types [list]

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

   441   set types

   442 } $types

   443 

   444 # Integers

   445 do_test $test.2 {

   446   set ints [list]

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

   448   set ints

   449 } $ints

   450 

   451 # bytes

   452 set lens [list]

   453 foreach i $::idxlist {

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

   455 }

   456 do_test $test.3 {

   457   set bytes [list]

   458   set lens [list]

   459   foreach i $idxlist {

   460     lappend bytes [sqlite3_column_bytes $STMT $i]

   461   }

   462   set bytes

   463 } $lens

   464 

   465 # bytes16

   466 ifcapable {utf16} {

   467   set lens [list]

   468   foreach i $::idxlist {

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

   470   }

   471   do_test $test.4 {

   472     set bytes [list]

   473     set lens [list]

   474     foreach i $idxlist {

   475       lappend bytes [sqlite3_column_bytes16 $STMT $i]

   476     }

   477     set bytes

   478   } $lens

   479 }

   480 

   481 # Blob

   482 do_test $test.5 {

   483   set utf8 [list]

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

   485   set utf8

   486 } $strings

   487 

   488 # UTF-8

   489 do_test $test.6 {

   490   set utf8 [list]

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

   492   set utf8

   493 } $strings

   494 

   495 # Floats

   496 do_test $test.7 {

   497   set utf8 [list]

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

   499   set utf8

   500 } $doubles

   501 

   502 # UTF-16

   503 ifcapable {utf16} {

   504   do_test $test.8 {

   505     set utf8 [list]

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

   507     set utf8

   508   } $strings

   509 }

   510 

   511 # Integers

   512 do_test $test.9 {

   513   set ints [list]

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

   515   set ints

   516 } $ints

   517 

   518 # Floats

   519 do_test $test.10 {

   520   set utf8 [list]

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

   522   set utf8

   523 } $doubles

   524 

   525 # UTF-8

   526 do_test $test.11 {

   527   set utf8 [list]

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

   529   set utf8

   530 } $strings

   531 

   532 # Types

   533 do_test $test.12 {

   534   set types [list]

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

   536   set types

   537 } $types

   538 

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

   540 do_test $test.13 {

   541   sqlite3_column_int $STMT -1

   542 } {0}

   543 do_test $test.13 {

   544   sqlite3_column_text $STMT -1

   545 } {}

   546 

   547 }

   548 

   549 ifcapable !floatingpoint {

   550   finish_test

   551   return

   552 }

   553 

   554 do_test capi3-5.0 {

   555   execsql {

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

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

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

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

   560   }

   561   set sql "SELECT * FROM t1"

   562   set STMT [sqlite3_prepare $DB $sql -1 TAIL]

   563   sqlite3_column_count $STMT

   564 } 3

   565 

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

   567 check_origin_header $STMT capi3-5.1 {main main main} {t1 t1 t1} {a b c}

   568 do_test capi3-5.2 {

   569   sqlite3_step $STMT

   570 } SQLITE_ROW

   571 

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

   573 check_origin_header $STMT capi3-5.3 {main main main} {t1 t1 t1} {a b c}

   574 check_data $STMT capi3-5.4 {INTEGER INTEGER TEXT} {1 2 3} {1.0 2.0 3.0} {1 2 3}

   575 

   576 do_test capi3-5.5 {

   577   sqlite3_step $STMT

   578 } SQLITE_ROW

   579 

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

   581 check_origin_header $STMT capi3-5.6 {main main main} {t1 t1 t1} {a b c}

   582 check_data $STMT capi3-5.7 {TEXT TEXT NULL} {0 0 0} {0.0 0.0 0.0} {one two {}}

   583 

   584 do_test capi3-5.8 {

   585   sqlite3_step $STMT

   586 } SQLITE_ROW

   587 

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

   589 check_origin_header $STMT capi3-5.9 {main main main} {t1 t1 t1} {a b c}

   590 check_data $STMT capi3-5.10 {FLOAT FLOAT TEXT} {1 1 1} {1.2 1.3 1.4} {1.2 1.3 1.4}

   591 

   592 do_test capi3-5.11 {

   593   sqlite3_step $STMT

   594 } SQLITE_DONE

   595 

   596 do_test capi3-5.12 {

   597   sqlite3_finalize $STMT

   598 } SQLITE_OK

   599 

   600 do_test capi3-5.20 {

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

   602   set STMT [sqlite3_prepare $DB $sql -1 TAIL]

   603   sqlite3_column_count $STMT

   604 } 3

   605 

   606 check_header $STMT capi3-5.21 {a sum(b) max(c)} {VARINT {} {}}

   607 check_origin_header $STMT capi3-5.22 {main {} {}} {t1 {} {}} {a {} {}}

   608 do_test capi3-5.23 {

   609   sqlite3_finalize $STMT

   610 } SQLITE_OK

   611 

   612 do_test capi3-5.30 {

   613   set sql "SELECT a AS x, sum(b) AS y, max(c) AS z FROM t1 AS m GROUP BY x"

   614   set STMT [sqlite3_prepare $DB $sql -1 TAIL]

   615   sqlite3_column_count $STMT

   616 } 3

   617 

   618 check_header $STMT capi3-5.31 {x y z} {VARINT {} {}}

   619 check_origin_header $STMT capi3-5.32 {main {} {}} {t1 {} {}} {a {} {}}

   620 do_test capi3-5.33 {

   621   sqlite3_finalize $STMT

   622 } SQLITE_OK

   623 

   624 

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

   626 db close

   627 

   628 do_test capi3-6.0 {

   629   sqlite3 db test.db

   630   set DB [sqlite3_connection_pointer db]

   631   sqlite3_key $DB xyzzy

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

   633   set STMT [sqlite3_prepare $DB $sql -1 TAIL]

   634   expr 0

   635 } {0}

   636 do_test capi3-6.1 {

   637   db cache flush

   638   sqlite3_close $DB

   639 } {SQLITE_BUSY}

   640 do_test capi3-6.2 {

   641   sqlite3_step $STMT

   642 } {SQLITE_ROW}

   643 check_data $STMT capi3-6.3 {INTEGER} {1} {1.0} {1}

   644 do_test capi3-6.3 {

   645   sqlite3_finalize $STMT

   646 } {SQLITE_OK}

   647 do_test capi3-6.4-misuse {

   648   db cache flush

   649   sqlite3_close $DB

   650 } {SQLITE_OK}

   651 db close

   652 

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

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

   655 # 

   656 proc set_file_format {newval} {

   657   hexio_write test.db 44 [hexio_render_int32 $newval]

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

   659   incr schemacookie

   660   hexio_write test.db 40 [hexio_render_int32 $schemacookie]

   661   return {}

   662 }

   663 

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

   665 # 

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

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

   668 }

   669 

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

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

   672   do_test capi3-7.1 {

   673     set_file_format 5

   674   } {}

   675   do_test capi3-7.2 {

   676     sqlite3 db test.db

   677     catchsql {

   678       SELECT * FROM sqlite_master;

   679     }

   680   } {1 {unsupported file format}}

   681   db close

   682 }

   683 

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

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

   686   # sqlite_master table (schema corruption).

   687   do_test capi3-8.1 {

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

   689     sqlite3 db test.db

   690     execsql {

   691       CREATE TABLE t1(a);

   692     }

   693     db close

   694   } {}

   695   do_test capi3-8.2 {

   696     sqlite3 db test.db

   697     execsql {

   698       PRAGMA writable_schema=ON;

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

   700     }

   701     db close

   702   } {}

   703   do_test capi3-8.3 {

   704     sqlite3 db test.db

   705     catchsql {

   706       SELECT * FROM sqlite_master;

   707     }

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

   709   do_test capi3-8.4 {

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

   711     # subsequent fields are all NULL.

   712     db close

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

   714     sqlite3 db test.db

   715     execsql {

   716       CREATE TABLE t1(a);

   717       PRAGMA writable_schema=ON;

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

   719     }

   720     db close

   721   } {};

   722   do_test capi3-8.5 {

   723     sqlite3 db test.db

   724     catchsql {

   725       SELECT * FROM sqlite_master;

   726     }

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

   728   db close

   729 }

   730 file delete -force test.db

   731 file delete -force test.db-journal

   732 

   733 

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

   735 set code2english [list \

   736 SQLITE_OK         {not an error} \

   737 SQLITE_ERROR      {SQL logic error or missing database} \

   738 SQLITE_PERM       {access permission denied} \

   739 SQLITE_ABORT      {callback requested query abort} \

   740 SQLITE_BUSY       {database is locked} \

   741 SQLITE_LOCKED     {database table is locked} \

   742 SQLITE_NOMEM      {out of memory} \

   743 SQLITE_READONLY   {attempt to write a readonly database} \

   744 SQLITE_INTERRUPT  {interrupted} \

   745 SQLITE_IOERR      {disk I/O error} \

   746 SQLITE_CORRUPT    {database disk image is malformed} \

   747 SQLITE_FULL       {database or disk is full} \

   748 SQLITE_CANTOPEN   {unable to open database file} \

   749 SQLITE_EMPTY      {table contains no data} \

   750 SQLITE_SCHEMA     {database schema has changed} \

   751 SQLITE_CONSTRAINT {constraint failed} \

   752 SQLITE_MISMATCH   {datatype mismatch} \

   753 SQLITE_MISUSE     {library routine called out of sequence} \

   754 SQLITE_NOLFS      {large file support is disabled} \

   755 SQLITE_AUTH       {authorization denied} \

   756 SQLITE_FORMAT     {auxiliary database format error} \

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

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

   759 unknownerror      {unknown error} \

   760 ]

   761 

   762 set test_number 1

   763 foreach {code english} $code2english {

   764   do_test capi3-9.$test_number "sqlite3_test_errstr $code" $english

   765   incr test_number

   766 }

   767 

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

   769 ifcapable memdebug {

   770   do_test capi3-10-1 {

   771     sqlite3 db test.db

   772     set DB [sqlite3_connection_pointer db]

   773     sqlite3_memdebug_fail 1

   774     catchsql {

   775       select * from sqlite_master;

   776     }

   777   } {1 {out of memory}}

   778   do_test capi3-10-2 {

   779     sqlite3_errmsg $::DB

   780   } {out of memory}

   781   ifcapable {utf16} {

   782     do_test capi3-10-3 {

   783       utf8 [sqlite3_errmsg16 $::DB]

   784     } {out of memory}

   785   }

   786   db close

   787   sqlite3_memdebug_fail -1

   788   do_test capi3-10-4 {

   789     sqlite3 db test.db

   790     set DB [sqlite3_connection_pointer db]

   791     sqlite3_memdebug_fail 1

   792     catchsql {

   793       select * from sqlite_master where rowid>5;

   794     }

   795   } {1 {out of memory}}

   796   do_test capi3-10-5 {

   797     sqlite3_errmsg $::DB

   798   } {out of memory}

   799   ifcapable {utf16} {

   800     do_test capi3-10-6 {

   801       utf8 [sqlite3_errmsg16 $::DB]

   802     } {out of memory}

   803   }

   804   db close

   805   sqlite3_memdebug_fail -1

   806 }

   807 

   808 # The following tests - capi3-11.* - test that a COMMIT or ROLLBACK

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

   810 # the transaction fails.

   811 sqlite3 db test.db

   812 set DB [sqlite3_connection_pointer db]

   813 sqlite_register_test_function $DB func

   814 do_test capi3-11.1 {

   815   execsql {

   816     BEGIN;

   817     CREATE TABLE t1(a, b);

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

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

   820   }

   821 } {}

   822 do_test capi3-11.1.1 {

   823   sqlite3_get_autocommit $DB

   824 } 0

   825 do_test capi3-11.2 {

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

   827   sqlite3_step $STMT

   828 } {SQLITE_ROW}

   829 do_test capi3-11.3 {

   830   catchsql {

   831     COMMIT;

   832   }

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

   834 do_test capi3-11.3.1 {

   835   sqlite3_get_autocommit $DB

   836 } 0

   837 do_test capi3-11.4 {

   838   sqlite3_step $STMT

   839 } {SQLITE_ERROR}

   840 do_test capi3-11.5 {

   841   sqlite3_finalize $STMT

   842 } {SQLITE_ERROR}

   843 do_test capi3-11.6 {

   844   catchsql {

   845     SELECT * FROM t1;

   846   }

   847 } {0 {1 int 2 notatype}}

   848 do_test capi3-11.6.1 {

   849   sqlite3_get_autocommit $DB

   850 } 0

   851 do_test capi3-11.7 {

   852   catchsql {

   853     COMMIT;

   854   }

   855 } {0 {}}

   856 do_test capi3-11.7.1 {

   857   sqlite3_get_autocommit $DB

   858 } 1

   859 do_test capi3-11.8 {

   860   execsql {

   861     CREATE TABLE t2(a);

   862     INSERT INTO t2 VALUES(1);

   863     INSERT INTO t2 VALUES(2);

   864     BEGIN;

   865     INSERT INTO t2 VALUES(3);

   866   }

   867 } {}

   868 do_test capi3-11.8.1 {

   869   sqlite3_get_autocommit $DB

   870 } 0

   871 do_test capi3-11.9 {

   872   set STMT [sqlite3_prepare $DB "SELECT a FROM t2" -1 TAIL]

   873   sqlite3_step $STMT

   874 } {SQLITE_ROW}

   875 do_test capi3-11.9.1 {

   876   sqlite3_get_autocommit $DB

   877 } 0

   878 do_test capi3-11.9.2 {

   879   catchsql {

   880     ROLLBACK;

   881   }

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

   883 do_test capi3-11.9.3 {

   884   sqlite3_get_autocommit $DB

   885 } 0

   886 do_test capi3-11.10 {

   887   sqlite3_step $STMT

   888 } {SQLITE_ROW}

   889 do_test capi3-11.11 {

   890   sqlite3_step $STMT

   891 } {SQLITE_ROW}

   892 do_test capi3-11.12 {

   893   sqlite3_step $STMT

   894 } {SQLITE_DONE}

   895 do_test capi3-11.13 {

   896   sqlite3_finalize $STMT

   897 } {SQLITE_OK}

   898 do_test capi3-11.14 {

   899   execsql {

   900     SELECT a FROM t2;

   901   }

   902 } {1 2 3}

   903 do_test capi3-11.14.1 {

   904   sqlite3_get_autocommit $DB

   905 } 0

   906 do_test capi3-11.15 {

   907   catchsql {

   908     ROLLBACK;

   909   }

   910 } {0 {}}

   911 do_test capi3-11.15.1 {

   912   sqlite3_get_autocommit $DB

   913 } 1

   914 do_test capi3-11.16 {

   915   execsql {

   916     SELECT a FROM t2;

   917   }

   918 } {1 2}

   919 

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

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

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

   923 # count as an active VM.

   924 do_test capi3-11.17 {

   925   execsql {

   926     BEGIN;

   927   }

   928 } {}

   929 do_test capi3-11.18 {

   930   set STMT [sqlite3_prepare $DB "SELECT a FROM t1" -1 TAIL]

   931   catchsql {

   932     COMMIT;

   933   }

   934 } {0 {}}

   935 do_test capi3-11.19 {

   936   sqlite3_step $STMT

   937 } {SQLITE_ROW}

   938 do_test capi3-11.20 {

   939   catchsql {

   940     BEGIN;

   941     COMMIT;

   942   }

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

   944 do_test capi3-11.20 {

   945   sqlite3_reset $STMT

   946   catchsql {

   947     COMMIT;

   948   }

   949 } {0 {}}

   950 do_test capi3-11.21 {

   951   sqlite3_finalize $STMT

   952 } {SQLITE_OK}

   953 

   954 # The following tests - capi3-12.* - check that its Ok to start a

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

   956 # atomic updates in the same situation 

   957 #

   958 do_test capi3-12.1 {

   959   set STMT [sqlite3_prepare $DB "SELECT a FROM t2" -1 TAIL]

   960   sqlite3_step $STMT

   961 } {SQLITE_ROW}

   962 do_test capi3-12.2 {

   963   catchsql {

   964     INSERT INTO t1 VALUES(3, NULL);

   965   }

   966 } {0 {}}

   967 do_test capi3-12.3 {

   968   catchsql {

   969     INSERT INTO t2 VALUES(4);

   970   }

   971 } {0 {}}

   972 do_test capi3-12.4 {

   973   catchsql {

   974     BEGIN;

   975     INSERT INTO t1 VALUES(4, NULL);

   976   }

   977 } {0 {}}

   978 do_test capi3-12.5 {

   979   sqlite3_step $STMT

   980 } {SQLITE_ROW}

   981 do_test capi3-12.5.1 {

   982   sqlite3_step $STMT

   983 } {SQLITE_ROW}

   984 do_test capi3-12.6 {

   985   sqlite3_step $STMT

   986 } {SQLITE_DONE}

   987 do_test capi3-12.7 {

   988   sqlite3_finalize $STMT

   989 } {SQLITE_OK}

   990 do_test capi3-12.8 {

   991   execsql {

   992     COMMIT;

   993     SELECT a FROM t1;

   994   }

   995 } {1 2 3 4}

   996 

   997 # Test cases capi3-13.* test the sqlite3_clear_bindings() and 

   998 # sqlite3_sleep APIs.

   999 #

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

  1001   do_test capi3-13.1 {

  1002     execsql {

  1003       DELETE FROM t1;

  1004     }

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

  1006     sqlite3_step $STMT

  1007   } {SQLITE_DONE}

  1008   do_test capi3-13.2 {

  1009     sqlite3_reset $STMT

  1010     sqlite3_bind_text $STMT 1 hello 5

  1011     sqlite3_bind_text $STMT 2 world 5

  1012     sqlite3_step $STMT

  1013   } {SQLITE_DONE}

  1014   do_test capi3-13.3 {

  1015     sqlite3_reset $STMT

  1016     sqlite3_clear_bindings $STMT

  1017     sqlite3_step $STMT

  1018   } {SQLITE_DONE}

  1019   do_test capi3-13-4 {

  1020     sqlite3_finalize $STMT

  1021     execsql {

  1022       SELECT * FROM t1;

  1023     }

  1024   } {{} {} hello world {} {}}

  1025 }

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

  1027   do_test capi3-13-5 {

  1028     set ms [sqlite3_sleep 80]

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

  1030   } {1}

  1031 }

  1032 

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

  1034 #

  1035 do_test capi3-14.1-misuse {

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

  1037   lappend rc $msg

  1038 } {1 SQLITE_MISUSE}

  1039 

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

  1041 #

  1042 do_test capi3-15.1 {

  1043   set sql {SELECT * FROM t2}

  1044   set nbytes [string length $sql]

  1045   append sql { WHERE a==1}

  1046   set STMT [sqlite3_prepare $DB $sql $nbytes TAIL]

  1047   sqlite3_step $STMT

  1048   sqlite3_column_int $STMT 0

  1049 } {1}

  1050 do_test capi3-15.2 {

  1051   sqlite3_step $STMT

  1052   sqlite3_column_int $STMT 0

  1053 } {2}

  1054 do_test capi3-15.3 {

  1055   sqlite3_finalize $STMT

  1056 } {SQLITE_OK}

  1057 do_test capi3-15.4 {

  1058   #        123456789 1234567

  1059   set sql {SELECT 1234567890}

  1060   set STMT [sqlite3_prepare $DB $sql 8 TAIL]

  1061   sqlite3_step $STMT

  1062   set v1 [sqlite3_column_int $STMT 0]

  1063   sqlite3_finalize $STMT

  1064   set v1

  1065 } {1}

  1066 do_test capi3-15.5 {

  1067   #        123456789 1234567

  1068   set sql {SELECT 1234567890}

  1069   set STMT [sqlite3_prepare $DB $sql 9 TAIL]

  1070   sqlite3_step $STMT

  1071   set v1 [sqlite3_column_int $STMT 0]

  1072   sqlite3_finalize $STMT

  1073   set v1

  1074 } {12}

  1075 do_test capi3-15.6 {

  1076   #        123456789 1234567

  1077   set sql {SELECT 1234567890}

  1078   set STMT [sqlite3_prepare $DB $sql 12 TAIL]

  1079   sqlite3_step $STMT

  1080   set v1 [sqlite3_column_int $STMT 0]

  1081   sqlite3_finalize $STMT

  1082   set v1

  1083 } {12345}

  1084 do_test capi3-15.7 {

  1085   #        123456789 1234567

  1086   set sql {SELECT 12.34567890}

  1087   set STMT [sqlite3_prepare $DB $sql 12 TAIL]

  1088   sqlite3_step $STMT

  1089   set v1 [sqlite3_column_double $STMT 0]

  1090   sqlite3_finalize $STMT

  1091   set v1

  1092 } {12.34}

  1093 do_test capi3-15.8 {

  1094   #        123456789 1234567

  1095   set sql {SELECT 12.34567890}

  1096   set STMT [sqlite3_prepare $DB $sql 14 TAIL]

  1097   sqlite3_step $STMT

  1098   set v1 [sqlite3_column_double $STMT 0]

  1099   sqlite3_finalize $STMT

  1100   set v1

  1101 } {12.3456}

  1102 

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

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

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

  1106 # to expire when the schema changes.

  1107 #

  1108 do_test capi3-16.1 {

  1109   set sql {DROP TABLE IF EXISTS t3}

  1110   set STMT [sqlite3_prepare $DB $sql -1 TAIL]

  1111   sqlite3_finalize $STMT

  1112   expr {$STMT!=""}

  1113 } {1}

  1114 do_test capi3-16.2 {

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

  1116   set STMT [sqlite3_prepare $DB $sql -1 TAIL]

  1117   sqlite3_finalize $STMT

  1118   expr {$STMT!=""}

  1119 } {1}

  1120 

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

  1122 #

  1123 do_test capi3-16.3 {

  1124   set STMT [sqlite3_prepare $DB {} -1 TAIL]

  1125   sqlite3_finalize $STMT

  1126   expr {$STMT==""}

  1127 } {1}

  1128 do_test capi3-16.4 {

  1129   set STMT [sqlite3_prepare $DB {;} -1 TAIL]

  1130   sqlite3_finalize $STMT

  1131   expr {$STMT==""}

  1132 } {1}

  1133 

  1134 # Ticket #2426:  Misuse of sqlite3_column_* by calling it after

  1135 # a sqlite3_reset should be harmless.