SQLite - SpatiaLite: le pourquoi du comment

Avec la sortie de SpatiaLite, basée sur SQLite, beaucoup de SIGistes ont commencé à s'intéresser à SQLite mais, en pratique, peu d'entre eux connaissent bien cette base de données et ces nombreuses utilisations.

Pourtant, tout le monde l'utilise, peut-être sans s'en rendre compte, car SQLite est devenu le moteur de base de données le plus distribué au monde, grâce à son utilisation dans de nombreux logiciels grand public comme Firefox ou Skype et par Google, Apple, Android, McAffee ou Adobe et autres (www.sqlite.org/famous.html). La première question à se poser est:

Qu'est ce que SQLite ?

Tout d'abord, deux petits articles de base qui permettront de clarifier les choses:

http://fr.wikipedia.org/wiki/SQLite
http://www.framasoft.net/article4813.html

• SQLite est donc un moteur de base de données relationnelle, accessible par le langage SQL. Une base est un fichier autonome, très léger et indépendant des plateformes (comme Access ou FileMakerPro, qui eux ne fonctionnent pas sur toutes les plateformes). Le programme/bibliothèque est écrit en ANSI-C.
• une base SQLite (= un fichier) peut contenir plusieurs tables et vues SQL, des index, des fonctions, des triggers etc. 
• contrairement à un tableur, on donc peut donc gérer des relations SQL entre plusieurs tables ou sur une même table.
• SQLite est libre avec une licence qui permet son utilisation sans restriction aussi bien dans les projets libres que propriétaires.
• le site de SQLite explique très bien dans www.sqlite.org/whentouse.html et www.sqlite.org/limits.html les limites de SQLite, quand on peut l'utiliser et quand il est préférable d'utiliser une autre base de données:

« Another way to look at SQLite is this: SQLite is not designed to replace Oracle... »

Comment l'utiliser ?

• dans sa version de base, SQLite s'utilise en ligne de commandes, peu pratique pour le débutant:

• c'est pourquoi de nombreux éditeurs offrent des interfaces graphiques « à la Access ou FileMakerPro », plus ou moins sophistiquées, gratuites ou non: http://www.sqlite.org/cvstrac/wiki?p=ManagementTools:

         - du simple (Sqlite Database Browser, OpenSource, multi OS):

         - au plus complexe (SQLite2009 Pro Enterprise Manager, freeware, Windows):

• Firefox offre une extension nommée "SQLite Manager" qui permet de très bien gérer toutes les bases SQLite (Firefox utilise des bases SQLite pour gérer les marque-pages, les mots de passe, etc.). Cette extension est aussi disponible en application autonome:

• il existe aussi de nombreux utilitaires (gratuits ou non) permettant de convertir de et vers SQLite (Microsoft Access - SQLite par exemple).
• les bases SQLite peuvent être accédées par JDBC et ODBC, ce qui permet énormément de choses (modélisation entité-association, liens avec d'autres bases de données, etc.). Il est aussi possible de lier n bases SQLite (= n fichiers) nativement pour effectuer des requêtes.
• les bases sont accessibles en C, C++, C#, Python, PHP, Ruby, Java, R et tous les langages que vous voulez (le module sqlite3 est un module standard des distributions Python), voir www.sqlite.org/cvstrac/wiki).
• il est aussi possible de travailler avec des bases en mémoire, sans fichier (In-Memory databases: www.sqlite.org/inmemorydb.html).
• l'application Base d'OpenOffice peut être utilisée pour créer des formulaires.
• à titre d'exemple, GRASS GIS permet d'utiliser des bases SQLite en lieu et place du dbf.
• SQLite permet l'ajout d'extensions supplémentaires au programme (sous forme de bibliothèques (www.sqlite.org/cvstrac/wiki.) Il faut cependant que SQLite soit compilé avec l'option pour accepter les extensions. Il y a des extensions dans beaucoup de domaines. 

SQLite dans le domaine géospatial (à la PostGIS)

• SpatiaLite est une de ces extensions. Elle joue le même rôle que PostGIS pour PostgreSQL, c'est à dire qu'elle permet de gérer les géométries spatiales, mais dans une base portable (un fichier). Il y en a/a eu d'autres comme le package SQLiteMap dans R ou SQLite FDO provider. Dans sa version programme, on télécharge une version compilée SQLite + SpatiaLite (spatialite en ligne de commandes et Spatialite-gui).
• il est tout aussi possible de rajouter la librairie spatialite à une base SQLite existante, comme extension:

sqlite> .load 'libspatialite.so,dll ou dylib'

ou

SELECT load_extension(libspatialite.so,dll ou dylib)

• les interfaces graphiques signalées auparavant ne serviront à rien pour visualiser les géométries.
• il faut donc des SIG comme GRASS GIS, QGIS, ou le programme Spatialite-gis pour les visualiser (tout comme PostGIS):

• pour certains, les fichiers SpatiaLite vont remplacer à terme les shapefiles puisqu'ils permettent d'avoir en un seul fichier l'équivalent des fichiers .shp, .dbf, .shx et .proj. et qu'ils sont multi-plateformes.  Il faudrait pour cela que les grands noms du domaine géospatial s'y intéressent (ESRI and co.).
• l'auteur de SpatiaLite a développé une extension comparable pour les rasters, RasterLite. Rien n'empêche d'avoir dans une même base les deux extensions.

Conclusions

Et voilà, j'espère avoir été clair. Pour moi, depuis que j'ai découvert SQLite, fini les tableurs. Légers, portables, multi OS, accessibles par une grande variété d'applications, en SQL, accessibles avec Python et R, avec des SIG ou par des liens JDBC ou ODBC, les fichiers-bases SQLite sont devenus mon outil de travail par excellence (malgré ses quelques limites embêtantes). Par contre, j'utilise plus rarement SpatiaLite ou RasterLite.

Et si vous connaissez bien SQLite, vous saurez alors comment accélérer grandement Firefox en défragmentant périodiquement ses diverses bases SQLite.

par exemple, les marque-pages de Firefox:

et donc en les traitant comme des fichiers SQLite qu'ils sont:

sqlite3 places.sqlite ‘VACUUM';

et avec tous les autres fichiers dans le dossier, en Bash:

for f in */*.sqlite; do sqlite3 $f ‘VACUUM;’; done

voir www.korben.info/accelerer-firefox-en-le-defragmentant-windows-linux-et-mac.html

 

une ébauche de cet article a d'abord été publiée sur le ForumSIG: www.forumsig.org/showthread.php

 

Site officiel : SQLite
Site officiel : SQLite Database Browser
Site officiel : SQLite2009 Pro Enterprise Manager
Site officiel : SQLite Manager
Site officiel : sqlite-manager (Application)
Site officiel : SpatiaLite, RasterLIte
Site officiel : SQLiteMap (paquet R)