Documentation Source Text

Check-in [7647551ed3]
Login

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

Overview
Comment:Improvements to SQLite Archive documentation.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 7647551ed32e9f0eb162c9be1e2dac227373c2e9042171f1c3504216058abd8c
User & Date: drh 2018-03-08 16:27:02
Context
2018-03-09
18:11
Merge search box fixes from the 3.22.0 branch. check-in: 848c37b010 user: drh tags: trunk
2018-03-08
16:27
Improvements to SQLite Archive documentation. check-in: 7647551ed3 user: drh tags: trunk
14:50
New alternative TCLINC and TCLLIB suggestions in the Makefile. check-in: 4cef637367 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to pages/affcase1.in.

   227    227   <p>
   228    228   For this first experiment, nothing else about the file format is changed.
   229    229   The OpenDocument is still a pile-of-files, only now each file is a row
   230    230   in an SQLite database rather than an entry in a ZIP archive.
   231    231   This simple change does not use the power of a relational
   232    232   database.  Even so, this simple change shows some improvements.
   233    233   
   234         -<tcl>hd_fragment smaller {SQLAR smaller than ZIP}</tcl>
          234  +<tcl>hd_fragment smaller {SQLite Archive smaller than ZIP}</tcl>
   235    235   <p>
   236    236   Surprisingly, using SQLite in place of ZIP makes the presentation
   237    237   file smaller.  Really.  One would think that a relational database file
   238    238   would be larger than a ZIP archive, but at least in the case of NeoOffice
   239    239   that is not so.  The following is an actual screen-scrape showing
   240    240   the sizes of the same NeoOffice presentation, both in its original 
   241    241   ZIP archive format as generated by NeoOffice (self2014.odp), and 

