Gérer et maintenir sa base

On a une base qui fonctionne, des interfaces pour saisir et consulter, mais la vie d'une base de données, c'est aussi tout ce qui se passe entre les deux : importer des données qu'on avait déjà dans un tableur, ajouter un champ oublié, corriger une erreur, supprimer un doublon. Ce chapitre couvre tout ça.

Importer des données existantes (CSV, tableur)

C'est souvent la première question qu'on pose : "J'ai déjà tout dans un Excel, je fais comment ?" Bonne nouvelle, DB Browser gère l'import de fichiers CSV directement. Mauvaise nouvelle (relat-ive (oui, je suis fou de jeux de mots aujourd'hui)), il faut que le CSV soit propre.

Préparer le fichier CSV

Avant de foncer tête baissée , quelques règles à respecter pour que l'import se passe bien :

• Un fichier = une table. Si on veut importer du mobilier et des zones, c'est deux fichiers séparés.
• La première ligne doit contenir les noms de colonnes, exactement comme ils apparaissent dans votre table (ou alors des noms qu'on va mapper manuellement, mais c'est pas giga pratique).
• Si la table existe déjà, même les colonnes vides doivent être présentes.
• Pas de colonnes calculées (on se souvient du chapitre 1 ? ). Pas de colonne "âge", "superficie calculée", etc. Ou alors on ne fait qu'importer une valeur fixe (et c'est pas vraiment l'idée d'une colonne calculée).
• Les clés étrangères doivent contenir les id numériques, pas les libellés. Donc si on veut importer du mobilier, le champ zone doit contenir 1, 2, 3... et non A1, A2, B1... Ça implique de bien préparer le fichier en amont.
• L'encodage : préférer UTF-8 pour éviter les problèmes d'accents. Dans Excel, Fichier → Enregistrer sous → CSV UTF-8 (délimité par des virgules). Dans LibreOffice Calc, on peut choisir l'encodage à l'export (comme toujours, le libre c'est mieux ).

Pour résumer, si on a ça :

Il faut obtenir ça :

Si vous avez les libellés dans un tableur et pas les id, la solution la plus simple c'est de faire la correspondance dans le tableur lui-même (avec un VLOOKUP / RECHERCHEV) avant d'exporter (alors je pourrais vous expliquer mais il y a PLEIN de tutos en ligne qui existent déjà (donc flemme )). Sinon, on peut aussi importer les données dans une table temporaire et faire la jointure en SQL directement dans DB Browser. :nerd_face:

Bon après, il y a une solution plus simple, mais c'est avec un autre logiciel. DBeaver gère très bien le mapping, c'est-à-dire que même si les colonnes n'ont pas le même nom, ne sont pas dans le même ordre et qu'elles ne sont pas toutes présentes, on peut les faire correspondre manuellement via interface graphique. En revanche, il faut tout même remplacer les valeurs explicites par leur clé primaire.

Importer avec DB Browser

1. Dans DB Browser, ouvrir la base (on pouvait s'en douter, je sais )
2. Menu Fichier → Importer → Table depuis un fichier CSV...
3. Sélectionner le fichier CSV
4. Une fenêtre de configuration s'ouvre :

• Nom de la table : On peut importer dans une table existante ou en créer une nouvelle. Pour importer dans une table existante (par exemple T_mobilier), il faut taper son nom sans faute et en respectant la casse. DB Browser demande une confirmation si la table existe déjà.
• Les colonnes ont des en-têtes : cocher cette case si la première ligne du CSV contient les noms des colonnes (ce qui devrait souvent être le cas).
• Séparateur de champs : virgule ou point-virgule selon votre export. Excel français exporte souvent en point-virgule...
• Guillemets : laisser " par défaut.

• Encodage : UTF-8 si les conseils ci-dessus ont bien été suivis !

• Cliquez sur OK et vérifiez l'aperçu. Si les colonnes se découpent bizarrement, c'est souvent un problème de séparateur.

DB Browser importe tout en TEXT quand on crée une nouvelle table. Si on importe dans une table existante avec les bons types définis, c'est mieux, les types (TEXT, NUMERIC, BOOLEAN,...) sont respectés. SQLite gère la conversion à la volée dans la plupart des cas, mais les id doivent être des INTEGER pour que les clés étrangères fonctionnent.

Vérifier après l'import

Après l'import, deux vérifications rapides dans l'onglet "Exécuter le SQL" :

-- Compter les lignes importées
SELECT COUNT(*) FROM T_mobilier;

-- Vérifier qu'il n'y a pas de doublons sur l'identifiant
SELECT identifiant, COUNT(*) AS nb
FROM T_mobilier
GROUP BY identifiant
HAVING nb > 1;

Si la deuxième requête retourne des lignes, il y a des doublons à corriger.

-- Vérifier que toutes les clés étrangères pointent vers quelque chose qui existe
SELECT m.identifiant, m.zone
FROM T_mobilier m
LEFT JOIN T_zones z ON m.zone = z.id
WHERE z.id IS NULL AND m.zone IS NOT NULL;

Si cette requête retourne des lignes, c'est que certaines valeurs dans zone ne correspondent à aucune zone connue. Il faut corriger ça avant de commencer à travailler avec ces données.

Clique sur "Écrire les modifications" après l'import.

Modifier le schéma de la base

La base est créée, des données ont été saisies, mais une base de données, c'est un objet vivant . Elle évolue et grandit jusqu'à qu'on se dise qu'on l'a connue alors qu'elle était "comme ça" et puis on continue de viellir et :skull_and_crossbones:. Un travail important en gestion de base de données c'est gérer les modifications qui seront régulièrement nécessaires.

Souvent, on met plein de champs qui s'avèrent inutiles et on se rend compte qu'il en manque des très importants. De mon côté, après un an d'utilisation, je fais souvent un petit audit des bases produites simplement pour aller regarder les colonnes vides ou les termes récurrents dans les commentaires; en plus de discuter avec les utilisateurices, ça donne une bonne idée des modifications à apporter. Je vous laisse vous gérer pour établir ces choix, moi je me contente de vous indiquer comment modifier vos bases.

Ce que SQLite permet (et ce qu'il ne permet pas)

SQLite est une base de données légère, et ça a des contreparties. Sur la modification du schéma, il est beaucoup plus limité que PostgreSQL :

Opération	DB Browser (graphique)	SQL direct
Ajouter une colonne		ALTER TABLE T_mobilier ADD COLUMN etat_general TEXT;
Renommer une colonne	(SQLite ≥ 3.25)	ALTER TABLE T_mobilier RENAME COLUMN vieille_col TO nouvelle_col;
Renommer une table		ALTER TABLE T_old RENAME TO T_new;
Supprimer une colonne	(SQLite ≥ 3.35)	ALTER TABLE T_mobilier DROP COLUMN champ_inutile;
Changer le type d'une colonne	pas direct	Stratégie de contournement (voir ci-dessous)
Supprimer une contrainte	pas direct	Stratégie de contournement

Pour connaître la version de SQLite embarquée dans ta DB Browser : SELECT sqlite_version();

Les cas simples dans DB Browser

Pour ajouter ou renommer une colonne, vous pouvez passer par l'interface graphique :

1. Clic droit sur la table dans "Structure de la base de données" → "Modifier la table"
2. Pour ajouter : clic sur "Ajouter", nomme le champ, choisis le type
3. Pour renommer : double-clic sur le nom du champ existant et modification
4. OK pour valider

Si vous renommez un champ, pensez à mettre à jour les requêtes, vues, et tous les formulaires qui y font référence. DB Browser ne le fait pas automatiquement. :police_officer:

La stratégie de contournement : recréer la table

Si on veut faire quelque chose que SQLite ne supporte pas directement (changer un type, supprimer une contrainte, réorganiser les colonnes), la recette standard c'est :

1. Créer une nouvelle table avec la structure voulue
2. Copier les données depuis l'ancienne
3. Supprimer l'ancienne
4. Renommer la nouvelle

En SQL, ça ressemble à ça (exemple idiot mais parlant : on veut passer photographie de TEXT à INTEGER dans T_mobilier) :

-- 1. Créer la nouvelle table avec la bonne structure
CREATE TABLE T_mobilier_new (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    identifiant TEXT UNIQUE,
    zone        INTEGER,
    nature      INTEGER,
    contexte_sol INTEGER,
    date_creation TEXT,
    date_modification TEXT,
    auteurice   INTEGER,
    photographie INTEGER,  -- le nouveau type
    commentaires TEXT
);

-- 2. Copier les données (SQLite fait la conversion TEXT → INTEGER à la volée si possible)
INSERT INTO T_mobilier_new
SELECT id, identifiant, zone, nature, contexte_sol,
       date_creation, date_modification, auteurice,
       CAST(photographie AS INTEGER), commentaires
FROM T_mobilier;

-- 3. Supprimer l'ancienne table
DROP TABLE T_mobilier;

-- 4. Renommer la nouvelle
ALTER TABLE T_mobilier_new RENAME TO T_mobilier;

Cette opération supprime les clés étrangères et les colonnes de géométrie SpatiaLite associées à l'ancienne table. Il faudra les redéclarer sur la nouvelle (voir chapitre 02). Fais une sauvegarde du fichier .db avant toute manipulation de ce type.

DB Browser propose aussi l'option "Modifier la table" → onglet "Avancé" qui permet d'éditer directement le SQL de création de la table et effectue la recréation automatiquement. C'est plus rapide et s'habitue très vite à ce genre de commande à force de manipuler des bases.

Modifier et supprimer des enregistrements

Avec LibreOffice Base

C'est l'interface la plus confortable pour corriger des données sans écrire de SQL. Il y a deux façons de faire.

En vue table

Double-cliquer sur une table dans la section "Tables" de LibreOffice Base. On arrive dans une vue tabulaire directement éditable :

• Pour modifier : cliquer sur la cellule à corriger, taper la nouvelle valeur et valider avec Entrée ou en changeant de ligne.
• Pour supprimer un enregistrement : cliquer sur le numéro de ligne à gauche pour sélectionner la ligne entière, puis appuyer sur Suppr (ou clic droit → Supprimer les lignes). LibreOffice demande une confirmation.

Si une clé étrangère pointe vers l'enregistrement qu'on veut supprimer (par exemple, supprimer une zone qui a encore des mobiliers associés), la suppression sera bloquée. C'est exactement le rôle des contraintes d'intégrité référentielle : éviter d'avoir des données orphelines. Il faut d'abord supprimer ou réaffecter les enregistrements liés.

Via un formulaire

Encore plus simple, via un formulaire (enfin évidemment, si on a d'abord fait un formulaire )

• Modifier : cliquer directement sur le champ voulu, corriger, et passer à l'enregistrement suivant. Le changement est enregistré automatiquement.
• Supprimer : dans le menu Données → Supprimer l'enregistrement (ou l'icône correspondante dans la barre d'outils). Confirmation demandée.

Avec DB Browser (SQL)

Pour des modifications en masse ou des corrections complexes, le SQL est beaucoup plus puissant qu'une interface graphique. L'onglet "Exécuter le SQL" est votre ami.

Modifier des enregistrements (UPDATE)

La syntaxe de base :

UPDATE nom_de_la_table
SET champ1 = nouvelle_valeur, champ2 = autre_valeur
WHERE condition;

Le WHERE est indispensable. Sans WHERE, on modifie tous les enregistrements de la table. La vie est cruelle, comme les bases de données (mais elles, elles ne décident pas d'aller acheter des cigarettes la veille de Noël).

Quelques exemples concrets :

-- Corriger le nom d'une zone
UPDATE T_zones
SET nom = 'B2_corrigé'
WHERE id = 5;

-- Marquer toutes les zones de priorité haute comme prospectées
UPDATE T_zones
SET fait = 1
WHERE priorite = (SELECT id FROM L_priorites WHERE priorite = 'Haute');

-- Corriger une faute de frappe dans les commentaires d'un mobilier
UPDATE T_mobilier
SET commentaires = 'Céramique médiévale, probablement XVe s.'
WHERE identifiant = 'MOB-012';

-- Réaffecter tout le mobilier d'une zone supprimée vers une autre
UPDATE T_mobilier
SET zone = 3
WHERE zone = 7;

Supprimer des enregistrements (DELETE)

DELETE FROM nom_de_la_table
WHERE condition;

Même mise en garde : toujours mettre un WHERE.

-- Supprimer un mobilier précis
DELETE FROM T_mobilier
WHERE identifiant = 'MOB-099';

-- Supprimer toutes les anomalies d'une zone
DELETE FROM T_anomalies
WHERE zone = 4;

-- Supprimer les doublons (garder seulement le premier enregistrement de chaque identifiant)
DELETE FROM T_mobilier
WHERE id NOT IN (
    SELECT MIN(id)
    FROM T_mobilier
    GROUP BY identifiant
);

Bonne pratique avant de supprimer : Commencer par un SELECT pour vérifier ce qui va être supprimé avant de le faire grâce au DELETE. Exemple : remplacer DELETE FROM T_mobilier WHERE ... par SELECT * FROM T_mobilier WHERE ... et regarde le résultat. Seulement si c'est bien ce qu'on veut, on lance la commande DELETE.

Activer les clés étrangères

Par défaut, SQLite ne vérifie pas les contraintes de clés étrangères à l'exécution SQL. Il faut les activer manuellement dans la session courante :

PRAGMA foreign_keys = ON;

Lancer cette commande avant les DELETE ou UPDATE si on veut que SQLite bloque les manipulations en cas de violation d'intégrité référentielle (on en a déjà parlé il me semble. Et sinon, bah envoyez-moi un mail ). C'est désactivé par défaut pour des raisons de compatibilité, mais l'activer pendant les opérations de maintenance, c'est une bonne habitude. :police_officer:

N'oubliez pas d'"Écrire les modifications" après les opérations SQL dans DB Browser.