Accés SPIP aux tables non-SPIP et jointures

Comment accéder avec des boucles SPIP à des tables non SPIP dans un squelette SPIP...

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 2e 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 2e 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

Notes

[1La page des nouveautés de SPIP 1.9 annonce cette possibilité

Détection automatique de tables SQL et de jointures

Dans un squelette comportant BOUCLE_a(xxx), la table xxx peut être n’importe quelle table SQL connue du serveur SQL. SPIP demandera alors au serveur SQL de décrire cette table, ce qui lui permettra de compiler le squelette en interprétant toute balise #NOM comme un accès au champ xxx.nom s’il existe. Ces champs sont également repérés dans les critères des boucles.

Dans un squelette comportant BOUCLE_a(table table1 ... tablen), les tables supplémentaires seront vues comme des candidates à une jointure, à travers les champs homonymes. Des exemples plus concrets seront donnés dans la documentation.

[2A partir de SPIP1.9.3, la procédure d’installation de SPIP permettra de déclarer de manière interactive les tables externes auxquelles SPIP pourra se connecter, y compris si elles sont dans une autre base de donnée ou sur un autre serveur. En attendant la doc officielle pour la sortie de la 1.9.3, on pourra consulter la page d’annonce de ces nouveautés

[3Wikipedia définit ainsi la jointure :

En gestion de base de données relationnelle, une jointure est une combinaison des enregistrements de deux tables disposant de valeurs correspondantes dans un champ determiné de chaque table (souvent ayant le même nom dans les deux).

La table résultante est construite temporairement en fonction des prédicats spécifiés dans la requête. En SQL, ils peuvent être définis par la clause WHERE.

Discussion

15 discussions

  • Philippe

    Bonjour
    Je n’arrive pas à reproduire l’exemple des pommes ...
    Sous spip 3, j’ai un fichier config/mes_options.php avec

    $tables_principales['pommes']['field']['variete']= "varchar(10)";
    $tables_principales['pommes']['key']['PRIMARY KEY']= "variete";
    
    $tables_principales['gardiens']['field']['id_gardien']= "int auto_increment";
    $tables_principales['gardiens']['field']['nom']= "varchar(10)";
    $tables_principales['gardiens']['field']['prenom']= "varchar(10)";
    ....
    $tables_principales['gardiens']['key']['PRIMARY KEY']= "id_gardien";

    et les tables qui vont bien, et la boucle :

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

    me répond :

    Erreur SQL 1054
    Champ ’pommes.id_gardien’ inconnu dans on clause
    SELECT pommes.variete, L1.nom, L1.prenom FROM pommes AS pommes INNER JOIN gardiens AS L1 ON ( L1.id_gardien = pommes.id_gardien ) WHERE (pommes.variete = NULL)

    et je ne comprends pas ...

    Répondre à ce message

  • 1

    Salut,

    il faudrait sûrement revoir un peu le paragraphe « $table_des_jointures » pour deux raisons :
    * la table s’appelle $tables_jointures (http://programmer.spip.org/Declarations-de-jointures)
    * pour afficher la balise #CHAMP_TABLE_2, il faut forcer la jointure, par (TABLE1 spip_table2)... (dernier paragraphe de http://programmer.spip.org/Forcer-des-jointures)

    Du coup, le paragraphe serait plutôt quelque chose comme :

    $tables_jointures :

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

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

    SPIP fera la liaison entre les 2 tables, à condition évidemment qu’elles aient une colonne de nom identique, par exemple « id_rubrique » toutes les deux.

    Attention, la jointure automatique fonctionne seulement pour les critères. Pour utiliser les champs de la table 2 comme balises, il faut forcer la jointure :

    <boucle_bb(TABLE1 spip_table2)>
    	#CHAMP_TABLE2
    </boucle_bb>
    • C’est fait Sévero, j’ai intégré ta proposition d’amélioration en la développant un peu avec notamment un lien vers ton nouvel article. Cette doc est une belle issue je trouve après tes difficultés avec les jointures. :-)

    Répondre à ce message

  • 1

    Bonjour,

    J’utilise une ancienne version de spip 1.8.2 et j’essaie de lier mes tables externes à spip hélas sans succès.

    J’ai déclaré ma nouvelle table dans un nouveau fichier php3, ma table est ajoutée à la variable table_des_tables.

    Néanmoins lorsque j’essaie de faire une boucle sur cette table j’ai une erreur :

    Erreur(s) dans le squelette

    (ag_vl)
    Erreur MySQL
    SELECT mytable. FROM mytable AS mytable
    Erreur de syntaxe près de ’FROM mytable AS mytable’ à la ligne 2
    ,

    Est-ce que pour cette version d’autres étapes sont nécessaires ?

    Merci.

    • oulala, il n’y a pas vraiment de raison de rester avec une aussi vieille version !

    Répondre à ce message

  • Hello,

    Si la table externe s’appelle ma_table (sans s donc), et qu’on la declare comme cela :
    $tables[ma_table][titre]=1 ;

    on va obtenir une erreur de requet sur ma_tables (spip rajoute le s tout seul a priori). Donc, la bonne declaration devient
    $tables[ma_tables][titre]=1 ;

    avec un s cette fois ci que spip retirera lui meme. Il semble également que les tables supplémentaires que l’on souhaite indexer doivent avoir au moins 1 index

    Chag

    Répondre à ce message

  • 2

    Bonjour,

    J’interroge depuis une boucle spip une base postgres externe. Tout marche nickel, à part qu’il m’affiche un warning disgracieux :
    « Warning : pg_query() [function.pg-query] : Query failed : ERROR : relation "spip_meta" does not exist in
    /spip/ecrire/req/pg.php on line 146 »

    Dans une autre fenêtre, j’ai par ailleurs un
    « Erreur SQL
    SELECT valeur FROM spip_meta WHERE nom=’charset_sql_connexion’
    0 »

    J’utilise "spip 2.0.7". Merci à toute bonne ame qui pourrait éclairer ma lanterne.

    Edit : et ma version de posgresql est 7.4.23. Ce pourrait bien être ca le problème...

    • Non, en fait, la machine sur laquelle tourne le serveur internet est en 8.3.7...

    • Bon, j’ai trouvé la solution à mon problème.

      Le truc, c’est que comme la base à laquelle je voulais acceder était sur une autre machine, et que la machine sur laquelle était hébergée la base spip n’avait pas de base postgresql, le fichier d’install (inc/install.php) ne me proposait pas de choisir entre différents types de bd (mysql versus postgresql).

      Du coup, j’avais écris un fichier de connection à la main en patchant celui généré par spip (nommé connect.php). Et manifestement, il n’était pas tout à fait bon.

      Donc, à grand coup de hache, et de positionnement de variables en dur dans install.php et pg.php, j’ai finalement réussit à lui faire me générer un fichier de connection propre. Et la seule différence était l’absence de la ligne :

      $GLOBALS[’spip_connect_version’] = 0.7 ;

      Une fois enlevé cette instruction de mon fichier de connection, plus de Warning disgracieux.
      Vous savez quoi, je suis content.

    Répondre à ce message

  • J’ai complété l’article avec une note pour permettre la recherche dans des tables externes.

    Répondre à ce message

  • Problème résolu.

    En effet, il fallait spécifier le nom de la table (amo_articles) avant la colonne (titre) :

    {par amo_articles.titre}

    Merci beaucoup.

    Répondre à ce message

  • 1

    sisi ça ne marche pas.
    le probleme vient peut-être d’ailleurs.

        * Erreur(s) dans le squelette
              o Erreur sur le site, {par titre inverse} BOUCLE_articleasso
    
    	<BOUCLE_rubriqueamo(amo_rubriques) {id_rubrique=14} >
    		<h2> #TITRE  </h2>
    		<br> #TEXTE
    		<B_articleamo>
    		<BOUCLE_articleamo(amo_articles) {id_rubrique} {statut='publie'}   {par titre}     {inverse} >
    			 #TITRE 
    		 	#TEXTE
    		</BOUCLE_articleamo>
    		</B_articleamo>
    	</BOUCLE_rubriqueamo>

    Notons que « par titre » et « inverse » se retrouve dans la même accolade de l’affichage du debug.

    Quand je ne mets pas
     {par titre}     {inverse} l’affichage des articles se fait par numéros.

    • tu ne nous dis pas tout :
      le message d’erreur dénonce « BOUCLE_articleasso » alors que tes boucles s’appellent « BOUCLE_...amo ».

      sinon, essaye {par amo_articles.titre}

    Répondre à ce message

  • 2

    Pour prolonger votre contribution, comment peut-on faire en sorte qu’un des champ d’une table externe puisse être utilisé comme un critère dans une boucle genre :

    <BOUCLE_naboucle(MATABLE {nom_dun_champ=valeur}>

    Pour l’instant, j’ai comme réponse : erreur critère inconnu.
    Quelque chose à déclarer quelque part ? (SPIP 192)

    • Le problème continue avec la version SPIP 2.0.3 qui affiche « Erreur(s) dans le squelette ».

      Quelqu’un a-t-il trouver une solution ?

    • sisi ça marche.
      le probleme doit venir d’ailleurs.

      dans ton précédent message, il manque la parenthese fermante apres le nom de table.
      essaye avec un squelette qui contient juste la boucle (entière)
      et si jamais encore pb, indique nous cette boucle

    Répondre à ce message

  • 1
    Pierre Bourgeois

    Je travaille avec 2 sites sous SPIP 1 beta2. Le deuxième site récupère les données du premier site grâce à la fonctionnalité des tables externes. Ça fonctionne super bien sauf pour les logo_article. J’ai essayé avec et sans filtre. Quelqu’un a une astuce pour contourner ce problème.

    Merci de votre,

    Pierre

    • Tu parles d’une utilisation de la balise #LOGO_ARTICLE qui référence un article issu d’une table externe ?

    Répondre à ce message

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