Accés SPIP aux tables non-SPIP et jointures

Petit rappel sur SPIP

Depuis la version 1.9 de SPIP il est possible d’accéder avec des boucles SPIP à des tables non SPIP. [1]

Cette possibilité trop méconnue offre des grandes possibilités. SPIP peut devenir le moteur de mise en page de nombreuses applications.

Les manières de faire ont varié avec les versions de SPIP aussi, cette doc risque d’être un peu confuse. C’est toutefois une bonne introduction, et vous trouverez des liens pour plus de technique à la fin de cet article.

Ces tables externes, quelles sont elles ?

Je vois au moins deux utilisations principales à l’accès aux tables extérieures :

Interroger une application extérieure

Soit pour interroger des tables venant d’une application extérieure pré-existant, dont on veut accéder au contenu à partir d’un squelette SPIP. On peut par exemple utiliser les tables MySQL de n’importe quel script de forum à partir d’un squelette SPIP, pour afficher les derniers messages du forum quelque part à l’intérieur de ce squelette. On pourrait également redéfinir complètement le forum en SPIP et abandonner le script existant...

Interroger des tables que l’on crée

Soit pour interroger des tables créées sur mesure adaptées à un contextes donné, pour lesquelles on veut des champs différents de ceux proposés en standard par SPIP. A la place de surtitre, titre, soustitre, chapo et texte, par exemple, on peut préférer une table « POMMES » proposant les champs : « variete », « couleur », « gout », « origine », « date_degustation » et « indications ».

Par exemple

variete = Belle Fille de Salin
couleur = rouge-jaune
gout = parfum d’amande amère
origine = jura
date_degustation = novembre 2006
indications = à croquer, en jus ou à cuire. N’est pas sensible au gel.

(Saviez vous qu’en 1900, il existait plus de mille variétés de pommes en France ?)

On peut ainsi créer des applications entières utilisant SPIP comme moteur d’affichage.

Certains plugins qui créent leurs propres tables, avec des données extraites dans les squelettes, sont des exemples de mise en œuvre.

Contraintes

Le traitement entièrement automatique ne requière aucune formalité préalable de la part du créateur de squelette. SPIP a en effet acquis la capacité à découvrir lui-même les particularités de ces tables qui lui sont inconnues et de travailler avec elles.

Il y a quelques contraintes à respecter pour que SPIP interroge les tables inconnues qui lui sont proposées :

-  Ces tables doivent être situées dans la même base de donnée que les tables SPIP.
Il est également possible de boucler sur des tables situées dans une autre base de donnée, et même sur un autre serveur SQL, au prix d’un formalisme spécial et, pour SPIP1.9.2x au prix d’une pré-déclaration en PHP du nom, de l’adresse et du mot de passe de la base. [2]

-  Euh... c’est tout ?

Pour l’instant, oui. Il y a quelques autres petites contraintes mais elles seront exposées plus loin.

Interroger directement une table

Il est possible d’interroger une table externe directement via un classique boucle SPIP.

On bénéficie alors à l’intérieur de la boucle
-  de critères portant sur tous les champs
-  des critères de tris utilisables sur tous les champs
-  des critères standards par hasard, inverse, etc...
-  de l’accés à tous les champs de la table

Exemple :

<ul>
<BOUCLE_mapomme(POMMES){par date_degustation}{inverse}{couleur=rouge}>
<li>Le #DATE_DEGUSTATION, la #VARIETE a été dégustée</li>
</BOUCLE_mapomme>
</ul>

Lever les ambiguités
-  en cas d’ambiguité sur le nom d’un champ (par exemple : titre) il faut le préfixer par le « nom de la table » et « point » pour y accéder : {matable.couleur = rouge}

Contrainte
-  Il n’est toutefois PAS possible de récupérer par #NOM_DU_CHAMP la valeur d’un champ si le nom SQL du champ est composé de minuscules mélangées à des majuscules : évitez le « CamelCase » et préférez le « tout-minuscule » !

doublons
-  dans la version SPIP 1.9.2 on ne peut pas utiliser le critère {doublons}.
-  Depuis la version 2., il est possible de bénéficier du puissant mécanisme du critère {doublons} sur toutes les tables dont un champ est de type PRIMARY KEY. (A défaut, spip dénonce l’absence d’index sur la table).

