/ Check-in [430d1a7d]
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:The ".ar" command deduces whether or not the target file is a ZIP or SQLAR and does the appropropriate thing. The "-z" option is omitted. The "--append" option is added to open auxiliary databases using apndvfs.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | archive-improvements
Files: files | file ages | folders
SHA3-256: 430d1a7daa823ae53606b7a158af4e7c16f62ff9b072b90606524e7c3f6131df
User & Date: drh 2018-01-10 15:17:34
Context
2018-01-10
15:53
Add the "filetype()" SQL function for interpreting file modes to the fileio.c extension. check-in: 58c0c74c user: drh tags: archive-improvements
15:17
The ".ar" command deduces whether or not the target file is a ZIP or SQLAR and does the appropropriate thing. The "-z" option is omitted. The "--append" option is added to open auxiliary databases using apndvfs. check-in: 430d1a7d user: drh tags: archive-improvements
14:00
Allow the use of ".ar -t" without specifying an archive file or the "-z" option when the command-line shell is opened on a ZIP archive. check-in: 9340a2c1 user: drh tags: archive-improvements
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/shell.c.in.

  4501   4501   /*
  4502   4502   ** Structure representing a single ".ar" command.
  4503   4503   */
  4504   4504   typedef struct ArCommand ArCommand;
  4505   4505   struct ArCommand {
  4506   4506     u8 eCmd;                        /* An AR_CMD_* value */
  4507   4507     u8 bVerbose;                    /* True if --verbose */
  4508         -  u8 bZip;                        /* True if --zip */
         4508  +  u8 bZip;                        /* True if the archive is a ZIP */
  4509   4509     u8 bDryRun;                     /* True if --dry-run */
         4510  +  u8 bAppend;                     /* True if --append */
  4510   4511     int nArg;                       /* Number of command arguments */
  4511         -  const char *zSrcTable;          /* "sqlar", "zipfile($file)" or "zip" */
         4512  +  char *zSrcTable;                /* "sqlar", "zipfile($file)" or "zip" */
  4512   4513     const char *zFile;              /* --file argument, or NULL */
  4513   4514     const char *zDir;               /* --directory argument, or NULL */
  4514   4515     char **azArg;                   /* Array of command arguments */
  4515   4516     ShellState *p;                  /* Shell state */
  4516   4517     sqlite3 *db;                    /* Database containing the archive */
  4517   4518   };
  4518   4519   
................................................................................
  4537   4538   "  -x, --extract              Extract files from archive\n"
  4538   4539   "\n"
  4539   4540   "And zero or more optional options:\n"
  4540   4541   "  -v, --verbose              Print each filename as it is processed\n"
  4541   4542   "  -f FILE, --file FILE       Operate on archive FILE (default is current db)\n"
  4542   4543   "  -C DIR, --directory DIR    Change to directory DIR to read/extract files\n"
  4543   4544   "  -n, --dryrun               Show the SQL that would have occurred\n"
  4544         -"  -z, --zip                  Operate on a ZIP archive instead of an SQLAR\n"
         4545  +"  -a, --append               Append the SQLAR to an existing file\n"
  4545   4546   "\n"
  4546   4547   "See also: http://sqlite.org/cli.html#sqlar_archive_support\n"
  4547   4548   "\n"
  4548   4549   );
  4549   4550     return SQLITE_ERROR;
  4550   4551   }
  4551   4552   
