/ Check-in [036e3320]
Login

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

Overview
Comment:Prototype implementation for the VACUUM INTO command.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | vacuum-into
Files: files | file ages | folders
SHA3-256:036e3320a4af36c1311b25b2e504b0079c8b33df8ad7b7e5fddad07150e6f87d
User & Date: drh 2018-12-07 17:28:28
Context
2018-12-07
20:40
Merge the VACUUM simplification from trunk. check-in: 93d92a0a user: drh tags: vacuum-into
17:28
Prototype implementation for the VACUUM INTO command. check-in: 036e3320 user: drh tags: vacuum-into
16:32
Fix the sqlite3_normalized_sql() interface so that it renders double-quoted string literals as "?". check-in: 0d8e1504 user: drh tags: trunk
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         -cmd ::= VACUUM.                {sqlite3Vacuum(pParse,0);}
  1371         -cmd ::= VACUUM nm(X).          {sqlite3Vacuum(pParse,&X);}
         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;}
  1372   1375   %endif  SQLITE_OMIT_ATTACH
  1373   1376   %endif  SQLITE_OMIT_VACUUM
  1374   1377   
  1375   1378   ///////////////////////////// The PRAGMA command /////////////////////////////
  1376   1379   //
  1377   1380   %ifndef SQLITE_OMIT_PRAGMA
  1378   1381   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*);
  3989         -int sqlite3RunVacuum(char**, sqlite3*, int);
         3988  +void sqlite3Vacuum(Parse*,Token*,Token*);
         3989  +int sqlite3RunVacuum(char**, sqlite3*, int, const char*);
  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){
          105  +void sqlite3Vacuum(Parse *pParse, Token *pNm, Token *pInto){
   106    106     Vdbe *v = sqlite3GetVdbe(pParse);
   107    107     int iDb = 0;
          108  +  assert( pInto!=0 );
   108    109     if( v==0 ) return;
   109    110     if( pNm ){
   110    111   #ifndef SQLITE_BUG_COMPATIBLE_20160819
   111    112       /* Default behavior:  Report an error if the argument to VACUUM is
   112    113       ** not recognized */
   113    114       iDb = sqlite3TwoPartName(pParse, pNm, pNm, &pNm);
   114    115       if( iDb<0 ) return;
................................................................................
   121    122       iDb = sqlite3FindDb(pParse->db, pNm);
   122    123       if( iDb<0 ) iDb = 0;
   123    124   #endif
   124    125     }
   125    126     if( iDb!=1 ){
   126    127       sqlite3VdbeAddOp1(v, OP_Vacuum, iDb);
   127    128       sqlite3VdbeUsesBtree(v, iDb);
          129  +    if( pInto->z ){
          130  +      char *zName = sqlite3NameFromToken(pParse->db, pInto);
          131  +      sqlite3VdbeChangeP4(v, -1, zName, P4_DYNAMIC);
          132  +    }
   128    133     }
   129    134     return;
   130    135   }
   131    136   
   132    137   /*
   133    138   ** This routine implements the OP_Vacuum opcode of the VDBE.
   134    139   */
   135         -int sqlite3RunVacuum(char **pzErrMsg, sqlite3 *db, int iDb){
          140  +int sqlite3RunVacuum(char **pzErrMsg, sqlite3 *db, int iDb, const char *zOut){
   136    141     int rc = SQLITE_OK;     /* Return code from service routines */
   137    142     Btree *pMain;           /* The database being vacuumed */
   138    143     Btree *pTemp;           /* The temporary database we vacuum into */
   139    144     u32 saved_mDbFlags;     /* Saved value of db->mDbFlags */
   140    145     u64 saved_flags;        /* Saved value of db->flags */
   141    146     int saved_nChange;      /* Saved value of db->nChange */
   142    147     int saved_nTotalChange; /* Saved value of db->nTotalChange */
................................................................................
   185    190     ** that actually made the VACUUM run slower.  Very little journalling
   186    191     ** actually occurs when doing a vacuum since the vacuum_db is initially
   187    192     ** empty.  Only the journal header is written.  Apparently it takes more
   188    193     ** time to parse and run the PRAGMA to turn journalling off than it does
   189    194     ** to write the journal header file.
   190    195     */
   191    196     nDb = db->nDb;
   192         -  rc = execSql(db, pzErrMsg, "ATTACH''AS vacuum_db");
          197  +  rc = execSqlF(db, pzErrMsg, "ATTACH %Q AS vacuum_db", zOut ? zOut : "");
   193    198     if( rc!=SQLITE_OK ) goto end_of_vacuum;
   194    199     assert( (db->nDb-1)==nDb );
   195    200     pDb = &db->aDb[nDb];
   196    201     assert( strcmp(pDb->zDbSName,"vacuum_db")==0 );
   197    202     pTemp = pDb->pBt;
   198    203   
   199    204     /* The call to execSql() to attach the temp database has left the file
................................................................................
   221    226   
   222    227     /* Begin a transaction and take an exclusive lock on the main database
   223    228     ** file. This is done before the sqlite3BtreeGetPageSize(pMain) call below,
   224    229     ** to ensure that we do not try to change the page-size on a WAL database.
   225    230     */
   226    231     rc = execSql(db, pzErrMsg, "BEGIN");
   227    232     if( rc!=SQLITE_OK ) goto end_of_vacuum;
   228         -  rc = sqlite3BtreeBeginTrans(pMain, 2, 0);
          233  +  rc = sqlite3BtreeBeginTrans(pMain, zOut==0 ? 2 : 0, 0);
   229    234     if( rc!=SQLITE_OK ) goto end_of_vacuum;
   230    235   
   231    236     /* Do not attempt to change the page size for a WAL database */
   232    237     if( sqlite3PagerGetJournalMode(sqlite3BtreePager(pMain))
   233    238                                                  ==PAGER_JOURNALMODE_WAL ){
   234    239       db->nextPagesize = 0;
   235    240     }
................................................................................
   316    321          BTREE_DEFAULT_CACHE_SIZE, 0,  /* Preserve the default page cache size */
   317    322          BTREE_TEXT_ENCODING,      0,  /* Preserve the text encoding */
   318    323          BTREE_USER_VERSION,       0,  /* Preserve the user version */
   319    324          BTREE_APPLICATION_ID,     0,  /* Preserve the application id */
   320    325       };
   321    326   
   322    327       assert( 1==sqlite3BtreeIsInTrans(pTemp) );
   323         -    assert( 1==sqlite3BtreeIsInTrans(pMain) );
          328  +    assert( zOut!=0 || 1==sqlite3BtreeIsInTrans(pMain) );
   324    329   
   325    330       /* Copy Btree meta values */
   326    331       for(i=0; i<ArraySize(aCopy); i+=2){
   327    332         /* GetMeta() and UpdateMeta() cannot fail in this context because
   328    333         ** we already have page 1 loaded into cache and marked dirty. */
   329    334         sqlite3BtreeGetMeta(pMain, aCopy[i], &meta);
   330    335         rc = sqlite3BtreeUpdateMeta(pTemp, aCopy[i], meta+aCopy[i+1]);
   331    336         if( NEVER(rc!=SQLITE_OK) ) goto end_of_vacuum;
   332    337       }
   333    338   
   334         -    rc = sqlite3BtreeCopyFile(pMain, pTemp);
          339  +    if( zOut==0 ){
          340  +      rc = sqlite3BtreeCopyFile(pMain, pTemp);
          341  +    }else{
          342  +      rc = sqlite3BtreeCommit(pMain);
          343  +    }
   335    344       if( rc!=SQLITE_OK ) goto end_of_vacuum;
   336    345       rc = sqlite3BtreeCommit(pTemp);
   337    346       if( rc!=SQLITE_OK ) goto end_of_vacuum;
   338    347   #ifndef SQLITE_OMIT_AUTOVACUUM
   339         -    sqlite3BtreeSetAutoVacuum(pMain, sqlite3BtreeGetAutoVacuum(pTemp));
          348  +    if( zOut==0 ){
          349  +      sqlite3BtreeSetAutoVacuum(pMain, sqlite3BtreeGetAutoVacuum(pTemp));
          350  +    }
   340    351   #endif
   341    352     }
   342    353   
   343    354     assert( rc==SQLITE_OK );
   344         -  rc = sqlite3BtreeSetPageSize(pMain, sqlite3BtreeGetPageSize(pTemp), nRes,1);
          355  +  if( zOut==0 ){
          356  +    rc = sqlite3BtreeSetPageSize(pMain, sqlite3BtreeGetPageSize(pTemp), nRes,1);
          357  +  }
   345    358   
   346    359   end_of_vacuum:
   347    360     /* Restore the original value of db->flags */
   348    361     db->init.iDb = 0;
   349    362     db->mDbFlags = saved_mDbFlags;
   350    363     db->flags = saved_flags;
   351    364     db->nChange = saved_nChange;
................................................................................
   364    377   
   365    378     if( pDb ){
   366    379       sqlite3BtreeClose(pDb->pBt);
   367    380       pDb->pBt = 0;
   368    381       pDb->pSchema = 0;
   369    382     }
   370    383   
   371         -  /* This both clears the schemas and reduces the size of the db->aDb[]
   372         -  ** array. */ 
   373         -  sqlite3ResetAllSchemasOfConnection(db);
          384  +  if( zOut==0 ){
          385  +    /* This both clears the schemas and reduces the size of the db->aDb[]
          386  +    ** array. */ 
          387  +    sqlite3ResetAllSchemasOfConnection(db);
          388  +  }
   374    389   
   375    390     return rc;
   376    391   }
   377    392   
   378    393   #endif  /* SQLITE_OMIT_VACUUM && SQLITE_OMIT_ATTACH */

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 * * * *
         6687  +/* Opcode: Vacuum P1 * * P4 *
  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  +**
         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.
  6691   6695   */
  6692   6696   case OP_Vacuum: {
  6693   6697     assert( p->readOnly==0 );
  6694         -  rc = sqlite3RunVacuum(&p->zErrMsg, db, pOp->p1);
         6698  +  rc = sqlite3RunVacuum(&p->zErrMsg, db, pOp->p1, pOp->p4.z);
  6695   6699     if( rc ) goto abort_due_to_error;
  6696   6700     break;
  6697   6701   }
  6698   6702   #endif
  6699   6703   
  6700   6704   #if !defined(SQLITE_OMIT_AUTOVACUUM)
  6701   6705   /* Opcode: IncrVacuum P1 P2 * * *