Outils pour utilisateurs

Outils du site


c_sqlite3

Différences

Ci-dessous, les différences entre deux révisions de la page.

Lien vers cette vue comparative

Les deux révisions précédentes Révision précédente
Prochaine révision
Révision précédente
c_sqlite3 [2009/12/22 11:34]
thierry
c_sqlite3 [2010/09/28 19:47] (Version actuelle)
thierry
Ligne 6: Ligne 6:
   *http://​www.sqlite.org   *http://​www.sqlite.org
   *http://​souptonuts.sourceforge.net/​readme_sqlite_tutorial.html   *http://​souptonuts.sourceforge.net/​readme_sqlite_tutorial.html
 +  *http://​defindit.com/​readme_files/​sqlite.html
 +  *http://​shokhirev.com/​nikolai/​abc/​sql/​joins.html
  
 ===== limites ===== ===== limites =====
Ligne 105: Ligne 107:
  
 | :!: Pour quelques exemples qui suivent, on est dans un shell | | :!: Pour quelques exemples qui suivent, on est dans un shell |
 +
 +=== base ===
  
 Avec la date: Avec la date:
Ligne 112: Ligne 116:
   4|test encore|2.9|2009-05-14 08:34:07   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 ====
Ligne 141: Ligne 167:
 Et voila, la date a été ecrite "​automatiquement"​ lors de l'​insertion. Et voila, la date a été ecrite "​automatiquement"​ lors de l'​insertion.
  
-| :!: DATETIME('​NOW'​),​ c'est la date UTC ! Ajouter '​LOCALTIME'​ pour la date à l'​heure locale | 
  
  
 +==== plusieurs databases ====
  
-==== date ==== +=== ATTACH ​===
-Exemples:+
  
-  $ sqlite3 test.db ​"select datetime('​now'​,'​localtime'​);"​+On a parfois besoin d'​ouvrir plusieurs "​database" , notamment pour faire des jointures...
  
-  ​$ sqlite3 ​test.db "​select strftime('%m-%d-%Y','​now','​localtime'​);"​+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 exempleun numéro de carte bancaire). 
 + 
 +On veut la liste des rangées (row) qui sont dans **''​Table_A''​** (leftet **''​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...
  
-Etc...+== 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 ===== ===== Perl =====
Ligne 164: Ligne 266:
   # aptitude install libdbd-sqlite3-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
  
c_sqlite3.1261478088.txt.gz · Dernière modification: 2009/12/22 11:34 par thierry