Carnet Wiki

Sqlite export pour SPIP

Version 3 — Mars 2013 Suske

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 (Voir aussi Editer votre base « en ligne » avec Adminer), par exemple.

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.

Passer son SPIP de Sqlite à MySQL - ce qui ne marche pas

J’ai testé plusieurs possibilités :

  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.

A l’arrivée, comme je savais ne pas être seul dans le cas, je me suis lancé dans une tentative de faire quelque chose de réutilisable... Cela a donné un squelette spécifique, distribué via ce plugin.

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. 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 certaines syntaxes (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 le code 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

Vous trouverez toutes les infos sur l’implémentation du langage SQL dans Sqlite à la page https://www.sqlite.org/lang.html.

Le squelette (inclure/sqlite-mysql.html) est appelé via une inclusion simple (depuis dumpmysql.html), ce 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 ! Il utilise aussi des fltres 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

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 [<pre>(#VALEUR|print_r{1})</pre>] 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.

Le plugin est sur la zone (http://zone.spip.org/trac/spip-zone...) et vous pouvez en obtenir un zip via Trac : http://zone.spip.org/trac/spip-zone.... Vu son côté expérimental, je ne pense pas qu’il soit opportun de le rendre disponible via SVP. Les personnes prêtes à prendre le risque trouveront bien le chemin jusqu’ici.

Une fois le plugin installé et activé sur un site en Sqlite, le webmestre peut lancer la création du fihcier dump en appelant la page spip.php?page=dumpmysql (le lien est fourni dans la description du plugin - configuration du site, page de gestion des plugins).

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. Le code des filtres présents dans dumpmysql_fonctions.php est repris plus bas.

#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 <span class="base64" title="PGNvZGUgY2xhc3M9InNwaXBfY29kZSBzcGlwX2NvZGVfaW5saW5lIiBkaXI9Imx0ciI+I1ZBTEVVUip7bmFtZX08L2NvZGU+"></span>; CREATE TABLE <span class="base64" title="PGNvZGUgY2xhc3M9InNwaXBfY29kZSBzcGlwX2NvZGVfaW5saW5lIiBkaXI9Imx0ciI+I1ZBTEVVUip7bmFtZX08L2NvZGU+"></span>


([(#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}}{", "}>
<span class="base64" title="PGNvZGUgY2xhc3M9InNwaXBfY29kZSBzcGlwX2NvZGVfaW5saW5lIiBkaXI9Imx0ciI+I1ZBTEVVUip7bmFtZX08L2NvZGU+"></span> [ (#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  <span class="base64" title="PGNvZGUgY2xhc3M9InNwaXBfY29kZSBzcGlwX2NvZGVfaW5saW5lIiBkaXI9Imx0ciI+KCNHRVR7a2V5fSk8L2NvZGU+"></span> ]


[(#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}}{", "}>
<span class="base64" title="PGNvZGUgY2xhc3M9InNwaXBfY29kZSBzcGlwX2NvZGVfaW5saW5lIiBkaXI9Imx0ciI+I1ZBTEVVUip7bmFtZX08L2NvZGU+"></span>
</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 <span class="base64" title="PGNvZGUgY2xhc3M9InNwaXBfY29kZSBzcGlwX2NvZGVfaW5saW5lIiBkaXI9Imx0ciI+I0dFVHt0YWJsZX08L2NvZGU+"></span> (
<BOUCLE_champs(DATA){source tableau,#GET{champs}}{", "}>
<span class="base64" title="PGNvZGUgY2xhc3M9InNwaXBfY29kZSBzcGlwX2NvZGVfaW5saW5lIiBkaXI9Imx0ciI+I1ZBTEVVUio8L2NvZGU+"></span>
</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 <span class="base64" title="PGNvZGUgY2xhc3M9InNwaXBfY29kZSBzcGlwX2NvZGVfaW5saW5lIiBkaXI9Imx0ciI+c3BpcF9tZXRhPC9jb2RlPg=="></span> (<span class="base64" title="PGNvZGUgY2xhc3M9InNwaXBfY29kZSBzcGlwX2NvZGVfaW5saW5lIiBkaXI9Imx0ciI+bm9tPC9jb2RlPg=="></span>, <span class="base64" title="PGNvZGUgY2xhc3M9InNwaXBfY29kZSBzcGlwX2NvZGVfaW5saW5lIiBkaXI9Imx0ciI+dmFsZXVyPC9jb2RlPg=="></span>, <span class="base64" title="PGNvZGUgY2xhc3M9InNwaXBfY29kZSBzcGlwX2NvZGVfaW5saW5lIiBkaXI9Imx0ciI+aW1wdDwvY29kZT4="></span>, <span class="base64" title="PGNvZGUgY2xhc3M9InNwaXBfY29kZSBzcGlwX2NvZGVfaW5saW5lIiBkaXI9Imx0ciI+bWFqPC9jb2RlPg=="></span>) VALUES ('charset_collation_sql_base', 'utf8_general_ci', 'non', ''),('charset_sql_base', 'utf8', 'non', ''),('charset_sql_connexion', 'utf8', 'non', '');

Et le code des filtres :

<?php
// la cle primaire est declaree avec le reste du sql => recuperer sa seule declaration
function cle_prim($val) {
if ($position_cle=strpos($val,"PRIMARY")) {
$cle_primaire=substr($val,$position_cle);
$cle_primaire=str_replace("))",")",$cle_primaire); 
if (preg_match(",PRIMARY KEY \(([^\)]+)\),Uims", $cle_primaire, $reg)) {
#	echo "<br>".$reg[1];
	return 	$reg[1];
}
}
return $cle_primaire;
}


//sqlite fournit des noms de cle de la forme nom_table_cle => supprimer nom_table
function keyname($nomlong,$nomtable) {
$nomtable=$nomtable."_";
if ($nomlong!=="") {
$nom_cle=str_replace($nomtable,"",$nomlong);
return $nom_cle;
}
else return false;
}


// nettoyer les guillemets droits simples pour MySQL => enlever les entites num, les échappements préexistants + addslashes
function mysql_prep($value) {
	$value = str_replace("&#39;","'",$value);
    $value = str_replace("\'","'",$value);
    $text_mysql = addslashes($value);
    return $text_mysql;
}


?>

La suite plus tard peut-être (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.