SQLite -baza danych w jednym pliku

Istotne cechy bazy SQLite

• Obsługa transakcji zgodnych ze standardem ACID.
• Brak konfiguracji – nic nie instalujemy ani nie konfigurujemy.
• Implementacja w większości zgodna ze standardem SQL92. – Wyjątki poniżej.
• Cała baza danych przechowywana w jednym pliku, który może być przenoszony między różnymi platformami.
• Obsługa baz danych o wielkości rzędu terabajtów oraz gigabajtowych stringów i BLOB-ów.
• Dla większości najpopularniejszych operacji szybsza od popularnych baz danych opartych na modelu klient-serwer.
• Napisana w ANSI-C, a używany w powiązaniu z innymi językami – szczególnie mocny związek z językiem skryptowym Tcl.
• Działająca na wielu platformach: Linux, MacOSX, OS/2, Win32, Win64 i WinCE.
• Kod źródłowy rozpowszechniany na licencji Public Domain.

Zgodność z SQL

SQLite posiada standardową składnię języka SQL. Istnieją jednak pewne rozbieżności.
Cechy standardu SQL-92, niezaimplementowane w SQLite:

• FOREIGN KEY – klucz zewnętrzny, klucz obcy.
• Wyzwalacze – pominięte zostały takie właściwości triggerów jak: FOR EACH STATEMENT (wszystkie wyzwalacze muszą być FOR EACH ROW) i INSTEAD OF na tabelach (INSTEAD OF możliwy tylko na widokach).
• SQLite wspiera tylko dwa warianty polecenia ALTER TABLE: RENAME TABLE i ADD COLUMN. Pozostałe zostały pominięte.
• Zagnieżdżone transakcje – obecnie możliwe są tylko pojedyncze transakcje.
• Operacje łączenia prawostronnego (RIGHT OUTER JOIN) i pełnego (FULL OUTER JOIN). Zaimplementowano jedynie łączenia lewostronne (LEFT OUTER JOIN).
• Operacje DELETE, INSERT i UPDATE na widokach (VIEWS). Widoki są tylko do odczytu.
• Polecenia GRANT i REVOKE – SQLite zapisuje i odczytuje dane bezpośrednio z pliku, więc prawa dostępu
  nadawane są dla pliku z poziomu systemu operacyjnego.

Podstawowe różnice SQLite w porównaniu z innymi bazami danych

Brak konfiguracji

SQLite nie wymaga instalowania, wystarczy umieścić w odpowiednim miejscu na dysku plik biblioteki. Nie ma procesu serwera, który musiałby być uruchamiany, zatrzymywany czy konfigurowany. Nie wymaga administracji – nie określa się praw dostępu dla użytkowników. SQLite nie używa także plików konfiguracyjnych. Żadne działania nie są potrzebne dla odzyskania danych po awarii systemu.

System bezserwerowy

W większości przypadków silniki baz danych implementowane są jako
oddzielne procesy serwera. Programy chcąc uzyskać dostęp do danych komunikują się z serwerem używając różnego rodzaju komunikacji międzyprocesowej, wysyłając żądanie do serwera i otrzymując w odpowiedzi wyniki.
SQLite nie działa w ten sposób. Proces, który chce uzyskać dostęp do bazy czyta bezpośrednio z pliku bazy na dysku. Nie istnieje pośredni proces serwera. Oczywiście istnieją zarówno zalety jak i wady takiego rozwiązania. Główna zaleta braku procesu serwera, to brak potrzeby instalowania, konfiguracji, zarządzania, inicjalizacji. Każdy program, który ma dostęp do plików dyskowych, ma możliwość dostępu do bazy SQLite.
Z drugiej strony silniki bazodanowe używające serwera mogą dostarczać lepszej ochrony przed nieautoryzowanym dostępem do bazy ze strony aplikacji klienckich. Większość silników bazodanowych oparta jest na architekturze klient-serwer. Z pośród bezserwerowych systemów baz danych, tylko SQLite pozwala na jednoczesny dostęp do tych samych danych w tym samym czasie kilku różnym użytkownikom.

Pojedynczy plik z bazą

