Scott Tiger Tech Blog

Blog technologiczny firmy Scott Tiger S.A.

SQLite

Autor: Piotr Karpiuk o wtorek 9. Sierpień 2011

SQLite to zapoczątkowana w 2000 r. przez Richarda Hippa i dostępna na licencji public domain niewielka biblioteka zawierająca interpreter języka SQL obsługujący dane relacyjne zawarte w pliku o strukturze niezależnej od systemu operacyjnego. Z racji użytego języka do implementacji (ANSI C) biblioteka jest przenośna (Windows, Linux, Mac OS X) a z racji niewielkich rozmiarów (ok. 300KB) i niewielkich wymagań (np. stos 4KB, sterta 100KB) jest używana powszechnie w urządzeniach przenośnych (Android, iOS, Symbian). Bardzo łatwo łączyć ją z kodem własnej aplikacji w C, a także posiada wiązania do innych języków, m.in. Common Lisp, C#, Haskell, Java, Lua, Objective-C, OCaml, Perl, REBOL, Ruby, Scheme, Smalltalk, Tcl, Visual Basic, JavaScript. SQLite jest wbudowany w języki programowania PHP i Python.

W efekcie, SQLite to najpowszechniej używany silnik baz danych, wykorzystują go takie firmy jak Adobe (AIR, Acrobat Reader, Lightroom), Apple, Mozilla, Google, Skype, Sun (np. zarządzanie usługami w Solarisie), Dropbox, Nokia. Powszechna praktyka osadzania biblioteki w przeglądarkach doprowadziła do powstania specyfikacji WebSimpleDB API w HTML5, co w praktyce oznacza dostęp z JavaScriptu w przeglądarce do SQLowej bazy danych po stronie klienta.

Biblioteka zawiera klienta wiersza poleceń: sqlite3 a niezależne projekty oferują wiele klientów GUI, m.in. SQLite Manager jako rozszerzenie do Firefoksa.

Przydatne łącza
Składnia SQL
    Pragmy
    Funkcje SQL
    Funkcje daty i czasu
    Funkcje agregujące
Shell
Tutorial

Zastosowania

  • Format pliku alternatwyny np. dla XML. Zamiast zapisywać dane w XMLu lub innym wymyślonym przez siebie formacie, a potem pisać parsery które ładują go do struktur danych aplikacji, można użyć przenośnego pliku danych SQLite. Dostęp do niego jest deklaratywny (SQL) a modyfikacje transakcyjne. Twórcy zachęcają: „Think of SQLite not as a replacement for Oracle but as a replacement for fopen().
  • Baza danych w urządzeniach przenośnych.
  • Alternatywa dla RDBMS takich jak MySQL, PostgreSQL czy Oracle. Jest o tyle ciekawa, że projekt na początku może korzystać z SQLite, aby wraz ze wzrostem wymagań można było łatwo przerzucić się na poważniejszy RDBMS. Ale nawet w dużych systemach SQLite przydaje się dla celów demonstracyjnych lub testowych.

