Sqltoci.php

<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

/**
 * CI Migrations Generator Library
 *
 * Create a base file for migrations to start off with;
 *
 * @author Fastworkx S.R.L. <development@fastworkx.com>
 * @license Free to use and abuse
 * @version 0.1.0 Beta
 *
 */
class Sqltoci {

    var $db_user;
    var $db_pass;
    var $db_host;
    var $db_name;
    var $email;
    var $tables = '*';
    var $newline = '\n';
    var $write_file = true;
    var $file_name = '';
    var $file_per_table = true;
    var $path = 'migrations';
    var $skip_tables = array();
    var $add_view = false;
    var $stmts = [
        'up' => [],
        'down' => []
    ];

    /*
     * defaults;
     */

    function __construct($params = null) {
        // parent::__construct();
        isset($this->ci) OR $this->ci = get_instance();
        $this->ci->db_master = $this->ci->db;
        $this->db_user = $this->ci->db_master->username;
        $this->db_pass = $this->ci->db_master->password;
        $this->db_host = $this->ci->db_master->hostname;
        $this->db_name = $this->ci->db_master->database;
        $this->path = APPPATH . $this->path;
        if ($params)
            $this->init_config($params);
    }

    /**
     * Init Config if there is any passed
     *
     *
     * @param type $params
     */
    function init_config($params = array()) { //apply config
        if (count($params) > 0)
        {
            foreach ($params as $key => $val)
            {
                if (isset($this->$key))
                {
                    $this->$key = $val;
                }
            }
        }
    }

