SqlToSchema.php

<?php
class ZendDbSchema_Filter_Db_Schema_Table_Mysql_SqlToSchema
    implements Zend_Filter_Interface
{
    protected $_asObject;

    public function __construct($asObject = true)
    {
        $this->_asObject = (bool) $asObject;
    }

    public function filter($value)
    {
        $schema = array();
        if (!empty($value)) {
            $data = explode(PHP_EOL, $value);
            $data = array_map('trim', $data);

            $tableName = array_shift($data);
            if (preg_match('|CREATE TABLE `([\w\d\_\-]+)|i', $tableName, $matches)) {
                $schema[ZendDbSchema_Db_Schema_Table::NAME_KEY] = $matches['1'];
            }

            $tableInfo = array_pop($data);
            if (preg_match('|ENGINE=(\w+)|i', $tableInfo, $matches)) {
                $schema['engine'] = $matches['1'];
            }
            if (preg_match('|CHARSET=([\w\d\_\-]+)|i', $tableInfo, $matches)) {
                $schema[ZendDbSchema_Db_Schema_Table::CHARSET_KEY] = $matches['1'];
            }

            $schema[ZendDbSchema_Db_Schema_Table::COLUMNS_KEY] = array();
            $schema[ZendDbSchema_Db_Schema_Table::INDEXES_KEY]   = array();
            $schema[ZendDbSchema_Db_Schema_Table::FOREIGNS_KEY] = array();

            foreach ($data as $row) {
                $row = trim($row, ",");

                if (0 === stripos($row, 'PRIMARY KEY')) {
                    preg_match_all('|\w+|', substr($row, 14), $matches);
                    $schema[ZendDbSchema_Db_Schema_Table::PRIMARY_KEY] = array(
                        ZendDbSchema_Db_Schema_Table_PrimaryKey::NAME_KEY    => 'primary',
                        ZendDbSchema_Db_Schema_Table_PrimaryKey::TYPE_KEY    => 'primary',
                        ZendDbSchema_Db_Schema_Table_PrimaryKey::COLUMNS_KEY => $matches['0']
                    );
                } elseif (0 === stripos($row, 'FULLTEXT')) {
                    $row = preg_replace('/^FULLTEXT (KEY|INDEX)?/i', '', $row, 1);
                    preg_match_all('|\w+|', $row, $matches);
                    $name = array_shift($matches['0']);
                    $schema[ZendDbSchema_Db_Schema_Table::INDEXES_KEY][$name] = array(
                        ZendDbSchema_Db_Schema_Table_Index::NAME_KEY    => $name,
                        ZendDbSchema_Db_Schema_Table_Index::TYPE_KEY    => 'fulltext',
                        ZendDbSchema_Db_Schema_Table_Index::COLUMNS_KEY => $matches['0']
                    );
                } elseif (0 === stripos($row, 'UNIQUE')) {
                    $row = preg_replace('/^UNIQUE (KEY|INDEX)?/i', '', $row, 1);
                    preg_match_all('|\w+|', $row, $matches);
                    $name = array_shift($matches['0']);
                    $schema[ZendDbSchema_Db_Schema_Table::INDEXES_KEY][$name] = array(
                        ZendDbSchema_Db_Schema_Table_Index::NAME_KEY    => $name,
                        ZendDbSchema_Db_Schema_Table_Index::TYPE_KEY    => 'unique',
                        ZendDbSchema_Db_Schema_Table_Index::COLUMNS_KEY => $matches['0']
                    );
                } elseif (0 === stripos($row, 'CONSTRAINT')) {
                    preg_match('|CONSTRAINT ?`(\w+)|i', $row, $matches);

                    $keyName = $matches['1'];
                    $key = array();

                    if (preg_match_all('|\([^)]*\)|i', $row, $matches)) {

                        $columns = $matches['0']['0'];
                        $refColumns = $matches['0']['1'];

                        preg_match_all('|\w+|i', $columns, $matches);
                        $columns = $matches['0'];
                        preg_match_all('|\w+|i', $refColumns, $matches);
                        $refColumns = $matches['0'];
                        $key[ZendDbSchema_Db_Schema_Table_ForeignKey::COLUMNS_KEY] = array_combine($columns, $refColumns);
                    }
                    if (preg_match('|REFERENCES ?`(\w+)|i', $row, $matches)) {
                        $key[ZendDbSchema_Db_Schema_Table_ForeignKey::REFERENCE_KEY] = $matches['1'];
                    }
                    if (preg_match('|ON DELETE (\w+)|i', $row, $matches)) {
                        $key[ZendDbSchema_Db_Schema_Table_ForeignKey::ONDELETE_KEY] = $matches['1'];
                    }
                    if (preg_match('|ON UPDATE (\w+)|i', $row, $matches)) {
                        $key[ZendDbSchema_Db_Schema_Table_ForeignKey::ONUPDATE_KEY] = $matches['1'];
                    }

                    $schema[ZendDbSchema_Db_Schema_Table::FOREIGNS_KEY][$keyName] = $key;
                } elseif (0 === stripos($row, '`')) {
                    $key = array(
                        'type'     => null,
                        'default'  => null,
                        'nullable' => null,
                        'signed' => null,
                        'comment'  => null,
                    );
                    if (preg_match('|DEFAULT (\w+)|i', $row, $matches)) {
                        $key['default'] = $matches['1'];
                    }
                    if (preg_match('|COMMENT \'([^\']+)\'|i', $row, $matches)) {
                        $key['comment'] = $matches['1'];
                    }
                    if (preg_match('|NOT NULL|i', $row, $matches)) {
                        $key['nullable'] = false;
                    } else {
                        $key['nullable'] = true;
                    }
                    if (preg_match('|AUTO_INCREMENT|i', $row, $matches)) {
                        $key['autoincrement'] = true;
                    }
                    if (preg_match('|^`(\w+)` (\w+)\((\d+)\)|i', $row, $matches)) {
                        $key['type'] = $matches['2'];
                        $key['length'] = $matches['3'];
                        $keyName = $matches['1'];
                        $schema[ZendDbSchema_Db_Schema_Table::COLUMNS_KEY][$keyName] = $key;
                    }
                }
            }
        }
        if (!$this->_asObject) {
            return $schema;
        }
        return new ZendDbSchema_Db_Schema_Table($schema[ZendDbSchema_Db_Schema_Table::NAME_KEY], $schema);
    }
}