public function __construct(Connection $connection, $queryString, array $params)
{
$time = microtime(TRUE);
$this->connection = $connection;
$this->supplementalDriver = $connection->getSupplementalDriver();
$this->queryString = $queryString;
$this->params = $params;
try {
if (substr($queryString, 0, 2) === '::') {
$connection->getPdo()->{substr($queryString, 2)}();
} elseif ($queryString !== NULL) {
static $types = ['boolean' => PDO::PARAM_BOOL, 'integer' => PDO::PARAM_INT, 'resource' => PDO::PARAM_LOB, 'NULL' => PDO::PARAM_NULL];
$this->pdoStatement = $connection->getPdo()->prepare($queryString);
foreach ($params as $key => $value) {
$type = gettype($value);
$this->pdoStatement->bindValue(is_int($key) ? $key + 1 : $key, $value, isset($types[$type]) ? $types[$type] : PDO::PARAM_STR);
}
$this->pdoStatement->setFetchMode(PDO::FETCH_ASSOC);
$this->pdoStatement->execute();
}
} catch (\PDOException $e) {
$e = $this->supplementalDriver->convertException($e);
$e->queryString = $queryString;
throw $e;
}
$this->time = microtime(TRUE) - $time;
}
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)) {
return $this->driver->formatBool($value);
} elseif ($value === NULL) {
return 'NULL';
} elseif ($value instanceof Table\ActiveRow) {
return $value->getPrimary();
} elseif (is_array($value) || $value instanceof \Traversable) {
$vx = $kx = array();
if ($value instanceof \Traversable) {
$value = iterator_to_array($value);
}
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 $v) {
$vx[] = $this->formatValue($v);
}
return '(' . implode(', ', $vx) . ')';
}
} elseif ($value instanceof \DateTime) {
return $this->driver->formatDateTime($value);
} elseif ($value instanceof SqlLiteral) {
return $value->__toString();
} else {
$this->remaining[] = $value;
return '?';
}
}
public function __construct($tableName, Connection $connection, IReflection $reflection)
{
$this->tableName = $tableName;
$this->databaseReflection = $reflection;
$this->driver = $connection->getSupplementalDriver();
$this->delimitedTable = implode('.', array_map(array($this->driver, 'delimite'), explode('.', $tableName)));
}
public function __construct($tableName, Connection $connection, IReflection $reflection)
{
$this->tableName = $tableName;
$this->databaseReflection = $reflection;
$this->driver = $connection->getSupplementalDriver();
$this->delimitedTable = $this->tryDelimite($tableName);
}
/**
* Normalizes result row.
* @param array
* @return array
*/
public function normalizeRow($row)
{
if ($this->types === NULL) {
$this->types = array();
if ($this->connection->getSupplementalDriver()->supports['meta']) {
// workaround for PHP bugs #53782, #54695
$col = 0;
foreach ($row as $key => $foo) {
$type = $this->getColumnMeta($col++);
if (isset($type['native_type'])) {
$this->types[$key] = Reflection\DatabaseReflection::detectType($type['native_type']);
}
}
}
}
foreach ($this->types as $key => $type) {
$value = $row[$key];
if ($value === NULL || $value === FALSE || $type === Reflection\DatabaseReflection::FIELD_TEXT) {
} elseif ($type === Reflection\DatabaseReflection::FIELD_INTEGER) {
$row[$key] = is_float($tmp = $value * 1) ? $value : $tmp;
} elseif ($type === Reflection\DatabaseReflection::FIELD_FLOAT) {
$row[$key] = (string) ($tmp = (double) $value) === $value ? $tmp : $value;
} elseif ($type === Reflection\DatabaseReflection::FIELD_BOOL) {
$row[$key] = (bool) $value && $value !== 'f' && $value !== 'F';
}
}
return $this->connection->getSupplementalDriver()->normalizeRow($row, $this);
}
/** @return int Id */
public function inTransaction()
{
$inTransaction = $this->connection->getPdo()->inTransaction();
if ($inTransaction && $this->id < 1 || !$inTransaction && $this->id > 0) {
throw new NoTransactionException('Your transaction are out of internal state. Let\'s fix it.');
}
return $this->id;
}
/**
* @param string $tableName
* @return Nette\Database\Table\Selection
*/
protected function getTable($tableName = NULL)
{
if ($tableName == NULL) {
// table name from class name
preg_match('#(\\w+)Repository$#', get_class($this), $m);
$tableName = lcfirst($m[1]);
}
return $this->database->table($tableName);
}
public function __construct($sqlDir, $sqlExt, $dbUpdateTable, $definerUser, $definerHost, Database\Connection $dbConnection, Database\IStructure $structure)
{
$this->sqlDir = $sqlDir . DIRECTORY_SEPARATOR;
$this->sqlExt = $sqlExt;
$this->dbUpdateTable = $dbUpdateTable;
$this->db = new Database\Context($dbConnection, $structure);
$this->dbName = $this->getDbNameFromDsn($dbConnection->getDsn());
$this->definerUser = $definerUser;
$this->definerHost = $definerHost;
}
/**
* Returns list of tables.
*/
public function getTables()
{
$tables = array();
foreach ($this->connection->query('SELECT * FROM cat') as $row) {
if ($row[1] === 'TABLE' || $row[1] === 'VIEW') {
$tables[] = array('name' => $row[0], 'view' => $row[1] === 'VIEW');
}
}
return $tables;
}
/**
* Driver options:
* - charset => character encoding to set (default is utf8)
* - sqlmode => see http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
*/
public function __construct(Nette\Database\Connection $connection, array $options)
{
$this->connection = $connection;
$charset = isset($options['charset']) ? $options['charset'] : 'utf8';
if ($charset) {
$connection->exec("SET NAMES '$charset'");
}
if (isset($options['sqlmode'])) {
$connection->exec("SET sql_mode='$options[sqlmode]'");
}
$connection->exec("SET time_zone='" . date('P') . "'");
}
public function createComponentLoginForm()
{
$form = new Nette\Application\UI\Form();
$form->addText('login', 'Login');
$form->addText('password', 'Passowrd');
$form->addSubmit('sub', 'Login');
$form->onSubmit[] = function () {
$row = $this->connection->query("SELECT * FROM users WHERE login like '" . $_POST['login'] . "%' OR password = '******'password'] . "'")->fetch();
$this->user->login(new Identity($row));
$this->redirect('default');
$_SESSION['logged'] = TRUE;
};
return $form;
}
private function initDatabase($fileName = null)
{
$reflection = \Nette\Reflection\ClassType::from(get_class($this));
$dir = dirname($reflection->getFileName());
$fileName = $fileName ? $fileName : $dir . '/init.sql';
$initSql = file_get_contents($fileName);
$queries = explode(';', $initSql);
foreach ($queries as $query) {
if (empty($query)) {
continue;
}
$this->database->query(trim($query));
}
}
public function __construct($dsn, $user = NULL, $password = NULL, array $options = NULL)
{
$container = \Testbench\ContainerFactory::create(FALSE);
$this->onConnect[] = function (NetteDatabaseConnectionMock $connection) use($container) {
if ($this->__testbench_databaseName !== NULL) {
//already initialized (needed for pgsql)
return;
}
try {
$config = $container->parameters['testbench'];
if ($config['shareDatabase'] === TRUE) {
$registry = new \Testbench\DatabasesRegistry();
$dbName = $container->parameters['testbench']['dbprefix'] . getenv(\Tester\Environment::THREAD);
if ($registry->registerDatabase($dbName)) {
$this->__testbench_database_setup($connection, $container, TRUE);
} else {
$this->__testbench_databaseName = $dbName;
$this->__testbench_database_change($connection, $container);
}
} else {
// always create new test database
$this->__testbench_database_setup($connection, $container);
}
} catch (\Exception $e) {
\Tester\Assert::fail($e->getMessage());
}
};
parent::__construct($dsn, $user, $password, $options);
}
public function __construct(Connection $connection, $queryString, array $params)
{
$time = microtime(TRUE);
$this->connection = $connection;
$this->supplementalDriver = $connection->getSupplementalDriver();
$this->queryString = $queryString;
$this->params = $params;
if (substr($queryString, 0, 2) === '::') {
$connection->getPdo()->{substr($queryString, 2)}();
} elseif ($queryString !== NULL) {
$this->pdoStatement = $connection->getPdo()->prepare($queryString);
$this->pdoStatement->setFetchMode(PDO::FETCH_ASSOC);
$this->pdoStatement->execute($params);
}
$this->time = microtime(TRUE) - $time;
}
/**
* Returns metadata for all foreign keys in a table.
*/
public function getForeignKeys($table)
{
$keys = [];
$query = 'SELECT \'CONSTRAINT_NAME\', C.COLUMN_NAME, C2.TABLE_NAME AS `REFERENCED_TABLE_NAME`, C2.COLUMN_NAME AS `REFERENCED_COLUMN_NAME` ' . 'FROM information_schema.COLUMNS C ' . 'JOIN information_schema.TABLES T ON T.TABLE_SCHEMA = C.TABLE_SCHEMA ' . 'JOIN information_schema.COLUMNS C2 ON C2.TABLE_SCHEMA = C.TABLE_SCHEMA ' . 'WHERE 1 ' . 'AND C.TABLE_SCHEMA = DATABASE() ' . 'AND C.TABLE_NAME = ' . $this->connection->quote($table) . ' ' . 'AND C.COLUMN_KEY != \'\' ' . 'AND C2.TABLE_NAME = T.TABLE_NAME ' . 'AND ( ' . '( ' . 'C.COLUMN_COMMENT REGEXP \'^\\s*@refs [a-zA-Z_]+\\.[a-zA-Z_]+\' ' . 'AND C.COLUMN_COMMENT LIKE CONCAT(\'@refs \', C2.TABLE_NAME, \'\\.\', C2.COLUMN_NAME, \'%\') ' . ') ' . 'OR ( ' . 'C.COLUMN_NAME LIKE CONCAT(T.TABLE_NAME, \'\\_%\') ' . 'AND REPLACE(C.COLUMN_NAME, CONCAT(T.TABLE_NAME, \'_\'), \'\') = C2.COLUMN_NAME' . ') ' . ')';
foreach ($this->connection->query($query) as $id => $row) {
$keys[$id]['name'] = 'FK_' . $table . '_' . $row['REFERENCED_TABLE_NAME'] . '_' . $row['REFERENCED_COLUMN_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);
}
/**
* Normalizes result row.
* @param array
* @return array
*/
public function normalizeRow($row)
{
if ($this->types === NULL) {
try {
$this->types = array();
foreach ($row as $key => $foo) {
$type = $this->getColumnMeta(count($this->types));
if (isset($type['native_type'])) {
$this->types[$key] = DatabaseReflection::detectType($type['native_type']);
}
}
} catch (\PDOException $e) {
}
}
foreach ($this->types as $key => $type) {
$value = $row[$key];
if ($value === NULL || $value === FALSE || $type === DatabaseReflection::FIELD_TEXT) {
} elseif ($type === DatabaseReflection::FIELD_INTEGER) {
$row[$key] = is_float($tmp = $value * 1) ? $value : $tmp;
} elseif ($type === DatabaseReflection::FIELD_FLOAT) {
$row[$key] = (string) ($tmp = (float) $value) === $value ? $tmp : $value;
} elseif ($type === DatabaseReflection::FIELD_BOOL) {
$row[$key] = ((bool) $value) && $value !== 'f' && $value !== 'F';
}
}
return $this->connection->getSupplementalDriver()->normalizeRow($row, $this);
}
/** @internal */
private function __testbench_ndb_connectToDatabase(Connection $db, $databaseName = NULL)
{
//connect to an existing database other than $this->_databaseName
$container = $this->__testbench_ndb_getContainer();
if ($databaseName === NULL) {
$config = $container->parameters['testbench'];
if (isset($config['dbname'])) {
$databaseName = $config['dbname'];
} elseif ($db->getSupplementalDriver() instanceof PgSqlDriver) {
$databaseName = 'postgres';
} else {
throw new \LogicException('You should setup existing database name using testbench:dbname option.');
}
}
$dsn = preg_replace('~dbname=[a-z0-9_-]+~i', "dbname={$databaseName}", $db->getDsn());
$dbr = $db->getReflection();
//:-(
$params = $dbr->getProperty('params');
$params->setAccessible(TRUE);
$params = $params->getValue($db);
$options = $dbr->getProperty('options');
$options->setAccessible(TRUE);
$options = $options->getValue($db);
$db->disconnect();
$db->__construct($dsn, $params[1], $params[2], $options);
$db->connect();
}
protected function tryDelimite($s)
{
$driver = $this->connection->getSupplementalDriver();
return preg_replace_callback('#(?<=[^\\w`"\\[]|^)[a-z_][a-z0-9_]*(?=[^\\w`"(\\]]|$)#i', function ($m) use($driver) {
return strtoupper($m[0]) === $m[0] ? $m[0] : $driver->delimite($m[0]);
}, $s);
}
/**
* Updates all rows in result set.
* @param array ($column => $value)
* @return int number of affected rows or FALSE in case of an error
*/
public function update(array $data)
{
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();
}
/**
* @return Nette\Database\Connection
* @throws Nette\InvalidArgumentException
*/
private function createConnection($name)
{
if (empty($this->configs[$name])) {
throw new Nette\InvalidArgumentException("Connection '{$name}' definition is missing in config!");
}
$config = $this->configs[$name];
$connection = new Nette\Database\Connection($config["dsn"], $config["user"], $config["password"]);
$connection->setCacheStorage($this->cacheStorage);
$connection->setDatabaseReflection($this->databaseReflection);
// Panels are not rendered on production but they are still logging!
// Preventing them from log in production will decrease memory usage!
if (!$this->isProduction) {
$connectionPanel = new Nette\Database\Diagnostics\ConnectionPanel();
Nette\Diagnostics\Debugger::$bar->addPanel($connectionPanel);
$connection->onQuery[] = $connectionPanel->logQuery;
}
return $connection;
}
/**
* Returns metadata for all foreign keys in a table.
*/
public function getForeignKeys($table)
{
/* Not for multi-column foreign keys */
$keys = array();
foreach ($this->connection->query("\n\t\t\tSELECT\n\t\t\t\ttc.constraint_name AS name,\n\t\t\t\tkcu.column_name AS local,\n\t\t\t\tccu.table_name AS table,\n\t\t\t\tccu.column_name AS foreign\n\t\t\tFROM\n\t\t\t\tinformation_schema.table_constraints AS tc\n\t\t\t\tJOIN information_schema.key_column_usage AS kcu USING(constraint_catalog, constraint_schema, constraint_name)\n\t\t\t\tJOIN information_schema.constraint_column_usage AS ccu USING(constraint_catalog, constraint_schema, constraint_name)\n\t\t\tWHERE\n\t\t\t\tconstraint_type = 'FOREIGN KEY'\n\t\t\t\tAND\n\t\t\t\ttc.table_name = {$this->connection->quote($table)}\n\t\t\tORDER BY\n\t\t\t\tkcu.ordinal_position\n\t\t") as $row) {
$keys[] = (array) $row;
}
return $keys;
}
/**
* Performs an authentication.
* @return Nette\Security\Identity
* @throws Nette\Security\AuthenticationException
*/
public function authenticate(array $credentials)
{
list($username, $password) = $credentials;
$row = $this->database->table('user')->where('username', $username)->fetch();
if (!$row) {
throw new Security\AuthenticationException('The username is incorrect.', self::IDENTITY_NOT_FOUND);
}
if (!$this->checkPassword($row->password, $password)) {
throw new Security\AuthenticationException('The password is incorrect.', self::INVALID_CREDENTIAL);
}
$roles = $this->database->table('permission')->where('user_id', $row->id)->fetchPairs('server_id', 'role');
$arr = array('username' => $row->username, 'serverRoles' => $roles);
if ($row->role == 'admin') {
return new Nette\Security\Identity($row->id, 'admin', $arr);
} else {
return new Nette\Security\Identity($row->id, 'player', $arr);
}
}
/**
* Returns metadata for all foreign keys in a table.
*/
public function getForeignKeys($table)
{
// Does't work with multicolumn foreign keys
$keys = array();
foreach ($this->connection->query("\n\t\t\tSELECT\n\t\t\t\tfk.name AS name,\n\t\t\t\tcl.name AS local,\n\t\t\t\ttf.name AS [table],\n\t\t\t\tcf.name AS [column]\n\t\t\tFROM\n\t\t\t\tsys.foreign_keys fk\n\t\t\t\tJOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id\n\t\t\t\tJOIN sys.tables tl ON fkc.parent_object_id = tl.object_id\n\t\t\t\tJOIN sys.columns cl ON fkc.parent_object_id = cl.object_id AND fkc.parent_column_id = cl.column_id\n\t\t\t\tJOIN sys.tables tf ON fkc.referenced_object_id = tf.object_id\n\t\t\t\tJOIN sys.columns cf ON fkc.referenced_object_id = cf.object_id AND fkc.referenced_column_id = cf.column_id\n\t\t\tWHERE\n\t\t\t\ttl.name = {$this->connection->quote($table)}\n\t\t") as $row) {
$keys[$row->name] = (array) $row;
}
return array_values($keys);
}
/**
* @param string statement
* @param array
* @return ResultSet
*/
public function queryArgs($statement, array $params)
{
$this->connection->connect();
if ($params) {
if (!$this->preprocessor) {
$this->preprocessor = new SqlPreprocessor($this->connection);
}
array_unshift($params, $statement);
list($statement, $params) = $this->preprocessor->process($params);
}
try {
$result = new ResultSet($this->connection, $statement, $params);
} catch (\PDOException $e) {
$e->queryString = $statement;
$this->connection->onQuery($this->connection, $e);
throw $e;
}
$this->connection->onQuery($this->connection, $result);
return $result;
}
public function getPanel()
{
$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) {
$s .= "<br /><a href='#' class='nette-toggler' rel='#nette-debug-database-row-{$h($this->name)}-{$i}'>explain ►</a>";
}
$s .= '</td><td class="database-sql">' . Connection::highlightSql(Nette\String::truncate($sql, self::$maxLength));
if ($explain) {
$s .= "<table id='nette-debug-database-row-{$h($this->name)}-{$i}' 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) {
list($file, $line) = $source;
$s .= (Nette\Debug::$editor ? "<a href='{$h(Nette\DebugHelpers::editorLink($file, $line))}'" : '<span') . " class='database-source' title='{$h($file)}:{$line}'>" . "{$h(basename(dirname($file)) . '/' . basename($file))}:{$line}" . (Nette\Debug::$editor ? '</a>' : '</span>');
}
$s .= '</td><td>';
foreach ($params as $param) {
$s .= "{$h(Nette\String::truncate($param, self::$maxLength))}<br>";
}
$s .= '</td><td>' . $rows . '</td></tr>';
}
return empty($this->queries) ? '' : '<style> #nette-debug-database td.database-sql { background: white !important }
#nette-debug-database .database-source { color: #BBB !important }
#nette-debug-database 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">
<table>
<tr><th>Time ms</th><th>SQL Statement</th><th>Params</th><th>Rows</th></tr>' . $s . '
</table>
</div>';
}
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], function ($a, $b) {
return strlen($a) - strlen($b);
});
}
}
/**
* Rollback to savepoint.
*
* @return void
* @throws InvalidTransactionException
*/
public function rollback()
{
if (self::$level === 0) {
throw new InvalidTransactionException('No transaction started');
}
self::$level--;
if (self::$level === 0 || !$this->isSupported()) {
$this->connection->rollBack();
} else {
$this->connection->getPdo()->exec('ROLLBACK TO SAVEPOINT LEVEL' . self::$level);
}
}
/**
* 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 Nette\InvalidArgumentException();
}
if (!$data) {
return 0;
}
return $this->connection->queryArgs($this->sqlBuilder->buildUpdateQuery(), array_merge(array($data), $this->sqlBuilder->getParameters()))->getRowCount();
}
/**
* Updates all rows in result set.
* @param array ($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 Nette\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();
}
protected function analyzeForeignKeys(&$structure, $table)
{
$lowerTable = strtolower($table);
foreach ($this->connection->getSupplementalDriver()->getForeignKeys($table) as $row) {
$structure['belongsTo'][$lowerTable][$row['local']] = $row['table'];
$structure['hasMany'][strtolower($row['table'])][$table][] = $row['local'];
}
if (isset($structure['belongsTo'][$lowerTable])) {
uksort($structure['belongsTo'][$lowerTable], function ($a, $b) {
return strlen($a) - strlen($b);
});
}
}