Intégrer une table existante dans SPIP 3 avec la Fabrique Étude, méthodologie, plugins, objet éditorial

, par Matthieu Marcillaud

Pour un site de mode fraîchement migré en SPIP 3 (toujours en développement à l’heure de ces lignes), et pour qu’il soit encore plus à la mode, on m’a demandé de pouvoir gérer des tables SQL déjà présentes en base, depuis l’interface privée de SPIP, et avec quelques contraintes. Ce document a un objectif de transfert de connaissances et se veut pédagogique. Il est fortement recommandé d’avoir lu Chats 2 - SPIP 3 avant de poursuivre.

Cet article va raconter la migration d’une table SQL nommée « villes » (qui n’a rien à voir avec le plugin « géographie »). Cette table devra pouvoir être parcourue depuis l’interface privée, mais seule une ou deux colonnes seront éditables (la table a 15 colonnes), et il ne sera pas possible de créer de nouvelles villes.

Nous créerons une première base du plugin grâce à un autre plugin « La Fabrique » que je développe actuellement. La Fabrique génère les fichiers d’un plugin et peut générer un plugin contenant des objets éditoriaux. Bien sûr, le code produit n’est qu’une base de travail (certes fonctionnel) que l’on édite ensuite à sa sauce.

Il nous faudra migrer les données présentes dans la table, pour créer une colonne de clé primaire id_ville, déplacer les coordonnées géographiques sur le plugin GIS 3, et charger à l’installation du plugin les données des villes.

Copie des tables pour une utilisation en serveur local

Tout d’abord, puisque je travaille uniquement sur un serveur local, j’ai ajouté les tables SQL en question dans une base de données crée sous PHPmyAdmin. Pour cela, j’avais à disposition un export des tables et données au format SQL. En ligne de commande (sous Ubuntu) je charge les données dans la base en me connectant à mysql :

  1. cd repertoire/ou/est/l/export/sql
  2. mysql -u nom_utilisateur -p
  3. use nom_de_la_base
  4. source nom_du_fichier.sql

Télécharger

Ensuite, dans un SPIP3 local, je vais déclarer ma nouvelle base sur la page « Maintenance technique » de sorte que je peux boucler sur ces nouvelles données

  1. <BOUCLE_villes(nom_connecteur:VILLES){par insee}{pagination 10}>
  2. ...
  3. </BOUCLE_villes>

Télécharger

Fabriquer la base du plugin

Nous allons générer un premier jet de plugin en utilisant la Fabrique. Dans un premier temps sans trop rien de plus. Une fois le plugin activé, il faut se rendre sur Squelettes > La Fabrique. Si un plugin avait été construit auparavant, il faut aller réinitialiser le formulaire dans l’onglet prévu.

On peut alors remplir les informations relatives au plugin. Cette première page (en ayant rempli la partie « paquet ») suffit à générer des fichiers du plugin (bouton « Créer le plugin ») mais nous allons continuer et ajouter un objet éditorial.

L’objet éditorial ajouté, l’interface propose d’utiliser une table existante pour en déduire la structure des données notre objet éditorial. Ici, j’utilise la table « villes » sur le connecteur « source ».

Au retour du pré-chargement, les infos de l’objet ont été déduites si cela était possible, et au mieux de l’information disponible. Il faudra donc apporter pour chaque partie quelques corrections ou informations supplémentaires.

En complétant quelques parties, on peut ajouter des logos, gérer les champs (en plus ou en moins), les déplacer, leur attribuer des types de saisies. On peut trouver des logos par exemple sur http://thenounproject.com, qui m’a servi dans cet exemple. Les chaînes de langues sont pré-remplies également (en français) et on peut lancer une première création du plugin.

Une fois créé, on peut se rendre sur la page d’administration des plugins pour l’activer et le tester.


Dans édition > villes on découvre la liste, vide, des villes. Et l’on peut alors créer une nouvelle ville. Évidemment, il faut avoir ajouté des saisies dans certains champs de notre objet dans la Fabrique, sinon le formulaire d’édition sera vide. Pour les tests, j’avais créé 2 champs en plus de ceux de la table d’origine : nom et description.


À la validation du formulaire, on obtient cependant un joli message d’erreur.

