/ Check-in [1d9468d2]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Add a new row type to RBU (a peer of insert, update and delete) - "delete then insert".
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 1d9468d2427d2c9b7240b364554ac85a0b62fa44
User & Date: dan 2016-03-08 15:52:43
Context
2016-03-08
17:44
Modify the memjournal.c code to make it a bit smaller. Closed-Leaf check-in: d99ac415 user: dan tags: memjournal-exp
16:02
Remove an unused goto label in the RBU extension. check-in: 2179a106 user: drh tags: trunk
15:52
Add a new row type to RBU (a peer of insert, update and delete) - "delete then insert". check-in: 1d9468d2 user: dan tags: trunk
15:47
Add the SQLITE_DEFAULT_SYNCHRONOUS and SQLITE_DEFAULT_WAL_SYNCHRONOUS compile-time options. Automatically switch to the WAL_SYNCHRONOUS setting when first opening a WAL-mode database if the synchronous setting has not been previously set by the application. check-in: 5a847a67 user: drh tags: trunk
2016-03-07
17:39
Add a new row type to RBU (a peer of insert, update and delete) - "delete then insert". Closed-Leaf check-in: 169311c8 user: dan tags: rbu-delete-then-insert
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added ext/rbu/rbuC.test.

            1  +# 2016 March 7
            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  +# Tests for RBU focused on the REPLACE operation (rbu_control column
           12  +# contains integer value 2).
           13  +#
           14  +
           15  +source [file join [file dirname [info script]] rbu_common.tcl]
           16  +set ::testprefix rbuC
           17  +
           18  +#-------------------------------------------------------------------------
           19  +# This test is actually of an UPDATE directive. Just to establish that
           20  +# these work with UNIQUE indexes before preceding to REPLACE.
           21  +#
           22  +do_execsql_test 1.0 {
           23  +  CREATE TABLE t1(i INTEGER PRIMARY KEY, a, b, c UNIQUE);
           24  +  INSERT INTO t1 VALUES(1, 'a', 'b', 'c');
           25  +}
           26  +
           27  +forcedelete rbu.db
           28  +do_execsql_test 1.1 {
           29  +  ATTACH 'rbu.db' AS rbu;
           30  +  CREATE TABLE rbu.data_t1(i, a, b, c, rbu_control);
           31  +  INSERT INTO data_t1 VALUES(1, 'a', 'b', 'c', '.xxx');
           32  +}
           33  +
           34  +do_test 1.2 {
           35  +  step_rbu test.db rbu.db
           36  +} {SQLITE_DONE}
           37  +
           38  +do_execsql_test 1.3 {
           39  +  SELECT * FROM t1
           40  +} {
           41  +  1 a b c
           42  +}
           43  +
           44  +#-------------------------------------------------------------------------
           45  +#
           46  +foreach {tn schema} {
           47  +  1 {
           48  +    CREATE TABLE t1(i INTEGER PRIMARY KEY, a, b, c UNIQUE);
           49  +    CREATE INDEX t1a ON t1(a);
           50  +  }
           51  +  2 {
           52  +    CREATE TABLE t1(i PRIMARY KEY, a, b, c UNIQUE);
           53  +    CREATE INDEX t1a ON t1(a);
           54  +  }
           55  +  3 {
           56  +    CREATE TABLE t1(i PRIMARY KEY, a, b, c UNIQUE) WITHOUT ROWID;
           57  +    CREATE INDEX t1a ON t1(a);
           58  +  }
           59  +} {
           60  +  reset_db
           61  +  forcedelete rbu.db
           62  +  execsql $schema
           63  +
           64  +  do_execsql_test 2.$tn.0 {
           65  +    INSERT INTO t1 VALUES(1, 'a', 'b', 'c');
           66  +    INSERT INTO t1 VALUES(2, 'b', 'c', 'd');
           67  +    INSERT INTO t1 VALUES(3, 'c', 'd', 'e');
           68  +  }
           69  +  
           70  +  do_execsql_test 2.$tn.1 {
           71  +    ATTACH 'rbu.db' AS rbu;
           72  +    CREATE TABLE rbu.data_t1(i, a, b, c, rbu_control);
           73  +    INSERT INTO data_t1 VALUES(1, 1, 2, 3, 2);
           74  +    INSERT INTO data_t1 VALUES(3, 'c', 'd', 'e', 2);
           75  +    INSERT INTO data_t1 VALUES(4, 'd', 'e', 'f', 2);
           76  +  }
           77  +  
           78  +  do_test 2.$tn.2 {
           79  +    step_rbu test.db rbu.db
           80  +  } {SQLITE_DONE}
           81  +  
           82  +  do_execsql_test 2.$tn.3 {
           83  +    SELECT * FROM t1 ORDER BY i
           84  +  } {
           85  +    1 1 2 3
           86  +    2 b c d
           87  +    3 c d e
           88  +    4 d e f
           89  +  }
           90  +  
           91  +  integrity_check 2.$tn.4
           92  +}
           93  +
           94  +foreach {tn schema} {
           95  +  1 {
           96  +    CREATE TABLE t1(a, b, c UNIQUE);
           97  +    CREATE INDEX t1a ON t1(a);
           98  +  }
           99  +
          100  +  2 {
          101  +    CREATE VIRTUAL TABLE t1 USING fts5(a, b, c);
          102  +  }
          103  +} {
          104  +  if {$tn==2} { ifcapable !fts5 break }
          105  +  reset_db
          106  +  forcedelete rbu.db
          107  +  execsql $schema
          108  +
          109  +  do_execsql_test 3.$tn.0 {
          110  +    INSERT INTO t1 VALUES('a', 'b', 'c');
          111  +    INSERT INTO t1 VALUES('b', 'c', 'd');
          112  +    INSERT INTO t1 VALUES('c', 'd', 'e');
          113  +  }
          114  +  
          115  +  do_execsql_test 3.$tn.1 {
          116  +    ATTACH 'rbu.db' AS rbu;
          117  +    CREATE TABLE rbu.data_t1(rbu_rowid, a, b, c, rbu_control);
          118  +    INSERT INTO data_t1 VALUES(1, 1, 2, 3, 2);
          119  +    INSERT INTO data_t1 VALUES(3, 'c', 'd', 'e', 2);
          120  +    INSERT INTO data_t1 VALUES(4, 'd', 'e', 'f', 2);
          121  +  }
          122  +  
          123  +  do_test 3.$tn.2 {
          124  +    step_rbu test.db rbu.db
          125  +  } {SQLITE_DONE}
          126  +  
          127  +  do_execsql_test 3.$tn.3 {
          128  +    SELECT rowid, * FROM t1 ORDER BY 1
          129  +  } {
          130  +    1 1 2 3
          131  +    2 b c d
          132  +    3 c d e
          133  +    4 d e f
          134  +  }
          135  +  
          136  +  integrity_check 3.$tn.4
          137  +}
          138  +
          139  +
          140  +
          141  +finish_test
          142  +

