• Home
  • Line#
  • Scopes#
  • Navigate#
  • Raw
  • Download
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.70 2009/01/09 02:49:32 drh Exp $
15#
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19
20# Do not use a codec for tests in this file, as the database file is
21# manipulated directly using tcl scripts (using the [hexio_write] command).
22#
23do_not_use_codec
24
25# Return the UTF-16 representation of the supplied UTF-8 string $str.
26# If $nt is true, append two 0x00 bytes as a nul terminator.
27proc utf16 {str {nt 1}} {
28  set r [encoding convertto unicode $str]
29  if {$nt} {
30    append r "\x00\x00"
31  }
32  return $r
33}
34
35# Return the UTF-8 representation of the supplied UTF-16 string $str.
36proc utf8 {str} {
37  # If $str ends in two 0x00 0x00 bytes, knock these off before
38  # converting to UTF-8 using TCL.
39  binary scan $str \c* vals
40  if {[lindex $vals end]==0 && [lindex $vals end-1]==0} {
41    set str [binary format \c* [lrange $vals 0 end-2]]
42  }
43
44  set r [encoding convertfrom unicode $str]
45  return $r
46}
47
48# These tests complement those in capi2.test. They are organized
49# as follows:
50#
51# capi3-1.*: Test sqlite3_prepare
52# capi3-2.*: Test sqlite3_prepare16
53# capi3-3.*: Test sqlite3_open
54# capi3-4.*: Test sqlite3_open16
55# capi3-5.*: Test the various sqlite3_result_* APIs
56# capi3-6.*: Test that sqlite3_close fails if there are outstanding VMs.
57#
58
59set DB [sqlite3_connection_pointer db]
60
61do_test capi3-1.0 {
62  sqlite3_get_autocommit $DB
63} 1
64do_test capi3-1.1 {
65  set STMT [sqlite3_prepare $DB {SELECT name FROM sqlite_master} -1 TAIL]
66  sqlite3_finalize $STMT
67  set TAIL
68} {}
69do_test capi3-1.2.1 {
70  sqlite3_errcode $DB
71} {SQLITE_OK}
72do_test capi3-1.2.2 {
73  sqlite3_extended_errcode $DB
74} {SQLITE_OK}
75do_test capi3-1.3 {
76  sqlite3_errmsg $DB
77} {not an error}
78do_test capi3-1.4 {
79  set sql {SELECT name FROM sqlite_master;SELECT 10}
80  set STMT [sqlite3_prepare $DB $sql -1 TAIL]
81  sqlite3_finalize $STMT
82  set TAIL
83} {SELECT 10}
84do_test capi3-1.5 {
85  set sql {SELECT name FROM sqlite_master;SELECT 10}
86  set STMT [sqlite3_prepare $DB $sql [string length $sql] TAIL]
87  sqlite3_finalize $STMT
88  set TAIL
89} {SELECT 10}
90do_test capi3-1.6 {
91  set sql {SELECT name FROM sqlite_master;SELECT 10}
92  set STMT [sqlite3_prepare $DB $sql [expr [string length $sql]+1] TAIL]
93  sqlite3_finalize $STMT
94  set TAIL
95} {SELECT 10}
96
97do_test capi3-1.7 {
98  set sql {SELECT namex FROM sqlite_master}
99  catch {
100    set STMT [sqlite3_prepare $DB $sql -1 TAIL]
101  }
102} {1}
103do_test capi3-1.8.1 {
104  sqlite3_errcode $DB
105} {SQLITE_ERROR}
106do_test capi3-1.8.2 {
107  sqlite3_extended_errcode $DB
108} {SQLITE_ERROR}
109do_test capi3-1.9 {
110  sqlite3_errmsg $DB
111} {no such column: namex}
112
113ifcapable {utf16} {
114  do_test capi3-2.1 {
115    set sql16 [utf16 {SELECT name FROM sqlite_master}]
116    set STMT [sqlite3_prepare16 $DB $sql16 -1 ::TAIL]
117    sqlite3_finalize $STMT
118    utf8 $::TAIL
119  } {}
120  do_test capi3-2.2 {
121    set sql [utf16 {SELECT name FROM sqlite_master;SELECT 10}]
122    set STMT [sqlite3_prepare16 $DB $sql -1 TAIL]
123    sqlite3_finalize $STMT
124    utf8 $TAIL
125  } {SELECT 10}
126  do_test capi3-2.3 {
127    set sql [utf16 {SELECT namex FROM sqlite_master}]
128    catch {
129      set STMT [sqlite3_prepare16 $DB $sql -1]
130    }
131  } {1}
132  do_test capi3-2.4.1 {
133    sqlite3_errcode $DB
134  } {SQLITE_ERROR}
135  do_test capi3-2.4.2 {
136    sqlite3_extended_errcode $DB
137  } {SQLITE_ERROR}
138  do_test capi3-2.5 {
139    sqlite3_errmsg $DB
140  } {no such column: namex}
141
142  ifcapable schema_pragmas {
143    do_test capi3-2.6 {
144      execsql {CREATE TABLE tablename(x)}
145      set sql16 [utf16 {PRAGMA table_info("TableName"); --excess text}]
146      set STMT [sqlite3_prepare16 $DB $sql16 -1]
147      sqlite3_step $STMT
148    } SQLITE_ROW
149    do_test capi3-2.7 {
150      sqlite3_step $STMT
151    } SQLITE_DONE
152    do_test capi3-2.8 {
153      sqlite3_finalize $STMT
154    } SQLITE_OK
155  }
156
157} ;# endif utf16
158
159# rename sqlite3_open sqlite3_open_old
160# proc sqlite3_open {fname options} {sqlite3_open_new $fname $options}
161
162do_test capi3-3.1 {
163  set db2 [sqlite3_open test.db {}]
164  sqlite3_errcode $db2
165} {SQLITE_OK}
166# FIX ME: Should test the db handle works.
167do_test capi3-3.2 {
168  sqlite3_close $db2
169} {SQLITE_OK}
170do_test capi3-3.3 {
171  catch {
172    set db2 [sqlite3_open /bogus/path/test.db {}]
173  }
174  sqlite3_extended_errcode $db2
175} {SQLITE_CANTOPEN}
176do_test capi3-3.4 {
177  sqlite3_errmsg $db2
178} {unable to open database file}
179do_test capi3-3.5 {
180  sqlite3_close $db2
181} {SQLITE_OK}
182do_test capi3-3.6.1-misuse {
183  sqlite3_close $db2
184} {SQLITE_MISUSE}
185do_test capi3-3.6.2-misuse {
186  sqlite3_errmsg $db2
187} {library routine called out of sequence}
188ifcapable {utf16} {
189  do_test capi3-3.6.3-misuse {
190    utf8 [sqlite3_errmsg16 $db2]
191  } {library routine called out of sequence}
192}
193
194do_test capi3-3.7 {
195  set db2 [sqlite3_open]
196  sqlite3_errcode $db2
197} {SQLITE_OK}
198do_test capi3-3.8 {
199  sqlite3_close $db2
200} {SQLITE_OK}
201
202# rename sqlite3_open ""
203# rename sqlite3_open_old sqlite3_open
204
205ifcapable {utf16} {
206do_test capi3-4.1 {
207  set db2 [sqlite3_open16 [utf16 test.db] {}]
208  sqlite3_errcode $db2
209} {SQLITE_OK}
210# FIX ME: Should test the db handle works.
211do_test capi3-4.2 {
212  sqlite3_close $db2
213} {SQLITE_OK}
214do_test capi3-4.3 {
215  catch {
216    set db2 [sqlite3_open16 [utf16 /bogus/path/test.db] {}]
217  }
218  sqlite3_errcode $db2
219} {SQLITE_CANTOPEN}
220do_test capi3-4.4 {
221  utf8 [sqlite3_errmsg16 $db2]
222} {unable to open database file}
223do_test capi3-4.5 {
224  sqlite3_close $db2
225} {SQLITE_OK}
226} ;# utf16
227
228# This proc is used to test the following API calls:
229#
230# sqlite3_column_count
231# sqlite3_column_name
232# sqlite3_column_name16
233# sqlite3_column_decltype
234# sqlite3_column_decltype16
235#
236# $STMT is a compiled SQL statement. $test is a prefix
237# to use for test names within this proc. $names is a list
238# of the column names that should be returned by $STMT.
239# $decltypes is a list of column declaration types for $STMT.
240#
241# Example:
242#
243# set STMT [sqlite3_prepare "SELECT 1, 2, 2;" -1 DUMMY]
244# check_header test1.1 {1 2 3} {"" "" ""}
245#
246proc check_header {STMT test names decltypes} {
247
248  # Use the return value of sqlite3_column_count() to build
249  # a list of column indexes. i.e. If sqlite3_column_count
250  # is 3, build the list {0 1 2}.
251  set ::idxlist [list]
252  set ::numcols [sqlite3_column_count $STMT]
253  for {set i 0} {$i < $::numcols} {incr i} {lappend ::idxlist $i}
254
255  # Column names in UTF-8
256  do_test $test.1 {
257    set cnamelist [list]
258    foreach i $idxlist {lappend cnamelist [sqlite3_column_name $STMT $i]}
259    set cnamelist
260  } $names
261
262  # Column names in UTF-16
263  ifcapable {utf16} {
264    do_test $test.2 {
265      set cnamelist [list]
266      foreach i $idxlist {
267        lappend cnamelist [utf8 [sqlite3_column_name16 $STMT $i]]
268      }
269      set cnamelist
270    } $names
271  }
272
273  # Column names in UTF-8
274  do_test $test.3 {
275    set cnamelist [list]
276    foreach i $idxlist {lappend cnamelist [sqlite3_column_name $STMT $i]}
277    set cnamelist
278  } $names
279
280  # Column names in UTF-16
281  ifcapable {utf16} {
282    do_test $test.4 {
283      set cnamelist [list]
284      foreach i $idxlist {
285        lappend cnamelist [utf8 [sqlite3_column_name16 $STMT $i]]
286      }
287      set cnamelist
288    } $names
289  }
290
291  # Column names in UTF-8
292  do_test $test.5 {
293    set cnamelist [list]
294    foreach i $idxlist {lappend cnamelist [sqlite3_column_decltype $STMT $i]}
295    set cnamelist
296  } $decltypes
297
298  # Column declaration types in UTF-16
299  ifcapable {utf16} {
300    do_test $test.6 {
301      set cnamelist [list]
302      foreach i $idxlist {
303        lappend cnamelist [utf8 [sqlite3_column_decltype16 $STMT $i]]
304      }
305      set cnamelist
306    } $decltypes
307  }
308
309
310  # Test some out of range conditions:
311  ifcapable {utf16} {
312    do_test $test.7 {
313      list \
314        [sqlite3_column_name $STMT -1] \
315        [sqlite3_column_name16 $STMT -1] \
316        [sqlite3_column_decltype $STMT -1] \
317        [sqlite3_column_decltype16 $STMT -1] \
318        [sqlite3_column_name $STMT $numcols] \
319        [sqlite3_column_name16 $STMT $numcols] \
320        [sqlite3_column_decltype $STMT $numcols] \
321        [sqlite3_column_decltype16 $STMT $numcols]
322    } {{} {} {} {} {} {} {} {}}
323  }
324}
325
326# This proc is used to test the following API calls:
327#
328# sqlite3_column_origin_name
329# sqlite3_column_origin_name16
330# sqlite3_column_table_name
331# sqlite3_column_table_name16
332# sqlite3_column_database_name
333# sqlite3_column_database_name16
334#
335# $STMT is a compiled SQL statement. $test is a prefix
336# to use for test names within this proc. $names is a list
337# of the column names that should be returned by $STMT.
338# $decltypes is a list of column declaration types for $STMT.
339#
340# Example:
341#
342# set STMT [sqlite3_prepare "SELECT 1, 2, 2;" -1 DUMMY]
343# check_header test1.1 {1 2 3} {"" "" ""}
344#
345proc check_origin_header {STMT test dbs tables cols} {
346  # If sqlite3_column_origin_name() and friends are not compiled into
347  # this build, this proc is a no-op.
348  ifcapable columnmetadata {
349    # Use the return value of sqlite3_column_count() to build
350    # a list of column indexes. i.e. If sqlite3_column_count
351    # is 3, build the list {0 1 2}.
352    set ::idxlist [list]
353    set ::numcols [sqlite3_column_count $STMT]
354    for {set i 0} {$i < $::numcols} {incr i} {lappend ::idxlist $i}
355
356    # Database names in UTF-8
357    do_test $test.8 {
358      set cnamelist [list]
359      foreach i $idxlist {
360        lappend cnamelist [sqlite3_column_database_name $STMT $i]
361      }
362      set cnamelist
363    } $dbs
364
365    # Database names in UTF-16
366    ifcapable {utf16} {
367      do_test $test.9 {
368        set cnamelist [list]
369        foreach i $idxlist {
370          lappend cnamelist [utf8 [sqlite3_column_database_name16 $STMT $i]]
371        }
372        set cnamelist
373      } $dbs
374    }
375
376    # Table names in UTF-8
377    do_test $test.10 {
378      set cnamelist [list]
379      foreach i $idxlist {
380        lappend cnamelist [sqlite3_column_table_name $STMT $i]
381      }
382      set cnamelist
383    } $tables
384
385    # Table names in UTF-16
386    ifcapable {utf16} {
387      do_test $test.11 {
388        set cnamelist [list]
389        foreach i $idxlist {
390          lappend cnamelist [utf8 [sqlite3_column_table_name16 $STMT $i]]
391        }
392        set cnamelist
393      } $tables
394    }
395
396    # Origin names in UTF-8
397    do_test $test.12 {
398      set cnamelist [list]
399      foreach i $idxlist {
400        lappend cnamelist [sqlite3_column_origin_name $STMT $i]
401      }
402      set cnamelist
403    } $cols
404
405    # Origin declaration types in UTF-16
406    ifcapable {utf16} {
407      do_test $test.13 {
408        set cnamelist [list]
409        foreach i $idxlist {
410          lappend cnamelist [utf8 [sqlite3_column_origin_name16 $STMT $i]]
411        }
412        set cnamelist
413      } $cols
414    }
415  }
416}
417
418# This proc is used to test the following APIs:
419#
420# sqlite3_data_count
421# sqlite3_column_type
422# sqlite3_column_int
423# sqlite3_column_text
424# sqlite3_column_text16
425# sqlite3_column_double
426#
427# $STMT is a compiled SQL statement for which the previous call
428# to sqlite3_step returned SQLITE_ROW. $test is a prefix to use
429# for test names within this proc. $types is a list of the
430# manifest types for the current row. $ints, $doubles and $strings
431# are lists of the integer, real and string representations of
432# the values in the current row.
433#
434# Example:
435#
436# set STMT [sqlite3_prepare "SELECT 'hello', 1.1, NULL" -1 DUMMY]
437# sqlite3_step $STMT
438# check_data test1.2 {TEXT REAL NULL} {0 1 0} {0 1.1 0} {hello 1.1 {}}
439#
440proc check_data {STMT test types ints doubles strings} {
441
442  # Use the return value of sqlite3_column_count() to build
443  # a list of column indexes. i.e. If sqlite3_column_count
444  # is 3, build the list {0 1 2}.
445  set ::idxlist [list]
446  set numcols [sqlite3_data_count $STMT]
447  for {set i 0} {$i < $numcols} {incr i} {lappend ::idxlist $i}
448
449# types
450do_test $test.1 {
451  set types [list]
452  foreach i $idxlist {lappend types [sqlite3_column_type $STMT $i]}
453  set types
454} $types
455
456# Integers
457do_test $test.2 {
458  set ints [list]
459  foreach i $idxlist {lappend ints [sqlite3_column_int64 $STMT $i]}
460  set ints
461} $ints
462
463# bytes
464set lens [list]
465foreach i $::idxlist {
466  lappend lens [string length [lindex $strings $i]]
467}
468do_test $test.3 {
469  set bytes [list]
470  set lens [list]
471  foreach i $idxlist {
472    lappend bytes [sqlite3_column_bytes $STMT $i]
473  }
474  set bytes
475} $lens
476
477# bytes16
478ifcapable {utf16} {
479  set lens [list]
480  foreach i $::idxlist {
481    lappend lens [expr 2 * [string length [lindex $strings $i]]]
482  }
483  do_test $test.4 {
484    set bytes [list]
485    set lens [list]
486    foreach i $idxlist {
487      lappend bytes [sqlite3_column_bytes16 $STMT $i]
488    }
489    set bytes
490  } $lens
491}
492
493# Blob
494do_test $test.5 {
495  set utf8 [list]
496  foreach i $idxlist {lappend utf8 [sqlite3_column_blob $STMT $i]}
497  set utf8
498} $strings
499
500# UTF-8
501do_test $test.6 {
502  set utf8 [list]
503  foreach i $idxlist {lappend utf8 [sqlite3_column_text $STMT $i]}
504  set utf8
505} $strings
506
507# Floats
508do_test $test.7 {
509  set utf8 [list]
510  foreach i $idxlist {lappend utf8 [sqlite3_column_double $STMT $i]}
511  set utf8
512} $doubles
513
514# UTF-16
515ifcapable {utf16} {
516  do_test $test.8 {
517    set utf8 [list]
518    foreach i $idxlist {lappend utf8 [utf8 [sqlite3_column_text16 $STMT $i]]}
519    set utf8
520  } $strings
521}
522
523# Integers
524do_test $test.9 {
525  set ints [list]
526  foreach i $idxlist {lappend ints [sqlite3_column_int $STMT $i]}
527  set ints
528} $ints
529
530# Floats
531do_test $test.10 {
532  set utf8 [list]
533  foreach i $idxlist {lappend utf8 [sqlite3_column_double $STMT $i]}
534  set utf8
535} $doubles
536
537# UTF-8
538do_test $test.11 {
539  set utf8 [list]
540  foreach i $idxlist {lappend utf8 [sqlite3_column_text $STMT $i]}
541  set utf8
542} $strings
543
544# Types
545do_test $test.12 {
546  set types [list]
547  foreach i $idxlist {lappend types [sqlite3_column_type $STMT $i]}
548  set types
549} $types
550
551# Test that an out of range request returns the equivalent of NULL
552do_test $test.13 {
553  sqlite3_column_int $STMT -1
554} {0}
555do_test $test.13 {
556  sqlite3_column_text $STMT -1
557} {}
558
559}
560
561ifcapable !floatingpoint {
562  finish_test
563  return
564}
565
566do_test capi3-5.0 {
567  execsql {
568    CREATE TABLE t1(a VARINT, b BLOB, c VARCHAR(16));
569    INSERT INTO t1 VALUES(1, 2, 3);
570    INSERT INTO t1 VALUES('one', 'two', NULL);
571    INSERT INTO t1 VALUES(1.2, 1.3, 1.4);
572  }
573  set sql "SELECT * FROM t1"
574  set STMT [sqlite3_prepare $DB $sql -1 TAIL]
575  sqlite3_column_count $STMT
576} 3
577
578check_header $STMT capi3-5.1 {a b c} {VARINT BLOB VARCHAR(16)}
579check_origin_header $STMT capi3-5.1 {main main main} {t1 t1 t1} {a b c}
580do_test capi3-5.2 {
581  sqlite3_step $STMT
582} SQLITE_ROW
583
584check_header $STMT capi3-5.3 {a b c} {VARINT BLOB VARCHAR(16)}
585check_origin_header $STMT capi3-5.3 {main main main} {t1 t1 t1} {a b c}
586check_data $STMT capi3-5.4 {INTEGER INTEGER TEXT} {1 2 3} {1.0 2.0 3.0} {1 2 3}
587
588do_test capi3-5.5 {
589  sqlite3_step $STMT
590} SQLITE_ROW
591
592check_header $STMT capi3-5.6 {a b c} {VARINT BLOB VARCHAR(16)}
593check_origin_header $STMT capi3-5.6 {main main main} {t1 t1 t1} {a b c}
594check_data $STMT capi3-5.7 {TEXT TEXT NULL} {0 0 0} {0.0 0.0 0.0} {one two {}}
595
596do_test capi3-5.8 {
597  sqlite3_step $STMT
598} SQLITE_ROW
599
600check_header $STMT capi3-5.9 {a b c} {VARINT BLOB VARCHAR(16)}
601check_origin_header $STMT capi3-5.9 {main main main} {t1 t1 t1} {a b c}
602check_data $STMT capi3-5.10 {FLOAT FLOAT TEXT} {1 1 1} {1.2 1.3 1.4} {1.2 1.3 1.4}
603
604do_test capi3-5.11 {
605  sqlite3_step $STMT
606} SQLITE_DONE
607
608do_test capi3-5.12 {
609  sqlite3_finalize $STMT
610} SQLITE_OK
611
612do_test capi3-5.20 {
613  set sql "SELECT a, sum(b), max(c) FROM t1 GROUP BY a"
614  set STMT [sqlite3_prepare $DB $sql -1 TAIL]
615  sqlite3_column_count $STMT
616} 3
617
618check_header $STMT capi3-5.21 {a sum(b) max(c)} {VARINT {} {}}
619check_origin_header $STMT capi3-5.22 {main {} {}} {t1 {} {}} {a {} {}}
620do_test capi3-5.23 {
621  sqlite3_finalize $STMT
622} SQLITE_OK
623
624do_test capi3-5.30 {
625  set sql "SELECT a AS x, sum(b) AS y, max(c) AS z FROM t1 AS m GROUP BY x"
626  set STMT [sqlite3_prepare $DB $sql -1 TAIL]
627  sqlite3_column_count $STMT
628} 3
629
630check_header $STMT capi3-5.31 {x y z} {VARINT {} {}}
631check_origin_header $STMT capi3-5.32 {main {} {}} {t1 {} {}} {a {} {}}
632do_test capi3-5.33 {
633  sqlite3_finalize $STMT
634} SQLITE_OK
635
636
637set ::ENC [execsql {pragma encoding}]
638db close
639
640do_test capi3-6.0 {
641  sqlite3 db test.db
642  set DB [sqlite3_connection_pointer db]
643  if {[sqlite3 -has-codec]==0} { sqlite3_key $DB xyzzy }
644  set sql {SELECT a FROM t1 order by rowid}
645  set STMT [sqlite3_prepare $DB $sql -1 TAIL]
646  expr 0
647} {0}
648do_test capi3-6.1 {
649  db cache flush
650  sqlite3_close $DB
651} {SQLITE_BUSY}
652do_test capi3-6.2 {
653  sqlite3_step $STMT
654} {SQLITE_ERROR}
655#check_data $STMT capi3-6.3 {INTEGER} {1} {1.0} {1}
656do_test capi3-6.3 {
657  sqlite3_finalize $STMT
658} {SQLITE_SCHEMA}
659do_test capi3-6.4-misuse {
660  db cache flush
661  sqlite3_close $DB
662} {SQLITE_OK}
663db close
664
665# This procedure sets the value of the file-format in file 'test.db'
666# to $newval. Also, the schema cookie is incremented.
667#
668proc set_file_format {newval} {
669  hexio_write test.db 44 [hexio_render_int32 $newval]
670  set schemacookie [hexio_get_int [hexio_read test.db 40 4]]
671  incr schemacookie
672  hexio_write test.db 40 [hexio_render_int32 $schemacookie]
673  return {}
674}
675
676# This procedure returns the value of the file-format in file 'test.db'.
677#
678proc get_file_format {{fname test.db}} {
679  return [hexio_get_int [hexio_read $fname 44 4]]
680}
681
682if {![sqlite3 -has-codec]} {
683  # Test what happens when the library encounters a newer file format.
684  do_test capi3-7.1 {
685    set_file_format 5
686  } {}
687  do_test capi3-7.2 {
688    catch { sqlite3 db test.db }
689    catchsql {
690      SELECT * FROM sqlite_master;
691    }
692  } {1 {unsupported file format}}
693  db close
694}
695
696if {![sqlite3 -has-codec]} {
697  # Now test that the library correctly handles bogus entries in the
698  # sqlite_master table (schema corruption).
699  do_test capi3-8.1 {
700    file delete -force test.db test.db-journal
701    sqlite3 db test.db
702    execsql {
703      CREATE TABLE t1(a);
704    }
705    db close
706  } {}
707  do_test capi3-8.2 {
708    sqlite3 db test.db
709    execsql {
710      PRAGMA writable_schema=ON;
711      INSERT INTO sqlite_master VALUES(NULL,NULL,NULL,NULL,NULL);
712    }
713    db close
714  } {}
715  do_test capi3-8.3 {
716    catch { sqlite3 db test.db }
717    catchsql {
718      SELECT * FROM sqlite_master;
719    }
720  } {1 {malformed database schema (?)}}
721  do_test capi3-8.4 {
722    # Build a 5-field row record. The first field is a string 'table', and
723    # subsequent fields are all NULL.
724    db close
725    file delete -force test.db test.db-journal
726    sqlite3 db test.db
727    execsql {
728      CREATE TABLE t1(a);
729      PRAGMA writable_schema=ON;
730      INSERT INTO sqlite_master VALUES('table',NULL,NULL,NULL,NULL);
731    }
732    db close
733  } {};
734  do_test capi3-8.5 {
735    catch { sqlite3 db test.db }
736    catchsql {
737      SELECT * FROM sqlite_master;
738    }
739  } {1 {malformed database schema (?)}}
740  db close
741}
742file delete -force test.db
743file delete -force test.db-journal
744
745
746# Test the english language string equivalents for sqlite error codes
747set code2english [list \
748SQLITE_OK         {not an error} \
749SQLITE_ERROR      {SQL logic error or missing database} \
750SQLITE_PERM       {access permission denied} \
751SQLITE_ABORT      {callback requested query abort} \
752SQLITE_BUSY       {database is locked} \
753SQLITE_LOCKED     {database table is locked} \
754SQLITE_NOMEM      {out of memory} \
755SQLITE_READONLY   {attempt to write a readonly database} \
756SQLITE_INTERRUPT  {interrupted} \
757SQLITE_IOERR      {disk I/O error} \
758SQLITE_CORRUPT    {database disk image is malformed} \
759SQLITE_FULL       {database or disk is full} \
760SQLITE_CANTOPEN   {unable to open database file} \
761SQLITE_EMPTY      {table contains no data} \
762SQLITE_SCHEMA     {database schema has changed} \
763SQLITE_CONSTRAINT {constraint failed} \
764SQLITE_MISMATCH   {datatype mismatch} \
765SQLITE_MISUSE     {library routine called out of sequence} \
766SQLITE_NOLFS      {large file support is disabled} \
767SQLITE_AUTH       {authorization denied} \
768SQLITE_FORMAT     {auxiliary database format error} \
769SQLITE_RANGE      {bind or column index out of range} \
770SQLITE_NOTADB     {file is encrypted or is not a database} \
771unknownerror      {unknown error} \
772]
773
774set test_number 1
775foreach {code english} $code2english {
776  do_test capi3-9.$test_number "sqlite3_test_errstr $code" $english
777  incr test_number
778}
779
780# Test the error message when a "real" out of memory occurs.
781ifcapable memdebug {
782  do_test capi3-10-1 {
783    sqlite3 db test.db
784    set DB [sqlite3_connection_pointer db]
785    sqlite3_memdebug_fail 1
786    catchsql {
787      select * from sqlite_master;
788    }
789  } {1 {out of memory}}
790  do_test capi3-10-2 {
791    sqlite3_errmsg $::DB
792  } {out of memory}
793  ifcapable {utf16} {
794    do_test capi3-10-3 {
795      utf8 [sqlite3_errmsg16 $::DB]
796    } {out of memory}
797  }
798  db close
799  sqlite3_memdebug_fail -1
800  do_test capi3-10-4 {
801    sqlite3 db test.db
802    set DB [sqlite3_connection_pointer db]
803    sqlite3_memdebug_fail 1
804    catchsql {
805      select * from sqlite_master where rowid>5;
806    }
807  } {1 {out of memory}}
808  do_test capi3-10-5 {
809    sqlite3_errmsg $::DB
810  } {out of memory}
811  ifcapable {utf16} {
812    do_test capi3-10-6 {
813      utf8 [sqlite3_errmsg16 $::DB]
814    } {out of memory}
815  }
816  db close
817  sqlite3_memdebug_fail -1
818}
819
820# The following tests - capi3-11.* - test that a COMMIT or ROLLBACK
821# statement issued while there are still outstanding VMs that are part of
822# the transaction fails.
823sqlite3 db test.db
824set DB [sqlite3_connection_pointer db]
825sqlite_register_test_function $DB func
826do_test capi3-11.1 {
827  execsql {
828    BEGIN;
829    CREATE TABLE t1(a, b);
830    INSERT INTO t1 VALUES(1, 'int');
831    INSERT INTO t1 VALUES(2, 'notatype');
832  }
833} {}
834do_test capi3-11.1.1 {
835  sqlite3_get_autocommit $DB
836} 0
837do_test capi3-11.2 {
838  set STMT [sqlite3_prepare $DB "SELECT func(b, a) FROM t1" -1 TAIL]
839  sqlite3_step $STMT
840} {SQLITE_ROW}
841
842# As of 3.6.5 a COMMIT is OK during while a query is still running -
843# as long as it is a read-only query and not an incremental BLOB write.
844#
845do_test capi3-11.3.1 {
846  catchsql {
847    COMMIT;
848  }
849} {0 {}}
850do_test capi3-11.3.2 {
851  sqlite3_extended_errcode $DB
852} {SQLITE_OK}
853do_test capi3-11.3.3 {
854  sqlite3_get_autocommit $DB
855} 1
856do_test capi3-11.3.4 {
857  db eval {PRAGMA lock_status}
858} {main shared temp closed}
859
860do_test capi3-11.4 {
861  sqlite3_step $STMT
862} {SQLITE_ERROR}
863do_test capi3-11.5 {
864  sqlite3_finalize $STMT
865} {SQLITE_ERROR}
866do_test capi3-11.6 {
867  catchsql {
868    SELECT * FROM t1;
869  }
870} {0 {1 int 2 notatype}}
871do_test capi3-11.7 {
872  sqlite3_get_autocommit $DB
873} 1
874do_test capi3-11.8 {
875  execsql {
876    CREATE TABLE t2(a);
877    INSERT INTO t2 VALUES(1);
878    INSERT INTO t2 VALUES(2);
879    BEGIN;
880    INSERT INTO t2 VALUES(3);
881  }
882} {}
883do_test capi3-11.8.1 {
884  sqlite3_get_autocommit $DB
885} 0
886do_test capi3-11.9 {
887  set STMT [sqlite3_prepare $DB "SELECT a FROM t2" -1 TAIL]
888  sqlite3_step $STMT
889} {SQLITE_ROW}
890do_test capi3-11.9.1 {
891  sqlite3_get_autocommit $DB
892} 0
893do_test capi3-11.9.2 {
894  catchsql {
895    ROLLBACK;
896  }
897} {1 {cannot rollback transaction - SQL statements in progress}}
898do_test capi3-11.9.3 {
899  sqlite3_get_autocommit $DB
900} 0
901do_test capi3-11.10 {
902  sqlite3_step $STMT
903} {SQLITE_ROW}
904do_test capi3-11.11 {
905  sqlite3_step $STMT
906} {SQLITE_ROW}
907do_test capi3-11.12 {
908  sqlite3_step $STMT
909} {SQLITE_DONE}
910do_test capi3-11.13 {
911  sqlite3_finalize $STMT
912} {SQLITE_OK}
913do_test capi3-11.14 {
914  execsql {
915    SELECT a FROM t2;
916  }
917} {1 2 3}
918do_test capi3-11.14.1 {
919  sqlite3_get_autocommit $DB
920} 0
921do_test capi3-11.15 {
922  catchsql {
923    ROLLBACK;
924  }
925} {0 {}}
926do_test capi3-11.15.1 {
927  sqlite3_get_autocommit $DB
928} 1
929do_test capi3-11.16 {
930  execsql {
931    SELECT a FROM t2;
932  }
933} {1 2}
934
935# Sanity check on the definition of 'outstanding VM'. This means any VM
936# that has had sqlite3_step() called more recently than sqlite3_finalize() or
937# sqlite3_reset(). So a VM that has just been prepared or reset does not
938# count as an active VM.
939do_test capi3-11.17 {
940  execsql {
941    BEGIN;
942  }
943} {}
944do_test capi3-11.18 {
945  set STMT [sqlite3_prepare $DB "SELECT a FROM t1" -1 TAIL]
946  catchsql {
947    COMMIT;
948  }
949} {0 {}}
950do_test capi3-11.19 {
951  sqlite3_step $STMT
952} {SQLITE_ROW}
953do_test capi3-11.20 {
954  catchsql {
955    BEGIN;
956    COMMIT;
957  }
958} {0 {}}
959do_test capi3-11.20 {
960  sqlite3_reset $STMT
961  catchsql {
962    COMMIT;
963  }
964} {1 {cannot commit - no transaction is active}}
965do_test capi3-11.21 {
966  sqlite3_finalize $STMT
967} {SQLITE_OK}
968
969# The following tests - capi3-12.* - check that its Ok to start a
970# transaction while other VMs are active, and that its Ok to execute
971# atomic updates in the same situation
972#
973do_test capi3-12.1 {
974  set STMT [sqlite3_prepare $DB "SELECT a FROM t2" -1 TAIL]
975  sqlite3_step $STMT
976} {SQLITE_ROW}
977do_test capi3-12.2 {
978  catchsql {
979    INSERT INTO t1 VALUES(3, NULL);
980  }
981} {0 {}}
982do_test capi3-12.3 {
983  catchsql {
984    INSERT INTO t2 VALUES(4);
985  }
986} {0 {}}
987do_test capi3-12.4 {
988  catchsql {
989    BEGIN;
990    INSERT INTO t1 VALUES(4, NULL);
991  }
992} {0 {}}
993do_test capi3-12.5 {
994  sqlite3_step $STMT
995} {SQLITE_ROW}
996do_test capi3-12.5.1 {
997  sqlite3_step $STMT
998} {SQLITE_ROW}
999do_test capi3-12.6 {
1000  sqlite3_step $STMT
1001} {SQLITE_DONE}
1002do_test capi3-12.7 {
1003  sqlite3_finalize $STMT
1004} {SQLITE_OK}
1005do_test capi3-12.8 {
1006  execsql {
1007    COMMIT;
1008    SELECT a FROM t1;
1009  }
1010} {1 2 3 4}
1011
1012# Test cases capi3-13.* test the sqlite3_clear_bindings() and
1013# sqlite3_sleep APIs.
1014#
1015if {[llength [info commands sqlite3_clear_bindings]]>0} {
1016  do_test capi3-13.1 {
1017    execsql {
1018      DELETE FROM t1;
1019    }
1020    set STMT [sqlite3_prepare $DB "INSERT INTO t1 VALUES(?, ?)" -1 TAIL]
1021    sqlite3_step $STMT
1022  } {SQLITE_DONE}
1023  do_test capi3-13.2 {
1024    sqlite3_reset $STMT
1025    sqlite3_bind_text $STMT 1 hello 5
1026    sqlite3_bind_text $STMT 2 world 5
1027    sqlite3_step $STMT
1028  } {SQLITE_DONE}
1029  do_test capi3-13.3 {
1030    sqlite3_reset $STMT
1031    sqlite3_clear_bindings $STMT
1032    sqlite3_step $STMT
1033  } {SQLITE_DONE}
1034  do_test capi3-13-4 {
1035    sqlite3_finalize $STMT
1036    execsql {
1037      SELECT * FROM t1;
1038    }
1039  } {{} {} hello world {} {}}
1040}
1041if {[llength [info commands sqlite3_sleep]]>0} {
1042  do_test capi3-13-5 {
1043    set ms [sqlite3_sleep 80]
1044    expr {$ms==80 || $ms==1000}
1045  } {1}
1046}
1047
1048# Ticket #1219:  Make sure binding APIs can handle a NULL pointer.
1049#
1050do_test capi3-14.1-misuse {
1051  set rc [catch {sqlite3_bind_text 0 1 hello 5} msg]
1052  lappend rc $msg
1053} {1 SQLITE_MISUSE}
1054
1055# Ticket #1650:  Honor the nBytes parameter to sqlite3_prepare.
1056#
1057do_test capi3-15.1 {
1058  set sql {SELECT * FROM t2}
1059  set nbytes [string length $sql]
1060  append sql { WHERE a==1}
1061  set STMT [sqlite3_prepare $DB $sql $nbytes TAIL]
1062  sqlite3_step $STMT
1063  sqlite3_column_int $STMT 0
1064} {1}
1065do_test capi3-15.2 {
1066  sqlite3_step $STMT
1067  sqlite3_column_int $STMT 0
1068} {2}
1069do_test capi3-15.3 {
1070  sqlite3_finalize $STMT
1071} {SQLITE_OK}
1072do_test capi3-15.4 {
1073  #        123456789 1234567
1074  set sql {SELECT 1234567890}
1075  set STMT [sqlite3_prepare $DB $sql 8 TAIL]
1076  sqlite3_step $STMT
1077  set v1 [sqlite3_column_int $STMT 0]
1078  sqlite3_finalize $STMT
1079  set v1
1080} {1}
1081do_test capi3-15.5 {
1082  #        123456789 1234567
1083  set sql {SELECT 1234567890}
1084  set STMT [sqlite3_prepare $DB $sql 9 TAIL]
1085  sqlite3_step $STMT
1086  set v1 [sqlite3_column_int $STMT 0]
1087  sqlite3_finalize $STMT
1088  set v1
1089} {12}
1090do_test capi3-15.6 {
1091  #        123456789 1234567
1092  set sql {SELECT 1234567890}
1093  set STMT [sqlite3_prepare $DB $sql 12 TAIL]
1094  sqlite3_step $STMT
1095  set v1 [sqlite3_column_int $STMT 0]
1096  sqlite3_finalize $STMT
1097  set v1
1098} {12345}
1099do_test capi3-15.7 {
1100  #        123456789 1234567
1101  set sql {SELECT 12.34567890}
1102  set STMT [sqlite3_prepare $DB $sql 12 TAIL]
1103  sqlite3_step $STMT
1104  set v1 [sqlite3_column_double $STMT 0]
1105  sqlite3_finalize $STMT
1106  set v1
1107} {12.34}
1108do_test capi3-15.8 {
1109  #        123456789 1234567
1110  set sql {SELECT 12.34567890}
1111  set STMT [sqlite3_prepare $DB $sql 14 TAIL]
1112  sqlite3_step $STMT
1113  set v1 [sqlite3_column_double $STMT 0]
1114  sqlite3_finalize $STMT
1115  set v1
1116} {12.3456}
1117
1118# Make sure code is always generated even if an IF EXISTS or
1119# IF NOT EXISTS clause is present that the table does not or
1120# does exists.  That way we will always have a prepared statement
1121# to expire when the schema changes.
1122#
1123do_test capi3-16.1 {
1124  set sql {DROP TABLE IF EXISTS t3}
1125  set STMT [sqlite3_prepare $DB $sql -1 TAIL]
1126  sqlite3_finalize $STMT
1127  expr {$STMT!=""}
1128} {1}
1129do_test capi3-16.2 {
1130  set sql {CREATE TABLE IF NOT EXISTS t1(x,y)}
1131  set STMT [sqlite3_prepare $DB $sql -1 TAIL]
1132  sqlite3_finalize $STMT
1133  expr {$STMT!=""}
1134} {1}
1135
1136# But still we do not generate code if there is no SQL
1137#
1138do_test capi3-16.3 {
1139  set STMT [sqlite3_prepare $DB {} -1 TAIL]
1140  sqlite3_finalize $STMT
1141  expr {$STMT==""}
1142} {1}
1143do_test capi3-16.4 {
1144  set STMT [sqlite3_prepare $DB {;} -1 TAIL]
1145  sqlite3_finalize $STMT
1146  expr {$STMT==""}
1147} {1}
1148
1149# Ticket #2426:  Misuse of sqlite3_column_* by calling it after
1150# a sqlite3_reset should be harmless.
1151#
1152do_test capi3-17.1 {
1153  set STMT [sqlite3_prepare $DB {SELECT * FROM t2} -1 TAIL]
1154  sqlite3_step $STMT
1155  sqlite3_column_int $STMT 0
1156} {1}
1157do_test capi3-17.2 {
1158  sqlite3_reset $STMT
1159  sqlite3_column_int $STMT 0
1160} {0}
1161do_test capi3-17.3 {
1162  sqlite3_finalize $STMT
1163} {SQLITE_OK}
1164
1165# Verify that sqlite3_step() fails with an SQLITE_SCHEMA error
1166# when the statement is prepared with sqlite3_prepare() (not
1167# sqlite3_prepare_v2()) and the schema has changed.
1168#
1169do_test capi3-18.1 {
1170  set STMT [sqlite3_prepare db {SELECT * FROM t2} -1 TAIL]
1171  sqlite3 db2 test.db
1172  db2 eval {CREATE TABLE t3(x)}
1173  db2 close
1174  sqlite3_step $STMT
1175} {SQLITE_ERROR}
1176do_test capi3-18.2 {
1177  sqlite3_reset $STMT
1178  sqlite3_errcode db
1179} {SQLITE_SCHEMA}
1180do_test capi3-18.3 {
1181  sqlite3_errmsg db
1182} {database schema has changed}
1183# The error persist on retry when sqlite3_prepare() has been used.
1184do_test capi3-18.4 {
1185  sqlite3_step $STMT
1186} {SQLITE_ERROR}
1187do_test capi3-18.5 {
1188  sqlite3_reset $STMT
1189  sqlite3_errcode db
1190} {SQLITE_SCHEMA}
1191do_test capi3-18.6 {
1192  sqlite3_errmsg db
1193} {database schema has changed}
1194sqlite3_finalize $STMT
1195
1196# Ticket #3134.  Prepare a statement with an nBytes parameter of 0.
1197# Make sure this works correctly and does not reference memory out of
1198# range.
1199#
1200do_test capi3-19.1 {
1201  sqlite3_prepare_tkt3134 db
1202} {}
1203
1204# Tests of the interface when no VFS is registered.
1205#
1206if {![info exists tester_do_binarylog]} {
1207  db close
1208  vfs_unregister_all
1209  do_test capi3-20.1 {
1210    sqlite3_sleep 100
1211  } {0}
1212  vfs_reregister_all
1213}
1214
1215finish_test
1216