Documentation Source Text

Check-in [260f2fd7ee]
Login

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

Overview
Comment:Improved documentation for recently added features such as SQL Archive support and the Zipfile extension.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:260f2fd7eecf1826366927a3e6d2edaf966ea448dbcf487d9ef5033cc70dbf9a
User & Date: drh 2018-01-10 19:26:08
Context
2018-01-10
19:39
Further tweaks to the cli.html document. check-in: c27765f3b7 user: drh tags: trunk
19:26
Improved documentation for recently added features such as SQL Archive support and the Zipfile extension. check-in: 260f2fd7ee user: drh tags: trunk
17:06
Update zipfile docs to match the implementation. check-in: 469ec4c63f user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/changes.in.

67
68
69
70
71
72
73
74
75

76
77
78
79
80
81
82
  <li> Use the strcspn() C-library routine to speed up the LIKE and
       GLOB operators.
</ol>
<li> Improvements to the [command-line shell]:
<ol type='a'>
  <li> The ".schema" command shows the structure of virtual tables
       inside of a comment.
  <li> Added support for reading and writing [SQL Archive] files using
       the ".archive" or ".ar" command.

  <li> Added the experimenal [.expert command]
  <li> Added the ".eqp trigger" variant of the ".eqp" command
  <li> Enhance the ".lint fkey-indexes" command so that it works with
       [WITHOUT ROWID] tables.
  <li> If the filename argument to the shell is a ZIP archive rather than
       an SQLite database, then the shell automatically opens that ZIP 
       archive using the [Zipfile virtual table].







|
|
>







67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
  <li> Use the strcspn() C-library routine to speed up the LIKE and
       GLOB operators.
