Generates and executes SQL query.
|
public query ( $sql, $params ) : |
||
| return | ||
/** @internal */
private function __testbench_ndb_createDatabase(Connection $db)
{
$db->query("CREATE DATABASE {$this->__testbench_ndb_databaseName}");
if ($db->getSupplementalDriver() instanceof MySqlDriver) {
$db->query("USE {$this->__testbench_ndb_databaseName}");
} else {
$this->__testbench_ndb_connectToDatabase($db, $this->__testbench_ndb_databaseName);
}
}
/** @return int Id of point */
public function rollback()
{
if ($this->checkTransaction() > 1) {
$this->connection->query('ROLLBACK TO ' . $this->getSavepoint());
} else {
$this->connection->getPdo()->rollBack();
}
return --$this->id;
}
/**
* 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;
}
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 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;
}
/**
* 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);
}
/**
* 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;
}
/**
* 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);
}
/**
* Inserts row in a table.
* @param array|\Traversable|Selection array($column => $value)|\Traversable|Selection for INSERT ... SELECT
* @return IRow|int|bool Returns IRow or number of affected rows for Selection or table without primary key
*/
public function insert($data)
{
if ($data instanceof self) {
$data = new Nette\Database\SqlLiteral($data->getSql(), $data->getSqlBuilder()->getParameters());
} elseif ($data instanceof \Traversable) {
$data = iterator_to_array($data);
}
$return = $this->connection->query($this->sqlBuilder->buildInsertQuery(), $data);
$this->loadRefCache();
if ($data instanceof Nette\Database\SqlLiteral || $this->primary === NULL) {
unset($this->refCache['referencing'][$this->getGeneralCacheKey()][$this->getSpecificCacheKey()]);
return $return->getRowCount();
}
$primaryKey = $this->connection->getInsertId($this->getPrimarySequence());
if ($primaryKey === FALSE) {
unset($this->refCache['referencing'][$this->getGeneralCacheKey()][$this->getSpecificCacheKey()]);
return $return->getRowCount();
}
if (is_array($this->getPrimary())) {
$primaryKey = array();
foreach ((array) $this->getPrimary() as $key) {
if (!isset($data[$key])) {
return $data;
}
$primaryKey[$key] = $data[$key];
}
if (count($primaryKey) === 1) {
$primaryKey = reset($primaryKey);
}
}
$row = $this->createSelectionInstance()
->select('*')
->wherePrimary($primaryKey)
->fetch();
if ($this->rows !== NULL) {
if ($signature = $row->getSignature(FALSE)) {
$this->rows[$signature] = $row;
$this->data[$signature] = $row;
} else {
$this->rows[] = $row;
$this->data[] = $row;
}
}
return $row;
}
/**
* Returns metadata for all foreign keys in a table.
*/
public function getForeignKeys($table)
{
$keys = [];
$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);
}
/**
* Inserts row in a table.
* @param mixed array($column => $value)|Traversable for single row insert or TableSelection|string for INSERT ... SELECT
* @return ActiveRow or FALSE in case of an error or number of affected rows for INSERT ... SELECT
*/
public function insert($data)
{
if ($data instanceof Selection) {
$data = $data->getSql();
} elseif ($data instanceof \Traversable) {
$data = iterator_to_array($data);
}
$return = $this->connection->query("INSERT INTO {$this->delimitedName}", $data);
$this->rows = NULL;
if (!is_array($data)) {
return $return->rowCount();
}
if (!isset($data[$this->primary]) && ($id = $this->connection->lastInsertId())) {
$data[$this->primary] = $id;
}
return new ActiveRow($data, $this);
}
public function removeTranslation($original, $lang, $namespace = NULL)
{
$arg = array();
$arg[0] = "SELECT d.`id` FROM {$this->defaultTable} d\n\t\t\tJOIN {$this->translationTable} t ON d.`id` = t.`text_id`\n\t\t\tWHERE ";
if ($namespace) {
$arg[0] .= 'd.`ns` = ? AND ';
$arg[] = $namespace;
}
$arg[0] .= "d.`text` = ? AND t.`lang` = ?";
$arg[] = $original;
$arg[] = $lang;
$id = $this->fetchField($arg);
if ($id) {
$this->db->query("DELETE FROM {$this->translationTable} WHERE text_id = ?", $id);
$this->db->query("DELETE FROM {$this->defaultTable} WHERE id = ?", $id);
}
}
/**
* Returns metadata for all indexes in a table.
*/
public function getIndexes($table)
{
$columns = array();
foreach ($this->connection->query("\n\t\t\tSELECT ordinal_position, column_name\n\t\t\tFROM information_schema.columns\n\t\t\tWHERE table_name = {$this->connection->quote($table)} AND table_schema = current_schema()\n\t\t\tORDER BY ordinal_position\n\t\t") as $row) {
$columns[$row['ordinal_position']] = $row['column_name'];
}
$indexes = array();
foreach ($this->connection->query("\n\t\t\tSELECT pg_class2.relname, indisunique, indisprimary, indkey\n\t\t\tFROM pg_class\n\t\t\tLEFT JOIN pg_index on pg_class.oid = pg_index.indrelid\n\t\t\tINNER JOIN pg_class as pg_class2 on pg_class2.oid = pg_index.indexrelid\n\t\t\tWHERE pg_class.relname = {$this->connection->quote($table)}\n\t\t") 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);
}
/**
* Inserts row in a table.
*
* @param mixed array($column => $value)|Traversable for single row insert or Selection|string for INSERT ... SELECT
*
* @return ActiveRow or FALSE in case of an error or number of affected rows for INSERT ... SELECT
*/
public function insert($data)
{
if ($data instanceof Selection) {
$data = $data->getSql();
} elseif ($data instanceof \Traversable) {
$data = iterator_to_array($data);
}
$return = $this->connection->query($this->sqlBuilder->buildInsertQuery(), $data);
$this->checkReferenced = true;
if (!is_array($data)) {
return $return->rowCount();
}
if (!isset($data[$this->primary]) && ($id = $this->connection->lastInsertId())) {
$data[$this->primary] = $id;
return $this->rows[$id] = $this->createRow($data);
} else {
return $this->createRow($data);
}
}
/**
* Returns metadata for all foreign keys in a table.
*/
public function getForeignKeys($table)
{
$keys = [];
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['from'];
// local columns
$keys[$row['id']]['table'] = $row['table'];
// referenced table
$keys[$row['id']]['foreign'] = $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);
}
/**
* Inserts row in a table.
* @param mixed array($column => $value)|Traversable for single row insert or Selection|string for INSERT ... SELECT
* @return ActiveRow or FALSE in case of an error or number of affected rows for INSERT ... SELECT
*/
public function insert($data)
{
if ($data instanceof Selection) {
$data = $data->getSql();
} elseif ($data instanceof \Traversable) {
$data = iterator_to_array($data);
}
$return = $this->connection->query($this->sqlBuilder->buildInsertQuery(), $data);
$this->checkReferenced = TRUE;
if (!is_array($data)) {
return $return->rowCount();
}
if (!is_array($this->primary) && !isset($data[$this->primary]) && ($id = $this->connection->lastInsertId($this->getPrimarySequence()))) {
$data[$this->primary] = $id;
}
$row = $this->createRow($data);
if ($signature = $row->getSignature(FALSE)) {
$this->rows[$signature] = $row;
}
return $row;
}
/**
* 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_class AS cl ON co.conrelid = cl.oid
JOIN pg_catalog.pg_class AS cf ON co.confrelid = cf.oid
JOIN pg_catalog.pg_namespace AS nf ON nf.oid = cf.relnamespace
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
co.contype = 'f'
AND cl.oid = {$this->connection->quote($this->delimiteFQN($table))}::regclass
AND nf.nspname = ANY (pg_catalog.current_schemas(FALSE))
")->fetchAll();
}
/**
* Shortcut for query()->fetchAll()
* @param string statement
* @param mixed [parameters, ...]
* @return array
*/
public function fetchAll($args)
{
return $this->connection->query(func_get_args())->fetchAll();
}
public function exec($sql)
{
return $this->conn->query($sql)->getRowCount();
}
/**
* Returns metadata for all foreign keys in a table.
*/
public function getForeignKeys($table)
{
/* Does't work with multicolumn foreign keys */
return $this->connection->query("\n\t\t\tSELECT\n\t\t\t\tco.conname::varchar AS name,\n\t\t\t\tal.attname::varchar AS local,\n\t\t\t\tnf.nspname || '.' || cf.relname::varchar AS table,\n\t\t\t\taf.attname::varchar AS foreign\n\t\t\tFROM\n\t\t\t\tpg_catalog.pg_constraint AS co\n\t\t\t\tJOIN pg_catalog.pg_class AS cl ON co.conrelid = cl.oid\n\t\t\t\tJOIN pg_catalog.pg_class AS cf ON co.confrelid = cf.oid\n\t\t\t\tJOIN pg_catalog.pg_namespace AS nf ON nf.oid = cf.relnamespace\n\t\t\t\tJOIN pg_catalog.pg_attribute AS al ON al.attrelid = cl.oid AND al.attnum = co.conkey[1]\n\t\t\t\tJOIN pg_catalog.pg_attribute AS af ON af.attrelid = cf.oid AND af.attnum = co.confkey[1]\n\t\t\tWHERE\n\t\t\t\tco.contype = 'f'\n\t\t\t\tAND cl.oid = {$this->connection->quote($this->delimiteFQN($table))}::regclass\n\t\t\t\tAND nf.nspname = ANY (pg_catalog.current_schemas(FALSE))\n\t\t")->fetchAll();
}
/**
* Shortcut for query()->fetchAll()
* @param string
* @return array
*/
public function fetchAll($sql, ...$params)
{
return $this->connection->query($sql, ...$params)->fetchAll();
}
/**
* Returns metadata for all foreign keys in a table.
*/
public function getForeignKeys($table)
{
/* Does't work with multicolumn foreign keys */
return $this->connection->query("\r\n\t\t\tSELECT\r\n\t\t\t\tco.conname::varchar AS name,\r\n\t\t\t\tal.attname::varchar AS local,\r\n\t\t\t\tcf.relname::varchar AS table,\r\n\t\t\t\taf.attname::varchar AS foreign\r\n\t\t\tFROM\r\n\t\t\t\tpg_catalog.pg_constraint AS co\r\n\t\t\t\tJOIN pg_catalog.pg_namespace AS n ON co.connamespace = n.oid\r\n\t\t\t\tJOIN pg_catalog.pg_class AS cl ON co.conrelid = cl.oid\r\n\t\t\t\tJOIN pg_catalog.pg_class AS cf ON co.confrelid = cf.oid\r\n\t\t\t\tJOIN pg_catalog.pg_attribute AS al ON al.attrelid = cl.oid AND al.attnum = co.conkey[1]\r\n\t\t\t\tJOIN pg_catalog.pg_attribute AS af ON af.attrelid = cf.oid AND af.attnum = co.confkey[1]\r\n\t\t\tWHERE\r\n\t\t\t\tARRAY[n.nspname] <@ pg_catalog.current_schemas(FALSE)\r\n\t\t\t\tAND co.contype = 'f'\r\n\t\t\t\tAND cl.relname = {$this->connection->quote($table)}\r\n\t\t")->fetchAll();
}
/**
* Method for dropping the testing database
* @param Connection $db
*/
protected function dropDatabase(Connection $db)
{
$db->query("DROP DATABASE IF EXISTS {$this->databaseName}");
}
/**
* Returns metadata for all foreign keys in a table.
*/
public function getForeignKeys($table)
{
return $this->connection->query("\n\t\t\tSELECT tc.table_name AS name, kcu.column_name AS local, ccu.table_name AS table, ccu.column_name AS foreign\n\t\t\tFROM information_schema.table_constraints AS tc\n\t\t\tJOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name AND tc.constraint_schema = kcu.constraint_schema\n\t\t\tJOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name AND ccu.constraint_schema = tc.constraint_schema\n\t\t\tWHERE\n\t\t\t\tconstraint_type = 'FOREIGN KEY' AND\n\t\t\t\ttc.table_name = {$this->connection->quote($table)} AND\n\t\t\t\ttc.constraint_schema = current_schema()\n\t\t")->fetchAll();
}
/**
* Import SQL dump from file - extreme fast.
* @return int count of commands
*/
public static function loadFromFile(Connection $connection, $file)
{
@set_time_limit(0);
// intentionally @
$handle = @fopen($file, 'r');
// intentionally @
if (!$handle) {
throw new Nette\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;
}
/**
* Import SQL dump from file - extremely fast.
* @return int count of commands
*/
public static function loadFromFile(Connection $connection, $file)
{
@set_time_limit(0);
// intentionally @
$handle = @fopen($file, 'r');
// intentionally @
if (!$handle) {
throw new Nette\FileNotFoundException("Cannot open file '{$file}'.");
}
$count = 0;
$delimiter = ';';
$sql = '';
while (!feof($handle)) {
$s = rtrim(fgets($handle));
if (!strncasecmp($s, 'DELIMITER ', 10)) {
$delimiter = substr($s, 10);
} elseif (substr($s, -strlen($delimiter)) === $delimiter) {
$sql .= substr($s, 0, -strlen($delimiter));
$connection->query($sql);
// native query without logging
$sql = '';
$count++;
} else {
$sql .= $s . "\n";
}
}
if (trim($sql) !== '') {
$connection->query($sql);
$count++;
}
fclose($handle);
return $count;
}
function __construct(Nette\Database\Connection $connection, array $options)
{
$this->connection = $connection;
$charset = isset($options['charset']) ? $options['charset'] : 'utf8';
if ($charset) {
$connection->query("SET NAMES '{$charset}'");
}
if (isset($options['sqlmode'])) {
$connection->query("SET sql_mode='{$options['sqlmode']}'");
}
$connection->query("SET time_zone='" . date('P') . "'");
}
/**
* Empty database
*/
protected function emptyDatabase()
{
$this->connection->query('DROP DATABASE oauth_test');
$this->connection->query('CREATE DATABASE oauth_test');
$this->connection->query('USE oauth_test');
}