Documentation du Dr FRAPPE

Ce wiki regroupe les résultats de mes expériences en informatique accumulés au cours de mes recherches sur le net.

Dans la mesure du possible, j'ai cité mes sources ; il en manque certainement… :-)

Bases de données SQLite

1. Installation

Installez le paquet sqlite3

ou en ligne de commande :

  • sudo apt-get install sqlite3

2. Avec un client graphique

2.1. 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 !

3. Utilisation en ligne de commande

Lancez dans un terminal :

  • sqlite3

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

3.1. Commandes de SQLite

3.1.1. Lister les commandes

.help
Afficher la liste des commandes :
.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

3.1.2. Quitter le programme sqlite

.exit
.quit
Quitte ce programme

3.1.3. 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)
.mode html

affichera :

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

3.1.4. 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 :

3.1.5. Rappel des paramètres

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

affichera :

3.1.6. Gestion des bases

3.1.6.1. 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.

3.1.6.2. 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

3.1.6.3. É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

3.1.6.4. 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:

3.1.7. Manipuler une base

3.1.7.1. 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.

3.1.7.2. Détruire une base

Il suffit d'effacer son fichier .db :

  • rm livres.db

3.1.7.3. 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);

3.1.7.4. 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'));

3.1.7.5. Simple requête pour visualiser le contenu de la table

  • select * from bandedessinée;

affichera :

3.1.7.6. 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 :

3.1.7.7. Effacer une valeur dans la table

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

3.1.7.8. Ajouter une colonne à la table

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

3.1.7.9. Mettre à jour une valeur de la table

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

3.1.7.10. Modifier le nom d'une table

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

3.1.8. 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

Navigation