Index: ext/session/sessionstat1.test ================================================================== --- ext/session/sessionstat1.test +++ ext/session/sessionstat1.test @@ -118,8 +118,194 @@ SELECT * FROM sqlite_stat1 } { } do_execsql_test -db db2 2.5 { SELECT count(*) FROM t1 } 32 + +#------------------------------------------------------------------------- +db2 close +forcedelete test.db2 +reset_db +sqlite3 db2 test.db2 + +do_test 3.0 { + do_common_sql { + CREATE TABLE t1(a, b, c); + ANALYZE; + DELETE FROM sqlite_stat1; + } + execsql { + INSERT INTO t1 VALUES(1, 1, 1); + INSERT INTO t1 VALUES(2, 2, 2); + INSERT INTO t1 VALUES(3, 3, 3); + INSERT INTO t1 VALUES(4, 4, 4); + } +} {} + +do_iterator_test 3.1 {} { + ANALYZE +} { + {INSERT sqlite_stat1 0 XX. {} {t t1 b {} t 4}} +} +db null null +db2 null null +do_execsql_test 3.2 { + SELECT * FROM sqlite_stat1; +} {t1 null 4} +do_test 3.3 { + execsql { DELETE FROM sqlite_stat1 } + do_then_apply_sql { ANALYZE } + execsql { SELECT * FROM sqlite_stat1 } db2 +} {t1 null 4} +do_test 3.4 { + execsql { INSERT INTO t1 VALUES(5,5,5) } + do_then_apply_sql { ANALYZE } + execsql { SELECT * FROM sqlite_stat1 } db2 +} {t1 null 5} +do_test 3.5 { + do_then_apply_sql { DROP TABLE t1 } + execsql { SELECT * FROM sqlite_stat1 } db2 +} {} + +do_test 3.6.1 { + execsql { + CREATE TABLE t1(a, b, c); + CREATE TABLE t2(x, y, z); + INSERT INTO t1 VALUES(1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5); + INSERT INTO t2 SELECT * FROM t1; + DELETE FROM sqlite_stat1; + } + sqlite3session S db main + S attach sqlite_stat1 + execsql { ANALYZE } +} {} +do_changeset_test 3.6.2 S { + {INSERT sqlite_stat1 0 XX. {} {t t2 b {} t 5}} + {INSERT sqlite_stat1 0 XX. {} {t t1 b {} t 5}} +} +do_changeset_invert_test 3.6.3 S { + {DELETE sqlite_stat1 0 XX. {t t2 b {} t 5} {}} + {DELETE sqlite_stat1 0 XX. {t t1 b {} t 5} {}} +} +do_test 3.6.4 { S delete } {} + +proc sql_changeset_concat {args} { + foreach sql $args { + sqlite3session S db main + S attach sqlite_stat1 + execsql $sql + set change [S changeset] + S delete + + if {[info vars ret]!=""} { + set ret [sqlite3changeset_concat $ret $change] + } else { + set ret $change + } + } + + changeset_to_list $ret +} + +proc do_scc_test {tn args} { + uplevel [list \ + do_test $tn [concat sql_changeset_concat [lrange $args 0 end-1]] \ + [list {*}[ lindex $args end ]] + ] +} + +do_execsql_test 3.7.0 { + DELETE FROM sqlite_stat1; +} +do_scc_test 3.7.1 { + ANALYZE; +} { + INSERT INTO t2 VALUES(6,6,6); + ANALYZE; +} { + {INSERT sqlite_stat1 0 XX. {} {t t1 b {} t 5}} + {INSERT sqlite_stat1 0 XX. {} {t t2 b {} t 6}} +} + +#------------------------------------------------------------------------- +catch { db2 close } +reset_db +forcedelete test.db2 +sqlite3 db2 test.db2 + +do_test 4.1.0 { + do_common_sql { + CREATE TABLE t1(a, b); + CREATE INDEX i1 ON t1(a); + CREATE INDEX i2 ON t1(b); + INSERT INTO t1 VALUES(1,1), (2,2); + ANALYZE; + } + execsql { DELETE FROM sqlite_stat1 } +} {} + +do_test 4.1.1 { + execsql { INSERT INTO t1 VALUES(3,3); } + set C [changeset_from_sql {ANALYZE}] + set ::c [list] + proc xConflict {args} { + lappend ::c $args + return "OMIT" + } + sqlite3changeset_apply db2 $C xConflict + set ::c +} [list {*}{ + {INSERT sqlite_stat1 CONFLICT {t t1 t i1 t {3 1}} {t t1 t i1 t {2 1}}} + {INSERT sqlite_stat1 CONFLICT {t t1 t i2 t {3 1}} {t t1 t i2 t {2 1}}} +}] + +do_execsql_test -db db2 4.1.2 { + SELECT * FROM sqlite_stat1 ORDER BY 1,2; +} {t1 i1 {2 1} t1 i2 {2 1}} + +do_test 4.1.3 { + proc xConflict {args} { + return "REPLACE" + } + sqlite3changeset_apply db2 $C xConflict + execsql { SELECT * FROM sqlite_stat1 ORDER BY 1,2 } db2 +} {t1 i1 {3 1} t1 i2 {3 1}} + +do_test 4.2.0 { + do_common_sql { + DROP TABLE t1; + CREATE TABLE t3(x,y); + INSERT INTO t3 VALUES('a','a'); + INSERT INTO t3 VALUES('b','b'); + ANALYZE; + } + execsql { DELETE FROM sqlite_stat1 } +} {} +do_test 4.2.1 { + execsql { INSERT INTO t3 VALUES('c','c'); } + set C [changeset_from_sql {ANALYZE}] + set ::c [list] + proc xConflict {args} { + lappend ::c $args + return "OMIT" + } + sqlite3changeset_apply db2 $C xConflict + set ::c +} [list {*}{ + {INSERT sqlite_stat1 CONFLICT {t t3 b {} t 3} {t t3 b {} t 2}} +}] + +db2 null null +do_execsql_test -db db2 4.2.2 { + SELECT * FROM sqlite_stat1 ORDER BY 1,2; +} {t3 null 2} + +do_test 4.2.3 { + proc xConflict {args} { + return "REPLACE" + } + sqlite3changeset_apply db2 $C xConflict + execsql { SELECT * FROM sqlite_stat1 ORDER BY 1,2 } db2 +} {t3 null 3} finish_test Index: ext/session/sqlite3session.c ================================================================== --- ext/session/sqlite3session.c +++ ext/session/sqlite3session.c @@ -44,10 +44,11 @@ int bIndirect; /* True if all changes are indirect */ int bAutoAttach; /* True to auto-attach tables */ int rc; /* Non-zero if an error has occurred */ void *pFilterCtx; /* First argument to pass to xTableFilter */ int (*xTableFilter)(void *pCtx, const char *zTab); + sqlite3_value *pZeroBlob; /* Value containing X'' */ sqlite3_session *pNext; /* Next session object on same db. */ SessionTable *pTable; /* List of attached tables */ SessionHook hook; /* APIs to grab new and old data with */ }; @@ -111,10 +112,11 @@ */ struct SessionTable { SessionTable *pNext; char *zName; /* Local name of table */ int nCol; /* Number of columns in table zName */ + int bStat1; /* True if this is sqlite_stat1 */ const char **azCol; /* Column names */ u8 *abPK; /* Array of primary key flags */ int nEntry; /* Total number of entries in hash table */ int nChange; /* Size of apChange[] array */ SessionChange **apChange; /* Hash table buckets */ @@ -494,10 +496,11 @@ n = sqlite3_value_bytes(pVal); if( !z && (eType!=SQLITE_BLOB || n>0) ) return SQLITE_NOMEM; h = sessionHashAppendBlob(h, n, z); }else{ assert( eType==SQLITE_NULL ); + assert( pTab->bStat1==0 || i!=1 ); *pbNullPK = 1; } } } @@ -1045,14 +1048,58 @@ if( abPK[i] ){ pTab->abPK = abPK; break; } } + if( 0==sqlite3_stricmp("sqlite_stat1", pTab->zName) ){ + pTab->bStat1 = 1; + } } } return (pSession->rc || pTab->abPK==0); } + +/* +** Versions of the four methods in object SessionHook for use with the +** sqlite_stat1 table. The purpose of this is to substitute a zero-length +** blob each time a NULL value is read from the "idx" column of the +** sqlite_stat1 table. +*/ +typedef struct SessionStat1Ctx SessionStat1Ctx; +struct SessionStat1Ctx { + SessionHook hook; + sqlite3_session *pSession; +}; +static int sessionStat1Old(void *pCtx, int iCol, sqlite3_value **ppVal){ + SessionStat1Ctx *p = (SessionStat1Ctx*)pCtx; + sqlite3_value *pVal = 0; + int rc = p->hook.xOld(p->hook.pCtx, iCol, &pVal); + if( rc==SQLITE_OK && iCol==1 && sqlite3_value_type(pVal)==SQLITE_NULL ){ + pVal = p->pSession->pZeroBlob; + } + *ppVal = pVal; + return rc; +} +static int sessionStat1New(void *pCtx, int iCol, sqlite3_value **ppVal){ + SessionStat1Ctx *p = (SessionStat1Ctx*)pCtx; + sqlite3_value *pVal = 0; + int rc = p->hook.xNew(p->hook.pCtx, iCol, &pVal); + if( rc==SQLITE_OK && iCol==1 && sqlite3_value_type(pVal)==SQLITE_NULL ){ + pVal = p->pSession->pZeroBlob; + } + *ppVal = pVal; + return rc; +} +static int sessionStat1Count(void *pCtx){ + SessionStat1Ctx *p = (SessionStat1Ctx*)pCtx; + return p->hook.xCount(p->hook.pCtx); +} +static int sessionStat1Depth(void *pCtx){ + SessionStat1Ctx *p = (SessionStat1Ctx*)pCtx; + return p->hook.xDepth(p->hook.pCtx); +} + /* ** This function is only called from with a pre-update-hook reporting a ** change on table pTab (attached to session pSession). The type of change ** (UPDATE, INSERT, DELETE) is specified by the first argument. @@ -1066,10 +1113,11 @@ SessionTable *pTab /* Table that change applies to */ ){ int iHash; int bNull = 0; int rc = SQLITE_OK; + SessionStat1Ctx stat1; if( pSession->rc ) return; /* Load table details if required */ if( sessionInitTable(pSession, pTab) ) return; @@ -1084,10 +1132,29 @@ /* Grow the hash table if required */ if( sessionGrowHash(0, pTab) ){ pSession->rc = SQLITE_NOMEM; return; } + + if( pTab->bStat1 ){ + stat1.hook = pSession->hook; + stat1.pSession = pSession; + pSession->hook.pCtx = (void*)&stat1; + pSession->hook.xNew = sessionStat1New; + pSession->hook.xOld = sessionStat1Old; + pSession->hook.xCount = sessionStat1Count; + pSession->hook.xDepth = sessionStat1Depth; + if( pSession->pZeroBlob==0 ){ + sqlite3_value *p = sqlite3ValueNew(0); + if( p==0 ){ + rc = SQLITE_NOMEM; + goto error_out; + } + sqlite3ValueSetStr(p, 0, "", 0, SQLITE_STATIC); + pSession->pZeroBlob = p; + } + } /* Calculate the hash-key for this change. If the primary key of the row ** includes a NULL value, exit early. Such changes are ignored by the ** session module. */ rc = sessionPreupdateHash(pSession, pTab, op==SQLITE_INSERT, &iHash, &bNull); @@ -1174,10 +1241,13 @@ } } /* If an error has occurred, mark the session object as failed. */ error_out: + if( pTab->bStat1 ){ + pSession->hook = stat1.hook; + } if( rc!=SQLITE_OK ){ pSession->rc = rc; } } @@ -1635,10 +1705,11 @@ if( pHead ) sqlite3_preupdate_hook(db, xPreUpdate, (void*)pHead); break; } } sqlite3_mutex_leave(sqlite3_db_mutex(db)); + sqlite3ValueFree(pSession->pZeroBlob); /* Delete all attached table objects. And the contents of their ** associated hash-tables. */ sessionDeleteTable(pSession->pTable); @@ -2102,32 +2173,45 @@ const char **azCol, /* Names of table columns */ u8 *abPK, /* PRIMARY KEY array */ sqlite3_stmt **ppStmt /* OUT: Prepared SELECT statement */ ){ int rc = SQLITE_OK; - int i; - const char *zSep = ""; - SessionBuffer buf = {0, 0, 0}; - - sessionAppendStr(&buf, "SELECT * FROM ", &rc); - sessionAppendIdent(&buf, zDb, &rc); - sessionAppendStr(&buf, ".", &rc); - sessionAppendIdent(&buf, zTab, &rc); - sessionAppendStr(&buf, " WHERE ", &rc); - for(i=0; ipInsert, 0); } sqlite3_free(buf.aBuf); return rc; } + +static int sessionPrepare(sqlite3 *db, sqlite3_stmt **pp, const char *zSql){ + return sqlite3_prepare_v2(db, zSql, -1, pp, 0); +} + +/* +** Prepare statements for applying changes to the sqlite_stat1 table. +** These are similar to those created by sessionSelectRow(), +** sessionInsertRow(), sessionUpdateRow() and sessionDeleteRow() for +** other tables. +*/ +static int sessionStat1Sql(sqlite3 *db, SessionApplyCtx *p){ + int rc = sessionSelectRow(db, "sqlite_stat1", p); + if( rc==SQLITE_OK ){ + rc = sessionPrepare(db, &p->pInsert, + "INSERT INTO main.sqlite_stat1 VALUES(?1, " + "CASE WHEN length(?2)=0 AND typeof(?2)='blob' THEN NULL ELSE ?2 END, " + "?3)" + ); + } + if( rc==SQLITE_OK ){ + rc = sessionPrepare(db, &p->pUpdate, + "UPDATE main.sqlite_stat1 SET " + "tbl = CASE WHEN ?2 THEN ?3 ELSE tbl END, " + "idx = CASE WHEN ?5 THEN ?6 ELSE idx END, " + "stat = CASE WHEN ?8 THEN ?9 ELSE stat END " + "WHERE tbl=?1 AND idx IS " + "CASE WHEN length(?4)=0 AND typeof(?4)='blob' THEN NULL ELSE ?4 END " + "AND (?10 OR ?8=0 OR stat IS ?7)" + ); + } + if( rc==SQLITE_OK ){ + rc = sessionPrepare(db, &p->pDelete, + "DELETE FROM main.sqlite_stat1 WHERE tbl=?1 AND idx IS " + "CASE WHEN length(?2)=0 AND typeof(?2)='blob' THEN NULL ELSE ?2 END " + "AND (?4 OR stat IS ?3)" + ); + } + assert( rc==SQLITE_OK ); + return rc; +} /* ** A wrapper around sqlite3_bind_value() that detects an extra problem. ** See comments in the body of this function for details. */ @@ -3853,15 +3979,29 @@ ); } }else{ assert( op==SQLITE_INSERT ); - rc = sessionBindRow(pIter, sqlite3changeset_new, nCol, 0, p->pInsert); - if( rc!=SQLITE_OK ) return rc; + if( p->bStat1 ){ + /* Check if there is a conflicting row. For sqlite_stat1, this needs + ** to be done using a SELECT, as there is no PRIMARY KEY in the + ** database schema to throw an exception if a duplicate is inserted. */ + rc = sessionSeekToRow(p->db, pIter, p->abPK, p->pSelect); + if( rc==SQLITE_ROW ){ + rc = SQLITE_CONSTRAINT; + sqlite3_reset(p->pSelect); + } + } - sqlite3_step(p->pInsert); - rc = sqlite3_reset(p->pInsert); + if( rc==SQLITE_OK ){ + rc = sessionBindRow(pIter, sqlite3changeset_new, nCol, 0, p->pInsert); + if( rc!=SQLITE_OK ) return rc; + + sqlite3_step(p->pInsert); + rc = sqlite3_reset(p->pInsert); + } + if( (rc&0xff)==SQLITE_CONSTRAINT ){ rc = sessionConflictHandler( SQLITE_CHANGESET_CONFLICT, p, pIter, xConflict, pCtx, pbReplace ); } @@ -4090,16 +4230,24 @@ "primary key mismatch for table %s", zTab ); } else{ sApply.nCol = nCol; - if((rc = sessionSelectRow(db, zTab, &sApply)) - || (rc = sessionUpdateRow(db, zTab, &sApply)) - || (rc = sessionDeleteRow(db, zTab, &sApply)) - || (rc = sessionInsertRow(db, zTab, &sApply)) - ){ - break; + if( 0==sqlite3_stricmp(zTab, "sqlite_stat1") ){ + if( (rc = sessionStat1Sql(db, &sApply) ) ){ + break; + } + sApply.bStat1 = 1; + }else{ + if((rc = sessionSelectRow(db, zTab, &sApply)) + || (rc = sessionUpdateRow(db, zTab, &sApply)) + || (rc = sessionDeleteRow(db, zTab, &sApply)) + || (rc = sessionInsertRow(db, zTab, &sApply)) + ){ + break; + } + sApply.bStat1 = 0; } } nTab = sqlite3Strlen30(zTab); } } Index: ext/session/sqlite3session.h ================================================================== --- ext/session/sqlite3session.h +++ ext/session/sqlite3session.h @@ -145,10 +145,39 @@ ** Changes are not recorded for individual rows that have NULL values stored ** in one or more of their PRIMARY KEY columns. ** ** SQLITE_OK is returned if the call completes without error. Or, if an error ** occurs, an SQLite error code (e.g. SQLITE_NOMEM) is returned. +** +**

