Bases de données SQLite

- Installation

Installez le paquet sqlite3

ou en ligne de commande :

  • sudo apt-get install sqlite3

- Avec un client graphique

- Installation de SQLiteManager

SQLite doit être installé : installez le paquet SQLite

Téléchargez la dernière version de SQLiteManager ici : http://sourceforge.net/projects/sqlitemanager/files/

Décompressez le fichier à la racine de votre serveur web. Cela crée un répertoire SQLiteManager-XYZ

Pour trouver la racine du serveur, chercher documentroot dans httpd.conf

Ouvrez votre navigateur à l'adresse_du_serveur_sqlitemanager-xyz → tout doit fonctionner avec une base de données de test pré-configurée.

La configuration de base de données doit être lisible et modifiable par le serveur web.

Par défaut, la base de données se trouve dans le répertoire SQLiteManager-XYZ/include/ ce qui pose un problème de sécurité.

Différentes méthodes pour améliorer la sécurité : http://www.sqlitemanager.org/en/installation/sqlitemanager/securising-of-sqlitemanager.html

Sous Windows, il n'y a pas de problème de droits d'accès sur les fichiers.

Sous Linux, nous devons attribuer des droits

Il faut d'abord attribuer un propriétaire et un groupe

Pour trouver ceux d'apache, lancer :

  • cat /etc/apache2/envvars | grep USER

Et pour le groupe, lancer :

  • cat /etc/apache2/envvars | grep GROUP

C'est en général www-data pour le user et pour le groupe : www-data:www-data

Appliquer ces propriétés :

  • sudo chown-R www-data:www-data RACINE_DU_SERVEUR/SQLiteManager-XYZ/

Ensuite, appliquez le droit d'écriture sur la base de données :

  • *
    chmod u+w /usr/local/apache/htdocs/SQLiteManager-XYZ/test.sqlite
    chmod u+w /usr/local/apache/htdocs/SQLiteManager-XYZ/include/config.db
    chmod u+w /usr/local/apache/htdocs/SQLiteManager-XYZ/include/config3.db

C'est prêt !

- Utilisation en ligne de commande

Lancez dans un terminal :

  • sqlite3

Le prompt change et signale que vous êtes dans le terminal SQlite :

- Commandes de SQLite

- Lister les commandes

? .help
: Afficher la liste des commandes :<code>

.backup ?DB? FILE Backup DB (default “main”) to FILE .bail ON|OFF Stop after hitting an error. Default OFF .databases List names and files of attached databases .dump ?TABLE? … Dump the database in an SQL text format

                       If TABLE specified, only dump tables matching
                       LIKE pattern TABLE.

.echo ON|OFF Turn command echo on or off .exit Exit this program .explain ?ON|OFF? Turn output mode suitable for EXPLAIN on or off.

                       With no args, it turns EXPLAIN on.

.header(s) ON|OFF Turn display of headers on or off .help Show this message .import FILE TABLE Import data from FILE into TABLE .indices ?TABLE? Show names of all indices

                       If TABLE specified, only show indices for tables
                       matching LIKE pattern TABLE.

.load FILE ?ENTRY? Load an extension library .log FILE|off Turn logging on or off. FILE can be stderr/stdout .mode MODE ?TABLE? Set output mode where MODE is one of:

                       csv      Comma-separated values
                       column   Left-aligned columns.  (See .width)
                       html     HTML <table> code
                       insert   SQL insert statements for TABLE
                       line     One value per line
                       list     Values delimited by .separator string
                       tabs     Tab-separated values
                       tcl      TCL list elements

.nullvalue STRING Print STRING in place of NULL values .output FILENAME Send output to FILENAME .output stdout Send output to the screen .prompt MAIN CONTINUE Replace the standard prompts .quit Exit this program .read FILENAME Execute SQL in FILENAME .restore ?DB? FILE Restore content of DB (default “main”) from FILE .schema ?TABLE? Show the CREATE statements

                       If TABLE specified, only show tables matching
                       LIKE pattern TABLE.