Cela signifie, dans notre cas qu’il y a eu une erreur SQL au moment de l’insertion. Un tour dans les logs nous indique l’erreur :

  1. Apr 05 19:33:14 127.0.0.1 (pid 11008) :Pri:ERREUR: | 0 : 23000 | 1 : 19 | 2 : spip_villes.insee may not be NULL - INSERT INTO spip_villes (maj) VALUES (datetime('now'))

On comprend que la description SQL des champs de la table d’origine ne correspond pas tout à fait au fonctionnement de SPIP. En effet, SPIP crée d’abord une ligne vide dans un objet et obtient ainsi un identifiant de ligne (ici un id_ville) pour insérer ensuite les valeurs soumises par le formulaire d’édition. Or dans ce cas, des champs ne tolèrent pas d’être « NULL » mais n’ont pas de valeur par défaut de définis. Il nous faut donc, pour chaque champ dans ce cas, ajouter cette valeur par défaut, ce qui revient le plus souvent à ajouter « DEFAULT ’’ » dans l’expression SQL du champ

On retourne donc dans la fabrique pour le faire, on recrée le plugin, on désinstalle puis réinstalle notre plugin dans admin plugin, puis on reteste.
Cette fois l’insertion se passe bien.


Peupler notre plugin avec les données de la table d’origine

Tout ceci est bien joli, mais dans notre cas, ce qui nous intéresse est de peupler notre table de ville avec un contenu pré-défini, à l’installation du plugin. Le contenu se trouve actuellement dans la table source ayant servi de base de déclaration de notre objet. Une fois peuplé, il faut afficher les champs, mais ne mettre que la description éditable (le reste étant figé).

La Fabrique ne gère pas depuis le formulaire de création du plugin l’insertion de ces données, mais propose sur la page ?exec=fabrique_peuple un outil pour se faciliter cette tâche.

Cet outil crée un ou deux fichiers qui seront à placer dans le répertoire base/ du plugin que l’on a créé. Il faut également ajouter l’appel de la fonction d’insertion dans le fichier d’administration lors de la création de la table, avec :

  1. $maj['create'] = array(array('maj_tables', array('spip_villes')));
  2. include_spip('base/importer_spip_villes');
  3. $maj['create'][] = array('importer_spip_villes');

Télécharger

Cette fonction d’import gère la reprise de l’insertion des données en cas de timeout (trop de temps utilisé par rapport à ce qui est autorisé dans la configuration de PHP). Très utile pour insérer ces 36000 lignes en SQLite notamment.

Du coup, nous pouvons voir sur la liste des villes que le compte est bon, mais que… nous n’avons pas correctement redéclaré le champ servant au calcul du titre. Je l’avais déclaré sur la colonne « nom » pour tester, colonne que j’avais créé, mais l’insertion insère les noms de nos villes dans une autre colonne. Il nous faut donc :

  • corriger soit directement notre plugin
  • soit en passant par la Fabrique, modifier les colonnes, recréer le plugin, remettre les fichiers d’imports, désinstaller et réinstaller notre plugin (réinstallation uniquement utile si une colonne a été ajoutée ou retirée, ce qui va être le cas car je vais supprimer la colonne « nom »).
Mettre le bon nom de colonne de titre

Les noms de ville s’affichent bien, mais il y a un problème de charset. Les données de la table d’origine ne semblent pas au bon format. Nous allons donc modifier notre script d’import pour corriger les caractères au passage et tester sur un panel d’insertion plus petit pour aller plus vite.

On choisit dans la fonction importer_spip_villes() du fichier du même nom de ne prendre que 100 villes, grâce à la fonction array_slice() :

  1. list($cles, $valeurs) = donnees_spip_villes();
  2. $valeurs = array_slice($valeurs, 0, 100);

Télécharger

Et l’on teste un utf8_encode() un peu plus loin :

  1. - $i[ $correspondances[$cle] ] = $valeur;
  2. + $i[ $correspondances[$cle] ] = utf8_encode($valeur);

Télécharger

Une fois désinstallé et réinstallé le plugin, on obtient les bons accents (coup de bol !).

On peut donc enlever la limitation à 100 villes, et réinstaller le plugin complètement.

Perdre le moins de modifications possibles entre chaque recréation de notre plugin

Au fur et à mesure de ces travaux, je constate qu’il est effectivement embêtant de devoir remettre les modifications apportées au plugin après sa création, à chaque fois qu’on le recrée avec la Fabrique (comme les fichiers d’import de ville et l’ajout de notre fonction dans $maj).

