Autor: Martin Kumst – www.kumst.net – martin.kumst@seznam.cz

Motivace

Sám jsem se s projektem SQLite seznámil poměrně nedávno a musím říci, že se mi velice zalíbil. Také lituji toho, že jsem jej neobjevil již dříve, protože bych si jeho použitím pro některá nasazení usnadnil život a ušetřil nemalé množství práce. Doufám, že vám následující text tento šikovný projekt přiblíží a třeba bude inspirací k tomu, že sami SQLite zkusíte a třeba i někde nasadíte.

Co je to SQLite?

SQLite je relativně malá knihovna, která implementuje kompletní relační databázový systém. Nejedná se však o klasickou klient – server databázi tak, jak ji známe z projektů MySQL a PostgreSQL, ale naopak je zde databáze uložena v souboru a předpokládá se tedy, že k datům přistupuje software, který běží na stejném počítači či serveru, na kterém jsou uložena data.

Základní myšlenkou projektu SQLite je jednoduchost. Jak již bylo řečeno, samotná databáze s daty je uložena v klasickém souboru na disku. Dále je třeba zmínit to, že SQLite prakticky vůbec nepotřebuje žádnou konfiguraci. Databázový software je absolutně spokojen, pokud mu sdělíte, s jakým databázovým souborem hodláte pracovat. Samozřejmě je nutné, aby uživatel, pod kterým poběží váš software přistupující k datům, měl k databázovému souboru náležitá práva. Neznamená to však, že SQLite nelze vůbec konfigurovat. Pokud si budete přát, můžete chování své instance SQLite upravit pomocí příkazu PRAGMA.

Autorem tohoto projektu je programátor D. Richard Hipp, který se rozhodl své dílo šířit pod public domain licencí, což může být považováno za jednu z výhod tohoto databázového systému, jelikož se v případě sporného použití tohoto projektu nemusíte licenčními podmínkami zabývat vůbec. SQLite je údajně použit na příklad v následujících aplikacích: Google Chrome, Safari, Opera, Android Browser, Mozilla Firefox a Thunderbird, Skype a Adobe Reader. SQLite je podporován následujícími organizacemi: Facebook, Expensify, Mozilla, Navagiation Data Standard, Bentley a Bloomberg. SQLite lze použít na všech GNU/Linuxových distribucích, Windows i OS X. Z uvedeného jasně vyplývá, že možnosti použití SQLite jsou velmi rozmanité a multiplatformní.

Výhody

Výhodami SQLite jsou tedy: velká jednoduchost použití, malá velikost, databáze v souboru, většinou nulová potřeba jakékoli konfigurace, nulová cena a jistě i public domain licence.

Jako další výhodu je také nutno uvést i jistou míru paralelního přístupu, kterou, na rozdíl od konkurenčních projektů, SQLite nabízí. SQLite umožňuje, aby z jedné databáze četlo najednou více instancí. Toto však neplatí pro zápis, ale o tom až dále.

Jako další výhodu vnímám i fakt, že vzhledem k velice malé velikosti SQLite je až obdivuhodné, že podporuje skoro celý standard SQL-92. SQLite podporuje dokonce i cizí klíče, jejichž vynucení je však potřeba aktivovat použitím příkazu PRAGMA.

Další výhodu také můžeme spatřovat také v tom, že vzhledem k tomu, že je databáze uložena v jednom jediném souboru, je její zálohování a případně i distribuce opravdu snadným úkolem.

Nevýhody

Jako hlavní nevýhodu bych viděl nízký výkon, který se projeví hlavně při zápisu většího množství dat. SQLite chápe každý insert jako vlastní transakci a podle dokumentace transakci dokončí opravdu až tehdy, kdy jsou data bezpečně fyzicky uložena na disku. Na jednu stranu je dobré, že se můžete v podstatě spolehnout na bezpečné uložení dat, ale na stranu druhou je nízký výkon silně obtěžující a hlavně omezující faktor. Tuto neblahou situaci můžete vyřešit tím, že uzavřete více insertů do vlastní transakce (begin transaction a commit). Tím docílíte velmi výrazného zrychlení v případě, že na databázi pouštíte větší množství insertů.

