/ Check-in [bd46c442]
Login

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

Overview
Comment:Experimental change to include changes made to the sqlite_stat1 table in changesets generated by the sessions module. sqlite_stat1 entries in such changesets are ignored by legacy clients.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | sessions-stat1
Files: files | file ages | folders
SHA3-256: bd46c4429693545eb16db85692fc591ac529796aa746f5f21df1ce4380619320
User & Date: dan 2018-01-12 16:44:29
Context
2018-01-12
17:25
Include changes made to the sqlite_stat1 table in changesets generated by the sessions module. sqlite_stat1 entries in such changesets are ignored by legacy clients. check-in: 20642335 user: dan tags: trunk
16:44
Experimental change to include changes made to the sqlite_stat1 table in changesets generated by the sessions module. sqlite_stat1 entries in such changesets are ignored by legacy clients. Closed-Leaf check-in: bd46c442 user: dan tags: sessions-stat1
12:02
Add a test to ensure that the sqlite3changeset_apply() function ignores tables that do not have the expected primary keys. check-in: bf2daf06 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added ext/session/sessionstat1.test.

            1  +# 2018 January 12
            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  +
           13  +if {![info exists testdir]} {
           14  +  set testdir [file join [file dirname [info script]] .. .. test]
           15  +} 
           16  +source [file join [file dirname [info script]] session_common.tcl]
           17  +source $testdir/tester.tcl
           18  +ifcapable !session {finish_test; return}
           19  +
           20  +set testprefix sessionstat1
           21  +
           22  +do_execsql_test 1.0 {
           23  +  CREATE TABLE t1(a PRIMARY KEY, b, c);
           24  +  CREATE INDEX t1b ON t1(b);
           25  +  CREATE INDEX t1c ON t1(c);
           26  +
           27  +  WITH s(i) AS (
           28  +    SELECT 0 UNION ALL SELECT i+1 FROM s WHERE (i+1)<32
           29  +  )
           30  +  INSERT INTO t1 SELECT i, i%8, i%2 FROM s;
           31  +}
           32  +
           33  +do_iterator_test 1.1 {} {
           34  +  ANALYZE
           35  +} {
           36  +  {INSERT sqlite_stat1 0 XX. {} {t t1 t sqlite_autoindex_t1_1 t {32 1}}}
           37  +  {INSERT sqlite_stat1 0 XX. {} {t t1 t t1b t {32 4}}} 
           38  +  {INSERT sqlite_stat1 0 XX. {} {t t1 t t1c t {32 16}}}
           39  +}
           40  +
           41  +do_execsql_test 1.2 {
           42  +  WITH s(i) AS (
           43  +    SELECT 32 UNION ALL SELECT i+1 FROM s WHERE (i+1)<64
           44  +  )
           45  +  INSERT INTO t1 SELECT i, i%8, i%2 FROM s;
           46  +}
           47  +
           48  +do_iterator_test 1.3 {} {
           49  +  ANALYZE
           50  +} {
           51  +  {UPDATE sqlite_stat1 0 XX. {t t1 t sqlite_autoindex_t1_1 t {32 1}} {{} {} {} {} t {64 1}}} 
           52  +  {UPDATE sqlite_stat1 0 XX. {t t1 t t1b t {32 4}} {{} {} {} {} t {64 8}}} 
           53  +  {UPDATE sqlite_stat1 0 XX. {t t1 t t1c t {32 16}} {{} {} {} {} t {64 32}}}
           54  +}
           55  +
           56  +do_iterator_test 1.5 {} {
           57  +  DROP INDEX t1b;
           58  +} {
           59  +  {DELETE sqlite_stat1 0 XX. {t t1 t t1b t {64 8}} {}}
           60  +}
           61  +
           62  +do_iterator_test 1.6 {} {
           63  +  DROP TABLE t1;
           64  +} {
           65  +  {DELETE sqlite_stat1 0 XX. {t t1 t sqlite_autoindex_t1_1 t {64 1}} {}}
           66  +  {DELETE sqlite_stat1 0 XX. {t t1 t t1c t {64 32}} {}}
           67  +}
           68  +
           69  +#-------------------------------------------------------------------------
           70  +#
           71  +catch { db2 close }
           72  +forcedelete test.db2
           73  +sqlite3 db2 test.db2
           74  +
           75  +do_test 2.0 {
           76  +  do_common_sql {
           77  +    CREATE TABLE t1(a PRIMARY KEY, b, c);
           78  +    CREATE INDEX t1b ON t1(b);
           79  +    CREATE INDEX t1c ON t1(c);
           80  +    ANALYZE;
           81  +  }
           82  +} {}
           83  +
           84  +do_test 2.1 {
           85  +  do_then_apply_sql {
           86  +    WITH s(i) AS (
           87  +        SELECT 0 UNION ALL SELECT i+1 FROM s WHERE (i+1)<32
           88  +    )
           89  +    INSERT INTO t1 SELECT i, i%8, i%2 FROM s;
           90  +    ANALYZE;
           91  +  }
           92  +} {}
           93  +
           94  +do_execsql_test -db db2 2.2 {
           95  +  SELECT * FROM sqlite_stat1
           96  +} {
           97  +  t1 sqlite_autoindex_t1_1 {32 1} 
           98  +  t1 t1b {32 4} 
           99  +  t1 t1c {32 16}
          100  +}
          101  +
          102  +do_test 2.3 {
          103  +  do_then_apply_sql { DROP INDEX t1c }
          104  +} {}
          105  +
          106  +do_execsql_test -db db2 2.4 {
          107  +  SELECT * FROM sqlite_stat1
          108  +} {
          109  +  t1 sqlite_autoindex_t1_1 {32 1} 
          110  +  t1 t1b {32 4} 
          111  +}
          112  +
          113  +do_test 2.3 {
          114  +  do_then_apply_sql { DROP TABLE t1 }
          115  +} {}
          116  +
          117  +do_execsql_test -db db2 2.4 {
          118  +  SELECT * FROM sqlite_stat1
          119  +} {
          120  +}
          121  +
          122  +do_execsql_test -db db2 2.4 { SELECT count(*) FROM t1 } 32
          123  +
          124  +finish_test
          125  +

