/ Check-in [45c6a760]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Allow the RBU module to read data from appropriately named SQL views created within the RBU database.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 45c6a760ca63d19a7ccc352c7e35d8391025b515
User & Date: dan 2015-07-24 18:58:59
Context
2015-07-24
19:56
Fix the pragma2.test module so that it works with SQLITE_ENABLE_MEMORY_MANAGEMENT. check-in: de281a4f user: drh tags: trunk
18:58
Allow the RBU module to read data from appropriately named SQL views created within the RBU database. check-in: 45c6a760 user: dan tags: trunk
18:22
Fix a test script bug introduced by [562687d9]. check-in: a343745d user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added ext/rbu/rbu14.test.

            1  +# 2015 July 25
            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  +#
           12  +# Test that an RBU data_xxx table may be a view instead of a regular
           13  +# table.
           14  +#
           15  +
           16  +if {![info exists testdir]} {
           17  +  set testdir [file join [file dirname [info script]] .. .. test]
           18  +}
           19  +source $testdir/tester.tcl
           20  +source $testdir/lock_common.tcl
           21  +set ::testprefix rbu14
           22  +
           23  +
           24  +foreach {tn schema} {
           25  +  1 {
           26  +    CREATE TABLE t1(a PRIMARY KEY, b, c);
           27  +    CREATE TABLE t2(a PRIMARY KEY, b, c);
           28  +  }
           29  +  2 {
           30  +    CREATE TABLE t1(a PRIMARY KEY, b, c);
           31  +    CREATE TABLE t2(a PRIMARY KEY, b, c);
           32  +    CREATE INDEX i1 ON t1(b, c);
           33  +    CREATE INDEX i2 ON t2(b, c);
           34  +  }
           35  +  3 {
           36  +    CREATE TABLE t1(a PRIMARY KEY, b, c) WITHOUT ROWID;
           37  +    CREATE TABLE t2(a PRIMARY KEY, b, c) WITHOUT ROWID;
           38  +  }
           39  +  4 {
           40  +    CREATE TABLE t1(a PRIMARY KEY, b, c) WITHOUT ROWID;
           41  +    CREATE TABLE t2(a PRIMARY KEY, b, c) WITHOUT ROWID;
           42  +    CREATE INDEX i1 ON t1(b, c);
           43  +    CREATE INDEX i2 ON t2(b, c);
           44  +  }
           45  +} {
           46  +  reset_db
           47  +
           48  +  execsql $schema
           49  +  execsql {
           50  +    INSERT INTO t1 VALUES(50, 50, 50);
           51  +    INSERT INTO t1 VALUES(51, 51, 51);
           52  +    INSERT INTO t2 VALUES(50, 50, 50);
           53  +    INSERT INTO t2 VALUES(51, 51, 51);
           54  +  }
           55  +
           56  +  forcedelete rbu.db
           57  +  do_execsql_test $tn.1 {
           58  +    ATTACH 'rbu.db' AS rbu;
           59  +    CREATE TABLE rbu.stuff(tbl, a, b, c, rbu_control);
           60  +    INSERT INTO stuff VALUES
           61  +      ('t1', 1, 2, 3, 0),                   -- insert into t1
           62  +      ('t2', 4, 5, 6, 0),                   -- insert into t2
           63  +      ('t1', 50, NULL, NULL, 1),            -- delete from t1
           64  +      ('t2', 51, NULL, NULL, 1);            -- delete from t2
           65  +
           66  +    CREATE VIEW rbu.data_t1 AS 
           67  +    SELECT a, b, c, rbu_control FROM stuff WHERE tbl='t1';
           68  +    CREATE VIEW rbu.data_t2 AS 
           69  +    SELECT a, b, c, rbu_control FROM stuff WHERE tbl='t2';
           70  +  }
           71  +
           72  +  do_test $tn.2 {
           73  +    while 1 {
           74  +      sqlite3rbu rbu test.db rbu.db
           75  +      set rc [rbu step]
           76  +      rbu close
           77  +      if {$rc != "SQLITE_OK"} break
           78  +    }
           79  +    set rc
           80  +  } {SQLITE_DONE}
           81  +
           82  +  do_execsql_test $tn.3.1 {
           83  +    SELECT * FROM t1 ORDER BY a;
           84  +  } {1 2 3 51 51 51}
           85  +
           86  +  do_execsql_test $tn.3.2 {
           87  +    SELECT * FROM t2 ORDER BY a;
           88  +  } {4 5 6 50 50 50}
           89  +
           90  +  integrity_check $tn.4
           91  +}
           92  +
           93  +
           94  +finish_test
           95  +

Changes to ext/rbu/sqlite3rbu.c.

   564    564   */
   565    565   static int rbuObjIterFirst(sqlite3rbu *p, RbuObjIter *pIter){
   566    566     int rc;
   567    567     memset(pIter, 0, sizeof(RbuObjIter));
   568    568   
   569    569     rc = prepareAndCollectError(p->dbRbu, &pIter->pTblIter, &p->zErrmsg, 
   570    570         "SELECT substr(name, 6) FROM sqlite_master "
   571         -      "WHERE type='table' AND name LIKE 'data_%'"
          571  +      "WHERE type IN ('table', 'view') AND name LIKE 'data_%'"
   572    572     );
   573    573   
   574    574     if( rc==SQLITE_OK ){
   575    575       rc = prepareAndCollectError(p->dbMain, &pIter->pIdxIter, &p->zErrmsg,
   576    576           "SELECT name, rootpage, sql IS NULL OR substr(8, 6)=='UNIQUE' "
   577    577           "  FROM main.sqlite_master "
   578    578           "  WHERE type='index' AND tbl_name = ?"