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