Carnet Wiki

Sqlite export pour SPIP

Version 1 — March 2013 Suske — Version initiale

Ce plugin est expérimental ! Même s’il semble donner un résultat satisfaisant, les différences entre sqlite3 et mysql ne sont pas toutes prises en compte. Au jour de la rédaction de ces lignes, je n’ai pas de retour de problèmes majeurs mais les tests ont été peu nombreux et ce problème majeur c’est peut-être chez vous qu’il se produira. Aujourd’hui ou plus tard. Vous êtes prévenu-e-s !

Le plugin tente de réaliser un dump MySQL de votre site en Sqlite3. Si l’opération fonctionne, le dump obtenu est à importer dans une base MySQL, via phpmyadmin ou adminer.

Prologue

J’ai découvert en aout 2012 que certains plugins n’étaient pas fonctionnels en sqlite3, ou que certaines fonctions étaient absentes dans ce moteur de base de données.

Du coup, pour un site dont je m’occupe et qui avait été installé avec une base sqlite3 (choix par défaut en SPIP3 mais MySQL est aussi possible), j’ai voulu repasser sur un base MySQL et là... Pas de solution facile.

Ce qui ne marche pas

  1. Le dump au format sqlite proposé au titre de sauvegarde par SPIP3 ne permet pas de changer de moteur de base de données. Par contre, si votre hébergement supporte sqlite3, les sauvegardes sont fonctionnelles en vue d’une restauration sur le même site, avec le même moteur de base de données.
  2. le passage par des exports intermédiaires (type csv) sont possibles mais ils ne concernent que les données. C’est à vous de faire en sorte que les bases contiennent les mêmes tables, de vérifier quelles tables sont nécessaires, de gérer les histoires de charset, etc. Compliqué mais surtout fastidieux.
  3. phpmyadmin ne gère pas sqlite, mais adminer le fait. Cependant, un export sql d’une base slite3 n’est pas utilisable en import dans une base mysql. Pour voir où ça bloque, j’ai simplement comparé un dump mysql et un dump sqlite (ce sont de simples fichiers texte)... Les différences ne sont pas énormes mais elles sont visibles et bloquantes.

Ma démarche

Le passage par les 3 étapes ci-dessus m’a permis de réaliser que:
-  il y a lieu de différencier la question des données (les contenus) de la question de la structure de la base (le contenant)
-  les contenus sqlite passent quasi tels que en mysql: le seul problème que j’ai détecté dans ma base est lié aux guillemets droits simples. Ils sont tantôt enregistrés en entités numériques, tantôt échappés, tantôt présents seuls...
-  les déclarations de tables sont différentes mais pas tellement. Je n’ai pas pris de notes mais les différences principales se trouvent dans les déclarations de clés (primaires ou non), dans la déclaration de certains types de données, dans certains mots réservés (PRAGMA)...

Du coup, il m’est venu l’idée de réaliser sur un squelette qui récupèrerait les données de la base sqlite et de les encadrer avec les déclarations de tables qui conviendraient pour MySQL. Autrement dit, composer avec SPIP un fichier texte compatible avec MySQL, semblable à ceux qu’on peut produire avec les fonctions d’exportation de phpmyadmin ou adminer.

Comme j’aime jouer avec les itérateurs de SPIP (la fameuse boucle DATA) et que par ailleurs, j’ai appris que la structure d’une base Sqlite est décrite dans une table spécifique (sqlite_master), cela m’a semblé faisable et j’ai essayé de le faire... Je détaillerai plus bas (et plus tard) le fonctionnement de ce squelette.

La qualité et les limites du plugin

Si j’écris cette page c’est que ce plugin a désormais été utile à plusieurs personnes, outre moi-même. Comme il est basé sur le fonctionnement interne de Sqlite (et non sur la configuration du site ou des plugin), il présente l’intérêt de récupérer l’intégralité de la base de données, indépendamment du fait que les tables soient réellement utilisées dans la configuration du site au moment du dump. Cela peut-être très utile si vous utilisez des tables externes ou s’il vous arrive, comme moi, de désactivez plus ou moins temporairement certains plugins...

