tiny 'n' smart
database layer

Připojení k databázi

Každé spojení je reprezentováno objektem Dibi\Connection. To komunikuje s databází přes ovladač (třída implementující Dibi\Driver). Který ovladač použít zvolíme při vytváření objektu:

$options = [
    'driver'   => 'mysqli',
    'host'     => 'localhost',
    'username' => 'root',
    'password' => '***',
    'database' => 'table',
];

// v případě chyby vyhodí Dibi\Exception
$database = new Dibi\Connection($options);
$res = $database->query('SELECT * FROM users');

Alternativně můžete používat statický registr dibi, který udržuje v globálně dostupném úložišti objekt spojení a nad ním volá všechny funkce:

dibi::connect([
    'driver'   => 'mysqli',
    'host'     => 'localhost',
    'username' => 'root',
    'password' => '***',
    'database' => 'test',
    'charset'  => 'utf8',
]);

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

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ď Dibi\Exception 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í Dibi\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ý znak ? 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 objekty DateTime:

$database->query('INSERT INTO users', [
    'name' => $name,
    'created' => new DateTime, // nebo $database::literal('NOW()')
]);

Mazání:

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

Úprava záznamů:

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

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

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 znakem ? doplněným o hint. Podporovány jsou tyto hinty:

?values (key1, key2, …) VALUES (value1, value2, …)
?set key1 = value1, key2 = value2, …
?and key1 = value1 OR key2 = value2 …
?or key1 = value1 AND 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ý znak ?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' => $names, // ['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)

Nebo ještě zajímavější 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ý znak ?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, $value);
// 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

SQL příkazy

dibi::query('SELECT * FROM [table] WHERE [id] = %i', $id);

$arr = [
    'pole' => 'hodnota',
    'bit'  => true,
];
dibi::query('INSERT INTO [table]', $arr);

dibi::query('UPDATE `table` SET ', $arr, 'WHERE `id`=%i', $x);

Jak vidíte, SQL příkaz se zapisuje jako série parametrů a před vložením proměnné uvedeme modifikátor (např. %i). Pokud ho neuvedeme, zjistí se typ automaticky (samozřejmě nelze zjistit typy jako je datum apod).

Proměnná na naformátuje do výsledného SQL podle pravidel aktivní databáze. Tak třeba true bude v MS SQL jako –1, jinde jako ‚1‘. Stejně tak se zformátují řetězce, časové údaje, atd.

I SQL prochází zpracováním, aby vyhovovalo konvencím dané databáze. Identifikátory (jména tabulek a sloupců) uvozuji do hranatých závorek nebo zpětných uvozovek (je to jedno), dále řetězce značím jednoduchými či dvojitými uvozovkami, ale na výstup se dostane vždy to, co databáze žádá. Příklad

dibi::query("UPDATE `table` SET [text]='I''m fine'");

// MySQL: UPDATE `table` SET `text`='I\'m fine'
// ODBC:  UPDATE [table] SET [text]='I''m fine'

Ještě doplním, že uvozovka se uvnitř řetězce v embedded SQL zapisuje zdvojením. Lomítko má totiž v PHP řetězci zvláštní význam, muselo by se tedy použít dvojité, což leda komplikuje život a cílem dibi je opak.

Modifikátory

Kromě zástupného symbolu ? můžeme používat i šikovnější modifikátory:

%s string
%sN string, ale '' se přeloží jako null
%bin binární data
%b boolean
%i %u integer
%iN integer, ale 0 se přeloží jako null
%f float
%d datum (očekává string nebo integer)
%t datum & čas (také string či integer)
%n identifikátor (tedy název tabulky či sloupce)
%SQL SQL – řetězec ponechá beze změny
%lmt speciální – určuje limit
%ofs speciální – určuje offset
%ex speciální – expanduje pole

Pokud za modifikátorem následuje null, vloží se do databáze null. Pokud následuje pole, tak se modifikátor aplikuje na všechny jeho prvky. Ty se pak vloží do SQL oddělené čárkama.

Vždy používejte modifikátor %s před proměnnou s řetězcem. Dibi by pak nemohlo rozlišit, co je SQL příkaz a co řetězec. V tomto příkladu je funkce dibi::query volána s dvěma argumenty, první je řetězec představující SQL, druhý je řetězec představující řetězec. Modifikátor %s to odliší:

$text = "I'm fine";
dibi::query('UPDATE `table` SET `text`=%s', $text);
// MySQL: UPDATE `table` SET `text`='I\'m fine'
// ODBC:  UPDATE [table] SET [text]='I''m fine'