.separator STRING Change separator used by output mode and .import .show Show the current values for various settings .stats ON|OFF Turn stats on or off .tables ?TABLE? List names of tables

                       If TABLE specified, only list tables matching
                       LIKE pattern TABLE.

.timeout MS Try opening locked tables for MS milliseconds .trace FILE|off Output each SQL statement as it is run .vfsname ?AUX? Print the name of the VFS stack .width NUM1 NUM2 … Set column widths for “column” mode .timer ON|OFF Turn the CPU timer measurement on or off</code>

- Quitter le programme sqlite

? .exit
? .quit
: Quitte ce programme

- Modifier le format de sortie

? .mode MODE ?TABLE?
: Mode de sortie
: MODE est à choisir parmi :
  ? csv
  : valeurs séparées par des virgules
  ? column
  : colonnes alignées à gauche (voir .width)
  ? html
  : <table> (code HTML)<WRAP center round info 60%>
.mode html

affichera :

</WRAP>

  ? insert
  : commande SQL **insert** pour la table TABLE
  ? line
  : Une valeur par ligne
  ? [__list__]
  : Valeurs délimitées par la chaîne de séparation .separator
  ? tabs
  : valeurs séparées par des tabulations
  ? tcl
  : Liste TCL des éléments

- Afficher le nom des colonnes / Changer l'aspect des colonnes (.mode column)

? .header(s) on|[__off__]
: Affiche (ou non) les titres

Par exemple,

  • .header on
    .mode column

affichera les résultats comme ceci :

L'affichage par défaut

.header off
.mode list

affiche comme ceci :

? .width NUM1 NUM2 ...
: largeur des colonnes pour le mode **column** [par défaut, 10 caractères]

Par exemple,

  • .width 2 15 10 20 3

affichera:

? .separator STRING
: change le séparateur utilisé par le mode de sortie et par .import

En mode liste,

  • .separator ", "

affichera :

- Rappel des paramètres

? .show
: Affiche les valeurs actuelles des différents paramètres
  • .show

affichera :

- Gestion des bases

- Lister les bases et les tables

? .databases
: Liste les noms et les fichiers des bases de données associées
? .tables ?TABLE?
: Liste les noms des tables dans la base courante. Si TABLE est spécifié, ne liste que les tables correspondant au motif TABLE.

- Importer les données d'une table

? .import FILE TABLE
: Importer les données de FILE dans TABLE
? .indices ?TABLE?
: Afficher les noms de tous les indices
: Si TABLE est spécifiée, n'affiche que les indices correspondant au motif TABLE

- Écrire la sortie des résultats dans un fichier

? .output FILENAME
: Envoyer la sortie vers FILENAME
? .output stdout
: Envoyer la sortie vers l'écran
  • .output bd.txt
    select * from bd;
    .quit

Pour visualiser dans le terminal le fichier créé : en ligne de commande :

  • cat bd.txt

- Dumper une table depuis SQLite en format SQL pour sauvegarder la structure et les données sur un disque

? .dump ?TABLE? ...
: Dump de la base de données dans un format texte SQL
: Si TABLE est spécifié, ne dumpe que les tables correspondant au motif TABLE

Voir le résultat de la commande .dump

  • .dump bd

Rediriger la sortie vers un fichier puis dumper la table depuis SQLite

  • .output bd.sql
    .dump bd

Le résultat n'est plus affiché dans le terminal, mais redirigé vers le fichier bd.sql. Pour le vérifier il suffit d'afficher le contenu du fichier:

  • .quit
    cat bd.sql

Lire directement un fichier dumpé depuis sqlite

Tout d'abord effacez la table de la base:

  • drop table bd;

Puis lisez le fichier sauvegardé:

  • .read bd.sql
    select * from bd;

Affichera:

Dumper une base en format SQL pour sauvegarder sa structure, ses tables et ses données

Dumper la base :

  • sqlite3 livres.db .dump > livres.sql

Récupérer un fichier dumpé pour recréer la base :

Effacez le fichier original avant de procéder à la récupération de la base :

  • Effacer la base originale
    rm -r livres.db
  • Récuperer la base depuis le fichier de svg
    sqlite3 livres.db < livres.sql
  • Se connecter à la base
    sqlite3 livres.db
  • Faire un requête pour vérification
    sqlite> select * from bd;

