Query2

Požadavky

PHP >= 5. Otestováno na PHP 5.0.5, 5.2.11 a 5.3.1. Query2 je možné použít pouze s kódováními kompatibilními s ASCII, např. UTF-8, ISO-8859-X/latinX, CP-XXXX atp. Mezi kódování nekompatibilní s ASCII patří např. UTF-16/UCS-2 nebo UTF-32/UCS-4.

Připojení k databázi

PHP (plain)
// Vytvoříme instanci objektu a zároveň se připojíme k databázi
 
$q = new Query2("localhost", "root", "", "my_database");
 
 
 
// Připojit se k databázi můžeme ale nezávisle na instanciaci objektu
 
$q = new Query2(); // pouze vytvoříme instanci objektu
 
$q->connect("localhost", "root", "", "my_database"); // connect() má stejné parametry jako konstruktor

Základní dotazy

PHP (plain)
echo $q->query("SELECT id FROM users WHERE login = %s AND password = %s", 
 
	$_POST["login"], sha1($_POST["password"]))->fetchOne();
 
 
 
// SQL kód můžeme mixovat s parametry, následující dotaz dělá úplně to samé, co předchozí
 
echo $q->query("SELECT id FROM users WHERE login = %s", sha1($_POST["password"]), 
 
	"AND password = %s", $_POST["login"])->fetchOne();

Co přesně kód dělá? Metoda query() zparsuje vstupní argumenty a spustí dotaz. Předali jsme jí 3 parametry (v prvním případě). Parser hledá znaky procenta, ty značí, že následující znak/řetězec má speciální význam (říkáme jim modifikátory). %s znamená, že další parametr bude řetězec, který se má na místo %s substituovat, ale v escapované podobě. Nemusíme se proto starat o spouštění addslashes() nebo mysql_real_escape_string().

Metoda query() vrací tři možné věci:

• False - dotaz neuspěl

• Instanci objektu Query2Result v případě, kdy dotaz do databáze vrací nějaká data (hlavně SELECT, DESCRIBE atp.)

• To je náš případ, metodou fetchOne() objektu třídy Query2Result si vyžádáme první sloupec prvního výsledku

• Instanci objektu Query2 v ostatních případech (tedy $this)

• Typicky pro INSERT, UPDATE, DELETE atp.

Seznam modifikátorů

Modifikátory pracující s řetězci se často vyskytují ve dvojicích - modifikátor %a (malé písmeno) parametr escapuje, %A parametr neescapuje. I neescapovaný řetězec je ale vždy obklopen jednoduchými uvozovkami.

• %i (od slova "integer") - celé číslo

• %f ("float") - reálné číslo

• %s a %S ("string") - řetězec

• %t ("table") - jméno tabulky/sloupce/view/indexu atp., obklopuje hodnotu znakem `

• Je vhodný např. na nastavitelné řazení tabulky ala $q->query("SELECT ... ORDER BY %t", $radici_sloupec)...

• V tomto případě se používá jiný druh escapování - escapuje se znak `, " a ' se nechávají být.

• Příklad: table.column => `table`.`column`

• %x a %X - pouze vloží argument, volitelně escapuje - neobklopuje ' ani `. Vhodné na vkládání částí nebo celých dotazů v čistém SQL

• liší se od 1.0, dnešní %X odpovídá %q z 1.0. Obdoba %x v 1.0 neexistovala.

• %in a %IN - přijímá pole hodnot a vytvoří něco jako IN ('A', 'B', 'C') - operátor IN se v SQL neuvádí, např. "WHERE born %in", array(1987, 1990)

• V případě, že pole v argumentu je prázdné, vloží se místo IN() hodnota FALSE. IN() je chybný zápis, IN(NULL) zase nefunguje pro NOT IN

• NOT IN(...) se zapisuje očekávatelně: "WHERE born NOT %in", array(...)

• %a a %A ("aktualizovat") - z asociativního pole v argumentu vytvoří řetězec vhodný pro UPDATE

• %v a %V ("vložit") - to samé jako %a a %A, ale pro INSERT

• Pokud v argumentu není asociativní pole, ale pole asociativních polí, vytvoří se multi insert

