Section 2 : DB Browser pour SQLite¶

Cette section vous apprendra à utiliser DB Browser pour SQLite, un outil graphique essentiel pour le développement d'applications mobiles.

Utilisation dans le développement mobile :

• Concevoir visuellement le schéma avant d'écrire le code Room
• Prototyper et tester les requêtes SQL
• Analyser les bases de données extraites d'appareils Android/iOS
• Déboguer les problèmes de données en développement
• Générer les scripts SQL pour les migrations Room

2.1 Installation de DB Browser¶

DB Browser pour SQLite est un outil graphique open-source pour créer, gérer et modifier des bases de données SQLite.

Installation¶

1
2

sudo apt update
sudo apt install sqlitebrowser

1

sqlitebrowser

1

brew install --cask db-browser-for-sqlite

2.2 Interface de DB Browser¶

Lancez DB Browser et familiarisez-vous avec l'interface :

graph TB
    A[DB Browser] --> B[Structure de BD]
    A --> C[Parcourir les données]
    A --> D[Modifier les données]
    A --> E[Exécuter SQL]

    style A fill:#e1f5ff
    style B fill:#fff9c4
    style C fill:#f3e5f5
    style D fill:#c8e6c9
    style E fill:#ffccbc

2.3 Ouvrir la base de données existante¶

2.4 Création d'une nouvelle base de données¶

Créer la base de données¶

1. Fichier → Nouvelle base de données
2. Nommez le fichier bibliotheque.db
3. Choisissez l'emplacement de sauvegarde

Créer la table Auteurs¶

1. Onglet Structure de base de données
2. Cliquez sur Créer une table
3. Nom de la table : Auteurs

4. Ajoutez les champs suivants :

   Nom	Type	Contraintes
   id	INTEGER	PK, AI (Primary Key, AutoIncrement)
   nom	TEXT	NOT NULL
   prenom	TEXT	NOT NULL
   nationalite	TEXT	-
   date_naissance	DATE	-

   Note importante sur les dates dans SQLite

   SQLite n'a pas de type de données DATE natif. Bien que la syntaxe DATE soit acceptée, les dates sont stockées selon l'un des formats suivants :

   • TEXT : format ISO-8601 (YYYY-MM-DD)
   • INTEGER : timestamp Unix (secondes depuis 1970-01-01)
   • REAL : jour julien

   Il est recommandé d'utiliser le format TEXT avec ISO-8601 pour une meilleure lisibilité et compatibilité avec les fonctions de dates SQLite (date(), datetime(), etc.).

5. Cliquez sur OK

Créer la table Livres¶

1. Cliquez sur Créer une table
2. Nom : Livres

3. Ajoutez les champs :

   Nom	Type	Contraintes
   id	INTEGER	PK, AI
   titre	TEXT	NOT NULL
   isbn	TEXT	UNIQUE
   auteur_id	INTEGER	NOT NULL
   annee_publication	INTEGER	-
   genre	TEXT	-
   nombre_pages	INTEGER	-

4. Onglet Foreign Keys :

   • Cliquez sur Ajouter
   • Colonne : auteur_id
   • Table de référence : Auteurs
   • Colonne de référence : id
   • On Delete : CASCADE

5. Cliquez sur OK

Créer la table Emprunts¶

1. Nom : Emprunts

2. Champs :

   Nom	Type	Contraintes	Valeur par défaut
   id	INTEGER	PK, AI	-
   livre_id	INTEGER	NOT NULL	-
   nom_emprunteur	TEXT	NOT NULL	-
   date_emprunt	DATE	-	CURRENT_DATE
   date_retour_prevue	DATE	-	-
   date_retour_effective	DATE	-	-

3. Foreign Keys :

   • livre_id → Livres(id) ON DELETE CASCADE

2.5 Insertion de données via l'interface¶

Méthode 1 : Via l'onglet "Parcourir les données"¶

1. Sélectionnez la table Auteurs dans le menu déroulant
2. Cliquez sur Nouveau enregistrement (icône +)
3. Remplissez les champs (laissez id vide, il sera auto-généré)

4. Ajoutez les auteurs suivants :

   Nom	Prénom	Nationalité	Date de naissance
   Hugo	Victor	France	1802-02-26
   Camus	Albert	France	1913-11-07
   Yacine	Kateb	Algérie	1929-08-02
   García Márquez	Gabriel	Colombie	1927-03-06

5. Cliquez sur Écrire les modifications (icône disquette)

Méthode 2 : Via SQL¶

1. Onglet Exécuter SQL

