SQL megközelítés

****************

.[perex]

A Nette Database két utat kínál: írhat SQL lekérdezéseket saját maga (SQL megközelítés), vagy hagyhatja, hogy automatikusan generálódjanak (lásd [Explorer |explorer]). Az SQL megközelítés teljes ellenőrzést biztosít a lekérdezések felett, miközben garantálja azok biztonságos összeállítását.

.[note]

Az adatbázis csatlakozásának és konfigurálásának részleteit a [Csatlakozás és konfiguráció |guide#Csatlakozás és konfiguráció] fejezetben találja.

Alapvető lekérdezés

===================

Az adatbázis lekérdezéséhez a `query()` metódus szolgál. Ez egy [ResultSet |api:Nette\Database\ResultSet] objektumot ad vissza, amely a lekérdezés eredményét reprezentálja. Hiba esetén a metódus [kivételt dob|exceptions]. A lekérdezés eredményét `foreach` ciklussal járhatjuk be, vagy használhatunk néhány [segédfüggvényt |#Adatlekérés].

```php

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

foreach ($result as $row) {

echo $row->id;

echo $row->name;

}

```

Az értékek biztonságos beillesztéséhez az SQL lekérdezésekbe paraméterezett lekérdezéseket használunk. A Nette Database ezt maximálisan egyszerűvé teszi - elegendő az SQL lekérdezés után egy vesszőt és az értéket hozzáadni:

```php

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

```

Több paraméter esetén kétféle írásmód lehetséges. Vagy "átszőheti" az SQL lekérdezést paraméterekkel:

```php

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

```

Vagy először megírhatja a teljes SQL lekérdezést, majd csatolhatja az összes paramétert:

```php

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

```

Védelem az SQL injection ellen

==============================

Miért fontos paraméterezett lekérdezéseket használni? Mert megvédenek az SQL injection nevű támadástól, amely során a támadó saját SQL parancsokat csempészhetne be, és ezzel adatokat szerezhetne vagy károsíthatna az adatbázisban.

.[warning]

**Soha ne illesszen be változókat közvetlenül az SQL lekérdezésbe!** Mindig használjon paraméterezett lekérdezéseket, amelyek megvédenek az SQL injection ellen.

```php

// ❌ VESZÉLYES KÓD - sebezhető az SQL injection-nel szemben

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

// ✅ Biztonságos paraméterezett lekérdezés

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

```

Ismerkedjen meg a [lehetséges biztonsági kockázatokkal |security].

Lekérdezési technikák

=====================

WHERE feltételek

----------------

A WHERE feltételeket asszociatív tömbként írhatja le, ahol a kulcsok az oszlopnevek, az értékek pedig az összehasonlítandó adatok. A Nette Database automatikusan kiválasztja a legmegfelelőbb SQL operátort az érték típusa alapján.

```php

$database->query('SELECT * FROM users WHERE', [

'name' => 'John',

'active' => true,

]);

// WHERE `name` = 'John' AND `active` = 1

```

A kulcsban explicit módon is megadhatja az összehasonlítási operátort:

```php

$database->query('SELECT * FROM users WHERE', [

'age >' => 25, // a > operátort használja

'name LIKE' => '%John%', // a LIKE operátort használja

'email NOT LIKE' => '%example.com%', // a NOT LIKE operátort használja

]);

// WHERE `age` > 25 AND `name` LIKE '%John%' AND `email` NOT LIKE '%example.com%'

```

A Nette automatikusan kezeli a speciális eseteket, mint a `null` értékek vagy tömbök.

```php

$database->query('SELECT * FROM products WHERE', [

'name' => 'Laptop', // az = operátort használja

'category_id' => [1, 2, 3], // az IN-t használja

'description' => null, // az IS NULL-t használja

]);

// WHERE `name` = 'Laptop' AND `category_id` IN (1, 2, 3) AND `description` IS NULL

```

Negatív feltételekhez használja a `NOT` operátort:

```php

$database->query('SELECT * FROM products WHERE', [

'name NOT' => 'Laptop', // a <> operátort használja

'category_id NOT' => [1, 2, 3], // a NOT IN-t használja

'description NOT' => null, // az IS NOT NULL-t használja

'id' => [], // kihagyja

]);

// WHERE `name` <> 'Laptop' AND `category_id` NOT IN (1, 2, 3) AND `description` IS NOT NULL

```

A feltételek összekapcsolásához az `AND` operátor használatos. Ezt a [?or helyettesítő karakterrel |#SQL összeállítási tippek] lehet megváltoztatni.

ORDER BY szabályok

------------------

Az `ORDER BY` rendezést tömb segítségével lehet leírni. A kulcsokban az oszlopokat adjuk meg, az érték pedig egy logikai érték lesz, amely meghatározza, hogy növekvő sorrendben kell-e rendezni:

```php

$database->query('SELECT id FROM author ORDER BY', [

'id' => true, // növekvő

'name' => false, // csökkenő

]);

// SELECT id FROM author ORDER BY `id`, `name` DESC

```

Adatbeszúrás (INSERT)

---------------------

Rekordok beszúrásához az `INSERT` SQL parancsot használjuk.

```php

$values = [

'name' => 'John Doe',

'email' => '[email protected]',

];

$database->query('INSERT INTO users ?', $values);

$userId = $database->getInsertId();

```

A `getInsertId()` metódus visszaadja az utoljára beszúrt sor ID-jét. Néhány adatbázisnál (pl. PostgreSQL) paraméterként meg kell adni annak a szekvenciának a nevét, amelyből az ID-t generálni kell a `$database->getInsertId($sequenceId)` segítségével.

Paraméterként átadhatunk [#Speciális értékek] is, mint például fájlokat, DateTime objektumokat vagy enum típusokat.

Több rekord beszúrása egyszerre:

```php

$database->query('INSERT INTO users ?', [

['name' => 'User 1', 'email' => '[email protected]'],

['name' => 'User 2', 'email' => '[email protected]'],

]);

```

A többszörös INSERT sokkal gyorsabb, mert egyetlen adatbázis-lekérdezés hajtódik végre, sok különálló helyett.

**Biztonsági figyelmeztetés:** Soha ne használjon validálatlan adatokat `$values`-ként. Ismerkedjen meg a [lehetséges kockázatokkal |security#Biztonságos munka az oszlopokkal].

Adatfrissítés (UPDATE)

----------------------

Rekordok frissítéséhez az `UPDATE` SQL parancsot használjuk.

```php

// Egy rekord frissítése

$values = [

'name' => 'John Smith',

];

$result = $database->query('UPDATE users SET ? WHERE id = ?', $values, 1);

```

Az érintett sorok számát a `$result->getRowCount()` adja vissza.

Az UPDATE-hez használhatjuk a `+=` és `-=` operátorokat:

```php

$database->query('UPDATE users SET ? WHERE id = ?', [

'login_count+=' => 1, // a login_count inkrementálása

], 1);

```

Példa egy rekord beszúrására vagy módosítására, ha már létezik. Az `ON DUPLICATE KEY UPDATE` technikát használjuk:

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

```

Figyelje meg, hogy a Nette Database felismeri, milyen kontextusban illesztjük be a tömböt tartalmazó paramétert az SQL parancsba, és ennek megfelelően állítja össze belőle az SQL kódot. Tehát az első tömbből `(id, name, year) VALUES (123, 'Jim', 1978)`-t állított össze, míg a másodikat `name = 'Jim', year = 1978` formára alakította át. Részletesebben ezzel az [#SQL összeállítási tippek] részben foglalkozunk.

Adattörlés (DELETE)

-------------------

Rekordok törléséhez a `DELETE` SQL parancsot használjuk. Példa a törölt sorok számának lekérésével:

```php

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

->getRowCount();

```

SQL összeállítási tippek

------------------------

A hint egy speciális helyettesítő karakter az SQL lekérdezésben, amely megmondja, hogyan kell a paraméter értékét SQL kifejezéssé átírni:

| Hint | Leírás | Automatikusan használva

|-----------|-------------------------------------------------|-----------------------------

| `?name` | tábla vagy oszlop nevének beillesztésére használja | -

| `?values` | `(key, ...) VALUES (value, ...)`-t generál | `INSERT ... ?`, `REPLACE ... ?`

| `?set` | `key = value, ...` hozzárendelést generál | `SET ?`, `KEY UPDATE ?`

| `?and` | a tömb feltételeit `AND` operátorral köti össze | `WHERE ?`, `HAVING ?`

| `?or` | a tömb feltételeit `OR` operátorral köti össze | -

| `?order` | `ORDER BY` záradékot generál | `ORDER BY ?`, `GROUP BY ?`

Táblák és oszlopok nevének dinamikus beillesztéséhez a lekérdezésbe a `?name` helyettesítő karakter szolgál. A Nette Database gondoskodik az azonosítók helyes kezeléséről az adott adatbázis konvenciói szerint (pl. backtickekbe zárás MySQL-ben).

```php

$table = 'users';

$column = 'name';

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

// SELECT `name` FROM `users` WHERE id = 1 (MySQL-ben)

```

**Figyelmeztetés:** a `?name` szimbólumot csak validált bemenetekből származó tábla- és oszlopnevekhez használja, különben [biztonsági kockázatnak |security#Dinamikus azonosítók] teszi ki magát.

A többi hintet általában nem szükséges megadni, mivel a Nette okos automatikus felismerést használ az SQL lekérdezés összeállításakor (lásd a táblázat harmadik oszlopát). De használhatja például olyan helyzetben, amikor a feltételeket `OR` helyett `AND`-del szeretné összekötni:

```php

$database->query('SELECT * FROM users WHERE ?or', [

'name' => 'John',

'email' => '[email protected]',

]);

// SELECT * FROM users WHERE `name` = 'John' OR `email` = '[email protected]'

```

Speciális értékek

-----------------

A szokásos skalár típusokon (string, int, bool) kívül speciális értékeket is átadhat paraméterként:

- fájlok: `fopen('image.gif', 'r')` beilleszti a fájl bináris tartalmát

- dátum és idő: a `DateTime` objektumok adatbázis formátumra konvertálódnak

- enum típusok: az `enum` példányok értékükre konvertálódnak

- SQL literálok: a `Connection::literal('NOW()')` segítségével létrehozottak közvetlenül beillesztődnek a lekérdezésbe

```php

$database->query('INSERT INTO articles ?', [

'title' => 'My Article',

'published_at' => new DateTime,

'content' => fopen('image.png', 'r'),

'state' => Status::Draft,

]);

```

Azoknál az adatbázisoknál, amelyek nem rendelkeznek natív támogatással a `datetime` adattípushoz (mint a SQLite és az Oracle), a `DateTime` az [adatbázis konfigurációjában|configuration] a `formatDateTime` tétellel meghatározott értékre konvertálódik (az alapértelmezett érték `U` - unix timestamp).

SQL literálok

-------------

Néhány esetben szükség van arra, hogy értékként közvetlenül SQL kódot adjunk meg, amelyet azonban nem szabad stringként értelmezni és escapelni. Erre szolgálnak a `Nette\Database\SqlLiteral` osztály objektumai. Ezeket a `Connection::literal()` metódus hozza létre.

```php

$result = $database->query('SELECT * FROM users WHERE', [

'name' => $name,

'year >' => $database::literal('YEAR()'),

]);

// SELECT * FROM users WHERE (`name` = 'Jim') AND (`year` > YEAR())

```

Vagy alternatívaként:

```php

$result = $database->query('SELECT * FROM users WHERE', [

'name' => $name,

$database::literal('year > YEAR()'),

]);

// SELECT * FROM users WHERE (`name` = 'Jim') AND (year > YEAR())

```

Az SQL literálok tartalmazhatnak paramétereket:

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

```

Ennek köszönhetően érdekes kombinációkat hozhatunk létre:

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

```

Adatlekérés

===========

Rövidítések SELECT lekérdezésekhez

----------------------------------

Az adatbetöltés egyszerűsítésére a `Connection` több rövidítést kínál, amelyek kombinálják a `query()` hívást a következő `fetch*()` hívásokkal. Ezek a metódusok ugyanazokat a paramétereket fogadják el, mint a `query()`, azaz az SQL lekérdezést és az opcionális paramétereket. A `fetch*()` metódusok teljes leírását [alább |#fetch] találja.

| `fetch($sql, ...$params): ?Row` | Végrehajtja a lekérdezést és visszaadja az első sort `Row` objektumként

| `fetchAll($sql, ...$params): array` | Végrehajtja a lekérdezést és visszaadja az összes sort `Row` objektumok tömbjeként

| `fetchPairs($sql, ...$params): array` | Végrehajtja a lekérdezést és visszaad egy asszociatív tömböt, ahol az első oszlop a kulcs, a második az érték

| `fetchField($sql, ...$params): mixed` | Végrehajtja a lekérdezést és visszaadja az első sor első mezőjének értékét

| `fetchList($sql, ...$params): ?array` | Végrehajtja a lekérdezést és visszaadja az első sort indexelt tömbként

Példa:

```php

// fetchField() - visszaadja az első cella értékét

$count = $database->query('SELECT COUNT(*) FROM articles')

->fetchField();

```

`foreach` - iteráció a sorokon

------------------------------

A lekérdezés végrehajtása után egy [ResultSet|api:Nette\Database\ResultSet] objektumot kapunk vissza, amely lehetővé teszi az eredmények több módon történő bejárását. A legegyszerűbb módja a lekérdezés végrehajtásának és a sorok lekérésének a `foreach` ciklussal történő iterálás. Ez a módszer a memóriatakarékosabb, mivel az adatokat fokozatosan adja vissza, és nem tárolja őket egyszerre a memóriában.

```php

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

foreach ($result as $row) {

echo $row->id;

echo $row->name;

// ...

}

```

.[note]

A `ResultSet`-et csak egyszer lehet iterálni. Ha ismételten kell iterálni, először be kell tölteni az adatokat egy tömbbe, például a `fetchAll()` metódussal.

fetch(): ?Row .[method]

-----------------------

Visszaad egy sort `Row` objektumként. Ha nincs több sor, `null`-t ad vissza. A belső mutatót a következő sorra mozgatja.

```php

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

$row = $result->fetch(); // betölti az első sort

if ($row) {

echo $row->name;

}

```

fetchAll(): array .[method]

---------------------------

Visszaadja a `ResultSet`-ből az összes fennmaradó sort `Row` objektumok tömbjeként.

```php

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

$rows = $result->fetchAll(); // betölti az összes sort

foreach ($rows as $row) {

echo $row->name;

}

```

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

---------------------------------------------------------------------------------------

Visszaadja az eredményeket asszociatív tömbként. Az első argumentum határozza meg az oszlop nevét, amely a tömb kulcsaként lesz használva, a második argumentum határozza meg az oszlop nevét, amely értékként lesz használva:

```php

$result = $database->query('SELECT id, name FROM users');

$names = $result->fetchPairs('id', 'name');

// [1 => 'John Doe', 2 => 'Jane Doe', ...]

```

Ha csak az első paramétert adjuk meg, az érték a teljes sor lesz, azaz egy `Row` objektum:

```php

$rows = $result->fetchPairs('id');

// [1 => Row(id: 1, name: 'John'), 2 => Row(id: 2, name: 'Jane'), ...]

```

Duplikált kulcsok esetén az utolsó sor értéke lesz használva. Ha `null`-t használunk kulcsként, a tömb numerikusan lesz indexelve nullától kezdve (ekkor nem történik ütközés):

```php

$names = $result->fetchPairs(null, 'name');

// [0 => 'John Doe', 1 => 'Jane Doe', ...]

```

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

----------------------------------------------

Alternatívaként megadhat egy callbacket paraméterként, amely minden sorhoz vagy magát az értéket, vagy egy kulcs-érték párt ad vissza.

```php

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

$items = $result->fetchPairs(fn($row) => "$row->id - $row->name");

// ['1 - John', '2 - Jane', ...]

// A callback visszaadhat egy tömböt is kulcs & érték párral:

$names = $result->fetchPairs(fn($row) => [$row->name, $row->age]);

// ['John' => 46, 'Jane' => 21, ...]

```

fetchField(): mixed .[method]

-----------------------------

Visszaadja az aktuális sor első mezőjének értékét. Ha nincs több sor, `null`-t ad vissza. A belső mutatót a következő sorra mozgatja.

```php

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

$name = $result->fetchField(); // betölti a nevet az első sorból

```

fetchList(): ?array .[method]

-----------------------------

Visszaad egy sort indexelt tömbként. Ha nincs több sor, `null`-t ad vissza. A belső mutatót a következő sorra mozgatja.

```php

$result = $database->query('SELECT name, email FROM users');

$row = $result->fetchList(); // ['John', '[email protected]']

```

getRowCount(): ?int .[method]

-----------------------------

Visszaadja az utolsó `UPDATE` vagy `DELETE` lekérdezés által érintett sorok számát. `SELECT` esetén ez a visszaadott sorok száma, de ez nem mindig ismert - ebben az esetben a metódus `null`-t ad vissza.

getColumnCount(): ?int .[method]

--------------------------------

Visszaadja az oszlopok számát a `ResultSet`-ben.

Információk a lekérdezésekről

=============================

Debuggolási célokra lekérhetjük az utoljára végrehajtott lekérdezés információit:

```php

echo $database->getLastQueryString(); // kiírja az SQL lekérdezést

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

echo $result->getQueryString(); // kiírja az SQL lekérdezést

echo $result->getTime(); // kiírja a végrehajtási időt másodpercben

```

Az eredmény HTML táblázatként való megjelenítéséhez használható:

```php

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

$result->dump();

```

A ResultSet információkat kínál az oszloptípusokról:

```php

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

$types = $result->getColumnTypes();

foreach ($types as $column => $type) {

echo "$column típusa $type->type"; // pl. 'id típusa int'

}

```

Lekérdezések naplózása

----------------------

Implementálhatunk saját lekérdezés-naplózást. Az `onQuery` esemény egy callback tömb, amely minden végrehajtott lekérdezés után meghívódik:

```php

$database->onQuery[] = function ($database, $result) use ($logger) {

$logger->info('Lekérdezés: ' . $result->getQueryString());

$logger->info('Idő: ' . $result->getTime());

if ($result->getRowCount() > 1000) {

$logger->warning('Nagy eredményhalmaz: ' . $result->getRowCount() . ' sor');

}

};

```