Seznámení s SQLite - díl druhý

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

Co bude obsahem dnešního dílu?

V minulém díle jsme se stručně seznámili s odlehčenou databázovou knihovnou SQLite. Dále jsme probrali její výhody a nevýhody, popsali její instalaci a ukázali jsme si, jak se v ní vytvoří jednoduchá databáze. Dnes bych chtěl čtenáře seznámit s řádkovým klientem SQLite. Dále bych se chtěl věnovat použití SQLite v nejpoužívanějších programovacích jazycích.

Po uveřejnění minulého dílu jsem byl velmi příjemně překvapen tím, že se pod blogem objevilo několik velmi konstruktivních připomínek v diskusi. Na závěr tohoto dílu se budu připomínkám věnovat. Chtěl bych tímto poděkovat všem za podnětné reakce.

Řádkový klient SQLite

SQLite nám přínáší CLI klienta, kterého můžeme použít k vytvoření nové či otevření již existující databáze a veškeré manipulaci s jejím obsahem.

Instalace

SQLite CLI klient je již poměrně dlouho standarní součástí hlavních Linuxových distribucí. Jeho instalace v Debianu je opravdu záležitostí několika sekund a provedeme ji takto:

aptitude install sqlite3

Spuštění a vytvoření testovací databáze

Spuštění řádkového klienta a vytvoření nové testovácí databáze provedeme tímto způsobem:

sqlite3 test.db

SQLite version 3.8.7.1 2014-10-29 13:59:56
Enter ".help" for usage hints.
sqlite>

Tímto jsme vytvořili novou databázi, která bude uložena v souboru test.db. Pokud bychom se k ní chtěli připojit v budoucnu, provedeme to úplně stejným příkazem. SQLite novou databázi vytvoří v případě zadání neexistujícího jména souboru. Pokud zadaný soubor existuje, pokusí se SQLite otevřít databázi, která je v něm obsažena.

Hlavní příkazy řádkového klienta

Jak můžete vidět výše, SQLite klient poslušně čeká na zadání příkazů. V následujících odstavcích bude probráno alespoň několik hlavních příkazů SQLite klienta. Na tomto místě je důležité říci, že vestavěné příkazy SQLite klienta jsou uvozeny znakem tečky.

Příkaz .help

Nevím jak vy, ale já osobně mám velice špatnou paměť, takže se tento příkaz stal mým nejoblíbenějším a nedocenitelným pomocníkem při práci s klientem SQLite. Jak je z názvu jistě patrné, slouží tento příkaz k vypsání nápovědy se seznamem příkazů klienta s jejich stručným popisem.

sqlite> .help
.backup ?DB? FILE      Backup DB (default "main") to FILE
.bail on|off           Stop after hitting an error.  Default OFF
.clone NEWDB           Clone data into NEWDB from the existing database
.databases             List names and files of attached databases
.dump ?TABLE? ...      Dump the database in an SQL text format
                         If TABLE specified, only dump tables matching
                         LIKE pattern TABLE.
.echo on|off           Turn command echo on or off
.eqp on|off            Enable or disable automatic EXPLAIN QUERY PLAN
.exit                  Exit this program
.explain ?on|off?      Turn output mode suitable for EXPLAIN on or off.
                         With no args, it turns EXPLAIN on.
.fullschema            Show schema and the content of sqlite_stat tables
.headers on|off        Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.indices ?TABLE?       Show names of all indices
                         If TABLE specified, only show indices for tables
                         matching LIKE pattern TABLE.
.load FILE ?ENTRY?     Load an extension library
.log FILE|off          Turn logging on or off.  FILE can be stderr/stdout
.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         csv      Comma-separated values
                         column   Left-aligned columns.  (See .width)
                         html     HTML table code
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by .separator string
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Use STRING in place of NULL values
.once FILENAME         Output for the next SQL command only to FILENAME
.open ?FILENAME?       Close existing database and reopen FILENAME
.output ?FILENAME?     Send output to FILENAME or stdout
.print STRING...       Print literal STRING
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.restore ?DB? FILE     Restore content of DB (default "main") from FILE
.save FILE             Write in-memory database into FILE
.schema ?TABLE?        Show the CREATE statements
                         If TABLE specified, only show tables matching
                         LIKE pattern TABLE.
.separator STRING ?NL? Change separator used by output mode and .import
                         NL is the end-of-line mark for CSV