Nevýhodou je také již zmíněné uzavření celé databáze v případě probíhajícího zápisu. To znamená, že v podstatě nelze realizovat paralelní zápis do databáze. Také nelze z databáze číst, pokud je do ní zrovna zapisováno.

Kdy se tedy SQLite hodí?

Ve výše uvedených odstavcích jsem se pokusil nastínit hlavní výhody a nevýhody projektu SQLite. Jistě by se o těchto tématech dalo napsat daleko více textu, ale obávám se, že je to mimo rozsah tohoto článku.

SQLite se hodí pro malá nasazení, kdy k datům přistupuje pouze jeden fyzický stroj a to nejlépe pouze z jedné instance či vlákna. Typickým příkladem tedy může být jednoduché osobní účetnictví, malý pokladní terminál, správce osobních financí či aplikace pro finanční analýzu.

SQLite se také hodí jako databáze pro malý až střední web. Zde můžete těžit z faktu, že databáze je uložena v souboru na disku, takže můžete případně ušetřit nějaké finance vybráním hostingu bez relační databáze. To samozřejmě předpokládá, že programovací jazyk použitý v rámci hostingu (např. PHP) bude mít nainstalovány potřebné extenze a moduly.

Na webu SQLite se také můžeme dočíst, že SQLite bývá používán jako cache dat nad některým větším databázovým systémem (Postgresql, Oracle, atd.). To znamená, že vlastní data jsou uložena ve větším databázovém systému a SQLite obsahuje jen jejich jakýsi obraz, který může být i nějakým způsobem agregovaný. Při změně vlastních dat je samozřejmě nutné data v SQLite, který slouží jako cache, obnovit. Těží se zde hlavně z rychlosti čtení, který SQLite nabízí, a také z faktu, že nasazením cache SQLite, který bude z pravidla běžet na jiném stroji než vlastní velká databáze, dojde ke snížení zátěže hlavního databázového serveru.

Instalace SQLite

Zdrojové kódy i binárky je možné stáhnout z webu samotného projektu, který sídli na adrese www.sqlite.org. Já osobně však preferuji balíčky, které mi nabízí má distribuce. Myslím, že v dnešní době již s podporou SQLite v žádné hlavní GNU/Linuxové distribuci není problém. Osobně používám Debian, takže si ukážeme instalaci právě na něm:

aptitude install sqlite3

Uvedeným příkazem jsme si nainstalovali samotnou knihovnu SQLite a také řádkového klienta sqlite3, který použijeme pro demonstraci použití této databáze.

Vytvoření nové databáze

Novou databázi a tím i soubor, kde bude uložena, vytvoříme opravdu velice snadno zadáním následujícího příkazu:

sqlite3 ourdb.db

Pokud soubor s uvedeným jménem neexistuje, bude vytvořen. Pokud existuje, dojde k připojení k databázi, která je v něm uložena.

Vytvoření tabulek v databázi

Samotná nová databáze je poněkud samoúčelná a je proto potřeba v ní vytvořit několik tabulek, do kterých budeme následně ukládat záznamy.

Podporované datové typy

K tomu, abychom úspěšně vytvořili tabulku v SQLite, ale potřebujeme znát datové typy, které nám SQLite nabízí. Podporované datové typy jsou:

• NULL – Hodnota null

• INTEGER – celočíselná hodnota

• REAL – číslo s plovoucí desetinnou čárkou

• TEXT – textový řetězec

• BLOB – binární data

Je nutno uvést, že v SQLite je v podstatě jedno, s jakým datovým typem sloupec v tabulce vytvoříte. Do jakéhokoli sloupce mohou být uložena jakákoli data a to bez ohledu na to, jaký typ sloupce byl uveden při vytvoření tabulky. Existuje zde však jedna výjimka a tou je celočíselný primární klíč (INTEGER PRIMARY KEY). V dokumentaci SQLite se dočteme, že toto není chyba a že typ dat definovaný pro sloupec při vytvoření tabulky má být vnímán jako jakési doporučení typu dat, která mají být v daném sloupci obsažena. Osobně toto jako chybu či nedostatek nevnímám. S ohledem na typické nasazení SQLite a jeho důraz na jednoduchost toto lze brát opravdu spíše jako pozitivní věc, která by nás však u vyspělé databáze právem pohoršila. SQLite sám s touto situací opravdu počítá. Pokud budete mít na příklad ve sloupci s typem INTEGER i nějaká textová data a zavoláte na tomto sloupci funkci SUM, dojde opravdu ke korektnímu sečtení číselných hodnot a textová data zůstanou nedotknuta.