</ol>
<li> Improvements to the [command-line shell]:
<ol type='a'>
  <li> The ".schema" command shows the structure of virtual tables
       inside of a comment.
  <li> Added support for reading and writing
       [https://sqlite.org/sqlar|SQL Archive] files using
       the [.archive command].
  <li> Added the experimenal [.expert command]
  <li> Added the ".eqp trigger" variant of the ".eqp" command
  <li> Enhance the ".lint fkey-indexes" command so that it works with
       [WITHOUT ROWID] tables.
  <li> If the filename argument to the shell is a ZIP archive rather than
       an SQLite database, then the shell automatically opens that ZIP 
       archive using the [Zipfile virtual table].

Changes to pages/cli.in.

757
758
759
760
761
762
763
764
765
766

767
768
769
770
771
772
773
774
...
784
785
786
787
788
789
790




791
792


793
794
795
796
797
798
799
...
856
857
858
859
860
861
862






















































863
864
865
866
867
868
869
...
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
does not already exists, then appends entries that check the SHA3
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 {SQL Archive}</tcl>
<h1>SQLAR Archive Support</h1>


<p>The shell tool ".ar" dot-command provides built-in support for the 
[https://sqlite.org/sqlar|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
................................................................................

<table striped=1>
  <tr><th style="width:15ex">Option<th style="width:17ex">Long&nbsp;Option<th>Purpose
  <tr><td>-v<td>--verbose<td>List each file as it is processed.
  <tr><td>-f FILE<td>--file FILE<td>If specified, use file FILE as the
  archive. Otherwise, assume that the current "main" database is the 
  archive to be operated on.




  <tr><td>-C DIR<td>--directory DIR<td>If specified, interpret all relative
  paths as relative to DIR, instead of the current working directory.


  <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:
................................................................................
<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.























































<tcl>hd_fragment expert {.expert command}</tcl>
<h1>Index Recommendations (SQLite Expert)</h1>

<p><b>Note: This command is experimental. It may be removed or the 
interface modified in incompatible ways at some point in the future.

<p>For most non-trivial SQL databases, the key to performance is creating
................................................................................
because the shell translates these into a semicolon before passing them 
to that function.</p>


<tcl>hd_fragment compiling</tcl>
<h1>Compiling the sqlite3 program from sources</h1>








































<p>
The source code to the sqlite3 command line interface is in a single
file named "shell.c".  The shell.c source file is generated from other
sources, but most of the code for shell.c can be found in
[https://sqlite.org/src/file/src/shell.c.in|src/shell.c.in].
(Regenerate shell.c by typing "make shell.c" from the canonical source tree.)
[how to compile|Compile] the shell.c file (together
with the [amalgamation | sqlite3 library source code]) to generate
the executable.  For example:</p>

<tclscript>DisplayCode {
gcc -o sqlite3 shell.c sqlite3.c -ldl -lpthread
} </tclscript>

<p>
You might want to provide additional compile-time options:
<ul>
<li><p>[-DSQLITE_THREADSAFE=0]:
The command-line shell does no use threads, so you might as well turn
thread-safety off, for improved performance and a reduced code footprint.
This also eliminates the need for the "-lpthread" switch on the
compiler command line.

<li><p>-DSQLITE_ENABLE_EXPLAIN_COMMENT:
This option adds comment text to the end of each opcode in the
output of [EXPLAIN].  The EXPLAIN output is much easier to read
with this option enabled.

<li><p>[-DSQLITE_ENABLE_UNKNOWN_SQL_FUNCTION]:
This option allows [EXPLAIN] statements to succeed even if the contain
references to unknown SQL functions.  This makes the shell useful for
looking at EXPLAIN or EXPLAIN QUERY PLAN output for queries pasted out
of applications that define and use custom SQL functions.
</ul>







|


>
|







 







>
>
>
>


>
>







 







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







 







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











|



|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
<
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
...
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
...
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
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
929
930
....
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115

does not already exists, then appends entries that check the SHA3
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 {SQL Archive} {.archive command}</tcl>
<h1>SQLAR Archive Support</h1>

<p>The shell tool ".archive" dot-command (often abbreviated as ".ar")
provides built-in support for the 
[https://sqlite.org/sqlar|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
................................................................................

<table striped=1>
  <tr><th style="width:15ex">Option<th style="width:17ex">Long&nbsp;Option<th>Purpose
  <tr><td>-v<td>--verbose<td>List each file as it is processed.
  <tr><td>-f FILE<td>--file FILE<td>If specified, use file FILE as the
  archive. Otherwise, assume that the current "main" database is the 
  archive to be operated on.
  <tr><td>-a FILE<td>--append FILE<td>Like --file, use file FILE as the
  archive, but open the file using the 
  [https://sqlite.org/src/file/ext/misc/appendvfs.c|apndvfs VFS] so that
  the archive will be appended to the end of FILE if FILE already exists.
  <tr><td>-C DIR<td>--directory DIR<td>If specified, interpret all relative
  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:
................................................................................
<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
the contents of a directory and the lsname() function for converting
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.
</ol>

<tcl>hd_fragment expert {.expert command}</tcl>
<h1>Index Recommendations (SQLite Expert)</h1>

<p><b>Note: This command is experimental. It may be removed or the 
interface modified in incompatible ways at some point in the future.

<p>For most non-trivial SQL databases, the key to performance is creating
................................................................................
because the shell translates these into a semicolon before passing them 
to that function.</p>


<tcl>hd_fragment compiling</tcl>
<h1>Compiling the sqlite3 program from sources</h1>

<p>
To compile the command-line shell on unix systems and on Windows with MinGW,
the usual configure-make command works:

<codeblock>
sh configure; make
</codeblock>

<p>
The configure-make works whether your are building from the canonical sources
from the source tree, or from a amalgamated bundle.  There are few
dependencies.  When building from canonical sources, a working 
[https://www.tcl.tk/man/tcl8.3/UserCmd/tclsh.htm|tclsh] is required.
If using an amalgamation bundle, all the preprocessing work normally 
done by tclsh will have already been carried out and only normal build
tools are required.

<p>
A working [https://zlib.net|zlib compression library] is
needed in order for the [.archive command] to operate.

<p>
On Windows with MSVC, use nmake with the Makefile.msc:

<codeblock>
nmake /f Makefile.msc
</codeblock>

<p>
For correct operation of the [.archive command], make a copy of the
[https://zlib.net|zlib source code] into the compat/zlib subdirectory 
of the source tree and compile this way:

<codeblock>
nmake /f Makefile.msc USE_ZLIB=1
</codeblock>

<h2> Do-It-Yourself Builds </h2>

<p>
The source code to the sqlite3 command line interface is in a single
file named "shell.c".  The shell.c source file is generated from other
sources, but most of the code for shell.c can be found in
[https://sqlite.org/src/file/src/shell.c.in|src/shell.c.in].
(Regenerate shell.c by typing "make shell.c" from the canonical source tree.)
[how to compile|Compile] the shell.c file (together
with the [amalgamation | sqlite3 library source code]) to generate
the executable.  For example:</p>

<tclscript>DisplayCode {
gcc -o sqlite3 shell.c sqlite3.c -ldl -lpthread -lz -lm
} </tclscript>

<p>
The following additional compile-time options are recommended in order to
provide a full-featured command-line shell:

<ul>
<li> [-DSQLITE_THREADSAFE=0]
<li> [-DSQLITE_ENABLE_EXPLAIN_COMMENTS]
<li> [-DSQLITE_USE_ZLIB]
<li> [-DSQLITE_INTROSPECTION_PRAGMAS]
<li> [-DSQLITE_ENABLE_UNKNOWN_SQL_FUNCTION]
<li> [-DSQLITE_ENABLE_STMTVTAB]
<li> [-DSQLITE_ENABLE_DBPAGE_VTAB]
<li> [-DSQLITE_ENABLE_DBSTAT_VTAB]
<li> [-DSQLITE_ENABLE_OFFSET_SQL_FUNC]
<li> [-DSQLITE_ENABLE_JSON1]
<li> [-DSQLITE_ENABLE_RTREE]
<li> [-DSQLITE_ENABLE_FTS4]
<li> [-DSQLITE_ENABLE_FTS5]
</ul>

Changes to pages/compile.in.

1123
1124
1125
1126
1127
1128
1129






1130
1131
1132
1133
1134
1135
1136
....
1140
1141
1142
1143
1144
1145
1146




















1147
1148
1149
1150
1151
1152
1153

COMPILE_OPTION {SQLITE_ENABLE_UNLOCK_NOTIFY} {
  This option enables the [sqlite3_unlock_notify()] interface and
  its associated functionality.  See the documentation titled
  [Using the SQLite Unlock Notification Feature] for additional
  information.
}







COMPILE_OPTION {SQLITE_SOUNDEX} {
  This option enables the [soundex() SQL function].
}

COMPILE_OPTION {SQLITE_USE_ALLOCA} {
  If this option is enabled, then the alloca() memory allocator will be
................................................................................
}

COMPILE_OPTION {SQLITE_USE_FCNTL_TRACE} {
  This option causes SQLite to issue extra [SQLITE_FCNTL_TRACE] file controls
  to provide supplementary information to the VFS.  The "vfslog.c" extension
  makes use of this to provide enhanced logs of VFS activity.
}





















COMPILE_OPTION {YYTRACKMAXSTACKDEPTH} {
  This option causes the LALR(1) parser stack depth to be tracked
  and reported using the [sqlite3_status]([SQLITE_STATUS_PARSER_STACK],...)
  interface.  SQLite's LALR(1) parser has a fixed stack depth
  (determined at compile-time using the [YYSTACKDEPTH] options).
  This option can be used to help determine if an application is







>
>
>
>
>
>







 







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







1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
....
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179

COMPILE_OPTION {SQLITE_ENABLE_UNLOCK_NOTIFY} {
  This option enables the [sqlite3_unlock_notify()] interface and
  its associated functionality.  See the documentation titled
  [Using the SQLite Unlock Notification Feature] for additional
  information.
}

COMPILE_OPTION {SQLITE_INTROSPECTION_PRAGMAS} {
  This option adds some extra PRAGMA statements such as
  [PRAGMA function_list], [PRAGMA module_list], and
  [PRAGMA pragma_list].
}

COMPILE_OPTION {SQLITE_SOUNDEX} {
  This option enables the [soundex() SQL function].
}

COMPILE_OPTION {SQLITE_USE_ALLOCA} {
  If this option is enabled, then the alloca() memory allocator will be
................................................................................
}

COMPILE_OPTION {SQLITE_USE_FCNTL_TRACE} {
  This option causes SQLite to issue extra [SQLITE_FCNTL_TRACE] file controls
  to provide supplementary information to the VFS.  The "vfslog.c" extension
  makes use of this to provide enhanced logs of VFS activity.
}

COMPILE_OPTION {SQLITE_USE_ZLIB} {
  This option causes some extensions to link against the 
  [https://zlib.net|zlib compression library].
  <p>
  This option has no affect on the SQLite core.  It is only used by extensions.
  This is option is necessary for the commpression and decompression
  functions that are part of [SQL Archive] support in the
  [command-line shell].
  <p>
  When compiling with this option, it will normally
  be necessary to add a linker option to include the zlib library in the
  build.  Normal this option is "-lz" but might be different on different
  systems.
  <p>
  When building with MSVC on Windows systems, one can put the zlib source
  code in the compat/zlib subdirectory of the source tree and then add
  the USE_ZLIB=1 option to the nmake command to cause the The Makefile.msc
  to automatically build and use an appropriate zlib library implementation.
}

COMPILE_OPTION {YYTRACKMAXSTACKDEPTH} {
  This option causes the LALR(1) parser stack depth to be tracked
  and reported using the [sqlite3_status]([SQLITE_STATUS_PARSER_STACK],...)
  interface.  SQLite's LALR(1) parser has a fixed stack depth
  (determined at compile-time using the [YYSTACKDEPTH] options).
  This option can be used to help determine if an application is

Changes to pages/pragma.in.

63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
....
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
    recommended for use by application programs.</p>
  }
}
proc EnableDisclaimer {copt} {
  return "\
    <p style='background-color: #f0e0ff;'>
    This pragma is only available if SQLite is built using the
    -D$copt compile-time option.
  "
}
# Legacy pragma - do not use these
proc LegacyPragma {namelist content} {
  Pragma $namelist [string map [list DISCLAIMER [LegacyDisclaimer]] $content]
  global PragmaLegacy
  foreach x $namelist {set PragmaLegacy($x) 1}
................................................................................

EnablePragma function_list {
    <p>^(<b>PRAGMA function_list;</b>
    <p>This pragma returns a list of SQL functions
    known to the database connection.)^

    DISCLAIMER
} SQLITE_INTROSPECTION_PRAGMAS

EnablePragma pragma_list {
    <p>^(<b>PRAGMA pragma_list;</b>
    <p>This pragma returns a list of PRAGMA commands
    known to the database connection.)^

    DISCLAIMER







|







 







|







63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
....
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
    recommended for use by application programs.</p>
  }
}
proc EnableDisclaimer {copt} {
  return "\
    <p style='background-color: #f0e0ff;'>
    This pragma is only available if SQLite is built using the
    \[-D$copt\] compile-time option.
  "
}
# Legacy pragma - do not use these
proc LegacyPragma {namelist content} {
  Pragma $namelist [string map [list DISCLAIMER [LegacyDisclaimer]] $content]
  global PragmaLegacy
  foreach x $namelist {set PragmaLegacy($x) 1}
................................................................................

EnablePragma function_list {
    <p>^(<b>PRAGMA function_list;</b>
    <p>This pragma returns a list of SQL functions
    known to the database connection.)^

    DISCLAIMER
} SQLITE_INTROSPECTION_PRAGMAS	

EnablePragma pragma_list {
    <p>^(<b>PRAGMA pragma_list;</b>
    <p>This pragma returns a list of PRAGMA commands
    known to the database connection.)^

    DISCLAIMER

Changes to pages/zipfile.in.

1
2
3
4
5
6
7
8

9
10
11
12

13


14
15
16
17
18
19
20
21



22
23
24
25
26
27
28
29
..
32
33
34
35
36
37
38


39
40
41
42
43
44
45
...
219
220
221
222
223
224
225
226
227
228
<title>The SQLite Zipfile Module</title>
<tcl>hd_keywords {Zipfile virtual table} {zipfile}</tcl>
<table_of_contents>

<h1>Overview</h1>

<p> The zipfile module provides read/write access to simple zip archives.
At time of writing, simple zip archives are roughly those that:


<ul>
  <li> Do not use encryption, 
  <li> Do not span multiple files, and

  <li> Do not use zip64 extensions.


</ul>

<p> Some or all of these restrictions may be removed at some point
in the future.

<h1>Obtaining and Compiling Zipfile</h1>

<p>The code for the zipfile module is found in the ext/misc/zipfile.c file of



the main SQLite source tree. It may be compiled into an SQLite 
[loadable extension] using a command like:

<codeblock>
    gcc -g -fPIC -shared zipfile.c -o zipfile.so
</codeblock>

<p>Alternatively, the zipfile.c file may be compiled into the application. 
................................................................................

<codeblock>
  int sqlite3_zipfile_init(sqlite3 *db, void*, void*);
</codeblock>

<p> The first argument passed should be the database handle to register the
extension with. The second and third arguments should both be passed 0.



<h1>Using Zipfile</h1>

<p>The zipfile module provides two similar interfaces for accessing zip
archives. A table-valued function, which provides read-only access to
existing archives, and a virtual table interface, which provides both
read and write access.
................................................................................
  <i>-- Create a new, empty, archive: </i>
  CREATE VIRTUAL TABLE temp.newzip USING zipfile('new.zip');

  <i>-- Copy the contents of the existing archive into the new archive</i>
  INSERT INTO temp.newzip(name, mode, mtime, sz, rawdata, method) 
      SELECT name, mode, mtime, sz, rawdata, method FROM temp.zzz;
</codeblock>









|
|
>


<
|
>
|
>
>


|
<



|
>
>
>
|







 







>
>







 







<
<
<
1
2
3
4
5
6
7
8
9
10
11

12
13
14
15
16
17
18
19

20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
..
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
...
226
227
228
229
230
231
232



<title>The SQLite Zipfile Module</title>
<tcl>hd_keywords {Zipfile virtual table} {zipfile}</tcl>
<table_of_contents>

<h1>Overview</h1>

<p> The zipfile module provides read/write access to simple 
[https://en.wikipedia.org/wiki/Zip_%28file_format%29|ZIP archives].
The current implementation has the following restrictions:

<ul>

  <li> Does not support encryption.
  <li> Does not support ZIP archives that span multiple files.
  <li> Does not support zip64 extensions.
  <li> The only compression algorithm supported is
       [https://zlib.net|"deflate"].
</ul>

<p> Some or all of these restrictions may be removed in the future.


<h1>Obtaining and Compiling Zipfile</h1>

<p>The code for the zipfile module is found in the 
[https://sqlite.org/src/file/ext/misc/zipfile.c|ext/misc/zipfile.c]
file of the
[https://sqlite.org/src|main SQLite source tree].
It may be compiled into an SQLite 
[loadable extension] using a command like:

<codeblock>
    gcc -g -fPIC -shared zipfile.c -o zipfile.so
</codeblock>

<p>Alternatively, the zipfile.c file may be compiled into the application. 
................................................................................

<codeblock>
  int sqlite3_zipfile_init(sqlite3 *db, void*, void*);
</codeblock>

<p> The first argument passed should be the database handle to register the
extension with. The second and third arguments should both be passed 0.

<p> Zipfile is included in most builds of the [command-line shell].

<h1>Using Zipfile</h1>

<p>The zipfile module provides two similar interfaces for accessing zip
archives. A table-valued function, which provides read-only access to
existing archives, and a virtual table interface, which provides both
read and write access.
................................................................................
  <i>-- Create a new, empty, archive: </i>
  CREATE VIRTUAL TABLE temp.newzip USING zipfile('new.zip');

  <i>-- Copy the contents of the existing archive into the new archive</i>
  INSERT INTO temp.newzip(name, mode, mtime, sz, rawdata, method) 
      SELECT name, mode, mtime, sz, rawdata, method FROM temp.zzz;
</codeblock>