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 Unified Diffs Ignore Whitespace Patch

Changes to pages/affcase1.in.

227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
<p>
For this first experiment, nothing else about the file format is changed.
The OpenDocument is still a pile-of-files, only now each file is a row
in an SQLite database rather than an entry in a ZIP archive.
This simple change does not use the power of a relational
database.  Even so, this simple change shows some improvements.

<tcl>hd_fragment smaller {SQLAR smaller than ZIP}</tcl>
<p>
Surprisingly, using SQLite in place of ZIP makes the presentation
file smaller.  Really.  One would think that a relational database file
would be larger than a ZIP archive, but at least in the case of NeoOffice
that is not so.  The following is an actual screen-scrape showing
the sizes of the same NeoOffice presentation, both in its original 
ZIP archive format as generated by NeoOffice (self2014.odp), and 







|







227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
<p>
For this first experiment, nothing else about the file format is changed.
The OpenDocument is still a pile-of-files, only now each file is a row
in an SQLite database rather than an entry in a ZIP archive.
This simple change does not use the power of a relational
database.  Even so, this simple change shows some improvements.

<tcl>hd_fragment smaller {SQLite Archive smaller than ZIP}</tcl>
<p>
Surprisingly, using SQLite in place of ZIP makes the presentation
file smaller.  Really.  One would think that a relational database file
would be larger than a ZIP archive, but at least in the case of NeoOffice
that is not so.  The following is an actual screen-scrape showing
the sizes of the same NeoOffice presentation, both in its original 
ZIP archive format as generated by NeoOffice (self2014.odp), and 

Changes to pages/appfileformat.in.

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

<p>
But an SQLite database is not limited to a simple key/value structure
like a pile-of-files database.  An SQLite database can have dozens







|
|







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

<p>
But an SQLite database is not limited to a simple key/value structure
like a pile-of-files database.  An SQLite database can have dozens

Changes to pages/cli.in.

1
2
3
4
5
6
7
8
9
10
...
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
...
904
905
906
907
908
909
910











911
912
913
914
915
916
917
...
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
...
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980

981
982
983
984








985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
....
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
<title>Command Line Shell For SQLite</title>
<tcl>hd_keywords {CLI} {Command Line Interface} {command-line shell} \
     {command-line interface} </tcl>
<table_of_contents>

<tcl>hd_fragment intro</tcl>
<h1>Getting Started</h1>

<p>The SQLite project provides a simple command-line program named
<b>sqlite3</b> (or <b>sqlite3.exe</b> on Windows)
................................................................................
hash of the content of all tables.  Subsequent runs of ".selftest"
will verify that the database has not been changed in any way.  To
generates tests to verify that a subset of the tables are unchanged,
simply run ".selftest --init" then [DELETE] the selftest rows that
refer to tables that are not constant.  

<tcl>hd_fragment sqlar {.archive command}</tcl>
<h1>SQLAR Archive Support</h1>

<p>The ".archive" dot-command (often abbreviated as ".ar")
provides built-in support for the 
[SQLite Archive format]. The interface is similar to
that of the "tar" command on unix systems. Each invocation of the ".ar"
command must specify a single command option. The following commands
are available:

<table striped=1>
  <tr><th style="width:15ex">Option<th style="width:17ex">Long&nbsp;Option<th>Purpose
  <tr><td>-c<td>--create<td>Create a new archive containing specified files.
  <tr><td>-x<td>--extract<td>Extract specified files from archive.
  <tr><td>-t<td>--list<td>List the files in the archive.
  <tr><td>-u<td>--update<td>Add files to existing archive.
................................................................................
  paths as relative to DIR, instead of the current working directory.
  <tr><td>-n<td>--dryrun<td>Show the SQL that would be run to carry out the
                       archive operation, but do not actually change anything.
  <tr><td>--<td>--<td>All subsequent command line words are command arguments,
  not options.
</table>












<p>
Long and short style options may be mixed. For example, the following are
equivalent:

