SQL достъп

Nette Database предлага два пътя: можете да пишете SQL заявки сами (SQL достъп) или да ги оставите да се генерират автоматично (вижте Explorer). SQL достъпът ви дава пълен контрол над заявките, като същевременно гарантира тяхното безопасно изграждане.

Подробности за свързването и конфигурацията на базата данни можете да намерите в главата Свързване и конфигурация.

Основно запитване

За запитвания към базата данни се използва методът query(). Той връща обект ResultSet, който представлява резултата от заявката. В случай на неуспех, методът хвърля изключение. Можем да обходим резултата от заявката с помощта на цикъл foreach или да използваме някоя от помощните функции.

$result = $database->query('SELECT * FROM users');

foreach ($result as $row) {
	echo $row->id;
	echo $row->name;
}

За безопасно вмъкване на стойности в SQL заявки използваме параметризирани заявки. Nette Database ги прави максимално прости – достатъчно е да добавите запетая и стойност след SQL заявката:

$database->query('SELECT * FROM users WHERE name = ?', $name);

При повече параметри имате две опции за запис. Можете или да “вмъквате” параметри в SQL заявката:

$database->query('SELECT * FROM users WHERE name = ?', $name, 'AND age > ?', $age);

Или първо да напишете цялата SQL заявка и след това да добавите всички параметри:

$database->query('SELECT * FROM users WHERE name = ? AND age > ?', $name, $age);

Защита от SQL инжекция

Защо е важно да се използват параметризирани заявки? Защото те ви защитават от атака, наречена SQL инжекция, при която нападателят може да вмъкне собствени SQL команди и по този начин да получи или повреди данни в базата данни.

Никога не вмъквайте променливи директно в SQL заявката! Винаги използвайте параметризирани заявки, които ви защитават от SQL инжекция.

// ❌ ОПАСЕН КОД - уязвим към SQL инжекция
$database->query("SELECT * FROM users WHERE name = '$name'");

// ✅ Безопасна параметризирана заявка
$database->query('SELECT * FROM users WHERE name = ?', $name);

Запознайте се с възможните рискове за сигурността.

Техники за запитване

Условия WHERE

Можете да запишете условията WHERE като асоциативен масив, където ключовете са имената на колоните, а стойностите са данните за сравнение. Nette Database автоматично избира най-подходящия SQL оператор според типа на стойността.

$database->query('SELECT * FROM users WHERE', [
	'name' => 'John',
	'active' => true,
]);
// WHERE `name` = 'John' AND `active` = 1

В ключа можете също изрично да посочите оператора за сравнение:

$database->query('SELECT * FROM users WHERE', [
	'age >' => 25,          // използва оператор >
	'name LIKE' => '%John%', // използва оператор LIKE
	'email NOT LIKE' => '%example.com%', // използва оператор NOT LIKE
]);
// WHERE `age` > 25 AND `name` LIKE '%John%' AND `email` NOT LIKE '%example.com%'

Nette автоматично обработва специални случаи като null стойности или масиви.

$database->query('SELECT * FROM products WHERE', [
	'name' => 'Laptop',         // използва оператор =
	'category_id' => [1, 2, 3], // използва IN
	'description' => null,      // използва IS NULL
]);
// WHERE `name` = 'Laptop' AND `category_id` IN (1, 2, 3) AND `description` IS NULL

За отрицателни условия използвайте оператора NOT:

$database->query('SELECT * FROM products WHERE', [
	'name NOT' => 'Laptop',         // използва оператор <>
	'category_id NOT' => [1, 2, 3], // използва NOT IN
	'description NOT' => null,      // използва IS NOT NULL
	'id' => [],                     // пропуска се
]);
// WHERE `name` <> 'Laptop' AND `category_id` NOT IN (1, 2, 3) AND `description` IS NOT NULL

За свързване на условия се използва операторът AND. Това може да се промени с помощта на заместващия символ ?or.

Правила ORDER BY

Сортирането ORDER BY може да се запише с помощта на масив. В ключовете посочваме колоните, а стойността ще бъде булева променлива, определяща дали да се сортира възходящо:

$database->query('SELECT id FROM author ORDER BY', [
	'id' => true, // възходящо
	'name' => false, // низходящо
]);
// SELECT id FROM author ORDER BY `id`, `name` DESC

Вмъкване на данни (INSERT)

За вмъкване на записи се използва SQL командата INSERT.

$values = [
	'name' => 'John Doe',
	'email' => 'john@example.com',
];
$database->query('INSERT INTO users ?', $values);
$userId = $database->getInsertId();

Методът getInsertId() връща ID на последния вмъкнат ред. При някои бази данни (напр. PostgreSQL) е необходимо да се посочи като параметър името на последователността, от която трябва да се генерира ID, с помощта на $database->getInsertId($sequenceId).

Като параметри можем да предаваме и #специални стойности като файлове, обекти DateTime или enum типове.

Вмъкване на няколко записа наведнъж:

$database->query('INSERT INTO users ?', [
	['name' => 'User 1', 'email' => 'user1@mail.com'],
	['name' => 'User 2', 'email' => 'user2@mail.com'],
]);

Многократното INSERT е много по-бързо, тъй като се изпълнява една единствена заявка към базата данни, вместо много отделни.

Предупреждение за сигурност: Никога не използвайте невалидирани данни като $values. Запознайте се с възможните рискове.

Актуализиране на данни (UPDATE)

За актуализиране на записи се използва SQL командата UPDATE.

// Актуализиране на един запис
$values = [
	'name' => 'John Smith',
];
$result = $database->query('UPDATE users SET ? WHERE id = ?', $values, 1);

Броят на засегнатите редове се връща от $result->getRowCount().

За UPDATE можем да използваме операторите += и -=:

$database->query('UPDATE users SET ? WHERE id = ?', [
	'login_count+=' => 1, // инкрементиране на login_count
], 1);

Пример за вмъкване или редактиране на запис, ако вече съществува. Ще използваме техниката ON DUPLICATE KEY UPDATE:

$values = [
	'name' => $name,
	'year' => $year,
];
$database->query('INSERT INTO users ? ON DUPLICATE KEY UPDATE ?',
	$values + ['id' => $id],
	$values,
);
// INSERT INTO users (`id`, `name`, `year`) VALUES (123, 'Jim', 1978)
//   ON DUPLICATE KEY UPDATE `name` = 'Jim', `year` = 1978

Забележете, че Nette Database разпознава в какъв контекст на SQL командата вмъкваме параметъра с масив и съответно изгражда SQL кода от него. Така от първия масив е изградил (id, name, year) VALUES (123, 'Jim', 1978), докато втория е преобразувал във формата name = 'Jim', year = 1978. Разглеждаме това по-подробно в секцията Подсказки за изграждане на SQL.

Изтриване на данни (DELETE)

За изтриване на записи се използва SQL командата DELETE. Пример за получаване на броя на изтритите редове:

$count = $database->query('DELETE FROM users WHERE id = ?', 1)
	->getRowCount();

Подсказки за изграждане на SQL

Подсказката е специален заместващ символ в SQL заявката, който указва как стойността на параметъра трябва да се преобразува в SQL израз:

Подсказка	Описание	Автоматично се използва
`?name`	използва се за вмъкване на име на таблица или колона	–
`?values`	генерира `(key, ...) VALUES (value, ...)`	`INSERT ... ?`, `REPLACE ... ?`
`?set`	генерира присвояване `key = value, ...`	`SET ?`, `KEY UPDATE ?`
`?and`	свързва условията в масива с оператор `AND`	`WHERE ?`, `HAVING ?`
`?or`	свързва условията в масива с оператор `OR`	–
`?order`	генерира клауза `ORDER BY`	`ORDER BY ?`, `GROUP BY ?`

За динамично вмъкване на имена на таблици и колони в заявката се използва заместващият символ ?name. Nette Database се грижи за правилното обработване на идентификаторите според конвенциите на дадената база данни (напр. затваряне в обратни кавички в MySQL).

$table = 'users';
$column = 'name';
$database->query('SELECT ?name FROM ?name WHERE id = 1', $column, $table);
// SELECT `name` FROM `users` WHERE id = 1 (в MySQL)

