howtos:sqliteutilitynotes

Trace:

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision		Previous revision
Next revisionBoth sides next revision
howtos:sqliteutilitynotes [2007/01/20 21:36] – created xueshenghowtos:sqliteutilitynotes [2007/01/23 00:26] – texasfett
Line 1: Line 1:
-===== sqlite: a command-line utility to administer SQLite databases =====+====== sqlite tutorial ====== 
 +a command-line program to administer SQLite databases 
  
 ==== Introduction ==== ==== Introduction ====
Line 9: Line 11:
 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. 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://www.sqlite.org/download.html | download]] links for the old **sqlite** command-line utility (listed under "Historical Binaries And Source Code"). +The SQLite web site still has some [[http://www.sqlite.org/download.html | download links]] for the old **sqlite** command-line utility (listed under "Historical Binaries And Source Code").
  
 ==== Getting Started ==== ==== Getting Started ====
Line 225: Line 226:
 $ $
 </code> </code>
 +
  
  
Line 256: Line 258:
 <code> <code>
 sqlite> .schema sqlite> .schema
-create table tbl1(one varchar(1), two smallint);+create table tbl1(one varchar(10), two smallint)
 CREATE TABLE tbl2 ( CREATE TABLE tbl2 (
-f1 varchar(30) primary key, +  f1 varchar(30) primary key, 
-f2 text, +  f2 text, 
-f3 real +  f3 real 
-);+)
 sqlite> .schema tbl2 sqlite> .schema tbl2
 CREATE TABLE tbl2 ( CREATE TABLE tbl2 (
-f1 varchar(30) primary key, +  f1 varchar(30) primary key, 
-f2 text, +  f2 text, 
-f3 real +  f3 real 
-);+)
 sqlite> sqlite>
 </code> </code>
Line 300: Line 302:
  
 <code>sqlite> .databases</code> <code>sqlite> .databases</code>
 +
 +
 +
  
  
Line 305: Line 310:
 ==== Converting An Entire Database To An ASCII Text File ==== ==== Converting An Entire Database To An ASCII Text File ====
  
-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>sqlite</b>.+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 **sqlite**.
  
 A good way to make an archival copy of a database is this: A good way to make an archival copy of a database is this:
  
-<code bash>echo '.dump' | sqlite ex1 | gzip -c >ex1.dump.gz</code>+<code bash>$ echo '.dump' | sqlite ex1 | gzip -c >ex1.dump.gz</code>
  
 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: 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:
Line 315: Line 320:
 <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 <nowiki>PostgreSQL</nowiki>, so you can also use the .dump command to export an SQLite database into a <nowiki>PostgreSQL</nowiki> database. Like this:
  
 <code bash> <code bash>
Line 322: Line 327:
 </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 <nowiki>PostgreSQL</nowiki> 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 <nowiki>PostgreSQL</nowiki> database and import that directly into SQLite.
  
 <code bash> <code bash>
 
howtos/sqliteutilitynotes.txt · Last modified: 2008/02/08 19:49 by 127.0.0.1
Old revisions

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.

Recent changes RSS feed Donate Driven by DokuWiki
The content of this wiki is protected by the GNU Fee Documentation License