Affichera:

- Manipuler une base

- Créer une base - ouvrir une base

Lancer sqlite3 avec le nom de la base :

  • sqlite3 livres.db

Si la base n'existe pas, elle sera créée. Toutes les commandes qui suivront concerneront cette base.

- Détruire une base

Il suffit d'effacer son fichier .db :

  • rm livres.db

- Créer une table

Dans sqlite, dans une base existante, lancer la commande :

  • CREATE TABLE bandedessinée (id integer primary key, titre VARCHAR(30), auteur VARCHAR(30), resume TEXT, num double, date_creation date);

Si le prompt apparaît après avoir tapé la commande, c'est qu'il manque le “;” à la fin de la requête. Ajoutez-le juste après le prompt validez.

Les types de données SQLite3 sont : NULL, INTEGER, REAL, TEXT et BLOB. Ce qui donne par exemple :

  • CREATE TABLE bandedessinée (id integer primary key, titre TEXT, auteur TEXT, resume TEXT, num REAL, date_creation INTEGER);

- Insérer des valeurs dans la table

Un exemple :

  • INSERT INTO "bandedessinée" VALUES(1, 'tintin au congo', 'hergé', 'Tintin est au congo.', 5.0, NULL);
    INSERT INTO "bandedessinée" VALUES(2, 'le nid des marsupilamis', 'franquin', 'Un reportage incroyable', 6.0, date('now'));
    INSERT INTO "bandedessinée" VALUES(3, 'la déesse', 'moebius', 'une aventure géniale', 7.0, strftime("%Y-%m-%d %H:%M:%S",'now','localtime'));

- Simple requête pour visualiser le contenu de la table

  • select * from bandedessinée;

affichera :

- Requête de visualisation d'une table formatée en sortie COMME une insertion de valeur

  • .mode insert bandedessinée
    select * from bandedessinée;

Affichera :

Quelques exemples de requêtes

  • Limiter une requête par nombre d'éléments :
    select * from bandedessinée limit 2;

    affiche :

  • Sélectionner les titres de la table bandedessinée enregistrés depuis février :
    select titre from bandedessinée where strftime('%m', date_creation)='02';

    affiche :

- Effacer une valeur dans la table

  • DELETE FROM "bandedessinée" WHERE id = 3;

- Ajouter une colonne à la table

  • ALTER TABLE "bandedessinée" add column "éditeur";

- Mettre à jour une valeur de la table

  • UPDATE "bandedessinée" SET éditeur ='casterman' WHERE id = 1;

- Modifier le nom d'une table

  • alter table 'bandedessinée' rename to 'bd';

- Autres commandes

? .backup ?DB? FILE
: Sauvegarde DB (par défaut "main") vers FILE
? .bail ON|[__OFF__]
: Stop après une erreur
? .echo ON|OFF
: Bascule la commande d'écho ON/OFF
? .explain ?ON|OFF?
: Bascule le mode de sortie approprié pour EXPLIQUER on or off
: Sans argument, on
? .load FILE ?ENTRY?
: Charger une bibliothèque d'extension
? .log FILE|off
: Activer ou désactiver la journalisation. FILE peut être stderr/stdout
? .nullvalue STRING
: Imprime STRING à la place des valeurs NULL
? .prompt MAIN CONTINUE
: Remplace les prompts standards  ? .read FILENAME
: Exécuter SQL dans FILENAME
? .restore ?DB? FILE
: Restaurer le contenu de la DB (par défaut "main") à partir de FILE
? .schema ?TABLE?
: Afficher les déclarations CREATE
: Si TABLE est spécifié, ne montrer que les tables correspondant au motif TABLE
? .stats ON|OFF
: Active ou désactive les stats
? .timeout MS
: Essayer d'ouvrir les tables verrouillées durant MS millisecondes
? .trace FILE|off
: afficher chaque instruction SQL au moment de son exécution
? .vfsname ?AUX?
: afficher le nom de la pile VFS
? .timer ON|OFF
: activer ou désactiver la minuterie du CPU