Page 1 of 1

Validation of mysql structure extraction mechanism

Posted: Wed Nov 05, 2008 12:28 pm
by crazycoders
I here is a bit of code used to read "create table" statements gotten from the mysql server. My attempt is to extract the whole structure of each table to make a custom OR-M. I'd need help to validate i'm not forgetting anything and that my logic is ok.

Thanks

PS: You have to substitute the table name on line 2 and connection info on line 1 for this to work :P

Code: Select all

 
include('dbconn.php');
$createstatement = mysql_fetch_assoc(mysql_query('SHOW CREATE TABLE `matches`'));
$createstatement = $createstatement['Create Table'];
echo $createstatement."\n\n";
preg_match_all('#create\s*table\s*`(\w[\w\d]*)`#i', $createstatement, $tablename);
preg_match_all('#`(\w[\w\d]*)`\s+((tinyint|smallint|mediumint|int|bigint|tinytext|text|mediumtext|longtext|tinyblob|blob|mediumblob|longblob|varchar|char|date|datetime|float|double|decimal|timestamp|time|year|enum|set|binary|varbinary)(\((\d+)(,\s*(\d+))?\))?){1}\s*(collate (\w+)\s*)?(unsigned\s*)?((NOT\s*NULL\s*)|(NULL\s*))?(auto_increment\s*)?(default \'([^\']*)\'\s*)?#i', $createstatement, $fields);
preg_match('#primary\s*key\s*\([^)]+\)#i', $createstatement, $primarykeydefinition);
preg_match_all('#`(\w[\w\d]*)`#', $primarykeydefinition[0], $primarykeydefinition);
preg_match_all('#key\s*`\w[\w\d]*`\s*\(.*\)#i', $createstatement, $keydefinition);
$keys = array();
foreach($keydefinition[0] as $key){
    preg_match_all('#`(\w[\w\d]*)`#', $key, $keydef);
    $keys[] = $keydef;
}
preg_match_all('#(constraint\s*(`(\w[\w\d]*)`)?)?\s*foreign\s*key\s*(`(\w[\w\d]*)`)?\s*\(`(\w[\w\d]*)`\)\s*references\s*`(\w[\w\d]*)`\s*\(`(\w[\w\d]*)`\)\s*(on\s*delete\s*(restrict|cascade|set null|no action))?\s*(on\s*update\s*(restrict|cascade|set null|no action))?#i', $createstatement, $foreignkeydefinition);
preg_match_all('#(\w+)=(\w+)#', $createstatement, $tableoptions);
 
//print_r($tablename);
//print_r($fields);
//print_r($primarykeydefinition);
//print_r($keys);
//print_r($foreignkeydefinition);
//print_r($tableoptions);
 
//Analyse into an array of data
$tabledata = array(
    'name' => $tablename[1][0],
    'fields' => array(),
    'primarykey' => array(),
    'indexes' => array(),
    'foreignkeys' => array(),
    'options' => array(),
);
foreach($fields[0] as $fieldkey => $fielddata){
    $tabledata['fields'][$fields[1][$fieldkey]] = array(
        'name' => trim($fields[1][$fieldkey]),
        'type' => trim($fields[3][$fieldkey]),
        'size' => trim($fields[5][$fieldkey]),
        'sizeext' => trim($fields[8][$fieldkey]),
        'collation' => trim($fields[9][$fieldkey]),
        'unsigned' => trim($fields[10][$fieldkey]),
        'notnull' => trim($fields[11][$fieldkey]),
        'autoincrement' => trim($fields[14][$fieldkey]),
        'defaultvalue' => trim($fields[16][$fieldkey]),
    );
}
foreach($primarykeydefinition[1] as $fieldkey => $field){
    $tabledata['primarykey'][] = $field;
}
foreach($keys as $keycollection){
    $indexkey = '';
    foreach($keycollection[1] as $key => $name){
        if($indexkey == ''){
            $indexkey = $name;
        }else{
            $tabledata['indexes'][$indexkey][] = $name;
        }
    }
}
foreach($foreignkeydefinition[0] as $keyindex => $keydata){
    $tabledata['foreignkeys'][$foreignkeydefinition[3][$keyindex]] = array(
        'name' => trim($foreignkeydefinition[3][$keyindex]),
        'column' => trim($foreignkeydefinition[6][$keyindex]),
        'foreigntable' => trim($foreignkeydefinition[7][$keyindex]),
        'foreigncolumn' => trim($foreignkeydefinition[8][$keyindex]),
        'ondelete' => trim($foreignkeydefinition[10][$keyindex]),
        'onupdate' => trim($foreignkeydefinition[12][$keyindex]),
    );
}
foreach($tableoptions[0] as $key => $optiondata){
    $tabledata['options'][$tableoptions[1][$key]] = $tableoptions[2][$key];
}
 
