Return to tkt2192.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

Annotation of embedaddon/sqlite3/test/tkt2192.test, revision 1.1.1.1

1.1       misho       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