tj - Wiki

Cette page est en lecture seule. Vous pouvez afficher le texte source, mais ne pourrez pas le modifier. Contactez votre administrateur si vous pensez qu'il s'agit d'une erreur.
====== sqlite ======
Une base de données légère avec une API C ...

Lien:
  *http://wiki.archlinux.fr/howto/sqlite
  *http://www.sqlite.org
  *http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html
  *http://defindit.com/readme_files/sqlite.html
  *http://shokhirev.com/nikolai/abc/sql/joins.html

===== 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 ====

Lien: http://stuvel.eu/archive/55/safely-copy-a-sqlite-database