Special sqlite_stat1 Handling

+** +** As of SQLite version 3.22.0, the "sqlite_stat1" table is an exception to +** some of the rules above. In SQLite, the schema of sqlite_stat1 is: +** 
+**        CREATE TABLE sqlite_stat1(tbl,idx,stat)  
+**
+** +** Even though sqlite_stat1 does not have a PRIMARY KEY, changes are +** recorded for it as if the PRIMARY KEY is (tbl,idx). Additionally, changes +** are recorded for rows for which (idx IS NULL) is true. However, for such +** rows a zero-length blob (SQL value X'') is stored in the changeset or +** patchset instead of a NULL value. This allows such changesets to be +** manipulated by legacy implementations of sqlite3changeset_invert(), +** concat() and similar. +** +** The sqlite3changeset_apply() function automatically converts the +** zero-length blob back to a NULL value when updating the sqlite_stat1 +** table. However, if the application calls sqlite3changeset_new(), +** sqlite3changeset_old() or sqlite3changeset_conflict on a changeset +** iterator directly (including on a changeset iterator passed to a +** conflict-handler callback) then the X'' value is returned. The application +** must translate X'' to NULL itself if required. +** +** Legacy (older than 3.22.0) versions of the sessions module cannot capture +** changes made to the sqlite_stat1 table. Legacy versions of the +** sqlite3changeset_apply() function silently ignore any modifications to the +** sqlite_stat1 table that are part of a changeset or patchset. */ int sqlite3session_attach( sqlite3_session *pSession, /* Session object */ const char *zTab /* Table name */ ); Index: src/analyze.c ================================================================== --- src/analyze.c +++ src/analyze.c @@ -1307,10 +1307,13 @@ assert( "BBB"[0]==SQLITE_AFF_TEXT ); sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regTemp, "BBB", 0); sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regNewRowid); sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regTemp, regNewRowid); sqlite3VdbeChangeP5(v, OPFLAG_APPEND); +#ifdef SQLITE_ENABLE_PREUPDATE_HOOK + sqlite3VdbeChangeP4(v, -1, (char*)pStat1, P4_TABLE); +#endif sqlite3VdbeJumpHere(v, jZeroRows); } }