Pour créer un fichier de base de données, (dans une console) lancez la commande “sqlite3” suivit du nom de fichier. Par exemple, pour créer la base de données nommée “test.db”, lancez la commande sqlite3 comme ceci :

$ sqlite3 test.db

Une fois lancer, vous aurez les messages suivants au numéro de version prêt :

SQLite version 3.0.8
Enter ".help for instructions
sqlite>

Pour quitter correctement et revenir au prompt bash tapez ”.quit” ou ”.exit” ou encore (c'est la manière la plus courte) ”.q”
À la fermeture, la base de données “test.db” est créée si elle n'existe pas encore.
Il est intéressant de savoir que vous n'êtes pas obligé d'utiliser l'environnement SQLite pour exécuter des commandes ce qui permet de faire des scripts bash et/ou d'utiliser ssh.
Voici un exemple d'une table simple sans utiliser l'environnement SQLite :

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

Après que la table t1 ait été créée, nous pouvons insérer des données comme suit :

$ 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);"

Ici, nous spécifions les champs que nous voulons renseigner avec ”(data,num)”

Comme prévu, lancer la commande “select” renvoie les données de la table. Notez que la première clef “t1key” s'auto-incrémente; Il faut remarquer qu'il n'y a pas de valeurs prédéfinies pour timeEnter. Pour remplir les champs de “timeEnter” avec un temps, un mise à jour avec un déclenchement est nécessaire. Il est important de noter qu'il ne faut pas utiliser l'abréviation “INT” quant on travaille avec les PRIMARY KEY. Il est obligatoire d'utiliser “INTERGER” en lieu et place.

$ sqlite3 test.db  "select * from t1 limit 2";

donne en sortie :

1|This is sample data|3|
2|More sample data|6|

Dans la déclaration ci-dessus, la clause de limite est utilisée, et seules deux lignes sont affichées. Pour une référence rapide à la syntaxe SQL sur les déclations disponible avec SQLite, se reporter à la page de syntaxe. Il y a une option excentrée pour la clause de limite. Par exemple, la troisième rangée est égale à ce qui suit : “limit 1 offset 2”.

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

La commande ”.table” montre les noms de la table. Pour une liste plus complète des tables, des déclenchements, et des index créés dans la base de données, il faut questionner la table principale “sqlite_master”, comme montré ci-dessous.

$ 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)

Toutes les informations SQL ainsi que les données insérées dans la base de données peuvent-être extraite avec la commande ”.dump”. Vous pouvez aussi regarder le fichier ”~/.sqlite_history” qui vous donnera les mêmes renseignement :

$ 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;

La commande ”.dump” peut-être filtré avec des tubes vers une autre base de données. Ci-dessous, la table t1 est changer en t2 avec la commande sed, le tout est envoyé dans la base de données nommé test2.db.

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

L'insertion d'un déclenchement est créé comme ci-dessous dans un fichier “trigger1”. Les coordonnées universelle du temps (UTC) seront entrées dans le champ “timeEnter” et le déclenchement sera inséré après qu'une données sera insérée dans la 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;
/-- ********************************************************************
/-- Il faut enlever les / en début de ligne. Ils ont été rajouté pour palier à une incompatibilité.
/-- Avec SQLite, les commentaires commencent par --

La spécification “AFTER” dans ”…insert_t1_timeEnter AFTER …” est nécessaire. Sans cette clef, le champ d'identification ne sera pas généré. C'est une source d'erreurs courantes avec les déclenchements car “AFTER” n'est pas la clef par défaut donc elle doit-être spécifiée. Si votre déclenchement dépend de données récemment créées et de n'importe quel champ de la ligne créée (ce qui est le cas dans cet exemple, car on veux l'identifiant de ligne) la clef “AFTER” est obligatoire. Sinon le déclenchements est un déclenchement “BEFORE” et il sera exécuté avant que l'identifiant de ligne ou d'autre données intéressantes soit entrées dans le champs.

Les commentaires sont précédés par ”–”. Mettez les lignes précédentes dans le fichier “trigger1”. Ensuite, nous pouvons aisément l'exécuter comme suit :

$ sqlite3 test.db < trigger1

Maintenant, essayons d'entrée une nouvelle valeur dans la base et regardons l'heure et le champ “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

