/** * 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; }