Popis a využití technologií relačních databází v geoinformatice Description and usage of relation database technologies in geoinformatics

ČESKÉ VYSOKÉ UČENÍ TECHNICKÉ V PRAZE Fakulta 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 in geoinformatics Bakalářská práce

Studijní program: Geodézie a kartografie Studijní obor: Geoinformatika Vedoucí práce: Ing. Jan Pytel, Ph.D.

Jan Synek kvě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školský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 studia. Tato podpora mi umožnila se plně soustředit pouze na studium.

Abstrakt Prá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 databá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á slova Databáze SQL, indexy, partitioning, C++ , Java, Qt, JDBC.

Abstract The 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 description 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.

Keywords Database SQL, indexes, partitioning, C++ , Java, Qt, JDBC. i

Obsah Seznam tabulek

iv

Seznam obrázků

v

Seznam zkratek

vi

Úvod

1

1 Databáze 1.1 Základní databázové objekty . . . . . . . . . 1.2 Indexy . . . . . . . . . . . . . . . . . . . . . 1.2.1 Exekuční plánování . . . . . . . . . . 1.2.2 Typy indexu dle použitého algoritmu 1.2.3 Typy indexu dle indexovaných dat . 1.2.4 Údržba indexu . . . . . . . . . . . . 1.3 Triggery . . . . . . . . . . . . . . . . . . . . 1.3.1 Trigger . . . . . . . . . . . . . . . . . 1.3.2 Trigger funkce . . . . . . . . . . . . . 1.4 Partitioning . . . . . . . . . . . . . . . . . . 1.4.1 Inheritance . . . . . . . . . . . . . . 1.4.2 Implementace partitioningu . . . . . 1.4.3 Údržba partitioningu . . . . . . . . . 1.5 Praktická část . . . . . . . . . . . . . . . . . 1.5.1 Index . . . . . . . . . . . . . . . . . . 1.5.2 Partitioning . . . . . . . . . . . . . .

ii

. . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . .

2 5 6 7 9 10 13 13 13 14 16 17 19 21 21 22 25

OBSAH

OBSAH

2 Rozhraní přístupu k databázi 2.1 JDBC . . . . . . . . . . . . 2.1.1 Spojení . . . . . . . 2.1.2 Výjimky . . . . . . . 2.1.3 Objekt ResultSet . 2.1.4 Transakce . . . . . . 2.1.5 PreparedStatement 2.1.6 Dávková modifikace . 2.2 Qt . . . . . . . . . . . . . . 2.2.1 Spojení . . . . . . . 2.2.2 Třída QSqlError . . 2.2.3 Objekt QSqlQuery . 2.2.4 Transakce . . . . . . 2.2.5 Dávková modifikace . 2.2.6 Třída QSqlRecord . 2.2.7 Modelové třídy . . . 2.3 Porovnání . . . . . . . . . . 2.3.1 Rozdíly . . . . . . . 2.3.2 Shrnutí . . . . . . .

. . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . .

3 Aplikace

55

Závěr

61

Použité zdroje

62

A Oracle functionality B ACID B.1 Atomicity . B.2 Consistency B.3 Isolation . . B.4 Durability .

30 30 31 33 34 36 37 38 40 41 42 43 45 46 47 47 50 50 54

. . . .

. . . .

. . . .

I

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

C Exekuční plány

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

III III IV IV V VI

iii

Seznam tabulek 1.1 1.2 1.3 1.4 1.5 1.6

Příkazy jazyka SQL . . . . . . . . Datové typy jazyka SQL . . . . . Nejznámější databázové systémy . Varianty příkazu REINDEX . . . . Proměnné v PL/pgSQL funkcích Možné formy partitioningu . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

3 4 4 13 15 16

2.1 2.2 2.3 2.4 2.5 2.6 2.7 2.8 2.9 2.10 2.11 2.12

