Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix the sqlite3_dbpage virtual table so that it can read and write from any attached database. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
d4f893e1ae53a0445939ea2920af87d2 |
| User & Date: | drh 2017-10-25 19:18:33 |
Context
|
2017-10-26
| ||
| 15:21 | Fix a bug causing "make amalgmation-autoconf" to create an invalid package if either the current or parent directory contains a file named install.sh or install-sh. (check-in: e1faa785 user: dan tags: trunk) | |
|
2017-10-25
| ||
| 23:28 | Use extra locks to prevent a readonly_shm=1 process from connecting to a WAL-mode database if there are no writers. (check-in: 35d97908 user: drh tags: readonly-wal-recovery) | |
| 19:18 | Fix the sqlite3_dbpage virtual table so that it can read and write from any attached database. (check-in: d4f893e1 user: drh tags: trunk) | |
| 18:17 | Add tests cases and fix minor issues in the rtreecheck() function. (check-in: d6130cd2 user: dan tags: trunk) | |
Changes
Changes to src/dbpage.c.
38 38 typedef struct DbpageTable DbpageTable; 39 39 typedef struct DbpageCursor DbpageCursor; 40 40 41 41 struct DbpageCursor { 42 42 sqlite3_vtab_cursor base; /* Base class. Must be first */ 43 43 int pgno; /* Current page number */ 44 44 int mxPgno; /* Last page to visit on this scan */ 45 + Pager *pPager; /* Pager being read/written */ 46 + DbPage *pPage1; /* Page 1 of the database */ 47 + int iDb; /* Index of database to analyze */ 48 + int szPage; /* Size of each page in bytes */ 45 49 }; 46 50 47 51 struct DbpageTable { 48 52 sqlite3_vtab base; /* Base class. Must be first */ 49 53 sqlite3 *db; /* The database */ 50 - Pager *pPager; /* Pager being read/written */ 51 - int iDb; /* Index of database to analyze */ 52 - int szPage; /* Size of each page in bytes */ 53 - int nPage; /* Number of pages in the file */ 54 54 }; 55 + 56 +/* Columns */ 57 +#define DBPAGE_COLUMN_PGNO 0 58 +#define DBPAGE_COLUMN_DATA 1 59 +#define DBPAGE_COLUMN_SCHEMA 2 60 + 61 + 55 62 56 63 /* 57 64 ** Connect to or create a dbpagevfs virtual table. 58 65 */ 59 66 static int dbpageConnect( 60 67 sqlite3 *db, 61 68 void *pAux, 62 69 int argc, const char *const*argv, 63 70 sqlite3_vtab **ppVtab, 64 71 char **pzErr 65 72 ){ 66 73 DbpageTable *pTab = 0; 67 74 int rc = SQLITE_OK; 68 - int iDb; 69 75 70 - if( argc>=4 ){ 71 - Token nm; 72 - sqlite3TokenInit(&nm, (char*)argv[3]); 73 - iDb = sqlite3FindDb(db, &nm); 74 - if( iDb<0 ){ 75 - *pzErr = sqlite3_mprintf("no such schema: %s", argv[3]); 76 - return SQLITE_ERROR; 77 - } 78 - }else{ 79 - iDb = 0; 80 - } 81 76 rc = sqlite3_declare_vtab(db, 82 77 "CREATE TABLE x(pgno INTEGER PRIMARY KEY, data BLOB, schema HIDDEN)"); 83 78 if( rc==SQLITE_OK ){ 84 79 pTab = (DbpageTable *)sqlite3_malloc64(sizeof(DbpageTable)); 85 80 if( pTab==0 ) rc = SQLITE_NOMEM_BKPT; 86 81 } 87 82 88 83 assert( rc==SQLITE_OK || pTab==0 ); 89 84 if( rc==SQLITE_OK ){ 90 - Btree *pBt = db->aDb[iDb].pBt; 91 85 memset(pTab, 0, sizeof(DbpageTable)); 92 86 pTab->db = db; 93 - pTab->iDb = iDb; 94 - pTab->pPager = pBt ? sqlite3BtreePager(pBt) : 0; 95 87 } 96 88 97 89 *ppVtab = (sqlite3_vtab*)pTab; 98 90 return rc; 99 91 } 100 92 101 93 /* ................................................................................ 105 97 sqlite3_free(pVtab); 106 98 return SQLITE_OK; 107 99 } 108 100 109 101 /* 110 102 ** idxNum: 111 103 ** 112 -** 0 full table scan 113 -** 1 pgno=?1 104 +** 0 schema=main, full table scan 105 +** 1 schema=main, pgno=?1 106 +** 2 schema=?1, full table scan 107 +** 3 schema=?1, pgno=?2 114 108 */ 115 109 static int dbpageBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){ 116 110 int i; 117 - pIdxInfo->estimatedCost = 1.0e6; /* Initial cost estimate */ 111 + int iPlan = 0; 112 + 113 + /* If there is a schema= constraint, it must be honored. Report a 114 + ** ridiculously large estimated cost if the schema= constraint is 115 + ** unavailable 116 + */ 117 + for(i=0; i<pIdxInfo->nConstraint; i++){ 118 + struct sqlite3_index_constraint *p = &pIdxInfo->aConstraint[i]; 119 + if( p->iColumn!=DBPAGE_COLUMN_SCHEMA ) continue; 120 + if( p->op!=SQLITE_INDEX_CONSTRAINT_EQ ) continue; 121 + if( !p->usable ){ 122 + /* No solution. Use the default SQLITE_BIG_DBL cost */ 123 + pIdxInfo->estimatedRows = 0x7fffffff; 124 + return SQLITE_OK; 125 + } 126 + iPlan = 2; 127 + pIdxInfo->aConstraintUsage[i].argvIndex = 1; 128 + pIdxInfo->aConstraintUsage[i].omit = 1; 129 + break; 130 + } 131 + 132 + /* If we reach this point, it means that either there is no schema= 133 + ** constraint (in which case we use the "main" schema) or else the 134 + ** schema constraint was accepted. Lower the estimated cost accordingly 135 + */ 136 + pIdxInfo->estimatedCost = 1.0e6; 137 + 138 + /* Check for constraints against pgno */ 118 139 for(i=0; i<pIdxInfo->nConstraint; i++){ 119 140 struct sqlite3_index_constraint *p = &pIdxInfo->aConstraint[i]; 120 141 if( p->usable && p->iColumn<=0 && p->op==SQLITE_INDEX_CONSTRAINT_EQ ){ 121 142 pIdxInfo->estimatedRows = 1; 122 143 pIdxInfo->idxFlags = SQLITE_INDEX_SCAN_UNIQUE; 123 144 pIdxInfo->estimatedCost = 1.0; 124 - pIdxInfo->idxNum = 1; 125 - pIdxInfo->aConstraintUsage[i].argvIndex = 1; 145 + pIdxInfo->aConstraintUsage[i].argvIndex = iPlan ? 2 : 1; 126 146 pIdxInfo->aConstraintUsage[i].omit = 1; 147 + iPlan |= 1; 127 148 break; 128 149 } 129 150 } 151 + pIdxInfo->idxNum = iPlan; 152 + 130 153 if( pIdxInfo->nOrderBy>=1 131 154 && pIdxInfo->aOrderBy[0].iColumn<=0 132 155 && pIdxInfo->aOrderBy[0].desc==0 133 156 ){ 134 157 pIdxInfo->orderByConsumed = 1; 135 158 } 136 159 return SQLITE_OK; ................................................................................ 156 179 } 157 180 158 181 /* 159 182 ** Close a dbpagevfs cursor. 160 183 */ 161 184 static int dbpageClose(sqlite3_vtab_cursor *pCursor){ 162 185 DbpageCursor *pCsr = (DbpageCursor *)pCursor; 186 + if( pCsr->pPage1 ) sqlite3PagerUnrefPageOne(pCsr->pPage1); 163 187 sqlite3_free(pCsr); 164 188 return SQLITE_OK; 165 189 } 166 190 167 191 /* 168 192 ** Move a dbpagevfs cursor to the next entry in the file. 169 193 */ ................................................................................ 175 199 } 176 200 177 201 static int dbpageEof(sqlite3_vtab_cursor *pCursor){ 178 202 DbpageCursor *pCsr = (DbpageCursor *)pCursor; 179 203 return pCsr->pgno > pCsr->mxPgno; 180 204 } 181 205 206 +/* 207 +** idxNum: 208 +** 209 +** 0 schema=main, full table scan 210 +** 1 schema=main, pgno=?1 211 +** 2 schema=?1, full table scan 212 +** 3 schema=?1, pgno=?2 213 +** 214 +** idxStr is not used 215 +*/ 182 216 static int dbpageFilter( 183 217 sqlite3_vtab_cursor *pCursor, 184 218 int idxNum, const char *idxStr, 185 219 int argc, sqlite3_value **argv 186 220 ){ 187 221 DbpageCursor *pCsr = (DbpageCursor *)pCursor; 188 222 DbpageTable *pTab = (DbpageTable *)pCursor->pVtab; 189 223 int rc = SQLITE_OK; 190 - Btree *pBt = pTab->db->aDb[pTab->iDb].pBt; 224 + sqlite3 *db = pTab->db; 225 + Btree *pBt; 226 + 227 + /* Default setting is no rows of result */ 228 + pCsr->pgno = 1; 229 + pCsr->mxPgno = 0; 191 230 192 - pTab->szPage = sqlite3BtreeGetPageSize(pBt); 193 - pTab->nPage = sqlite3BtreeLastPage(pBt); 194 - if( idxNum==1 ){ 195 - pCsr->pgno = sqlite3_value_int(argv[0]); 196 - if( pCsr->pgno<1 || pCsr->pgno>pTab->nPage ){ 231 + if( idxNum & 2 ){ 232 + const char *zSchema; 233 + assert( argc>=1 ); 234 + zSchema = (const char*)sqlite3_value_text(argv[0]); 235 + pCsr->iDb = sqlite3FindDbName(db, zSchema); 236 + if( pCsr->iDb<0 ) return SQLITE_OK; 237 + }else{ 238 + pCsr->iDb = 0; 239 + } 240 + pBt = db->aDb[pCsr->iDb].pBt; 241 + if( pBt==0 ) return SQLITE_OK; 242 + pCsr->pPager = sqlite3BtreePager(pBt); 243 + pCsr->szPage = sqlite3BtreeGetPageSize(pBt); 244 + pCsr->mxPgno = sqlite3BtreeLastPage(pBt); 245 + if( idxNum & 1 ){ 246 + assert( argc>(idxNum>>1) ); 247 + pCsr->pgno = sqlite3_value_int(argv[idxNum>>1]); 248 + if( pCsr->pgno<1 || pCsr->pgno>pCsr->mxPgno ){ 197 249 pCsr->pgno = 1; 198 250 pCsr->mxPgno = 0; 199 251 }else{ 200 252 pCsr->mxPgno = pCsr->pgno; 201 253 } 202 254 }else{ 203 - pCsr->pgno = 1; 204 - pCsr->mxPgno = pTab->nPage; 255 + assert( pCsr->pgno==1 ); 205 256 } 257 + rc = sqlite3PagerGet(pCsr->pPager, 1, &pCsr->pPage1, 0); 206 258 return rc; 207 259 } 208 260 209 261 static int dbpageColumn( 210 262 sqlite3_vtab_cursor *pCursor, 211 263 sqlite3_context *ctx, 212 264 int i 213 265 ){ 214 266 DbpageCursor *pCsr = (DbpageCursor *)pCursor; 215 - DbpageTable *pTab = (DbpageTable *)pCursor->pVtab; 216 267 int rc = SQLITE_OK; 217 268 switch( i ){ 218 269 case 0: { /* pgno */ 219 270 sqlite3_result_int(ctx, pCsr->pgno); 220 271 break; 221 272 } 222 273 case 1: { /* data */ 223 274 DbPage *pDbPage = 0; 224 - rc = sqlite3PagerGet(pTab->pPager, pCsr->pgno, (DbPage**)&pDbPage, 0); 275 + rc = sqlite3PagerGet(pCsr->pPager, pCsr->pgno, (DbPage**)&pDbPage, 0); 225 276 if( rc==SQLITE_OK ){ 226 - sqlite3_result_blob(ctx, sqlite3PagerGetData(pDbPage), pTab->szPage, 277 + sqlite3_result_blob(ctx, sqlite3PagerGetData(pDbPage), pCsr->szPage, 227 278 SQLITE_TRANSIENT); 228 279 } 229 280 sqlite3PagerUnref(pDbPage); 230 281 break; 231 282 } 232 283 default: { /* schema */ 233 284 sqlite3 *db = sqlite3_context_db_handle(ctx); 234 - sqlite3_result_text(ctx, db->aDb[pTab->iDb].zDbSName, -1, SQLITE_STATIC); 285 + sqlite3_result_text(ctx, db->aDb[pCsr->iDb].zDbSName, -1, SQLITE_STATIC); 235 286 break; 236 287 } 237 288 } 238 289 return SQLITE_OK; 239 290 } 240 291 241 292 static int dbpageRowid(sqlite3_vtab_cursor *pCursor, sqlite_int64 *pRowid){ ................................................................................ 251 302 sqlite_int64 *pRowid 252 303 ){ 253 304 DbpageTable *pTab = (DbpageTable *)pVtab; 254 305 int pgno; 255 306 DbPage *pDbPage = 0; 256 307 int rc = SQLITE_OK; 257 308 char *zErr = 0; 309 + const char *zSchema; 310 + int iDb; 311 + Btree *pBt; 312 + Pager *pPager; 313 + int szPage; 258 314 259 315 if( argc==1 ){ 260 316 zErr = "cannot delete"; 261 317 goto update_fail; 262 318 } 263 319 pgno = sqlite3_value_int(argv[0]); 264 - if( pgno<1 || pgno>pTab->nPage ){ 265 - zErr = "bad page number"; 266 - goto update_fail; 267 - } 268 320 if( sqlite3_value_int(argv[1])!=pgno ){ 269 321 zErr = "cannot insert"; 270 322 goto update_fail; 271 323 } 324 + zSchema = (const char*)sqlite3_value_text(argv[4]); 325 + iDb = zSchema ? sqlite3FindDbName(pTab->db, zSchema) : -1; 326 + if( iDb<0 ){ 327 + zErr = "no such schema"; 328 + goto update_fail; 329 + } 330 + pBt = pTab->db->aDb[iDb].pBt; 331 + if( pgno<1 || pBt==0 || pgno>sqlite3BtreeLastPage(pBt) ){ 332 + zErr = "bad page number"; 333 + goto update_fail; 334 + } 335 + szPage = sqlite3BtreeGetPageSize(pBt); 272 336 if( sqlite3_value_type(argv[3])!=SQLITE_BLOB 273 - || sqlite3_value_bytes(argv[3])!=pTab->szPage 337 + || sqlite3_value_bytes(argv[3])!=szPage 274 338 ){ 275 339 zErr = "bad page value"; 276 340 goto update_fail; 277 341 } 278 - rc = sqlite3PagerGet(pTab->pPager, pgno, (DbPage**)&pDbPage, 0); 342 + pPager = sqlite3BtreePager(pBt); 343 + rc = sqlite3PagerGet(pPager, pgno, (DbPage**)&pDbPage, 0); 279 344 if( rc==SQLITE_OK ){ 280 345 rc = sqlite3PagerWrite(pDbPage); 281 346 if( rc==SQLITE_OK ){ 282 347 memcpy(sqlite3PagerGetData(pDbPage), 283 348 sqlite3_value_blob(argv[3]), 284 - pTab->szPage); 349 + szPage); 285 350 } 286 351 } 287 352 sqlite3PagerUnref(pDbPage); 288 353 return rc; 289 354 290 355 update_fail: 291 356 sqlite3_free(pVtab->zErrMsg); 292 357 pVtab->zErrMsg = sqlite3_mprintf("%s", zErr); 293 358 return SQLITE_ERROR; 294 359 } 360 + 361 +/* Since we do not know in advance which database files will be 362 +** written by the sqlite_dbpage virtual table, start a write transaction 363 +** on them all. 364 +*/ 365 +static int dbpageBegin(sqlite3_vtab *pVtab){ 366 + DbpageTable *pTab = (DbpageTable *)pVtab; 367 + sqlite3 *db = pTab->db; 368 + int i; 369 + for(i=0; i<db->nDb; i++){ 370 + Btree *pBt = db->aDb[i].pBt; 371 + if( pBt ) sqlite3BtreeBeginTrans(pBt, 1); 372 + } 373 + return SQLITE_OK; 374 +} 375 + 295 376 296 377 /* 297 378 ** Invoke this routine to register the "dbpage" virtual table module 298 379 */ 299 380 int sqlite3DbpageRegister(sqlite3 *db){ 300 381 static sqlite3_module dbpage_module = { 301 382 0, /* iVersion */ ................................................................................ 308 389 dbpageClose, /* xClose - close a cursor */ 309 390 dbpageFilter, /* xFilter - configure scan constraints */ 310 391 dbpageNext, /* xNext - advance a cursor */ 311 392 dbpageEof, /* xEof - check for end of scan */ 312 393 dbpageColumn, /* xColumn - read data */ 313 394 dbpageRowid, /* xRowid - read data */ 314 395 dbpageUpdate, /* xUpdate */ 315 - 0, /* xBegin */ 396 + dbpageBegin, /* xBegin */ 316 397 0, /* xSync */ 317 398 0, /* xCommit */ 318 399 0, /* xRollback */ 319 400 0, /* xFindMethod */ 320 401 0, /* xRename */ 321 402 0, /* xSavepoint */ 322 403 0, /* xRelease */
Changes to test/dbpage.test.
45 45 } {4 X'0D00000016'} 46 46 do_execsql_test 140 { 47 47 SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage WHERE pgno=5; 48 48 } {} 49 49 do_execsql_test 150 { 50 50 SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage WHERE pgno=0; 51 51 } {} 52 +do_execsql_test 160 { 53 + ATTACH ':memory:' AS aux1; 54 + PRAGMA aux1.page_size=4096; 55 + CREATE TABLE aux1.t2(a,b,c); 56 + INSERT INTO t2 VALUES(11,12,13); 57 + SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage('aux1'); 58 +} {1 X'53514C6974' 2 X'0D00000001'} 52 59 53 60 do_execsql_test 200 { 54 61 CREATE TEMP TABLE saved_content(x); 55 62 INSERT INTO saved_content(x) SELECT data FROM sqlite_dbpage WHERE pgno=4; 56 63 UPDATE sqlite_dbpage SET data=zeroblob(4096) WHERE pgno=4; 57 64 } {} 58 65 do_catchsql_test 210 { ................................................................................ 63 70 } {1 X'53514C6974' 2 X'0500000001' 3 X'0D0000004E' 4 X'0000000000'} 64 71 do_execsql_test 230 { 65 72 UPDATE sqlite_dbpage SET data=(SELECT x FROM saved_content) WHERE pgno=4; 66 73 } {} 67 74 do_catchsql_test 230 { 68 75 PRAGMA integrity_check; 69 76 } {0 ok} 70 - 71 - 72 - 77 +do_execsql_test 240 { 78 + DELETE FROM saved_content; 79 + INSERT INTO saved_content(x) 80 + SELECT data FROM sqlite_dbpage WHERE schema='aux1' AND pgno=2; 81 +} {} 82 +do_execsql_test 241 { 83 + UPDATE sqlite_dbpage SET data=zeroblob(4096) WHERE pgno=2 AND schema='aux1'; 84 +} {} 85 +do_catchsql_test 250 { 86 + PRAGMA aux1.integrity_check; 87 +} {1 {database disk image is malformed}} 88 +do_execsql_test 260 { 89 + UPDATE sqlite_dbpage SET data=(SELECT x FROM saved_content) 90 + WHERE pgno=2 AND schema='aux1'; 91 +} {} 92 +do_catchsql_test 270 { 93 + PRAGMA aux1.integrity_check; 94 +} {0 ok} 73 95 74 96 finish_test