Changes to ext/rbu/sqlite3rbu.c.

   276    276   
   277    277   /*
   278    278   ** Within the RBU_STAGE_OAL stage, each call to sqlite3rbu_step() performs
   279    279   ** one of the following operations.
   280    280   */
   281    281   #define RBU_INSERT     1          /* Insert on a main table b-tree */
   282    282   #define RBU_DELETE     2          /* Delete a row from a main table b-tree */
   283         -#define RBU_IDX_DELETE 3          /* Delete a row from an aux. index b-tree */
   284         -#define RBU_IDX_INSERT 4          /* Insert on an aux. index b-tree */
   285         -#define RBU_UPDATE     5          /* Update a row in a main table b-tree */
          283  +#define RBU_REPLACE    3          /* Delete and then insert a row */
          284  +#define RBU_IDX_DELETE 4          /* Delete a row from an aux. index b-tree */
          285  +#define RBU_IDX_INSERT 5          /* Insert on an aux. index b-tree */
   286    286   
          287  +#define RBU_UPDATE     6          /* Update a row in a main table b-tree */
   287    288   
   288    289   /*
   289    290   ** A single step of an incremental checkpoint - frame iWalFrame of the wal
   290    291   ** file should be copied to page iDbPage of the database file.
   291    292   */
   292    293   struct RbuFrame {
   293    294     u32 iDbPage;
................................................................................
  1905   1906             zSql = sqlite3_mprintf(
  1906   1907                 "SELECT %s, rbu_control FROM %s.'rbu_tmp_%q' ORDER BY %s%s",
  1907   1908                 zCollist, p->zStateDb, pIter->zDataTbl,
  1908   1909                 zCollist, zLimit
  1909   1910             );
  1910   1911           }else{
  1911   1912             zSql = sqlite3_mprintf(
         1913  +              "SELECT %s, rbu_control FROM %s.'rbu_tmp_%q' "
         1914  +              "UNION ALL "
  1912   1915                 "SELECT %s, rbu_control FROM '%q' "
  1913   1916                 "WHERE typeof(rbu_control)='integer' AND rbu_control!=1 "
  1914         -              "UNION ALL "
  1915         -              "SELECT %s, rbu_control FROM %s.'rbu_tmp_%q' "
  1916   1917                 "ORDER BY %s%s",
  1917         -              zCollist, pIter->zDataTbl, 
  1918   1918                 zCollist, p->zStateDb, pIter->zDataTbl, 
         1919  +              zCollist, pIter->zDataTbl, 
  1919   1920                 zCollist, zLimit
  1920   1921             );
  1921   1922           }
  1922   1923           p->rc = prepareFreeAndCollectError(p->dbRbu, &pIter->pSelect, pz, zSql);
  1923   1924         }
  1924   1925   
  1925   1926         sqlite3_free(zImposterCols);