Rozhraní balíčku java.sql . . . . . . . . . . . . . . . . Vybraná URL používaná v JDBC . . . . . . . . . . . Metody třídy SQLException . . . . . . . . . . . . . . Metody třídy ResultSet pro posun kurzoru . . . . . Vybrané moduly Qt frameworku . . . . . . . . . . . . Ovladače podporovaných databází v Qt . . . . . . . . Výčtový typ enum QSqlError::ErrorType . . . . . . Funkce třídy QSqlQuery pro posun kurzoru . . . . . . Členské funkce třídy QSqlRecord . . . . . . . . . . . Modelové třídy v Qt . . . . . . . . . . . . . . . . . . Výčtový typ enum QSqlTableModel::EditStrategy Shrnutí rozhraní JDBC a Qt . . . . . . . . . . . . . .

. . . . . . . . . . . .

. . . . . . . . . . . .

. . . . . . . . . . . .

. . . . . . . . . . . .

. . . . . . . . . . . .

. . . . . . . . . . . .

. . . . . . . . . . . .

. . . . . . . . . . . .

. . . . . . . . . . . .

. . . . . . . . . . . .

. . . . . . . . . . . .

. . . . . . . . . . . .

31 32 33 35 40 42 43 44 47 48 49 54

3.1

Porovnání časů jednotlivých aplikací . . . . . . . . . . . . . . . . . . . . .

60

A.1 Orafce: Funkce pro práci s typem date . . . . . . . . . . . . . . . . . . . . A.2 Orafce: Funkce pro práci s řetězci . . . . . . . . . . . . . . . . . . . . . . . A.3 Orafce: Ostatní funkce . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

I II II

B.1 Isolation levels a typy čtení

V

. . . . . . . . . . . . . . . . . . . . . . . . . .

iv

Seznam obrázků 3.1 3.2 3.3 3.4

Ukázka aplikace napsané v Javě . Zobrazení výsledku Java aplikace Ukázka aplikace napsané v C++ Zobrazení výsledku C++ aplikace

. . . .

v

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

. . . .

56 57 58 59

Seznam zkratek ACID ANSI API DBMS DCL DDL DML GIN GiST GUI IBM ISO Java SE JDBC JDK ODBC RDBMS SEQUEL SQL SŘBD S-JTSK TCL Xt

Atomicity, Consistency, Isolation, Durability American National Standards Institute Application Programming Interface DataBase Management System Data Control Language Data Definition Language Data Manipulation Language Generalized Inverted Index Generalized Search Tree Graphical User Interface International Business Machines International Organization for Standardization Java Standard Edition Java DataBase Connectivity Java Development Kit Open DataBase Connectivity Relational DataBase Management System Structured English QUEry Language Structured Query Language Systém řízení báze dat Systém jednotné trigonometrické sítě katastrální Transaction Control Language X toolkit

vi

Úvod Cílem této bakalářské práce je vytvořit rešerši popisující technologie databázových systémů. V dnešní době, kdy je potřeba zpracovávat velká množství dat, jsou databázové systé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 prostř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 framework. 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šířeně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 elektromechanický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 sedmdesá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: 1

