Přednášky

ní
schemata není možné vnořovat

* Schemata schemata
pomáhá oddělovat různé skupiny uživatelů
člení databázi na logické celky
umožňují uchovávat objekty třetích stran (aplikace apod.), aby nekolidovaly identifikátory
vytváření a rušení:
CREATE SCHEMA myschema;
CREATE SCHEMA myschema AUTHORIZATION username;
--majitelem nového schematu bude username

DROP SCHEMA myschema [CASCADE];
--CASCADE nařídí odstranění všech vnořených objektů
* Schemata schemata
implicitně jsou všechny objekty umístěny ve schematu public
přístup k vnořeným objektům přes tečkovou notaci
CREATE TABLE myschema.mytable (...);
tečková notace je otravná, proto se často nepíše - bez jejího použití prohledá stroj podle nějakého algoritmu seznam schemat (search path) a první vyhovující tabulka se vezme
SHOW search_path;
SET search_path TO myschema, public;
* Schemata schemata
uživatel může pracovat jen se schematem, které vlastní, nebo je nutné oprávnění USAGE (viz přednáška o oprávněních)
search path implicitně začíná uživatelským schematem
standardně, všichni mají oprávnění CREATE a USAGE na schema PUBLIC, to lze změnit
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
systémové schema pg_catalog – obsahuje systémové objekty
* Schemata způsoby použití
ponechat pouze schema public
emuluje situaci, kdy schemata vůbec nejsou
pro každého uživatele vytvořit jeho schema
jelikož se search path prohladává od začátku, uživatel bude pracovat (bez tečkové notace) s vlastním sch.
pro instalované aplikace vytvořit vlastní schema
nutno zajistit patřičná oprávnění
uživatelé buď mohou používat kvalifikovaná jména (tečkovou notaci) nebo si zařadit schema do své search path *
Databázové systémy 2 Kapitola 8
Pokročilejší rysy jazyka SQL,
transakce, indexy, oprávnění Transakce koncept, jak zajistit
paralelní přístup k datům
zotavení z chyb
operace se vykonávají v rámci nedělitelných programových jednotek – transakcí
transakce
nedělitelná, buď se provede celá nebo vůbec
převádí DB z jednoho konzistentního stavu do jiného konzistentního stavu
dokud není potvrzena, dílčí změny nejsou viditelné pro ostatní transakce
efekty úspěšné transakce jsou již trvalé
* Transakce transakce
posloupnost akcí (čtení, zápis, výpočet...), s nimiž se zachází jako s jedním celkem
typický úkol – převod peněz z účtu na účet
odečíst peníze ze zdrojového účtu
přičíst peníze na cílový účet
musí proběhnout buď obě dílčí operace nebo žádná
řízení transakcí
automatické
explicitně lze transakce řídit pomocí příkazů COMMIT a ROLLBACK
nutnost žurnálování * Transakce stav transakce
aktivní – počátek provádění transakce
částečně potvrzený – těsně před COMMIT
chybný – vyskytla se chyba
zrušený – po ROLLBACKu
potvrzený – po COMMITu * Transakce paralelní zpracování
není efektivní všechny transakce provádět za sebou, proto je snaha je vzájemně prokládat
vzniká závislost transakcí jedna na druhé
pozor, transakce nevidí změny, které právě provádí jiná transakce
některé typy operací nelze libovolně prokládat
např. čtení a zápis téhož objektu
nutnost dodržení serializovatelnosti
takové proložení operací, které vede ke stejnému výsledku, jako kdyby byly transakce prováděny za sebou
lze řešit algoritmicky, ale je to náročné
konfliktní operace: typu READ a WRITE * Transakce paralelní zpracování
řešení konfliktů
uzamykání objektů
jednoduchý uzamykací protokol – principy
transakce zamkne objekt, chce-li jej používat
transakce nezamyká již zamčený objekt
transakce může odemknout jen to, co sama zamkla
po ukončení transakce jsou všechny objekty odemknuty
dvoufázový protokol
v první fázi se postupně zamykají potřebné objekty, ale nic se neodemyká
v druhé fázi se objekty postupně odemykají, ale žádný se již nesmí zamknout
zajišťuje uspořádatelnost
velké nebezpečí deadlocku! * Transakce zotavení z chyb
transakce je i jednotkou zotavení z chyb
pokud dojde v průběhu transakce k chybě, vrátí se DB do stavu před transakcí
je nutné vést tzv. žurnál (log)
zapisuje se, co se provádí, aby to šlo případně vrátit
definují se kontrolní body (checkpoint)
dva způsoby
odložená realizace změn – vše se zapíše do DB až po úspěšném skončení všech paralelních transakcí, není nutný ROLLBACK
bezprostřední realizace změn – vše se zapisuje do DB rovnou, ale žurnál změny detailně eviduje, aby se dal provést ROLLBACK * Transakce v PostgreSQL příkazy BEGIN, COMMIT, ROLLBACK, SAVEPOINT
automatické i uživatelsky řízené zamykání
např.
BEGIN;
UPDATE accounts SET balance = balance + 100.00
WHERE name = ’Bob’;
UPDATE accounts SET balance = balance - 100.00
WHERE name = ’Alice’;
COMMIT; * Transakce v PostgreSQL řeší i problém současného vložení řádků do dvou tabulek při povinném členství
v tabulce lze u cizího klíče nastavit modifikátor DEFERRABLE, který zajistí, že kontrola referenční integrity může proběhnout až na konci transakce
v každé transakci nastává kontrola ref. int. podle toho, jak je nastaveno výchozí chování, lze pro aktuální transakci změnit příkazem
SET CONSTRAINTS name DEFERRED
fk integer references tbl2(pk) deferrable initially deferred,
fk2 integer references tbl3(pk) deferrable,
.... * initially immediate Indexy odezvu DB lze zrychlit pomocí přídavných datových struktur, které usnadní vyhledávání
pozor, každá přídavná struktura zvyšuje zároveň zátěž SŘBD, proto se musí užívat s rozmyslem
index
přísluší tabulce (pozn.: pohled nelze indexovat)
informace, kde leží která data
využitelné pro SELECT, UPDATE, DELETE, JOIN
je to podobné, jako rejstřík knihy
najdeme si v rejstříku, kde leží požadovaná informace, a nalistujeme příslušnou stránku * Indexy něco o fyzickém uspořádání souborů na disku
hromada
neuspořádaný soubor záznamů proměnné délky
nalezení záznamu má složitost O(N)
sekvenční soubor
neuspořádaný resp. uspořádaný soubor záznamů stejné délky
nalezení záznamu má složitost O(N) resp. O(log N)
indexsekvenční soubor
sekvenční soubor setříděný dle PK plus přídavný víceúrovňový index
index neukazuje na jednotlivé záznamy, ale na krátké setříděné bloky více záznamů
* Indexy něco o fyzickém uspořádání souborů na disku
indexovaný soubor
sekvenční soubor (nemusí být setříděný ani souvislý) plus přídavný jednoúrovňový index
index ukazuje na jednotlivé záznamy
víceúrovňový index
indexace indexu
řekněme, že indexujeme podle sloupce (celá čísla), pak první úroveň indexu ukazuje na jednotlivá čísla, druhá indexuje první číslo z každé pětice indexů první úrovně (třeba), atd... * Indexy vyhledávání v indexu
lze zjevně použít binární vyhledávací strom
každý uzel X má dva potomky, levý potomek je vždy menší než uzel X, pravý potomek je vždy větší než X
složitost prohledávání O(log2N) – to může být pro velká množství záznamů stále ještě příliš mnoho (uvažme opakované přístupy na disk...)
B-stromy
každý uzel má m potomků
snížení hloubky stromu => omezení počtu přístupů na disk
obvyklý počet úrovní stromu 2-3 pro stovky záznamů
* Indexy příklad
chceme vyhledat jeden záznam podle jeho PK
pokud o uspořádání záznamů v tabulce nic nevíme, musíme projít celou tabulku
pokud máme index, lze hledání výrazně urychlit
vytvoříme index nad sloupcem, podle kterého často vyhledáváme
lze tvořit index pro více sloupců naráz
CREATE TABLE test ( major integer, minor integer, content varchar );
CREATE INDEX test_mm_index ON test (major, minor);
SELECT content FROM test WHERE major = 5 AND minor = 10;
DROP INDEX test_mm_index;
* Indexy PostgreSQL
umí tvořit indexy různými technikami
B-strom je implicitní
umí analyzovat, zda je index užíván či nikoli
umí indexovat skalární funkce dat
lze tak vynutit dodržení některých IO, např. to, že se ve sloupci nesmí opakovat stejný řetězec s malými či velkými písmeny
CREATE UNIQUE INDEX test_lower_col1_idx ON test (lower(col1));
umí vytvářet index jen na podmnožinu záznamů
CREATE INDEX orders_unbilled_idx ON orders (order_nr)
WHERE billed is not true; * Oprávnění databázové stroje umožňují dobře definovat přístupová práva k jednotlivým databázovým objektům
spolu s pohledy efektivní nástroj k zajištění bezpečnosti DB
pokud uživatel vytvoří objekt, stane se jeho vlastníkem
standardně smí s objektem manipulovat pouze jeho vlastník
aby mohli objekt užívat i ostatní uživatelé, musejí jim být udělena patřičná oprávnění * Oprávnění několik typů oprávnění pro různé akce nad objekty (liší se podle typu objektu)
SELECT – právo číst tabulku
INSERT – právo vložit záznam
UPDATE, DELETE – právo upravovat data a mazat, obvykle potřebuje též práva pro SELECT
TRUNCATE – právo naráz mazat obsah celé tabulky
REFERENCES – právo vytvořit cizí klíč, nutno mít přiděleno na odkazující i odkazované tabulce
TRIGGER – právo vytvořit trigger nad tabulkou * Oprávnění několik typů oprávnění pro různé akce nad objekty (liší se podle typu objektu)
CREATE – právo vytvářet objekty
CONNECT – právo připojovat se k dané DB
TEMPORARY – právo vytvářet dočasné tabulky
EXECUTE – právo spouštět konkrétní funkci a s ní svázané operátory (jediné právo na funkce)
USAGE
pro jazyky právo používat konkr. procedurální jazyk
pro schemata právou vidět objekty ve schematu * Oprávnění tato oprávnění lze delegovat uživatelům, avšak odstranění a modifikaci objektu smí provádět vždy jen vlastník
specifická práva vlastníka nelze delegovat, některá lze omezit, tj. vlastník si může například nastavit tabulku jako read-only
pomocí příkazu ALTER TABLE lze vlastníka změnit
admin má přístup ke všemu
je možné udělit právo udělovat práva
pokud je toto právo uživateli odebráno, všechna práva jím udělená ostatním uživatelům jsou rovněž odebrána! * Oprávnění udělení privilegia
příkaz GRANT
GRANT UPDATE ON accounts TO joe;
GRANT UPDATE ON accounts TO bob WITH GRANT OPTION;
na místo konkr. privilegia můžeme zapsat ALL a uživateli jsou předána všechna práva
pro udělení práv všem uživatelům použijeme speciálního „uživatele“ PUBLIC
odebrání privilegia
příkaz REVOKE
REVOKE ALL ON accounts FROM PUBLIC; * Oprávnění udělení privilegia
privilegia se mohou kumulovat, tj. příkaz grant pouze přidá další právo k již nastaveným
pouze práva udělená WITH GRANT OPTION mohou být uživatelem delegována dalším uživatelům
v právech lze jít až na úroveň sloupců v tabulkách, tj. lze udělit právo číst třeba jen jeden sloupec
GRANT SELECT jméno, příjmení ON osoba TO jirka;
*
Databázové systémy 2 Kapitola 9
Pokročilejší rysy jazyka SQL
Procedurální rozšíření jazyka SQL Uživatelské role PostgreSQL nabízí koncept uživatelských rolí
role může být
jediný uživatel (speciální případ)
skupina uživatelů
rolím lze udělovat práva
role mohou vlastnit objekty (tabulky apod.)
vytvoření, modifikace, zrušení role
CREATE ROLE name;
ALTER ROLE name RENAME TO newname;
DROP ROLE newname; * Uživatelské role role může být definována s různými parametry, které specifikují práva role
běžný uživatel
CREATE ROLE name LOGIN;
CREATE USER name; -- totéž
admin
musím být admin, abych mohl tuto roli vytvořit
CREATE ROLE name SUPERUSER;
možnost vytváření databáze
CREATE ROLE name CREATEDB;
možnost vytváření dalších rolí
CREATE ROLE name CREATEROLE;
práva role lze změnit pomocí ALTER ROLE
* Uživatelské role uživatelé mohou být přiřazování do rolí, čímž jim mohou být hromadně udílena práva
funguje to tak, že se uživateli přiřadí role příkazem GRANT
vytvoříme „skupinovou“ roli
CREATE ROLE skupina CREATEDB;
přidáme člena
GRANT skupina TO jirka;
člen může být pochopitelně opět skupina
* Uživatelské role aby člen skupiny měl automaticky i práva skupiny, musí být definován s atributem INHERIT
potom dědí práva každé skupiny, jejímž je členem
poněkud opačně, než by člověk čekal
dědičnost je definována zespoda, ne shora
například
CREATE ROLE joe LOGIN INHERIT;
CREATE ROLE admin NOINHERIT;
CREATE ROLE wheel NOINHERIT;
GRANT admin TO joe;
GRANT wheel TO admin;
joe dědí práva přímo nadřazených skupin, tj. skupiny admin, ale nedědí práva skupiny wheel, protože admin také nedědí práva skupiny wheel * Uživatelské role aby člen skupiny měl mohl užívat práva skupiny i když nemá INHERIT
po přihlášení do databáze může uživatel použít příkaz SET ROLE
člen skupiny se může tímto příkazem „přepnout“ na práva kterékoli skupiny, jíž je členem
ztrácí tím ovšem práva, která má sám a skupina je nemá – operace není aditivní
nastavení přesně práv role admin
SET ROLE admin;
nastavení původních práv
RESET ROLE; * Procedurální rozšíření - motivace SQL je neprocedurální jazyk
říká CO chceme udělat, ne JAK
postrádá možnosti sofistikovanějšího řešení některých problémů
složitější integritní omezení
dotazy, které přirozeně vedou na cykly, podmínky a podobné programátorské konstrukce
mnohdy je obtížné přemýšlet „v množinách“, řešení nás často napadá snadněji s využitím klasických paradigmat sekvenčního funkcionálního programování
nevýhoda je také v tom, že pro čisté SQL (na serveru) a procedurální zpracování v klientovi se zvyšuje zátěž sítě
* Procedurální rozšíření - motivace procedurální rozšíření SQL
doplnění SQL o možnost vestavěných funkcí, procedur, triggerů apod.
funkce lze volat například při událostech jako je vkládání dat, manipulace s daty a podobně
budeme používat volně dostupný SŘBD PostreSQL, který disponuje procedurálním jazykem PL/pgSQL – obdoba PL/SQL firmy ORACLE
http://www.postgresql.org/docs/8.4/static/index.html * Typy funkcí SQL funkce
procedurální funkce (psané v PL/pgSQL nebo jiném jazyce)
vnitřní (interní funkce)
funkce v jazyce C
vstupní parametry
všechny základní i kompozitní datové typy
návratová hodnota
jedna nebo více hodnot (set, množina) základního nebo kompozitního datového typu
void * SQL funkce zapouzdřuje běžné SQL dotazy
vrací výsledek posledního SQL příkazu
mohou vracet pouze první řádek nebo celou výslednou tabulku
nemusí také vracet nic, pokud je tak deklarováno
RETURNS SETOF integer
RETURNS TABLE(columns)
RETURNS void
může obsahovat SELECT bloky, INSERT, UPDATE, DELETE, ...
pokud není poslední příkaz SELECT a funkce má něco vracet, musí se použít RETURNING * SQL funkce nemůže obsahovat řízení transakcí
přístup k parametrům
číslování, např. první parametr je $1
pokud jsou parametry strukturované, použijeme tečkovou notaci, např. $3.name
parametry mohou být použity pouze jako datové hodnoty, ne jako identifikátory
lze používat výstupní parametry:
CREATE FUNCTION soucet(integer, integer, OUT sum integer) AS $$
SELECT $1+$2;
$$ LANGUAGE SQL; * SQL funkce deklarace
CREATE FUNCTION jméno(parlist) RETURNS datatype AS
$$
příkazy...
$$ LANGUAGE SQL;
tělo funkce musí být deklarováno jako řetězec, tj. uzavřeme jej například do $$
volání: SELECT fname();
volání funkce lze použít i v klauzuli FROM
odstranění: DROP FUNCTION jméno(parlist);
* SQL funkce příklady
CREATE FUNCTION clean_emp() RETURNS void AS $$
DELETE FROM emp WHERE salary < 0;
$$ LANGUAGE SQL;
CREATE FUNCTION add(integer, integer) RETURNS integer AS
$$
SELECT $1+$2;
$$ LANGUAGE SQL;
SELECT add(1,2) AS answer;
* SQL funkce příklady
CREATE FUNCTION tf1(integer, numeric) RETURNS integer AS
$$
UPDATE bank
SET balance = balance-$2
WHERE accountNo = $1
RETURNING balance;
$$ LANGUAGE SQL;
SELECT tf1(17, 100.0);
* SQL funkce použití kompozitních typů
CREATE TABLE emp (name text, salary numeric, age integer);
CREATE FUNCTION doubleSalary(emp) RETURNS numeric AS $$
SELECT $1.salary * 2 AS salary;
$$ LANGUAGE SQL;
SELECT name, doubleSalary(emp.*)
FROM emp
WHERE name=‘Jenda’;
* SQL funkce použití kompozitních typů
pokud je použit kompozitní typ jako návratová hodnota, vrací se jen jeden řádek!

