Return to tkt2192.test CVS log

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

sqlite3

    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.1.1.1 2012/02/21 17:04:16 misho 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