/**
* Extract the Database schema from a MySQL database.
*
* @param Database $db
*
* @return array schema definition
*/
private function getSchemaMySql($db, $prefix = '')
{
$schema = [];
if ($prefix != '') {
$tables = $db->query('SHOW TABLES LIKE ' . $db->quote($prefix . '%'));
} else {
$tables = $db->query('SHOW TABLES');
}
foreach ($tables as $row) {
$table = current($row);
$referencedBy = isset($schema[$table]) ? $schema[$table]['referencedBy'] : [];
$schema[$table] = ['table' => $table, 'columns' => [], 'primaryKeys' => [], 'referencedBy' => $referencedBy];
$config =& $schema[$table];
$showCreate = $db->fetchRow('SHOW CREATE TABLE ' . $db->quoteIdentifier($table));
$createSyntax = $showCreate['Create Table'];
$lines = explode("\n", $createSyntax);
unset($lines[0]);
// CREATE TABLE * (
unset($lines[count($lines)]);
// ) ENGINE=*
foreach ($lines as $line) {
$line = preg_replace('/^ |,$/', '', $line);
// " " & "," weghalen
$line = str_replace('NOT ', 'NOT_', $line);
$line = str_replace(' KEY', '_KEY', $line);
$line = str_ireplace('CHARACTER SET', 'CHARACTER_SET', $line);
$line = str_ireplace('ON UPDATE', 'ON_UPDATE', $line);
$line = str_replace(' ', ' ', $line);
$parts = explode(' ', $line);
if (substr($parts[0], 0, 1) == '`') {
// Column description
$column = substr($parts[0], 1, -1);
$config['columns'][$column] = ['type' => $parts[1], 'null' => true];
$columnConfig =& $config['columns'][$column];
for ($i = 2; $i < count($parts); ++$i) {
$part = $parts[$i];
switch (strtoupper($part)) {
case 'NOT_NULL':
$columnConfig['null'] = false;
break;
case 'NULL':
$columnConfig['null'] = true;
break;
case 'AUTO_INCREMENT':
break;
case 'DEFAULT':
$default = '';
while ($part = $parts[$i + 1]) {
++$i;
$default .= $part;
if (substr($default, 0, 1) != "'") {
// Not a quoted string value?
break;
// end for loop
}
if (substr($default, -1) == "'") {
// End of quoted string?
break;
// end for loop
}
$default .= ' ';
}
if (substr($default, 0, 1) == "'") {
$config['columns'][$column]['default'] = substr($default, 1, -1);
// remove quotes
} else {
switch ($default) {
case 'NULL':
$default = null;
break;
case 'CURRENT_TIMESTAMP':
$default = null;
break;
default:
notice('Unknown default "' . $default . '" in "' . $line . '"');
break;
}
$config['columns'][$column]['default'] = $default;
}
break;
case 'UNSIGNED':
$config['columns'][$column]['type'] = 'unsigned ' . $config['columns'][$column]['type'];
break;
case 'COMMENT':
$i += $this->stripQuotedValue($parts, $comment, $i);
$config['columns'][$column]['comment'] = $comment;
break;
case 'ON_UPDATE':
case 'CHARACTER_SET':
case 'COLLATE':
$i++;
// ignore value
break;
default:
notice('Unknown part "' . $part . '" in "' . $line . '"');
break;
}
}
} else {
// Key description
$exploded = explode(' ', $line);
$parts = [];
for ($i = 0; $i < count($exploded); ++$i) {
$part = $exploded[$i];
if (substr($part, 0, 1) === '`') {
--$i;
$i += $this->stripQuotedValue($exploded, $value, $i, '`');
$parts[] = $value;
} else {
$parts[] = str_replace('`', '', $part);
// @todo Parse the "(`id`)" parts. Spaces in columnnames are unlikely but possible.
}
}
switch ($parts[0]) {
case 'PRIMARY_KEY':
$config['primaryKeys'] = explode(',', substr($parts[1], 1, -1));
break;
case 'KEY':
case 'UNIQUE_KEY':
case 'FULLTEXT_KEY':
break;
// Skip
// Skip
case 'CONSTRAINT':
if ($parts[2] != 'FOREIGN_KEY') {
notice('Unknown constraint: "' . $line . '"');
break;
}
if ($parts[4] != 'REFERENCES') {
notice('Unknown foreign key: "' . $line . '"');
break;
}
$column = substr($parts[3], 1, -1);
$foreignTable = $parts[5];
$foreignColumn = substr($parts[6], 1, -1);
$config['columns'][$column]['foreignKeys'][] = ['table' => $foreignTable, 'column' => $foreignColumn];
$schema[$foreignTable]['referencedBy'][] = ['table' => $table, 'column' => $column];
break;
default:
notice('Unknown metadata "' . $parts[0] . '" in "' . $line . '"', $lines);
break;
}
}
}
unset($config);
}
return $schema;
}
/**
* Compile the SQL partial for a JOIN statement and return it.
*
* @param \Gleez\Database\Database $db Database instance or name of instance
* @param array The array of join condition
* @return string
*/
protected function compileJoin(Database $db, $join)
{
if (!empty($join['type'])) {
$query = strtoupper($join['type']) . ' JOIN';
} else {
$query = 'JOIN';
}
// Quote the table name that is being joined
$query .= ' ' . $db->getConnection()->quoteTable($join['table']);
if (!empty($this->using)) {
$quote_column = array($db->getConnection(), 'quoteIdentifier');
// Quote and concat the columns
$query .= ' USING (' . implode(', ', array_map($quote_column, $this->using)) . ')';
} elseif (isset($join['on']) && !empty($join['on'])) {
$conditions = array();
foreach ($join['on'] as $k => $condition) {
// Split the condition
list($c1, $op, $c2) = $condition;
if ($op) {
// Make the operator uppercase and spaced
$op = ' ' . strtoupper($op);
}
// Quote each of the columns used for the condition
$conditions[] = $db->getConnection()->quoteIdentifier($c1) . $op . ' ' . $db->quoteIdentifier($c2);
}
// Concat the conditions "... AND ..."
$query .= ' ON (' . implode(' AND ', $conditions) . ')';
if (isset($join['and']) && !empty($join['and'])) {
$and_conditions = array();
foreach ($join['and'] as $icondition) {
// Split the condition
list($c1, $op, $v1) = $icondition;
if ($op) {
// Make the operator uppercase and spaced
$op = ' ' . strtoupper($op);
}
// Quote each of the columns used for the condition. v1 is quote value not column
$and_conditions[] = $db->getConnection()->quoteIdentifier($c1) . $op . ' ' . $db->quote($v1);
}
if (!empty($and_conditions)) {
// Concat the conditions "... AND ..."
$query .= ' AND ' . implode(' AND ', $and_conditions) . '';
}
}
}
return $query;
}