CREATE FUNCTION newEmp() RETURNS emp AS $$
SELECT text ’None’ AS name,
1000.0 AS salary,
25 AS age;
$$ LANGUAGE SQL;
SELECT (new_emp()).name; * SQL funkce použití kompozitních typů
jak vrátit víc řádků

CREATE FUNCTION getOldEmp(integer) RETURNS SETOF emp AS $$
SELECT * FROM emp WHERE age>$1;
$$ LANGUAGE SQL;
CREATE FUNCTION sum_n_product (x int)
RETURNS TABLE(sum int, product int) AS $$
SELECT $1 + tab.y, $1 * tab.y
FROM tab;
$$ LANGUAGE SQL; *
Databázové systémy 2 Kapitola 10
Procedurální rozšíření jazyka SQL, procedurální funkce, kursory Procedurální funkce funkce psané v nějakém procedurálním jazyce
použijeme PL/pgSQL, ale PostgreSQL umožňuje i Python, Perl nebo Tcl
CREATE FUNCTION func(parlist) RETURNS datatype AS $$
BEGIN
...
END;
$$ LANGUAGE plpgsql; * PL/pgSQL obdoba jazyka PL/SQL firmy ORACLE
umožňuje
vytvářet funkce a obsluhu triggerů
přidat řízení běhu programu k jazyku SQL
provádět složité výpočty
využívat všech datových typů v DB
využívat operací a jazyka SQL
být důvěryhodný pro server * PL/pgSQL vstupní parametry
jakýkoli datový typ, definovaný v DB, i tabulka
odkazy číslováním ($1, $2, atd.)
CREATE FUNCTION funkce(int, real) RETURNS ....
lze ale v seznamu parametrů pojmenovat
CREATE FUNCTION sales_tax(subtotal real) RETURNS ....
návratové hodnoty
jakýkoli základní či kompozitní datový typ
set (množina)
tabulka
void * PL/pgSQL blokově orientovaný jazyk, vnořování bloků
nezaměňovat BEGIN s transakcemi
case insensitive
blok má strukturu
návěští (label) – nepovinné
deklarace
tělo bloku
komentář
--
/* ... */
* Deklarace všechny proměnné musejí být deklarovány
kromě iterátoru LOOP a FOR
mohou mít libovolný datový typ, i z SQL
kopírování datových typů
variable%TYPE
table_name%ROWTYPE
syntaxe
name [ CONSTANT ] type [ NOT NULL ] [ { DEFAULT | := } expression ];

