| Both sides previous revisionPrevious revisionNext revision | Previous revision |
| howtos:sqlite_tutorial [2008/02/15 01:35] – xuesheng | howtos:sqlite_tutorial [2013/10/25 14:52] (current) – external edit 127.0.0.1 |
|---|
| ====== 'sqlite' Tutorial ====== | ====== 'sqlite' Tutorial for POPFile 0.21.0 to 1.0.1 ====== |
| | |
| **'sqlite'** is a command-line program to administer SQLite databases. The POPFile-compatible versions of this utility are called **''sqlite.exe''** or **''sqlite''** depending upon the platform. For simplicity this tutorial refers to the utility as **''sqlite''**. | |
| |
| | **'sqlite'** is a command-line program to administer SQLite 2.x databases as used by POPFile 0.21.0 to 1.0.1. This utility is called **''sqlite.exe''** or **''sqlite''** depending upon the platform. For simplicity this tutorial refers to the utility as **''sqlite''**. |
| |
| | ^ POPFile 1.1.0 (or later) uses the SQLite 3.x format for its database which requires a different command-line program (**''sqlite3.exe''** or **''sqlite3''**). There is a [[http://www.sqlite.org/sqlite.html | tutorial]] showing how to use this SQLite 3.x format utility on the SQLite website.^ |
| |
| |
| ==== Introduction ==== | ==== Introduction ==== |
| |
| POPFile uses an SQLite database to hold essential information, such as the corpus used to classify the messages it handles. At present POPFile only works with SQLite 2.x libraries (it does not work with SQLite 3.x libraries). | POPFile uses an SQLite database to hold essential information, such as the corpus used to classify the messages it handles. POPFile versions 0.21.0 to 1.0.1 only work with SQLite 2.x libraries (none of these versions can work with SQLite 3.x libraries). |
| |
| 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 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 only seems to document the **''sqlite3''** utility which is not compatible with the SQLite databases created by POPFile 0.21.0 to 1.0.1 (POPFile 1.1.0, or later, uses SQLite 3.x). 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's [[http://www.sqlite.org/download.html | download]] page no longer has links for the old **''sqlite''** command-line utility (Windows and Linux binary versions used to be listed under "Historical Binaries And Source Code") but the SQLite 2.8 source code is still available. (POPFile's installer includes a SQLite 2.x version of the **''sqlite.exe''** command-line utility.) | |
| |
| | The SQLite web site's [[http://www.sqlite.org/download.html | download]] page no longer has links for the old **''sqlite''** command-line utility (Windows and Linux binary versions used to be listed under "Historical Binaries And Source Code") but the SQLite 2.8 source code is still available. (POPFile's Windows installer includes a SQLite 2.x version of the **''sqlite.exe''** command-line utility.) |
| |
| ==== Getting Started ==== | ==== Getting Started ==== |
| $ | $ |
| </code> | </code> |
| | |
| |
| |
| seq name file | seq name file |
| |
| --- --------------- ---------------------------------------------------------- | --- --------------- --------------------------------- |
| |
| 0 main C:\TEMP\ex1.db | 0 main C:\TEMP\ex1.db |
| <code bash>$ zcat ex1.dump.gz | sqlite ex2</code> | <code bash>$ zcat ex1.dump.gz | sqlite ex2</code> |
| |
| The text format used is the same as used by PostgreSQL, so you can also use the **''.dump''** command to export an SQLite database into a PostgreSQL database. Like this: | The text format used is the same as used by %%PostgreSQL%%, so you can also use the **''.dump''** command to export an SQLite database into a %%PostgreSQL%% database. Like this: |
| |
| <code bash> | <code bash> |
| </code> | </code> |
| |
| You can almost (but not quite) go the other way and export a PostgreSQL database into SQLite using the **pg_dump** utility. Unfortunately, when **pg_dump** writes the database schema information, it uses some SQL syntax that SQLite does not understand. So you cannot pipe the output of **pg_dump** directly into **''sqlite''**. But if you can recreate the schema separately, you can use **pg_dump** with the **-a** option to list just the data of a PostgreSQL database and import that directly into SQLite. | You can almost (but not quite) go the other way and export a %%PostgreSQL%% database into SQLite using the **pg_dump** utility. Unfortunately, when **pg_dump** writes the database schema information, it uses some SQL syntax that SQLite does not understand. So you cannot pipe the output of **pg_dump** directly into **''sqlite''**. But if you can recreate the schema separately, you can use **pg_dump** with the **-a** option to list just the data of a %%PostgreSQL%% database and import that directly into SQLite. |
| |
| <code bash> | <code bash> |
| $ pg_dump -a ex2 | sqlite ex3 | $ pg_dump -a ex2 | sqlite ex3 |
| </code> | </code> |
| | |
| |
| |
| sqlite> explain delete from tbl1 where two<20; | sqlite> explain delete from tbl1 where two<20; |
| addr opcode p1 p2 p3 | addr opcode p1 p2 p3 |
| ---- ------------ ---------- ---------- ----------------------------------- | ---- ------------ ---------- ---------- ---------- |
| 0 Transaction 0 0 | 0 Transaction 0 0 |
| 1 VerifyCookie 0 180 | 1 VerifyCookie 0 180 |