Après 3 jours de réflexions, j’opte pour un compromis qui me satisfait pour le moment :

  • à chaque fois que l’on crée le plugin, une copie de l’ancien est sauvegardée et la Fabrique génère un « diff » entre les répertoires. Si des fichiers manquent dans le nouveau plugin, une alerte est levée et on affiche la liste de ces fichiers absents bien visibles après la création. Ainsi, si c’est un oubli de les remettre dans le nouveau plugin, on peut les récupérer dans la sauvegarde.
  • des points d’insertions sont ajoutés où l’on peut intégrer son propre code, rempli depuis l’interface de création du plugin. Évidemment, il faut que ce code soit valide, la Fabrique ne peut pas le vérifier !
  • des points d’exécution de scripts sont ajoutés où l’on peut faire exécuter du code (via eval()) que l’on rempli depuis l’interface également. Ce code ne sera exécuté uniquement si l’on est webmestre (sinon c’est potentiellement très dangereux). Ainsi, on peut, après la création du plugin, déplacer des fichiers qui étaient présents dans la sauvegarde.

Nous ajoutons donc, dans la partie insertions le code d’insertion des données de la ville, et dans la partie script, la recopie des fichiers allant avec :


Le code de recopie est donc assez simple :

  1. foreach(array(
  2. 'base/importer_spip_villes.php',
  3. 'base/importer_spip_villes_donnees.gz',
  4. ) as $f) {
  5. copy($destination_ancien_plugin . $f, $destination_plugin . $f);
  6. }

Télécharger

Migrer à l’installation les données géographique des villes vers le plugin GIS

L’ancienne table de villes contient des données géographiques que l’on va migrer vers des points du plugin GIS. Chaque ville sera donc liée à 1 point géographique. Il va donc nous falloir modifier le script d’import qui avait été automatiquement créé, pour gérer notre spécificité.

Pour s’en sortir et tester, on remet déjà une liste courte de villes, et non les 37000. En prendre 10 suffira largement. On remet donc le array_slice sur le fichier d’import :

  1. $valeurs = array_slice($valeurs, 0, 10);

Sur le principe, il faudra :

  • créer un point géographique pour chaque ville (table spip_gis) et récupérer d’identifiant
  • créer la ville et récupérer son identifiant (table spip_villes)
  • ajouter le lien entre les 2 (table spip_gis_liens)

Du coup, on ne peut plus utiliser la fonction d’insertions multiples (sql_instertq_multi), puisqu’il va falloir agir ligne par ligne.

Commençons pas ajouter le plugin GIS dans les dépendances du plugin.
Pour cela, on ajoute dans la Fabrique, paquet > insertions de code > paquet.xml la ligne :

  1. <necessite nom="gis" compatibilite="[3.2.0;]" />

Nous n’avons plus besoin des colonnes « lon » et « lng » de la table « spip_villes » que l’on supprime alors dans la Fabrique. On observe un peu les tables de GIS (dans base/gis.php du plugin GIS). On en déduit comment doit se réaliser notre migration. On fait un premier jet de code.

  1. // inserer les donnees en base.
  2. $nb_deja_la = sql_countsel($table);
  3. // ne pas reimporter ceux deja la (en cas de timeout)
  4. $inserts = array_slice($inserts, $nb_deja_la);
  5. foreach ($inserts as $i) {
  6.  
  7. // point gis
  8. $id_gis = sql_insertq("spip_gis", array(
  9. 'titre' => $i['nom_ville_min'],
  10. 'lat' => $i['lat'],
  11. 'lon' => $i['lng'],
  12. 'ville' => $i['nom_ville_min'],
  13. 'code_postal' => $i['zip'],
  14. ));
  15.  
  16. // ville
  17. unset($i['lat'], $i['lng']);
  18. $id_ville = sql_insertq($table, $i);
  19.  
  20. // liaison gis / ville
  21. sql_insertq("spip_gis_liens", array(
  22. 'id_gis' => $id_gis,
  23. 'objet' => 'ville',
  24. 'id_objet' => $id_ville,
  25. ));
  26.  
  27. if (time() >= _TIME_OUT) { return; }
  28. }

Télécharger

On installe désinstalle notre plugin, ainsi que GIS s’il était là, puis le réinstalle.
On va dans la configuration GIS dire qu’on a besoin de GIS sur les villes, mettre un générateur de carte et une position par défaut (on fera cela dans une fonction d’installation ensuite pour automatiser), et on se rend sur une ville pour voir.

