Validation of mysql structure extraction mechanism
Posted: Wed Nov 05, 2008 12:28 pm
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
Thanks
PS: You have to substitute the table name on line 2 and connection info on line 1 for this to work
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);