From b8293a5bee4d064b29760da72c2aa9c17485d1a1 Mon Sep 17 00:00:00 2001 From: dan Date: Fri, 26 Jul 2019 20:33:23 +0000 Subject: [PATCH] Update RBU so that it supports indexes on expressions. FossilOrigin-Name: 6bfa44da226fd758a2ee924d1e8e3a742b0635b6d3f922a4a7919cb14b2418e6 --- ext/rbu/rbuexpr.test | 93 ++++++++++++++++++++++++++++++++++++ ext/rbu/sqlite3rbu.c | 110 +++++++++++++++++++++++++++++++++---------- manifest | 13 ++--- manifest.uuid | 2 +- 4 files changed, 185 insertions(+), 33 deletions(-) create mode 100644 ext/rbu/rbuexpr.test diff --git a/ext/rbu/rbuexpr.test b/ext/rbu/rbuexpr.test new file mode 100644 index 0000000000..a392c4ed8c --- /dev/null +++ b/ext/rbu/rbuexpr.test @@ -0,0 +1,93 @@ +# 2014 August 30 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# + +source [file join [file dirname [info script]] rbu_common.tcl] +set ::testprefix rbuexpr + +db close +sqlite3_shutdown +sqlite3_config_uri 1 + +sqlite3 db test.db + +do_execsql_test 1.0 { + CREATE TABLE t1(a, b, c PRIMARY KEY); + CREATE INDEX i1 ON t1(a, null, b+1); + CREATE INDEX i2 ON t1(a+1, b+1, c+1); + + INSERT INTO t1 VALUES(1, 2, 3); + INSERT INTO t1 VALUES(4, 5, 6); + INSERT INTO t1 VALUES(7, 8, 9); + INSERT INTO t1 VALUES(10, 11, 12); + + PRAGMA integrity_check; +} {ok} + +forcedelete rbu.db +sqlite3 db2 rbu.db +do_execsql_test -db db2 1.1 { + CREATE TABLE data_t1(a, b, c, rbu_control); + INSERT INTO data_t1 VALUES(13, 14, 15, 0); + INSERT INTO data_t1 VALUES(NULL, NULL, 6, 1); + INSERT INTO data_t1 VALUES(NULL, 'three', 3, '.x.'); +} +db2 close +db close + +do_test 1.2 { + run_rbu test.db rbu.db +} {SQLITE_DONE} + +sqlite3 db test.db + +do_execsql_test 1.3 { + SELECT * FROM t1 WHERE a=4; +} + +integrity_check 1.4 + +#------------------------------------------------------------------------- +# +reset_db +do_execsql_test 2.0 { + CREATE TABLE t1(c1, c2, c3, i INTEGER PRIMARY KEY); + INSERT INTO t1 VALUES('one', 'one', 'one', 1); + INSERT INTO t1 VALUES('two', 'two', 'two', 2); + INSERT INTO t1 VALUES('three', 'three', 'three', 3); + INSERT INTO t1 VALUES('four', 'four', 'four', 4); + + CREATE INDEX i1 ON t1( substr(c1, 1, 2) ); + CREATE INDEX i2 ON t1( c1 || c2 || c3 ); + CREATE INDEX i3 ON t1( length(c1) + length(c2) - 1, c3||i ); +} + +forcedelete rbu.db +sqlite3 db2 rbu.db +do_execsql_test -db db2 2.1 { + CREATE TABLE data_t1(c1, c2, c3, i, rbu_control); + INSERT INTO data_t1 VALUES(NULL, NULL, NULL, 2, 1); + INSERT INTO data_t1 VALUES('thirty', NULL, NULL, 3, 'xx..'); + INSERT INTO data_t1 VALUES('five', 'five', 'five', 5, 0); +} +db2 close + +db close + +do_test 2.2 { + run_rbu test.db rbu.db +} {SQLITE_DONE} + +sqlite3 db test.db +integrity_check 2.3 + +finish_test + diff --git a/ext/rbu/sqlite3rbu.c b/ext/rbu/sqlite3rbu.c index b7f7dfdc2a..ee49ff361b 100644 --- a/ext/rbu/sqlite3rbu.c +++ b/ext/rbu/sqlite3rbu.c @@ -182,6 +182,7 @@ typedef struct RbuFrame RbuFrame; typedef struct RbuObjIter RbuObjIter; typedef struct RbuState RbuState; +typedef struct RbuSpan RbuSpan; typedef struct rbu_vfs rbu_vfs; typedef struct rbu_file rbu_file; typedef struct RbuUpdateStmt RbuUpdateStmt; @@ -226,6 +227,11 @@ struct RbuUpdateStmt { RbuUpdateStmt *pNext; }; +struct RbuSpan { + const char *zSpan; + int nSpan; +}; + /* ** An iterator of this type is used to iterate through all objects in ** the target database that require updating. For each such table, the @@ -275,6 +281,9 @@ struct RbuObjIter { sqlite3_stmt *pInsert; /* Statement for INSERT operations */ sqlite3_stmt *pDelete; /* Statement for DELETE ops */ sqlite3_stmt *pTmpInsert; /* Insert into rbu_tmp_$zDataTbl */ + int nIdxCol; + RbuSpan *aIdxCol; + char *zIdxSql; /* Last UPDATE used (for PK b-tree updates only), or NULL. */ RbuUpdateStmt *pRbuUpdate; @@ -809,6 +818,8 @@ static void rbuObjIterClearStatements(RbuObjIter *pIter){ sqlite3_free(pUp); pUp = pTmp; } + sqlite3_free(pIter->aIdxCol); + sqlite3_free(pIter->zIdxSql); pIter->pSelect = 0; pIter->pInsert = 0; @@ -816,6 +827,9 @@ static void rbuObjIterClearStatements(RbuObjIter *pIter){ pIter->pRbuUpdate = 0; pIter->pTmpInsert = 0; pIter->nCol = 0; + pIter->nIdxCol = 0; + pIter->aIdxCol = 0; + pIter->zIdxSql = 0; } /* @@ -1089,14 +1103,15 @@ static void rbuAllocateIterArrays(sqlite3rbu *p, RbuObjIter *pIter, int nCol){ static char *rbuStrndup(const char *zStr, int *pRc){ char *zRet = 0; - assert( *pRc==SQLITE_OK ); - if( zStr ){ - size_t nCopy = strlen(zStr) + 1; - zRet = (char*)sqlite3_malloc64(nCopy); - if( zRet ){ - memcpy(zRet, zStr, nCopy); - }else{ - *pRc = SQLITE_NOMEM; + if( *pRc==SQLITE_OK ){ + if( zStr ){ + size_t nCopy = strlen(zStr) + 1; + zRet = (char*)sqlite3_malloc64(nCopy); + if( zRet ){ + memcpy(zRet, zStr, nCopy); + }else{ + *pRc = SQLITE_NOMEM; + } } } @@ -1268,6 +1283,9 @@ static void rbuObjIterCacheIndexedCols(sqlite3rbu *p, RbuObjIter *pIter){ while( p->rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pXInfo) ){ int iCid = sqlite3_column_int(pXInfo, 1); if( iCid>=0 ) pIter->abIndexed[iCid] = 1; + if( iCid==-2 ){ + memset(pIter->abIndexed, 0x01, sizeof(u8)*pIter->nTblCol); + } } rbuFinalize(p, pXInfo); bIndex = 1; @@ -1682,26 +1700,34 @@ static char *rbuObjIterGetIndexCols( const char *zCol; const char *zType; - if( iCid<0 ){ - /* An integer primary key. If the table has an explicit IPK, use - ** its name. Otherwise, use "rbu_rowid". */ - if( pIter->eType==RBU_PK_IPK ){ - int i; - for(i=0; pIter->abTblPk[i]==0; i++); - assert( inTblCol ); - zCol = pIter->azTblCol[i]; - }else if( rbuIsVacuum(p) ){ - zCol = "_rowid_"; + if( iCid==-2 ){ + int iSeq = sqlite3_column_int(pXInfo, 0); + zRet = sqlite3_mprintf("%z%s(%.*s) COLLATE %Q", zRet, zCom, + pIter->aIdxCol[iSeq].nSpan, pIter->aIdxCol[iSeq].zSpan, zCollate + ); + zType = ""; + }else { + if( iCid<0 ){ + /* An integer primary key. If the table has an explicit IPK, use + ** its name. Otherwise, use "rbu_rowid". */ + if( pIter->eType==RBU_PK_IPK ){ + int i; + for(i=0; pIter->abTblPk[i]==0; i++); + assert( inTblCol ); + zCol = pIter->azTblCol[i]; + }else if( rbuIsVacuum(p) ){ + zCol = "_rowid_"; + }else{ + zCol = "rbu_rowid"; + } + zType = "INTEGER"; }else{ - zCol = "rbu_rowid"; + zCol = pIter->azTblCol[iCid]; + zType = pIter->azTblType[iCid]; } - zType = "INTEGER"; - }else{ - zCol = pIter->azTblCol[iCid]; - zType = pIter->azTblType[iCid]; + zRet = sqlite3_mprintf("%z%s\"%w\" COLLATE %Q", zRet, zCom,zCol,zCollate); } - zRet = sqlite3_mprintf("%z%s\"%w\" COLLATE %Q", zRet, zCom, zCol, zCollate); if( pIter->bUnique==0 || sqlite3_column_int(pXInfo, 5) ){ const char *zOrder = (bDesc ? " DESC" : ""); zImpPK = sqlite3_mprintf("%z%s\"rbu_imp_%d%w\"%s", @@ -2181,6 +2207,8 @@ static char *rbuObjIterGetIndexWhere(sqlite3rbu *p, RbuObjIter *pIter){ int rc = p->rc; char *zRet = 0; + assert( pIter->zIdxSql==0 && pIter->nIdxCol==0 && pIter->aIdxCol==0 ); + if( rc==SQLITE_OK ){ rc = prepareAndCollectError(p->dbMain, &pStmt, &p->zErrmsg, "SELECT trim(sql) FROM sqlite_master WHERE type='index' AND name=?" @@ -2190,21 +2218,50 @@ static char *rbuObjIterGetIndexWhere(sqlite3rbu *p, RbuObjIter *pIter){ int rc2; rc = sqlite3_bind_text(pStmt, 1, pIter->zIdx, -1, SQLITE_STATIC); if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){ - const char *zSql = (const char*)sqlite3_column_text(pStmt, 0); + char *zSql = (char*)sqlite3_column_text(pStmt, 0); + if( zSql ){ + pIter->zIdxSql = zSql = rbuStrndup(zSql, &rc); + } if( zSql ){ int nParen = 0; /* Number of open parenthesis */ int i; + int iIdxCol = 0; + int nIdxAlloc = 0; for(i=0; zSql[i]; i++){ char c = zSql[i]; + + /* If necessary, grow the pIter->aIdxCol[] array */ + if( iIdxCol==nIdxAlloc ){ + RbuSpan *aIdxCol = (RbuSpan*)sqlite3_realloc( + pIter->aIdxCol, (nIdxAlloc+16)*sizeof(RbuSpan) + ); + if( aIdxCol==0 ){ + rc = SQLITE_NOMEM; + break; + } + pIter->aIdxCol = aIdxCol; + nIdxAlloc += 16; + } + if( c=='(' ){ + if( nParen==0 ){ + assert( iIdxCol==0 ); + pIter->aIdxCol[0].zSpan = &zSql[i+1]; + } nParen++; } else if( c==')' ){ nParen--; if( nParen==0 ){ + int nSpan = &zSql[i] - pIter->aIdxCol[iIdxCol].zSpan; + pIter->aIdxCol[iIdxCol++].nSpan = nSpan; i++; break; } + }else if( c==',' && nParen==1 ){ + int nSpan = &zSql[i] - pIter->aIdxCol[iIdxCol].zSpan; + pIter->aIdxCol[iIdxCol++].nSpan = nSpan; + pIter->aIdxCol[iIdxCol].zSpan = &zSql[i+1]; }else if( c=='"' || c=='\'' || c=='`' ){ for(i++; 1; i++){ if( zSql[i]==c ){ @@ -2228,6 +2285,7 @@ static char *rbuObjIterGetIndexWhere(sqlite3rbu *p, RbuObjIter *pIter){ if( zSql[i] ){ zRet = rbuStrndup(&zSql[i], &rc); } + pIter->nIdxCol = iIdxCol; } } @@ -2272,11 +2330,11 @@ static int rbuObjIterPrepareAll( int nBind = 0; assert( pIter->eType!=RBU_PK_VTAB ); + zPart = rbuObjIterGetIndexWhere(p, pIter); zCollist = rbuObjIterGetIndexCols( p, pIter, &zImposterCols, &zImposterPK, &zWhere, &nBind ); zBind = rbuObjIterGetBindlist(p, nBind); - zPart = rbuObjIterGetIndexWhere(p, pIter); /* Create the imposter table used to write to this index. */ sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, p->dbMain, "main", 0, 1); diff --git a/manifest b/manifest index e370a31b6e..ddbd09314c 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Fix\sRBU\shandling\sof\spartial\sindexes\swith\scomments\sembedded\sin\stheir\sCREATE\sINDEX\sstatements. -D 2019-07-26T16:16:31.199 +C Update\sRBU\sso\sthat\sit\ssupports\sindexes\son\sexpressions. +D 2019-07-26T20:33:23.040 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724 @@ -345,6 +345,7 @@ F ext/rbu/rbucrash.test 000981a1fe8a6e4d9a684232f6a129e66a3ef595f5ed74655e2f9c68 F ext/rbu/rbucrash2.test efa143cc94228eb0266d3f1abfbee60a5838a84cef7cc3fcb8c145b74d96fd41 F ext/rbu/rbudiff.test 156957851136b63c143478518dc1bda6c832103cdbe8ac1d7cdd47edb3cbe0a3 F ext/rbu/rbudor.test e3e8623926012f43eebe51fedf06a102df2640750d971596b052495f2536db20 +F ext/rbu/rbuexpr.test 10d0420537c3bc7666e576d72adeffe7e86cfbb00dcc30aa9ce096c042415190 F ext/rbu/rbufault.test 2d7f567b79d558f6e093c58808cab4354f8a174e3802f69e7790a9689b3c09f8 F ext/rbu/rbufault2.test c81327a3ac2c385b9b954db3644d4e0df93eeebfc3de9f1f29975a1e73fd3d0c F ext/rbu/rbufault3.test b2fcc9db5c982b869f67d1d4688d8cb515d5b92f58011fff95665f2e62cec179 @@ -362,7 +363,7 @@ F ext/rbu/rbuvacuum.test 55e101e90168c2b31df6c9638fe73dc7f7cc666b6142266d1563697 F ext/rbu/rbuvacuum2.test b8e5b51dc8b2c0153373d024c0936be3f66f9234acbd6d0baab0869d56b14e6b F ext/rbu/rbuvacuum3.test 8addd82e4b83b4c93fa47428eae4fd0dbf410f8512c186f38e348feb49ba03dc F ext/rbu/rbuvacuum4.test a78898e438a44803eb2bc897ba3323373c9f277418e2d6d76e90f2f1dbccfd10 -F ext/rbu/sqlite3rbu.c 38c57ecab6697996cca56f56d4712ee0465a0957af1ea994a5a2ca815d5a06f6 +F ext/rbu/sqlite3rbu.c b254d13ae0058a07ee6322d01370fa15d4679e734a8962e923324e51b5cab556 F ext/rbu/sqlite3rbu.h 1dc88ab7bd32d0f15890ea08d23476c4198d3da3056985403991f8c9cd389812 F ext/rbu/test_rbu.c 03f6f177096a5f822d68d8e4069ad8907fe572c62ff2d19b141f59742821828a F ext/repair/README.md 92f5e8aae749a4dae14f02eea8e1bb42d4db2b6ce5e83dbcdd6b1446997e0c15 @@ -1837,7 +1838,7 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0 -P 16472112b6886ada18d45cfb409cca2e83dde5a8ba8a36d481e87b61100b0186 -R b8711fee1b5f80da15fd5f89a8e1d802 +P e1ccf211aa975a6688a27f7dc4cea80784ed8dcfe1950b2fa6eb0a3cb938838a +R d25f513b3e1c94b2e54a2b873df68d96 U dan -Z f0a7ed38f3e42e2ed2e83f589a12fac7 +Z 99e04d0115a0c5741a15100c0c189bee diff --git a/manifest.uuid b/manifest.uuid index 71225592f6..77ff56a6b1 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -e1ccf211aa975a6688a27f7dc4cea80784ed8dcfe1950b2fa6eb0a3cb938838a \ No newline at end of file +6bfa44da226fd758a2ee924d1e8e3a742b0635b6d3f922a4a7919cb14b2418e6 \ No newline at end of file -- 2.47.2