Baza SQLite jest zwykłym pojedynczym plikiem, który może znajdować się w dowolnym miejscu na dysku. Jeżeli SQLite może przeczytać ten plik to może przeczytać każdą informacje zapisaną w bazie, a jeżeli plik i katalog, w którym się znajduje mają ustawione prawa do zapisu wtedy SQLite może zmienić każdą daną w bazie. Plik z bazą można łatwo kopiować na nośniki przenośne oraz przesyłać e-maile’m.
Inne silniki bazodanowe z reguły zapisują dane w wielu plikach. Często te pliki muszą znajdować się w standardowych lokalizacjach, aby serwer bazy danych miał do nich dostęp. Sprawia to, że dane są lepiej zabezpieczone, ale jednocześnie istnieje trudniejszy dostęp do nich oraz mogą pojawić się problemy z przenoszeniem bazy.

Stabilny i działający na różnych platformach plik bazy

Plik z bazą może być przenoszony bez problemu z jednej platformy sprzętowej na inną, nie ma znaczenia czy jest to architektura 32 czy 64 bitowa. Wszystkie maszyny używają tego samego formatu pliku, w którym przechowywana jest baza. SQLite działa na platformach z rodziny Linux, MacOSX, OS/2, Win32, Win64 i WinCE.

Odmienne od innych baz określanie typów danych tzw. manifest typing

Większość SQL-owych baz danych używa typów statycznych. Typy danych są związane z każdą kolumną w tabeli i tylko dane takiego typu mogą być składowane w określonej kolumnie.
SQLite rozluźnia nieco te restrykcje. Typ danych zależy do wartości zapisanej w kolumnie, a nie do kolumny, w której ta wartość jest składowana. Umożliwia to wstawianie wartości dowolnego typu w dowolnej kolumnie bez względu na wcześniej zadeklarowany typ owej kolumny. Jest jednak kilka wyjątków od tej reguły: kolumna określona jako INTEGER PRIMARY KEY może przechowywać dane tylko typu INTEGER. Ze względu na to, że większość innych baz SQL-owych używa statycznego określania typów, niektórzy użytkownicy odbierają jako błąd sposób określania typów w SQLite. Jednak autorzy SQLite twierdzą, że jest to cecha świadomie zaprojektowana, która w praktyce udowadnia, że SQLite jest bardziej niezawodny i prostszy w użytkowaniu, szczególnie wtedy, gdy używany jest w połączeniu z takimi językami jak Tcl czy Pyton.

Rekordy zmiennej długości

Większość znanych baz danych alokuje zmienną ilość przestrzeni dyskowej dla każdego wiersza tabeli. Jeżeli kolumna jest typu np. VARCHAR(100), wtedy silnik bazodanowy zaalokuje 100 bajtów przestrzeni dyskowej bez względu na to jak duża ilość informacji jest aktualnie przechowywana w danej kolumnie. SQLite natomiast używa tylko wielkości przestrzeni dyskowej aktualnie potrzebnej do składowania informacji zawartej w danym wierszu. Jeśli przechowywany jest jeden znak w kolumnie VARCHAR(100) wtedy tylko jeden bajt przestrzeni dyskowej jest zajęty. Użycie rekordów o zmiennej długości powoduje zmniejszenie wielkości bazy, pozwala także na opisane we wcześniejszym punkcie stosowanie różnych typów danych w pojedynczej kolumnie.

Rozszerzenia języka SQL

SQLite dostarcza kilku usprawnień do języka SQL, których nie znajdziemy
w innych systemach bazodanowych. SQLite dostarcza członów składniowych takich jak REPLACE i ON_CONFLICT, które dają kontrolę nad rozwiązywaniem wymuszonych konfliktów. SQLite wspiera także komendy ATTACH i DETACH, które pozwalają na użycie kilku niezależnych baz danych w jednym zapytaniu. SQLite definiuje również programowy interfejs (API), który pozwala użytkownikom na dodawanie nowych SQLowych funkcji.

Typy danych w SQLite

Klasy składowania

W SQLite 2.8 wszystkie wartości zarówno tekstowe jak i liczbowe składowane były w bazie jako tekst ASCII. SQLite 3.0 wprowadza możliwość składowania liczb całkowitych (INTEGER) i zmiennoprzecinkowych (REAL) w bardziej kompaktowej formie oraz zdolność przechowywania obiektów binarnych (BLOB-ów).
Każda z wartości przechowywanych w bazie należy do jednej z poniższych klas składowania:

Wartość znajdująca się w określonej kolumnie może być innego typu niż zdefiniowany przy tworzeniu tabeli typ kolumny. Typ kolumny ma znaczenie przy porównywaniu i sortowaniu danych, bo właśnie na podstawie typu kolumny wybierana jest metoda porównywania czy sortowania.

