/Kernel/Entity/DatabaseSchema.php

<?php
namespace Core\Kernel\Entity;

use Core\Kernel\Entity\SharedCache;
use Core\Kernel\Driver\CacheController;

class DatabaseSchema extends SharedCache
{
    protected $_maxcache = 1440;

    function __construct()
    {
        $this->onCacheLimit();
    }

    protected function refreshTableList()
    {
        $this->_cached_data = array();

        $table_list = (array)\App::getDB()->fetch_all('SHOW TABLES');
        foreach( $table_list as $table ) {
            $table_name = current($table);
            $this->_cached_data[ $table_name ]= $this->commonFromDatabase( $table_name );
        }
    }

    protected function commonFromDatabase( $table_name )
    {
        $common = array( 'index' => array(), 'col' => array(), 'key' => array(), 'constraint'=>array(), 'engine'=>'', 'charset'=>'' );

        $create = \App::getDB()->fetch_all("SHOW CREATE TABLE {$table_name}");
        if( is_array($create) && isset($create[0]['Create Table']) ) {
            $create = $create[0]['Create Table'];
            $preg_table_name = "/^CREATE\s+(?:TEMPORARY\s+)?TABLE\s+(?:IF NOT EXISTS\s+)?([^\s]+)\s+\((?:\s+)?/i";
            $preg_table_settings = "/\)\s+(ENGINE)=([^\s]+)(?:\s+(AUTO_INCREMENT)=([^\s]+))?(?:\s+(DEFAULT CHARSET)=([^\s]+))?$/i";
            $preg_table_primary = "/^(?:\s+)?PRIMARY KEY\s+\(([^\s]+)\)/i";
            $preg_table_key = "/^(?:\s+)?KEY\s+([^\s]+)\s+\(([^\s]+)\)/i";
            $preg_table_constraint = "/^(?:\s+)?CONSTRAINT\s+([^\s]+)\s+FOREIGN KEY\s+\(([^\s]+)\)\s+REFERENCES\s+([^\s]+)\s+\(([^\s]+)\)(?:\s+(ON\s+[^\s]+)\s+(RESTRICT|CASCADE|SET NULL)|$|,)(?:\s+(ON\s+[^\s]+)\s+(RESTRICT|CASCADE|SET NULL)|$|,)/i";
            $preg_table_col = "/^(?:\s+)?\s+([^\s]+)\s+([^\s]+)/i";

            $table_info = array();
            if( preg_match($preg_table_name, $create, $name_matches) && preg_match($preg_table_settings, $create, $settings_matches) ) {
                for( $i=1; $i<count($settings_matches); $i+=2 ) {
                    switch($settings_matches[$i]) {
                        case 'ENGINE': $common['engine'] = $settings_matches[$i+1]; break;
                        case 'DEFAULT CHARSET': $common['charset'] =$settings_matches[$i+1]; break;
                    }
                }

                foreach( explode("\n",trim(substr($create, strpos($create,'(')+1, strrpos($create,')')-strpos($create,'(')-1 ))) as $table_col ) {
                    if( preg_match($preg_table_constraint, $table_col, $matches) ) {
                        $common['constraint'][ $name=str_replace(array('`','"',"'"),'',$matches[1]) ]= array(
                                'foreign' => str_replace(array('`','"',"'"),'',$matches[2]),
                                'reftable' => str_replace(array('`','"',"'"),'',$matches[3]),
                                'refcolumns' => explode(',',str_replace(array('`','"',"'"),'',$matches[4]))
                        );

                        if( isset($matches[5]) && isset($matches[6]) && $matches[5] == 'ON DELETE' ) {
                            if( isset($matches[7]) && isset($matches[8]) && $matches[7] == 'ON UPDATE' ) {
                                $common['constraint'][ $name ]['onupdate'] = $matches[8];
                            }
                            $common['constraint'][ $name ]['ondelete'] = $matches[6];
                        } elseif( isset($matches[5]) && isset($matches[6]) && $matches[5] == 'ON UPDATE'  ) {
                            $common['constraint'][ $name ]['onupdate'] = $matches[6];
                            if( isset($matches[7]) && isset($matches[8]) && $matches[7] == 'ON DELETE' ) {
                                $common['constraint'][ $name ]['ondelete'] = $matches[8];
                            }
                        }
                        ksort($common['constraint'][ $name ]);

                    }
                }
                ksort($common['constraint']);
            }
        }

        foreach( (array)\App::getDB()->fetch_all( "SHOW INDEX FROM {$table_name}" ) as $key ) {
            $type = $key['Non_unique'] == '0' ? 'UNIQUE' : 'INDEX';
            if( !isset($common['index'][ $key['Key_name'] ])) {
                $common['index'][ $key['Key_name'] ] = array(
                        'type' => $type,
                        'columns' => array($key['Column_name'])
                );
            } else {
                $common['index'][ $key['Key_name'] ]['columns'][]=$key['Column_name'];
            }
            if( $type == 'UNIQUE' || $key['Key_name'] == 'PRIMARY' ) {
                $common['key'][]=$key['Column_name'];
            }
        }

        foreach( (array)\App::getDB()->fetch_all( "DESCRIBE {$table_name}" ) as $col ) {
            $nullable = $col['Null'] != 'NO';
            $common['col'][ $col['Field'] ] = array(
                    'type' => $col['Type'],
                    'null' => $nullable ? ' NULL' : ' NOT NULL',
                    'default' => $col['Default'] !== NULL ? " DEFAULT '{$col['Default']}'" : ( $nullable ? ' DEFAULT NULL' : ''),
                    'extra' => empty($col['Extra']) ? '' : " {$col['Extra']}"
            );
        }


        return $common;
    }

