AryaWu/sqlite

1# 2007 January 262#3# The author disclaims copyright to this source code.  In place of4# 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 been14# fixed.  15#16#17# $Id: tkt2192.test,v 1.3 2008/08/04 03:51:24 danielk1977 Exp $18 19set testdir [file dirname $argv0]20source $testdir/tester.tcl21 22ifcapable !datetime||!compound {23  finish_test24  return25}26 27do_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        text38    );39    40    -- Direct Debits ----------------41    create view direct_debits as42      select * from records where type = 'D/D';43    44    create view monthly_direct_debits as45      select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value46        from direct_debits47       group by strftime('%Y-%m', date);48    49    -- Expense Categories ---------------50    create view energy as51      select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value52        from direct_debits53       where description like '%NPOWER%'54       group by strftime('%Y-%m', date);55    56    create view phone_internet as57      select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value58        from direct_debits59       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 as65      select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value66        from direct_debits where description like '%VISA%'67       group by strftime('%Y-%m', date);68    69    -- Overview ---------------------70    71    create view expense_overview as72      select 'Energy' as expense, date, value from energy73      union74      select 'Phone/Internet' as expense, date, value from phone_internet75      union76      select 'Credit Card' as expense, date, value from credit_cards;77    78    create view jan as79      select 'jan', expense, value from expense_overview80       where date like '%-01';81    82    create view nov as83      select 'nov', expense, value from expense_overview84       where date like '%-11';85    86    create view summary as87      select * from jan join nov on (jan.expense = nov.expense);88  }89} {}90do_test tkt2192-1.2 {91  # set ::sqlite_addop_trace 192  execsql {93    select * from summary;94  }95} {}96do_test tkt2192-2.1 {97  execsql {98    CREATE TABLE t1(a,b);99    CREATE VIEW v1 AS100      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}118do_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}123do_test tkt2192-2.3 {124  execsql {125    SELECT x.a || '/' || x.b || '/' || y.b126      FROM v1 AS x JOIN v1 AS y ON x.a=y.a AND x.b<y.b127     ORDER BY x.a, x.b, y.b128  }129} {3/14/20 3/14/25 3/20/25 4/15/21}130do_test tkt2192-2.4 {131  execsql {132    CREATE VIEW v2 AS133    SELECT x.a || '/' || x.b || '/' || y.b AS z134      FROM v1 AS x JOIN v1 AS y ON x.a=y.a AND x.b<y.b135     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 140finish_test141