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 |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
260f2fd7eecf1826366927a3e6d2edaf |
User & Date: | drh 2018-01-10 19:26:08.873 |
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
Changes to pages/changes.in.
︙ | ︙ | |||
67 68 69 70 71 72 73 | <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. | | > | | 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 | 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. | | > | | 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 | 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 Option<th>Purpose |
︙ | ︙ | |||
784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 | <table striped=1> <tr><th style="width:15ex">Option<th style="width:17ex">Long 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: | > > > > > > | 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 | <table striped=1> <tr><th style="width:15ex">Option<th style="width:17ex">Long 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: |
︙ | ︙ | |||
856 857 858 859 860 861 862 863 864 865 866 867 868 869 | <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 | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | <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] — 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] — 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] — 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] — 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 |
︙ | ︙ | |||
976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 | 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 { | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | | > > | < < < < < | < < < | > | > > > > > | | | < | 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 | 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 1137 1138 1139 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 used in a few situations where it is appropriate. This results in a slightly smaller and faster binary. The SQLITE_USE_ALLOCA compile-time only only works, of course, on systems that support alloca(). } 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 1143 1144 1145 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 used in a few situations where it is appropriate. This results in a slightly smaller and faster binary. The SQLITE_USE_ALLOCA compile-time only only works, of course, on systems that support alloca(). } 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 | 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 | | | 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 | 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} |
︙ | ︙ | |||
1757 1758 1759 1760 1761 1762 1763 | EnablePragma function_list { <p>^(<b>PRAGMA function_list;</b> <p>This pragma returns a list of SQL functions known to the database connection.)^ DISCLAIMER | | | 1757 1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 | 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 | <title>The SQLite Zipfile Module</title> <tcl>hd_keywords {Zipfile virtual table} {zipfile}</tcl> <table_of_contents> <h1>Overview</h1> | | | > | | | > > | < | > > > | > > | 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 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | <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. In this case, the following function should be invoked to register the extension with each new database connection: <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. |
︙ | ︙ | |||
219 220 221 222 223 224 225 | <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> | < < < | 226 227 228 229 230 231 232 | <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> |