194.255.108.253: /* Úvod do SQL/PSM (PL/pgPSM) */

2007-12-09T09:47:58Z

Úvod do SQL/PSM (PL/pgPSM)

Nová stránka

==Úvod do SQL/PSM (PL/pgPSM)==
Začátkem devadesátých let minulého století začalo být jasné, že standard ANSI SQL postrádá prostředky pro tvorbu uložených procedur (zejména možnost deklarovat proměnné a dále s nimi pracovat, řízení toku - cykly a podmínky). Na tuto skutečnost reagovaly komerční firmy implementací vlastních proprietárních prostředí. Zřejmě nejpopulárnějšími v té době byly jazyky PL/SQL (Oracle, 1992), T-SQL (Sybase a Microsoft, 1995) a SPL (Informix, 1996). Od roku 1990 se v standardizační komisi ANSI SQl této problematice začala věnovat skupina vývojářů kolem Jima Meltona. Nejstarší zmínka o SQL/PSM je ze srpna roku 1994. (Prvním výstupem bylo PSM-96 (PSM - Persistent Stored Modules) postavené nad SQL92. Ve svém článku [http://www.dbmsmag.com/9701d06.html Budoucnost programování v SQL] z ledna 1997 zmiňuje Joe Celko SQL/PSM jako jazyk vycházející z Algolu rozšířený o prvky Ady (řešení výjimek). V roce 1998 (draft) standard byl již součástí SQL3 a označen současným názvem SQL/PSM (ANSI/ISO/IEC 9075-4:1999). Bohužel v té době většina významných firem již měla své vlastní prostředí (nekompatibilní se standardem), které, jak se ukázalo později, neopustily. Standard byl implementován pouze v těch RDBMS, kde do roku 1998 nebyla možnost tvorby SQL uložených procedur. Kromě DB2 (SQL PL, IBM, 2001) se většinou jedná o minoritní RDNMS: Mimer, Solid, 602SQL server. Do popředí se SQL/PSM opět dostává po roce 2005, kdy je implementován v Advantage Database Serveru (Sybase iAnywhere, 2005), v MySQL (2005) a PostgreSQL (2007). Jen zřídka je SQL/PSM implementován v plném rozsahu. Za zdařilou implementaci se pokládá SQL PL v DB2. Implementace SQL/PSM v PostgreSQL se nazývá (jak je v PostgreSQL zvykem) PL/pgPSM.

Stručně lze programovací jazyk definovaný v SQL/PSM charakterizovat jako jednoúčelový, zcela nový, moderní, jednoduchý procedurální programovací jazyk s integrovaným SQL a úzkou vazbou na prostředí SQL serverů. Jedná se o jazyk s bohatým repertoárem řídících konstrukcí a komfortním modelem zachycení a zpracování chyb. Datové typy, funkce přebírá z hostitelského SQL serveru. I/O rutiny pak úplně chybí (přesně v duchu architektury uložených procedur). Standard SQL/PSM se nevěnuje pouze popisu SQL procedur. Obsahuje i popis uspořádání procedur do modulů a popis tzv. externích procedur (implementace procedur v dalších prg. jazycích, např. C, Cobol, atd). Přestože se jedná o zcela nový programovací jazyk, na celé řadě konstrukcí je zřejmá inspirace jazyky PL/1, jazyky ADA, Modula a dalšími jazyky.

Následující ukázka je modifikací klasického příkladu Hello world v prostředí SQL/PSM. Parametrem funkce je jedinečný identifikátor uživatele. Uvnitř funkce se na základě tohoto identifikátoru získá skutečné jméno a příjmení uživatele z tabulky Users. Všimněte si deklarace proměnné, plnění proměnné hodnotou z tabulky (integrace SQL příkazu SELECT), použití SQL datového typu (varchar) a SQL oparátoru (operátor ||), komentáře.
<pre>
CREATE OR REPLACE FUNCTION hello(uid integer)
RETURNS varchar AS
$$
BEGIN
DECLARE real_name varchar;
-- Get real name
SET real_name = (SELECT name || ' ' || surname
FROM Users
WHERE Users.uid = hello.uid);
RETURN 'Hello, ' || real_name;
END;
$$ LANGUAGE plpgpsm;

SELECT hello(123);
</pre>
Vložení zdrojového kódu funkce mezi dvojici symbolů $$ je specifické pro PostgreSQL. PL/pgPSM je z pohledu PostgreSQL externí programovací jazyk, jako ostatní podporované PL jazyky (PL/pgSQL, PL/Perl, PL/Python), a tudíž PostgreSQL k tomuto jazyku přistupuje stejně a vynucuje si stejná pravidla zápisu. Implementace SQL/PSM má ještě další dvě podstatné odlišnosti od standardu. Za prvé, PostgreSQL umožňuje definovat pouze funkce (nikoliv procedury), což se projevuje určitými omezeními v řízení transakcí. Za druhé, PostgreSQL implicitně spouští funkce v režimu SECURITY CALLER, kdežto standard předpokládá režim SECURITY DEFINER. Mezi standardem a implementací SQL/PSM v PostgreSQL je ještě několik dalších rozdílů daných určitou volností standardu a implementačními závislostmi, nicméně nejedná se o nijak zvlášť markantní rozdíly. Asi je ještě předčasné počítat s plnou přenositelností kódu mezi RDBMS podporujícími SQL/PSM, nicméně v celé řadě případů sou jsou nutné úpravy minimální. Problémy mohou nastat u normou neošetřených konstrukcí (unbound selects, multirecordsets, atd).

Jazyk SQL/PSM je v RDBMS PostgreSQL (PL/pgPSM) běží na modifikovaném run-time PL/pgSQL. Tudíž výkonnostně i funkčně je na tom velice podobně jako jazyk PL/pgSQL. PL/pgSQL je v tuto chvíli vyzrálejší a časem prověřené prostředí vhodné pro jakkoliv kritické aplikace. PL/pgPSM naopak přináší shodu se standardem a novější, a bohatší programovací jazyk.

Rozlišujeme mezi tzv. externími uloženými procedurami a SQL uloženými procedurami. Rozdíl je v použitém jazyce a v přístupu. Pokud uložené procedury jsou realizovány v klasických prg. jazycích, a běží mimo prostředí SQL serveru (vlastní funkce, vlastní datové typy), jedná se o externí procedury. V případě, že se použije specializovaný jazyk a procedura běží v prostředí SQL serveru (sdílí datové typy a funkce), pak takovou proceduru označujeme jako SQL proceduru. Příklady SQL procedur jsou prostředí PL/SQL, T-SQL, nebo PSQL či PL/pgSQL, PL/pgPSM. Externí procedury jsou standardizované pouze pro jazyk Java (SQL/J). Existuje ovšem celá řada dalších implementací např. CLR pro SQLServer 2005 nebo PL/Perl, PL/Python pro PostgreSQL. Obě třídy procedur mají své pro a proti, doporučuje se ale upřednostnění SQL procedur vyjma těch případů, kdy jsou neefektivní (např. iterační výpočet integrálu) nebo chybí dostatečná funkcionalita (např. I/O operace). Velkou výhodou SQL procedur je integrace s prostředím. Zpravidla nedochází k zbytečným konverzím, a obvykle se používají předzpracované SQL příkazy (prepared statements). Kromě toho všechny statické SQL příkazy jsou verifikované (což ještě neznamená, že jsou 100% správné, nicméně to znamená, že jsou syntakticky správné).

== Praktické tipy návrhu uložených procedur v prostředí PL/pgPSM ==
V podstatě platí veškerá doporučení pro návrh jakéhokoliv software, tj. požadavky na čitelnost, srozumitelnost kódu:
* dbejte na dodržování jmenné konvence,
* používejte komentáře a názorné názvy proměnných,
* používejte jednotnou konvenci pro odsazování bloků,
* dodržujte rozumnou délku procedury (kolem 50 řádků),
* používejte "upovídaný" styl .. využívejte volitelná návěstí smyček.

K tomu ještě specifická doporučení platná pouze pro SQL uložené procedury (bez ohledu na konkrétní prostředí):
* pomocí prefixů a kvalifikovaných jmen atributů se vyhněte možným kolizím názvů sloupců a jmen proměnných,
* jednotným způsobem řešte zachycení a ošetření chyb,
* vyhněte se ISAM programování - co lze vyřešit pomocí SQL řešte pomocí SQL (pozor na cykly napříč celými tabulkami),
* omezte kurzory a dočasné tabulky na nezbytné minimum,
* v triggerech neopravujte data,
* procedury by měli realizovat určitou činnost, nikoliv jen zapouzdřit selecty.

Na následujících dvou příkladech si všimněte špatného (ISAM) přístupu a dobrého přístupu k návrhu uložených procedur. Obě procedury volají pro určitou podmnožinu zaměstnanců proceduru print_info.
<pre>
-- spatne (ISAM pristup)
CREATE OR REPLACE FUNCTION report_a()
RETURNS void AS
$$
main: FOR outer AS
SELECT id FROM Users
DO
BEGIN
DECLARE name, surname varchar;
DECLARE age int;
SET (name, surname, age) = (SELECT e.name, e.surname, e.age
FROM Employers e
WHERE e.id = outer.id);
IF age >= 20 AND age <= 29 THEN
CALL print_info(name, surname, age, 'y');
ELSE IF age >=30 AND age <= 50 THEN
CALL print_info(name, surname, age, 'o');
END IF;
END;
END FOR main;
$$ LANGUAGE plpgpsm;

-- dobre
CREATE OR REPLACE FUNCTION report_b()
RETURNS void AS
$$
main: FOR fc AS
-- veskere mozne podminky a transformace resim v prikazu SELECT
SELECT e.*, CASE WHEN e.age BETWEEN 20 AND 29 THEN 'y'
WHEN e.age BETWEEN 30 AND 50 THEN 'o' END AS tp
FROM Employers e
WHERE e.age BETWEEN 20 AND 50
DO
CALL print_info(fc.name, fc.surname, fc.age, fc.tp);
END FOR main;
$$ LANGUAGE plpgpsm;
</pre>

== Příručka jazyka PL/pgPSM ==
Každá funkce obsahuje jeden PL/pgPSM příkaz. Následující jedno příkazové funkce jsou korektní. Pozn. Pro uživatele PL/SQL jazyků to může být mírný šok. Na rozíl od PL/SQL, kde základním příkazem funkce nebo procedury je složený příkaz, je v SQL/PSM základním příkazem libovolný příkaz. Pokud funkce neobsahuje žádný SQL příkaz, používejte atribut IMMUTABLE. Kromě jiného i zajistíte efektivnější provádění funkce. Speciálním PL/pgPSM příkazem je [[Složený příkaz|složený příkaz]], který umožňuje zadat libovolně dlouhou syntakticky správnou posloupnost PL/pgPSM příkazů.
<pre>
CREATE OR REPLACE FUNCTION sum2params(IN a integer, IN b integer, OUT c integer) AS
$$
SET c = a + b;
$$ LANGUAGE plpgpsm IMMUTABLE;

CREATE OR REPLACE FUNCTION insert_val(a integer)
RETURNS void AS
$$
INSERT INTO Foo VALUES(a);
$$ LANGUAGE plpgpsm;

CREATE OR REPLACE FUNCTION get_sum(IN a integer, OUT b integer) AS
$$
SET b = (SELECT sum(f.a)
FROM Foo f
WHERE f.a > get_sum.a);
$$ LANGUAGE plpgpsm;

CREATE OR REPLACE FUNCTION dummy()
RETURNS void AS
$$
BEGIN
END;
$$ LANGUAGE plpgpsm IMMUTABLE;
</pre>

K dispozici jsou následující příkazy:
* [[SQL příkaz#SQL příkaz|SQL příkaz]],
* [[Složený příkaz|složený příkaz]] - mezi dvojicí BEGIN a END je libovolný počet PL/pgPSM příkazů oddělených středníkem,
* [[Příkazy CALL a RETURN|Volání a ukončení procedury]] - příkazy CALL a RETURN,
* [[Přiřazovací příkaz|přiřazovací příkaz]] - SET proměnná = hodnota,
* [[Podmíněné provádění příkazů]]:
** [[Podmíněné provádění příkazů#Příkaz IF|IF ELSEIF ELSE END IF]],
** [[Podmíněné provádění příkazů#Příkaz CASE|CASE WHEN THEN END CASE]].
* [[Příkazy cyklu|Příkazy cyklu]]
** [[Příkazy cyklu#Příkaz LOOP|LOOP co END LOOP]],
** [[Příkazy cyklu#Příkaz WHILE|WHILE podmínka DO co END WHILE]],
** [[Příkazy cyklu#Příkaz REPEAT|REPEAT co UNTIL podmínka END REPEAT]],
** [[Příkazy cyklu#Příkaz FOR|FOR select DO co END FOR]] (iterace napříč výsledkem dotazu).
* [[signalizace chyb]] - SIGNAL a RESIGNAL
* [[Příkaz PRINT|zobrazení výsledku na konzoli]] - PRINT (nestandardní konstrukce),
* [[Příkazy cyklu#Příkazy LEAVE a ITERATE|opuštění a nová iterace cyklu]] - příkazy LEAVE a ITERATE,
* [[Dynamické SQL|dynamické SQL]] - EXECUTE a EXECUTE IMMEDIATE,
* [[Příkaz GET DIAGNOSTICS]] - získání diagnostických údajů.

Ještě před samotným popisem PL/pgPSM příkazů si projděte následující jednoduché ukázky:
<pre>
-- case statement
CREATE OR REPLACE FUNCTION foo1(a integer)
RETURNS void AS $$
CASE a
WHEN 1, 3, 5, 7, 9 THEN
PRINT a, 'is odd number';
WHEN 2, 4, 6, 8, 10 THEN
PRINT a. 'is odd number';
ELSE
PRINT a, 'isn't from range 1..10';
END CASE;
$$ LANGUAGE plpgpsm;

-- while statement
CREATE OR REPLACE FUNCTION foo2(a integer)
RETURNS void AS
$$
BEGIN
DECLARE i integer DEFAULT 1;
WHILE i <= a
DO
PRINT i;
SET i = i + 1;
END WHILE;
END
$$ LANGUAGE plpgpsm;

-- for statement
CREATE OR REPLACE FUNCTION foo3(a integer)
RETURNS void AS
$$
FOR fc AS
SELECT i
FROM generate_series(1,a) AS g(i)
DO
PRINT fc.i;
END FOR;
$$ LANGUAGE plpgpsm;
</pre>
=== Použití jazyka PL/pgPSM ===
* [[SQL příkaz#Použití kurzorů|Použití kurzorů]]
* [[SQL příkaz#Funkce vracející tabulky|Funkce vracející tabulky]]
* [[Složený příkaz#Ošetření chyb|Ošetření chyb, signalizace chyby (výjimky) a zachycení signálu]]
* [[Použití dočasných tabulek v PL/pgPSM]]

== Parametry ovlivňující provádění funkcí ==
V PL/pgPSM jsou k dispozici dva přepínače: DUMP a RECOMPILE. Použití prvního způsobí výpis přeloženého kódu funkce do systémového logu. Druhý zajistí vynulování nakešovaných prováděcích plánů při každém startu funkce. Parametry se zapisují ještě před vlastní kód za klíčové slovo #OPTION.

=== #OPTION DUMP ===
Účelem tohoto přepínače je zobrazení přeloženého kódu do systémováho logu. Rozhodně se nejedná o typickou činnost - většina uživatelů PL/pgSQL o této možnosti v životě neslyšela. Dokáže být velice užitečný při hledání kolize názvů proměnných a SQL atributů. V případě této chyby nám systém hlásí (v lepším případě), že dotaz nelze přeložit nebo (v horším případě) je výsledek dotazu jiný než očekáváme.

Tento přepínač demonstruje skutečnost, že PL/pgSQL je v podstatě preprocesor jazyka SQL. Vlastní interpret je natolik minimalistický, že pro provádění veškerých operací (logických, aritmetických) se spoléhá na SQL. Díky tomu je zajištěna kompatibilita s SQL. Na druhou stranu PL/pgSQL se nehodí pro výpočetně náročné úlohy (s provedením každého SQL příkazu je spojená nezanedbatelná režie). K těmto účelům v PostgreSQL slouží PL/Perl, PL/Python nebo klasické C.
<pre>
-- tabulka foo obsahuje sloupec a
CREATE OR REPLACE FUNCTION kolize(a integer)
RETURNS SETOF Foo AS
$$
#option dump
SELECT *
FROM Foo
WHERE a = a;
$$ LANGUAGE plpgpsm;
</pre>
Jedná se o skutečně záludnout chybu (a poměrně častou). Přeložený kód výpadá následovně:
<pre>
Execution tree of successfully compiled PL/pgSQL function kolize(integer):

Function's data area:
entry 0: VAR $1 type int4 (typoid 23) atttypmod -1
entry 1: VAR found type bool (typoid 16) atttypmod -1

Function's statements:
0: *unnamed*:
BLOCK
2: SQL 'SELECT * FROM Foo WHERE $1 = $1 {$1=0}'
0: RETURN NULL
END *unnamed*

End of execution tree of function kolize(integer)
</pre>
Chyba je v podmínce WHERE. PL/pgSQL nechápe zápis a = a jako podmínku typu: SQL atribut je roven proměnné. Zápis znamená, že proměnná a je rovna proměnné a, což je splněno pro všechny řádky tabulky, a také výsledkem je celá tabulka Foo.

=== #OPTION RECOMPILE ===
Tento přepínač snižuje pravděpodobnost nekonzistence prováděcích plánů tím, že před každým startem procesury se procedura znovu přeloží a tím dojde k vyčištění cache prováděcích plánů. Zároveň se tím ale prodlouží doba spuštění procedury (dochází k překladu) a doba běhu (dochází k průbežnému vytváření prováděcích plánů). Tudíž tento přepínač používejte jen v nutných případech, nebo ještě lépe, nepoužívejte jej vůbec. Jak se vyhnout použití přepínače RECOMPILE je popsáno v sekci věnované [[Použití dočasných tabulek v PL/pgPSM|dočasným tabulkám]].Účelem tohoto přepínače je usnadnit portování uložených procedur z jiných RDBMS, kde jinak funguje ukládání prováděcích plánů.
<pre>
CREATE OR REPLACE FUNCTION testr()
RETURNS SETOF Foo AS
$$
#option dump recompile
BEGIN
DROP TABLE IF EXISTS FooG;
INSERT INTO FooG
SELECT *
FROM Foo;
RETURN TABLE(SELECT *
FROM FooG);
END;
$$ LANGUAGE plpgpsm;
</pre>
Tento přepínač je analogií přepínači WITH RECOMPILE v T-SQL Microsoft SQL Serveru.

== Portování uložených procedur z MySQL 5.x ==
Originální [[zdrojový kód procedury generující fraktály]] je přebrán z archivu MySQL dev. PostgreSQL řeší jinak:
* změnu aktuálního schématu,
* zápis zdrojového kódu procedury (nepoužívá mechanismus separátoru),
* pro spojení řetězců používá operátor || nikoliv funkci concat,
* neumožňuje volné SQL dotazy, jelikož nepodporuje procedury, ale pouze funkce.

Všimněte si minimálních rozdílů v [[zdrojový kód procedury generující fraktál pro PostgreSQL|upraveném kódu pro PostgreSQL]].

== Instalace PL/pgPSM ==
Vzhledem k nezralosti implementace PL/pgPSM ještě tento interpret není zařazen do distribuce. Zatím je ke stažení v jeslích postgresql projektů http://pgfoundry. Přesun do distribuce bude možný až po plné implementaci standardu a po důkladném otestování. Zatím zbývá dopsat podporu příkazů RESIGNAL a GET STACKET DIAGNOSTIC. Pokud se obejdete bez těchto příkazů, můžete používat PL/pgPSM bez obav. Interpret PL/pgPSM je modifikací interpretu PL/pgSQL, který je lety i tisíci projekty důkladně prověřen.

''Pro překlad PL/pgPSM je potřeba instalovat PostgreSQL ze zdrojových kódů.'' Potřebujete alespoň verzi 8.2. Poslední verzi PL/pgPSM naleznete na http://pgfoundry.org/frs/?group_id=1000238&release_id=767.
=== Postup ===
* Stáhněte si nejnovější zdrojové soubory PL/pgPSM z adresáře http://pgfoundry.org/frs/?group_id=1000238
* pokud máte PostgreSQL instalovaný ze zdrojových kódů, rozbalte archív do adresáře contrib
* proveďte příkazy make a make install
* jako superuser spusťe psql postgres < plpgpsm.sql
* od tohoto okamžiku můžete používat plpgpsm stejně jako ostatní jazyky
* test instalace make installcheck

Pokud nemáte PostgreSQL instalovaný ze zdrojových kódů, musíte mít alespoň develop knihovny PostgreSQL
* kdekoliv rozbalte archiv plpgpsm
* spusťte příkazy make USE_PGXS=1 a make USE_PGXS=1 install
* další postup je stejný jako v předchozím případě

Uvítáme jakoukoliv formu spolupráce. A to ať doplněním této dokumentace, její korekturou, překladem, rozšířením testovacích scénářů, doplněním funkcionality nebo samotným použitím.

Příručka SQL/PSM - Historie editací

194.255.108.253: /* Úvod do SQL/PSM (PL/pgPSM) */