SPIP

[ar] [ast] [bg] [br] [ca] [co] [cpf] [cs] [da] [de] [en] [eo] [es] [eu] [fa] [fon] [fr] [gl] [id] [it] [ja] [lb] [nl] [oc] [pl] [pt] [ro] [ru] [sk] [sv] [tr] [uk] [vi] [zh] Espace de traduction

Télécharger

L’interface de SPIP avec SQL

Décembre 2008 — mis à jour le : Mai 2012

Toutes les versions de cet article :

Rappelons d’abord que le Structured Query Langage ne s’est standardisé que très progressivement, chaque implémentation comblant les lacunes de la spécification avec ses propres solutions. Les premières versions de SPIP ne connaissaient qu’une seule implémentation de SQL ce qui lui permettait d’en centraliser l’utilisation à travers une seule fonction (spip_query) dont l’unique argument était la requête. Le portage de SPIP sur différentes implémentations de SQL a imposé de renoncer à ce modèle ; cette fonction reste disponible par souci de compatibilité, mais son usage doit être désormais évité. On lui préférera le jeu de fonctions suivant, qui a de plus l’avantage de neutraliser la plupart des techniques d’attaque par injection de code.

Le premier portage de SPIP sur un autre serveur que MySQL3 a été réalisé pour le serveur PostGreSQL version 8.2. Il a été immédiatement suivi d’un double portage en SQLite 2 et 3. Ces portages se fondent sur le remplacement de la fonction spip_query par autant de fonctions que d’instructions SQL (select, update etc) ayant chacune autant d’arguments que l’instruction admet de clauses (where, limit etc), plus un argument optionnel précisant la base SQL. Les opérandes SQL (en particulier les dates et les nombres en hexadécimal) restent écrits en syntaxe MySQL, les fonctions d’interface se chargeant de les réécrire au besoin. Quelques fonctions supplémentaires sont proposées : des indispensables (l’accès aux lignes successives d’un résultat de select) et des abréviations (décompte, accès à une ligne qu’on sait unique etc). Des informations générales sont également fournies : alphabets utilisés, présence d’un LDAP etc.

Le double portage en SQLite n’a pas nécessité de réviser le jeu de fonctions défini d’abord pour le seul portage PostGres, ce qui tend à prouver la perennité de l’interface ainsi définie. Toutefois, de tels outils sont tôt ou tard condamnés à évoluer, aussi cette nouvelle l’interface proposée avec SPIP 2.0 intègre dès à présent un gestionnaire de ses propres versions, lui permettant d’utiliser une connexion SQL avec plusieurs versions de l’interface simultanément. Ainsi, les extensions de SPIP respectant cette nouvelle interface sont-elles assurées de fonctionner dans les versions ultérieures de SPIP.

Signalons enfin que le choix de développer cette architecture a été imposé par l’inexistence d’une bibliothèque de niveau d’abstraction équivalent disponible aussi librement que SPIP : les extensions de PHP couramment offertes se contentent d’uniformiser les appels aux fonctions de base de celui-ci, ce qui n’est que la partie émergée de l’iceberg auquel est confronté le développeur d’applications sur bases de données hétérogènes.


Cet article comporte trois parties. Les deux premières sont destinées aux développeurs désireux d’écrire des extensions de SPIP ; elles présentent l’architecture générale, puis les fonctions disponibles. La troisième détaille l’implémentation et est destinée aux contributeurs de SPIP, désireux de le porter sur d’autres implémentations de SQL ou d’améliorer les portages existants. Dans tout l’article, on parlera de serveur SQL pour désigner une implémentation de SQL utilisée par SPIP, bien qu’à proprement parler certaines implémentations ne sont pas des serveurs.

Architecture générale

Dans un premier temps, on peut considérer que l’interface de SPIP aux implémentations de SQL se réduit à l’unique fonction suivante, définie dans le fichier ecrire/base/abstract_sql.php :

sql_serveur($ins_sql, $serveur='', $continue=false)

