/ Check-in [9404765e]
Login

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

Overview
Comment:Enhance the ".ar" command in the CLI so that it is able to update and create ZIP Archives.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 9404765ef7487013f01ecf24c0a1f70040cd11e7dbb6378646d15de4e5660a40
User & Date: drh 2018-03-09 21:54:01
Context
2018-03-09
22:18
Fix the .archive command in the CLI so that it actually compresses content. check-in: 3c2e3c2d user: drh tags: trunk
21:54
Enhance the ".ar" command in the CLI so that it is able to update and create ZIP Archives. check-in: 9404765e user: drh tags: trunk
16:37
Setting ".stats 2" in the CLI causes column metadata for each prepared statement to be displayed. check-in: 7fea00fd user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/shell.c.in.

  3428   3428     return 1;
  3429   3429   }
  3430   3430   #endif
  3431   3431   
  3432   3432   /*
  3433   3433   ** Try to deduce the type of file for zName based on its content.  Return
  3434   3434   ** one of the SHELL_OPEN_* constants.
         3435  +**
         3436  +** If the file does not exist or is empty but its name looks like a ZIP
         3437  +** archive and the dfltZip flag is true, then assume it is a ZIP archive.
         3438  +** Otherwise, assume an ordinary database regardless of the filename if
         3439  +** the type cannot be determined from content.
  3435   3440   */
  3436         -static int deduceDatabaseType(const char *zName){
         3441  +static int deduceDatabaseType(const char *zName, int dfltZip){
  3437   3442     FILE *f = fopen(zName, "rb");
  3438   3443     size_t n;
  3439   3444     int rc = SHELL_OPEN_UNSPEC;
  3440   3445     char zBuf[100];
  3441         -  if( f==0 ) return SHELL_OPEN_NORMAL;
         3446  +  if( f==0 ){
         3447  +    if( dfltZip && sqlite3_strlike("%.zip",zName,0)==0 ) return SHELL_OPEN_ZIPFILE;
         3448  +    return SHELL_OPEN_NORMAL;
         3449  +  }
  3442   3450     fseek(f, -25, SEEK_END);
  3443   3451     n = fread(zBuf, 25, 1, f);
  3444   3452     if( n==1 && memcmp(zBuf, "Start-Of-SQLite3-", 17)==0 ){
  3445   3453       rc = SHELL_OPEN_APPENDVFS;
  3446   3454     }else{
  3447   3455       fseek(f, -22, SEEK_END);
  3448   3456       n = fread(zBuf, 22, 1, f);
  3449   3457       if( n==1 && zBuf[0]==0x50 && zBuf[1]==0x4b && zBuf[2]==0x05
  3450   3458          && zBuf[3]==0x06 ){
  3451   3459         rc = SHELL_OPEN_ZIPFILE;
         3460  +    }else if( n==0 && dfltZip && sqlite3_strlike("%.zip",zName,0)==0 ){
         3461  +      return SHELL_OPEN_ZIPFILE;
  3452   3462       }
  3453   3463     }
  3454   3464     fclose(f);
  3455   3465     return rc;  
  3456   3466   }
  3457   3467   
  3458   3468   /*
................................................................................
  3459   3469   ** Make sure the database is open.  If it is not, then open it.  If
  3460   3470   ** the database fails to open, print an error message and exit.
  3461   3471   */
  3462   3472   static void open_db(ShellState *p, int keepAlive){
  3463   3473     if( p->db==0 ){
  3464   3474       sqlite3_initialize();
  3465   3475       if( p->openMode==SHELL_OPEN_UNSPEC && access(p->zDbFilename,0)==0 ){
  3466         -      p->openMode = (u8)deduceDatabaseType(p->zDbFilename);
         3476  +      p->openMode = (u8)deduceDatabaseType(p->zDbFilename, 0);
  3467   3477       }
  3468   3478       switch( p->openMode ){
  3469   3479         case SHELL_OPEN_APPENDVFS: {
  3470   3480           sqlite3_open_v2(p->zDbFilename, &p->db, 
  3471   3481              SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, "apndvfs");
  3472   3482           break;
  3473   3483         }
................................................................................
  5254   5264         "  name TEXT PRIMARY KEY,  -- name of the file\n"
  5255   5265         "  mode INT,               -- access permissions\n"
  5256   5266         "  mtime INT,              -- last modification time\n"
  5257   5267         "  sz INT,                 -- original file size\n"
  5258   5268         "  data BLOB               -- compressed content\n"
  5259   5269         ")";
  5260   5270     const char *zDrop = "DROP TABLE IF EXISTS sqlar";
  5261         -  const char *zInsertFmt = 
  5262         -     "REPLACE INTO sqlar(name,mode,mtime,sz,data)\n"
         5271  +  const char *zInsertFmt[2] = {
         5272  +     "REPLACE INTO %s(name,mode,mtime,sz,data)\n"
  5263   5273        "  SELECT\n"
  5264   5274        "    %s,\n"
  5265   5275        "    mode,\n"
  5266   5276        "    mtime,\n"
  5267   5277        "    CASE substr(lsmode(mode),1,1)\n"
  5268   5278        "      WHEN '-' THEN length(data)\n"
  5269   5279        "      WHEN 'd' THEN 0\n"
  5270   5280        "      ELSE -1 END,\n"
  5271         -     "    CASE WHEN lsmode(mode) LIKE 'd%%' THEN NULL else data END\n"
         5281  +     "    data\n"
         5282  +     "  FROM fsdir(%Q,%Q)\n"
         5283  +     "  WHERE lsmode(mode) NOT LIKE '?%%';",
         5284  +     "REPLACE INTO %s(name,mode,mtime,data)\n"
         5285  +     "  SELECT\n"
         5286  +     "    %s,\n"
         5287  +     "    mode,\n"
         5288  +     "    mtime,\n"
         5289  +     "    data\n"
  5272   5290        "  FROM fsdir(%Q,%Q)\n"
  5273         -     "  WHERE lsmode(mode) NOT LIKE '?%%';";
         5291  +     "  WHERE lsmode(mode) NOT LIKE '?%%';"
         5292  +  };
  5274   5293     int i;                          /* For iterating through azFile[] */
  5275   5294     int rc;                         /* Return code */
         5295  +  const char *zTab = 0;           /* SQL table into which to insert */
         5296  +  char *zSql;
         5297  +  char zTemp[50];
  5276   5298   
         5299  +  arExecSql(pAr, "PRAGMA page_size=512");
  5277   5300     rc = arExecSql(pAr, "SAVEPOINT ar;");
  5278   5301     if( rc!=SQLITE_OK ) return rc;
  5279         -  if( bUpdate==0 ){
  5280         -    rc = arExecSql(pAr, zDrop);
  5281         -    if( rc!=SQLITE_OK ) return rc;
         5302  +  zTemp[0] = 0; 
         5303  +  if( pAr->bZip ){
         5304  +    /* Initialize the zipfile virtual table, if necessary */
         5305  +    if( pAr->zFile ){
         5306  +      sqlite3_uint64 r;
         5307  +      sqlite3_randomness(sizeof(r),&r);
         5308  +      sqlite3_snprintf(sizeof(zTemp),zTemp,"zip%016llx",r);
         5309  +      zTab = zTemp;
         5310  +      zSql = sqlite3_mprintf(
         5311  +         "CREATE VIRTUAL TABLE temp.%s USING zipfile(%Q)",
         5312  +         zTab, pAr->zFile
         5313  +      );
         5314  +      rc = arExecSql(pAr, zSql);
         5315  +      sqlite3_free(zSql);
         5316  +    }else{
         5317  +      zTab = "zip";
         5318  +    }
         5319  +  }else{
         5320  +    /* Initialize the table for an SQLAR */
         5321  +    zTab = "sqlar";
         5322  +    if( bUpdate==0 ){
         5323  +      rc = arExecSql(pAr, zDrop);
         5324  +      if( rc!=SQLITE_OK ) goto end_ar_transaction;
         5325  +    }
         5326  +    rc = arExecSql(pAr, zCreate);
  5282   5327     }
  5283         -  rc = arExecSql(pAr, zCreate);
  5284   5328     for(i=0; i<pAr->nArg && rc==SQLITE_OK; i++){
  5285         -    char *zSql = sqlite3_mprintf(zInsertFmt,
         5329  +    char *zSql = sqlite3_mprintf(zInsertFmt[pAr->bZip], zTab,
  5286   5330           pAr->bVerbose ? "shell_putsnl(name)" : "name",
  5287   5331           pAr->azArg[i], pAr->zDir);
  5288   5332       rc = arExecSql(pAr, zSql);
  5289   5333       sqlite3_free(zSql);
  5290   5334     }
         5335  +end_ar_transaction:
  5291   5336     if( rc!=SQLITE_OK ){
  5292   5337       arExecSql(pAr, "ROLLBACK TO ar; RELEASE ar;");
  5293   5338     }else{
  5294   5339       rc = arExecSql(pAr, "RELEASE ar;");
         5340  +    if( pAr->bZip && pAr->zFile ){
         5341  +      zSql = sqlite3_mprintf("DROP TABLE %s", zTemp);
         5342  +      arExecSql(pAr, zSql);
         5343  +      sqlite3_free(zSql);
         5344  +    }
  5295   5345     }
  5296   5346     return rc;
  5297   5347   }
  5298   5348   
  5299   5349   /*
  5300   5350   ** Implementation of ".ar" dot command.
  5301   5351   */
................................................................................
  5309   5359     memset(&cmd, 0, sizeof(cmd));
  5310   5360     rc = arParseCommand(azArg, nArg, &cmd);
  5311   5361     if( rc==SQLITE_OK ){
  5312   5362       int eDbType = SHELL_OPEN_UNSPEC;
  5313   5363       cmd.p = pState;
  5314   5364       cmd.db = pState->db;
  5315   5365       if( cmd.zFile ){
  5316         -      eDbType = deduceDatabaseType(cmd.zFile);
         5366  +      eDbType = deduceDatabaseType(cmd.zFile, 1);
  5317   5367       }else{
  5318   5368         eDbType = pState->openMode;
  5319   5369       }
  5320   5370       if( eDbType==SHELL_OPEN_ZIPFILE ){
  5321         -      if( cmd.zFile==0 ){
  5322         -        cmd.zSrcTable = sqlite3_mprintf("zip");
  5323         -      }else{
  5324         -        cmd.zSrcTable = sqlite3_mprintf("zipfile(%Q)", cmd.zFile);
  5325         -      }
  5326         -      if( cmd.eCmd==AR_CMD_CREATE || cmd.eCmd==AR_CMD_UPDATE ){
  5327         -        utf8_printf(stderr, "zip archives are read-only\n");
  5328         -        rc = SQLITE_ERROR;
  5329         -        goto end_ar_command;
         5371  +      if( cmd.eCmd==AR_CMD_EXTRACT || cmd.eCmd==AR_CMD_LIST ){
         5372  +        if( cmd.zFile==0 ){
         5373  +          cmd.zSrcTable = sqlite3_mprintf("zip");
         5374  +        }else{
         5375  +          cmd.zSrcTable = sqlite3_mprintf("zipfile(%Q)", cmd.zFile);
         5376  +        }
  5330   5377         }
  5331   5378         cmd.bZip = 1;
  5332   5379       }else if( cmd.zFile ){
  5333   5380         int flags;
  5334   5381         if( cmd.bAppend ) eDbType = SHELL_OPEN_APPENDVFS;
  5335   5382         if( cmd.eCmd==AR_CMD_CREATE || cmd.eCmd==AR_CMD_UPDATE ){
  5336   5383           flags = SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE;
................................................................................
  5354   5401   #ifdef SQLITE_HAVE_ZLIB
  5355   5402         sqlite3_sqlar_init(cmd.db, 0, 0);
  5356   5403   #endif
  5357   5404         sqlite3_create_function(cmd.db, "shell_putsnl", 1, SQLITE_UTF8, cmd.p,
  5358   5405                                 shellPutsFunc, 0, 0);
  5359   5406   
  5360   5407       }
  5361         -    if( cmd.zSrcTable==0 ){
         5408  +    if( cmd.zSrcTable==0 && cmd.bZip==0 ){
  5362   5409         if( cmd.eCmd!=AR_CMD_CREATE
  5363   5410          && sqlite3_table_column_metadata(cmd.db,0,"sqlar","name",0,0,0,0,0)
  5364   5411         ){
  5365   5412           utf8_printf(stderr, "database does not contain an 'sqlar' table\n");
  5366   5413           rc = SQLITE_ERROR;
  5367   5414           goto end_ar_command;
  5368   5415         }