Bonnes pratiques SQL : ALTERation de schéma de données

Il arrive de temps à autre qu’on ait besoin de modifier le type ou le nom d’une colonne (ou plusieurs) de la base de données d’un plugin. Moins versés en SQL qu’en PHP, les développeurs recopient souvent les instructions qui ont fonctionné en local sans se douter de leur non-portabilité.

Cette situation se pose rarement, sinon c’est le signe d’un manque de réflexion sur le schéma des données. Mais comme cela peut arriver, cet article vise à vous aider à ce que ça se passe bien.

Au menu :


Le problème : les dialectes d’éditeurs...

Pour le cas qui nous concerne ; la norme, comme on va le voir, ne prévoit pas de façon simple d’arriver à nos fins. Mais soucieux du confort de leurs utilisateurs, les différents éditeurs de SGBDR ont prévu les raccourcis qui nous simplifient la vie... sauf qu’il n’y a pas de consencus de fait (même si des tendances se dégagent)

Changement de type d’une colonne
  • DB2 & Informix : ALTER TABLE ma_table MODIFY (ma_colonne NouveauType);
  • MySQL & Oracle : ALTER TABLE ma_table MODIFY ma_colonne NouveauType;
  • PostgreSQL : ALTER TABLE ma_table ALTER COLUMN ma_colonne TYPE NouveauType;
  • Jet-SQL & TransactSQL : ALTER TABLE ma_table ALTER COLUMN ma_colonne NouveauType;
  • SQLite : ce n’est pas possible ; il faut re-creer la table...
Renommage de colonne
  • DB2 & Informix : RENAME COLUMN ma_table.ancien_nom TO nouveau_nom;
  • MySQL : ALTER ma_table CHANGE ancien_nom nouveau_nom NouveauType; (il faut redire le type ou en profiter pour le changer)
  • DB2 & Informix & Oracle & PostgreSQL : ALTER ma_table RENAME COLUMN ancien_nom TO nouveau_nom NouveauTypeOptionnellement;
  • TransactSQL : Il faut passer par exec sp_RENAME 'ma_table.ancien_nom', 'nouveau_nom', 'COLUMN'
Renommage de table
  • DB2 & Informix & MySQL : RENAME TABLE ma_table TO nouveau_nom;
  • Oracle & PostgreSQL & SQLite : ALTER TABLE ma_table RENAME TO nouveau_nom;
  • TransactSQL : Il faut passer par exec sp_RENAME 'ma_table', 'nouveau_nom' puis ne pas oublier de faire un EXEC sp_RECOMPILE nouveau_nom
Suppression d’index
  • DB2 & Oracle & PostgreSQL & SQLite & TransactSQL : DROP INDEX mon_schema.ancien_index;
  • Jet-SQL : DROP INDEX ancien_index ON ma_table;
  • MySQL & ...ANSI : ALTER TABLE ma_table DROP INDEX ancien_index;
Suppression d’une clé
  • DB2 & Oracle & PostgreSQL & SQLite & Jet-SQL & TransactSQL : ALTER TABLE ma_table DROP CONSTRAINT nom_de_cle;
  • MySQL utilise selon le type de clé : ALTER TABLE ma_table DROP PRIMARY KEY ancienne_clef; (clé primaire) ou ALTER TABLE ma_table DROP UNIQUE ancienne_clef; (contrainte d’unicité) ou ALTER TABLE ma_table DROP CHECK ancienne_clef; (contrainte de plage de valeur) ou ALTER TABLE ma_table DROP FOREIGN KEY ancienne_clef; (clé étrangère)
  • Il est à noter que pour une clé étrangère on peut parfois préciser (à la fin) CASCADE (suppression aussi des lignes correspondantes dans la table maitre) ou RESTRICT (suppression seulement des clés inutilisées dans l’index)... Il est à noter également que les clés sont des index particulers et donc que la syntaxe de suppression d’index est souvent applicable aussi !
Suppression d’une valeur par défaut
  • DB2 & Oracle & PostgreSQL & Jet-SQL & TransactSQL : ALTER TABLE ma_table ALTER COLUMN ma_colonne DROP DEFAULT;
  • MySQL : ALTER TABLE ma_table ALTER ma_colonne DROP DEFAULT;
