Přejít k navigační liště

Zdroják » Databáze » SQLite: Databáze pro váš web

SQLite: Databáze pro váš web

Dynamické webové stránky mohou používat nejrůznější typy datových úložišť. Jedním z nich je velmi lehká a přitom výkonná databáze SQLite.

Nálepky:

Webové stránky jsou v dnešní době nejčastěji spojovány s komponentami Apache, PHP a MySQL. Přitom ani jedna ze součástí však není nezbytná. Místo Apache může být Lighttpd nebo Nginx, místo PHP třeba Python a místo MySQL? Výběr je kupodivu docela velký. Většina webhostingů mívá sice v nabídce jen MySQL a případně i PostgreSQL, ale po prostudování konfigurace je obvykle možné najít i pět dalších databází.

Jedna z těchto pěti databází se může pyšnit tím, že umí jazyk SQL. Je to databáze SQLite, která si našla své místo zejména v mobilních přístrojích a také jako datové úložiště mnoha desktopových aplikací, zejména internetových browserů.

Nejedná se však o plnokrevnou databázi, jako výše jmenovaná sestřička. Není spuštěna jako démon, ale jedná se pouze o poměrně malou knihovnu, která je přilinkována k některému skriptovacímu jazyku. Neběží samostatně, ale umožňuje otevřít datový soubor a pracovat s ním prostřednictvím jazyka SQL.

Co všechno SQLite umí?

Základní povinností každé databáze je ACID: Atomicita, konzistence, izolovanost operací a trvanlivost dat. To vše splňuje. Dále je to CRUD, tedy schopnost databáze zapisovat, číst, modifikovat nebo mazat data dle požadavku aplikace. To také splňuje. Dále po takové databázi požadujeme znalost jazyka SQL na co nejvyšší úrovni. Zde SQLite také nezaostává a kromě řízení přístupových práv umí téměř vše, co má SQL databáze umět.

Co SQLite neumí?

SQLite nemá a ani nebude mít jednu důležitou vlastnost: Neumí korektně pracovat na sdíleném úložišti. Pokud by někdo chtěl SQLite používat přes NFS či Sambu, bylo by nejlépe, kdyby na to ihned zapomněl. Na síti totiž špatně funguje zamykání souborů kvůli přítomnosti vyrovnávacích pamětí.

Velmi často se setkávám s dotazem, jak tuto databázi zprovoznit sdíleně. Nijak. Nedělejte to. Použijte jinou databázi nebo si napište démona.

Polovičatá je i podpora Unicode. Jak autoři tvrdí, plná implementace Unicode by knihovnu zvětšila minimálně čtyřnásobně. Nebyla by pak „lite“. To nám však nebrání ukládat a číst data v UTF-8. Pokud bychom potřebovali například české řazení podle abecedy, museli bychom si ho sami dopsat do aplikace nebo do SQLite v jazyku hostitele.

Jak SQLite běží na webu?

Situace s PHP či Pythonem je zcela jiná. SQLite je přímo jejich součástí. Databázové soubory se lokálně otevírají v režii jádra operačního systému a nejsou tedy problémy s distribuovanými vyrovnávacími pamětmi. Zámky fungují tak jak mají i v případě, že se souborem pracují stovky skriptů současně.

Jak je to s podporou jazyka SQL? Databáze SQLite obsahuje podporu velké množiny příkazů jazyka SQL. Nenajdete v ní příkaz `GRANT`, ale je to tím, že SQLite neřeší přihlašování. Aplikace má přímý přístup k datovému souboru na disku. Přihlašování by bylo jen zbytečnou obstrukcí, která by se stejně dala obejít.

Databáze SQLite má pro vývojáře jednu zvláštní vlastnost: Místo pevné datové struktury má jen tzv. typovou afinitu. Do každého sloupce můžete zapsat hodnotu jakéhokoli typu, která je dle možností převedena na typ sloupce nebo ponechána jak je. Je to výhodné zejména v kombinaci s dynamicky typovanými jazyky, jako jsou právě PHP a Python.

Podpora transakcí je v SQLite samozřejmostí, integritní omezení a cizí klíče také. Najdeme zde i triggery. Nejsou zde vnořené procedury, ale vzhledem k tomu, že mezi aplikací a databází nedochází k síťovým přenosům, nejsou ani potřebné. Přímo v jazyku SQL je pak možné používat funkce napsané např. v PHP.

Jak je to s konkurenčním přístupem?