Cependant, l’utilisation du plugin n’est pas forcément vouée à la réussite sur votre site...

La principale limite est celle qui jusqu’ici n’a pas posé de problème... Elle est évoquée en haut de cette page. MySQL et Sqlite sont deux moteurs différents, utilisant des procédures, des stockages et des types de données différents. Ce plugin n’a absolument pas visé à l’exhaustivité à ce sujet. Les différences qu’il permet de corriger sont celles qui me sont apparues visuellement par la comparaison de fichiers dump produits avec ces deux moteurs. Il y en a certainement d’autres qui ont jour apparaitront et seront probablement bloquantes.

Ensuite, il y a des limites liées à votre hébergement et à la taille de votre base de données. En résumé, disons que les configurations de PHP et de Sqlite/MySQL prévoient des limites de temps d’exécution des scripts, de taille de fichiers utilisables et d’utilisation de la RAM du serveur. Au plus ces limites sont basses et au plus votre base de donnée est importante, au moins il y a de chances que le dump MySQL soit produit.

Par contre, et c’est important, comme une base de donnée Sqlite tient en un seul fichier, il est facile de récupérer celui-ci sur votre machine personnelle. Il vous suffit alors d’installer localement un serveur et d’en pousser les limites à des valeurs suffisantes pour que le dump soit produit. C’est une opération que j’ai réalisée plusieurs fois. Néanmoins, l’importation du dump dans le serveur MySQL de votre hébergeur risque d’être un peu plus compliquée vu que le dump pour MySQL sera d’une taille analogue à celle du dump Sqlite. Une solution est alors de découper le dump MySQL en plusieurs fichiers et de les importer l’un après l’autre.

Les ressources

Avant d’analyser ce que fait le squelette, sachez que vous trouverez toutes les infos sur l’implémentation du langage SQL dans Sqlite à la page https://www.sqlite.org/lang.html.

Vous verrez que ce squelette fait appel à des fltres php qui sont définis dans dumpmysql_fonctions.php selon le principe de SPIP qui permet de définir des fonctions dédiées à un squelette xxx.html dans un fichier xxx_fonctions.php

Enfin, notez que ce squelette contient une inclusion simple qui permet de limiter son usage au webmestre du site: c’est tout le contenu d’une base de données qui est potentiellement exposé au monde !

A noter enfin que lorsque vous faites un développement avec les itérateurs, vous avez intérêt à avoir sous la main le code suivant qui vous permet de visualiser le contenu brut des tableaux procurés par les itérateurs. Je l’ai utilisé pour chaque boucle DATA, puis retiré uniquement quand le résultat a été satisfaisant.