    /**
     * Generate the file.
     *
     * @param string $tables
     * @return boolean|string
     */
    function generate($tables = null) {
        if ($tables)
            $this->tables = $tables;

        $return = '';
        /* open file */
        if ($this->write_file)
        {
            if (!is_dir($this->path) OR !is_really_writable($this->path))
            {
                $msg = "Unable to write migration file: " . $this->path;
                log_message('error', $msg);
                echo $msg;
                return;
            }

            if (!$this->file_per_table)
            {
                $file_path = $this->path . '/' . $this->file_name . '.sql';
                $file = fopen($file_path, 'w+');

                if (!$file)
                {
                    $msg = "no file";
                    log_message('error', $msg);
                    echo $msg;
                    return FALSE;
                }
            }
        }


        // if default, then run all tables, otherwise just do the list provided
        if ($this->tables == '*')
        {

            $query = $this->ci->db_master->query('SHOW full TABLES FROM ' . $this->ci->db_master->protect_identifiers($this->ci->db_master->database));

            $retval = array();


            if ($query->num_rows() > 0)
            {

                foreach ($query->result_array() as $row)
                {

                    $tablename = 'Tables_in_' . $this->ci->db_master->database;

                    if (isset($row[$tablename]))
                    {
                        /* check if table in skip arrays, if so, go next */
                        if (in_array($row[$tablename], $this->skip_tables))
                            continue;

                        /* check if views to be migrated */
                        if ($this->add_view)
                        {
                            ## not implemented ##
                            //$retval[] = $row[$tablename];
                        } else
                        {
                            /* skip views */
                            if (strtolower($row['Table_type']) == 'view')
                            {
                                continue;
                            }
                            $retval[] = $row[$tablename];
                        }
                    }
                }
            }

            $this->tables = array();
            $this->tables = $retval;
        } else
        {
            $this->tables = is_array($tables) ? $tables : explode(',', $tables);
        }


        ## if write file, check if we can
        if ($this->write_file)
        {

            /* make subdir */
            $path = $this->path . '/' . $this->file_name;

            if (!@is_dir($path))
            {

                if (!@mkdir($path, DIR_WRITE_MODE, true))
                {
                    return FALSE;
                }

                @chmod($path, DIR_WRITE_MODE);
            }

            if (!is_dir($path) OR !is_really_writable($path))
            {
                $msg = "Unable to write backup per table file: " . $path;
                log_message('error', $msg);

                return;
            }

            $file_sufix = $this->getMigrationType() == "timestamp" ? date('YmdHis', now()) : "001";
            echo "Version = {$file_sufix} (change the version in \"config/migration.php\" file) <br>";
            $file_path = $path . "/{$file_sufix}_create_base.php";
            $file = fopen($file_path, 'w+');

            if (!$file)
            {
                $msg = 'No File';
                log_message('error', $msg);
                echo $msg;

                return FALSE;
            }
        }


        $up = '';
        $down = '';
        //loop through tables
        foreach ($this->tables as $table)
        {

            log_message('debug', print_r($table, true));

            $q = $this->ci->db_master->query('describe ' . $this->ci->db_master->protect_identifiers($this->ci->db_master->database . '.' . $table));
            // No result means the table name was invalid
            if ($q === FALSE)
            {
                continue;
            }

            $columns = $q->result_array();

            $q = $this->ci->db_master->query(' SHOW TABLE STATUS WHERE Name = \'' . $table . '\'');
            $engines = $q->row_array();

            $key = '';
            $up .= PHP_EOL."\n\t\t" . '## Create Table ' . $table . "\n";
            $up .= "\t\t" . '$this->dbforge->add_field(array(';

            foreach ($columns as $column)
            {
                $column_type = '';
                $column_constraint = '';
                $column_null = ( $column['Null'] == 'NO' ? 'FALSE' : 'TRUE' );
                $column_default = $column['Default'];
                $column_unsigned = FALSE;
                $unsigned = 'unsigned';
                
                //si tiene constraint
                if ( strpos($column['Type'], '(') )
                {
                    //verificar si tiene 'unsigned'
                    if( strpos($column['Type'], $unsigned) )
                    {
                        $column_unsigned = $unsigned;
                        $column['Type'] = substr( $column['Type'], 0, strpos( $column['Type'], ')' ) );
                    }

                    $column_type = strtoupper( substr($column['Type'], 0, strpos($column['Type'], '(') ) );

                    //si tiene un valores enum o set
                    if( $column_type == 'ENUM' || $column_type == 'SET' )
                    {
                        //reemplazamos comilla simple por doole
                        $column['Type'] = str_replace('\'', '"', substr( $column['Type'], strpos( $column['Type'], '(') ) );
                        
                        //concadenamos
                        $column_type = $column_type.$column['Type'];
                        $column_constraint = FALSE;
                    }
                    else
                    {
                        $column['Type'] = substr( $column['Type'], strpos( $column['Type'], '(') + 1 );
                        $column_constraint = substr($column['Type'], 0, -1 );
                    }
                }
                else
                {
                    $column_type = strtoupper( $column['Type'] );
                    $column_constraint = FALSE;
                }
                
                //si tiene DEAFAULT generar  sql texto plano para escapar el strin en caso e.g. CURRENT_TIMESTAMP
                if( $column_default == 'CURRENT_TIMESTAMP' )
                {
                    $up .= PHP_EOL."\t\t\t"."'`$column[Field]` $column[Type] " . ($column['Null'] == 'NO' ? 'NOT NULL' : 'NULL') .
                    (
                    #  if its timestamp column, don't '' around default value .... crap way, but should work for now
                    $column['Default'] ? ' DEFAULT ' . ($column['Type'] == 'timestamp' ? $column['Default'] : '' . $column['Default'] . '') : ''
                    )
                    . " $column[Extra]',";
                }
                else
                {
                    $up .= PHP_EOL.
                    "\t\t\t".'\'' . $column['Field'] . '\' => array('.PHP_EOL.
                    "\t\t\t\t".'\'type\' => \'' . $column_type . '\','.PHP_EOL.
                    ( 
                        $column_constraint ? 
                        "\t\t\t\t".'\'constraint\' => "' . $column_constraint . '",'.PHP_EOL :
                        ''
                    ).
                    ( 
                        $column_unsigned ? 
                        "\t\t\t\t".'\''.$unsigned.'\' => TRUE,'.PHP_EOL :
                        ''
                    ).
                    "\t\t\t\t".'\'null\' => ' . $column_null . ','.PHP_EOL.
                    ( 
                        $column_default != NULL ? 
                        "\t\t\t\t".'\'default\' => ' . (is_numeric($column_default) ? $column_default : "\"{$column_default}\"") . ','.PHP_EOL :
                        ''
                    ).
                    ( 
                    $column['Extra'] ? 
                    "\t\t\t\t".'\''.$column['Extra'].'\' => TRUE'.PHP_EOL :
                    ''.PHP_EOL
                    ).
                    "\t\t\t".'),';
                }

                if ($column['Key'] == 'PRI')
                    $key = "\t\t" . '$this->dbforge->add_key("' . $column['Field'] . '",true);';
            }

            $up .= PHP_EOL."\t\t));". PHP_EOL.$key.PHP_EOL."\t\t" . '$this->dbforge->create_table("' . $table . '", TRUE);' . PHP_EOL;
            $up = str_replace("\"b'0'\"",0,$up);
            $up = str_replace("\"b'1'\"",1,$up);
            if (isset($engines['Engine']) and $engines['Engine'])
                $up .= "\t\t" . '$this->db->query(\'ALTER TABLE  ' . $this->ci->db_master->protect_identifiers($table) .
                        ' ENGINE = ' . $engines['Engine']. '\');';


            $down .= "\t\t" . '### Drop table ' . $table . ' ##' . "\n";
            $down .= "\t\t" . '$this->dbforge->drop_table("' . $table . '", TRUE);' . "\n";

            /* clear some mem */
            $q->free_result();
        }
        ### generating routines queries (procedures,functions, triggers) ###
        $this->generateKeys();
        $this->generateQueriesArray();
        $queries = $this->stmts;
        if ( count($queries['up']) != 0 )
        {
            $up     .=  PHP_EOL."\n\t\t" . '## Create Routines' . "\n";
            $up     .=  "\t\t". '$queries = '."[";
            foreach ($queries['up'] as $query) {
                $up.= "\n\t\t\t'{$query}',";
            }
            $up     .= "\n\t\t]; \n\t\t".'foreach($queries as $query) $this->db->query($query);'."\n";
            ### Dropping routines

            $down     .=  PHP_EOL."\n\t\t" . '## Dropping Routines' . "\n";
            $down     .=  "\t\t". '$queries = '."[";
            foreach ($queries['down'] as $query) {
                $down.= "\n\t\t\t'{$query}',";
            }
            $down     .= "\n\t\t]; \n\t\t".'foreach($queries as $query) $this->db->query($query);'."\n";
        }
        ### generate the text ##
        $return .= '<?php ';
        $return .= 'defined(\'BASEPATH\') OR exit(\'No direct script access allowed\');' . "\n\n";
        $return .= 'class Migration_create_base extends CI_Migration {' . "\n";
        $return .= "\n\t" . 'public function up() {';

        $return .= $up;
        $return .= "\n\t" . ' }' . "\n";

        $return .= "\n\t" . 'public function down()';
        $return .= "\t" . '{' . "\n";
        $return .= $down . "\n";
        $return .= "\t" . '}' . "\n" . '}';

        ## write the file, or simply return if write_file false
        if ($this->write_file)
        {
            fwrite($file, $return);
            fclose($file);
            echo "Create file migration with success!";
            return true;
        } else
        {
            return $return;
        }
    }