On voit entre autres que… la ville française est placée quelque part en Afrique. Quelque chose cloche ! Il faut comparer nos données en base avec la table originale pour savoir si on a bien remis les mêmes valeurs.

Dans la table source, j’ai :

lnglat
45.979851 5.33689

Dans la table de GIS, j’ai :

lonlat
45.979851 5.33689

Manifestement, ce sont bien les mêmes données. Le problème est ailleurs.
Je vais donc créer un nouveau point dans GIS pour comparer. Au hasard, je prends Grenoble, qui ne semble pas très loin en coordonnées.

Et là je lis dans ma table

lonlat
5.745458496026258 45.17920020811579

Soit l’inverse. Ce qui veut dire que les coordonnées sont inversées dans la table source ! Magnifique. Plutôt que d’adapter notre script, on inverse les colonnes dans la table source (via phpmyadmin ou sqlite manager selon), et on la réexporte depuis la Fabrique. On remplace simplement le fichier compressé de données ensuite et on inverse les colonnes dans le tableau $cles du fichier importer_spip_villes.php. On retourne désinstaller, réinstaller GIS et notre plugin, on reconfigure GIS, puis on va tester une ville. Aussitôt, c’est bien mieux placé.

Configurer GIS au démarrage de notre plugin

Puisque notre plugin ajoute des points sur les villes, autant activer les villes sur GIS dès l’installation de notre plugin. Pour cela, on génère un tableau de la configuration de GIS (une fois configuré), en écrivant dans un quelconque squelette (disons test.html que l’on appellera ensuite par ?page=test) :

  1. <?php
  2. include_spip('inc/config');
  3. var_export(lire_config('gis'));
  4. ?>

Télécharger

Ce qui nous donne (ici) :

  1. array (
  2. 'lat' => '46.50764083069263',
  3. 'lon' => '0.32931686417954',
  4. 'zoom' => '6',
  5. 'geocoder' => '',
  6. 'adresse' => '',
  7. 'geolocaliser_user_html5' => '',
  8. 'api_key_cloudmade' => '',
  9. 'api_key_google' => '',
  10. 'api_key_yandex' => '',
  11. 'gis_objets' =>
  12. array (
  13. 0 => 'spip_villes',
  14. 1 => '',
  15. ),
  16. 'api' => 'openlayers',
  17. 'maptype' => 'ROAD',
  18. )

Télécharger

On ne garde que les infos que l’on veut proposer :

  1. array (
  2. 'lat' => '46.50764083069263',
  3. 'lon' => '0.32931686417954',
  4. 'zoom' => '6',
  5. 'gis_objets' =>
  6. array( 'spip_villes'),
  7. 'api' => 'openlayers',
  8. )

Télécharger

On s’arrangera pour qu’à l’installation, si GIS est déjà configuré, que l’on n’écrase pas sa configuration, mais que l’on ajoute simplement la ville. On utilisera pour cela la fonction array merge de PHP.

Et on ajoute au code inséré par la Fabrique dans l’installation notre modification de la configuration de GIS, en créant une fonction (qui se retrouvera l’intérieur de la fonction de mise à jour, mais PHP tolère cela).

  1. include_spip('base/importer_spip_villes');
  2. $maj['create'][] = array('importer_spip_villes');
  3. // configurer GIS sans toucher aux valeurs deja configurees si c'est le cas
  4. function configurer_gis_ville() {
  5. $conf = lire_config('gis', array());
  6. $conf = array_merge(array(
  7. 'lat' => '46.50764083069263',
  8. 'lon' => '0.32931686417954',
  9. 'zoom' => '6',
  10. 'gis_objets' => array(),
  11. 'api' => 'openlayers',
  12. ), $conf);
  13. $conf['gis_objets'] = array_merge($conf['gis_objets'], array('spip_villes'));
  14. ecrire_config('gis', $conf);
  15. }
  16. $maj['create'][] = array('configurer_gis_ville');

Télécharger

Améliorer le chargement des points

On peut maintenant améliorer un peu notre fonction de peuplement en :

  • encapsulant dans une transaction l’ensemble des requêtes si le moteur le préfère. C’est en fait un hack pas terrible pour SQLite, qui permet de ne pas lui faire générer de verrous pour chaque requête, ce qui lui prend du temps. Du coup, il en génère un pour un groupe de requêtes cohérent.
  • en testant qu’un point GIS identique n’existe pas déjà avant d’en créer un nouveau
  • en utilisant une fonction interne de SPIP pour les liaisons entre gis et la ville.

