Outils pour utilisateurs

Outils du site


c_sqlite3

sqlite

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 de main.MyTable
  • src est un alias de source.MyTable
  • src.* signifie: toutes les colonnes de src (et donc de source.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
FIXME 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)

Safe copy

c_sqlite3.txt · Dernière modification : 2010/09/28 17:47 de thierry