/ Check-in [af172b53]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Allow the INTO clause of VACUUM to be a text-valued expression.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | vacuum-into
Files: files | file ages | folders
SHA3-256: af172b53b46759f491f522356e14c5e2374d3f25ec70fbc1e100cadded8f9b22
User & Date: drh 2018-12-08 00:43:08
Context
2018-12-08
01:09
Add the --async option to the ".backup" command in the CLI. check-in: 7b6a605b user: drh tags: vacuum-into
00:43
Allow the INTO clause of VACUUM to be a text-valued expression. check-in: af172b53 user: drh tags: vacuum-into
2018-12-07
23:48
Do not allow VACUUM INTO into a file that already exists. check-in: 92f70e0f user: drh tags: vacuum-into
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/parse.y.

  1363   1363   //
  1364   1364   cmd ::= DROP INDEX ifexists(E) fullname(X).   {sqlite3DropIndex(pParse, X, E);}
  1365   1365   
  1366   1366   ///////////////////////////// The VACUUM command /////////////////////////////
  1367   1367   //
  1368   1368   %ifndef SQLITE_OMIT_VACUUM
  1369   1369   %ifndef SQLITE_OMIT_ATTACH
  1370         -%type vinto {Token}
  1371         -cmd ::= VACUUM vinto(Y).                {sqlite3Vacuum(pParse,0,&Y);}
  1372         -cmd ::= VACUUM nm(X) vinto(Y).          {sqlite3Vacuum(pParse,&X,&Y);}
  1373         -vinto(A) ::= INTO nm(X).                {A = X;}
  1374         -vinto(A) ::= .                          {A.z = 0;}
         1370  +%type vinto {Expr*}
         1371  +%destructor vinto {sqlite3ExprDelete(pParse->db, $$);}
         1372  +cmd ::= VACUUM vinto(Y).                {sqlite3Vacuum(pParse,0,Y);}
         1373  +cmd ::= VACUUM nm(X) vinto(Y).          {sqlite3Vacuum(pParse,&X,Y);}
         1374  +vinto(A) ::= INTO expr(X).              {A = X;}
         1375  +vinto(A) ::= .                          {A = 0;}
  1375   1376   %endif  SQLITE_OMIT_ATTACH
  1376   1377   %endif  SQLITE_OMIT_VACUUM
  1377   1378   
  1378   1379   ///////////////////////////// The PRAGMA command /////////////////////////////
  1379   1380   //
  1380   1381   %ifndef SQLITE_OMIT_PRAGMA
  1381   1382   cmd ::= PRAGMA nm(X) dbnm(Z).                {sqlite3Pragma(pParse,&X,&Z,0,0);}

Changes to src/sqliteInt.h.

  3981   3981   #define LOCATE_VIEW    0x01
  3982   3982   #define LOCATE_NOERR   0x02
  3983   3983   Table *sqlite3LocateTable(Parse*,u32 flags,const char*, const char*);
  3984   3984   Table *sqlite3LocateTableItem(Parse*,u32 flags,struct SrcList_item *);
  3985   3985   Index *sqlite3FindIndex(sqlite3*,const char*, const char*);
  3986   3986   void sqlite3UnlinkAndDeleteTable(sqlite3*,int,const char*);
  3987   3987   void sqlite3UnlinkAndDeleteIndex(sqlite3*,int,const char*);
  3988         -void sqlite3Vacuum(Parse*,Token*,Token*);
  3989         -int sqlite3RunVacuum(char**, sqlite3*, int, const char*);
         3988  +void sqlite3Vacuum(Parse*,Token*,Expr*);
         3989  +int sqlite3RunVacuum(char**, sqlite3*, int, sqlite3_value*);
  3990   3990   char *sqlite3NameFromToken(sqlite3*, Token*);
  3991   3991   int sqlite3ExprCompare(Parse*,Expr*, Expr*, int);
  3992   3992   int sqlite3ExprCompareSkip(Expr*, Expr*, int);
  3993   3993   int sqlite3ExprListCompare(ExprList*, ExprList*, int);
  3994   3994   int sqlite3ExprImpliesExpr(Parse*,Expr*, Expr*, int);
  3995   3995   int sqlite3ExprImpliesNonNullRow(Expr*,int);
  3996   3996   void sqlite3ExprAnalyzeAggregates(NameContext*, Expr*);

