#!/usr/bin/env php
<?php
include '../framework/helper.class.php';
include '../lib/dbh/dbh.class.php';

$config = new stdclass();

$config->db      = new stdclass();
$config->default = new stdclass();
include '../config/my.php';

if (!file_exists('dmconfig.php')) {
    print("Please create the config file named 'dmconfig.php' in this directory. Config example:");
    print <<<EOT


<?php
\$dmConfig = new stdclass();
\$dmConfig->driver   = 'dm';
\$dmConfig->host     = '10.0.7.242';
\$dmConfig->port     = '5236';
\$dmConfig->name     = 'lybiz85';
\$dmConfig->user     = 'SYSDBA';
\$dmConfig->encoding = 'UTF8';
\$dmConfig->prefix   = 'zt_';
\$dmConfig->password = 'SYSDBA001';


EOT;
    exit;
}

echo "开始迁移MySQL数据到达梦数据库\n\n";

include 'dmconfig.php';

function formatSQL($sql)
{
    $fieldsBegin = stripos($sql, 'select');
    $fieldsEnd   = stripos($sql, 'from');
    $fields      = substr($sql, $fieldsBegin + 6, $fieldsEnd - $fieldsBegin - 6);
    $fieldList   = preg_split("/,(?![^(]+\))/", $fields);
    foreach ($fieldList as $key => $field) {
        $aliasPos = stripos($field, ' AS ');
        $subField = substr($field, 0, $aliasPos);
        if (stripos($field, 'SUM(') === 0) $subField = substr($subField, 4, -1);

        $fieldParts = preg_split("/\+(?![^(]+\))/", $subField);
        foreach ($fieldParts as $pkey => $fieldPart) {
            $originField = trim($fieldPart);
            if (stripos($originField, 'if(') === false) continue;
            $fieldParts[$pkey] = formatDmIfFunction($originField);
        }
        $fieldList[$key] = str_replace($subField, implode(' + ', $fieldParts), $field);
    }
    $fields = implode(',', $fieldList);
    return substr($sql, 0, $fieldsBegin + 6) . $fields . substr($sql, $fieldsEnd);
}

function formatDmIfFunction($field)
{
    preg_match('/if\(.+\)+/i', $field, $matches);

    $if = $matches[0];
    if (substr_count($if, '(') == 1) {
        $pos = strpos($if, ')');
        $if  = substr($if, 0, $pos + 1);
    }

    /* fix sum(if(..., 1, 0)) , count(if(..., 1, 0)) */
    if (substr($if, strlen($if) - 2) == '))' and (stripos($field, 'sum(') == 0 or stripos($field, 'count(') == 0)) $if = substr($if, 0, strlen($if) - 1);

    $parts = explode(',', substr($if, 3, strlen($if) - 4)); // remove 'if(' and ')'
    $case  = 'CASE WHEN ' . implode(',', array_slice($parts, 0, count($parts) - 2)) . ' THEN ' . $parts[count($parts) - 2] . ' ELSE ' . $parts[count($parts) - 1] . ' END';
    $field = str_ireplace($if, $case, $field);

    return $field;
}

$mysql = new dbh($config->db);

$stmt = $mysql->query('SHOW TABLES');
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
$tables  = array();
$views   = array();

$indexes = array(); //普通索引
$tablePrimary   = array(); //表的主键