Automatyczne przydzielanie klas składowania przez SQLite

Wartości przechowywane są jako typ:

• TEXT – jeżeli w zapytaniu SQL-a ujęte są w pojedynczy lub podwójny cudzysłów
• INTEGER – jeżeli w zapytaniu występują jako liczby nieujęte w cudzysłów i nieposiadające separatora lub wykładnika
• REAL – jeżeli są liczbą nieujętą w cudzysłów oraz posiadają separator w postaci kropki lub wykładnik
• BLOB – jeżeli zostały dostarczone za pomocą funkcji interfejsu API sqlite3_bind_blob()

Typy kolumn

W SQLite typy danych związane są z wartością znajdującą się w kolumnie, a nie z kolumną (odwrotnie niż w innych systemach bazodanowych). Każda kolumna, z wyjątkiem kolumny określonej jako INTEGER PRIMARY KEY, może przechowywać dane dowolnego typu. Kolumna określona jako klucz główny musi zawierać 32-bitową liczbę całkowitą. Wszelkie próby wstawienia wartości innego typu do takiej kolumny spowodują błąd. Dla zwiększenia kompatybilności między bazą SQLite, a innymi bazami danych zaleca się składowanie danych odpowiedniego typu dopasowanego do typu kolumny.
SQLite definiuje następujące typy kolumn:

• TEXT
• NUMERIC
• INTEGER
• REAL
• NONE

Konwersja typów

W kolumnie określonej jako TEXT zgodnie z zaleceniami powinno się przechowywać wartości odpowiadające klasom składowania: TEXT, BLOB i NULL. W kolumnie NUMERIC można przechowywać dane wszystkich pięciu klas składowania, przy czym, gdy próbujemy wstawić tam wartość tekstową, to SQLite przed zapisaniem jej, próbuje zamienić ją na typ INTEGER lub REAL. Jest to tzw. konwersja typów. Jeśli konwersja się powiedzie to wartość zapisywana jest w klasie składowania INTEGER lub REAL. Jeśli natomiast nie uda się przeprowadzić konwersji to wartość zapisywana jest jako TEXT. Podobne zmiany typów zachodzą przy próbie wstawienia „stringa” do kolumn typu INTEGER i REAL. Dla kompatybilności z innymi bazami danych, SQLite dokonuje takŜe konwersji nazw typów występujących w innych bazach na nazwy typów z SQLite’a. Jeżeli przy tworzeniu tabeli podamy typ niewystępujący w SQLite, a zdefiniowany w innych silnikach bazodanowych to możemy mieć pewność, Że nie spowoduje to błędu.
Konwersje nazw typów:

• INT zamieniany jest na INTEGER
• CHAR i VARCHAR zamieniane są na TEXT
• FLOAT i DOUBLE zamieniane są na REAL
• Jeśli kolumna nie posiada typu lub posiada typ BLOB to zamieniany jest on na NONE
• Pozostałe typy zamieniane są na NUMERIC

Porównywanie i sortowanie danych w zależności od typu

Przy porównywaniu i sortowaniu danych, kolumny lub wyrażenia mogą należeć do jednej z dwóch grup, mogą być numeryczne lub tekstowe. Typ kolumny określa, do której grupy zostanie zaliczona kolumna i jaka metoda porównywania lub sortowania będzie użyta. Jeżeli dane są tekstowe to do porównywania ich używana jest standardowa funkcja języka C memcmp() lub strcmp(). Funkcje te porównują dwa łańcuchy znaków bajt po bajcie i zwracają pierwszą napotkaną różnice.

Optymalizacja bazy SQLite

Optymalizacja to działania, których zadaniem jest poprawa wydajności systemu bazodanowego, przede wszystkim przez zwiększenie szybkości działania i zmniejszenie wykorzystania zasobów komputera. W przypadku SQLite klucz do poprawy wydajności leży przed wszystkim w ograniczeniu i optymalizowaniu operacji zapisu i odczytu danych na dysku.
Na optymalizację bazy SQLite wpływ mają następujące elementy:

Odpowiednie ustawienie stałych konfiguracyjnych PRAGMA

PRAGMA cache_size

