SQLite: Tutorial

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

Copyright (c) 2004 by Mike Chirico mchirico@users.sourceforge.net
This material may be distributed only subject to the terms and conditions set forth in the Open Publication License v1.0, 8 June 1999 or later.
The latest update of this document .

Updated: Thu Jan 11 17:35:19 EST 2007

Tulisan ini mengeksplorasi kekuatan dan kesederhanaan sqlite3, mulai dengan commands biasa dan triggers. Tulisan ini akan membahas pernyataan penggabungan dengan operasi union, pendahuluan cara untuk membuat banyak tabel, di database yang berbeda, yang akan digabungkan sebagai satu virtual tabel, tanpa perlu mengcopy dan memindahkan data.

Selanjutnya, akan di demonstrasikan fungsi sign sederhana dan trik penggunaan fungsi ini di perintah select SQL untuk mengatasi query yang kompleks dengan sekali pass ke data yang ada, setelah menggunakan case matematik sederhana bagaimana fungsi sign mendefinisikan nilai absolut dan kondisi IF.

Meskipun fungsi sign tidak ada di sqlite3, pada dasarnya sangat mudah untuk membuatnya di file "/src/func.c" sehingga fungsi tersebut ada secara permanen untuk semua aplikasi sqlite. Umumnya, fungsi user dibuat di C, Perl, atau C++ yang di dokumentasikan di tulisan ini. sqlite3 mempunyai kemampuan untuk menyimpan "blob", binary data. Contoh program "eatblob.c", membaca file binary apapun besarnya ke dalam memory dan menyimpan data di tempat yang di tentukan oleh user.

Semua contoh dapat di temukan di

http://prdownloads.sourceforge.net/souptonuts/sqlite_examples.tar.gz?download

dan sangat di sarankan untuk men-download contoh ini sambil kita membaca tulisan ini.

Tutorial ini dibuat menggunakan sqlite3 versi 3.0.8.

Common Commands

To create a database file, run the command "sqlite3", followed by the database 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. There are 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 when running 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"