user_id integer; quantity numeric(5); url varchar;
myrow tablename%ROWTYPE;
myfield tablename.columnname%TYPE;
arow RECORD; -- obecný kompozitní typ bez určení struktury
* Deklarace příklady
CREATE FUNCTION SP(x int, y int, OUT sum int, OUT prod int) AS $$
BEGIN
sum := x + y;
prod := x * y;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION ext_sales(n int)
RETURNS TABLE(quantity int, total numeric) AS $$
BEGIN
RETURN QUERY SELECT quantity, quantity * price FROM sales WHERE itemno = n;
END; $$ LANGUAGE plpgsql; * Deklarace příklady
CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$
DECLARE
t2_row table2%ROWTYPE;
BEGIN
SELECT * INTO t2_row FROM table2 WHERE ... ;
RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
END;
$$ LANGUAGE plpgsql;
SELECT merge_fields(t.*) FROM table1 t WHERE ... ; * Příkazy přiřazení
variable := expression;
SQL příkazy nevracející data
BEGIN
UPDATE mytab SET val = val + delta WHERE id = key;
END;
dotazy vracející jeden řádek (do proměnné)
SELECT select_expressions INTO target FROM ...;
INSERT ... RETURNING expressions INTO target;
nedělat nic: příkaz NULL;
informace o zpracování příkazu
GET DIAGNOSTICS variable = item [ , ... ];
proměnná FOUND
* Příkazy dynamické SQL příkazy
často chceme, aby SQL příkaz obsahoval pokaždé něco jiného (jiné identifikátory apod.)
příkaz EXECUTE command_string
uvnitř příkazového řetězce nutno ošetřit uvozovky – např. funkcemi quote_ident a quote_literal
CREATE FUNCTION vypis(tab text, podm text) RETURNS void AS $$
BEGIN
EXECUTE ‘SELECT * FROM ‘ || quote_ident(tabulka) ||
‘ WHERE ‘ || quote_literal(podm);
END;
$$ LANGUAGE plpgsql; * Řízení běhu programu návrat z funkce
RETURN expression; -- jednotlivá hodnota
vracení složitějších dat
ve funkci je zavedena proměnná pro výsledek, do níž se ukládají data
příkaz RETURN QUERY query; připojí do návratové proměnné výsledek dotazu
příkaz RETURN NEXT expression; připojí do návratové proměnné výsledek výrazu
tyto příkazy neukončí funkci, to je nutno zařídit dalším samostatným příkazem RETURN;
* Řízení běhu programu příklad
CREATE FUNCTION getAllTbl() RETURNS SETOF Tbl AS $$
DECLARE
r Tbl%rowtype;
BEGIN
FOR r IN SELECT * FROM Tbl WHERE id > 0
LOOP
-- nějaké zpracování
RETURN NEXT r; -- vrátí aktuální řádek SELECT
END LOOP;
RETURN;
END
$$ LANGUAGE ’plpgsql’ ;

SELECT * FROM getAllTbl(); * Řízení běhu programu podmínka IF a CASE
IF ... THEN
IF ... THEN ... ELSE
IF ... THEN ... ELSIF ... THEN