     /**
     * generateQueriesArray
     *  generate the queries needed with the procedures, functions and triggers
     * @return null
     */
    private function generateQueriesArray()
    {
        //GET FUNCTIONS, PROCEDURES AND TRIGGERS
        $queryProcedures = $this->ci->db_master->query('show procedure status where db = (SELECT DATABASE())');
        $queryFunctions = $this->ci->db_master->query('show function status where db = (SELECT DATABASE())');
        $queryTriggers = $this->ci->db_master->query('show triggers');

        //RETURN EMPTY ARRAY IF NOTHING FOUND
        if ($queryProcedures->num_rows() == 0 && $queryFunctions->num_rows() == 0 && $queryTriggers->num_rows() == 0 ) return [];

        //MERGE ARRAYS ( THEY HAVE THE SAME STRUCTURE )
        $rutines = array_merge($queryProcedures->result_object(),$queryFunctions->result_object());

        foreach ($rutines as $r) {
            //GET ALL INFO ABOUT RUTINE
            $query = $this->ci->db_master->query("SHOW CREATE {$r->Type} {$r->Name}");
            if( $query->num_rows() == 0) continue;
            $query = $query->row_object();
            $type = ucfirst(strtolower($r->Type));
            //REPLACING HOST AND USER TO GET THE NAME OF THE PROCEDURE
            $upStmt = str_replace("'",'"',preg_replace("#DEFINER=`(.+)`@`(.+)\.(.+)\.(.+)\.(.+)` #",'',$query->{"Create {$type}"}));
            $this->stmts['up'][] = $upStmt;
            //DOWN STATEMENT
            $downStmt = "DROP {$r->Type} IF EXISTS {$r->Name}";
            $this->stmts['down'][] = $downStmt;
        }

        foreach ($queryTriggers->result_object() as $trigger) {
            
            $upStmt = "CREATE TRIGGER {$trigger->Trigger} {$trigger->Timing} {$trigger->Event} ON `{$trigger->Table}` FOR EACH ROW {$trigger->Statement}";
            $upStmt = str_replace("'",'"',$upStmt);
            $this->stmts['up'][] = $upStmt;
            $downStmt = "DROP TRIGGER IF EXISTS {$trigger->Trigger}";
            $this->stmts['down'][] = $downStmt;
        }
        return null;
    }