print_r($tabledata);

Re: Validation of mysql structure extraction mechanism

Posted: Wed Nov 05, 2008 11:52 pm
by infolock
If you want a critique, i'd say it looks pretty nice to me. I like your regex to verify valid queries based on what they are trying to issue. Only issue I can see if you aren't checking to verify that the table exists or that data exists, so you might get an error on failure. otherwise, nicely done

Re: Validation of mysql structure extraction mechanism

Posted: Thu Nov 06, 2008 6:58 am
by crazycoders
For that don't worry, the code is to parse existing CREATE TABLE statements gotten from SHOW CREATE TABLE XYZ and XYZ is taken from SHOW TABLES.

Re: Validation of mysql structure extraction mechanism

Posted: Thu Nov 06, 2008 7:32 am
by crazycoders
Allright completed this morning the script, now outputs XML structure of all tables in the connected database plus auto extracts the database name.

This XML can then be read using simplexml and you have access to an object structure of the whole database structure.

Code: Select all

 
<?php header('content-type: text/xml'); ?>
<?php echo '<?xml version="1.0" encoding="windows-1250"?>'; ?>
<?php 
//Please substitute these for your own includes. This portion of code should open a database connection and select a database
include('includes/base.php'); 
include('includes/db.php');
 
//Extract the tables and database name at the same time
$tables = mysql_query('SHOW TABLES');
$currentrow = mysql_fetch_assoc($tables);
preg_match('#Tables_in_(\w+)#i', array_pop(array_keys($currentrow)), $db);
$db = $db[1];
 
//Begin outputting the XML here
?>
<database name="<?php echo $db; ?>">
    <tables>
        <?php   
        //A do is used because the first line is fetched beforehand to get the database name from the keys of the array
        do{ 
            //Extract the create statement from MySQL
            $createstatement = mysql_fetch_assoc(mysql_query('SHOW CREATE TABLE '.array_pop($currentrow)));
            $createstatement = array_pop($createstatement);
            
            //Parse the statement using PREG
            preg_match_all('#create\s*table\s*`(\w[\w\d]*)`#i', $createstatement, $tablename);
            preg_match_all('#`(\w[\w\d]*)`\s+((tinyint|smallint|mediumint|int|bigint|tinytext|text|mediumtext|longtext|tinyblob|blob|mediumblob|longblob|varchar|char|date|datetime|float|double|decimal|timestamp|time|year|enum|set|binary|varbinary)(\((\d+)(,\s*(\d+))?\))?){1}\s*(collate (\w+)\s*)?(unsigned\s*)?((NOT\s*NULL\s*)|(NULL\s*))?(auto_increment\s*)?(default \'([^\']*)\'\s*)?#i', $createstatement, $fields);
            preg_match('#primary\s*key\s*\([^)]+\)#i', $createstatement, $primarykeydefinition);
            //Note: There is a possibility that no primary key is found, that is why we skip the second portion of analysis here
            if(isset($primarykeydefinition[0])){ preg_match_all('#`(\w[\w\d]*)`#', $primarykeydefinition[0], $primarykeydefinition); }
            else{ $primarykeydefition = array(); }
            //Resume scanning for parts of the statement
            preg_match_all('#key\s*`\w[\w\d]*`\s*\(.*\)#i', $createstatement, $keydefinition);
            $keys = array(); foreach($keydefinition[0] as $key){ preg_match_all('#`(\w[\w\d]*)`#', $key, $keydef); $keys[] = $keydef; }
            preg_match_all('#(constraint\s*(`(\w[\w\d]*)`)?)?\s*foreign\s*key\s*(`(\w[\w\d]*)`)?\s*\(`(\w[\w\d]*)`\)\s*references\s*`(\w[\w\d]*)`\s*\(`(\w[\w\d]*)`\)\s*(on\s*delete\s*(restrict|cascade|set null|no action))?\s*(on\s*update\s*(restrict|cascade|set null|no action))?#i', $createstatement, $foreignkeydefinition);
            preg_match_all('#(\w+)=(\w+)#', $createstatement, $tableoptions);
            
            //Analyse into an array of data that will be used to output the XML
            $tabledata = array(
                'name' => $tablename[1][0],
                'fields' => array(),
                'primarykey' => array(),
                'indexes' => array(),
                'foreignkeys' => array(),
                'options' => array(),
            );
            foreach($fields[0] as $fieldkey => $fielddata){
                $tabledata['fields'][$fields[1][$fieldkey]] = array(
                    'name' => strtolower(trim($fields[1][$fieldkey])),
                    'type' => strtolower(trim($fields[3][$fieldkey])),
                    'size' => strtolower(trim($fields[5][$fieldkey])),
                    'sizeext' => strtolower(trim($fields[8][$fieldkey])),
                    'collation' => strtolower(trim($fields[9][$fieldkey])),
                    'unsigned' => strtolower(trim($fields[10][$fieldkey])),
                    'notnull' => strtolower(trim($fields[11][$fieldkey])),
                    'autoincrement' => strtolower(trim($fields[14][$fieldkey])),
                    'defaultvalue' => strtolower(trim($fields[16][$fieldkey])),
                );
            }
            if(count($primarykeydefinition) > 0){
                foreach($primarykeydefinition[1] as $fieldkey => $field){
                    $tabledata['primarykey'][] = $field;
                }
            }
            foreach($keys as $keycollection){
                $indexkey = '';
                foreach($keycollection[1] as $key => $name){
                    if($indexkey == ''){
                        $indexkey = $name;
                    }else{
                        $tabledata['indexes'][$indexkey][] = $name;
                    }
                }
            }
            foreach($foreignkeydefinition[0] as $keyindex => $keydata){
                $tabledata['foreignkeys'][strtolower(trim($foreignkeydefinition[3][$keyindex]))] = array(
                    'name' => strtolower(trim($foreignkeydefinition[3][$keyindex])),
                    'column' => strtolower(trim($foreignkeydefinition[6][$keyindex])),
                    'foreigntable' => strtolower(trim($foreignkeydefinition[7][$keyindex])),
                    'foreigncolumn' => strtolower(trim($foreignkeydefinition[8][$keyindex])),
                    'ondelete' => strtolower(trim($foreignkeydefinition[10][$keyindex])),
                    'onupdate' => strtolower(trim($foreignkeydefinition[12][$keyindex])),
                );
            }
            foreach($tableoptions[0] as $key => $optiondata){
                $tabledata['options'][strtolower(trim($tableoptions[1][$key]))] = strtolower(trim($tableoptions[2][$key]));
            }
            
            //Output to XML structure
            echo '<table name="'.$tabledata['name'].'" ';
            foreach($tabledata['options'] as $key => $value){
                echo $key.'="'.$value.'" ';
            }
            echo '>';
            echo '<columns>';
            foreach($tabledata['fields'] as $key => $field){
                echo '<column name="'.$field['name'].'" type="'.$field['type'].'" size="'.$field['size'].'" collation="'.$field['collation'].'" attributes="'.$field['unsigned'].'">';
                echo '<constraints>';
                if($field['notnull'] == 'not null'){ echo '<constraint type="notnull" />'; }
                if($field['autoincrement'] == 'auto_increment'){ echo '<constraint type="auto_increment" />'; }
                echo '</constraints>';
                echo '</column>';
            }
            echo '</columns>';
            echo '<indexes>';
            foreach($tabledata['indexes'] as $key => $columncollection){
                echo '<index name="'.$key.'">';
                echo '<columns>';
                foreach($columncollection as $column){
                echo '<column name="'.$column.'" />';
                }
                echo '</columns>';
                echo '</index>';
            }
            echo '</indexes>';
            if(count($tabledata['primarykey']) > 0){
                echo '<primarykey>';
                    echo '<columns>';
                    foreach($tabledata['primarykey'] as $key => $column){
                    echo '<column name="'.$column.'" />';
                    }
                    echo '</columns>';
                echo '</primarykey>';
            }
            echo '<foreignkeys>';
                foreach($tabledata['foreignkeys'] as $key => $foreignkey){
                echo '<foreignkey name="'.$key.'" column="'.$foreignkey['column'].'" foreigntable="'.$foreignkey['foreigntable'].'" foreigncolumn="'.$foreignkey['foreigncolumn'].'" ondelete="'.$foreignkey['ondelete'].'" onupdate="'.$foreignkey['onupdate'].'" />';
                }
            echo '</foreignkeys>';
            echo '</table>';
            
            //Get the next row
            $currentrow = mysql_fetch_assoc($tables);
            
        }while($currentrow !== false);
        ?>
    </tables>
</database>
 
This project is a part of my PSQLx2.0 data access framework, visit PHP Theory and Design Forum for more information on PSQLx2.0.