Konkurenční čtení je v SQLite vyřešeno. Při zápisu se však projevuje velmi kritizovaná vlastnost: Aplikace si po dobu vykonávání zapisující transakce zamkne celou databázi. Ostatní aplikace mohou v tentýž okamžik pouze číst původní data. Nově zapsaná data však vidí až po potvrzení transakce. Pokud potřebuje zapisovat víc procesů současně, řadí se do fronty.

Jak je to s výkonem?

Vývojáři sami tvrdí, že SQLite není náhradou velkých databází, ale spíš náhradou `fopen()`. Pokud budeme od takové databáze požadovat komplexní dotazy, nejspíš s výkonem spokojeni nebudeme. Stačí se však podívat na velké množství skriptů válejících se v hlubinách Internetu: Většina u nich vytahuje data z jedné tabulky nebo provádí jen několik málo spojení více tabulek. Při tomto použití nás výkon SQLite příjemně překvapí.

Proč používat SQLite pro web?

SQLite má velmi příznivou licenci Public Domain. Můžeme ji tedy bez omezení používat v komerčních i nekomerčních aplikacích. Také výkon je u aplikací, které data převážně čtou (u webu je to typicky přes 90 %) vynikající. Množství databází je omezeno pouze velikostí dostupného diskového prostoru, stejně jako i velikost databáze.

Vývojáře však bude spíš zajímat, jakým způsobem bude s databází pracovat.

Jak je to s verzemi?

V současné době jsou k dispozici tři verze:

  • SQLite 2 – zastaralá verze, pro nové projekty by se již neměla používat
  • SQLite 3 – produkční verze, kterou doporučuji pro ostré nasazení
  • SQLite 4 – nejnovější verze, se kterou jsem se ještě pořádně neseznámil

Tyto verze jsou udržovány paralelně, každá z nich používá jiný formát datového souboru. V PHP jsou v současné době stále k dispozici ovladače pro SQLite 2.

Verzi SQLite 3 je v PHP doporučeno používat pouze prostřednictvím třídy PDO. Knihovnou PDO, která zajistí vazbu mezi databází a strukturami či objekty v PHP, se budu zabývat příště.

Komentáře

Subscribe
Upozornit na
guest
45 Komentářů
Nejstarší
Nejnovější Most Voted
Inline Feedbacks
View all comments
Tomáš

Nevím no, ale neumět správně česky řadit mi přijde jako solidní stopper :)

Honza Kral

Ne, to skutecne neni jedno. PHP (ani zadny jiny genericky jazyk) nikdy nebude tak efektivni jako DB ktera muze radit za pomoci indexu, jedna se o rozdil radu.

janek

Potřeba řadit nijak nesouvisí s frameworkem. Prostě mám x uživatelů a chci prvních dvacet seřazených podle abecedy.

Martin Sura

„Ve většině aplikací to ani nemusí vadit“.

To je snad vtip ne? Tzn, že když budeš chtít kdykoliv použít třeba stránkování, tak to budeš dělat tak, že vezmeš z db všechny položky a pak je v php/pythonu seřadíš?

Nebo jsem něco špatně pochopil?

David Grudl

Existuje SQLite „ICU“ extension, která tohle řeší. http://www.sqlite.org/src/artifact?ci=trunk&filename=ext/icu/README.txt

Martin

Ahoj. Postupně zapojuji SQLite do našeho projektu, který původně pracoval s MSSQL, ale z různých důvodů chceme alespoň určitou část převést na SQLite. Mám ale obrovské problémy s výkonností. Je normální, aby zápis jednoho řádku do jednoduché prázdné tabulky trval cca 200 ms, někdy je to ale i více než 1 sekunda? Databáze je před vytvořením tabulky zcela prázdná (soubor o nulové délce). SQL vypadá takto:

DROP TABLE IF EXISTS „dataA“;
CREATE TABLE „dataA“ (
„id“ integer NOT NULL PRIMARY KEY AUTOINCREMENT,
„minute“ integer NULL,
„sample“ integer NULL
);

INSERT INTO „dataA“ („minute“, „sample“) VALUES (9, 2);

Vždyť to jsou jen dvě celá čísla a databáze i tabulka je jinak prázdná (i když je pak plnější, doba zápisu zůstává přibližně stejná). Potřebuji takto po jenom řádku zapisovat cca 50x každých 10 s, což databáze vůbec nestíhá. Zkouším to z C++ aplikace přes Kompex zapouzdření i wrapper z Admineru (přes PDO), zdá se, že jde o vlastnost samotné databáze. Když to samé udělám v MS SQL Server 2008 Express na stejném počítači, časy jsou neměřitelně malé! Můžete někdo tento primitivní příklad vyzkoušet, jestli máte také tak pomalou odezvu, nebo poradit, co mám v příkladu špatně? Dík, Martin