Modification (ou ajout) d’une valeur par défaut
  • DB2 & Oracle & PostgreSQL : ALTER TABLE ma_table MODIFY ma_colonne DEFAULT 'Nouvelle Valeur';
  • Jet-SQL & TransactSQL : ALTER TABLE ma_table ALTER COLUMN ma_colonne SET DEFAULT 'Nouvelle Valeur';
  • MySQL : ALTER TABLE ma_table ALTER ma_colonne SET DEFAULT 'Nouvelle Valeur';

La solution : la norme...

On ne le répète jamais assez, mais il faut garder un maximum d’indépeendance vis à vis des éditeurs : non seulement vous vous éviterez des arachages de cheveux lors de migration de SGBD mais vous rendez votre plugin accessible au plus grand nombre. Ceci passe bien sur par l’utilisation de l’API-SQL de SPIP, mais aussi en évitant les syntaxes privées au profit de la norme... [1]

Modifier une colonne
Pour changer le nom et/ou le type de la colonne d’une table, la norme ne prévoit rien... (mais après tout, ce n’est pas une opération courante...) Ceci dit, on peut y arriver avec le jeu de base prévu.
  1. Tout d’abord il faut créer/ajouter une nouvelle colonne dans la table ALTER TABLE ma_table ADD nouveau_nom NouveauType; (et donc redire son type ou en profiter pour le changer) Cela se traduit avec l’API SPIP par : sql_alter("TABLE ma_table ADD nouveau_nom NouveauType"); Il faut bien entendu indiquer si le champ est NOT NULL et la valeur par défaut DEFAULT ..., mais c’est tout ; pas de truc propriétaire (donc problématique) comme AFTER telle_nom_colonne que recopient les utilisateurs de PhpMyAdmin !
  2. Ensuite recopier l’ancienne colonne dans la nouvelle... UPDATE ma_table SET nouveau_nom=ancien_nom; Soit avec l’API SQL de SPIP : sql_update('ma_table', array('nouveau_nom'=>'ancien_nom') );
  3. Enfin supprimer l’ancienne colonne... ALTER TABLE ma_table DROP ancien_nom; Soit : sql_alter("TABLE ma_table DROP ancien_nom");
Le changement de la valeur par defaut variant d’un système à l’autre, il vaut mieux redéfinir la colonne avec la bonne valeur par défaut !
Ajouter ou modifier un index
Pour ajouter un index sur des colonnes d’une table : ALTER TABLE ma_table ADD INDEX nouvel_index (liste,des,colonnes,indexees); Soit sql_alter("TABLE ma_table ADD INDEX nouvel_index (liste,des,colonnes,indexees)"); Mais, mais, mais :
  • Noter qu’il faut préferer le mot clé INDEX à KEY moins connu en dehors de MySQL...
  • Noter aussi que bien que tous les gestionnaires permettent d’indiquer le type d’index à utiliser que cela est à éviter car non portable... De plus il s’agit là d’optimisation du ressort de l’administrateur de la base...
  • Noter enfin que la syntaxe avec ALTER TABLE est plus portable que la syntaxe CREATE INDEX nouvel_index ON ma_table(liste,des,colonnes,indexees); car ayant pas mal de variation d’un système à un autre...
La norme ne prévoit pas d’instruction de modification/renommage et il faut procéder en deux temps...
  1. Supprimer l’ancien en préférant la syntaxe normalisée ANSI : ALTER TABLE ma_table DROP INDEX ancien_index; ; soit sql_alter("TABLE ma_table DROP INDEX ancien_index"); (Noter cependant qu’en général un index est supprimé avec la suppression de toutes les colonnes qu’il prend en compte...)
  2. Bien le recréer comme vu précédement