foreach ($data as $table) {
    $table = current($table);

    /* Table. */
    if (strpos($table, 'zt_') === 0) {
        $stmt  = $mysql->prepare('DESC ' . $table);
        $stmt->execute();
        $fields = $stmt->fetchAll(PDO::FETCH_ASSOC);

        /* Table. */
        $tables[$table] = array();
        foreach ($fields as $field) {
            $type = strtolower(explode('(', $field['Type'])[0]);
            $type = trim(str_replace('unsigned', '', $type));
            switch ($type) {
                case 'int':
                case 'mediumint':
                case 'smallint':
                case 'tinyint':
                    $type = 'integer';
                    break;
                case 'enum':
                case 'varchar':
                case 'char':
                    $type = 'varchar(1000)';
                    break;
                case 'mediumtext':
                case 'longtext':
                    $type = 'text';
                    break;
            }

            $tables[$table][$field['Field']] = array(
                'type'          => $type,
                'name'          => $field['Field'],
                'null'          => $field['Null'] == 'YES',
                'default'       => $field['Default'],
                //'isPrimary'     => $field['Key'] == 'PRI', // 不需要从DESC读取索引
                'autoIncrement' => $field['Extra'] == 'auto_increment',
            );
        }

        /* Index. */
        $stmt  = $mysql->prepare('SHOW INDEX FROM ' . $table);
        $stmt->execute();
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if ($result) {
            foreach ($result as $item) {
                $keyName = $item['Key_name'];

                /* 主键索引在创建表的时候一起创建。 */
                if ($keyName == 'PRIMARY') {
                    if (!isset($tablePrimary[$table])) $tablePrimary[$table] = array();
                    $tablePrimary[$table][(int)$item['Seq_in_index'] - 1] = $item['Column_name'];
                    continue;
                }

                /* 其他索引单独创建。 */
                if (!isset($indexes[$table . $keyName])) $indexes[$table . $keyName] = array('table' => $table, 'name' => $keyName, 'type' => $item['Index_type'], 'isUnique' => !$item['Non_unique'], 'cols' => array());

                /* 达梦的全文索引只支持一个字段。 */
                if ($item['Index_type'] == 'FULLTEXT' && $item['Column_name'] != 'title') continue;

                $indexes[$table . $keyName]['cols'][(int)$item['Seq_in_index'] - 1] = $item['Column_name'];
            }
        }
    }
    /* View. */ elseif (strpos($table, 'ztv_') === 0) {
        $stmt  = $mysql->prepare('SHOW CREATE VIEW ' . $table);
        $stmt->execute();
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $pos = stripos($result[0]['Create View'], ' as ');
        $sql = substr($result[0]['Create View'], $pos + 4);
        $sql = str_replace('`', '"', $sql);
        $views[$table] = array(
            'sql' => formatSQL($sql),
        );
    }
}

echo "初始化表空间和用户\n";
$dm = new dbh($dmConfig, false);

/* Delete. */
$res = $dm->query("SELECT * FROM dba_users WHERE username = '{$dmConfig->name}'")->fetchAll();
if (!empty($res)) {
    $dropUser = "DROP USER \"{$dmConfig->name}\" CASCADE";
    $dm->rawQuery($dropUser);
}

$tableSpace = strtoupper($dmConfig->name);
$res = $dm->query("SELECT * FROM dba_data_files WHERE TABLESPACE_NAME = '$tableSpace'")->fetchAll();
if (!empty($res)) {
    $dropTableSpace = "DROP TABLESPACE \"$tableSpace\"";

    $dm->rawQuery($dropTableSpace);
}

/* Create. */
//$createTableSpace = "CREATE TABLESPACE \"$tableSpace\" DATAFILE '{$dmConfig->name}.DBF' size 150 AUTOEXTEND ON";
//$createUser       = "CREATE USER \"{$dmConfig->name}\" IDENTIFIED by \"{$dmConfig->password}\" DEFAULT TABLESPACE \"{$dmConfig->name}\" DEFAULT INDEX TABLESPACE \"{$dmConfig->name}\"";
//
//$dm->query($createTableSpace);
//$dm->query($createUser);

$createSchema = "CREATE SCHEMA \"{$dmConfig->name}\" AUTHORIZATION \"{$dmConfig->user}\"";
$dm->query($createSchema);

echo "生成达梦数据表结构\n";
$dm = new dbh($dmConfig);

$identityTables = array(); //标记有自增字段的表，插入数据的时候会判断