Changes to pages/appfileformat.in.

   142    142   <p>
   143    143   Any application state that can be recorded in a pile-of-files can
   144    144   also be recorded in an SQLite database with a simple key/value schema
   145    145   like this:
   146    146   <blockquote><pre>
   147    147   CREATE TABLE files(filename TEXT PRIMARY KEY, content BLOB);
   148    148   </pre></blockquote>
   149         -If the content is compressed, then such an SQLite database is
   150         -[SQLAR smaller than ZIP|the same size] (&#177;1%)
          149  +If the content is compressed, then such an [SQLite Archive] database is
          150  +[SQLite Archive smaller than ZIP|the same size] (&#177;1%)
   151    151   as an equivalent ZIP archive, and it has the advantage
   152    152   of being able to update individual "files" without rewriting
   153    153   the entire document.
   154    154   
   155    155   <p>
   156    156   But an SQLite database is not limited to a simple key/value structure
   157    157   like a pile-of-files database.  An SQLite database can have dozens

Changes to pages/cli.in.

     1      1   <title>Command Line Shell For SQLite</title>
     2      2   <tcl>hd_keywords {CLI} {Command Line Interface} {command-line shell} \
     3         -     {command-line interface} </tcl>
            3  +     {command-line interface} {sqlite3.exe command-line shell} {sqlite3.exe} </tcl>
     4      4   <table_of_contents>
     5      5   
     6      6   <tcl>hd_fragment intro</tcl>
     7      7   <h1>Getting Started</h1>
     8      8   
     9      9   <p>The SQLite project provides a simple command-line program named
    10     10   <b>sqlite3</b> (or <b>sqlite3.exe</b> on Windows)
................................................................................
   865    865   hash of the content of all tables.  Subsequent runs of ".selftest"
   866    866   will verify that the database has not been changed in any way.  To
   867    867   generates tests to verify that a subset of the tables are unchanged,
   868    868   simply run ".selftest --init" then [DELETE] the selftest rows that
   869    869   refer to tables that are not constant.  
   870    870   
   871    871   <tcl>hd_fragment sqlar {.archive command}</tcl>
   872         -<h1>SQLAR Archive Support</h1>
          872  +<h1>SQLite Archive Support</h1>
   873    873   
   874         -<p>The ".archive" dot-command (often abbreviated as ".ar")
   875         -provides built-in support for the 
          874  +<p>The ".archive" dot-command and the "-A" command-line option
          875  +provide built-in support for the 
   876    876   [SQLite Archive format]. The interface is similar to
   877    877   that of the "tar" command on unix systems. Each invocation of the ".ar"
   878    878   command must specify a single command option. The following commands
   879         -are available:
          879  +are available for ".archive":
   880    880   
   881    881   <table striped=1>
   882    882     <tr><th style="width:15ex">Option<th style="width:17ex">Long&nbsp;Option<th>Purpose
   883    883     <tr><td>-c<td>--create<td>Create a new archive containing specified files.
   884    884     <tr><td>-x<td>--extract<td>Extract specified files from archive.
   885    885     <tr><td>-t<td>--list<td>List the files in the archive.
   886    886     <tr><td>-u<td>--update<td>Add files to existing archive.
................................................................................
   904    904     paths as relative to DIR, instead of the current working directory.
   905    905     <tr><td>-n<td>--dryrun<td>Show the SQL that would be run to carry out the
   906    906                          archive operation, but do not actually change anything.
   907    907     <tr><td>--<td>--<td>All subsequent command line words are command arguments,
   908    908     not options.
   909    909   </table>
   910    910   
          911  +<p>
          912  +For command-line usage, add the short style command-line options immediately
          913  +following the "-A", without an intervening space.  All subsequent arguments
          914  +are considered to be part of the .archive command.  For example, the following 
          915  +commands are equivalent:
          916  +
          917  +<codeblock>
          918  +sqlite3 new_archive.db -Acv file1 file2 file3
          919  +sqlite3 new_archive.db ".ar -tv file1 file2 file3"
          920  +</codeblock>
          921  +
   911    922   <p>
   912    923   Long and short style options may be mixed. For example, the following are
   913    924   equivalent:
   914    925   
   915    926   <codeblock>
   916    927     <i>-- Two ways to create a new archive named "new_archive.db" containing</i>
   917    928     <i>-- files "file1", "file2" and "file3".</i>
................................................................................
   926    937   
   927    938   <codeblock>
   928    939     <i>-- Create a new archive "new_archive.db" containing files "file1" and</i>
   929    940     <i>-- "file2" from directory "dir1".</i>
   930    941     .ar cCf dir1 new_archive.db file1 file2 file3
   931    942   </codeblock>
   932    943   
   933         -<h2> SQLAR Create Command </h2>
          944  +<h2> SQLite Archive Create Command </h2>
   934    945   
   935    946   <p>Create a new archive, overwriting any existing archive (either in the current
   936    947   "main" db or in the file specified by a --file option). Each argument following
   937    948   the options is a file to add to the archive. Directories are imported
   938    949   recursively. See above for examples.
   939    950   
   940         -<h2> SQLAR Extract Command </h2>
          951  +<h2> SQLite Archive Extract Command </h2>
   941    952   
   942    953   <p>Extract files from the archive (either to the current working directory or
   943    954   to the directory specified by a --directory option). If there are no arguments
   944    955   following the options all files are extracted from the archive. Or, if there
   945    956   are arguments, they are the names of files to extract from the archive. Any
   946    957   specified directories are extracted recursively. It is an error if any
   947    958   specified files are not part of the archive.
................................................................................
   951    962     <i>-- current working directory. List files as they are extracted. </i>
   952    963     .ar --extract --verbose
   953    964   
   954    965     <i>-- Extract file "file1" from archive "ar.db" to directory "dir1".</i>
   955    966     .ar fCx ar.db dir1 file1
   956    967   </codeblock>
   957    968   
   958         -<h2> SQLAR List Command </h2>
          969  +<h2> SQLite Archive List Command </h2>
   959    970   
   960    971   <p>List the contents of the archive. If no arguments are specified, then all
   961    972   files are listed. Otherwise, only those specified as arguments are. Currently,
   962    973   the --verbose option does not change the behaviour of this command. That may
   963    974   change in the future.
   964    975   
   965    976   <codeblock>
   966    977     <i>-- List contents of archive in current "main" db.</i>.
   967    978     .ar --list
   968    979   </codeblock>
   969    980   
   970         -<h2> SQLAR Update Command </h2>
          981  +<h2> SQLite Archive Update Command </h2>
   971    982   
   972    983   <p> This command works the same way as the --create command, except that 
   973    984   it does not delete the current archive before commencing. New versions of
   974    985   files silently replace existing files with the same names, but otherwise
   975    986   the initial contents of the archive (if any) remain intact.
   976    987   
   977    988   
   978    989   <h2> Operations On ZIP Archives </h2>
   979    990   
   980         -<p>If FILE is a ZIP archive rather than an SQLAR, the ".archive" command still
   981         -works, except that only --extract and --list operations are supported.
          991  +<p>If FILE is a ZIP archive rather than an SQLite Archive, the ".archive"
          992  +command and the "-A" command-line option still work, except that
          993  +only --extract and --list operations are supported.
   982    994   ZIP archives are currently read-only to SQLite.  (This limitation may be
   983    995   relaxed in a future release.)
   984    996   
   985         -<h2> SQL Used To Implement SQLAR Operations </h2>
          997  +<p>For example, the following commands are roughly equivalent
          998  +(differing only in output formatting):
   986    999   
   987         -<p>The various SQLAR Archive commands are implemented using SQL statements.
   988         -Application developers can easily add SQLAR Archive reading and writing
         1000  +<codeblock>
         1001  +unzip -l archive.zip
         1002  +sqlite3 archive.zip -Atv
         1003  +</codeblock>
         1004  +
         1005  +<h2> SQL Used To Implement SQLite Archive Operations </h2>
         1006  +
         1007  +<p>The various SQLite Archive Archive commands are implemented using SQL statements.
         1008  +Application developers can easily add SQLite Archive Archive reading and writing
   989   1009   support to their own projects by running the appropriate SQL.
   990   1010   
   991         -<p>To see what SQL statements are used to implement an SQLAR Archive
         1011  +<p>To see what SQL statements are used to implement an SQLine Archive
   992   1012   operation, add the --dryrun or -n option.  This causes the SQL to be
   993   1013   displayed but inhibits the execution of the SQL.
   994   1014   
   995         -<p>The SQL statements used to implement SQLAR operations make use of
         1015  +<p>The SQL statements used to implement SQLite Archive operations make use of
   996   1016   various [loadable extensions].  These extensions are all available in
   997   1017   the [https://sqlite.org/src|SQLite source tree] in the
   998   1018   [https://sqlite.org/src/file/ext/misc|ext/misc/ subfolder].
   999         -The extensions needed for full SQLAR support include:
         1019  +The extensions needed for full SQLite Archive support include:
  1000   1020   
  1001   1021   <ol>
  1002   1022   <li><p>
  1003   1023   [https://sqlite.org/src/file/ext/misc/fileio.c|fileio.c] &mdash;
  1004   1024   This extension adds SQL functions readfile() and writefile() for
  1005   1025   reading and writing content from files on disk.  The fileio.c
  1006   1026   extension also includes fsdir() table-valued function for listing
................................................................................
  1008   1028   numeric st_mode integers from the stat() system call into human-readable
  1009   1029   strings after the fashion of the "ls -l" command.
  1010   1030   
  1011   1031   <li><p>
  1012   1032   [https://sqlite.org/src/file/ext/misc/sqlar.c|sqlar.c] &mdash;
  1013   1033   This extension adds the sqlar_compress() and sqlar_uncompress()
  1014   1034   functions that are needed to compress and uncompress file content
  1015         -as it is insert and extracted from an SQLAR.
         1035  +as it is insert and extracted from an SQLite Archive.
  1016   1036   
  1017   1037   <li><p>
  1018   1038   [zipfile|zipfile.c] &mdash;
  1019   1039   This extension implements the "zipfile(FILE)" table-valued function
  1020   1040   which is used to read ZIP archives.  This extension is only needed
  1021         -when reading ZIP archives instead of SQLAR archives.
         1041  +when reading ZIP archives instead of SQLite archives.
  1022   1042   
  1023   1043   <li><p>
  1024   1044   [https://sqlite.org/src/file/ext/misc/appendvfs.c|appendvfs.c] &mdash;
  1025   1045   This extension implements a new [VFS] that allows an SQLite database
  1026   1046   to be appended to some other file, such as an executable.  This
  1027   1047   extension is only needed if the --append option to the .archive
  1028   1048   command is used.

Changes to pages/crew.in.

    16     16   [http://www.duke.edu/ | Duke University] (PhD, 1992) and is
    17     17   the founder of the consulting firm
    18     18   [https://www.hwaci.com/ | Hwaci].</p>
    19     19   
    20     20   <br clear="both"></br>
    21     21   <hr>
    22     22   
           23  +<tcl>hd_fragment dan {Dan Kennedy}</tcl>
    23     24   <img src="images/dan1.jpg" align="left" hspace="25" vspace="0">
    24     25   <p>
    25     26   <b>Dan Kennedy</b> is an Australian currently based in South-East Asia.
    26     27   He holds a degree in Computer System Engineering from the University of
    27     28   Queensland and has worked in a variety of fields, including industrial
    28     29   automation, computer graphics and embedded software development.
    29     30   Dan has been a key contributor to SQLite since 2002.

Changes to pages/sqlar.in.

     2      2   <tcl>hd_keywords {SQLAR} {SQLite Archive} {SQL Archive} \
     3      3        {SQLite Archive format} {SQLite Archive files} </tcl>
     4      4   <table_of_contents>
     5      5   
     6      6   <h1>Introduction</h1>
     7      7   
     8      8   <p>
     9         -An "SQLite Archive" (also sometimes called an "SQL Archive" or an "SQLAR") is
    10         -a file container similar to a 
            9  +An "SQLite Archive" is a file container similar to a 
    11     10   [https://en.wikipedia.org/wiki/Zip_(file_format)|ZIP archive] or
    12     11   [https://en.wikipedia.org/wiki/Tar_(computing)|Tarball] but
    13     12   based on an SQLite database.
    14     13   
    15     14   <p>
    16     15   An SQLite Archive is an ordinary SQLite database file that contains the
    17     16   following table as part of its schema:
................................................................................
    31     30   The filename (the full pathname relative to the root of the archive)
    32     31   is in the "name" field.
    33     32   The "mode" field is an integer which is the unix-style access permissions
    34     33   for the file.  "mtime" is the modification time of the file in seconds
    35     34   since 1970.  "sz" is the original uncompressed size of the file.
    36     35   The "data" field contains the file content.  The content is usually
    37     36   compressed using [http://zlib.net/|Deflate], though not always.  If the
    38         -The "sz" field is equal to the size of the "data" field, then the content
           37  +"sz" field is equal to the size of the "data" field, then the content
    39     38   is stored uncompressed.
    40     39   
    41         -<tcl>hd_fragment dbasobj {database as object}</tcl>
    42         -<h2>Database As Object</h2>
           40  +<tcl>hd_fragment dbasobj {database as object} \
           41  +     {database as container object}</tcl>
           42  +<h2>Database As Container Object</h2>
    43     43   
    44     44   <p>
    45         -The concept of an SQLite Archive is one specific example of a more general
    46         -idea that an SQLite database can behave as an object.
           45  +An SQLite Archive is one example of a more general
           46  +idea that an SQLite database can behave as a container object holding
           47  +lots of smaller data components.
    47     48   
    48     49   <p>
    49     50   With client/server databases like PostgreSQL or Oracle, users and
    50     51   developers tend to think of the database as a service or a "node", not
    51     52   as an object.  This is because the database content is spread out across
    52     53   multiple files on the server, or possibly across multiple servers in a
    53     54   service cluster.  One cannot point to a single file or even a single
    54         -diretory and say "this is the database".
           55  +directory and say "this is the database".
    55     56   
    56     57   <p>
    57     58   SQLite, in contrast, stores all content in a [file format|single file on disk].
    58     59   That single file is something you can point to and say
    59     60   "this is the database".  It behaves as an object.
    60         -Just like an image or document
    61         -or media file, the database file can be copied, renamed, sent as an
           61  +An SQLite database file can be copied, renamed, sent as an
    62     62   email attachment, passed as the argument a POST HTTP request,
    63         -or otherwise treated as any other data object is normally treated.
           63  +or otherwise treated as other data object such as an image,
           64  +document, or media file.
    64     65   
    65     66   <p>
    66         -It is as if the content for a client/server database is firmly bolted to
    67         -the floor of the data-center, whereas the content for an SQLite database
    68         -is free to flitter about the internet
           67  +Studies show that many applications already use
           68  +SQLite as a container object.  For example,
           69  +[https://odin.cse.buffalo.edu/papers/2015/TPCTC-sqlite-final.pdf|Kennedy]
           70  +(no relation to the [Dan Kennedy|SQLite developer]) reports that 14% of
           71  +Android applications never write to their SQLite databases.  It is
           72  +believed that these applications are downloading entire databases
           73  +from the cloud and then using the information locally as needed.  In other
           74  +words, the applications are using SQLite not so much as a database but as
           75  +a queryable wire-transfer format.
    69     76   
    70     77   <h2>Applications Using SQLite Archives</h2>
    71     78   
    72     79   <p>
    73     80   The [https://fossil-scm.org/|Fossil Distributed Version Control] system
    74     81   provides users with the option to download check-ins as either Tarballs,
    75     82   ZIP Archives, or SQLite Archives.
................................................................................
   138    145   
   139    146   <li><p>
   140    147   An SQLite Archive supports only the [https://zlib.net/|Deflate] compression
   141    148   method.  Tarballs and ZIP Archive support a wider assortment of
   142    149   compression methods.
   143    150   </ol>
   144    151   
   145         -
          152  +<tcl>hd_fragment cltools {managing SQLite Archives from the command-line}</tcl>
   146    153   <h1>Managing An SQLite Archive From The Command-Line</h1>
   147    154   
   148    155   <p>
   149         -Just as there is the "zip" program to manage ZIP Archives, and the
   150         -"tar" program to manage Tarballs, the 
   151         -[https://sqlite.org/sqlar|"sqlar" program] exists to manage SQL Archives.
   152         -The "sqlar" program is able to create a new SQLite Archive, list the
   153         -content of an existing archive, add or remove files from the archive,
   154         -and/or extract files from the archive.
   155         -A separate "sqlarfs" program is able to mount the SQLite Archive as
   156         -a [https://github.com/libfuse/libfuse|Fuse Filesystem].
          156  +The recommended way of creating, updating, listing, and extracting
          157  +an SQLite Archive is to use the [sqlite3.exe command-line shell] 
          158  +for SQLite [version 3.23.0] ([dateof:3.23.0]) or later.  This CLI
          159  +supports the -A command-line option that allows easy management
          160  +of SQLite Archives.
          161  +The CLI for SQLite [version 3.22.0] ([dateof:3.22.0]) has the
          162  +[.archive command] for manageing SQLite Archives, but that requires
          163  +interacting with the shell.
   157    164   
   158    165   <p>
   159         -Beginning with SQLite [version 3.22.0] ([dateof:3.22.0]), SQLite Archives
   160         -can also be added using the generate [CLI|"sqlite3.exe" command-line tool]
   161         -using the [.archive command].
   162         -Beginning with SQLite [version 3.23.0] ([dateof:3.23.0]), the
   163         -[CLI|sqlite3.exe] is extended with new command-line options for
   164         -processing SQLite Archives that make it even easier to use.
          166  +To list all of the files in an SQLite Archive named "example.sqlar"
          167  +using one of these commands:
   165    168   
   166         -<h2>Command-line examples</h2>
          169  +<codeblock>
          170  +sqlite3 example.sqlar -At
          171  +sqlite3 example.sqlar -Atv
          172  +</codeblock>
   167    173   
   168    174   <p>
   169    175   To extract all files from an SQLite Archive named "example.sqlar":
   170    176   
   171    177   <codeblock>
   172    178   sqlite3 example.sqlar -Ax
   173    179   </codeblock>
   174    180   
   175    181   <p>
   176    182   To create a new SQLite Archive named "alltxt.sqlar" containing all *.txt
   177    183   files in the current directory:
   178    184   
   179    185   <codeblock>
   180         -sqlite3 alltxt.sql -Ac *.txt
          186  +sqlite3 alltxt.sqlar -Ac *.txt
          187  +</codeblock>
          188  +
          189  +<p>
          190  +For usage hints and a summary of all options, simply give the [CLI]
          191  +the -A option with no additional arguments:
          192  +
          193  +<codeblock>
          194  +sqlite3 -A
   181    195   </codeblock>
   182    196   
          197  +<h2>Other command-line tools</h2>
          198  +
          199  +<p>
          200  +Just as there is the "zip" program to manage ZIP Archives, and the
          201  +"tar" program to manage Tarballs, the 
          202  +[https://sqlite.org/sqlar|"sqlar" program] exists to manage SQL Archives.
          203  +The "sqlar" program is able to create a new SQLite Archive, list the
          204  +content of an existing archive, add or remove files from the archive,
          205  +and/or extract files from the archive.
          206  +A separate "sqlarfs" program is able to mount the SQLite Archive as
          207  +a [https://github.com/libfuse/libfuse|Fuse Filesystem].
          208  +
   183    209   <h1>Managing SQLite Archives From Application Code</h1>
   184    210   
   185    211   <p>
   186    212   Applications can easily read or write SQLite Archives by linking against
   187    213   SQLite and including the 
   188    214   [https://sqlite.org/src/file/ext/misc/sqlar.c|ext/misc/sqlar.c] extension
   189    215   to handle the compression and decompression.  The sqlar.c extension

Changes to pages/whentouse.in.

   157    157   
   158    158   
   159    159   </li>
   160    160   
   161    161   <li><p><b>File archive and/or data container</b></p>
   162    162   
   163    163   <p>
   164         -The [https://www.sqlite.org/sqlar|SQL Archive] project shows how
          164  +The [SQLite Archive] idea shows how
   165    165   SQLite can be used as a substitute for ZIP archives or Tarballs.
   166    166   An archive of files stored in SQLite is only very slightly larger, and
   167    167   in some cases actually smaller, than the equivalent ZIP archive.
   168    168   And an SQLite archive features incremental and atomic updating
   169    169   and the ability to store much richer metadata.
   170    170   </p>
   171    171   
   172    172   <p>[https://www.fossil-scm.org/|Fossil] version 2.5 and later offers
   173         -[https://www.sqlite.org/sqlar|SQL Archive] as a download format, in addition
          173  +[SQLite Archive files] as a download format, in addition
   174    174   to traditional tarball and ZIP archive.
   175         -The sqlite3.exe [command-line shell] version 3.22.0 and later will create,
          175  +The [sqlite3.exe command-line shell] version 3.22.0 and later will create,
   176    176   list, or unpack an SQL archiving using the 
   177    177   [.archive command].</p>
   178    178   
   179    179   <p>
   180    180   SQLite is a good solution for any situation that requires bundling
   181    181   diverse content into a self-contained and self-describing package 
   182    182   for shipment across a network.