Martin

Dík za odpověď. Takto to vysvětlují i v SQlite FAQ. Tam ale píší 60 transakcí za sekundu, které by mi stačily. Je normální, že to trvá cca od 170 ms do více než jedné sekundy v podstatě náhodně? Přijde mi, že to nějak souvisí s aktuálním využitím stránkovacího souboru a možná i s antivirem. Zkoušeno na sqlite3, dvou různých počítačích (WP7 64 a XP Professional), třech různých discích, v obou případech zapnutý rezidentní Norton antivirus (nemohu vypnout, jsou to servery) a občas se to přehoupne přes sekundu. To je pro mě nepoužitelné, protože jde o systém kontinuálního měření. Zabalení do jedné transakce mohu použít jen omezeně, jde o zápis z různých aplikací. Ty musí jet nezávisle mnoho let v kuse i bez údržby v zapečetěné bedně. Proto se SQLite zdálo být dobrou volbou oproti MSSQL a jiným strojům, u kterých jsme po měsících či letech provozu vždy narazili na nějaký problém i při nastavené automatické údržbě. Zřejmě budu muset napsat vlastní engine s frontou pro údaje z jednotlivých aplikací, který všechna data za časový interval uloží v jedné transakci. Tím se ale část výhod SQLite ztratí. Nezabezpečená verze nepřichází v úvahu, data musí přežít výpadky napájení, updaty jednotlivých aplikací za běhu, požár i povodeň.

okbob

V pg konferenci se docela pravidelně objevují varování před použitím antivirů na db serverech. S tím, že v extrémních případech mohou způsobit ztrátu dat – pokud si usmyslí, že Vaše databáze je virus.

Martin

Těch 6 zápisů mám průměrně také, ale občas některý přeleze sekundu a to i na druhém počítači s vypnutým antivirem. Myslím, že tam je to způsobeno stránkováním ve Windows 7 při hodně zaplněném disku. No nic, nějak se s tím vypořádám. PRAGMA synchronous=OFF použít nemohu z výše uvedených důvodů. Server u nás je „pro všechno“, jede na něm kdeco od FTP přes webové služby, několik druhů vzdálených ploch, Historian a testovací aplikace. Připojuje se tam „kdekdo“, ale je poměrně řídce využívaný, takže antivirus je na něm správně. Jsme příliš malá firma na zaplacení někoho, kdo by se server staral. Mimochodem drobný offtopic – netušíte někdo, jak se mohlo stát, že Apache jednoho dne náhle nefungoval, protože port 80 obsadil IIS? Sám od sebe v době, kdy na serveru nikdo nepracoval.

uplnej vypatlanec

a jake je zakladni nastaveni ?! kdyz pri ON i OF to jsou tisice za sec ? ( ptam se, protoze SQLite take pouzivam)

uplnej vypatlanec
uplnej vypatlanec
Honza Kral

NE, dalsi blud! nejedna se o zadne cekani na plony nebo neco podobneho, s otacenim disku to nema spolecne naprosto vubec nic. Jedna se o to, ze kvuli zachovani ACID se vola fsync (odtud koneckoncu i nazev nastaveni pro vypnuti). Jestli chcete zvysit rychlost zapisu, pouzijte proste DB ktera je k tomu urcena (skoro vsechny ostatni) a nebo zmente sve pozadavky – ne rdbms ale cache s rdbms featurami a pouzijte in-memory sqlite (‚:memory:‘ jako db name).

Martin

Dík, tohle zní rozumněji než plotna otáčející se pětkrát za sekundu. Jsou ale in-memory tabulky přistupné z různých procesů – mohu je umístit do sdílené paměti, aniž bych příliš měnil zdrojáky SQLite? Synchronizaci je vyřešená, k souběhu nedojde. Předpokládám, že zkopírování změn na disk pak bude opět v čase jedné transakce, tedy těch průměrně 170 ms, což by mi bohatě stačilo.

David Grudl

Nechtěl jsem remcat, když se tu objevil „Velký letní test nevíme-vlastně-nikdo-čeho“, protože jsem chápal, že když má Root komiks, Zdroják chce taky bavit. Nechtěl jsem remcat ani u článku „Shrnutí soutěže WebTop100 2012“, jehož obsah by vydal na jeden ne příliš zajímavý tvít. Ale úvod do SQLite na konci roku 2013? V němž se dozvíme, proč používat SQLite pro web: „SQLite má velmi příznivou licenci Public Domain. Můžeme ji tedy bez omezení používat v komerčních i nekomerčních aplikacích.“ WTF?