<codeblock>
  <i>-- Two ways to create a new archive named "new_archive.db" containing</i>
  <i>-- files "file1", "file2" and "file3".</i>
................................................................................

<codeblock>
  <i>-- Create a new archive "new_archive.db" containing files "file1" and</i>
  <i>-- "file2" from directory "dir1".</i>
  .ar cCf dir1 new_archive.db file1 file2 file3
</codeblock>

<h2> SQLAR Create Command </h2>

<p>Create a new archive, overwriting any existing archive (either in the current
"main" db or in the file specified by a --file option). Each argument following
the options is a file to add to the archive. Directories are imported
recursively. See above for examples.

<h2> SQLAR Extract Command </h2>

<p>Extract files from the archive (either to the current working directory or
to the directory specified by a --directory option). If there are no arguments
following the options all files are extracted from the archive. Or, if there
are arguments, they are the names of files to extract from the archive. Any
specified directories are extracted recursively. It is an error if any
specified files are not part of the archive.
................................................................................
  <i>-- current working directory. List files as they are extracted. </i>
  .ar --extract --verbose

  <i>-- Extract file "file1" from archive "ar.db" to directory "dir1".</i>
  .ar fCx ar.db dir1 file1
</codeblock>

<h2> SQLAR List Command </h2>

<p>List the contents of the archive. If no arguments are specified, then all
files are listed. Otherwise, only those specified as arguments are. Currently,
the --verbose option does not change the behaviour of this command. That may
change in the future.

<codeblock>
  <i>-- List contents of archive in current "main" db.</i>.
  .ar --list
</codeblock>

<h2> SQLAR Update Command </h2>

<p> This command works the same way as the --create command, except that 
it does not delete the current archive before commencing. New versions of
files silently replace existing files with the same names, but otherwise
the initial contents of the archive (if any) remain intact.


<h2> Operations On ZIP Archives </h2>

<p>If FILE is a ZIP archive rather than an SQLAR, the ".archive" command still

works, except that only --extract and --list operations are supported.
ZIP archives are currently read-only to SQLite.  (This limitation may be
relaxed in a future release.)









<h2> SQL Used To Implement SQLAR Operations </h2>

<p>The various SQLAR Archive commands are implemented using SQL statements.
Application developers can easily add SQLAR Archive reading and writing
support to their own projects by running the appropriate SQL.

<p>To see what SQL statements are used to implement an SQLAR Archive
operation, add the --dryrun or -n option.  This causes the SQL to be
displayed but inhibits the execution of the SQL.

