/ Check-in [a532a0f6]
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:Have the shell tool ".ar --list" and ".ar --extract" commands support zip files. Currently the "-zip" switch is required.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | sqlar-shell-support
Files: files | file ages | folders
SHA3-256: a532a0f6fd59e81086d46f09151ba7fb26725198231d902c71d0f95cb01dbe91
User & Date: dan 2017-12-27 18:54:11
Context
2017-12-27
21:13
Improve the shell tool ".ar --list --verbose" command. check-in: b64681a6 user: dan tags: sqlar-shell-support
18:54
Have the shell tool ".ar --list" and ".ar --extract" commands support zip files. Currently the "-zip" switch is required. check-in: a532a0f6 user: dan tags: sqlar-shell-support
2017-12-26
20:39
Add new file ext/misc/zipfile.c, containing a virtual table for read-only access to simple zip archives. check-in: 8e366b99 user: dan tags: sqlar-shell-support
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to ext/misc/zipfile.c.

675
676
677
678
679
680
681
















































682
683
684
685
686
687
688
689
690
691
692
693




694
695
696

  int rc = sqlite3_create_module(db, "zipfile"  , &zipfileModule, 0);
  return rc;
}
#else         /* SQLITE_OMIT_VIRTUALTABLE */
# define zipfileRegister(x) SQLITE_OK
#endif

















































#ifdef _WIN32
__declspec(dllexport)
#endif
int sqlite3_zipfile_init(
  sqlite3 *db, 
  char **pzErrMsg, 
  const sqlite3_api_routines *pApi
){
  int rc = SQLITE_OK;
  SQLITE_EXTENSION_INIT2(pApi);
  (void)pzErrMsg;  /* Unused parameter */




  return zipfileRegister(db);
}








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>












>
>
>
>



675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748

  int rc = sqlite3_create_module(db, "zipfile"  , &zipfileModule, 0);
  return rc;
}
#else         /* SQLITE_OMIT_VIRTUALTABLE */
# define zipfileRegister(x) SQLITE_OK
#endif

#include <zlib.h>

/*
** zipfile_uncompress(DATA, SZ, METHOD)
*/
static void zipfileUncompressFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  int iMethod;

  iMethod = sqlite3_value_int(argv[2]);
  if( iMethod==0 ){
    sqlite3_result_value(context, argv[0]);
  }else if( iMethod==8 ){
    Byte *res;
    int sz = sqlite3_value_int(argv[1]);
    z_stream str;
    memset(&str, 0, sizeof(str));
    str.next_in = (Byte*)sqlite3_value_blob(argv[0]);
    str.avail_in = sqlite3_value_bytes(argv[0]);
    res = str.next_out = (Byte*)sqlite3_malloc(sz);
    if( res==0 ){
      sqlite3_result_error_nomem(context);
    }else{
      int err;
      str.avail_out = sz;

      err = inflateInit2(&str, -15);
      if( err!=Z_OK ){
        zipfileCtxErrorMsg(context, "inflateInit2() failed (%d)", err);
      }else{
        err = inflate(&str, Z_NO_FLUSH);
        if( err!=Z_STREAM_END ){
          zipfileCtxErrorMsg(context, "inflate() failed (%d)", err);
        }else{
          sqlite3_result_blob(context, res, sz, SQLITE_TRANSIENT);
        }
      }
      sqlite3_free(res);
      inflateEnd(&str);
    }
  }else{
    zipfileCtxErrorMsg(context, "unrecognized compression method: %d", iMethod);
  }
}

#ifdef _WIN32
__declspec(dllexport)
#endif
int sqlite3_zipfile_init(
  sqlite3 *db, 
  char **pzErrMsg, 
  const sqlite3_api_routines *pApi
){
  int rc = SQLITE_OK;
  SQLITE_EXTENSION_INIT2(pApi);
  (void)pzErrMsg;  /* Unused parameter */
  rc = sqlite3_create_function(db, "zipfile_uncompress", 3,
      SQLITE_UTF8, 0, zipfileUncompressFunc, 0, 0
  );
  if( rc!=SQLITE_OK ) return rc;
  return zipfileRegister(db);
}

Changes to src/shell.c.in.

4234
4235
4236
4237
4238
4239
4240
4241






4242
4243
4244
4245
4246
4247
4248
4249
4250
4251
4252

4253
4254
4255
4256
4257
4258
4259
....
4311
4312
4313
4314
4315
4316
4317

4318
4319
4320
4321
4322
4323
4324
....
4327
4328
4329
4330
4331
4332
4333