    function onCacheLimit()
    {
        $this->_cached = time();
        $this->refreshTableList();
        \Core\Kernel\Driver\CacheController::putSharedObject( $this );
    }

    public function describe( $table )
    {
        if( isset($this->_cached_data[ (string)$table ]) ) {
            return $this->_cached_data[ (string)$table ];
        } else {
            throw new \Exception("TABLE NOT FOUND");
        }
    }

    public function schemaUpdate()
    {
        $this->refreshTableList();
        $result = "";
        foreach( $this->collectSchemas() as $table ) {
            try {
                $current = $this->describe($table['name']);
                $result .= $this->updateTable( $current, $table );
            } catch( \Exception $e ) {
                $result .= $this->createTable( $table );
            }
        }
        if( !empty($result) ) $this->refreshTableList();
        return $result;
    }

    /**
     * @param array $current from database
     * @param SimpleXmlElement $table from schema file
     */
    protected function updateTable( $current, $table )
    {
        $result = "";

        if( strtolower($current['engine']) != strtolower($table['engine']) ) {
            $result .= $SQL = "ALTER TABLE `{$table['name']}` ENGINE = {$table['engine']}\n";
            \App::getDB()->exec($SQL);
        }

        if( strtolower($current['charset']) != strtolower($table['charset']) ) {
            $charset = strtolower($table['charset']);
            $result .= $SQL = "ALTER TABLE `{$table['name']}` CONVERT TO CHARACTER SET {$charset}\n";
            \App::getDB()->exec($SQL);
        }

        $schema_keys = array();
        foreach( $table->xpath("key") as $key ) {
            $schema_keys[ (string)$key['name'] ] = array(
                    'type' => isset($key['unique']) && $key['unique'] == 'true' ? 'UNIQUE' : 'INDEX',
                    'columns' => explode(',', (string)$key['columns'])
            );
        }
        $schema_constraint = array();
        foreach( $table->xpath("constraint") as $constraint ) {
            $schema_constraint[ (string)$constraint['name'] ] = array(
                    'foreign' => (string)$constraint['foreign'],
                    'reftable' => (string)$constraint['reftable'],
                    'refcolumns' => explode(',',(string)$constraint['refcolumns'])
            );

            if( isset($constraint['onupdate']) && !empty($constraint['onupdate']) ) {
                $schema_constraint[ (string)$constraint['name'] ]['onupdate'] = (string)$constraint['onupdate'];
            }
            if( isset($constraint['ondelete']) && !empty($constraint['ondelete']) ) {
                $schema_constraint[ (string)$constraint['name'] ]['ondelete'] = (string)$constraint['ondelete'];
            }
            ksort($schema_constraint[ (string)$constraint['name'] ]);
        }
        ksort($schema_constraint);

        /*
         * Compare current - defined indexes/keys and update on difference.
         */
        $keys_mismatch = md5(serialize($current['index'])) != md5(serialize($schema_keys));
        if( $keys_mismatch ) {
            foreach( $current['index'] as $name=>$key ) {
                if( !isset($schema_keys[ $name ]) ||  md5(serialize($key)) != md5(serialize($schema_keys[ $name ])) ) {
                    if( $name == 'PRIMARY' ) {
                        $result .= $SQL = "ALTER TABLE {$table['name']} DROP PRIMARY KEY\n";
                        \App::getDB()->exec($SQL);
                    } else {
                        $result .= $SQL = "ALTER TABLE `{$table['name']}` DROP INDEX `{$name}`\n";
                        \App::getDB()->exec($SQL);
                    }
                } else {
                    unset( $schema_keys[ $name ] ); // If the current index matches with the schema, then delete it from sync-queue.
                }
            }
        }

        /*
         * Compare current - defined foreign keys and update on difference.
        */
        $constraint_mismatch = md5(serialize($current['constraint'])) != md5(serialize($schema_constraint));
        if( $constraint_mismatch ) {
            foreach( $current['constraint'] as $name => $constraint ) {
                if( !isset($schema_constraint[ $name ]) ||  md5(serialize($constraint)) != md5(serialize($schema_constraint[ $name ])) ) {
                        $result .= $SQL = "ALTER TABLE {$table['name']} DROP FOREIGN KEY `{$name}`\n";
                        \App::getDB()->exec($SQL);
                } else {
                    unset( $schema_constraint[ $name ] );
                }
            }
        }

        /*
         * Compare current - defined columns and update on difference.
        */
        $schema_columns = array();
        foreach( $table->xpath("column") as $col ) {
            $nullable = isset($col['nullable']) && $col['nullable'] == 'true';
            $schema_columns[ (string)$col['name'] ] = array(
                    'type' => (string)$col['type'],
                    'null' => $nullable ? ' NULL' : ' NOT NULL',
                    'default' => isset($col['default']) ? " DEFAULT '{$col['default']}'" : ( $nullable ? ' DEFAULT NULL' : ''),
                    'extra' => isset($col['extra']) ? " {$col['extra']}" : ''
            );
        }
        if( md5(serialize($current['col'])) != md5(serialize($schema_columns)) ) {
            $AFTER = "FIRST";
            foreach( $schema_columns as $name => $col ) {
                if( !isset($current['col'][ $name ]) ) {
                    $result .= $SQL = "ALTER TABLE `{$table['name']}` ADD `{$name}` {$col['type']}{$col['null']}{$col['default']}{$col['extra']} {$AFTER};\n";
                    \App::getDB()->exec($SQL);

                } elseif( md5(serialize($current['col'][ $name ])) != md5(serialize($schema_columns[ $name ])) ) {
                    $result .= $SQL = "ALTER TABLE `{$table['name']}` CHANGE `{$name}` `{$name}` {$col['type']}{$col['null']}{$col['default']}{$col['extra']};\n";
                    \App::getDB()->exec($SQL);

                }
                $AFTER = "AFTER `{$name}`";
            }
        }

        /*
         * Re-add keys.
         */
        if( $keys_mismatch ) {
            foreach( $schema_keys as $name=>$key ) {
                $type = $name == 'PRIMARY' ? 'PRIMARY KEY' : "{$key['type']} `{$name}`";
                $result .= $SQL = "ALTER TABLE `{$table['name']}` ADD {$type} (`" . implode('`,`',$key['columns'] ) ."`);\n" ;
                \App::getDB()->exec($SQL);
            }
        }

        /*
         * Re-add constraints.
         */
        if( $constraint_mismatch ) {
            foreach( $schema_constraint as $name=>$constraint) {
                $ondelete = isset($constraint['ondelete']) ? " ON DELETE ".strtoupper($constraint['ondelete']) : '';
                $onupdate = isset($constraint['onupdate']) ? " ON UPDATE ".strtoupper($constraint['onupdate']) : '';
                $result .= $SQL = "ALTER TABLE `{$table['name']}` ADD CONSTRAINT `{$name}` FOREIGN KEY (`{$constraint['foreign']}`) REFERENCES `{$constraint['reftable']}` (`" . implode('`,`',$constraint['refcolumns'] ) ."`){$onupdate}{$ondelete};\n" ;
                \App::getDB()->exec($SQL);
            }
        }

        return $result;
    }

