public function __construct($tableName, NConnection $connection, IReflection $reflection)
{
$this->tableName = $tableName;
$this->databaseReflection = $reflection;
$this->driver = $connection->getSupplementalDriver();
$this->driverName = $connection->getAttribute(PDO::ATTR_DRIVER_NAME);
$this->delimitedTable = $this->tryDelimite($tableName);
}
/**
*
* @return NTableSelection
*/
public function getTable()
{
// název tabulky odvodit z názvu třídy
preg_match('#(\\w+)Repository$#', get_class($this), $m);
$string = $m[1];
$string[0] = strtolower($string[0]);
return $this->connection->table($string);
}
/**
* Returns metadata for all indexes in a table.
*/
public function getIndexes($table)
{
$columns = array();
foreach ($this->connection->query("
SELECT ordinal_position, column_name
FROM information_schema.columns
WHERE table_name = {$this->connection->quote($table)} AND table_schema = current_schema()
ORDER BY ordinal_position
") as $row) {
$columns[$row['ordinal_position']] = $row['column_name'];
}
$indexes = array();
foreach ($this->connection->query("
SELECT pg_class2.relname, indisunique, indisprimary, indkey
FROM pg_class
LEFT JOIN pg_index on pg_class.oid = pg_index.indrelid
INNER JOIN pg_class as pg_class2 on pg_class2.oid = pg_index.indexrelid
WHERE pg_class.relname = {$this->connection->quote($table)}
") as $row) {
$indexes[$row['relname']]['name'] = $row['relname'];
$indexes[$row['relname']]['unique'] = $row['indisunique'] === 't';
$indexes[$row['relname']]['primary'] = $row['indisprimary'] === 't';
foreach (explode(' ', $row['indkey']) as $index) {
$indexes[$row['relname']]['columns'][] = $columns[$index];
}
}
return array_values($indexes);
}
protected function reloadForeignKeys($table)
{
foreach ($this->connection->getSupplementalDriver()->getForeignKeys($table) as $row) {
$this->structure['belongsTo'][strtolower($table)][$row['local']] = $row['table'];
$this->structure['hasMany'][strtolower($row['table'])][$row['local'] . $table] = array($row['local'], $table);
}
if (isset($this->structure['belongsTo'][$table])) {
uksort($this->structure['belongsTo'][$table], create_function('$a, $b', '
return strlen($a) - strlen($b);
'));
}
}
/**
* Returns list of tables.
*/
public function getTables()
{
$tables = array();
foreach ($this->connection->query('SELECT * FROM cat', PDO::FETCH_NUM) as $row) {
if ($row[1] === 'TABLE' || $row[1] === 'VIEW') {
$tables[] = array(
'name' => $row[0],
'view' => $row[1] === 'VIEW',
);
}
}
return $tables;
}
private function detectColumnTypes()
{
if ($this->types === NULL) {
$this->types = array();
if ($this->connection->getSupplementalDriver()->supports['meta']) { // workaround for PHP bugs #53782, #54695
$col = 0;
while ($meta = $this->getColumnMeta($col++)) {
if (isset($meta['native_type'])) {
$this->types[$meta['name']] = self::detectType($meta['native_type']);
}
}
}
}
return $this->types;
}
/**
* Returns metadata for all foreign keys in a table.
*/
public function getForeignKeys($table)
{
$keys = array();
$query = 'SELECT CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE '
. 'WHERE TABLE_SCHEMA = DATABASE() AND REFERENCED_TABLE_NAME IS NOT NULL AND TABLE_NAME = ' . $this->connection->quote($table);
foreach ($this->connection->query($query) as $id => $row) {
$keys[$id]['name'] = $row['CONSTRAINT_NAME']; // foreign key name
$keys[$id]['local'] = $row['COLUMN_NAME']; // local columns
$keys[$id]['table'] = $row['REFERENCED_TABLE_NAME']; // referenced table
$keys[$id]['foreign'] = $row['REFERENCED_COLUMN_NAME']; // referenced columns
}
return array_values($keys);
}
protected function reloadTableReference($table)
{
$tables = array();
$query = 'SELECT COLUMN_NAME, REFERENCED_TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ' . $this->connection->quote($table);
foreach ($this->connection->query($query) as $row) {
$tables[strtolower($row[0])] = $row[1];
}
uksort($tables, create_function('$a, $b', '
return strlen($a) - strlen($b);
'));
$this->structure['belongsTo'][$table] = $tables;
}
private function detectColumnTypes()
{
if ($this->types === NULL) {
$this->types = array();
if ($this->connection->getSupplementalDriver()->isSupported(ISupplementalDriver::SUPPORT_COLUMNS_META)) { // workaround for PHP bugs #53782, #54695
$count = $this->columnCount();
for ($col = 0; $col < $count; $col++) {
$meta = $this->getColumnMeta($col);
if (isset($meta['native_type'])) {
$this->types[$meta['name']] = NDatabaseHelpers::detectType($meta['native_type']);
}
}
}
}
return $this->types;
}
/**
* Returns metadata for all foreign keys in a table.
*/
public function getForeignKeys($table)
{
$keys = array();
foreach ($this->connection->query("PRAGMA foreign_key_list({$this->delimite($table)})") as $row) {
$keys[$row['id']]['name'] = $row['id']; // foreign key name
$keys[$row['id']]['local'][$row['seq']] = $row['from']; // local columns
$keys[$row['id']]['table'] = $row['table']; // referenced table
$keys[$row['id']]['foreign'][$row['seq']] = $row['to']; // referenced columns
$keys[$row['id']]['onDelete'] = $row['on_delete'];
$keys[$row['id']]['onUpdate'] = $row['on_update'];
if ($keys[$row['id']]['foreign'][0] == NULL) {
$keys[$row['id']]['foreign'] = NULL;
}
}
return array_values($keys);
}
/**
* Returns metadata for all indexes in a table.
*/
public function getIndexes($table)
{
/*$this->connection->query("
SELECT *
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = {$this->connection->quote($table)} AND TABLE_SCHEMA = DATABASE()
AND REFERENCED_COLUMN_NAME IS NULL
");*/
$indexes = array();
foreach ($this->connection->query('SHOW INDEX FROM ' . $this->delimite($table)) as $row) {
$indexes[$row['Key_name']]['name'] = $row['Key_name'];
$indexes[$row['Key_name']]['unique'] = !$row['Non_unique'];
$indexes[$row['Key_name']]['primary'] = $row['Key_name'] === 'PRIMARY';
$indexes[$row['Key_name']]['columns'][$row['Seq_in_index'] - 1] = $row['Column_name'];
}
return array_values($indexes);
}
/**
* Updates all rows in result set.
* @param array|Traversable ($column => $value)
* @return int number of affected rows or FALSE in case of an error
*/
public function update($data)
{
if ($data instanceof Traversable) {
$data = iterator_to_array($data);
} elseif (!is_array($data)) {
throw new InvalidArgumentException;
}
if (!$data) {
return 0;
}
// joins in UPDATE are supported only in MySQL
return $this->connection->queryArgs(
'UPDATE' . $this->topString() . " $this->delimitedName SET ?" . $this->whereString(),
array_merge(array($data), $this->parameters)
)->rowCount();
}
/**
* Updates all rows in result set.
* Joins in UPDATE are supported only in MySQL
* @param array|Traversable ($column => $value)
* @return int number of affected rows or FALSE in case of an error
*/
public function update($data)
{
if ($data instanceof Traversable) {
$data = iterator_to_array($data);
} elseif (!is_array($data)) {
throw new InvalidArgumentException;
}
if (!$data) {
return 0;
}
return $this->connection->queryArgs(
$this->sqlBuilder->buildUpdateQuery(),
array_merge(array($data), $this->sqlBuilder->getParameters())
)->rowCount();
}
/**
* Returns metadata for all foreign keys in a table.
*/
public function getForeignKeys($table)
{
/* Does't work with multicolumn foreign keys */
return $this->connection->query("
SELECT
co.conname::varchar AS name,
al.attname::varchar AS local,
cf.relname::varchar AS table,
af.attname::varchar AS foreign
FROM
pg_catalog.pg_constraint AS co
JOIN pg_catalog.pg_namespace AS n ON co.connamespace = n.oid
JOIN pg_catalog.pg_class AS cl ON co.conrelid = cl.oid
JOIN pg_catalog.pg_class AS cf ON co.confrelid = cf.oid
JOIN pg_catalog.pg_attribute AS al ON al.attrelid = cl.oid AND al.attnum = co.conkey[1]
JOIN pg_catalog.pg_attribute AS af ON af.attrelid = cf.oid AND af.attnum = co.confkey[1]
WHERE
n.nspname = current_schema()
AND co.contype = 'f'
AND cl.relname = {$this->connection->quote($table)}
")->fetchAll();
}
public function getPanel()
{
$this->disabled = TRUE;
$s = '';
$h = 'htmlSpecialChars';
foreach ($this->queries as $i => $query) {
list($sql, $params, $time, $rows, $connection, $source) = $query;
$explain = NULL; // EXPLAIN is called here to work SELECT FOUND_ROWS()
if ($this->explain && preg_match('#\s*SELECT\s#iA', $sql)) {
try {
$explain = $connection->queryArgs('EXPLAIN ' . $sql, $params)->fetchAll();
} catch (PDOException $e) {}
}
$s .= '<tr><td>' . sprintf('%0.3f', $time * 1000);
if ($explain) {
static $counter;
$counter++;
$s .= "<br /><a href='#' class='nette-toggler' rel='#nette-DbConnectionPanel-row-$counter'>explain ►</a>";
}
$s .= '</td><td class="nette-DbConnectionPanel-sql">' . NConnection::highlightSql(self::$maxLength ? NStrings::truncate($sql, self::$maxLength) : $sql);
if ($explain) {
$s .= "<table id='nette-DbConnectionPanel-row-$counter' class='nette-collapsed'><tr>";
foreach ($explain[0] as $col => $foo) {
$s .= "<th>{$h($col)}</th>";
}
$s .= "</tr>";
foreach ($explain as $row) {
$s .= "<tr>";
foreach ($row as $col) {
$s .= "<td>{$h($col)}</td>";
}
$s .= "</tr>";
}
$s .= "</table>";
}
if ($source) {
$s .= NDebugHelpers::editorLink($source[0], $source[1])->class('nette-DbConnectionPanel-source');
}
$s .= '</td><td>';
foreach ($params as $param) {
$s .= NDebugger::dump($param, TRUE);
}
$s .= '</td><td>' . $rows . '</td></tr>';
}
return empty($this->queries) ? '' :
'<style> #nette-debug td.nette-DbConnectionPanel-sql { background: white !important }
#nette-debug .nette-DbConnectionPanel-source { color: #BBB !important }
#nette-debug nette-DbConnectionPanel tr table { margin: 8px 0; max-height: 150px; overflow:auto } </style>
<h1>Queries: ' . count($this->queries) . ($this->totalTime ? ', time: ' . sprintf('%0.3f', $this->totalTime * 1000) . ' ms' : '') . '</h1>
<div class="nette-inner nette-DbConnectionPanel">
<table>
<tr><th>Time ms</th><th>SQL Statement</th><th>Params</th><th>Rows</th></tr>' . $s . '
</table>
</div>';
}
/**
* @return NConnection
*/
protected function createServiceNette__database__default()
{
$service = new NConnection('mysql:host=localhost;dbname=rs', 'root', NULL, NULL);
$service->setCacheStorage($this->getService('cacheStorage'));
NDebugger::$blueScreen->addPanel('NDatabasePanel::renderException');
$service->setDatabaseReflection(new NDiscoveredReflection($this->getService('cacheStorage')));
return $service;
}
/**
* Import SQL dump from file - extreme fast.
* @return int count of commands
*/
public static function loadFromFile(NConnection $connection, $file)
{
@set_time_limit(0); // intentionally @
$handle = @fopen($file, 'r'); // intentionally @
if (!$handle) {
throw new FileNotFoundException("Cannot open file '$file'.");
}
$count = 0;
$sql = '';
while (!feof($handle)) {
$s = fgets($handle);
$sql .= $s;
if (substr(rtrim($s), -1) === ';') {
$connection->query($sql); // native query without logging
$sql = '';
$count++;
}
}
if (trim($sql) !== '') {
$connection->query($sql);
$count++;
}
fclose($handle);
return $count;
}
public function createAuthenticatorService()
{
return new Authenticator($this->database->table('users'));
}
private function formatValue($value)
{
if (is_string($value)) {
if (strlen($value) > 20) {
$this->remaining[] = $value;
return '?';
} else {
return $this->connection->quote($value);
}
} elseif (is_int($value)) {
return (string) $value;
} elseif (is_float($value)) {
return rtrim(rtrim(number_format($value, 10, '.', ''), '0'), '.');
} elseif (is_bool($value)) {
$this->remaining[] = $value;
return '?';
} elseif ($value === NULL) {
return 'NULL';
} elseif ($value instanceof NTableRow) {
return $value->getPrimary();
} elseif (is_array($value) || $value instanceof Traversable) {
$vx = $kx = array();
if (isset($value[0])) { // non-associative; value, value, value
foreach ($value as $v) {
$vx[] = $this->formatValue($v);
}
return implode(', ', $vx);
} elseif ($this->arrayMode === 'values') { // (key, key, ...) VALUES (value, value, ...)
$this->arrayMode = 'multi';
foreach ($value as $k => $v) {
$kx[] = $this->driver->delimite($k);
$vx[] = $this->formatValue($v);
}
return '(' . implode(', ', $kx) . ') VALUES (' . implode(', ', $vx) . ')';
} elseif ($this->arrayMode === 'assoc') { // key=value, key=value, ...
foreach ($value as $k => $v) {
$vx[] = $this->driver->delimite($k) . '=' . $this->formatValue($v);
}
return implode(', ', $vx);
} elseif ($this->arrayMode === 'multi') { // multiple insert (value, value, ...), ...
foreach ($value as $k => $v) {
$vx[] = $this->formatValue($v);
}
return '(' . implode(', ', $vx) . ')';
}
} elseif ($value instanceof DateTime) {
return $this->driver->formatDateTime($value);
} elseif ($value instanceof NSqlLiteral) {
return $value->value;
} else {
$this->remaining[] = $value;
return '?';
}
}
/**
* @return NConnection
*/
protected function createServiceNette__database__default()
{
$service = new NConnection('mysql:host=sql.satoya.cz;dbname=gymfed_demo', 'gymfed_demo', 'HuuJNUw6GpjMjqfx', NULL);
$service->setCacheStorage($this->getService('cacheStorage'));
NDebugger::$blueScreen->addPanel('NDatabasePanel::renderException');
$service->setDatabaseReflection(new NDiscoveredReflection($this->getService('cacheStorage')));
return $service;
}
