Chromium Code Reviews
chromiumcodereview-hr@appspot.gserviceaccount.com (chromiumcodereview-hr) | Please choose your nickname with Settings | Help | Chromium Project | Gerrit Changes | Sign out
(65)

Issues Search
    My Issues | Starred     Open | Closed | All

Side by Side Diff: third_party/sqlite/sqlite-src-3080704/test/transitive1.test

Issue 883353008: [sql] Import reference version of SQLite 3.8.7.4. (Closed) Base URL: http://chromium.googlesource.com/chromium/src.git@master
Patch Set: Hold back encoding change which is messing up patch. Created 5 years, 10 months ago
Use n/p to move between diff chunks; N/P to move between comments. Draft comments are only viewable by you.
Jump to:
View unified diff | Download patch
« no previous file with comments | « third_party/sqlite/sqlite-src-3080704/test/trans3.test ('k') | third_party/sqlite/sqlite-src-3080704/test/trigger1.test » ('j') | no next file with comments »
Toggle Intra-line Diffs ('i') | Expand Comments ('e') | Collapse Comments ('c') | Show Comments Hide Comments ('s')
OLDNEW
(Empty)
1 # 2013 April 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. The
12 # focus of this script is testing of transitive WHERE clause constraints
13 #
14
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17
18 do_execsql_test transitive1-100 {
19 CREATE TABLE t1(a TEXT, b TEXT, c TEXT COLLATE NOCASE);
20 INSERT INTO t1 VALUES('abc','abc','Abc');
21 INSERT INTO t1 VALUES('def','def','def');
22 INSERT INTO t1 VALUES('ghi','ghi','GHI');
23 CREATE INDEX t1a1 ON t1(a);
24 CREATE INDEX t1a2 ON t1(a COLLATE nocase);
25
26 SELECT * FROM t1 WHERE a=b AND c=b AND c='DEF';
27 } {def def def}
28 do_execsql_test transitive1-110 {
29 SELECT * FROM t1 WHERE a=b AND c=b AND c>='DEF' ORDER BY +a;
30 } {def def def ghi ghi GHI}
31 do_execsql_test transitive1-120 {
32 SELECT * FROM t1 WHERE a=b AND c=b AND c<='DEF' ORDER BY +a;
33 } {abc abc Abc def def def}
34
35 do_execsql_test transitive1-200 {
36 CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT);
37 INSERT INTO t2 VALUES(100,100,100);
38 INSERT INTO t2 VALUES(20,20,20);
39 INSERT INTO t2 VALUES(3,3,3);
40
41 SELECT * FROM t2 WHERE a=b AND c=b AND c=20;
42 } {20 20 20}
43 do_execsql_test transitive1-210 {
44 SELECT * FROM t2 WHERE a=b AND c=b AND c>=20 ORDER BY +a;
45 } {3 3 3 20 20 20}
46 do_execsql_test transitive1-220 {
47 SELECT * FROM t2 WHERE a=b AND c=b AND c<=20 ORDER BY +a;
48 } {20 20 20 100 100 100}
49
50 # Test cases for ticket [[d805526eae253103] 2013-07-08
51 # "Incorrect join result or assertion fault due to transitive constraints"
52 #
53 do_execsql_test transitive1-300 {
54 CREATE TABLE t301(w INTEGER PRIMARY KEY, x);
55 CREATE TABLE t302(y INTEGER UNIQUE, z);
56 INSERT INTO t301 VALUES(1,2),(3,4),(5,6);
57 INSERT INTO t302 VALUES(1,3),(3,6),(5,7);
58 SELECT *
59 FROM t301 CROSS JOIN t302
60 WHERE w=y AND y IS NOT NULL
61 ORDER BY +w;
62 } {1 2 1 3 3 4 3 6 5 6 5 7}
63 do_execsql_test transitive1-301 {
64 SELECT *
65 FROM t301 CROSS JOIN t302
66 WHERE w=y AND y IS NOT NULL
67 ORDER BY w;
68 } {1 2 1 3 3 4 3 6 5 6 5 7}
69 do_execsql_test transitive1-310 {
70 SELECT *
71 FROM t301 CROSS JOIN t302 ON w=y
72 WHERE y>1
73 ORDER BY +w
74 } {3 4 3 6 5 6 5 7}
75 do_execsql_test transitive1-311 {
76 SELECT *
77 FROM t301 CROSS JOIN t302 ON w=y
78 WHERE y>1
79 ORDER BY w
80 } {3 4 3 6 5 6 5 7}
81 do_execsql_test transitive1-312 {
82 SELECT *
83 FROM t301 CROSS JOIN t302 ON w=y
84 WHERE y>1
85 ORDER BY w DESC
86 } {5 6 5 7 3 4 3 6}
87 do_execsql_test transitive1-320 {
88 SELECT *
89 FROM t301 CROSS JOIN t302 ON w=y
90 WHERE y BETWEEN 2 AND 4;
91 } {3 4 3 6}
92 do_execsql_test transitive1-331 {
93 SELECT *
94 FROM t301 CROSS JOIN t302 ON w=y
95 WHERE y BETWEEN 1 AND 4
96 ORDER BY w;
97 } {1 2 1 3 3 4 3 6}
98 do_execsql_test transitive1-332 {
99 SELECT *
100 FROM t301 CROSS JOIN t302 ON w=y
101 WHERE y BETWEEN 1 AND 4
102 ORDER BY w DESC;
103 } {3 4 3 6 1 2 1 3}
104
105 # Ticket [c620261b5b5dc] circa 2013-10-28.
106 # Make sureconstraints are not used with LEFT JOINs.
107 #
108 # The next case is from the ticket report. It outputs no rows in 3.8.1
109 # prior to the bug-fix.
110 #
111 do_execsql_test transitive1-400 {
112 CREATE TABLE t401(a);
113 CREATE TABLE t402(b);
114 CREATE TABLE t403(c INTEGER PRIMARY KEY);
115 INSERT INTO t401 VALUES(1);
116 INSERT INTO t403 VALUES(1);
117 SELECT '1-row' FROM t401 LEFT JOIN t402 ON b=a JOIN t403 ON c=a;
118 } {1-row}
119
120 # The following is a script distilled from the XBMC project where the
121 # bug was originally encountered. The correct answer is a single row
122 # of output. Before the bug was fixed, zero rows were generated.
123 #
124 do_execsql_test transitive1-410 {
125 CREATE TABLE bookmark ( idBookmark integer primary key, idFile integer, timeIn Seconds double, totalTimeInSeconds double, thumbNailImage text, player text, pla yerState text, type integer);
126 CREATE TABLE path ( idPath integer primary key, strPath text, strContent text, strScraper text, strHash text, scanRecursive integer, useFolderNames bool, strS ettings text, noUpdate bool, exclude bool, dateAdded text);
127 INSERT INTO "path" VALUES(1,'/tmp/tvshows/','tvshows','metadata.tvdb.com','989 B1CE5680A14F5F86123F751169B49',0,0,'<settings><setting id="absolutenumber" value ="false" /><setting id="dvdorder" value="false" /><setting id="fanart" value="tr ue" /><setting id="language" value="en" /></settings>',0,0,NULL);
128 INSERT INTO "path" VALUES(2,'/tmp/tvshows/The.Big.Bang.Theory/','','','85E1DAA B2F5FF6EAE8AEDF1B5C882D1E',NULL,NULL,NULL,NULL,NULL,'2013-10-23 18:58:43');
129 CREATE TABLE files ( idFile integer primary key, idPath integer, strFilename t ext, playCount integer, lastPlayed text, dateAdded text);
130 INSERT INTO "files" VALUES(1,2,'The.Big.Bang.Theory.S01E01.WEB-DL.AAC2.0.H264. mkv',NULL,NULL,'2013-10-23 18:57:36');
131 CREATE TABLE tvshow ( idShow integer primary key,c00 text,c01 text,c02 text,c0 3 text,c04 text,c05 text,c06 text,c07 text,c08 text,c09 text,c10 text,c11 text,c 12 text,c13 text,c14 text,c15 text,c16 text,c17 text,c18 text,c19 text,c20 text, c21 text,c22 text,c23 text);
132 INSERT INTO "tvshow" VALUES(1,'The Big Bang Theory','Leonard Hofstadter and Sh eldon Cooper are brilliant physicists, the kind of "beautiful minds" that unders tand how the universe works. But none of that genius helps them interact with pe ople, especially women. All this begins to change when a free-spirited beauty na med Penny moves in next door. Sheldon, Leonard''s roommate, is quite content spe nding his nights playing Klingon Boggle with their socially dysfunctional friend s, fellow CalTech scientists Howard Wolowitz and Raj Koothrappali. However, Leon ard sees in Penny a whole new universe of possibilities... including love.','',' ','9.200000','2007-09-24','<thumb aspect="banner">http://thetvdb.com/banners/gra phical/80379-g13.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/gr aphical/80379-g23.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/g raphical/80379-g18.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/ graphical/80379-g17.jpg</thumb><thumb aspect="banner">http://
133 thetvdb.com/banners/graphical/80379-g6.jpg</thumb><thumb aspect="banner">http: //thetvdb.com/banners/graphical/80379-g5.jpg</thumb><thumb aspect="banner">http: //thetvdb.com/banners/graphical/80379-g2.jpg</thumb><thumb aspect="banner">http: //thetvdb.com/banners/graphical/80379-g11.jpg</thumb><thumb aspect="banner">http ://thetvdb.com/banners/graphical/80379-g12.jpg</thumb><thumb aspect="banner">htt p://thetvdb.com/banners/graphical/80379-g19.jpg</thumb><thumb aspect="banner">ht tp://thetvdb.com/banners/graphical/80379-g3.jpg</thumb><thumb aspect="banner">ht tp://thetvdb.com/banners/graphical/80379-g4.jpg</thumb><thumb aspect="banner">ht tp://thetvdb.com/banners/graphical/80379-g15.jpg</thumb><thumb aspect="banner">h ttp://thetvdb.com/banners/graphical/80379-g.jpg</thumb><thumb aspect="banner">ht tp://thetvdb.com/banners/graphical/80379-g22.jpg</thumb><thumb aspect="banner">h ttp://thetvdb.com/banners/graphical/80379-g7.jpg</thumb><thumb aspect="banner">h ttp://thetvdb.com/banners/graphical/80379-g10.jpg</thumb><thumb
134 aspect="banner">http://thetvdb.com/banners/graphical/80379-g24.jpg</thumb><thu mb aspect="banner">http://thetvdb.com/banners/graphical/80379-g8.jpg</thumb><thu mb aspect="banner">http://thetvdb.com/banners/graphical/80379-g9.jpg</thumb><thu mb aspect="banner">http://thetvdb.com/banners/graphical/80379-g14.jpg</thumb><th umb aspect="banner">http://thetvdb.com/banners/graphical/80379-g16.jpg</thumb><t humb aspect="banner">http://thetvdb.com/banners/graphical/80379-g21.jpg</thumb>< thumb aspect="banner">http://thetvdb.com/banners/text/80379-4.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/text/80379-2.jpg</thumb><thumb aspect ="banner">http://thetvdb.com/banners/text/80379-3.jpg</thumb><thumb aspect="bann er">http://thetvdb.com/banners/text/80379-5.jpg</thumb><thumb aspect="poster" ty pe="season" season="6">http://thetvdb.com/banners/seasons/80379-6-8.jpg</thumb>< thumb aspect="poster" type="season" season="0">http://thetvdb.com/banners/season s/80379-0-4.jpg</thumb><thumb aspect="poster" type="season"
135 season="1">http://thetvdb.com/banners/seasons/80379-1-12.jpg</thumb><thumb asp ect="poster" type="season" season="3">http://thetvdb.com/banners/seasons/80379-3 -9.jpg</thumb><thumb aspect="poster" type="season" season="2">http://thetvdb.com /banners/seasons/80379-2-11.jpg</thumb><thumb aspect="poster" type="season" seas on="5">http://thetvdb.com/banners/seasons/80379-5-9.jpg</thumb><thumb aspect="po ster" type="season" season="4">http://thetvdb.com/banners/seasons/80379-4-8.jpg< /thumb><thumb aspect="poster" type="season" season="7">http://thetvdb.com/banner s/seasons/80379-7-3.jpg</thumb><thumb aspect="poster" type="season" season="3">h ttp://thetvdb.com/banners/seasons/80379-3-4.jpg</thumb><thumb aspect="poster" ty pe="season" season="4">http://thetvdb.com/banners/seasons/80379-4-5.jpg</thumb>< thumb aspect="poster" type="season" season="2">http://thetvdb.com/banners/season s/80379-2-9.jpg</thumb><thumb aspect="poster" type="season" season="0">http://th etvdb.com/banners/seasons/80379-0-2.jpg</thumb><thumb aspect="
136 poster" type="season" season="6">http://thetvdb.com/banners/seasons/80379-6-6. jpg</thumb><thumb aspect="poster" type="season" season="4">http://thetvdb.com/ba nners/seasons/80379-4-4.jpg</thumb><thumb aspect="poster" type="season" season=" 6">http://thetvdb.com/banners/seasons/80379-6-2.jpg</thumb><thumb aspect="poster " type="season" season="1">http://thetvdb.com/banners/seasons/80379-1-9.jpg</thu mb><thumb aspect="poster" type="season" season="6">http://thetvdb.com/banners/se asons/80379-6-4.jpg</thumb><thumb aspect="poster" type="season" season="1">http: //thetvdb.com/banners/seasons/80379-1.jpg</thumb><thumb aspect="poster" type="se ason" season="3">http://thetvdb.com/banners/seasons/80379-3.jpg</thumb><thumb as pect="poster" type="season" season="4">http://thetvdb.com/banners/seasons/80379- 4-2.jpg</thumb><thumb aspect="poster" type="season" season="5">http://thetvdb.co m/banners/seasons/80379-5-7.jpg</thumb><thumb aspect="poster" type="season" seas on="2">http://thetvdb.com/banners/seasons/80379-2-10.jpg</
137 thumb><thumb aspect="poster" type="season" season="6">http://thetvdb.com/banne rs/seasons/80379-6-5.jpg</thumb><thumb aspect="poster" type="season" season="1"> http://thetvdb.com/banners/seasons/80379-1-5.jpg</thumb><thumb aspect="poster" t ype="season" season="5">http://thetvdb.com/banners/seasons/80379-5-4.jpg</thumb> <thumb aspect="poster" type="season" season="4">http://thetvdb.com/banners/seaso ns/80379-4.jpg</thumb><thumb aspect="poster" type="season" season="6">http://the tvdb.com/banners/seasons/80379-6-3.jpg</thumb><thumb aspect="poster" type="seaso n" season="5">http://thetvdb.com/banners/seasons/80379-5.jpg</thumb><thumb aspec t="poster" type="season" season="3">http://thetvdb.com/banners/seasons/80379-3-6 .jpg</thumb><thumb aspect="poster" type="season" season="2">http://thetvdb.com/b anners/seasons/80379-2.jpg</thumb><thumb aspect="poster" type="season" season="7 ">http://thetvdb.com/banners/seasons/80379-7.jpg</thumb><thumb aspect="poster" t ype="season" season="1">http://thetvdb.com/banners/seasons/80379-
138 1-7.jpg</thumb><thumb aspect="poster" type="season" season="5">http://thetvdb. com/banners/seasons/80379-5-2.jpg</thumb><thumb aspect="poster" type="season" se ason="5">http://thetvdb.com/banners/seasons/80379-5-3.jpg</thumb><thumb aspect=" poster" type="season" season="7">http://thetvdb.com/banners/seasons/80379-7-2.jp g</thumb><thumb aspect="poster" type="season" season="1">http://thetvdb.com/bann ers/seasons/80379-1-2.jpg</thumb><thumb aspect="poster" type="season" season="2" >http://thetvdb.com/banners/seasons/80379-2-5.jpg</thumb><thumb aspect="poster" type="season" season="4">http://thetvdb.com/banners/seasons/80379-4-3.jpg</thumb ><thumb aspect="poster" type="season" season="5">http://thetvdb.com/banners/seas ons/80379-5-5.jpg</thumb><thumb aspect="poster" type="season" season="0">http:// thetvdb.com/banners/seasons/80379-0.jpg</thumb><thumb aspect="poster" type="seas on" season="3">http://thetvdb.com/banners/seasons/80379-3-5.jpg</thumb><thumb as pect="poster" type="season" season="1">http://thetvdb.com/banners/
139 seasons/80379-1-6.jpg</thumb><thumb aspect="poster" type="season" season="2">h ttp://thetvdb.com/banners/seasons/80379-2-3.jpg</thumb><thumb aspect="poster" ty pe="season" season="2">http://thetvdb.com/banners/seasons/80379-2-8.jpg</thumb>< thumb aspect="poster" type="season" season="6">http://thetvdb.com/banners/season s/80379-6-7.jpg</thumb><thumb aspect="poster" type="season" season="5">http://th etvdb.com/banners/seasons/80379-5-8.jpg</thumb><thumb aspect="poster" type="seas on" season="4">http://thetvdb.com/banners/seasons/80379-4-7.jpg</thumb><thumb as pect="poster" type="season" season="2">http://thetvdb.com/banners/seasons/80379- 2-6.jpg</thumb><thumb aspect="poster" type="season" season="3">http://thetvdb.co m/banners/seasons/80379-3-8.jpg</thumb><thumb aspect="poster" type="season" seas on="1">http://thetvdb.com/banners/seasons/80379-1-11.jpg</thumb><thumb aspect="p oster" type="season" season="1">http://thetvdb.com/banners/seasons/80379-1-10.jp g</thumb><thumb aspect="poster" type="season" season="1">http://
140 thetvdb.com/banners/seasons/80379-1-8.jpg</thumb><thumb aspect="poster" type=" season" season="3">http://thetvdb.com/banners/seasons/80379-3-7.jpg</thumb><thum b aspect="poster" type="season" season="2">http://thetvdb.com/banners/seasons/80 379-2-4.jpg</thumb><thumb aspect="poster" type="season" season="1">http://thetvd b.com/banners/seasons/80379-1-3.jpg</thumb><thumb aspect="poster" type="season" season="1">http://thetvdb.com/banners/seasons/80379-1-4.jpg</thumb><thumb aspect ="poster" type="season" season="3">http://thetvdb.com/banners/seasons/80379-3-3. jpg</thumb><thumb aspect="poster" type="season" season="2">http://thetvdb.com/ba nners/seasons/80379-2-7.jpg</thumb><thumb aspect="poster" type="season" season=" 6">http://thetvdb.com/banners/seasons/80379-6.jpg</thumb><thumb aspect="poster" type="season" season="2">http://thetvdb.com/banners/seasons/80379-2-2.jpg</thumb ><thumb aspect="poster" type="season" season="5">http://thetvdb.com/banners/seas ons/80379-5-6.jpg</thumb><thumb aspect="poster" type="season"
141 season="3">http://thetvdb.com/banners/seasons/80379-3-2.jpg</thumb><thumb aspe ct="poster" type="season" season="4">http://thetvdb.com/banners/seasons/80379-4- 6.jpg</thumb><thumb aspect="banner" type="season" season="5">http://thetvdb.com/ banners/seasonswide/80379-5.jpg</thumb><thumb aspect="banner" type="season" seas on="3">http://thetvdb.com/banners/seasonswide/80379-3-2.jpg</thumb><thumb aspect ="banner" type="season" season="1">http://thetvdb.com/banners/seasonswide/80379- 1-2.jpg</thumb><thumb aspect="banner" type="season" season="2">http://thetvdb.co m/banners/seasonswide/80379-2-2.jpg</thumb><thumb aspect="banner" type="season" season="4">http://thetvdb.com/banners/seasonswide/80379-4-2.jpg</thumb><thumb as pect="banner" type="season" season="0">http://thetvdb.com/banners/seasonswide/80 379-0.jpg</thumb><thumb aspect="banner" type="season" season="0">http://thetvdb. com/banners/seasonswide/80379-0-2.jpg</thumb><thumb aspect="banner" type="season " season="1">http://thetvdb.com/banners/seasonswide/80379-1.jpg</
142 thumb><thumb aspect="banner" type="season" season="2">http://thetvdb.com/banne rs/seasonswide/80379-2.jpg</thumb><thumb aspect="banner" type="season" season="4 ">http://thetvdb.com/banners/seasonswide/80379-4.jpg</thumb><thumb aspect="banne r" type="season" season="3">http://thetvdb.com/banners/seasonswide/80379-3.jpg</ thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-22.jpg</th umb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-18.jpg</thum b><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-13.jpg</thumb> <thumb aspect="poster">http://thetvdb.com/banners/posters/80379-10.jpg</thumb><t humb aspect="poster">http://thetvdb.com/banners/posters/80379-16.jpg</thumb><thu mb aspect="poster">http://thetvdb.com/banners/posters/80379-1.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-9.jpg</thumb><thumb asp ect="poster">http://thetvdb.com/banners/posters/80379-2.jpg</thumb><thumb aspect ="poster">http://thetvdb.com/banners/posters/80379-19.jpg</
143 thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-8.jpg</t humb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-4.jpg</thum b><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-20.jpg</thumb> <thumb aspect="poster">http://thetvdb.com/banners/posters/80379-23.jpg</thumb><t humb aspect="poster">http://thetvdb.com/banners/posters/80379-7.jpg</thumb><thum b aspect="poster">http://thetvdb.com/banners/posters/80379-3.jpg</thumb><thumb a spect="poster">http://thetvdb.com/banners/posters/80379-12.jpg</thumb><thumb asp ect="poster">http://thetvdb.com/banners/posters/80379-11.jpg</thumb><thumb aspec t="poster">http://thetvdb.com/banners/posters/80379-15.jpg</thumb><thumb aspect= "poster">http://thetvdb.com/banners/posters/80379-21.jpg</thumb><thumb aspect="p oster">http://thetvdb.com/banners/posters/80379-14.jpg</thumb><thumb aspect="pos ter">http://thetvdb.com/banners/posters/80379-17.jpg</thumb><thumb aspect="poste r">http://thetvdb.com/banners/posters/80379-6.jpg</thumb><thumb
144 aspect="poster">http://thetvdb.com/banners/posters/80379-5.jpg</thumb><thumb a spect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/8037 9-22.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb. com/banners/posters/80379-18.jpg</thumb><thumb aspect="poster" type="season" sea son="-1">http://thetvdb.com/banners/posters/80379-13.jpg</thumb><thumb aspect="p oster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-10.jpg </thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/bann ers/posters/80379-16.jpg</thumb><thumb aspect="poster" type="season" season="-1" >http://thetvdb.com/banners/posters/80379-1.jpg</thumb><thumb aspect="poster" ty pe="season" season="-1">http://thetvdb.com/banners/posters/80379-9.jpg</thumb><t humb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/poster s/80379-2.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://the tvdb.com/banners/posters/80379-19.jpg</thumb><thumb aspect="
145 poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-8.j pg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/ba nners/posters/80379-4.jpg</thumb><thumb aspect="poster" type="season" season="-1 ">http://thetvdb.com/banners/posters/80379-20.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-23.jpg</thumb ><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/pos ters/80379-7.jpg</thumb><thumb aspect="poster" type="season" season="-1">http:// thetvdb.com/banners/posters/80379-3.jpg</thumb><thumb aspect="poster" type="seas on" season="-1">http://thetvdb.com/banners/posters/80379-12.jpg</thumb><thumb as pect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379 -11.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.c om/banners/posters/80379-15.jpg</thumb><thumb aspect="poster" type="season" seas on="-1">http://thetvdb.com/banners/posters/80379-21.jpg</
146 thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/bann ers/posters/80379-14.jpg</thumb><thumb aspect="poster" type="season" season="-1" >http://thetvdb.com/banners/posters/80379-17.jpg</thumb><thumb aspect="poster" t ype="season" season="-1">http://thetvdb.com/banners/posters/80379-6.jpg</thumb>< thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/poste rs/80379-5.jpg</thumb>','','Comedy','','<episodeguide><url cache="80379-en.xml"> http://thetvdb.com/api/1D62F2F90030C444/series/80379/all/en.zip</url></episodegu ide>','<fanart url="http://thetvdb.com/banners/"><thumb dim="1920x1080" colors=" |192,185,169|19,20,25|57,70,89|" preview="_cache/fanart/original/80379-2.jpg">fa nart/original/80379-2.jpg</thumb><thumb dim="1920x1080" colors="|94,28,16|194,18 ,38|0,0,8|" preview="_cache/fanart/original/80379-34.jpg">fanart/original/80379- 34.jpg</thumb><thumb dim="1280x720" colors="|254,157,210|11,12,7|191,152,111|" p review="_cache/fanart/original/80379-4.jpg">fanart/original/80379-
147 4.jpg</thumb><thumb dim="1920x1080" colors="" preview="_cache/fanart/original/ 80379-42.jpg">fanart/original/80379-42.jpg</thumb><thumb dim="1920x1080" colors= "|236,187,155|136,136,128|254,254,252|" preview="_cache/fanart/original/80379-37 .jpg">fanart/original/80379-37.jpg</thumb><thumb dim="1920x1080" colors="|112,10 2,152|116,109,116|235,152,146|" preview="_cache/fanart/original/80379-14.jpg">fa nart/original/80379-14.jpg</thumb><thumb dim="1920x1080" colors="|150,158,161|17 4,75,121|150,98,58|" preview="_cache/fanart/original/80379-16.jpg">fanart/origin al/80379-16.jpg</thumb><thumb dim="1280x720" colors="|224,200,176|11,1,28|164,96 ,0|" preview="_cache/fanart/original/80379-1.jpg">fanart/original/80379-1.jpg</t humb><thumb dim="1920x1080" colors="" preview="_cache/fanart/original/80379-35.j pg">fanart/original/80379-35.jpg</thumb><thumb dim="1920x1080" colors="" preview ="_cache/fanart/original/80379-40.jpg">fanart/original/80379-40.jpg</thumb><thum b dim="1920x1080" colors="|255,255,255|30,19,13|155,112,70|"
148 preview="_cache/fanart/original/80379-31.jpg">fanart/original/80379-31.jpg</th umb><thumb dim="1920x1080" colors="|241,195,172|84,54,106|254,221,206|" preview= "_cache/fanart/original/80379-29.jpg">fanart/original/80379-29.jpg</thumb><thumb dim="1280x720" colors="|197,167,175|219,29,39|244,208,192|" preview="_cache/fan art/original/80379-11.jpg">fanart/original/80379-11.jpg</thumb><thumb dim="1280x 720" colors="|195,129,97|244,192,168|219,148,118|" preview="_cache/fanart/origin al/80379-24.jpg">fanart/original/80379-24.jpg</thumb><thumb dim="1920x1080" colo rs="|14,10,11|255,255,255|175,167,164|" preview="_cache/fanart/original/80379-30 .jpg">fanart/original/80379-30.jpg</thumb><thumb dim="1920x1080" colors="" previ ew="_cache/fanart/original/80379-19.jpg">fanart/original/80379-19.jpg</thumb><th umb dim="1920x1080" colors="|246,199,69|98,55,38|161,127,82|" preview="_cache/fa nart/original/80379-9.jpg">fanart/original/80379-9.jpg</thumb><thumb dim="1280x7 20" colors="|129,22,14|48,50,39|223,182,64|" preview="_cache/
149 fanart/original/80379-13.jpg">fanart/original/80379-13.jpg</thumb><thumb dim=" 1920x1080" colors="" preview="_cache/fanart/original/80379-45.jpg">fanart/origin al/80379-45.jpg</thumb><thumb dim="1920x1080" colors="" preview="_cache/fanart/o riginal/80379-33.jpg">fanart/original/80379-33.jpg</thumb><thumb dim="1280x720" colors="|103,77,60|224,180,153|129,100,84|" preview="_cache/fanart/original/8037 9-10.jpg">fanart/original/80379-10.jpg</thumb><thumb dim="1920x1080" colors="" p review="_cache/fanart/original/80379-23.jpg">fanart/original/80379-23.jpg</thumb ><thumb dim="1280x720" colors="|219,29,39|0,4,10|88,117,135|" preview="_cache/fa nart/original/80379-12.jpg">fanart/original/80379-12.jpg</thumb><thumb dim="1920 x1080" colors="|226,209,165|51,18,9|89,54,24|" preview="_cache/fanart/original/8 0379-5.jpg">fanart/original/80379-5.jpg</thumb><thumb dim="1280x720" colors="" p review="_cache/fanart/original/80379-26.jpg">fanart/original/80379-26.jpg</thumb ><thumb dim="1280x720" colors="|249,251,229|126,47,53|251,226,
150 107|" preview="_cache/fanart/original/80379-27.jpg">fanart/original/80379-27.j pg</thumb><thumb dim="1920x1080" colors="|233,218,65|30,27,46|173,53,18|" previe w="_cache/fanart/original/80379-32.jpg">fanart/original/80379-32.jpg</thumb><thu mb dim="1280x720" colors="|248,248,248|64,54,78|188,193,196|" preview="_cache/fa nart/original/80379-3.jpg">fanart/original/80379-3.jpg</thumb><thumb dim="1280x7 20" colors="" preview="_cache/fanart/original/80379-25.jpg">fanart/original/8037 9-25.jpg</thumb><thumb dim="1280x720" colors="|159,150,133|59,39,32|168,147,104| " preview="_cache/fanart/original/80379-7.jpg">fanart/original/80379-7.jpg</thum b><thumb dim="1920x1080" colors="|221,191,157|11,7,6|237,146,102|" preview="_cac he/fanart/original/80379-21.jpg">fanart/original/80379-21.jpg</thumb><thumb dim= "1280x720" colors="" preview="_cache/fanart/original/80379-28.jpg">fanart/origin al/80379-28.jpg</thumb><thumb dim="1920x1080" colors="" preview="_cache/fanart/o riginal/80379-36.jpg">fanart/original/80379-36.jpg</thumb><thumb
151 dim="1920x1080" colors="|253,237,186|33,25,22|245,144,38|" preview="_cache/fan art/original/80379-38.jpg">fanart/original/80379-38.jpg</thumb><thumb dim="1920x 1080" colors="|174,111,68|243,115,50|252,226,45|" preview="_cache/fanart/origina l/80379-20.jpg">fanart/original/80379-20.jpg</thumb><thumb dim="1920x1080" color s="|63,56,123|87,59,47|63,56,123|" preview="_cache/fanart/original/80379-17.jpg" >fanart/original/80379-17.jpg</thumb><thumb dim="1920x1080" colors="" preview="_ cache/fanart/original/80379-43.jpg">fanart/original/80379-43.jpg</thumb><thumb d im="1280x720" colors="|69,68,161|142,118,142|222,191,137|" preview="_cache/fanar t/original/80379-22.jpg">fanart/original/80379-22.jpg</thumb><thumb dim="1280x72 0" colors="|1,108,206|242,209,192|250,197,163|" preview="_cache/fanart/original/ 80379-15.jpg">fanart/original/80379-15.jpg</thumb><thumb dim="1280x720" colors=" |239,229,237|0,0,0|167,136,115|" preview="_cache/fanart/original/80379-18.jpg">f anart/original/80379-18.jpg</thumb><thumb dim="1280x720" colors=""
152 preview="_cache/fanart/original/80379-6.jpg">fanart/original/80379-6.jpg</thum b><thumb dim="1280x720" colors="" preview="_cache/fanart/original/80379-8.jpg">f anart/original/80379-8.jpg</thumb><thumb dim="1280x720" colors="" preview="_cach e/fanart/original/80379-41.jpg">fanart/original/80379-41.jpg</thumb><thumb dim=" 1920x1080" colors="" preview="_cache/fanart/original/80379-44.jpg">fanart/origin al/80379-44.jpg</thumb><thumb dim="1280x720" colors="" preview="_cache/fanart/or iginal/80379-39.jpg">fanart/original/80379-39.jpg</thumb></fanart>','80379','TV- PG','CBS','','/tmp/tvshows/The.Big.Bang.Theory/','1',NULL,NULL,NULL,NULL,NULL,NU LL);
153 CREATE TABLE episode ( idEpisode integer primary key, idFile integer,c00 text, c01 text,c02 text,c03 text,c04 text,c05 text,c06 text,c07 text,c08 text,c09 text ,c10 text,c11 text,c12 varchar(24),c13 varchar(24),c14 text,c15 text,c16 text,c1 7 varchar(24),c18 text,c19 text,c20 text,c21 text,c22 text,c23 text, idShow inte ger);
154 INSERT INTO "episode" VALUES(1,1,'Pilot','Brilliant physicist roommates Leonar d and Sheldon meet their new neighbor Penny, who begins showing them that as muc h as they know about science, they know little about actual living.','','7.70000 0','Chuck Lorre / Bill Prady','2007-09-24','<thumb>http://thetvdb.com/banners/ep isodes/80379/332484.jpg</thumb>','',NULL,'1800','James Burrows','','1','1','','- 1','-1','-1','/tmp/tvshows/The.Big.Bang.Theory/The.Big.Bang.Theory.S01E01.WEB-DL .AAC2.0.H264.mkv','2','332484',NULL,NULL,NULL,1);
155 CREATE TABLE tvshowlinkpath (idShow integer, idPath integer);
156 INSERT INTO "tvshowlinkpath" VALUES(1,2);
157 CREATE TABLE seasons ( idSeason integer primary key, idShow integer, season in teger);
158 INSERT INTO "seasons" VALUES(1,1,-1);
159 INSERT INTO "seasons" VALUES(2,1,0);
160 INSERT INTO "seasons" VALUES(3,1,1);
161 INSERT INTO "seasons" VALUES(4,1,2);
162 INSERT INTO "seasons" VALUES(5,1,3);
163 INSERT INTO "seasons" VALUES(6,1,4);
164 INSERT INTO "seasons" VALUES(7,1,5);
165 INSERT INTO "seasons" VALUES(8,1,6);
166 INSERT INTO "seasons" VALUES(9,1,7);
167 CREATE TABLE art(art_id INTEGER PRIMARY KEY, media_id INTEGER, media_type TEXT , type TEXT, url TEXT);
168 INSERT INTO "art" VALUES(1,1,'actor','thumb','http://thetvdb.com/banners/actor s/73597.jpg');
169 INSERT INTO "art" VALUES(2,2,'actor','thumb','http://thetvdb.com/banners/actor s/73596.jpg');
170 INSERT INTO "art" VALUES(3,3,'actor','thumb','http://thetvdb.com/banners/actor s/73595.jpg');
171 INSERT INTO "art" VALUES(4,4,'actor','thumb','http://thetvdb.com/banners/actor s/73599.jpg');
172 INSERT INTO "art" VALUES(5,5,'actor','thumb','http://thetvdb.com/banners/actor s/73598.jpg');
173 INSERT INTO "art" VALUES(6,6,'actor','thumb','http://thetvdb.com/banners/actor s/283158.jpg');
174 INSERT INTO "art" VALUES(7,7,'actor','thumb','http://thetvdb.com/banners/actor s/283157.jpg');
175 INSERT INTO "art" VALUES(8,8,'actor','thumb','http://thetvdb.com/banners/actor s/91271.jpg');
176 INSERT INTO "art" VALUES(9,9,'actor','thumb','http://thetvdb.com/banners/actor s/294178.jpg');
177 INSERT INTO "art" VALUES(10,10,'actor','thumb','http://thetvdb.com/banners/act ors/283159.jpg');
178 INSERT INTO "art" VALUES(11,1,'tvshow','banner','http://thetvdb.com/banners/gr aphical/80379-g13.jpg');
179 INSERT INTO "art" VALUES(12,1,'tvshow','fanart','http://thetvdb.com/banners/fa nart/original/80379-2.jpg');
180 INSERT INTO "art" VALUES(13,1,'tvshow','poster','http://thetvdb.com/banners/po sters/80379-22.jpg');
181 INSERT INTO "art" VALUES(14,1,'season','poster','http://thetvdb.com/banners/po sters/80379-22.jpg');
182 INSERT INTO "art" VALUES(15,2,'season','banner','http://thetvdb.com/banners/se asonswide/80379-0.jpg');
183 INSERT INTO "art" VALUES(16,2,'season','poster','http://thetvdb.com/banners/se asons/80379-0-4.jpg');
184 INSERT INTO "art" VALUES(17,3,'season','banner','http://thetvdb.com/banners/se asonswide/80379-1-2.jpg');
185 INSERT INTO "art" VALUES(18,3,'season','poster','http://thetvdb.com/banners/se asons/80379-1-12.jpg');
186 INSERT INTO "art" VALUES(19,4,'season','banner','http://thetvdb.com/banners/se asonswide/80379-2-2.jpg');
187 INSERT INTO "art" VALUES(20,4,'season','poster','http://thetvdb.com/banners/se asons/80379-2-11.jpg');
188 INSERT INTO "art" VALUES(21,5,'season','banner','http://thetvdb.com/banners/se asonswide/80379-3-2.jpg');
189 INSERT INTO "art" VALUES(22,5,'season','poster','http://thetvdb.com/banners/se asons/80379-3-9.jpg');
190 INSERT INTO "art" VALUES(23,6,'season','banner','http://thetvdb.com/banners/se asonswide/80379-4-2.jpg');
191 INSERT INTO "art" VALUES(24,6,'season','poster','http://thetvdb.com/banners/se asons/80379-4-8.jpg');
192 INSERT INTO "art" VALUES(25,7,'season','banner','http://thetvdb.com/banners/se asonswide/80379-5.jpg');
193 INSERT INTO "art" VALUES(26,7,'season','poster','http://thetvdb.com/banners/se asons/80379-5-9.jpg');
194 INSERT INTO "art" VALUES(27,8,'season','poster','http://thetvdb.com/banners/se asons/80379-6-8.jpg');
195 INSERT INTO "art" VALUES(28,9,'season','poster','http://thetvdb.com/banners/se asons/80379-7-3.jpg');
196 INSERT INTO "art" VALUES(29,1,'episode','thumb','http://thetvdb.com/banners/ep isodes/80379/332484.jpg');
197 CREATE INDEX ix_bookmark ON bookmark (idFile, type);
198 CREATE INDEX ix_path ON path ( strPath );
199 CREATE INDEX ix_files ON files ( idPath, strFilename );
200 CREATE UNIQUE INDEX ix_episode_file_1 on episode (idEpisode, idFile);
201 CREATE UNIQUE INDEX id_episode_file_2 on episode (idFile, idEpisode);
202 CREATE INDEX ix_episode_season_episode on episode (c12, c13);
203 CREATE INDEX ix_episode_bookmark on episode (c17);
204 CREATE INDEX ix_episode_show1 on episode(idEpisode,idShow);
205 CREATE INDEX ix_episode_show2 on episode(idShow,idEpisode);
206 CREATE UNIQUE INDEX ix_tvshowlinkpath_1 ON tvshowlinkpath ( idShow, idPath );
207 CREATE UNIQUE INDEX ix_tvshowlinkpath_2 ON tvshowlinkpath ( idPath, idShow );
208 CREATE INDEX ixEpisodeBasePath ON episode ( c19 );
209 CREATE INDEX ixTVShowBasePath on tvshow ( c17 );
210 CREATE INDEX ix_seasons ON seasons (idShow, season);
211 CREATE INDEX ix_art ON art(media_id, media_type, type);
212 CREATE VIEW episodeview
213 AS
214 SELECT episode.*,
215 files.strfilename AS strFileName,
216 path.strpath AS strPath,
217 files.playcount AS playCount,
218 files.lastplayed AS lastPlayed,
219 files.dateadded AS dateAdded,
220 tvshow.c00 AS strTitle,
221 tvshow.c14 AS strStudio,
222 tvshow.c05 AS premiered,
223 tvshow.c13 AS mpaa,
224 tvshow.c16 AS strShowPath,
225 bookmark.timeinseconds AS resumeTimeInSeconds,
226 bookmark.totaltimeinseconds AS totalTimeInSeconds,
227 seasons.idseason AS idSeason
228 FROM episode
229 JOIN files
230 ON files.idfile = episode.idfile
231 JOIN tvshow
232 ON tvshow.idshow = episode.idshow
233 LEFT JOIN seasons
234 ON seasons.idshow = episode.idshow
235 AND seasons.season = episode.c12
236 JOIN path
237 ON files.idpath = path.idpath
238 LEFT JOIN bookmark
239 ON bookmark.idfile = episode.idfile
240 AND bookmark.type = 1;
241 CREATE VIEW tvshowview
242 AS
243 SELECT tvshow.*,
244 path.strpath AS strPath,
245 path.dateadded AS dateAdded,
246 Max(files.lastplayed) AS lastPlayed,
247 NULLIF(Count(episode.c12), 0) AS totalCount,
248 Count(files.playcount) AS watchedcount,
249 NULLIF(Count(DISTINCT( episode.c12 )), 0) AS totalSeasons
250 FROM tvshow
251 LEFT JOIN tvshowlinkpath
252 ON tvshowlinkpath.idshow = tvshow.idshow
253 LEFT JOIN path
254 ON path.idpath = tvshowlinkpath.idpath
255 LEFT JOIN episode
256 ON episode.idshow = tvshow.idshow
257 LEFT JOIN files
258 ON files.idfile = episode.idfile
259 GROUP BY tvshow.idshow;
260 SELECT
261 episodeview.c12,
262 path.strPath,
263 tvshowview.c00,
264 tvshowview.c01,
265 tvshowview.c05,
266 tvshowview.c08,
267 tvshowview.c14,
268 tvshowview.c13,
269 seasons.idSeason,
270 count(1),
271 count(files.playCount)
272 FROM episodeview
273 JOIN tvshowview ON tvshowview.idShow = episodeview.idShow
274 JOIN seasons ON (seasons.idShow = tvshowview.idShow
275 AND seasons.season = episodeview.c12)
276 JOIN files ON files.idFile = episodeview.idFile
277 JOIN tvshowlinkpath ON tvshowlinkpath.idShow = tvshowview.idShow
278 JOIN path ON path.idPath = tvshowlinkpath.idPath
279 WHERE tvshowview.idShow = 1
280 GROUP BY episodeview.c12;
281 } {1 /tmp/tvshows/The.Big.Bang.Theory/ {The Big Bang Theory} {Leonard Hofstadter and Sheldon Cooper are brilliant physicists, the kind of "beautiful minds" that understand how the universe works. But none of that genius helps them interact with people, especially women. All this begins to change when a free-spirited be auty named Penny moves in next door. Sheldon, Leonard's roommate, is quite conte nt spending his nights playing Klingon Boggle with their socially dysfunctional friends, fellow CalTech scientists Howard Wolowitz and Raj Koothrappali. However , Leonard sees in Penny a whole new universe of possibilities... including love. } 2007-09-24 Comedy CBS TV-PG 3 1 0}
282
283
284 finish_test
OLDNEW
« no previous file with comments | « third_party/sqlite/sqlite-src-3080704/test/trans3.test ('k') | third_party/sqlite/sqlite-src-3080704/test/trigger1.test » ('j') | no next file with comments »

Powered by Google App Engine
This is Rietveld 408576698