SQLite

Getting Started

Common Commands

To create a database file, run the command "sqlite3", followed by thedatabase name. For example, to create the database "test.db", run the sqlite3 command as follows:

     $ sqlite3 test.db
     SQLite version 3.0.8
     Enter ".help" for instructions
     sqlite> .quit
     $

The database file test.db will be created, if it does not already exist.Running this command will leave you in the sqlite3 environment. Thereare three ways to safely exit this environment: .q, .quit, and .exit.

You do not have to enter the sqlite3 interactive environment. Instead, you could perform all commands at the shell prompt, which is ideal whenrunning bash scripts and commands in an ssh string. Here is an example of how you would create a simple table from the command prompt:

     $ sqlite3 test.db  "create table t1 (t1key INTEGER
                  PRIMARY KEY,data TEXT,num double,timeEnter DATE);"

After table t1 has been created, data can be inserted as follows:

     $ sqlite3 test.db  "insert into t1 (data,num) values ('This is sample data',3);"
     $ sqlite3 test.db  "insert into t1 (data,num) values ('More sample data',6);"
     $ sqlite3 test.db  "insert into t1 (data,num) values ('And a little more',9);"

As expected, doing a select returns the data in the table. Note that the primary key "t1key" auto increments; however, there are no default values for timeEnter. To populate the timeEnter field with the time, an update trigger is needed. Note that you should not use the abbreviation "INT" when working with the PRIMARY KEY. You must use "INTEGER" for the primary key to update.

     $ sqlite3 test.db  "select * from t1 limit 2";
     1|This is sample data|3|
     2|More sample data|6|

In the statement above, the limit clause is used, and only two rows are displayed. For a quick reference of SQL syntax statements available with SQLite, see the syntax page. There is an offset option for the limit clause. For instance, the third row is equal to the following: "limit 1 offset 2".

     $ sqlite3 test.db "select * from t1 order by t1key limit 1 offset 2";
     3|And a little more|9|

The ".table" command shows the table names. For a more comprehensive list of tables, triggers, and indexes created in the database, query the master table "sqlite_master", as shown below.

     $ sqlite3 test.db ".table"
     t1

     $ sqlite3 test.db "select * from sqlite_master"
     table|t1|t1|2|CREATE TABLE t1 (t1key INTEGER
                  PRIMARY KEY,data TEXT,num double,timeEnter DATE)

All SQL information and data inserted into a database can be extracted with the ".dump" command. Also, you might want to look for the "~/.sqlite_history" file.



     $ sqlite3 test.db ".dump"
     BEGIN TRANSACTION;
     CREATE TABLE t1 (t1key INTEGER
                  PRIMARY KEY,data TEXT,num double,timeEnter DATE);
     INSERT INTO "t1" VALUES(1, 'This is sample data', 3, NULL);
     INSERT INTO "t1" VALUES(2, 'More sample data', 6, NULL);
     INSERT INTO "t1" VALUES(3, 'And a little more', 9, NULL);
     COMMIT;

The contents of the ".dump" can be filtered and piped to another database. Below, table t1 is changed to t2 with the sed command, and it is piped into the test2.db database.

      $ sqlite3 test.db ".dump"|sed -e s/t1/t2/|sqlite3 test2.db

Perl and sqlite3

To use Perl with sqlite3, DBI and DBD::SQLite must be installed. To install the packages from CPAN, use the following commands.

   # perl -MCPAN -e shell
   cpan> install DBI
   cpan> install DBD::SQLite

The following program will create a database and enter records:

   #!/usr/bin/perl

   use DBI;

   $dbh = DBI->connect( "dbi:SQLite:data.dbl" ) || die "Cannot connect: $DBI::errstr";

   $dbh->do( "CREATE TABLE authors ( lastname, firstname )" );
   $dbh->do( "INSERT INTO authors VALUES ( 'Conway', 'Damian' ) " );
   $dbh->do( "INSERT INTO authors VALUES ( 'Booch', 'Grady' ) " );
   $dbh->do( "CREATE TABLE books ( title, author )" );
   $dbh->do( "INSERT INTO books VALUES ( 'Object Oriented Perl',
                                             'Conway' ) " );
   $dbh->do( "INSERT INTO books VALUES ( 'Object-Oriented Analysis and Design',
                                             'Booch' ) ");
   $dbh->do( "INSERT INTO books VALUES ( 'Object Solutions', 'Booch' ) " );


   $res = $dbh->selectall_arrayref( q( SELECT a.lastname, a.firstname, b.title
                                           FROM books b, authors a
                                           WHERE b.title like '%Orient%'
                                       AND a.lastname = b.author ) );

   foreach( @$res ) {
    foreach $i (0..$#$_) {
       print "$_->[$i] "
       }
    print "\n";

   }

   $dbh->disconnect;

Source: http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html


Links: