/ Check-in [287aa306]
Login

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

Overview
Comment:Allow RBU tables to be named "data[0-9]*_<target>" instead of strictly "data_<target>". Also update RBU so that it always processes data tables in order sorted by name.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 287aa30601506f168d355c35176a3383474444ca
User & Date: dan 2015-07-30 11:38:19
Context
2015-07-30
20:26
Add the --rbu switch to the "sqldiff" utility. check-in: 098bea26 user: dan tags: trunk
11:38
Allow RBU tables to be named "data[0-9]*_<target>" instead of strictly "data_<target>". Also update RBU so that it always processes data tables in order sorted by name. check-in: 287aa306 user: dan tags: trunk
11:26
Return an error message (instead of segfaulting) if test function fts5_expr() is invoked with no arguments. check-in: 8e818b89 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added ext/rbu/rbufts.test.

            1  +# 2014 August 30
            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  +# This file contains tests for the RBU module. More specifically, it
           13  +# contains tests to ensure that RBU works with FTS tables.
           14  +#
           15  +
           16  +if {![info exists testdir]} {
           17  +  set testdir [file join [file dirname [info script]] .. .. test]
           18  +}
           19  +source $testdir/tester.tcl
           20  +set ::testprefix rbufts
           21  +
           22  +ifcapable !fts3 {
           23  +  finish_test
           24  +  return
           25  +}
           26  +
           27  +proc step_rbu {target rbu} {
           28  +  while 1 {
           29  +    sqlite3rbu rbu $target $rbu
           30  +    set rc [rbu step]
           31  +    rbu close
           32  +    if {$rc != "SQLITE_OK"} break
           33  +  }
           34  +  set rc
           35  +}
           36  +
           37  +do_execsql_test 1.0 {
           38  +  CREATE TABLE t1(i INTEGER PRIMARY KEY, a, b);
           39  +  CREATE VIRTUAL TABLE xx USING fts4(content=t1, a, b);
           40  +  INSERT INTO t1(rowid, a, b) VALUES(10, 'a b c', 'c b a');
           41  +  INSERT INTO t1(rowid, a, b) VALUES(20, 'a b c', 'd e f');
           42  +  INSERT INTO t1(rowid, a, b) VALUES(30, 'd e f', 'a b c');
           43  +  INSERT INTO t1(rowid, a, b) VALUES(40, 'd e f', 'd e f');
           44  +}
           45  +
           46  +do_execsql_test 1.1 {
           47  +  INSERT INTO xx(xx) VALUES('rebuild');
           48  +  INSERT INTO xx(xx) VALUES('integrity-check');
           49  +}
           50  +
           51  +forcedelete rbu.db
           52  +do_test 2.0 {
           53  +  sqlite3 dbrbu rbu.db
           54  +  dbrbu eval {
           55  +    CREATE TABLE data_t1(i, a, b, rbu_control);
           56  +    INSERT INTO data_t1 VALUES(20, NULL, NULL, 1);        -- delete
           57  +    INSERT INTO data_t1 VALUES(30, 'x y z', NULL, '.x.'); -- update
           58  +    INSERT INTO data_t1 VALUES(50, '1 2 3', 'x y z', 0);  -- insert
           59  +
           60  +    CREATE VIEW data0_xx AS 
           61  +    SELECT i AS rbu_rowid, a, b, 
           62  +    CASE WHEN rbu_control IN (0, 1) 
           63  +    THEN rbu_control ELSE substr(rbu_control, 2) END AS rbu_control
           64  +    FROM data_t1;
           65  +
           66  +  }
           67  +  dbrbu close
           68  +
           69  +  step_rbu test.db rbu.db
           70  +} {SQLITE_DONE}
           71  +
           72  +do_execsql_test 2.1 {
           73  +  INSERT INTO xx(xx) VALUES('integrity-check');
           74  +}
           75  +
           76  +
           77  +
           78  +
           79  +finish_test
           80  +

