Klient shellowy SQLite

Andrzej Galiński · 2021-02-12

Cel: udokumentować podstawowe przypadki użycia SQLite z klientem shellowym.

SQLite

SQLite (https://sqlite.org/):

jest Open-Source i w domenie publicznej;
zawsze wydawał mi się “niepoważną”, zabawkową bazą danych… niesłusznie;
jest najbardziej rozpowszechnioną bazą danych (prostota i wieloplatformowość sprawiają, że to “baza z wyboru” m.in. dla rozmaitych rozwiązań “wbudowanych”, jak systemy multimedialne w automotive);
nie jest bazą klient-serwer, tylko wbudowaną. Programy używające SQLite wywołują funkcje z biblioteki sqlite, z którą zostały zlinkowane;
wspiera pełnoprawny SQL i ma zaawansowane opcje, np. indeksy częściowe czy obsługę JSON. Lista ficzerów jest imponująca;
ponoć pozwala wykonywać operacje zapisu/odczytu danych szybciej niż przy bezpośrednim I/O z dysku; prawdopodobną przyczyną tego zjawiska jest jednokrotne używanie open / close przez sqlite (taka sama operacja na wielu plikach wymagałaby wielu wywołań systemowych).

A poza tym:

mają prostą i czytelną stronę internetową
używają “nietypowego” systemu kontroli wersji, fossil-scm (jest też klon na githubie)
deklarują wsparcie i kompatybilność wsteczną przynajmniej do 2050 r.
mają szalone, w pozytywnym znaczeniu, testy. 100% pokrycia i dwukierunkowe testowanie wszystkich branchy. I fuzz testy.

Instalacja

ze strony można pobrać binarki na wszystkie główne platformy (https://sqlite.org/download.html)
dystrybucje linuksa udostępniają pakiety (np. APTowe sqlite, sqlite3 na Debianie/Ubuntu)
każdy szanujący się język programowania ma biblioteki klienckie

Dla wygody przykłady będę uruchamiał w dockerze:

$ docker run --rm -it alpine:latest sh
/ # apk update -q && apk add sqlite -q
/ # sqlite3
SQLite version 3.25.3 2018-11-05 20:37:38
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> 

Podstawowe użycie

Uruchamianie

sqlite [options] filename [SQL]

1 plik, 1 baza. Plik podaje się przy starcie, jeśli nie istnieje, zostanie założony. W SQLite3 można także korzystać z bazy danych w pamięci i zapisać ją do pliku w wybranym momencie.

Meta-komendy, czyli polecenia kierowane do interpretera, a nie wprost do biblioteki bazodanowej zaczynają się od kropki - np.:

/ # sqlite test1.sqlite
SQLite version 3.25.3 2018-11-05 20:37:38
Enter ".help" for usage hints.
sqlite> .databases
main: //test1.sqlite
sqlite>

Tak, .help warto przeczytać. :)

Do sesji można dodać dodatkową bazę danych SQLową komendą ATTACH:

sqlite> ATTACH './test2.sqlite' AS test2_db;
sqlite> .databases
main: //test1.sqlite
test2_db: //./test2.sqlite
sqlite>

SQL

Wyrażenia działają tak jak w innych dialektach:

sqlite> select 1+3, MAX(3,42), null, "test";
4|42||test

Metakomendami .mode i .headers można zmienić tryb wyświetlania na podobny do innych SQLi:

sqlite> .mode column
sqlite> .headers on
sqlite> select 1+3, MAX(3,42), null, "test";
1+3         MAX(3,42)   null        "test"
----------  ----------  ----------  ----------
4           42                      test

Komendy SQLowe można podawać jako argument (wchodząc w tryb nieinteraktywny):

/ # sqlite3 foo.db 'select 123'
123

Można też czytać je z pliku:

/ # cat tabele.sql
-- średnie miesięczne temperatury w Krakowie (2005-2018 z dziurami)
CREATE TABLE temperatures(month_symbol, avg_temp);
INSERT INTO temperatures VALUES
    ("I",-2.48),
    ("II",-1.06),
    ("III",3.36),
    ("IV",9.6),
    ("V",14.18),
    ("VI",18),
    ("VII",19.96),
    ("VIII",19.8),
    ("IX",13.94),
    ("X",8.32),
    ("XI",4.8),
    ("XII",0.04);

-- mapowania oznaczeń miesięcy
CREATE TABLE months(month_symbol, month);
INSERT INTO months VALUES
    ("I",1),
    ("II",2),
    ("III",3),
    ("IV",4),
    ("V",5),
    ("VI",6),
    ("VII",7),
    ("VIII",8),
    ("IX",9),
    ("X",10),
    ("XI",11),
    ("XII",12);
/ # sqlite3 krk.sqlite < tabele.sql 

Reszta SQLa (filtry, joiny, sortowania) też działa:

/ # sqlite3 krk.sqlite 
SQLite version 3.25.3 2018-11-05 20:37:38
Enter ".help" for usage hints.
sqlite> .tables
months        temperatures
sqlite> SELECT * FROM temperatures where avg_temp > 5;
IV|9.6
V|14.18
VI|18
VII|19.96
VIII|19.8
IX|13.94
X|8.32
sqlite> SELECT avg(avg_temp) FROM temperatures;
9.03833333333333
sqlite> 
sqlite> CREATE TABLE temperatures_fixed AS
   ...> SELECT m.month,t.avg_temp
   ...>   FROM temperatures AS t
   ...>   JOIN months AS m
   ...>   ON t.month_symbol=m.month_symbol;
sqlite> 
sqlite> SELECT * from temperatures_fixed LIMIT 4;
1|-2.48
2|-1.06
3|3.36
4|9.6
sqlite> 
sqlite> UPDATE temperatures_fixed
   ...>   SET avg_temp=0
   ...>   WHERE month=1;
sqlite> 
sqlite> SELECT * from temperatures_fixed LIMIT 4;
1|0
2|-1.06
3|3.36
4|9.6

Rozmaitości

Typy w SQLite są ”elastyczne”. Baza nie sprawdza, czy np. nie próbujemy wsadzić tekstu do pola typu INT. Nie trzeba też podawać typów przy zakładaniu tabel, co bardzo upraszcza prototypowanie.
Łatwo zacząć od nowa, kasując plik z bazą.
.dump pozwala zrzucić bazę w formacie nadającym się do reimportu. Przydatne, jeśli chcemy przeczytać dane, poprawić ręcznie trochę rekordów i wrzucić z powrotem.
Interpreter jest wygodny, ale daleko mu do mycli albo chociaż psql.
SQLite2 nie czyta baz SQLite3 (i vice versa, i bardzo dobrze).
Jest wiele tematów, które warto jeszcze poruszyć przy okazji innych jednodniówek - transakcje, indeksy, widoki, triggerry, rozszerzenia, równoczesne użycie bazy przez wiele procesów…