Cette fonction commence, si elle ne l’a pas déjà fait auparavant, par se connecter au serveur spécifié en deuxième argument. Cet argument est souvent omis, ce qui désigne alors l’implémentation SQL choisie à l’installation, et mémorisé par SPIP dans un fichier de connexion (voir Les bases de données en SPIP). Sinon, l’argument doit indiquer explicitement le nom du fichier de connexion à utiliser, l’extension .php étant omise. Le résultat retourné est une autre fonction, réalisant le type d’action demandée par la chaîne passée en premier argument (par exemple select ou fetch) sur la base SQL indiquée par le fichier de connexion. Le troisième argument indique ce qu’il faut faire lorsqu’une telle fonction ne peut être retournée. S’il est absent ou égal à false, une erreur fatale sera déclenchée. Autrement, deux autres situations sont distinguées. Si la connexion indiquée est inconnue ou inopérante, la valeur false sera retournée. Autrement, sera retournée une structure de données décrivant la connexion (elle sera décrite dans la dernière partie), ce qui permet d’une part de vérifier qu’une fonction existe sans risquer l’erreur fatale, et d’autre part d’obtenir plusieurs informations avant utilisation.

Cette vision minimaliste de l’interface permet de tout faire, mais avec une syntaxe assez opaque. Si par exemple $db est le nom d’une base dans le serveur principal, on ne le sélectionnera pas

$f = sql_serveur('selectdb');
$f($db);

Pour clarifier l’écriture, il existe un jeu de fonctions enchaînant les deux instructions ci-dessus pour les cas les plus fréquents. Par exemple, il existe

sql_selectdb($nom, $serveur='')

Ce qui permet de réécrire plus simplement l’opération précédente :

sql_selectdb($db)

De manière générale, l’interface de SPIP aux serveurs SQL est un jeu de fonctions dont le nom est sql_f et dont le dernier argument, optionnel, est le nom du serveur. Elles appellent sql_serveur pour obtenir une fonction f qu’elles appliquent sur leurs arguments, y compris le nom du serveur. Toutes les fonctions dont le nom est ainsi construit sont réservées à l’interface de SPIP aux serveurs SQL.

Dans une vision orientée objet, ce jeu de fonctions représente les méthodes de l’objet sql, mais on écrit sql_f à la place de sql->f, et il n’est pas nécessaire d’instancier une classe. La présence du nom du serveur dans tous les appels permet de simuler les opérations impliquant l’objet moi-même.

Ce jeu de fonctions dispense donc la plupart du temps d’utiliser sql_serveur. Signalons également la fonction (présente dans SPIP de longue date) :

spip_connect($serveur='')

qui simplement ouvre la connexion au serveur, et est donc équivalente à

sql_serveur('', $serveur, true)

et qui retourne false si le serveur est indisponible, sinon la structure de données décrivant les possibilités du serveur.

Fonctions disponibles

Les fonctions de l’interface SQL peuvent se classer en plusieurs groupes, pour lesquels on donnera à chaque fois un tableau présentant leurs arguments. Pour les exemples on se reportera au code de SPIP.

Un premier groupe de fonctions concernent la lecture des tables SQL. Les fonctions incontournables sont :

-  sql_select dont les arguments sont les clauses SQL habituelles de cette instruction, et dont le résultat est une ressource Select ;

-  sql_fetch, utilisée presque toujours dans une boucle, qui permet de récupérer les lignes successives d’une ressource Select ; son résultat est un tableau indexé par le nom des champs ;

-  sql_free qui signale au serveur de libérer une ressource.

D’autres fonctions offrent des compositions fréquentes de ces opérations :

-  sql_fetsel de mêmes arguments que sql_select, qui applique celle-ci sur ses arguments puis sql_fetch sur la ressource retournée ; pratique pour les requêtes dont le résultat ne comporte qu’une ligne ;

-  sql_getfetsel de mêmes arguments que sql_select, qui applique celle-ci sur ses arguments puis sql_fetch sur la ressource retournée, et enfin extrait du tableau retourné le champ dont le nom est donné comme premier argument (la liste des champs n’en comporte donc qu’un) ; pratique pour les requêtes dont le résultat ne comporte qu’une ligne d’un seul champ ;