.shell CMD ARGS...     Run CMD ARGS... in a system shell
.show                  Show the current values for various settings
.stats on|off          Turn stats on or off
.system CMD ARGS...    Run CMD ARGS... in a system shell
.tables ?TABLE?        List names of tables
                         If TABLE specified, only list tables matching
                         LIKE pattern TABLE.
.timeout MS            Try opening locked tables for MS milliseconds
.timer on|off          Turn SQL timer on or off
.trace FILE|off        Output each SQL statement as it is run
.vfsname ?AUX?         Print the name of the VFS stack
.width NUM1 NUM2 ...   Set column widths for "column" mode
                         Negative values right-justify

Příkaz .tables

Jak je z názvu toho příkazu zřejmé, slouží k vypsání seznamu tabulek. Abychom si mohli nějaké tabulky zobrazit, musíme v databázi nějaké mít. Použijeme tedy příkazy z minulého dílu a vložíme několik testovacích tabulek:

sqlite> PRAGMA foreign_keys = ON;
sqlite> CREATE TABLE persons(id integer primary key autoincrement, name text, surname text);
sqlite> CREATE TABLE things(id integer primary key autoincrement, name text, owner_id integer, FOREIGN KEY(owner_id) REFERENCES persons(id));

Prvním příkazem jsme zapnuli kontrolu referenční integrity, což osobně považuji v dnešní době za samozřejmost. Dalším příkazem jsme vložili tabulku persons a posledním příkazem tabulku things. Pokud máte zájem, je vytvoření testovací databáze podrobněji probráno v minulém díle tohoto seriálu.

V databázi tedy máme dvě testovací tabulky a nic nám nyní tedy nebrání v tom, abychom si vypsali jejich seznam:

sqlite> .tables
persons  things

Jak můžeme vidět, SQLite klient nám vypsal vložené testovací tabulky. V našem případě tam jsou pouze dvě, ale v reálném světě může nastat situace, kdy databáze bude obsahovat velké množství tabulek. Příkaz .tables nám proto nabízí i možnost filtrovat vypsané tabulky pomocí jednoduchých vzorů:

sqlite> .tables %
persons  things
sqlite> .tables p%
persons
sqlite> .tables thi%s
things
sqlite> .tables %s
persons  things

V případě většího množství tabulek může tato možnost usnadnit vaši práci.

Příkaz .schema

Seznam tabulek si sice umíme zobrazit, ale jistě by se nám hodilo si zobrazit, jakou má která tabulka strukturu. K tomuto zjištění slouží příkaz .schema.

Strukturu všech tabulek v databázi si můžeme zobrazit následujícím způsobem:

sqlite> .schema
CREATE TABLE persons(id integer primary key autoincrement, name text, surname text);
CREATE TABLE things(id integer primary key autoincrement, name text, owner_id integer, FOREIGN KEY(owner_id) REFERENCES persons(id));

Jak můžeme vidět na výpisu výše, byly nám zobrazeny příkazy sloužící k vytvoření našich tabulek. Tímto způsobem můžeme zjistit, jaké sloupce naše tabulky obsahují.

Pokud uvedeme za příkazem jméno tabulky, bude zobrazen příkaz pouze pro tuto tabulku:

sqlite> .schema persons
CREATE TABLE persons(id integer primary key autoincrement, name text, surname text);

Příkaz .schema umí, obdobně jako příkaz .tables, filtrovat jména tabulek, pro která bude vypsána struktura, podle jednoduchých vzorů:

sqlite> .schema p%
CREATE TABLE persons(id integer primary key autoincrement, name text, surname text);
sqlite> .schema t%
CREATE TABLE things(id integer primary key autoincrement, name text, owner_id integer, FOREIGN KEY(owner_id) REFERENCES persons(id));
sqlite> .schema %s
CREATE TABLE persons(id integer primary key autoincrement, name text, surname text);
CREATE TABLE things(id integer primary key autoincrement, name text, owner_id integer, FOREIGN KEY(owner_id) REFERENCES persons(id));

Zadávání SQL příkazů

I když je to patrně jasné, pro jistotu zde uvedu, že do řádkové klienta lze samozřejmě vkládat i klasické SQL příkazy pro manipulaci s daty:

sqlite> insert into persons(id, name, surname) values(null, 'Jmeno', 'Prijmeni');
sqlite> select * from persons;
1|Jmeno|Prijmeni
sqlite>

Příkaz .exit a .quit