................................................................................
  1977   1978               , (pIter->eType==RBU_PK_EXTERNAL ? ", 0 AS rbu_rowid" : "")
  1978   1979               , pIter->zDataTbl
  1979   1980           );
  1980   1981   
  1981   1982           rbuMPrintfExec(p, p->dbMain,
  1982   1983               "CREATE TEMP TRIGGER rbu_delete_tr BEFORE DELETE ON \"%s%w\" "
  1983   1984               "BEGIN "
  1984         -            "  SELECT rbu_tmp_insert(2, %s);"
         1985  +            "  SELECT rbu_tmp_insert(3, %s);"
  1985   1986               "END;"
  1986   1987   
  1987   1988               "CREATE TEMP TRIGGER rbu_update1_tr BEFORE UPDATE ON \"%s%w\" "
  1988   1989               "BEGIN "
  1989         -            "  SELECT rbu_tmp_insert(2, %s);"
         1990  +            "  SELECT rbu_tmp_insert(3, %s);"
  1990   1991               "END;"
  1991   1992   
  1992   1993               "CREATE TEMP TRIGGER rbu_update2_tr AFTER UPDATE ON \"%s%w\" "
  1993   1994               "BEGIN "
  1994         -            "  SELECT rbu_tmp_insert(3, %s);"
         1995  +            "  SELECT rbu_tmp_insert(4, %s);"
  1995   1996               "END;",
  1996   1997               zWrite, zTbl, zOldlist,
  1997   1998               zWrite, zTbl, zOldlist,
  1998   1999               zWrite, zTbl, zNewlist
  1999   2000           );
  2000   2001   
  2001   2002           if( pIter->eType==RBU_PK_EXTERNAL || pIter->eType==RBU_PK_NONE ){
................................................................................
  2505   2506   static int rbuStepType(sqlite3rbu *p, const char **pzMask){
  2506   2507     int iCol = p->objiter.nCol;     /* Index of rbu_control column */
  2507   2508     int res = 0;                    /* Return value */
  2508   2509   
  2509   2510     switch( sqlite3_column_type(p->objiter.pSelect, iCol) ){
  2510   2511       case SQLITE_INTEGER: {
  2511   2512         int iVal = sqlite3_column_int(p->objiter.pSelect, iCol);
  2512         -      if( iVal==0 ){
  2513         -        res = RBU_INSERT;
  2514         -      }else if( iVal==1 ){
  2515         -        res = RBU_DELETE;
  2516         -      }else if( iVal==2 ){
  2517         -        res = RBU_IDX_DELETE;
  2518         -      }else if( iVal==3 ){
  2519         -        res = RBU_IDX_INSERT;
         2513  +      switch( iVal ){
         2514  +        case 0: res = RBU_INSERT;     break;
         2515  +        case 1: res = RBU_DELETE;     break;
         2516  +        case 2: res = RBU_REPLACE;    break;
         2517  +        case 3: res = RBU_IDX_DELETE; break;
         2518  +        case 4: res = RBU_IDX_INSERT; break;
  2520   2519         }
  2521   2520         break;
  2522   2521       }
  2523   2522   
  2524   2523       case SQLITE_TEXT: {
  2525   2524         const unsigned char *z = sqlite3_column_text(p->objiter.pSelect, iCol);
  2526   2525         if( z==0 ){
................................................................................
  2550   2549   static void assertColumnName(sqlite3_stmt *pStmt, int iCol, const char *zName){
  2551   2550     const char *zCol = sqlite3_column_name(pStmt, iCol);
  2552   2551     assert( 0==sqlite3_stricmp(zName, zCol) );
  2553   2552   }
  2554   2553   #else
  2555   2554   # define assertColumnName(x,y,z)
  2556   2555   #endif
         2556  +
         2557  +/*
         2558  +** Argument eType must be one of RBU_INSERT, RBU_DELETE, RBU_IDX_INSERT or
         2559  +** RBU_IDX_DELETE. This function performs the work of a single
         2560  +** sqlite3rbu_step() call for the type of operation specified by eType.
         2561  +*/
         2562  +static void rbuStepOneOp(sqlite3rbu *p, int eType){
         2563  +  RbuObjIter *pIter = &p->objiter;
         2564  +  sqlite3_value *pVal;
         2565  +  sqlite3_stmt *pWriter;
         2566  +  int i;
         2567  +
         2568  +  assert( p->rc==SQLITE_OK );
         2569  +  assert( eType!=RBU_DELETE || pIter->zIdx==0 );
         2570  +
         2571  +  if( eType==RBU_IDX_DELETE || eType==RBU_DELETE ){
         2572  +    pWriter = pIter->pDelete;
         2573  +  }else{
         2574  +    pWriter = pIter->pInsert;
         2575  +  }
         2576  +
         2577  +  for(i=0; i<pIter->nCol; i++){
         2578  +    /* If this is an INSERT into a table b-tree and the table has an
         2579  +    ** explicit INTEGER PRIMARY KEY, check that this is not an attempt
         2580  +    ** to write a NULL into the IPK column. That is not permitted.  */
         2581  +    if( eType==RBU_INSERT 
         2582  +     && pIter->zIdx==0 && pIter->eType==RBU_PK_IPK && pIter->abTblPk[i] 
         2583  +     && sqlite3_column_type(pIter->pSelect, i)==SQLITE_NULL
         2584  +    ){
         2585  +      p->rc = SQLITE_MISMATCH;
         2586  +      p->zErrmsg = sqlite3_mprintf("datatype mismatch");
         2587  +      return;
         2588  +    }
         2589  +
         2590  +    if( eType==RBU_DELETE && pIter->abTblPk[i]==0 ){
         2591  +      continue;
         2592  +    }
         2593  +
         2594  +    pVal = sqlite3_column_value(pIter->pSelect, i);
         2595  +    p->rc = sqlite3_bind_value(pWriter, i+1, pVal);
         2596  +    if( p->rc ) return;
         2597  +  }
         2598  +  if( pIter->zIdx==0
         2599  +   && (pIter->eType==RBU_PK_VTAB || pIter->eType==RBU_PK_NONE) 
         2600  +  ){
         2601  +    /* For a virtual table, or a table with no primary key, the 
         2602  +    ** SELECT statement is:
         2603  +    **
         2604  +    **   SELECT <cols>, rbu_control, rbu_rowid FROM ....
         2605  +    **
         2606  +    ** Hence column_value(pIter->nCol+1).
         2607  +    */
         2608  +    assertColumnName(pIter->pSelect, pIter->nCol+1, "rbu_rowid");
         2609  +    pVal = sqlite3_column_value(pIter->pSelect, pIter->nCol+1);
         2610  +    p->rc = sqlite3_bind_value(pWriter, pIter->nCol+1, pVal);
         2611  +  }
         2612  +  if( p->rc==SQLITE_OK ){
         2613  +    sqlite3_step(pWriter);
         2614  +    p->rc = resetAndCollectError(pWriter, &p->zErrmsg);
         2615  +  }
         2616  +}
  2557   2617   
  2558   2618   /*
  2559   2619   ** This function does the work for an sqlite3rbu_step() call.
  2560   2620   **
  2561   2621   ** The object-iterator (p->objiter) currently points to a valid object,
  2562   2622   ** and the input cursor (p->objiter.pSelect) currently points to a valid
  2563   2623   ** input row. Perform whatever processing is required and return.
................................................................................
  2565   2625   ** If no  error occurs, SQLITE_OK is returned. Otherwise, an error code
  2566   2626   ** and message is left in the RBU handle and a copy of the error code
  2567   2627   ** returned.
  2568   2628   */
  2569   2629   static int rbuStep(sqlite3rbu *p){
  2570   2630     RbuObjIter *pIter = &p->objiter;
  2571   2631     const char *zMask = 0;
  2572         -  int i;
  2573   2632     int eType = rbuStepType(p, &zMask);
  2574   2633   
  2575   2634     if( eType ){
         2635  +    assert( eType==RBU_INSERT     || eType==RBU_DELETE
         2636  +         || eType==RBU_REPLACE    || eType==RBU_IDX_DELETE
         2637  +         || eType==RBU_IDX_INSERT || eType==RBU_UPDATE
         2638  +    );
  2576   2639       assert( eType!=RBU_UPDATE || pIter->zIdx==0 );
  2577   2640   
  2578   2641       if( pIter->zIdx==0 && eType==RBU_IDX_DELETE ){
  2579   2642         rbuBadControlError(p);
  2580   2643       }
  2581         -    else if( 
  2582         -        eType==RBU_INSERT 
  2583         -     || eType==RBU_DELETE
  2584         -     || eType==RBU_IDX_DELETE 
  2585         -     || eType==RBU_IDX_INSERT
  2586         -    ){
  2587         -      sqlite3_value *pVal;
  2588         -      sqlite3_stmt *pWriter;
  2589         -
  2590         -      assert( eType!=RBU_UPDATE );
  2591         -      assert( eType!=RBU_DELETE || pIter->zIdx==0 );
  2592         -
  2593         -      if( eType==RBU_IDX_DELETE || eType==RBU_DELETE ){
  2594         -        pWriter = pIter->pDelete;
  2595         -      }else{
  2596         -        pWriter = pIter->pInsert;
  2597         -      }
  2598         -
  2599         -      for(i=0; i<pIter->nCol; i++){
  2600         -        /* If this is an INSERT into a table b-tree and the table has an
  2601         -        ** explicit INTEGER PRIMARY KEY, check that this is not an attempt
  2602         -        ** to write a NULL into the IPK column. That is not permitted.  */
  2603         -        if( eType==RBU_INSERT 
  2604         -         && pIter->zIdx==0 && pIter->eType==RBU_PK_IPK && pIter->abTblPk[i] 
  2605         -         && sqlite3_column_type(pIter->pSelect, i)==SQLITE_NULL
  2606         -        ){
  2607         -          p->rc = SQLITE_MISMATCH;
  2608         -          p->zErrmsg = sqlite3_mprintf("datatype mismatch");
  2609         -          goto step_out;
  2610         -        }
  2611         -
  2612         -        if( eType==RBU_DELETE && pIter->abTblPk[i]==0 ){
  2613         -          continue;
  2614         -        }
  2615         -
  2616         -        pVal = sqlite3_column_value(pIter->pSelect, i);
  2617         -        p->rc = sqlite3_bind_value(pWriter, i+1, pVal);
  2618         -        if( p->rc ) goto step_out;
  2619         -      }
  2620         -      if( pIter->zIdx==0
  2621         -       && (pIter->eType==RBU_PK_VTAB || pIter->eType==RBU_PK_NONE) 
  2622         -      ){
  2623         -        /* For a virtual table, or a table with no primary key, the 
  2624         -        ** SELECT statement is:
  2625         -        **
  2626         -        **   SELECT <cols>, rbu_control, rbu_rowid FROM ....
  2627         -        **
  2628         -        ** Hence column_value(pIter->nCol+1).
  2629         -        */
  2630         -        assertColumnName(pIter->pSelect, pIter->nCol+1, "rbu_rowid");
  2631         -        pVal = sqlite3_column_value(pIter->pSelect, pIter->nCol+1);
  2632         -        p->rc = sqlite3_bind_value(pWriter, pIter->nCol+1, pVal);
  2633         -      }
  2634         -      if( p->rc==SQLITE_OK ){
  2635         -        sqlite3_step(pWriter);
  2636         -        p->rc = resetAndCollectError(pWriter, &p->zErrmsg);
  2637         -      }
  2638         -    }else{
         2644  +    else if( eType==RBU_REPLACE ){
         2645  +      if( pIter->zIdx==0 ) rbuStepOneOp(p, RBU_DELETE);
         2646  +      if( p->rc==SQLITE_OK ) rbuStepOneOp(p, RBU_INSERT);
         2647  +    }
         2648  +    else if( eType!=RBU_UPDATE ){
         2649  +      rbuStepOneOp(p, eType);
         2650  +    }
         2651  +    else{
  2639   2652         sqlite3_value *pVal;
  2640   2653         sqlite3_stmt *pUpdate = 0;
  2641   2654         assert( eType==RBU_UPDATE );
  2642   2655         rbuGetUpdateStmt(p, pIter, zMask, &pUpdate);
  2643   2656         if( pUpdate ){
         2657  +        int i;
  2644   2658           for(i=0; p->rc==SQLITE_OK && i<pIter->nCol; i++){
  2645   2659             char c = zMask[pIter->aiSrcOrder[i]];
  2646   2660             pVal = sqlite3_column_value(pIter->pSelect, i);
  2647   2661             if( pIter->abTblPk[i] || c!='.' ){
  2648   2662               p->rc = sqlite3_bind_value(pUpdate, i+1, pVal);
  2649   2663             }
  2650   2664           }