/ Check-in [a8b264d8]
Login

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 | SQL archive
Timelines: family | ancestors | descendants | both | dbpage
Files: files | file ages | folders
SHA3-256: a8b264d811e5bcb7e3ae8a12bf5b6830a9d1adff1f59436dda9e886f97da242f
User & Date: drh 2017-10-11 15:02:53
Context
2017-10-11
17:13
Enable sqlite_dbpage and dbstat virtual tables in the shell, by default. check-in: 01bf856c user: drh tags: dbpage
15:02
Get writes working on the sqlite_dbpage virtual table. Add a few test cases. check-in: a8b264d8 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: c2c1d656 user: drh tags: dbpage
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/dbpage.c.

    12     12   **
    13     13   ** This file contains an implementation of the "sqlite_dbpage" virtual table.
    14     14   **
    15     15   ** The sqlite_dbpage virtual table is used to read or write whole raw
    16     16   ** pages of the database file.  The pager interface is used so that 
    17     17   ** uncommitted changes and changes recorded in the WAL file are correctly
    18     18   ** retrieved.
           19  +**
           20  +** Usage example:
           21  +**
           22  +**    SELECT data FROM sqlite_dbpage('aux1') WHERE pgno=123;
           23  +**
           24  +** This is an eponymous virtual table so it does not need to be created before
           25  +** use.  The optional argument to the sqlite_dbpage() table name is the
           26  +** schema for the database file that is to be read.  The default schema is
           27  +** "main".
           28  +**
           29  +** The data field of sqlite_dbpage table can be updated.  The new
           30  +** value must be a BLOB which is the correct page size, otherwise the
           31  +** update fails.  Rows may not be deleted or inserted.
    19     32   */
    20     33   
    21     34   #include "sqliteInt.h"   /* Requires access to internal data structures */
    22     35   #if (defined(SQLITE_ENABLE_DBPAGE_VTAB) || defined(SQLITE_TEST)) \
    23     36       && !defined(SQLITE_OMIT_VIRTUALTABLE)
    24     37   
    25     38   typedef struct DbpageTable DbpageTable;
    26     39   typedef struct DbpageCursor DbpageCursor;
    27     40   
    28     41   struct DbpageCursor {
    29     42     sqlite3_vtab_cursor base;       /* Base class.  Must be first */
    30     43     int pgno;                       /* Current page number */
           44  +  int mxPgno;                     /* Last page to visit on this scan */
    31     45   };
    32     46   
    33     47   struct DbpageTable {
    34     48     sqlite3_vtab base;              /* Base class.  Must be first */
    35     49     sqlite3 *db;                    /* The database */
    36     50     Pager *pPager;                  /* Pager being read/written */
    37     51     int iDb;                        /* Index of database to analyze */
................................................................................
    61     75         *pzErr = sqlite3_mprintf("no such schema: %s", argv[3]);
    62     76         return SQLITE_ERROR;
    63     77       }
    64     78     }else{
    65     79       iDb = 0;
    66     80     }
    67     81     rc = sqlite3_declare_vtab(db, 
    68         -          "CREATE TABLE x(pgno INTEGER PRIMARY KEY, data BLOB)");
           82  +          "CREATE TABLE x(pgno INTEGER PRIMARY KEY, data BLOB, schema HIDDEN)");
    69     83     if( rc==SQLITE_OK ){
    70     84       pTab = (DbpageTable *)sqlite3_malloc64(sizeof(DbpageTable));
    71     85       if( pTab==0 ) rc = SQLITE_NOMEM_BKPT;
    72     86     }
    73     87   
    74     88     assert( rc==SQLITE_OK || pTab==0 );
    75     89     if( rc==SQLITE_OK ){
................................................................................
    95    109   /*
    96    110   ** idxNum:
    97    111   **
    98    112   **     0     full table scan
    99    113   **     1     pgno=?1
   100    114   */
   101    115   static int dbpageBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
          116  +  int i;
   102    117     pIdxInfo->estimatedCost = 1.0e6;  /* Initial cost estimate */
          118  +  for(i=0; i<pIdxInfo->nConstraint; i++){
          119  +    struct sqlite3_index_constraint *p = &pIdxInfo->aConstraint[i];
          120  +    if( p->usable && p->iColumn<=0 && p->op==SQLITE_INDEX_CONSTRAINT_EQ ){
          121  +      pIdxInfo->estimatedRows = 1;
          122  +      pIdxInfo->idxFlags = SQLITE_INDEX_SCAN_UNIQUE;
          123  +      pIdxInfo->estimatedCost = 1.0;
          124  +      pIdxInfo->idxNum = 1;
          125  +      pIdxInfo->aConstraintUsage[i].argvIndex = 1;
          126  +      pIdxInfo->aConstraintUsage[i].omit = 1;
          127  +      break;
          128  +    }
          129  +  }
          130  +  if( pIdxInfo->nOrderBy>=1
          131  +   && pIdxInfo->aOrderBy[0].iColumn<=0
          132  +   && pIdxInfo->aOrderBy[0].desc==0
          133  +  ){
          134  +    pIdxInfo->orderByConsumed = 1;
          135  +  }
   103    136     return SQLITE_OK;
   104    137   }
   105    138   
   106    139   /*
   107    140   ** Open a new dbpagevfs cursor.
   108    141   */
   109    142   static int dbpageOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){
................................................................................
   139    172     DbpageCursor *pCsr = (DbpageCursor *)pCursor;
   140    173     pCsr->pgno++;
   141    174     return rc;
   142    175   }
   143    176   
   144    177   static int dbpageEof(sqlite3_vtab_cursor *pCursor){
   145    178     DbpageCursor *pCsr = (DbpageCursor *)pCursor;
   146         -  DbpageTable *pTab = (DbpageTable *)pCursor->pVtab;
   147         -  return pCsr->pgno >= pTab->nPage;
          179  +  return pCsr->pgno > pCsr->mxPgno;
   148    180   }
   149    181   
   150    182   static int dbpageFilter(
   151    183     sqlite3_vtab_cursor *pCursor, 
   152    184     int idxNum, const char *idxStr,
   153    185     int argc, sqlite3_value **argv
   154    186   ){
   155    187     DbpageCursor *pCsr = (DbpageCursor *)pCursor;
   156    188     DbpageTable *pTab = (DbpageTable *)pCursor->pVtab;
   157    189     int rc = SQLITE_OK;
   158    190     Btree *pBt = pTab->db->aDb[pTab->iDb].pBt;
   159    191   
          192  +  pTab->szPage = sqlite3BtreeGetPageSize(pBt);
          193  +  pTab->nPage = sqlite3BtreeLastPage(pBt);
   160    194     if( idxNum==1 ){
   161    195       pCsr->pgno = sqlite3_value_int(argv[0]);
          196  +    if( pCsr->pgno<1 || pCsr->pgno>pTab->nPage ){
          197  +      pCsr->pgno = 1;
          198  +      pCsr->mxPgno = 0;
          199  +    }else{
          200  +      pCsr->mxPgno = pCsr->pgno;
          201  +    }
   162    202     }else{
   163         -    pCsr->pgno = 0;
          203  +    pCsr->pgno = 1;
          204  +    pCsr->mxPgno = pTab->nPage;
   164    205     }
   165         -  pTab->szPage = sqlite3BtreeGetPageSize(pBt);
   166         -  pTab->nPage = sqlite3BtreeLastPage(pBt);
   167    206     return rc;
   168    207   }
   169    208   
   170    209   static int dbpageColumn(
   171    210     sqlite3_vtab_cursor *pCursor, 
   172    211     sqlite3_context *ctx, 
   173    212     int i
................................................................................
   200    239   }
   201    240   
   202    241   static int dbpageRowid(sqlite3_vtab_cursor *pCursor, sqlite_int64 *pRowid){
   203    242     DbpageCursor *pCsr = (DbpageCursor *)pCursor;
   204    243     *pRowid = pCsr->pgno;
   205    244     return SQLITE_OK;
   206    245   }
          246  +
          247  +static int dbpageUpdate(
          248  +  sqlite3_vtab *pVtab,
          249  +  int argc,
          250  +  sqlite3_value **argv,
          251  +  sqlite_int64 *pRowid
          252  +){
          253  +  DbpageTable *pTab = (DbpageTable *)pVtab;
          254  +  int pgno;
          255  +  DbPage *pDbPage = 0;
          256  +  int rc = SQLITE_OK;
          257  +  char *zErr = 0;
          258  +
          259  +  if( argc==1 ){
          260  +    zErr = "cannot delete";
          261  +    goto update_fail;
          262  +  }
          263  +  pgno = sqlite3_value_int(argv[0]);
          264  +  if( pgno<1 || pgno>pTab->nPage ){
          265  +    zErr = "bad page number";
          266  +    goto update_fail;
          267  +  }
          268  +  if( sqlite3_value_int(argv[1])!=pgno ){
          269  +    zErr = "cannot insert";
          270  +    goto update_fail;
          271  +  }
          272  +  if( sqlite3_value_type(argv[3])!=SQLITE_BLOB 
          273  +   || sqlite3_value_bytes(argv[3])!=pTab->szPage 
          274  +  ){
          275  +    zErr = "bad page value";
          276  +    goto update_fail;
          277  +  }
          278  +  rc = sqlite3PagerGet(pTab->pPager, pgno, (DbPage**)&pDbPage, 0);
          279  +  if( rc==SQLITE_OK ){
          280  +    rc = sqlite3PagerWrite(pDbPage);
          281  +    if( rc==SQLITE_OK ){
          282  +      memcpy(sqlite3PagerGetData(pDbPage),
          283  +             sqlite3_value_blob(argv[3]),
          284  +             pTab->szPage);
          285  +    }
          286  +  }
          287  +  sqlite3PagerUnref(pDbPage);
          288  +  return rc;
          289  +
          290  +update_fail:
          291  +  sqlite3_free(pVtab->zErrMsg);
          292  +  pVtab->zErrMsg = sqlite3_mprintf("%s", zErr);
          293  +  return SQLITE_ERROR;
          294  +}
   207    295   
   208    296   /*
   209    297   ** Invoke this routine to register the "dbpage" virtual table module
   210    298   */
   211    299   int sqlite3DbpageRegister(sqlite3 *db){
   212    300     static sqlite3_module dbpage_module = {
   213    301       0,                            /* iVersion */
................................................................................
   219    307       dbpageOpen,                   /* xOpen - open a cursor */
   220    308       dbpageClose,                  /* xClose - close a cursor */
   221    309       dbpageFilter,                 /* xFilter - configure scan constraints */
   222    310       dbpageNext,                   /* xNext - advance a cursor */
   223    311       dbpageEof,                    /* xEof - check for end of scan */
   224    312       dbpageColumn,                 /* xColumn - read data */
   225    313       dbpageRowid,                  /* xRowid - read data */
   226         -    0,                            /* xUpdate */
          314  +    dbpageUpdate,                 /* xUpdate */
   227    315       0,                            /* xBegin */
   228    316       0,                            /* xSync */
   229    317       0,                            /* xCommit */
   230    318       0,                            /* xRollback */
   231    319       0,                            /* xFindMethod */
   232    320       0,                            /* xRename */
   233    321       0,                            /* xSavepoint */

Added test/dbpage.test.

            1  +# 2017-10-11
            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  +# This file implements regression tests for SQLite library.  The
           12  +# focus of this file is testing the sqlite_dbpage virtual table.
           13  +#
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +set testprefix dbpage
           18  +
           19  +ifcapable !vtab||!compound {
           20  +  finish_test
           21  +  return
           22  +}
           23  +
           24  +do_execsql_test 100 {
           25  +  PRAGMA page_size=4096;
           26  +  PRAGMA journal_mode=WAL;
           27  +  CREATE TABLE t1(a,b);
           28  +  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
           29  +  INSERT INTO t1(a,b) SELECT x, printf('%d-x%.*c',x,x,'x') FROM c;
           30  +  PRAGMA integrity_check;
           31  +} {wal ok}
           32  +do_execsql_test 110 {
           33  +  SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage('main') ORDER BY pgno;
           34  +} {1 X'53514C6974' 2 X'0500000001' 3 X'0D0000004E' 4 X'0D00000016'}
           35  +do_execsql_test 120 {
           36  +  SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage WHERE pgno=2;
           37  +} {2 X'0500000001'}
           38  +do_execsql_test 130 {
           39  +  SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage WHERE pgno=4;
           40  +} {4 X'0D00000016'}
           41  +do_execsql_test 140 {
           42  +  SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage WHERE pgno=5;
           43  +} {}
           44  +do_execsql_test 150 {
           45  +  SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage WHERE pgno=0;
           46  +} {}
           47  +
           48  +do_execsql_test 200 {
           49  +  CREATE TEMP TABLE saved_content(x);
           50  +  INSERT INTO saved_content(x) SELECT data FROM sqlite_dbpage WHERE pgno=4;
           51  +  UPDATE sqlite_dbpage SET data=zeroblob(4096) WHERE pgno=4;
           52  +} {}
           53  +do_catchsql_test 210 {
           54  +  PRAGMA integrity_check;
           55  +} {1 {database disk image is malformed}}
           56  +do_execsql_test 220 {
           57  +  SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage('main') ORDER BY pgno;
           58  +} {1 X'53514C6974' 2 X'0500000001' 3 X'0D0000004E' 4 X'0000000000'}
           59  +do_execsql_test 230 {
           60  +  UPDATE sqlite_dbpage SET data=(SELECT x FROM saved_content) WHERE pgno=4;
           61  +} {}
           62  +do_catchsql_test 230 {
           63  +  PRAGMA integrity_check;
           64  +} {0 ok}
           65  +
           66  +
           67  +
           68  +
           69  +finish_test