Interroger directement deux tables avec jointure

Parfois on a besoin de joindre deux tables pour faire le lien entre leurs éléments. C’est ce qu’on appelle une jointure [3].

Par exemple, j’ai une table « pomme » et une table « gardiens » qui référencent les propriétaires de vergers qui se sont engagés à préserver certaines variétés rustiques de pommes. (C’est un peu ce que promeut l’association Kokopelli, pour la sauvegarde de la diversité potagère).

Cette table contient les champs « nom », « prenom », « adresse », « codepostal » « ville », « telephone », « variete », « quantite ».

Cette table contient des informations du genre Paul Lafarge, habitant La Petite Corcellie, 71190 La Chapelle, dispose de 10 pommiers de variété « Belle Fille de Salin »

C’est le champ « variete » qui fait le lien entre les 2 tables.

<BOUCLE_trouve(pommes gardiens){variete}>
La pomme #VARIETE est gardée par #NOM #PRENOM
</BOUCLE_trouve>

Contraintes

Il est nécessaire que le champ (ici : variete) sur lequel se fait la jointure soit clé primaire de la table.

Cas de plus de deux jointures

Ce qui est valable pour 2 tables l’est également pour 3 ou plus : SPIP fait les jointures automatiquement dés qu’il peut.

Petite complication : surtout à partir de 3 tables de jointures, l’ordre des tables est important et le résultat dépendra de l’ordre. Donc faire des essais sur l’ordre jusqu’à ce que le résultat escompté s’affiche.

Indice
-  Lorsque les critères explicités portent sur une seule table, il semble qu’il faille mettre cette table en dernier dans la liste des tables de la boucle.

Cela permet de faire des tas de choses.

Aller plus loin : déclaration des jointures

Lorsque les jointures automagiques ne suffisent plus, il est possible d’aider spip en lui indiquant quelles jointures rechercher.

Ainsi si dans la table spip_trucs, il y à la fois un champ id_auteur qui référence un auteur (enregsitrement de la table standard spip_auteurs), ET un champ id_traducteur qui référence AUSSI un auteur (autre enregistrement de spip_auteurs), alors il faut faire une déclaration explicite : il faut explicitement déclarer (dans le mes_options.php par exemple) les champs spip_trucs.id_auteur et spip_trucs.id_traducteur comme candidats à une jointure :

$tables_principales['spip_trucs']['join'] =
      array("id_auteur" => "id_auteur",
            "id_traducteur" => "id_auteur"); 

Nommage des Tables Externes

Recommandation de nommage

Il est trés fortement recommandé de prendre des noms de table au PLURIEL, et terminées par un ’s’, de même que les tables pour les objets standards de spip sont au pluriel : spip_articles, spip_breves...

Ainsi, préférez FRUITS à FRUIT.

En effet, certains mécanismes standards de spip font des opérations d’accord automatiques, qui devront également pouvoir s’appliquer correctement si vous voulez bénéficier de ces mécanismes pour vos tables externes.

C’est le cas notamment pour la recherche intégrée (voir plus bas).

Si vos tables ne se conforment pas à cette convention, il est parfois utile alors d’utiliser le pipeline ’declarer_tables_objets_surnoms’ mais ces petits noms ne sont pas utilisés dans toutes les situations par le noyau de spip, et donc ce ne sera pas toujours suffisant (la recherche intégrée notamment).

Noms complets et noms résumés

Jusqu’à SPIP 2.0.10, lorsqu’une table, même externe a le même préfixe qu’une table SPIP, alors il est possible de la référencer sans le préfixe à l’intérieur d’une boucle. On écrira alors son nom en majuscules, sans le préfixe : c’est son nom « logique » tandis que le nom en minuscule et avec le préfixe, c’est le nom « physique ».

Par défaut, le préfixe est « spip_ ».
Dans une telle configuration, si on a dans la base de donnée une table « spip_mesfruits » on pourra la référencer directement avec « MESFRUITS » dans une boucle, exemple :

<BOUCLE_ext(MESFRUITS)>...</BOUCLE_ext>
( et dans une jointure idem. )

