wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 # 2008 December 8
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 is a verification that the bugs identified in ticket
14 # #3527 have been fixed.
15 #
16 # $Id: tkt3527.test,v 1.1 2008/12/08 13:42:36 drh Exp $
17  
18 set testdir [file dirname $argv0]
19 source $testdir/tester.tcl
20  
21 do_test tkt3527-1.1 {
22 db eval {
23 CREATE TABLE Element (
24 Code INTEGER PRIMARY KEY,
25 Name VARCHAR(60)
26 );
27  
28 CREATE TABLE ElemOr (
29 CodeOr INTEGER NOT NULL,
30 Code INTEGER NOT NULL,
31 PRIMARY KEY(CodeOr,Code)
32 );
33  
34 CREATE TABLE ElemAnd (
35 CodeAnd INTEGER,
36 Code INTEGER,
37 Attr1 INTEGER,
38 Attr2 INTEGER,
39 Attr3 INTEGER,
40 PRIMARY KEY(CodeAnd,Code)
41 );
42  
43 INSERT INTO Element VALUES(1,'Elem1');
44 INSERT INTO Element VALUES(2,'Elem2');
45 INSERT INTO Element VALUES(3,'Elem3');
46 INSERT INTO Element VALUES(4,'Elem4');
47 INSERT INTO Element VALUES(5,'Elem5');
48 INSERT INTO ElemOr Values(3,4);
49 INSERT INTO ElemOr Values(3,5);
50 INSERT INTO ElemAnd VALUES(1,3,1,1,1);
51 INSERT INTO ElemAnd VALUES(1,2,1,1,1);
52  
53 CREATE VIEW ElemView1 AS
54 SELECT
55 CAST(Element.Code AS VARCHAR(50)) AS ElemId,
56 Element.Code AS ElemCode,
57 Element.Name AS ElemName,
58 ElemAnd.Code AS InnerCode,
59 ElemAnd.Attr1 AS Attr1,
60 ElemAnd.Attr2 AS Attr2,
61 ElemAnd.Attr3 AS Attr3,
62  
63  
64 FROM Element JOIN ElemAnd ON ElemAnd.CodeAnd=Element.Code
65 WHERE ElemAnd.CodeAnd NOT IN (SELECT CodeOr FROM ElemOr)
66 UNION ALL
67 SELECT
68 CAST(ElemOr.CodeOr AS VARCHAR(50)) AS ElemId,
69 Element.Code AS ElemCode,
70 Element.Name AS ElemName,
71 ElemOr.Code AS InnerCode,
72 NULL AS Attr1,
73 NULL AS Attr2,
74 NULL AS Attr3,
75  
76 1 AS IsOrElem
77 FROM ElemOr JOIN Element ON Element.Code=ElemOr.CodeOr
78 ORDER BY ElemId, InnerCode;
79  
80 CREATE VIEW ElemView2 AS
81 SELECT
82 ElemId,
83 ElemCode,
84 ElemName,
85 InnerCode,
86 Attr1,
87 Attr2,
88 Attr3,
89 Level,
90 IsOrElem
91 FROM ElemView1
92 UNION ALL
93 SELECT
94 Element.ElemId || '.' || InnerElem.ElemId AS ElemId,
95 InnerElem.ElemCode,
96 InnerElem.ElemName,
97 InnerElem.InnerCode,
98 InnerElem.Attr1,
99 InnerElem.Attr2,
100 InnerElem.Attr3,
101 InnerElem.Level+1,
102 InnerElem.IsOrElem
103 FROM ElemView1 AS Element
104 JOIN ElemView1 AS InnerElem
105 ON Element.Level=0 AND Element.InnerCode=InnerElem.ElemCode
106 ORDER BY ElemId, InnerCode;
107  
108 SELECT * FROM ElemView1;
109 }
110 } {1 1 Elem1 2 1 1 1 0 0 1 1 Elem1 3 1 1 1 0 0 3 3 Elem3 4 {} {} {} 0 1 3 3 Elem3 5 {} {} {} 0 1}
111  
112 do_test tkt3527-1.2 {
113 db eval {
114 SELECT * FROM ElemView2;
115 }
116 } {1 1 Elem1 2 1 1 1 0 0 1 1 Elem1 3 1 1 1 0 0 1.3 3 Elem3 4 {} {} {} 1 1 1.3 3 Elem3 5 {} {} {} 1 1 3 3 Elem3 4 {} {} {} 0 1 3 3 Elem3 5 {} {} {} 0 1}
117  
118 finish_test