PRAGMA foreign_keys=OFF;
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.0,NULL);
INSERT INTO t1 VALUES(2,'More sample data',6.0,NULL);
INSERT INTO t1 VALUES(3,'And a little more',9.0,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

Triggers

An insert trigger is created below in the file "trigger1". The Coordinated Universal Time (UTC) will be entered into the field "timeEnter", and this trigger will fire after a row has been inserted into the table t1.

    -- ********************************************************************
    --   Creating a trigger for timeEnter
    --     Run as follows:
    --            $ sqlite3 test.db < trigger1
    -- ********************************************************************
    CREATE TRIGGER insert_t1_timeEnter AFTER  INSERT ON t1
    BEGIN
     UPDATE t1 SET timeEnter = DATETIME('NOW')  WHERE rowid = new.rowid;
    END;
    -- ********************************************************************

The AFTER specification in ..."insert_t1_timeEnter AFTER..." is necessary. Without the AFTER keyword, the rowid would not have been generated. This is a common source of errors with triggers, since AFTER is not the default, so it must be specified. If your trigger depends on newly-created data in any of the fields from the created row (which was the case in this example, since we need the rowid), the AFTER specification is needed. Otherwise, the trigger is a BEFORE trigger, and will fire before rowid or other pertinent data is entered into the field.

Comments are preceded by "--". If this script were created in the file "trigger1", you could easily execute it as follows.

    $ sqlite3 test.db < trigger1

Now try entering a new record as before, and you should see the time in the field timeEnter.

    $ sqlite3 test.db  "insert into t1 (data,num) values ('First entry with timeEnter',19);"

    $ sqlite3 test.db "select * from t1";
    1|This is sample data|3|
    2|More sample data|6|
    3|And a little more|9|
    4|First entry with timeEnter|19|2004-10-02 15:12:19

The last value has timeEnter filled automatically with Coordinated Universal Time, or UTC. If you want localtime, use select datetime('now','localtime'). See the note at the end of this section regarding UTC and localtime.

For the examples that follow, the table "exam" and the database "examScript" will be used. The table and trigger are defined below. Just like the trigger above, UTC time will be used.

-- ******************************************************************* -- examScript: Script for creating exam table -- Usage: -- $ sqlite3 examdatabase < examScript -- -- Note: The trigger insert_exam_timeEnter -- updates timeEnter in exam -- ******************************************************************* -- ******************************************************************* CREATE TABLE exam (ekey INTEGER PRIMARY KEY, fn VARCHAR(15),

                  ln        VARCHAR(30),
                  exam      INTEGER,
                  score     DOUBLE,
                  timeEnter DATE);

CREATE TRIGGER insert_exam_timeEnter AFTER INSERT ON exam BEGIN

UPDATE exam SET timeEnter = DATETIME('NOW')

        WHERE rowid = new.rowid;

END; -- ******************************************************************* -- *******************************************************************

Here's an example usage:

   $ sqlite3 examdatabase < examScript
   $ sqlite3 examdatabase "insert into exam (ln,fn,exam,score)
          values ('Anderson','Bob',1,75)"

   $ sqlite3 examdatabase "select * from exam"

   1|Bob|Anderson|1|75|2004-10-02 15:25:00

As you can see, the PRIMARY KEY and current UTC time have been updated correctly. Logging All Inserts, Updates, and Deletes

The script below creates the table examlog and three triggers (update_examlog, insert_examlog, and delete_examlog) to record updates, inserts, and deletes made to the exam table. In other words, whenever a change is made to the exam table, the changes will be recorded in the examlog table, including the old value and the new value. If you are familiar with MySQL, the functionality of this log table is similar to MySQL's binlog. See Tips 2, 24, and 25 if you would like more information on MySQL's log file.

-- ******************************************************************* -- examLog: Script for creating log table and related triggers -- Usage: -- $ sqlite3 examdatabase < examLOG -- -- -- ******************************************************************* -- ******************************************************************* CREATE TABLE examlog (lkey INTEGER PRIMARY KEY,

                 ekey INTEGER,
                 ekeyOLD INTEGER,
                 fnNEW   VARCHAR(15),
                 fnOLD   VARCHAR(15),
                 lnNEW   VARCHAR(30),
                 lnOLD   VARCHAR(30),
                 examNEW INTEGER,
                 examOLD INTEGER,
                 scoreNEW DOUBLE,
                 scoreOLD DOUBLE,
                 sqlAction VARCHAR(15),
                 examtimeEnter    DATE,
                 examtimeUpdate   DATE,
                 timeEnter        DATE);

-- Create an update trigger CREATE TRIGGER update_examlog AFTER UPDATE ON exam BEGIN

 INSERT INTO examlog  (ekey,ekeyOLD,fnOLD,fnNEW,lnOLD,
                       lnNEW,examOLD,examNEW,scoreOLD,
                       scoreNEW,sqlAction,examtimeEnter,
                       examtimeUpdate,timeEnter)

         values (new.ekey,old.ekey,old.fn,new.fn,old.ln,
                 new.ln,old.exam, new.exam,old.score,
                 new.score, 'UPDATE',old.timeEnter,
                 DATETIME('NOW'),DATETIME('NOW') );

END; -- -- Also create an insert trigger -- NOTE AFTER keyword ------v CREATE TRIGGER insert_examlog AFTER INSERT ON exam BEGIN INSERT INTO examlog (ekey,fnNEW,lnNEW,examNEW,scoreNEW,

                     sqlAction,examtimeEnter,timeEnter)

         values (new.ekey,new.fn,new.ln,new.exam,new.score,
                 'INSERT',new.timeEnter,DATETIME('NOW') );

END;

-- Also create a DELETE trigger CREATE TRIGGER delete_examlog DELETE ON exam BEGIN

INSERT INTO examlog (ekey,fnOLD,lnNEW,examOLD,scoreOLD,

                     sqlAction,timeEnter)

         values (old.ekey,old.fn,old.ln,old.exam,old.score,
                 'DELETE',DATETIME('NOW') );

END; -- ******************************************************************* -- *******************************************************************

Since the script above has been created in the file examLOG, you can execute the commands in sqlite3 as shown below. Also shown below is a record insert, and an update to test these newly-created triggers.

 $ sqlite3 examdatabase < examLOG

 $ sqlite3 examdatabase "insert into exam
                           (ln,fn,exam,score)
                         values
                           ('Anderson','Bob',2,80)"

 $ sqlite3 examdatabase "update exam set score=82
                            where
                          ln='Anderson' and fn='Bob' and exam=2"

Now, by doing the select statement below, you will see that examlog contains an entry for the insert statement, plus two updates. Although we only did one update on the commandline, the trigger "insert_exam_t