A partir de SPIP 2.1, c’est plus strict :
-  sans déclaration : il faut le nom exact de la table, casse comprise, c’est à dire ici, peut-être « MesPommes » ou « spip_mespommes »
-  avec déclaration (tables_principales + tables_interfaces ’table_des_tables’),
il faut le nom de l’alias donné dans table_des_tables (l’écriture sans déclaration marche bien sûr également)

Utilisation de la variable $table_des_tables :

Cette variable sert à SPIP pour le nom des boucles.

Si la table « spip_toto » n’est pas déclarée avec $table_des_tables[’toto’]=’toto’ ; alors il sera impossible de faire <boucle_bb(TOTO) >, mais seulement <boucle_(spip_toto) >, comme expliqué précédemment, ce qui a l’inconvénient d’être dépendant du préfix de table de SPIP.

En le déclarant, SPIP gère donc le préfixe de table dans les noms des boucles, ce qui permet des plus génériques.

Tables SPIP standard

La manière normale de boucler sur les tables SPIP est d’utiliser leur nom résumé : ARTICLES, RUBRIQUES, AUTEURS, ... Il est cependant possible de boucler sur les tables spip normales en utilisant leur nom entier (avec préfixe) en minuscule :
<BOUCLE_tous(spip_articles)> ...</BOUCLE_tous>

Dans ce cas, SPIP ne fait pas les tests de statuts des articles et autres traitements standards qui simplifient l’utilisation des ARTICLES, RUBRIQUES et autres objets prédéfinis.

Plugins en rapport

Voir aussi :

-  le plugin TableData qui permet de visualiser et modifier dans la partie privée les champs de n’importe quelle table non SPIP.

En faire plus

On peut aussi en vouloir plus de SPIP, et décider d’aller plus loin. Pour cela, on peut utiliser un protocole de déclaration de bases de données externes, de tables externes et de jointures.

Cela permet par exemple d’intégrer les tables externes aux sauvegardes de la base de donnée réalisées par SPIP, ou cela permet de définir des jointures complexes. Attention ces formalismes ne sont pas parfaitement stabilisés, et en les utilisant on s’expose à refaire une partie du boulot lors d’une prochaine évolution de SPIP.

Emploi de $tables_jointures : la table des jointures

Voir aussi :
-  sur http;//programmer.spip.org : la doc $tables_jointures
-  sur ce site : Note sur les jointures entre tables

Cette table sert à SPIP pour relier automatiquement 2 tables, ce qui fait que si on fait une boucle

<boucle_bb(TABLE1){champ_table2}>
	...
</boucle_bb>

alors que champ_table2 n’est pas un champ de TABLE1, SPIP fera automatiquement la liaison entre les 2 tables, à condition évidemment qu’elles aient une colonne de nom identique, par exemple « id_rubrique » toutes les deux.

Attention, SPIP va chercher une 2^e table non mentionnée dans la liste des tables pour faire une jointure automatique avec, seulement lorsqu’il en a besoin pour un critère.

Pour utiliser des champs de la table 2 comme balises, il faut forcer la jointure en explicitant cette 2^e table dans la liste des tables de la boucle :

<boucle_bb(TABLE1 spip_table2)>
	#CHAMP_TABLE2
</boucle_bb>

Voir par exemple cette page du Carnet : MultiBase

Recherche dans une table externe

Un pipeline sert à spécifier quels champs sont intégrés dans une recherche.
http://programmer.spip.org/recherch...

Ce pipeline peut aussi être utilisé pour les tables externes mais attention :
si la table s’appelle « clients_actions » (avec un « s » donc, à la fin) il faut dans le pipeline rechercher_liste_des_champs déclarer les champs SANS LE S !! :

$tables[clients_action][titre]=1;

Sauvegardes des tables externes

PHP : Les tables déclarées par un plugin grace à declarer_tables_principales sont automatiquement sauvegardées dans le DUMP de SPIP.
Il est toutefois possible d’exclure certaines tables déclarées du DUMP au moyen du tableau global $EXPORT_tables_noexport et du pipeline : lister_tables_noexport (voir aussi http://doc.spip.org/@Declarer-et-aj...)

Doc technique plus complète
-  http://programmer.spip.org/declarer_tables_principales
-  http://programmer.spip.org/declarer_tables_interfaces,379
-  http://doc.spip.org/@Declarer-et-ajouter-des-tables
Et également :
-  http://marcimat.magraine.net/Creer-un-nouvel-objet-editorial