Operátor LIKE

Pro operátor LIKE jsou k dispozici tři speciální výrazy:

%like~ výraz začíná řetězcem
%~like výraz končí řetězcem
%~like~ výraz obsahuje řetězec

Příklad použítí – hledej jména začínající na určitý řetězec:

dibi::query("SELECT * FROM table WHERE name LIKE %like~", $query);

Formátování polí

Jak jsem už psal, modifikátor je možné aplikovat také na všechny prvky pole, které se pak oddělené čárkami vloží do SQL. Ovšem můžeme využít také dvou speciálních modifikátorů %a nebo %v.

%and   [key]=val AND [key2]="val2" AND ...
%or   [key]=val OR [key2]="val2" OR ...
%a assoc [key]=val, [key2]="val2", ...
%l %in list (val, "val2", ...)
%v values ([key], [key2], ...) VALUES (val, "val2", ...)
%m multivalues ([key], [key2], ...) VALUES (val, "val2", ...), (val, "val2", ...), ...
%by ordering [key] ASC, [key2] DESC ...
%n identifikátory [key], [key2] AS alias, ...
jiný val, val2, ...

Také si můžeme dovolit luxus žádný modifikátor před polem neuvést. V tom případě dibi použije tuto dedukci: jde-li o příkaz INSERT či REPLACE, zvol %v, jinak %a (platí pro asociativní pole).

Takže příklad:

$arr = [
    'a' => 'hello',
    'b'  => true,
];
dibi::query('INSERT INTO [table]', $arr);
// INSERT INTO `table` (`a`, `b`) VALUES ('hello', 1)

dibi::query('UPDATE `table` SET ', $arr);
// UPDATE `table` SET `a`='hello', `b`=1

Složitější výrazy v polích

Přímo v polích (v klíčích) je možné používat modifikátory:

dibi::query('UPDATE `table` SET ', [
    'number%SQL' => 'RAND()', // %SQL means SQL ;)
]);
// UPDATE `table` SET 'number' = RAND()

Také hodnoty mohou být složitější výrazy:

dibi::query('UPDATE `table` SET ', [
    'title' => array('SHA1(%s)', 'tajneheslo'),
]);
// UPDATE `table` SET 'title' = SHA1('tajneheslo')

Klíče je možné zcela vynechat:

dibi::query('SELECT * FROM `table` WHERE %and', [
    array('number > %i', 10),
    array('number < %i', 100),
]);
// SELECT * FROM `table` WHERE (number > 10) AND (number < 100)

Datum a čas

Parametrem může být také objekt DateTime.

dibi::query('UPDATE `table` SET ', [
    'time' => new DateTime,
]);
// UPDATE `table` SET ('2008-01-01 01:08:10')

Postupné skládání dotazu

Dibi disponuje také podporou pro postupné skládání SQL dotazu:

$query[] = 'SELECT * FROM [table]';
if ($where){
    array_push($query, 'WHERE [id]=%d', $where);
}

// a nyní předáme pole
$result = dibi::query($query);

Nebo lze použít expanzi pole přes speciální modifikátor %ex.

Podmíněné SQL příkazy

Podmíněné SQL příkazy jsou velmi silným nástrojem. Ovládají se pomocí tří klíčových slov %if, %else a %end. První z nich %if se musí, obdobně jako modifikátor, nacházet zcela na konci řetězce představujícího SQL:

$user = ???

dibi::query('
SELECT *
FROM [table]
%if', isset($user), 'WHERE [user]=%s', $user
);

Závěrečné %end je možno vynechat (nebo bude lepší na něm trvat?).

Podmínku lze rozšířit o část %else:

dibi::query('
SELECT *
FROM %if', $cond, '[one_table] %else [second_table]'
);

Podmínky můžete zanořovat do libovolné hloubky!

Prefixy & substituce

Názvy tabulek a sloupců mohou obsahovat proměnné části. Ty si nejprve nadefinujeme:

// create new substitution :blog:  ==>  wp_
dibi::addSubst('blog', 'wp_');

a poté použijeme v SQL. Všimněte si, že v SQL jsou uvozeny dvojtečkama:

dibi::test("UPDATE [:blog:items] SET [text]='Hello World'");
// UPDATE `wp_items` SET `text`='Hello World'

Testování query()

Abyste si mohli trošku s dibi hrát, je tu připravena funkce dibi::test(), které předáte parametry stejně jako dibi::query(), ovšem místo provedení SQL příkazu se tento barevně vypíše na obrazovku.