4334
4335
4336
4337
4338
4339
4340
....
4364
4365
4366
4367
4368
4369
4370
4371

4372
4373
4374
4375
4376
4377
4378
....
4497
4498
4499
4500
4501
4502
4503
4504





4505
4506
4507
4508
4509
4510
4511
....
4560
4561
4562
4563
4564
4565
4566
4567


4568
4569
4570
4571
4572
4573
4574
4575



4576
4577
4578


4579
4580
4581
4582
4583
4584
4585
4586
4587
4588
4589
4590
4591










4592
4593
4594
4595
4596
4597
4598
....
4611
4612
4613
4614
4615
4616
4617
4618



4619
4620



4621
4622
4623
4624
4625
4626
4627
....
4671
4672
4673
4674
4675
4676
4677


4678
4679
4680
4681
4682
4683
4684
....
4768
4769
4770
4771
4772
4773
4774










4775
4776
4777
4778
4779
4780
4781
4782
}

static void shellReset(
  int *pRc, 
  sqlite3_stmt *pStmt
){
  int rc = sqlite3_reset(pStmt);
  if( *pRc==SQLITE_OK ) *pRc = rc;






}

/* 
** Structure representing a single ".ar" command.
*/
typedef struct ArCommand ArCommand;
struct ArCommand {
  int eCmd;                       /* An AR_CMD_* value */
  const char *zFile;              /* --file argument, or NULL */
  const char *zDir;               /* --directory argument, or NULL */
  int bVerbose;                   /* True if --verbose */

  int nArg;                       /* Number of command arguments */
  char **azArg;                   /* Array of command arguments */
};

/*
** Print a usage message for the .ar command to stderr and return SQLITE_ERROR.
*/
................................................................................

/*
** Other (non-command) switches.
*/
#define AR_SWITCH_VERBOSE   6
#define AR_SWITCH_FILE      7
#define AR_SWITCH_DIRECTORY 8