Changes to ext/session/sqlite3session.c.

   940    940     u8 *pAlloc = 0;
   941    941     char **azCol = 0;
   942    942     u8 *abPK = 0;
   943    943   
   944    944     assert( pazCol && pabPK );
   945    945   
   946    946     nThis = sqlite3Strlen30(zThis);
   947         -  zPragma = sqlite3_mprintf("PRAGMA '%q'.table_info('%q')", zDb, zThis);
          947  +  if( nThis==12 && 0==sqlite3_stricmp("sqlite_stat1", zThis) ){
          948  +    /* For sqlite_stat1, pretend that (tbl,idx) is the PRIMARY KEY. */
          949  +    zPragma = sqlite3_mprintf(
          950  +        "SELECT 0, 'tbl',  '', 0, '', 1     UNION ALL "
          951  +        "SELECT 1, 'idx',  '', 0, '', 2     UNION ALL "
          952  +        "SELECT 2, 'stat', '', 0, '', 0"
          953  +    );
          954  +  }else{
          955  +    zPragma = sqlite3_mprintf("PRAGMA '%q'.table_info('%q')", zDb, zThis);
          956  +  }
   948    957     if( !zPragma ) return SQLITE_NOMEM;
   949    958   
   950    959     rc = sqlite3_prepare_v2(db, zPragma, -1, &pStmt, 0);
   951    960     sqlite3_free(zPragma);
   952    961     if( rc!=SQLITE_OK ) return rc;
   953    962   
   954    963     nByte = nThis + 1;