Changes to src/vacuum.c.

    98     98   ** the copy of step (3) were replaced by deleting the original database
    99     99   ** and renaming the transient database as the original.  But that will
   100    100   ** not work if other processes are attached to the original database.
   101    101   ** And a power loss in between deleting the original and renaming the
   102    102   ** transient would cause the database file to appear to be deleted
   103    103   ** following reboot.
   104    104   */
   105         -void sqlite3Vacuum(Parse *pParse, Token *pNm, Token *pInto){
          105  +void sqlite3Vacuum(Parse *pParse, Token *pNm, Expr *pInto){
   106    106     Vdbe *v = sqlite3GetVdbe(pParse);
   107    107     int iDb = 0;
   108         -  assert( pInto!=0 );
   109         -  if( v==0 ) return;
          108  +  if( v==0 ) goto build_vacuum_end;
   110    109     if( pNm ){
   111    110   #ifndef SQLITE_BUG_COMPATIBLE_20160819
   112    111       /* Default behavior:  Report an error if the argument to VACUUM is
   113    112       ** not recognized */
   114    113       iDb = sqlite3TwoPartName(pParse, pNm, pNm, &pNm);
   115         -    if( iDb<0 ) return;
          114  +    if( iDb<0 ) goto build_vacuum_end;
   116    115   #else
   117    116       /* When SQLITE_BUG_COMPATIBLE_20160819 is defined, unrecognized arguments
   118    117       ** to VACUUM are silently ignored.  This is a back-out of a bug fix that
   119    118       ** occurred on 2016-08-19 (https://www.sqlite.org/src/info/083f9e6270).
   120    119       ** The buggy behavior is required for binary compatibility with some
   121    120       ** legacy applications. */
   122    121       iDb = sqlite3FindDb(pParse->db, pNm);
   123    122       if( iDb<0 ) iDb = 0;
   124    123   #endif
   125    124     }
   126    125     if( iDb!=1 ){
   127         -    sqlite3VdbeAddOp1(v, OP_Vacuum, iDb);
   128         -    sqlite3VdbeUsesBtree(v, iDb);
   129         -    if( pInto->z ){
   130         -      char *zName = sqlite3NameFromToken(pParse->db, pInto);
   131         -      sqlite3VdbeChangeP4(v, -1, zName, P4_DYNAMIC);
          126  +    int iIntoReg = 0;
          127  +    if( pInto ){
          128  +      iIntoReg = ++pParse->nMem;
          129  +      sqlite3ExprCode(pParse, pInto, iIntoReg);
   132    130       }
          131  +    sqlite3VdbeAddOp2(v, OP_Vacuum, iDb, iIntoReg);
          132  +    sqlite3VdbeUsesBtree(v, iDb);
   133    133     }
          134  +build_vacuum_end:
          135  +  sqlite3ExprDelete(pParse->db, pInto);
   134    136     return;
   135    137   }
   136    138   
   137    139   /*
   138    140   ** This routine implements the OP_Vacuum opcode of the VDBE.
   139    141   */
   140         -int sqlite3RunVacuum(char **pzErrMsg, sqlite3 *db, int iDb, const char *zOut){
          142  +int sqlite3RunVacuum(
          143  +  char **pzErrMsg,        /* Write error message here */
          144  +  sqlite3 *db,            /* Database connection */
          145  +  int iDb,                /* Which attached DB to vacuum */
          146  +  sqlite3_value *pOut     /* Write results here, if not NULL */
          147  +){
   141    148     int rc = SQLITE_OK;     /* Return code from service routines */
   142    149     Btree *pMain;           /* The database being vacuumed */
   143    150     Btree *pTemp;           /* The temporary database we vacuum into */
   144    151     u32 saved_mDbFlags;     /* Saved value of db->mDbFlags */
   145    152     u64 saved_flags;        /* Saved value of db->flags */
   146    153     int saved_nChange;      /* Saved value of db->nChange */
   147    154     int saved_nTotalChange; /* Saved value of db->nTotalChange */
   148    155     u8 saved_mTrace;        /* Saved trace settings */
   149    156     Db *pDb = 0;            /* Database to detach at end of vacuum */
   150    157     int isMemDb;            /* True if vacuuming a :memory: database */
   151    158     int nRes;               /* Bytes of reserved space at the end of each page */
   152    159     int nDb;                /* Number of attached databases */
   153    160     const char *zDbMain;    /* Schema name of database to vacuum */
          161  +  const char *zOut;       /* Name of output file */
   154    162   
   155    163     if( !db->autoCommit ){
   156    164       sqlite3SetString(pzErrMsg, db, "cannot VACUUM from within a transaction");
   157    165       return SQLITE_ERROR;
   158    166     }
   159    167     if( db->nVdbeActive>1 ){
   160    168       sqlite3SetString(pzErrMsg, db,"cannot VACUUM - SQL statements in progress");
   161    169       return SQLITE_ERROR;
   162    170     }
          171  +  if( pOut ){
          172  +    if( sqlite3_value_type(pOut)!=SQLITE_TEXT ){
          173  +      sqlite3SetString(pzErrMsg, db, "non-text filename");
          174  +      return SQLITE_ERROR;
          175  +    }
          176  +    zOut = (const char*)sqlite3_value_text(pOut);
          177  +  }else{
          178  +    zOut = "";
          179  +  }
   163    180   
   164    181     /* Save the current value of the database flags so that it can be 
   165    182     ** restored before returning. Then set the writable-schema flag, and
   166    183     ** disable CHECK and foreign key constraints.  */
   167    184     saved_flags = db->flags;
   168    185     saved_mDbFlags = db->mDbFlags;
   169    186     saved_nChange = db->nChange;
................................................................................
   190    207     ** that actually made the VACUUM run slower.  Very little journalling
   191    208     ** actually occurs when doing a vacuum since the vacuum_db is initially
   192    209     ** empty.  Only the journal header is written.  Apparently it takes more
   193    210     ** time to parse and run the PRAGMA to turn journalling off than it does
   194    211     ** to write the journal header file.
   195    212     */
   196    213     nDb = db->nDb;
   197         -  rc = execSqlF(db, pzErrMsg, "ATTACH %Q AS vacuum_db", zOut ? zOut : "");
          214  +  rc = execSqlF(db, pzErrMsg, "ATTACH %Q AS vacuum_db", zOut);
   198    215     if( rc!=SQLITE_OK ) goto end_of_vacuum;
   199    216     assert( (db->nDb-1)==nDb );
   200    217     pDb = &db->aDb[nDb];
   201    218     assert( strcmp(pDb->zDbSName,"vacuum_db")==0 );
   202    219     pTemp = pDb->pBt;
   203         -  if( zOut!=0 ){
          220  +  if( pOut ){
   204    221       sqlite3_file *id = sqlite3PagerFile(sqlite3BtreePager(pTemp));
   205    222       i64 sz = 0;
   206    223       if( id->pMethods!=0 && (sqlite3OsFileSize(id, &sz)!=SQLITE_OK || sz>0) ){
   207    224         rc = SQLITE_ERROR;
   208    225         sqlite3SetString(pzErrMsg, db, "output file already exists");
   209    226         goto end_of_vacuum;
   210    227       }
................................................................................
   228    245   
   229    246     /* Begin a transaction and take an exclusive lock on the main database
   230    247     ** file. This is done before the sqlite3BtreeGetPageSize(pMain) call below,
   231    248     ** to ensure that we do not try to change the page-size on a WAL database.
   232    249     */
   233    250     rc = execSql(db, pzErrMsg, "BEGIN");
   234    251     if( rc!=SQLITE_OK ) goto end_of_vacuum;
   235         -  rc = sqlite3BtreeBeginTrans(pMain, zOut==0 ? 2 : 0, 0);
          252  +  rc = sqlite3BtreeBeginTrans(pMain, pOut==0 ? 2 : 0, 0);
   236    253     if( rc!=SQLITE_OK ) goto end_of_vacuum;
   237    254   
   238    255     /* Do not attempt to change the page size for a WAL database */
   239    256     if( sqlite3PagerGetJournalMode(sqlite3BtreePager(pMain))
   240    257                                                  ==PAGER_JOURNALMODE_WAL ){
   241    258       db->nextPagesize = 0;
   242    259     }
................................................................................
   323    340          BTREE_DEFAULT_CACHE_SIZE, 0,  /* Preserve the default page cache size */
   324    341          BTREE_TEXT_ENCODING,      0,  /* Preserve the text encoding */
   325    342          BTREE_USER_VERSION,       0,  /* Preserve the user version */
   326    343          BTREE_APPLICATION_ID,     0,  /* Preserve the application id */
   327    344       };
   328    345   
   329    346       assert( 1==sqlite3BtreeIsInTrans(pTemp) );
   330         -    assert( zOut!=0 || 1==sqlite3BtreeIsInTrans(pMain) );
          347  +    assert( pOut!=0 || 1==sqlite3BtreeIsInTrans(pMain) );
   331    348   
   332    349       /* Copy Btree meta values */
   333    350       for(i=0; i<ArraySize(aCopy); i+=2){
   334    351         /* GetMeta() and UpdateMeta() cannot fail in this context because
   335    352         ** we already have page 1 loaded into cache and marked dirty. */
   336    353         sqlite3BtreeGetMeta(pMain, aCopy[i], &meta);
   337    354         rc = sqlite3BtreeUpdateMeta(pTemp, aCopy[i], meta+aCopy[i+1]);
   338    355         if( NEVER(rc!=SQLITE_OK) ) goto end_of_vacuum;
   339    356       }
   340    357   
   341         -    if( zOut==0 ){
          358  +    if( pOut==0 ){
   342    359         rc = sqlite3BtreeCopyFile(pMain, pTemp);
   343         -    }else{
   344         -      rc = sqlite3BtreeCommit(pMain);
   345    360       }
   346    361       if( rc!=SQLITE_OK ) goto end_of_vacuum;
   347    362       rc = sqlite3BtreeCommit(pTemp);
   348    363       if( rc!=SQLITE_OK ) goto end_of_vacuum;
   349    364   #ifndef SQLITE_OMIT_AUTOVACUUM
   350         -    if( zOut==0 ){
          365  +    if( pOut==0 ){
   351    366         sqlite3BtreeSetAutoVacuum(pMain, sqlite3BtreeGetAutoVacuum(pTemp));
   352    367       }
   353    368   #endif
   354    369     }
   355    370   
   356    371     assert( rc==SQLITE_OK );
   357         -  if( zOut==0 ){
          372  +  if( pOut==0 ){
   358    373       rc = sqlite3BtreeSetPageSize(pMain, sqlite3BtreeGetPageSize(pTemp), nRes,1);
   359    374     }
   360    375   
   361    376   end_of_vacuum:
   362    377     /* Restore the original value of db->flags */
   363    378     db->init.iDb = 0;
   364    379     db->mDbFlags = saved_mDbFlags;

Changes to src/vdbe.c.

  6680   6680     sqlite3VdbeChangeEncoding(pOut, encoding);
  6681   6681     if( rc ) goto abort_due_to_error;
  6682   6682     break;
  6683   6683   };
  6684   6684   #endif /* SQLITE_OMIT_PRAGMA */
  6685   6685   
  6686   6686   #if !defined(SQLITE_OMIT_VACUUM) && !defined(SQLITE_OMIT_ATTACH)
  6687         -/* Opcode: Vacuum P1 * * P4 *
         6687  +/* Opcode: Vacuum P1 P2 * * *
  6688   6688   **
  6689   6689   ** Vacuum the entire database P1.  P1 is 0 for "main", and 2 or more
  6690   6690   ** for an attached database.  The "temp" database may not be vacuumed.
  6691   6691   **
  6692         -** If P4 is not a NULL pointer, then it is a UTF8 string which is the
  6693         -** name of a file into which to write the result of the vacuum.  When
  6694         -** P4 is NULL, the result of vacuum overwrites the original database.
         6692  +** If P2 is not zero, then it is a register holding a string which is
         6693  +** the file into which the result of vacuum should be written.  When
         6694  +** P2 is zero, the vacuum overwrites the original database.
  6695   6695   */
  6696   6696   case OP_Vacuum: {
  6697   6697     assert( p->readOnly==0 );
  6698         -  rc = sqlite3RunVacuum(&p->zErrMsg, db, pOp->p1, pOp->p4.z);
         6698  +  rc = sqlite3RunVacuum(&p->zErrMsg, db, pOp->p1,
         6699  +                        pOp->p2 ? &aMem[pOp->p2] : 0);
  6699   6700     if( rc ) goto abort_due_to_error;
  6700   6701     break;
  6701   6702   }
  6702   6703   #endif
  6703   6704   
  6704   6705   #if !defined(SQLITE_OMIT_AUTOVACUUM)
  6705   6706   /* Opcode: IncrVacuum P1 P2 * * *

Changes to test/vacuum-into.test.

    50     50   do_catchsql_test vacuum-into-150 {
    51     51     VACUUM INTO 'out2.db';
    52     52   } {1 {output file already exists}}
    53     53   
    54     54   do_catchsql_test vacuum-into-200 {
    55     55     VACUUM main INTO ':memory:';
    56     56   } {0 {}}
           57  +
           58  +# The INTO argument can be an arbitrary expression.
           59  +#
           60  +do_execsql_test vacuum-into-300 {
           61  +  CREATE TABLE t2(name TEXT);
           62  +  INSERT INTO t2 VALUES(':memory:');
           63  +  VACUUM main INTO (SELECT name FROM t2);
           64  +} {}
           65  +do_catchsql_test vacuum-into-310 {
           66  +  VACUUM INTO null;
           67  +} {1 {non-text filename}}
    57     68   
    58     69   finish_test