static int arProcessSwitch(ArCommand *pAr, int eSwitch, const char *zArg){
  switch( eSwitch ){
    case AR_CMD_CREATE:
    case AR_CMD_EXTRACT:
    case AR_CMD_LIST:
    case AR_CMD_UPDATE:
................................................................................
        return arErrorMsg("multiple command options");
      }
      pAr->eCmd = eSwitch;
      break;

    case AR_SWITCH_VERBOSE:
      pAr->bVerbose = 1;



      break;

    case AR_SWITCH_FILE:
      pAr->zFile = zArg;
      break;
    case AR_SWITCH_DIRECTORY:
      pAr->zDir = zArg;
................................................................................
    { 'c', "create",    AR_CMD_CREATE, 0 },
    { 'x', "extract",   AR_CMD_EXTRACT, 0 },
    { 't', "list",      AR_CMD_LIST, 0 },
    { 'u', "update",    AR_CMD_UPDATE, 0 },
    { 'h', "help",      AR_CMD_HELP, 0 },
    { 'v', "verbose",   AR_SWITCH_VERBOSE, 0 },
    { 'f', "file",      AR_SWITCH_FILE, 1 },
    { 'C', "directory", AR_SWITCH_DIRECTORY, 1 }

  };
  int nSwitch = sizeof(aSwitch) / sizeof(struct ArSwitch);
  struct ArSwitch *pEnd = &aSwitch[nSwitch];

  if( nArg<=1 ){
    return arUsage(stderr);
  }else{
................................................................................
*/
static int arCheckEntries(sqlite3 *db, ArCommand *pAr){
  int rc = SQLITE_OK;
  if( pAr->nArg ){
    int i;
    sqlite3_stmt *pTest = 0;

    shellPrepare(db, &rc, "SELECT name FROM sqlar WHERE name=?", &pTest);





    for(i=0; i<pAr->nArg && rc==SQLITE_OK; i++){
      char *z = pAr->azArg[i];
      int n = strlen(z);
      int bOk = 0;
      while( n>0 && z[n-1]=='/' ) n--;
      z[n] = '\0';
      sqlite3_bind_text(pTest, 1, z, -1, SQLITE_STATIC);
................................................................................
  *pzWhere = zWhere;
}

/*
** Implementation of .ar "lisT" command. 
*/
static int arListCommand(ShellState *p, sqlite3 *db, ArCommand *pAr){
  const char *zSql = "SELECT name FROM sqlar WHERE %s"; 


  char *zWhere = 0;
  sqlite3_stmt *pSql = 0;
  int rc;

  rc = arCheckEntries(db, pAr);
  arWhereClause(&rc, pAr, &zWhere);

  shellPreparePrintf(db, &rc, &pSql, zSql, zWhere);



  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSql) ){
    raw_printf(p->out, "%s\n", sqlite3_column_text(pSql, 0));
  }


  return rc;
}


/*
** Implementation of .ar "eXtract" command. 
*/
static int arExtractCommand(ShellState *p, sqlite3 *db, ArCommand *pAr){
  const char *zSql1 = 
    "SELECT "
    "  :1 || name, "
    "  writefile(:1 || name, sqlar_uncompress(data, sz), mode, mtime) "
    "FROM sqlar WHERE (%s) AND (data IS NULL OR :2 = 0)";











  struct timespec times[2];
  sqlite3_stmt *pSql = 0;
  int rc = SQLITE_OK;
  char *zDir = 0;
  char *zWhere = 0;
  int i;
................................................................................
    }
    if( zDir==0 ) rc = SQLITE_NOMEM;
  }

  memset(times, 0, sizeof(times));
  times[0].tv_sec = time(0);

  shellPreparePrintf(db, &rc, &pSql, zSql1, zWhere);



  if( rc==SQLITE_OK ){
    sqlite3_bind_text(pSql, 1, zDir, -1, SQLITE_STATIC);




    /* Run the SELECT statement twice. The first time, writefile() is called
    ** for all archive members that should be extracted. The second time,
    ** only for the directories. This is because the timestamps for
    ** extracted directories must be reset after they are populated (as
    ** populating them changes the timestamp).  */
    for(i=0; i<2; i++){
................................................................................
  const char *zDrop = "DROP TABLE IF EXISTS sqlar";
  const char *zInsert = "REPLACE INTO sqlar VALUES(?,?,?,?,sqlar_compress(?))";

  sqlite3_stmt *pStmt = 0;        /* Directory traverser */
  sqlite3_stmt *pInsert = 0;      /* Compilation of zInsert */
  int i;                          /* For iterating through azFile[] */
  int rc;                         /* Return code */



  rc = sqlite3_exec(db, "SAVEPOINT ar;", 0, 0, 0);
  if( rc!=SQLITE_OK ) return rc;

  if( bUpdate==0 ){
    rc = sqlite3_exec(db, zDrop, 0, 0, 0);
    if( rc!=SQLITE_OK ) return rc;
................................................................................
){
  ArCommand cmd;
  int rc;
  rc = arParseCommand(azArg, nArg, &cmd);
  if( rc==SQLITE_OK ){
    sqlite3 *db = 0;              /* Database handle to use as archive */











    if( cmd.zFile ){
      int flags;
      if( cmd.eCmd==AR_CMD_CREATE || cmd.eCmd==AR_CMD_UPDATE ){
        flags = SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE;
      }else{
        flags = SQLITE_OPEN_READONLY;
      }
      rc = sqlite3_open_v2(cmd.zFile, &db, flags, 0);







|
>
>
>
>
>
>











>







 







>







 







>
>
>







 







|
>







 







|
>
>
>
>
>







 







|
>
>







|
>
>
>



>
>











|
|
>
>
>
>
>
>
>
>
>
>







 







|
>
>
>


>
>
>







 







>
>







 







>
>
>
>
>
>
>
>
>
>
|







4234
4235
4236
4237
4238
4239
4240
4241
4242
4243
4244
4245
4246
4247
4248
4249
4250
4251
4252
4253
4254
4255
4256
4257
4258
4259
4260
4261
4262
4263
4264
4265
4266
....
4318
4319
4320
4321
4322
4323
4324
4325
4326
4327
4328
4329
4330
4331
4332
....
4335
4336
4337
4338
4339
4340
4341
4342
4343
4344
4345
4346
4347
4348
4349
4350
4351
....
4375
4376
4377
4378
4379
4380
4381
4382
4383
4384
4385
4386
4387
4388
4389
4390
....
4509
4510
4511
4512
4513
4514
4515
4516
4517
4518
4519
4520
4521
4522
4523
4524
4525
4526
4527
4528
....
4577
4578
4579
4580
4581
4582
4583
4584
4585
4586
4587
4588
4589
4590
4591
4592
4593
4594
4595
4596
4597
4598
4599
4600
4601
4602
4603
4604
4605
4606
4607
4608
4609
4610
4611
4612
4613
4614
4615
4616
4617
4618
4619
4620
4621
4622
4623
4624
4625
4626
4627
4628
4629
4630
4631
4632
....
4645
4646
4647
4648
4649
4650
4651
4652
4653
4654
4655
4656
4657
4658
4659
4660
4661
4662
4663
4664
4665
4666
4667
....
4711
4712
4713
4714
4715
4716
4717
4718
4719
4720
4721
4722
4723
4724
4725
4726
....
4810
4811
4812
4813
4814
4815
4816
4817
4818
4819
4820
4821
4822
4823
4824
4825
4826
4827
4828
4829
4830
4831
4832
4833
4834
}

static void shellReset(
  int *pRc, 
  sqlite3_stmt *pStmt
){
  int rc = sqlite3_reset(pStmt);
  if( *pRc==SQLITE_OK ){
    if( rc!=SQLITE_OK ){
      sqlite3 *db = sqlite3_db_handle(pStmt);
      raw_printf(stderr, "SQL error: %s\n", sqlite3_errmsg(db));
    }
    *pRc = rc;
  }
}

/* 
** Structure representing a single ".ar" command.
*/
typedef struct ArCommand ArCommand;
struct ArCommand {
  int eCmd;                       /* An AR_CMD_* value */
  const char *zFile;              /* --file argument, or NULL */
  const char *zDir;               /* --directory argument, or NULL */
  int bVerbose;                   /* True if --verbose */
  int bZip;                       /* True if --zip */
  int nArg;                       /* Number of command arguments */
  char **azArg;                   /* Array of command arguments */
};

/*
** Print a usage message for the .ar command to stderr and return SQLITE_ERROR.
*/
................................................................................

/*
** Other (non-command) switches.
*/
#define AR_SWITCH_VERBOSE   6
#define AR_SWITCH_FILE      7
#define AR_SWITCH_DIRECTORY 8
#define AR_SWITCH_ZIP       9

static int arProcessSwitch(ArCommand *pAr, int eSwitch, const char *zArg){
  switch( eSwitch ){
    case AR_CMD_CREATE:
    case AR_CMD_EXTRACT:
    case AR_CMD_LIST:
    case AR_CMD_UPDATE:
................................................................................
        return arErrorMsg("multiple command options");
      }
      pAr->eCmd = eSwitch;
      break;

    case AR_SWITCH_VERBOSE:
      pAr->bVerbose = 1;
      break;
    case AR_SWITCH_ZIP:
      pAr->bZip = 1;
      break;

    case AR_SWITCH_FILE:
      pAr->zFile = zArg;
      break;
    case AR_SWITCH_DIRECTORY:
      pAr->zDir = zArg;
................................................................................
    { 'c', "create",    AR_CMD_CREATE, 0 },
    { 'x', "extract",   AR_CMD_EXTRACT, 0 },
    { 't', "list",      AR_CMD_LIST, 0 },
    { 'u', "update",    AR_CMD_UPDATE, 0 },
    { 'h', "help",      AR_CMD_HELP, 0 },
    { 'v', "verbose",   AR_SWITCH_VERBOSE, 0 },
    { 'f', "file",      AR_SWITCH_FILE, 1 },
    { 'C', "directory", AR_SWITCH_DIRECTORY, 1 },
    { 'z', "zip",       AR_SWITCH_ZIP, 0 }
  };
  int nSwitch = sizeof(aSwitch) / sizeof(struct ArSwitch);
  struct ArSwitch *pEnd = &aSwitch[nSwitch];

  if( nArg<=1 ){
    return arUsage(stderr);
  }else{
................................................................................
*/
static int arCheckEntries(sqlite3 *db, ArCommand *pAr){
  int rc = SQLITE_OK;
  if( pAr->nArg ){
    int i;
    sqlite3_stmt *pTest = 0;

    shellPreparePrintf(db, &rc, &pTest, "SELECT name FROM %s WHERE name=?1", 
        pAr->bZip ? "zipfile(?2)" : "sqlar"
    );
    if( rc==SQLITE_OK && pAr->bZip ){
      sqlite3_bind_text(pTest, 2, pAr->zFile, -1, SQLITE_TRANSIENT);
    }
    for(i=0; i<pAr->nArg && rc==SQLITE_OK; i++){
      char *z = pAr->azArg[i];
      int n = strlen(z);
      int bOk = 0;
      while( n>0 && z[n-1]=='/' ) n--;
      z[n] = '\0';
      sqlite3_bind_text(pTest, 1, z, -1, SQLITE_STATIC);
................................................................................
  *pzWhere = zWhere;
}

/*
** Implementation of .ar "lisT" command. 
*/
static int arListCommand(ShellState *p, sqlite3 *db, ArCommand *pAr){
  const char *zSql = "SELECT name FROM %s WHERE %s"; 
  const char *zTbl = (pAr->bZip ? "zipfile(?)" : "sqlar");

  char *zWhere = 0;
  sqlite3_stmt *pSql = 0;
  int rc;

  rc = arCheckEntries(db, pAr);
  arWhereClause(&rc, pAr, &zWhere);

  shellPreparePrintf(db, &rc, &pSql, zSql, zTbl, zWhere);
  if( rc==SQLITE_OK && pAr->bZip ){
    sqlite3_bind_text(pSql, 1, pAr->zFile, -1, SQLITE_TRANSIENT);
  }
  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSql) ){
    raw_printf(p->out, "%s\n", sqlite3_column_text(pSql, 0));
  }

  shellFinalize(&rc, pSql);
  return rc;
}


/*
** Implementation of .ar "eXtract" command. 
*/
static int arExtractCommand(ShellState *p, sqlite3 *db, ArCommand *pAr){
  const char *zSql1 = 
    "SELECT "
    "  :1 || name, "
    "  writefile(?1 || name, %s, mode, mtime) "
    "FROM %s WHERE (%s) AND (data IS NULL OR ?2 = 0)";

  const char *azExtraArg[] = { 
    "sqlar_uncompress(data, sz)",
    "zipfile_uncompress(data, sz, method)"
  };
  const char *azSource[] = {
    "sqlar", "zipfile(?3)"
  };



  struct timespec times[2];
  sqlite3_stmt *pSql = 0;
  int rc = SQLITE_OK;
  char *zDir = 0;
  char *zWhere = 0;
  int i;
................................................................................
    }
    if( zDir==0 ) rc = SQLITE_NOMEM;
  }

  memset(times, 0, sizeof(times));
  times[0].tv_sec = time(0);

  shellPreparePrintf(db, &rc, &pSql, zSql1, 
      azExtraArg[pAr->bZip], azSource[pAr->bZip], zWhere
  );

  if( rc==SQLITE_OK ){
    sqlite3_bind_text(pSql, 1, zDir, -1, SQLITE_STATIC);
    if( pAr->bZip ){
      sqlite3_bind_text(pSql, 3, pAr->zFile, -1, SQLITE_STATIC);
    }

    /* Run the SELECT statement twice. The first time, writefile() is called
    ** for all archive members that should be extracted. The second time,
    ** only for the directories. This is because the timestamps for
    ** extracted directories must be reset after they are populated (as
    ** populating them changes the timestamp).  */
    for(i=0; i<2; i++){
................................................................................
  const char *zDrop = "DROP TABLE IF EXISTS sqlar";
  const char *zInsert = "REPLACE INTO sqlar VALUES(?,?,?,?,sqlar_compress(?))";

  sqlite3_stmt *pStmt = 0;        /* Directory traverser */
  sqlite3_stmt *pInsert = 0;      /* Compilation of zInsert */
  int i;                          /* For iterating through azFile[] */
  int rc;                         /* Return code */

  assert( pAr->bZip==0 );

  rc = sqlite3_exec(db, "SAVEPOINT ar;", 0, 0, 0);
  if( rc!=SQLITE_OK ) return rc;

  if( bUpdate==0 ){
    rc = sqlite3_exec(db, zDrop, 0, 0, 0);
    if( rc!=SQLITE_OK ) return rc;
................................................................................
){
  ArCommand cmd;
  int rc;
  rc = arParseCommand(azArg, nArg, &cmd);
  if( rc==SQLITE_OK ){
    sqlite3 *db = 0;              /* Database handle to use as archive */

    if( cmd.bZip ){
      if( cmd.zFile==0 ){
        raw_printf(stderr, "zip format requires a --file switch\n");
        return SQLITE_ERROR;
      }else
      if( cmd.eCmd==AR_CMD_CREATE || cmd.eCmd==AR_CMD_UPDATE ){
        raw_printf(stderr, "zip archives are read-only\n");
        return SQLITE_ERROR;
      }
      db = pState->db;
    }else if( cmd.zFile ){
      int flags;
      if( cmd.eCmd==AR_CMD_CREATE || cmd.eCmd==AR_CMD_UPDATE ){
        flags = SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE;
      }else{
        flags = SQLITE_OPEN_READONLY;
      }
      rc = sqlite3_open_v2(cmd.zFile, &db, flags, 0);