Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Enhance CLI documentation to talk more about reading ZIP archives as database files. |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
e92fb4339629afdd798ca34709d87e54 |
User & Date: | drh 2019-12-21 15:22:21 |
Context
2019-12-21
| ||
17:23 | Enhancements and updates to the discussion of fuzzing in the testing document. (check-in: 3bf90e0caa user: drh tags: trunk) | |
15:22 | Enhance CLI documentation to talk more about reading ZIP archives as database files. (check-in: e92fb43396 user: drh tags: trunk) | |
2019-12-07
| ||
12:14 | Update the rtree documentation to report that type affinity is ignored for auxiliary columns. (check-in: 6958f518ec user: drh tags: trunk) | |
Changes
Changes to pages/cli.in.
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
725
726
727
728
729
730
731
732
733
734
735
736
737
738
|
<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) that allows the user to manually enter and execute SQL statements against an SQLite database. This document provides a brief introduction on how to use the <b>sqlite3</b> program. <p>Start the <b>sqlite3</b> program by typing "sqlite3" at the command prompt, optionally followed by the name the file that holds the SQLite database. If the named file does not exist, a new database file with the given name will be created automatically. If no database file is specified on the command-line, a temporary database is created, then deleted when the "sqlite3" program exits. <p>On startup, the <b>sqlite3</b> program will show a brief banner message then prompt you to enter SQL. Type in SQL statements (terminated ................................................................................ for CSV files. <p> There is also a ".once -e" command which works similarly, except that it names the temporary file with a ".txt" suffix so that the default text editor for the system will be invoked, instead of the default spreadsheet. <tcl>hd_fragment dump {.dump}</tcl> <h1>Converting An Entire Database To An ASCII Text File</h1> <p>Use the ".dump" command to convert the entire contents of a database into a single ASCII text file. This file can be converted back into a database by piping it back into <b>sqlite3</b>.</p> |
|
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
...
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
|
<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) that allows the user to manually enter and execute SQL statements against an SQLite database or against a <a href="#zipdb">ZIP archive</a>. This document provides a brief introduction on how to use the <b>sqlite3</b> program. <p>Start the <b>sqlite3</b> program by typing "sqlite3" at the command prompt, optionally followed by the name the file that holds the SQLite database (or <a href="#zipdb">ZIP archive</a>). If the named file does not exist, a new database file with the given name will be created automatically. If no database file is specified on the command-line, a temporary database is created, then deleted when the "sqlite3" program exits. <p>On startup, the <b>sqlite3</b> program will show a brief banner message then prompt you to enter SQL. Type in SQL statements (terminated ................................................................................ for CSV files. <p> There is also a ".once -e" command which works similarly, except that it names the temporary file with a ".txt" suffix so that the default text editor for the system will be invoked, instead of the default spreadsheet. <tcl>hd_fragment zipdb {ZIP file as database}</tcl> <h1>Accessing ZIP Archives As Database Files</h1> <p>In addition to reading and writing SQLite database files, the <b>sqlite3</b> program will also read and write ZIP archives. Simply specify a ZIP archive filename in place of an SQLite database filename on the initial command line, or in the ".open" command, and <b>sqlite3</b> will automatically detect that the file is a ZIP archive instead of an SQLite database and will open it as such. This works regardless of file suffix. So you can open JAR, DOCX, and ODP files and any other file format that is really a ZIP archive and SQLite will read it for you. <p>A ZIP archive appears to be a database containing a single table with the following schema: <tclscript>DisplayCode { CREATE TABLE zip( name, // Name of the file mode, // Unix-style file permissions mtime, // Timestamp, seconds since 1970 sz, // File size after decompression rawdata, // Raw compressed file data data, // Uncompressed file content method // ZIP compression method code ); }</tclscript> <p>So, for example, if you wanted to see the compression efficiency (expressed as the size of the compressed content relative to the original uncompressed file size) for all files in the ZIP archive, sorted from most compressed to least compressed, you could run a query like this: <tclscript>DisplayCode { sqlite> SELECT name, (100.0*length(rawdata))/sz FROM zip ORDER BY 2; }</tclscript> <p>Or using [file I/O functions], you can extract elements of the ZIP archive: <tclscript>DisplayCode { sqlite> SELECT writefile(name,content) FROM zip ...> WHERE name LIKE 'docProps/%'; }</tclscript> <h2>How ZIP archive access is implemented</h2> <p>The command-line shell uses the [Zipfile virtual table] to access ZIP archives. You can see this by running the ".schema" command when a ZIP archive is open: <tclscript>DisplayCode { sqlite> .schema CREATE VIRTUAL TABLE zip USING zipfile('document.docx') /* zip(name,mode,mtime,sz,rawdata,data,method) */; }</tclscript> <p>When opening a file, if the command-line client discovers that the file is ZIP archive instead of an SQLite database, it actually opens an [in-memory database] and then in that in-memory database it creates an instance of the [Zipfile virtual table] that is attached to the ZIP archive. <p>The special processing for opening ZIP archives is a trick of the command-line shell, not the core SQLite library. So if you want to open a ZIP archive as a database in your application, you will need to activate the [Zipfile virtual table] module then run an appropriate [CREATE VIRTUAL TABLE] statement. <tcl>hd_fragment dump {.dump}</tcl> <h1>Converting An Entire Database To An ASCII Text File</h1> <p>Use the ".dump" command to convert the entire contents of a database into a single ASCII text file. This file can be converted back into a database by piping it back into <b>sqlite3</b>.</p> |
Changes to pages/pragma.in.
1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 1603 |
hidden columns are shown rather than being omitted.
}
DangerousPragma {schema_version} {
<p><b>PRAGMA DB.schema_version;
<br>PRAGMA DB.schema_version = </b><i>integer </i>;
<p> ^The schema_version pragma will to get or set
the value of the schema-version integer at offset 40 in the
[database header].
<p> ^SQLite automatically increments the schema-version whenever the
schema changes. ^As each SQL statement runs, the schema version is
checked to ensure that the schema has not changed since the SQL
statement was [sqlite3_prepare|prepared].
|
| |
1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 1603 |
hidden columns are shown rather than being omitted. } DangerousPragma {schema_version} { <p><b>PRAGMA DB.schema_version; <br>PRAGMA DB.schema_version = </b><i>integer </i>; <p> ^The schema_version pragma will get or set the value of the schema-version integer at offset 40 in the [database header]. <p> ^SQLite automatically increments the schema-version whenever the schema changes. ^As each SQL statement runs, the schema version is checked to ensure that the schema has not changed since the SQL statement was [sqlite3_prepare|prepared]. |