Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Get writes working on the sqlite_dbpage virtual table. Add a few test cases. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | dbpage |
Files: | files | file ages | folders |
SHA3-256: |
a8b264d811e5bcb7e3ae8a12bf5b6830 |
User & Date: | drh 2017-10-11 15:02:53.047 |
Context
2017-10-11
| ||
17:13 | Enable sqlite_dbpage and dbstat virtual tables in the shell, by default. (check-in: 01bf856c42 user: drh tags: dbpage) | |
15:02 | Get writes working on the sqlite_dbpage virtual table. Add a few test cases. (check-in: a8b264d811 user: drh tags: dbpage) | |
13:48 | Initial implementation of the "sqlite_dbpage" virtual table. Currently it is read-only and has a place-holder xBestIndex. (check-in: c2c1d656e3 user: drh tags: dbpage) | |
Changes
Changes to src/dbpage.c.
︙ | ︙ | |||
12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | ** ** This file contains an implementation of the "sqlite_dbpage" virtual table. ** ** The sqlite_dbpage virtual table is used to read or write whole raw ** pages of the database file. The pager interface is used so that ** uncommitted changes and changes recorded in the WAL file are correctly ** retrieved. */ #include "sqliteInt.h" /* Requires access to internal data structures */ #if (defined(SQLITE_ENABLE_DBPAGE_VTAB) || defined(SQLITE_TEST)) \ && !defined(SQLITE_OMIT_VIRTUALTABLE) typedef struct DbpageTable DbpageTable; typedef struct DbpageCursor DbpageCursor; struct DbpageCursor { sqlite3_vtab_cursor base; /* Base class. Must be first */ int pgno; /* Current page number */ }; struct DbpageTable { sqlite3_vtab base; /* Base class. Must be first */ sqlite3 *db; /* The database */ Pager *pPager; /* Pager being read/written */ int iDb; /* Index of database to analyze */ | > > > > > > > > > > > > > > | 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 | ** ** This file contains an implementation of the "sqlite_dbpage" virtual table. ** ** The sqlite_dbpage virtual table is used to read or write whole raw ** pages of the database file. The pager interface is used so that ** uncommitted changes and changes recorded in the WAL file are correctly ** retrieved. ** ** Usage example: ** ** SELECT data FROM sqlite_dbpage('aux1') WHERE pgno=123; ** ** This is an eponymous virtual table so it does not need to be created before ** use. The optional argument to the sqlite_dbpage() table name is the ** schema for the database file that is to be read. The default schema is ** "main". ** ** The data field of sqlite_dbpage table can be updated. The new ** value must be a BLOB which is the correct page size, otherwise the ** update fails. Rows may not be deleted or inserted. */ #include "sqliteInt.h" /* Requires access to internal data structures */ #if (defined(SQLITE_ENABLE_DBPAGE_VTAB) || defined(SQLITE_TEST)) \ && !defined(SQLITE_OMIT_VIRTUALTABLE) typedef struct DbpageTable DbpageTable; typedef struct DbpageCursor DbpageCursor; struct DbpageCursor { sqlite3_vtab_cursor base; /* Base class. Must be first */ int pgno; /* Current page number */ int mxPgno; /* Last page to visit on this scan */ }; struct DbpageTable { sqlite3_vtab base; /* Base class. Must be first */ sqlite3 *db; /* The database */ Pager *pPager; /* Pager being read/written */ int iDb; /* Index of database to analyze */ |
︙ | ︙ | |||
61 62 63 64 65 66 67 | *pzErr = sqlite3_mprintf("no such schema: %s", argv[3]); return SQLITE_ERROR; } }else{ iDb = 0; } rc = sqlite3_declare_vtab(db, | | | 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 | *pzErr = sqlite3_mprintf("no such schema: %s", argv[3]); return SQLITE_ERROR; } }else{ iDb = 0; } rc = sqlite3_declare_vtab(db, "CREATE TABLE x(pgno INTEGER PRIMARY KEY, data BLOB, schema HIDDEN)"); if( rc==SQLITE_OK ){ pTab = (DbpageTable *)sqlite3_malloc64(sizeof(DbpageTable)); if( pTab==0 ) rc = SQLITE_NOMEM_BKPT; } assert( rc==SQLITE_OK || pTab==0 ); if( rc==SQLITE_OK ){ |
︙ | ︙ | |||
95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 | /* ** idxNum: ** ** 0 full table scan ** 1 pgno=?1 */ static int dbpageBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){ pIdxInfo->estimatedCost = 1.0e6; /* Initial cost estimate */ return SQLITE_OK; } /* ** Open a new dbpagevfs cursor. */ static int dbpageOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){ | > > > > > > > > > > > > > > > > > > > | 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 | /* ** idxNum: ** ** 0 full table scan ** 1 pgno=?1 */ static int dbpageBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){ int i; pIdxInfo->estimatedCost = 1.0e6; /* Initial cost estimate */ for(i=0; i<pIdxInfo->nConstraint; i++){ struct sqlite3_index_constraint *p = &pIdxInfo->aConstraint[i]; if( p->usable && p->iColumn<=0 && p->op==SQLITE_INDEX_CONSTRAINT_EQ ){ pIdxInfo->estimatedRows = 1; pIdxInfo->idxFlags = SQLITE_INDEX_SCAN_UNIQUE; pIdxInfo->estimatedCost = 1.0; pIdxInfo->idxNum = 1; pIdxInfo->aConstraintUsage[i].argvIndex = 1; pIdxInfo->aConstraintUsage[i].omit = 1; break; } } if( pIdxInfo->nOrderBy>=1 && pIdxInfo->aOrderBy[0].iColumn<=0 && pIdxInfo->aOrderBy[0].desc==0 ){ pIdxInfo->orderByConsumed = 1; } return SQLITE_OK; } /* ** Open a new dbpagevfs cursor. */ static int dbpageOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){ |
︙ | ︙ | |||
139 140 141 142 143 144 145 | DbpageCursor *pCsr = (DbpageCursor *)pCursor; pCsr->pgno++; return rc; } static int dbpageEof(sqlite3_vtab_cursor *pCursor){ DbpageCursor *pCsr = (DbpageCursor *)pCursor; | < | > > > > > | | | > | | > | 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 | DbpageCursor *pCsr = (DbpageCursor *)pCursor; pCsr->pgno++; return rc; } static int dbpageEof(sqlite3_vtab_cursor *pCursor){ DbpageCursor *pCsr = (DbpageCursor *)pCursor; return pCsr->pgno > pCsr->mxPgno; } static int dbpageFilter( sqlite3_vtab_cursor *pCursor, int idxNum, const char *idxStr, int argc, sqlite3_value **argv ){ DbpageCursor *pCsr = (DbpageCursor *)pCursor; DbpageTable *pTab = (DbpageTable *)pCursor->pVtab; int rc = SQLITE_OK; Btree *pBt = pTab->db->aDb[pTab->iDb].pBt; pTab->szPage = sqlite3BtreeGetPageSize(pBt); pTab->nPage = sqlite3BtreeLastPage(pBt); if( idxNum==1 ){ pCsr->pgno = sqlite3_value_int(argv[0]); if( pCsr->pgno<1 || pCsr->pgno>pTab->nPage ){ pCsr->pgno = 1; pCsr->mxPgno = 0; }else{ pCsr->mxPgno = pCsr->pgno; } }else{ pCsr->pgno = 1; pCsr->mxPgno = pTab->nPage; } return rc; } static int dbpageColumn( sqlite3_vtab_cursor *pCursor, sqlite3_context *ctx, int i |
︙ | ︙ | |||
200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 | } static int dbpageRowid(sqlite3_vtab_cursor *pCursor, sqlite_int64 *pRowid){ DbpageCursor *pCsr = (DbpageCursor *)pCursor; *pRowid = pCsr->pgno; return SQLITE_OK; } /* ** Invoke this routine to register the "dbpage" virtual table module */ int sqlite3DbpageRegister(sqlite3 *db){ static sqlite3_module dbpage_module = { 0, /* iVersion */ dbpageConnect, /* xCreate */ dbpageConnect, /* xConnect */ dbpageBestIndex, /* xBestIndex */ dbpageDisconnect, /* xDisconnect */ dbpageDisconnect, /* xDestroy */ dbpageOpen, /* xOpen - open a cursor */ dbpageClose, /* xClose - close a cursor */ dbpageFilter, /* xFilter - configure scan constraints */ dbpageNext, /* xNext - advance a cursor */ dbpageEof, /* xEof - check for end of scan */ dbpageColumn, /* xColumn - read data */ dbpageRowid, /* xRowid - read data */ | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | | 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 | } static int dbpageRowid(sqlite3_vtab_cursor *pCursor, sqlite_int64 *pRowid){ DbpageCursor *pCsr = (DbpageCursor *)pCursor; *pRowid = pCsr->pgno; return SQLITE_OK; } static int dbpageUpdate( sqlite3_vtab *pVtab, int argc, sqlite3_value **argv, sqlite_int64 *pRowid ){ DbpageTable *pTab = (DbpageTable *)pVtab; int pgno; DbPage *pDbPage = 0; int rc = SQLITE_OK; char *zErr = 0; if( argc==1 ){ zErr = "cannot delete"; goto update_fail; } pgno = sqlite3_value_int(argv[0]); if( pgno<1 || pgno>pTab->nPage ){ zErr = "bad page number"; goto update_fail; } if( sqlite3_value_int(argv[1])!=pgno ){ zErr = "cannot insert"; goto update_fail; } if( sqlite3_value_type(argv[3])!=SQLITE_BLOB || sqlite3_value_bytes(argv[3])!=pTab->szPage ){ zErr = "bad page value"; goto update_fail; } rc = sqlite3PagerGet(pTab->pPager, pgno, (DbPage**)&pDbPage, 0); if( rc==SQLITE_OK ){ rc = sqlite3PagerWrite(pDbPage); if( rc==SQLITE_OK ){ memcpy(sqlite3PagerGetData(pDbPage), sqlite3_value_blob(argv[3]), pTab->szPage); } } sqlite3PagerUnref(pDbPage); return rc; update_fail: sqlite3_free(pVtab->zErrMsg); pVtab->zErrMsg = sqlite3_mprintf("%s", zErr); return SQLITE_ERROR; } /* ** Invoke this routine to register the "dbpage" virtual table module */ int sqlite3DbpageRegister(sqlite3 *db){ static sqlite3_module dbpage_module = { 0, /* iVersion */ dbpageConnect, /* xCreate */ dbpageConnect, /* xConnect */ dbpageBestIndex, /* xBestIndex */ dbpageDisconnect, /* xDisconnect */ dbpageDisconnect, /* xDestroy */ dbpageOpen, /* xOpen - open a cursor */ dbpageClose, /* xClose - close a cursor */ dbpageFilter, /* xFilter - configure scan constraints */ dbpageNext, /* xNext - advance a cursor */ dbpageEof, /* xEof - check for end of scan */ dbpageColumn, /* xColumn - read data */ dbpageRowid, /* xRowid - read data */ dbpageUpdate, /* xUpdate */ 0, /* xBegin */ 0, /* xSync */ 0, /* xCommit */ 0, /* xRollback */ 0, /* xFindMethod */ 0, /* xRename */ 0, /* xSavepoint */ |
︙ | ︙ |
Added test/dbpage.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 | # 2017-10-11 # # 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. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the sqlite_dbpage virtual table. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix dbpage ifcapable !vtab||!compound { finish_test return } do_execsql_test 100 { PRAGMA page_size=4096; PRAGMA journal_mode=WAL; CREATE TABLE t1(a,b); WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100) INSERT INTO t1(a,b) SELECT x, printf('%d-x%.*c',x,x,'x') FROM c; PRAGMA integrity_check; } {wal ok} do_execsql_test 110 { SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage('main') ORDER BY pgno; } {1 X'53514C6974' 2 X'0500000001' 3 X'0D0000004E' 4 X'0D00000016'} do_execsql_test 120 { SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage WHERE pgno=2; } {2 X'0500000001'} do_execsql_test 130 { SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage WHERE pgno=4; } {4 X'0D00000016'} do_execsql_test 140 { SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage WHERE pgno=5; } {} do_execsql_test 150 { SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage WHERE pgno=0; } {} do_execsql_test 200 { CREATE TEMP TABLE saved_content(x); INSERT INTO saved_content(x) SELECT data FROM sqlite_dbpage WHERE pgno=4; UPDATE sqlite_dbpage SET data=zeroblob(4096) WHERE pgno=4; } {} do_catchsql_test 210 { PRAGMA integrity_check; } {1 {database disk image is malformed}} do_execsql_test 220 { SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage('main') ORDER BY pgno; } {1 X'53514C6974' 2 X'0500000001' 3 X'0D0000004E' 4 X'0000000000'} do_execsql_test 230 { UPDATE sqlite_dbpage SET data=(SELECT x FROM saved_content) WHERE pgno=4; } {} do_catchsql_test 230 { PRAGMA integrity_check; } {0 ok} finish_test |