Database Core

Nette Database Core je základní vrstva pro přístup k databázi, tzv. database abstraction layer.

Instalace

Knihovnu stáhnete a nainstalujete pomocí nástroje Composer:

composer require nette/database

Připojení a konfigurace

Pro připojení k databázi stačí vytvořit instanci třídy Nette\Database\Connection:

$database = new Nette\Database\Connection($dsn, $user, $password);

Parametr $dsn (data source name) je stejný, jaký používá PDO, např. host=127.0.0.1;dbname=test. V případě selhání vyhodí výjimku Nette\Database\ConnectionException.

Nicméně šikovnější způsob nabízí aplikační konfigurace, kam stačí přidat sekci database a vytvoří se potřebné objekty a také databázový panel v Tracy baru.

database:
	dsn: 'mysql:host=127.0.0.1;dbname=test'
	user: root
	password: password

Poté objekt spojení získáme jako službu z DI kontejneru, např.:

class Model
{
	// pro práci s vrstvou Database Explorer si předáme Nette\Database\Explorer
	public function __construct(
		private Nette\Database\Connection $database,
	) {
	}
}

Více informací o konfiguraci databáze.

Dotazy

Databázové dotazy pokládáme metodou query(), která vrací ResultSet.

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

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

echo $result->getRowCount(); // vrací počet řádků výsledku, pokud je znám

Nad ResultSet je možné iterovat pouze jednou, pokud potřebujeme iterovat vícekrát, je nutno výsledek převést na pole metodou fetchAll().

Do dotazu lze velmi snadno přidávat i parametry, všimněte si otazníku:

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

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

$database->query('SELECT * FROM users WHERE id IN (?)', $ids); // $ids je pole

POZOR, nikdy dotazy neskládejte jako řetězce, vznikla by zranitelnost SQL injection

$db->query('SELECT * FROM users WHERE name = ' . $name); // ŠPATNĚ!!!

V případě selhání query() vyhodí buď Nette\Database\DriverException nebo některého z potomků:

ConstraintViolationException – porušení nějakého omezení pro tabulku
ForeignKeyConstraintViolationException – neplatný cizí klíč
NotNullConstraintViolationException – porušení podmínky NOT NULL
UniqueConstraintViolationException – koliduje unikátní index

Kromě query() jsou tu další užitečné funkce:

// vrátí asociativní pole id => name
$pairs = $database->fetchPairs('SELECT id, name FROM users');

// vrátí všechny záznamy jako pole
$rows = $database->fetchAll('SELECT * FROM users');

// vrátí jeden záznam
$row = $database->fetch('SELECT * FROM users WHERE id = ?', $id);

// vrátí přímo hodnotu buňky
$name = $database->fetchField('SELECT name FROM users WHERE id = ?', $id);

V případě selhání všechny tyto metody vyhodí Nette\Database\DriverException.

Insert, Update & Delete

Parameterem, který vkládáme do SQL dotazu, může být i pole (v takovém případě je navíc možné zástupný symbol ? vynechat), což se hodí třeba pro sestavení příkazu INSERT:

$database->query('INSERT INTO users ?', [ // tady můžeme otazník vynechat
	'name' => $name,
	'year' => $year,
]);
// INSERT INTO users (`name`, `year`) VALUES ('Jim', 1978)

$id = $database->getInsertId(); // vrátí auto-increment vloženého záznamu

$id = $database->getInsertId($sequence); // nebo hodnotu sekvence

Vícenásobný INSERT:

$database->query('INSERT INTO users', [
	[
		'name' => 'Jim',
		'year' => 1978,
	], [
		'name' => 'Jack',
		'year' => 1987,
	],
]);
// INSERT INTO users (`name`, `year`) VALUES ('Jim', 1978), ('Jack', 1987)

Jako parametry můžeme předávat i soubory, objekty DateTime nebo výčtové typy:

$database->query('INSERT INTO users', [
	'name' => $name,
	'created' => new DateTime, // nebo $database::literal('NOW()')
	'avatar' => fopen('image.gif', 'r'), // vloží soubor
	'status' => State::New, // enum State
]);

Úprava záznamů:

$result = $database->query('UPDATE users SET', [
	'name' => $name,
	'year' => $year,
], 'WHERE id = ?', $id);
// UPDATE users SET `name` = 'Jim', `year` = 1978 WHERE id = 123

echo $result->getRowCount(); // vrací počet ovlivněných řádků

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

$database->query('UPDATE users SET', [
	'age+=' => 1, // všimněte si +=
], 'WHERE id = ?', $id);
// UPDATE users SET `age` = `age` + 1

Mazání:

$result = $database->query('DELETE FROM users WHERE id = ?', $id);
echo $result->getRowCount(); // vrací počet ovlivněných řádků

Pokročilé dotazy

Vložení, nebo úprava záznamu, pokud již existuje:

$database->query('INSERT INTO users', [
	'id' => $id,
	'name' => $name,
	'year' => $year,
], 'ON DUPLICATE KEY UPDATE', [
	'name' => $name,
	'year' => $year,
]);
// 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.

Také řazení můžeme ovlivnit polem, v klíčích uvedeme sloupce a hodnotou bude boolean určující, zda řadit vzestupně:

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

Pokud by u neobvyklé konstrukce detekce nezafungovala, můžete formu sestavení určit zástupným symbolem ? doplněným o hint. Podporovány jsou tyto hinty:

?values	(key1, key2, …) VALUES (value1, value2, …)
?set	key1 = value1, key2 = value2, …
?and	key1 = value1 AND key2 = value2 …
?or	key1 = value1 OR key2 = value2 …
?order	key1 ASC, key2 DESC

V klauzuli WHERE se používá operátor ?and, takže podmínky se spojují operátorem AND:

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

Což můžeme snadno změnit na OR tím, že uvedeme zástupný symbol ?or:

$result = $database->query('SELECT * FROM users WHERE ?or', [
	'name' => $name,
	'year' => $year,
]);
// SELECT * FROM users WHERE `name` = 'Jim' OR `year` = 1978

V podmínkách můžeme používat operátory:

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

A také výčty:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => ['Jim', 'Jack'],
	'role NOT IN' => ['admin', 'owner'], // výčet + operátor NOT IN
]);
// SELECT * FROM users WHERE
//   `name` IN ('Jim', 'Jack') AND `role` NOT IN ('admin', 'owner')

Do podmínky také můžeme vložit kus vlastního SQL kódu pomocí tzv. SQL literálu:

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

Nebo alternativě:

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

SQL literál také může mít své parametry:

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

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

Proměnný název

Ještě existuje zástupný symbol ?name, který využijete v případě, že název tabulky nebo sloupce je proměnnou. (Pozor, nedovolte uživateli manipulovat s obsahem takové proměnné):

$table = 'blog.users';
$column = 'name';
$database->query('SELECT * FROM ?name WHERE ?name = ?', $table, $column, $name);
// SELECT * FROM `blog`.`users` WHERE `name` = 'Jim'

Transakce

Pro práci s transakcemi slouží trojice metod:

$database->beginTransaction(); // zahájení transakce

$database->commit(); // potvrzení

$database->rollback(); // vrácení zpět

Elegantní způsob nabízí metoda transaction(), které předáme callback, který se vykoná v transakci. Pokud během vykonávání dojde k vyhození výjimky, transakce se zahodí, pokud vše proběhne v pořádku, transakce se potvrdí.

$id = $database->transaction(function ($database) {
	$database->query('DELETE FROM ...');
	$database->query('INSERT INTO ...');
	// ...
	return $database->getInsertId();
});

Jak vidíte, metoda transaction() vrací návratovou hodnotu callbacku.

Volání transaction() může být i zanořeno, což zjednodušuje implementaci nezávislých repozitářů.

Reflexe

Nette Database poskytuje nástroje pro introspekci databázové struktury pomocí třídy Nette\Database\Reflection. Ta umožňuje získávat informace o tabulkách, sloupcích, indexech a cizích klíčích. Reflexi můžete využít ke generování schémat, vytváření flexibilních aplikací pracujících s databází nebo obecných databázových nástrojů.

Objekt reflexe získáme z instance připojení k databázi:

$reflection = $database->getReflection();

Práce s tabulkami

Pomocí reflexe můžeme procházet všechny tabulky v databázi:

// Výpis názvů všech tabulek
foreach ($reflection->tables as $tableName => $table) {
    echo $tableName . "\n";
}

// Ověření existence tabulky
if ($reflection->hasTable('users')) {
    echo "Tabulka users existuje";
}

// Získání konkrétní tabulky
$table = $reflection->getTable('users');

Informace o sloupcích

Pro každou tabulku můžeme získat detailní informace o jejích sloupcích:

// Procházení všech sloupců
foreach ($table->columns as $column) {
    echo "Sloupec: " . $column->name . "\n";
    echo "Typ: " . $column->nativeType . "\n";
    echo "Může být NULL: " . ($column->nullable ? 'Ano' : 'Ne') . "\n";
    echo "Výchozí hodnota: " . ($column->default ?? 'Není') . "\n";
    echo "Je primární klíč: " . ($column->primary ? 'Ano' : 'Ne') . "\n";
    echo "Je auto-increment: " . ($column->autoIncrement ? 'Ano' : 'Ne') . "\n";
}

// Získání konkrétního sloupce
$idColumn = $table->getColumn('id');

Indexy a primární klíče

Reflection poskytuje informace o indexech a primárních klíčích:

$vypisNazvySloupcu = fn(array $columns) => implode(', ', array_map(fn($col) => $col->name, $columns));

// Výpis všech indexů
foreach ($table->indexes as $index) {
    echo "Index: " . ($index->name ?? 'Nepojmenovaný') . "\n";
    echo "Sloupce: " . $vypisNazvySloupcu($index->columns) . "\n";
    echo "Je unikátní: " . ($index->unique ? 'Ano' : 'Ne') . "\n";
    echo "Je primární klíč: " . ($index->primary ? 'Ano' : 'Ne') . "\n";
}

// Získání primárního klíče
if ($table->primaryKey) {
    echo "Primární klíč: " . $vypisNazvySloupcu($table->primaryKey->columns) . "\n";
}

Cizí klíče

Můžeme také získat informace o cizích klíčích:

foreach ($table->foreignKeys as $fk) {
    echo "Cizí klíč: " . ($fk->name ?? 'Nepojmenovaný') . "\n";
    echo "Lokální sloupce: " . $vypisNazvySloupcu($fk->localColumns) . "\n";
    echo "Odkazuje na tabulku: {$fk->foreignTable->name}\n";
    echo "Odkazuje na sloupce: " . $vypisNazvySloupcu($fk->foreignColumns) . "\n";
}