-  sql_allfetsel de mêmes arguments que sql_select, qui applique celle-ci sur ses arguments puis sql_fetch sur la ressource retournée tant que celui-ci retourne un tableau non vide ; sql_allfetsel retourne pour finir le tableau de tous les tableaux retournés par sql_fetch (attention à ne pas saturer la mémoire avec cette fonction) ;

-  sql_countsel de mêmes arguments que sql_select moins le premier, qui applique celle-ci sur COUNT(*) et ses autres arguments et retourne le nombre calculé ; pratique pour connaître le nombre de lignes que retournerait la requête Select ainsi décrite.

-  sql_count qui retourne le nombre de lignes d’une ressource Select, comme si on avait ajouté COUNT(*) dans la requête ;

-  sql_get_select utilise les mêmes arguments que sql_select, mais retourne le code SQL de la requête, sans l’exécuter. Cette fonction peut-être utile pour les besoins de certains plugins ou pour créer facilement des vues SQL.

Les quatre premières appellent pour finir sql_free, et sont donc à préférer autant que possible au trio select-fetch-free dont on oublie facilement le dernier membre.

Le tableau ci-dessous précise le contenu et l’ordre des arguments attendus par ces fonctions.

Fonction Arguments
sql_select
  1. liste des champs : chaîne ou tableau
  2. liste des tables : chaîne ou tableau
  3. clause Where : chaîne ou tableau
  4. clause Groupby : chaîne avec virgule séparatrice ou tableau
  5. clause Orderby : chaîne avec virgule séparatrice ou tableau
  6. clause Limit : un ou deux entiers séparés par des virgules
  7. clause Having : chaîne ou tableau
  8. serveur
sql_fetch
  1. ressource
  2. serveur
sql_free
  1. ressource
  2. serveur
sql_count
  1. ressource
  2. serveur
sql_countsel
  1. liste des tables : chaîne ou tableau
  2. clause Where : chaîne ou tableau
  3. clause Groupby : chaîne ou tableau
  4. clause Orderby : chaîne avec virgule séparatrice ou tableau
  5. clause Limit : un ou deux entiers séparés par des virgules
  6. clause Having : chaîne ou tableau
  7. serveur
sql_fetsel
  1. liste des champs : chaîne ou tableau
  2. liste des tables : chaîne ou tableau
  3. clause Where : chaîne ou tableau
  4. clause Groupby : chaîne avec virgule séparatrice ou tableau
  5. clause Orderby : chaîne avec virgule séparatrice ou tableau
  6. clause Limit : un ou deux entiers séparés par des virgules
  7. clause Having : chaîne ou tableau
  8. serveur
sql_allfetsel
  1. liste des champs : chaîne ou tableau
  2. liste des tables : chaîne ou tableau
  3. clause Where : chaîne ou tableau
  4. clause Groupby : chaîne avec virgule séparatrice ou tableau
  5. clause Orderby : chaîne avec virgule séparatrice ou tableau
  6. clause Limit : un ou deux entiers séparés par des virgules
  7. clause Having : chaîne ou tableau
  8. serveur
sql_getfetsel
  1. nom d’un champ : chaîne
  2. liste des tables : chaîne ou tableau
  3. clause Where : chaîne ou tableau
  4. clause Groupby : chaîne ou tableau
  5. clause Orderby : chaîne avec virgule séparatrice ou tableau
  6. clause Limit : un ou deux entiers séparés par des virgules
  7. clause Having : chaîne ou tableau
  8. serveur
sql_get_select
  1. liste des champs : chaîne ou tableau
  2. liste des tables : chaîne ou tableau
  3. clause Where : chaîne ou tableau
  4. clause Groupby : chaîne avec virgule séparatrice ou tableau
  5. clause Orderby : chaîne avec virgule séparatrice ou tableau
  6. clause Limit : un ou deux entiers séparés par des virgules
  7. clause Having : chaîne ou tableau
  8. serveur