<p>The SQL statements used to implement SQLAR operations make use of
various [loadable extensions].  These extensions are all available in
the [https://sqlite.org/src|SQLite source tree] in the
[https://sqlite.org/src/file/ext/misc|ext/misc/ subfolder].
The extensions needed for full SQLAR support include:

<ol>
<li><p>
[https://sqlite.org/src/file/ext/misc/fileio.c|fileio.c] &mdash;
This extension adds SQL functions readfile() and writefile() for
reading and writing content from files on disk.  The fileio.c
extension also includes fsdir() table-valued function for listing
................................................................................
numeric st_mode integers from the stat() system call into human-readable
strings after the fashion of the "ls -l" command.

<li><p>
[https://sqlite.org/src/file/ext/misc/sqlar.c|sqlar.c] &mdash;
This extension adds the sqlar_compress() and sqlar_uncompress()
functions that are needed to compress and uncompress file content
as it is insert and extracted from an SQLAR.

<li><p>
[zipfile|zipfile.c] &mdash;
This extension implements the "zipfile(FILE)" table-valued function
which is used to read ZIP archives.  This extension is only needed
when reading ZIP archives instead of SQLAR archives.

<li><p>
[https://sqlite.org/src/file/ext/misc/appendvfs.c|appendvfs.c] &mdash;
This extension implements a new [VFS] that allows an SQLite database
to be appended to some other file, such as an executable.  This
extension is only needed if the --append option to the .archive
command is used.


|







 







|

|
|



|







 







>
>
>
>
>
>
>
>
>
>
>







 







|






|







 







|











|









|
>
|



>
>
>
>
>
>
>
>
|

|
|


|



|



|







 







|





|







1
2
3
4
5
6
7
8
9
10
...
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
...
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
...
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
...
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
....
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
<title>Command Line Shell For SQLite</title>
<tcl>hd_keywords {CLI} {Command Line Interface} {command-line shell} \
     {command-line interface} {sqlite3.exe command-line shell} {sqlite3.exe} </tcl>
<table_of_contents>

<tcl>hd_fragment intro</tcl>
<h1>Getting Started</h1>

<p>The SQLite project provides a simple command-line program named
<b>sqlite3</b> (or <b>sqlite3.exe</b> on Windows)
................................................................................
hash of the content of all tables.  Subsequent runs of ".selftest"
will verify that the database has not been changed in any way.  To
generates tests to verify that a subset of the tables are unchanged,
simply run ".selftest --init" then [DELETE] the selftest rows that
refer to tables that are not constant.  

<tcl>hd_fragment sqlar {.archive command}</tcl>
<h1>SQLite Archive Support</h1>

<p>The ".archive" dot-command and the "-A" command-line option
provide built-in support for the 
[SQLite Archive format]. The interface is similar to
that of the "tar" command on unix systems. Each invocation of the ".ar"
command must specify a single command option. The following commands
are available for ".archive":

<table striped=1>
  <tr><th style="width:15ex">Option<th style="width:17ex">Long&nbsp;Option<th>Purpose
  <tr><td>-c<td>--create<td>Create a new archive containing specified files.
  <tr><td>-x<td>--extract<td>Extract specified files from archive.
  <tr><td>-t<td>--list<td>List the files in the archive.
  <tr><td>-u<td>--update<td>Add files to existing archive.
................................................................................
  paths as relative to DIR, instead of the current working directory.
  <tr><td>-n<td>--dryrun<td>Show the SQL that would be run to carry out the
                       archive operation, but do not actually change anything.
  <tr><td>--<td>--<td>All subsequent command line words are command arguments,
  not options.
</table>

<p>
For command-line usage, add the short style command-line options immediately
following the "-A", without an intervening space.  All subsequent arguments
are considered to be part of the .archive command.  For example, the following 
commands are equivalent:

<codeblock>
sqlite3 new_archive.db -Acv file1 file2 file3
sqlite3 new_archive.db ".ar -tv file1 file2 file3"
</codeblock>

<p>
Long and short style options may be mixed. For example, the following are
equivalent:

<codeblock>
  <i>-- Two ways to create a new archive named "new_archive.db" containing</i>
  <i>-- files "file1", "file2" and "file3".</i>
................................................................................

<codeblock>
  <i>-- Create a new archive "new_archive.db" containing files "file1" and</i>
  <i>-- "file2" from directory "dir1".</i>
  .ar cCf dir1 new_archive.db file1 file2 file3
</codeblock>

<h2> SQLite Archive Create Command </h2>

<p>Create a new archive, overwriting any existing archive (either in the current
"main" db or in the file specified by a --file option). Each argument following
the options is a file to add to the archive. Directories are imported
recursively. See above for examples.

<h2> SQLite Archive Extract Command </h2>

<p>Extract files from the archive (either to the current working directory or
to the directory specified by a --directory option). If there are no arguments
following the options all files are extracted from the archive. Or, if there
are arguments, they are the names of files to extract from the archive. Any
specified directories are extracted recursively. It is an error if any
specified files are not part of the archive.
................................................................................
  <i>-- current working directory. List files as they are extracted. </i>
  .ar --extract --verbose

  <i>-- Extract file "file1" from archive "ar.db" to directory "dir1".</i>
  .ar fCx ar.db dir1 file1
</codeblock>

<h2> SQLite Archive List Command </h2>

<p>List the contents of the archive. If no arguments are specified, then all
files are listed. Otherwise, only those specified as arguments are. Currently,
the --verbose option does not change the behaviour of this command. That may
change in the future.

<codeblock>
  <i>-- List contents of archive in current "main" db.</i>.
  .ar --list
</codeblock>

<h2> SQLite Archive Update Command </h2>

<p> This command works the same way as the --create command, except that 
it does not delete the current archive before commencing. New versions of
files silently replace existing files with the same names, but otherwise
the initial contents of the archive (if any) remain intact.


<h2> Operations On ZIP Archives </h2>

<p>If FILE is a ZIP archive rather than an SQLite Archive, the ".archive"
command and the "-A" command-line option still work, except that
only --extract and --list operations are supported.
ZIP archives are currently read-only to SQLite.  (This limitation may be
relaxed in a future release.)

<p>For example, the following commands are roughly equivalent
(differing only in output formatting):

<codeblock>
unzip -l archive.zip
sqlite3 archive.zip -Atv
</codeblock>

<h2> SQL Used To Implement SQLite Archive Operations </h2>

<p>The various SQLite Archive Archive commands are implemented using SQL statements.
Application developers can easily add SQLite Archive Archive reading and writing
support to their own projects by running the appropriate SQL.

<p>To see what SQL statements are used to implement an SQLine Archive
operation, add the --dryrun or -n option.  This causes the SQL to be
displayed but inhibits the execution of the SQL.

<p>The SQL statements used to implement SQLite Archive operations make use of
various [loadable extensions].  These extensions are all available in
the [https://sqlite.org/src|SQLite source tree] in the
[https://sqlite.org/src/file/ext/misc|ext/misc/ subfolder].
The extensions needed for full SQLite Archive support include:

<ol>
<li><p>
[https://sqlite.org/src/file/ext/misc/fileio.c|fileio.c] &mdash;
This extension adds SQL functions readfile() and writefile() for
reading and writing content from files on disk.  The fileio.c
extension also includes fsdir() table-valued function for listing
................................................................................
numeric st_mode integers from the stat() system call into human-readable
strings after the fashion of the "ls -l" command.

<li><p>
[https://sqlite.org/src/file/ext/misc/sqlar.c|sqlar.c] &mdash;
This extension adds the sqlar_compress() and sqlar_uncompress()
functions that are needed to compress and uncompress file content
as it is insert and extracted from an SQLite Archive.

<li><p>
[zipfile|zipfile.c] &mdash;
This extension implements the "zipfile(FILE)" table-valued function
which is used to read ZIP archives.  This extension is only needed
when reading ZIP archives instead of SQLite archives.

<li><p>
[https://sqlite.org/src/file/ext/misc/appendvfs.c|appendvfs.c] &mdash;
This extension implements a new [VFS] that allows an SQLite database
to be appended to some other file, such as an executable.  This
extension is only needed if the --append option to the .archive
command is used.

Changes to pages/crew.in.

16
17
18
19
20
21
22

23
24
25
26
27
28
29
[http://www.duke.edu/ | Duke University] (PhD, 1992) and is
the founder of the consulting firm
[https://www.hwaci.com/ | Hwaci].</p>

<br clear="both"></br>
<hr>


<img src="images/dan1.jpg" align="left" hspace="25" vspace="0">
<p>
<b>Dan Kennedy</b> is an Australian currently based in South-East Asia.
He holds a degree in Computer System Engineering from the University of
Queensland and has worked in a variety of fields, including industrial
automation, computer graphics and embedded software development.
Dan has been a key contributor to SQLite since 2002.







>







16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
[http://www.duke.edu/ | Duke University] (PhD, 1992) and is
the founder of the consulting firm
[https://www.hwaci.com/ | Hwaci].</p>

<br clear="both"></br>
<hr>

<tcl>hd_fragment dan {Dan Kennedy}</tcl>
<img src="images/dan1.jpg" align="left" hspace="25" vspace="0">
<p>
<b>Dan Kennedy</b> is an Australian currently based in South-East Asia.
He holds a degree in Computer System Engineering from the University of
Queensland and has worked in a variety of fields, including industrial
automation, computer graphics and embedded software development.
Dan has been a key contributor to SQLite since 2002.

Changes to pages/sqlar.in.

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
..
31
32
33
34
35
36
37
38
39
40
41

42
43
44
45
46

47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

64
65
66
67
68






69
70
71
72
73
74
75
...
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166




167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182




















183
184
185
186
187
188
189
<tcl>hd_keywords {SQLAR} {SQLite Archive} {SQL Archive} \
     {SQLite Archive format} {SQLite Archive files} </tcl>
<table_of_contents>

<h1>Introduction</h1>

<p>
An "SQLite Archive" (also sometimes called an "SQL Archive" or an "SQLAR") is
a file container similar to a 
[https://en.wikipedia.org/wiki/Zip_(file_format)|ZIP archive] or
[https://en.wikipedia.org/wiki/Tar_(computing)|Tarball] but
based on an SQLite database.

<p>
An SQLite Archive is an ordinary SQLite database file that contains the
following table as part of its schema:
................................................................................
The filename (the full pathname relative to the root of the archive)
is in the "name" field.
The "mode" field is an integer which is the unix-style access permissions
for the file.  "mtime" is the modification time of the file in seconds
since 1970.  "sz" is the original uncompressed size of the file.
The "data" field contains the file content.  The content is usually
compressed using [http://zlib.net/|Deflate], though not always.  If the
The "sz" field is equal to the size of the "data" field, then the content
is stored uncompressed.

<tcl>hd_fragment dbasobj {database as object}</tcl>

<h2>Database As Object</h2>

<p>
The concept of an SQLite Archive is one specific example of a more general
idea that an SQLite database can behave as an object.


<p>
With client/server databases like PostgreSQL or Oracle, users and
developers tend to think of the database as a service or a "node", not
as an object.  This is because the database content is spread out across
multiple files on the server, or possibly across multiple servers in a
service cluster.  One cannot point to a single file or even a single
diretory and say "this is the database".

<p>
SQLite, in contrast, stores all content in a [file format|single file on disk].
That single file is something you can point to and say
"this is the database".  It behaves as an object.
Just like an image or document
or media file, the database file can be copied, renamed, sent as an
email attachment, passed as the argument a POST HTTP request,
or otherwise treated as any other data object is normally treated.


<p>
It is as if the content for a client/server database is firmly bolted to
the floor of the data-center, whereas the content for an SQLite database
is free to flitter about the internet







<h2>Applications Using SQLite Archives</h2>

<p>
The [https://fossil-scm.org/|Fossil Distributed Version Control] system
provides users with the option to download check-ins as either Tarballs,
ZIP Archives, or SQLite Archives.
................................................................................

<li><p>
An SQLite Archive supports only the [https://zlib.net/|Deflate] compression
method.  Tarballs and ZIP Archive support a wider assortment of
compression methods.
</ol>


<h1>Managing An SQLite Archive From The Command-Line</h1>

<p>
Just as there is the "zip" program to manage ZIP Archives, and the
"tar" program to manage Tarballs, the 
[https://sqlite.org/sqlar|"sqlar" program] exists to manage SQL Archives.
The "sqlar" program is able to create a new SQLite Archive, list the
content of an existing archive, add or remove files from the archive,
and/or extract files from the archive.
A separate "sqlarfs" program is able to mount the SQLite Archive as
a [https://github.com/libfuse/libfuse|Fuse Filesystem].

<p>
Beginning with SQLite [version 3.22.0] ([dateof:3.22.0]), SQLite Archives
can also be added using the generate [CLI|"sqlite3.exe" command-line tool]
using the [.archive command].
Beginning with SQLite [version 3.23.0] ([dateof:3.23.0]), the
[CLI|sqlite3.exe] is extended with new command-line options for
processing SQLite Archives that make it even easier to use.

<h2>Command-line examples</h2>





<p>
To extract all files from an SQLite Archive named "example.sqlar":

<codeblock>
sqlite3 example.sqlar -Ax
</codeblock>

<p>
To create a new SQLite Archive named "alltxt.sqlar" containing all *.txt
files in the current directory:

<codeblock>
sqlite3 alltxt.sql -Ac *.txt
</codeblock>





















<h1>Managing SQLite Archives From Application Code</h1>

<p>
Applications can easily read or write SQLite Archives by linking against
SQLite and including the 
[https://sqlite.org/src/file/ext/misc/sqlar.c|ext/misc/sqlar.c] extension
to handle the compression and decompression.  The sqlar.c extension







<
|







 







|


|
>
|


|
|
>







|





<
|

|
>


|
|
|
>
>
>
>
>
>







 







|



|
|
|
|
|
|
|
|


|
|
<
<
<
<

<
>
>
>
>













|


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







2
3
4
5
6
7
8

9
10
11
12
13
14
15
16
..
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
...
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167




168

169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
<tcl>hd_keywords {SQLAR} {SQLite Archive} {SQL Archive} \
     {SQLite Archive format} {SQLite Archive files} </tcl>
<table_of_contents>

<h1>Introduction</h1>

<p>

An "SQLite Archive" is a file container similar to a 
[https://en.wikipedia.org/wiki/Zip_(file_format)|ZIP archive] or
[https://en.wikipedia.org/wiki/Tar_(computing)|Tarball] but
based on an SQLite database.

<p>
An SQLite Archive is an ordinary SQLite database file that contains the
following table as part of its schema:
................................................................................
The filename (the full pathname relative to the root of the archive)
is in the "name" field.
The "mode" field is an integer which is the unix-style access permissions
for the file.  "mtime" is the modification time of the file in seconds
since 1970.  "sz" is the original uncompressed size of the file.
The "data" field contains the file content.  The content is usually
compressed using [http://zlib.net/|Deflate], though not always.  If the
"sz" field is equal to the size of the "data" field, then the content
is stored uncompressed.

<tcl>hd_fragment dbasobj {database as object} \
     {database as container object}</tcl>
<h2>Database As Container Object</h2>

<p>
An SQLite Archive is one example of a more general
idea that an SQLite database can behave as a container object holding
lots of smaller data components.

<p>
With client/server databases like PostgreSQL or Oracle, users and
developers tend to think of the database as a service or a "node", not
as an object.  This is because the database content is spread out across
multiple files on the server, or possibly across multiple servers in a
service cluster.  One cannot point to a single file or even a single
directory and say "this is the database".

<p>
SQLite, in contrast, stores all content in a [file format|single file on disk].
That single file is something you can point to and say
"this is the database".  It behaves as an object.

An SQLite database file can be copied, renamed, sent as an
email attachment, passed as the argument a POST HTTP request,
or otherwise treated as other data object such as an image,
document, or media file.

<p>
Studies show that many applications already use
SQLite as a container object.  For example,
[https://odin.cse.buffalo.edu/papers/2015/TPCTC-sqlite-final.pdf|Kennedy]
(no relation to the [Dan Kennedy|SQLite developer]) reports that 14% of
Android applications never write to their SQLite databases.  It is
believed that these applications are downloading entire databases
from the cloud and then using the information locally as needed.  In other
words, the applications are using SQLite not so much as a database but as
a queryable wire-transfer format.

<h2>Applications Using SQLite Archives</h2>

<p>
The [https://fossil-scm.org/|Fossil Distributed Version Control] system
provides users with the option to download check-ins as either Tarballs,
ZIP Archives, or SQLite Archives.
................................................................................

<li><p>
An SQLite Archive supports only the [https://zlib.net/|Deflate] compression
method.  Tarballs and ZIP Archive support a wider assortment of
compression methods.
</ol>

<tcl>hd_fragment cltools {managing SQLite Archives from the command-line}</tcl>
<h1>Managing An SQLite Archive From The Command-Line</h1>

<p>
The recommended way of creating, updating, listing, and extracting
an SQLite Archive is to use the [sqlite3.exe command-line shell] 
for SQLite [version 3.23.0] ([dateof:3.23.0]) or later.  This CLI
supports the -A command-line option that allows easy management
of SQLite Archives.
The CLI for SQLite [version 3.22.0] ([dateof:3.22.0]) has the
[.archive command] for manageing SQLite Archives, but that requires
interacting with the shell.

<p>
To list all of the files in an SQLite Archive named "example.sqlar"
using one of these commands:






<codeblock>
sqlite3 example.sqlar -At
sqlite3 example.sqlar -Atv
</codeblock>

<p>
To extract all files from an SQLite Archive named "example.sqlar":

<codeblock>
sqlite3 example.sqlar -Ax
</codeblock>

<p>
To create a new SQLite Archive named "alltxt.sqlar" containing all *.txt
files in the current directory:

<codeblock>
sqlite3 alltxt.sqlar -Ac *.txt
</codeblock>

<p>
For usage hints and a summary of all options, simply give the [CLI]
the -A option with no additional arguments:

<codeblock>
sqlite3 -A
</codeblock>

<h2>Other command-line tools</h2>

<p>
Just as there is the "zip" program to manage ZIP Archives, and the
"tar" program to manage Tarballs, the 
[https://sqlite.org/sqlar|"sqlar" program] exists to manage SQL Archives.
The "sqlar" program is able to create a new SQLite Archive, list the
content of an existing archive, add or remove files from the archive,
and/or extract files from the archive.
A separate "sqlarfs" program is able to mount the SQLite Archive as
a [https://github.com/libfuse/libfuse|Fuse Filesystem].

<h1>Managing SQLite Archives From Application Code</h1>

<p>
Applications can easily read or write SQLite Archives by linking against
SQLite and including the 
[https://sqlite.org/src/file/ext/misc/sqlar.c|ext/misc/sqlar.c] extension
to handle the compression and decompression.  The sqlar.c extension

Changes to pages/whentouse.in.

157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182


</li>

<li><p><b>File archive and/or data container</b></p>

<p>
The [https://www.sqlite.org/sqlar|SQL Archive] project shows how
SQLite can be used as a substitute for ZIP archives or Tarballs.
An archive of files stored in SQLite is only very slightly larger, and
in some cases actually smaller, than the equivalent ZIP archive.
And an SQLite archive features incremental and atomic updating
and the ability to store much richer metadata.
</p>

<p>[https://www.fossil-scm.org/|Fossil] version 2.5 and later offers
[https://www.sqlite.org/sqlar|SQL Archive] as a download format, in addition
to traditional tarball and ZIP archive.
The sqlite3.exe [command-line shell] version 3.22.0 and later will create,
list, or unpack an SQL archiving using the 
[.archive command].</p>

<p>
SQLite is a good solution for any situation that requires bundling
diverse content into a self-contained and self-describing package 
for shipment across a network.







|








|

|







157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182


</li>

<li><p><b>File archive and/or data container</b></p>

<p>
The [SQLite Archive] idea shows how
SQLite can be used as a substitute for ZIP archives or Tarballs.
An archive of files stored in SQLite is only very slightly larger, and
in some cases actually smaller, than the equivalent ZIP archive.
And an SQLite archive features incremental and atomic updating
and the ability to store much richer metadata.
</p>

<p>[https://www.fossil-scm.org/|Fossil] version 2.5 and later offers
[SQLite Archive files] as a download format, in addition
to traditional tarball and ZIP archive.
The [sqlite3.exe command-line shell] version 3.22.0 and later will create,
list, or unpack an SQL archiving using the 
[.archive command].</p>

<p>
SQLite is a good solution for any situation that requires bundling
diverse content into a self-contained and self-describing package 
for shipment across a network.