This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionLast revisionBoth sides next revision | ||
howtos:sqliteutilitynotes [2007/01/23 00:06] – texasfett | howtos:sqliteutilitynotes [2007/01/23 00:30] – texasfett | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ===== sqlite Utility Notes ===== | + | Duplicate pages merged |
- | a command-line utility | + | |
- | + | ||
- | ==== Introduction ==== | + | |
- | + | ||
- | POPFile uses an SQLite database to hold essential information, | + | |
- | + | ||
- | The SQLite library includes a simple command-line utility that allows the user to manually enter and execute SQL commands against an SQLite database. Databases built using SQLite 2.x libraries are not compatible with databases built using the SQLite 3.x libraries. Therefore there are separate command-line utilities: **sqlite** for SQLite 2.x format databases and **sqlite3** for SQLite 3.x format databases. | + | |
- | + | ||
- | The SQLite web site only seems to document the **sqlite3** utility which is not compatible with the SQLite databases created by POPFile. The information on this wiki page is based upon an old page (last modified on 2004/05/31 15:06:30) from the SQLite web site which documented the features supported by the **sqlite** command-line utility. | + | |
- | + | ||
- | The SQLite web site still has some [[http:// | + | |
- | + | ||
- | + | ||
- | ==== Getting Started ==== | + | |
- | + | ||
- | To start the **sqlite** program, just type " | + | |
- | + | ||
- | For example, to create a new SQLite database named " | + | |
- | + | ||
- | < | + | |
- | $ sqlite ex1 | + | |
- | SQLite version 2.8.17 | + | |
- | Enter " | + | |
- | sqlite> create table tbl1(one varchar(10), | + | |
- | sqlite> insert into tbl1 values(' | + | |
- | sqlite> insert into tbl1 values(' | + | |
- | sqlite> select * from tbl1; | + | |
- | hello!|10 | + | |
- | goodbye|20 | + | |
- | sqlite> | + | |
- | </ | + | |
- | + | ||
- | You can terminate the sqlite program by typing your systems End-Of-File character (usually a Control-D) or the interrupt character (usually a Control-C). | + | |
- | + | ||
- | Make sure you type a semicolon at the end of each SQL command! The sqlite looks for a semicolon to know when your SQL command is complete. If you omit the semicolon, **sqlite** will give you a continuation prompt and wait for you to enter more text to be added to the current SQL command. This feature allows you to enter SQL commands that span multiple lines. For example: | + | |
- | + | ||
- | < | + | |
- | sqlite> CREATE TABLE tbl2 ( | + | |
- | | + | |
- | | + | |
- | | + | |
- | | + | |
- | sqlite> | + | |
- | </ | + | |
- | + | ||
- | + | ||
- | ==== Aside: Querying the SQLITE_MASTER table ==== | + | |
- | + | ||
- | The database schema in an SQLite database is stored in a special table named " | + | |
- | + | ||
- | < | + | |
- | $ sqlite ex1 | + | |
- | SQLite version 2.8.17 | + | |
- | Enter " | + | |
- | sqlite> .mode lines | + | |
- | sqlite> select * from sqlite_master; | + | |
- | type = table | + | |
- | name = tbl1 | + | |
- | tbl_name = tbl1 | + | |
- | rootpage = 3 | + | |
- | sql = create table tbl1(one varchar(10), | + | |
- | sqlite> | + | |
- | </ | + | |
- | + | ||
- | But you cannot execute DROP TABLE, UPDATE, INSERT or DELETE against the sqlite_master table. The sqlite_master table is updated automatically as you create or drop tables and indices from the database. You can not make manual changes to the sqlite_master table. | + | |
- | + | ||
- | The schema for TEMPORARY tables is not stored in the " | + | |
- | + | ||
- | + | ||
- | ==== Special commands to sqlite ==== | + | |
- | + | ||
- | Most of the time, sqlite just reads lines of input and passes them on to the SQLite library for execution. But if an input line begins with a dot (" | + | |
- | + | ||
- | For a listing of the available dot commands, you can enter " | + | |
- | + | ||
- | < | + | |
- | sqlite> .help | + | |
- | .databases | + | |
- | .dump ?TABLE? ... Dump the database in a text format | + | |
- | .echo ON|OFF | + | |
- | .exit Exit this program | + | |
- | .explain ON|OFF | + | |
- | .header(s) ON|OFF | + | |
- | .help Show this message | + | |
- | .indices TABLE Show names of all indices on TABLE | + | |
- | .mode MODE Set mode to one of " | + | |
- | " | + | |
- | .mode insert TABLE | + | |
- | .nullvalue STRING | + | |
- | .output FILENAME | + | |
- | .output stdout | + | |
- | .prompt MAIN CONTINUE | + | |
- | .quit Exit this program | + | |
- | .read FILENAME | + | |
- | .schema ? | + | |
- | .separator STRING | + | |
- | .show Show the current values for various settings | + | |
- | .tables ? | + | |
- | .timeout MS Try opening locked tables for MS milliseconds | + | |
- | .width NUM NUM ... Set column widths for " | + | |
- | sqlite> | + | |
- | </ | + | |
- | + | ||
- | + | ||
- | ==== Changing Output Formats ==== | + | |
- | + | ||
- | The sqlite program is able to show the results of a query in five different formats: " | + | |
- | + | ||
- | The default output mode is " | + | |
- | + | ||
- | < | + | |
- | sqlite> .mode list | + | |
- | sqlite> select * from tbl1; | + | |
- | hello!|10 | + | |
- | goodbye|20 | + | |
- | sqlite> | + | |
- | </ | + | |
- | + | ||
- | You can use the " | + | |
- | + | ||
- | < | + | |
- | sqlite> .separator ", " | + | |
- | sqlite> select * from tbl1; | + | |
- | hello!, 10 | + | |
- | goodbye, 20 | + | |
- | sqlite> | + | |
- | </ | + | |
- | + | ||
- | In " | + | |
- | + | ||
- | < | + | |
- | sqlite> .mode line | + | |
- | sqlite> select * from tbl1; | + | |
- | one = hello! | + | |
- | two = 10 | + | |
- | + | ||
- | one = goodbye | + | |
- | two = 20 | + | |
- | sqlite> | + | |
- | </ | + | |
- | + | ||
- | In column mode, each record is shown on a separate line with the data aligned in columns. For example: | + | |
- | + | ||
- | < | + | |
- | sqlite> .headers on | + | |
- | sqlite> .mode column | + | |
- | sqlite> select * from tbl1; | + | |
- | one two | + | |
- | ---------- | + | |
- | hello! | + | |
- | goodbye | + | |
- | sqlite> | + | |
- | </ | + | |
- | + | ||
- | By default, each column is at least 10 characters wide. Data that is too wide to fit in a column is truncated. You can adjust the column widths using the " | + | |
- | + | ||
- | < | + | |
- | sqlite> .headers on | + | |
- | sqlite> .mode column | + | |
- | sqlite> .width 12 6 | + | |
- | sqlite> select * from tbl1; | + | |
- | one two | + | |
- | ------------ | + | |
- | hello! | + | |
- | goodbye | + | |
- | sqlite> | + | |
- | </ | + | |
- | + | ||
- | The " | + | |
- | + | ||
- | If you specify a column a width of 0, then the column width is automatically adjusted to be the maximum of three numbers: 10, the width of the header, and the width of the first row of data. This makes the column width self-adjusting. The default width setting for every column is this auto-adjusting 0 value. | + | |
- | + | ||
- | The column labels that appear on the first two lines of output can be turned on and off using the " | + | |
- | + | ||
- | < | + | |
- | sqlite> .header off | + | |
- | sqlite> select * from tbl1; | + | |
- | hello! | + | |
- | goodbye | + | |
- | sqlite> | + | |
- | </ | + | |
- | + | ||
- | Another useful output mode is " | + | |
- | + | ||
- | When specifying insert mode, you have to give an extra argument which is the name of the table to be inserted into. For example: | + | |
- | + | ||
- | < | + | |
- | sqlite> .mode insert new_table | + | |
- | sqlite> select * from tbl1; | + | |
- | INSERT INTO new_table VALUES(' | + | |
- | INSERT INTO new_table VALUES(' | + | |
- | sqlite> | + | |
- | </ | + | |
- | + | ||
- | The last output mode is " | + | |
- | + | ||
- | < | + | |
- | sqlite> .mode html | + | |
- | sqlite> select * from tbl1; | + | |
- | < | + | |
- | < | + | |
- | </ | + | |
- | < | + | |
- | < | + | |
- | </ | + | |
- | sqlite> | + | |
- | </ | + | |
- | + | ||
- | The html output mode is envisioned as being useful for CGI. | + | |
- | + | ||
- | + | ||
- | ==== Writing results to a file ==== | + | |
- | + | ||
- | By default, sqlite sends query results to standard output. You can change this using the " | + | |
- | + | ||
- | < | + | |
- | sqlite> .mode list | + | |
- | sqlite> .separator | | + | |
- | sqlite> .output test_file_1.txt | + | |
- | sqlite> select * from tbl1; | + | |
- | sqlite> .exit | + | |
- | $ cat test_file_1.txt | + | |
- | hello!|10 | + | |
- | goodbye|20 | + | |
- | $ | + | |
- | </ | + | |
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | ==== Querying the database schema ==== | + | |
- | + | ||
- | The sqlite program provides several convenience commands that are useful for looking at the schema of the database. There is nothing that these commands do that cannot be done by some other means. These commands are provided purely as a shortcut. | + | |
- | + | ||
- | For example, to see a list of the tables in the database, you can enter " | + | |
- | + | ||
- | < | + | |
- | sqlite> .tables | + | |
- | tbl1 | + | |
- | tbl2 | + | |
- | sqlite> | + | |
- | </ | + | |
- | + | ||
- | The " | + | |
- | + | ||
- | <code sql> | + | |
- | SELECT name FROM sqlite_master WHERE type=' | + | |
- | UNION ALL SELECT name FROM sqlite_temp_master WHERE type=' | + | |
- | ORDER BY name; | + | |
- | </ | + | |
- | + | ||
- | In fact, if you look at the source code to the sqlite program (found in the source tree in the file src/ | + | |
- | + | ||
- | The " | + | |
- | + | ||
- | < | + | |
- | sqlite> .schema | + | |
- | create table tbl1(one varchar(1), two smallint); | + | |
- | CREATE TABLE tbl2 ( | + | |
- | f1 varchar(30) primary key, | + | |
- | f2 text, | + | |
- | f3 real | + | |
- | ); | + | |
- | sqlite> .schema tbl2 | + | |
- | CREATE TABLE tbl2 ( | + | |
- | f1 varchar(30) primary key, | + | |
- | f2 text, | + | |
- | f3 real | + | |
- | ); | + | |
- | sqlite> | + | |
- | </ | + | |
- | + | ||
- | The " | + | |
- | + | ||
- | <code sql> | + | |
- | | + | |
- | (SELECT * FROM sqlite_master UNION ALL | + | |
- | | + | |
- | WHERE type!=' | + | |
- | ORDER BY tbl_name, type DESC, name; | + | |
- | </ | + | |
- | + | ||
- | Or, if you give an argument to " | + | |
- | + | ||
- | <code sql> | + | |
- | SELECT sql FROM | + | |
- | | + | |
- | SELECT * FROM sqlite_temp_master) | + | |
- | WHERE tbl_name LIKE ' | + | |
- | ORDER BY type DESC, name; | + | |
- | </ | + | |
- | + | ||
- | The **%s** in the query above is replaced by the argument to " | + | |
- | + | ||
- | < | + | |
- | + | ||
- | Along these same lines, the " | + | |
- | + | ||
- | The " | + | |
- | + | ||
- | < | + | |
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | ==== Converting An Entire Database To An ASCII Text File ==== | + | |
- | + | ||
- | Use the " | + | |
- | + | ||
- | A good way to make an archival copy of a database is this: | + | |
- | + | ||
- | <code bash> | + | |
- | + | ||
- | This generates a file named **ex1.dump.gz** that contains everything you need to reconstruct the database at a later time, or on another machine. To reconstruct the database, just type: | + | |
- | + | ||
- | <code bash>$ zcat ex1.dump.gz | sqlite ex2</ | + | |
- | + | ||
- | The text format used is the same as used by < | + | |
- | + | ||
- | <code bash> | + | |
- | $ createdb ex2 | + | |
- | $ echo ' | + | |
- | </ | + | |
- | + | ||
- | You can almost (but not quite) go the other way and export a < | + | |
- | + | ||
- | <code bash> | + | |
- | $ sqlite ex3 < | + | |
- | $ pg_dump -a ex2 | sqlite ex3 | + | |
- | </ | + | |
- | + | ||
- | + | ||
- | + | ||
- | ==== Other Dot Commands ==== | + | |
- | + | ||
- | The " | + | |
- | + | ||
- | < | + | |
- | sqlite> .explain | + | |
- | sqlite> explain delete from tbl1 where two< | + | |
- | addr opcode | + | |
- | ---- ------------ | + | |
- | 0 | + | |
- | 1 | + | |
- | 2 | + | |
- | 3 | + | |
- | 4 | + | |
- | 5 | + | |
- | 6 | + | |
- | 7 | + | |
- | 8 | + | |
- | 9 | + | |
- | 10 ListWrite | + | |
- | 11 Next 0 6 | + | |
- | 12 Close | + | |
- | 13 ListRewind | + | |
- | 14 Integer | + | |
- | 15 OpenWrite | + | |
- | 16 ListRead | + | |
- | 17 NotExists | + | |
- | 18 Delete | + | |
- | 19 Goto 0 16 | + | |
- | 20 ListReset | + | |
- | 21 Close | + | |
- | 22 SetCounts | + | |
- | 23 Commit | + | |
- | 24 Halt 0 0 | + | |
- | sqlite> | + | |
- | </ | + | |
- | + | ||
- | The " | + | |
- | + | ||
- | And finally, we mention the " | + | |
- | + | ||
- | ==== Using sqlite in a shell script ==== | + | |
- | + | ||
- | One way to use sqlite in a shell script is to use " | + | |
- | + | ||
- | <code bash> | + | |
- | $ sqlite ex1 ' | + | |
- | > awk ' | + | |
- | < | + | |
- | < | + | |
- | $</ | + | |
- | + | ||
- | + | ||
- | ==== Ending shell commands ==== | + | |
- | + | ||
- | SQLite commands are normally terminated by a semicolon. In the shell you can also use the word " | + | |
- | + | ||
- | ==== Compiling the sqlite program from sources ==== | + | |
- | + | ||
- | The sqlite program is built automatically when you compile the sqlite library. Just get a copy of the source tree, run " | + |
Should you find anything in the documentation that is incomplete, unclear, outdated or just plain wrong, please let us know and leave a note in the Documentation Forum.