daily pastebin goal
2%
SHARE
TWEET

Untitled

a guest May 31st, 2017 70 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. <?php
  2.  
  3. /* Replace with your connection parameters & database name */
  4.  
  5. $hostname = 'localhost';
  6. $username = 'root';
  7. $password = '';
  8. $database = 'test';
  9.  
  10. $pdo = new PDO("mysql:host=$hostname", $username, $password);
  11.  
  12. $converter = new Converter($pdo, $database);
  13. echo $converter->getSQL();
  14.  
  15. class Converter
  16. {
  17.     /**
  18.      * @var PDO
  19.      */
  20.     private $pdo;
  21.  
  22.     /**
  23.      * @var string
  24.      */
  25.     private $schemaName;
  26.  
  27.     /**
  28.      * @param PDO    $pdo
  29.      * @param string $schemaName
  30.      */
  31.     public function __construct(PDO $pdo, $schemaName)
  32.     {
  33.         $this->pdo        = $pdo;
  34.         $this->schemaName = $schemaName;
  35.     }
  36.  
  37.     /**
  38.      * @return string
  39.      */
  40.     public function getSQL()
  41.     {
  42.         $tables  = $this->loadSchemaInfo('TABLES');
  43.         $columns = $this->loadSchemaInfo('COLUMNS');
  44.  
  45.         $changeset = [];
  46.  
  47.         foreach ($tables as $row) {
  48.             $table = $row['TABLE_NAME'];
  49.             $collation = $row['TABLE_COLLATION'];
  50.             $collation = $this->convertCollation($collation);
  51.  
  52.             if ($collation === null) {
  53.                 continue;
  54.             }
  55.  
  56.             $changeset[$table][] = "COLLATE $collation";
  57.         }
  58.  
  59.         foreach ($columns as $row) {
  60.             $table = $row['TABLE_NAME'];
  61.             $column = $row['COLUMN_NAME'];
  62.             $collation = $row['COLLATION_NAME'];
  63.             $collation  = $this->convertCollation($collation);
  64.  
  65.             if ($collation === null) {
  66.                 continue;
  67.             }
  68.  
  69.             $definition = $this->getColumnDefinition($row);
  70.  
  71.             $changeset[$table][] = "MODIFY COLUMN $column $definition COLLATE $collation";
  72.         }
  73.  
  74.         $sql = '';
  75.  
  76.         foreach ($changeset as $table => $changes) {
  77.             $lastKey = count($changes) - 1;
  78.  
  79.             $sql .= "ALTER TABLE $table" . PHP_EOL;
  80.  
  81.             foreach ($changes as $key => $change) {
  82.                 $sql .= "  $change";
  83.                 $sql .= ($key === $lastKey) ? ';' : ',';
  84.                 $sql .= PHP_EOL;
  85.             }
  86.  
  87.             $sql .= PHP_EOL;
  88.         }
  89.  
  90.         return $sql;
  91.     }
  92.  
  93.     /**
  94.      * @param string $name
  95.      *
  96.      * @return array
  97.      */
  98.     private function loadSchemaInfo($name)
  99.     {
  100.         $statement = $this->pdo->prepare("SELECT * FROM INFORMATION_SCHEMA.$name where table_schema = ?");
  101.         $statement->execute([$this->schemaName]);
  102.  
  103.         return $statement->fetchAll();
  104.     }
  105.  
  106.     /**
  107.      * @param string|null $collation
  108.      *
  109.      * @return string|null
  110.      */
  111.     private function convertCollation($collation)
  112.     {
  113.         if ($collation === null) {
  114.             return null;
  115.         }
  116.  
  117.         $collation = preg_replace('/^utf8_/', 'utf8mb4_', $collation, -1, $count);
  118.  
  119.         if ($count !== 1) {
  120.             return null;
  121.         }
  122.  
  123.         return $collation;
  124.     }
  125.  
  126.     /**
  127.      * @param array $column
  128.      *
  129.      * @return string
  130.      */
  131.     private function getColumnDefinition(array $column)
  132.     {
  133.         $definition = $column['COLUMN_TYPE'];
  134.  
  135.         if ($column['IS_NULLABLE'] === 'YES') {
  136.             $definition .= ' NULL';
  137.         } else {
  138.             $definition .= ' NOT NULL';
  139.         }
  140.  
  141.         if ($column['COLUMN_DEFAULT'] !== null) {
  142.             $definition .= ' DEFAULT ' . $column['COLUMN_DEFAULT'];
  143.         }
  144.  
  145.         return $definition;
  146.     }
  147. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top