Možná by vás zajímalo, co celé to parsování a skládání dotazu stojí. Napsal jsem tyto funkce co nejoptimálněji a situace je taková, že zaberou jen zlomek času, který si ukousne samotné vykonání SQL příkazu. Můžete si ověřit.

Získávání výsledků

Nejjednodušší cesta vede přes klasickou iteraci

$result = dibi::query('SELECT * FROM table');

foreach ($result as $n => $row) {
    print_r($row);
}

unset($result);

Všimněte si, že zdroje se uvolní automaticky při zrušení objektu.

Je možné také nastavit offset a eventuálně i limit

$result = dibi::query('SELECT * FROM table');

$offset = 10;
$limit = 3;

foreach ($result->getIterator($offset, $limit)
          as $n => $row) {
    print_r($row);
}

Můžeme získat jen první políčko výsledku

$value = $result->fetchSingle();

Nebo celou tabulku do indexovaného pole:

$all = $result->fetchAll();

A pak tu máme k dispozici jednu mocnou funkci:

$assoc = $result->fetchAssoc('id');

Získá celou tabulku do asociativního a klíčem je políčko ‚id‘. Největší síla funkce se projeví tehdy, pokud provedete asociaci podle více políček. Takto lze nesmírně elegantně získávat data z dotazů, ve kterých spojujeme více tabulek. Příklad si nechám na příště.

Užitečná je také funkce pro získávání dat v podobě asociativního pole klíč ⇒ hodnota

$pairs = $result->fetchPairs('customerID', 'name');

Počet řádků zjistíme voláním:

$rows = count($result);

// přesun kurzoru:
$result->seek($row);

Datové typy

Stále to není všechno, jedeme dále. Při získávání záznamů můžeme specifikovat datový typ jednotlivých sloupců a dibi je bude automaticky převádět.

$result->setType('id', Dibi\Type::INTEGER);
$record = $res->fetch();

if (is_int($record['id']))
    echo 'yes, it is integer';

Nebo lze nechat všechny typy automaticky detekovat:

$result->detectTypes();
...

Případně nastavit v metodě connect() parameter 'resultDetectTypes' => true a typy se budou detekovat vždy.

Vrácený záznam

Dibi vrací záznamy jako objekty Dibi\Row. Ty mají tu vlastnost, že k hodnotám atributů lze přistupovat dvěma způsoby – buď jako k hodnotám objektů anebo jako k asociativnímu poli.

Výjimky, logování chyb a profiler

Jakákoliv chyba vzniklá během operace s databázovým serverem vyhodí výjimku Dibi\Exception nebo potomka Dibi\DriverException. Pokud dojde k chybě během vykonávání SQL příkazu, je i tento předán jako výjimce.

Užitečnou vlastností je logování provozu:

dibi::getProfiler()->setFile('log.sql');

Pokud používáte Firefox, Firebug a FirePHP, bude se provoz logovat i do Firebug konzole. Což se hodí při ladění. Tehdy se uplatní i velmi jednoduchý profiler:

echo dibi::$sql; // poslední SQL příklaz
echo dibi::$elapsedTime; // jeho doba trvání v sec
echo dibi::$numOfQueries; // celkem SQL příkazů
echo dibi::$totalTime; // celkový čas v sec

Dibi disponuje rozhraním pro připojení vlastního profileru nebo logovací knihovny.

Profiler a propojení na Firebug

Dibi má v sobě zabudovaný profiler, kterým můžete sledovat všechny vykonané SQL příkazy a měřit délku jejich trvání. Aktivace profileru:

dibi::connect([
        'driver'   => 'sqlite',
        'database' => 'sample.sdb',
        'profiler' => true, // spustí profiler
]);

Provoz lze buď logovat do souboru:

dibi::getProfiler()->setFile('log.sql');

…nebo do Firebug konzole. Jak na to?

  1. je vyžadován je Firefox verze 2 nebo 3
  2. stáhněte si rozšíření Firebug
  3. stáhněte si rozšíření FirePHP (minimálně ve verzi 0.2)
  4. zapněte si FirePHP v FirePHP menu a aktivujte Firebug Net panel

Logování do Firebugu se zapíná automaticky, je-li detekována jeho přítomnost.

Připojení do Nette

Nicméně šikovnější způsob nabízí bridge pro Nette. 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 Dibi\Context
    public function __construct(Dibi\Connection $database)
    {
        $this->database = $database;
    }
}

Více informací o konfiguraci databáze.