Chápu, že podobně kvalitní autory, jako je třeba Jakub Mrozek, je těžké najít, ale nebylo by lepší jít cestou překladů, jako byly třeba výborné články Honzy Javorka o Pythonu? Tohle už totiž dost vrže…

(Disclaimer: slíbil jsem a následně nedodal Zdrojáku nemalé množství článků.)

Honza Černý

Troufalost to neni, jen mi prijde ze je spise doba, kdy by se melo psat treba o https://www.firebase.com nez nic-clanek o SQLite. (Nic proti ni nemam)

head

Ide to dole vodou odkedy sa zdrojak oddelil od roota. Uz sem chodil len ked sa velmi nudim a nepamatam, kedy som tu naposledy nasiel nieco zaujimave…

blizz

hladam objektovu databazu do, ktorej by sa dali jednoducho serializovat / deserializovat .NET objekty, ktore by zaroven vystupovali ako recordy v tabulke… databaza by zvladala aj relacny aj objektovy pristup mala by podporu OQL. skusal som Caché ale odradil ma objectscript – co je objektivne velmi hnusny jazyk a gui nastroje, ktore vyzeraju ako notepad z roku 92.

jirkakosek

A co zkusit nějakou XML databázi a XQuery?

Petr Prchal

Mozna by bylo dobry popsat jeste nejakou zastaralejsi technologii, aby mladez videla, jak jsme to meli tezke…

Zdrojaku prosim, zacni vydavat zas nejaky lidsky clanky, ORM test PHP frameworku sotva skoncil a uz zas je to spatny.

jakubvrana

SQLite při zápisu zamkne celou databázi, během čehož nemůže kdokoliv další nic zapisovat, ale ani číst. To nevadí u mobilních a dalších jednouživatelských aplikací, ale na webu to může být fatální problém, protože s databází typicky potřebuje pracovat mnoho uživatelů najednou. Pro většinu webových aplikací je tedy SQLite kvůli výkonnostní charakteristice nevhodné (celkem rychlý jednouživatelský režim nevyváží blokující víceuživatelský režim).

Chybějící podpora Unicode bude často také problém. Samozřejmě si můžeme data setřídit v hostitelském jazyku, to ale znamená nejprve načíst všechna data z disku a zabrat jimi spoustu paměti. Dotaz ORDER BY name LIMIT 10 při existenci indexu nad sloupcem name ve správném způsobu porovnávání načte z disku a zabere v paměti maximálně 10 řádek. Při chybějící podpoře požadovaného porovnávání to znamená z disku načíst všech, třeba 1000000 řádek, které jsou v tabulce. Je to problém nejen z výkonnostního pohledu, ale i kvůli komplikaci kódu.

Není pravda, že SQLite 3 je doporučeno používat pouze prostřednictvím PDO. Extenze sqlite3 je plnohodnotnou alternativou.

David Grudl

Extenze sqlite3 v PHP trpí nikdy neopraveným bugem týkajícím se zamykání, tudíž je pro webové aplikace zcela nepoužitelná. Bohužel.

https://bugs.php.net/bug.php?id=51295

jakubvrana

Odkazovaný bug je opravený, metoda ve zdrojácích PHP skutečně je. Nemyslel jsi nějaký jiný?

David Grudl

Máš pravdu, netuším proč jsem ho považoval za nevyřešený.

jirkakosek

Zdá se, že historie se musí opakovat. Princip SQLite pro webové aplikace mi připomněl skoro 15 let starou historku, kdy se aplikace napsaná v ASP + Access zasekla, když ji začalo používat více uživatelů najednou. Lidi, pokud chcete na webu používat databázi, tak něco pořádného, a ne něco, co si tam šudlá a zamyká jeden soubor.

jirkakosek

Má cenu na tohle odpovídat?

Problém SQLite je v tom, že je to embeded databáze ne databázový server, ke kterému se připojují klienti. Pro více zatížené webové aplikace, kdy do databáze přistupuje obsluha více požadavků najednou, není koncept SQLite z principu vhodný. Naopak je SQLite vhodné, jako úložiště na klientovi – ať už třeba pro nativní mobilní aplikace, nebo volané z JS v prohlížeči, …

Podobně jako u relačních databází i u XML databází existují embeded a serverové produkty.

jakubvrana