• Pokud vkládáte velké množství řádků, pak je "multi insert" řádově rychlejší než X samostatných INSERTů (ale pozor na maximální velikost packetů!)

• %va a %VA - vytvoří řetězec vhodný pro INSERT ... ON DUPLICATE KEY, je to ale trochu složitější a rozeberu to zvlášť níže

Speciálním případem je PHP hodnota null, která se bez ohledu na modifikátor vždy převede na NULL v SQL.

PHP (plain)
// Vytvoří dotaz SELECT 'SQL \'injection', 'SQL 'injection'
 
$q->query("SELECT %s, %S", "SQL 'injection", "SQL 'injection");
 
 
 
// Chceme najít všechny loginy končící na písmeno s (pouze demonstrativní příklad)
 
// Vytvoří SELECT * FROM users WHERE login LIKE '%s'
 
$q->query("SELECT * FROM users WHERE %q", "login LIKE '%s'");
 
 
 
// Vytvoří dotaz UPDATE users SET name = 'Lil\' John', password = '47bce5c74f589f4867dbd57e9ca9f808' WHERE id = 25
 
$q->query("UPDATE users SET %a WHERE id = %i", array(
 
	"name" => "Lil' John",
 
	"password" => "47bce5c74f589f4867dbd57e9ca9f808"
 
), 25);
 
 
 
// Vytvoří dotaz SELECT * FROM users WHERE login IN ('Eva', 'Filip', 'Jakub') ORDER BY `lo'gi``n`
 
$q->query("SELECT * FROM users WHERE login %in ORDER BY %t", array("Eva", "Filip", "Jakub"), "lo'gi`n");
 
 
 
// Vytvoří dotaz INSERT INTO users (name, password) VALUES ('Lil\' John', '47bce5c74f589f4867dbd57e9ca9f808')
 
$q->query("INSERT INTO users %v", array(
 
	"name" => "Lil' John",
 
	"password" => "47bce5c74f589f4867dbd57e9ca9f808"
 
));

PHP (plain)
$arr = array(
 
	"name" => "Eva",
 
	"password" => "47bce5c74f589f4867dbd57e9ca9f808"
 
);
 
 
 
if($id)
 
	$q->query("UPDATE users SET %a WHERE id = %i", $arr, $id);
 
else
 
	$id = $q->query("INSERT INTO users %v", $arr)->lastInsertId();

PHP (plain)
// Vytvoří dotaz INSERT INTO users (name, password) VALUES ('Eva', '47bce5c74f589f4867dbd57e9ca9f808') 
 
//		 ON DUPLICATE KEY UPDATE name = VALUES(name), password = VALUES(password)
 
$q->query("INSERT INTO users %va", array(
 
	"name" => "Eva",
 
	"password" => "47bce5c74f589f4867dbd57e9ca9f808"
 
));

PHP (plain)
// Vytvoří dotaz INSERT INTO users (id, name, password) VALUES (5, 'Eva', '47bce5c74f589f4867dbd57e9ca9f808') 
 
//		 ON DUPLICATE KEY UPDATE password = VALUES(password), id = LAST_INSERT_ID(id)
 
$id = $q->query("INSERT INTO users %va", 
 
	"data" => array(
 
		"id" => $id
 
		"name" => "Eva",
 
		"password" => "47bce5c74f589f4867dbd57e9ca9f808"
 
	),
 
	"update" => array("password"),
 
	"auto_increment" => "id"
 
)->lastInsertId();

SQL (plain)
INSERT INTO users_login (id_user, last_login) VALUES (1, NOW())

PHP (plain)
$q->query("INSERT INTO users_login %v", array(
 
	"id_user" => 1,
 
	"last_login" => new Query2Statement("NOW()")
 
));

PHP (plain)
$q->query("INSERT INTO users_login (id_user, last_login) VALUES (%i, NOW())", $id_user);

Fetchování dat

Zatím jsem rozebíral pouze "kompozici" dotazů, teď se dostanu k "fetchování" dat z výsledku dotazu. Máme několik metod, většina je celkem standardní. Pojmenování je stejné jako u Zend_Db.