Changes to src/analyze.c.

   230    230         aCreateTbl[i] = 0;
   231    231         sqlite3TableLock(pParse, iDb, aRoot[i], 1, zTab);
   232    232         if( zWhere ){
   233    233           sqlite3NestedParse(pParse,
   234    234              "DELETE FROM %Q.%s WHERE %s=%Q",
   235    235              pDb->zDbSName, zTab, zWhereType, zWhere
   236    236           );
          237  +#ifdef SQLITE_ENABLE_PREUPDATE_HOOK
          238  +      }else if( db->xPreUpdateCallback ){
          239  +        sqlite3NestedParse(pParse, "DELETE FROM %Q.%s", pDb->zDbSName, zTab);
          240  +#endif
   237    241         }else{
   238    242           /* The sqlite_stat[134] table already exists.  Delete all rows. */
   239    243           sqlite3VdbeAddOp2(v, OP_Clear, aRoot[i], iDb);
   240    244         }
   241    245       }
   242    246     }
   243    247   
................................................................................
   994    998     int regRowid = iMem++;       /* Rowid argument passed to stat_push() */
   995    999   #endif
   996   1000     int regTemp = iMem++;        /* Temporary use register */
   997   1001     int regTabname = iMem++;     /* Register containing table name */
   998   1002     int regIdxname = iMem++;     /* Register containing index name */
   999   1003     int regStat1 = iMem++;       /* Value for the stat column of sqlite_stat1 */
  1000   1004     int regPrev = iMem;          /* MUST BE LAST (see below) */
         1005  +#ifdef SQLITE_ENABLE_PREUPDATE_HOOK
         1006  +  Table *pStat1 = 0; 
         1007  +#endif
  1001   1008   
  1002   1009     pParse->nMem = MAX(pParse->nMem, iMem);
  1003   1010     v = sqlite3GetVdbe(pParse);
  1004   1011     if( v==0 || NEVER(pTab==0) ){
  1005   1012       return;
  1006   1013     }
  1007   1014     if( pTab->tnum==0 ){
................................................................................
  1018   1025     assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
  1019   1026   #ifndef SQLITE_OMIT_AUTHORIZATION
  1020   1027     if( sqlite3AuthCheck(pParse, SQLITE_ANALYZE, pTab->zName, 0,
  1021   1028         db->aDb[iDb].zDbSName ) ){
  1022   1029       return;
  1023   1030     }
  1024   1031   #endif
         1032  +
         1033  +#ifdef SQLITE_ENABLE_PREUPDATE_HOOK
         1034  +  if( db->xPreUpdateCallback ){
         1035  +    pStat1 = (Table*)sqlite3DbMallocZero(db, sizeof(Table) + 13);
         1036  +    if( pStat1==0 ) return;
         1037  +    pStat1->zName = (char*)&pStat1[1];
         1038  +    memcpy(pStat1->zName, "sqlite_stat1", 13);
         1039  +    pStat1->nCol = 3;
         1040  +    pStat1->iPKey = -1;
         1041  +    sqlite3VdbeAddOp4(pParse->pVdbe, OP_Noop, 0, 0, 0,(char*)pStat1,P4_DYNBLOB);
         1042  +  }
         1043  +#endif
  1025   1044   
  1026   1045     /* Establish a read-lock on the table at the shared-cache level. 
  1027   1046     ** Open a read-only cursor on the table. Also allocate a cursor number
  1028   1047     ** to use for scanning indexes (iIdxCur). No index cursor is opened at
  1029   1048     ** this time though.  */
  1030   1049     sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
  1031   1050     iTabCur = iTab++;
................................................................................
  1220   1239   
  1221   1240       /* Add the entry to the stat1 table. */
  1222   1241       callStatGet(v, regStat4, STAT_GET_STAT1, regStat1);
  1223   1242       assert( "BBB"[0]==SQLITE_AFF_TEXT );
  1224   1243       sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regTemp, "BBB", 0);
  1225   1244       sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regNewRowid);
  1226   1245       sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regTemp, regNewRowid);
         1246  +#ifdef SQLITE_ENABLE_PREUPDATE_HOOK
         1247  +    sqlite3VdbeChangeP4(v, -1, (char*)pStat1, P4_TABLE);
         1248  +#endif
  1227   1249       sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
  1228   1250   
  1229   1251       /* Add the entries to the stat3 or stat4 table. */
  1230   1252   #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
  1231   1253       {
  1232   1254         int regEq = regStat1;
  1233   1255         int regLt = regStat1+1;

Changes to src/delete.c.

   756    756     ** the update-hook is not invoked for rows removed by REPLACE, but the 
   757    757     ** pre-update-hook is.
   758    758     */ 
   759    759     if( pTab->pSelect==0 ){
   760    760       u8 p5 = 0;
   761    761       sqlite3GenerateRowIndexDelete(pParse, pTab, iDataCur, iIdxCur,0,iIdxNoSeek);
   762    762       sqlite3VdbeAddOp2(v, OP_Delete, iDataCur, (count?OPFLAG_NCHANGE:0));
   763         -    if( pParse->nested==0 ){
          763  +    if( pParse->nested==0 || 0==sqlite3_stricmp(pTab->zName, "sqlite_stat1") ){
   764    764         sqlite3VdbeAppendP4(v, (char*)pTab, P4_TABLE);
   765    765       }
   766    766       if( eMode!=ONEPASS_OFF ){
   767    767         sqlite3VdbeChangeP5(v, OPFLAG_AUXDELETE);
   768    768       }
   769    769       if( iIdxNoSeek>=0 && iIdxNoSeek!=iDataCur ){
   770    770         sqlite3VdbeAddOp1(v, OP_Delete, iIdxNoSeek);

Changes to src/vdbe.c.

  4479   4479         (pOp->p5 & (OPFLAG_APPEND|OPFLAG_SAVEPOSITION)), seekResult
  4480   4480     );
  4481   4481     pC->deferredMoveto = 0;
  4482   4482     pC->cacheStatus = CACHE_STALE;
  4483   4483   
  4484   4484     /* Invoke the update-hook if required. */
  4485   4485     if( rc ) goto abort_due_to_error;
  4486         -  if( db->xUpdateCallback && op ){
         4486  +  assert( !op || pTab->aCol || !sqlite3_stricmp(pTab->zName,"sqlite_stat1") );
         4487  +  if( db->xUpdateCallback && op && pTab->aCol ){
  4487   4488       db->xUpdateCallback(db->pUpdateArg, op, zDb, pTab->zName, x.nKey);
  4488   4489     }
  4489   4490     break;
  4490   4491   }
  4491   4492   
  4492   4493   /* Opcode: Delete P1 P2 P3 P4 P5
  4493   4494   **

Changes to src/vdbe.h.

   123    123   #define P4_EXPR       (-10) /* P4 is a pointer to an Expr tree */
   124    124   #define P4_MEM        (-11) /* P4 is a pointer to a Mem*    structure */
   125    125   #define P4_VTAB       (-12) /* P4 is a pointer to an sqlite3_vtab structure */
   126    126   #define P4_REAL       (-13) /* P4 is a 64-bit floating point value */
   127    127   #define P4_INT64      (-14) /* P4 is a 64-bit signed integer */
   128    128   #define P4_INTARRAY   (-15) /* P4 is a vector of 32-bit integers */
   129    129   #define P4_FUNCCTX    (-16) /* P4 is a pointer to an sqlite3_context object */
          130  +#define P4_DYNBLOB    (-17) /* Pointer to memory from sqliteMalloc() */
   130    131   
   131    132   /* Error message codes for OP_Halt */
   132    133   #define P5_ConstraintNotNull 1
   133    134   #define P5_ConstraintUnique  2
   134    135   #define P5_ConstraintCheck   3
   135    136   #define P5_ConstraintFK      4
   136    137   

Changes to src/vdbeaux.c.

   862    862       case P4_FUNCCTX: {
   863    863         freeP4FuncCtx(db, (sqlite3_context*)p4);
   864    864         break;
   865    865       }
   866    866       case P4_REAL:
   867    867       case P4_INT64:
   868    868       case P4_DYNAMIC:
          869  +    case P4_DYNBLOB:
   869    870       case P4_INTARRAY: {
   870    871         sqlite3DbFree(db, p4);
   871    872         break;
   872    873       }
   873    874       case P4_KEYINFO: {
   874    875         if( db->pnBytesFreed==0 ) sqlite3KeyInfoUnref((KeyInfo*)p4);
   875    876         break;
................................................................................
  1403   1404         sqlite3StrAccumAppend(&x, "]", 1);
  1404   1405         break;
  1405   1406       }
  1406   1407       case P4_SUBPROGRAM: {
  1407   1408         sqlite3XPrintf(&x, "program");
  1408   1409         break;
  1409   1410       }
         1411  +    case P4_DYNBLOB:
  1410   1412       case P4_ADVANCE: {
  1411   1413         zTemp[0] = 0;
  1412   1414         break;
  1413   1415       }
  1414   1416       case P4_TABLE: {
  1415   1417         sqlite3XPrintf(&x, "%s", pOp->p4.pTab->zName);
  1416   1418         break;

Changes to test/hook.test.

   901    901   } {
   902    902     INSERT main t3 1 1 0 {} 1
   903    903   }
   904    904   do_execsql_test 10.2 { SELECT * FROM t3 } {{} 1}
   905    905   do_preupdate_test 10.3 {
   906    906     DELETE FROM t3 WHERE b=1
   907    907   } {DELETE main t3 1 1 0 {} 1}
          908  +
          909  +#-------------------------------------------------------------------------
          910  +# Test that the "update" hook is not fired for operations on the 
          911  +# sqlite_stat1 table performed by ANALYZE, even if a pre-update hook is
          912  +# registered.
          913  +ifcapable analyze {
          914  +  reset_db
          915  +  do_execsql_test 11.1 {
          916  +    CREATE TABLE t1(a, b);
          917  +    CREATE INDEX idx1 ON t1(a);
          918  +    CREATE INDEX idx2 ON t1(b);
          919  +
          920  +    INSERT INTO t1 VALUES(1, 2);
          921  +    INSERT INTO t1 VALUES(3, 4);
          922  +    INSERT INTO t1 VALUES(5, 6);
          923  +    INSERT INTO t1 VALUES(7, 8);
          924  +  }
          925  +
          926  +  db preupdate hook preupdate_cb
          927  +  db update_hook update_cb
          928  +
          929  +  proc preupdate_cb {args} { lappend ::res "preupdate" $args }
          930  +  proc update_cb {args} { lappend ::res "update" $args }
          931  +
          932  +  set ::res [list]
          933  +  do_test 11.2 {
          934  +    execsql ANALYZE
          935  +    set ::res
          936  +  } [list {*}{
          937  +    preupdate {INSERT main sqlite_stat1 1 1}
          938  +    preupdate {INSERT main sqlite_stat1 2 2}
          939  +  }]
          940  +
          941  +  do_execsql_test 11.3 {
          942  +    INSERT INTO t1 VALUES(9, 10);
          943  +    INSERT INTO t1 VALUES(11, 12);
          944  +    INSERT INTO t1 VALUES(13, 14);
          945  +    INSERT INTO t1 VALUES(15, 16);
          946  +  }
          947  +
          948  +  set ::res [list]
          949  +  do_test 11.4 {
          950  +    execsql ANALYZE
          951  +    set ::res
          952  +  } [list {*}{
          953  +    preupdate {DELETE main sqlite_stat1 1 1}
          954  +    preupdate {DELETE main sqlite_stat1 2 2}
          955  +    preupdate {INSERT main sqlite_stat1 1 1}
          956  +    preupdate {INSERT main sqlite_stat1 2 2}
          957  +  }]
          958  +}
   908    959   
   909    960   
   910    961   finish_test