Changes to ext/rbu/sqlite3rbu.c.

   235    235     u8 *abNotNull;                  /* Array of flags, set on NOT NULL columns */
   236    236     u8 *abIndexed;                  /* Array of flags, set on indexed & PK cols */
   237    237     int eType;                      /* Table type - an RBU_PK_XXX value */
   238    238   
   239    239     /* Output variables. zTbl==0 implies EOF. */
   240    240     int bCleanup;                   /* True in "cleanup" state */
   241    241     const char *zTbl;               /* Name of target db table */
          242  +  const char *zDataTbl;           /* Name of rbu db table (or null) */
   242    243     const char *zIdx;               /* Name of target db index (or null) */
   243    244     int iTnum;                      /* Root page of current object */
   244    245     int iPkTnum;                    /* If eType==EXTERNAL, root of PK index */
   245    246     int bUnique;                    /* Current index is unique */
   246    247   
   247    248     /* Statements created by rbuObjIterPrepareAll() */
   248    249     int nCol;                       /* Number of columns in current object */
   249    250     sqlite3_stmt *pSelect;          /* Source data */
   250    251     sqlite3_stmt *pInsert;          /* Statement for INSERT operations */
   251    252     sqlite3_stmt *pDelete;          /* Statement for DELETE ops */
   252         -  sqlite3_stmt *pTmpInsert;       /* Insert into rbu_tmp_$zTbl */
          253  +  sqlite3_stmt *pTmpInsert;       /* Insert into rbu_tmp_$zDataTbl */
   253    254   
   254    255     /* Last UPDATE used (for PK b-tree updates only), or NULL. */
   255    256     RbuUpdateStmt *pRbuUpdate;
   256    257   };
   257    258   
   258    259   /*
   259    260   ** Values for RbuObjIter.eType
................................................................................
   522    523           pIter->bCleanup = 0;
   523    524           rc = sqlite3_step(pIter->pTblIter);
   524    525           if( rc!=SQLITE_ROW ){
   525    526             rc = resetAndCollectError(pIter->pTblIter, &p->zErrmsg);
   526    527             pIter->zTbl = 0;
   527    528           }else{
   528    529             pIter->zTbl = (const char*)sqlite3_column_text(pIter->pTblIter, 0);
   529         -          rc = pIter->zTbl ? SQLITE_OK : SQLITE_NOMEM;
          530  +          pIter->zDataTbl = (const char*)sqlite3_column_text(pIter->pTblIter,1);
          531  +          rc = (pIter->zDataTbl && pIter->zTbl) ? SQLITE_OK : SQLITE_NOMEM;
   530    532           }
   531    533         }else{
   532    534           if( pIter->zIdx==0 ){
   533    535             sqlite3_stmt *pIdx = pIter->pIdxIter;
   534    536             rc = sqlite3_bind_text(pIdx, 1, pIter->zTbl, -1, SQLITE_STATIC);
   535    537           }
   536    538           if( rc==SQLITE_OK ){
................................................................................
   553    555     if( rc!=SQLITE_OK ){
   554    556       rbuObjIterFinalize(pIter);
   555    557       p->rc = rc;
   556    558     }
   557    559     return rc;
   558    560   }
   559    561   
          562  +
          563  +/*
          564  +** The implementation of the rbu_target_name() SQL function. This function
          565  +** accepts one argument - the name of a table in the RBU database. If the
          566  +** table name matches the pattern:
          567  +**
          568  +**     data[0-9]_<name>
          569  +**
          570  +** where <name> is any sequence of 1 or more characters, <name> is returned.
          571  +** Otherwise, if the only argument does not match the above pattern, an SQL
          572  +** NULL is returned.
          573  +**
          574  +**     "data_t1"     -> "t1"
          575  +**     "data0123_t2" -> "t2"
          576  +**     "dataAB_t3"   -> NULL
          577  +*/
          578  +static void rbuTargetNameFunc(
          579  +  sqlite3_context *context,
          580  +  int argc,
          581  +  sqlite3_value **argv
          582  +){
          583  +  const char *zIn;
          584  +  assert( argc==1 );
          585  +
          586  +  zIn = (const char*)sqlite3_value_text(argv[0]);
          587  +  if( zIn && strlen(zIn)>4 && memcmp("data", zIn, 4)==0 ){
          588  +    int i;
          589  +    for(i=4; zIn[i]>='0' && zIn[i]<='9'; i++);
          590  +    if( zIn[i]=='_' && zIn[i+1] ){
          591  +      sqlite3_result_text(context, &zIn[i+1], -1, SQLITE_STATIC);
          592  +    }
          593  +  }
          594  +}
          595  +
   560    596   /*
   561    597   ** Initialize the iterator structure passed as the second argument.
   562    598   **
   563    599   ** If no error occurs, SQLITE_OK is returned and the iterator is left 
   564    600   ** pointing to the first entry. Otherwise, an error code and message is 
   565    601   ** left in the RBU handle passed as the first argument. A copy of the 
   566    602   ** error code is returned.
   567    603   */
   568    604   static int rbuObjIterFirst(sqlite3rbu *p, RbuObjIter *pIter){
   569    605     int rc;
   570    606     memset(pIter, 0, sizeof(RbuObjIter));
   571    607   
   572    608     rc = prepareAndCollectError(p->dbRbu, &pIter->pTblIter, &p->zErrmsg, 
   573         -      "SELECT substr(name, 6) FROM sqlite_master "
   574         -      "WHERE type IN ('table', 'view') AND name LIKE 'data_%'"
          609  +      "SELECT rbu_target_name(name) AS target, name FROM sqlite_master "
          610  +      "WHERE type IN ('table', 'view') AND target IS NOT NULL "
          611  +      "ORDER BY name"
   575    612     );
   576    613   
   577    614     if( rc==SQLITE_OK ){
   578    615       rc = prepareAndCollectError(p->dbMain, &pIter->pIdxIter, &p->zErrmsg,
   579    616           "SELECT name, rootpage, sql IS NULL OR substr(8, 6)=='UNIQUE' "
   580    617           "  FROM main.sqlite_master "
   581    618           "  WHERE type='index' AND tbl_name = ?"
................................................................................
   913    950            || pIter->eType==RBU_PK_VTAB
   914    951       );
   915    952   
   916    953       /* Populate the azTblCol[] and nTblCol variables based on the columns
   917    954       ** of the input table. Ignore any input table columns that begin with
   918    955       ** "rbu_".  */
   919    956       p->rc = prepareFreeAndCollectError(p->dbRbu, &pStmt, &p->zErrmsg, 
   920         -        sqlite3_mprintf("SELECT * FROM 'data_%q'", pIter->zTbl)
          957  +        sqlite3_mprintf("SELECT * FROM '%q'", pIter->zDataTbl)
   921    958       );
   922    959       if( p->rc==SQLITE_OK ){
   923    960         nCol = sqlite3_column_count(pStmt);
   924    961         rbuAllocateIterArrays(p, pIter, nCol);
   925    962       }
   926    963       for(i=0; p->rc==SQLITE_OK && i<nCol; i++){
   927    964         const char *zName = (const char*)sqlite3_column_name(pStmt, i);
................................................................................
   938    975       pStmt = 0;
   939    976   
   940    977       if( p->rc==SQLITE_OK
   941    978        && bRbuRowid!=(pIter->eType==RBU_PK_VTAB || pIter->eType==RBU_PK_NONE)
   942    979       ){
   943    980         p->rc = SQLITE_ERROR;
   944    981         p->zErrmsg = sqlite3_mprintf(
   945         -          "table data_%q %s rbu_rowid column", pIter->zTbl,
          982  +          "table %q %s rbu_rowid column", pIter->zDataTbl,
   946    983             (bRbuRowid ? "may not have" : "requires")
   947    984         );
   948    985       }
   949    986   
   950    987       /* Check that all non-HIDDEN columns in the destination table are also
   951    988       ** present in the input table. Populate the abTblPk[], azTblType[] and
   952    989       ** aiTblOrder[] arrays at the same time.  */
................................................................................
   959    996         const char *zName = (const char*)sqlite3_column_text(pStmt, 1);
   960    997         if( zName==0 ) break;  /* An OOM - finalize() below returns S_NOMEM */
   961    998         for(i=iOrder; i<pIter->nTblCol; i++){
   962    999           if( 0==strcmp(zName, pIter->azTblCol[i]) ) break;
   963   1000         }
   964   1001         if( i==pIter->nTblCol ){
   965   1002           p->rc = SQLITE_ERROR;
   966         -        p->zErrmsg = sqlite3_mprintf("column missing from data_%q: %s",
   967         -            pIter->zTbl, zName
         1003  +        p->zErrmsg = sqlite3_mprintf("column missing from %q: %s",
         1004  +            pIter->zDataTbl, zName
   968   1005           );
   969   1006         }else{
   970   1007           int iPk = sqlite3_column_int(pStmt, 5);
   971   1008           int bNotNull = sqlite3_column_int(pStmt, 3);
   972   1009           const char *zType = (const char*)sqlite3_column_text(pStmt, 2);
   973   1010   
   974   1011           if( i!=iOrder ){
................................................................................
  1515   1552     int bRbuRowid = (pIter->eType==RBU_PK_EXTERNAL || pIter->eType==RBU_PK_NONE);
  1516   1553     char *zBind = rbuObjIterGetBindlist(p, pIter->nTblCol + 1 + bRbuRowid);
  1517   1554     if( zBind ){
  1518   1555       assert( pIter->pTmpInsert==0 );
  1519   1556       p->rc = prepareFreeAndCollectError(
  1520   1557           p->dbRbu, &pIter->pTmpInsert, &p->zErrmsg, sqlite3_mprintf(
  1521   1558             "INSERT INTO %s.'rbu_tmp_%q'(rbu_control,%s%s) VALUES(%z)", 
  1522         -          p->zStateDb, pIter->zTbl, zCollist, zRbuRowid, zBind
         1559  +          p->zStateDb, pIter->zDataTbl, zCollist, zRbuRowid, zBind
  1523   1560       ));
  1524   1561     }
  1525   1562   }
  1526   1563   
  1527   1564   static void rbuTmpInsertFunc(
  1528   1565     sqlite3_context *pCtx, 
  1529   1566     int nVal,
................................................................................
  1611   1648   
  1612   1649         /* Create the SELECT statement to read keys in sorted order */
  1613   1650         if( p->rc==SQLITE_OK ){
  1614   1651           char *zSql;
  1615   1652           if( pIter->eType==RBU_PK_EXTERNAL || pIter->eType==RBU_PK_NONE ){
  1616   1653             zSql = sqlite3_mprintf(
  1617   1654                 "SELECT %s, rbu_control FROM %s.'rbu_tmp_%q' ORDER BY %s%s",
  1618         -              zCollist, p->zStateDb, pIter->zTbl,
         1655  +              zCollist, p->zStateDb, pIter->zDataTbl,
  1619   1656                 zCollist, zLimit
  1620   1657             );
  1621   1658           }else{
  1622   1659             zSql = sqlite3_mprintf(
  1623         -              "SELECT %s, rbu_control FROM 'data_%q' "
         1660  +              "SELECT %s, rbu_control FROM '%q' "
  1624   1661                 "WHERE typeof(rbu_control)='integer' AND rbu_control!=1 "
  1625   1662                 "UNION ALL "
  1626   1663                 "SELECT %s, rbu_control FROM %s.'rbu_tmp_%q' "
  1627   1664                 "ORDER BY %s%s",
  1628         -              zCollist, pIter->zTbl, 
  1629         -              zCollist, p->zStateDb, pIter->zTbl, 
         1665  +              zCollist, pIter->zDataTbl, 
         1666  +              zCollist, p->zStateDb, pIter->zDataTbl, 
  1630   1667                 zCollist, zLimit
  1631   1668             );
  1632   1669           }
  1633   1670           p->rc = prepareFreeAndCollectError(p->dbRbu, &pIter->pSelect, pz, zSql);
  1634   1671         }
  1635   1672   
  1636   1673         sqlite3_free(zImposterCols);
................................................................................
  1650   1687         zCollist = rbuObjIterGetCollist(p, pIter);
  1651   1688         pIter->nCol = pIter->nTblCol;
  1652   1689   
  1653   1690         /* Create the SELECT statement to read keys from data_xxx */
  1654   1691         if( p->rc==SQLITE_OK ){
  1655   1692           p->rc = prepareFreeAndCollectError(p->dbRbu, &pIter->pSelect, pz,
  1656   1693               sqlite3_mprintf(
  1657         -              "SELECT %s, rbu_control%s FROM 'data_%q'%s", 
  1658         -              zCollist, (bRbuRowid ? ", rbu_rowid" : ""), zTbl, zLimit
         1694  +              "SELECT %s, rbu_control%s FROM '%q'%s", 
         1695  +              zCollist, (bRbuRowid ? ", rbu_rowid" : ""), 
         1696  +              pIter->zDataTbl, zLimit
  1659   1697               )
  1660   1698           );
  1661   1699         }
  1662   1700   
  1663   1701         /* Create the imposter table or tables (if required). */
  1664   1702         rbuCreateImposterTable(p, pIter);
  1665   1703         rbuCreateImposterTable2(p, pIter);
................................................................................
  1689   1727           if( pIter->eType==RBU_PK_EXTERNAL || pIter->eType==RBU_PK_NONE ){
  1690   1728             zRbuRowid = ", rbu_rowid";
  1691   1729           }
  1692   1730   
  1693   1731           /* Create the rbu_tmp_xxx table and the triggers to populate it. */
  1694   1732           rbuMPrintfExec(p, p->dbRbu,
  1695   1733               "CREATE TABLE IF NOT EXISTS %s.'rbu_tmp_%q' AS "
  1696         -            "SELECT *%s FROM 'data_%q' WHERE 0;"
  1697         -            , p->zStateDb
  1698         -            , zTbl, (pIter->eType==RBU_PK_EXTERNAL ? ", 0 AS rbu_rowid" : "")
  1699         -            , zTbl
         1734  +            "SELECT *%s FROM '%q' WHERE 0;"
         1735  +            , p->zStateDb, pIter->zDataTbl
         1736  +            , (pIter->eType==RBU_PK_EXTERNAL ? ", 0 AS rbu_rowid" : "")
         1737  +            , pIter->zDataTbl
  1700   1738           );
  1701   1739   
  1702   1740           rbuMPrintfExec(p, p->dbMain,
  1703   1741               "CREATE TEMP TRIGGER rbu_delete_tr BEFORE DELETE ON \"%s%w\" "
  1704   1742               "BEGIN "
  1705   1743               "  SELECT rbu_tmp_insert(2, %s);"
  1706   1744               "END;"
................................................................................
  1857   1895     }
  1858   1896   
  1859   1897     if( p->rc==SQLITE_OK ){
  1860   1898       p->rc = sqlite3_create_function(p->dbMain, 
  1861   1899           "rbu_tmp_insert", -1, SQLITE_UTF8, (void*)p, rbuTmpInsertFunc, 0, 0
  1862   1900       );
  1863   1901     }
         1902  +
         1903  +  if( p->rc==SQLITE_OK ){
         1904  +    p->rc = sqlite3_create_function(p->dbRbu, 
         1905  +        "rbu_target_name", 1, SQLITE_UTF8, (void*)p, rbuTargetNameFunc, 0, 0
         1906  +    );
         1907  +  }
  1864   1908   
  1865   1909     if( p->rc==SQLITE_OK ){
  1866   1910       p->rc = sqlite3_file_control(p->dbMain, "main", SQLITE_FCNTL_RBU, (void*)p);
  1867   1911     }
  1868   1912     rbuMPrintfExec(p, p->dbMain, "SELECT * FROM sqlite_master");
  1869   1913   
  1870   1914     /* Mark the database file just opened as an RBU target database. If 
................................................................................
  2399   2443   
  2400   2444             if( pIter->bCleanup ){
  2401   2445               /* Clean up the rbu_tmp_xxx table for the previous table. It 
  2402   2446               ** cannot be dropped as there are currently active SQL statements.
  2403   2447               ** But the contents can be deleted.  */
  2404   2448               if( pIter->abIndexed ){
  2405   2449                 rbuMPrintfExec(p, p->dbRbu, 
  2406         -                  "DELETE FROM %s.'rbu_tmp_%q'", p->zStateDb, pIter->zTbl
         2450  +                  "DELETE FROM %s.'rbu_tmp_%q'", p->zStateDb, pIter->zDataTbl
  2407   2451                 );
  2408   2452               }
  2409   2453             }else{
  2410   2454               rbuObjIterPrepareAll(p, pIter, 0);
  2411   2455   
  2412   2456               /* Advance to the next row to process. */
  2413   2457               if( p->rc==SQLITE_OK ){

Changes to ext/rbu/sqlite3rbu.h.

    93     93   **
    94     94   ** Then the RBU database should contain:
    95     95   **
    96     96   **   CREATE TABLE data_t1(a INTEGER, b TEXT, c, rbu_control);
    97     97   **
    98     98   ** The order of the columns in the data_% table does not matter.
    99     99   **
          100  +** Instead of a regular table, the RBU database may also contain virtual
          101  +** tables or view named using the data_<target> naming scheme. 
          102  +**
          103  +** Instead of the plain data_<target> naming scheme, RBU database tables 
          104  +** may also be named data<integer>_<target>, where <integer> is any sequence
          105  +** of zero or more numeric characters (0-9). This can be significant because
          106  +** tables within the RBU database are always processed in order sorted by 
          107  +** name. By judicious selection of the the <integer> portion of the names
          108  +** of the RBU tables the user can therefore control the order in which they
          109  +** are processed. This can be useful, for example, to ensure that "external
          110  +** content" FTS4 tables are updated before their underlying content tables.
          111  +**
   100    112   ** If the target database table is a virtual table or a table that has no
   101    113   ** PRIMARY KEY declaration, the data_% table must also contain a column 
   102    114   ** named "rbu_rowid". This column is mapped to the tables implicit primary 
   103    115   ** key column - "rowid". Virtual tables for which the "rowid" column does 
   104    116   ** not function like a primary key value cannot be updated using RBU. For 
   105    117   ** example, if the target db contains either of the following:
   106    118   **