ČESKÉ VYSOKÉ UČENÍ TECHNICKÉ V PRAZEFakulta stavební
Katedra mapování a kartografie
Popis a využití technologií relačních databázív geoinformatice
Description and usage of relation database technologies ingeoinformatics
Bakalářská práce
Studijní program: Geodézie a kartografieStudijní obor: Geoinformatika
Vedoucí práce: Ing. Jan Pytel, Ph.D.
Jan Synekkvěten 2011
original zadani
ProhlášeníProhlašuji, že jsem předloženou práci vypracoval samostatně a že jsem uvedl veškeré
použité informační zdroje v souladu s Metodickým pokynem o etické přípravě vysokoškol-
ských závěrečných prací.
V Praze dne . . . . . . . . . . . . .
. . . . . . . . . . . . . . . .
Jan Synek
PoděkováníTouto cestou bych především rád poděkoval vedoucímu bakalářské práce Ing. Janu
Pytlovi, Ph.D. za možnost se věnovat právě těmto tématům. Dále bych mu chtěl poděkovat
za trpělivost, cenné rady a časté konzultace, které pro mě byly velkým přínosem. Díky této
práci a jeho vedení jsem se naučil nové a zajímavé věci.
Své rodině bych chtěl poděkovat za neustálou podporu během celého dosavadního stu-
dia. Tato podpora mi umožnila se plně soustředit pouze na studium.
AbstraktPráce je zaměřená na databáze, neoddělitelnou součást geoinformatiky. Podstatou práce
je popis vlastností jako jsou indexy, triggery, partitioning a zároveň popis přístupu k data-
bázi pomocí jednotlivých rozhraní jazyků Java a C++ . U jazyku Java se jedná o rozhraní
JDBC a pro C++ je to SQL modul Qt frameworku. Práce obsahuje řadu praktických uká-
zek SQL kódu. Vznikla řada ukázek kódu jak v jazyce Java tak v C++ . Na konci je uvedeno
porovnání obou rozhraní a aplikace demonstrující popisované technologie.
Klíčová slovaDatabáze SQL, indexy, partitioning, C++ , Java, Qt, JDBC.
AbstractThe thesis focuses on databases, integral component of the geoinformatics. The essence
of the thesis is a description of properties as indexes, triggers, partitioning, and also de-
scription of the access the database through various interfaces of the languages Java and
C++ . For Java there is JDBC and for C++ there is the SQL module of Qt framework.
The thesis contains many practical examples of SQL code. There was produced a number
of code samples in Java as well as in C++ . At the end of the thesis there is a comparison
of the two interfaces and applications that demonstrate the described technologies.
KeywordsDatabase SQL, indexes, partitioning, C++ , Java, Qt, JDBC.
i
Obsah
Seznam tabulek iv
Seznam obrázků v
Seznam zkratek vi
Úvod 1
1 Databáze 2
1.1 Základní databázové objekty . . . . . . . . . . . . . . . . . . . . . . . . . . 5
1.2 Indexy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
1.2.1 Exekuční plánování . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
1.2.2 Typy indexu dle použitého algoritmu . . . . . . . . . . . . . . . . . 9
1.2.3 Typy indexu dle indexovaných dat . . . . . . . . . . . . . . . . . . 10
1.2.4 Údržba indexu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
1.3 Triggery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
1.3.1 Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
1.3.2 Trigger funkce . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
1.4 Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
1.4.1 Inheritance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
1.4.2 Implementace partitioningu . . . . . . . . . . . . . . . . . . . . . . 19
1.4.3 Údržba partitioningu . . . . . . . . . . . . . . . . . . . . . . . . . . 21
1.5 Praktická část . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
1.5.1 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
1.5.2 Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
ii
OBSAH OBSAH
2 Rozhraní přístupu k databázi 30
2.1 JDBC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
2.1.1 Spojení . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
2.1.2 Výjimky . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
2.1.3 Objekt ResultSet . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
2.1.4 Transakce . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
2.1.5 PreparedStatement . . . . . . . . . . . . . . . . . . . . . . . . . . 37
2.1.6 Dávková modifikace . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
2.2 Qt . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
2.2.1 Spojení . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
2.2.2 Třída QSqlError . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
2.2.3 Objekt QSqlQuery . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
2.2.4 Transakce . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
2.2.5 Dávková modifikace . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
2.2.6 Třída QSqlRecord . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
2.2.7 Modelové třídy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
2.3 Porovnání . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
2.3.1 Rozdíly . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
2.3.2 Shrnutí . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
3 Aplikace 55
Závěr 61
Použité zdroje 62
A Oracle functionality I
B ACID III
B.1 Atomicity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . III
B.2 Consistency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . IV
B.3 Isolation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . IV
B.4 Durability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . V
C Exekuční plány VI
iii
Seznam tabulek
1.1 Příkazy jazyka SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
1.2 Datové typy jazyka SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
1.3 Nejznámější databázové systémy . . . . . . . . . . . . . . . . . . . . . . . . 4
1.4 Varianty příkazu REINDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
1.5 Proměnné v PL/pgSQL funkcích . . . . . . . . . . . . . . . . . . . . . . . 15
1.6 Možné formy partitioningu . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
2.1 Rozhraní balíčku java.sql . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
2.2 Vybraná URL používaná v JDBC . . . . . . . . . . . . . . . . . . . . . . . 32
2.3 Metody třídy SQLException . . . . . . . . . . . . . . . . . . . . . . . . . . 33
2.4 Metody třídy ResultSet pro posun kurzoru . . . . . . . . . . . . . . . . . 35
2.5 Vybrané moduly Qt frameworku . . . . . . . . . . . . . . . . . . . . . . . . 40
2.6 Ovladače podporovaných databází v Qt . . . . . . . . . . . . . . . . . . . . 42
2.7 Výčtový typ enum QSqlError::ErrorType . . . . . . . . . . . . . . . . . . 43
2.8 Funkce třídy QSqlQuery pro posun kurzoru . . . . . . . . . . . . . . . . . . 44
2.9 Členské funkce třídy QSqlRecord . . . . . . . . . . . . . . . . . . . . . . . 47
2.10 Modelové třídy v Qt . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
2.11 Výčtový typ enum QSqlTableModel::EditStrategy . . . . . . . . . . . . 49
2.12 Shrnutí rozhraní JDBC a Qt . . . . . . . . . . . . . . . . . . . . . . . . . . 54
3.1 Porovnání časů jednotlivých aplikací . . . . . . . . . . . . . . . . . . . . . 60
A.1 Orafce: Funkce pro práci s typem date . . . . . . . . . . . . . . . . . . . . I
A.2 Orafce: Funkce pro práci s řetězci . . . . . . . . . . . . . . . . . . . . . . . II
A.3 Orafce: Ostatní funkce . . . . . . . . . . . . . . . . . . . . . . . . . . . . . II
B.1 Isolation levels a typy čtení . . . . . . . . . . . . . . . . . . . . . . . . . . V
iv
Seznam obrázků
3.1 Ukázka aplikace napsané v Javě . . . . . . . . . . . . . . . . . . . . . . . . 56
3.2 Zobrazení výsledku Java aplikace . . . . . . . . . . . . . . . . . . . . . . . 57
3.3 Ukázka aplikace napsané v C++ . . . . . . . . . . . . . . . . . . . . . . . 58
3.4 Zobrazení výsledku C++ aplikace . . . . . . . . . . . . . . . . . . . . . . . 59
v
Seznam zkratek
ACID Atomicity, Consistency, Isolation, Durability
ANSI American National Standards Institute
API Application Programming Interface
DBMS DataBase Management System
DCL Data Control Language
DDL Data Definition Language
DML Data Manipulation Language
GIN Generalized Inverted Index
GiST Generalized Search Tree
GUI Graphical User Interface
IBM International Business Machines
ISO International Organization for Standardization
Java SE Java Standard Edition
JDBC Java DataBase Connectivity
JDK Java Development Kit
ODBC Open DataBase Connectivity
RDBMS Relational DataBase Management System
SEQUEL Structured English QUEry Language
SQL Structured Query Language
SŘBD Systém řízení báze dat
S-JTSK Systém jednotné trigonometrické sítě katastrální
TCL Transaction Control Language
Xt X toolkit
vi
Úvod
Cílem této bakalářské práce je vytvořit rešerši popisující technologie databázových sys-
témů. V dnešní době, kdy je potřeba zpracovávat velká množství dat, jsou databázové sys-
témy nedílnou součástí života. Není tomu jinak ani v geoinformatice. Laserové skenování
může býti dobrým příkladem. Současným trendem je tvorba projektů globálních měřítek
typu Google Maps, které obsahují nespočet dat. Tato data je nutné nějak uchovávat a
třídit. Databázové systémy jsou pro tyto případy vhodným řešením.
Protože je nutné data z databází získávat co nejrychleji, vzala si tato práce za cíl popsat
technologie, které tyto úkony urychlují. Technologie indexace a partitioningu je v této práci
popisována na databázovém systému PostgreSQL. PostgreSQL byl vybrán z toho důvodu,
že je v dnešní době nejvíce propracovaným open–source databázovým systémem na trhu.
Práce obsahuje tvorbu a použití indexů, partitioningu a další pro ně nezbytné součásti,
jako jsou například triggery. V závěru první části je uveden příklad implementace obou
technologií na vzorku geodetických dat.
Druhá část pojednává o tom, jak lze k datům databázového systému přistupovat z pro-
středí aplikací. Pro tvorbu aplikací jsou vybrány programovací jazyky Java a C++ . V textu
jsou popsány rozhraní těchto jazyků, které komunikují s databázemi. Pro jazyk Java je to
nativní rozhraní JDBC, pro jazyk C++ bylo vybráno rozhraní, které poskytuje Qt fra-
mework. V závěru kapitoly je uvedeno porovnání obou zmíněných rozhraní.
Poslední kapitola spojuje všechny popsané technologie dohromady. Za tímto účelem
jsou vytvořeny dvě testovací GUI aplikace, které komunikují s databází a testují rychlosti,
jak databázového systému, tak použitých rozhraní. Celá práce obsahuje řadu praktických
ukázek kódů, na nichž jsou technologie vysvětlovány.
1
Kapitola 1
Databáze
Databáze je systém určený k získávání, organizování a ukládání dat na paměťové mé-
dium. Paměťovým médiem není myšlena pouze digitální forma, i když je dnes nejrozšíře-
nější, ale jakákoliv věc, na kterou se dají zaznamenávat informace (např. papír).[1]
Historie
Za předchůdce dnešních databází by se daly považovat papírové kartotéky. Umožňovaly
uspořádání dat dle různých kritérií a zatřiďování nových položek. Správa takových kartoték
byla velmi podobná správě dnešních databází. Dalším krokem byl přenos zpracování dat
na stroje. Paměťovým médiem byl tehdy děrný štítek a zpracování probíhalo na elektro-
mechanických strojích. Velkým přínosem byl vývoj počítačů. V šedesátých letech minulého
století začaly vznikat první síťové DBMS1. V této době byly také vyvíjeny hierarchické
databáze. V roce 1970 Edgar F. Codd, britský informatik pracující tehdy ve firmě IBM,
uvedl v článku „A Relational Model of Data for Large Shared Data Banksÿ principy relač-
ního modelu, který pohlíží na databázi jako na soubor tabulek. Tato technologie přinesla
výkonově srovnatelné systémy se síťovými a hierarchickými databázemi. V průběhu sedm-
desátých let byla vyvinuta první verze dotazovacího jazyka SQL. V devadesátých letech 20.
století se začaly objevovat první objektově orientované databáze, které vycházely z filozofie
objektově orientovaných jazyků. Předpokládalo se, že nahradí v té době nejvíce používaný
relační model. Tyto předpovědi se však nenaplnily a vznikl kompromisní objektově relační
model databází.[1]
Z hlediska ukládání dat a vazeb mezi nimi lze databáze rozdělit na tyto modely:
1V české literatuře se často používá termín SŘBD
2
KAPITOLA 1. DATABÁZE
• Síťový model
• Hierarchický model
• Relační model
• Objektově orientovaný model
• Objektově relační model
Dotazovací jazyk SQL
SQL je zkratkou anglického slovního spojení Structured Query Language (struktu-
rovaný dotazovací jazyk). Je to standardizovaný dotazovací jazyk vyšší úrovně používaný
pro práci v relačních databázích. Je nejrozšířenějším jazykem používaným v současných
databázových systémech.
SQL byl vyvinut firmou IBM na počátku sedmdesátých let minulého století pod názvem
SEQUEL a byl použit v projektu System R. Cílem bylo vytvořit takový jazyk, aby se co
nejvíce blížil syntaxi přirozeného jazyka (angličtina). Později se k vývoji připojily další
firmy např. Relational Software, Inc. (dnešní Oracle Corporation). V roce 1986 resp. 1987
byl poprvé standardizován institucí ANSI resp. ISO. Do dnešní doby bylo provedeno několik
dalších aktualizací těchto norem. Prakticky všechny relační databáze dnešní doby podporují
tyto standardy a zároveň přidávají i svoje prvky, proto je přenositelnost omezena. V tabulce
1.1 jsou uvedeny rozděleny příkazy jazyka SQL podporované standardem. Některé datové
typy jazyka SQL jsou uvedeny v tabulce 1.2.[1]
Tabulka 1.1: Příkazy jazyka SQL
Zkratka Popis Příkazy
DML příkazy pro manipulaci s daty SELECT, UPDATE, INSERT, . . .DDL příkazy pro definici dat CREATE, ALTER, DROP, . . .DCL příkazy pro řízení přístupových práv GRANT, REVOKETCL příkazy pro řízení transakcí COMMIT, ROLLBACK, . . .
Database Management System
DBMS je software, který provádí tvorbu, uložení, údržbu a užití databáze a tvoří roz-
hraní mezi uloženými daty a aplikačními programy. Tento systém přijímá požadavek pro-
3
KAPITOLA 1. DATABÁZE
Tabulka 1.2: Datové typy jazyka SQL
Popis Datové typy
Znakové datové typy CHAR(n), VARCHAR(n), . . .Bitové datové typy BIT(n), . . .Číselné datové typy INTEGER, REAL, . . .Časové datové typy DATE, TIMESTAMP, . . .
střednictvím instrukcí nějakého dotazovacího jazyku, zpracuje požadavek a vrátí dotazo-
vaná data. Často je slovem databáze myšlen právě DBMS. V tabulce 1.3 jsou uvedeny
nejznámější DBMS současnosti. Poslední dvě uvedené databáze jsou zajímavými projekty,
ale nedosahují funkcionality pěti předešlých. [1]
Tabulka 1.3: Nejznámější databázové systémy
DBMS URL
Oracle Database v dnešní době zřejmě nejrozšířenější komerční objektově relačnídatabázový systém
PostgreSQL vynikající open–source objektově relační databázový systém, tímtoDBMS se budeme zabývat v této práci
IBM DB2 relační databázový systém vyvíjený firmou IBMMySQL open-source relační databázový systém nejčastěji využívaný v in-
ternetových aplikacíchMicrosoft SQL Server databázové řešení firmy Microsoft
Apache Derby open-source databázový systém napsaný kompletně v JavěSQLite relativně malá knihovna v C šířená pod licencí public domain,
kterou lze snadno připojit k aplikaci pomocí
PostgreSQL je mocný, open–source, objektově relační databázový systém. Vývoj trvá
už přes 15 let a je veden početnou komunitou vývojářů po celém světě. Běží na všech
předních operačních systémech zahrnující Linux, UNIX (Mac OS X, BSD, Solaris, . . .) a
Windows. Plně podporuje cizí klíče, joiny, pohledy a uložené procedury(v několika jazy-
cích). Zahrnuje většinu standardizovaných datových typů jako je INTEGER, NUMERIC,
VARCHAR, DATE a TIMESTAMP. Také podporuje ukládání velkých binárních dat (ob-
rázky, zvuk nebo video atd.). Obsahuje nativní rozhraní pro Java, C/C++ , Python, .NET,
ODBC a další.[2]
4
1.1. ZÁKLADNÍ DATABÁZOVÉ OBJEKTY KAPITOLA 1. DATABÁZE
1.1 Základní databázové objekty
Každý relační databázový systém poskytuje sadu databázových objektů (entit). Exis-
tuje jich celá řada, ale pro pochopení tohoto textu byly vybrány tyto:
• Tabulka
• Pohled
• Index
• Trigger
• Procedura
Tabulka (Table)
Tabulka je hlavním databázovým objektem v relačních databázích. Databázovou ta-
bulku je možné si představit jako dvourozměrnou tabulku, která se skládá z řádků a pevně
daného počtu sloupců. Každý sloupec má definován svůj vlastní datový typ. Nemůže se
tedy stát, že v rámci jednoho sloupce bude jednou číslo a podruhé třeba datum. Tabulky
lze vytvářet, měnit a mazat pomocí příkazů DDL SQL. Počet řádků v tabulce je omezen
pouze technickými možnostmi databázového systému nebo serveru. Data nejsou v samotné
tabulce nijak setříděna. Nové řádky se zpravidla vytvářejí za posledním známým zázna-
mem.
Pohled (View)
View je dalším databázovým objektem. Svým chováním připomíná tabulku, ale ne-
obsahuje žádná data. Obsahuje pouze předpis, jak mají být data získána z tabulek. Lze
si to představit jako souhrn příkazů SELECT. Z toho důvodu pohledy nezabírají téměř
žádné místo. Data poskytovaná pohledy nelze přímo modifikovat, jako je to u tabulek.
Musí se modifikovat v tabulkách, ze kterých jsou pohledy vytvořeny. Nevýhodou je, že
některé zejména složité dotazy nad komplikovaně vytvořenými pohledy mohou být časově
náročnější.2 Pohledy se vytváří obdobným způsobem jako tabulky pomocí DDL SQL.
2Oracle jako první představil tzv. materializované pohledy, které obsahují data. Jedná se o lokální kopiedat z výsledku agregace jiných tabulek
5
1.2. INDEXY KAPITOLA 1. DATABÁZE
Index
Index je konstrukce, která zrychluje dotazování v databázi. Zrychlení spočívá ve vy-
tvoření B-tree pro určitý klíčový prvek (obecně podle jednoho nebo více sloupců). Existují
i jiné varianty, o kterých bude více řečeno v následující sekci. Index sice zrychlí dotazy,
ale je nutné jej tvořit s opatrností, protože zpomaluje modifikaci dat. Musejí se při každé
změně dat zaktualizovat, a to zatěžuje databázový systém.
Trigger
Triggery jsou databázové objekty, které reagují na nějakou událost. Mohou být vyvo-
lány například DML nebo DDL operacemi. Obecně by se dalo říct, že reagují na všechny
události, které se mohou objevit při provozu databáze. Triggerům bude věnována jedna z
dalších sekcí, kde budou popsány podrobněji.
Procedura (Procedure)
Procedura je objekt, který neobsahuje žádná data, ale funkci, která se nad daty v data-
bázi má vykonat. Bývá psána v procedurálním jazyce zpravidla specifickém pro konkretní
databázový systém. Některé DBMS poskytují podporu uložených procedur psaných v kla-
sických programovacích jazycích jako například Java, C++ a další. Obvykle bývá spuštěna
právě triggerem.[1]
1.2 Indexy
Indexy jsou obvyklý způsob jak zvýšit výkon databáze. Umožňují databázovému sys-
tému najít vybraná data mnohem rychleji, než by to zvládl bez indexů. Při vykonání dotazu
databázový systém projede sekvenčně data vyhovující požadavku. Pokud však je tabulka
rozsáhlá a vyhovujících řádků je naopak málo nebo žádné, je tato metoda velice pomalá a je
vhodné použít indexy. Vytvořením indexu se vytvoří objekt, který v sobě uchovává data
seřazená podle klíče, který většinou představuje sloupec, nad nímž si návrhář databáze
přeje urychlit dotazy. Tento objekt zabírá nemalý diskový prostor zejména v případě, kdy
jsou objemné tabulky. V některých případech můžou indexy zabírat větší část úložného
prostoru než data samotná, zejména v případě použití více indexů na tabulce. Toto je
jeden z důvodů, proč je nutné volit indexy s obezřetností.
6
1.2. INDEXY KAPITOLA 1. DATABÁZE
Standard SQL se tvorbě a údržbě indexů nevěnuje. Správa indexů je většinou specifická
pro každý databázový systém. V PostgreSQL se index vytváří pomocí této syntaxe:
CREATE INDEX nazev_indexu ON tabulka (sloupec);
Tvorba samotného indexu je časově náročná (závisí na množství dat). Během této
doby PostgreSQL implicitně povoluje číst (provádět SELECT), ale blokuje zapisování.
Tedy nelze modifikovat tabulku pomocí INSERT,UPDATE a DELETE příkazů. To je v
ostrém nasazení často velmi nevhodné, proto PostgreSQL za určitých podmínek povoluje
tvorbu indexu a zápis současně. Tím se ale v tomto textu zabývat nebudeme.
Jakmile je index vytvořen, dále není nutné se o něj starat. PostgreSQL si jej aktualizuje
při změně dat v tabulce. To vyžaduje jistou režii. DML operace jsou v tomto případě poma-
lejší. O to, kdy použít nebo nepoužít index při dotazování, se stará tzv. planner. Planner
sestavuje exekuční plán na základě analýzy obsahu (statistik) tabulky. Tyto statistiky by
se měly udržovat aktuální. Podrobněji níže. [2]
1.2.1 Exekuční plánování
Každý SQL dotaz je nejprve zkontrolován parserem, zda neobsahuje syntaktické chyby.
Poté je analyzován plannerem, jenž sestaví optimální exekuční plán a předá ho executorovi,
který dotaz vykoná. Aby se mohl planner správně rozhodnout, musí znát strukturu tabulek.
Pokud planner nemá aktuální statistiky, nemůže sestavit nejrychlejší exekuční plán. Tato
statistika se analyzuje příkazem ANALYZE
ANALYZE tabulka (sloupec);
Pokud je ANALYZE uveden samostatně, jsou aktualizovány statistiky pro všechny
tabulky v databázi. Je možné analyzovat jednotlivé tabulky dokonce i jednotlivé sloupce
v tabulce přidáním názvu tabulky, případně sloupce za klíčové slovo ANALYZE.
Tyto statistiky jsou uchovávány v systémovém katalogu pg_statistic. Měly by být
aktualizovány pravidelně a po rozsáhlejších změnách v tabulce. V PostgreSQL se o au-
tomatickou aktualizaci statistik stará The Autovacuum Daemon. Pokud je tento démon
zastaven, je dobré pouštět ANALYZE pravidelně a nejlépe v dobách malé zátěže systému.
Z časových důvodů se ve velkých tabulkách analyzuje pouze náhodný vzorek dat. Z tohoto
důvodu se mohou statistiky mírně lišit při každém spuštění ANALYZE, i když se obsah
tabulky nezměnil.
7
1.2. INDEXY KAPITOLA 1. DATABÁZE
Výpis exekučního plánu poskytovaný plannerem pro jednotlivé dotazy lze vypsat po-
mocí klíčového slova EXPLAIN, které se uvede před dotaz. Exekuční plán je nejefektivnější
soupis operací, které plánuje databázový systém vykonat. Tyto operace zahrnují mimo jiné
i použité scany. Základní syntax je:
EXPLAIN SELECT * FROM points WHERE y = 45.7;
V tomto případě planner poskytne tento plán:
QUERY PLAN
----------------------------------------------------------
Seq Scan on points (cost=0.00..1791.00 rows=1 width=16)
Filter: (y = 45.7::double precision)
(2 rows)
Z toho výpisu je zřejmé, že byl použit sekvenční scan na tabulce points. Hodnota „costÿ
v závorce uvádí dvě hodnoty. První je odhadovaná doba, kdy by byl vrácen první řádek
výsledku, a druhá hodnota je odhadovaná doba, kdy by byly vráceny všechny řádky. V
tomto případě je doba měřená v jednotkách, kde jedna jednotka se rovná jednomu sekvenč-
nímu čtení jedné stránky (disk page fetch). Dále pak předpokládaný počet řádků výstupu a
průměrná šířka řádku v bajtech. Pokud se uvede pouze EXPLAIN, zobrazí pouze exekuční
plán, aniž by se dotaz skutečně vykonal. Proto se jedná pouze o odhadované hodnoty,
které jsou získány pravě ze statistik získaných pomocí ANALYZE. Pokud se však uvede za
EXPLAIN klíčové slovo ANALYZE, dotaz se provede a potom se zobrazí exekuční plán se
skutečnou dobou běhu.
CREATE INDEX points_y_index ON points (y);
EXPLAIN ANALYZE SELECT * FROM points WHERE y = 45.7;
Nejprve byl vytvořen index nad sloupcem y a poté byl analyzován dotaz, který byl
zároveň proveden. Exekuční plán by vypadal takto:
QUERY PLAN
-----------------------------------------------------------------------------
Index Scan using points_y_index on points (cost=0.00..8.28 rows=1 width=16)
(actual time=0.025..0.025 rows=0 loops=1)
Index Cond: (y = 45.7::double precision)
Total runtime: 0.071 ms
(3 rows)
8
1.2. INDEXY KAPITOLA 1. DATABÁZE
V tomto případě planner vybral jako rychlejší variantu použití index scanu. Protože
se dotaz vykonal, v exekučním plánu se objevily další hodnoty, které udávají skutečnou
situaci běhu dotazu. „Actual timeÿ uvádí podobné informace jako hodnota „costÿ, ale teď
už skutečný čas v milisekundách. Dále pak skutečný počet řádků výsledku a počet cyklů.
„Total runtimeÿ uvádí celkový čas doby běhu dotazu.[2]
1.2.2 Typy indexu dle použitého algoritmu
PostgreSQL nabízí několik různých typů indexu, které se liší použitým algoritmem.
Každý algoritmus je stavěný na jiný typ dotazu. Jsou to tyto čtyři:
• B-tree
• Hash
• GiST
• GIN
Implicitně je vytvářen B-tree index, který je nejvhodnější v běžných dotazech. Je možné
však explicitně nastavit při tvorbě jiný typ algoritmu tak, že za klíčovým slovem USING
se uvede jméno typu indexu:
CREATE INDEX nazev_indexu ON tabulka USING hash (sloupec);
B-tree
Tento index urychluje operace na datech, které jsou v nějakém rozmezí nebo rov-
nosti a lze je řadit. PostgreSQL planner zvažuje využití indexu, kdykoliv se v dotazu
objevují tyto operátory pro srovnání:
• <
• <=
• =
• >=
• >
9
1.2. INDEXY KAPITOLA 1. DATABÁZE
Je vhodný i pro dotazy obsahující klíčová slova BETWEEN a IN, která představujíc kom-
binace výše uvedených operátorů, stejně tak pro dotazy, kde se vyskytuje operátor IS
NULL, tak i pro dotazy využívající operátor LIKE, kde je vzor ukotven na začátek ře-
tězce. Lze použít pro sloupec LIKE ’foo%’, ale ne pro sloupec LIKE ’%foo’. B-tree
implicitně uchovává svoje záznamy seřazené vzestupně s NULL hodnotami nakonec. Tyto
indexy mohou být skenovány vpřed i vzad a poskytovat tak výstup, jako seřazení pomocí
ORDER BY. Je možné explicitně nařídit, aby se index například vytvořil se záznamy se-
řazenými sestupně a s NULL na konci. Toho se dá docílit přidáním DESC a NULLS LAST
do příkazu vytvoření indexu:
CREATE INDEX tabulka_sloupec_index ON tabulka (sloupec DESC NULLS LAST);
Hash
Hash index zvládá pouze jednoduché rovnosti. Planner zváží použití indexu pouze v
případě užití operátoru =. Tento index není ve většině případech doporučován.
GiST
GiST je soubor několika různých indexovacích strategií zahrnujících např. B-stromy
nebo R-stromy. Jsou hojně využívány pro urychlení operací s dvoudimenzionálními geo-
metrickými datovými typy. Například při práci s PostGIS3.
GIN
GIN indexy jsou rovněž souborem různých indexovacích strategií jako je to v případě
GiST. Volená strategie závisí na použitém operátoru. Jsou vhodné pro práci s hodnotami
obsahující více než jeden klíč (např. pole).[2]
1.2.3 Typy indexu dle indexovaných dat
PostgreSQL nabízí několik možností, jak index využít. Kromě jednoduchých indexů pro
jeden sloupec, které už byly představeny, jsou ještě další možnosti:
• vícesloupcové indexy (multicolumn)
• unique indexy
3nadstavba PostgreSQL umožňující podporu geografických objektů
10
1.2. INDEXY KAPITOLA 1. DATABÁZE
• funkční indexy (Indexes on Expressions)
• částečné indexy (partial)
Vícesloupcové indexy
Jeden index může být vytvořen nad více než jedním sloupcem v tabulce, maximálně
32 sloupců. V současnosti pouze B-tree, GiST a GIN typy podporují vícesloupcové in-
dexy. Tyto indexy jsou vhodné, pokud dotazování zahrnuje více sloupců, jak je uvedeno v
následujícím příkladu pro B-tree index:
SELECT * FROM table WHERE a = constant AND b = constant ;
Pokud jen tento typ dotazování častý, je vhodné použít vícesloupcový index:
CREATE INDEX table_ab_index ON table (a, b);
Samozřejmě, že se v klauzuli WHERE musí nacházet ty operátory, které jsou pod-
porovány jednotlivými typy indexů. Není doporučováno používat indexy pro více jak tři
sloupce. Jednoduché indexy jsou ve většině případů vhodnější díky menším nárokům na
čas a místo na disku. Pokud je použit operátor OR, nebude tento typ indexu uplatněn.
Unique indexy
Indexy mohou být také použity k zajištění jedinečnosti ve sloupcích. V současné době
pouze B-tree indexy podporují deklaraci UNIQUE. Oproti klasické syntaxi se syntax unique
indexu liší pouze přidáním klíčového slova UNIQUE:
CREATE UNIQUE INDEX nazev_indexu ON tabulka (sloupec);
Pokud sloupec obsahuje duplicitní data, nelze tento typ indexu vytvořit. PostgreSQL
automaticky vytváří unique index při tvorbě tabulky definováním primárního klíče nebo
UNIQUE.
Funkční indexy
Index může být i funkcí nebo skalárním výrazem jednoho či více sloupců. Tyto indexy
jsou užitečné v případě, když se často dotazuje na výsledky těchto funkcí nebo výrazů. Jak
již bylo dříve uvedeno, index si neumí poradit s dotazem typu: sloupec LIKE ’%foo’. Lze
to ale vyřešit pomocí funkčního indexu a funkce reverse() z balíku orafce viz příloha A,
která umí zrcadlově převrátit řetězec. Místo dotazu:
11
1.2. INDEXY KAPITOLA 1. DATABÁZE
SELECT * FROM tabulka WHERE sloupec LIKE ’%foo’;
By se používal dotaz se stejným výsledkem:
SELECT * FROM tabulka WHERE sloupec LIKE reverse(’oof%’);
V tomto dotazu se už má index za co „chytitÿ, proto je vhodné použít index. Pro
zrychlení by se tedy hodil funkční index, který by mohl vypadat takto:
CREATE INDEX tabulka_reverse_index ON tabulka (reverse(sloupec));
Funkční indexy jsou nákladné na údržbu, zvláště pro složité funkce, protože tyto výrazy
musejí být přepočítány pro každý řádek, kdykoliv je něco vloženo nebo změněno. Proto
jsou tyto indexy vhodné jen v případě, kdy je rychlost hledání důležitější než rychlost
vkládání.
Částečné indexy
Částečné neboli partial indexy jsou indexy, které se tvoří jen nad určitou částí dat.
Zpravidla to bývá ta část, která je nejvíce využívána v dotazech. Pokud jsou data vý-
hradně vybírána jen z malé části tabulky, je vhodné tuto část zindexovat právě částečným
indexem. Tyto indexy pak zabírají mnohem méně místa, než by zabíraly indexy pro celou
tabulku. Protože jsou indexy menší, zrychlí se i dotazy, které tyto indexy využívají. Také
se urychlí změna dat v tabulce, protože není ve všech případech nutné aktualizovat index.
Příkladem může být tabulka katastrálních map, která mimo jiné uvádí, v jakém jsou zob-
razení jednotlivé mapy. Dotazy směřují hlavně na mapy, které ještě nejsou převedeny do
Křovákova zobrazení. Například:
SELECT * FROM maps
WHERE NOT projection = ’krovak’ AND id < 1000;
Potom by index, který by zvyšoval výkon, mohl vypadat takto:
CREATE INDEX maps_projection_index ON maps (id)
WHERE NOT projection = ’krovak’;
Aby částečné indexy byly přínosem, je nutné znát, jak indexy pracují a také jak vy-
hodnocuje planner. V opačném případě může být přínos minimální, ne-li žádný.[2]
12
1.3. TRIGGERY KAPITOLA 1. DATABÁZE
1.2.4 Údržba indexu
Jak již bylo řečeno, PostgreSQL si sám aktualizuje indexy. Z toho důvodu není ve
většině případů nutné se o indexy nadále starat. V některých případech se však může stát,
že se index poškodí. Například kvůli hardwarovým výpadkům nebo softwarovým chybám.
V této situaci PostgreSQL nabízí příkaz REINDEX, který přetvoří poškozený index. V
tabulce 1.4 jsou uvedeny varianty tohoto příkazu
Tabulka 1.4: Varianty příkazu REINDEX
Příkaz Popis
REINDEX INDEX nazev_indexu přetvoří jeden specifikovaný indexREINDEX TABLE nazev_tabulky přetvoří všechny indexy v uvedené tabulceREINDEX DATABASE nazev_databaze přetvoří všechny indexy v aktuální databáziREINDEX SYSTEM nazev_databaze přetvoří všechny indexy systémových katalogů
Pokud již není nějaký index používán, měl by se smazat, aby zbytečně nezpomaloval
systém. Index se maže podobným příkazem jako obyčejná tabulka:[2]
DROP INDEX nazev_indexu;
1.3 Triggery
1.3.1 Trigger
Trigger je objekt, který může spustit nějakou funkci (proceduru), kdykoliv je provedena
nějaká operace, pro kterou je trigger definován. Trigger může být definován tak, aby vykonal
zadanou funkci před nebo až po operacích INSERT, UPDATE, DELETE. Stejně tak může
být definován, aby tuto funkci vykonal jednou pro každý modifikovaný řádek (per-row
trigger) nebo jen jednou za dotaz (per-statement trigger). Triggery jsou součástí SQL
standardu.
Aby mohl být vytvořen trigger, musí se nejprve vytvořit trigger funkce, kterou trigger
volá. Jakmile je trigger funkce vytvořena, trigger může být vytvořen pomocí CREATE
TRIGGER:
CREATE TRIGGER nazev_triggeru { BEFORE | AFTER } { udalost [ OR ... ] }
ON nazev_tabulky FOR EACH { ROW | STATEMENT }
EXECUTE PROCEDURE nazev_funkce( argumenty );
13
1.3. TRIGGERY KAPITOLA 1. DATABÁZE
Trigger je pak přidružen k tabulce, pro kterou byl vytvořen podobně jako index. Po-
kud je tedy smazána tabulka, jsou smazány i triggery, které jsou pro ni vytvořeny. Jak již
bylo řečeno, trigger může spouštět funkci před (klíčové slovo BEFORE) nebo po (klíčové
slovo AFTER) vykonání dotazu. Za tím následuje jedna z událostí INSERT, UPDATE,
DELETE nebo TRUNCATE, které spustí trigger. Je možné specifikovat více událostí na-
jednou pomocí OR.
Za klíčovým slovem ON následuje název tabulky. Trigger (per-row), který je ozna-
čen FOR EACH ROW, je volán pro každý řádek, který je dotazem modifikován. Trigger
(per-statement), který obsahuje FOR EACH STATEMENT, je naopak spouštěn pouze jed-
nou pro daný dotaz nehledě na počet modifikovaných řádků. Následuje EXECUTE PRO-
CEDURE a jméno trigger funkce, kterou trigger volá. Trigger pro TRUNCATE operaci je
volán pouze pro celý dotaz.
Pokud je definováno více triggerů pro stejnou událost ve stejné tabulce, tyto triggery
budou volány podle abecedního pořadí. Trigger může spouštět v některých případech sám
sebe. Například pokud insert trigger vyvolá další insert ve stejné tabulce, což způsobí, že
se tento trigger spustí znovu. V takovém případě se jedná o nekonečnou rekurzi a designér
databáze by se jí měl vyhnout. [2]
Příkladem jednoduchého triggeru může být trigger, který spouští funkci, která aktua-
lizuje čas poslední změny:
CREATE TRIGGER students_insert_trigger
BEFORE INSERT OR UPDATE
ON students FOR EACH ROW
EXECUTE PROCEDURE students_time_function();
Nepotřebné triggery se mažou pomocí:
DROP TRIGGER nazev_triggeru ON nazev_tabulky;
1.3.2 Trigger funkce
Trigger funkce jsou funkce, které volá trigger, aby provedly nějakou operaci. Musejí být
definovány před vytvořením triggerů. Stejná trigger funkce může být použita pro více trig-
gerů. Trigger funkce musí být deklarovány jako funkce bez argumentu a vracející typ trigger.
Funkce musejí být deklarovány bez argumentu, i když se očekávají argumenty specifikované
v CREATE TRIGGER. Tyto argumenty jsou předány prostřednictvím TG ARGV.
14
1.3. TRIGGERY KAPITOLA 1. DATABÁZE
Trigger funkce volané per-statement triggery by měly vždy vracet NULL. Trigger funkce
volané per-row triggery mají dvě možnosti. Buď vrátí NULL a tím tak přeskočí operaci pro
aktuální řádek, nebo vrátí řádek, který bude vložen nebo modifikován. Návratová hodnota
per-row triggerů obsahující AFTER je ignorována a tedy vrací NULL.
Základní syntaxe vytvoření trigger funkce:
CREATE FUNCTION nazev_funkce()
RETURNS trigger AS $$
BEGIN
[...]
END;
$$
LANGUAGE jmeno_jazyka;
PostgreSQL nabízí možnost psát trigger funkce v C nebo v procedurálních jazycích –
PL/pgSQL, PL/Tcl, PL/Perl a PL/Python. Je na každém, který jazyk zvolí. Pro tuto práci
byl zvolen procedurální jazyk PL/pgSQL. Proměnné nacházející se v PL/pgSQL funkcích
jsou uvedeny v tabulce 1.5.
Tabulka 1.5: Proměnné v PL/pgSQL funkcích
Proměnná Popis
NEW proměnná obsahující nově vkládaný řádek při INSERT nebo UPDATEu per-row triggerů, u per-statement triggerů je tato hodnota NULL.
OLD proměnná obsahující řádek ještě před změnou při INSERT nebo UP-DATE u per-row triggerů, u per-statement triggerů je tato hodnotaNULL.
TG NAME proměnná, která obsahuje název triggeru, který funkci spustilTG WHEN obsahuje řetězec ’BEFORE’ nebo ’AFTER’ v závislosti na použitém
triggeruTG LEVEL obsahuje řetězec ’ROW’ nebo ’STATEMENT’ v závislosti na použitém
triggeruTG OP obsahuje řetězec ’INSERT’, ’UPDATE’ nebo ’DELETE’ v závislosti na
použitém triggeruTG RELNAME obsahuje název tabulky, která spustila trigger
TG ARGV obsahuje pole argumentů definovaných v triggeru
Příklad trigger funkce pro vkládání času změny a počtu změn:
CREATE OR REPLACE FUNCTION students_time_function()
15
1.4. PARTITIONING KAPITOLA 1. DATABÁZE
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = ’INSERT’ THEN
NEW.modified = current_timestamp;
NEW.count = 1;
END IF;
IF TG_OP = ’UPDATE’ THEN
NEW.modified = current_timestamp;
NEW.count = OLD.count + 1;
END IF;
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
Protože jsou triggery závislé na trigger funkcích, není možné smazat trigger funkci,
dokud nejsou smazány všechny triggery, které na trigger funkci závisí. Lze smazat funkci
společně s jejími triggery přidáním klíčového slova CASCADE. Mazání trigger funkcí se
provádí pomocí DROP FUNCTION:[2]
DROP FUNCTION nazev_funkce() [ CASCADE ];
1.4 Partitioning
Partitioning je další způsob jak zvýšit výkon databáze. V této sekci bude popsáno, proč
a jak implementovat partitioning v PostgreSQL. Partitioning se týká rozdělení jedné velké
tabulky na několik menších částí (partitions). V PostgreSQL je možné vytvořit dvě formy
partitioningu, jsou popsány v tabulce 1.6.
Tabulka 1.6: Možné formy partitioningu
Varianta Popis
Range Partitioning tabulka je rozdělena na několik menších podle rozsahu klíčového sloupce.Při tomto typu se nesmí vytvořit žádné překryty. To znamená, že senesmí ve dvou nebo více tabulkách objevit stejného hodnoty v klíčovémsloupci. Například rozdělení podle data narození.
List Partitioning tabulka je rozdělena podle explicitně uvedeného seznamu hodnot, kterése v ní budou vyskytovat.
16
1.4. PARTITIONING KAPITOLA 1. DATABÁZE
Je doporučováno použít partitioning v případě, že je tabulka větší než je fyzická paměť
databázového serveru. Vhodnost partitioningu záleží na používaných dotazech, proto by
se měly partitions volit buď podle předem známého využití nebo by se měly sledovat sta-
tistiky nejpoužívanějších dotazů a partitions tvořit posléze. Implementací partitioningu se
může dosáhnout výhod za předpokladu, že se selektuje podle klíčového sloupce, v opačném
případě je dotaz vždy pomalejší.
• Výrazně se zvýší rychlost dotazů v některých situacích. Zejména když jsou výsledkem
dotazu řádky jedné nebo malého počtu partitions.
• Může nahradit index, takže se sníží velikost zabíraného místa právě indexem.
• Rychlé vyřízení UPDATE/DELETE v rámci jedné partition.
• Rychlejší sekvenční scan na menší partition, prochází se méně dat než v celé tabulce.
• Rychlé hromadné mazání nebo vkládání dat prostým přidáním nebo umazáním par-
titions.
• Rychlejší operace ALTER TABLE, VACUUM.
• Málo používaná data je možné přesunovat na levnější a pomalejší paměťová média.
V PostgreSQL je partitioning řešen pomocí inheritance. Principy inheritance jsou uve-
deny v následující podsekci.[2]
1.4.1 Inheritance
Jak již bylo řečeno, PostgreSQL je objektově relační databázový systém a tedy pod-
poruje některé prvky objektově orientovaného modelu. Právě inheritance (dědičnost) je
jedním z těchto prvků. Inheritance je jedním z hlavních konceptů objektově orientovaného
programování a v PostgreSQL má velmi podobné využití jako v programovacích jazycích.
Je to stav, kdy dceřiná tabulka resp. tabulky dědí strukturu a některá chování tabulky
resp. tabulek rodičovských. To znamená, že např. všechny sloupce rodičovské tabulky jsou
vytvořeny i v tabulce dceřiné.
Inheritance se vytváří při tvorbě tabulek pomocí klíčového slova INHERITS a v násle-
dující závorce je uvedeno jméno rodičovské tabulky. Jako příklad tvorby tabulky pomocí
inheritance lze použít tabulku měst a z ní odvozenou tabulku hlavních měst:
17
1.4. PARTITIONING KAPITOLA 1. DATABÁZE
CREATE TABLE cities (
name VARCHAR(25),
population INT
);
CREATE TABLE capitals (
country CHAR(2)
) INHERITS (cities);
V tomto případě tabulka hlavních měst capitals obsahuje sloupce zděděné z tabulky
měst cities (name, population) a navíc sloupec se znakem země (country). Inheritance
jde vytvořit i na tabulce, která je už vytvořena, pomocí ALTER TABLE a INHERITS.
Tyto nově vzniklé dceřiné tabulky musí obsahovat všechny sloupce, které obsahuje tabulka
rodičovská.
Dotaz na všechna města včetně těch hlavních by vypadal asi takto:
SELECT * FROM cities;
A vrátil by:
name | population
---------+------------
Ostrava | 311419
Brno | 404688
Prague | 1289561
London | 7556900
(4 rows)
Pomocí inheritance se nedědí jenom všechny sloupce, ale také „check constraintsÿ a
„not-null constraintsÿ. Ostatní omezení (unique, primary key, foreign key) nejsou zděděny.
Inheritance také automaticky nerozšiřuje data při vkládání (INSERT, COPY) do správ-
ných tabulek ve vytvořené hierarchii. Není proto možné zapsat tento příkaz:
INSERT INTO cities (name, population, country)
VALUES (’Paris’, 2193031, ’fr’);
Tento případ lze vyřešit například pomocí triggeru. Stejně tak nelze mazat rodičovské
tabulky, pokud existují nějací potomci. Sloupce nebo omezení nejdou smazat nebo změnit,
pokud jsou zděděny. Pokud je nutné smazat rodičovskou tabulku, musíme přidat do pří-
kazu klíčové slovo CASCADE. Tím smažeme rodičovskou tabulku a všechny její potomky.
18
1.4. PARTITIONING KAPITOLA 1. DATABÁZE
V PostgreSQL lze dědit od více než jedné tabulky. Pokud se v rodičovských tabulkách
vyskytuje sloupec se stejným jménem, bude v potomkovi spojen do jednoho sloupce. Aby
mohly být spojeny, musí mít stejné datové typy.[2]
1.4.2 Implementace partitioningu
Každá partiton musí být vytvořena jako potomek jedné rodičovské tabulky. Tyto ro-
dičovské tabulky nazývané také „masterÿ tabulky neobsahují žádná data. Existují pouze
proto, aby reprezentovaly všechna data a všechny partitions mohly od nich dědit.
1. Vytvoření „masterÿ tabulky, která neobsahuje žádná data, nedefinuje žádná omezení,
pokud je tedy nechceme zdědit do všech partitions. Není nutné definovat žádné indexy
nebo unique omezení.
CREATE TABLE workers (
firstname VARCHAR(20),
surname VARCHAR(30),
birth DATE
);
2. Vytvoření několika dceřiných tabulek, které budou dědit z „masterÿ tabulky. Tyto
zděděné tabulky jsou ony partitions. Ve všech směrech vystupují jako normální ta-
bulky. Obvykle nepřidávají žádné další sloupce, ale přidávají podmínky, které defi-
nují, jaká data budou jednotlivé partitions obsahovat.
CREATE TABLE workers_1940 (
CHECK ( birth >= DATE ’1940-01-01’ AND birth < DATE ’1950-01-01’ )
) INHERITS (workers);
...
CREATE TABLE workers_1990 (
CHECK ( birth >= DATE ’1990-01-01’ AND birth < DATE ’2000-01-01’ )
) INHERITS (workers);
3. Vytvoření indexu pro každou partition nad klíčovým sloupcem, podle kterého byla
tabulka rozdělena. Urychlí dotazy nad indexem v rámci každé partition.
CREATE INDEX workers_1940_birth_index ON workers_1940 (birth);
...
CREATE INDEX workers_1990_birth_index ON workers_1990 (birth);
19
1.4. PARTITIONING KAPITOLA 1. DATABÁZE
4. Vytvoření triggeru a funkce, která se bude starat o rozdělení dat do správných tabulek
(partitions). Funkce:
CREATE OR REPLACE FUNCTION workers_insert_function()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.birth >= DATE ’1940-01-01’ AND
NEW.birth < DATE ’1950-01-01’ ) THEN
INSERT INTO workers_1940 VALUES (NEW.*);
...
ELSIF ( NEW.birth >= DATE ’1990-01-01’ AND
NEW.birth < DATE ’2000-01-01’ ) THEN
INSERT INTO workers_1990 VALUES (NEW.*);
ELSE
RAISE EXCEPTION ’Date out of range!’;
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
Trigger:
CREATE TRIGGER insert_workers_trigger
BEFORE INSERT ON workers
FOR EACH ROW EXECUTE PROCEDURE workers_insert_function();
V tomto případě je partitioning připraven k použití. Při vložení trigger automaticky
rozmístí data do vhodných tabulek. Pro každé nové období je dobré vytvořit další tabulku.
Proto je vhodné si napsat funkci, která se o to bude automaticky starat.
Implementovat partitioning lze i jinými způsoby než jsou triggery. Například pomocí
pravidel (RULE). Pravidla jsou ale mnohem náročnější na systém než triggery. Jsou ale
vhodná například v případě hromadných insertů, kdy trigger je spouštěn pro každý řá-
dek, zatímco pravidlo pouze jednou za celý dotaz. Další nevýhodou pravidel je nevyvolání
výjimky, pokud vkládaná data nejsou v rozmezí definovaném podmínkami. V takovém
případě se tyto data potichu vloží do „masterÿ tabulky.[2]
20
1.5. PRAKTICKÁ ČÁST KAPITOLA 1. DATABÁZE
1.4.3 Údržba partitioningu
Jednou z nejdůležitější výhod partitioningu je nenáročná manipulace s daty. Ve vel-
kých tabulkách tyto operace vyžadují velké nároky na systém. V případě partitioningu se
nepotřebná data smažou pouhým smazáním celé tabulky, což je nenáročná operace pro
databázový systém oproti vyhledávání a mazání jednotlivých řádků.
DROP TABLE workers_1940;
Často je žádané zrychlit dotazování, ale nesmazat data kvůli případnému dohledání.
Lze tedy tyto tabulky „vyřaditÿ z partitioningu.
ALTER TABLE workers_1940 NO INHERIT workers;
Partitioning a Constraint Exclusion
Constraint exclusion je optimalizační technika, která pomáhá zlepšit výkon tabulek s
implementovaným partitioningem. Bez constraint exclusion by databázový systém prohle-
dával všechny partitions. Pokud je však constraint exclusion povolen, planner vyhodnotí
omezení všech partition tabulek a vyřadí z exekučního plánu ty partition, které není nutné
skenovat. Constraint exclusion se povoluje resp. zakazuje příkazem:
SET constraint_exclusion = [on | partition | off];
PostgreSQL implicitně nastavuje hodnotu partition, která způsobí, že tato technika je
uplatněna pouze při partitioningu. Hodnota on způsobí, že planner zkoumá constraints v
každém případě, dokonce i v jednoduchých dotazech, kdy je to kontraproduktivní.[2]
1.5 Praktická část
V této části budou otestovány nástroje, které byly probrány. Testování bude prováděno
v PostgreSQL verze 8.4.6 v interaktivním terminálu psql na cvičné databázi test :
$ psql test
Pomocí funkce \timing budou sledovány časy běhu jednotlivých úkonů.
21
1.5. PRAKTICKÁ ČÁST KAPITOLA 1. DATABÁZE
1.5.1 Index
Pro testování indexů byla vytvořena tabulka geodetických bodů s časem jejich pořízení.
Tato tabulka bude obsahovat čísla bodů id, které se budou sekvenčně zvětšovat o jednu při
každém přidání bodu. Dále pak sloupce souřadnice x, y, z a sloupec time, který znázorňuje
čas změření. Vytvoření tabulky bude tedy vypadat takto:
test=> CREATE TABLE test (
test(> id SERIAL,
test(> x REAL,
test(> y REAL,
test(> z REAL,
test(> time TIMESTAMP);
NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for serial
column "test.id"
CREATE TABLE
Time: 205.927 ms
Kvůli testovaní musí být tabulka naplněna reprezentativním vzorkem dat. Pomocí
funkce random(), která vrací pseudonáhodné číslo v rozmezí od 0 do 1, byly vygenerovány
souřadnice S-JTSK v přibližném rozmezí souřadnic ČR. Ke každému bodu byl vygenero-
ván datum a čas s přesností na minuty v rozmezí od 1.1.1960 do 31.12.2009. Celkově bylo
vygenerováno 50 000 000 bodů pomocí funkce generate_series:
test=> INSERT INTO test (x,y,z,time)
test-> SELECT 280000*random()+940000,470000*random()+430000,1000*random()+200,
test-> ’1960-01-01’::timestamp+(random()*26298000)::int*interval ’1 minute’
test-> FROM generate_series(1,50000000);
INSERT 0 50000000
Time: 791120.592 ms
Vygenerovaná data budou vypadat takto:
test=> SELECT * FROM test LIMIT 5;
id | x | y | z | time
----------+-------------+--------+---------+---------------------
53283729 | 1.03669e+06 | 610004 | 713.846 | 1985-08-09 05:02:00
53283730 | 1.03428e+06 | 716360 | 971.31 | 2001-02-04 11:41:00
53283731 | 1.15045e+06 | 522123 | 837.489 | 1984-06-04 14:58:00
53283732 | 1.07067e+06 | 808914 | 577.092 | 1973-03-03 15:16:00
53283733 | 1.05551e+06 | 437860 | 261.17 | 1986-10-21 01:13:00
(5 rows)
22
1.5. PRAKTICKÁ ČÁST KAPITOLA 1. DATABÁZE
Pokud je třeba zjistit počet bodů, které byly změřeny v červnu roku 1988, dotaz bude
následující. Kvůli zjištění exekučního plánu a skutečné doby běhu dotazu byl použit příkaz
EXPLAIN ANALYZE:
EXPLAIN ANALYZE SELECT count(*) FROM test
WHERE time >= TIMESTAMP ’1988-06-01’ AND time < TIMESTAMP ’1988-07-01’;
QUERY PLAN
-------------------------------------------------------------------------------
Aggregate (cost=1068652.22..1068652.23 rows=1 width=0)
(actual time=277098.419..277098.421 rows=1 loops=1)
-> Seq Scan on test (cost=0.00..1068457.00 rows=78087 width=0)
(actual time=8.512..276951.679 rows=81789 loops=1)
Filter: (("time" >= ’1988-06-01 00:00:00’::timestamp without time zone)
AND ("time" < ’1988-07-01 00:00:00’::timestamp without time zone))
Total runtime: 277241.916 ms
(4 rows)
Tento dotaz byl extrémně pomalý. Běžel téměř 5 minut. Dále je možné si všimnout
sekvenčního scanu, který byl použit při vyhledávání. Je nutné zrychlit dotazy. Jednou z
možností je použití indexu. Protože se bude vyhledávat podle času, vytvoříme nad sloupcem
time index typu b-tree:
test=> CREATE INDEX test_time_index ON test (time);
CREATE INDEX
Time: 467465.394 ms
Aby se mohl planner správně rozhodnout, jakou vyhledávací strategii použít, musí být
aktualizovány statistiky o tabulce test pomocí příkazu ANALYZE:4
test=> ANALYZE VERBOSE test;
INFO: analyzing "public.test"
INFO: "test": scanned 30000 of 318472 pages,
containing 4710000 live rows and 0 dead rows;
30000 rows in sample, 50000104 estimated total rows
ANALYZE
Time: 89950.257 ms
Z výpisu je poznat, že ANALYZE z časových důvodů analyzoval pouze vzorek dat.
Oskenoval 30 000 stránek, které obsahovaly 4 710 000 řádků. Odhadl, že se v tabulce
nachází 50 000 104 řádků. Tyto statistiky a další jiné uložil do pg_statistic.
Po těchto úpravách by měl předešlý dotaz běžet o poznání rychleji:4ANALYZE je také spouštěn automaticky viz 1.2.1
23
1.5. PRAKTICKÁ ČÁST KAPITOLA 1. DATABÁZE
test=> EXPLAIN ANALYZE SELECT count(*) FROM test
test-> WHERE time >= TIMESTAMP ’1988-06-01’ AND time < TIMESTAMP ’1988-07-01’;
QUERY PLAN
--------------------------------------------------------------------------------
Aggregate (cost=177095.38..177095.39 rows=1 width=0)
(actual time=5935.228..5935.229 rows=1 loops=1)
-> Bitmap Heap Scan on test (cost=1574.82..176910.72 rows=73862 width=0)
(actual time=1451.599..5807.794 rows=81789 loops=1)
Recheck Cond: (("time" >= ’1988-06-01 00:00:00’::timestamp)
AND ("time" < ’1988-07-01 00:00:00’::timestamp))
-> Bitmap Index Scan on test_time_index (cost=0.00..1556.35 rows=73862 width=0)
(actual time=1446.290..1446.290 rows=81789 loops=1)
Index Cond: (("time" >= ’1988-06-01 00:00:00’::timestamp)
AND ("time" < ’1988-07-01 00:00:00’::timestamp))
Total runtime: 5935.417 ms
(6 rows)
Použitím indexu se dotaz zrychlil téměř 47×. To si ale vybírá daň v podobě údržby
indexu. Nehledě na dobu tvorby indexu, kdy není možné modifikovat data v tabulce, index
zabírá podstatnou část diskového prostoru:
test=> SELECT pg_size_pretty(pg_relation_size(’test_time_index’));
pg_size_pretty
----------------
1071 MB
(1 row)
test=> SELECT pg_size_pretty(pg_relation_size(’test’));
pg_size_pretty
----------------
2488 MB
(1 row)
Index v této tabulce zabírá 43% velikosti tabulky a to se jedná pouze o jeden index.
Kdyby byly v tabulce další indexy, mohla by celková velikost indexů snadno přesáhnout
velikost tabulky.
Velikost indexu je jen jedna z nevýhod. Další nevýhodou jsou výrazně pomalejší změny
v datech tabulky. S každou změnou se totiž musí přepočítat index. V prvním případě bude
uvedeno vložení 1000 řádků do tabulky s index a podruhé bez indexu:
test=> INSERT INTO test (x,y,z,time)
SELECT 280000*random()+940000,470000*random()+430000,1000*random()+200,
24
1.5. PRAKTICKÁ ČÁST KAPITOLA 1. DATABÁZE
’1960-01-01’::timestamp + (random()*26298000)::int * interval ’1 minute’
FROM generate_series(1,1000);
INSERT 0 1000
Time: 13896.304 ms
test=> DROP INDEX test_time_index;
DROP INDEX
Time: 271.973 ms
test=> INSERT INTO test (x,y,z,time)
SELECT 280000*random()+940000,470000*random()+430000,1000*random()+200,
’1960-01-01’::timestamp + (random()*26298000)::int * interval ’1 minute’
FROM generate_series(1,1000);
INSERT 0 1000
Time: 27.840 ms
Zpomalení údržbou indexu bylo více jak 500×. Kdyby se nejednalo jen o jeden index,
zpomalení by bylo ještě mnohem větší.
1.5.2 Partitioning
Pro testování partitioningu byla vytvořena stejná tabulka jako v případě indexu. Jedná
se tedy o tabulku geodetických bodů a času jejich pořízení. U partitioningu se nejdříve
vytvoří „masterÿ tabulka, která nebude obsahovat žádná data:
test=> CREATE TABLE test_part (
test(> id serial,
test(> x real,
test(> y real,
test(> z real,
test(> time timestamp);
NOTICE: CREATE TABLE will create implicit sequence "test_part_id_seq" for serial
column "test_part.id"
CREATE TABLE
Time: 100.385 ms
Poté se vytvoří partition „slaveÿ tabulky. Bylo zvoleno pět tabulek tak, aby každá
zahrnovala jedno desetiletí:
test=> CREATE TABLE test_part_year196x (
test(> CHECK (time >= TIMESTAMP ’1960-01-01’ AND time < TIMESTAMP ’1970-01-01’)
test(> ) INHERITS (test_part);
CREATE TABLE
25
1.5. PRAKTICKÁ ČÁST KAPITOLA 1. DATABÁZE
Time: 37.505 ms
test=>
test=> CREATE TABLE test_part_year197x (
test(> CHECK (time >= TIMESTAMP ’1970-01-01’ AND time < TIMESTAMP ’1980-01-01’)
test(> ) INHERITS (test_part);
CREATE TABLE
Time: 10.545 ms
test=>
test=> CREATE TABLE test_part_year198x (
test(> CHECK (time >= TIMESTAMP ’1980-01-01’ AND time < TIMESTAMP ’1990-01-01’)
test(> ) INHERITS (test_part);
CREATE TABLE
Time: 7.912 ms
test=>
test=> CREATE TABLE test_part_year199x (
test(> CHECK (time >= TIMESTAMP ’1990-01-01’ AND time < TIMESTAMP ’2000-01-01’)
test(> ) INHERITS (test_part);
CREATE TABLE
Time: 8.036 ms
test=>
test=> CREATE TABLE test_part_year200x (
test(> CHECK (time >= TIMESTAMP ’2000-01-01’ AND time < TIMESTAMP ’2010-01-01’)
test(> ) INHERITS (test_part);
CREATE TABLE
Time: 7.520 ms
Poté bude vytvořena trigger funkce, která se bude starat o rozmístění bodů do správných
tabulek podle data měření:
test=> CREATE OR REPLACE FUNCTION test_part_insert_function()
test-> RETURNS TRIGGER AS $$
test$> BEGIN
test$> IF (NEW.time >= TIMESTAMP ’1960-01-01’ AND
test$> NEW.time < TIMESTAMP ’1970-01-01’) THEN
test$> INSERT INTO test_part_year196x VALUES (NEW.*);
test$> ELSEIF (NEW.time >= TIMESTAMP ’1970-01-01’ AND
test$> NEW.time < TIMESTAMP ’1980-01-01’) THEN
test$> INSERT INTO test_part_year197x VALUES (NEW.*);
test$> ELSEIF (NEW.time >= TIMESTAMP ’1980-01-01’ AND
test$> NEW.time < TIMESTAMP ’1990-01-01’) THEN
test$> INSERT INTO test_part_year198x VALUES (NEW.*);
test$> ELSEIF (NEW.time >= TIMESTAMP ’1990-01-01’ AND
26
1.5. PRAKTICKÁ ČÁST KAPITOLA 1. DATABÁZE
test$> NEW.time < TIMESTAMP ’2000-01-01’) THEN
test$> INSERT INTO test_part_year199x VALUES (NEW.*);
test$> ELSEIF (NEW.time >= TIMESTAMP ’2000-01-01’ AND
test$> NEW.time < TIMESTAMP ’2010-01-01’) THEN
test$> INSERT INTO test_part_year200x VALUES (NEW.*);
test$> ELSE
test$> RAISE EXCEPTION ’Time out of range.
Fix the test_part_insert_function()’;
test$> END IF;
test$> RETURN NULL;
test$> END;
test$> $$
test-> LANGUAGE plpgsql;
CREATE FUNCTION
Time: 142.592 ms
Následuje trigger, který bude spouštět předešlou funkci při vložení dat do „masterÿ
tabulky:
test=> CREATE TRIGGER test_insert_trigger
test-> BEFORE INSERT ON test_part
test-> FOR EACH ROW EXECUTE PROCEDURE test_part_insert_function();
CREATE TRIGGER
Time: 22.274 ms
Teď by mělo následovat zindexování klíčového sloupce time, ale z časových důvodů bude
nejprve proveden INSERT a až potom vytvoření indexů pro všechny „slaveÿ tabulky. Bude
vloženo opět 50 000 000 bodů s časem měření v rozmezí let 1960-2009. V tomto případě
budou body roztříděny do vhodných tabulek pomocí triggeru.
test=> INSERT INTO test_part (x,y,z,time)
test-> SELECT 280000*random()+940000,470000*random()+430000,1000*random()+200,
test-> ’1960-01-01’::timestamp+(random()*26298000)::int*interval ’1 minute’
test-> FROM generate_series(1,50000000);
INSERT 0 0
Time: 2922377.256 ms
Doba běhu insertu byla delší 3.7× než v prvním případě. Je to způsobeno tím, že pro
každý řádek musí být spuštěn trigger a trigger funkce. Pro takto rozsáhlý insert by bylo
efektivnější použít pravidla (RULE), která jsou spouštěna pouze jednou za celý dotaz.
Jednotlivé „slaveÿ tabulky budou vypadat takto (jedna stránka je typicky 8kB):
27
1.5. PRAKTICKÁ ČÁST KAPITOLA 1. DATABÁZE
test=> SELECT relname,reltuples AS "rows",relpages*8/1024 AS "size [MB]"
FROM pg_class WHERE relname LIKE ’test_part_year___x’;
relname | rows | size [MB]
--------------------+-------------+-----------
test_part_year196x | 1.00021e+07 | 497
test_part_year197x | 9.99762e+06 | 497
test_part_year198x | 1.00023e+07 | 497
test_part_year199x | 9.99478e+06 | 497
test_part_year200x | 1.00031e+07 | 497
(5 rows)
Pro urychlení čtení jednotlivých partition bude zindexován klíčový sloupec time.
test=> CREATE INDEX test_part_year196x_index_time ON test_part_year196x (time);
CREATE INDEX
Time: 90962.043 ms
test=> CREATE INDEX test_part_year197x_index_time ON test_part_year197x (time);
CREATE INDEX
Time: 95054.517 ms
test=> CREATE INDEX test_part_year198x_index_time ON test_part_year198x (time);
CREATE INDEX
Time: 97290.763 ms
test=> CREATE INDEX test_part_year199x_index_time ON test_part_year199x (time);
CREATE INDEX
Time: 99341.608 ms
test=> CREATE INDEX test_part_year200x_index_time ON test_part_year200x (time);
CREATE INDEX
Time: 116375.899 ms
Nakonec se analyzují data pomocí ANALYZE, aby planner mohl sestavit nejefektivnější
exekuční plán.
test=> ANALYZE test_part_year196x;
ANALYZE
Time: 21302.234 ms
test=> ANALYZE test_part_year197x;
ANALYZE
Time: 21341.448 ms
test=> ANALYZE test_part_year198x;
ANALYZE
Time: 21123.983 ms
test=> ANALYZE test_part_year199x;
28
1.5. PRAKTICKÁ ČÁST KAPITOLA 1. DATABÁZE
ANALYZE
Time: 20988.318 ms
test=> ANALYZE test_part_year200x;
ANALYZE
Time: 20727.116 ms
Teď už jen zbývá analyzovat stejný dotaz jako v sekci s indexy:
test=> EXPLAIN ANALYZE SELECT count(*) FROM test_part
WHERE time >= TIMESTAMP ’1988-06-01’ AND time < TIMESTAMP ’1988-07-01’;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Aggregate (cost=70305.47..70305.48 rows=1 width=0)
(actual time=3066.605..3066.607 rows=1 loops=1)
-> Append (cost=0.00..70101.39 rows=81629 width=0)
(actual time=379.348..2939.296 rows=81788 loops=1)
-> Seq Scan on test_part (cost=0.00..32.65 rows=8 width=0)
(actual time=0.003..0.003 rows=0 loops=1)
Filter: (("time" >= ’1988-06-01 00:00:00’::timestamp)
AND ("time" < ’1988-07-01 00:00:00’::timestamp))
-> Bitmap Heap Scan on test_part_year198x test_part
(cost=1733.96..70068.74 rows=81621 width=0)
(actual time=379.339..2694.887 rows=81788 loops=1)
Recheck Cond: (("time" >= ’1988-06-01 00:00:00’::timestamp)
AND ("time" < ’1988-07-01 00:00:00’::timestamp))
-> Bitmap Index Scan on test_part_year198x_index_time
(cost=0.00..1713.56 rows=81621 width=0)
(actual time=374.107..374.107 rows=81788 loops=1)
Index Cond: (("time" >= ’1988-06-01 00:00:00’::timestamp)
AND ("time" < ’1988-07-01 00:00:00’::timestamp))
Total runtime: 3066.834 ms
(9 rows)
PostgreSQL se nejdříve podíval do „masterÿ tabulky. Tam nenašel žádná data, proto
zkontroloval podmínku v klauzuli WHERE, vstoupil do tabulky, která obsahuje hledaná
data a použil index scan. Pomocí partitioningu byl dotaz zrychlen dvojnásobně.
29
Kapitola 2
Rozhraní přístupu k databázi
Každý moderní programovací jazyk by měl obsahovat nástroje (rozhraní) pro práci s da-
tabázemi. Tato rozhraní zpravidla bývají souborem tříd, funkcí nebo procedur, které slouží
programátorovi, aby mohl pohodlně využívat funkce databázového systému z prostředí jeho
aplikace. Jednotlivá rozhraní jsou implementována výrobci databázových systémů. Exis-
tují jednotná rozhraní, která sjednocují společný přístup do různých databází od různých
výrobců. Dvěma takovým bude věnována tato kapitola. Jedná se o JDBC API pro pro-
gramovací jazyk Java a Qt framework, přesněji řečeno jeho SQL modul, pro programovací
jazyk C++.
2.1 JDBC
JDBC je API programovacího jazyka Java, které definuje způsob, kterým klient při-
stupuje do databáze. Toto rozhraní poskytuje metody pro dotazování a modifikování da-
tabáze. Je zaměřeno na relačně orientované databáze. Pro přístup do databáze je nutné
použít JDBC ovladač, který je specifický pro DBMS. Z tohoto důvodu není nutné znát API
jednotlivých databází, ale pouze JDBC API. Ovladače jsou obvykle vydávány subjektem,
který provozuje databázi. Mimo tyto ovladače je možné využít tzv. JDBC-ODBC mostu
pro přístup ke zdrojům dat podporující ODBC rozhraní, který je začleněn do JDK.
JDBC bylo představeno v roce 1997 jako součást JDK 1.1 v balíčcích java.sql a javax.sql.
Vývoj byl inspirován ODBC standardem navrženým firmou Microsoft. Od verze 3.0 je
JDBC vyvíjeno pod Java Community Process. Poslední stabilní verze je rozhraní JDBC
4.0, které je zahrnuto v Java SE 6. JDBC 4.1 je připravováno do Java SE 7.[1]
Obecně pro vykonání kteréhokoliv SQL dotazu pomocí JDBC se postupuje takto:
30
2.1. JDBC KAPITOLA 2. ROZHRANÍ PŘÍSTUPU K DATABÁZI
1. Navázání spojení
2. Vytvoření příkazu (statement)
3. Vykonání příkazu
4. Zpracování výsledku objektu ResultSet
5. Uzavření spojení
V tabulce 2.1 jsou uvedena rozhraní balíčku java.sql, která přistupují k SQL příkazům
(statements) jiným způsobem.[5]
Tabulka 2.1: Rozhraní balíčku java.sql
DBMS Popis
Statement Používá se v jednoduchých dotazech bez parametru.PreparedStatement Rozšiřuje rozhraní Statement. Užívá se pro předkompilované SQL
dotazy, které mohou obsahovat vstupní parametry.CallableStatement Rozšiřuje rozhraní PreparedStatement. Používá se pro vykonání
uložených procedur, které mohou obsahovat vstupní i výstupníparametry.
2.1.1 Spojení
Navázání spojení
První, co je nutné udělat při použití JDBC, je navázat spojení se zdrojem dat. Aplikace
se většinou připojují ke zdroji dat pomocí dvou tříd: DriverManager a DataSource.
DriverManager třída připojí aplikaci ke zdroji dat, který je specifikován databázovým
URL. Při prvním pokusu o spojení se automaticky načte JDBC 4.0 ovladač z classpath.
Connection conn = DriverManager.getConnection(url);
Statická metoda DriverManager.getConnection vrací objekt typu Connection. Tato
metoda vyžaduje databázové URL závisející na použitém DBMS. Pro zadání uživatele a
hesla třída DriverManager nabízí přetíženou metodu getConnection, ve které se buď uve-
dou explicitně jako řetězce, nebo v objektu typu Properties. V tabulce 2.2 jsou uvedeny
vybraná URL.
31
2.1. JDBC KAPITOLA 2. ROZHRANÍ PŘÍSTUPU K DATABÁZI
Tabulka 2.2: Vybraná URL používaná v JDBC
DBMS URL
Oracle jdbc:oracle:thin:@//localhost:1521/orcl
PostgreSQL jdbc:postgresql://localhost:5432/test
MySQL jdbc:mysql://localhost:3306/testdb
Po navázání spojení se zpravidla vytváří objekt typu Statement pomocí metody
Connection.createStatement(), Connection.prepareStatement() pro objekt typu
PreparedStatement, nebo případně Connection.prepareCall() pro objekt typu
CallableStatement. Například:
Statement stmt = conn.createStatement();
Uzavření spojení
Pokud se objekt Statement nepoužívá, měla by být zavolána metoda Statement.close(),
která okamžitě uvolní systémové prostředky. Zavoláním této metody jsou uvolněny i ob-
jekty ResultSet. Poté je uzavřeno spojení pomocí Connection.close(). Je dobré zavolat
tuto metodu v bloku finally, který se vykoná i v případě vyvolání výjimky:
finally {stmt.close();
conn.close();
}
V JDBC 4.1 v připravované Java SE 7 je možné využít try-with-resources konstrukci,
která automaticky uzavírá Connection, Statement i ResultSet bez ohledu na to, zda byla
vyvolána výjimka SQLException.[5]
try (Statement stmt = con.createStatement()) {// ...
} catch (SQLException e) {// ...
}
32
2.1. JDBC KAPITOLA 2. ROZHRANÍ PŘÍSTUPU K DATABÁZI
2.1.2 Výjimky
Třída SQLException
Pokud JDBC narazí na chybu během komunikace s databází, vyvolá instanci třídy
SQLException. SQLException instance obsahuje informace, které mohou pomoci najít pří-
činu chyby. V tabulce 2.3 jsou uvedeny metody třídy SQLException.
Tabulka 2.3: Metody třídy SQLException
Metoda Popis
getMessage() vrací String s popisem chyby.getSQLState() vrací řetězec se standardizovaným kódem chyby.getErrorCode() vrací celé číslo popisující chybu, záleží na implementaci da-
tabázegetCause() vrací typ Throwable, pokud je známa příčina chyby, jinak
vrátí null.
Ukázka použití některých výše uvedených metod:
try {// ...
} catch (SQLException e) {System.err.println(e.getMessage());
System.err.println(e.getSQLState());
System.err.println(e.getErrorCode());
}
Třída SQLWarning
SQLWarning je podtřídou třídy SQLException a stará se o databázová varování. Tyto
varování nezastaví běh aplikace, ale upozorní, že se děje něco, co není plánované. Například,
že nebyla odebrána práva pomocí příkazu REVOKE.
Třída BatchUpdateException
BatchUpdateException je také podtřídou třídy SQLException a je vyvolána, pokud na-
stane chyba při dávkové modifikaci dat. Poskytuje instanční metodu getUpdateCounts(),
která vrací pole počtu modifikovaných řádků pro každý dotaz do doby, než byla vyvolána
výjimka.[5]
33
2.1. JDBC KAPITOLA 2. ROZHRANÍ PŘÍSTUPU K DATABÁZI
2.1.3 Objekt ResultSet
Rozhraní ResultSet
ResultSet je objekt představující výsledek vrácený databázovým systémem po vyko-
nání dotazu. Tento výsledek si lze představit jako tabulku, kterou je procházeno pomocí
speciálního kurzoru. Tento kurzor je ukazatel, který ukazuje na řádky objektu ResultSet.
Je umístěn před první řádek a pohybuje se jím pomocí metod. Např. metoda next() posu-
nuje kurzor na další řádek a vrací hodnotu false, pokud je kurzor umístěn za posledním řád-
kem výsledku. ResultSet může být vytvořen pomocí jakéhokoliv objektu implementující
rozhraní Statement, PreparedStatement nebo CallableStatement. Následující příklad
demonstruje, jak se s objektem ResultSet pracuje:
public static void viewTable(Connection conn, String statement)
throws SQLException {
Statement stmt = null;
try {stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(statement);
while (rs.next()) {int id = rs.getInt(1);
double x = rs.getDouble(2);
double y = rs.getDouble("y");
double z~= rs.getDouble("z");
doSomething();
}} catch (SQLException e) {
e.printStackTrace();
} finally {if (stmt != null) {
stmt.close();
}}
}
Pomocí cyklu while a metody next() je procházeno všemi řádky. Na každém řádku
je pomocí getteru uložena hodnota do proměnné ekvivalentního datového typu jako je
datový typ použitého DBMS. Tyto gettery se používají buď s celočíselným parametrem
34
2.1. JDBC KAPITOLA 2. ROZHRANÍ PŘÍSTUPU K DATABÁZI
indexu sloupce, kde číslo 1 značí první sloupec výsledku, nebo s řetězcem jména daného
sloupce. V tabulce 2.4 jsou uvedeny metody pro posun kurzoru různými směry.
Tabulka 2.4: Metody třídy ResultSet pro posun kurzoru
Metoda Popis
next() posune kurzor o řádek vpředprevious() posune kurzor o řádek zpětfirst() posune kurzor na první řádek objektulast() posune kurzor na poslední řádek objekturelative(int) posune kurzor o zadaný počet řádků vzhledem k jeho aktuální poziciabsolute(int) posune kurzor na řádek uvedený v parametru metody
Objekt ResultSet má implicitně nastaven typ TYPE FORWARD ONLY, takže lze použít
pouze metoda next(). Pokud je potřeba použít i další z výše uvedených metod, musí být
nastaven typ pomocí jedné z následujících konstant:
• TYPE FORWARD ONLY: Kurzorem je možné pohybovat pouze směrem vpřed, tedy od
prvního řádku až po poslední.
• TYPE SCROLL INSENSITIVE: Kurzorem je možné pohybovat vpřed i vzad, o relativní
i absolutní počet řádků a výsledný počet řádků odpovídá výsledku daném v době
provedení dotazu.
• TYPE SCROLL SENSITIVE: Kurzorem je možné pohybovat vpřed i vzad, o relativní i
absolutní počet řádků, ale výsledek se mění v závislosti na změnách dat v databázi,
pokud je tento výsledek stále otevřen.
Objektu lze nastavit i další chování.
• ResultSet Concurrency
– CONCUR READ ONLY
– CONCUR UPDATABLE
• ResultSet Holdability
– HOLD CURSORS OVER COMMIT
– CLOSE CURSORS AT COMMIT
[5]
35
2.1. JDBC KAPITOLA 2. ROZHRANÍ PŘÍSTUPU K DATABÁZI
2.1.4 Transakce
Pokud je navázáno nové spojení pomocí JDBC, je implicitně nastaven tzv. auto-commit
mode. Tedy každý SQL příkaz je brán jako jedna transakce a po vykonání toho příkazu a
vrácení výsledků je proveden COMMIT automaticky. Aby mohlo být zahrnuto více příkazů
do jedné transakce musí být tento mód zakázán:
conn.setAutoCommit(false);
Pokud je zakázán, žádný SQL příkaz není potvrzen do té doby, než je explicitně zavolána
metoda Connection.commit(). Všechny příkazy provedené po předešlém volání metody
commit() jsou zahrnuty do další transakce.
Statement createStmt = null;
Statement insertStmt = null;
Statement selectStmt = null;
try {conn.setAutoCommit(false);
createStmt = conn.createStatement();
insertStmt = conn.createStatement();
selectStmt = conn.createStatement();
int cr = createStmt.executeUpdate("CREATE TABLE theodolits " +
"(id INT PRIMARY KEY, name VARCHAR(40))");
int ins = insertStmt.executeUpdate("INSERT INTO theodolits " +
"VALUES (1,’ZEISS Theo 010A’)");
conn.commit();
ResultSet sel = selectStmt.executeQuery("SELECT name FROM theodolits");
conn.commit();
conn.setAutoCommit(true);
} catch (SQLException e) {e.printStackTrace();
conn.rollback();
}
V tomto příkladě byl nejdříve zakázán auto-commit, aby mohla být vytvořena transakce
s více příkazy. Poté byla provedena první transakce, která obsahovala vytvoření tabulky
a naplnění prvním řádkem. Zavoláním metody commit() byla první transakce ukončena,
36
2.1. JDBC KAPITOLA 2. ROZHRANÍ PŘÍSTUPU K DATABÁZI
pokud se tedy nevyskytla výjimka, která volá metodu rollback() a vrací změny do původ-
ního stavu. Následuje další transakce obsahující dotaz, která je opět zakončena commit().
Nakonec je opět nastaven auto-commit mód.
Aby si databáze udržela vnitřní integritu při použití transakcí, musí dodržovat pravidla
ACID viz příloha B. Speciálně při použití souběžných transakcí se uplatňuje vlastnost
Isolation, více v příloze B.3. Databázový systém má implicitně nastavenu úroveň (isolation
level). Pomocí JDBC lze pro instanci Connection nastavit isolation level pomocí metody:
conn.setTransactionIsolation(int);
Parametrem metody jedna z konstant třídy Connection:
• TRANSACTION NONE
• TRANSACTION READ UNCOMMITTED
• TRANSACTION READ COMMITTED
• TRANSACTION REPEATABLE READ
• TRANSACTION SERIALIZABLE
Konstanta TRANSACTION NONE signalizuje, že transakce nejsou podporovány. Ostatní
viz příloha B.3. Pomocí metody getTransactionIsolation() lze zjistit isolation level
nastavený v databázi. Některé JDBC ovladače nemusí podporovat všechny isolation levels.
2.1.5 PreparedStatement
Pokud je potřeba Statement objekt vykonat víckrát, je výhodnější použít
PreparedStatement objekt z důvodu výrazného zrychlení. Výhodou je, že SQL příkaz je
poslán do DBMS předkompilován. To znamená, že když je dotaz vykonáván, DBMS pouze
stačí SQL dotaz spustit bez předchozí kompilace. Další nespornou výhodou je odolnost
vůči SQL injection útokům.
Ačkoli je možné používat PreparedStatement objekt i bez vstupních parametrů, nej-
více se používá u SQL příkazů přebírajících parametry. Výhodou je možnost použití stej-
ného SQL příkazu, ale s úplně jinými hodnotami. Příklad použití PreparedStatement
s parametry:
37
2.1. JDBC KAPITOLA 2. ROZHRANÍ PŘÍSTUPU K DATABÁZI
PreparedStatement pstmt = conn.prepareStatement(
"UPDATE measurement_services SET price = ? WHERE service = ?");
pstmt.setInt(1,20000)
pstmt.setString(2,"Leveling")
pstmt.executeUpdate();
Na místech zástupných znaků (otazník) v SQL příkazu se pomocí setteru nastaví hod-
noty, které jsou následně odeslány pomocí executeUpdate(). V prvním argumentu setteru
je určen konkretní zástupný znak a v druhém je uvedena hodnota. Když je hodnota na-
stavena, zůstává v objektu do té doby, než je přepsána na jinou hodnotu nebo smazána
metodou clearParameters(). Pomocí PreparedStatement lze parametrizovat SELECT
dotazy. V tomto případě je pak volána metoda executeQuery() a je vrácen ResultSet
objekt.[5]
2.1.6 Dávková modifikace
Statement,PreparedStatement a CallableStatement objekty mohou obsahovat se-
znam příkazů, které jsou s nimi spojeny. Tento seznam může obsahovat SQL příkazy IN-
SERT, UPDATE nebo DELETE a dále také DDL příkazy (příkaz SELECT, který vytváří
objekt ResultSet, nemůže být zahrnut). Jinými slovy, všechny příkazy, které vrací počet
modifikovaných řádků.
Pokud je jeden z výše uvedených objektů vytvořen, je zprvu prázdný. SQL příkazy
mohou být přiřazeny pomocí metody addBatch(). Metoda clearBatch() celý seznam
maže. Po ukončení vkládání příkazů se zavolá metoda executeBatch a celý seznam se
pošle databázovému systému jako celek. Příklad vkládání dotazů do objektu Statement:
Statement stmt = null;
try {conn.setAutoCommit(false);
stmt = conn.createStatement();
stmt.addBatch("CREATE TABLE theodolits " +
"(id INT PRIMARY KEY, name VARCHAR(40))");
stmt.addBatch("INSERT INTO theodolits VALUES (1,’ZEISS Theo 010A’)");
stmt.addBatch("INSERT INTO theodolits VALUES (2,’Leica TC 403’)");
int [] updateCounts = stmt.executeBatch();
conn.commit();
38
2.1. JDBC KAPITOLA 2. ROZHRANÍ PŘÍSTUPU K DATABÁZI
} catch (BatchUpdateException e) {e.printStackTrace();
conn.rollback();
} catch (SQLException e) {e.printStackTrace();
} finally {if (stmt != null) {
stmt.close();
}conn.setAutoCommit(true);
}
V tomto příkladě byl nejprve zakázán auto-commit, aby nebyla transakce automaticky
dokončena a mohla být případně obsloužena výjimka. Poté byl auto-commit opět povolen
v bloku finally. Tento postup je doporučován. Metoda executeBatch pošle dávku příkazů,
následně DBMS vykoná příkazy v pořadí, jak byly přidávány do seznamu. Pokud všechny
příkazy proběhly úspěšně, DBMS vrátí číslo počtu změn řádků pro každý příkaz a tyto čísla
jsou vložena do celočíselného pole. V tomto případě by DBMS poskytlo na prvním místě
0 (CREATE TABLE .. .) a dvakrát číslo 1 (INSERT). Metoda Connection.commit() pak
potvrdí transakci a změny se stanou trvalé. Výjimka BatchUpdateException může nastat,
pokud je přidán do seznamu SQL dotaz (produkuje objekt ResultSet) nebo pokud jeden
z příkazů ztroskotá z jiných příčin.[5]
Pomocí objektu PreparedStatement lze vytvářet parametrizované dávkové příkazy:
PreparedStatement pstmt = con.prepareStatement(
"INSERT INTO theodolits VALUES(?, ?)");
pstmt.setInt(1,1);
pstmt.setString(2,’ZEISS Theo 010A’);
pstmt.addBatch();
pstmt.setInt(1,2);
pstmt.setString(2,’Leica TC 403’);
pstmt.addBatch();
int [] updateCounts = pstmt.executeBatch();
39
2.2. QT KAPITOLA 2. ROZHRANÍ PŘÍSTUPU K DATABÁZI
2.2 Qt
V programovacím jazyku C++ lze do databází přistupovat pomocí několika různých
rozhraní. Jedním z těchto rozhraní je i SQL modul Qt frameworku. Qt je multiplatformní
aplikační framework, který je používán zejména pro vývoj aplikací s grafickým uživatel-
ským rozhraním (GUI). Lze jej využít i pro vývoj non-GUI programů, jako jsou například
aplikace příkazového řádku. Qt je knihovna pro C++ , ale existují i varianty pro jiné pro-
gramovací jazyky, jako jsou např. C, C# nebo Python.
Vývoj Qt započal v roce 1991 norskou společností Trolltech. Jméno Qt bylo vybráno
protože písmeno „Qÿ připadalo vývojářům atraktivní, tak jak ho viděli v Emacs na Slack-
ware a „tÿ po vzoru Xt. První dvě verze byly dostupné pouze pro Unix a Windows. Verze
3.0 přidala podporu Mac OS X. V roce 2008 získala Qt firma Nokia. Současná verze 4.7
byla vydána v září roku 2010.[1]
Qt je nabízeno pod následujícími licencemi:
• Qt Commercial Developer License – vhodná pro firmy, které nechtějí sdílet zdrojový
kód svých komerčních projektu.
• Qt GNU LGPL v. 2.1 – vhodná pro projekty dodržující licenci GNU LGPL verze 2.1
• Qt GNU GPL v. 3.0 – vhodná pro projekty dodržující licenci GNU GPL verze 3.0
Qt je členěno do tzv. modulů. Každý z nich poskytuje služby pro vývoj specifických
částí aplikací. Tabulka 2.5 představuje některé z nich.
Tabulka 2.5: Vybrané moduly Qt frameworku
Modul Popis
QtCore obsahuje základní negrafické třídy Qt, mechanismus signálů a slotů, atd.QtGui obsahuje grafické třídy
QtNetwork obsahuje třídy pro síťové programováníQtXml obsahuje třídy pro práci s XMLQtSql obsahuje třídy pro práci s databází pomocí SQL
Posledním zmíněným modulem (QtSql) se bude zabývat následující část textu. QtSql
modul rozděluje třídy do tří vrstev:
• Driver Layer – tato vrstva poskytuje nízkoúrovňový přechod mezi databází a SQL
API vrstvou. (QSqlDriver, QSqlResult, . . .)
40
2.2. QT KAPITOLA 2. ROZHRANÍ PŘÍSTUPU K DATABÁZI
• SQL API Layer – tato vrstva poskytuje třídy pro interakci s databází. (QSqlDatabase,
QSqlQuery, . . .)
• User Interface Layer – tato vrstva poskytuje modelové třídy úzce spjaté s databázemi.
(QSqlQueryModel, QSqlTableModel, . . .)
Aby bylo možné využívat tyto třídy, je nutné do zdrojových kódů zahrnout tuto direk-
tivu:
#include <QtSql>
Dále je nutné přidat řádek do Makefile souboru pro program qmake1 a tím tak odkázat
na určený modul (*.pro soubor):
QT += sql
[7]
2.2.1 Spojení
Navázání spojení
Pro přístup do databáze pomocí Qt frameworku je nejprve nutné vytvořit spojení a poté
otevřít databázové spojení. Lze vytvořit několik spojení, která jsou identifikována jménem.
Je možné vytvořit nepojmenované spojení, které je automaticky vytvořeno, pokud není
explicitně uvedeno jméno spojení.
QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL", "first");
db.setHostName("localhost");
db.setDatabaseName("test");
db.setUserName("testuser");
db.setPassword("testpass");
bool ok = db.open();
Na prvním řádku je vytvořeno spojení s databází pomocí statické funkce
QSqlDatabase::addDatabase(), která vrací QSqlDatabase objekt. Druhý argument je již
zmíněné jméno spojení. Prvním argumentem je QString specifikující typ ovladače, který
bude používán při spojení. Pokud je uveden pouze ovladač, je použit koncept nepojme-
novaného spojení. V tabulce 2.6 jsou uvedeny některé ovladače podporovaných databází
v Qt.1qmake je nástroj, který usnadňuje sestavení projektu napříč různými platformami. Automaticky gene-
ruje Makefile. Lze ho využít i v non-Qt projektech.
41
2.2. QT KAPITOLA 2. ROZHRANÍ PŘÍSTUPU K DATABÁZI
Tabulka 2.6: Ovladače podporovaných databází v Qt
Ovladač DBMS
QOCI Oracle Call Interface DriverQPSQL PostgreSQL verze 7.3 a vyššíQMYSQL MySQLQSQLITE SQLite verze 3QODBC ODBCQDB2 IBM DB2 verze 7.1 a vyšší
Na dalších řádcích uvedeného příkladu jsou funkce setXXX(), které poskytují nezbytné
informace pro vytvoření spojení s databázovým systémem. Tedy po řadě jméno hosta,
jméno databáze, jméno uživatele a heslo. Až na posledním řádku je otevřeno spojení pomocí
funkce QSqlDatabase::open(), která vrací true, pokud je spojení navázáno. Je rozdíl mezi
pouhým vytvořením spojení, které je uvedeno na prvním řádku, a otevřením spojení, které
je uvedeno na posledním řádku příkladu.
Uzavření spojení
Na konci práce s databází je nutné uzavřít spojení. Pomocí funkce QSqlDatabase::close()
se uzavře spojení a použitím statické funkce QSqlDatabase::removeDatabase() se spojení
odebere.[7]
2.2.2 Třída QSqlError
Třída QSqlError poskytuje chybové informace při interakci s databází. Její objekt může
poskytnout chybová data jako jsou:
• QSqlError::driverText() – vrací string s popisem chyby tak, jak ho poskytne
ovladač
• QSqlError::databaseText() – vrací string s popisem chyby tak, jak ho poskytuje
databáze
• QSqlError::number() – vrací databázově specifické chybové číslo nebo -1, pokud
chybu nelze určit
• QSqlError::type() – vrací výčtový typ ErrorType nebo -1, pokud typ nelze určit
42
2.2. QT KAPITOLA 2. ROZHRANÍ PŘÍSTUPU K DATABÁZI
Tabulka 2.7 popisuje výčtový typ QSqlError::ErrorType a jeho konstanty.
Tabulka 2.7: Výčtový typ enum QSqlError::ErrorType
Konstanta Hodnota Popis
QSqlError::NoError 0 Žádná chybaQSqlError::ConnectionError 1 Chyba při spojeníQSqlError::StatementError 2 Chyba v syntaxi SQL příkazuQSqlError::TransactionError 3 Chyba při transakciQSqlError::UnknownError 4 Neznámá chyba
Dále existují funkce QSqlDatabase::lastError() a QSqlQuery::lastError(), které
vrací typ QSqlError a lze je zavolat zpětně po výskytu chyby. První z nich vrací informace
o poslední chybě, která se vyskytla v databázi a druhá informuje o poslední chybě, která se
vyskytla v dotazu. Následující příklad testuje, zda bylo otevřeno správně spojení. Pokud
nastane chyba, vypíše se na standardní chybový výstup text poskytnutý databázovým
strojem.[7]
if (!db.open())
std::cerr << db.lastError().databaseText().toStdString();
2.2.3 Objekt QSqlQuery
QSqlQuery je třída, která poskytuje rozhraní pro vykonávání SQL příkazů a prochá-
zení výsledků dotazů. Zahrnuje vytváření, procházení a získávání dat z SQL příkazů.
Může být použita jak k DML příkazům, tak i DDL příkazům. Může dokonce být po-
užita v případě příkazů specifických pro určitou databázi, které nejsou v SQL standardu
(SET DATESTYLE=ISO (PostgreSQL))2. Pro vykonání SQL dotazu se jednoduše vytvoří
QSqlQuery objekt a zavolá se funkce QSqlQuery::exec():
QSqlQuery query("first");
query.exec("SELECT id,x,y,z FROM points WHERE x > 1000000");
QSqlQuery konstruktor přijímá volitelný QSqlDatabase objekt, který specifikuje data-
bázové spojení. Pokud není uveden, je použito defaultní spojení. Pokud se vyskytne chyba,
funkce exec() vrátí false. Chyba lze vyvolat pomocí QSqlQuery::lastError().
2Pro práci s indexy je implementována speciální třída QSqlIndex, která s nimi dokáže manipulovat.
43
2.2. QT KAPITOLA 2. ROZHRANÍ PŘÍSTUPU K DATABÁZI
Procházení výsledků
QSqlQuery poskytuje přístup k výsledkům vráceným databází po zavolání funkce exec().
Výsledky se prochází pomocí speciálního ukazatele, který je umístěn před první řádek vý-
sledku, který je poskytnut databází. Pro procházení jednotlivými řádky výsledky je nutné
použít jednu z funkcí popsaných v tabulce 2.8.
Tabulka 2.8: Funkce třídy QSqlQuery pro posun kurzoru
Funkce Popis
next() přesune ukazatel na další řádek výsledkuprevious() přesune ukazatel na předchozí řádek výsledkufirst() přesune ukazatel na první řádek výsledkulast() přesune ukazatel na poslední řádek výsledkuseek(int, bool) přesune ukazatel na řádek určený parametrem index
Pokud je potřeba pohybovat ukazatelem pouze vpřed (např. next()), je možné použít
funkci setForwardOnly(), která výrazně sníží paměťové nároky a zvýší výkon na některých
databází. Jakmile ukazatel ukazuje na platný řádek, je možné data získat pomocí funkce
value(), která vrací QVariant. Třída QVariant se chová jako souhrn nejběžnějších Qt
datových typů. Pomocí funkcí toXXX() se převede objekt na běžné datové typy (int, double,
. . .).
QSqlQuery query;
query.exec("SELECT id,x,y,z FROM points WHERE x > 1000000");
while (query.next())
{int id = query.value(0).toInt();
double x = query.value(1).toDouble();
double y = query.value(2).toDouble();
double z = query.value(3).toDouble();
doSomething();
}
Argument funkce value() určuje index sloupce výsledku a je číslován od nuly. Použití
SELECT * dotazů není doporučováno, protože je nejasné pořadí výsledku.
Pomocí funkce numRowsAffected() lze zjistit, kolik bylo zahrnuto řádků v non-SELECT
příkazu (INSERT, UPDATE). Pro SELECT příkazy se používa funkce size()
44
2.2. QT KAPITOLA 2. ROZHRANÍ PŘÍSTUPU K DATABÁZI
Funkce QSqlQuery::prepare()
QSqlQuery také podporuje „předpřipravené příkazyÿ. Některé databázové systémy ne-
podporují tyto funkce, tak Qt tuto vlastnost emuluje. Pomocí funkce hasFeature() lze
zjistit zda ovladač podporuje vlastnost nebo nikoliv. Qt podporuje dvě syntaxe zápisu zá-
stupných znaků. Zástupný znak :id (jako v Oracle databázích) nebo ? (jako např. JDBC).
Nelze je však míchat v rámci jednoho příkazu. Pokud databáze podporuje jednu ze syntaxí
nativně, Qt jednoduše pošle příkaz do DBMS. Pokud nepodporuje, Qt před zpracováním
dotazu nasimuluje požadovanou syntaxi. Příklad možných zápisu:
QSqlQuery query;
query.prepare("INSERT INTO points (x,y,z) VALUES (?,?,?)");
query.bindValue(0,1000);
query.bindValue(1,950);
query.bindValue(2,250);
query.exec();
Druhý způsob:
QSqlQuery query;
query.prepare("INSERT INTO points (x,y,z) VALUES (:x,:y,:z)");
query.bindValue(0,1000);
query.bindValue(1,950);
query.bindValue(2,250);
query.exec();
Ve výše uvedených příkladech jsou do tabulky bodů vkládány souřadnice. Pomocí
funkce bindValue() jsou nahrazeny zástupné znaky zadanými hodnotami. První argu-
ment určuje pořadí zástupného znaku a druhý vkládanou hodnotu.[7]
2.2.4 Transakce
Stejně jako v případě JDBC je u databázového spojení pomocí Qt frameworku im-
plicitně nastaven auto-commit mode, tedy po zavolání exec() jsou automaticky zapsány
změny v DBMS pro každý jednotlivý příkaz. Pokud hasFeature(QSqlDriver::Transactions)
vrátí true, databázový systém podporuje transakce. Zavoláním funkce transaction() se
zahájí transakce. Funkce musí být zavolána ještě před vytvořením objektu QSqlQuery.
Všechny příkazy jsou poté zahrnuty do transakce až do části programu, kdy je zavolána
buď funkce commit(), nebo funkce rollback().
45
2.2. QT KAPITOLA 2. ROZHRANÍ PŘÍSTUPU K DATABÁZI
QSqlDatabase::database().transaction();
QSqlQuery query;
query.exec("SELECT id FROM theodolits WHERE name = ’ZEISS Theo 010A’");
if (query.next())
{int machineId = query.value(0).toInt();
query.exec("INSERT INTO projects (id, name, machine_id) "
"VALUES (4001, ’Tunel Blanka’, "
+ QString::number(machineId) + ’)’);
}QSqlDatabase::database().commit();
Nejprve je zavolána funkce transaction() a tím je zahájena transakce. Poté je vy-
hledáno id přístroje, se kterým byla zakázka změřena. Pokud takový přístroj existuje,
je přiřazen k příslušnému projektu do tabulky projektů. Funkcí commit() je transakce
potvrzena.[7]
2.2.5 Dávková modifikace
V Qt lze skrze předpřipravené příkazy využít, například dávkové inserty do databázo-
vého systému. Na místa zástupných znaků (?, . . .) se místo jednoho elementu vloží seznam
elementů. Poté se pomocí funkce QSqlQuery::execBatch() pošle celá dávka databázo-
vému systému. Funkce vrací true, pokud je příkaz vykonán korektně.
QSqlQuery q;
q.prepare("INSERT INTO points (x,y,z) VALUES (?, ?, ?)");
QVariantList xList;
xList << 1000 << 1005 << 1004 << 1007 << 1008;
q.bindValue(0, xList);
QVariantList yList;
yList << 565 << 567 << 559 << 575 << 552;
q.bindValue(1, yList);
QVariantList zList;
zList << 320.26 << 234.16 << 450.78 << QVariant(QVariant::Double) << 546.12;
q.bindValue(2, zList);
q.execBatch();
46
2.2. QT KAPITOLA 2. ROZHRANÍ PŘÍSTUPU K DATABÁZI
V příkladě byly vloženy dávkově souřadnice bodů. Pomocí speciálního seznamu QVariantList,
obsahující objekty typu QVariant, jsou vloženy souřadnice na místo zástupných znaků.
QVariant je třída, která se tváří jako spojení všech běžných datových typů v Qt. Je nutné,
aby všechny seznamy měly shodný počet položek a také aby v každém seznamu byly shodné
typy. Hodnotu NULL lze vložit pomocí objektu QVariant příslušného datového typu jako
je to uvedeno v příkladu pro seznam souřadnic z.[7]
2.2.6 Třída QSqlRecord
QSqlRecord třída zapouzdřuje záznam v databázi3. Zapouzdřuje funkcionalitu a cha-
rakteristiku databázového záznamu (zpravidla řádek). Pomocí členských funkcí podpo-
ruje přidávání, odebírání, nastavování nebo získávání hodnot. Tabulka 2.9 uvádí některé
funkce.[7]
Tabulka 2.9: Členské funkce třídy QSqlRecord
Funkce Popis
append(QSqlField) přidá kopii hodnoty na konec záznamuclear() smaže všechny hodnoty záznamucount() vratí počet hodnot v záznamufield(int) vratí hodnotu (objekt QSqlField) zadaného sloupceisEmpty() testuje, zda jsou v záznamu nějaké hodnotyisNull(int) testuje, zda je hodnota zadaného sloupce nullremove(int) smaže hodnotu na určené pozicisetNull(int) nastaví null na určené pozicisetValue(int, QVariant) nastaví hodnotu na určené pozicivalue(int) vratí hodnotu (objekt QVariant) zadaného sloupce
2.2.7 Modelové třídy
Navíc ke QSqlQuery Qt nabízí tři třídy vyšší úrovně pro přístup do databáze. Tyto
třídy jsou uvedeny v tabulce 2.10.
Zmíněné třídy jsou odvozeny od třídy QAbstractTableModel a ulehčují tak prezentaci
dat pomocí grafických tříd jako jsou například QTableView nebo QListView. Další výhodou
je, že ulehčují přechod na jiný typ zdroje dat. Například při přechodu z databáze na
3Existuje ještě třída QSqlField, která zapouzdřuje pouze hodnotu záznamu.
47
2.2. QT KAPITOLA 2. ROZHRANÍ PŘÍSTUPU K DATABÁZI
Tabulka 2.10: Modelové třídy v Qt
Třída Popis
QSqlQueryModel Model určený pouze pro čtení pro libovolný SQL dotaz.QSqlTableModel Model pro čtení i zápis, který pracuje na jedné tabulce.QSqlRelationalTableModel Podtřída QSqlTableModel s podporou cizích klíčů.
ukládání dat do XML, jedná se v podstatě jen o změnu jednoho datového modelu za
druhý.
QSqlQueryModel
Jak již bylo napsáno, tato třída nabízí model určený pouze pro čtení.
QSqlQueryModel model;
model.setQuery("SELECT * FROM employee");
for (int i = 0; i < model.rowCount(); ++i)
{int id = model.record(i).value("id").toInt();
QString name = model.record(i).value("name").toString();
qDebug() << id << name;
}
Po nastavení vybraného dotazu pomocí funkce setQuery() se použije funkce record()
která vrací jeden záznam (typ QSqlRecord) a z toho je funkcí QSqlRecord::value()
vybrán požadovaný sloupec (id, name).
QSqlTableModel
QSqlTableModel dědí z QSqlQueryModel. Poskytuje alternativu vyšší úrovně k QSqlQuery
pro modifikování jednotlivých SQL tabulek. Pokud použijeme tento model, není nutná
žádná znalost SQL syntaxe. Třída totiž obsahuje funkce zastupující jednoduché SQL ope-
race, jako je například filtrování pomocí WHERE. Jednoduchý SELECT použitím modelu
vypadá takto:
QSqlTableModel model;
model.setTable("employee");
model.setFilter("salary > 50000");
48
2.2. QT KAPITOLA 2. ROZHRANÍ PŘÍSTUPU K DATABÁZI
model.setSort(2, Qt::DescendingOrder);
model.select();
model.setEditStrategy(QSqlTableModel::OnManualSubmit)
for (int i = 0; i < model.rowCount(); ++i)
{double salary = model.record(i).record.value("salary").toInt();
salary *= 1.1;
record.setValue("salary", salary);
model.setRecord(i, record);
}model.submitAll();
Z tabulky jsou nejprve vybráni zaměstnanci s platem vyšším než je 50 000 a jejich plat
byl následně zvýšen o deset procent. Je možné si všimnout, že výběr z tabulky byl proveden
bez pomoci SQL příkazu. Vše obstaraly funkce třídy QSqlTableModel. Objekt model byl
nejprve naplněn daty z databáze zavoláním funkce select(). Pak byla zvolena editační
strategie viz tabulka 2.11. Dále byl procházen výsledek, data v modelu byla měněna a
nakonec byla zavolána funkce submitAll(), která vložila změny do databáze.
Tabulka 2.11: Výčtový typ enum QSqlTableModel::EditStrategy
Konstanta Hodnota PopisQSqlTableModel::OnFieldChange 0 Všechny změny v modelu jsou ihned aplikovány
do databáze.QSqlTableModel::OnRowChange 1 Změny řádku jsou aplikovány do databáze, když
je vybrán jiný řádek.QSqlTableModel::OnRowChange 2 Všechny změny jsou uloženy v modelu, dokud
není zavolán submitAll() nebo revertAll.
QSqlRelationalTableModel
QSqlRelationalTableModel rozšiřuje QSqlTableModel a přidává podporu cizích klíčů.
Pomocí funkce setRelation() se nastavují vztahy mezi tabulkami pomocí klíčů. Uživateli
se pak navenek nezobrazují hodnoty identifikátorů, ale přímo názvy.[7]
49
2.3. POROVNÁNÍ KAPITOLA 2. ROZHRANÍ PŘÍSTUPU K DATABÁZI
2.3 Porovnání
2.3.1 Rozdíly
Spojení
Přístup obou rozhraní k otevíraní a uzavírání spojení je velice podobný. V obou přípa-
dech je nutné použít ovladač vybrané databáze. JDBC používá ovladače vydávané výrobci
databází a je nutné je získávat zpravidla od těchto výrobců a přidat jej do classpath. Pomocí
statické metody DriverManager.getConnection je načten ovladač z classpath a vytvořen
objekt Connection, který představuje navázané spojení.
Qt standardně obsahuje ovladače nejběžnějších databázových systémů, a proto není
nutné se tím dále zabývat. Použitím statické metody QSqlDatabase::addDatabase() je
nejprve vytvořeno spojení. Metoda přebírá parametr s použitým ovladačem a názvem spo-
jení. Metodami setXXX() jsou nastaveny parametry spojení. Až metodou QSqlDatabase::open()
se spojení navazuje.
JDBC:
Connection conn = DriverManager.getConnection(url);
Qt:
QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL", "first");
db.open();
Spojení se v obou případech uzavírá metodou close(). V Qt se ještě statickou funkcí
QSqlDatabase::removeDatabase() spojení odebere.
Výjimky
Obě rozhraní poskytují jednu základní třídu, která umí obsluhovat databázová chybová
hlášení. V JDBC je to třída SQLException, v Qt třída QSqlError. JDBC ještě zahrnuje
podtřídy třídy SQLException, které specifikují chyby určitého charakteru. Obě třídy obsa-
hují metody pro práci s chybovým hlášením, zejména pro jejich vypsání. Podrobnější popis
obou tříd lze najít výše viz 2.1.2 a 2.2.2.
Hlavní rozdílem není přístup samotných rozhraní, ten je velice podobný, ale rozdíl
přístupu k výjimkám obou programovacích jazyků. V JDBC se pracuje s výjimkami pomocí
50
2.3. POROVNÁNÍ KAPITOLA 2. ROZHRANÍ PŘÍSTUPU K DATABÁZI
try-catch bloků. V bloku try se nacházejí metody, které mohou vyvolat výjimku a v bloku
catch se tyto výjimky obslouží:
try {
// ...
} catch (SQLException e) {
// ...
} finally {
// provede se i v pripade vyvolani vyjimky
}
Zato v Qt se používají funkce QSqlDatabase::lastError() nebo QSqlQuery::lastError(),
které vyvolají poslední chybu a vrátí objekt typu QSqlError:
if (!query.exec())
qDebug() << query.lastError().;
Procházení výsledku
Procházení výsledku obou rozhraní má velmi podobnou filosofii. Používají tzv. ukaza-
tel, který prochází řádky výsledku uložené v objektu ResultSet (JDBC) nebo QSqlQuery
(Qt). V praxi se dotaz pošle databázovému systému a ten vrátí výsledek, který je uložen
do příslušného objektu. Ukazatel je v obou případech implicitně nastaven před prvním
záznamem. Tento ukazatel se umí pohybovat pomocí metod, které jsou v obou případech
téměř totožné.
JDBC:
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT id,x,y FROM points");
while (rs.next()) {
int id = rs.getInt(1);
double x = rs.getDouble(2);
double y = rs.getDouble(3);
}
Qt:
QSqlQuery query;
query.exec("SELECT id,x,y FROM points");
51
2.3. POROVNÁNÍ KAPITOLA 2. ROZHRANÍ PŘÍSTUPU K DATABÁZI
while (query.next()) {
int id = query.value(0).toInt();
double x = query.value(1).toDouble();
double y = query.value(2).toDouble();
}
Z příkladu je vidět, že obě rozhraní pracují na stejném principu. Jedním z rozdílů
je například to, že Qt pracuje s pouze jedním základním objektem QSqlQuery a JDBC
má jeden objekt pro příkaz (Statement) a jeden pro výsledek (ResultSet). Dalším roz-
dílem je možnost vícero nastavení chování ukazatele v JDBC, v Qt jde pouze nastavit
setForwardOnly(). Podrobnější informace viz sekce 2.1.3 a 2.2.3
Transakce
Transakce mají rovněž podobnou filosofii. Obě rozhraní mají implicitně nastaven auto-
commit mód. V JDBC je nutné jej explicitně zamezit pomocí Connection.setAutoCommit(false)
a tím tak zahájit transakci. V Qt je to funkce QSqlDatabase::transaction(), která také za-
mezí auto-commit a zahájí transakci. Všechny příkazy (v obou případech) směrující na
databázi jsou zahrnuty do jedné transakce. Transakce je ukončena metodami commit(),
resp. rollback(). Ukázky obou přístupů k transakcím jsou uvedeny v částech 2.1.4 a 2.2.4.
JDBC dále poskytuje podporu izolačních úrovní. Lze si pro jednotlivé konexe nastavit
libovolnou úroveň pomocí metody setTransactionIsolation(), více viz 2.1.4 a B.3.
Předpřipravené příkazy
Obě rozhraní podporují předpřipravené příkazy. JDBC skrze objekt PreparedStatement,
Qt skrze metodu prepare() třídy QSqlQuery. V Qt je možné použít dvě varianty zástup-
ných znaků. Znak ? jako v JDBC (JDBC má pouze tento) a znak :jmeno sloupce. Hodnoty
se „upínajíÿ na pozice zástupných znaků pomocí členských metod objektů PreparedStatement
resp. QSqlQuery.
JDBC:
PreparedStatement pstmt = conn.prepareStatement(
"INSERT INTO points (x,y) VALUES (?,?)");
pstmt.setDouble(1,1020.123)
pstmt.setDouble(2,510.321)
pstmt.executeUpdate();
52
2.3. POROVNÁNÍ KAPITOLA 2. ROZHRANÍ PŘÍSTUPU K DATABÁZI
Qt:
QSqlQuery query;
query.prepare("INSERT INTO points (x,y) VALUES (?,?)");
query.bindValue(0,1020.123);
query.bindValue(1,510.321);
query.exec();
Pro JDBC jsou to metody setXXX(), pro Qt bindValue(). Jediným rozdílem je to, že
se u JDBC indexují zástupné znaky od jedničky, u Qt od nuly. V Qt se pak příkaz odesílá
databázi univerzální funkcí exec(). JDBC metodou executeUpdate() pro non–SELECT
příkazy (vrací int) a metodou executeQuery() pro SELECT dotazy (vrací ResultSet).
Dávková modifikace
Dávkové zpracování podporují opět obě rozhraní. Qt řeší dávkovou modifikaci skrze
předpřipravené příkazy, na místo zástupného znaku nedosadí jednu hodnotu, ale celý se-
znam hodnot. Databázovému systému se pak pošle dávka stejných příkazů s měnícími se
hodnotami odebranými ze seznamu. Místo funkce exec() se potvrzuje funkcí execBatch().
QSqlQuery query;
query.prepare("INSERT INTO points (x,y) VALUES (?,?)");
QVariantList xList;
xList << 1000 << 1005 << 1004 << 1007 << 1008;
q.bindValue(0, xList);
QVariantList yList;
yList << 565 << 567 << 559 << 575 << 552;
q.bindValue(1, yList);
q.execBatch();
JDBC nabízí trochu jiný přístup. Do objektu Statement i jeho potomků lze ukládat
příkazy pomocí metody addBatch(). Je možné uložit všechny příkazy, které vrací počet
změn. Nejedná se o SELECT dotazy (vrací ResultSet). Pokud jsou do objektu vloženy
všechny příkazy, zavolá se metoda executeBatch(), která vrátí pole počtu změn jednotli-
vých příkazů.
Statement stmt = conn.createStatement();
stmt.addBatch("INSERT INTO points (x,y) VALUES (1020.123,510.321)");
stmt.addBatch("UPDATE points SET y = 510.322 WHERE x = 1020.123");
int[] updateCounts = stmt.executeBatch();
Je možné použít dávky i s předpřipravenými příkazy podobně jako u Qt viz 2.1.6
53
2.3. POROVNÁNÍ KAPITOLA 2. ROZHRANÍ PŘÍSTUPU K DATABÁZI
2.3.2 Shrnutí
Přístup obou rozhraní z hlediska použití tříd a jejich metod je velice podobný. Je možné,
že se jedno druhým inspirovalo. Zato technický přístup nižší úrovně je poměrně odlišný.
JDBC je součástí Javy SE, je tedy vyvíjeno vývojáři samotné platformy, proto není nutné
ho získávat samostatně. Naopak Qt framework je produkt třetí strany (v současné době
firma Nokia).
V tabulce 2.12 je přehledně uvedeno porovnání a shrnutí obou rozhraní. Z výsledku je
patrné, že obě rozhraní jsou si velice blízké. S nadsázkou se dá říci, že jsou stejné až na
pár nuancí.
Tabulka 2.12: Shrnutí rozhraní JDBC a Qt
JDBC (Java) Qt (C++ )
Výjimky 3 3
Transakce 3 3
Předpřipravené příkazy 3 3
Dávková modifikace 3 3
Modifikace výsledku 3 3
Modelové třídy 3 3
Isolation úrovně 3 7
54
Kapitola 3
Aplikace
Kapitola uvádí testování popisovaných databázových technologií skrze jednoduché tes-
tovací aplikace využívající zmiňovaná rozhraní. Jedná se tedy o aplikaci v programovacím
jazyku Java s využitím JDBC rozhraní, aplikaci v programovacím jazyku C++ s využitím
QtSql modulu a program psql. V závěru kapitoly je uvedeno porovnání.
Databáze
Testování bylo prováděno nad databází PostgreSQL verze 8.4. Byly vytvořeny tři ta-
bulky (test, test index, test part). Do první vytvořené tabulky bylo vygenerováno
100 000 000 záznamů. Do zbývajících byly stejné záznamy zkopírovány, aby všechny ta-
bulky obsahovaly stejná data. Tabulky byly vytvořeny s následujícími parametry:
CREATE TABLE test (
id SERIAL,
x REAL,
y REAL,
z~REAL,
time TIMESTAMP);
Obdobně i pro tabulky test index a test part. Pak byl pro tabulku test part im-
plementován partitioning viz 1.4.2. Následně byla vygenerována data pro jednu tabulku a
do ostatních zkopírována pomocí příkazu:
INSERT INTO test_index SELECT * FROM test;
Nakonec byl vytvořen index nad sloupcem time v tabulce test index i ve všech par-
titions tabulky test part.
55
KAPITOLA 3. APLIKACE
Java
Aplikace napsaná v Javě využívá pro tvorbu GUI grafickou knihovnu Swing. Komu-
nikuje s DBMS pomocí rozhraní JDBC. Je primárně určena k měření časů běhu dotazů
a zobrazení výsledků v tabulce. GUI obsahuje roletové menu se seznamem tabulek po-
skytnutým databázovým systémem. Při startu programu se automaticky načtou tabulky.
Dále aplikace obsahuje dva seznamy. První seznam (Indexes) obsahuje použité indexy pro
vybranou tabulku a druhý (Columns) vypisuje seznam sloupců tabulky. Do textového pole
(Condition) lze vpisovat omezující podmínky dotazu (např. klauzule WHERE). Tlačítkem
(Run!) je dotaz odeslán databázovému systému. Ukázka aplikace je uvedena na obrázku 3.1.
Obrázek 3.1: Ukázka aplikace napsané v Javě
Po stisku tlačítka (Run!) se zobrazí doba běhu dotazu. Doba běhu dotazu je měřena
pro příkaz:
ResultSet rs = st.executeQuery(statement);
56
KAPITOLA 3. APLIKACE
Tedy doba, kdy program vyšle dotaz databázovému stroji, ten jej zpracuje, odešle vý-
sledek a ten se uloží do objektu (ResultSet). Měřený čas v milisekundách je poté vypsán
v GUI. Do výsledného času tedy není zahrnuta doba, která je nutná pro vytvoření spo-
jení ani doba zpracování výsledku aplikací. Kromě času program vypisuje počet vrácených
záznamů výsledku a tyto záznamy zobrazí v tabulce. Ukázka aplikace po spuštění dotazu
nad tabulkou s implementovaným partitioningem (test part) je na obrázku 3.2.
Obrázek 3.2: Zobrazení výsledku Java aplikace
C++
C++ aplikace využívá Qt framework, jak pro vytvoření GUI, tak pro komunikaci s da-
tabází. Je tedy nutné připojit moduly QtCore, QtGui a QtSql. Aplikace obsahuje téměř
totožné uživatelské rozhraní a funkčnost jako předešlá Java aplikace. Obsahuje roletové
menu pro výběr tabulky. Zobrazení indexů a sloupců pro vybranou tabulku i textové pole,
do kterého se zadává omezující podmínka. Tlačítko (Run!) rovněž odesílá dotaz. Ukázka
uživatelského rozhraní aplikace je na obrázku 3.3.
57
KAPITOLA 3. APLIKACE
Obrázek 3.3: Ukázka aplikace napsané v C++
Pro jednodušší implementaci tabulky s výsledky byl místo standardního objektu QSqlQuery
použit model QSqlQueryModel. Doba běhu dotazu byla měřena pro funkci setQuery:
model->setQuery(statement);
Dotaz je odeslán databázovému systému a výsledek poskytnutý DBMS je uložen do
modelového objektu (QSqlQueryModel). Čas byl změřen v milisekundách za pomocí ob-
jektu QTime a je v opět zobrazen v GUI. Do zobrazeného času není zahrnuto zpracování
výsledku aplikací. Počet řádků výsledku je vypsán ve spodním textovém poli. Aplikace
umí také zobrazit záznamy v přehledné tabulce. Ukázka aplikace po spuštění dotazu nad
tabulkou s implementovaným partitioningem je na obrázku 3.4.
58
KAPITOLA 3. APLIKACE
Obrázek 3.4: Zobrazení výsledku C++ aplikace
psql
Pro porovnání byly stejné dotazy spuštěny i v programu psql. Tento program je termi-
nálová front-end aplikace pro PostgreSQL. Lze pomocí něho interaktivně spouštět dotazy,
přijímat výsledky nebo kontrolovat čas běhu dotazu (příkaz \timing).
Porovnání
Obě testovací aplikace byly vytvořeny z důvodu demonstrace aplikačních rozhraní a
testování popsaných databázových technologií (index, partitioning). Funkcionalita obou
aplikací je stejná, aby bylo možné obě použitá rozhraní porovnat. Obě používají k zobrazení
výsledků v tabulkách modelové třídy. Zdrojové kódy jsou uloženy na přiloženém CD.
V tabulce 3.1 je uvedeno porovnání časů běhů dotazů jednotlivých aplikací. Všechny
časy byly změřeny několikrát a zapsán byl jejich průměr. Dotazy byly vykonány i v pro-
gramu psql. Čas je měřen v milisekundách.
59
KAPITOLA 3. APLIKACE
Měření bylo provedeno pro dotazy:
1. SELECT * FROM [tabulka]WHERE time <= ’1976-12-09 13:50:00’ AND time >= ’1976-12-09 12:50:00’;
Rows: 212
2. SELECT * FROM [tabulka]WHERE time > ’2009-12-24 00:00:00’;
Rows: 41142
3. SELECT * FROM [tabulka]WHERE time BETWEEN ’1970-05-06 06:06:00’ AND ’1970-06-06 06:06:00’;
Rows: 169599
Tabulka 3.1: Porovnání časů jednotlivých aplikací
Aplikace Dotaz test [ms] test index [ms] test part [ms]
Java1. 211005 10 122. 206394 2820 19103. 208449 213192 34518
C++1. 211271 6 82. 206616 3260 22833. 209168 213926 35089
psql1. 206758 2 32. 205864 2534 18453. 206907 208408 32740
Předem je nutné říci, že uvedené časy jsou pouze orientační a závislé na použitém
stroji. Na výkonnějších databázových strojích by byla doba běhu dotazů o poznání kratší.
Z tabulky je zřejmé, že dotazy generující malý počet záznamů jsou na tabulkách s indexy
a partitioningem nesrovnatelně rychlejší. S rostoucím počtem záznamů klesá efektivita
indexů. Z třetího dotazu je zřejmé, že dotaz je stejně náročný pro tabulku s i bez indexu.
Dotaz nad tabulkou s partitioningem je o poznání rychlejší, protože je skenován index
vytvořený pro část dat náležících jedné partition.
Dále je možné si všimnout, že se Java aplikace umí rychleji vypořádat s vráceným
výsledkem, který obsahuje větší počet záznamů, než aplikace v C++ využívající Qt fra-
mework. Exekuční plány jednotlivých dotazů je možné shlédnout v příloze C.
60
Závěr
Bakalářská práce se zaměřovala na popis databázových technologií pro práci s velkým
rozsahem dat. Tyto databáze jsou běžné například v katastru nemovitostí nebo při zpraco-
vání bodů měřených pomocí laserového skenování. Cílem práce bylo zmapování možností
indexů a partitioningu. Bylo zjištěno, že dotazy nad tabulkami s indexy a partitionin-
gem jsou o poznání rychlejší. Pozornost byla také věnována exekučním plánům a do jisté
míry optimalizaci dotazů. Jedním z výsledků této práce je naznačení, jak vytvářet tabulky
obsahující velké množství dat a zároveň velký počet modifikací.
Druhá část popisuje rozhraní přístupu do databáze pro jazyk Java resp. C++. Obě
rozhraní jsou si velice podobná, až na drobné implementační rozdíly. Výsledkem této části
je porovnání obou rozhrání a dvě zkušební aplikace, které testují rozhraní spolu s popiso-
vanými technologiemi z první části. Testování ukázalo, že obě rozhraní jsou si z hlediska
výkonu rovnocenné. Dalším pozitivem, které mají obě rozhraní společné, je kvalitně zpra-
covaná dokumentace.
Dále je nutné zmínit, že mě tato práce obohatila o znalosti, které přesahují rámec učiva
předmětů zabývající se podobnou tematikou na naší škole. V případě zájmu lze text využít
jako studijní pomůcku. Závěrem bych podotkl, že zmiňované technologie (zejména parti-
tioning) jsou velmi silným nástrojem, jak spravovat stále zvyšující se nároky na množství
dat v databázích.
61
Použité zdroje
[1] Otevřená encyklopedie WikipediA
http://www.wikipedia.org
[2] Dokumentace PostgreSQL verze 8.4.6
http://www.postgresql.org/docs/8.4/static/
[3] České stránky věnované PostgreSQL
http://www.pgsql.cz/index.php/PostgreSQL
[4] Tutoriál JDBC API
http://download.oracle.com/javase/tutorial/jdbc/basics/index.html
[5] Java SE 6 API Dokumentace
http://download.oracle.com/javase/6/docs/api/index.html
[6] ISO/ANSI standard SQL92
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
[7] Dokumentace Qt verze 4.7
http://doc.qt.nokia.com/4.7/index.html
62
Příloha A
Oracle functionality
Orafce je balík užitečných funkcí, které nabízí RDBMS Oracle a zároveň nejsou v ANSI
SQL ani PostgreSQL, napsaný Pavlem Stěhulem. V současné době není prakticky možné,
aby se do PostgreSQL dostala funkce, která nemá podporu standardu. Za tímto účelem je
vyvíjena databáze EnterpriseDB, která poskytuje kompatibilitu Oracle Database, a nebo
právě použití Orafce. Následuje podrobnější popis základních funkcí a výčet některých
dalších balíčků implementovaných funkcí.[3]
Základní Funkce
V tabulce A.1 jsou uvedeny funkce převážně vracející typ date. V tabulce A.2 jsou uve-
deny funkce pracující s řetězci, ale zpravidla k nim existují ANSI SQL ekvivalenty. V ta-
bulce A.3 jsou další zajímavé funkce sloužící hlavně pro ulehčení portu aplikací z Oracle.
Jiném v případě je lepší využít ANSI funkce COALESCE.
Tabulka A.1: Orafce: Funkce pro práci s typem date
Funkce Popis
add months(date, integer) přičte zadaný počet měsícůast date(date) vrací poslední den v měsícinext day(date, text) vrací první následující den v týdnu po zadaném dnumonths between(date, date) vrací počet měsíců(float) mezi zadanými dnytrunc(date, text) ořízne datum podle zadaného formáturound(date, text) zaokrouhlí datum podle zadaného formátu
I
PŘÍLOHA A. ORACLE FUNCTIONALITY
Tabulka A.2: Orafce: Funkce pro práci s řetězci
Funkce Popis
instr(text, text, [int[,int]]) hledá n-tý výskyt vzoru v řetězcireverse(text) zrcadlově převrátí řetězecoracle.substr(text, [int[,int]]) vrací část řetězce
Tabulka A.3: Orafce: Ostatní funkce
Funkce Popis
nvl(element, element) vrací první neprázdný parametrnvl2(element, element, element) vrací první neprázdný parametrconcat(text, text) slučuje řetězce, oproti operátoru || ignoruje NULL
Další balíky funkcí
Balík Orafce obsahuje mimo jiné i funkce frameworku PLVision:
• PLVdate
• PLVsubst
• PLVlex
• PLVstr
• PLVchr
• DBMS ALERT
• DBMS OUTPUT
• DBMS UTILITY
• DBMS PIPE
• UTL FILE
[3]
II
Příloha B
ACID
ACID je sada základních vlastností, kterou by měl databázový systém splňovat, aby
jeho transakce byly zpracovány spolehlivě. Každá z těchto vlastností předepisuje, jak se
má databázový systém chovat při zpracování transakcí. Transakce je soubor příkazů, který
je brán jako jedna logická operace. Například peněžní převod mezi bankami, kdy jedna
transakce zahrnuje mimo jiné příkazy odepsání částky z jednoho účtu a připsání na další.[1]
ACID zahrnuje 4 základní vlastnosti:
• Atomicita (Atomicity)
• Konzistence (Consistency)
• Izolovanost (Isolation)
• Trvalost (Durability)
B.1 Atomicity
Atomicita vyžaduje pravidlo „všechno nebo nicÿ. To znamená, že se buď provedou
všechny operace v rámci jedná transakce, nebo žádné a databáze se vrátí do své výchozí
pozice. Takové transakci se pak říká, že je atomická. Z tohoto důvodu se uživatelé nemusí
obávat, že se transakce neprovede úplně.
III
B.2. CONSISTENCY PŘÍLOHA B. ACID
B.2 Consistency
Konzistence se stará o to, aby se databáze po provedení transakce opět dostala do
konzistentního stavu. Mohou být zapsána pouze platná data, která neporušují integritní
omezení.
B.3 Isolation
Izolovanost je vlastnost, která definuje, jak a kdy bude jedna operace viditelná pro dru-
hou souběžnou operaci. Definuje několik úrovní a typů čtení, které mohou nastat. Pokud
by databázový systém nepodporoval izolovanost, data by se mohla dostat do nekonzistent-
ního stavu. Moderní DBMS povolují explicitní nastavení úrovně (isolation level). S rostoucí
úrovní roste pravděpodobnost tzv. deadlock 1.
Typy čtení
ANSI/ISO standard SQL92 definuje tři varianty čtení, které mohou nastat při vyko-
návání souběžných transakci. Příkladem může být transakce A čtoucí data, která mohou
být během této doby změněna transakcí B. Jednotlivé varianty budou demonstrovány na
příkladech vztahu dvou transakcí:
Dirty Reads Transakce A změní řádek. Transakce B potom přečte data z řádku modifi-
kovaného transakcí A, aniž by transakce A provedla COMMIT. Pokud transakce A provede
ROLLBACK, transakce B přečetla řádek, který nebyl potvrzen pomocí COMMIT, a proto
jsou data získaná transakcí B považována za neplatná.
Non-Repeatable Reads Transakce A čte řádek. Transakce B poté změní nebo smaže
tento řádek a provede COMMIT. Pokud transakce A přečte znova ten samý řádek, může
dostat jiná data nebo zjistit, že řádek byl smazán.
Phantom Reads Transakce A čte sadu řádků, které odpovídají určité vyhledávací pod-
mínce (zpravidla klauzule WHERE). Transakce B potom vykoná příkazy, které vygenerují
jeden nebo více řádků, které odpovídají podmínce z transakce A. Pokud transakce A zo-
pakuje čtení se stejnou podmínkou, dostane úplně jinou sadu řádků.[6]
1situace, kdy je první akce podmíněna dokončením druhé akce a ta zároveň čeká na dokončení prvníakce.
IV
B.4. DURABILITY PŘÍLOHA B. ACID
Úrovně (isolation levels)
SQL92 definuje tyto čtyři úrovně:
Read Uncommitted je nejnižší úroveň. Povoluje i dirty reads, tzn. čtení nepotvrzených
dat jinou transakcí.
Read Committed je založeno na uzamykání zápisu do řádků pro souběžné operace do
dokončení transakce. Čtecí zámek je uvolněn, pokud je vykonán SELECT, takže může
nastat non-repeatable reads.
Repeatable Read uzamyká čtení i zápis až do konce transakce. Povoluje phantom reads.
Serializable je nejvyšší úroveň. Spravuje tzv. range-lock, který je aplikován, když je za-
dán SELECT s klauzulí WHERE. Zamezuje phantom reads.
Tabulka B.1 popisuje vzájemný vztah mezi úrovněmi a typy čtení. Symbol 3 znamená,
že databáze povoluje typ čtení, 7 naopak nepovoluje.
Tabulka B.1: Isolation levels a typy čtení
Isolation Level Dirty Reads Non-Repeatable Reads Phantom Reads
Read Uncommitted 3 3 3
Read Committed 7 3 3
Repeatable Read 7 7 3
Serializable 7 7 7
B.4 Durability
Trvalost je schopnost databáze zachovat změny po potvrzení (commit) transakce i po
pádu systému způsobeném například softwarovým nebo hardwarovým výpadkem.[1]
V
Příloha C
Exekuční plány
Tabulka test
1. Dotaz:
EXPLAIN ANALYZE SELECT * FROM test
WHERE time <= ’1976-12-09 13:50:00’ AND time >= ’1976-12-09 12:50:00’;
Exekuční plán:
Seq Scan on test (cost=0.00..2136943.72 rows=210 width=24)
(actual time=1612.520..206757.163 rows=212 loops=1)
Filter: (("time" <= ’1976-12-09 13:50:00’::timestamp without time zone)
AND ("time" >= ’1976-12-09 12:50:00’::timestamp without time zone))
Total runtime: 206757.801 ms
2. Dotaz:
EXPLAIN ANALYZE SELECT * FROM test WHERE time > ’2009-12-24 00:00:00’;
Exekuční plán:
Seq Scan on test (cost=0.00..1886943.60 rows=39469 width=24)
(actual time=57.583..205796.608 rows=41142 loops=1)
Filter: ("time" > ’2009-12-24 00:00:00’::timestamp without time zone)
Total runtime: 205864.791 ms
3. Dotaz:
EXPLAIN ANALYZE SELECT * FROM test
WHERE time BETWEEN ’1970-05-06 06:06:00’ AND ’1970-06-06 06:06:00’;
Exekuční plán:
Seq Scan on test (cost=0.00..2136943.72 rows=167904 width=24)
(actual time=28.612..206643.445 rows=169599 loops=1)
Filter: (("time" >= ’1970-05-06 06:06:00’::timestamp without time zone)
AND ("time" <= ’1970-06-06 06:06:00’::timestamp without time zone))
Total runtime: 206906.823 ms
VI
PŘÍLOHA C. EXEKUČNÍ PLÁNY
Tabulka test index
1. Dotaz:
EXPLAIN ANALYZE SELECT * FROM test_index
WHERE time <= ’1976-12-09 13:50:00’ AND time >= ’1976-12-09 12:50:00’;
Exekuční plán:
Bitmap Heap Scan on test_index (cost=17.82..1022.41 rows=254 width=24)
(actual time=0.247..1.223 rows=212 loops=1)
Recheck Cond: (("time" <= ’1976-12-09 13:50:00’::timestamp without time zone)
AND ("time" >= ’1976-12-09 12:50:00’::timestamp without time zone))
-> Bitmap Index Scan on test_index_time_idx (cost=0.00..17.76 rows=254 width=0)
(actual time=0.160..0.160 rows=212 loops=1)
Index Cond: (("time" <= ’1976-12-09 13:50:00’::timestamp without time zone)
AND ("time" >= ’1976-12-09 12:50:00’::timestamp without time zone))
Total runtime: 1.688 ms
2. Dotaz:
EXPLAIN ANALYZE SELECT * FROM test_index
WHERE time > ’2009-12-24 00:00:00’;
Exekuční plán:
Bitmap Heap Scan on test_index (cost=745.79..125046.47 rows=39042 width=24)
(actual time=768.042..2473.756 rows=41142 loops=1)
Recheck Cond: ("time" > ’2009-12-24 00:00:00’::timestamp without time zone)
-> Bitmap Index Scan on test_index_time_idx (cost=0.00..736.03 rows=39042 width=0)
(actual time=763.210..763.210 rows=41142 loops=1)
Index Cond: ("time" > ’2009-12-24 00:00:00’::timestamp without time zone)
Total runtime: 2533.574 ms
3. Dotaz:
EXPLAIN ANALYZE SELECT * FROM test_index
WHERE time BETWEEN ’1970-05-06 06:06:00’ AND ’1970-06-06 06:06:00’;
Exekuční plán:
Bitmap Heap Scan on test_index (cost=3576.80..384792.44 rows=167959 width=24)
(actual time=5835.434..208119.613 rows=169599 loops=1)
Recheck Cond: (("time" >= ’1970-05-06 06:06:00’::timestamp without time zone)
AND ("time" <= ’1970-06-06 06:06:00’::timestamp without time zone))
-> Bitmap Index Scan on test_index_time_idx (cost=0.00..3534.81 rows=167959 width=0)
(actual time=5823.170..5823.170 rows=169599 loops=1)
Index Cond: (("time" >= ’1970-05-06 06:06:00’::timestamp without time zone)
AND ("time" <= ’1970-06-06 06:06:00’::timestamp without time zone))
Total runtime: 208407.212 ms
VII
PŘÍLOHA C. EXEKUČNÍ PLÁNY
Tabulka test part
1. Dotaz:
EXPLAIN ANALYZE SELECT * FROM test_part
WHERE time <= ’1976-12-09 13:50:00’ AND time >= ’1976-12-09 12:50:00’;
Exekuční plán:
Result (cost=0.00..920.25 rows=234 width=24) (actual time=0.252..2.961 rows=212 loops=1)
-> Append (cost=0.00..920.25 rows=234 width=24) (actual time=0.247..2.075 rows=212 loops=1)
-> Seq Scan on test_part (cost=0.00..32.65 rows=8 width=24) (actual time=0.003..0.003 rows=0 loops=1)
Filter: (("time" <= ’1976-12-09 13:50:00’::timestamp without time zone)
AND ("time" >= ’1976-12-09 12:50:00’::timestamp without time zone))
-> Bitmap Heap Scan on test_part_year197x test_part (cost=8.76..887.60 rows=226 width=24)
(actual time=0.237..1.258 rows=212 loops=1)
Recheck Cond: (("time" <= ’1976-12-09 13:50:00’::timestamp without time zone)
AND ("time" >= ’1976-12-09 12:50:00’::timestamp without time zone))
-> Bitmap Index Scan on test_part_year197x_time_idx (cost=0.00..8.70 rows=226 width=0)
(actual time=0.153..0.153 rows=212 loops=1)
Index Cond: (("time" <= ’1976-12-09 13:50:00’::timestamp without time zone)
AND ("time" >= ’1976-12-09 12:50:00’::timestamp without time zone))
Total runtime: 3.437 ms
2. Dotaz:
EXPLAIN ANALYZE SELECT * FROM test_part
WHERE time > ’2009-12-24 00:00:00’;
Exekuční plán:
Result (cost=0.00..82242.20 rows=37984 width=24) (actual time=238.529..1785.973 rows=41142 loops=1)
-> Append (cost=0.00..82242.20 rows=37984 width=24) (actual time=238.524..1662.303 rows=41142 loops=1)
-> Seq Scan on test_part (cost=0.00..28.88 rows=503 width=24) (actual time=0.003..0.003 rows=0 loops=1)
Filter: ("time" > ’2009-12-24 00:00:00’::timestamp without time zone)
-> Bitmap Heap Scan on test_part_year200x test_part (cost=704.92..82213.32 rows=37481 width=24)
(actual time=238.514..1543.517 rows=41142 loops=1)
Recheck Cond: ("time" > ’2009-12-24 00:00:00’::timestamp without time zone)
-> Bitmap Index Scan on test_part_year200x_time_idx (cost=0.00..695.55 rows=37481 width=0)
(actual time=233.678..233.678 rows=41142 loops=1)
Index Cond: ("time" > ’2009-12-24 00:00:00’::timestamp without time zone)
Total runtime: 1845.707 ms
VIII
PŘÍLOHA C. EXEKUČNÍ PLÁNY
3. Dotaz:
EXPLAIN ANALYZE SELECT * FROM test_part
WHERE time BETWEEN ’1970-05-06 06:06:00’ AND ’1970-06-06 06:06:00’;
Exekuční plán:
Result (cost=0.00..140461.02 rows=172385 width=24) (actual time=1387.588..32483.562 rows=169599 loops=1)
-> Append (cost=0.00..140461.02 rows=172385 width=24) (actual time=1387.583..31955.226 rows=169599 loops=1)
-> Seq Scan on test_part (cost=0.00..32.65 rows=8 width=24) (actual time=0.004..0.004 rows=0 loops=1)
Filter: (("time" >= ’1970-05-06 06:06:00’::timestamp without time zone)
AND ("time" <= ’1970-06-06 06:06:00’::timestamp without time zone))
-> Bitmap Heap Scan on test_part_year197x test_part (cost=3661.31..140428.37 rows=172377 width=24)
(actual time=1387.573..31448.654 rows=169599 loops=1)
Recheck Cond: (("time" >= ’1970-05-06 06:06:00’::timestamp without time zone)
AND ("time" <= ’1970-06-06 06:06:00’::timestamp without time zone))
-> Bitmap Index Scan on test_part_year197x_time_idx (cost=0.00..3618.21 rows=172377 width=0)
(actual time=1382.622..1382.622 rows=169599 loops=1)
Index Cond: (("time" >= ’1970-05-06 06:06:00’::timestamp without time zone)
AND ("time" <= ’1970-06-06 06:06:00’::timestamp without time zone))
Total runtime: 32740.377 ms
IX
Top Related