• fetchRow() - vrátí řádek v asociovaném poli, je možné volat opakovaně v cyklu, stejně jako mysql_fetch_row()

• fetchAll() - vrátí celý výsledek v dvourozměrném poli

• fetchOne() - vrátí hodnotu prvního sloupce prvního řádku výsledku. Volitelný parametr může obsahovat jméno sloupce, který se má vrátit místo prvního

• fetchCol() - vrací pole obsahující první sloupce všech řádků výsledku

• fetchPairs() - vrátí výsledek v asociativním poli složený z prvních dvou sloupců

• fetchAssoc($col, ...) - vrátí celý výsledek asociovaný podle zadaného sloupce. Pokud je zadáno více sloupců, asociuje se víceúrovňově. Pro každou hodnotu asociovaného sloupce se vytvoří pole, počítá se tím pádem s duplikátními hodnotami.

PHP (plain)
$result = $q->query("SELECT id, name, password FROM users");
 
 
 
var_export($result->fetchRow());
 
array(
 
	"id" => 1,
 
	"name" => "Alice",
 
	"password" => "A52E094A3580FC32"
 
)
 
 
 
var_export($result->fetchAll());
 
array(
 
	array(
 
		"id" => 1,
 
		"name" => "Alice",
 
		"password" => "A52E094A3580FC32"
 
	),
 
	array(
 
		"id" => 2,
 
		"name" => "Markéta",
 
		"password" => "E87E094A3580FC32"
 
	)
 
);
 
 
 
echo $result->fetchOne();
 
1
 
 
 
var_export($result->fetchCol("name"));
 
array("Alice", "Markéta");
 
 
 
var_export($result->fetchPairs());
 
array(
 
	1 => "Alice",
 
	2 => "Markéta"
 
);
 
 
 
$result = $q->query("SELECT id, country, city, name FROM users");
 
