SQL přístup
***********

.[perex]
Nette Database nabízí dvě cesty: můžete psát SQL dotazy sami (SQL přístup), nebo je nechat generovat automaticky (viz [Explorer |explorer]). SQL přístup vám dává plnou kontrolu nad dotazy a přitom zajišťuje jejich bezpečné sestavení.

.[note]
Detaily k připojení a konfiguraci databáze najdete v kapitole [Připojení a konfigurace |guide#Připojení a konfigurace].


Základní dotazování
===================

Pro dotazování do databáze slouží metoda `query()`. Ta vrací objekt [ResultSet |api:Nette\Database\ResultSet], který reprezentuje výsledek dotazu. V případě selhání metoda [vyhodí výjimku|exceptions].
Výsledek dotazu můžeme procházet pomocí cyklu `foreach`, nebo použít některou z [pomocných funkcí|#Získání dat].

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

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

Pro bezpečné vkládání hodnot do SQL dotazů používáme parametrizované dotazy. Nette Database je dělá maximálně jednoduché - stačí za SQL dotaz přidat čárku a hodnotu:

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

Při více parametrech máte dvě možnosti zápisu. Buď můžete SQL dotaz "prokládat" parametry:

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

Nebo napsat nejdříve celý SQL dotaz a pak připojit všechny parametry:

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


Ochrana před SQL injection
==========================

Proč je důležité používat parametrizované dotazy? Protože vás chrání před útokem zvaným SQL injection, při kterém by útočník mohl podstrčit vlastní SQL příkazy a tím získat nebo poškodit data v databázi.

.[warning]
**Nikdy nevkládejte proměnné přímo do SQL dotazu!** Vždy používejte parametrizované dotazy, které vás ochrání před SQL injection.

```php
// ❌ NEBEZPEČNÝ KÓD - zranitelný vůči SQL injection
$database->query("SELECT * FROM users WHERE name = '$name'");

// ✅ Bezpečný parametrizovaný dotaz
$database->query('SELECT * FROM users WHERE name = ?', $name);
```

Seznamte se s [možnými bezpečnostními riziky |security].


Techniky dotazování
===================


Podmínky WHERE
--------------

Podmínky WHERE můžete zapsat jako asociativní pole, kde klíče jsou názvy sloupců a hodnoty jsou data pro porovnání. Nette Database automaticky vybere nejvhodnější SQL operátor podle typu hodnoty.

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

V klíči můžete také explicitně specifikovat operátor pro porovnání:

```php
$database->query('SELECT * FROM users WHERE', [
	'age >' => 25,          // použije operátor >
	'name LIKE' => '%John%', // použije operátor LIKE
	'email NOT LIKE' => '%example.com%', // použije operátor NOT LIKE
]);
// WHERE `age` > 25 AND `name` LIKE '%John%' AND `email` NOT LIKE '%example.com%'
```

Nette automaticky ošetřuje speciální případy jako `null` hodnoty nebo pole.

```php
$database->query('SELECT * FROM products WHERE', [
	'name' => 'Laptop',         // použije operátor =
	'category_id' => [1, 2, 3], // použije IN
	'description' => null,      // použije IS NULL
]);
// WHERE `name` = 'Laptop' AND `category_id` IN (1, 2, 3) AND `description` IS NULL
```

Pro negativní podmínky použijte operátor `NOT`:

```php
$database->query('SELECT * FROM products WHERE', [
	'name NOT' => 'Laptop',         // použije operátor <>
	'category_id NOT' => [1, 2, 3], // použije NOT IN
	'description NOT' => null,      // použije IS NOT NULL
	'id' => [],                     // vynechá se
]);
// WHERE `name` <> 'Laptop' AND `category_id` NOT IN (1, 2, 3) AND `description` IS NOT NULL
```

Pro spojování podmínek se používá operátor `AND`. To lze změnit pomocí [zástupného symbolu ?or|#Hinty pro sestavování SQL].


Pravidla ORDER BY
-----------------

Řazení `ORDER BY` se dá zapsat pomocí pole. V klíčích uvedeme sloupce a hodnotou bude boolean určující, zda řadit vzestupně:

```php
$database->query('SELECT id FROM author ORDER BY', [
	'id' => true, // vzestupně
	'name' => false, // sestupně
]);
// SELECT id FROM author ORDER BY `id`, `name` DESC
```


Vkládání dat (INSERT)
---------------------

Pro vkládání záznamů se používá SQL příkaz `INSERT`.

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

Metoda `getInsertId()` vrátí ID naposledy vloženého řádku. U některých databází (např. PostgreSQL) je nutné jako parametr specifikovat název sekvence, ze které se má ID generovat pomocí `$database->getInsertId($sequenceId)`.

Jako parametry můžeme předávat i [#speciální hodnoty] jako soubory, objekty DateTime nebo výčtové typy.

Vložení více záznamů najednou:

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

Vícenásobný INSERT je mnohem rychlejší, protože se provede jediný databázový dotaz, namísto mnoha jednotlivých.

**Bezpečnostní upozornění:** Nikdy nepoužívejte jako `$values` nevalidovaná data. Seznamte se s [možnými riziky |security#bezpecna-prace-se-sloupci].


Aktualizace dat (UPDATE)
------------------------

Pro aktualizacizáznamů se používá SQL příkaz `UPDATE`.

```php
// Aktualizace jednoho záznamu
$values = [
	'name' => 'John Smith',
];
$result = $database->query('UPDATE users SET ? WHERE id = ?', $values, 1);
```

Počet ovlivněných řádků vrátí `$result->getRowCount()`.

Pro UPDATE můžeme využít operátorů `+=` a `-=`:

```php
$database->query('UPDATE users SET ? WHERE id = ?', [
	'login_count+=' => 1, // inkrementace login_count
], 1);
```

Příklad vložení, nebo úpravy záznamu, pokud již existuje. Použijeme techniku `ON DUPLICATE KEY UPDATE`:

```php
$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
```

Všimněte si, že Nette Database pozná, v jakém kontextu SQL příkazu parametr s polem vkládáme a podle toho z něj sestaví SQL kód. Takže z prvního pole sestavil `(id, name, year) VALUES (123, 'Jim', 1978)`, zatímco druhé převedl do podoby `name = 'Jim', year = 1978`. Podroběji se tomu věnujeme v části [Hinty pro sestavování SQL|#Hinty pro sestavování SQL].


Mazání dat (DELETE)
-------------------

Pro mazání záznamů se používá SQL příkaz `DELETE`. Příklad se získáním počtu smazaných řádků:

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


Hinty pro sestavování SQL
-------------------------

Hint je speciální zástupný symbol v SQL dotazu, který říká, jak se má hodnota parametru přepsat do SQL výrazu:

| Hint      | Popis                                           | Automaticky se použije
|-----------|-------------------------------------------------|-----------------------------
| `?name`   | použije pro vložení názvu tabulky nebo sloupce  | -
| `?values` | vygeneruje `(key, ...) VALUES (value, ...)`     | `INSERT ... ?`, `REPLACE ... ?`
| `?set`    | vygeneruje přiřazení `key = value, ...`         | `SET ?`, `KEY UPDATE ?`
| `?and`    | spojí podmínky v poli operátorem `AND`          | `WHERE ?`, `HAVING ?`
| `?or`     | spojí podmínky v poli operátorem `OR`           | -
| `?order`  | vygeneruje klauzuli `ORDER BY`                  | `ORDER BY ?`, `GROUP BY ?`

Pro dynamické vkládání názvů tabulek a sloupců do dotazu slouží zástupný symbol `?name`. Nette Database se postará o správné ošetření identifikátorů podle konvencí dané databáze (např. uzavření do zpětných uvozovek v MySQL).

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

**Upozornění:** symbol `?name` používejte pouze pro názvy tabulek a sloupců z validovaných vstupů, jinak se vystavujete [bezpečnostnímu riziku |security#Dynamické identifikátory].

Ostatní hinty obvykle není potřeba uvádět, neboť Nette používá při skládání SQL dotazu chytrou autodetekci (viz třetí sloupec tabulky). Ale můžete jej využít například v situaci, kdy chcete spojit podmínky pomocí `OR` namísto `AND`:

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


Speciální hodnoty
-----------------

Kromě běžných skalárních typů (string, int, bool) můžete jako parametry předávat i speciální hodnoty:

- soubory: `fopen('image.gif', 'r')` vloží binární obsah souboru
- datum a čas: objekty `DateTime` se převedou na databázový formát
- výčtové typy: instance `enum` se převedou na jejich hodnotu
- SQL literály: vytvořené pomocí `Connection::literal('NOW()')` se vloží přímo do dotazu

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

U databází, které nemají nativní podporu pro datový typ `datetime` (jako SQLite a Oracle), se `DateTime` převádí na hodnotu určenou v [konfiguraci databáze|configuration] položkou `formatDateTime` (výchozí hodnota je `U` - unix timestamp).


SQL literály
------------

V některých případech potřebujete jako hodnotu uvést přímo SQL kód, který se ale nemá chápat jako řetězec a escapovat. K tomuto slouží objekty třídy `Nette\Database\SqlLiteral`. Vytváří je metoda `Connection::literal()`.

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

Nebo alternativě:

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

SQL literály mohou obsahovat parametry:

```php
$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)
```

Díky čemuž můžeme vytvářet zajímavé kombinace:

```php
$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')
```


Získání dat
===========


Zkratky pro SELECT dotazy
-------------------------

Pro zjednodušení načítání dat nabízí `Connection` několik zkratek, které kombinují volání `query()` s následujícím `fetch*()`. Tyto metody přijímají stejné parametry jako `query()`, tedy SQL dotaz a volitelné parametry.
Plnohodnotný popis metod `fetch*()` najdete [níže|#fetch()].

| `fetch($sql, ...$params): ?Row`       | Provede dotaz a vrátí první řádek jako objekt `Row`
| `fetchAll($sql, ...$params): array`   | Provede dotaz a vrátí všechny řádky jako pole objektů `Row`
| `fetchPairs($sql, ...$params): array` | Provede dotaz a vrátí asocitivní pole, kde první sloupec představuje klíč a druhý hodnotu
| `fetchField($sql, ...$params): mixed` | Provede dotaz a vrátí hodnotu prvního políčka z prvního řádku
| `fetchList($sql, ...$params): ?array` | Provede dotaz a vrací první řádek jako indexované pole

Příklad:

```php
// fetchField() - vrátí hodnotu první buňky
$count = $database->query('SELECT COUNT(*) FROM articles')
	->fetchField();
```


`foreach` - iterace přes řádky
------------------------------

Po vykonání dotazu se vrací objekt [ResultSet|api:Nette\Database\ResultSet], který umožňuje procházet výsledky několika způsoby. Nejsnazší způsob, jak vykonat dotaz a získat řádky, je iterováním v cyklu `foreach`. Tento způsob je paměťově nejúspornější, neboť vrací data postupně a neukládá si je do paměti najednou.

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

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

.[note]
`ResultSet` lze iterovat pouze jednou. Pokud potřebujete iterovat opakovaně, musíte nejprve načíst data do pole, například pomocí metody `fetchAll()`.


fetch(): ?Row .[method]
-----------------------

Vrací řádek jako objekt `Row`. Pokud už neexistují další řádky, vrací `null`. Posune interní ukazatel na další řádek.

```php
$result = $database->query('SELECT * FROM users');
$row = $result->fetch(); // načte první řádek
if ($row) {
	echo $row->name;
}
```


fetchAll(): array .[method]
---------------------------

Vrací všechny zbývající řádky z `ResultSetu` jako pole objektů `Row`.

```php
$result = $database->query('SELECT * FROM users');
$rows = $result->fetchAll(); // načte všechny řádky
foreach ($rows as $row) {
	echo $row->name;
}
```


fetchPairs(string|int|null $key = null, string|int|null $value = null): array .[method]
---------------------------------------------------------------------------------------

Vrátí výsledky jako asociativní pole. První argument určuje název sloupce, který se použije jako klíč v poli, druhý argument určuje název sloupce, který se použije jako hodnota:

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

Pokud uvedeme pouze první parametr, bude hodnotou celý řádek, tedy objekt `Row`:

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

V případě duplicitních klíčů se použije hodnota z posledního řádku. Při použití `null` jako klíče bude pole indexováno numericky od nuly (pak ke kolizím nedochází):

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


fetchPairs(Closure $callback): array .[method]
----------------------------------------------

Alternativně můžete jako parametr uvést callback, který bude pro každý řádek vracet buď samotnou hodnotu, nebo dvojici klíč-hodnota.

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

// Callback může také vracet pole s dvojicí klíč & hodnota:
$names = $result->fetchPairs(fn($row) => [$row->name, $row->age]);
// ['John' => 46, 'Jane' => 21, ...]
```


fetchField(): mixed .[method]
-----------------------------

Vrací hodnotu prvního políčka z aktuálního řádku. Pokud už neexistují další řádky, vrací `null`. Posune interní ukazatel na další řádek.

```php
$result = $database->query('SELECT name FROM users');
$name = $result->fetchField(); // načte jméno z prvního řádku
```


fetchList(): ?array .[method]
-----------------------------

Vrací řádek jako indexované pole. Pokud už neexistují další řádky, vrací `null`. Posune interní ukazatel na další řádek.

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


getRowCount(): ?int .[method]
-----------------------------

Vrací počet ovlivněných řádků posledním dotazem `UPDATE` nebo `DELETE`. Pro `SELECT` je to počet vrácených řádků, ale ten nemusí být znám - v takovém případě metoda vrátí `null`.


getColumnCount(): ?int .[method]
--------------------------------

Vrací počet sloupců v `ResultSetu`.


Informace o dotazech
====================

Pro ladicí účely můžeme získat informace o posledním provedeném dotazu:

```php
echo $database->getLastQueryString();   // vypíše SQL dotaz

$result = $database->query('SELECT * FROM articles');
echo $result->getQueryString();    // vypíše SQL dotaz
echo $result->getTime();           // vypíše dobu vykonání v sekundách
```

Pro zobrazení výsledku jako HTML tabulky lze použít:

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

ResultSet nabízí informace o typech sloupců:

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

foreach ($types as $column => $type) {
	echo "$column je typu $type->type"; // např. 'id je typu int'
}
```


Logování dotazů
---------------

Můžeme implementovat vlastní logování dotazů. Událost `onQuery` je pole callbacků, které se zavolají po každém provedeném dotazu:

```php
$database->onQuery[] = function ($database, $result) use ($logger) {
	$logger->info('Query: ' . $result->getQueryString());
	$logger->info('Time: ' . $result->getTime());

	if ($result->getRowCount() > 1000) {
		$logger->warning('Large result set: ' . $result->getRowCount() . ' rows');
	}
};
```