Ajouter ou modifier une clé
L’ajout d’une clé (avec un nom système automatiquement attribué) sur une colonne d’une table se fait par : ALTER TABLE ma_table ADD PRIMARY KEY (la_colonne); (clé primaire) ou ALTER TABLE ma_table ADD UNIQUE (la_colonne); (contrainte d’unicité) ou ALTER TABLE ma_table ADD CHECK (contrainte sur la_colonne); (contrainte de plage de valeurs) ou ALTER TABLE ma_table ADD FOREIGN KEY (la_colonne) REFERENCES lautre_table(ses,colonnes); (clé étrangère).
L’ajout d’une clé nommée par nos soins, et pouvant porter sur plusieurs colonnes d’une table, se fait par : ALTER TABLE ma_table ADD CONSTRAINT nouvelle_clef PRIMARY (les,colonnes); (clé primaire) ou ALTER TABLE ma_table ADD CONSTRAINT nouvelle_clef UNIQUE (les,colonnes); (contrainte d’unicité) ou ALTER TABLE ma_table ADD CONSTRAINT nouvelle_clef CHECK (contraintes logiquement_combinees sur les_colonnes); (contrainte de plage de valeurs) ou ALTER TABLE ma_table ADD CONSTRAINT nouvelle_clef FOREIGN KEY (la_colonne) REFERENCES lautre_table(ses,colonnes); (clé étrangère).
Quand la clé existe déjà et que l’on désire la modifier, il n’existe pas d’instruction dédiée ; il faut procéder en deux temps...
  1. Supprimer l’ancienne en préférant la syntaxe normalisée ANSI92 : ALTER TABLE ma_table DROP CONSTRAINT ancienne_clef; ; soit sql_alter("TABLE ma_table DROP CONSTRAINT ancienne_clef"); (Noter cependant qu’en général une clé est supprimée avec la suppression de toutes les colonnes qu’elle prend en compte...)
  2. Bien le recréer comme vu précédement
Renommer une table
La norme ne prévoyait rien si, pour une raison quelconque, on désire changer le nom d’une table (ce qui ne devrait normalement pas arriver mais bon.) Il faut faire comme précédemment...
  1. D’abord créer la nouvelle table en redéfinissant les anciennes colonnes à conserver et les nouvelles à rajouter : CREATE TABLE nouveau_nom ( nom_colonne1 definition_colonne1..., ...autres_definitions... CONSTRAINT nom_cle1 definition_cle1, ...autres_clefs... );  ; ce qui se fait dans SPIP avec sql_create('nouveau_nom', array('nom_colonne1'=>"definition_colonne1...", ...autres_definitions...), array('nom_cle1'=>"definition_cle1", ...autres_clefs..) );
  2. Puis recopier l’ancienne table dans la nouvelle. Pour cela, faire SELECT liste,des,colonnes INTO nouveau_nom FROM ancien_nom WHERE eventuel...; plutôt que INSERT nouveau_nom (liste,des,colonnes) SELECT liste,des,colonnes FROM ancien_nom WHERE eventuel...; (cela que cela induit une grosse charge sur le journal de transactions et peut être problématique avec les tables volumineuses car besoin du double de la taille de la table).
    Mais seule la seconde forme est possible avec l’API SPIP qui ne connait en fait que INSERT INTO ma_table (liste,de,colonnes) VALUES (les,valeurs,respectives) ; cependant on devrait [2] arriver à nos fins avec sql_insertq_multi('nouveau_nom', sql_allfetsel('liste,des,colonne', 'ancien_nom', 'where éventuel') );
  3. Enfin supprimer l’ancienne table... DROP TABLE ancienne_table Soit : sql_drop_table('ancienne_table');
Bon, il faut avouer que ce coup-ci la norme a évoluée en 1992 par l’ajout d’une commande de renommage : ALTER TABLE ancien_nom RENAME TO nouveau_nom; ; soit : sql_alter("TABLE ancien_nom RENAME TO nouveau_nom"); Ceci est reconnu par un certain nombre de SGBDR (mais pas toutes) dans leurs versions récentes : MySQL (3.22.16a/3.23.23) Oracle (8i) PosgreSQL (7.2 ?) SQLite (2.?)
Vider une table
Ici, presque tout le monde est d’accord : DELETE TABLE ma_table WHERE 1=1; ou mieux TRUNCATE ma_table (plus rapide et charge moins le journal des transactions !) Soit :
sql_delete('ma_table', '1=1');

SPIP avancé

On peut se demander si SPIP ne devrait pas fournir des fonctions qui vont bien pour ces cas. Non parce-qu’il ne s’agit pas d’opérations courantes et qu’elles doivent être mûrement réfléchies.

