FAKULTA INFORMATIKY, MASARYKOVA UNIVERSITA V BRNE Slide k přednášce PV003 - Architektura relačních databází Milan Drášil, 2004 - 2011 1 Datové modelování, entitně-relační model, od konceptuálního datového modelu k fyzickému Slouží k popisu datových struktur potřebných pro infomarční systém na konceptuální (abstraktní) úrovni. Entita (entitní typ) - je typ objektu zájmu informačního systému, který je jednoznačně identifikovatelný. Instance entity múže být fyzicky existující objekt (dům, osoba), nebo také událost (prodej auta). Vztah (relationship) - entity mohou být spolu svázány vztahy (auto „bylo prodáno" osobě). Entity (a vztahy) mohou obsahovat atributy. Entity a vztahy znázorňujeme E-R diagramy, entity vyjadřujeme jako obdélníky, vztahy jako spojnice. Vztahy mohou mít určenu kardinalitu. Okres Část obce 0,n Budovy v obci 1,1 ± Budova N 1,1 1,n Obec má části 1,n i Obce za okres Obec 1,n 1,1 Obec se skládá z KÚ Katastrální území Oprávněný subjekt Parcely v KÚ T Vlastní 0,n 1,1 0,n 4- Budova je na listu vlastnictví 0,n List vlastnictví 0,n 4- 1,1 Parcela je na listu vlastnictví H- Parcela 1,n 1,1 Souborově orientované systémy: Výhody: - optimalizace datových struktur vzhledem k řešené problematice - menší systémové nároky Nevýhody: - aplikační závislost - obtížné zabezpečení konzistence dat - obtížná realizace konkurentních operací (zamykání souborů) - obtížná čitelnost - dokumentovatelnost datového modelu - téměř nemožný transakční přístup pro operaci s daty - obtížné řízení přístupových práv Databázově orientované systémy: File system Datový sklad RDBMS databázový server Aplikační vybavení (*.exe,*.cgi,*.php, *. asp ...) Výhody: - aplikační „nezávislost" - snadné zabezpečení konzistence dat - snadná realizace konkurentních operací - snadná čitelnost - dokumentovatelnost datového modelu - standardizovaná data umožňují i standardní vývoj IS, strukturovanou analýzu problematiky (vývoj pomocí prostředků CASE), od konceptuálního datového modelu je přechod do fyzického DM takřka automatizovatelný. - neprocedurální přístup k datům (tj. neříkám „jak" to chci, ale „co" chci) Nevýhody: - obtížná implementace nestandardních přístupových technik - obtížná implementace komplikovanějších datových struktur, je nutné je normalizovat do tabulek a to může zpomalit přístup k datům - neprocedurální přístup k datům 2 Relační algebra Aby bylo možné standardizovat přístup k datům pomocí databáze, bylo nutné standardizovat datové struktury. Běžně lze ukládat data ve formě záznamů, jejichž jednotlivé položky mají týž (sémantický) význam (převod z E-R modelu 1:1): Příklad 1. (data o podniku): Zam: ID Jmeno Prijmeni Odd 1 Přemysl Novák Pro 2 Jan Procházka Pro 3 Jiřina Tichá Adm .. Odd: ID Nazev ] Adm Administrativa Pro Provoz Proj: DP-ISKN Dálkový přístup ISKN UAP Územní analytika •• ZaP: Zam Projekt Procent 1 ÚAP 50 1 DP-ISKN 50 2 DP-ISKN 100 .. Nad těmito strukturami budeme požadovat obecný aparát, který nám bude poskytovat data například v této formě: Přemysl Novák Provoz UAP 50 Přemysl Novák Provoz DP-ISKN 50 Jan Procházka Provoz DP-ISKN 100 Relační databáze (Codd červen 1970, ACM Journal, Communications of ACM): - datové struktury jsou n-ární relace - relace je reprezentována tabulkou, výčtem pravdivých n-tic relace - nad relacemi jsou proveditelné operace relační algebry Relační algebra: n -ární relace R - nad množinami Mj.,..,Mn (domény relace) je jakákoli podmnožina: R CZ M1 x .. x Mn Projekce relace II(R, (Mx..My)) - vznikne z relace r tak, že do ní zahrneme pouze vyjmenované domény (Mx..My). Selekce a(R,p) - je podmnožina relace r splňující danou podmínku (predikát) p. Součin relací a x b jsou všechny m+n-tice (a1ř ..am,b1..,bn), kde (a1,..am) GA a (b1..,bn) GB. Sjednocení relací (stejných typů) - je běžné množinové sjednocení Průnik relací (stejných typů) - je běžný množinový průnik Příklad 2.: Požadavek na data z Příkladu 1. Lze formulovat v relační algebře: II(<7(Zam x Odd x ZaP,Zam.Odd=Odd.ID a Zam.ID=Zap.Zam),(Zam.Jmeno, Zam.Prijmeni, Odd.Nazev, ZaP.Projekt,ZaP.Procent)) Zam.ID Zam.Jmeno Zam.Prijmeni Zam.Odd Odd.ID Odd.Nazev ZaP.Zam ZaP.Projekt Zap.Procent 1 Přemysl Novák Pro Adm Administrativa 1 UAP 50 1 Přemysl Novák Pro Adm Administrativa 1 DP-ISKN 50 1 Přemysl Novák Pro Adm Administrativa 2 DP-ISKN 100 " 1 Přemysl Novák Pro Pro Provoz 1 ÚAP 50 1 Přemysl Novák Pro Pro Provoz 1 DP-ISKN 50 1 Přemysl Novák Pro Pro Provoz 2 DP-ISKN 100 2 Jan Procházka Pro Adm Administrativa 1 Úap 50 2 Jan Procházka Pro Adm Administrativa 1 DP-ISKN 50 2 Jan Procházka Pro Adm Administrativa 2 DP-ISKN 100 2 Jan Procházka Pro Pro Provoz 1 ÚAP 50 2 Jan Procházka Pro Pro Provoz 1 DP-ISKN 50 2 Jan Procházka Pro Pro Provoz 2 DP-ISKN 100 3 Jiřina Tichá Adm Adm Administrativa 1 ÚAP 50 3 Jiřina Tichá Adm Adm Administrativa 1 DP-ISKN 50 3 Jiřina Tichá Adm Adm Administrativa 2 DP-ISKN 100 3 Jiřina Tichá Adm Pro Provoz 1 ÚAP 50 3 Jiřina Tichá Adm Pro Provoz 1 DP-ISKN 50 3 Jiřina Tichá Adm Pro Provoz 2 DP-ISKN 100 3 Jazyk relační databáze Požadavky na jazyk relační databáze: - vytváření, modifikace a rušení relací - dotazy nad tabulkami tj. implementace relační algebry - vkládání, změna, odstranění řádku v tabulce - garance konzistence dat - řízení přístupových práv - řízení transakcí Krátká historie SQL: - IBM se věnovala vývoji jazyka, který by "lidským" způsobem zabezpečil operace nad relacemi, vznikl jazyk SEQUEL (Structured English Query Language) - Z SEQUEL (už se angličtině moc nepodobal) později vznikl jazyk SQL Structured Query Language - dnes všeobecně uznáván za standard pro komunikaci s relačními databázemi. - Jsou kodifikovány standardy SQL (ANSI, ISO/IEC) Vývoj v komerčních firmách jde vývoj (pochopitelně!) rychleji, než práce standardizačních komisí => univerzální standard neexistuje jednotlivé implementace se liší (ORACLE, MS-SQL, INFORMIX, DB2, MySQL.Postgre, Sybase) Části jazyka SQL - Definiční část - Data Definition Language - Manipulační část - Data Manipulation Language - Řízení transakcí - Transaction Control Procedurální nadstavby - Transact SQL (MS-SQL,Sybase) - PL/SQL (Procedural Language/SQL, ORACLE) Souborový přístup k datům: FILE *inf; inf=fopen(...); while( ) { fseek(inf,...); fread(inf,...); } Použití databázového stroje: string sql = "select jmeno, prijmeni, plat from zamestnanci"; Cursor cursorRes = OpenCursor(sql); while ( (object fetchRes = FetchCursor(cursorRes) ) !=null ) { //Zpracuj.. } CursorRes.Close(); 4 Lexikální konvence SQL a základní datové typy Příkaz jazyka SQL může být víceřádkový mohou být použity tabelátory. Tedy příkaz SELECT ENAME, SAL*12, MONTHS_BETWEEN (HIREDATE,SYSDATE) FROM EMP; a příkaz SELECT ENAME, SAL * 12, MONTHS_BETWEEN( HIREDATE, SYSDATE ) FROM EMP; jsou ekvivalentní. Velká a malá písmena nejsou podstatná v rezervovaných slovech jazyka SQL a identifikátorech. Tedy příkaz: SELECT ename, sal * 12, month_between( HIREDATE, SYSDATE ) FROM emp; je ekvivalentní s předchozími příkazy. Některé databázové stroje (MySQL) lze instalovat jako „case/accent sensitive", tedy rozlišuje i diakritická znaménka a malá/velká písmena. V databázích je zavedena konvence pro vynucení case/accent sensitivity, například v DB ORACLE jsou to uvozovky: CASE_ACCENT_INSENSITIVE_IDENTIFIER "Identifikátor zohledňující velká/malá a diakritiku" Základní elementy jazyka SQL: - Konstanty (101J'text',','něco jiného''') - Integer(5803042157) - Number (580304.2157) - Datové typy (int, number(m,n), date, varchar(n),long, long raw) - null speciální hodnota pro prázdnou hodnotu - Komentáře (/* */) - Objekty databázového schématu (tabulky, pohledy, indexy, sekvence, ...) Z uvedeného vyplývá, že příkazy jazyka jsou závislé na zadaném databázovém schématu, tedy jeden příkaz SQL může být syntakticky správný v jednom schématu a v jiném nikoli. Například dotaz na tabulku je syntakticky špatně, když ve schématu tabulka daného jména neexistuje. 5 DDL - Data Definition Language Vytváření tabulek příkaz create table CREATE TABLE scott.emp ( empno NUMBER, ename VARCHAR2(10) ); CREATE TABLE emp ( empno NUMBER CONSTRAINT pk_emp PRIMARY KEY, ename VARCHAR2(10) CONSTRAINT nn_ename NOT NULL CONSTRAINT upper_ename CHECK (ename = UPPER(ename)), job VARCHAR2(9), mgr NUMBER CONSTRAINT fk_mgr REFERENCES scott.emp(empno), hiredt DATE DEFAULT SYSDATE, sal NUMBER(10,2) CONSTRAINT ck_sal CHECK (sal > 500), comm NUMBER(9,0) DEFAULT NULL, deptno NUMBER(2) CONSTRAINT nn_deptno NOT NULL CONSTRAINT fk_deptno REFERENCES scott.dept(deptno) ); Modifikace tabulek - příkaz alter table Přidání sloupce: ALTER TABLE emp ADD ssn varchar2(32); Změna typu sloupce: ALTER TABLE emp modify date of birth (26); Odebrání sloupce: ALTER TABLE emp DROP COLUMN date_of_birth; Inteqritní omezení: Primární klíč: ALTER TABLE emp ADD CONSTRAINT pk_emp PRIMARY KEY (empno,deptno); Cizí klíč: ALTER TABLE EMP ADD CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES scott.dept(deptno); Přehled integritních omezení: not null Vyplnění sloupce je povinné UNiQUE_Sloupec (sloupce) má unikátní hodnoty v celé tabulce PRIMARY Primární klíč tabulky KEY REFERENCES Referenční integrita, hodnota sloupce je hodnotou primárního klíče jiné (stejné) tabulky CHECK Kontrola vloženého řádku Indexování tabulek příkaz - create index Index je uspořádaný seznam všech hodnot jednoho nebo více sloupců: - rychlý přístupu k řádkům tabulek - přístupu do tabulek v po řadě podle uspořádání CREATE INDEX emp_idx1 ON emp (ename, job); není totéž, co CREATE INDEX emp idx1 ON emp (job, ename); Pro získání jednoznačné hodnoty typu INT (celé číslo) slouží tzv. sekvence. Obyčejně jsou využívány v těch situacích, kde neexistuje objektivní primární klíč v relační tabulce. Hodnota sekvence je generována nezávisle na transakčním zpracování. Ke každé sekvenci přistupujeme pomocí pseudosloupců: currval vrací současný stav sekvence nextval vrací následný stav sekvence create sequence seq1; create sequemce seq1 start with 32 increment by 100; Příkaz drop: drop typ_objektu jméno_objektu odstraní objekt z datového schématu. Např. drop public synonym s1; odstraní ze schématu synonymum si Klauzule cascade constraints odstraní intergritní omezení související s touto tabulkou. drop table okres cascade constraints; odstraní i integritní omezení poi_obec_fkoi Synonyma: create public synonym t1 for tabulka1; create public synonym tabulka1 for u1.tabulka1; 6 DML - Data Manipulation Language Vkládání řádků do tabulek Příkaz INSERT: INSERT INTO tabulka (sloupect ,sloupec2 ,..,sloupecn) VALUES (hodnotax ,hodnota2 ,.,hodnotan) Pořadí sloupců nemusí odpovídat pořadí v definici tabulky a nemusí být všechny. INSERT INTO tabulka VALUES (hodnota1 ,hodnota2 ,...,hodnotan) Pořadí sloupců musí odpovídat pořadí v definici tabulky, nedoporučuje se - změna struktury tabulky, přidání sloupců vynucuje změnu všech aplikací, které takový insert používají. Při příkazu insert se kontrolují všechna integritní omezení na tabulce. V případě, že není dodána hodnota a v definici tabulky je použita DEFAULT klausule, potom je dosazena příslušná hodnota z DEFAULT klausule. Sloupce které jsou primárním nebo unikátním klíčem jsou vždy indexovány, kontrola je rychlá. Kontrola referenční integrity - sloupce, na které odkazuje referenční integrita jsou buď primární, nebo unikátní klíče, proto je kontrola referenční integrity rychlá. Změna hodnot v řádcích tabulky Příkaz UPDATE: UPDATE tabulka SET sloupec1=hodnota1, sloupecn= hodnotan Změní hodnoty na všech řádcích UPDATE tabulka SET sloupec1=hodnota1, sloupecn= hodnotan WHERE logická_podmínka např. WHERE (VEK>40) and (VZDELANI='MUNI') Při příkazu update se kontrolují všechna dotčená integritní omezení na tabulce. Při změně hodnoty sloupce, který je primárním nebo unikátním klíčem je kontrola rychlá, sloupce jsou indexovány. Při změně hodnoty sloupce, na který odkazuje jiná tabulka cizím klíčem je kontrolována korektnost této operace, tedy prochází se "detailová" tabulka a kontroluje se výskyt staré hodnoty, v případě jeho nalezení operace končí chybou. Z toho plyne nutnost vytvořit indexy na každý cizí klíč! Odstranění řádků z tabulky Příkaz DELETE: DELETE FROM tabulka Odstraní vše! DELETE FROM tabulka WHERE počmiňka Při mazání řádku z tabulky, na kterou odkazuje jiná tabulka cizím klíčem je kontrolována korektnost této operace, tedy prochází se "detailová" tabulka a kontroluje se výskyt mazané hodnoty, v případě jeho nalezení operace končí chybou. Další důvod, proč vytvářet index na každý cizí klíč! on delete klausule cascade - při odstranění řádků z nadřízené tabulky (a1) se odstraní i řádky z tabulky podřízené (b1). create table a1 (i int primary key); create table b1 (i int references a1(i) on delete cascade); set null - při odstranění řádků z nadřízené tabulky (ai) se odstraní je nastavena hodnota cizích klíčů podřízené tabulky (bi) na hodnotu null. create table ai (i int primary key); create table bi (i int references ai(i) on delete set null); 7 VÝBĚRY Z TABULEK, VYTVÁŘENÍ RELACÍ Jednoduché příkazy select: select all SL1, SL2 from TABULKA; Sloupce lze v rámci příkazu SELECT přejmenovat: select SL1 A,SL2 B from TABULKA; Výstup lze uspořádat (při velkých tabulkách je vhodné na sloupce vytvořit index): select SL1 A,SL2 B from TABULKA order by SL1; select SL1 A,SL2 B from TABULKA order by SL1 DESC; Fráze distinct neopakuje stejné řádky select distinct SL1 A,SL2 B from TABULKA; V příkazu select lze použít funkce (pozor jejich repertoár a jména se mohou lišit v závislosti na implementaci RDBMS stroje). select SL1,SL2 from TABULKA where SL1 ='BRNO' and SL2>0; select SL1,SL2 from TABULKA where upper(SL1)='BRNO'; Výsledek každého select příkazu je formálně tabulka lze s ním tedy v příkazech takto nakládat: select * from ( select JMÉNO, PRIJMENI FROM ... order by prijmenimrc ) WHERE PRIJMENImRC BETWEEN 'xxxx' AND ' yyyy'; Přepínač - výraz case: Přepínač typu case může být použit v části select tam kde vybíráme sloupce (nebo i ve where klausuli) a potřebujeme „rozskok" podle konečného počtu podmínek. Například: SELECT case when poc_obyv>=500 and when poc_obyv>=1000 and when poc_obyv>=5000 and when poc_obyv>=10000 and when poc_obyv>=50000 and when poc_obyv>=100000 then else 0 end SET_TEXT_HEIGHT FROM NAZVY OBCI poc_obyv<1000 then 9 poc_obyv<5000 then 10 poc_obyv<10000 then 11 poc_obyv<50000 then 12 poc_obyv<100000 then 13 14 Množinové operace nad relacemi: Sjednocení: select ... union [all] select... Průnik: select ... intersect select... Diference: select . minus select. 8 Spojování tabulek (join) - násobení a selekce Jméno, Příjmení, Okres, Obec, "Část obce", "Číslo popisné" select OS.JMENO OS.PRIJMENI OK.NAZEV OB.NAZEV CO.NAZEV OS.ADRESA_CPOP from P01_OKRES OK, P01_OBEC OB, P01_CAST_OBCE CO, P01_OPSUB OS where OS.ID =58342157 AND OS.ADRESA_OKRES=OK.ID AND OS.ADRESA_OBEC =OB.ID AND OS.ADRESA_COBCE=CO.ID P01_OKRES ID NUMBER(4) NAZEV VARCHAR2(64) P01_KAT_UZ ID = ID_OBEC P01_OBEC in NUMBER(5) ID NUMBER(5) NAZEV VARCHAR2(64) ID_OBEC NUMBER(5) NAZEV VARCHAR2(64) in_OKRES NUMBER(5) ID = ADRESA_OKRES ID = SJM_PARTNER2 ID = SJM_PARTNER1 ID = ID_OKRES P01_OPSUB ID NUMBER ICO NUMBER(8) RC NUMBER(10) SJM_PARTNER1 NUMBER SJM_PARTNER2 NUMBER PRIJMENI VARCHAR2(128) JMENO VARCHAR2(64) TITUL_PRED VARCHAR2(16) TITUL_ZA VARCHAR2(16) ADRESA_OKRES NUMBER(4) ADRESA_OBEC NUMBER(5) ADRESA_COBCE NUMBER(5) ADRESA_CPOP NUMBER(5) AD = ADRESA_OBEC ID = ADRESA_COBCE P01_CAST_OBCE ID NUMBER(5) NAZEV VARCHAR2(64) ID_OBEC NUMBER(5) P01_VLASTNI ID KU NUMBER(6) ID LV NUMBER(5) ID OPSUB NUMBER PODIL_CITATEL NUMBER PODIL_JMENOVATEL NUMBER ID = ID_CAST_OBCE P01_PARCELA ID KU NUMBER(6) PARC CIS NUMBER(4) ID_LV = CIS_LV PAR POD NUMBER(3) DRUH_POZ NUMBER(2) NEM_VYUZ NUMBER(3) VYMERA NUMBER(9) CIS_LV NUMBER(5) ID_KU = ID_KU ID_LV = ID_LV P01_LV ID KU NUMBER(6) ID 1 V NUMBER(5) = LV_KU| = cis_lV P01_BUDOVA TYP CISLA VARCHAR2(1) CISLO DOMOVNI NUMBER(5) ID CAST OBCE NUMBER(5) CIS_LV NUMBER(5) ID = ID OBEC D = ID KU ID D_OPSUB D_KU 9 where klausule 1) Porovnání výrazu s výrazem nebo poddotazem (subquery) select * from P01_OPSUB where ADRESA_OBEC= (select ID from P01_OBEC where nazev=,Prahav); 2) Porovnání výrazu se seznamem výrazů nebo poddotazem select * from P01_OPSUB where ADRESA_OBEC = SOME(3701,3801,3201); select * from P01_OPSUB where ADRESA_OBEC <> ALL(3701,3801,3201); 3) Příslušnost k množině select * from P01_OPSUB where ADRESA_OBEC IN (select ID from P01_OBEC where počet_obyv>2000); 4) Rozsahový dotaz select * from P01_OPSUB where RC BETWEEN 5800000000 AND 5899999999; 5) NULL test select * from P01_OPSUB where TITUL_PRED IS NOT NULL; 6) Existence v poddotazu select * from P01_OPSUB A where exists ( select NULL from P01_OBEC B where B.ID=A.ADRESA_OBEC AND B.ID_OKRES<>A.ADRESA_OBEC ); 7) Srovnání řetězců select * from P01_OPSUB where PRIJMENI LIKE 'Nov%'; 8) Logická kombinace 1) - 7) pomocí logických operátorů AND OR NOT 10 Techniky spojování (JOIN) tabulek: Nested loops (vnořené cykly): Prochází se celá „vnější" tabulka a hledá se odpovídající klíč v tabulce „vnitřní". Podle existence indexu ve vnitřní tabulce se hledá odpovídající řádek podle indexu, nebo opět plným průchodem. Sort merge join : Uspořádá obě tabulky (vrácené řádky) podle klíče, kterým tabulky spojujeme, v případě existence indexu použije vhodný index. Poté prochází obě uspořádané tabulky a vrací kombinace řádků se stejnými klíči. Hash join : Pro menší tabulku se z klíčů vytvoří hash tabulka. Poté se prochází větší tabulka a hledá se odpovídající klíč v hash tabulce. Databáze ORACLE se rozhoduje pro strategii na základě naplnění tabulek, frekvence výskytu klíčů. 11 Outer join - vnější spojení tabulek Outer join vrací všechny řádky, které vyhovují podmínce a takové řádky z jedné tabulky, které ji nesplňují. Sloupce, resp. výrazy v příkazu select z ostatních tabulek jsou v těchto případech vraceny jako null. Provádí se (+) operátorem ve where klausuli. create table tl (i int); create table t2 (i int); insert into tl values (1); ..(2); ..(3); insert into t2 values (2); ..(3); ..(4); SELECT tl.i i1,t2.i i2 FROM t1,t2 WHERE t1.i=t2.i SELECT tl.i i1,t2.i i2 Il I2 FROM t1,t2 ------ WHERE t1.i=t2.i (+) 1 2 3 2 3 SELECT tl.i i1,t2.i i2 FROM tl,t2 WHERE tl.i(+)=t2.i Il 2 3 I2 2 3 4 12 Uložené příkazy select = view create view jmeno as select ... S objekty typu view se v DML zachází: SELECT: stejně jako s tabulkami update: všechny sloupce jsou jednoznačně přiřazeny key-preserved tabulkám - tj. takovým tabulkám jejichž každý klíč je zároveň klíčem view, příkaz mění řádky právě jedné tabulky delete: řádky view odkazují na právě jednu key-preserved tabulku, z ní jsou řádky vymazány insert: nesmí se explicitně nebo implicitně odvolávat na sloupce náležící non-key-preserved tabulce, všechny vkládané sloupce náleží právě jedné key-preserved tabulce Příklad view: create table odděleni id nazev int primary key, varchar2(256) create table pracovník id jmeno int varchar2(32), primary key, příjmení varchar2(32), id_odd int, constraint pfk1 foreign key (id_odd) references oddeleni(id) from pracovnik a, oddeleni b where a.id odd=b.id; 1) Které sloupce z tohoto VIEW jdou vkládat? 2) Které sloupce z tohoto VIEW jdou měnit? ); create view prac_ext as select a.id id_prac, a.prijmeni prijmeni, a. jmeno jmeno, b. id id_odd, b.nazev naz odd 3) Lze z tohoto VIEW mazat (DELETE), co se stane při? Příklad: Jeden až několik pracovníků ze stejné oblasti má přidělen účet a může vidět jen svou oblast: create table pvp_pracovnik ( id_pracovnik number not null, oblast varchar2 (b) not null, org_jedn_hr varchar2 (10) not null, harmonogram varchar2 (9) not null, user_name varchar2 (16), prijmeni varchar2 (50) not null, jmeno varchar2 (25) not null, tarifni_trida varchar2 (4), tarifni_stupen varchar2 (2), datum_nastupu date not null, datum výstupu date, ) ; CREATE OR REPLACE VIEW U_PVP_PRACOVNIK AS select * from PVP_PRACOVNIK WHERE OBLAST IN (SELECT OBLAST FROM PVP_PRACOVNIK WHERE USER_NAME=USER ) Materializované pohledy Jsou uložené výsledky dotazů (select), narozdíl od view výsledky jsou skutečně fyzicky uloženy. Je možnost výsledky dotazu obnovovat. create materialized view vl REFRESH FORCE START WITH SYSDATE NEXT SYSDATE + 1/1440 as select ... refresh metoda obnovy fast pohled musí mít primární klíč, musí exitsovat materialized view log na detailové tabulce COMPLETE provedeni celého dotazu znovu force server vybere rychlejší metodu start with .. next interval obnovy 13 Uživatelsky definované datové typy (ADT), objektově relační databáze Vytvoření typu: create type Point as object ( x number, y number ) create type Points as varray (10000) of Point; create type LineString as object ( NumPoints int, Vertexes Points ) create table Streets ( id int, geom LineString, constraint Streets_pk primary key (id) ) insert into Streets (id,geom) values (1, Linestring(3, Points( Point(0 , 0), Point(2000, 123), Point(2020,13460) ) ) ) Vytvoření typu s metodami: create or replace type AType as object ( x number, y varchar2(10), member function ToString return varchar DETERMINISTIC ) NOT FINAL —[, NOT INSTANTIABLE] / create type body AType is member function ToString return varchar DETERMINISTIC is begin return y; -- PL/SQL tělo metody viz. funkce end; end; / ADT nelze indexovat, lze však použít deterministickou funkci (viz. deterministické funkce PL/SQL), jejíž argument je ADT a která vrací „indexovatelný" typ. create table ATable(a Atype); create index ATable_i1 on ATable(a.ToString()); Typ použitý jako typ sloupce by neměl být not instantiable, jinak do něj nelze vkládat hodnoty. Dědičnost typů: create or replace type BType under AType ( z varchar2(200) ); Typ, ze kterého dědíme musí být not final. ORACLE nepodporuje vícenásobnou dědičnost. Obecně není možné select na celý ADT, neboť ne všechny typy klientských rozhraní podporují ADT. Musíme vybírat jeho jednotlivé složky select AT.A.x from ATable AT; V případě neznáme položky ADT, možností je využití podpory pomocí XML: select xmlelement(MROWM,geom).getStringVal()from Streets resp: select xmlelement(MROW",geom).getClobVal() from Streets vrátí: 2 0 0 2000 123 Vzhledem, tomu, že v moderních vývojových prostředí klientský aplikací (C++, C# .NET) je implementována masivní podpora parsingu XML, jedná se o poměrně silný a univerzální prostředek. Pozor, cenou je zvýšená zátěž databázového serveru a řádově větší přenosu dat v XML formátu. 14 Hierarchické dotazy Vybere „podstrom" ze stromové struktury v tabulce. create table HI ( ID INT, PARENT INT, CONSTRAINT HI_FK01 FOREIGN KEY (PARENT) REFERENCES HI(ID); ); INSERT INTO HI VALUES (1,null); INSERT INTO HI VALUES (2,1); INSERT INTO HI VALUES (3,1); INSERT INTO HI VALUES (4,3); INSERT INTO HI VALUES (5,3); connect by klausule: definuje relaci „rodič" ve stromu start with klausule: Příklad: select level,id,parent from hi connect by prior ID=PARENT start with id=3; LEVEL ID PARENT 1 3 1 2 4 3 2 5 3 15 Skupinové (agregační) funkce Jsou funkce které vrací jeden výsledek na základě vstupu z více řádků. Pokud není uvedena group by klausule potom je výsledek funkce aplikován na celý výsledek select dotazu. AVG(expr) - průměr z expr select AVG(PLAT) from zamestnanci where VEK between 25 and 30; count({* | [distinct|all] expr}) - počet řádků, ve kterých je expr NOT NULL select count(*) from P01_VL vrátí počet řádků z tabulky p01_vl Další skupinové funkce: MAX(expr) MIN(expr) STDDÉV(expr) SUM(n) group by klausule Použitím group by klausule jsou podle výrazu v této klausuli agregovány řádky výsledku select prijmeni from P01_OPSUB group by PRIJMENI; Seznam sloupců v select příkazu, který obsahuje group by může obsahovat pouze: - Konstanty - skupinové funkce - výrazy, které jsou identické s výrazy v group by - výrazy, které jsou založeny na předešlých výrazech select rtrim(upper(PRIJMENI),40)||x-x||count(*) from P01_OPSUB group by PRIJMENI; having klausule Používá se k omezení výstupu na základě skupinových funkcí select rtrim(upper(PRIJMENI),40)||x- \ count(*) from P01_OPSUB group by PRIJMENI having count(*)>2; Strategie vyhodnocení SQL dotazu 1. Obsahuje-li select where klausuli zpracují se pouze řádky které vyhovují where 2. Obsahuje-li select group by klausuli, vytvářejí se skupiny podle výrazů group by 3. Obsahuje-li select having klausuli, potom jsou vyřazeny ty skupiny, které podmínku having nesplňují 16 Optimalizace příkazů Exekuční plán: delete from plan_table; commit; explain plan SET STATEMENT_ID = 'Adresa 01' INTO plan_table for select a. NAZEV Okres, b. NAZEV Obec, c. NAZEV Ulice, d. CIS_OR COR from u_okresy A, u_obce B, u_ulice C, u_adresy D where b. kodok=a.kodok and c. iczuj=b.iczuj and d. kodul=c.kodul and d.PRIZNAK_ulice=c.priznak and b. nazev like 'Brno%' and c. NAZEV like 'Podlesná%' SELECT LPAD(' ',2*(LEVEL-l))||operation operation, options, object_name,object_type FROM plan_table START WITH id = 0 AND statement_id = 'Adresa 0lf CONNECT BY PRIOR id = parent_id AND statement_id = 'Adresa 0l'; SELECT STATEMENT NESTED LOOPS NESTED LOOPS HASH JOIN TABLE ACCESS BY INDEX ROWID U OBCE TABLE INDEX RANGE SCAN IX U OBCE INDEX TABLE ACCESS FULL U ULICE TABLE TABLE ACCESS BY INDEX ROWID U OKRESY TABLE INDEX UNIQUE SCAN PK U OKRESY INDEX (UNIQUE) INDEX RANGE SCAN IX U ADRESY 2 INDEX Přístup k tabulce: FULL INDEX RANGE SCAN UNIQUE SCAN Řízení přístupu - HINT {DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */ /*+ INDEX(jméno_indexu) */ - vynutí použití indexu CREATE INDEX P01_OPSUB_I2 ON P01_OPSUB(ADRESA_OKRES); CREATE INDEX P01_OPSUB_I1 ON P01_OPSUB(PRIJMENI); CREATE INDEX P01_OKRES_I1 ON P01_OKRES(NAZEV); select /*+ INDEX(P01_OKRES_I1) */ a. prijmeni, b. nazev from p01_okres b, p01_opsub a where a.ADRESA_OKRES=b.ID AND B.NAZEV='KROMĚŘÍŽ' AND A.PRIJMENI = 'NOVÁK' SELECT STATEMENT Optimizer=CHOOSE NESTED LOOPS TABLE ACCESS (BY INDEX ROWID) OF P01_OKRES INDEX (RANGE SCAN) OF P01_OKRES_I1 (NON-UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF P01_OPSUB INDEX (RANGE SCAN) OF P01_OPSUB_I1 (NON-UNIQUE) /*+ ORDERED */ Spojeni (JOIN) tabulek probíhá v pořadí podle FROM klausule. select /*+ ORDERED */ a. prijmeni, b. nazev from p01_opsub a, p01_okres b where b.ID=a.ADRESA OKRES AND B.NAZEV='KROMEŘÍŽ' AND A. PRIJMENI = 'NOVÁK' SELECT STATEMENT Optimizer=CHOOSE NESTED LOOPS TABLE ACCESS (BY INDEX ROWID) OF P01_OPSUB INDEX (RANGE SCAN) OF P01_OPSUB_I1 (NON-UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF P01_OKRES INDEX (UNIQUE SCAN) OF P01_OKRES_PK (UNIQUE) select /*+ ORDERED */ a. prijmeni, b. nazev from p01_okres b, p01_opsub a where b.ID=a.ADRESA OKRES AND B. NAZEV='KROMÍŘÍŽ' AND A.PRIJMENI = 'NOVÁK' SELECT STATEMENT Optimizer=CHOOSE NESTED LOOPS TABLE ACCESS (BY INDEX ROWID) OF P01_OKRES INDEX (RANGE SCAN) OF P01_OKRES_I1 (NON-UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF P01_OPSUB INDEX (RANGE SCAN) OF P01_OPSUB_I1 (NON-UNIQUE) EXPLAIN PLAN FOR select KU.NAZEV, PA.PARC_TYP, PA.PARC_CIS, PA.PAR_POD, VL.PODIL_CITATEL||'/'||VL.PODIL_JMENOVATEL from P01_VLASTNI VL, P01_PARCELA PA, P01_KAT_UZ KU where VL.ID_OPSUB=1 AND VL.ID_KU =PA.ID_KU AND VL.ID_LV =PA.CIS_LV AND PA.ID_KU =KU.ID; SELECT STATEMENT Optimizer=CHOOSE NESTED LOOPS NESTED LOOPS TABLE ACCESS (FULL) OF P01_KAT_UZ TABLE ACCESS (BY INDEX ROWID) OF P01_PARCELA INDEX (RANGE SCAN) OF P01_PARCELA_PK (UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF P01_VLASTNI INDEX (UNIQUE SCAN) OF P01_VLASTNI_PK (UNIQUE) create index P0l_VLASTNI_il on P0l_VLASTNI(ID_OPSUB); EXPLAIN PLAN FOR select KU.NAZEV, PA.PARC_TYP, PA.PARC_CIS, PA.PAR_POD, VL.PODIL_CITATEL||'/'||VL.PODIL_JMENOVATEL from P0l_VLASTNI VL, P0l_PARCELA PA, P0l_KAT_UZ KU where VL.ID_OPSUB=l AND VL.ID_KU =PA.ID_KU AND VL.ID_LV =PA.CIS_LV AND PA.ID_KU =KU.ID; SELECT STATEMENT Optimizer=CHOOSE NESTED LOOPS NESTED LOOPS TABLE ACCESS (BY INDEX ROWID) OF P01_VLASTNI INDEX (RANGE SCAN) OF P01_VLASTNI_I1 (NON-UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF P01_PARCELA INDEX (RANGE SCAN) OF P01_PARCELA_PK (UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF P01_KAT_UZ INDEX (UNIQUE SCAN) OF P01_KAT_UZ_PK (UNIQUE) 17 TCC - Transaction Control Commands Transakce - je posloupnost DML příkazů, které převedou datové schéma z jednoho konzistentního stavu do druhého. ACID celá se provede, nebo odvolá na konci není porušeno žádné omezení operace jsou izolovány od ostatních transakcí po ukončení transakce jsou data trvale uložena A - Atomic C - Consistent I - Isolated D - Durable commit - Potvrzení změn DML od počátku transakce. rollback [to savepoint] -Odvolá změny od počátku transakce/ savepoitnt savepoint - Stanoví místo po které lze provést rollback set transaction read write - default nastavení transakcí set transaction read only - nastaví transakci tak, že nejsou povoleny příkazy insert, update, delete a select s klausulí for update. Musí být prvním příkazem transakce. Úrovně izolace: set transaction isolation level serializable - Úroveň izolace podle normy SQL92. V případě že se transakce mění objekt, který je měněn jinou konkurentní transakcí, potom transakce končí chybou v případě, že konkurentní transakce je potvrzena. set transaction isolation level read committed - (default chování ORACLE). V případě, že transakce požaduje zámek na řádky, které jsou drženy jinou transakcí, potom transakce čeká na uvolnění, potom DML příkaz provede. select ...... for update [nowait]; Uzamkne vybrané řádky/sloupce pro aktuální transakci až do commit nebo rollback. lock table lock mode mode [nowait]; row share - Zakazuje exclusive lock, jinak nechává povolené konkurentní aktivity na tabulce exclusive - Výhradní právo na tabulku pro transakci, mimo select zakazuje cokoli. share - Zakazuje update tabulky CREATE TABLE II ( I INT, C VARCHAR2(64), CONSTRAINT I1_PK PRIMARY KEY (I) ); INSERT INTO I1 VALUES (1,XAX); COMMIT; READ COMMITED SE #1 - UPDATE I1 SET C='B' WHERE I=1;[OK] SE #2 - UPDATE I1 SET C='C WHERE I=1;[OK - čeká] SE #1 - COMMIT; [OK] SE #2 - COMMIT; [OK] SELECT * FROM I1 I T C 1 C SERIALIZABLE SE #1 - UPDATE I1 SET C='B' WHERE I=1;[OK] SE #2 - UPDATE I1 SET C='C WHERE I=1;[OK - čeká] SE #1 - COMMIT; [OK] SE #2 - havaruje SELECT * FROM I1 I T C 1 B Integritní omezení initially deferred kontrolují se až v okamžiku commit transakce. Příklad: povinná vazba 1:1 create table t1 ( i int primary key ); create table t2 ( i int primary key ); alter table t1 add constraint t1_fk1 foreign key (i) references t2(i); alter table t2 add constraint t2_fk1 foreign key (i) references t1(i) initially deferred; Proběhne: insert into t2 values (2); insert into t1 values (2); commit; Havaruje: insert into t1 values (3); insert into t2 values (3); commit; I select příkaz je v jistém smyslu transakční, databáze musí vrátit stav dat, který byl platný v okamžiku jeho zadání!! Deadlock: CREATE TABLE II ( I INT, C VARCHAR2(64), CONSTRAINT I1_PK PRIMARY KEY (I) ); INSERT INTO I1 VALUES (1,XAX); INSERT INTO I1 VALUES (2,XBX); COMMIT; SE #1 - UPDATE I1 SE #2 - UPDATE I1 SE #1 - UPDATE I1 SE #2 - UPDATE I1 SET C='C WHERE SET C='D' WHERE SET C='E' WHERE SET C='F' WHERE I=1;[OK] I=2;[OK] I=2;[OK - čeká] I=1;[OK - čeká] se #1 - během čekání na prostředek došlo k deadlocku SE #1 - COMMIT; SE #2 - COMMIT; SELECT * FROM I1 I T C 1 F 2 D Strategie transakcí z klientských aplikací. Optimistický a pesimistický přístup transakcí. Organizace rollback segmentů (snímek je příliš starý) 18 Jazyk - PL/SQL 18.1 Struktura bloku PL/SQL je součástí databázového stroje. Je procedurální jazyk, tak jak je pojem procedurálního jazyka běžně chápán. Je strukturován do bloků, tj. funkce a procedury jsou logické bloky, které mohou obsahovat bloky atd. Příkazy: řídící příkazy jazyka PL/SQL, přiřazení-výrazy, SQL příkazy DML. [declare -- declarations] begin -- statements [exception -- handlers] END; 18.2 Deklarace Kolik_mi_zbyva_penez NUMBÉR(6); skutecne BOOLEAN; Datový typ tabuika%ROWTYPE odpovídá struktuře tabulky. Datový typ tabulka .sioupec%ROWTYPE odpovídá typu sloupce v tabulce JM P01_OPSPUB.JMÉNO%TYPE; OBSUB%ROWTYPÉ; Přiřazení, výrazy: tax := price * tax_rate; bonus := current_salary * 0.10; amount := TO_NUMBER(SUBSTR('750 dollars', 1, 3)); valid := FALSE; 18.3 DML a kursory INTO fráze: SELECT sal*0.10 INTO bonus FROM emp WHERE empno = emp_id; Kursor: DECLARE CURSOR c1 IS SELECT empno, ename, job FROM emp WHERE deptno = 20; Ovládání kursorů: 1) Analogie k souborovému přístupu: OPEN, FETCH, CLOSE OPEN C1; FETCH C1 into a,b,c; CLOSE C1; 2) For cykly pro kursory: DECLARE CURSOR c1 IS SELECT ename, sal, hiredate, deptno FROM emp; BEGIN FOR emp_rec IN c1 LOOP salary_total := salary_total + emp_rec.sal; ... END LOOP; Použití rowtype pro kursory: DECLARE CURSOR c1 IS SELECT ename, sal, hiredate, job FROM emp; emp_rec c1%ROWTYPE; Dynamické SQL příkazy: Jsou dotazy jejichž konečný tvar vzniká až při běhu programu. EXECUTE IMMEDIATE sql_stmt := 'INSERT INTO dept VALUES :2, :3)'; EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location; 18.4 Řídící příkazy IF-THEN-ELSE: IF acct_balance >= debit_amt THEN UPDATE accounts SET bal = bal - debit_amt WHERE account id = acct; ELSE INSERT INTO temp VALUES (acct, acct balance, 'Insufficient funds'); END IF; FOR-LOOP: FOR i IN 1..order_qty LOOP UPDATE sales SET custno = customer_id WHERE serial_num = serial_num_seq.NEXTVAL; END LOOP; WHILE-LOOP: WHILE salary < 4000 LOOP SELECT sal, mgr, ename INTO salary, mgr_num, last_name FROM emp WHERE empno = mgr_num; END LOOP; 18.5 Asynchronní ošetření chyb begin select ...... into a,b,c; EXCEPTION WHEN NO_DATA_FOUND THEN -- process error end; 18.6 Funkce a procedury CREATE OR REPLACE PROCEDURE [FUNCTION] jmeno ( pari IN VARCHAR2, par2 OUT INT ) [RETURN VARCHAR2] IS varl VARCHAR2(1); BEGIN RETURN [varl]; END jmeno; / Deterministické funkce: Jsou funkce, které vrací pro stejné argumenty vždy stejný výsledek. Výsledek tedy není ovlivněn momentálním stavem databáze (schéma, data, čas..). Jen tyto funkce lze použít v indexech založených na funkcích. create or replace function fun1(...) return varchar2 deterministic Funkce lze použít v DML příkazech například: SELECT moje_funkce(43) FROM DUAL; SELECT moje_funkce(SL3+SL2); DELETE FROM TABl WHERE SLl=moje funkce(SL3+SL2); Procedury spouštíme v rámci PL/SQL bloku: Begin moje_procedura(argument,......); end; 18.7 Balíky - Package Jsou pojmenované programové jednotky, které mohou obsahovat typy, proměnné, funkce a procedury. CREATE PACKAGE name AS -- public type and item declarations -- subprogram specifications END [name]; CREATE PACKAGE BODY name IS -- private type and item declarations -- subprogram bodies END [name]; Instance objektů vznikají v rámci sezení, tj. nemohou vzniknout kolize zapříčiněné konkurentním používáním objektů balíku. CREATE PACKAGE STEMIG AS C_MASTER_NAME VARCHAR2(16):='S3'; FUNCTION TO_NUMEXT (x in char) RETURN number; FUNCTION ANG (X1 IN NUMBER,Y1 IN NUMBER, X2 IN NUMBER,Y2 NUMBER) RETURN NUMBER; END STEMIG; CREATE PACKAGE BODY STEMIG IS FUNCTION TO_NUMEXT (x in char) RETURN number IS R number; BEGIN R:=TO_NUMBER(x); return(R); exception when VALUE_ERROR THEN return(NULL); END; END STEMIG; 19 Triggery PL/SQL bloky, které jsou přidruženy k tabulkám. Události které spouští triggery: INSERT,UPDATE,DELETE Typy triggerů: STATEMENT ROW BEFORE Trigger je spuštěn jednou před provedením příkazu Trigger je spuštěn jednou před modifikací každého řádku AFTER Trigger je spuštěn jednou po provedení příkazu Trigger je spuštěn jednou po modifikaci každého řádku :NEW a :OLD proměnné v řádkovém triggeru odkazují na nové resp. staré hodnoty modifikovaného řádku. Logické proměnné v každém řádkovém triggeru: inserting - true jestliže trigger je spuštěn INSERT deleting - true jestliže trigger je spuštěn DELETE updating - true jestliže trigger je spuštěn UPDATE UPDATING( column_name) modifikuje sloupec PL/SQL bloky nesmí obsahovat příkazy řízení transakcí (commit, rollback, ...) Triggery by neměly "šifrovat" data tedy by neměly obsahovat bloky typu: if UPDATING('STAV_KONTA') and JMENO_MAJITELE_UCTU='Drášil' and :NEW.STAV_KONTA < :OLD.STAV_KONTA THEN :NEW.STAV_KONTA := :OLD.STAV_KONTA; end if; Kódování zdrojových kódů balíků, těl balíků, procedur, funkcí -vznikne zašifrovaný zdrojový text (doporučuji - nikdy nepoužívat, programátoři svoje zdroje většinou šifrují dostatečně): WRAP INAME=input_file [ONAME=output_file] Přiklad: trigger hlídající akce nad tabulkou: CREATE TRIGGER audit_trigger BEFORE INSERT OR DELETE OR UPDATE ON nejaka_tabulka FOR EACH ROW BEGIN IF INSERTING THEN INSERT INTO audit_table VALUES (USER||' is inserting'!!' new key: '|| :new.key); :NEW.USER_NAME=USER; ELSIF DELETING THEN INSERT INTO audit_table VALUES (USER||' is deleting'||' old key: '|| :old.key); ELSIF UPDATING('FORMULA') THEN INSERT INTO audit_table VALUES (USER||' is updating'||' old formula: '|| :old.formula||' new formula: ' || :new.formula); ELSIF UPDATING THEN IF :OLD.USER_NAME<>USER THEN RAISE_APPLICATION_ERROR('-20000 'Přístup k řádku odmítnut') END_IF; INSERT INTO audit_table VALUES (USER||' is updating'!!' old key: ' || :old.key||' new key: ' || :new.key); END IF; END; 20 Administrace přístupových práv Role jsou seznamy práv: create role jméno; grant [system_priv\role,...] to [user| role|public]; Příklady systémových práv: alter any table, create any sequence, create procedure, select any table ... grant [object_priv|all (colvaan,...),... ] on schema.object to [user\role\public] Příklady práv k objektům: ALTER,EXECUTE,INSERT,READ,SELECT,UPDATE Práva na tabulky končí na úrovni sloupců, pro práva na řádky tabulek musíme použít techniku triggerů nebo „updatable view". Zrušení práv REVOKE [priv] from [user\role\PUBLIC] Příklad postupu administrace: 1) Vytvoříme DB schéma master uživatele. 2) Vytvoříme PUBLIC synonyma pro každý objekt. 3) Stanovíme role pro přístup k objektům, podle typů uživatelů. 4) Rolím přidělíme práva pro jednotlivé objekty. 5) Každý nový uživatel systému nevlastní žádné objekty, "vidí" je prostřednictvím veřejných synonym. 6) Správce systému přidělí potřebné role každému uživateli. Nestandardní indexy FullText: Umožňuje efektivní vyhledání řádků podle „volného" textu, který je obsažen ve sloupci typu clob. Tabulka s dokumenty (text, *.doc, *.pdf ..) create table FULLWORD ( ID INT primary key, TEXT CLOB Full text index: CREATE INDEX FULLWORD_FTI ON FULLWORD(TEXT) INDEXTYPE IS CTXSYS.CONTEXT; Naplnění tabulky dokumenty: insert into fullword (ID,TEXT) values (1, 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Vivamus ornare congue turpis.'); insert into fullword (ID,TEXT) values (2, 'Cras volutpat, neque et sodales congue, urna mauris dignissim sem, blandit laoreet magna mauris in nibh.'); insert into fullword (ID,TEXT) values (3, 'In ac felis eget velit elementum consectetur. Morbi vitae sem. Proin varius luctus risus.'); insert into fullword (ID,TEXT) values (4, 'Fusce ut dui a massa congue vehicula.'); insert into fullword (ID,TEXT) values (5, 'Class aptent taciti sociosqu ad litora torquent per conubia nostra, per inceptos himenaeos.'); insert into fullword (ID,TEXT) values (6, 'Phasellus ut lacus. Praesent nec nisl.1); insert into fullword (ID,TEXT) values (7, 'Pellentesque sapien lectus, ultrices rhoncus, consequat at, elementum non, orci.'); insert into fullword (ID,TEXT) values (8, 'Ut consequat ipsum ut ante. Quisque diam sem, posuere id, vehicula eu, tincidunt ac, neque.'); insert into fullword (ID,TEXT) values (9, 'Vestibulum leo. Fusce vel ante ac dui tincidunt ornare.'); insert into fullword (ID,TEXT) values (10, 'Nam est augue, vehicula ut, molestie non, iaculis quis, arcu.'); commit; Obnovení indexu (není automatické): EXEC CTX_DDL.SYNC_INDEX('FULLWORD_FTľ); Select příkaz z frází contains: SELECT ID,text FROM FULLWORD WHERE CONTAINS(TEXT, 'vehicula') > 0 Vrátí: 4 Fusce ut dui a massa congue vehicula. 8 Ut consequat ipsum ut ante. Quisque diam sem, posuere id, vehicula eu, tincidunt ac, neque. 10 Nam est augue, vehicula ut, molestie non, iaculis quis, arcu. Prostorové indexy (Oracle Spatial Data Option) Slouží k efektivnímu přístupu ke geometrickým datům (čáry, polygony..). Zdrojem pro index je tzv. minimální omezující obdélník (maxima a minima souřadnic). Použitá metoda pro tento typ indexu je R-Tree (modifikace B+ stromu). Indexovatelné sloupce jsou typu mdsys. sdo_geometry Tabulka s geometrickým sloupcem: ALTER TABLE AK_HRANICE_PARCEL ADD GEOMETRY MDSYS.SDO_GEOMETRY; Metadata pro geometrický sloupec: insert into mdsys.user_sdo_geom_metadata (table_name,column_name,diminfo) values ('AK_HRANICE_PARCEL','GE0METRY', mdsys.sdo_dim_array (MDSYS.SD0_DIM_ELEMENT('X',1230000000, 933000000,5), MDSYS.SD0_DIM_ELEMENT('Y', 906000000, 410000000,5) )); Prostorový index: create index AK_HRANICE_PARCEL_SPAT ON ak_hranice_parcel(geometry) indextype is mdsys.spatial index; Prostorový dotaz: select ID,GEOMETRIE FROM ISKN.AK_HRANICE_PARCEL A WHERE MDSYS.SDO_FILTER (GEOMETRIE, MDSYS.SDO_GEOMETRY (2003, (SELECT SRID FROM user_sdo_geom_metadata WHERE TABLE_NAME='AK_HRANICE_PARCEL' AND COLUMN_NAME='GEOMETRY'), NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3), MDSYS.SDO_ORDINATE_ARRAY ( 1144945653, -- dotazovací obdélník 520792013, 1145270564, 521132433) ), 'queryType=window')='TRUE'; 21 Hostitelské nadstavby SQL 21.1 PRO*C Výhoda: relativní platformová nezávislost, vznikne „čistý" C kód. Deklarační část: EXEC SQL BEGIN DECLARE SECTION; VARCHAR DBuser[80]; VARCHAR DBpswd[20]; VARCHAR sql_stmt[8192]; EXEC SQL END DECLARE SECTION; SQLDA *selda; int i; Výkonná část: EXEC SQL WHENEVER SQLERROR do gsSqlError(); strcpy(DBuser.arr,"TEST@GB001"); strcpy(DBpswd.arr,"TEST"); DBuser.len=strlen(DBuser.arr); DBpswd.len=strlen(DBpswd.arr); printf("connect\n"); EXEC SQL CONNECT :DBuser IDENTIFIED BY :DBpswd; srintf(stmtP,"DROP TABLE AUDIT"); strcpy(sql_stmt.arr,stmtP); sql_stmt.len=strlen(sql_stmt.arr); EXEC SQL PREPARE STMT FROM :sql_stmt; EXEC SQL EXECUTE STMT; PRO*C prekompilator: strcpy(sql_stmt.arr,stmtP); sql_stmt.len=strlen(sql_stmt.arr); /* EXEC SQL PREPARE STMT FROM :sql_stmt; */ { struct sqlexd sqlstm; sqlstm.sqlvsn = 10; sqlstm.sqhstv[0] = (void *)&sql_stmt; sqlstm.sqlest = (unsigned char *)&sqlca; sqlstm.sqlety = (unsigned short)256; sqlstm.occurs = (unsigned int )0; sqlstm.sqhstl[0] = (unsigned int )8194; sqlstm.sqhsts[0] = ( int )0; sqlstm.sqindv[0] = ( void *)0; sqlstm.sqinds[0] = ( int )0; sqlstm.sqharm[0] = (unsigned int )0; sqlstm.sqadto[0] = (unsigned short )0; sqlstm.sqtdso[0] = (unsigned short )0; sqlstm.sqphsv = sqlstm.sqhstv; sqlstm.sqphsl = sqlstm.sqhstl; sqlstm.sqphss = sqlstm.sqhsts; sqlstm.sqpind = sqlstm.sqindv; sqlstm.sqpins = sqlstm.sqinds; sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn); if (sqlca.sqlcode < 0) gsSqlError(); } /* EXEC SQL EXECUTE STMT; */ {struct sqlexd sqlstm; sqlstm.sqlvsn = 10; sqlstm.arrsiz = 4; sqlstm.sqladtp = &sqladt; sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn); if (sqlca.sqlcode < 0) gsSqlError(); } 21.2 Objektová rozhraní SQL, ADO.NET Výhoda: Moderní prostředí, relativní nezávislost na typu databáze (pokud používáme základ SQL). this.oracleConnection = new Oracle.DataAccess.Client.OracleConnection(); this.oracleConnection.ConnectionString = MyConnectString; this.oracleConnection.Open(); this.oracleConnection.SetCommand(sqlStmt); 21.3 Vázané proměnné (bind), obrana proti SQL injekcím Při vývoji aplikace je nutné dát si pozor na možné útoky, tzv. SQL injekce. V databázové schématu máme například tabulku, která obsluhuje přístupová práva: CREATE TABLE REMOTE_USERS ( USER_NAME VARCHAR2(64), USER_PSWD VARCHAR2(64) ); Aplikace potom "ověřuje" uživatele například takto: public bool Authenticate (string userName, string userPassword) { string sqlStmt = "SELECT COUNT(*) FROM REMOTE_USERS WHERE USER_NAME="' + userName + "' AND USER_PSWD="' + userPassword+ System.Data.SqlClient.SqlCommand command = new SqlCommand(sqlStmt); object o = command.ExecuteScalar(sqlStmt); return (Convert.ToInt32(o) == 1); } Útok: potom probíhá například takto: userName="SQL injekce" userPassword="je snadná' OR ROWNUM<'2" Výsledný dotaz do tabulky uživatelů vždy vrací hodnotu 1: SELECT COUNT(*) FROM REMOTE_USERS WHERE USER_NAME='SQL injekce' AND USER_PSWD='je snadná' OR ROWNUM<'2' Obrana: Použijeme tzv. vázané proměnné (bind), tj. využijeme možnosti nezávislého odeslání příkazu a jeho parametrů. public bool Authenticate (string userName, string userPassword) { string sqlStmt = "SELECT COUNT(*) FROM REMOTE_USERS "+ " WHERE USER_NAME=@a1 AND USER_PSWD=@a2"; System.Data.SqlClient.SqlCommand command = new SqlCommand(sqlStmt); command.Parameters.AddWithValue("@a1", userName); command.Parameters.AddWithValue("@a2", userPassword); object o = command.ExecuteScalar(sqlStmt); return (Convert.ToInt32(o) == 1); } 22 Normalizace a SQL 22.1 Nultá normální forma Žádné omezení (někdy se uvádí nutnost existence alespoň jednoho atributu, který může obsahovat více než jednu hodnotu, někdy se uvádí "entity jsou reprezentovány tabulkami, jejich atributy sloupci"). 22.2 První normální forma Všechny atributy tabulky jsou již dále nedělitelné, atomické. PARCELA KU# TYP# CISLO# PODLOMENI# VLASTNICI 523641 1 231 2 ID1,ID2,ID3 ... VLASTNÍK ID# JMENO T 5803042751 - nelze zaručit konzistenci databáze pomocí referenční integrity (lze ji však zajistit pomocí triggerů) - nelze efektivně indexovat - komplikované neefektivní SQL dotazy (i když jsou v principu možné) function vlast (VLASTNICI IN VARCHAR2,PORADI IN INT) RETURN INT; /* vrací jedno ID z řetězce PORADÍ) */ select ... from PARCELA A,VLASTNIK B where vlast (A.VLASTNICI,1)=B.ID union all select ... from PARCELA A,VLASTNIK B where vlast (A.VLASTNICI,2)=B.ID ... - Problém vymezení domén - je "rodné číslo" doména nebo se skládá ze den,mesic,rok,pohlavi,podlomeni...? Zásadně vždy dodržet !!! 22.3 Druhá normální forma Každá tabulka obsahuje primární klíč a každý neklíčový atribut je plně závislý na všech atributech tvořící primární klíč. OBEC ID OKRES# ID OBEC# POCET OBYV OBEC POCET OBYV OKRES 3702 l 398456 l456024 (není v 2. normální formě - pocet_obyv_okres je závislý na části klíče signalizuje existenci entity "okres") V zásadě není bezpodmínečně nutné dodržet (někdy kvůli výkonnosti opravdu nebývá dodržena - v některých případech se vyhneme join operaci), musíme dát pozor na: - existenci entit, jejichž existenci signalizuje podklíč denormalizovaných tabulek, který způsobuje porušení 2. normální formy. - zaručení konzistence atributů v denormalizované tabulce pomocí triggerů Někdy se jedná o netriviální systém triggerů viz. uvedený příklad: a) Změna počtu obyvatel v tabulce obec vyvolá trigger, který přepočítá P0CET_0BYV_0KRES v tabulce OKRES. b) Změna počtu obyvatel v tabulce okres se musí zpětně promítnout do tabulky obec . Uvedené nelze provádět řádkovými triggery - tabulka je měněna a nelze v ní provádět UPDATE a SELECT!!! 22.4 třetí normální forma Hodnoty atributů nejsou (funkčně) závislé na hodnotách jiných atributů. VLASTNIK ID# JMENO PRIJMENI RODNE CISLO POHLAVI ... 1 Drášil Milan S8C3C427S1 M 2 Drášilová Dominika 6SS21C4S31 Z (není v 3. Normální formě 3. cifra sloupce rodne_cislo je závislá na sloupci pohlaví) VLASTNIK ID# POHLAVI ROK N MESIC A DEN N RC 1 M 58 C3 C4 2751 2 Z 65 C2 1C 4531 U rozsáhlejších systémů téměř nelze dodržet - 3. Normální forma zakazuje redundanci dat. Ta bývá někdy i užitečná - rodné číslo může sloužit i ke kontrole správnosti pořízení data narození a pohlaví. Redundanci můžeme s klidným svědomím povolit, musíme však prostředky databáze zajistit její konsistenci (triggery,integritní omezení) alter table VLASTNÍK ADD constraint VLASTNIK_CH1 check ((POHLAVÍ in ('M','Z')) AND ( (POHLAVI='M' and SUBSTR(RC,3,1) IN ('0','1')) ) OR (POHLAVI='Z' and SUBSTR(RC,3,1) IN ('5','6')) )))