Database Table

Podívejme se na základy práce s vrstvou „Table“ v Nette Frameworku.

Nette\Database\Table zjednodušuje a optimalizuje výběr dat z databáze. Hlavní myšlenkou je načítání dat pouze z jedné tabulky a tak, aby se tyto dotazy pokládaly jen jednou. Data jsou načtena do ActiveRow instancí. Data z jiných tabulek připojených vazbami jsou načteny samostatnými požadavky – o to se stará samotná vrstva Database\Table.

Pojďme si vyzkoušet jednoduchý příklad. Potřebujeme z databáze vybrat knihy a jejich autory. To je jednoduchý příklad vazby 1:N. Často používaná implementace vybírá data jedním SQL dotazem se spojením tabulek pomocí JOINu. Druhou možností je vybrat data odděleně, jedním dotazem knihy, a poté pro každou knihu vybrat jejího autora (např. pomocí foreach cyklu). To může být optimalizováno do dvou požadavků do databáze, jeden pro knihy a druhý pro autory – a přesně takto to dělá Nette\Database\Table.

Vrstva Database\Table poskytuje instanci Selection, která reprezentuje výběr dat z jedné databázové tabulky. Tato instance umožňuje filtrování požadovaných dat a vrací je jako instance ActiveRow.

Vytvoření Selection je jednoduché, stačí zavolat metodu table() na objektu databázového připojení. Jak se k databázi připojíme je popsáno v kapitole Nette\Database, pokud však používáme Nette\Database samostatně, je nutno tento objekt vytvořit.

$selection = $context->table('book'); // jméno tabulky je "book"

Selection implementuje interface Traversable: nad instancí můžeme jednoduše iterovat a vybrat tak všechny knihy. Řádky jsou vráceny jako instance ActiveRow a data z nich můžeme přímo číst.

$books = $context->table('book');
foreach ($books as $book) {
    echo $book->title;
    echo $book->author_id;
}

Výběr jednoho konkrétního řádku se provádí pomocí metody Selection::get(). Je to „filtrovací“ metoda, která vrací přímo instanci ActiveRow.

$book = $context->table('book')->get(2); // vrátí knihu s id 2
echo $book->title;
echo $book->author_id;

Vazby mezi tabulkami

Jak jsme zmínili na začátku kapitoly, Database\Table za nás spravuje vazby mezi tabulkami. S těmito vazbami můžeme pracovat dvěma způsoby:

  1. Filtrování záznamů, které vrátí Selection. Jak jsme zmínili na začátku kapitoly, Selection vrací data z jedné databázové tabulky. Může ale použít JOIN několika tabulek pro filtrování dat. Například pokud chceme vybrat jen ty autory, kteří napsali více než dvě knihy. Pro více podrobností se podívejte do kapitoly Database: Selection.
  2. Získání souvisejících dat pro vybrané ActiveRows. Vybírání dat z více tabulek najednou jsme zavrhli, bohužel vypsání pouze author_id nám nestačí. Potřebujeme získat celý řádek z tabulky author, ideálně také jako instanci ActiveRow. Získávání tohoto typu závislostí má na starosti ActiveRow. Pro více podrobností se podívejte do kapitoly Database: ActiveRow.

V níže uvedených příkladech budeme pracovat s databázovým schématem na obrázku. Jsou v něm vazby OneHasMany (1:N) a ManyHasMany (M:N). Vztah OneHasMany je dvojitý, kniha musí mít autora a může mít překladatele (translator_id může mít hodnotu NULL).

Struktura databáze pro uvedené příklady

V následujícím příkladu získáváme související data pro vybrané knihy. Property author v ActiveRow instanci book obsahuje další ActiveRow instanci reprezentující autora knihy. Získání instancí book_tag se provádí voláním metody related(), která vrací kolekci těchto instancí. V cyklu získáme jméno tagu z instance ActiveRow uložené v property tag u právě procházené vazby v instanci bookTag. Pro detailnější informace o získávání souvisejících dat se podívejte do kapitoly Database: ActiveRow.

$books = $context->table('book');

foreach ($books as $book) {
    echo 'title:      ' . $book->title;
    echo 'written by: ' . $book->author->name;

    echo 'tags: ';
    foreach ($book->related('book_tag') as $bookTag) {
        echo $bookTag->tag->name . ', ';
    }
}

Příjemně vás překvapí, jak efektivně databázová vrstva pracuje. Výše uvedený příklad provede konstantní počet požadavků, podívejte se na tyto čtyři dotazy:

SELECT * FROM `book`
SELECT * FROM `author` WHERE (`author`.`id` IN (11, 12))
SELECT * FROM `book_tag` WHERE (`book_tag`.`book_id` IN (1, 4, 2, 3))
SELECT * FROM `tag` WHERE (`tag`.`id` IN (21, 22, 23))

Pokud použijete cache (ve výchozím nastavení je zapnutá), nebudou z databáze načítány žádné nepotřebné sloupce. Po prvním dotazu se do cache uloží jména použitých sloupců a dále budou z databáze vybírány pouze ty sloupce, které skutečně použijete:

SELECT `id`, `title`, `author_id` FROM `book`
SELECT `id`, `name` FROM `author` WHERE (`author`.`id` IN (11, 12))
SELECT `book_id`, `tag_id` FROM `book_tag` WHERE (`book_tag`.`book_id` IN (1, 4, 2, 3))
SELECT `id`, `name` FROM `tag` WHERE (`tag`.`id` IN (21, 22, 23))

Zapisování dotazů

Database\Table umí chytře escapovat parametry a identifikátory. Pro správnou funkčnost je ale nutno dodržovat několik pravidel:

  • klíčová slova, názvy funkcí, procedur apod. psát velkými písmeny
  • názvy sloupečků a tabulek psát malými písmeny
  • hodnoty dosazovat přes parametry
//SPATNE!:
//->where("name like ?", "John"); //vygeneruje: `name` `like` ?
//spravne:
->where("name LIKE ?", "John");

//SPATNE!:
//->where("KEY = ?", $value); //KEY je klíčové slovo
//spravne:
->where("key = ?", $value); //vygeneruje: `key` = ?

//SPATNE!:
//->where("name = " . $name); //sql injection!
//spravne:
->where("name = ?", $name);

Špatné použití může vést k bezpečnostním dírám v aplikaci.

Ruční vytvoření Database\Context

Pokud jsme si vytvořili databázové spojení pomocí aplikační konfigurace, nemusíme se o nic starat. Vytvořila se nám totiž i služba typu Nette\Database\Context, kterou si můžeme předat pomocí DI.

Pokud ale používáme Nette\Database samostatně a chceme využívat vrstvu Table, musíme instanci Database\Context vytvořit ručně.

//$storage obsahuje implementaci Nette\Caching\IStorage, napr.:
//$storage = new Nette\Caching\FileStorage($tempDir);

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

$structure = new Nette\Database\Structure($connection, $storage);
$conventions = new Nette\Database\Conventions\DiscoveredConventions($structure);
$context = new Nette\Database\Context($connection, $structure, $conventions, $storage);