1 # 2007 January 26 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 #2192 has been 14 # fixed. 15 # 16 # 17 # $Id: tkt2192.test,v 1.3 2008/08/04 03:51:24 danielk1977 Exp $ 18 19 set testdir [file dirname $argv0] 20 source $testdir/tester.tcl 21 22 ifcapable !datetime||!compound { 23 finish_test 24 return 25 } 26 27 do_test tkt2192-1.1 { 28 execsql { 29 -- Raw data (RBS) -------- 30 31 create table records ( 32 date real, 33 type text, 34 description text, 35 value integer, 36 acc_name text, 37 acc_no text 38 ); 39 40 -- Direct Debits ---------------- 41 create view direct_debits as 42 select * from records where type = 'D/D'; 43 44 create view monthly_direct_debits as 45 select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value 46 from direct_debits 47 group by strftime('%Y-%m', date); 48 49 -- Expense Categories --------------- 50 create view energy as 51 select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value 52 from direct_debits 53 where description like '%NPOWER%' 54 group by strftime('%Y-%m', date); 55 56 create view phone_internet as 57 select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value 58 from direct_debits 59 where description like '%BT DIRECT%' 60 or description like '%SUPANET%' 61 or description like '%ORANGE%' 62 group by strftime('%Y-%m', date); 63 64 create view credit_cards as 65 select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value 66 from direct_debits where description like '%VISA%' 67 group by strftime('%Y-%m', date); 68 69 -- Overview --------------------- 70 71 create view expense_overview as 72 select 'Energy' as expense, date, value from energy 73 union 74 select 'Phone/Internet' as expense, date, value from phone_internet 75 union 76 select 'Credit Card' as expense, date, value from credit_cards; 77 78 create view jan as 79 select 'jan', expense, value from expense_overview 80 where date like '%-01'; 81 82 create view nov as 83 select 'nov', expense, value from expense_overview 84 where date like '%-11'; 85 86 create view summary as 87 select * from jan join nov on (jan.expense = nov.expense); 88 } 89 } {} 90 do_test tkt2192-1.2 { 91 # set ::sqlite_addop_trace 1 92 execsql { 93 select * from summary; 94 } 95 } {} 96 do_test tkt2192-2.1 { 97 execsql { 98 CREATE TABLE t1(a,b); 99 CREATE VIEW v1 AS 100 SELECT * FROM t1 WHERE b%7=0 UNION SELECT * FROM t1 WHERE b%5=0; 101 INSERT INTO t1 VALUES(1,7); 102 INSERT INTO t1 VALUES(2,10); 103 INSERT INTO t1 VALUES(3,14); 104 INSERT INTO t1 VALUES(4,15); 105 INSERT INTO t1 VALUES(1,16); 106 INSERT INTO t1 VALUES(2,17); 107 INSERT INTO t1 VALUES(3,20); 108 INSERT INTO t1 VALUES(4,21); 109 INSERT INTO t1 VALUES(1,22); 110 INSERT INTO t1 VALUES(2,24); 111 INSERT INTO t1 VALUES(3,25); 112 INSERT INTO t1 VALUES(4,26); 113 INSERT INTO t1 VALUES(1,27); 114 115 SELECT b FROM v1 ORDER BY b; 116 } 117 } {7 10 14 15 20 21 25} 118 do_test tkt2192-2.2 { 119 execsql { 120 SELECT * FROM v1 ORDER BY a, b; 121 } 122 } {1 7 2 10 3 14 3 20 3 25 4 15 4 21} 123 do_test tkt2192-2.3 { 124 execsql { 125 SELECT x.a || '/' || x.b || '/' || y.b 126 FROM v1 AS x JOIN v1 AS y ON x.a=y.a AND x.b<y.b 127 ORDER BY x.a, x.b, y.b 128 } 129 } {3/14/20 3/14/25 3/20/25 4/15/21} 130 do_test tkt2192-2.4 { 131 execsql { 132 CREATE VIEW v2 AS 133 SELECT x.a || '/' || x.b || '/' || y.b AS z 134 FROM v1 AS x JOIN v1 AS y ON x.a=y.a AND x.b<y.b 135 ORDER BY x.a, x.b, y.b; 136 SELECT * FROM v2; 137 } 138 } {3/14/20 3/14/25 3/20/25 4/15/21} 139 140 finish_test 141
