wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 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 |