Ve chvili, kdy ukončíte svou práci s databázi, budete jistě chtít korektně ukončit běh řádkového klienta. To provedete zadáním příkazu .exit či .quit.

Ukázky použití SQLite

Použití SQLite v řádkovém klientu je samozřejmě dobrá věc, ale v reálném světě budeme samozřejmě chtít použít námi vytvořenou databázi ve svém počítačovém programu. SQLite podporuje velké množství jazyků, takže s dostupností by problém snad být neměl. V tomto článku ukáži použítí SQLite v programovacích jazycích Java, C a PHP. Ukázky budou velice stručné, ale doufám, že mohou posloužit jako inspirace.

Použití v Javě

Pro práci se SQLite existuje v Javě více možností. Pro ukázku jsem vybral knihovnu sqlite-jdbc, kterou můžete nalézt zde: https://github.com/xerial/sqlite-jdbc.

K tomu, abychom mohli začít využívat knihovnu sqlite-jdbc, musíme si nejdříve stáhnout její jar archív. Stažení provedeme takto:

wget 'https://bitbucket.org/xerial/sqlite-jdbc/downloads/sqlite-jdbc-3.8.11.2.jar'

Výše uvedený příkaz nám pomocí mého oblíbeného programu wget stáhnul do aktuálního pracovního adresáře jar soubor obsahující knihovnu sqlite-jdbc.

Nyní můžeme přejít k samotné ukázce. Do stejného adresáře, kde máme stáhnutý zmíněný jar soubor, umístíme naši testovací databázi test.db a také tento soubor se zdrojovým kódem v Javě, který nazveme SQLiteDemo.java:

import java.sql.*;


class SQLiteDemo {

    public static void main(String args[]) throws Exception {

        // Connection string je "jdbc:sqlite:CESTA_K_SOUBORU_S_DB"
        Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");

        // Vlozime testovaci data
        Statement s = conn.createStatement();
        s.executeUpdate("insert into persons(id, name, surname) values(null, 'Jan', 'Novak')");
        s.executeUpdate("insert into persons(id, name, surname) values(null, 'Jana', 'Novakova')");
        s.executeUpdate("insert into persons(id, name, surname) values(null, 'Josef', 'Uzasny')");

        // Provedeme vyber vsech dat z tabulky a jejich vypsani
        ResultSet rSet = s.executeQuery("select id, name, surname from persons");

        System.out.println("Seznam osob v testovaci databazi");
        System.out.println("================================");

        while (rSet.next()) {

            int personID = rSet.getInt("id");
            String personName = rSet.getString("name");
            String personSurname = rSet.getString("surname");

            System.out.println("ID: " + personID + " Jmeno: " + personName + " Prijmeni: " + personSurname);
        }

        // Zavreme databazi
        conn.close();
    }
}

Klíčové věci jsou ve zdrojovém kódu komentovány. Pokud máme zdrojový kód uložený, zkompilujeme jej následujícím příkazem:

javac SQLiteDemo.java

Pokud vše proběhne v pořádku, což by mělo, dojde k vytvoření zkompilovaného souboru SQLiteDemo.class, který spustíme následujícím příkazem (do classpath přidáme dříve stažený jar soubor):

java -cp ".:sqlite-jdbc-3.8.11.2.jar" SQLiteDemo

Program se připojí k databázi obsažené v souboru test.db, vloží několik testovacích záznamů do tabulky persons, provede výběr všech záznamů z tabulky persons a následně získaná data zobrazí a zavře připojení k databázi. Pokud vše dopadlo bez chyby, měli byste vidět následující výstup:

Seznam osob v testovaci databazi
================================
ID: 1 Jmeno: Jmeno Prijmeni: Prijmeni
ID: 2 Jmeno: Jan Prijmeni: Novak
ID: 3 Jmeno: Jana Prijmeni: Novakova
ID: 4 Jmeno: Josef Prijmeni: Uzasny

Poznámka: Položka s ID 1 vznikla při našem zkoušení řádkového klienta. Uvedený kód byl zkompilován a otestován na Debianu ve verzi 8.4 s distribučním openjdk.

Použití v C

Na rozdíl od Javy, kde jsme použili cizí 3rd party knihovnu, nám v případě programovacího jazyka C nic nebrání v tom použít kód i dokumentaci přímo od programátorů SQLite. Dokumentaci naleznete např. zde: https://www.sqlite.org/cintro.html.