Režim WAL je ve výchozím nastavení vypnut. V článku o tom není ani slovo, takže začátečník (pro kterého je článek určen) použije SQLite tak, jak je přednastavena, a bude si myslet, že se bude chovat v souladu s tím, co je uvedeno v článku. To ale nebude.

Martin

Ahoj Jakube. Máš naprostou pravdu, že pro běžné použití na webu není SQLite vhodná. Ale třeba mně článek pomohl a hlavně diskuse k němu. Potřebuji databázi, která bude zdarma, bezúdržbová, pro zápis přístupná z průmyslové aplikace a pro čtení přes webové rozhraní, nejlépe z Admineru. Nic lepšího než SQlite jsem zatím nenašel, protože MySQL, MSSQL i další „dospělé“ databáze se ukázaly pro mnohaleté kontinuální bezúdržbové použití v uzavřené bedně (jednoúčelový průmyslový počítač pro sběr dat, kam mohu často jen na jednom TCP portu díky bezpečností politice u většiny průmyslových koncernů) nevhodné. Divil by ses, jakou setrvačnost má průmyslová IT oblast a i 20 let staré noviny se občas hodí. Mimochodem měl jsem pár námětů na diskusi k Admineru 3.7, ale asi tam starší diskuse už nečteš, tak se zeptám ještě tady: Lze v novém Admineru vypnout „mobilní“ zobrazení na mobilním zařízení? Víš o chybičce, kdy se u krátké obrazovky zápatí píše přes data a roluje s nimi nahoru? Lze pro sqlite dodělat podporu triggerů na pohledu? Lze pro sqlite dodělat podporu importu adminer.sql.gz v jedné transakci (import tabulky s několika tisíci řádky teď trvá skoro celý den a zasekne na tu dobu na serveru přístup k sqlite databázi – viz diskuse tady nahoře). Nebo exportu do gz „obaleného“ transakcí? Samozřejmě mohu sql editovat ručně a zabalit do gz, ale většina editorů má s velmi dlouhými textovými soubory problém. Ideální by bylo nastavit v Admineru možnost „obalit transakcí každých 1000 insertů“ nebo něco podobného s možností nastavení této konstanty.

klw

Zkuste H2 nebo Derby, pokud pro vás není překážkou, že jsou psané v Javě.

honza

Samozrejme, ze sqlite funguje i ve viceuzivatelskem modu. Jak je v diskuzi spravne uvedeno, je treba pouzivat transakce a prepare statements a ve vyhodnocovani reagovat na SQLITE_BUSY.

Martin

Ahoj. Občas mám výpadek v aplikaci, která nyní jako jediná zapisuje do SQLite databáze, když současně čtu z Admineru (přes PDO). Je to v pořádku? Děje se to dost zřídka a zatím jsem neodchytl výjimku, nějak jí vnitřně obslouží Kompex wrapper, ale občas je v zapsaných datech díra (chybí cca 1 řádek z každých 50000, když jich zapisuji v jedné transakci okolo 1,5M). Debugger VS se zastaví v obsluze výjimky, ale zatím jsem nezjistil, odkud pochází, výjimky jsou tam řešené dost zmateně. Myslel jsem, že zámky jsou jen v době zápisu. Ono je to tak, že čtení může u SQLite 3 v defaultním nastavení znemožnit zápis? Nemělo by to v takovém případě čekat na timeout? Ten mám defaultní, což je, myslím, jedna minuta. Nebo je pro zápis jiný než pro čtení?

Martin

Oprava: Není to v celé v jedné transakci, ale rozděleno do několika po zapisovaných 14400 řádcích (denní záznam s deseti řádky za minutu). Jak je vůbec možné, že v takovém případě chybí v tabulce jednotlivé řádky? Indexy jsou zrušené a znovu vytvořené po ukončení poslední transakce.

Martin

Pochopil-li jsem to dobře, mělo by to řešit nastavení PRAGMA journal_mode=WAL. Otestuji a uvidím, teď už vím jistě, že šlo o zámek databáze pro zapisující aplikaci při čtení z jiné aplikace.

Enum a statická analýza kódu

Mám jednu univerzální radu pro začínající programátorty. V učení sice neexistují rychlé zkratky, ovšem tuhle radu můžete snadno začít používat a zrychlit tak tempo učení. Tou tajemnou ingrediencí je statická analýza kódu. Ukážeme si to na příkladu enum.

Pocta C64

Za prvopočátek své programátorské kariéry vděčím počítači Commodore 64. Tehdy jsem genialitu návrhu nemohl docenit. Dnes dokážu lehce nahlédnout pod pokličku. Chtěl bych se o to s vámi podělit a vzdát mu hold.