     /**
     * generateKeys
     *  generate the keys of the tables
     * @return null 
     */
    private function generateKeys()
    {
        $queryUnique = $this->ci->db_master->query('select concat("ALTER TABLE ", TABLE_NAME," ADD CONSTRAINT ",INDEX_NAME ," UNIQUE (",group_concat(COLUMN_NAME ORDER BY SEQ_IN_INDEX),")") as query from information_schema.STATISTICS where INDEX_NAME!="PRIMARY" and NON_UNIQUE=0 and TABLE_SCHEMA=(SELECT DATABASE())  group by TABLE_NAME,INDEX_NAME order by TABLE_NAME,INDEX_NAME;')->result_object();

        $queryForeign = $this->ci->db_master->query('select TABLE_NAME as id, CONCAT("ALTER TABLE ",TABLE_NAME," ADD CONSTRAINT ", CONSTRAINT_NAME," FOREIGN KEY (",COLUMN_NAME ,") REFERENCES ",REFERENCED_TABLE_NAME,"(",REFERENCED_COLUMN_NAME,")") as query
        from information_schema.key_column_usage
        WHERE CONSTRAINT_SCHEMA= (SELECT DATABASE()) AND CONSTRAINT_NAME IN (select CONSTRAINT_NAME
        from information_schema.TABLE_CONSTRAINTS
        where table_name in (SELECT TABLE_NAME 
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_TYPE = "BASE TABLE") AND CONSTRAINT_SCHEMA= (SELECT DATABASE()) AND CONSTRAINT_TYPE ="FOREIGN KEY" );
        ')->result_object();

        $tables = array_map(fn($t) => "'{$t->id}'", $queryForeign);
        $tables_string = implode(', ', $tables);

        $queryIndexes = $this->ci->db_master->query("SELECT CONCAT('CREATE INDEX ',INDEX_NAME,' ON ',TABLE_NAME,' (',COLUMN_NAME,')') as query
        FROM INFORMATION_SCHEMA.STATISTICS
        WHERE TABLE_SCHEMA = (SELECT DATABASE()) AND INDEX_NAME != 'PRIMARY' AND INDEX_NAME NOT IN (select CONSTRAINT_NAME
        from information_schema.TABLE_CONSTRAINTS
        where table_name in ({$tables_string}) AND CONSTRAINT_SCHEMA= (SELECT DATABASE()) AND CONSTRAINT_TYPE ='FOREIGN KEY') AND INDEX_NAME NOT IN (SELECT DISTINCT INDEX_NAME from information_schema.STATISTICS where INDEX_NAME!='PRIMARY' and NON_UNIQUE=0 and TABLE_SCHEMA=(SELECT DATABASE()))")->result_object();

        $stmts = [];
        foreach ( ['Unique', 'Foreign', 'Indexes'] as $value) {
            $varName = "query{$value}";
            $temp_array = array_map(fn($t) => $t->query, $$varName);
            $stmts = array_merge($stmts, $temp_array);
        }
        $this->stmts['up'] = $stmts;
        return;
    }

    /**
     * getMigrationType
     *  Returns the migration type for naming purposes;
     * @return string timestamp|sequential
     */
    private function getMigrationType()
    {
        $this->ci->config->load('migration');
        return $this->ci->config->item('migration_type') ?? 'timestamp';
    }
}

?>