Primární a cizí klíče

Každá tabulka v SQLite obsahuje speciální sloupec nazvaný rowid, který roste od jedničky a slouží k jednoznačné identifikaci řádku v tabulce. Tento sloupec je použit interně samotným databázovým systémem, ale nic nebrání tomu, abychom používali sloupec rowid ve svých dotazech jako ekvivalent pro auto-inkrementální číselný klíč. Speciální sloupec rowid nám může v některých případech ušetřit trochu práce s psaním dotazů, ale bohužel není všemocný. Také je nutno podotknout, že hodnoty rowid ze smazaných záznamů mohou být v budoucnu použity pro nové záznamy.

Jak již bylo napsáno, SQLite podporuje i cizí klíče. Nejdříve je však potřeba vynucení cizích klíčů zapnout. Je to tak údajně z důvodu zpětné kompatibility. Každopádně provedení následujícího příkazu silně doporučuji vždy před začátkem manipulace s daty, čímž můžete předejít nekonzistencím ve své databázi. Aktivaci cizích klíčů provedete tímto příkazem:

PRAGMA foreign_keys = ON;

Cizí klíč z jedné tabulky však nemůže mířit na speciální sloupec rowid do tabulky jiné, takže proto musíme primární klíč zpravidla vytvořit sami. SQLite samozřejmě podporuje i vícesloupcové klíče, ale příklady jejich použití leží podle mého mimo rámec tohoto textu.

Dlužno říci, že k využívání primárních a cizích klíčů nás opravdu nikdo nenutí. Jejich podporu v SQLite vnímám osobně jako úžasné plus, a proto jejich použití silně doporučuji. Myslím, že ve 21. století by referenční integrita i v jednoduché databází měla být samozřejmostí.

Konečně vytvoření nových tabulek

Výše jsme zlehka probrali problematiku datových typů a problematiku cizích a primárních klíčů. Myslím, že nám tedy už snad nic nebrání k tomu, abychom si vytvořili několik pokusných tabulek. Na tomto místě již předpokládám, že má uživatel vytvořenu novou databázi a nachází se v sqlite3 konzoli.

Jako příklad pro vyzkoušení si vybereme jednoduchou evidenci majetku. Naší databázovou strukturou budeme chtít popsat kdo vlastní jakou věc. Každá věc má své jméno, vlastníka a unikátní identifikátor. Vlastníkem může být jakákoli osoba. Osoba má své jméno, příjmení a unikátní identifikátor. Nemůže existovat věc, která má neexistujícího vlastníka. Tato struktura vám zní možná až příliš primitivně, ale osobně se domnívám, že dobře demonstruje nabyté znalosti a pro účel tohoto textu se hodí velmi dobře.

Z uvedeného tedy vyplývá, že budeme potřebovat dvě tabulky. První bude tabulka vlastníků, kterou pojmenujeme persons a ne owners, protože můžeme předpokládat, že v budoucnu budeme na tuto tabulku vázat další. Druhá tabulka se bude jmenovat things.

Nejdříve si zapneme podporu cizích klíčů:

PRAGMA foreign_keys = ON;

Následně již můžeme vytvořit první tabulku se jménem persons:

CREATE TABLE persons(id integer primary key autoincrement, name text, surname text);

Dále si můžeme vytvořit druhou tabulku. Bude to tabulka things, která bude navázána na první tabulku persons:

CREATE TABLE things(id integer primary key autoincrement, name text, owner_id integer, FOREIGN KEY(owner_id) REFERENCES persons(id));

Naplnění tabulek daty

Nyní bychom měli mít zapnuté vynucení cizích klíčů a vytvořeny dvě tabulky. Nic nám tedy nebrání v tom, abychom pokročili k naplnění tabulek daty.

Využijeme faktu, že naše tabulky neobsahují žádné záznamy, a otestujeme podporu cizích klíčů v SQLite. Pokusíme se vložit záznam do tabulky things s nějakým fiktivním vlastníkem, který samozřejmě v tabulce persons ještě nemůže existovat.

