wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
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 #1433 has been
14 # fixed.
15 #
16 # The problem in ticket #1433 was that the dependencies on the right-hand
17 # side of an IN operator were not being checked correctly. So in an
18 # expression of the form:
19 #
20 # t1.x IN (1,t2.b,3)
21 #
22 # the optimizer was missing the fact that the right-hand side of the IN
23 # depended on table t2. It was checking dependencies based on the
24 # Expr.pRight field rather than Expr.pList and Expr.pSelect.
25 #
26 # Such a bug could be verifed using a less elaborate test case. But
27 # this test case (from the original bug poster) exercises so many different
28 # parts of the system all at once, that it seemed like a good one to
29 # include in the test suite.
30 #
31 # NOTE: Yes, in spite of the name of this file (tkt1443.test) this
32 # test is for ticket #1433 not #1443. I mistyped the name when I was
33 # creating the file and I had already checked in the file by the wrong
34 # name be the time I noticed the error. With CVS it is a really hassle
35 # to change filenames, so I'll just leave it as is. No harm done.
36 #
37 # $Id: tkt1443.test,v 1.4 2006/01/17 09:35:02 danielk1977 Exp $
38  
39 set testdir [file dirname $argv0]
40 source $testdir/tester.tcl
41  
42 ifcapable !subquery||!memorydb {
43 finish_test
44 return
45 }
46  
47 # Construct the sample database.
48 #
49 do_test tkt1443-1.0 {
50 sqlite3 db :memory:
51 execsql {
52 CREATE TABLE Items(
53 itemId integer primary key,
54 item str unique
55 );
56 INSERT INTO "Items" VALUES(0, 'ALL');
57 INSERT INTO "Items" VALUES(1, 'double:source');
58 INSERT INTO "Items" VALUES(2, 'double');
59 INSERT INTO "Items" VALUES(3, 'double:runtime');
60 INSERT INTO "Items" VALUES(4, '.*:runtime');
61  
62 CREATE TABLE Labels(
63 labelId INTEGER PRIMARY KEY,
64 label STR UNIQUE
65 );
66 INSERT INTO "Labels" VALUES(0, 'ALL');
67 INSERT INTO "Labels" VALUES(1, 'localhost@rpl:linux');
68 INSERT INTO "Labels" VALUES(2, 'localhost@rpl:branch');
69  
70 CREATE TABLE LabelMap(
71 itemId INTEGER,
72 labelId INTEGER,
73 branchId integer
74 );
75 INSERT INTO "LabelMap" VALUES(1, 1, 1);
76 INSERT INTO "LabelMap" VALUES(2, 1, 1);
77 INSERT INTO "LabelMap" VALUES(3, 1, 1);
78 INSERT INTO "LabelMap" VALUES(1, 2, 2);
79 INSERT INTO "LabelMap" VALUES(2, 2, 3);
80 INSERT INTO "LabelMap" VALUES(3, 2, 3);
81  
82 CREATE TABLE Users (
83 userId INTEGER PRIMARY KEY,
84 user STRING UNIQUE,
85 salt BINARY,
86 password STRING
87 );
88 INSERT INTO "Users" VALUES(1, 'test', 'Šæ$d',
89 '43ba0f45014306bd6df529551ffdb3df');
90 INSERT INTO "Users" VALUES(2, 'limited', 'ªš>S',
91 'cf07c8348fdf675cc1f7696b7d45191b');
92 CREATE TABLE UserGroups (
93 userGroupId INTEGER PRIMARY KEY,
94 userGroup STRING UNIQUE
95 );
96 INSERT INTO "UserGroups" VALUES(1, 'test');
97 INSERT INTO "UserGroups" VALUES(2, 'limited');
98  
99 CREATE TABLE UserGroupMembers (
100 userGroupId INTEGER,
101 userId INTEGER
102 );
103 INSERT INTO "UserGroupMembers" VALUES(1, 1);
104 INSERT INTO "UserGroupMembers" VALUES(2, 2);
105  
106 CREATE TABLE Permissions (
107 userGroupId INTEGER,
108 labelId INTEGER NOT NULL,
109 itemId INTEGER NOT NULL,
110 write INTEGER,
111 capped INTEGER,
112 admin INTEGER
113 );
114 INSERT INTO "Permissions" VALUES(1, 0, 0, 1, 0, 1);
115 INSERT INTO "Permissions" VALUES(2, 2, 4, 0, 0, 0);
116 }
117 } {}
118  
119 # Run the query with an index
120 #
121 do_test tkt1443-1.1 {
122 execsql {
123 select distinct
124 Items.Item as trove, UP.pattern as pattern
125 from
126 ( select
127 Permissions.labelId as labelId,
128 PerItems.item as pattern
129 from
130 Users, UserGroupMembers, Permissions
131 left outer join Items as PerItems
132 on Permissions.itemId = PerItems.itemId
133 where
134 Users.user = 'limited'
135 and Users.userId = UserGroupMembers.userId
136 and UserGroupMembers.userGroupId = Permissions.userGroupId
137 ) as UP join LabelMap on ( UP.labelId = 0 or
138 UP.labelId = LabelMap.labelId ),
139 Labels, Items
140 where
141 Labels.label = 'localhost@rpl:branch'
142 and Labels.labelId = LabelMap.labelId
143 and LabelMap.itemId = Items.itemId
144 ORDER BY +trove, +pattern
145 }
146 } {double .*:runtime double:runtime .*:runtime double:source .*:runtime}
147  
148 # Create an index and rerun the query.
149 # Verify that the results are the same
150 #
151 do_test tkt1443-1.2 {
152 execsql {
153 CREATE UNIQUE INDEX PermissionsIdx
154 ON Permissions(userGroupId, labelId, itemId);
155 select distinct
156 Items.Item as trove, UP.pattern as pattern
157 from
158 ( select
159 Permissions.labelId as labelId,
160 PerItems.item as pattern
161 from
162 Users, UserGroupMembers, Permissions
163 left outer join Items as PerItems
164 on Permissions.itemId = PerItems.itemId
165 where
166 Users.user = 'limited'
167 and Users.userId = UserGroupMembers.userId
168 and UserGroupMembers.userGroupId = Permissions.userGroupId
169 ) as UP join LabelMap on ( UP.labelId = 0 or
170 UP.labelId = LabelMap.labelId ),
171 Labels, Items
172 where
173 Labels.label = 'localhost@rpl:branch'
174 and Labels.labelId = LabelMap.labelId
175 and LabelMap.itemId = Items.itemId
176 ORDER BY +trove, +pattern
177 }
178 } {double .*:runtime double:runtime .*:runtime double:source .*:runtime}
179  
180 finish_test