1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/tkt2192.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,140 @@
1.4 +# 2007 January 26
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library.
1.15 +#
1.16 +# This file implements tests to verify that ticket #2192 has been
1.17 +# fixed.
1.18 +#
1.19 +#
1.20 +# $Id: tkt2192.test,v 1.3 2008/08/04 03:51:24 danielk1977 Exp $
1.21 +
1.22 +set testdir [file dirname $argv0]
1.23 +source $testdir/tester.tcl
1.24 +
1.25 +ifcapable !datetime||!compound {
1.26 + finish_test
1.27 + return
1.28 +}
1.29 +
1.30 +do_test tkt2192-1.1 {
1.31 + execsql {
1.32 + -- Raw data (RBS) --------
1.33 +
1.34 + create table records (
1.35 + date real,
1.36 + type text,
1.37 + description text,
1.38 + value integer,
1.39 + acc_name text,
1.40 + acc_no text
1.41 + );
1.42 +
1.43 + -- Direct Debits ----------------
1.44 + create view direct_debits as
1.45 + select * from records where type = 'D/D';
1.46 +
1.47 + create view monthly_direct_debits as
1.48 + select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
1.49 + from direct_debits
1.50 + group by strftime('%Y-%m', date);
1.51 +
1.52 + -- Expense Categories ---------------
1.53 + create view energy as
1.54 + select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
1.55 + from direct_debits
1.56 + where description like '%NPOWER%'
1.57 + group by strftime('%Y-%m', date);
1.58 +
1.59 + create view phone_internet as
1.60 + select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
1.61 + from direct_debits
1.62 + where description like '%BT DIRECT%'
1.63 + or description like '%SUPANET%'
1.64 + or description like '%ORANGE%'
1.65 + group by strftime('%Y-%m', date);
1.66 +
1.67 + create view credit_cards as
1.68 + select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
1.69 + from direct_debits where description like '%VISA%'
1.70 + group by strftime('%Y-%m', date);
1.71 +
1.72 + -- Overview ---------------------
1.73 +
1.74 + create view expense_overview as
1.75 + select 'Energy' as expense, date, value from energy
1.76 + union
1.77 + select 'Phone/Internet' as expense, date, value from phone_internet
1.78 + union
1.79 + select 'Credit Card' as expense, date, value from credit_cards;
1.80 +
1.81 + create view jan as
1.82 + select 'jan', expense, value from expense_overview
1.83 + where date like '%-01';
1.84 +
1.85 + create view nov as
1.86 + select 'nov', expense, value from expense_overview
1.87 + where date like '%-11';
1.88 +
1.89 + create view summary as
1.90 + select * from jan join nov on (jan.expense = nov.expense);
1.91 + }
1.92 +} {}
1.93 +do_test tkt2192-1.2 {
1.94 + # set ::sqlite_addop_trace 1
1.95 + execsql {
1.96 + select * from summary;
1.97 + }
1.98 +} {}
1.99 +do_test tkt2192-2.1 {
1.100 + execsql {
1.101 + CREATE TABLE t1(a,b);
1.102 + CREATE VIEW v1 AS
1.103 + SELECT * FROM t1 WHERE b%7=0 UNION SELECT * FROM t1 WHERE b%5=0;
1.104 + INSERT INTO t1 VALUES(1,7);
1.105 + INSERT INTO t1 VALUES(2,10);
1.106 + INSERT INTO t1 VALUES(3,14);
1.107 + INSERT INTO t1 VALUES(4,15);
1.108 + INSERT INTO t1 VALUES(1,16);
1.109 + INSERT INTO t1 VALUES(2,17);
1.110 + INSERT INTO t1 VALUES(3,20);
1.111 + INSERT INTO t1 VALUES(4,21);
1.112 + INSERT INTO t1 VALUES(1,22);
1.113 + INSERT INTO t1 VALUES(2,24);
1.114 + INSERT INTO t1 VALUES(3,25);
1.115 + INSERT INTO t1 VALUES(4,26);
1.116 + INSERT INTO t1 VALUES(1,27);
1.117 +
1.118 + SELECT b FROM v1 ORDER BY b;
1.119 + }
1.120 +} {7 10 14 15 20 21 25}
1.121 +do_test tkt2192-2.2 {
1.122 + execsql {
1.123 + SELECT * FROM v1 ORDER BY a, b;
1.124 + }
1.125 +} {1 7 2 10 3 14 3 20 3 25 4 15 4 21}
1.126 +do_test tkt2192-2.3 {
1.127 + execsql {
1.128 + SELECT x.a || '/' || x.b || '/' || y.b
1.129 + FROM v1 AS x JOIN v1 AS y ON x.a=y.a AND x.b<y.b
1.130 + ORDER BY x.a, x.b, y.b
1.131 + }
1.132 +} {3/14/20 3/14/25 3/20/25 4/15/21}
1.133 +do_test tkt2192-2.4 {
1.134 + execsql {
1.135 + CREATE VIEW v2 AS
1.136 + SELECT x.a || '/' || x.b || '/' || y.b AS z
1.137 + FROM v1 AS x JOIN v1 AS y ON x.a=y.a AND x.b<y.b
1.138 + ORDER BY x.a, x.b, y.b;
1.139 + SELECT * FROM v2;
1.140 + }
1.141 +} {3/14/20 3/14/25 3/20/25 4/15/21}
1.142 +
1.143 +finish_test