/* 插入表结构。 */
foreach ($tables as $table => $fields) {
    $sql = "CREATE TABLE IF NOT EXISTS \"$table\" (\n";

    foreach (array_values($fields) as $key => $field) {
        $sql .= '"' . $field['name'] . '" ' . $field['type'] . ($field['null'] ? ' NULL ' : ' NOT NULL ');
        if($field['default'] == '0000-00-00') $field['default'] = NULL;
        if($field['default'] !== NULL && $field['default'] != 'CURRENT_TIMESTAMP') $sql .= " DEFAULT '{$field['default']}' ";

        if ($field['autoIncrement']) {
            $sql .= " IDENTITY(1, 1) ";
            $identityTables[$table] = $table;
        }

        $sql .= $key == count(array_values($fields)) - 1 && !isset($tablePrimary[$table]) ? "\n" : ",\n";
    }

    if (isset($tablePrimary[$table])) {
        $primary = array();
        ksort($tablePrimary[$table]);
        foreach ($tablePrimary[$table] as $col) $primary[] = '"' . $col . '"';

        $sql .= "NOT CLUSTER PRIMARY KEY (" . implode(',', $primary) . ")"; // 只有NOT CLUSTER可以删除
    }

    $sql .= "\n)\n";

    $dm->rawQuery($sql);
}

echo "生成达梦表索引\n";
foreach ($indexes as $index) {
    $name = strtolower($index['table']) . '_' . $index['name'];
    foreach ($index['cols'] as $col) {
        $fields = array();
        ksort($index['cols']);
        foreach ($index['cols'] as $col) {
            $fields[] = '"' . $col . '"';
        }
    }

    $sql = 'CREATE ';
    if ($index['isUnique']) // 唯一索引
    {
        $sql .= ' UNIQUE ';
    } elseif ($index['type'] == 'FULLTEXT') // 全文索引
    {
        $sql .= ' CONTEXT ';
    }

    $sql .= ' INDEX "' . $name . '" ON `' . $index['table'] . '` (' . implode(',', $fields) . ')';

    $dm->rawQuery($sql);
}

echo "生成达梦视图\n";
/* 插入视图结构。 */
foreach ($views as $view => $info) {
    $sql = str_replace('convert(', '(', $info['sql']);
    $sql = str_replace('using utf8mb3', '', $sql);
    $sql = str_replace('using utf8_general_ci', '', $sql);
    $dm->rawQuery("CREATE OR REPLACE VIEW \"$view\" AS " . $sql);
}

echo "开始插入数据，请耐心等待...";
foreach ($tables as $table => $fields) {
    $dm->rawQuery("DELETE FROM `$table`");
    if ($table == 'zt_compile') continue;
    $rows = $mysql->query("SELECT * FROM `$table`")->fetchAll(PDO::FETCH_ASSOC);
    if (empty($rows)) continue;

    $fs = array();
    foreach ($fields as $field) $fs[] = '"' . $field['name'] . '"';
    $fs = implode(',', $fs);

    $template = "INSERT INTO \"$table\"($fs) VALUES";

    $step  = 5000;
    $begin = 0;
    while ($begin < count($rows)) {
        $data  = array();
        for ($key = $begin; $key < $begin + $step; $key++) {
            if (!isset($rows[$key])) continue;
            $row    = $rows[$key];
            $values = array();
            foreach ($fields as $field) {
                $value = str_replace("'", "''''", $row[$field['name']]);
                $value = "'{$value}'";
                if ($value == "'0000-00-00 00:00:00'" || $value == "'0000-00-00'") {
                    $null = $tables[$table][$field['name']]['null'];
                    $value = $null ? 'NULL' : '1970-01-01';
                }
                $values[] = $value;
            }
            $values = '(' . implode(',', $values) . ')';
            $data[] = $values;
        }

        $begin += $step;
        if (empty($data)) continue;
        $sql = $template . implode(',', $data) . ';';

        if (isset($identityTables[$table])) $sql = "SET IDENTITY_INSERT $table ON;" . $sql;

        //file_put_contents('./a.sql', $sql. "\n", FILE_APPEND);
        $dm->rawQuery($sql);
    }
}

echo "\n迁移成功，修改my.php测试一下吧\n\n";