|
1 # 2005 September 17 |
|
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. |
|
12 # |
|
13 # This file implements tests to verify that ticket #1435 has been |
|
14 # fixed. |
|
15 # |
|
16 # |
|
17 # $Id: tkt1435.test,v 1.2 2006/01/17 09:35:02 danielk1977 Exp $ |
|
18 |
|
19 set testdir [file dirname $argv0] |
|
20 source $testdir/tester.tcl |
|
21 |
|
22 ifcapable !memorydb { |
|
23 finish_test |
|
24 return |
|
25 } |
|
26 |
|
27 # Construct the sample database. |
|
28 # |
|
29 do_test tkt1435-1.0 { |
|
30 sqlite3 db :memory: |
|
31 execsql { |
|
32 CREATE TABLE Instances( |
|
33 instanceId INTEGER PRIMARY KEY, |
|
34 troveName STR, |
|
35 versionId INT, |
|
36 flavorId INT, |
|
37 timeStamps STR, |
|
38 isPresent INT, |
|
39 pinned BOOLEAN |
|
40 ); |
|
41 INSERT INTO "Instances" |
|
42 VALUES(1, 'libhello:runtime', 1, 1, 1126929880.094, 1, 1); |
|
43 INSERT INTO "Instances" |
|
44 VALUES(2, 'libhello:user', 1, 1, 1126929880.094, 1, 0); |
|
45 INSERT INTO "Instances" |
|
46 VALUES(3, 'libhello:script', 1, 1, 1126929880.094, 1, 0); |
|
47 INSERT INTO "Instances" |
|
48 VALUES(4, 'libhello', 1, 1, 1126929880.094, 1, 0); |
|
49 |
|
50 CREATE TABLE Versions(versionId INTEGER PRIMARY KEY,version STR UNIQUE); |
|
51 INSERT INTO "Versions" VALUES(0, NULL); |
|
52 INSERT INTO "Versions" VALUES(1, '/localhost@rpl:linux/0-1-1'); |
|
53 |
|
54 CREATE TABLE Flavors(flavorId integer primary key, flavor str unique); |
|
55 INSERT INTO "Flavors" VALUES(0, NULL); |
|
56 INSERT INTO "Flavors" VALUES(1, '1#x86'); |
|
57 |
|
58 CREATE TEMPORARY TABLE tlList ( |
|
59 row INTEGER PRIMARY KEY, |
|
60 name STRING, |
|
61 version STRING, |
|
62 flavor STRING |
|
63 ); |
|
64 |
|
65 INSERT INTO tlList |
|
66 values(NULL, 'libhello:script', '/localhost@rpl:linux/0-1-1', '1#x86'); |
|
67 INSERT INTO tlList |
|
68 values(NULL, 'libhello:user', '/localhost@rpl:linux/0-1-1', '1#x86'); |
|
69 INSERT INTO tlList |
|
70 values(NULL, 'libhello:runtime', '/localhost@rpl:linux/0-1-1', '1#x86'); |
|
71 } |
|
72 } {} |
|
73 |
|
74 # Run the query with an index |
|
75 # |
|
76 do_test tkt1435-1.1 { |
|
77 execsql { |
|
78 select row, pinned from tlList, Instances, Versions, Flavors |
|
79 where |
|
80 Instances.troveName = tlList.name |
|
81 and Versions.version = tlList.version |
|
82 and Instances.versionId = Versions.versionId |
|
83 and ( Flavors.flavor = tlList.flavor or Flavors.flavor is NULL |
|
84 and tlList.flavor = '') |
|
85 and Instances.flavorId = Flavors.flavorId |
|
86 order by row asc; |
|
87 } |
|
88 } {1 0 2 0 3 1} |
|
89 |
|
90 # Create a indices, analyze and rerun the query. |
|
91 # Verify that the results are the same |
|
92 # |
|
93 do_test tkt1435-1.2 { |
|
94 execsql { |
|
95 CREATE INDEX InstancesNameIdx ON Instances(troveName); |
|
96 CREATE UNIQUE INDEX InstancesIdx |
|
97 ON Instances(troveName, versionId, flavorId); |
|
98 ANALYZE; |
|
99 select row, pinned from tlList, Instances, Versions, Flavors |
|
100 where |
|
101 Instances.troveName = tlList.name |
|
102 and Versions.version = tlList.version |
|
103 and Instances.versionId = Versions.versionId |
|
104 and ( Flavors.flavor = tlList.flavor or Flavors.flavor is NULL |
|
105 and tlList.flavor = '') |
|
106 and Instances.flavorId = Flavors.flavorId |
|
107 order by row asc; |
|
108 } |
|
109 } {1 0 2 0 3 1} |
|
110 |
|
111 finish_test |