V č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 (strukturovaný 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 DML DDL DCL TCL

příkazy příkazy příkazy příkazy

pro pro pro pro

Popis manipulaci s daty definici dat řízení přístupových práv řízení transakcí

Příkazy SELECT, UPDATE, INSERT, . . . CREATE, ALTER, DROP, . . . GRANT, REVOKE COMMIT, ROLLBACK, . . .

Database Management System DBMS je software, který provádí tvorbu, uložení, údržbu a užití databáze a tvoří rozhraní mezi uloženými daty a aplikačními programy. Tento systém přijímá požadavek pro3

KAPITOLA 1. DATABÁZE

Tabulka 1.2: Datové typy jazyka SQL Popis Znakové datové typy Bitové datové typy Číselné datové typy Časové datové typy

Datové typy CHAR(n), VARCHAR(n), . . . BIT(n), . . . INTEGER, REAL, . . . DATE, TIMESTAMP, . . .

střednictvím instrukcí nějakého dotazovacího jazyku, zpracuje požadavek a vrátí dotazovaná 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 Oracle Database PostgreSQL IBM DB2 MySQL Microsoft SQL Server Apache Derby SQLite

URL v dnešní době zřejmě nejrozšířenější komerční objektově relační databázový systém vynikající open–source objektově relační databázový systém, tímto DBMS se budeme zabývat v této práci relační databázový systém vyvíjený firmou IBM open-source relační databázový systém nejčastěji využívaný v internetových aplikacích databázové řešení firmy Microsoft open-source databázový systém napsaný kompletně v Javě 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 jazycí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 (obrá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

1.1


Základní databázové objekty

Každý relační databázový systém poskytuje sadu databázových objektů (entit). Existuje 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 tabulku 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áznamem. Pohled (View) View je dalším databázovým objektem. Svým chováním připomíná tabulku, ale neobsahuje žá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. 2

Oracle jako první představil tzv. materializované pohledy, které obsahují data. Jedná se o lokální kopie dat z výsledku agregace jiných tabulek

5

1.2. INDEXY


Index Index je konstrukce, která zrychluje dotazování v databázi. Zrychlení spočívá ve vytvoř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 vyvolá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 databá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 klasický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 systé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


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ě pomalejší. 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 automatickou 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


Výpis exekučního plánu poskytovaný plannerem pro jednotlivé dotazy lze vypsat pomocí 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


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 rovnosti 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


Je vhodný i pro dotazy obsahující klíčová slova BETWEEN a IN, která představujíc kombinace 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 řetě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 geometrický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 3

nadstavba PostgreSQL umožňující podporu geografických objektů

10

1.2. INDEXY


• 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é indexy. 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 podporová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


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 zobrazení 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 vyhodnocuje planner. V opačném případě může být přínos minimální, ne-li žádný.[2]

12

1.3. TRIGGERY

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

REINDEX REINDEX REINDEX REINDEX

Příkaz INDEX nazev_indexu TABLE nazev_tabulky DATABASE nazev_databaze SYSTEM nazev_databaze

přetvoří přetvoří přetvoří přetvoří

Popis jeden specifikovaný index všechny indexy v uvedené tabulce všechny indexy v aktuální databázi 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 1.3.1

Triggery 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


Trigger je pak přidružen k tabulce, pro kterou byl vytvořen podobně jako index. Pokud 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í najednou 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 jednou pro daný dotaz nehledě na počet modifikovaných řádků. Následuje EXECUTE PROCEDURE 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á aktualizuje č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 triggerů. 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


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á NEW OLD

TG NAME TG WHEN TG LEVEL TG OP TG RELNAME TG ARGV

Popis proměnná obsahující nově vkládaný řádek při INSERT nebo UPDATE u per-row triggerů, u per-statement triggerů je tato hodnota NULL. proměnná obsahující řádek ještě před změnou při INSERT nebo UPDATE u per-row triggerů, u per-statement triggerů je tato hodnota NULL. proměnná, která obsahuje název triggeru, který funkci spustil obsahuje řetězec ’BEFORE’ nebo ’AFTER’ v závislosti na použitém triggeru obsahuje řetězec ’ROW’ nebo ’STATEMENT’ v závislosti na použitém triggeru obsahuje řetězec ’INSERT’, ’UPDATE’ nebo ’DELETE’ v závislosti na použitém triggeru obsahuje název tabulky, která spustila trigger 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


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 Range Partitioning

List Partitioning

Popis 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 se nesmí ve dvou nebo více tabulkách objevit stejného hodnoty v klíčovém sloupci. Například rozdělení podle data narození. tabulka je rozdělena podle explicitně uvedeného seznamu hodnot, které se v ní budou vyskytovat.

16

1.4. PARTITIONING


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 statistiky 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 partitions. • 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 uvedeny 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 podporuje 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ásledují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


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ávný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


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 rodič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í tabulky. Obvykle nepřidávají žádné další sloupce, ale přidávají podmínky, které definují, jaká data budou jednotlivé partitions obsahovat. CREATE TABLE workers_1940 CHECK ( birth >= DATE ) INHERITS (workers); ... CREATE TABLE workers_1990 CHECK ( birth >= DATE ) INHERITS (workers);

( ’1940-01-01’ AND birth < DATE ’1950-01-01’ )

( ’1990-01-01’ AND birth < DATE ’2000-01-01’ )

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


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

1.4.3


Údržba partitioningu

Jednou z nejdůležitější výhod partitioningu je nenáročná manipulace s daty. Ve velký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 prohledá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.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 vygenerová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



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: 4

ANALYZE je také spouštěn automaticky viz 1.2.1

23



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



’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



Time: 37.505 ms test=> test=> CREATE TABLE test_part_year197x ( test(> CHECK (time >= TIMESTAMP ’1970-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’ test(> ) INHERITS (test_part); CREATE TABLE Time: 7.912 ms test=> test=> CREATE TABLE test_part_year199x ( test(> CHECK (time >= TIMESTAMP ’1990-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’ test(> ) INHERITS (test_part); CREATE TABLE Time: 7.520 ms

AND time < TIMESTAMP ’1980-01-01’)




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=> test-> test$> test$> test$> test$> test$> test$> test$> test$> test$> test$> test$>

CREATE OR REPLACE FUNCTION test_part_insert_function() RETURNS TRIGGER AS $$ BEGIN IF (NEW.time >= TIMESTAMP ’1960-01-01’ AND NEW.time < TIMESTAMP ’1970-01-01’) THEN INSERT INTO test_part_year196x VALUES (NEW.*); ELSEIF (NEW.time >= TIMESTAMP ’1970-01-01’ AND NEW.time < TIMESTAMP ’1980-01-01’) THEN INSERT INTO test_part_year197x VALUES (NEW.*); ELSEIF (NEW.time >= TIMESTAMP ’1980-01-01’ AND NEW.time < TIMESTAMP ’1990-01-01’) THEN INSERT INTO test_part_year198x VALUES (NEW.*); ELSEIF (NEW.time >= TIMESTAMP ’1990-01-01’ AND

26



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



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 CREATE INDEX Time: 90962.043 ms test=> CREATE INDEX CREATE INDEX Time: 95054.517 ms test=> CREATE INDEX CREATE INDEX Time: 97290.763 ms test=> CREATE INDEX CREATE INDEX Time: 99341.608 ms test=> CREATE INDEX CREATE INDEX Time: 116375.899 ms

test_part_year196x_index_time ON test_part_year196x (time);





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



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 databá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ů. Existují 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 programovací 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řistupuje do databáze. Toto rozhraní poskytuje metody pro dotazování a modifikování databá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 Statement PreparedStatement CallableStatement

2.1.1

Popis Používá se v jednoduchých dotazech bez parametru. Rozšiřuje rozhraní Statement. Užívá se pro předkompilované SQL dotazy, které mohou obsahovat vstupní parametry. Rozšiřuje rozhraní PreparedStatement. Používá se pro vykonání uložených procedur, které mohou obsahovat vstupní i výstupní parametry.

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ď uvedou explicitně jako řetězce, nebo v objektu typu Properties. V tabulce 2.2 jsou uvedeny vybraná URL. 31

2.1. JDBC


Tabulka 2.2: Vybraná URL používaná v JDBC DBMS Oracle PostgreSQL MySQL

URL jdbc:oracle:thin:@//localhost:1521/orcl jdbc:postgresql://localhost:5432/test 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 objekty 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

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 getMessage() getSQLState() getErrorCode() getCause()

Popis vrací String s popisem chyby. vrací řetězec se standardizovaným kódem chyby. vrací celé číslo popisující chybu, záleží na implementaci databáze 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 nastane 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

2.1.3


Objekt ResultSet

Rozhraní ResultSet ResultSet je objekt představující výsledek vrácený databázovým systémem po vykoná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() posunuje kurzor na další řádek a vrací hodnotu false, pokud je kurzor umístěn za posledním řádkem 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


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 next() previous() first() last() relative(int) absolute(int)

Popis posune posune posune posune posune posune

kurzor kurzor kurzor kurzor kurzor kurzor

o řádek vpřed o řádek zpět na první řádek objektu na poslední řádek objektu o zadaný počet řádků vzhledem k jeho aktuální pozici 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

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


pokud se tedy nevyskytla výjimka, která volá metodu rollback() a vrací změny do původní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ů, nejvíce se používá u SQL příkazů přebírajících parametry. Výhodou je možnost použití stejného SQL příkazu, ale s úplně jinými hodnotami. Příklad použití PreparedStatement s parametry:

37

2.1. JDBC


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í hodnoty, 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 nastavena, 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 seznam příkazů, které jsou s nimi spojeny. Tento seznam může obsahovat SQL příkazy INSERT, 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


} 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

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živatelský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é programovací 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 Slackware 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 QtCore QtGui QtNetwork QtXml QtSql

obsahuje obsahuje obsahuje obsahuje obsahuje

Popis základní negrafické třídy Qt, mechanismus signálů a slotů, atd. grafické třídy třídy pro síťové programování třídy pro práci s XML 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


• 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 direktivu: #include

Dále je nutné přidat řádek do Makefile souboru pro program qmake 1 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 nepojmenovaného spojení. V tabulce 2.6 jsou uvedeny některé ovladače podporovaných databází v Qt. 1

qmake je nástroj, který usnadňuje sestavení projektu napříč různými platformami. Automaticky generuje Makefile. Lze ho využít i v non-Qt projektech.

41

2.2. QT


Tabulka 2.6: Ovladače podporovaných databází v Qt Ovladač QOCI QPSQL QMYSQL QSQLITE QODBC QDB2

DBMS Oracle Call Interface Driver PostgreSQL verze 7.3 a vyšší MySQL SQLite verze 3 ODBC 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


Tabulka 2.7 popisuje výčtový typ QSqlError::ErrorType a jeho konstanty. Tabulka 2.7: Výčtový typ enum QSqlError::ErrorType Konstanta QSqlError::NoError QSqlError::ConnectionError QSqlError::StatementError QSqlError::TransactionError QSqlError::UnknownError

Hodnota 0 1 2 3 4

Popis Žádná chyba Chyba při spojení Chyba v syntaxi SQL příkazu Chyba při transakci 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 použ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 databá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(). 2

Pro práci s indexy je implementována speciální třída QSqlIndex, která s nimi dokáže manipulovat.

43

2.2. QT


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 next() previous() first() last() seek(int, bool)

přesune přesune přesune přesune přesune

ukazatel ukazatel ukazatel ukazatel ukazatel

na na na na na

Popis další řádek výsledku předchozí řádek výsledku první řádek výsledku poslední řádek výsledku řá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


Funkce QSqlQuery::prepare() QSqlQuery také podporuje „předpřipravené příkazyÿ. Některé databázové systémy nepodporují 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í argument 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 implicitně 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


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 vyhledá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ázové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ázové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


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 charakteristiku databázového záznamu (zpravidla řádek). Pomocí členských funkcí podporuje 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 append(QSqlField) clear() count() field(int) isEmpty() isNull(int) remove(int) setNull(int) setValue(int, QVariant) value(int)

2.2.7

Popis přidá kopii hodnoty na konec záznamu smaže všechny hodnoty záznamu vratí počet hodnot v záznamu vratí hodnotu (objekt QSqlField) zadaného sloupce testuje, zda jsou v záznamu nějaké hodnoty testuje, zda je hodnota zadaného sloupce null smaže hodnotu na určené pozici nastaví null na určené pozici nastaví hodnotu na určené pozici vratí hodnotu (objekt QVariant) zadaného sloupce

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 3

Existuje ještě třída QSqlField, která zapouzdřuje pouze hodnotu záznamu.

47

2.2. QT


Tabulka 2.10: Modelové třídy v Qt Třída QSqlQueryModel QSqlTableModel QSqlRelationalTableModel

Popis Model určený pouze pro čtení pro libovolný SQL dotaz. Model pro čtení i zápis, který pracuje na jedné tabulce. 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 operace, 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


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 QSqlTableModel::OnFieldChange

Hodnota 0

QSqlTableModel::OnRowChange

1

QSqlTableModel::OnRowChange

2

Popis Všechny změny v modelu jsou ihned aplikovány do databáze. Změny řádku jsou aplikovány do databáze, když je vybrán jiný řádek. 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Í

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řípadech 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 spojení. 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 obsahují 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Í


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. ukazatel, 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Í


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 rozdí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 autocommit 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é zamezí 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ástupný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Í


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ý seznam 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 jednotlivý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Í

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

Výjimky Transakce Předpřipravené příkazy Dávková modifikace Modifikace výsledku Modelové třídy Isolation úrovně

JDBC (Java) 3 3 3 3 3 3 3

54

Qt (C++ ) 3 3 3 3 3 3 7

Kapitola 3 Aplikace Kapitola uvádí testování popisovaných databázových technologií skrze jednoduché testovací 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 tabulky (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 tabulky 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 implementová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 partitions tabulky test part. 55

KAPITOLA 3. APLIKACE Java Aplikace napsaná v Javě využívá pro tvorbu GUI grafickou knihovnu Swing. Komunikuje 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 poskytnutý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í spojení 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 databá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í objektu 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 terminá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 programu 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 Java

C++

psql

Dotaz 1. 2. 3. 1. 2. 3. 1. 2. 3.

test [ms] 211005 206394 208449 211271 206616 209168 206758 205864 206907

test index [ms] 10 2820 213192 6 3260 213926 2 2534 208408

test part [ms] 12 1910 34518 8 2283 35089 3 1845 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 framework. 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 zpracová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 partitioningem 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 popisovaný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ě zpracovaná 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 partitioning) 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 uvedeny funkce pracující s řetězci, ale zpravidla k nim existují ANSI SQL ekvivalenty. V tabulce 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 add months(date, integer) ast date(date) next day(date, text) months between(date, date) trunc(date, text) round(date, text)

Popis přičte zadaný počet měsíců vrací poslední den v měsíci vrací první následující den v týdnu po zadaném dnu vrací počet měsíců(float) mezi zadanými dny ořízne datum podle zadaného formátu 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 instr(text, text, [int[,int]]) reverse(text) oracle.substr(text, [int[,int]])

Popis hledá n-tý výskyt vzoru v řetězci zrcadlově převrátí řetězec vrací část řetězce

Tabulka A.3: Orafce: Ostatní funkce Funkce nvl(element, element) nvl2(element, element, element) concat(text, text)

Popis vrací první neprázdný parametr vrací první neprázdný parametr 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

B.2

PŘÍLOHA B. ACID

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 druhou 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 nekonzistentní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 vykoná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 modifikované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í podmí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 zopakuje čtení se stejnou podmínkou, dostane úplně jinou sadu řádků.[6] 1

situace, 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 zadá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 Read Uncommitted Read Committed Repeatable Read Serializable

B.4

Dirty Reads 3 7 7 7

Non-Repeatable Reads 3 3 7 7

Phantom Reads 3 3 3 7

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’;


(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’;


(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’;


(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’;


(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

Popis a využití technologií relačních databází v geoinformatice Description and usage of relation database technologies in geoinformatics

Recommend Documents