/**
* Return object (table/schema) configuration
*
* @throws Exception\ErrorException
*
* @param string $table
* @param boolean $include_options
* @return array
*/
protected function getObjectConfig($table = null, $include_options = false)
{
$schema = $this->schema;
$qSchema = $this->adapter->getPlatform()->quoteValue($schema);
if ($table !== null) {
$qTable = $this->adapter->getPlatform()->quoteValue($table);
$table_clause = "and (t.TABLE_NAME = {$qTable} or (kcu.referenced_table_name = {$qTable} and kcu.constraint_name = 'FOREIGN KEY'))";
$table_join_condition = "(t.table_name = kcu.table_name or kcu.referenced_table_name = t.table_name)";
} else {
$table_join_condition = "t.table_name = kcu.table_name";
$table_clause = '';
}
$query = "\n\n SELECT\n t.table_name,\n c.column_name,\n c.data_type,\n c.column_type,\n\n c.extra,\n\n tc.constraint_type,\n kcu.constraint_name,\n kcu.referenced_table_name,\n kcu.referenced_column_name,\n\n c.column_default,\n c.is_nullable,\n c.numeric_precision,\n c.numeric_scale,\n c.character_octet_length,\n c.character_maximum_length,\n c.ordinal_position,\n\n c.column_key, -- UNI/MUL/PRI\n c.character_set_name,\n\n\n c.collation_name,\n\n c.column_comment,\n\n t.table_type,\n t.engine,\n t.table_comment,\n t.table_collation\n\n FROM `INFORMATION_SCHEMA`.`COLUMNS` c\n INNER JOIN `INFORMATION_SCHEMA`.`TABLES` t on c.TABLE_NAME = t.TABLE_NAME\n LEFT JOIN `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` kcu\n on (\n {$table_join_condition}\n and kcu.table_schema = t.table_schema\n and kcu.column_name = c.column_name\n )\n LEFT JOIN\n `INFORMATION_SCHEMA`.`TABLE_CONSTRAINTS` tc\n on (\n t.table_name = tc.table_name\n and tc.table_schema = t.table_schema\n and tc.constraint_name = kcu.constraint_name\n )\n\n\n where c.TABLE_SCHEMA = {$qSchema}\n and t.TABLE_SCHEMA = {$qSchema}\n {$table_clause}\n and (kcu.table_schema = {$qSchema} or kcu.table_schema is null)\n\n and (kcu.column_name = c.column_name or kcu.column_name is null)\n order by t.table_name, c.ordinal_position\n ";
$this->disableInnoDbStats();
try {
$results = $this->adapter->query($query, Adapter::QUERY_MODE_EXECUTE);
} catch (\Exception $e) {
//@codeCoverageIgnoreStart
$this->restoreInnoDbStats();
throw new Exception\ErrorException(__METHOD__ . ": " . $e->getMessage());
//@codeCoverageIgnoreEnd
}
$this->restoreInnoDbStats();
$references = array();
$config = new Config(array('tables' => array()), true);
$tables = $config->offsetGet('tables');
foreach ($results as $r) {
// Setting table information
$table_name = $r['table_name'];
if (!$tables->offsetExists($table_name)) {
$table_def = array('name' => $table_name, 'columns' => array(), 'primary_keys' => array(), 'unique_keys' => array(), 'foreign_keys' => array(), 'references' => array(), 'indexes' => array());
if ($include_options) {
$table_def['options'] = array('comment' => $r['table_comment'], 'collation' => $r['table_collation'], 'type' => $r['table_type'], 'engine' => $r['engine']);
}
$tables->offsetSet($table_name, $table_def);
}
$table = $tables->offsetGet($table_name);
$columns = $table->columns;
$column_name = $r['column_name'];
$data_type = strtolower($r['data_type']);
$col_def = array('type' => $data_type, 'primary' => $r['constraint_type'] == 'PRIMARY KEY', 'nullable' => $r['is_nullable'] == 'YES', 'default' => $r['column_default']);
if ($r['constraint_type'] == 'PRIMARY KEY') {
$col_def['primary'] = true;
$col_def['autoincrement'] = $r['extra'] == 'auto_increment';
}
$has_charset = false;
if (in_array($data_type, array('int', 'tinyint', 'mediumint', 'bigint', 'int', 'smallint', 'year'))) {
$col_def['unsigned'] = (bool) preg_match('/unsigned/', strtolower($r['column_type']));
$col_def['precision'] = $r['numeric_precision'];
} elseif (in_array($data_type, array('real', 'double precision', 'decimal', 'numeric', 'float', 'dec', 'fixed'))) {
$col_def['precision'] = $r['numeric_precision'];
$col_def['scale'] = $r['numeric_scale'];
} elseif (in_array($data_type, array('timestamp', 'date', 'time', 'datetime'))) {
// nothing yet
} elseif (in_array($data_type, array('char', 'varchar', 'binary', 'varbinary', 'text', 'tinytext', 'mediumtext', 'longtext'))) {
$col_def['octet_length'] = $r['character_octet_length'];
$col_def['length'] = $r['character_maximum_length'];
$has_charset = true;
} elseif (in_array($data_type, array('blob', 'tinyblob', 'mediumblob', 'longblob'))) {
$col_def['octet_length'] = $r['character_octet_length'];
$col_def['length'] = $r['character_maximum_length'];
} elseif (in_array($data_type, array('enum', 'set'))) {
$col_def['octet_length'] = $r['character_octet_length'];
$col_def['length'] = $r['character_maximum_length'];
$def = $r['column_type'];
preg_match_all("/'([^']+)'/", $def, $matches);
if (is_array($matches[1]) && count($matches) > 0) {
$col_def['values'] = $matches[1];
}
}
if ($include_options) {
$col_def['options'] = array('comment' => $r['column_comment'], 'definition' => $r['column_type'], 'column_key' => $r['column_key'], 'ordinal_position' => $r['ordinal_position'], 'constraint_type' => $r['constraint_type']);
if ($has_charset) {
$col_def['options']['charset'] = $r['character_set_name'];
$col_def['options']['collation'] = $r['collation_name'];
}
}
$columns[$column_name] = $col_def;
$foreign_keys = $table->foreign_keys;
$unique_keys = $table->unique_keys;
$constraint_name = $r['constraint_name'];
$referenced_table_name = $r['referenced_table_name'];
$referenced_column_name = $r['referenced_column_name'];
switch ($r['constraint_type']) {
case 'PRIMARY KEY':
$table->primary_keys = array_merge($table->primary_keys->toArray(), (array) $column_name);
break;
case 'UNIQUE':
if (!$unique_keys->offsetExists($constraint_name)) {
$unique_keys[$constraint_name] = array();
}
$unique_keys[$constraint_name] = array_merge($unique_keys[$constraint_name]->toArray(), (array) $column_name);
break;
case 'FOREIGN KEY':
/*
if (!$foreign_keys->offsetExists($constraint_name)) {
$foreign_keys[$constraint_name] = array();
}
*
*/
$fk = array('referenced_table' => $referenced_table_name, 'referenced_column' => $referenced_column_name, 'constraint_name' => $constraint_name);
$foreign_keys[$column_name] = $fk;
//$table->references[$referenced_table_name] = array($column_name => $r['referenced_column_name']);
if (!array_key_exists($referenced_table_name, $references)) {
$references[$referenced_table_name] = array();
}
$references[$referenced_table_name][] = array('column' => $column_name, 'referenced_column' => $referenced_column_name, 'constraint_name' => $constraint_name);
break;
}
}
foreach ($references as $referenced_table_name => $refs) {
if ($tables->offsetExists($referenced_table_name)) {
$table = $tables[$referenced_table_name];
$references = $table->references;
$references[$referenced_table_name] = $refs;
}
}
$array = $config->toArray();
unset($config);
return $array;
}
/**
* {@inheritdoc}
* @throws Exception\ErrorException
*/
public function getSchemaConfig($table = null, $include_options = true)
{
$results = $this->executeQuery($table);
$references = [];
$config = new Config(['tables' => []], true);
$tables = $config->offsetGet('tables');
foreach ($results as $r) {
// Setting table information
$table_name = $r['table_name'];
if (!$tables->offsetExists($table_name)) {
$table_def = ['name' => $table_name, 'columns' => [], 'primary_keys' => [], 'unique_keys' => [], 'foreign_keys' => [], 'references' => [], 'indexes' => []];
if ($include_options) {
$table_def['options'] = ['comment' => $r['table_comment'], 'collation' => $r['table_collation'], 'type' => $r['table_type'], 'engine' => $r['engine']];
}
$tables->offsetSet($table_name, $table_def);
}
$table = $tables->offsetGet($table_name);
$columns = $table->columns;
$column_name = $r['column_name'];
$data_type = strtolower($r['data_type']);
$col_def = ['type' => $data_type, 'primary' => $r['constraint_type'] == 'PRIMARY KEY', 'nullable' => $r['is_nullable'] == 'YES', 'default' => $r['column_default']];
if ($r['constraint_type'] == 'PRIMARY KEY') {
$col_def['primary'] = true;
$col_def['autoincrement'] = $r['extra'] == 'auto_increment';
}
$has_charset = false;
if (in_array($data_type, ['int', 'tinyint', 'mediumint', 'bigint', 'int', 'smallint', 'year'])) {
$col_def['unsigned'] = (bool) preg_match('/unsigned/', strtolower($r['column_type']));
$col_def['precision'] = is_numeric($r['numeric_precision']) ? (int) $r['numeric_precision'] : null;
} elseif (in_array($data_type, ['real', 'double precision', 'decimal', 'numeric', 'float', 'dec', 'fixed'])) {
$col_def['precision'] = is_numeric($r['numeric_precision']) ? (int) $r['numeric_precision'] : null;
$col_def['scale'] = is_numeric($r['numeric_scale']) ? (int) $r['numeric_scale'] : null;
} elseif (in_array($data_type, ['timestamp', 'date', 'time', 'datetime'])) {
// nothing yet
} elseif (in_array($data_type, ['char', 'varchar', 'binary', 'varbinary', 'text', 'tinytext', 'mediumtext', 'longtext'])) {
$col_def['octet_length'] = is_numeric($r['character_octet_length']) ? (int) $r['character_octet_length'] : null;
$col_def['length'] = is_numeric($r['character_maximum_length']) ? (int) $r['character_maximum_length'] : null;
$has_charset = true;
} elseif (in_array($data_type, ['blob', 'tinyblob', 'mediumblob', 'longblob'])) {
$col_def['octet_length'] = (int) $r['character_octet_length'];
$col_def['length'] = (int) $r['character_maximum_length'];
} elseif (in_array($data_type, ['enum', 'set'])) {
$col_def['octet_length'] = (int) $r['character_octet_length'];
$col_def['length'] = (int) $r['character_maximum_length'];
$def = $r['column_type'];
preg_match_all("/'([^']+)'/", $def, $matches);
if (is_array($matches[1]) && count($matches) > 0) {
$col_def['values'] = $matches[1];
}
}
if ($include_options) {
$col_def['options'] = ['comment' => $r['column_comment'], 'definition' => $r['column_type'], 'column_key' => $r['column_key'], 'ordinal_position' => $r['ordinal_position'], 'constraint_type' => $r['constraint_type']];
if ($has_charset) {
$col_def['options']['charset'] = $r['character_set_name'];
$col_def['options']['collation'] = $r['collation_name'];
}
}
$columns[$column_name] = $col_def;
$foreign_keys = $table->foreign_keys;
$unique_keys = $table->unique_keys;
$constraint_name = $r['constraint_name'];
$referenced_table_name = $r['referenced_table_name'];
$referenced_column_name = $r['referenced_column_name'];
switch ($r['constraint_type']) {
case 'PRIMARY KEY':
$table->primary_keys = array_merge($table->primary_keys->toArray(), (array) $column_name);
break;
case 'UNIQUE':
if (!$unique_keys->offsetExists($constraint_name)) {
$unique_keys[$constraint_name] = [];
}
$unique_keys[$constraint_name] = array_merge($unique_keys[$constraint_name]->toArray(), (array) $column_name);
break;
case 'FOREIGN KEY':
/*
if (!$foreign_keys->offsetExists($constraint_name)) {
$foreign_keys[$constraint_name] = array();
}
*
*/
$fk = ['referenced_table' => $referenced_table_name, 'referenced_column' => $referenced_column_name, 'constraint_name' => $constraint_name];
$foreign_keys[$column_name] = $fk;
//$table->references[$referenced_table_name] = array($column_name => $r['referenced_column_name']);
if (!array_key_exists($referenced_table_name, $references)) {
$references[$referenced_table_name] = [];
}
$k = "{$table_name}:{$referenced_column_name}->{$column_name}";
$references[$referenced_table_name][$k] = ['column' => $column_name, 'referencing_table' => $table_name, 'referencing_column' => $referenced_column_name, 'constraint_name' => $constraint_name];
break;
}
}
foreach ($references as $referenced_table_name => $refs) {
if ($tables->offsetExists($referenced_table_name)) {
$table = $tables[$referenced_table_name];
$table->references = $refs;
}
}
$array = new ArrayObject($config->toArray());
unset($config);
return $array;
}
