Validation of mysql structure extraction mechanism

Coding Critique is the place to post source code for peer review by other members of DevNetwork. Any kind of code can be posted. Code posted does not have to be limited to PHP. All members are invited to contribute constructive criticism with the goal of improving the code. Posted code should include some background information about it and what areas you specifically would like help with.

Popular code excerpts may be moved to "Code Snippets" by the moderators.

Moderator: General Moderators

Post Reply
crazycoders
Forum Contributor
Posts: 260
Joined: Tue Oct 28, 2008 7:48 am
Location: Montreal, Qc, Canada

Validation of mysql structure extraction mechanism

Post 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);
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Re: Validation of mysql structure extraction mechanism

Post 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
crazycoders
Forum Contributor
Posts: 260
Joined: Tue Oct 28, 2008 7:48 am
Location: Montreal, Qc, Canada

Re: Validation of mysql structure extraction mechanism

Post 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.
crazycoders
Forum Contributor
Posts: 260
Joined: Tue Oct 28, 2008 7:48 am
Location: Montreal, Qc, Canada

Re: Validation of mysql structure extraction mechanism

Post 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.
Post Reply