Cechy

  • Zawartość bazy danych przetrzymywana jest w jednym pliku (do 2 TB). Baza SQLite jest utrzymywana na dysku przy użyciu B-drzew. Osobne drzewo jest używane dla każdej z tabel i każdego z indeksów. Narzędzie obsługuje łańcuchy lub bloby o rozmiarach idących w gigabajty. Ograniczenia domyślne można w pewnym zakresie zmieniać np. podczas kompilacji źródeł SQLite.
  • Transakcje ACID:
    • Izolacja osiągnięta dzięki zastosowaniu modelu wielu czytelników i jeden pisarz, który na czas zapisu blokuje całą bazę danych (blokada na pliku), co daje wyśmienitą izolację ale kiepską skalowalność. Tym niemniej jeden plik bazy danych może być jednocześnie otwarty przez wiele aplikacji bez obawy zniszczenia zawartości pliku.
    • W implementacji i testach SQLite położono silny nacisk na zachowanie w sytuacjach niedoboru pamięci, błędów I/O, awarii systemu lub zasilania.
  • Brak konfiguracji: nie ma osobnego procesu serwera który trzeba byłoby konfigurować, zatrzymywać, uruchamiać, podejmować działania po awarii systemu. Nie istnieją pliki konfiguracyjne. Utworzenie nowej instancji bazy danych sprowadza się do utworzenia pliku w systemie operacyjnym.
  • Implementuje większość standardu SQL92. Łatwo wymienić czego nie ma:
    • Nie ma RIGHT OUTER JOIN, FULL OUTER JOIN (ale jest LEFT OUTER JOIN),
    • Z poleceń ALTER TABLE mamy jedynie RENAME TABLE i ADD COLUMN, brak np. DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT itp.
    • Nie można zdefiniować triggera FOR EACH STATEMENT, ale można FOR EACH ROW,
    • Perspektywy są tylko do odczytu (ale można zdefiniować triggera który reaguje na polecenia modyfikacji perspektywy).
    • Nie ma systemu uprawnień, dostęp możemy ograniczyć na poziomie całej bazy danych manipulując uprawnieniami systemu plików w systemie operacyjnym – stąd brak poleceń GRANT i REVOKE.
  • Stabilność: korzystając z małego rozmiaru biblioteki i łatwości użycia, każda jej wersja jest bardzo mocno testowana przy użyciu rozbudowanej platformy testowej i dużej ilości danych testowych.

Typy danych

SQLite używa dynamicznego typowania, tzn. typ jest przyporządkowany nie do kolumny, ale do wartości komórki. Oznacza to że do kolumny zadeklarowanej jako REAL można wstawiać np. łańcuch. SQLite podejmie próbę przekonwertowania go na liczbę, ale jeśli się nie uda to bez żadnego błędu wstawi łańcuch (typ kolumny w deklaracji oznacza typ preferowany).

Tak więc mamy następujące typy:

  • NULL
  • INTEGER (1-6 bajtów w zależności od wartości)
  • REAL (8-bajtowy IEEE)
  • TEXT (Unicode)
  • BLOB (tablica bajtów)
  • NUMERIC – typ wirtualny; tak naprawdę wartość tego typu jest konwertowana do INTEGER, REAL, a w ostateczności do TEXT.

Znane z SQLa typy są podmieniane na jeden z powyższych, np. jeśli zadeklarujemy kolumnę jako VARCHAR(50), to zostanie tak naprawdę zadeklarowana jako TEXT, DECIMAL(10,5) jako NUMERIC itp. przy czym SQLite ignoruje ograniczenia na długość, tzn. do kolumny zadeklarowanej jako VARCHAR2(10) możesz spokojnie wrzucać łańcuchy znacznie dłuższe bez obawy ich przycięcia.

Zwróć uwagę na brak typów reprezentujących datę/czas (istnieją funkcje pozwalające przetwarzać daty jako wartości TEXT, REAL lub INTEGER) i wartości logiczne (reprezentowane zwykle przez wartości całkowite 0 i 1).

SQLite reprezentuje liczby zmiennoprzecinkowe binarnie i wykorzystuje arytmetykę binarną (a nie dziesiętną). Ponieważ często skończona reprezentacja dziesiętna ma nieskończoną reprezentację binarną, stąd np. ROUND(9.95, 1) zwraca 9.9 zamiast 10 – bo binarnie 9.95 to coś w rodzaju 9.949999999999999289457264239899814128875732421875.

W domyślnej instalacji SQLite może nie działać prawidłowo porównywanie łańcuchów Unicode ignorujące wielkość liter. Powód jest taki, że implementacja takich porównań i konwersji wielkości liter wymagałaby podwojenia obecnej wielkości implementacji SQLite. Jeśli ci na tym zależy, musisz trochę popracować przy kompilacji, ale szlak jest już przetarty.

Shell

W poniższym zestawieniu polecenia zaczynające się od znaku dolara oznaczają polecenia powłoki Linuksa, pozostałe interaktywnej powłoki SQLite, przy czym polecenia zaczynające się od kropki są interpretowane przez shella, a nie interpreter SQLa.