Внимание: използвайте символа ?name само за имена на таблици и колони от валидирани входове, в противен случай се излагате на риск за сигурността.

Обикновено не е необходимо да се посочват другите подсказки, тъй като Nette използва интелигентно автоматично откриване при съставянето на SQL заявката (вижте третата колона на таблицата). Но можете да ги използвате например в ситуация, когато искате да свържете условията с OR вместо с AND:

$database->query('SELECT * FROM users WHERE ?or', [
	'name' => 'John',
	'email' => 'john@example.com',
]);
// SELECT * FROM users WHERE `name` = 'John' OR `email` = 'john@example.com'

Специални стойности

Освен обичайните скаларни типове (string, int, bool), можете да предавате като параметри и специални стойности:

файлове: fopen('image.gif', 'r') вмъква бинарното съдържание на файла
дата и час: обекти DateTime се преобразуват в база данни формат
enum типове: инстанции на enum се преобразуват в тяхната стойност
SQL литерали: създадени с помощта на Connection::literal('NOW()') се вмъкват директно в заявката

$database->query('INSERT INTO articles ?', [
	'title' => 'My Article',
	'published_at' => new DateTime,
	'content' => fopen('image.png', 'r'),
	'state' => Status::Draft,
]);

При бази данни, които нямат нативна поддръжка за типа данни datetime (като SQLite и Oracle), DateTime се преобразува в стойност, определена в конфигурацията на базата данни чрез елемента formatDateTime (стойността по подразбиране е U – unix timestamp).

SQL литерали

В някои случаи трябва да посочите директно SQL код като стойност, който обаче не трябва да се разбира като низ и да се екранира. За това служат обектите от класа Nette\Database\SqlLiteral. Те се създават от метода Connection::literal().

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	'year >' => $database::literal('YEAR()'),
]);
// SELECT * FROM users WHERE (`name` = 'Jim') AND (`year` > YEAR())

Или алтернативно:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('year > YEAR()'),
]);
// SELECT * FROM users WHERE (`name` = 'Jim') AND (year > YEAR())

SQL литералите могат да съдържат параметри:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('year > ? AND year < ?', $min, $max),
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND (year > 1978 AND year < 2017)

Благодарение на което можем да създаваме интересни комбинации:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('?or', [
		'active' => true,
		'role' => $role,
	]),
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND (`active` = 1 OR `role` = 'admin')

Получаване на данни

Кратки пътища за SELECT заявки

За опростяване на извличането на данни Connection предлага няколко кратки пътя, които комбинират извикването на query() със следващо fetch*(). Тези методи приемат същите параметри като query(), т.е. SQL заявка и незадължителни параметри. Пълно описание на методите fetch*() ще намерите по-долу.

`fetch($sql, ...$params): ?Row`	Изпълнява заявка и връща първия ред като обект `Row`
`fetchAll($sql, ...$params): array`	Изпълнява заявка и връща всички редове като масив от обекти `Row`
`fetchPairs($sql, ...$params): array`	Изпълнява заявка и връща асоциативен масив, където първата колона представлява ключ, а втората – стойност
`fetchField($sql, ...$params): mixed`	Изпълнява заявка и връща стойността на първото поле от първия ред
`fetchList($sql, ...$params): ?array`	Изпълнява заявка и връща първия ред като индексиран масив

Пример:

// fetchField() - връща стойността на първата клетка
$count = $database->query('SELECT COUNT(*) FROM articles')
	->fetchField();

`foreach` – итерация през редове

След изпълнение на заявката се връща обект ResultSet, който позволява обхождане на резултатите по няколко начина. Най-лесният начин да изпълните заявка и да получите редовете е чрез итерация в цикъл foreach. Този начин е най-икономичен откъм памет, тъй като връща данните постепенно и не ги съхранява всички наведнъж в паметта.

$result = $database->query('SELECT * FROM users');

foreach ($result as $row) {
	echo $row->id;
	echo $row->name;
	// ...
}

ResultSet може да се итерира само веднъж. Ако трябва да итерирате многократно, първо трябва да заредите данните в масив, например с помощта на метода fetchAll().