Knihovnu pro využítí SQLite v C nainstalujeme v Debian Linuxu takto:

aptitude install libsqlite3-dev

Ukázku budeme kompilovat tradičním kompilátorem gcc. Pokud jej nemáte nainstalovaný, můžete jej do svého systému přidat třeba takto:

aptitude -r install gcc

V tomto bodu bychom měli mít nainstalovány veškeré potřebné věci, které pro úspěšné spuštění ukázky budeme potřebovat.

Do stejného adresáře, kde máme umístěnu naši testovací databázi test.db, umístíme soubor se zdrojovým kódem v jazyce C, který nazveme SQLiteDemo.c. Soubor bude mít tento obsah:

#include <stdio.h>
#include <stdlib.h>

// Vlozeni hlavickoveho souboru sqlite
#include <sqlite3.h>

int main() {

    sqlite3 * testDB;
    int r;
    sqlite3_stmt * s;
    char * sql;
    int personID;
    const unsigned char * personName;
    const unsigned char * personSurname;

    // Otevreni databaze
    r = sqlite3_open("test.db", &testDB);
    if (r != SQLITE_OK) {
        printf("Chyba pri otevirani db!\n");
        exit(EXIT_FAILURE);
    }
    else {

        printf("DB uspesne otevrena.\n");
    }

    // Vypsani dat z tabulky persons
    // Poznamka: Vyber dat by bylo mozne velmi zjednodusit pouzitim funkce sqlite3_exec.
    sql = "select id, name, surname from persons";
    r = sqlite3_prepare(testDB, sql, -1, &s, NULL);
    if (r != SQLITE_OK) {
        printf("Chyba pri priprave db statement!\n");
        exit(EXIT_FAILURE);
    }

    // Zpracovani vracenych zaznamu z databaze
    printf("Seznam osob v tabulce persons\n=====\n");
    while (sqlite3_step(s) == SQLITE_ROW) {
        personID = sqlite3_column_int(s, 0);
        personName = sqlite3_column_text(s, 1);
        personSurname = sqlite3_column_text(s, 2);
        printf("ID: %d Jmeno: %s Prijmeni: %s\n=====\n", personID, personName, personSurname);
    }

    // Zavreni db statement
    r = sqlite3_finalize(s);
    if (r != SQLITE_OK) {
        printf("Chyba pri uzavreni db statement!\n");
        exit(EXIT_FAILURE);
    }

    // Zavreni databaze
    sqlite3_close(testDB);

    return EXIT_SUCCESS;
}

Všechny klíčové záležitosti ve zdrojovém kódu jsou náležitě okomentovány.

Zdrojový kód zkompilujeme následujícím příkazem:

gcc SQLiteDemo.c -o sqlitedemo -lsqlite3 -Wall

Uvedený příkaz zkompiluje zdrojový soubor naší ukázky v souboru SQLiteDemo.c. V případě úspěšné kompilace je vygenerován spustitelný soubor sqlitedemo.

Nyní již můžeme následujícím příkazem ukázku spustit:

./sqlitedemo
DB uspesne otevrena.
Seznam osob v tabulce persons
=====
ID: 1 Jmeno: Jmeno Prijmeni: Prijmeni
=====
ID: 2 Jmeno: Jan Prijmeni: Novak
=====
ID: 3 Jmeno: Jana Prijmeni: Novakova
=====
ID: 4 Jmeno: Josef Prijmeni: Uzasny
=====

Pokud šlo vše tak, jak by mělo, měli bychom vidět výpis testovacích dat z tabulky persons.

Poznámka: Uvedený zdrojový kód byl zkompilován a spuštěn na počítači se systémem Debian Linux 8.4. Veškeré použité knihovny a kompilátor pocházely z distribučního repozitáře.

Použití v PHP

V PHP je již dlouhé roky standardem přistupovat k databázím pomocí mechanismu PDO, které nabízí možnost odstínit programátora od konkrétního typu použité databáze a také přináší některé bezpečnostní benefity, jejichž popsání je však mimo rámec tohoto článku. PDO podporuje mnoho typů databází a SQLite samozřejmě není výjimkou, což nám umožnuje využít PDO i v naší ukázce.

Ve stejném adresáři, kde máme umístěnu naši testovací databázi test.db, vytvoříme soubor se zdrojovým kódem v PHP, který nazveme SQLiteDemo.php, s následujícím obsahem:

<?php
    echo "Verze PHP: " . phpversion() . "\n\n";

    try {
        // Pripojeni k databazi
        $pdo = new PDO("sqlite:test.db");

        // Vyber vsech dat z tabulky persons
        $statement = $pdo->query("select id, name, surname from persons");

        // Zobrazeni vsech zaznamu
        foreach ($statement as $person) {
            $personID = $person["id"];
            $personName = $person["name"];
            $personSurname = $person["surname"];

            echo "ID: {$personID} Jmeno: {$personName} Prijmeni: {$personSurname}\n=====\n";
        }
    }
    catch (Exception $e) {
        echo "Vyjimka:\n";
        var_dump($e);
    }

Nyní můžeme náš skript spustit následujícím příkazem:

php SQLiteDemo.php
Verze PHP: 5.6.19-0+deb8u1

ID: 1 Jmeno: Jmeno Prijmeni: Prijmeni
=====
ID: 2 Jmeno: Jan Prijmeni: Novak
=====
ID: 3 Jmeno: Jana Prijmeni: Novakova
=====
ID: 4 Jmeno: Josef Prijmeni: Uzasny
=====

Pokud dopadlo vše dobře, uvidíte na výstupu výpis dat z testovací tabulky persons.

Poznámka: Skript byl otestován na počítači s Debian Linuxem ve verzi 8.4 s nainstalovaným PHP ve verzi 5.6.19 z distribučních repozitářů.

Přípomínky diskutujících

Po uveřejnění minulého dílu jsem byl velice příjemně překvapen, protože se pod článkem objevilo spoustu pochvalných a podnětných reakcí od čtenářů. Za všechny reakce bych chtěl touto cestou poděkovat.

Jak jsem již napsal, diskutující měli několik podnětných připomínek, které jsem se rozhodl zmínit v tomto díle.

Databáze uložená v paměti

SQLite podporuje i uložení používané databáze v operační paměti počítače. Tyto databáze existují jen do té doby, kdy je uzavřeno připojení k databázi. Operace s databázemi uloženými v operační paměti jsou samozřejmě o hodně rychlejší než operace s databázemi uloženými na pevném disku. Osobně si umím představit využití pro různé cache a případně pro zjednodušení výpočtů, které vyžadují zpracovat nějaká data a získat z nich statistiku, což může být použitím jazyka SQL výrazně usnadněno a může vést k úspoře kódu v klasickém programovacím jazyku.

Novou databázi v operační paměti vytvoříte tak, že místo názvu souboru pro databázi uvedete řetězec :memory:

sqlite3 ":memory:"
SQLite version 3.8.7.1 2014-10-29 13:59:56
Enter ".help" for usage hints.

Vývoj SQLite a nové verze

SQLite je poměrně aktivně vyvíjen a je dobré sledovat poznámky k jednotlivým vydaným verzím, protože může dojít k přidání nových zajímavých vlastností a třeba i ke zlepšení výkonu. Poznámky k vydáním naleznete zde: http://www.sqlite.org/changes.html.

Výkonostnímu testu aktuální a některých starších verzí SQLite se budu možná věnovat v dalším dílu.

Šifrování databáze

Další přípomínka jednoho z diskutujících se týkala šifrování databázového souboru. Šifrování je možné používat díky doplňkům, které tuto funkcionalitu do SQLite přidávají. Osobně se mi líbi tento projekt: https://github.com/sqlcip­her/sqlcipher, který je poměrně aktivně vyvíjen a jehož použití se možná budu věnovat v dalším dílu.

SQLite jako formát souborů aplikace

Jedním z dalších diskutujících jsem byl upozorněn, že další možností využití SQLite je použít jej jako formát pro ukládání aplikačních dat (aplikační formát). Pokud v aplikaci potřebujete uložit na příklad více souborů s různými typy, můžete použitím SQLite jako aplikačního formátu ušetřit čas při vývoji. Více o této problematice naleznete zde: https://www.sqlite.org/ap­pfileformat.html.

Závěr

Dnes jsme si představili řádkového klienta SQLite, ukázali jsme si využití databáze SQLite v programovacích jazycích Java, C a PHP a na závěr jsme probrali podnětné připomínky od čtenářů minulého dílu.

Děkuji každému čtenáři, který dočetl až sem. Doufám, že byl tento díl pro někoho přínosný.

V příštím dílu bych se chtěl věnovat složitějším SQL dotazům, indexům a některým netradičním rozšířením.