$ sqlite3 mydbfile
tworzy nową bazę danych (lub otwiera istniejącą) w pliku o nazwie mydbfile i wchodzi do interaktywnej powłoki SQLite.
$ cat polecenia.txt | sqlite3 mydbfile
wykonuje w trybie wsadowym polecenia zawarte w pliku polecenia.txt
<EOF>
(znak końca pliku, np. Ctrl+D pod Linuksem) wyjście z shella
Ctrl+C
przerywa trwające długo polecenie
.help
wyświetla listę dostępnych poleceń shella (nie SQL)
.databases
lista otwartych baz danych
.dump [TABLE_NAME]
zrzuca tabelę (lub całą bazę danych) w formacie SQL
.indices TABLE_NAME
nazwy indeksów na wskazanej tabeli
.read FILE_NAME
wykonuje instrukcje SQL z pliku
.schema TABLE_NAME
wyświetla instrukcje CREATE TABLE i CREATE INDEX dla wskazanej tabeli lub całej bazy danych
.show
wyświetla bieżące wartości dla ustawień zmiennych shella (separator, nagłówki, reprezentacja wartości null, przekierowanie wyjścia, format zrzutu itp.)
.tables [PATTERN]
wyświetla listę nazw tabel o nazwach pasujących do wzorca (albo wszystkich jeśli brak wzorca)
.timer ON/OFF
włącza/wyłącza wyświetlanie informacji o czasie trwania każdego polecenia
.explain
explain delete from tbl1 where two<20;
wyświetla tabelkę wymieniającą instrukcje maszyny wirtualnej które zostałyby użyte do wykonania polecenia gdyby nie było poprzedzone słowem kluczowym EXPLAIN

Ponadto, można wpływać na sposób formatowania wyniku wybierając tryb formatowania poleceniem .mode (np. csv, column, html, insert, list, tabs itp.) i dopieszczając szczegóły poleceniami .separator, .width, oraz .header.

Inne ciekawe wiadomości

  • Kompatybilność wsteczna: każdy plik utworzony przez SQLite 3.x da się otworzyć każdym późniejszym wydaniem SQLite 3.x (obecnie najnowsza wersja: 3.7.4).
  • Informacje o schemacie i tabelach tymczasowych można odczytać z zawartych w każdej bazie tabel SQLITE_MASTER i SQLITE_TEMP_MASTER.
  • Polecenie VACUUM reorganizuje bazę danych dając w wyniku najmniejszy plik bazy danych z możliwych (to może potrwać - pod Linuksem ok. 1sek na każde 2MB początkowego rozmiaru pliku, i potrzebować nawet 2 razy więcej miejsca tymczasowej przestrzeni na dysku niż początkowy rozmiar pliku).
  • Domyślnie SQLite zatwierdza transakcję po każdym poleceniu INSERT, co pozwala wykonać zaledwie kilkadziesiąt takich poleceń na sekundę. Otoczenie grupy poleceń INSERT blokiem BEGIN...COMMIT pozwala upchać wszystko w jednej transakcji, co na średnio szybkiej maszynie pozwala osiągnąć prędkość 50000 rekordów na sekundę i więcej.
  • Póki co nie istnieją narzędzia będące w stanie odzyskać dane po zatwierdzonym wykonaniu instrukcji DELETE.
  • Z uwagi na realizację blokady przy zapisie (sprowadzającej się do blokady pliku) mamy praktyczne ograniczenia:
    • Jeśli z bazy danych ma korzystać wiele procesów naraz, nie umieszczajmy pliku bazy w NFSie (blokada zapisu do pliku nie działa poprawnie w wielu implementacjach NFS).
    • Współdzielenie pliku bazy danych pomiędzy maszynami Windows może powodować nieoczekiwane problemy.
  • Automatycznie inkrementowaną kolumnę definiujemy tak: CREATE TABLE T ( a INTEGER PRIMARY KEY AUTOINCREMENT );
Share and Enjoy:
  • del.icio.us
  • Facebook
  • Google Bookmarks
  • Śledzik
  • Blip
  • Blogger.com
  • Gadu-Gadu Live
  • LinkedIn
  • MySpace
  • Wykop

Zostaw komentarz

XHTML: Możesz użyć następujących tagów: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>