1*1da57d55SToomas Soome# 27c478bd9Sstevel@tonic-gate# 2001 September 15 37c478bd9Sstevel@tonic-gate# 47c478bd9Sstevel@tonic-gate# The author disclaims copyright to this source code. In place of 57c478bd9Sstevel@tonic-gate# a legal notice, here is a blessing: 67c478bd9Sstevel@tonic-gate# 77c478bd9Sstevel@tonic-gate# May you do good and not evil. 87c478bd9Sstevel@tonic-gate# May you find forgiveness for yourself and forgive others. 97c478bd9Sstevel@tonic-gate# May you share freely, never taking more than you give. 107c478bd9Sstevel@tonic-gate# 117c478bd9Sstevel@tonic-gate#*********************************************************************** 127c478bd9Sstevel@tonic-gate# This file implements regression tests for SQLite library. The 137c478bd9Sstevel@tonic-gate# focus of this file is exercising the code in main.c. 147c478bd9Sstevel@tonic-gate# 157c478bd9Sstevel@tonic-gate# $Id: main.test,v 1.14 2003/05/04 17:58:27 drh Exp $ 167c478bd9Sstevel@tonic-gate 177c478bd9Sstevel@tonic-gateset testdir [file dirname $argv0] 187c478bd9Sstevel@tonic-gatesource $testdir/tester.tcl 197c478bd9Sstevel@tonic-gate 207c478bd9Sstevel@tonic-gate# Tests of the sqlite_complete() function. 217c478bd9Sstevel@tonic-gate# 227c478bd9Sstevel@tonic-gatedo_test main-1.1 { 237c478bd9Sstevel@tonic-gate db complete {This is a test} 247c478bd9Sstevel@tonic-gate} {0} 257c478bd9Sstevel@tonic-gatedo_test main-1.2 { 267c478bd9Sstevel@tonic-gate db complete { 277c478bd9Sstevel@tonic-gate } 287c478bd9Sstevel@tonic-gate} {1} 297c478bd9Sstevel@tonic-gatedo_test main-1.3 { 307c478bd9Sstevel@tonic-gate db complete { 317c478bd9Sstevel@tonic-gate -- a comment ; 327c478bd9Sstevel@tonic-gate } 337c478bd9Sstevel@tonic-gate} {1} 347c478bd9Sstevel@tonic-gatedo_test main-1.4 { 357c478bd9Sstevel@tonic-gate db complete { 367c478bd9Sstevel@tonic-gate -- a comment ; 377c478bd9Sstevel@tonic-gate ; 387c478bd9Sstevel@tonic-gate } 397c478bd9Sstevel@tonic-gate} {1} 407c478bd9Sstevel@tonic-gatedo_test main-1.5 { 417c478bd9Sstevel@tonic-gate db complete {DROP TABLE 'xyz;} 427c478bd9Sstevel@tonic-gate} {0} 437c478bd9Sstevel@tonic-gatedo_test main-1.6 { 447c478bd9Sstevel@tonic-gate db complete {DROP TABLE 'xyz';} 457c478bd9Sstevel@tonic-gate} {1} 467c478bd9Sstevel@tonic-gatedo_test main-1.7 { 477c478bd9Sstevel@tonic-gate db complete {DROP TABLE "xyz;} 487c478bd9Sstevel@tonic-gate} {0} 497c478bd9Sstevel@tonic-gatedo_test main-1.8 { 507c478bd9Sstevel@tonic-gate db complete {DROP TABLE "xyz';} 517c478bd9Sstevel@tonic-gate} {0} 527c478bd9Sstevel@tonic-gatedo_test main-1.9 { 537c478bd9Sstevel@tonic-gate db complete {DROP TABLE "xyz";} 547c478bd9Sstevel@tonic-gate} {1} 557c478bd9Sstevel@tonic-gatedo_test main-1.10 { 567c478bd9Sstevel@tonic-gate db complete {DROP TABLE xyz; hi} 577c478bd9Sstevel@tonic-gate} {0} 587c478bd9Sstevel@tonic-gatedo_test main-1.11 { 597c478bd9Sstevel@tonic-gate db complete {DROP TABLE xyz; } 607c478bd9Sstevel@tonic-gate} {1} 617c478bd9Sstevel@tonic-gatedo_test main-1.12 { 627c478bd9Sstevel@tonic-gate db complete {DROP TABLE xyz; -- hi } 637c478bd9Sstevel@tonic-gate} {1} 647c478bd9Sstevel@tonic-gatedo_test main-1.13 { 657c478bd9Sstevel@tonic-gate db complete {DROP TABLE xyz; -- hi 667c478bd9Sstevel@tonic-gate } 677c478bd9Sstevel@tonic-gate} {1} 687c478bd9Sstevel@tonic-gatedo_test main-1.14 { 697c478bd9Sstevel@tonic-gate db complete {SELECT a-b FROM t1; } 707c478bd9Sstevel@tonic-gate} {1} 717c478bd9Sstevel@tonic-gatedo_test main-1.15 { 727c478bd9Sstevel@tonic-gate db complete {SELECT a-b FROM t1 } 737c478bd9Sstevel@tonic-gate} {0} 747c478bd9Sstevel@tonic-gatedo_test main-1.16 { 757c478bd9Sstevel@tonic-gate db complete { 767c478bd9Sstevel@tonic-gate CREATE TABLE abc(x,y); 777c478bd9Sstevel@tonic-gate } 787c478bd9Sstevel@tonic-gate} {1} 797c478bd9Sstevel@tonic-gatedo_test main-1.17 { 807c478bd9Sstevel@tonic-gate db complete { 817c478bd9Sstevel@tonic-gate CREATE TRIGGER xyz AFTER DELETE abc BEGIN UPDATE pqr; 827c478bd9Sstevel@tonic-gate } 837c478bd9Sstevel@tonic-gate} {0} 847c478bd9Sstevel@tonic-gatedo_test main-1.18 { 857c478bd9Sstevel@tonic-gate db complete { 867c478bd9Sstevel@tonic-gate CREATE TRIGGER xyz AFTER DELETE abc BEGIN UPDATE pqr; END; 877c478bd9Sstevel@tonic-gate } 887c478bd9Sstevel@tonic-gate} {1} 897c478bd9Sstevel@tonic-gatedo_test main-1.19 { 907c478bd9Sstevel@tonic-gate db complete { 917c478bd9Sstevel@tonic-gate CREATE TRIGGER xyz AFTER DELETE abc BEGIN 927c478bd9Sstevel@tonic-gate UPDATE pqr; 937c478bd9Sstevel@tonic-gate unknown command; 947c478bd9Sstevel@tonic-gate } 957c478bd9Sstevel@tonic-gate} {0} 967c478bd9Sstevel@tonic-gatedo_test main-1.20 { 977c478bd9Sstevel@tonic-gate db complete { 987c478bd9Sstevel@tonic-gate CREATE TRIGGER xyz AFTER DELETE backend BEGIN 997c478bd9Sstevel@tonic-gate UPDATE pqr; 1007c478bd9Sstevel@tonic-gate } 1017c478bd9Sstevel@tonic-gate} {0} 1027c478bd9Sstevel@tonic-gatedo_test main-1.21 { 1037c478bd9Sstevel@tonic-gate db complete { 1047c478bd9Sstevel@tonic-gate CREATE TRIGGER xyz AFTER DELETE end BEGIN 1057c478bd9Sstevel@tonic-gate SELECT a, b FROM end; 1067c478bd9Sstevel@tonic-gate } 1077c478bd9Sstevel@tonic-gate} {0} 1087c478bd9Sstevel@tonic-gatedo_test main-1.22 { 1097c478bd9Sstevel@tonic-gate db complete { 1107c478bd9Sstevel@tonic-gate CREATE TRIGGER xyz AFTER DELETE end BEGIN 1117c478bd9Sstevel@tonic-gate SELECT a, b FROM end; 1127c478bd9Sstevel@tonic-gate END; 1137c478bd9Sstevel@tonic-gate } 1147c478bd9Sstevel@tonic-gate} {1} 1157c478bd9Sstevel@tonic-gatedo_test main-1.23 { 1167c478bd9Sstevel@tonic-gate db complete { 1177c478bd9Sstevel@tonic-gate CREATE TRIGGER xyz AFTER DELETE end BEGIN 1187c478bd9Sstevel@tonic-gate SELECT a, b FROM end; 1197c478bd9Sstevel@tonic-gate END; 1207c478bd9Sstevel@tonic-gate SELECT a, b FROM end; 1217c478bd9Sstevel@tonic-gate } 1227c478bd9Sstevel@tonic-gate} {1} 1237c478bd9Sstevel@tonic-gatedo_test main-1.24 { 1247c478bd9Sstevel@tonic-gate db complete { 1257c478bd9Sstevel@tonic-gate CREATE TRIGGER xyz AFTER DELETE [;end;] BEGIN 1267c478bd9Sstevel@tonic-gate UPDATE pqr; 1277c478bd9Sstevel@tonic-gate } 1287c478bd9Sstevel@tonic-gate} {0} 1297c478bd9Sstevel@tonic-gatedo_test main-1.25 { 1307c478bd9Sstevel@tonic-gate db complete { 1317c478bd9Sstevel@tonic-gate CREATE TRIGGER xyz AFTER DELETE backend BEGIN 1327c478bd9Sstevel@tonic-gate UPDATE pqr SET a=[;end;];;; 1337c478bd9Sstevel@tonic-gate } 1347c478bd9Sstevel@tonic-gate} {0} 1357c478bd9Sstevel@tonic-gatedo_test main-1.26 { 1367c478bd9Sstevel@tonic-gate db complete { 1377c478bd9Sstevel@tonic-gate CREATE -- a comment 1387c478bd9Sstevel@tonic-gate TRIGGER xyz AFTER DELETE backend BEGIN 1397c478bd9Sstevel@tonic-gate UPDATE pqr SET a=5; 1407c478bd9Sstevel@tonic-gate } 1417c478bd9Sstevel@tonic-gate} {0} 1427c478bd9Sstevel@tonic-gatedo_test main-1.27.1 { 1437c478bd9Sstevel@tonic-gate db complete { 1447c478bd9Sstevel@tonic-gate CREATE -- a comment 1457c478bd9Sstevel@tonic-gate TRIGGERX xyz AFTER DELETE backend BEGIN 1467c478bd9Sstevel@tonic-gate UPDATE pqr SET a=5; 1477c478bd9Sstevel@tonic-gate } 1487c478bd9Sstevel@tonic-gate} {1} 1497c478bd9Sstevel@tonic-gatedo_test main-1.27.2 { 1507c478bd9Sstevel@tonic-gate db complete { 1517c478bd9Sstevel@tonic-gate CREATE/**/TRIGGER xyz AFTER DELETE backend BEGIN 1527c478bd9Sstevel@tonic-gate UPDATE pqr SET a=5; 1537c478bd9Sstevel@tonic-gate } 1547c478bd9Sstevel@tonic-gate} {0} 1557c478bd9Sstevel@tonic-gatedo_test main-1.27.3 { 1567c478bd9Sstevel@tonic-gate db complete { 1577c478bd9Sstevel@tonic-gate /* */ EXPLAIN -- A comment 1587c478bd9Sstevel@tonic-gate CREATE/**/TRIGGER xyz AFTER DELETE backend BEGIN 1597c478bd9Sstevel@tonic-gate UPDATE pqr SET a=5; 1607c478bd9Sstevel@tonic-gate } 1617c478bd9Sstevel@tonic-gate} {0} 1627c478bd9Sstevel@tonic-gatedo_test main-1.27.4 { 1637c478bd9Sstevel@tonic-gate db complete { 1647c478bd9Sstevel@tonic-gate BOGUS token 1657c478bd9Sstevel@tonic-gate CREATE TRIGGER xyz AFTER DELETE backend BEGIN 1667c478bd9Sstevel@tonic-gate UPDATE pqr SET a=5; 1677c478bd9Sstevel@tonic-gate } 1687c478bd9Sstevel@tonic-gate} {1} 1697c478bd9Sstevel@tonic-gatedo_test main-1.27.5 { 1707c478bd9Sstevel@tonic-gate db complete { 171*1da57d55SToomas Soome EXPLAIN 1727c478bd9Sstevel@tonic-gate CREATE TEMP TRIGGER xyz AFTER DELETE backend BEGIN 1737c478bd9Sstevel@tonic-gate UPDATE pqr SET a=5; 1747c478bd9Sstevel@tonic-gate } 1757c478bd9Sstevel@tonic-gate} {0} 1767c478bd9Sstevel@tonic-gatedo_test main-1.28 { 1777c478bd9Sstevel@tonic-gate db complete { 1787c478bd9Sstevel@tonic-gate CREATE TEMP TRIGGER xyz AFTER DELETE backend BEGIN 1797c478bd9Sstevel@tonic-gate UPDATE pqr SET a=5; 1807c478bd9Sstevel@tonic-gate } 1817c478bd9Sstevel@tonic-gate} {0} 1827c478bd9Sstevel@tonic-gatedo_test main-1.29 { 1837c478bd9Sstevel@tonic-gate db complete { 1847c478bd9Sstevel@tonic-gate CREATE TRIGGER xyz AFTER DELETE backend BEGIN 1857c478bd9Sstevel@tonic-gate UPDATE pqr SET a=5; 1867c478bd9Sstevel@tonic-gate EXPLAIN select * from xyz; 1877c478bd9Sstevel@tonic-gate } 1887c478bd9Sstevel@tonic-gate} {0} 1897c478bd9Sstevel@tonic-gatedo_test main-1.30 { 1907c478bd9Sstevel@tonic-gate db complete { 1917c478bd9Sstevel@tonic-gate CREATE TABLE /* In comment ; */ 1927c478bd9Sstevel@tonic-gate } 1937c478bd9Sstevel@tonic-gate} {0} 1947c478bd9Sstevel@tonic-gatedo_test main-1.31 { 1957c478bd9Sstevel@tonic-gate db complete { 1967c478bd9Sstevel@tonic-gate CREATE TABLE /* In comment ; */ hi; 1977c478bd9Sstevel@tonic-gate } 1987c478bd9Sstevel@tonic-gate} {1} 1997c478bd9Sstevel@tonic-gatedo_test main-1.31 { 2007c478bd9Sstevel@tonic-gate db complete { 2017c478bd9Sstevel@tonic-gate CREATE TABLE /* In comment ; */; 2027c478bd9Sstevel@tonic-gate } 2037c478bd9Sstevel@tonic-gate} {1} 2047c478bd9Sstevel@tonic-gatedo_test main-1.32 { 2057c478bd9Sstevel@tonic-gate db complete { 2067c478bd9Sstevel@tonic-gate stuff; 2077c478bd9Sstevel@tonic-gate /* 2087c478bd9Sstevel@tonic-gate CREATE TABLE 2097c478bd9Sstevel@tonic-gate multiple lines 2107c478bd9Sstevel@tonic-gate of text 2117c478bd9Sstevel@tonic-gate */ 2127c478bd9Sstevel@tonic-gate } 2137c478bd9Sstevel@tonic-gate} {1} 2147c478bd9Sstevel@tonic-gatedo_test main-1.33 { 2157c478bd9Sstevel@tonic-gate db complete { 2167c478bd9Sstevel@tonic-gate /* 2177c478bd9Sstevel@tonic-gate CREATE TABLE 2187c478bd9Sstevel@tonic-gate multiple lines 2197c478bd9Sstevel@tonic-gate of text; 2207c478bd9Sstevel@tonic-gate } 2217c478bd9Sstevel@tonic-gate} {0} 2227c478bd9Sstevel@tonic-gatedo_test main-1.34 { 2237c478bd9Sstevel@tonic-gate db complete { 2247c478bd9Sstevel@tonic-gate /* 2257c478bd9Sstevel@tonic-gate CREATE TABLE 2267c478bd9Sstevel@tonic-gate multiple lines "*/ 2277c478bd9Sstevel@tonic-gate of text; 2287c478bd9Sstevel@tonic-gate } 2297c478bd9Sstevel@tonic-gate} {1} 2307c478bd9Sstevel@tonic-gatedo_test main-1.35 { 2317c478bd9Sstevel@tonic-gate db complete {hi /**/ there;} 2327c478bd9Sstevel@tonic-gate} {1} 2337c478bd9Sstevel@tonic-gatedo_test main-1.36 { 2347c478bd9Sstevel@tonic-gate db complete {hi there/***/;} 2357c478bd9Sstevel@tonic-gate} {1} 2367c478bd9Sstevel@tonic-gate 2377c478bd9Sstevel@tonic-gate 2387c478bd9Sstevel@tonic-gate# Try to open a database with a corrupt database file. 2397c478bd9Sstevel@tonic-gate# 2407c478bd9Sstevel@tonic-gatedo_test main-2.0 { 2417c478bd9Sstevel@tonic-gate catch {db close} 2427c478bd9Sstevel@tonic-gate file delete -force test.db 2437c478bd9Sstevel@tonic-gate set fd [open test.db w] 2447c478bd9Sstevel@tonic-gate puts $fd hi! 2457c478bd9Sstevel@tonic-gate close $fd 2467c478bd9Sstevel@tonic-gate set v [catch {sqlite db test.db} msg] 2477c478bd9Sstevel@tonic-gate if {$v} {lappend v $msg} {lappend v {}} 2487c478bd9Sstevel@tonic-gate} {0 {}} 2497c478bd9Sstevel@tonic-gate 250*1da57d55SToomas Soome# Here are some tests for tokenize.c. 2517c478bd9Sstevel@tonic-gate# 2527c478bd9Sstevel@tonic-gatedo_test main-3.1 { 2537c478bd9Sstevel@tonic-gate catch {db close} 2547c478bd9Sstevel@tonic-gate foreach f [glob -nocomplain testdb/*] {file delete -force $f} 2557c478bd9Sstevel@tonic-gate file delete -force testdb 2567c478bd9Sstevel@tonic-gate sqlite db testdb 2577c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT * from T1 where x!!5}} msg] 2587c478bd9Sstevel@tonic-gate lappend v $msg 2597c478bd9Sstevel@tonic-gate} {1 {unrecognized token: "!!"}} 2607c478bd9Sstevel@tonic-gatedo_test main-3.2 { 2617c478bd9Sstevel@tonic-gate catch {db close} 2627c478bd9Sstevel@tonic-gate foreach f [glob -nocomplain testdb/*] {file delete -force $f} 2637c478bd9Sstevel@tonic-gate file delete -force testdb 2647c478bd9Sstevel@tonic-gate sqlite db testdb 2657c478bd9Sstevel@tonic-gate set v [catch {execsql {SELECT * from T1 where @x}} msg] 2667c478bd9Sstevel@tonic-gate lappend v $msg 2677c478bd9Sstevel@tonic-gate} {1 {unrecognized token: "@"}} 2687c478bd9Sstevel@tonic-gate 2697c478bd9Sstevel@tonic-gatedo_test main-3.3 { 2707c478bd9Sstevel@tonic-gate catch {db close} 2717c478bd9Sstevel@tonic-gate foreach f [glob -nocomplain testdb/*] {file delete -force $f} 2727c478bd9Sstevel@tonic-gate file delete -force testdb 2737c478bd9Sstevel@tonic-gate sqlite db testdb 2747c478bd9Sstevel@tonic-gate execsql { 2757c478bd9Sstevel@tonic-gate create table T1(X REAL); 2767c478bd9Sstevel@tonic-gate insert into T1 values(0.5); 2777c478bd9Sstevel@tonic-gate insert into T1 values(0.5e2); 2787c478bd9Sstevel@tonic-gate insert into T1 values(0.5e-002); 2797c478bd9Sstevel@tonic-gate insert into T1 values(5e-002); 2807c478bd9Sstevel@tonic-gate insert into T1 values(-5.0e-2); 2817c478bd9Sstevel@tonic-gate insert into T1 values(-5.1e-2); 2827c478bd9Sstevel@tonic-gate insert into T1 values(0.5e2); 2837c478bd9Sstevel@tonic-gate insert into T1 values(0.5E+02); 2847c478bd9Sstevel@tonic-gate insert into T1 values(5E+02); 2857c478bd9Sstevel@tonic-gate insert into T1 values(5.0E+03); 2867c478bd9Sstevel@tonic-gate select x*10 from T1 order by x*5; 2877c478bd9Sstevel@tonic-gate } 2887c478bd9Sstevel@tonic-gate} {-0.51 -0.5 0.05 0.5 5 500 500 500 5000 50000} 2897c478bd9Sstevel@tonic-gatedo_test main-3.4 { 2907c478bd9Sstevel@tonic-gate set v [catch {execsql {create bogus}} msg] 2917c478bd9Sstevel@tonic-gate lappend v $msg 2927c478bd9Sstevel@tonic-gate} {1 {near "bogus": syntax error}} 2937c478bd9Sstevel@tonic-gatedo_test main-3.5 { 2947c478bd9Sstevel@tonic-gate set v [catch {execsql {create}} msg] 2957c478bd9Sstevel@tonic-gate lappend v $msg 2967c478bd9Sstevel@tonic-gate} {1 {near "create": syntax error}} 2977c478bd9Sstevel@tonic-gate 2987c478bd9Sstevel@tonic-gatefinish_test 299