    protected function createTable( $table )
    {
        $COLUMNS=array();
        foreach( $table->xpath("column") as $column ) {
            $default = isset($column['default']) ? " DEFAULT '{$column['default']}'" : '';
            $nullable = isset($column['nullable']) && $column['nullable'] == 'true' ? '' : " NOT NULL";
            $extra = isset($column['extra'])  ? " {$column['extra']}" : '';
            $COLUMNS []= "  \n`{$column['name']}` {$column['type']}{$nullable}{$default}{$extra}";
        }
        $KEYS="";
        foreach( $table->xpath("key") as $key ) {
            $key_cols = '`' . implode('`,`', explode(',', $key['columns']) ) .'`';
            if( $key['name'] == 'PRIMARY' ) {
                $KEYS .= ",  \nPRIMARY KEY ({$key_cols})";
            } else {
                $unique = isset($key['unique']) && $key['unique'] == 'true' ? 'UNIQUE ' : '';
                $KEYS .= ",  \n{$unique}KEY `{$key['name']}` ({$key_cols})";
            }
        }
        foreach( $table->xpath("constraint") as $constraint ) {
            $KEYS .= ",  \nCONSTRAINT `{$constraint['name']}` FOREIGN KEY ({$constraint['foreign']}) REFERENCES `{$constraint['reftable']}` ({$constraint['refcolumns']})";
            if( isset($constraint['onupdate']) && !empty($constraint['onupdate']) )  {
                $KEYS.=" ON UPDATE {$constraint['onupdate']}";
            }
            if( isset($constraint['ondelete']) && !empty($constraint['ondelete']) )  {
                $KEYS.=" ON DELETE {$constraint['onupdate']}";
            }
        }
        $SQL = "CREATE TABLE IF NOT EXISTS `{$table['name']}` (".implode(',',$COLUMNS)."{$KEYS}\n) ENGINE={$table['engine']} DEFAULT CHARSET={$table['charset']}\n";
        \App::getDB()->exec($SQL);
        return $SQL;
    }


    protected function collectSchemas()
    {
        $tables = array();
        $dir = \App::getDocumentRoot() . 'modules';

        if ( $modules = opendir( $dir ) ) {
            while (false !== ($module = readdir($modules))) { // Each module
                if( substr($module, 0, 1)  == '.' || !is_dir($module_dir =$dir . "/{$module}") ) continue;

                if ($submodules = opendir( $module_dir )) {
                    while (false !== ($submodule = readdir($submodules))) { // Each submodule
                        if( substr($submodule, 0, 1) == '.' || !is_dir($schema_dir = "{$module_dir}/{$submodule}/Resources/Schema") ) continue;

                        if ( $schemas = opendir( $schema_dir )) {
                            while (false !== ($schema_file = readdir($schemas))) { // Each schema file
                                if( substr($schema_file, 0, 1) == '.' ) continue;
                                $tables[]=simplexml_load_file("{$schema_dir}/{$schema_file}");
                            }
                            closedir($schemas);
                        }
                    }
                }
                closedir($submodules);
            }
            closedir($modules);
        }

        return $tables;
    }

}