Debian Codegraph @

test/tclsqlite.test

raw
1 # 2001 September 15
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 TCL interface to the
12 # SQLite library.
13 #
14 # Actually, all tests are based on the TCL interface, so the main
15 # interface is pretty well tested. This file contains some addition
16 # tests for fringe issues that the main test suite does not cover.
17 #
18 # $Id: tclsqlite.test,v 1.73 2009/03/16 13:19:36 danielk1977 Exp $
19
20 catch {sqlite3}
21
22 set testdir [file dirname $argv0]
23 source $testdir/tester.tcl
24 set testprefix tcl
25
26 # Check the error messages generated by tclsqlite
27 #
28 set r "sqlite_orig HANDLE ?FILENAME? ?-vfs VFSNAME? ?-readonly BOOLEAN? ?-create BOOLEAN? ?-nofollow BOOLEAN? ?-nomutex BOOLEAN? ?-fullmutex BOOLEAN? ?-uri BOOLEAN?"
29 if {[sqlite3 -has-codec]} {
30 append r " ?-key CODECKEY?"
31 }
32 do_test tcl-1.1 {
33 set v [catch {sqlite3 -bogus} msg]
34 regsub {really_sqlite3} $msg {sqlite3} msg
35 lappend v $msg
36 } [list 1 "wrong # args: should be \"$r\""]
37 do_test tcl-1.1.1 {
38 set v [catch {sqlite3} msg]
39 regsub {really_sqlite3} $msg {sqlite3} msg
40 lappend v $msg
41 } [list 1 "wrong # args: should be \"$r\""]
42 do_test tcl-1.2 {
43 set v [catch {db bogus} msg]
44 lappend v $msg
45 } {1 {bad option "bogus": must be authorizer, backup, bind_fallback, busy, cache, changes, close, collate, collation_needed, commit_hook, complete, config, copy, deserialize, enable_load_extension, errorcode, erroroffset, eval, exists, format, function, incrblob, interrupt, last_insert_rowid, nullvalue, onecolumn, preupdate, profile, progress, rekey, restore, rollback_hook, serialize, status, timeout, total_changes, trace, trace_v2, transaction, unlock_notify, update_hook, version, or wal_hook}}
46 do_test tcl-1.2.1 {
47 set v [catch {db cache bogus} msg]
48 lappend v $msg
49 } {1 {bad option "bogus": must be flush or size}}
50 do_test tcl-1.2.2 {
51 set v [catch {db cache} msg]
52 lappend v $msg
53 } {1 {wrong # args: should be "db cache option ?arg?"}}
54 do_test tcl-1.3 {
55 execsql {CREATE TABLE t1(a int, b int)}
56 execsql {INSERT INTO t1 VALUES(10,20)}
57 set v [catch {
58 db eval {SELECT * FROM t1} data {
59 error "The error message"
60 }
61 } msg]
62 lappend v $msg
63 } {1 {The error message}}
64 do_test tcl-1.4 {
65 set v [catch {
66 db eval {SELECT * FROM t2} data {
67 error "The error message"
68 }
69 } msg]
70 lappend v $msg
71 } {1 {no such table: t2}}
72 do_test tcl-1.5 {
73 set v [catch {
74 db eval {SELECT * FROM t1} data {
75 break
76 }
77 } msg]
78 lappend v $msg
79 } {0 {}}
80 catch {expr x*} msg
81 do_test tcl-1.6 {
82 set v [catch {
83 db eval {SELECT * FROM t1} data {
84 expr x*
85 }
86 } msg]
87 lappend v $msg
88 } [list 1 $msg]
89 do_test tcl-1.7 {
90 set v [catch {db} msg]
91 lappend v $msg
92 } {1 {wrong # args: should be "db SUBCOMMAND ..."}}
93 if {[catch {db auth {}}]==0} {
94 do_test tcl-1.8 {
95 set v [catch {db authorizer 1 2 3} msg]
96 lappend v $msg
97 } {1 {wrong # args: should be "db authorizer ?CALLBACK?"}}
98 }
99 do_test tcl-1.9 {
100 set v [catch {db busy 1 2 3} msg]
101 lappend v $msg
102 } {1 {wrong # args: should be "db busy CALLBACK"}}
103 do_test tcl-1.10 {
104 set v [catch {db progress 1} msg]
105 lappend v $msg
106 } {1 {wrong # args: should be "db progress N CALLBACK"}}
107 do_test tcl-1.11 {
108 set v [catch {db changes xyz} msg]
109 lappend v $msg
110 } {1 {wrong # args: should be "db changes "}}
111 do_test tcl-1.12 {
112 set v [catch {db commit_hook a b c} msg]
113 lappend v $msg
114 } {1 {wrong # args: should be "db commit_hook ?CALLBACK?"}}
115 ifcapable {complete} {
116 do_test tcl-1.13 {
117 set v [catch {db complete} msg]
118 lappend v $msg
119 } {1 {wrong # args: should be "db complete SQL"}}
120 }
121 do_test tcl-1.14 {
122 set v [catch {db eval} msg]
123 lappend v $msg
124 } {1 {wrong # args: should be "db eval ?OPTIONS? SQL ?VAR-NAME? ?SCRIPT?"}}
125 do_test tcl-1.15 {
126 set v [catch {db function} msg]
127 lappend v $msg
128 } {1 {wrong # args: should be "db function NAME ?SWITCHES? SCRIPT"}}
129 do_test tcl-1.16 {
130 set v [catch {db last_insert_rowid xyz} msg]
131 lappend v $msg
132 } {1 {wrong # args: should be "db last_insert_rowid "}}
133 do_test tcl-1.17 {
134 set v [catch {db rekey} msg]
135 lappend v $msg
136 } {1 {wrong # args: should be "db rekey KEY"}}
137 do_test tcl-1.18 {
138 set v [catch {db timeout} msg]
139 lappend v $msg
140 } {1 {wrong # args: should be "db timeout MILLISECONDS"}}
141 do_test tcl-1.19 {
142 set v [catch {db collate} msg]
143 lappend v $msg
144 } {1 {wrong # args: should be "db collate NAME SCRIPT"}}
145 do_test tcl-1.20 {
146 set v [catch {db collation_needed} msg]
147 lappend v $msg
148 } {1 {wrong # args: should be "db collation_needed SCRIPT"}}
149 do_test tcl-1.21 {
150 set v [catch {db total_changes xyz} msg]
151 lappend v $msg
152 } {1 {wrong # args: should be "db total_changes "}}
153 do_test tcl-1.22 {
154 set v [catch {db copy} msg]
155 lappend v $msg
156 } {1 {wrong # args: should be "db copy CONFLICT-ALGORITHM TABLE FILENAME ?SEPARATOR? ?NULLINDICATOR?"}}
157 do_test tcl-1.23 {
158 set v [catch {sqlite3 db2 test.db -vfs nosuchvfs} msg]
159 lappend v $msg
160 } {1 {no such vfs: nosuchvfs}}
161
162 catch {unset ::result}
163 do_test tcl-2.1 {
164 execsql "CREATE TABLE t\u0123x(a int, b\u1235 float)"
165 } {}
166 ifcapable schema_pragmas {
167 do_test tcl-2.2 {
168 execsql "PRAGMA table_info(t\u0123x)"
169 } "0 a INT 0 {} 0 1 b\u1235 float 0 {} 0"
170 }
171 do_test tcl-2.3 {
172 execsql "INSERT INTO t\u0123x VALUES(1,2.3)"
173 db eval "SELECT * FROM t\u0123x" result break
174 set result(*)
175 } "a b\u1235"
176
177
178 # Test the onecolumn method
179 #
180 do_test tcl-3.1 {
181 execsql {
182 INSERT INTO t1 SELECT a*2, b*2 FROM t1;
183 INSERT INTO t1 SELECT a*2+1, b*2+1 FROM t1;
184 INSERT INTO t1 SELECT a*2+3, b*2+3 FROM t1;
185 }
186 set rc [catch {db onecolumn {SELECT * FROM t1 ORDER BY a}} msg]
187 lappend rc $msg
188 } {0 10}
189 do_test tcl-3.2 {
190 db onecolumn {SELECT * FROM t1 WHERE a<0}
191 } {}
192 do_test tcl-3.3 {
193 set rc [catch {db onecolumn} errmsg]
194 lappend rc $errmsg
195 } {1 {wrong # args: should be "db onecolumn SQL"}}
196 do_test tcl-3.4 {
197 set rc [catch {db onecolumn {SELECT bogus}} errmsg]
198 lappend rc $errmsg
199 } {1 {no such column: bogus}}
200 ifcapable {tclvar} {
201 do_test tcl-3.5 {
202 set b 50
203 set rc [catch {db one {SELECT * FROM t1 WHERE b>$b}} msg]
204 lappend rc $msg
205 } {0 41}
206 do_test tcl-3.6 {
207 set b 500
208 set rc [catch {db one {SELECT * FROM t1 WHERE b>$b}} msg]
209 lappend rc $msg
210 } {0 {}}
211 do_test tcl-3.7 {
212 set b 500
213 set rc [catch {db one {
214 INSERT INTO t1 VALUES(99,510);
215 SELECT * FROM t1 WHERE b>$b
216 }} msg]
217 lappend rc $msg
218 } {0 99}
219 }
220 ifcapable {!tclvar} {
221 execsql {INSERT INTO t1 VALUES(99,510)}
222 }
223
224 # Turn the busy handler on and off
225 #
226 do_test tcl-4.1 {
227 proc busy_callback {cnt} {
228 break
229 }
230 db busy busy_callback
231 db busy
232 } {busy_callback}
233 do_test tcl-4.2 {
234 db busy {}
235 db busy
236 } {}
237
238 ifcapable {tclvar} {
239 # Parsing of TCL variable names within SQL into bound parameters.
240 #
241 do_test tcl-5.1 {
242 execsql {CREATE TABLE t3(a,b,c)}
243 catch {unset x}
244 set x(1) A
245 set x(2) B
246 execsql {
247 INSERT INTO t3 VALUES($::x(1),$::x(2),$::x(3));
248 SELECT * FROM t3
249 }
250 } {A B {}}
251 do_test tcl-5.2 {
252 execsql {
253 SELECT typeof(a), typeof(b), typeof(c) FROM t3
254 }
255 } {text text null}
256 do_test tcl-5.3 {
257 catch {unset x}
258 set x [binary format h12 686900686f00]
259 execsql {
260 UPDATE t3 SET a=$::x;
261 }
262 db eval {
263 SELECT a FROM t3
264 } break
265 binary scan $a h12 adata
266 set adata
267 } {686900686f00}
268 do_test tcl-5.4 {
269 execsql {
270 SELECT typeof(a), typeof(b), typeof(c) FROM t3
271 }
272 } {blob text null}
273 }
274
275 # Operation of "break" and "continue" within row scripts
276 #
277 do_test tcl-6.1 {
278 db eval {SELECT * FROM t1} {
279 break
280 }
281 lappend a $b
282 } {10 20}
283 do_test tcl-6.2 {
284 set cnt 0
285 db eval {SELECT * FROM t1} {
286 if {$a>40} continue
287 incr cnt
288 }
289 set cnt
290 } {4}
291 do_test tcl-6.3 {
292 set cnt 0
293 db eval {SELECT * FROM t1} {
294 if {$a<40} continue
295 incr cnt
296 }
297 set cnt
298 } {5}
299 do_test tcl-6.4 {
300 proc return_test {x} {
301 db eval {SELECT * FROM t1} {
302 if {$a==$x} {return $b}
303 }
304 }
305 return_test 10
306 } 20
307 do_test tcl-6.5 {
308 return_test 20
309 } 40
310 do_test tcl-6.6 {
311 return_test 99
312 } 510
313 do_test tcl-6.7 {
314 return_test 0
315 } {}
316
317 do_test tcl-7.1 {
318 db version
319 expr 0
320 } {0}
321
322 # modify and reset the NULL representation
323 #
324 do_test tcl-8.1 {
325 db nullvalue NaN
326 execsql {INSERT INTO t1 VALUES(30,NULL)}
327 db eval {SELECT * FROM t1 WHERE b IS NULL}
328 } {30 NaN}
329 proc concatFunc args {return [join $args {}]}
330 do_test tcl-8.2 {
331 db function concat concatFunc
332 db eval {SELECT concat('a', b, 'z') FROM t1 WHERE b is NULL}
333 } {aNaNz}
334 do_test tcl-8.3 {
335 db nullvalue NULL
336 db nullvalue
337 } {NULL}
338 do_test tcl-8.4 {
339 db nullvalue {}
340 db eval {SELECT * FROM t1 WHERE b IS NULL}
341 } {30 {}}
342 do_test tcl-8.5 {
343 db function concat concatFunc
344 db eval {SELECT concat('a', b, 'z') FROM t1 WHERE b is NULL}
345 } {az}
346
347 # Test the return type of user-defined functions
348 #
349 do_test tcl-9.1 {
350 db function ret_str {return "hi"}
351 execsql {SELECT typeof(ret_str())}
352 } {text}
353 do_test tcl-9.2 {
354 db function ret_dbl {return [expr {rand()*0.5}]}
355 execsql {SELECT typeof(ret_dbl())}
356 } {real}
357 do_test tcl-9.3 {
358 db function ret_int {return [expr {int(rand()*200)}]}
359 execsql {SELECT typeof(ret_int())}
360 } {integer}
361
362 proc breakAsNullUdf args {
363 if {"1" eq [lindex $args 0]} {return -code break}
364 }
365 do_test tcl-9.4 {
366 db function banu breakAsNullUdf
367 execsql {SELECT typeof(banu()), typeof(banu(1))}
368 } {text null}
369 do_test tcl-9.5 {
370 db nullvalue banunull
371 db eval {SELECT banu(), banu(1)}
372 } {{} banunull}
373
374
375 # Recursive calls to the same user-defined function
376 #
377 ifcapable tclvar {
378 do_test tcl-9.10 {
379 proc userfunc_r1 {n} {
380 if {$n<=0} {return 0}
381 set nm1 [expr {$n-1}]
382 return [expr {[db eval {SELECT r1($nm1)}]+$n}]
383 }
384 db function r1 userfunc_r1
385 execsql {SELECT r1(10)}
386 } {55}
387 # Fails under -fsanitize=address,undefined due to stack overflow
388 # do_test tcl-9.11 {
389 # execsql {SELECT r1(100)}
390 # } {5050}
391 }
392
393 # Tests for the new transaction method
394 #
395 do_test tcl-10.1 {
396 db transaction {}
397 } {}
398 do_test tcl-10.2 {
399 db transaction deferred {}
400 } {}
401 do_test tcl-10.3 {
402 db transaction immediate {}
403 } {}
404 do_test tcl-10.4 {
405 db transaction exclusive {}
406 } {}
407 do_test tcl-10.5 {
408 set rc [catch {db transaction xyzzy {}} msg]
409 lappend rc $msg
410 } {1 {bad transaction type "xyzzy": must be deferred, exclusive, or immediate}}
411 do_test tcl-10.6 {
412 set rc [catch {db transaction {error test-error}} msg]
413 lappend rc $msg
414 } {1 test-error}
415 do_test tcl-10.7 {
416 db transaction {
417 db eval {CREATE TABLE t4(x)}
418 db transaction {
419 db eval {INSERT INTO t4 VALUES(1)}
420 }
421 }
422 db eval {SELECT * FROM t4}
423 } 1
424 do_test tcl-10.8 {
425 catch {
426 db transaction {
427 db eval {INSERT INTO t4 VALUES(2)}
428 db eval {INSERT INTO t4 VALUES(3)}
429 db eval {INSERT INTO t4 VALUES(4)}
430 error test-error
431 }
432 }
433 db eval {SELECT * FROM t4}
434 } 1
435 do_test tcl-10.9 {
436 db transaction {
437 db eval {INSERT INTO t4 VALUES(2)}
438 catch {
439 db transaction {
440 db eval {INSERT INTO t4 VALUES(3)}
441 db eval {INSERT INTO t4 VALUES(4)}
442 error test-error
443 }
444 }
445 }
446 db eval {SELECT * FROM t4}
447 } {1 2}
448 do_test tcl-10.10 {
449 for {set i 0} {$i<1} {incr i} {
450 db transaction {
451 db eval {INSERT INTO t4 VALUES(5)}
452 continue
453 }
454 error "This line should not be run"
455 }
456 db eval {SELECT * FROM t4}
457 } {1 2 5}
458 do_test tcl-10.11 {
459 for {set i 0} {$i<10} {incr i} {
460 db transaction {
461 db eval {INSERT INTO t4 VALUES(6)}
462 break
463 }
464 }
465 db eval {SELECT * FROM t4}
466 } {1 2 5 6}
467 do_test tcl-10.12 {
468 set rc [catch {
469 for {set i 0} {$i<10} {incr i} {
470 db transaction {
471 db eval {INSERT INTO t4 VALUES(7)}
472 return
473 }
474 }
475 }]
476 } {2}
477 do_test tcl-10.13 {
478 db eval {SELECT * FROM t4}
479 } {1 2 5 6 7}
480
481 # Now test that [db transaction] commands may be nested with
482 # the expected results.
483 #
484 do_test tcl-10.14 {
485 db transaction {
486 db eval {
487 DELETE FROM t4;
488 INSERT INTO t4 VALUES('one');
489 }
490
491 catch {
492 db transaction {
493 db eval { INSERT INTO t4 VALUES('two') }
494 db transaction {
495 db eval { INSERT INTO t4 VALUES('three') }
496 error "throw an error!"
497 }
498 }
499 }
500 }
501
502 db eval {SELECT * FROM t4}
503 } {one}
504 do_test tcl-10.15 {
505 # Make sure a transaction has not been left open.
506 db eval {BEGIN ; COMMIT}
507 } {}
508 do_test tcl-10.16 {
509 db transaction {
510 db eval { INSERT INTO t4 VALUES('two'); }
511 db transaction {
512 db eval { INSERT INTO t4 VALUES('three') }
513 db transaction {
514 db eval { INSERT INTO t4 VALUES('four') }
515 }
516 }
517 }
518 db eval {SELECT * FROM t4}
519 } {one two three four}
520 do_test tcl-10.17 {
521 catch {
522 db transaction {
523 db eval { INSERT INTO t4 VALUES('A'); }
524 db transaction {
525 db eval { INSERT INTO t4 VALUES('B') }
526 db transaction {
527 db eval { INSERT INTO t4 VALUES('C') }
528 error "throw an error!"
529 }
530 }
531 }
532 }
533 db eval {SELECT * FROM t4}
534 } {one two three four}
535 do_test tcl-10.18 {
536 # Make sure a transaction has not been left open.
537 db eval {BEGIN ; COMMIT}
538 } {}
539
540 # Mess up a [db transaction] command by locking the database using a
541 # second connection when it tries to commit. Make sure the transaction
542 # is not still open after the "database is locked" exception is thrown.
543 #
544 do_test tcl-10.18 {
545 sqlite3 db2 test.db
546 db2 eval {
547 BEGIN;
548 SELECT * FROM sqlite_master;
549 }
550
551 set rc [catch {
552 db transaction {
553 db eval {INSERT INTO t4 VALUES('five')}
554 }
555 } msg]
556 list $rc $msg
557 } {1 {database is locked}}
558 do_test tcl-10.19 {
559 db eval {BEGIN ; COMMIT}
560 } {}
561
562 # Thwart a [db transaction] command by locking the database using a
563 # second connection with "BEGIN EXCLUSIVE". Make sure no transaction is
564 # open after the "database is locked" exception is thrown.
565 #
566 do_test tcl-10.20 {
567 db2 eval {
568 COMMIT;
569 BEGIN EXCLUSIVE;
570 }
571 set rc [catch {
572 db transaction {
573 db eval {INSERT INTO t4 VALUES('five')}
574 }
575 } msg]
576 list $rc $msg
577 } {1 {database is locked}}
578 do_test tcl-10.21 {
579 db2 close
580 db eval {BEGIN ; COMMIT}
581 } {}
582 do_test tcl-10.22 {
583 sqlite3 db2 test.db
584 db transaction exclusive {
585 catch { db2 eval {SELECT * FROM sqlite_master} } msg
586 set msg "db2: $msg"
587 }
588 set msg
589 } {db2: database is locked}
590 db2 close
591
592 do_test tcl-11.1 {
593 db eval {INSERT INTO t4 VALUES(6)}
594 db exists {SELECT x,x*2,x+x FROM t4 WHERE x==6}
595 } {1}
596 do_test tcl-11.2 {
597 db exists {SELECT 0 FROM t4 WHERE x==6}
598 } {1}
599 do_test tcl-11.3 {
600 db exists {SELECT 1 FROM t4 WHERE x==8}
601 } {0}
602 do_test tcl-11.3.1 {
603 tcl_objproc db exists {SELECT 1 FROM t4 WHERE x==8}
604 } {0}
605
606 do_test tcl-12.1 {
607 unset -nocomplain a b c version
608 set version [db version]
609 scan $version "%d.%d.%d" a b c
610 expr $a*1000000 + $b*1000 + $c
611 } [sqlite3_libversion_number]
612
613
614 # Check to see that when bindings of the form @aaa are used instead
615 # of $aaa, that objects are treated as bytearray and are inserted
616 # as BLOBs.
617 #
618 ifcapable tclvar {
619 do_test tcl-13.1 {
620 db eval {CREATE TABLE t5(x BLOB)}
621 set x abc123
622 db eval {INSERT INTO t5 VALUES($x)}
623 db eval {SELECT typeof(x) FROM t5}
624 } {text}
625 do_test tcl-13.2 {
626 binary scan $x H notUsed
627 db eval {
628 DELETE FROM t5;
629 INSERT INTO t5 VALUES($x);
630 SELECT typeof(x) FROM t5;
631 }
632 } {text}
633 do_test tcl-13.3 {
634 db eval {
635 DELETE FROM t5;
636 INSERT INTO t5 VALUES(@x);
637 SELECT typeof(x) FROM t5;
638 }
639 } {blob}
640 do_test tcl-13.4 {
641 set y 1234
642 db eval {
643 DELETE FROM t5;
644 INSERT INTO t5 VALUES(@y);
645 SELECT hex(x), typeof(x) FROM t5
646 }
647 } {31323334 blob}
648 }
649
650 db func xCall xCall
651 proc xCall {} { return "value" }
652 do_execsql_test tcl-14.1 {
653 CREATE TABLE t6(x);
654 INSERT INTO t6 VALUES(1);
655 }
656 do_test tcl-14.2 {
657 db one {SELECT x FROM t6 WHERE xCall()!='value'}
658 } {}
659
660 # Verify that the "exists" and "onecolumn" methods work when
661 # a "profile" is registered.
662 #
663 catch {db close}
664 sqlite3 db :memory:
665 proc noop-profile {args} {
666 return
667 }
668 do_test tcl-15.0 {
669 db eval {CREATE TABLE t1(a); INSERT INTO t1 VALUES(1),(2),(3);}
670 db onecolumn {SELECT a FROM t1 WHERE a>2}
671 } {3}
672 do_test tcl-15.1 {
673 db exists {SELECT a FROM t1 WHERE a>2}
674 } {1}
675 do_test tcl-15.2 {
676 db exists {SELECT a FROM t1 WHERE a>3}
677 } {0}
678 db profile noop-profile
679 do_test tcl-15.3 {
680 db onecolumn {SELECT a FROM t1 WHERE a>2}
681 } {3}
682 do_test tcl-15.4 {
683 db exists {SELECT a FROM t1 WHERE a>2}
684 } {1}
685 do_test tcl-15.5 {
686 db exists {SELECT a FROM t1 WHERE a>3}
687 } {0}
688
689
690 # 2017-06-26: The -withoutnulls flag to "db eval".
691 #
692 # In the "db eval -withoutnulls SQL TARGET" form, NULL results cause the
693 # corresponding target entry to be unset. The default behavior (without
694 # the -withoutnulls flags) is for the corresponding target value to get
695 # the [db nullvalue] string.
696 #
697 catch {db close}
698 forcedelete test.db
699 sqlite3 db test.db
700 do_execsql_test tcl-16.100 {
701 CREATE TABLE t1(a,b);
702 INSERT INTO t1 VALUES(1,2),(2,NULL),(3,'xyz');
703 }
704 do_test tcl-16.101 {
705 set res {}
706 unset -nocomplain x
707 db eval {SELECT * FROM t1} x {
708 lappend res $x(a) [array names x]
709 }
710 set res
711 } {1 {a b *} 2 {a b *} 3 {a b *}}
712 do_test tcl-16.102 {
713 set res [catch {
714 db eval -unknown {SELECT * FROM t1} x {
715 lappend res $x(a) [array names x]
716 }
717 } rc]
718 lappend res $rc
719 } {1 {unknown option: "-unknown"}}
720 do_test tcl-16.103 {
721 set res {}
722 unset -nocomplain x
723 db eval -withoutnulls {SELECT * FROM t1} x {
724 lappend res $x(a) [array names x]
725 }
726 set res
727 } {1 {a b *} 2 {a *} 3 {a b *}}
728
729 #-------------------------------------------------------------------------
730 # Test the -type option to [db function].
731 #
732 reset_db
733 proc add {a b} { return [expr $a + $b] }
734 proc ret {a} { return $a }
735
736 db function add_i -returntype integer add
737 db function add_r -ret real add
738 db function add_t -return text add
739 db function add_b -returntype blob add
740 db function add_a -returntype any add
741
742 db function ret_i -returntype int ret
743 db function ret_r -returntype real ret
744 db function ret_t -returntype text ret
745 db function ret_b -returntype blob ret
746 db function ret_a -r any ret
747
748 do_execsql_test 17.0 {
749 SELECT quote( add_i(2, 3) );
750 SELECT quote( add_r(2, 3) );
751 SELECT quote( add_t(2, 3) );
752 SELECT quote( add_b(2, 3) );
753 SELECT quote( add_a(2, 3) );
754 } {5 5.0 '5' X'35' 5}
755
756 do_execsql_test 17.1 {
757 SELECT quote( add_i(2.2, 3.3) );
758 SELECT quote( add_r(2.2, 3.3) );
759 SELECT quote( add_t(2.2, 3.3) );
760 SELECT quote( add_b(2.2, 3.3) );
761 SELECT quote( add_a(2.2, 3.3) );
762 } {5.5 5.5 '5.5' X'352E35' 5.5}
763
764 do_execsql_test 17.2 {
765 SELECT quote( ret_i(2.5) );
766 SELECT quote( ret_r(2.5) );
767 SELECT quote( ret_t(2.5) );
768 SELECT quote( ret_b(2.5) );
769 SELECT quote( ret_a(2.5) );
770 } {2.5 2.5 '2.5' X'322E35' 2.5}
771
772 do_execsql_test 17.3 {
773 SELECT quote( ret_i('2.5') );
774 SELECT quote( ret_r('2.5') );
775 SELECT quote( ret_t('2.5') );
776 SELECT quote( ret_b('2.5') );
777 SELECT quote( ret_a('2.5') );
778 } {2.5 2.5 '2.5' X'322E35' '2.5'}
779
780 do_execsql_test 17.4 {
781 SELECT quote( ret_i('abc') );
782 SELECT quote( ret_r('abc') );
783 SELECT quote( ret_t('abc') );
784 SELECT quote( ret_b('abc') );
785 SELECT quote( ret_a('abc') );
786 } {'abc' 'abc' 'abc' X'616263' 'abc'}
787
788 do_execsql_test 17.5 {
789 SELECT quote( ret_i(X'616263') );
790 SELECT quote( ret_r(X'616263') );
791 SELECT quote( ret_t(X'616263') );
792 SELECT quote( ret_b(X'616263') );
793 SELECT quote( ret_a(X'616263') );
794 } {'abc' 'abc' 'abc' X'616263' X'616263'}
795
796 do_test 17.6.1 {
797 list [catch { db function xyz -return object ret } msg] $msg
798 } {1 {bad type "object": must be integer, real, text, blob, or any}}
799
800 do_test 17.6.2 {
801 list [catch { db function xyz -return ret } msg] $msg
802 } {1 {option requires an argument: -return}}
803
804 do_test 17.6.3 {
805 list [catch { db function xyz -n object ret } msg] $msg
806 } {1 {bad option "-n": must be -argcount, -deterministic, -directonly, -innocuous, or -returntype}}
807
808 # 2019-02-28: The "bind_fallback" command.
809 #
810 do_test 18.100 {
811 unset -nocomplain bindings abc def ghi jkl mno e01 e02
812 set bindings(abc) [expr {1+2}]
813 set bindings(def) {hello}
814 set bindings(ghi) [expr {3.1415926*1.0}]
815 proc bind_callback {nm} {
816 global bindings
817 set n2 [string range $nm 1 end]
818 if {[info exists bindings($n2)]} {
819 return $bindings($n2)
820 }
821 if {[string match e* $n2]} {
822 error "no such variable: $nm"
823 }
824 return -code return {}
825 }
826 db bind_fallback bind_callback
827 db eval {SELECT $abc, typeof($abc), $def, typeof($def), $ghi, typeof($ghi)}
828 } {3 integer hello text 3.1415926 real}
829 do_test 18.110 {
830 db eval {SELECT quote(@def), typeof(@def)}
831 } {X'68656C6C6F' blob}
832 do_execsql_test 18.120 {
833 SELECT typeof($mno);
834 } {null}
835 do_catchsql_test 18.130 {
836 SELECT $e01;
837 } {1 {no such variable: $e01}}
838 do_test 18.140 {
839 db bind_fallback
840 } {bind_callback}
841 do_test 18.200 {
842 db bind_fallback {}
843 db eval {SELECT $abc, typeof($abc), $def, typeof($def), $ghi, typeof($ghi)}
844 } {{} null {} null {} null}
845 do_test 18.300 {
846 unset -nocomplain bindings
847 proc bind_callback {nm} {lappend ::bindings $nm}
848 db bind_fallback bind_callback
849 db eval {SELECT $abc, @def, $ghi(123), :mno}
850 set bindings
851 } {{$abc} @def {$ghi(123)} :mno}
852 do_test 18.900 {
853 set rc [catch {db bind_fallback a b} msg]
854 lappend rc $msg
855 } {1 {wrong # args: should be "db bind_fallback ?CALLBACK?"}}
856 do_test 18.910 {
857 db bind_fallback bind_fallback_does_not_exist
858 } {}
859 do_catchsql_test 19.911 {
860 SELECT $abc, typeof($abc), $def, typeof($def), $ghi, typeof($ghi);
861 } {1 {invalid command name "bind_fallback_does_not_exist"}}
862 db bind_fallback {}
863
864 # 2025-05-05: the -asdict eval flag
865 #
866 do_test 20.0 {
867 execsql {CREATE TABLE tad(a,b)}
868 execsql {INSERT INTO tad(a,b) VALUES('aa','bb'),('AA','BB')}
869 db eval -asdict {
870 SELECT a, b FROM tad WHERE 0
871 } D {}
872 set D
873 } {* {a b}}
874
875 do_test 20.1 {
876 unset D
877 set i 0
878 set res {}
879 set colNames {}
880 db eval -asdict {
881 SELECT a, b FROM tad ORDER BY a
882 } D {
883 dict set D i [incr i]
884 lappend res $i [dict get $D a] [dict get $D b]
885 if {1 == $i} {
886 set colNames [dict get $D *]
887 }
888 }
889 lappend res $colNames
890 unset D
891 set res
892 } {1 AA BB 2 aa bb {a b}}
893
894 do_test 20.2 {
895 set res {}
896 db eval -asdict -withoutnulls {
897 SELECT n, a, b FROM (
898 SELECT 1 as n, 'aa' as a, NULL as b
899 UNION ALL
900 SELECT 2 as n, NULL as a, 'bb' as b
901 )
902 ORDER BY n
903 } D {
904 dict unset D *
905 lappend res [dict values $D]
906 }
907 unset D
908 execsql {DROP TABLE tad}
909 set res
910 } {{1 aa} {2 bb}}
911
912 #-------------------------------------------------------------------------
913 do_test 21.0 {
914 db transaction {
915 db close
916 }
917 } {}
918
919 do_test 21.1 {
920 sqlite3 db test.db
921 set rc [catch {
922 db eval {SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3} { db close }
923 } msg]
924 list $rc $msg
925 } {1 {invalid command name "db"}}
926
927
928
929 proc closedb {} {
930 db close
931 return 10
932 }
933 proc func1 {} { return 1 }
934
935 sqlite3 db test.db
936 db func closedb closedb
937 db func func1 func1
938
939 do_test 21.2 {
940 set rc [catch {
941 db eval {
942 SELECT closedb(),func1() UNION ALL SELECT 20,30 UNION ALL SELECT 30,40
943 }
944 } msg]
945 list $rc $msg
946 } {0 {10 1 20 30 30 40}}
947
948 sqlite3 db :memory:
949 do_test 22.1 {
950 catch {db eval {SELECT 1 2 3;}} msg
951 db erroroffset
952 } {9}
953
954
955 finish_test