Search notes:

SQLite archives

An SQLite archive is an SQLite database that can be used like other well known archive file formats, such as zip files, tar etc.
An SQLite archive contains one table (sqlar) in which each record contains the compressed file content stored in a blob column along with the stored file's meta data (such as last modification time) in additional columns.

Command line options

In the command line (shell, cmd.exe, …) archive mode is enabled with the command line option -A.
The other command line options are modelled like those of tar:

Example

The current directory contains the three files foo.txt, bar.txt and baz.txt.
We first touch these files with the -t option so that we know their modification time: 
#        YYYYMMDDHHMI.SS
$ touch -t 201112131415.16 foo.txt
$ touch -t 201312111009.08 bar.txt
$ touch -t 201212121212.12 bar.txt
We also change their access rights with chmod: 
chmod    0666            foo.txt
chmod    0660            bar.txt
chmod    0600            baz.txt
The archive file is created with the -A and -c command line option: 
$ sqlite3 archive.db -A -c foo.txt bar.txt baz.txt
The -t option shows the content of the archive file: 
$ sqlite3 archive.db -A -t
bar.txt
baz.txt
foo.txt
The SQLite shell command .schema command shows the structure of the sqlar table: 
$ sqlite3 archive.db .schema
CREATE TABLE sqlar(
  name TEXT PRIMARY KEY,  -- name of the file
  mode INT,               -- access permissions
  mtime INT,              -- last modification time
  sz INT,                 -- original file size
  data BLOB               -- compressed content
);
A select statement to select all from sqlar table: 
.header on
.mode column
.width 10 10 20 10 50

select
   name,
   printf("%o", mode),
   datetime(mtime, 'unixepoch') mtime,
   sz,
   replace(data, x'0a', '\n') data_txt
from
   sqlar;
Github repository about-sqlite, path: /archives/select-from-sqlar.sql
Each file that is stored in the SQLite archive is represented by a record: 
name        printf("%o  mtime                 sz          data_txt                                          
----------  ----------  --------------------  ----------  --------------------------------------------------
foo.txt     100666      2011-12-13 13:15:16   22          This is the foo file.\n                           
bar.txt     100660      2012-12-12 11:12:12   14          The bar file.\n                                   
baz.txt     100600      2019-06-22 10:09:34   48          And, of course, the baz file cannot be missing.\n
Removing the *.txt files: 
$ rm foo.txt bar.txt baz.txt
Extract one file from the archive 
sqlite3 archive.db -A -x foo.txt
And also extract the other two files: 
sqlite3 archive.db -A -x bar.txt baz.txt

Index