var_export($q->fetchAssoc("country", "city");
 
array(
 
	"CR" => array(
 
		"Praha" => array(
 
			0 => array(
 
				"id" => 1,
 
				"country" => "CR"
 
				"city" => "Praha",
 
				"name" => "Alice"
 
			),
 
			1 => array(
 
				"id" => 2,
 
				"country" => "CR"
 
				"city" => "Praha",
 
				"name" => "Markéta"
 
			)
 
		),
 
		"Brno" => array(
 
			0 => array(
 
				"id" => 3,
 
				"country" => "CR"
 
				"city" => "Brno",
 
				"name" => "Eva"
 
			)
 
		)
 
	),
 
	"SR" => array(
 
		"Bratislava" => array(
 
			0 => array(
 
				"id" => 4,
 
				"country" => "SR"
 
				"city" => "Bratislava",
 
				"name" => "Martina"
 
			)
 
		)
 
	)
 
);

PHP (plain)
$result = $q->query("SELECT * FROM users");
 
 
 
echo "Počet uživatelů je: ", count($result), "<br />\n";
 
 
 
foreach($result as $row)
 
	echo $row["name"], "<br />\n";
 
 
 
// Result se dá "rewindnout", jde tedy procházet výsledek opakovaně
 
foreach($result as $row)
 
	echo $row["surname"], "<br />\n";

Query2Builder

SQL je jazyk poměrně blízký lidskému jazyku - to má své výhody, ale také jednu nevýhodu - dotaz se kódem blbě modifikuje. Vytvořme si modelovou situaci. V administraci blogovacího systému máme seznam blogpostů, ten je možné řadit podle libovolného sloupce, stránkovat, filtrovat podle data publikace a autora. Možných variant dotazu je docela dost a kód, který tento dotaz generuje je zbytečně dlouhý a nepřehledný. Query2Builder se snaží tento problém usnadnit:

PHP (plain)
// Objekt Query2Builder získáváme z objektu Query2 metodou builder()
 
$builder = $q->builder();
 
 
 
$builder->select("blog.id")->select("blog.name, blog.id_autor")->from("blog"); // je možné více způsobů zápisu
 
 
 
if(isset($_COOKIE["filter_autor"]) // v Query2Builderu je možné úplně normálně používat modifikátory
 
	$builder->from("JOIN autor USING(id_autor)")->whereAnd("autor.name LIKE %s", $_COOKIE["filter_autor"]);
 
 
 
if(isset($_COOKIE["filter_datum"]))
 
	$builder->whereAnd("blog.datum = %s", $_COOKIE["filter_datum"]); // je možné používat modifikátory
 
 
 
if(isset($_COOKIE["orderby"]))
 
	$builder->orderBy($_COOKIE["orderby"]);
 
 
 
$builder->limit(($page - 1) * $perpage, $perpage); // stránkování
 
 
 
$blogposty = $q->query($builder)->fetchAll(); // do metody query() zadáme jako argument instanci Query2Builder
 
 
 
// použijeme trochu modifikovaný dotaz pro získání celkového počtu řádků (užitečné na zobrazení stránkování)
 
// BTW, v MySQL jde udělat i bez spouštění druhého dotazu
 
$celkem_pocet = $q->query($builder->clearSelect()->select("COUNT(*)")->clearLimit())->fetchOne();

PHP (plain)
$or = $q->builder()->whereOr("skladem = 1")->whereOr("ocekavane_naskladneni < NOW() + INTERVAL 1 MONTH");
 
$builder = $q->builder()->select("*")->from("knihy")->whereAnd("v_prodeji = 1")->whereAnd($or);
 
 
 
// composeQuery() pouze vytvoří SQL dotaz, ale nespouští jej
 
echo $q->composeQuery($builder);
 
 
 
// => SELECT * FROM knihy WHERE v_prodeji = 1 AND (skladem = 1 OR ocekavane_naskladneni < NOW() + INTERVAL 1 MONTH)

PHP (plain)
$where = $q->builder()->whereAnd("active = 1")->whereAnd("id_category = %i", $id_category);
 
$q->query("UPDATE book SET %a", $cols_to_update, $where);
 
// => UPDATE book SET availability = 1, ... WHERE active = 1 AND id_category = 456

Obsluha chyb

Obsluha chyb je velmi jednoduchá, při každé se vyhodí výjimka Query2Exception. Vyhozená výjimka má tyto atributy:

• code - číselný kód chyby:

• 100 - Can't connect to database server.

• 101 - Can't select database.

• 200 - Wrong argument list/order to query()

• Nastává, v případech: $q->query("INSERT INTO users %v"); (tedy zapomenutí parametru)

• 201 - Wrong modifier to query() function.

• 300 - Chyba při vykonávání dotazu

• error - textový popis chyby, v případě 300 se jedná o mysql_error()

• sql - u 300 dotaz, který chybu způsobil

Logování

Objektu lze předat callback, který bude volán po vykonání dotazu s údaji o délce provedení, úspěchu dotazu (callback se volá před vyhozením výjimky).

PHP (plain)
function logger($success, $query, $time)
 
{
 
	echo "Podařilo se dotaz vykonat: ($success ? "ANO" : "NE"), ", dotaz zabral $time milisekund. Obsah dotazu: $query";
 
}
 
 
 
$q->setLogCallback("logger");
 
 
 
$callback = $q->getLogCallback(); // v případe, když bychom ho potřebovali zpět

Pomocné metody

• composeQuery() - akceptuje stejné parametry jako query() a vrací vygenerovaný dotaz v čistém SQL

• pquery() - vygeneruje dotaz, vytiskne ho na výstup, ale dotaz také spustí. Vhodné na rychlé hledání chyb, protože jediné, co potřebujete pro výpis dotazu na výstup je přidání jediného písmenka (query() => pquery()), jinak totiž všechno funguje úplně stejně.

Statické metody

V Dibi najdete zdánlivě praktické statické metody dibi::connect(), dibi::query() a možná i další. V Query2 žádné takové nenajdete a to jednoduše proto, že jsou z principu velmi špatné. Proč jsou statické metody tak špatné zjistíte (např.), když podědíte třídu dibi a budete ji chtít použít v existujícím projektu místo dibi.

Unit testy

V repozitáři je k dispozici soubor s PHPUnit testy. Přestože je pokrytí kódu prakticky stoprocentní, je stále co zlepšovat. Může se hodit na věci, které z manuálu nejsou úplně jasné.