Określa maksymalną liczbę stron btree trzymaną w pamięci podręcznej, dostęp do tych stron jest znacznie szybszy, ponieważ nie wymaga operacji dyskowych. Można zwiększyć wartość cache_size, przez co system będzie mógł przechowywać większą liczbę stron w pamięci. Nie będzie to miało wpływu na marnotrawienie zasobów serwera, ponieważ pamięć alokowana jest dopiero w trakcie wykonywania zapytania i tylko w potrzebnej ilości.

PRAGMA synchronous

Ustawienie tej stałej na OFF spowoduje, że SQLite nie będzie czekał, aż operacja zapisu danych na dysk zostanie wykonana w całości, jedynie zleci zapis i zajmie się kolejnymi operacjami, spowoduje to szybsze działanie bazy, ale zmniejszy kontrolę integralności danych.

PRAGMA count_changes

Gdy jest włączone (ON), SQLite zlicza rekordy zmodyfikowane przez operacje INSERT, UPDATE, DELETE. Jeżeli niepotrzebne są nam te dane to możemy wyłączyć tę opcję, co nieznacznie przyspieszy wykonywanie tych operacji.

PRAGMA temp_store

Określa sposób przechowywania plików tymczasowych, możliwe są trzy ustawienia:

• DEFAULT(0),
• FILE(1),
• MEMORY(2).

Użycie pamięci (2) spowoduje znaczne przyspieszenie wykonywania operacji.

Stosowanie transakcji

SQLite używa transakcji domyślnie dla każdego zapytania, ma to na celu zwiększenie niezawodności. Jest to jednak bardzo czasochłonne, ponieważ wymaga każdorazowo otwarcia pliku, zapisania danych oraz zamknięcia pliku dla pojedynczego zapytania. Można tego uniknąć grupując szereg zapytań w transakcje korzystając z poleceń BEGIN TRANSACTION i END
TRANSACTION (COMMIT).

Używanie indeksów

Indeksy utrzymują kolejność sortowania kolumny lub zbioru kolumn w tabeli. Umożliwia to pobranie wartości bez potrzeby skanowania całej tabeli, co znacznie przyspiesza operacje wyszukiwania danych. Wstawianie danych do tabeli z indeksami jest nieco wolniejsze niż w przypadku tabeli bez indeksów.

Porządkowanie bazy danych

Dodawanie i usuwanie rekordów powoduje fragmentację bazy i przez to zwiększenie rozmiaru pliku bazy. Najprostszym sposobem poradzenia sobie z tym problemem jest użycie polecenia VACUUM, które uporządkuje naszą bazę.

Bazy działające w pamięci

Dostęp do pamięci operacyjnej jest dużo szybszy niż dostęp do pliku dyskowego, dlatego, jeżeli zależy nam na szybkości warto korzystać z baz tworzonych w pamięci operacyjnej. Bazę taką tworzymy tak samo jak zwykłą bazę w pliku, z tym, że zamiast nazwy pliku wpisujemy MEMORY. Baza taka niestety jest bazą tymczasową.

Ograniczenia SQLite

• Maksymalna długość łańcucha znaków (string) lub obiektu binarnego (BLOB) obsługiwana obecnie przez SQLite wynosi 2³¹-1 bajtów (domyślnie długość ta określona jest na 1 miliard bajtów, ale można ją zwiększyć wpisując przy kompilacji biblioteki SQLite inną wartość do stałej SQLITE_MAX_LENGTH).
  Parametr SQLITE_MAX_LENGTH określa jednocześnie maksymalną liczbę bajtów w wierszu.
• Maksymalna liczba kolumn w jednej bazie obsługiwana przez SQLite wynosi 32676 (domyślnie w SQLITE_MAX_COLUMN wpisane jest 2000, zmiana tej liczby możliwa przy kompilacji biblioteki).
• Maksymalna liczba złączonych tabel to 64. Limit ten wynika z architektury SQLite, mianowicie generator kodu w optymalizatorze zapytań używa bitmap z jednym bitem na złączenie tabeli.
• Maksymalna liczba połączonych baz tzn. baz, na których operacje wykonywane są w jednym połączeniu wynosi 30 na maszynach 32 bitowych i 62 na 64 bitowych (domyślnie ustawiona w stałej SQLITE_MAX_ATTACHED wynosi 10).

W artykule wykorzystano pracę magisterską Damiana Dziechciarza, „SQLITE JAKO ALTERNATYWA DLA SERWEROWYCH SYSTEMÓW BAZODANOWYCH”