Pokus o vložení nového záznamu do tabulky things provedeme třeba takto:

insert into things(id, name, owner_id) values(null, 'Zidle', 4);

Tímto příkazem se snažíme o vložení nového záznamu do tabulky things s tím, že nová věc se bude jmenovat Zidle a její vlastník bude osoba s id 4. Kvůli faktu, že v tabulce persons není žádný záznam, naše snaha selže a SQLite nám zobrazí tuto chybovou zprávu:

Error: FOREIGN KEY constraint failed

Není to úžasné? I tak malý databázový systém, jaký je SQLite, nám ohlídá referenční integritu našich dat.

Posuňme se od neúspěšného snažení k pokusu, který by měl skončit úspěchem. Následujícím příkazem vložíme novou osobu do tabulky persons:

insert into persons(id, name, surname) values(null, 'Martin', 'Kumst');

Uvedeným příkazem jsme vložili autora textu do tabulky osob a potenciálních vlastníků. Následujícím příkazem tam vložíme ještě jednoho:

insert into persons(id, name, surname) values(null, 'Theo', 'Kumst');

Uvedeným příkazem jsme vložili do tabulky osob fiktivního nežijícího člověka. Pojďme se nyní podívat na stav dat v tabulce persons:

sqlite> select * from persons;

1|Martin|Kumst

2|Theo|Kumst

Uvedeným příkladem jsme si vypsali data, která jsou uložena v tabulce persons. Také můžeme vidět, že i když jsme do sloupce id ukládali hodnotu null, použila se pro jeho hodnotu číselná řada rostoucí od jedničky. Tento sloupec je primárním klíčem v tabulce persons.

Nyní již můžeme do tabulky things vkládat věci, které bude někdo vlastnit. Vložme jich tam několik:

insert into things(id, name, owner_id) values(null, 'Zidle', 1);

insert into things(id, name, owner_id) values(null, 'Zidle', 1);

insert into things(id, name, owner_id) values(null, 'Zidle', 2);

insert into things(id, name, owner_id) values(null, 'Stul', 2);

Uvedenými příkazy jsme autorovi textu přiřadili dvě židle a fiktivní osobě židli a stůl. Podívejme se tedy nyní na stav dat v tabulce things:

sqlite> select * from things;

1|Zidle|1

2|Zidle|1

3|Zidle|2

4|Stul|2

Vidíme, že data odpovídají tomu, jak jsme je vložili, což je potěšující. Pojďme si však zkusit vypsat k evidovaným věcem rovnou jméno vlastníka:

select things.id, things.name, persons.name as owner_name, persons.surname as owner_surname from things join persons on things.owner_id = persons.id;

1|Zidle|Martin|Kumst

2|Zidle|Martin|Kumst

3|Zidle|Theo|Kumst

4|Stul|Theo|Kumst

V uvedeném výstupu vidíme seznam evidovaných věci i se jménem a příjmením vlastníka. Docílili jsme tak spojením (join) obou tabulek.

Na závěr si ještě znovu otestujeme podporu pro cizí klíče. V prvním testu jsme v tabulce persons nikoho neměli. Nyní tam však máme dva potenciální vlastníky. Pokusíme se však vložit záznam s neexistujícím vlastníkem:

sqlite> insert into things(id, name, owner_id) values(null, 'Rotoped', 200);

Error: FOREIGN KEY constraint failed

Vidíme, že kontrola dodržování referenční integrity opět zafungovala. Díky tomu budou naše data vždy v pořádku.

Závěr

Popsali jsme si stučně to, co SQLite vlastně je. Následně jsme si nastínili výhody, nevýhody a případné možnosti vhodného nasazení. Potom jsme se podívali, jak se SQLite nainstaluje a jak je v něm možné vytvořit a naplnit daty jednoduchou databázi.

Děkuji všem, kteří dočetli až sem. Doufám, že byl tento text někomu přínosný.

V příštím díle se pokusím probrat příkazy řádkového klienta SQLite a představit některé složitější SQL dotazy, abychom si ukázali, co SQLite umí. Dále bych se chtěl zaměřit na ukázky použití v hlavních programovacích jazycích: Java, PHP a C.

Děkuji za případné dotazy a připomínky.