Cependant, de part son historique MySQL, SPIP reconnait et traduit par exemple ALTER TABLE ma_table MODIFY ma_colonne NouveauType; pour SQLite... mais pas pour PostgreSQL... Je pense que pour favoriser le portage vers le maximum de bases de données, il ne faut pas privilégier de dialecte mais se reposer uniquement sur le standard...

Revenons à nos moutons et voyons quelques plus SPIP fort intéressants.

Modifier une/un colonne/index
On sait comment faire de façon portable. Mais il est pénible de déclarer la/le nouvelle/nouvel colonne/index dans le fichier décrivant la structure de vos tables, puis de recopier cette définition dans le fichier d’administration du schéma de données... [3] La magie de SPIP est qu’avec maj_tables('ma_table'); toutes/tous les nouvelles/nouveaux colonnes/index de la table sont créés et il n’y a plus qu’à écrire les étapes suivantes : recopie de l’ancienne colonne vers la nouvelle ; et suppression de l’ancienne/ancien colonne/index.
Petit bémol cependant : il ne faut pas faire cela plusieurs fois sur la même table... En effet, dès la seconde fois, la structure de la base ayant changée, les requêtes de recopie et de suppression risquent d’échouer.
Petit plus sympathique : il est possible de faire ces ajouts de champs/clés pour plusieurs tables par un seul appel maj_tables(array('table1','table2',...));
Renommer une table
C’est encore plus pénible de se retaper la description d’une table juste pour en changer le nom. La magie de SPIP est qu’avec maj_tables('nouvelle_table'); on la crée ! Il ne reste plus qu’à recopier les données puis à supprimer l’ancienne table.
Même bémol : il ne faut pas changer plusieurs fois le nom de la même table ...car les requêtes de recopie et de suppression échoueront.
Petits plus sympas :
  • Il est possible de créer toutes les tables absentes sans même préciser leur nom avec creer_base();
  • Pour la suppression des tables, certains SGBDR permettent de ne pas reporter d’erreur si la table n’existe pas ; ce qui s’active en précisant sql_drop_table('ancienne_table', TRUE);

Voilà voilà.

Notes

[1Si votre éditeur en a un support déficient changez-en : c’est à lui de s’aligner sur les standards d’interopérabilité et non à vous de le subir... Ce n’est pas au prix de faux compromis que l’on fait avancer les choses...

[2Ceci n’a pas éncore été expérimenté...

[3Je sais que c’est vite fait et bien fait avec la magie du « copier-coller » mais double boulot quand il y a une erreur à corriger et quand même pénible quand il y a plusieurs champs n’est-ce pas ?

Discussion

Aucune discussion

Ajouter un commentaire

Avant de faire part d’un problème sur un plugin X, merci de lire ce qui suit :

  • Désactiver tous les plugins que vous ne voulez pas tester afin de vous assurer que le bug vient bien du plugin X. Cela vous évitera d’écrire sur le forum d’une contribution qui n’est finalement pas en cause.
  • Cherchez et notez les numéros de version de tout ce qui est en place au moment du test :
    • version de SPIP, en bas de la partie privée
    • version du plugin testé et des éventuels plugins nécessités
    • version de PHP (exec=info en partie privée)
    • version de MySQL / SQLite
  • Si votre problème concerne la partie publique de votre site, donnez une URL où le bug est visible, pour que les gens puissent voir par eux-mêmes.
  • En cas de page blanche, merci d’activer l’affichage des erreurs, et d’indiquer ensuite l’erreur qui apparaît.

Merci d’avance pour les personnes qui vous aideront !

Par ailleurs, n’oubliez pas que les contributeurs et contributrices ont une vie en dehors de SPIP.

Qui êtes-vous ?
[Se connecter]

Pour afficher votre trombine avec votre message, enregistrez-la d’abord sur gravatar.com (gratuit et indolore) et n’oubliez pas d’indiquer votre adresse e-mail ici.

Ajoutez votre commentaire ici

Ce champ accepte les raccourcis SPIP {{gras}} {italique} -*liste [texte->url] <quote> <code> et le code HTML <q> <del> <ins>. Pour créer des paragraphes, laissez simplement des lignes vides.

Ajouter un document

Suivre les commentaires : RSS 2.0 | Atom