SQL pristop

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

Podrobnosti o povezavi in konfiguraciji podatkovne baze najdete v poglavju Povezava in konfiguracija.

Osnovno poizvedovanje

Za poizvedovanje v podatkovni bazi služi metoda query(). Ta vrne objekt ResultSet, ki predstavlja rezultat poizvedbe. V primeru napake metoda vrže izjemo. Rezultat poizvedbe lahko prehajamo z zanko foreach ali uporabimo katero od pomožnih funkcij.

$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:

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

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

$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:

$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.

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

// ❌ 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.

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.

$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:

$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.

$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:

$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.

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:

$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.

$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:

$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.

Posodabljanje podatkov (UPDATE)

Za posodabljanje zapisov se uporablja SQL ukaz UPDATE.

// 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 -=:

$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:

$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:

$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).

$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.

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:

$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

$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 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().

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

Ali alternativno:

$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:

$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:

$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($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:

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

`foreach` – iteracija čez vrstice

Po izvedbi poizvedbe se vrne objekt 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.

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

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

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

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

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

fetchAll(): array

Vrne vse preostale vrstice iz ResultSet kot polje objektov Row.

$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

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:

$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:

$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):

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

fetchPairs(Closure $callback): array

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

$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

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

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

fetchList(): ?array

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

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

getRowCount(): ?int

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

Vrne število stolpcev v ResultSet.

Informacije o poizvedbah

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

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:

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

ResultSet ponuja informacije o tipih stolpcev:

$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:

$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');
	}
};

Nette Documentation Preview