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); }); } }