Table des matières
sqlite
Une base de données légère avec une API C …
Lien:
limites
Perl
Bien sur, Perl peut aussi utiliser sqlite3 .
Accès concurrents
Les bases sqlite semble accepter les accès concurrents… toutefois les ecritures sont mutuellement exclusive.
“Several computer processes or threads may access the same database without problems. Several read accesses can be satisfied in parallel. A write access can only be satisfied if no other accesses are currently being serviced, otherwise the write access fails with an error code (or can automatically be retried until a configurable timeout expires). This concurrent access situation would change when dealing with temporary tables.”
Voir aussi: “begin transaction;” et “commit;”
Installer
# aptitude install libsqlite3-dev
Pour une interface en ligne de commande cool:
# aptitude install sqlite3
commandes de base
avec sqlite3
pour commencer.
creation
Creer une base:
$ sqlite3 test.db sqlite>
Et voila.
Creer un table nommé “t1”.
sqlite> create table t1 (t1key INTEGER PRIMARY KEY, data TEXT, num double, timeEnter DATE);
Voir vite:
sqlite> .dump t1 BEGIN TRANSACTION; CREATE TABLE t1 (t1key INTEGER PRIMARY KEY, data TEXT, num double, timeEnter DATE); COMMIT;
insertion
Inserer des données:
sqlite> insert into t1 (data,num) values ('exemple1',3); sqlite> insert into t1 (data,num) values ('exemple2',6); sqlite> insert into t1 (data,num) values ('exemple3',9);
recuperation
Retourner les données:
sqlite> select * from t1; 1|exemple1|3.0| 2|exemple2|6.0| 3|exemple3|9.0|
pareil, mais avec une “limit”:
sqlite> select * from t1 limit 2; 1|exemple1|3.0| 2|exemple2|6.0|
pareil, mais avec un point de départ different:
sqlite> select * from t1 limit 2 offset 1; 2|exemple2|6.0| 3|exemple3|9.0|
trier selon une colonne…
sqlite> select * from t1 order by t1key; ...
dump
tables
liste des tables:
sqlite> .table t1
plus:
sqlite> select * from sqlite_master; table|t1|t1|2|CREATE TABLE t1 (t1key INTEGER PRIMARY KEY, data TEXT, num double, timeEnter DATE)
sqlite> .schema t1
encore plus:
sqlite> .dump BEGIN TRANSACTION; CREATE TABLE t1 (t1key INTEGER PRIMARY KEY, data TEXT, num double, timeEnter DATE); INSERT INTO "t1" VALUES(1,'exemple1',3.0,NULL); INSERT INTO "t1" VALUES(2,'exemple2',6.0,NULL); INSERT INTO "t1" VALUES(3,'exemple3',9.0,NULL); COMMIT;
date
Pour quelques exemples qui suivent, on est dans un shell |
base
Avec la date:
$ sqlite3 test.db "insert into t1 (data,num,timeEnter) values ('test encore',2.9,datetime('now','localtime'));"
$ sqlite3 test.db "select * from t1 order by num limit 1;" 4|test encore|2.9|2009-05-14 08:34:07
DATETIME('NOW'), c'est la date UTC ! Ajouter 'LOCALTIME' pour la date à l'heure locale |
simples
$ sqlite3 test.db "select datetime('now','localtime');"
$ sqlite3 test.db "select strftime('%m-%d-%Y','now','localtime');"
utc
D'aprés la doc, il est plus souple d'enregistrer l'heure UTC… comme cela:
$ sqlite3 test.db "insert into t1 (the_date) values (DATETIME('now'));
Donc the_date
est en UTC… on peut retrouver leur “local” ainsi:
$ sqlite3 test.db "select DATETIME(the_date,'localtime') from t1;"
Et voila la conversion faite.
comparaisons
En supposant des colonnes initialisés avec DATETIME('now','localtime')
, c'est facile:
$ sqlite3 test.db "DELETE FROM MyTable WHERE ( Last_Access_Date < DATETIME('now','localtime','-2 hours') ) OR ( Create_Date < DATETIME('now','localtime','-10 days') );"
Lorque qu'une colonne specifie une durée, par exemple la colonne TTL
exprime en heures, on peut faire aussi:
$ sqlite3 test.db "DELETE FROM MyTable WHERE ( Last_Access_Date < DATETIME('now','localtime','-'||TTL||' hours') );
Le ||
permet de concatener une chaine…
trigger
“trigger” == déclenchement |
A partir d'un fichier texte comme cela, avec des instructions “sqlite” bien sur:
$ cat trigger1.sql3 -- commentaire -- debut: CREATE TRIGGER insert_t1_timeEnter AFTER INSERT ON t1 BEGIN UPDATE t1 SET timeEnter = DATETIME('NOW') WHERE rowid = new.rowid; END; -- fin.
On installe le trigger:
$ sqlite3 test.db < trigger1.sql3
On insere de nouvelle données:
$ sqlite3 test.db "insert into t1 (data,num) values ('1er test de trigger',2.8);"
On regarde:
$ sqlite3 test.db "select * from t1 order by num limit 1;" 5|1er test de trigger|2.8|2009-05-14 06:42:23
Et voila, la date a été ecrite “automatiquement” lors de l'insertion.
plusieurs databases
ATTACH
On a parfois besoin d'ouvrir plusieurs “database” , notamment pour faire des jointures…
Dans ce cas, il faut utiliser la commande “ATTACH DATABASE”
Exemple:
$ sqlite3 sqlite> ATTACH DATABASE './base.sqlite' AS source; sqlite> .database seq name file --- --------------- ---------------------------------------------------------- 0 main 2 source /home/thierry/devel/myshit/./base.sqlite
- On peut en 'attacher' plusieurs.
- On peut aussi ouvrir une base principale en même temps.
$ sqlite3 mabase.sqlite
Nommage
Pour utiliser l'une autre des databases, il faut les nommés par leur Alias.
Ainsi: source.MyTable
est une table dans la database dont alias est source
.
Pour nommé la base principale, utilisé l'alias: main.
.
Exemple:
sqlite> CREATE TABLE source.MyTable ( Name TEXT, Age INTEGER );
Ou encore:
sqlite> SELECT dst.Name,src.* FROM main.MyTable dst LEFT JOIN source.MyTable src ON src.Name=dst.Name;
dst
est un alias demain.MyTable
src
est un alias desource.MyTable
src.*
signifie: toutes les colonnes desrc
(et donc desource.MyTable
)
DBI Perl
Il y a un hack a faire, pour que ca fonctionne sous perl: (dans le cas de AutoCommit=0
)
{ local $dbh->{AutoCommit} = 1; $dbh->do("ATTACH DATABASE './base.sqlite' AS example"); }
Sans “hack” autour de “AutoCommit”, l'attache n'est pas possible.
Left Join (Jointure)
Dans Table_A et Table_B
Soit une Table_A
et une Table_B
.
Soit un champ commun de fonction commune entre elle (par exemple, un numéro de carte bancaire).
On veut la liste des rangées (row) qui sont dans Table_A
(left) et Table_B
(right).
SELECT Table_A.CB, Table_B.CB FROM Table_A LEFT JOIN Table_B ON Table_A.CB=Table_B.CB ;
ATTENTION: toutes les rangées de Table_A
sont retournées !
Toutefois, lorsqu'une rangée de la Table_A
n'a pas de jointure correspondant, alors la rangée de Table_B
est entièrement NULL.
Donc, pour avoir vraiment la “jointure” de Table_A
et Table_B
, sans NULL , il faut ajouter un petit WHERE
comme ça:
SELECT Table_A.CB, Table_B.CB FROM Table_A LEFT JOIN Table_B ON Table_A.CB=Table_B.CB WHERE Table_B.CB IS NOT NULL ;
Et voila.
Dans Table_A et pas dans Table_B
Maintenant, on veut les rangées qui sont dans la Table_A
, et PAS dans la Table_B
…
Il suffit d'inverser le “WHERE”…
SELECT Table_A.CB FROM Table_A LEFT JOIN Table_B ON Table_A.CB=Table_B.CB WHERE Table_B.CB IS NULL ;
plusieurs databases
Lorsqu'on travaille avec plusieurs databases (grâce à la commande ATTACH DATABASE
), et que les tables ont les mêmes noms…
Ainsi, soit 2 databases:
- main
- source
Leurs tables sont identiques, et particulièrement une table commune nommé MyTable
…
On peut créer le même genre de jointure que ci-dessus… mais faut faire attention au nommage…
Sans alias
SELECT main.MyTable.CB, source.MyTable.CB FROM main.MyTable LEFT JOIN source.MyTable ON main.MyTable.CB=source.MyTable.CB WHERE source.MyTable.CB IS NOT NULL ;
Avec alias
On peut créer le même genre de jointure que ci-dessus, mais avec plein d'aliases !
Exemple:
SELECT dst.CB, src.CB FROM main.MyTable dst LEFT JOIN source.MyTable src ON dst.CB=src.CB WHERE src.CB IS NOT NULL ;
C'est un peu plus court grâce aux alias créés dans le FROM …
Perl
Lien: http://search.cpan.org/dist/DBD-SQLite/lib/DBD/SQLite.pm
( # aptitude install libdbi-perl )
# aptitude install libdbd-sqlite3-perl
ca me parait bien court ! |
tips
DBI et SQlite super lent ?
Trés instructif: http://defindit.com/readme_files/sqlite.html
J'ai un scripte perl qui “insert” un tas de données en un peu plus de 2 minutes…
Ce qui me semble extraordinairement rapide, par rapport à un DomU (machine virtuelle dans un Dom0 Xen), qui avec le même scripte et les mêmes données, met plus de 15 minutes !!!!
A la recherche du problème, je découvre que:
AutoCommit⇒1 est super lent ! |
Aprés avoir forcé “AutoCommit⇒0” et puis avoir parsemé (judicieusement(*)) des appels a la méthode “commit()” … GAIN: 900 fois + rapide
PC | AutoCommit⇒1 | AutoCommit⇒0 et methode commit() |
mon mien | 2 minutes | 11 secondes |
un DomU | 15 minutes | 15 secondes |
(*) Entre autres: Appel de la method “commit()” tous les 250 “insert into” … (a voir au cas par cas)