Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
<?php
class MySQL_AXMLS extends Tangra_Class {
private $translation_table = array(
'BIT' => 'convert_bool',
'BOOL' => 'convert_bool',
'BOOLEAN' => 'convert_bool',
'TINYINT' => 'convert_bool',
'SMALLINT' => 'convert_smallint',
'MEDIUMINT' => 'convert_mediumint',
'INT' => 'convert_int',
'BIGINT' => 'convert_bigint',
'FLOAT' => 'convert_float',
'DOUBLE' => 'convert_float',
'DOUBLE PRECISION' => 'convert_float',
'REAL' => 'convert_float',
'DECIMAL' => 'convert_decimal',
'DEC' => 'convert_decimal',
'NUMERIC' => 'convert_decimal',
'FIXED' => 'convert_decimal',
'DATE' => 'convert_date',
'DATETIME' => 'convert_datetime',
'TIMESTAMP' => 'convert_datetime',
'CHAR' => 'convert_char',
'VARCHAR' => 'convert_char',
'TINYBLOB' => 'convert_blob',
'TINYTEXT' => 'convert_char',
'BLOB' => 'convert_blob',
'TEXT' => 'convert_text',
'MEDIUMBLOB' => 'convert_blob',
'MEDIUMTEXT' => 'convert_text',
'LONGBLOB' => 'convert_blob',
'LONGTEXT' => 'convert_text'
);
public function get_xml($create_table) {
$rows = explode("\n", $create_table);
$last_index = count($rows) - 1;
$mysql_opt = substr($rows[$last_index], 2);
unset($rows[$last_index]);
if (count($rows) >= 2) {
$table_name = $this->parse_table_name($rows[0]);
unset($rows[0]); // we don't need it anymore
$columns_raw = $this->extract_columns_rows($rows);
$columns = $this->parse_columns($columns_raw);
$this->detect_primary_key($rows, $columns);
$keys = $this->parse_keys($rows, $columns);
$xml = "<?xml version=\"1.0\"?>\r\n";
$xml .= "<schema version=\"0.3\">\r\n";
$xml .= " <table name=\"$table_name\">\r\n";
if ($mysql_opt) {
}
$xml .= " <opt platform=\"mysql\">$mysql_opt</opt>\r\n";
$xml .= $this->get_columns_xml($columns);
$xml .= $this->get_keys_xml($keys);
$xml .= " </table>\r\n";
$xml .= "</schema>\r\n";
} else {
$xml = 'Invalid CREATE TABLE SQL statement';
}
return $xml;
}
private function parse_table_name($row) {
$ret = $this->extract_one_quoted($row);
return $ret;
}
private function extract_columns_rows(&$rows) {
$ret = array();
foreach($rows as $key => $row) {
$tmp_row = trim($row);
if (substr($tmp_row, 0, 1) == '`') {
$ret[] = $tmp_row;
unset($rows[$key]);
}
}
return $ret;
}
private function parse_columns($columns_raw) {
$ret = array();
foreach($columns_raw as $cr) {
$parse_function = $this->detect_parse_function($cr);
$ret[] = call_user_func(array($this, $parse_function), $cr);
}
return $ret;
}
private function detect_parse_function($row) {
$ret = '';
$second_q = strpos($row, '`', 1); // skipping first character, looking for the second `
if ($second_q !== false) {
$type_start = $second_q + 2;
$type_end = strpos($row, ' ', $type_start);
if ($type_end === false) {
$type_end = strpos($row, ',', $type_start);
}
if ($type_end !== false) {
$type = strtoupper(substr($row, $type_start, $type_end - $type_start));
} else {
$type = strtoupper(substr($row, $type_start));
}
$open_parenthesis = strpos($type, '(');
if ($open_parenthesis !== false) {
$type = substr($type, 0, $open_parenthesis);
}
if (array_key_exists($type, $this->translation_table)) {
$ret = $this->translation_table[$type];
}
}
return $ret;
}
private function extract_one_quoted($str) {
$ret = '';
$start = strpos($str, '`');
if ($start !== false) {
$end = strpos($str, '`', $start + 1);
$ret = substr($str, $start + 1, $end - $start - 1);
}
return $ret;
}
private function extract_in_parenthesises($str) {
$ret = false;
$open_parenthesis = strpos($str, '(');
if ($open_parenthesis !== false) {
$close_parenthesis = strpos($str, ')');
$ret = substr($str, $open_parenthesis + 1, $close_parenthesis - $open_parenthesis - 1);
}
return $ret;
}
public function convert_int($row) {
$ret = array();
$ret['name'] = $this->extract_one_quoted($row);
$ret['type'] = 'I4';
$row = substr($row, strpos($row, '`', 1) + 1);
$not_null = strpos($row, 'NOT NULL');
if ($not_null !== false) {
$ret['not_null'] = true;
} else {
$ret['not_null'] = false;
}
$unsigned = strpos($row, 'unsigned');
if ($unsigned !== false) {
$ret['unsigned'] = true;
} else {
$ret['unsigned'] = false;
}
$ret['primary'] = false;
$ret['default'] = $this->extract_default($row);
if ($ret['default']) {
tangra_if_not_int_throw_e($ret['default']);
}
return $ret;
}
public function convert_char($row) {
$ret = array();
$ret['name'] = $this->extract_one_quoted($row);
$ret['type'] = 'C';
$row = substr($row, strpos($row, '`', 1) + 1);
$not_null = strpos($row, 'NOT NULL');
if ($not_null !== false) {
$ret['not_null'] = true;
} else {
$ret['not_null'] = false;
}
$ret['primary'] = false;
$size = $this->extract_in_parenthesises($row);
if ($size) {
$ret['size'] = $size;
} else {
$ret['size'] = false;
}
$ret['default'] = $this->extract_default($row);
return $ret;
}
public function convert_text($row) {
$ret = array();
$ret['name'] = $this->extract_one_quoted($row);
$ret['type'] = 'XL';
$row = substr($row, strpos($row, '`', 1) + 1);
$not_null = strpos($row, 'NOT NULL');
if ($not_null !== false) {
$ret['not_null'] = true;
} else {
$ret['not_null'] = false;
}
$ret['primary'] = false;
$ret['default'] = $this->extract_default($row);
return $ret;
}
public function convert_bool($row) {
$ret = array();
$ret['name'] = $this->extract_one_quoted($row);
$ret['type'] = 'I1';
$row = substr($row, strpos($row, '`', 1) + 1);
$not_null = strpos($row, 'NOT NULL');
if ($not_null !== false) {
$ret['not_null'] = true;
} else {
$ret['not_null'] = false;
}
$ret['primary'] = false;
$ret['unsigned'] = true;
$ret['primary'] = false;
$ret['default'] = $this->extract_default($row) ? 1 : 0;
return $ret;
}
public function convert_smallint($row) {
$ret = array();
$ret['name'] = $this->extract_one_quoted($row);
$ret['type'] = 'I2';
$row = substr($row, strpos($row, '`', 1) + 1);
$not_null = strpos($row, 'NOT NULL');
if ($not_null !== false) {
$ret['not_null'] = true;
} else {
$ret['not_null'] = false;
}
$unsigned = strpos($row, 'unsigned');
if ($unsigned !== false) {
$ret['unsigned'] = true;
} else {
$ret['unsigned'] = false;
}
$ret['primary'] = false;
$ret['default'] = $this->extract_default($row);
if ($ret['default']) {
tangra_if_not_int_throw_e($ret['default']);
}
return $ret;
}
public function convert_mediumint($row) {
$ret = array();
$ret['name'] = $this->extract_one_quoted($row);
$ret['type'] = 'I3';
$row = substr($row, strpos($row, '`', 1) + 1);
$not_null = strpos($row, 'NOT NULL');
if ($not_null !== false) {
$ret['not_null'] = true;
} else {
$ret['not_null'] = false;
}
$unsigned = strpos($row, 'unsigned');
if ($unsigned !== false) {
$ret['unsigned'] = true;
} else {
$ret['unsigned'] = false;
}
$ret['primary'] = false;
$ret['default'] = $this->extract_default($row);
if ($ret['default']) {
tangra_if_not_int_throw_e($ret['default']);
}
return $ret;
}
public function convert_bigint($row) {
$ret = array();
$ret['name'] = $this->extract_one_quoted($row);
$ret['type'] = 'I8';
$row = substr($row, strpos($row, '`', 1) + 1);
$not_null = strpos($row, 'NOT NULL');
if ($not_null !== false) {
$ret['not_null'] = true;
} else {
$ret['not_null'] = false;
}
$unsigned = strpos($row, 'unsigned');
if ($unsigned !== false) {
$ret['unsigned'] = true;
} else {
$ret['unsigned'] = false;
}
$ret['primary'] = false;
$ret['default'] = $this->extract_default($row);
if ($ret['default']) {
tangra_if_not_int_throw_e($ret['default']);
}
return $ret;
}
public function convert_float($row) {
$ret = array();
$ret['name'] = $this->extract_one_quoted($row);
$ret['type'] = 'F';
$row = substr($row, strpos($row, '`', 1) + 1);
$not_null = strpos($row, 'NOT NULL');
if ($not_null !== false) {
$ret['not_null'] = true;
} else {
$ret['not_null'] = false;
}
$unsigned = strpos($row, 'unsigned');
if ($unsigned !== false) {
$ret['unsigned'] = true;
} else {
$ret['unsigned'] = false;
}
$ret['primary'] = false;
$ret['default'] = $this->extract_default($row);
if ($ret['default'] && !is_numeric($ret['default'])) {
throw Tangra_Module_Exception('Default value is not numeric');
}
return $ret;
}
public function convert_decimal($row) {
$ret = array();
$ret['name'] = $this->extract_one_quoted($row);
$ret['type'] = 'N';
$row = substr($row, strpos($row, '`', 1) + 1);
$not_null = strpos($row, 'NOT NULL');
if ($not_null !== false) {
$ret['not_null'] = true;
} else {
$ret['not_null'] = false;
}
$unsigned = strpos($row, 'unsigned');
if ($unsigned !== false) {
$ret['unsigned'] = true;
} else {
$ret['unsigned'] = false;
}
$ret['primary'] = false;
$ret['default'] = $this->extract_default($row);
if ($ret['default'] && !is_numeric($ret['default'])) {
throw Tangra_Module_Exception('Default value is not numeric');
}
return $ret;
}
private function convert_date($row) {
$ret = array();
$ret['name'] = $this->extract_one_quoted($row);
$ret['type'] = 'D';
$row = substr($row, strpos($row, '`', 1) + 1);
$not_null = strpos($row, 'NOT NULL');
if ($not_null !== false) {
$ret['not_null'] = true;
} else {
$ret['not_null'] = false;
}
$ret['primary'] = false;
$ret['default'] = $this->extract_default($row);
return $ret;
}
private function convert_datetime($row) {
$ret = array();
$ret['name'] = $this->extract_one_quoted($row);
$ret['type'] = 'T';
$row = substr($row, strpos($row, '`', 1) + 1);
$not_null = strpos($row, 'NOT NULL');
if ($not_null !== false) {
$ret['not_null'] = true;
} else {
$ret['not_null'] = false;
}
$ret['primary'] = false;
$ret['default'] = $this->extract_default($row);
return $ret;
}
public function convert_blob($row) {
$ret = array();
$ret['name'] = $this->extract_one_quoted($row);
$ret['type'] = 'B';
$row = substr($row, strpos($row, '`', 1) + 1);
$not_null = strpos($row, 'NOT NULL');
if ($not_null !== false) {
$ret['not_null'] = true;
} else {
$ret['not_null'] = false;
}
$ret['primary'] = false;
$ret['default'] = false;
return $ret;
}
private function extract_default($str) {
$default_start = strpos($str, 'DEFAULT');
if ($default_start !== false) {
$def_str = substr($str, $default_start + 8);
$def_str = str_replace("\\'", '', $def_str);
$def_str = str_replace("\\\\", '', $def_str);
$def_str = str_replace('\'', '', $def_str);
$def_str = str_replace(',', '', $def_str);
$ret = $def_str;
} else {
$ret = false;
}
return $ret;
}
private function detect_primary_key($rows, &$columns) {
foreach($rows as $key => $row) {
$tmp_row = trim($row);
if (substr($tmp_row, 0, 1) != '`') {
$primary_start = strpos($tmp_row, 'PRIMARY KEY');
if ($primary_start !== false) {
$column_name = $this->extract_one_quoted($tmp_row);
if ($column_name) {
foreach($columns as $key => &$value) {
if ($value['name'] == $column_name) {
$value['primary'] = true;
}
}
}
break;
}
}
}
}
private function parse_keys($rows, $columns) {
$ret = array();
$c = 0;
foreach($rows as $key => $row) {
$tmp_row = trim($row);
if (substr($tmp_row, 0, 1) != '`') {
if (substr($tmp_row, 0, 3) == 'KEY' ) {
$ret[$c]['name'] = $this->extract_one_quoted($tmp_row);
$ret[$c]['unique'] = false;
$ret[$c]['columns'] = $this->get_key_columns($tmp_row);
$c++;
} elseif (substr($tmp_row, 0, 6) == 'UNIQUE') {
$ret[$c]['name'] = $this->extract_one_quoted($tmp_row);
$ret[$c]['unique'] = true;
$ret[$c]['columns'] = $this->get_key_columns($tmp_row);
$c++;
}
}
}
return $ret;
}
private function get_key_columns($str) {
$ret = array();
$columns_raw = $this->extract_in_parenthesises($str);
$columns_list_raw = explode(',', $columns_raw);
foreach($columns_list_raw as &$col) {
$col = str_replace('`', '', $col);
$ret[] = $col;
}
return $ret;
}
private function get_columns_xml($columns) {
$ret = '';
foreach($columns as $col) {
if ($col['type'] == 'C') {
if ($col['size']) {
$ret .= " <field name=\"{$col['name']}\" type=\"{$col['type']}\" size=\"{$col['size']}\">\r\n";
}
} else {
$ret .= " <field name=\"{$col['name']}\" type=\"{$col['type']}\">\r\n";
}
if ($col['not_null']) {
$ret .= " <NOTNULL />\r\n";
}
if ($col['primary']) {
$ret .= " <KEY />\r\n";
}
if (substr($col['type'], 0, 1) == 'I' || $col['type'] == 'F' || $col['type'] == 'N') {
if ($col['unsigned']) {
$ret .= " <UNSIGNED />\r\n";
}
}
if ($col['default'] !== false) {
$ret .= " <DEFAULT value=\"{$col['default']}\" />\r\n";
}
$ret .= " </field>\r\n";
$ret .= "\r\n";
}
return $ret;
}
private function get_keys_xml($keys) {
$ret = '';
foreach($keys as $key) {
$ret .= " <index name=\"{$key['name']}\">\r\n";
foreach($key['columns'] as $col) {
$ret .= " <col>$col</col>\r\n";
}
if ($key['unique']) {
$ret .= " <UNIQUE />\r\n";
}
$ret .= " </index>\r\n\r\n";
}
return $ret;
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment