SQL pristop
***********

.[perex]
Nette Database ponuja dve poti: lahko pišete SQL poizvedbe sami (SQL pristop) ali pa jih pustite samodejno generirati (glej [Explorer |explorer]). SQL pristop vam daje popoln nadzor nad poizvedbami in hkrati zagotavlja njihovo varno sestavljanje.

.[note]
Podrobnosti o povezavi in konfiguraciji podatkovne baze najdete v poglavju [Povezava in konfiguracija |guide#Povezava in konfiguracija].


Osnovno poizvedovanje
=====================

Za poizvedovanje v podatkovni bazi služi metoda `query()`. Ta vrne objekt [ResultSet |api:Nette\Database\ResultSet], ki predstavlja rezultat poizvedbe. V primeru napake metoda [vrže izjemo|exceptions]. Rezultat poizvedbe lahko prehajamo z zanko `foreach` ali uporabimo katero od [pomožnih funkcij |#Pridobivanje podatkov].

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

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

Za varno vstavljanje vrednosti v SQL poizvedbe uporabljamo parametrizirane poizvedbe. Nette Database jih naredi maksimalno preproste - zadostuje, da za SQL poizvedbo dodamo vejico in vrednost:

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

Pri več parametrih imate dve možnosti zapisa. Lahko SQL poizvedbo "prepletate" s parametri:

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

Ali pa najprej napišete celotno SQL poizvedbo in nato priključite vse parametre:

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


Zaščita pred SQL injection
==========================

Zakaj je pomembno uporabljati parametrizirane poizvedbe? Ker vas ščitijo pred napadom, imenovanim SQL injection, pri katerem bi napadalec lahko podtaknil lastne SQL ukaze in s tem pridobil ali poškodoval podatke v podatkovni bazi.

.[warning]
**Nikoli ne vstavljajte spremenljivk neposredno v SQL poizvedbo!** Vedno uporabljajte parametrizirane poizvedbe, ki vas ščitijo pred SQL injection.

```php
// ❌ NEVARNA KODA - ranljiva za SQL injection
$database->query("SELECT * FROM users WHERE name = '$name'");

// ✅ Varna parametrizirana poizvedba
$database->query('SELECT * FROM users WHERE name = ?', $name);
```

Seznanite se z [možnimi varnostnimi tveganji |security].


Tehnike poizvedovanja
=====================


Pogoji WHERE
------------

Pogoje WHERE lahko zapišete kot asociativno polje, kjer so ključi imena stolpcev in vrednosti podatki za primerjavo. Nette Database samodejno izbere najprimernejši SQL operator glede na tip vrednosti.

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

V ključu lahko tudi eksplicitno določite operator za primerjavo:

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

Nette samodejno obravnava posebne primere, kot so `null` vrednosti ali polja.

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

Za negativne pogoje uporabite operator `NOT`:

```php
$database->query('SELECT * FROM products WHERE', [
	'name NOT' => 'Laptop',         // uporabi operator <>
	'category_id NOT' => [1, 2, 3], // uporabi NOT IN
	'description NOT' => null,      // uporabi IS NOT NULL
	'id' => [],                     // izpusti se
]);
// WHERE `name` <> 'Laptop' AND `category_id` NOT IN (1, 2, 3) AND `description` IS NOT NULL
```

Za združevanje pogojev se uporablja operator `AND`. To lahko spremenite z uporabo [nadomestnega znaka ?or |#Namigi za sestavljanje SQL].


Pravila ORDER BY
----------------

Razvrščanje `ORDER BY` lahko zapišemo z uporabo polja. V ključih navedemo stolpce, vrednost pa bo boolean, ki določa, ali razvrščati naraščajoče:

```php
$database->query('SELECT id FROM author ORDER BY', [
	'id' => true, // naraščajoče
	'name' => false, // padajoče
]);
// SELECT id FROM author ORDER BY `id`, `name` DESC
```


Vstavljanje podatkov (INSERT)
-----------------------------

Za vstavljanje zapisov se uporablja SQL ukaz `INSERT`.

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

Metoda `getInsertId()` vrne ID zadnje vstavljene vrstice. Pri nekaterih podatkovnih bazah (npr. PostgreSQL) je treba kot parameter določiti ime sekvence, iz katere naj se ID generira z uporabo `$database->getInsertId($sequenceId)`.

Kot parametre lahko posredujemo tudi [#Posebne vrednosti] kot so datoteke, objekti DateTime ali naštevni tipi.

Vstavljanje več zapisov hkrati:

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

Večkratni INSERT je veliko hitrejši, ker se izvede ena sama poizvedba podatkovne baze namesto mnogih posameznih.

**Varnostno opozorilo:** Nikoli ne uporabljajte kot `$values` nevalidiranih podatkov. Seznanite se z [možnimi tveganji |security#Varno delo s stolpci].


Posodabljanje podatkov (UPDATE)
-------------------------------

Za posodabljanje zapisov se uporablja SQL ukaz `UPDATE`.

```php
// Posodobitev enega zapisa
$values = [
	'name' => 'John Smith',
];
$result = $database->query('UPDATE users SET ? WHERE id = ?', $values, 1);
```

Število prizadetih vrstic vrne `$result->getRowCount()`.

Za UPDATE lahko uporabimo operatorja `+=` in `-=`:

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

Primer vstavljanja ali urejanja zapisa, če že obstaja. Uporabimo tehniko `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
```

Opazite, da Nette Database prepozna, v kakšnem kontekstu SQL ukaza vstavljamo parameter s poljem in glede na to iz njega sestavi SQL kodo. Tako je iz prvega polja sestavil `(id, name, year) VALUES (123, 'Jim', 1978)`, medtem ko je drugega pretvoril v obliko `name = 'Jim', year = 1978`. Podrobneje se temu posvečamo v delu [#Namigi za sestavljanje SQL].


Brisanje podatkov (DELETE)
--------------------------

Za brisanje zapisov se uporablja SQL ukaz `DELETE`. Primer s pridobivanjem števila izbrisanih vrstic:

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


Namigi za sestavljanje SQL
--------------------------

Namig je poseben nadomestni znak v SQL poizvedbi, ki pove, kako naj se vrednost parametra prepiše v SQL izraz:

| Namig     | Opis                                            | Samodejno se uporabi
|-----------|-------------------------------------------------|-----------------------------
| `?name`   | uporabi za vstavljanje imena tabele ali stolpca  | -
| `?values` | generira `(key, ...) VALUES (value, ...)`     | `INSERT ... ?`, `REPLACE ... ?`
| `?set`    | generira prirejanje `key = value, ...`         | `SET ?`, `KEY UPDATE ?`
| `?and`    | združi pogoje v polju z operatorjem `AND`          | `WHERE ?`, `HAVING ?`
| `?or`     | združi pogoje v polju z operatorjem `OR`           | -
| `?order`  | generira klavzulo `ORDER BY`                  | `ORDER BY ?`, `GROUP BY ?`

Za dinamično vstavljanje imen tabel in stolpcev v poizvedbo služi nadomestni znak `?name`. Nette Database poskrbi za pravilno obdelavo identifikatorjev glede na konvencije dane podatkovne baze (npr. zapiranje v povratne narekovaje 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)
```

**Opozorilo:** simbol `?name` uporabljajte samo za imena tabel in stolpcev iz validiranih vnosov, sicer se izpostavljate [varnostnemu tveganju |security#Dinamični identifikatorji].

Drugih namigov običajno ni treba navajati, saj Nette pri sestavljanju SQL poizvedbe uporablja pametno samodejno zaznavanje (glej tretji stolpec tabele). Lahko pa ga uporabite na primer v situaciji, ko želite združiti pogoje z `OR` namesto `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'
```


Posebne vrednosti
-----------------

Poleg običajnih skalarnih tipov (string, int, bool) lahko kot parametre posredujete tudi posebne vrednosti:

- datoteke: `fopen('image.gif', 'r')` vstavi binarno vsebino datoteke
- datum in čas: objekti `DateTime` se pretvorijo v format podatkovne baze
- naštevni tipi: instance `enum` se pretvorijo v njihovo vrednost
- SQL literali: ustvarjeni z `Connection::literal('NOW()')` se vstavijo neposredno v poizvedbo

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

Pri podatkovnih bazah, ki nimajo nativne podpore za podatkovni tip `datetime` (kot SQLite in Oracle), se `DateTime` pretvori v vrednost, določeno v [konfiguraciji podatkovne baze|configuration] z vnosom `formatDateTime` (privzeta vrednost je `U` - unix timestamp).


SQL literali
------------

V nekaterih primerih morate kot vrednost navesti neposredno SQL kodo, ki pa se ne sme razumeti kot niz in ubežati. Za to služijo objekti razreda `Nette\Database\SqlLiteral`. Ustvarja jih 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())
```

Ali alternativno:

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

SQL literali lahko vsebujejo parametre:

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

Zaradi česar lahko ustvarjamo zanimive kombinacije:

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


Pridobivanje podatkov
=====================


Bližnjice za SELECT poizvedbe
-----------------------------

Za poenostavitev nalaganja podatkov `Connection` ponuja več bližnjic, ki kombinirajo klic `query()` z naslednjim `fetch*()`. Te metode sprejemajo enake parametre kot `query()`, torej SQL poizvedbo in neobvezne parametre. Popoln opis metod `fetch*()` najdete [spodaj |#fetch].

| `fetch($sql, ...$params): ?Row`       | Izvede poizvedbo in vrne prvo vrstico kot objekt `Row`
| `fetchAll($sql, ...$params): array`   | Izvede poizvedbo in vrne vse vrstice kot polje objektov `Row`
| `fetchPairs($sql, ...$params): array` | Izvede poizvedbo in vrne asociativno polje, kjer prvi stolpec predstavlja ključ in drugi vrednost
| `fetchField($sql, ...$params): mixed` | Izvede poizvedbo in vrne vrednost prvega polja iz prve vrstice
| `fetchList($sql, ...$params): ?array` | Izvede poizvedbo in vrne prvo vrstico kot indeksirano polje

Primer:

```php
// fetchField() - vrne vrednost prve celice
$count = $database->query('SELECT COUNT(*) FROM articles')
	->fetchField();
```


`foreach` - iteracija čez vrstice
---------------------------------

Po izvedbi poizvedbe se vrne objekt [ResultSet|api:Nette\Database\ResultSet], ki omogoča prehajanje rezultatov na več načinov. Najlažji način za izvedbo poizvedbe in pridobitev vrstic je iteracija v zanki `foreach`. Ta način je pomnilniško najbolj varčen, saj vrača podatke postopoma in jih ne shranjuje vseh hkrati v pomnilnik.

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

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

.[note]
`ResultSet` je mogoče iterirati samo enkrat. Če potrebujete iterirati večkrat, morate najprej naložiti podatke v polje, na primer z metodo `fetchAll()`.


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

Vrne vrstico kot objekt `Row`. Če ni več vrstic, vrne `null`. Premakne notranji kazalec na naslednjo vrstico.

```php
$result = $database->query('SELECT * FROM users');
$row = $result->fetch(); // naloži prvo vrstico
if ($row) {
	echo $row->name;
}
```


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

Vrne vse preostale vrstice iz `ResultSet` kot polje objektov `Row`.

```php
$result = $database->query('SELECT * FROM users');
$rows = $result->fetchAll(); // naloži vse vrstice
foreach ($rows as $row) {
	echo $row->name;
}
```


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

Vrne rezultate kot asociativno polje. Prvi argument določa ime stolpca, ki se uporabi kot ključ v polju, drugi argument določa ime stolpca, ki se uporabi kot vrednost:

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

Če navedemo samo prvi parameter, bo vrednost celotna vrstica, torej objekt `Row`:

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

V primeru podvojenih ključev se uporabi vrednost iz zadnje vrstice. Pri uporabi `null` kot ključa bo polje indeksirano numerično od nič (potem do kolizij ne pride):

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


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

Alternativno lahko kot parameter navedete povratni klic (callback), ki bo za vsako vrstico vrnil bodisi samo vrednost ali par ključ-vrednost.

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

// Callback lahko vrne tudi polje s parom ključ & vrednost:
$names = $result->fetchPairs(fn($row) => [$row->name, $row->age]);
// ['John' => 46, 'Jane' => 21, ...]
```


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

Vrne vrednost prvega polja iz trenutne vrstice. Če ni več vrstic, vrne `null`. Premakne notranji kazalec na naslednjo vrstico.

```php
$result = $database->query('SELECT name FROM users');
$name = $result->fetchField(); // naloži ime iz prve vrstice
```


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

Vrne vrstico kot indeksirano polje. Če ni več vrstic, vrne `null`. Premakne notranji kazalec na naslednjo vrstico.

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


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

Vrne število prizadetih vrstic zadnje poizvedbe `UPDATE` ali `DELETE`. Za `SELECT` je to število vrnjenih vrstic, vendar to morda ni znano - v takem primeru metoda vrne `null`.


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

Vrne število stolpcev v `ResultSet`.


Informacije o poizvedbah
========================

Za namene razhroščevanja lahko pridobimo informacije o zadnji izvedeni poizvedbi:

```php
echo $database->getLastQueryString();   // izpiše SQL poizvedbo

$result = $database->query('SELECT * FROM articles');
echo $result->getQueryString();    // izpiše SQL poizvedbo
echo $result->getTime();           // izpiše čas izvedbe v sekundah
```

Za prikaz rezultata kot HTML tabele lahko uporabimo:

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

ResultSet ponuja informacije o tipih stolpcev:

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

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


Dnevniško beleženje poizvedb
----------------------------

Lahko implementiramo lastno dnevniško beleženje poizvedb. Dogodek `onQuery` je polje povratnih klicev (callback), ki se pokličejo po vsaki izvedeni poizvedbi:

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

	if ($result->getRowCount() > 1000) {
		$logger->warning('Velik nabor rezultatov: ' . $result->getRowCount() . ' vrstic');
	}
};
```