La dernière donnée à la clef timeEnter renseignée automatiquement avec l'heure universelle (UTC). Si vous souhaitez l'heure locale, vous devez utilier “datetime('now', 'localtime')”. Pour plus d'information sur le sujet, reportez-vous à la fin de cette section.

Pour les exemples qui suivent, la table “exam” et la base de données “examScript” vont être utilisées. La table et les déclenchements sont défini comme ci-dessous. Comme pour le déclenchement précédent, l'heure UTC sera utilisée.

/-- *******************************************************************
/--  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;
/-- *******************************************************************
/-- *******************************************************************

Voici un exemple d'utilisation :

$ 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

Comme vous pouvez le voir, la PRIMARY KEY et le temps UTC ont été mis à jour correctement.

Le script suivant crée la table examlog et trois déclenchements (pdate_examlog, insert_examlog, and delete_examlog) pour enregistrer les mises à jour, les insertions et les suppressions faites dans la table exam. En d'autres termes, lorsqu'il y a un changement de fait dans la table exam, les changements seront enregistrés dans la table examlog, incluant les anciennes données ainsi que les nouvelles. Si vous êtes familiarisé avec MySQL, le fonctionnement de cet enregistrement de table est similaire à MySQL's binlog. Regardez les tuyaux 2, 24 et 25 si vous voulez plus d'informations sur les fichiers d'enregistrements de MySQL.

/-- *******************************************************************
/--  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;
/-- *******************************************************************
/-- *******************************************************************

Après que le script ci-dessus ait été créé dans le fichier examLOG, vous pouvez exécuter les commandes dans sqlite3 comme montré ci-dessous. Vous pouvez voir ci-dessous un enregistrement d'insertions et une mise à jour pour tester ces déclenchements nouvellement créés.

$ 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"

Maintenant en faisant la sélection du rapport ci-dessous, vous verrez que examLOG contient une entrée pour les rapports d'insertions avec 2 mises à jour. Bien que nous n'ayons fait qu'une mise à jour via la ligne de commande, le déclenchement “insert_exam_timeEnter” a exécuté une mise à jour pour le champ timeEnter; C'est le déclencheur défini dans “examScript”. Dans la seconde mise à jour nous pouvons voir que le score à changé. Le déclencheur fonctionne. Quelque soit le changement fait sur la table, soit par intéraction de l'utilisateur ou soit un autre déclencheur, cela est enregistré dans examlog.

$ sqlite3 examdatabase "select * from examlog"

1|2||Bob||Anderson||2||80||INSERT|||2004-10-02 15:33:16
2|2|2|Bob|Bob|Anderson|Anderson|2|2|80|80|UPDATE||2004-10-02 15:33:16|2004-10-02 15:33:16
3|2|2|Bob|Bob|Anderson|Anderson|2|2|82|80|UPDATE|2004-10-02 15:33:16|2004-10-02 15:33:26|2004-10-02 15:33:26

Encore une fois, faites particulièrement attention au mot-clef «AFTER». Rappelez vous que par défaut, les déclencheurs sont de type «BEFORE», donc vous devez imposer AFTER pour vous assurer que toutes les nouvelles valeurs seront disponible si vos déclencheurs ont besoin de travailler avec de nouvelles données.

Notez que “select DATETIME('NOW')” retourne le temps UTC ou les Coordonnées Universelles du temps mais “select DATETIME('now', 'localtime')” retourne l'heure courante.

sqlite> select datetime('now');
2004-10-18 23:32:34

sqlite> select datetime('now','localtime');
2004-10-18 19:32:46

Il y a un avantage à insérer l'heure UTC comme nous l'avons fait avec les déclencheurs auparavant car elle peut-être convertie facilement à l'heure locale après que l'heure UTC ait été rentrée dans la table. Regardez la commande suivante. Par insertion de l'heure UTC, vous évitez les problèmes quand travaillant avec de multiples base de données qui pourraient ne pas partager le même fuseau horaire ou paramètre dst. En commençant avec l'heure UTC vous pouvez toujours obtenir l'heure locale (Référence : Travailler avec le temps)

CONVERTING TO LOCALTIME:
sqlite> select datetime(timeEnter,'localtime') from exam;

