/ Check-in [d4f893e1]
Login

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: d4f893e1ae53a0445939ea2920af87d21dd36270494381028b2eaebe5c188f18
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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