<?php
  $message = "

  Usage php database_to_utf8mb4.php [database name]
  
";
  if($argc==1)
  {
    echo $message;
    exit();
  }
  echo "Run: {$argv[1]}...\n";
  
  $DB_HOST="localhost";
  $DB_LOGIN="我的資料庫帳號";
  $DB_PASSWORD="我的資料庫密碼";
  $DB_NAME=$argv[1];
  $DB_KIND="mysql";
  $is_debug=true;
  try{    
    $pdo = new PDO("{$DB_KIND}:dbname={$DB_NAME};host={$DB_HOST}",$DB_LOGIN,$DB_PASSWORD);
  }catch(PDOException $Exception){
    echo "資料庫未連線...";
    //print_r($Exception);
    exit();
  }
  $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
  $pdo->query("
  SET NAMES UTF8mb4;
  SET time_zone = '+8:00';
  SET CHARACTER_SET_CLIENT=utf8mb4;
  SET CHARACTER_SET_RESULTS=utf8mb4;
  ");
  $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
  require '../inc/include.php';
  
  //DB to utf8mb4
  $SQL = "ALTER DATABASE {$DB_NAME} CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;";
  echo "{$SQL}\n";
  execSQL_SAFE($SQL,ARRAY());
  
  $SQL = "SHOW TABLES";
  $ra = selectSQL_SAFE($SQL,ARRAY());    
  for($i=0,$max_i=count($ra);$i<$max_i;$i++)
  {
    $table_name = $ra[$i]["Tables_in_{$DB_NAME}"];
    
    //table to utf8mb4
    $SQL = "ALTER TABLE `{$table_name}` DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci;";
    echo "{$SQL}\n";
    execSQL_safe($SQL,ARRAY());
    
    //From : https://dev.mysql.com/doc/refman/8.0/en/show-columns.html
    $SQL = "SELECT * FROM information_schema.`COLUMNS` WHERE  table_schema = '{$DB_NAME}' AND table_name = '{$table_name}';";
    $ra_c = selectSQL_SAFE($SQL,ARRAY());
    print_r($ra_c);
    /*
    (
    
            [TABLE_CATALOG] => def
            [TABLE_SCHEMA] => test
            [TABLE_NAME] => 鄉鎮代碼
            [COLUMN_NAME] => TOWNNAME
            [ORDINAL_POSITION] => 3
            [COLUMN_DEFAULT] => NULL
            [IS_NULLABLE] => YES
            [DATA_TYPE] => varchar
            [CHARACTER_MAXIMUM_LENGTH] => 20
            [CHARACTER_OCTET_LENGTH] => 60
            [NUMERIC_PRECISION] =>
            [NUMERIC_SCALE] =>
            [DATETIME_PRECISION] =>
            [CHARACTER_SET_NAME] => utf8   ----------->如果有值，才要轉
            [COLLATION_NAME] => utf8_general_ci
            [COLUMN_TYPE] => varchar(20)
            [COLUMN_KEY] =>
            [EXTRA] =>
            [PRIVILEGES] => select,insert,update,references
            [COLUMN_COMMENT] =>
            [IS_GENERATED] => NEVER
            [GENERATION_EXPRESSION] =>
        )
    */
    //print_r($ra_c);
    for($j=0,$max_j=count($ra_c);$j<$max_j;$j++)
    {
      //echo ($ra_c[$j]['CHARACTER_SET_NAME']=="")."\n";
      if($ra_c[$j]['CHARACTER_SET_NAME']=="")
      {
        continue; //不用轉的跳過
      }
      $column_name = $ra_c[$j]['COLUMN_NAME'];
      $COLUMN_TYPE = $ra_c[$j]['COLUMN_TYPE'];
      $SQL = "ALTER TABLE `{$table_name}` MODIFY COLUMN `{$column_name}` {$COLUMN_TYPE} CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;";
      echo "{$SQL}\n";
      execSQL_SAFE($SQL,ARRAY());
    }
  }