[<pre>(#VALEUR|print_r{1})</pre>]

Le squelette

Le fichier principal est inclure/sqlite-mysql.html. La version présente dans le plugin vise à ce que le dump produit soit “lisible” mais c’est au prix d’un code très compact. Voici ce même code mis en forme de manière plus aérée (mais qui produirait en l’état un dump beaucoup moins lisible du fait des sauts de lignes intempestifs qu’il produirait). Le code est commenté plus ou moins sommairement dans des balises #REM.

#CACHE{0} 
 
[(#REM) lister les tables et description sqlite]
[(#REM) array type=>table name=>nom table tbl_name=>nom_table rootpage=>2  sql=>CREATE TABLE name (...,PRIMARY KEY(key))]
 
<BOUCLE_tables(DATA){source sql,SELECT * FROM sqlite_master WHERE type='table'}>
#SET{table,#VALEUR*{name}} 
#SET{cle_primaire,#VALEUR*{sql}|cle_prim} 
#SET{req_table,PRAGMA table_info ( #VALEUR*{name} )}
 
[(#REM) les enonces de base: supprimer et creer ]
 
DROP TABLE IF EXISTS `#VALEUR*{name}`; CREATE TABLE `#VALEUR*{name}` 
 
([(#REM) liste des champs - RECUPERER AUTO INCREMENT !!! - on stocke aussi pour les insert]
 
#SET{champs,#ARRAY}
 
<B_table_desc>
<BOUCLE_table_desc(DATA){source sql,#GET{req_table}}{", "}>
`#VALEUR*{name}` [ (#VALEUR*{type}|=={"TIMESTAMP"}|?{"timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP",#VALEUR*{type}|strtolower}) ][ (#VALEUR*{notnull}|=={1}|?{"NOT NULL",""}) ][(#VALEUR*{type}|=={"INTEGER"}|et{#VALEUR*{name}|=={#GET{cle_primaire}}}|oui) AUTO_INCREMENT ][ default (#VALEUR*{dflt_value}|=={"''"}|?{"",#VALEUR*{dflt_value}})]
 
[(#REM) et on stocke la liste des champs pour plus tard] #SET{champs,#GET{champs}|push{#VALEUR*{name}}}
</BOUCLE_table_desc>
 
[(#REM) ensuite la clé primaire ]
[, PRIMARY KEY ((#GET{cle_primaire}))]
 
[(#REM) on cherche la liste des autres clés]
#SET{req_keys,PRAGMA index_list( #GET{table} )}
 
<B_keys_liste> , 
<BOUCLE_keys_liste(DATA){source sql,#GET{req_keys}}{","}>
 
[(#REM) le nom de table est répété dans les clés, on va l'enlever]
#SET{key,#VALEUR*{name}|keyname{#GET{table}}}[ KEY  `(#GET{key})` ]
 
[(#REM) les valeurs des autres clés ]
#SET{req_cle,PRAGMA index_info( #VALEUR*{name})}
 
<B_keys> ( 
<BOUCLE_keys(DATA){source sql,#GET{req_cle}}{", "}>
`#VALEUR*{name}`
</BOUCLE_keys>
 )</B_keys>
 
</BOUCLE_keys_liste>
 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
 
[(#REM) insérons-maintenant les valeurs des contenus]
#SET{req_donnees,SELECT * FROM  #GET{table}} 
 
<B_lignes>INSERT INTO `#GET{table}` (
<BOUCLE_champs(DATA){source tableau,#GET{champs}}{", "}>
`#VALEUR*`
</BOUCLE_champs>) 
VALUES 
<BOUCLE_lignes(DATA){source sql,#GET{req_donnees}}{", "}>
<B_donnees>(
<BOUCLE_donnees(DATA){source table,#VALEUR*}{", "}>
[(#VALEUR*|?{['(#VALEUR*|mysql_prep)'],"''"})]
</BOUCLE_donnees>
)</B_donnees>
 
</BOUCLE_lignes>
;</B_lignes>
 
</BOUCLE_tables>
 
INSERT INTO `spip_meta` (`nom`, `valeur`, `impt`, `maj`) VALUES ('charset_collation_sql_base', 'utf8_general_ci', 'non', ''),('charset_sql_base', 'utf8', 'non', ''),('charset_sql_connexion', 'utf8', 'non', '');

La suite plus tard (surtout s’il y a des demandes ;-) ).

Des améliorations ?

Si ce système devait s’avérer crédible, c’est chacune des limites qu’il faudrait adresser au cours de développements ultérieurs. Cela n’est pas forcément dans mes compétences actuelles (mais j’apprend chaque jour davantage). N’empêche, si leur coeur vous en dit, on crée des branches et faisons ça ensemble ;-)

C’est un wiki: n’hésitez pas à compléter et corriger... Eventuellement, envoyez un mail sur spip-zone, histoire que l’on puisse envisager des améliorations.