xref: /sqlite-3.40.0/test/main.test (revision 1691cd4b) 
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 SQLite library.  The
12# focus of this file is exercising the code in main.c.
13#
14# $Id: main.test,v 1.32 2009/04/28 04:51:29 drh Exp $
15
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18
19# Only do the next group of tests if the sqlite3_complete API is available
20#
21ifcapable {complete} {
22
23# Tests of the sqlite_complete() function.
24#
25do_test main-1.1 {
26  db complete {This is a test}
27} {0}
28do_test main-1.2 {
29  db complete {
30  }
31} {1}
32do_test main-1.3 {
33  db complete {
34     -- a comment ;
35  }
36} {1}
37do_test main-1.4 {
38  db complete {
39     -- a comment ;
40     ;
41  }
42} {1}
43do_test main-1.5 {
44  db complete {DROP TABLE 'xyz;}
45} {0}
46do_test main-1.6 {
47  db complete {DROP TABLE 'xyz';}
48} {1}
49do_test main-1.7 {
50  db complete {DROP TABLE "xyz;}
51} {0}
52do_test main-1.8 {
53  db complete {DROP TABLE "xyz';}
54} {0}
55do_test main-1.9 {
56  db complete {DROP TABLE "xyz";}
57} {1}
58do_test main-1.10 {
59  db complete {DROP TABLE xyz; hi}
60} {0}
61do_test main-1.11 {
62  db complete {DROP TABLE xyz; }
63} {1}
64do_test main-1.12 {
65  db complete {DROP TABLE xyz; -- hi }
66} {1}
67do_test main-1.13 {
68  db complete {DROP TABLE xyz; -- hi
69  }
70} {1}
71do_test main-1.14 {
72  db complete {SELECT a-b FROM t1; }
73} {1}
74do_test main-1.15 {
75  db complete {SELECT a/e FROM t1 }
76} {0}
77do_test main-1.16 {
78  db complete {
79    CREATE TABLE abc(x,y);
80  }
81} {1}
82ifcapable {trigger} {
83  do_test main-1.17 {
84    db complete {
85      CREATE TRIGGER xyz AFTER DELETE abc BEGIN UPDATE pqr;
86    }
87  } {0}
88  do_test main-1.17.2 {
89    db complete {
90      EXPLAIN CREATE TRIGGER xyz AFTER DELETE abc BEGIN UPDATE pqr;
91    }
92  } {0}
93  do_test main-1.17.3 {
94    db complete {
95      EXPLAIN QUERY PLAN CREATE TRIGGER xyz AFTER DELETE abc BEGIN UPDATE pqr;
96    }
97  } {0}
98  do_test main-1.18 {
99    db complete {
100      CREATE TRIGGER xyz AFTER DELETE abc BEGIN UPDATE pqr; END;
101    }
102  } {1}
103  do_test main-1.19 {
104    db complete {
105      CREATE TRIGGER xyz AFTER DELETE abc BEGIN
106         UPDATE pqr;
107         unknown command;
108    }
109  } {0}
110  do_test main-1.20 {
111    db complete {
112      CREATE TRIGGER xyz AFTER DELETE backend BEGIN
113         UPDATE pqr;
114    }
115  } {0}
116  do_test main-1.21 {
117    db complete {
118      CREATE TRIGGER xyz AFTER DELETE end BEGIN
119         SELECT a, b FROM end;
120    }
121  } {0}
122  do_test main-1.22 {
123    db complete {
124      CREATE TRIGGER xyz AFTER DELETE end BEGIN
125         SELECT a, b FROM end;
126      END;
127    }
128  } {1}
129  do_test main-1.23 {
130    db complete {
131      CREATE TRIGGER xyz AFTER DELETE end BEGIN
132         SELECT a, b FROM end;
133      END;
134      SELECT a, b FROM end;
135    }
136  } {1}
137  do_test main-1.24 {
138    db complete {
139      CREATE TRIGGER xyz AFTER DELETE [;end;] BEGIN
140         UPDATE pqr;
141    }
142  } {0}
143  do_test main-1.25 {
144    db complete {
145      CREATE TRIGGER xyz AFTER DELETE backend BEGIN
146         UPDATE cantor SET a=[;end;];;;
147    }
148  } {0}
149  do_test main-1.26 {
150    db complete {
151      CREATE -- a comment
152      TRIGGER exy AFTER DELETE backend BEGIN
153         UPDATE pqr SET a=5;
154    }
155  } {0}
156  do_test main-1.27.1 {
157    db complete {
158      CREATE -- a comment
159      TRIGGERX tangentxx AFTER DELETE backend BEGIN
160         UPDATE pqr SET a=5;
161    }
162  } {1}
163  do_test main-1.27.2 {
164    db complete {
165      CREATE/**/TRIGGER tiger00 AFTER DELETE backend BEGIN
166         UPDATE pqr SET a=5;
167    }
168  } {0}
169  ifcapable {explain} {
170    do_test main-1.27.3 {
171      db complete {
172        /* */ EXPLAIN -- A comment
173        CREATE/**/TRIGGER ezxyz12 AFTER DELETE backend BEGIN
174           UPDATE pqr SET a=5;
175      }
176    } {0}
177  }
178  do_test main-1.27.4 {
179    db complete {
180      BOGUS token
181      CREATE  TRIGGER xyz AFTER DELETE backend BEGIN
182         UPDATE pqr SET a=5;
183    }
184  } {1}
185  ifcapable {explain} {
186    do_test main-1.27.5 {
187      db complete {
188        EXPLAIN
189        CREATE TEMP TRIGGER xyz AFTER DELETE backend BEGIN
190           UPDATE pqr SET a=5;
191      }
192    } {0}
193  }
194  do_test main-1.28 {
195    db complete {
196      CREATE TEMPORARY TRIGGER xyz AFTER DELETE backend BEGIN
197         UPDATE pqr SET a=5;
198    }
199  } {0}
200  do_test main-1.29 {
201    db complete {
202      CREATE TRIGGER xyz AFTER DELETE backend BEGIN
203         UPDATE pqr SET a=5;
204         EXPLAIN select * from xyz;
205    }
206  } {0}
207
208} ;# end ifcapable {complete}
209
210}
211do_test main-1.30 {
212  db complete {
213     CREATE TABLE /* In comment ; */
214  }
215} {0}
216do_test main-1.31 {
217  db complete {
218     CREATE TABLE /* In comment ; */ hi;
219  }
220} {1}
221do_test main-1.31 {
222  db complete {
223     CREATE TABLE /* In comment ; */;
224  }
225} {1}
226do_test main-1.32 {
227  db complete {
228     stuff;
229     /*
230       CREATE TABLE
231       multiple lines
232       of text
233     */
234  }
235} {1}
236do_test main-1.33 {
237  db complete {
238     /*
239       CREATE TABLE
240       multiple lines
241       of text;
242  }
243} {0}
244do_test main-1.34 {
245  db complete {
246     /*
247       CREATE TABLE
248       multiple lines "*/
249       of text;
250  }
251} {1}
252do_test main-1.35 {
253  db complete {hi /**/ there;}
254} {1}
255do_test main-1.36 {
256  db complete {hi there/***/;}
257} {1}
258do_test main-1.37 {
259  db complete {hi there/**}
260} {0}
261do_test main-1.38 {
262  db complete {hi [there}
263} {0}
264
265ifcapable {trigger} {
266  # Characters less than \040 can never be part of an identifier.
267  # Characters greater than \u177 are always identifier characters.
268  do_test main-1.100 {
269    db complete "create \037\036\035\034trigger\001\002;"
270  } {1}
271  do_test main-1.101 {
272    db complete "create trigger\200;"
273  } {1}
274  do_test main-1.102 {
275    db complete "create \200trigger;"
276  } {1}
277}
278
279
280# Try to open a database with a corrupt database file.
281#
282do_test main-2.0 {
283  catch {db close}
284  file delete -force test.db
285  set fd [open test.db w]
286  puts $fd hi!
287  close $fd
288  set v [catch {sqlite3 db test.db} msg]
289  if {$v} {lappend v $msg} {lappend v {}}
290} {0 {}}
291
292# Here are some tests for tokenize.c.
293#
294do_test main-3.1 {
295  catch {db close}
296  foreach f [glob -nocomplain testdb/*] {file delete -force $f}
297  file delete -force testdb
298  sqlite3 db testdb
299  set v [catch {execsql {SELECT * from T1 where x!!5}} msg]
300  lappend v $msg
301} {1 {unrecognized token: "!!"}}
302do_test main-3.2 {
303  catch {db close}
304  foreach f [glob -nocomplain testdb/*] {file delete -force $f}
305  file delete -force testdb
306  sqlite3 db testdb
307  set v [catch {execsql {SELECT * from T1 where ^x}} msg]
308  lappend v $msg
309} {1 {unrecognized token: "^"}}
310do_test main-3.2.2 {
311  catchsql {select 'abc}
312} {1 {unrecognized token: "'abc"}}
313do_test main-3.2.3 {
314  catchsql {select "abc}
315} {1 {unrecognized token: ""abc"}}
316do_test main-3.2.4 {
317  catchsql {select [abc}
318} {1 {unrecognized token: "[abc"}}
319do_test main-3.2.5 {
320  catchsql {select x'4869}
321} {1 {unrecognized token: "x'4869"}}
322do_test main-3.2.6 {
323  catchsql {select x'4869'}
324} {0 Hi}
325do_test main-3.2.7 {
326  catchsql {select x'48695'}
327} {1 {unrecognized token: "x'48695'"}}
328do_test main-3.2.8 {
329  catchsql {select x'486x'}
330} {1 {unrecognized token: "x'486x'"}}
331do_test main-3.2.9 {
332  catchsql {select $abc(}
333} {1 {unrecognized token: "$abc("}}
334do_test main-3.2.10 {
335  catchsql {select $abc(x}
336} {1 {unrecognized token: "$abc(x"}}
337set xyz 123
338do_test main-3.2.11 {
339  catchsql {select $::xyz}
340} {0 123}
341namespace eval ::testnamespace {
342  variable xyz 321
343}
344do_test main-3.2.12 {
345  catchsql {select $testnamespace::xyz}
346} {0 321}
347do_test main-3.2.13 {
348  catchsql {select $(abc)}
349} {1 {unrecognized token: "$"}}
350do_test main-3.2.14 {
351  set hi\u1234x 987
352  db eval "select \$hi\u1234x"
353} {987}
354do_test main-3.2.15 {
355  catchsql "select 456\u1234"
356} [list 1 "unrecognized token: \"456\u1234\""]
357do_test main-3.2.16 {
358  catchsql {select cast(3.14e+4 AS integer)}
359} {0 31400}
360do_test main-3.2.17 {
361  catchsql {select cast(3.14e+04 AS integer)}
362} {0 31400}
363do_test main-3.2.18 {
364  catchsql {select cast(3.14e+004 AS integer)}
365} {0 31400}
366do_test main-3.2.19 {
367  catchsql {select cast(3.14e4 AS integer)}
368} {0 31400}
369do_test main-3.2.20 {
370  catchsql {select cast(3.14e04 AS integer)}
371} {0 31400}
372do_test main-3.2.21 {
373  catchsql {select cast(3.14e004 AS integer)}
374} {0 31400}
375do_test main-3.2.16 {
376  catchsql {select cast(3.14E+4 AS integer)}
377} {0 31400}
378do_test main-3.2.17 {
379  catchsql {select cast(3.14E+04 AS integer)}
380} {0 31400}
381do_test main-3.2.18 {
382  catchsql {select cast(3.14E+004 AS integer)}
383} {0 31400}
384do_test main-3.2.19 {
385  catchsql {select cast(3.14E4 AS integer)}
386} {0 31400}
387do_test main-3.2.20 {
388  catchsql {select cast(3.14E04 AS integer)}
389} {0 31400}
390do_test main-3.2.21 {
391  catchsql {select cast(3.14E004 AS integer)}
392} {0 31400}
393do_test main-3.2.22 {
394  catchsql {select cast(3.14e-4 * 1e8 AS integer)}
395} {0 31400}
396do_test main-3.2.23 {
397  catchsql {select cast(3.14E-04 * 1E08 AS integer)}
398} {0 31400}
399do_test main-3.2.24 {
400  catchsql {select cast(3.14e-004 * 01.0E+8 AS integer)}
401} {0 31400}
402do_test main-3.2.25 {
403  catchsql {select 123/*abc}
404} {0 123}
405do_test main-3.2.26 {
406  catchsql {select 123/***abc}
407} {0 123}
408do_test main-3.2.27 {
409  catchsql {select 123/*/*2}
410} {0 123}
411do_test main-3.2.28 {
412  catchsql {select 123/**/*2}
413} {0 246}
414do_test main-3.2.29 {
415  catchsql {select 123/}
416} {1 {near "/": syntax error}}
417do_test main-3.2.30 {
418  catchsql {select 123--5}
419} {0 123}
420
421
422do_test main-3.3 {
423  catch {db close}
424  foreach f [glob -nocomplain testdb/*] {file delete -force $f}
425  file delete -force testdb
426  sqlite3 db testdb
427  execsql {
428    create table T1(X REAL);  /* C-style comments allowed */
429    insert into T1 values(0.5);
430    insert into T1 values(0.5e2);
431    insert into T1 values(0.5e-002);
432    insert into T1 values(5e-002);
433    insert into T1 values(-5.0e-2);
434    insert into T1 values(-5.1e-2);
435    insert into T1 values(0.5e2);
436    insert into T1 values(0.5E+02);
437    insert into T1 values(5E+02);
438    insert into T1 values(5.0E+03);
439    select x*10 from T1 order by x*5;
440  }
441} {-0.51 -0.5 0.05 0.5 5.0 500.0 500.0 500.0 5000.0 50000.0}
442do_test main-3.4 {
443  set v [catch {execsql {create bogus}} msg]
444  lappend v $msg
445} {1 {near "bogus": syntax error}}
446do_test main-3.5 {
447  set v [catch {execsql {create}} msg]
448  lappend v $msg
449} {1 {near "create": syntax error}}
450do_test main-3.6 {
451  catchsql {SELECT 'abc' + #9}
452} {1 {near "#9": syntax error}}
453
454# The following test-case tests the linked list code used to manage
455# sqlite3_vfs structures.
456if {$::tcl_platform(platform)=="unix"
457     && [info command sqlite3async_initialize]!=""} {
458  ifcapable threadsafe {
459    do_test main-4.1 {
460      sqlite3_crash_enable 1
461      sqlite3_crash_enable 0
462
463      sqlite3async_initialize "" 1
464      sqlite3async_shutdown
465
466      sqlite3_crash_enable 1
467      sqlite3async_initialize "" 1
468      sqlite3_crash_enable 0
469      sqlite3async_shutdown
470
471      sqlite3_crash_enable 1
472      sqlite3async_initialize "" 1
473      sqlite3async_shutdown
474      sqlite3_crash_enable 0
475
476      sqlite3async_initialize "" 1
477      sqlite3_crash_enable 1
478      sqlite3_crash_enable 0
479      sqlite3async_shutdown
480
481      sqlite3async_initialize "" 1
482      sqlite3_crash_enable 1
483      sqlite3async_shutdown
484      sqlite3_crash_enable 0
485    } {}
486    do_test main-4.2 {
487      set rc [catch {sqlite3 db test.db -vfs crash} msg]
488      list $rc $msg
489    } {1 {no such vfs: crash}}
490    do_test main-4.3 {
491      set rc [catch {sqlite3 db test.db -vfs async} msg]
492      list $rc $msg
493    } {1 {no such vfs: async}}
494  }
495}
496
497finish_test
498