wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 # 2006 January 02
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 script is descending indices.
13 #
14 # $Id: descidx3.test,v 1.6 2008/03/19 00:21:31 drh Exp $
15 #
16  
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
19  
20 # Do not use a codec for tests in this file, as the database file is
21 # manipulated directly using tcl scripts (using the [hexio_write] command).
22 #
23 do_not_use_codec
24  
25 ifcapable !bloblit {
26 finish_test
27 return
28 }
29 db eval {PRAGMA legacy_file_format=OFF}
30  
31 # This procedure sets the value of the file-format in file 'test.db'
32 # to $newval. Also, the schema cookie is incremented.
33 #
34 proc set_file_format {newval} {
35 hexio_write test.db 44 [hexio_render_int32 $newval]
36 set schemacookie [hexio_get_int [hexio_read test.db 40 4]]
37 incr schemacookie
38 hexio_write test.db 40 [hexio_render_int32 $schemacookie]
39 return {}
40 }
41  
42 # This procedure returns the value of the file-format in file 'test.db'.
43 #
44 proc get_file_format {{fname test.db}} {
45 return [hexio_get_int [hexio_read $fname 44 4]]
46 }
47  
48 # Verify that the file format starts as 4.
49 #
50 do_test descidx3-1.1 {
51 execsql {
52 CREATE TABLE t1(i INTEGER PRIMARY KEY,a,b,c,d);
53 CREATE INDEX t1i1 ON t1(a DESC, b ASC, c DESC);
54 CREATE INDEX t1i2 ON t1(b DESC, c ASC, d DESC);
55 }
56 get_file_format
57 } {4}
58  
59 # Put some information in the table and verify that the descending
60 # index actually works.
61 #
62 do_test descidx3-2.1 {
63 execsql {
64 INSERT INTO t1 VALUES(1, NULL, NULL, NULL, NULL);
65 INSERT INTO t1 VALUES(2, 2, 2, 2, 2);
66 INSERT INTO t1 VALUES(3, 3, 3, 3, 3);
67 INSERT INTO t1 VALUES(4, 2.5, 2.5, 2.5, 2.5);
68 INSERT INTO t1 VALUES(5, -5, -5, -5, -5);
69 INSERT INTO t1 VALUES(6, 'six', 'six', 'six', 'six');
70 INSERT INTO t1 VALUES(7, x'77', x'77', x'77', x'77');
71 INSERT INTO t1 VALUES(8, 'eight', 'eight', 'eight', 'eight');
72 INSERT INTO t1 VALUES(9, x'7979', x'7979', x'7979', x'7979');
73 SELECT count(*) FROM t1;
74 }
75 } 9
76 do_test descidx3-2.2 {
77 execsql {
78 SELECT i FROM t1 ORDER BY a;
79 }
80 } {1 5 2 4 3 8 6 7 9}
81 do_test descidx3-2.3 {
82 execsql {
83 SELECT i FROM t1 ORDER BY a DESC;
84 }
85 } {9 7 6 8 3 4 2 5 1}
86  
87 # The "natural" order for the index is decreasing
88 do_test descidx3-2.4 {
89 execsql {
90 SELECT i FROM t1 WHERE a<=x'7979';
91 }
92 } {9 7 6 8 3 4 2 5}
93 do_test descidx3-2.5 {
94 execsql {
95 SELECT i FROM t1 WHERE a>-99;
96 }
97 } {9 7 6 8 3 4 2 5}
98  
99 # Even when all values of t1.a are the same, sorting by A returns
100 # the rows in reverse order because this the natural order of the
101 # index.
102 #
103 do_test descidx3-3.1 {
104 execsql {
105 UPDATE t1 SET a=1;
106 SELECT i FROM t1 ORDER BY a;
107 }
108 } {9 7 6 8 3 4 2 5 1}
109 do_test descidx3-3.2 {
110 execsql {
111 SELECT i FROM t1 WHERE a=1 AND b>0 AND b<'zzz'
112 }
113 } {2 4 3 8 6}
114 do_test descidx3-3.3 {
115 execsql {
116 SELECT i FROM t1 WHERE b>0 AND b<'zzz'
117 }
118 } {6 8 3 4 2}
119 do_test descidx3-3.4 {
120 execsql {
121 SELECT i FROM t1 WHERE a=1 AND b>-9999 AND b<x'ffffffff'
122 }
123 } {5 2 4 3 8 6 7 9}
124 do_test descidx3-3.5 {
125 execsql {
126 SELECT i FROM t1 WHERE b>-9999 AND b<x'ffffffff'
127 }
128 } {9 7 6 8 3 4 2 5}
129  
130 ifcapable subquery {
131 # If the subquery capability is not compiled in to the binary, then
132 # the IN(...) operator is not available. Hence these tests cannot be
133 # run.
134 do_test descidx3-4.1 {
135 execsql {
136 UPDATE t1 SET a=2 WHERE i<6;
137 SELECT i FROM t1 WHERE a IN (1,2) AND b>0 AND b<'zzz';
138 }
139 } {8 6 2 4 3}
140 do_test descidx3-4.2 {
141 execsql {
142 UPDATE t1 SET a=1;
143 SELECT i FROM t1 WHERE a IN (1,2) AND b>0 AND b<'zzz';
144 }
145 } {2 4 3 8 6}
146 do_test descidx3-4.3 {
147 execsql {
148 UPDATE t1 SET b=2;
149 SELECT i FROM t1 WHERE a IN (1,2) AND b>0 AND b<'zzz';
150 }
151 } {9 7 6 8 3 4 2 5 1}
152 }
153  
154 finish_test