You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

222 lines
7.4 KiB

  1. <?php
  2. namespace Spip\Cli\Command;
  3. use Spip\Cli\Console\Command;
  4. use Symfony\Component\Console\Input\InputInterface;
  5. use Symfony\Component\Console\Input\InputOption;
  6. use Symfony\Component\Console\Output\OutputInterface;
  7. use Symfony\Component\Console\Helper\ProgressHelper;
  8. use Symfony\Component\Console\Helper\ProgressBar;
  9. class SqlConvertToUTF8 extends Command
  10. {
  11. protected function configure() {
  12. $this->setName("sql:convert:toutf8")
  13. ->setDescription("Convertit une base en UTF8 (utile pour un site en mysql)")
  14. ->setHelp("
  15. - on verifie que ENGINE=MYISAM et on corrige si besoin
  16. - la collation est passee en utf8
  17. - les champs iso sont modifies en utf8 on conservant leur contenu sans conversion (on suppose que les contenus sont en utf8 dans une base en iso, ce qui est le cas general dans les vieux SPIP)")
  18. ->addOption('convert', null, InputOption::VALUE_NONE, 'Pour forcer la conversion de charset des contenus (contenus encodes en iso dans une base iso)', null)
  19. ->addOption('exceptions', null, InputOption::VALUE_OPTIONAL, 'Pour traiter certains cas particuliers de tables --exceptions=spip_forum ou de champs --exceptions=spip_forum.texte,spip_breves.texte
  20. Pour ces champs on applique l\'inverse de l\'option convert');
  21. }
  22. protected function execute(InputInterface $input, OutputInterface $output) {
  23. $this->demarrerSpip();
  24. $this->io->title("Convertir en UTF8");
  25. $tables = sql_alltable('%'); // charger la connection mysql
  26. if ($GLOBALS['connexions'][0]['type'] !== 'mysql') {
  27. $this->io->error('Ce script est réservé aux installations utilisant mySQL');
  28. }
  29. $convert = !!$input->getOption('convert');
  30. // le passage latin=>utf8 des champs se fait en general sans conversion du contenu
  31. // car SPIP stocke deja du contenu UTF dans des tables latin1
  32. // toutefois si certains champs ont besoin d'une conversion SQL on les passes dans
  33. // $exceptions['table'][] = 'champ' pour convertir un champ
  34. // $exceptions['table'][] = '*' pour convertir tous les champs de la table
  35. $exceptions = array();
  36. $exceptions_option = $input->getOption('exceptions');
  37. if ($exceptions_option) {
  38. $exceptions_option = explode(',', $exceptions_option);
  39. foreach ($exceptions_option as $e) {
  40. $e = explode('.', $e, 2);
  41. $table = reset($e);
  42. if (!isset($exceptions[$table])) {
  43. $exceptions[$table] = array();
  44. }
  45. if (count($e) === 1) {
  46. $exceptions[$table][] = '*';
  47. }
  48. else {
  49. $champ = end($e);
  50. $exceptions[$table][] = $champ;
  51. }
  52. }
  53. }
  54. // convertir l'engine en myisam d'abord
  55. $this->sqlConvertEngine();
  56. // puis le charset
  57. $this->sqlConvertCharset($convert, $exceptions);
  58. ecrire_meta('charset_sql_connexion','utf8');
  59. ecrire_meta('charset','utf-8');
  60. $this->io->success("Fini");
  61. }
  62. protected function sqlConvertEngine() {
  63. $this->io->section("Vérification du Engine MySQL");
  64. $trouver_table = charger_fonction('trouver_table', 'base');
  65. $trouver_table('');
  66. $tables = sql_alltable('%');
  67. $this->io->text(count($tables) . " tables");
  68. foreach ($tables as $table){
  69. $ligne = "$table";
  70. $s = spip_mysql_query("SHOW CREATE TABLE `$table`");
  71. if (intval(10*floatval($GLOBALS['spip_version_branche']))>30) {
  72. list(,$a) = mysqli_fetch_array($s ,MYSQLI_NUM);
  73. }
  74. else {
  75. list(,$a) = mysql_fetch_array($s ,MYSQLI_NUM);
  76. }
  77. if (strpos($a,"ENGINE=MyISAM")===false OR strpos($a,"DEFAULT CHARSET=latin1")!==false){
  78. sql_alter($q = "TABLE $table ENGINE = MYISAM DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
  79. spip_log("ALTER $q","maj_utf");
  80. $ligne .= " : ALTER $table ENGINE = MYISAM ";
  81. $this->io->text("$ligne");
  82. }
  83. else {
  84. $this->io->text("$ligne OK");
  85. }
  86. }
  87. $this->io->success("Engine MYISAM + DEFAULT CHARACTER SET utf8 OK");
  88. }
  89. protected function sqlConvertCharset($convert, $exceptions = array()){
  90. ecrire_meta("charset_sql_connexion", "utf8");
  91. $this->io->section("Charset des champs de chaque table");
  92. $trouver_table = charger_fonction('trouver_table', 'base');
  93. $trouver_table('');
  94. $tables = sql_alltable('%');
  95. $this->io->text(count($tables) . " tables");
  96. foreach ($tables as $table){
  97. $this->io->section("Table $table");
  98. $desc = $trouver_table($table);
  99. $exception_champs = (isset($exceptions[$table]) ? $exceptions[$table] : array());
  100. $tochange = array();
  101. foreach ($desc['field'] as $field => $d){
  102. if (strpos($d, "latin1")!==false and !in_array($field, array('login', 'spip_listes_format'))){
  103. $tochange[$field] = $d;
  104. }
  105. }
  106. if ($tochange){
  107. $nbtochange = count($tochange);
  108. $this->io->text("$nbtochange à modifier : ".implode(', ', array_keys($tochange)));
  109. $fulltext_indexes = $this->sqlConvertGetFulltextIndex($desc);
  110. // supprimer les index fulltext
  111. if ($fulltext_indexes){
  112. spip_log($s = "Suppression des index Fulltext : " . implode(', ', array_keys($fulltext_indexes)), "maj_utf");
  113. $this->io->text($s);
  114. foreach ($fulltext_indexes as $key => $alter){
  115. sql_alter("TABLE $table DROP INDEX $key");
  116. }
  117. }
  118. // changer les champs
  119. foreach ($tochange as $field => $d){
  120. $this->sqlConvertCharsetField($convert, $table, $field, $d, $exception_champs);
  121. }
  122. // remettre les index fulltext
  123. if ($fulltext_indexes){
  124. spip_log($s = "Remettre les index Fulltext : " . implode(', ', array_keys($fulltext_indexes)), "maj_utf");
  125. $this->io->text($s);
  126. foreach ($fulltext_indexes as $key => $alter){
  127. sql_alter("$alter");
  128. }
  129. }
  130. }
  131. else {
  132. $this->io->text("OK, Rien à faire");
  133. }
  134. }
  135. }
  136. protected function sqlConvertCharsetField($convert, $table, $field, $d, $exception_champs) {
  137. $converted = true;
  138. if (strpos($d,"COLLATE")!==false AND strpos($d,"latin1_bin")!==false) {
  139. $dutf = str_replace("latin1","utf8",$d);
  140. sql_alter($q="TABLE $table change $field $field $dutf");
  141. spip_log("ALTER $q","maj_utf");
  142. }
  143. else {
  144. if (strpos($d,"COLLATE")!==false) {
  145. $d = preg_replace(",COLLATE\s+\w+\s,i","",$d);
  146. }
  147. #if (strpos($d,"NOT NULL")!==false and strpos($d,"DEFAULT") === false) {
  148. # $d .= ' DEFAULT \'\'';
  149. #}
  150. // on passe par un format binaire pour empecher toute conversion de conversion par mysql
  151. // sinon le passage direct latin1=>utf8 entreune une conversion de contenu
  152. // si certains champs ont besoin d'une conversion SQL on les passes dans
  153. // $exceptions['table']['champ'] pour convertir un champ
  154. // $exceptions['table']['*'] pour convertir tous les champs de la table
  155. if (
  156. ($convert and (in_array($field, $exception_champs) or in_array('*', $exception_champs)))
  157. or (!$convert and !in_array($field, $exception_champs) and !in_array('*', $exception_champs))
  158. ){
  159. $dbin = str_replace("latin1","binary",$d);
  160. sql_alter($q="TABLE $table change $field $field $dbin");
  161. #echo $q . "\n";
  162. spip_log("ALTER $q","maj_utf");
  163. $converted = false;
  164. }
  165. $dutf = str_replace("latin1","utf8 COLLATE utf8_general_ci",$d);
  166. sql_alter($q="TABLE $table change $field $field $dutf");
  167. #echo $q . "\n";
  168. spip_log("ALTER $q","maj_utf");
  169. }
  170. $this->io->text("$field : $d =>". ($converted ? " (CONVERTED) " : '') ." $dutf");
  171. }
  172. protected function sqlConvertGetFulltextIndex($desc) {
  173. $indexes = array();
  174. foreach ($desc['key'] as $key => $d) {
  175. if (0 === strpos($key, 'FULLTEXT ')) {
  176. $key = explode(' ', trim(substr($key, strlen('FULLTEXT '))));
  177. $key = end($key);
  178. $indexes[$key] = 'TABLE ' . $desc['table'] . " ADD FULLTEXT $key ($d)";
  179. }
  180. }
  181. return $indexes;
  182. }
  183. }