fetch(): ?Row

Връща ред като обект Row. Ако няма повече редове, връща null. Премества вътрешния указател към следващия ред.

$result = $database->query('SELECT * FROM users');
$row = $result->fetch(); // зарежда първия ред
if ($row) {
	echo $row->name;
}

fetchAll(): array

Връща всички останали редове от ResultSet като масив от обекти Row.

$result = $database->query('SELECT * FROM users');
$rows = $result->fetchAll(); // зарежда всички редове
foreach ($rows as $row) {
	echo $row->name;
}

fetchPairs (string|int|null $key = null, string|int|null $value = null): array

Връща резултатите като асоциативен масив. Първият аргумент определя името на колоната, която ще се използва като ключ в масива, вторият аргумент определя името на колоната, която ще се използва като стойност:

$result = $database->query('SELECT id, name FROM users');
$names = $result->fetchPairs('id', 'name');
// [1 => 'John Doe', 2 => 'Jane Doe', ...]

Ако посочим само първия параметър, стойността ще бъде целият ред, т.е. обект Row:

$rows = $result->fetchPairs('id');
// [1 => Row(id: 1, name: 'John'), 2 => Row(id: 2, name: 'Jane'), ...]

В случай на дублиращи се ключове, се използва стойността от последния ред. При използване на null като ключ, масивът ще бъде индексиран числово от нула (тогава не възникват колизии):

$names = $result->fetchPairs(null, 'name');
// [0 => 'John Doe', 1 => 'Jane Doe', ...]

fetchPairs (Closure $callback): array

Алтернативно, можете да посочите като параметър callback, който за всеки ред ще връща или самата стойност, или двойка ключ-стойност.

$result = $database->query('SELECT * FROM users');
$items = $result->fetchPairs(fn($row) => "$row->id - $row->name");
// ['1 - John', '2 - Jane', ...]

// Callback може също да връща масив с двойка ключ & стойност:
$names = $result->fetchPairs(fn($row) => [$row->name, $row->age]);
// ['John' => 46, 'Jane' => 21, ...]

fetchField(): mixed

Връща стойността на първото поле от текущия ред. Ако няма повече редове, връща null. Премества вътрешния указател към следващия ред.

$result = $database->query('SELECT name FROM users');
$name = $result->fetchField(); // зарежда името от първия ред

fetchList(): ?array

Връща ред като индексиран масив. Ако няма повече редове, връща null. Премества вътрешния указател към следващия ред.

$result = $database->query('SELECT name, email FROM users');
$row = $result->fetchList(); // ['John', 'john@example.com']

getRowCount(): ?int

Връща броя на засегнатите редове от последната заявка UPDATE или DELETE. За SELECT това е броят на върнатите редове, но той може да не е известен – в такъв случай методът връща null.

getColumnCount(): ?int

Връща броя на колоните в ResultSet.

Информация за заявките

За целите на дебъгването можем да получим информация за последната изпълнена заявка:

echo $database->getLastQueryString();   // извежда SQL заявката

$result = $database->query('SELECT * FROM articles');
echo $result->getQueryString();    // извежда SQL заявката
echo $result->getTime();           // извежда времето за изпълнение в секунди

За показване на резултата като HTML таблица може да се използва:

$result = $database->query('SELECT * FROM articles');
$result->dump();

ResultSet предлага информация за типовете на колоните:

$result = $database->query('SELECT * FROM articles');
$types = $result->getColumnTypes();

foreach ($types as $column => $type) {
	echo "$column е тип $type->type"; // напр. 'id е тип int'
}

Логване на заявки

Можем да реализираме собствено логване на заявки. Събитието onQuery е масив от callback-ове, които се извикват след всяка изпълнена заявка:

$database->onQuery[] = function ($database, $result) use ($logger) {
	$logger->info('Заявка: ' . $result->getQueryString());
	$logger->info('Време: ' . $result->getTime());

	if ($result->getRowCount() > 1000) {
		$logger->warning('Голям резултатен набор: ' . $result->getRowCount() . ' реда');
	}
};