Database Core

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

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
{
    private $database;

    // pro práci s vrstvou Database Explorer si předáme Nette\Database\Context
    public function __construct(Nette\Database\Connection $database)
    {
        $this->database = $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ů:

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 nebo objekty DateTime:

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

Ú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