Dans les fonctions ci-dessus, si la clause Select est fournie sous forme de tableau, ses éléments seront concaténés, séparés par des virgules. En cas de tableau pour les clauses Where et Having, les éléments doivent être des chaînes (des sous-tableaux en notation préfixée sont également pris en charge, mais réservés au compilateur). Ces chaînes seront réunies en une grande conjonction (i.e, elles seront concaténées avec AND comme séparateur).

La clause From est une chaîne (le cas du tableau est réservé au compilateur de SPIP). Attention : s’il est nécessaire de référencer les tables dans les autres clauses, il faut en définir des alias dans ce paramètre et les utiliser systématiquement. Ainsi, on écrira :

sql_countsel('spip_articles AS a, spip_rubriques AS r', "a.id_secteur=r.id_rubrique AND r.titre='monsecteur')

ou

sql_countsel('spip_articles AS a JOIN spip_rubriques AS r ON a.id_secteur=r.id_rubrique", "r.titre='monsecteur'")

alors que l’écriture suivante ne sera pas comprise :

sql_countsel('spip_articles, spip_rubriques', "spip_articles.id_rubrique=spip_rubriques.id_secteur AND spip_rubriques.titre='monsecteur'")

Un deuxième groupe de fonctions est constitué par celles modifiant le contenu des tables. Ces fonctions sont délicates à définir car la syntaxe des valeurs à introduire dans les tables change d’un serveur SQL à un autre (notamment les dates). Pour cette raison, ces fonctions doivent disposer de la description de la table à modifier, afin de connaître le type des valeurs attendues par le serveur SQL. SPIP retrouve automatiquement ces informations (données au moment de la création de la table) mais il est possible de fournir une description arbitraire (avant-dernier argument de ces fonctions, optionnel et d’ailleurs rarement utile).

SPIP fournit donc une fonction d’insertion, sql_insertq, et une fonction de mise à jour, sql_updateq, qui prennent un tableau champ=>valeur et s’occupent de citer les valeurs en fonction du type (avec la fonction sql_quote spécifiée ci-dessous). Est également disponible sql_insertq_multi permettant de faire des insertions de plusieurs entrées en prenant un tableau de tableau champ=>valeur. Pour les mises à jour où les nouvelles valeurs dépendent des anciennes (comme dans cpt=cpt+1), utiliser sql_update où les valeurs seront prises littérallement, mais il faudra interdire soigneusement les possibilités d’attaque par injection de code. Il existe également sql_replace, fonction effectuant une mise à jour sur une ligne correspondant à une clé primaire, ou insérant les valeurs si cette ligne n’existe pas ainsi qu’une fonction sql_replace_multi pour des mises à jour ou insertions multiples. Enfin, sql_delete efface d’une table les lignes répondant à une clause Where.

Fonction Arguments
sql_updateq
  1. table
  2. tableau champ=>valeur à citer
  3. clause Where
  4. description
  5. serveur
sql_update
  1. table
  2. tableau champ=>valeur
  3. clause Where
  4. description
  5. serveur
sql_insertq
  1. table
  2. tableau champ=>valeur à citer
  3. description
  4. serveur
sql_insertq_multi
  1. table
  2. tableau de tableau champ=>valeur à citer
  3. description
  4. serveur
sql_replace
  1. table
  2. tableau champ=>valeur à citer
  3. description
  4. serveur
sql_replace_multi
  1. table
  2. tableau de tableau champ=>valeur à citer
  3. description
  4. serveur
sql_delete
  1. table
  2. clause Where
  3. serveur

Un groupe un peu à part est formé de fonctions traitant spécifiquement des opérandes. : elles ne se connectent pas au serveur, mais retournent des chaînes dépendant de celui-ci :

-  sql_quote prend une chaîne ou un nombre, retourne un nombre si l’argument était un nombre ou une chaîne représentant un entier, sinon retourne la chaîne initiale entourée d’apostrophes et avec les apostrophes protégées selon la syntaxe propre au serveur (un \ devant pour certains, une deuxième apostrophe pour d’autres) ;

-  sql_hex prend une chaîne de chiffres hexadécimaux et retourne sa représentation dans le serveur SQL visé ;

-  sql_in construit un appel à l’opérande IN, en traitant les éventuelles valeurs hexadécimales y figurant ;

-  sql_test_int prédicat retournant Vrai si le type SQL fourni désigne un entier ;

-  sql_test_date prédicat retournant Vrai si le type SQL fourni désigne une date ;

-  sql_multi applique une expression SQL sur champ contenant un bloc multi (voir Réaliser un site multilingue) pour y prendre la partie correspondant à la langue indiquée ; l’intérêt d’effectuer cette opération au niveau SQL est essentiellement de demander simultanément un tri sur cette colonne.

Fonction Arguments
sql_quote
  1. valeur
  2. serveur
sql_hex
  1. valeur
  2. serveur
sql_in
  1. colonne
  2. valeurs
  3. vrai si négation souhaitée
  4. serveur
sql_multi
  1. colonne
  2. langue
  3. serveur
sql_test_date
  1. type
  2. serveur
sql_test_int
  1. type
  2. serveur

Un groupe important est constitué par les fonctions manipulant les déclarations de bases et de tables. Pour des raisons historiques, cette première version de l’interface reprend quasi littéralement la syntaxe de MySQL3 et devra certainement être revue, en particulier pour y faire apparaître la déclaration des jointures. Les fonctions sql_create, sql_alter, sql_showtable et sql_drop_table permettent de créer, modifier, voir et supprimer une table. Les fonctions sql_create_view, sql_drop_view permettent de créer ou supprimer une vue. Les fonctions sql_listdbs, sql_showbase et sql_selectdb permettent de voir les bases accessibles, de voir leur contenu et d’en sélectionner une. À noter que tous les hébergeurs n’autorisent pas forcément de telles actions ; SPIP essaiera de le deviner, en notant ses essais dans le fichier spip.log.

Fonction Arguments
sql_create
  1. nom de la table
  2. tableau nom de colonne => type SQL et valeur par défaut
  3. tableau nom d’index => colonne(s)
  4. vrai si auto-incrément
  5. vrai si temporaire
  6. serveur
sql_alter
  1. requête MYSQL Alter
  2. serveur
sql_showtable
  1. RegExp
  2. vrai si table déclarée par SPIP
  3. serveur
sql_drop_table
  1. nom de la table
  2. vrai s’il faut insérer la condition existe
  3. serveur
sql_create_view
  1. nom de la vue
  2. requête de sélection de champs (créé par exemple avec sql_get_select)
  3. serveur
sql_drop_view
  1. nom de la vue
  2. vrai s’il faut insérer la condition existe
  3. serveur
sql_listdbs
sql_selectdb
  1. nom de la base
  2. serveur
sql_showbase
  1. RegExp
  2. serveur

Deux fonctions permettent de régler le codage des caractères lors des communications avec le serveur :

-  sql_set_charset, demande d’utiliser le codage indiqué ;

-  sql_get_charset, demande si un codage de nom donné est disponible sur le serveur.

Fonction Arguments
sql_get_charset
  1. RegExp
  2. serveur
sql_set_charset
  1. codage
  2. serveur

Un dernier groupe de fonctions offre quelques outils de gestion des requêtes et des tables ; on se reportera à leurs homonymes dans la documentation des serveurs SQL. Signalons toutefois que sql_explain est utilisée implicitement par le débusqueur de SPIP, accessible par les boutons d’administration de l’espace public, lorsqu’on lui demande le plan de calcul d’une boucle (ou de tout un squelette).

Fonction Arguments
sql_optimize
  1. requête
  2. serveur
sql_repair
  1. table
  2. serveur
sql_explain
  1. requête
  2. serveur
sql_error
  1. requête
  2. serveur
sql_errno
sql_version

Hors groupe, la fonction générale sql_query, nom qu’aurait dû porter l’historique spip_query ; leur utilisation est de toute façon à éviter.

Réalisation des portages

Cette section est destinée à ceux souhaitant porter SPIP sur d’autres serveurs SQL, ou ayant besoin d’informations plus techniques, notamment sur la gestion des versions de l’interface. Les fonctions du fichier ecrire/base/abstract_sql.php étudiées ci-dessus se contentent d’offrir une interface homogène aux différents serveurs, mais ne procèdent elles-mêmes à aucun calcul. C’est dans le fichier ecrire/base/connect_sql.php que se situe le travail effectif.

La fonction essentielle est spip_connect qui ouvre la connexion au serveur SQL indiqué par son argument (ou, s’il est omis, le serveur principal) en repérant les connexions déjà faites. Cette ouverture consiste à inclure un fichier de connexion créé lors de l’installation de SPIP par les scripts présents dans le répertoire install. Un fichier de connexion se réduit pour l’essentiel à appliquer la fonction spip_connect_db aux valeurs fournies lors de l’installation.

La fonction spip_connect_db reçoit en particulier comme argument le type du serveur SQL. Ce type doit être le nom d’un fichier présent dans le répertoire req. Ce fichier est chargé et doit définir toutes les fonctions d’interfaces définies à la section précédente, plus la fonction req_type_dist qui sera immédiatement appliquée sur les mêmes arguments que spip_connect_db, type excepté. C’est cette fonction qui doit établir effectivement la connexion.

Porter SPIP sur d’autres serveurs SQL consiste donc à définir ce jeu de fonctions et à le placer dans le répertoire req.

Le gestionnaire de versions d’interface repose sur le deuxième argument de spip_connect qui indique la version, la version courante étant prise par défaut. Toutes les fonctions de l’interface sont définies dans le fichier abstract_sql, se nomment sql_X et sont les seules à se nommer ainsi. Elles se connectent toutes en appelant une variante de spip_connect dont le premier argument est le numéro de version de l’interface. Au cas où le fichier abstract_sql nécessiterait une révision, il sera renommé abstract_sql_N, et le Sed suivant lui sera appliqué (N désigne le numéro de version) :

s/\(sql_[A-Za-z_0-9 ]*\)/\1_N/

En appliquant également ce script aux extensions de SPIP fondées sur cette version, on leur permettra d’en appeler ses fonctions, qui seront chargées sans collision de noms, le Sed ayant préfixé le nom des anciennes avec leur numéro de version. Il faudra juste rajouter une instruction include portant sur le fichier abstract_sql_N. Du côté du portage, il faudra renommer pareillement les fichiers du répertoire req, et écrire les nouvelles versions.

La coexistence de plusieurs versions de l’interface pendant l’exécution de SPIP repose sur la structure décrivant le serveur. Celle-ci est en fait un tableau, contenant :

-  link, ressource indiquant la connexion ;

-  db, nom de la base de données ;

-  prefixe, nom du préfixe de table ;

-  ldap, nom de l’éventuel fichier décrivant le serveur LDAP.

Les autres entrées sont les numéros de versions disponibles, et leur valeur est le tableau des fonctions implémentant cette version de l’interface.

Les autres fonctions du fichier connect_sqlconcernent essentiellement la gestion des versions et le traitement de quelques cas particuliers de déclarations des tables standards de SPIP.

P.-S.

Le remplacement de spip_query dans l’ancien code de SPIP par ce jeu de fonctions a été réalisé en partie automatiquement par des scripts Sed. Ces scripts font partie des dépôts des versions successives du code, et peuvent donc être récupérés pour être appliqués aux extensions de SPIP voulant migrer vers la nouvelle architecture. En voici la liste :

9916 9918 9919 10394 10492 10493 10497 10501 10504 10508 10509 10519 10520 10707 10742

Attention cependant : ces scripts ne fonctionnent pas pour n’importe quelle utilisation de spip_query, il est indispensable de lire chacun d’eux pour vérifier qu’ils s’appliquent bien au code à faire migrer.


Voir le squelette de cette page Site réalisé avec SPIP | Espace de traduction | Espace privé