1. Wstęp
Celem tego artykułu jest przedstawienie coraz większego konkurenta MySQL a mianowicie SQLite. Postaram się w nim przybliżyć zarówno plusy jak i minusy tego rozwiązania, a także sposób implementacji w języku PHP. No to zaczynamy [; .
(wersja poprawiona została dołączona jako plik .PSD)
2. Co to jest i jak to działa ?
SQLite to biblioteka napisana w języku C#, implementująca silnik SQL. Została stworzona przez Richard'a Hipp'a i jest dostępna na licencji public domain*.
Biblioteka ta daje możliwość używania bazy danych bez konieczności uruchamiana oddzielnego procesu jak to jest np. w przypadku MySQL. Zawartość bazy danych przetrzymywana jest w jednym pliku binarnym(do 2 TB wielkości). SQLite jest utrzymywany na dysku przy użyciu drzew binarnych**.Osobne drzewo jest używane dla każdej z tabel i każdego z indeksów.
SQLite rozwiązuje odwieczny problem programistów przechowujących dane w plikach płaskich, a mianowicie blokowanie i dostęp współbieżny.
3. Plusy i minusy
Głównym plusem tej bazy danych jest wydajność(szczególnie przy wykonywaniu zapytań typu INSERT i SELECT), a także wieloplatformowość(baza działa na zasadzie rozszerzenia języka PHP). Niestety nie jest to rozwiązanie wolne od wad. Podczas procesu dopisywania nowych danych SQLite musi zablokować cały plik, aż do czasu zakończenia operacji. Zatem rozwiązanie to nie jest wydajne w sytuacjach gdy dane podlegają ciągłym zmianom. Drugim poważnym minusem jest brak pojęcia praw dostępu do bazy danych(znanym chociażby z MySQL'a), przez co nie możemy stworzyć "bezpiecznej" tabeli do której dostęp mieliby tylko uprawnieni użytkownicy. W skrócie oznacza to, że musimy sami stworzyć system kontroli dostępu, poprzez nadawanie praw do zapisu i odczytu odpowiednim plikom. Rozwiązanie takie nie jest wygodne.
Podsumowując SQLite nie nadaje się do zastosowań na witrynach gdzie dane podlegają ciągłym zmianom, a także tam gdzie jest wymagany system kontroli dostępu. Jednak na małych i średnich stronach rozwiązanie to może okazać się naprawdę wydajne. W końcu do stworzenia tabeli z kilkunastoma rekordami nie potrzeba całego "kombajna" jakim jest niewątpliwie MySQL.
4. Skąd to wziąć i jak zainstalować ?
Windows :
Rozszerzenie SQLite jest standardowo dostarczane z pakietem PHP5. W pliku php.ini***
wystarczy usunąć średnik sprzed extension=php_sqlite.dll( w systemach Windows), aby uaktywnić SQLite.
W przypadku PHP4 obsługa SQLite jest zaimplementowana w wersjach od 4.3.x wzwyż.
Samą bibliotekę jednak trzeba ściągnąć i zainstalować oddzielnie. Pobieramy ją z http://snaps.php.net/win32/PECL_STABLE/php_sqlite.dll a następnie kopiujemy do folderu z rozszerzeniami języka PHP. Pozostało nam już tylko w sekcji Extensions pliku php.ini dodać extension=php_sqlite.dll.
Linuks :
W PHP5 sytuacja jest podobna do systemów Windows. Rozszerzenie powinno być dostarczone razem z pakietem PHP oznaczone numerkiem 5. Zatem w pliku php.ini szukamy slowa kluczowego php_sqlite.so, dalej postepujemy tak jak w przypadku Windows'a.
Jeżeli posiadamy PHP w wersji 4 mamy do wyboru dwie opcje instalacji
Instalacja z pakietu :
Instalacja SQLite z pakietu ogranicza się tylko do wywołania systemu zarządzania pakietami z odpowiednimi argumentami. W tym punkcie opisze sposób instalacji pakietu SQLite za pomocą najbardziej znanych systemów zarządzania pakietami, a mianowicie APT i Yum.
Odpowiednio dla zainstalowanego w naszym distrze systemu zarządzania pakietami inicjujemy powłoke systemową i wpisujemy :
APT:
apt-get install php4-sqlite
Yum:
yum install php4-sqlite
Nastepnie do php.ini dodajemy :
[sqlite]
extension="sqlite.so"
Instalacja ze źródeł :
Ściągamy źródła z http://pecl.php.net/package/SQLite .
Po udanym procesie pobierania źródeł rozpakowujemy je i kompilujemy w folderze gdzie przechowujemy resztę rozszerzeń.
Na koniec dodajemy do pliku php.ini :
[sqlite]
extension="sqlite.so"
Oto przykład :
wget http://pecl.php.net/get/SQLite-1.0.tgz
tar xzf SQLite-1.0.tgz
cd sqlite
export PHP_PREFIX="/usr"
$PHP_PREFIX/bin/phpize
./configure
make
make install
5. Typy danych
SQLite pod tym względem jest bardzo ubogi. Wszystko w nim jest łańcuchem znaków. Dla przykładu możemy zdefiniować kolumne typu INT, jednak po wprowadzeniu do tej kolumny ciągu znaków SQLite nie zwróci żadnego błędu. Typ kolumny(nakładany podczas jej tworzenia) ma znaczenie tylko przy sortowaniu np. SQLite musi wiedzieć co będzie pierwsze 3 czy 6 ? Drugim ważnym powodem nakładania typów danych na kolumny jest przejrzystość tabeli( zawsze lepiej wiedzieć gdzie co jest [; ).
6. Implementacja
W poprzednich punktach poznaliśmy sposób instalacji, zasade działania, a także plusy i minusy SQLite. Czas poznać w praktyce moc SQLite.
6.1 Podstawy
Całość połączenia z bazą danych ogranicza się do podania scieżki dostępu do owej bazy.
Odbywa się to za pomocą funkcji sqlite_open()
Kod:
Powyższy kod otworzy plik bazy danych znajdujący się w /sciezka_do_bazy_danych/books.db, lub w razie próby otwarcia pliku bazy danych który nie istnieje stworzy nowy plik books.db.Po więcej informacji nt. funkcji sqlite_open(); odsyłam do manual'a : http://pl.php.net/manual/pl/function.sqlite-open.php
Po "połączeniu" z bazą danych nie musimy wybierać tabeli(jak to ma miejsce np. w MySQL), spowodowane jest to wcześniej wspomnianym brakiem systemu kontroli dostępu. Możemy więc od razu wysłać zapytanie do silnika SQL.
Kod:
Połączenie z bazą danych zamykamy funkcją sqlite_close();
Całość może wyglądać np. tak :
Kod:
sqlite_query($db,"CREATE TABLE books(autor VARCHAR(100), tytul VARCHAR(100)) ");
sqlite_close($db);
6.1.1 Wstawianie danych
Wstawianie danych odbywa się za pomocą SQL'owego INSERT'a
Kod:
Aby uniknąć problemu z cudzysłowami i innymi znakami specjalnymi filtrujemy dane funkcją sqlite_escape_string();
Całość dotychczasowego skryptu mogłaby wyglądać tak :
Kod:
sqlite_query($uchwyt_db,"INSERT INTO books VALUES('sqlite_escape_string ($autor)',' sqlite_escape_string($tytul) ') ");
sqlite_close($db);
6.1.2 Odczytywanie danych
Aby odczytać dane z SQLite należy wywołać funkcje sqlite_query() z instrukcją SELECT, a następnie przejść kolejno przez zwrócone przez nią wyniki. Obrazuje to poniższy przykład :
Kod:
while($row = sqlite_fetch_array($query)
{
// Operacje na $row
}
Osoby mające styczność z MySQL zauważyły na pewno podobny sposób implementacji w obu przypadkach.
W sytuacjach gdy dużą role będzie odgrywać szybkość działania lepiej będzie użyć funkcji sqlite_array_query(); , która w tym samym kroku odczyta dane i umieści je w tablicy.
Kod:
foreach($table as $value)
{
// Operacje na $value
}
Na szczególną uwagę przy odczycie danych zasługuje drugi parametr funkcji sqlite_fetch_array().
Standardowo funkcja sqlite_fetch_array() zwraca tablice zawierającą zarówno klucze liczbowe jak i asocjacyjne. Standardowo takie "zachowanie" tej funkcji jest na rękę programiście który zarówno może odwoływać się do danych w tablicy za pomocą kluczy liczbowych jak i asocjacyjnych. Problem jednak może powstać w przypadku zagnieżdżenia w pętli while(), pętli foreach() podczas odczytu danych jak to widać na przykładzie :
Kod:
while($row = sqlite_fetch_array($query)
{
foreach($row as $value)
{
echo $value .'<br>';
}
}
Dane z każdej kolumny zostaną wyświetlone dwukrotnie . Aby temu zapobiec do funkcji sqlite_fetch_array() trzeba przekazywać dodatkowy argument :
SQLITE_NUM - przechowywane będą tylko klucze liczbowe
SQLITE_ASSOC - przechowywane będą tylko klucze asocjacyjne
SQLITE_BOTH - przechowywane będą obydwa typy kluczy
6.2 Indeksy
Jak wiadomo stosowanie indeksów jest chyba najprostszą drogą do zwiększenia wydajności skryptu, pod kątem wyszukiwania i sortowania danych pochądzących z bazy danych. Większość osób mająca styczność z inną relacyjną bazą danych np. MySQL'em zapewne wie co to są indeksy.Więc następne pare linijek może spokojnie ominąć.
Podczas przeszukiwania tabeli nie zawierającej żadnych indeksów SQLite musi przeszukiwać tabele wiersz po wierszu porównując wartości. W sytuacji gdy "polom wyszukiwania" nadamy indeksy, SQLite odwoła się do właśnie takiego specjalnie stworzonego pola, dzięki któremu odnalezienie lokalizacji interesujących nas danych będzie znacznie szybsze.
W przypadkach gdy z góry wiadomo, że szuakanie będzie odbywało się pod kątem danych zawartych w konkretnych polach. Możemy je oznaczyć jako unikatowe. Spowoduje to automatyczne indeksowanie tych pól.
Kod:
Nadawanie indeksów na już istniejące pola odbywa się poprzez instrukcje CREATE INDEX.
Pokazuje to poniższy przykład :
Kod:
6.3 Klucze główne
Kolejnym szybkim sposobem na zwiększenie wydajności skryptu jest zastosowanie kluczy głównych. Ponownie osoby mające styczność z tym zagadnieniem mogą opuścić kilka poniższych linii.
Klucz główny jest unikatowym identyfiaktorem wiersza. Pole pełniące role klucza głównego musi zawierać wartości całkowito-liczbowe . Nie musimy jednak sami nadawać wartości tym polom, ponieważ zrobi to za nas silnik SQL, np. pierwszemu wierszowi SQLite przyporządkuje wartość 1, drugiemu 2 itd ..
Przejdźmy zatem do praktyki. Aby stworzyć owe identyfikatory pól musimy utworzyć kolumne typu INTEGER PRIMARY KEY.
Kod:
Powyższy przykład utworzy tabele z takimi kolumnami :
bookid || autor || tytul
W której to wszystkie pola kolumny bookid będą spełniać role kluczy głównych.
Aby SQLite wypełnił automatycznie pole oznaczone jako klucz główny podczas wstawiania danych trzeba jako wartość podać NULL. Na przykład :
Kod:
Jeżeli jednak sami chcemy nadać mu wartość po prostu wprowadzamy liczbe całkowitą na to miejsce.
Przydatna przy okazji kluczy głównych może okazać się funkcja sqlite_insert_rowid() zwracająca wartość klucza głównego ostatnio wstawianego wiersza.
6.4 Transakcje
Na szczególną uwagę w SQLite zasługują transakcje, które pozwalają zwiększyć wydajność skryptu. Za każdym razem gdy wywołamy funkcje sqlite_query(), sqlite_connect() etc .. SQLite musi wykonać pare zadań(blokowanie dostępu do pliku , odczyt pliku , etc ... ) . Zamiast więc za każdym razem wywoływać funkcje, można zgrupować zapytania w jedną transakcje. Funkcja zostanie wywołana tylko raz, a co za tym idzie ograniczymy robote
SQLite'owi
Użycie transakcji :
Kod:
for((int)$i = 0; $i >= 100; $i++)
{
$sql .= "INSERT INTO books VALUES('Sienkiewicz','Krzyzacy')";
}
$sql .= 'COMMIT;';
sqlite_query($uchwyt_db , $sql);
Przykład bardziej z życia :
Kod:
"BEGIN;
CREATE TABLE books(bookid INTEGER PRIMARY KEY , autor VARCHAR(100) , tytul VARCHAR(100);
INSERT INTO books VALUES(NULL,'Sienkiewicz','Krzyzacy');
INSERT INTO books VALUES(NULL,'Kaminski','Kamienie na szaniec');
INSERT INTO books VALUES(NULL,'Prus','Lalka') ;
COMMIT; "
);
6.5 Przechowywanie tabeli w pamięci operacyjnej
SQLite posiada także przydatny mechanizm przechowywania tabeli w pamięci RAM .Z pierwszych lekcji informatyki w szkole zapewne wiecie, że pamięć RAM nie jest pamięcią stałą. Dlatego też nie można odwołać się do zapytania wykonanego np. przy poprzednim odświerzeniu strony. Takie działanie jest nieocenione w aplikacjach, które na początku ładują znaczne ilosći danych, a potem wykonują wiele zapytań.
Włączenie tego mechanizmu ogranicza się jedynie do przekazania argumentu :memory: funkcji sqlite_open() czyli np.
Kod:
Od tej chwili wszystkie wyniki zapytań będą zapisywane w pamięci operacyjnej.
Przykład :
Kod:
sqlite_query("CREATE TABLE books(autor VARCHAR(100) , tytul VARCHAR(100))" );
6.6 Tworzenie własnych funkcji SQLite
Kolejnym przydatnym mechanizmem w SQLite jest możliwość tworzenia własnych funkcji rozszerzających możliwości bazy danych .
Oto przykład :
Kod:
{
$string .= 'a';
return $string ;
}
$db = sqlite_open('/sciezka_do_bazy_danych/books.db');
sqlite_create_function($db, 'add', 'add_a');
$sql = sqlite_query($db,"SELECT add(autor) AS wynik FROM books ");
$rows = sqlite_fetch_array($sql,SQLITE_ASSOC);
echo $rows["wynik"];
sqlite_close($db);
Jak widać na powżyszym przykładzie stworzenie SQLite'owej funkcji ogranicza się tak naprawde do użycia funkcji sqlite_query_function(), w której to jako drugi argument podajemy nazwe wcześniej stworzonej PeHaPowej funkcji, natomiast jako pierwszy nazwe pod jaką owa PeHaPowa funkcja będzie rozpoznywana przez SQL.
6.7 Obsługa błędów
Tak jak w przypadku innych baz danych, także SQLite posiada swój własny "system" obsługi błędów. W omawianym przeze mnie programowaniu strukturalnym, musimy sprawdzać wartości zwracane przez funkcje, a następnie w zależności od tych wartości logicznych generować błąd. Nie musimy się jednak głowić definiowaniem informacjami o tych błędów, zrobi to za nas SQLite.
W przypadku niepowodzenia podczas wykonania jakiejś czynności SQLite przypisze numer błędu funkcji sqlite_last_error(), która to zwraca numer ostatnio wygenerowanego przez SQLite błędu.Oczywiście sam numer błędu nam, zwykłym śmiertelnikom nic nie będzie mówił.Zatem musimy go przekształcić na coś bardziej zrozumiałego. Do tego celu służy funkcja sqlite_error_string(), przekształcająca ową niezrozumiałą liczbe na w miare wyczerpującą informacje o błędzie.
Przykład :
Kod:
{
// dalsze operacje
}
else
die( sqlite_error_string( sqlite_last_error($uchwyt_db) ) );
albo :
Kod:
Pewną odmienność stanowi tutaj funkcja sqlite_open() , która to w przypadku niepowodzenia informacje o błędzie przypisze zmiennej, którą podaliśmy jako trzeci argument funkcji .
Np.
Kod:
Warto zapoznać się też z drugim argumentem funkcji sqlite_open(), który to poprzez wartość ósemkową określa tryb w jakim otworzymy plik bazy danych.
7. Tytułem zakończenia
Podsumowując SQLite pomimo swojej nazwy, która może sugerować pewną jej "ułomność", obsługuje prawie wszystkie aspekty języka SQL, a także rozszerza go o możliwość definiowania własnych funkcji. Jest to baza danych, która świetnie będzie się spisywać na naszym np. domowym blogu bądź niedużym forum.
Jeżeli jednak po przeczytaniu tego artykułu macie wątpliwości co do wydajności SQLite, a także jak wypada na tle innych baz danych w kwesti wydajności, radze zapoznać się z :
http://www.maciaszek.pl/phpcon/download/sqlite.pdf
8. Pozdrowienia
Juice - za ocene merytoryczną artykułu [;
Fuma - dla anonimowego przeglądacza haxite
Emdek - za to że zawsze można z nim pogawędzić o PHP gdy na IRCu cicho [;
Crook - za głowę pełną świetnych pomysłów
Conel - za obrone perla [;
Adex - za to, że zawsze można z nim ponabijać się z komara [; ...
Komar - Heh, nie darował by mi, gdyby go tu nie było [;
oraz reszty ludzi z #candys-team @ Polnet
Skorowidz nazw :
* Licencja public domain - ogół tekstów, zdjęć, muzyki, dzieł sztuki, oprogramowania itp.,
które na skutek decyzji twórcy, braku spadkobierców lub upływu odpowiedniego czasu stały się dostępne do dowolnych zastosowań bez ograniczeń wynikających z przepisów prawa autorskiego.
** Drzewa Binarne - jeden z rodzajów drzewa (struktury danych), w którym liczba synów każdego wierzchołka wynosi nie więcej niż dwa. Wyróżnia się wtedy lewego syna i prawego syna danego wierzchołka.
*** Php.ini - Plik konfiguracyjny języka PHP.
Artykuł napisany na podstawie :
http://www.sqlite.org/
http://pl.php.net/manual/pl/ref.sqlite.php
http://pl.wikipedia.org/wiki/
http://www.filipdewaard.com/21_SQLite_inmemory_databases.html
http://developer.mimer.com/documentation/html_82/Mimer_SQL_Refer ence_Manual/Intro_SQL_Stds3.html
http://www.maciaszek.pl/phpcon/download/sqlite.pdf
Artykuł opublikowany na licencji : http://creativecommons.org/licenses/by-sa/2.5/
by y00da.
Autor: Master_Yoda
Licencja: Creative Commons - bez utworów zależnych