Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?php
- /* Replace with your connection parameters & database name */
- $hostname = 'localhost';
- $username = 'root';
- $password = '';
- $database = 'test';
- $pdo = new PDO("mysql:host=$hostname", $username, $password);
- $converter = new Converter($pdo, $database);
- echo $converter->getSQL();
- class Converter
- {
- /**
- * @var PDO
- */
- private $pdo;
- /**
- * @var string
- */
- private $schemaName;
- /**
- * @param PDO $pdo
- * @param string $schemaName
- */
- public function __construct(PDO $pdo, $schemaName)
- {
- $this->pdo = $pdo;
- $this->schemaName = $schemaName;
- }
- /**
- * @return string
- */
- public function getSQL()
- {
- $tables = $this->loadSchemaInfo('TABLES');
- $columns = $this->loadSchemaInfo('COLUMNS');
- $changeset = [];
- foreach ($tables as $row) {
- $table = $row['TABLE_NAME'];
- $collation = $row['TABLE_COLLATION'];
- $collation = $this->convertCollation($collation);
- if ($collation === null) {
- continue;
- }
- $changeset[$table][] = "COLLATE $collation";
- }
- foreach ($columns as $row) {
- $table = $row['TABLE_NAME'];
- $column = $row['COLUMN_NAME'];
- $collation = $row['COLLATION_NAME'];
- $collation = $this->convertCollation($collation);
- if ($collation === null) {
- continue;
- }
- $definition = $this->getColumnDefinition($row);
- $changeset[$table][] = "MODIFY COLUMN $column $definition COLLATE $collation";
- }
- $sql = '';
- foreach ($changeset as $table => $changes) {
- $lastKey = count($changes) - 1;
- $sql .= "ALTER TABLE $table" . PHP_EOL;
- foreach ($changes as $key => $change) {
- $sql .= " $change";
- $sql .= ($key === $lastKey) ? ';' : ',';
- $sql .= PHP_EOL;
- }
- $sql .= PHP_EOL;
- }
- return $sql;
- }
- /**
- * @param string $name
- *
- * @return array
- */
- private function loadSchemaInfo($name)
- {
- $statement = $this->pdo->prepare("SELECT * FROM INFORMATION_SCHEMA.$name where table_schema = ?");
- $statement->execute([$this->schemaName]);
- return $statement->fetchAll();
- }
- /**
- * @param string|null $collation
- *
- * @return string|null
- */
- private function convertCollation($collation)
- {
- if ($collation === null) {
- return null;
- }
- $collation = preg_replace('/^utf8_/', 'utf8mb4_', $collation, -1, $count);
- if ($count !== 1) {
- return null;
- }
- return $collation;
- }
- /**
- * @param array $column
- *
- * @return string
- */
- private function getColumnDefinition(array $column)
- {
- $definition = $column['COLUMN_TYPE'];
- if ($column['IS_NULLABLE'] === 'YES') {
- $definition .= ' NULL';
- } else {
- $definition .= ' NOT NULL';
- }
- if ($column['COLUMN_DEFAULT'] !== null) {
- $definition .= ' DEFAULT ' . $column['COLUMN_DEFAULT'];
- }
- return $definition;
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement