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