Si vous regardez dans le fichier source ”./src/date.c” vous verrez que datetime prend d'autres options. Par exemple pour avoir l'heure locale (localtime), plus 3.5 secondes, plus 10 minutes, vous devrez exécuter les commandes suivantes:

sqlite> select datetime('now','localtime','+3.5 seconds','+10 minutes');
2004-11-07 15:42:26

Il est aussi possible d'obtenir les jours de la semaine (weekday) où 0=dimanche, 1=lundi, 2=mardi, …, 6=samedi.

sqlite> select datetime('now','localtime','+3.5 seconds','weekday 2');
2004-11-09 15:36:51

La liste complète des options, ou modificateurs comme ils sont appelés dans ce fichier, sont comme suit:

NNN days
NNN hours
NNN minutes
NNN.NNNN seconds
NNN months
NNN years
start of month
start of year
start of week
start of day
weekday N
unixepoch
localtime
utc

En plus, il y a la fonction “strftime”, laquelle prend une chaine du temps (timestring), et la convertie dans un format spécifié, avec les modificateurs. Voici un format pour cette fonction :

strftime( FORMAT, TIMESTRING, MOD, MOD, ...)

Return a string described by FORMAT.  Conversions as follows:

%d  day of month
%f  ** fractional seconds  SS.SSS
%H  hour 00-24
%j  day of year 000-366
%J  ** Julian day number
%m  month 01-12
%M  minute 00-59
%s  seconds since 1970-01-01
%S  seconds 00-59
%w  day of week 0-6  sunday==0
%W  week of year 00-53
%Y  year 0000-9999

Voici un exemple :

sqlite> select strftime("%m-%d-%Y %H:%M:%S %s %w %W",'now','localtime');
11-07-2004 16:23:15 1099844595 0 44

C'est un concept très puissant. Comme vous l'avez vu, SQLite3 travaille avec des fichiers locales de bases de données. Dans la base de données locale, plusieurs tables peuvent être créées. Cette section examinera une technique avec les mêmes dispositions de champs qu'il existe dans des bases de données séparées dans une seule table virtuelle. Dans cette table virtuelle, vous verrez comment la commande “select” peut-être performante. Il n'y a pas de frais dans la copie ou dans le déplacement de données. Aucune données n'a besoin d'être copier ou déplacé. C'est la situation idéale quand vous travaillez sur une table énorme. Supposez que l'ordinateur sur lequel vous travaillez enregistre les ports scannés par Snort vers un fichier local sqlite3. Pourvu que vous ayez les autorisations sur les fichiers individuels des bases de données, via les montages NFS ou samba, vous pouvez combiner virtuellement les tables de tous vos ordinateurs dans une seule table virtuelle pour exécuter des des questions de base de données dans un effort d'identifier les modèles globaux de l'attaque contre votre réseau.

Cet exemple sera fait avec la base “examdatabase” car nous avons encore les scripts qui ont été utilisés pour la table exam. Nous pouvons aisément créer une nouvelle base de données “examdatabase2” avec une nouvelle table exam en exécutant le script suivant dans un environnement bash:

$ sqlite3 examdatabase2 < examScript
$ sqlite3 examdatabase2 < examLOG
$ sqlite3 examdatabase2 "insert into exam (ln,fn,exam,score) values ('Carter','Sue',1,89);
                         insert into exam (ln,fn,exam,score) values ('Carter','Sue',2,100);"
$ sqlite3 examdatabase2 "select * from exam"
1|Sue|Carter|1|89|2004-10-02 16:04:12
2|Sue|Carter|2|100|2004-10-02 16:04:12

Pour combiner les deux fichiers de base de données, utilisez la commande attach. L'alias pour “examdatabase” sera e1 et e2 pour “examdatabase2. Les noms plus courts deviendront plus maniable quand les tables seront rassemblée avec la close d'union (une commande standard au langage SQL).

Après la commande “attach” soit exécutée, la commande .database peut-être utilisée pour voir l'emplacement des files de bases de données. L'emplacement suit l'alias. Regardez l'exemple suivant:

$ sqlite3
SQLite version 3.0.8
Enter ".help" for instructions
sqlite> attach database 'examdatabase' as e1;
sqlite> attach database 'examdatabase2' as e2;
sqlite> .database
seq  name             file
---  ---------------  ----------------------------------------------------------
0    main
2    e1               /work/cpearls/src/posted_on_sf/sqlite_examples/sqlite_exam
3    e2               /work/cpearls/src/posted_on_sf/sqlite_examples/sqlite_exam
sqlite>