Cela donne :

  1. include_spip('action/editer_liens');
  2. foreach ($inserts as $i) {
  3.  
  4. // eviter des creation / suppression de verrous pour SQLite
  5. if (sql_preferer_transaction()) {
  6. sql_demarrer_transaction();
  7. }
  8.  
  9. // un point existe deja ?
  10. if (!$id_gis = sql_getfetsel("id_gis", "spip_gis", array(
  11. 'titre = ' . sql_quote($i['nom_ville_min']),
  12. 'lat = ' . sql_quote($i['lat']),
  13. 'lon = ' . sql_quote($i['lng'])
  14. ))) {
  15. // sinon on le cree...
  16. $id_gis = sql_insertq("spip_gis", array(
  17. 'titre' => $i['nom_ville_min'],
  18. 'lat' => $i['lat'],
  19. 'lon' => $i['lng'],
  20. 'ville' => $i['nom_ville_min'],
  21. 'code_postal' => $i['zip'],
  22. ));
  23. }
  24.  
  25. // ville
  26. unset($i['lat'], $i['lng']);
  27. $id_ville = sql_insertq($table, $i);
  28.  
  29. // liaison gis / ville
  30. objet_associer(array('gis'=>$id_gis), array('ville'=>$id_ville));
  31.  
  32. if (sql_preferer_transaction()) {
  33. sql_terminer_transaction();
  34. }
  35.  
  36. if (time() >= _TIME_OUT) { return; }
  37. }

Télécharger

Supprimer les informations liées à GIS à la désinstallation de notre plugin

Comme on l’a fait pour l’installation, il faut également supprimer nos informations à la désinstallation :

  • la table « spip_villes » dans la config de GIS
  • les liaisons « spip_gis_liens » avec les villes
  • les points GIS qui étaient liés à une ville mais qui ne sont plus utilisés.

On insère notre code dans la partie prévue par la Fabrique (insérer > administrations > vider_table).

  1. // enlever les villes de la configuration de GIS
  2. $conf = lire_config('gis');
  3. $conf['gis_objets'] = array_diff($conf['gis_objets'], array('spip_villes'));
  4. ecrire_config('gis', $conf);
  5. // recuperer tous les points lies a une ville
  6. $ids_gis = sql_allfetsel('id_gis', 'spip_gis_liens', 'objet=' . sql_quote('ville'));
  7. $ids_gis = array_map('array_shift', $ids_gis);
  8. // supprimer les liaisons avec une ville
  9. sql_delete('spip_gis_liens', 'objet=' . sql_quote('ville'));
  10. // trouver les points encore utilises
  11. if ($ids_gis_utilises = sql_allfetsel('id_gis', 'spip_gis_liens', sql_in('id_gis', $ids_gis))) {
  12. $ids_gis_utilises = array_map('array_shift', $ids_gis_utilises);
  13. // les points a supprimer sont les autres !
  14. $ids_gis = array_diff($ids_gis, $ids_gis_utilises);
  15. }
  16. // on supprime les points GIS non utilises
  17. sql_delete('spip_gis', sql_in('id_gis', $ids_gis));

Télécharger

Fin du premier épisode

Bien, nous avons avec ceci à peu près migré correctement une table en l’intégrant dans un objet éditorial géré par SPIP. Il y a eu depuis quelques petits changements dans les codes qui n’ont pas lieu d’être commentés ici. Par exemple le script d’importation de données indique le nombre d’éléments qui sont insérés s’il doit se relancer en cas de timeout.

Dans le prochain article nous allons ajouter à notre plugin un autre objet avec d’autres contraintes.

Coup de pouce

Pour me maintenir en éveil et en pleine forme physique et mentale, vous pouvez me faire le cadeau d'un jus de fruit pressé.

En plus de m'hydrater, de m'offrir une alimentation saine crudivore et frugivore, cela peut aussi me motiver à produire d'autres documentations ou peut-être à prendre des vacances ! :)

Vous pouvez également me « Flattrer » si vous utilisez le service en ligne très malin Flattr de microdonations qui permet d'allouer un budget mensuel à des créateurs de contenu. Votre budget est partagé chaque mois entre toutes les personnes que vous avez flattées ce mois là.