third_party/sqlite/src/test/transitive1.test

   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, timeInSeconds double, totalTimeInSeconds double, thumbNailImage text, player text, playerState text, type integer);
 126   CREATE TABLE path ( idPath integer primary key, strPath text, strContent text, strScraper text, strHash text, scanRecursive integer, useFolderNames bool, strSettings text, noUpdate bool, exclude bool, dateAdded text);
 127   INSERT INTO "path" VALUES(1,'/tmp/tvshows/','tvshows','metadata.tvdb.com','989B1CE5680A14F5F86123F751169B49',0,0,'<settings><setting id="absolutenumber" value="false" /><setting id="dvdorder" value="false" /><setting id="fanart" value="true" /><setting id="language" value="en" /></settings>',0,0,NULL);
 128   INSERT INTO "path" VALUES(2,'/tmp/tvshows/The.Big.Bang.Theory/','','','85E1DAAB2F5FF6EAE8AEDF1B5C882D1E',NULL,NULL,NULL,NULL,NULL,'2013-10-23 18:58:43');
 129   CREATE TABLE files ( idFile integer primary key, idPath integer, strFilename text, 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,c03 text,c04 text,c05 text,c06 text,c07 text,c08 text,c09 text,c10 text,c11 text,c12 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 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 beauty named Penny moves in next door. Sheldon, Leonard''s roommate, is quite content 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.','','','9.200000','2007-09-24','<thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g13.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g23.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/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">http://thetvdb.com/banners/graphical/80379-g19.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g3.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g4.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g15.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g22.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g7.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g10.jpg</thumb><thumb
 134   aspect="banner">http://thetvdb.com/banners/graphical/80379-g24.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g8.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g9.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g14.jpg</thumb><thumb aspect="banner">http://thetvdb.com/banners/graphical/80379-g16.jpg</thumb><thumb 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="banner">http://thetvdb.com/banners/text/80379-5.jpg</thumb><thumb aspect="poster" type="season" season="6">http://thetvdb.com/banners/seasons/80379-6-8.jpg</thumb><thumb aspect="poster" type="season" season="0">http://thetvdb.com/banners/seasons/80379-0-4.jpg</thumb><thumb aspect="poster" type="season"
 135   season="1">http://thetvdb.com/banners/seasons/80379-1-12.jpg</thumb><thumb aspect="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" season="5">http://thetvdb.com/banners/seasons/80379-5-9.jpg</thumb><thumb aspect="poster" type="season" season="4">http://thetvdb.com/banners/seasons/80379-4-8.jpg</thumb><thumb aspect="poster" type="season" season="7">http://thetvdb.com/banners/seasons/80379-7-3.jpg</thumb><thumb aspect="poster" type="season" season="3">http://thetvdb.com/banners/seasons/80379-3-4.jpg</thumb><thumb aspect="poster" type="season" season="4">http://thetvdb.com/banners/seasons/80379-4-5.jpg</thumb><thumb aspect="poster" type="season" season="2">http://thetvdb.com/banners/seasons/80379-2-9.jpg</thumb><thumb aspect="poster" type="season" season="0">http://thetvdb.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/banners/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</thumb><thumb aspect="poster" type="season" season="6">http://thetvdb.com/banners/seasons/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="season" season="3">http://thetvdb.com/banners/seasons/80379-3.jpg</thumb><thumb aspect="poster" type="season" season="4">http://thetvdb.com/banners/seasons/80379-4-2.jpg</thumb><thumb aspect="poster" type="season" season="5">http://thetvdb.com/banners/seasons/80379-5-7.jpg</thumb><thumb aspect="poster" type="season" season="2">http://thetvdb.com/banners/seasons/80379-2-10.jpg</
 137   thumb><thumb aspect="poster" type="season" season="6">http://thetvdb.com/banners/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" type="season" season="5">http://thetvdb.com/banners/seasons/80379-5-4.jpg</thumb><thumb aspect="poster" type="season" season="4">http://thetvdb.com/banners/seasons/80379-4.jpg</thumb><thumb aspect="poster" type="season" season="6">http://thetvdb.com/banners/seasons/80379-6-3.jpg</thumb><thumb aspect="poster" type="season" season="5">http://thetvdb.com/banners/seasons/80379-5.jpg</thumb><thumb aspect="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/banners/seasons/80379-2.jpg</thumb><thumb aspect="poster" type="season" season="7">http://thetvdb.com/banners/seasons/80379-7.jpg</thumb><thumb aspect="poster" type="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" season="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.jpg</thumb><thumb aspect="poster" type="season" season="1">http://thetvdb.com/banners/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/seasons/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="season" season="3">http://thetvdb.com/banners/seasons/80379-3-5.jpg</thumb><thumb aspect="poster" type="season" season="1">http://thetvdb.com/banners/
 139   seasons/80379-1-6.jpg</thumb><thumb aspect="poster" type="season" season="2">http://thetvdb.com/banners/seasons/80379-2-3.jpg</thumb><thumb aspect="poster" type="season" season="2">http://thetvdb.com/banners/seasons/80379-2-8.jpg</thumb><thumb aspect="poster" type="season" season="6">http://thetvdb.com/banners/seasons/80379-6-7.jpg</thumb><thumb aspect="poster" type="season" season="5">http://thetvdb.com/banners/seasons/80379-5-8.jpg</thumb><thumb aspect="poster" type="season" season="4">http://thetvdb.com/banners/seasons/80379-4-7.jpg</thumb><thumb aspect="poster" type="season" season="2">http://thetvdb.com/banners/seasons/80379-2-6.jpg</thumb><thumb aspect="poster" type="season" season="3">http://thetvdb.com/banners/seasons/80379-3-8.jpg</thumb><thumb aspect="poster" type="season" season="1">http://thetvdb.com/banners/seasons/80379-1-11.jpg</thumb><thumb aspect="poster" type="season" season="1">http://thetvdb.com/banners/seasons/80379-1-10.jpg</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><thumb aspect="poster" type="season" season="2">http://thetvdb.com/banners/seasons/80379-2-4.jpg</thumb><thumb aspect="poster" type="season" season="1">http://thetvdb.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/banners/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/seasons/80379-5-6.jpg</thumb><thumb aspect="poster" type="season"
 141   season="3">http://thetvdb.com/banners/seasons/80379-3-2.jpg</thumb><thumb aspect="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" season="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.com/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 aspect="banner" type="season" season="0">http://thetvdb.com/banners/seasonswide/80379-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/banners/seasonswide/80379-2.jpg</thumb><thumb aspect="banner" type="season" season="4">http://thetvdb.com/banners/seasonswide/80379-4.jpg</thumb><thumb aspect="banner" type="season" season="3">http://thetvdb.com/banners/seasonswide/80379-3.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-22.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-18.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-13.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-10.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-16.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-1.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-9.jpg</thumb><thumb aspect="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</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-4.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-20.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-23.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-7.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-3.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-12.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-11.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-15.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-21.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-14.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-17.jpg</thumb><thumb aspect="poster">http://thetvdb.com/banners/posters/80379-6.jpg</thumb><thumb
 144   aspect="poster">http://thetvdb.com/banners/posters/80379-5.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-22.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-18.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-13.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-10.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-16.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-1.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-9.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-2.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-19.jpg</thumb><thumb aspect="
 145   poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-8.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/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/posters/80379-7.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-3.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-12.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-11.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-15.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-21.jpg</
 146   thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-14.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-17.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-6.jpg</thumb><thumb aspect="poster" type="season" season="-1">http://thetvdb.com/banners/posters/80379-5.jpg</thumb>','','Comedy','','<episodeguide><url cache="80379-en.xml">http://thetvdb.com/api/1D62F2F90030C444/series/80379/all/en.zip</url></episodeguide>','<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">fanart/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|" preview="_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,102,152|116,109,116|235,152,146|" preview="_cache/fanart/original/80379-14.jpg">fanart/original/80379-14.jpg</thumb><thumb dim="1920x1080" colors="|150,158,161|174,75,121|150,98,58|" preview="_cache/fanart/original/80379-16.jpg">fanart/original/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</thumb><thumb dim="1920x1080" colors="" preview="_cache/fanart/original/80379-35.jpg">fanart/original/80379-35.jpg</thumb><thumb dim="1920x1080" colors="" preview="_cache/fanart/original/80379-40.jpg">fanart/original/80379-40.jpg</thumb><thumb 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</thumb><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/fanart/original/80379-11.jpg">fanart/original/80379-11.jpg</thumb><thumb dim="1280x720" colors="|195,129,97|244,192,168|219,148,118|" preview="_cache/fanart/original/80379-24.jpg">fanart/original/80379-24.jpg</thumb><thumb dim="1920x1080" colors="|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="" preview="_cache/fanart/original/80379-19.jpg">fanart/original/80379-19.jpg</thumb><thumb dim="1920x1080" colors="|246,199,69|98,55,38|161,127,82|" preview="_cache/fanart/original/80379-9.jpg">fanart/original/80379-9.jpg</thumb><thumb dim="1280x720" 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/original/80379-45.jpg</thumb><thumb dim="1920x1080" colors="" preview="_cache/fanart/original/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/80379-10.jpg">fanart/original/80379-10.jpg</thumb><thumb dim="1920x1080" colors="" preview="_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/fanart/original/80379-12.jpg">fanart/original/80379-12.jpg</thumb><thumb dim="1920x1080" colors="|226,209,165|51,18,9|89,54,24|" preview="_cache/fanart/original/80379-5.jpg">fanart/original/80379-5.jpg</thumb><thumb dim="1280x720" colors="" preview="_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.jpg</thumb><thumb dim="1920x1080" colors="|233,218,65|30,27,46|173,53,18|" preview="_cache/fanart/original/80379-32.jpg">fanart/original/80379-32.jpg</thumb><thumb dim="1280x720" colors="|248,248,248|64,54,78|188,193,196|" preview="_cache/fanart/original/80379-3.jpg">fanart/original/80379-3.jpg</thumb><thumb dim="1280x720" colors="" preview="_cache/fanart/original/80379-25.jpg">fanart/original/80379-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</thumb><thumb dim="1920x1080" colors="|221,191,157|11,7,6|237,146,102|" preview="_cache/fanart/original/80379-21.jpg">fanart/original/80379-21.jpg</thumb><thumb dim="1280x720" colors="" preview="_cache/fanart/original/80379-28.jpg">fanart/original/80379-28.jpg</thumb><thumb dim="1920x1080" colors="" preview="_cache/fanart/original/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/fanart/original/80379-38.jpg">fanart/original/80379-38.jpg</thumb><thumb dim="1920x1080" colors="|174,111,68|243,115,50|252,226,45|" preview="_cache/fanart/original/80379-20.jpg">fanart/original/80379-20.jpg</thumb><thumb dim="1920x1080" colors="|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 dim="1280x720" colors="|69,68,161|142,118,142|222,191,137|" preview="_cache/fanart/original/80379-22.jpg">fanart/original/80379-22.jpg</thumb><thumb dim="1280x720" 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">fanart/original/80379-18.jpg</thumb><thumb dim="1280x720" colors=""
 152   preview="_cache/fanart/original/80379-6.jpg">fanart/original/80379-6.jpg</thumb><thumb dim="1280x720" colors="" preview="_cache/fanart/original/80379-8.jpg">fanart/original/80379-8.jpg</thumb><thumb dim="1280x720" colors="" preview="_cache/fanart/original/80379-41.jpg">fanart/original/80379-41.jpg</thumb><thumb dim="1920x1080" colors="" preview="_cache/fanart/original/80379-44.jpg">fanart/original/80379-44.jpg</thumb><thumb dim="1280x720" colors="" preview="_cache/fanart/original/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,NULL);
 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,c17 varchar(24),c18 text,c19 text,c20 text,c21 text,c22 text,c23 text, idShow integer);
 154   INSERT INTO "episode" VALUES(1,1,'Pilot','Brilliant physicist roommates Leonard and Sheldon meet their new neighbor Penny, who begins showing them that as much as they know about science, they know little about actual living.','','7.700000','Chuck Lorre / Bill Prady','2007-09-24','<thumb>http://thetvdb.com/banners/episodes/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 integer);
 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/actors/73597.jpg');
 169   INSERT INTO "art" VALUES(2,2,'actor','thumb','http://thetvdb.com/banners/actors/73596.jpg');
 170   INSERT INTO "art" VALUES(3,3,'actor','thumb','http://thetvdb.com/banners/actors/73595.jpg');
 171   INSERT INTO "art" VALUES(4,4,'actor','thumb','http://thetvdb.com/banners/actors/73599.jpg');
 172   INSERT INTO "art" VALUES(5,5,'actor','thumb','http://thetvdb.com/banners/actors/73598.jpg');
 173   INSERT INTO "art" VALUES(6,6,'actor','thumb','http://thetvdb.com/banners/actors/283158.jpg');
 174   INSERT INTO "art" VALUES(7,7,'actor','thumb','http://thetvdb.com/banners/actors/283157.jpg');
 175   INSERT INTO "art" VALUES(8,8,'actor','thumb','http://thetvdb.com/banners/actors/91271.jpg');
 176   INSERT INTO "art" VALUES(9,9,'actor','thumb','http://thetvdb.com/banners/actors/294178.jpg');
 177   INSERT INTO "art" VALUES(10,10,'actor','thumb','http://thetvdb.com/banners/actors/283159.jpg');
 178   INSERT INTO "art" VALUES(11,1,'tvshow','banner','http://thetvdb.com/banners/graphical/80379-g13.jpg');
 179   INSERT INTO "art" VALUES(12,1,'tvshow','fanart','http://thetvdb.com/banners/fanart/original/80379-2.jpg');
 180   INSERT INTO "art" VALUES(13,1,'tvshow','poster','http://thetvdb.com/banners/posters/80379-22.jpg');
 181   INSERT INTO "art" VALUES(14,1,'season','poster','http://thetvdb.com/banners/posters/80379-22.jpg');
 182   INSERT INTO "art" VALUES(15,2,'season','banner','http://thetvdb.com/banners/seasonswide/80379-0.jpg');
 183   INSERT INTO "art" VALUES(16,2,'season','poster','http://thetvdb.com/banners/seasons/80379-0-4.jpg');
 184   INSERT INTO "art" VALUES(17,3,'season','banner','http://thetvdb.com/banners/seasonswide/80379-1-2.jpg');
 185   INSERT INTO "art" VALUES(18,3,'season','poster','http://thetvdb.com/banners/seasons/80379-1-12.jpg');
 186   INSERT INTO "art" VALUES(19,4,'season','banner','http://thetvdb.com/banners/seasonswide/80379-2-2.jpg');
 187   INSERT INTO "art" VALUES(20,4,'season','poster','http://thetvdb.com/banners/seasons/80379-2-11.jpg');
 188   INSERT INTO "art" VALUES(21,5,'season','banner','http://thetvdb.com/banners/seasonswide/80379-3-2.jpg');
 189   INSERT INTO "art" VALUES(22,5,'season','poster','http://thetvdb.com/banners/seasons/80379-3-9.jpg');
 190   INSERT INTO "art" VALUES(23,6,'season','banner','http://thetvdb.com/banners/seasonswide/80379-4-2.jpg');
 191   INSERT INTO "art" VALUES(24,6,'season','poster','http://thetvdb.com/banners/seasons/80379-4-8.jpg');
 192   INSERT INTO "art" VALUES(25,7,'season','banner','http://thetvdb.com/banners/seasonswide/80379-5.jpg');
 193   INSERT INTO "art" VALUES(26,7,'season','poster','http://thetvdb.com/banners/seasons/80379-5-9.jpg');
 194   INSERT INTO "art" VALUES(27,8,'season','poster','http://thetvdb.com/banners/seasons/80379-6-8.jpg');
 195   INSERT INTO "art" VALUES(28,9,'season','poster','http://thetvdb.com/banners/seasons/80379-7-3.jpg');
 196   INSERT INTO "art" VALUES(29,1,'episode','thumb','http://thetvdb.com/banners/episodes/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 beauty named Penny moves in next door. Sheldon, Leonard's roommate, is quite content 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