Pour sélectionner toutes les données des deux tables, utilisez l'union des deux rapports choisis comme montré précédemment. Notez qu'en ajoutant “e1” et “e2” dans leur sélection respective, il est possible d'identifier d'où viennent les enregistrements.

sqlite> select 'e1',* from e1.exam union select 'e2',* from e2.exam;
e1|1|Bob|Anderson|1|75|2004-10-02 15:25:00
e1|2|Bob|Anderson|2|82|2004-10-02 15:33:16
e2|1|Sue|Carter|1|89|2004-10-02 16:04:12
e2|2|Sue|Carter|2|100|2004-10-02 16:04:12

Pour récapituler: Une question a été envoyé sur les deux tables séparée. L'union crée la table virtuelle. La syntaxe de la commande select est comme suit: SELECT <expression> FROM <TABLE>. Pour l'option de table nous avons employé la chaine complète ”(select 'e1' as db,* from e1.exam union select 'e2' as db,* from e2.exam)” laquelle est notre table virtuelle.

Voici un exemple de questionnement utilisé sur cette table virtuelle. Supposez que vous vouliez les scores maximums de chaque bases de données.

sqlite> select  exam,max(score) from
       (select 'e1' as db,* from e1.exam union select 'e2' as db,* from e2.exam)
         group by exam;
1|89
2|100

Aucun problème. Vous obtenez le score maximum de chaque exam, mais qui fait moins ? Trouver le dernier nom (ln : last name) et le premier nom (fn : first name) peut-être dangereux. Si vous avez “ln” et “fn” dans la première partie de la commande select vous aurez une réponse erronée.

sqlite> select  exam,max(score),ln,fn from
      (select 'e1' as db,* from e1.exam union select 'e2' as db,* from e2.exam)
        group by exam;
   ** THIS IS INCORRECT; it should be Carter|Sue. **
1|89|Anderson|Bob
2|100|Anderson|Bob

“Anderson”, “Bob” s'avère justement être le nom qui est tombé dans ce rapport. Ce n'est pas la bonne réponse. Si, par chance, vous obtenez la bonne réponse en exécutant cette requête, c'est parce que vous avez entré les noms dans un ordre différent. Si c'est le cas, exécutez la requête ci-dessous, qui prend le minimum (score) et obtient une erreur sur un de ces exemples.

Ici, le minimum (score) est demandé. Par chance, à cause de l'ordre dans lequel les données on été entrée dans la table, la bonne réponse est affichée.

sqlite> select  exam,min(score),ln,fn from
       (select 'e1' as db,* from e1.exam union select 'e2' as db,* from e2.exam)
         group by exam;
   ** correct answer -- just chance **
1|75|Anderson|Bob
2|82|Anderson|Bob

Clairement, il faut une meilleure manière pour trouver qui a obtenu le maximum et le minimum pour chaque examen. Voici la bonne commande SQL qui fournit toujours la bonne réponse.

sqlite> select db,ln,fn,exam,score from
        (select 'e1' as db,* from e1.exam union select 'e2' as db,* from e2.exam)
      where
          (
            score=(
            select max(score) from
             (select 'e1' as db,* from e1.exam union select 'e2' as db,* from e2.exam)
              where exam=1
                 )
            and exam = 1
            )
            OR
            (
            score=(
             select max(score) from
              (select 'e1' as db,* from e1.exam union select 'e2' as db,* from e2.exam)
              where exam=2
                 )
            and exam = 2
              )  ;
e2|Carter|Sue|1|89
e2|Carter|Sue|2|100

Ou il peut être fait comme deux rapports indépendants comme suit :

sqlite> select db,ln,fn,exam,score from
      (select 'e1' as db,* from e1.exam union select 'e2' as db,* from e2.exam)
       where exam=1 order by score desc limit 1;
e2|Carter|Sue|1|89

sqlite> select db,ln,fn,exam,score from
       (select 'e1' as db,* from e1.exam union select 'e2' as db,* from e2.exam)
       where exam=2 order by score desc limit 1;
e2|Carter|Sue|2|100

— chipster 01/01/2007 11:59