Guillaume Kulakowski's blog

Opération de maintenance sur les forums de Fedora-Fr

Guillaume Kulakowski par Guillaume Kulakowski dans Fedora-Fr 20 juillet 2008 0
Tags : eZ Publish eZFluxBB MySQL PunBB UTF-8
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) :

# 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.

Commentaires

Les commentaires pour ce poste sont fermés.

Réseaux sociaux