Opération de maintenance sur les forums de Fedora-Fr
C'est en développant mon extension eZFluxBB et en concevant la nouvelle version de Fedora-Fr sous eZ Publish que je me suis rendu compte de quelque chose : la base de données du forum PunBB en UTF-8 n'était peut être pas autant en UTF-8 que je le pensais ;-)...
Bref, aujourd'hui, j'ai donc procédé à une opération de maintenance sur la base de données avec la migration de cette dernière en vrai utf-8 via quelques requêtes qui vont bien et l'utilisation massive de phpMyAdmin. A titre d'exemple voici à quoi ressemble les requêtes (merci à l'ami Remi) :
[mysql] # punbb_bans ALTER TABLE punbb_bans CHANGE username username TEXT CHARACTER SET latin1; ALTER TABLE punbb_bans CHANGE username username BLOB; ALTER TABLE punbb_bans CHANGE username username varchar(200) CHARACTER SET utf8; ALTER TABLE punbb_bans CHANGE message message TEXT CHARACTER SET latin1; ALTER TABLE punbb_bans CHANGE message message BLOB; ALTER TABLE punbb_bans CHANGE message message varchar(255) CHARACTER SET utf8; # punbb_categories ALTER TABLE punbb_categories CHANGE cat_name cat_name TEXT CHARACTER SET latin1; ALTER TABLE punbb_categories CHANGE cat_name cat_name BLOB; ALTER TABLE punbb_categories CHANGE cat_name cat_name varchar(80) CHARACTER SET utf8; # punbb_config ALTER TABLE punbb_config CHANGE conf_value conf_value TEXT CHARACTER SET latin1; ALTER TABLE punbb_config CHANGE conf_value conf_value BLOB; ALTER TABLE punbb_config CHANGE conf_value conf_value TEXT CHARACTER SET utf8; # punbb_contacts ALTER TABLE punbb_contacts CHANGE contact_name contact_name TEXT CHARACTER SET latin1; ALTER TABLE punbb_contacts CHANGE contact_name contact_name BLOB; ALTER TABLE punbb_contacts CHANGE contact_name contact_name varchar(200) CHARACTER SET utf8; # punbb_forums ALTER TABLE punbb_forums CHANGE forum_name forum_name TEXT CHARACTER SET latin1; ALTER TABLE punbb_forums CHANGE forum_name forum_name BLOB; ALTER TABLE punbb_forums CHANGE forum_name forum_name varchar(80) CHARACTER SET utf8; ALTER TABLE punbb_forums CHANGE forum_desc forum_desc TEXT CHARACTER SET latin1; ALTER TABLE punbb_forums CHANGE forum_desc forum_desc BLOB; ALTER TABLE punbb_forums CHANGE forum_desc forum_desc TEXT CHARACTER SET utf8; ALTER TABLE punbb_forums CHANGE moderators moderators TEXT CHARACTER SET latin1; ALTER TABLE punbb_forums CHANGE moderators moderators BLOB; ALTER TABLE punbb_forums CHANGE moderators moderators TEXT CHARACTER SET utf8; ALTER TABLE punbb_forums CHANGE last_poster last_poster TEXT CHARACTER SET latin1; ALTER TABLE punbb_forums CHANGE last_poster last_poster BLOB; ALTER TABLE punbb_forums CHANGE last_poster last_poster varchar(200) CHARACTER SET utf8; # punbb_groups ALTER TABLE punbb_groups CHANGE g_title g_title TEXT CHARACTER SET latin1; ALTER TABLE punbb_groups CHANGE g_title g_title BLOB; ALTER TABLE punbb_groups CHANGE g_title g_title varchar(50) CHARACTER SET utf8; ALTER TABLE punbb_groups CHANGE g_user_title g_user_title TEXT CHARACTER SET latin1; ALTER TABLE punbb_groups CHANGE g_user_title g_user_title BLOB; ALTER TABLE punbb_groups CHANGE g_user_title g_user_title varchar(50) CHARACTER SET utf8; # punbb_messages ALTER TABLE punbb_messages CHANGE subject subject TEXT CHARACTER SET latin1; ALTER TABLE punbb_messages CHANGE subject subject BLOB; ALTER TABLE punbb_messages CHANGE subject subject varchar(255) CHARACTER SET utf8; ALTER TABLE punbb_messages CHANGE message message TEXT CHARACTER SET latin1; ALTER TABLE punbb_messages CHANGE message message BLOB; ALTER TABLE punbb_messages CHANGE message message TEXT CHARACTER SET utf8; ALTER TABLE punbb_messages CHANGE sender sender TEXT CHARACTER SET latin1; ALTER TABLE punbb_messages CHANGE sender sender BLOB; ALTER TABLE punbb_messages CHANGE sender sender varchar(200) CHARACTER SET utf8; # punbb_online ALTER TABLE punbb_online CHANGE ident ident TEXT CHARACTER SET latin1; ALTER TABLE punbb_online CHANGE ident ident BLOB; ALTER TABLE punbb_online CHANGE ident ident varchar(200) CHARACTER SET utf8; # punbb_plugin_cau ALTER TABLE punbb_plugin_cau CHANGE name name TEXT CHARACTER SET latin1; ALTER TABLE punbb_plugin_cau CHANGE name name BLOB; ALTER TABLE punbb_plugin_cau CHANGE name name varchar(255) CHARACTER SET utf8; # punbb_posts ALTER TABLE punbb_posts CHANGE poster poster TEXT CHARACTER SET latin1; ALTER TABLE punbb_posts CHANGE poster poster BLOB; ALTER TABLE punbb_posts CHANGE poster poster varchar(200) CHARACTER SET utf8; ALTER TABLE punbb_posts CHANGE message message TEXT CHARACTER SET latin1; ALTER TABLE punbb_posts CHANGE message message BLOB; ALTER TABLE punbb_posts CHANGE message message TEXT CHARACTER SET utf8; ALTER TABLE punbb_posts CHANGE edited_by edited_by TEXT CHARACTER SET latin1; ALTER TABLE punbb_posts CHANGE edited_by edited_by BLOB; ALTER TABLE punbb_posts CHANGE edited_by edited_by varchar(200) CHARACTER SET utf8; # punbb_ranks ALTER TABLE punbb_ranks CHANGE rank rank TEXT CHARACTER SET latin1; ALTER TABLE punbb_ranks CHANGE rank rank BLOB; ALTER TABLE punbb_ranks CHANGE rank rank varchar(50) CHARACTER SET utf8; # punbb_reports ALTER TABLE punbb_reports CHANGE message message TEXT CHARACTER SET latin1; ALTER TABLE punbb_reports CHANGE message message BLOB; ALTER TABLE punbb_reports CHANGE message message TEXT CHARACTER SET utf8; # punbb_search_cache ALTER TABLE punbb_search_cache CHANGE ident ident TEXT CHARACTER SET latin1; ALTER TABLE punbb_search_cache CHANGE ident ident BLOB; ALTER TABLE punbb_search_cache CHANGE ident ident varchar(200) CHARACTER SET utf8; # punbb_search_words ALTER TABLE punbb_search_words CHANGE word word varchar(200) CHARACTER SET latin1; ALTER TABLE punbb_search_words CHANGE word word BLOB; ALTER TABLE punbb_search_words CHANGE word word varchar(20) CHARACTER SET utf8; # punbb_topics ALTER TABLE punbb_topics CHANGE poster poster TEXT CHARACTER SET latin1; ALTER TABLE punbb_topics CHANGE poster poster BLOB; ALTER TABLE punbb_topics CHANGE poster poster varchar(200) CHARACTER SET utf8; ALTER TABLE punbb_topics CHANGE subject subject TEXT CHARACTER SET latin1; ALTER TABLE punbb_topics CHANGE subject subject BLOB; ALTER TABLE punbb_topics CHANGE subject subject varchar(255) CHARACTER SET utf8; ALTER TABLE punbb_topics CHANGE last_poster last_poster TEXT CHARACTER SET latin1; ALTER TABLE punbb_topics CHANGE last_poster last_poster BLOB; ALTER TABLE punbb_topics CHANGE last_poster last_poster varchar(200) CHARACTER SET utf8; # punbb_users ALTER TABLE punbb_users CHANGE username username TEXT CHARACTER SET latin1; ALTER TABLE punbb_users CHANGE username username BLOB; ALTER TABLE punbb_users CHANGE username username varchar(200) CHARACTER SET utf8; ALTER TABLE punbb_users CHANGE title title TEXT CHARACTER SET latin1; ALTER TABLE punbb_users CHANGE title title BLOB; ALTER TABLE punbb_users CHANGE title title varchar(50) CHARACTER SET utf8; ALTER TABLE punbb_users CHANGE realname realname TEXT CHARACTER SET latin1; ALTER TABLE punbb_users CHANGE realname realname BLOB; ALTER TABLE punbb_users CHANGE realname realname varchar(40) CHARACTER SET utf8; ALTER TABLE punbb_users CHANGE location location TEXT CHARACTER SET latin1; ALTER TABLE punbb_users CHANGE location location BLOB; ALTER TABLE punbb_users CHANGE location location varchar(30) CHARACTER SET utf8; ALTER TABLE punbb_users CHANGE signature signature TEXT CHARACTER SET latin1; ALTER TABLE punbb_users CHANGE signature signature BLOB; ALTER TABLE punbb_users CHANGE signature signature TEXT CHARACTER SET utf8;
Une fois la base en UTF-8, j'ai du relancer l'indexation des postes, opération qui a bien pris plus d'1H30 avec de grosses montées en charge du serveur.
A présent, les informations extraites du forum PunBB de Fedora-Fr s'affichent parfaitement dans eZ Publish via eZFluxBB et nous pouvons envisager la migration du site existant pour bientôt, ainsi que de nouvelles surprises qui sont annoncées pour la suite, mais je vous en dirais plus en temps et en heure...
Remarque importante : La branche 1,2 de PunBB ne supporte pas officiellement l'UTF-8. Cette fonctionnalité sera apportée par la branche 1.3 actuellement en version beta 1, Si toute fois vous voulez vous lancer dans l'aventure, je ne saurais vous conseiller de lire ce post.