................................................................................
  4575   4576   
  4576   4577   /*
  4577   4578   ** Other (non-command) switches.
  4578   4579   */
  4579   4580   #define AR_SWITCH_VERBOSE     6
  4580   4581   #define AR_SWITCH_FILE        7
  4581   4582   #define AR_SWITCH_DIRECTORY   8
  4582         -#define AR_SWITCH_ZIP         9
         4583  +#define AR_SWITCH_APPEND      9
  4583   4584   #define AR_SWITCH_DRYRUN     10
  4584   4585   
  4585   4586   static int arProcessSwitch(ArCommand *pAr, int eSwitch, const char *zArg){
  4586   4587     switch( eSwitch ){
  4587   4588       case AR_CMD_CREATE:
  4588   4589       case AR_CMD_EXTRACT:
  4589   4590       case AR_CMD_LIST:
................................................................................
  4597   4598   
  4598   4599       case AR_SWITCH_DRYRUN:
  4599   4600         pAr->bDryRun = 1;
  4600   4601         break;
  4601   4602       case AR_SWITCH_VERBOSE:
  4602   4603         pAr->bVerbose = 1;
  4603   4604         break;
  4604         -    case AR_SWITCH_ZIP:
  4605         -      pAr->bZip = 1;
         4605  +    case AR_SWITCH_APPEND:
         4606  +      pAr->bAppend = 1;
  4606   4607         break;
  4607   4608   
  4608   4609       case AR_SWITCH_FILE:
  4609   4610         pAr->zFile = zArg;
  4610   4611         break;
  4611   4612       case AR_SWITCH_DIRECTORY:
  4612   4613         pAr->zDir = zArg;
................................................................................
  4637   4638       { "extract",   'x', AR_CMD_EXTRACT,      0 },
  4638   4639       { "list",      't', AR_CMD_LIST,         0 },
  4639   4640       { "update",    'u', AR_CMD_UPDATE,       0 },
  4640   4641       { "help",      'h', AR_CMD_HELP,         0 },
  4641   4642       { "verbose",   'v', AR_SWITCH_VERBOSE,   0 },
  4642   4643       { "file",      'f', AR_SWITCH_FILE,      1 },
  4643   4644       { "directory", 'C', AR_SWITCH_DIRECTORY, 1 },
  4644         -    { "zip",       'z', AR_SWITCH_ZIP,       0 },
         4645  +    { "append",    'a', AR_SWITCH_APPEND,    0 },
  4645   4646       { "dryrun",    'n', AR_SWITCH_DRYRUN,    0 },
  4646   4647     };
  4647   4648     int nSwitch = sizeof(aSwitch) / sizeof(struct ArSwitch);
  4648   4649     struct ArSwitch *pEnd = &aSwitch[nSwitch];
  4649   4650   
  4650   4651     if( nArg<=1 ){
  4651   4652       return arUsage(stderr);
................................................................................
  4775   4776       int i, j;
  4776   4777       sqlite3_stmt *pTest = 0;
  4777   4778   
  4778   4779       shellPreparePrintf(pAr->db, &rc, &pTest,
  4779   4780           "SELECT name FROM %s WHERE name=$name", 
  4780   4781           pAr->zSrcTable
  4781   4782       );
  4782         -    if( rc==SQLITE_OK
  4783         -     && (j = sqlite3_bind_parameter_index(pTest, "$archiveFile"))>0
  4784         -    ){
  4785         -      sqlite3_bind_text(pTest, j, pAr->zFile, -1, SQLITE_TRANSIENT);
  4786         -    }
  4787   4783       j = sqlite3_bind_parameter_index(pTest, "$name");
  4788   4784       for(i=0; i<pAr->nArg && rc==SQLITE_OK; i++){
  4789   4785         char *z = pAr->azArg[i];
  4790   4786         int n = strlen30(z);
  4791   4787         int bOk = 0;
  4792   4788         while( n>0 && z[n-1]=='/' ) n--;
  4793   4789         z[n] = '\0';
................................................................................
  4878   4874       "name",
  4879   4875       "mode, sz, datetime(mtime, 'unixepoch'), name"
  4880   4876     };
  4881   4877   
  4882   4878     char *zWhere = 0;
  4883   4879     sqlite3_stmt *pSql = 0;
  4884   4880     int rc;
  4885         -  int j;
  4886   4881   
  4887   4882     rc = arCheckEntries(pAr);
  4888   4883     arWhereClause(&rc, pAr, &zWhere);
  4889   4884   
  4890   4885     shellPreparePrintf(pAr->db, &rc, &pSql, zSql, azCols[pAr->bVerbose],
  4891   4886                        pAr->zSrcTable, zWhere);
  4892         -  if( rc==SQLITE_OK 
  4893         -   && (j = sqlite3_bind_parameter_index(pSql, "$archiveFile"))>0
  4894         -  ){
  4895         -    sqlite3_bind_text(pSql, j, pAr->zFile, -1, SQLITE_TRANSIENT);
  4896         -  }
  4897   4887     if( pAr->bDryRun ){
  4898   4888       utf8_printf(pAr->p->out, "%s\n", sqlite3_sql(pSql));
  4899   4889     }else{
  4900   4890       while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSql) ){
  4901   4891         if( pAr->bVerbose ){
  4902   4892           char zMode[11];
  4903   4893           shellModeToString(zMode, sqlite3_column_int(pSql, 0));
................................................................................
  4956   4946     shellPreparePrintf(pAr->db, &rc, &pSql, zSql1, 
  4957   4947         azExtraArg[pAr->bZip], pAr->zSrcTable, zWhere
  4958   4948     );
  4959   4949   
  4960   4950     if( rc==SQLITE_OK ){
  4961   4951       j = sqlite3_bind_parameter_index(pSql, "$dir");
  4962   4952       sqlite3_bind_text(pSql, j, zDir, -1, SQLITE_STATIC);
  4963         -    j = sqlite3_bind_parameter_index(pSql, "$archiveFile");
  4964         -    if( j ){
  4965         -      sqlite3_bind_text(pSql, j, pAr->zFile, -1, SQLITE_STATIC);
  4966         -    }
  4967   4953   
  4968   4954       /* Run the SELECT statement twice. The first time, writefile() is called
  4969   4955       ** for all archive members that should be extracted. The second time,
  4970   4956       ** only for the directories. This is because the timestamps for
  4971   4957       ** extracted directories must be reset after they are populated (as
  4972   4958       ** populating them changes the timestamp).  */
  4973   4959       for(i=0; i<2; i++){
................................................................................
  5018   5004   ** The create command is the same as update, except that it drops
  5019   5005   ** any existing "sqlar" table before beginning.
  5020   5006   */
  5021   5007   static int arCreateOrUpdateCommand(
  5022   5008     ArCommand *pAr,                 /* Command arguments and options */
  5023   5009     int bUpdate                     /* true for a --create.  false for --update */
  5024   5010   ){
  5025         -  const char *zSql = "SELECT name, mode, mtime, data FROM fsdir(?, ?)";
         5011  +  const char *zSql = "SELECT name, mode, mtime, data FROM fsdir($name, $dir)";
  5026   5012     const char *zCreate = 
  5027   5013         "CREATE TABLE IF NOT EXISTS sqlar(\n"
  5028   5014         "  name TEXT PRIMARY KEY,  -- name of the file\n"
  5029   5015         "  mode INT,               -- access permissions\n"
  5030   5016         "  mtime INT,              -- last modification time\n"
  5031   5017         "  sz INT,                 -- original file size\n"
  5032   5018         "  data BLOB               -- compressed content\n"
................................................................................
  5033   5019         ")";
  5034   5020     const char *zDrop = "DROP TABLE IF EXISTS sqlar";
  5035   5021     const char *zInsert = "REPLACE INTO sqlar VALUES(?,?,?,?,sqlar_compress(?))";
  5036   5022   
  5037   5023     sqlite3_stmt *pStmt = 0;        /* Directory traverser */
  5038   5024     sqlite3_stmt *pInsert = 0;      /* Compilation of zInsert */
  5039   5025     int i;                          /* For iterating through azFile[] */
         5026  +  int j;                          /* Parameter index */
  5040   5027     int rc;                         /* Return code */
  5041   5028   
  5042   5029     assert( pAr->bZip==0 );
  5043   5030   
  5044   5031     rc = arExecSql(pAr, "SAVEPOINT ar;");
  5045   5032     if( rc!=SQLITE_OK ) return rc;
  5046   5033   
  5047   5034     if( bUpdate==0 ){
  5048   5035       rc = arExecSql(pAr, zDrop);
  5049   5036       if( rc!=SQLITE_OK ) return rc;
  5050   5037     }
  5051   5038   
  5052   5039     rc = arExecSql(pAr, zCreate);
  5053         -  shellPrepare(pAr->db, &rc, zInsert, &pInsert);
         5040  +  if( !pAr->bDryRun ){
         5041  +    shellPrepare(pAr->db, &rc, zInsert, &pInsert);
         5042  +  }
  5054   5043     shellPrepare(pAr->db, &rc, zSql, &pStmt);
  5055         -  sqlite3_bind_text(pStmt, 2, pAr->zDir, -1, SQLITE_STATIC);
         5044  +  j = sqlite3_bind_parameter_index(pStmt, "$dir");
         5045  +  sqlite3_bind_text(pStmt, j, pAr->zDir, -1, SQLITE_STATIC);
         5046  +  if( pAr->bDryRun ){
         5047  +    utf8_printf(pAr->p->out, "%s;\n", sqlite3_sql(pStmt));
         5048  +  }
  5056   5049   
  5057   5050     for(i=0; i<pAr->nArg && rc==SQLITE_OK; i++){
  5058         -    sqlite3_bind_text(pStmt, 1, pAr->azArg[i], -1, SQLITE_STATIC);
         5051  +    j = sqlite3_bind_parameter_index(pStmt, "$name");
         5052  +    sqlite3_bind_text(pStmt, j, pAr->azArg[i], -1, SQLITE_STATIC);
  5059   5053       while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){
  5060   5054         int sz;
  5061   5055         const char *zName = (const char*)sqlite3_column_text(pStmt, 0);
  5062   5056         int mode = sqlite3_column_int(pStmt, 1);
  5063   5057         unsigned int mtime = sqlite3_column_int(pStmt, 2);
  5064   5058   
  5065   5059         if( pAr->bVerbose ){
  5066   5060           utf8_printf(pAr->p->out, "%s\n", zName);
         5061  +      }
         5062  +      if( pAr->bDryRun ){
         5063  +        utf8_printf(pAr->p->out, "%s;\n", zInsert);
         5064  +        continue;
  5067   5065         }
  5068   5066   
  5069   5067         sqlite3_bind_text(pInsert, 1, zName, -1, SQLITE_STATIC);
  5070   5068         sqlite3_bind_int(pInsert, 2, mode);
  5071   5069         sqlite3_bind_int64(pInsert, 3, (sqlite3_int64)mtime);
  5072   5070   
  5073   5071         if( S_ISDIR(mode) ){
................................................................................
  5111   5109     char **azArg,                   /* Array of arguments passed to dot command */
  5112   5110     int nArg                        /* Number of entries in azArg[] */
  5113   5111   ){
  5114   5112     ArCommand cmd;
  5115   5113     int rc;
  5116   5114     rc = arParseCommand(azArg, nArg, &cmd);
  5117   5115     if( rc==SQLITE_OK ){
         5116  +    int eDbType = SHELL_OPEN_UNSPEC;
  5118   5117       cmd.p = pState;
  5119   5118       cmd.db = pState->db;
  5120         -    cmd.zSrcTable = "sqlar";
  5121         -    if( cmd.bZip || pState->openMode==SHELL_OPEN_ZIPFILE ){
  5122         -      if( cmd.zFile==0
  5123         -       && sqlite3_table_column_metadata(cmd.db,0,"zip","name",0,0,0,0,0)==SQLITE_OK
  5124         -      ){
  5125         -        cmd.zSrcTable = "zip";
  5126         -      }else if( cmd.zFile!=0 ){
  5127         -        cmd.zSrcTable = "zipfile($archiveFile)";
         5119  +    cmd.zSrcTable = 0;
         5120  +    if( cmd.zFile ){
         5121  +      eDbType = deduceDatabaseType(cmd.zFile);
         5122  +    }else{
         5123  +      eDbType = pState->openMode;
         5124  +    }
         5125  +    if( eDbType==SHELL_OPEN_ZIPFILE ){
         5126  +      if( cmd.zFile==0 ){
         5127  +        cmd.zSrcTable = sqlite3_mprintf("zip");
  5128   5128         }else{
  5129         -        utf8_printf(stderr, "no zip archive file specified\n");
  5130         -        return SQLITE_ERROR;
         5129  +        cmd.zSrcTable = sqlite3_mprintf("zipfile(%Q)", cmd.zFile);
  5131   5130         }
  5132   5131         if( cmd.eCmd==AR_CMD_CREATE || cmd.eCmd==AR_CMD_UPDATE ){
  5133   5132           utf8_printf(stderr, "zip archives are read-only\n");
  5134         -        return SQLITE_ERROR;
         5133  +        rc = SQLITE_ERROR;
         5134  +        goto end_ar_command;
  5135   5135         }
         5136  +      cmd.bZip = 1;
  5136   5137       }else if( cmd.zFile ){
  5137   5138         int flags;
         5139  +      if( cmd.bAppend ) eDbType = SHELL_OPEN_APPENDVFS;
  5138   5140         if( cmd.eCmd==AR_CMD_CREATE || cmd.eCmd==AR_CMD_UPDATE ){
  5139   5141           flags = SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE;
  5140   5142         }else{
  5141   5143           flags = SQLITE_OPEN_READONLY;
  5142   5144         }
  5143   5145         cmd.db = 0;
  5144         -      rc = sqlite3_open_v2(cmd.zFile, &cmd.db, flags, 0);
         5146  +      if( cmd.bDryRun ){
         5147  +        utf8_printf(pState->out, "-- open database '%s'%s\n", cmd.zFile,
         5148  +             eDbType==SHELL_OPEN_APPENDVFS ? " using 'apndvfs'" : "");
         5149  +      }
         5150  +      rc = sqlite3_open_v2(cmd.zFile, &cmd.db, flags, 
         5151  +             eDbType==SHELL_OPEN_APPENDVFS ? "apndvfs" : 0);
  5145   5152         if( rc!=SQLITE_OK ){
  5146   5153           utf8_printf(stderr, "cannot open file: %s (%s)\n", 
  5147   5154               cmd.zFile, sqlite3_errmsg(cmd.db)
  5148   5155           );
  5149         -        sqlite3_close(cmd.db);
  5150         -        return rc;
         5156  +        goto end_ar_command;
  5151   5157         }
  5152   5158         sqlite3_fileio_init(cmd.db, 0, 0);
  5153   5159   #ifdef SQLITE_HAVE_ZLIB
  5154   5160         sqlite3_sqlar_init(cmd.db, 0, 0);
  5155   5161   #endif
  5156   5162       }
         5163  +    if( cmd.zSrcTable==0 ){
         5164  +      if( sqlite3_table_column_metadata(cmd.db,0,"sqlar","name",0,0,0,0,0) ){
         5165  +        utf8_printf(stderr, "database does not contain an 'sqlar' table\n");
         5166  +        rc = SQLITE_ERROR;
         5167  +        goto end_ar_command;
         5168  +      }
         5169  +      cmd.zSrcTable = sqlite3_mprintf("sqlar");
         5170  +    }
  5157   5171   
  5158   5172       switch( cmd.eCmd ){
  5159   5173         case AR_CMD_CREATE:
  5160   5174           rc = arCreateOrUpdateCommand(&cmd, 0);
  5161   5175           break;
  5162   5176   
  5163   5177         case AR_CMD_EXTRACT:
................................................................................
  5173   5187           break;
  5174   5188   
  5175   5189         default:
  5176   5190           assert( cmd.eCmd==AR_CMD_UPDATE );
  5177   5191           rc = arCreateOrUpdateCommand(&cmd, 1);
  5178   5192           break;
  5179   5193       }
  5180         -
  5181         -    if( cmd.db!=pState->db ){
  5182         -      sqlite3_close(cmd.db);
  5183         -    }
  5184   5194     }
         5195  +end_ar_command:
         5196  +  if( cmd.db!=pState->db ){
         5197  +    sqlite3_close(cmd.db);
         5198  +  }
         5199  +  sqlite3_free(cmd.zSrcTable);
  5185   5200   
  5186   5201     return rc;
  5187   5202   }
  5188   5203   /* End of the ".archive" or ".ar" command logic
  5189   5204   **********************************************************************************/
  5190   5205   #endif /* !defined(SQLITE_OMIT_VIRTUALTABLE) && defined(SQLITE_HAVE_ZLIB) */
  5191   5206   

Changes to test/shell8.test.

    97     97       set c1 ".ar --create ar1"
    98     98       set x1 ".ar --extract"
    99     99   
   100    100       set c2 ".ar --directory ar1 --create ."
   101    101       set x2 ".ar --extract --dir ar3"
   102    102   
   103    103       set c3 ".ar --creat --dir ar1 --file test_xyz.db ."
   104         -    set x3 ".ar --e  --d ar3 --f test_xyz.db"
          104  +    set x3 ".ar --e  --dir ar3 --f test_xyz.db"
   105    105     }
   106    106   
   107    107     4 {
   108    108       set c1 ".ar --cr ar1"
   109    109       set x1 ".ar --e"
   110    110   
   111    111       set c2 ".ar -C ar1 -c ."
................................................................................
   165    165   }
   166    166   
   167    167   finish_test
   168    168   
   169    169   
   170    170   
   171    171   finish_test
   172         -