# 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the sqlite_exec_printf() and # sqlite_get_table_printf() APIs. # # $Id: tableapi.test,v 1.5 2002/03/23 00:52:01 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl do_test tableapi-1.0 { set ::dbx [sqlite_open testdb] catch {sqlite_exec_printf $::dbx {DROP TABLE xyz} {}} sqlite_exec_printf $::dbx {CREATE TABLE %s(a int, b text)} xyz } {0 {}} do_test tableapi-1.1 { sqlite_exec_printf $::dbx { INSERT INTO xyz VALUES(1,'%q') } {Hi Y'all} } {0 {}} do_test tableapi-1.2 { sqlite_exec_printf $::dbx {SELECT * FROM xyz} {} } {0 {a b 1 {Hi Y'all}}} do_test tableapi-2.1 { sqlite_get_table_printf $::dbx { BEGIN TRANSACTION; SELECT * FROM xyz WHERE b='%q' } {Hi Y'all} } {0 1 2 a b 1 {Hi Y'all}} do_test tableapi-2.2 { sqlite_get_table_printf $::dbx { SELECT * FROM xyz } {} } {0 1 2 a b 1 {Hi Y'all}} do_test tableapi-2.3 { for {set i 2} {$i<=50} {incr i} { sqlite_get_table_printf $::dbx \ "INSERT INTO xyz VALUES($i,'(%s)')" $i } sqlite_get_table_printf $::dbx { SELECT * FROM xyz ORDER BY a } {} } {0 50 2 a b 1 {Hi Y'all} 2 (2) 3 (3) 4 (4) 5 (5) 6 (6) 7 (7) 8 (8) 9 (9) 10 (10) 11 (11) 12 (12) 13 (13) 14 (14) 15 (15) 16 (16) 17 (17) 18 (18) 19 (19) 20 (20) 21 (21) 22 (22) 23 (23) 24 (24) 25 (25) 26 (26) 27 (27) 28 (28) 29 (29) 30 (30) 31 (31) 32 (32) 33 (33) 34 (34) 35 (35) 36 (36) 37 (37) 38 (38) 39 (39) 40 (40) 41 (41) 42 (42) 43 (43) 44 (44) 45 (45) 46 (46) 47 (47) 48 (48) 49 (49) 50 (50)} do_test tableapi-2.3.1 { sqlite_get_table_printf $::dbx { SELECT * FROM xyz WHERE a>49 ORDER BY a } {} } {0 1 2 a b 50 (50)} do_test tableapi-2.3.2 { sqlite_get_table_printf $::dbx { SELECT * FROM xyz WHERE a>47 ORDER BY a } {} } {0 3 2 a b 48 (48) 49 (49) 50 (50)} do_test tableapi-2.4 { set ::big_str [sqlite_mprintf_str {%500'* Hello %500'*} 0 0 {}] sqlite_get_table_printf $::dbx { INSERT INTO xyz VALUES(51,'%q') } $::big_str } {0 0 0} do_test tableapi-2.5 { sqlite_get_table_printf $::dbx { SELECT * FROM xyz WHERE a>49 ORDER BY a; } {} } "0 2 2 a b 50 (50) 51 \173$::big_str\175" do_test tableapi-2.6 { sqlite_get_table_printf $::dbx { INSERT INTO xyz VALUES(52,NULL) } {} sqlite_get_table_printf $::dbx { SELECT * FROM xyz WHERE a IN (42,50,52) ORDER BY a DESC } {} } {0 3 2 a b 52 NULL 50 (50) 42 (42)} do_test tableapi-2.7 { sqlite_get_table_printf $::dbx { SELECT * FROM xyz WHERE a>1000 } {} } {0 0 0} # Repeat all tests with the empty_result_callbacks pragma turned on # do_test tableapi-3.1 { sqlite_get_table_printf $::dbx { ROLLBACK; PRAGMA empty_result_callbacks = ON; SELECT * FROM xyz WHERE b='%q' } {Hi Y'all} } {0 1 2 a b 1 {Hi Y'all}} do_test tableapi-3.2 { sqlite_get_table_printf $::dbx { SELECT * FROM xyz } {} } {0 1 2 a b 1 {Hi Y'all}} do_test tableapi-3.3 { for {set i 2} {$i<=50} {incr i} { sqlite_get_table_printf $::dbx \ "INSERT INTO xyz VALUES($i,'(%s)')" $i } sqlite_get_table_printf $::dbx { SELECT * FROM xyz ORDER BY a } {} } {0 50 2 a b 1 {Hi Y'all} 2 (2) 3 (3) 4 (4) 5 (5) 6 (6) 7 (7) 8 (8) 9 (9) 10 (10) 11 (11) 12 (12) 13 (13) 14 (14) 15 (15) 16 (16) 17 (17) 18 (18) 19 (19) 20 (20) 21 (21) 22 (22) 23 (23) 24 (24) 25 (25) 26 (26) 27 (27) 28 (28) 29 (29) 30 (30) 31 (31) 32 (32) 33 (33) 34 (34) 35 (35) 36 (36) 37 (37) 38 (38) 39 (39) 40 (40) 41 (41) 42 (42) 43 (43) 44 (44) 45 (45) 46 (46) 47 (47) 48 (48) 49 (49) 50 (50)} do_test tableapi-3.3.1 { sqlite_get_table_printf $::dbx { SELECT * FROM xyz WHERE a>49 ORDER BY a } {} } {0 1 2 a b 50 (50)} do_test tableapi-3.3.2 { sqlite_get_table_printf $::dbx { SELECT * FROM xyz WHERE a>47 ORDER BY a } {} } {0 3 2 a b 48 (48) 49 (49) 50 (50)} do_test tableapi-3.4 { set ::big_str [sqlite_mprintf_str {%500'* Hello %500'*} 0 0 {}] sqlite_get_table_printf $::dbx { INSERT INTO xyz VALUES(51,'%q') } $::big_str } {0 0 0} do_test tableapi-3.5 { sqlite_get_table_printf $::dbx { SELECT * FROM xyz WHERE a>49 ORDER BY a; } {} } "0 2 2 a b 50 (50) 51 \173$::big_str\175" do_test tableapi-3.6 { sqlite_get_table_printf $::dbx { INSERT INTO xyz VALUES(52,NULL) } {} sqlite_get_table_printf $::dbx { SELECT * FROM xyz WHERE a IN (42,50,52) ORDER BY a DESC } {} } {0 3 2 a b 52 NULL 50 (50) 42 (42)} do_test tableapi-3.7 { sqlite_get_table_printf $::dbx { SELECT * FROM xyz WHERE a>1000 } {} } {0 0 2 a b} do_test tableapi-4.1 { set rc [catch { sqlite_get_table_printf $::dbx { SELECT * FROM xyz; SELECT * FROM sqlite_master } {} } msg] concat $rc $msg } {0 1 {sqlite_get_table() called with two or more incompatible queries}} do_test tableapi-99.0 { sqlite_close $::dbx } {} finish_test