2. Saisissez le code suivant :

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

   -- Livres INSERT INTO Livres (titre, isbn, auteur_id, annee_publication, genre, nombre_pages) VALUES ('Les Misérables', '978-2070409228', 1, 1862, 'Roman historique', 1232), ('L''Étranger', '978-2070360024', 2, 1942, 'Roman philosophique', 186), ('La Peste', '978-2070360420', 2, 1947, 'Roman', 279), ('Nedjma', '978-2020006415', 3, 1956, 'Roman', 256), ('Cent ans de solitude', '978-2020024488', 4, 1967, 'Réalisme magique', 417); -- Emprunts INSERT INTO Emprunts (livre_id, nom_emprunteur, date_emprunt, date_retour_prevue) VALUES (1, 'Ahmed Benali', '2024-12-01', '2024-12-15'), (2, 'Fatima Zahra', '2024-12-10', '2024-12-24'), (3, 'Mohamed Salah', '2024-12-15', '2024-12-29');

3. Cliquez sur ▶ Exécuter (ou appuyez sur F5)

4. Cliquez sur Écrire les modifications

2.6 Visualisation et filtrage des données¶

Filtrage simple¶

1. Onglet Parcourir les données → Sélectionnez Livres
2. Utilisez le champ Filtre en haut :
3. genre = 'Roman'
4. annee_publication > 1900
5. nombre_pages > 200

Tri des données¶

• Cliquez sur les en-têtes de colonnes pour trier
• Cliquez à nouveau pour inverser l'ordre

Recherche rapide¶

• Utilisez Ctrl+F (ou Cmd+F sur Mac) pour rechercher dans la table actuelle

2.7 Requêtes SQL avancées¶

Dans l'onglet Exécuter SQL, testez les requêtes suivantes :

Vue complète des livres avec leurs auteurs¶

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT 
    l.titre,
    l.isbn,
    a.prenom || ' ' || a.nom as auteur,
    a.nationalite,
    l.annee_publication,
    l.genre
FROM Livres l
JOIN Auteurs a ON l.auteur_id = a.id
ORDER BY l.annee_publication DESC;

Livres actuellement empruntés¶

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

SELECT 
    l.titre,
    a.prenom || ' ' || a.nom as auteur,
    e.nom_emprunteur,
    e.date_emprunt,
    e.date_retour_prevue,
    CASE 
        WHEN e.date_retour_prevue < date('now') THEN 'En retard'
        ELSE 'Dans les délais'
    END as statut
FROM Emprunts e
JOIN Livres l ON e.livre_id = l.id
JOIN Auteurs a ON l.auteur_id = a.id
WHERE e.date_retour_effective IS NULL
ORDER BY e.date_retour_prevue;

Statistiques par auteur¶

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT 
    a.prenom || ' ' || a.nom as auteur,
    COUNT(l.id) as nombre_livres,
    ROUND(AVG(l.nombre_pages), 0) as moyenne_pages,
    MIN(l.annee_publication) as premiere_publication,
    MAX(l.annee_publication) as derniere_publication
FROM Auteurs a
LEFT JOIN Livres l ON a.id = l.auteur_id
GROUP BY a.id
ORDER BY nombre_livres DESC;

2.8 Modification de structure¶

Ajouter une colonne¶

1. Clic droit sur la table Livres → Modifier la table
2. Cliquez sur Ajouter un champ
3. Nom : prix
4. Type : REAL
5. Cliquez sur OK

Mettre à jour les prix¶

1
2
3
4
5

UPDATE Livres SET prix = 25.50 WHERE id = 1;
UPDATE Livres SET prix = 12.00 WHERE id = 2;
UPDATE Livres SET prix = 15.50 WHERE id = 3;
UPDATE Livres SET prix = 18.00 WHERE id = 4;
UPDATE Livres SET prix = 22.00 WHERE id = 5;

1

UPDATE Livres SET prix = 15.00 WHERE annee_publication < 1950;

2.9 Création de vues¶

Les vues sont des requêtes SQL enregistrées qui se comportent comme des tables virtuelles.

Vue des livres disponibles¶

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

CREATE VIEW Livres_Disponibles AS
SELECT 
    l.id,
    l.titre,
    a.prenom || ' ' || a.nom as auteur,
    l.genre,
    l.prix
FROM Livres l
JOIN Auteurs a ON l.auteur_id = a.id
WHERE l.id NOT IN (
    SELECT livre_id 
    FROM Emprunts 
    WHERE date_retour_effective IS NULL
);

Vue des emprunts en cours¶

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE VIEW Emprunts_En_Cours AS
SELECT 
    e.id,
    l.titre,
    e.nom_emprunteur,
    e.date_emprunt,
    e.date_retour_prevue,
    julianday(e.date_retour_prevue) - julianday('now') as jours_restants
FROM Emprunts e
JOIN Livres l ON e.livre_id = l.id
WHERE e.date_retour_effective IS NULL;

2.10 Import/Export de données¶

Export en CSV¶

1. Onglet Parcourir les données → Sélectionnez Livres
2. Fichier → Exporter → Table(s) en CSV...
3. Choisissez le nom et l'emplacement
4. Configurez les options (séparateur, guillemets, etc.)

Import depuis CSV¶

1. Fichier → Importer → Table depuis fichier CSV...
2. Sélectionnez le fichier CSV
3. Configurez les options d'import :
4. Nom de la table
5. Première ligne contient les en-têtes
6. Séparateur de champs
7. Prévisualisez et importez

Export complet de la base¶

1
2

-- Méthode 1 : Via le menu
-- Fichier → Exporter → Base de données en fichier SQL...

Ou via SQL :

1
2
3
4

-- Dans l'onglet Exécuter SQL
.output bibliotheque_backup.sql
.dump
.output stdout

2.11 Optimisation et maintenance¶

Créer des index¶

Les index accélèrent les recherches sur les colonnes fréquemment utilisées.

1
2
3
4
5
6
7
8

-- Index sur le titre des livres
CREATE INDEX idx_livres_titre ON Livres(titre);

-- Index sur l'auteur
CREATE INDEX idx_livres_auteur ON Livres(auteur_id);

-- Index sur le livre emprunté
CREATE INDEX idx_emprunts_livre ON Emprunts(livre_id);

Analyser et optimiser¶

1
2
3
4
5
6
7
8

-- Analyser les statistiques
ANALYZE;

-- Nettoyer la base (récupérer l'espace)
VACUUM;

-- Vérifier l'intégrité
PRAGMA integrity_check;

2.12 Exercice final intégré¶

Étape 1 : Enrichir la base¶

1. Ajoutez 5 nouveaux auteurs de votre choix
2. Ajoutez 10 nouveaux livres avec différents genres
3. Créez 5 emprunts avec différentes dates

Étape 2 : Requêtes d'analyse¶

Créez les requêtes suivantes :

Livres en retard avec pénalités¶

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

SELECT 
    l.titre,
    a.prenom || ' ' || a.nom as auteur,
    e.nom_emprunteur,
    e.date_retour_prevue,
    julianday('now') - julianday(e.date_retour_prevue) as jours_retard,
    l.prix * 0.10 * (julianday('now') - julianday(e.date_retour_prevue)) as penalite
FROM Emprunts e
JOIN Livres l ON e.livre_id = l.id
JOIN Auteurs a ON l.auteur_id = a.id
WHERE e.date_retour_effective IS NULL 
  AND e.date_retour_prevue < date('now')
ORDER BY jours_retard DESC;

Montant total des livres empruntés par personne¶

1
2
3
4
5
6
7
8

SELECT 
    e.nom_emprunteur,
    COUNT(e.id) as nombre_emprunts,
    SUM(l.prix) as montant_total
FROM Emprunts e
JOIN Livres l ON e.livre_id = l.id
GROUP BY e.nom_emprunteur
ORDER BY montant_total DESC;

Top 3 des genres les plus empruntés¶

1
2
3
4
5
6
7
8
9

SELECT 
    l.genre,
    COUNT(e.id) as nombre_emprunts,
    ROUND(AVG(l.prix), 2) as prix_moyen
FROM Emprunts e
JOIN Livres l ON e.livre_id = l.id
GROUP BY l.genre
ORDER BY nombre_emprunts DESC
LIMIT 3;

Synthèse et bonnes pratiques¶

Points clés à retenir¶

Bonnes pratiques¶

• Toujours utiliser des transactions pour les modifications multiples
• Définir des contraintes (PRIMARY KEY, FOREIGN KEY, UNIQUE)
• Créer des index sur les colonnes fréquemment recherchées
• Utiliser PRAGMA foreign_keys = ON pour activer les clés étrangères
• Faire des sauvegardes régulières avec export SQL
• Tester l'intégrité avec PRAGMA integrity_check

Commandes essentielles¶

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- Activer les clés étrangères
PRAGMA foreign_keys = ON;

-- Voir la configuration
PRAGMA database_list;

-- Analyser une table
PRAGMA table_info(nom_table);

-- Optimiser
VACUUM;
ANALYZE;

Ressources supplémentaires¶

• Documentation officielle SQLite
• DB Browser pour SQLite
• SQLite Tutorial
• SQL Cheat Sheet