Bazy danych SQLite w PHP

Autor: Tomasz Jędrzejewski
Data publikacji: 12.11.2003, 09:16 | Ostatnia modyfikacja: 29.10.2006, 16:21

Z bazą danych SQLite, oraz jej obsługą w PHP warto zapoznać się z dwóch powodów. Pierwszy - nie wymaga instalacji ŻADNYCH dodatkowych programów; Drugi - od PHP 5 zastąpi ona MySQL'a jako wbudowany na stałe moduł obsługi baz danych.

Spis treści

Z pewnością wielu chciałoby przetestować możliwości innych systemów baz danych. Na ich drodze piętrzy się jednak sporo trudności: brak dostępu do odpowiednich materiałów, mała popularność danej bazy, a wreszcie trudności ze zwykle skomplikowaną instalacją odpowiednich programów zewnętrznych. Jednak od niedawna PHP dysponuje znakomitym narzędziem, które nadaje się do większości zwykłych zastosowań, a przy tym praktycznie nie wymaga instalacji. Chodzi o SQLite i tym pakietem zajmiemy się w czytanym przez Ciebie tekście.

SQLite bardzo dobrze wspiera zapewne znany ci już język komunikacji z bazami danych zwany SQL (Structured Query Language). Co więcej - obsługuje kilka rozwiązań, których np. MySQL'owi po prostu brakuje:

Obsługa zapytań zagnieżdżonych
Obsługa widoków
Obsługa transakcji
Obsługa triggerów (acz jeszcze niepełna)
Możliwość definiowania własnych funkcji.

Jednak najważniejszym faktem dla Ciebie, programisty PHP jest to, że nie będziesz musiał instalować praktycznie żadnego dodatkowego serwera tejże bazy! Dlaczego? Bo sam moduł php_sqlite jest tym serwerem, a to oznacza, że serwer SQLite jest po prostu wbudowany w PHP :). Ponadto od PHP 5 będzie on już na stałe w niego wbudowany i nie będzie możliwości wyłączyć jego obsługi (do niedawna miejsce to dzierżył moduł dla baz MySQL).

SQLite przechowuje swoje dane w jednym pliku, który zwykle znajduje się zaraz obok twoich skryptów PHP. Nie jest on jednak szyfrowany i dlatego SQLite niezbyt nadaje się, jeśli zależy Ci na bezpieczeństwie swych danych. Z drugiej strony dzięki takiemu krokowi, według zapewnień autorów, SQLite działa prawie dwa razy szybciej niż MySQL i PostgreSQL. Tak więc jeżeli bezpieczeństwo stawiasz na miejscu pierwszym - wybierz inną bazę danych. W przeciwnym wypadku - warto spróbować. Być może kiedyś będzie to dla Ciebie jedyna alternatywa, gdy dostaniesz serwer bez obsługi żadnych innych baz danych?

Muszę także wspomnieć o pewnej rzeczy związanej z obsługą języka SQL w omawianej bazie - zapytania ALTER musisz odłożyć na półkę, bowiem SQLite ich po prostu nie obsługuje.

Instalacja

Instrukcje odnośnie instalacji w systemach Unix znajdują się w pliku INSTALL dostępnym razem z pakietem kodu źródłowego PHP. Posiadacze PHP 5 mogą spokojnie ten rozdział pominąć, natomiast ci, którzy używają wciąż PHP 4 (UWAGA: Koniecznie wersja 4.3.x; jeśli masz starszą, to zrób update i nie popełniaj więcej grzechu zaniedbania tejże kwestii :)), muszą pobrać plik php_sqlite.dll spod adresu: http://snaps.php.net/win32/PECL_STABLE/php_sqlite.dll, skopiować go do folderu "extensions" w katalogu ze swoim PHP, po czym otworzyć plik php.ini. Tam należy zlokalizować zbiór linijek z napisami "extension=php_xxx.dll" i pod nim dopisać "extension=php_sqlite.dll".

Do góry

Tworzenie nowej bazy danych

Na początku zapoznamy się z samym "łączeniem się" do bazy SQLite, oraz zakładaniem w niej tabel. Połączenie realizujemy przy pomocy funkcji sqlite_open(nazwa_pliku). Jako parametr podajemy nazwę pliku, w której znajduje się baza danych, natomiast po jej wykonaniu otrzymujemy uchwyt połączenia, który będzie potrzebny praktycznie we wszystkich innych funkcjach. Połączenie zamykamy poleceniem sqlite_close(), gdzie za parametr podajemy uchwyt. Zapytanie możemy wysłać komendą sqlite_query(uchwyt, zapytanie). A oto przykład numer 1, dzięki któremu stworzymy bazę potrzebną nam w późniejszych przykładach.

<?php
 
   $db = sqlite_open('./sklep.sqlite');
   
   sqlite_query($db,'
   CREATE TABLE products (
      product_id INTEGER PRIMARY KEY,
      product_name VARCHAR(32),
      product_desc BLOB,
      product_price FLOAT
   );
   
   CREATE TABLE clients (
      client_id INTEGER PRIMARY KEY,
      client_name,
      client_address
   );
   
   INSERT INTO products VALUES (NULL, \'Spadochron do nurkowania\', \'Rewelacyjny spadochron dla nurków! Znakomicie zapobiega zderzeniu się z dnem!\', 250.99);
   INSERT INTO products VALUES (NULL, \'Wanna z drzwiami jednoskrzyd3owymi\', \'Dzięki wbudowanym drzwiom już wiecej nie pośliźniesz się wychodząc z wanny\', 599.8);
   INSERT INTO products VALUES (NULL, \'Używany papier toaletowy\', \'Tani, wygodny, ekologiczny (bo z odzysku) - czyli to, co ekolodzy lubią najbardziej\', 1.99);
   
   INSERT INTO clients VALUES (NULL, \'Mietek Żulewicz\', \'Prosektorium przy ul. Ofiar Hiroszimy; 00-000 Pcim Dolny\');
   INSERT INTO clients VALUES (NULL, \'Jan Kowalski\', \'ul. Stołeczna 13, 99-999 Bździągowo\');   
   ');
   
   echo 'Baza danych SQLite założona pomyślnie!';
   
   sqlite_close($db);
 
?>

Przyjrzyj się wywołaniu funkcji sqlite_query(), gdyż przedstawia ono parę ciekawych cech SQLite'a. Pierwsza z nich - możemy wysłać dowolnie dużo zapytań wywołując funkcję tylko raz - w tym wypadku kolejne zapytania oddzielone są średnikami. Druga sprawa dotyczy samego tworzenia tabel (CREATE TABLE). Zauważ - w tabeli "clients" nie zdefiniowaliśmy typów dla pól. To nie jest błąd, bowiem SQLite dopuszcza takie rozwiązanie, a działa ono podobnie do zmiennych w PHP - typ dobierany jest automatycznie. Warto zwrócić także uwagę na "INTEGER PRIMARY KEY". Kod ten informuje SQLite, że dane pole ma zawierać sekwencję. W PostgreSQL'u odpowiada to typowi SERIAL, natomiast w MySQL'u parametrowi AUTO_INCREMENT. Jeżeli chcesz zapoznać się ze wszystkimi dostępnymi typami, wejdź pod adres http://sqlite.org/datatypes.html.

Do góry

Pobieranie danych z bazy

W tej sferze SQLite nie oferuje zbyt wielu niespodzianek - praktycznie wszystko przebiega podobnie, jak w innych bazach. Oto przykład:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Sklep</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-2">
</head>
 
<body>
<center>Produkty</center>
<?php
   $db = sqlite_open('./sklep.sqlite');
 
   $r = sqlite_query($db, 'SELECT * FROM products');
 
   echo 'Znaleziono '.sqlite_num_rows($r).' produktów<br>';
 
   while($row = sqlite_fetch_array($r, SQLITE_ASSOC)){
      echo '<b>'.$row['product_id'].'. '.$row['product_name'].'</b><br> Opis: '.$row['product_desc'].'<br>Cena: '.$row['product_price'].'<br><br>';
   }
?>
<hr>
<center>Klienci</center>
<?php
 
   $r = sqlite_query($db, 'SELECT * FROM clients');
 
   echo 'Znaleziono '.sqlite_num_rows($r).' klientów<br>';   
 
   while($row = sqlite_fetch_array($r, SQLITE_ASSOC)){
      echo '<b>'.$row['client_id'].'. '.$row['client_name'].'</b><br> Adres: '.$row['client_address'].'<br><br>';
   }
 
   sqlite_close($db);
?>
</body>
</html>

W zasadzie można zainteresować się tu tylko drugim parametrem funkcji sql_fetch_array(). Określa on, jak zostaną określone nazwy pól w tabeli wynikowej. SQLITE_ASSOC oznacza, że będziemy mogli odwoływać się poprzez nazwy pól: $row['nazwa_pola']; SQLITE_NUM oznacza numeryczne oznaczenia: $row[1]; natomiast SQLITE_BOTH spowoduje, że tabela wynikowa będzie zawierała i jedne, i drugie odwołania.

Do góry

Widoki

Widok to po prostu pojedyncze zapytanie SELECT, którego możemy używac tak jak zwykłą tabelę (z tego powodu jest zwany czasem wirtualną tabelą). Stworzyć go możemy poleceniem CREATE VIEW nazwa AS zapytanie_select tak, jak w poniższym przykładzie:

<?php
 
   $db = sqlite_open('./sklep.sqlite');
 
   sqlite_query($db, '
      CREATE TABLE products_to_clients (product_id, client_id);
 
      INSERT INTO products_to_clients VALUES(1,2);
      INSERT INTO products_to_clients VALUES(3,2);
      INSERT INTO products_to_clients VALUES(2,1);
 
      CREATE VIEW client_products AS SELECT pc.client_id AS client_id, p.product_name AS product_name FROM products_to_clients pc, products p WHERE pc.product_id = p.product_id;');
 
   $r = sqlite_query($db, 'SELECT product_name FROM client_products WHERE client_id=2');
   echo 'Produkty zamówione przez klienta 2:<br>';
   while($row = sqlite_fetch_array($r, SQLITE_ASSOC)){
      echo $row['product_name'].'<br>';
   }
 
   $r = sqlite_query($db, 'SELECT product_name FROM client_products');
   echo '<br><br>Wszystkie zamówione przynajmniej raz produkty<br>';
   while($row = sqlite_fetch_array($r, SQLITE_ASSOC)){
      echo $row['product_name'].'<br>';
   }
 
   sqlite_close($db);
?>

Stworzyliśmy tutaj widok "client_products". Mówi on nam, że posiada dwa pola: client_id, oraz product_name (fragment SELECT pc.client_id AS client_id, p.product_name AS product_name). Wartość client_id pobierana jest z tabeli products_to_orders (informacje o tym, jakie produkty zamówił jaki klient), natomiast product_name z tabeli products, ale tylko wtedy, gdy spełnia warunek WHERE. Użytkownik nie widzi całego tego kombinowania z tym, skąd pobrać wartości jakich pól - dla niego client_products to najzwyklejsza w świecie tabelka z dwoma polami. Jedynym "dziwnym" jej zachowaniem jest to, że jeżeli zmienimy nazwę jakiegoś produktu w tabeli "products", to "zmieni" się ona także w widoku.

UWAGA: W SQLite dozwolone jest tylko i wyłącznie czytanie z widoków - tak więc naszego "client_products" nie będziemy mogli użyć w zapytaniach typu INSERT, UPDATE, czy DELETE.

Widoki usuwamy poleceniem DROP VIEW nazwa_widoku.

Do góry

Tworzenie indeksów

Co to jest indeks, wie każdy, kto zabawiał się PostgreSQL'em, czy MySQL'em - po prostu jest to kopia danego pola, gdzie wszystkie wartości są już posortowane, tak więc przyspiesza się wyszukiwanie, czy sortowanie, natomiast spowalnia dodawanie rekordów (z racji tego, że trzeba jeszcze zmodyfikować indeks), a także zwiększa się objętość bazy. Indeksów nie można zakładać, jak popadnie - nie da się też tego dobrze opisać. Dlatego proponuję pooglądać, gdzie są one założone w bazacch danych różnych znanych skryptów, np. phpBB, czy InvisionBoard. Indeks tworzymy w sposób przedstawiony na przykładzie:

<?php
 
   $db = sqlite_open('./sklep.sqlite');
 
   sqlite_query($db, 'CREATE INDEX i ON products_to_clients (client_id, product_id);');
 
   if(!sqlite_last_error($db)){
      echo 'Indeks dodany!';
   }else{
      echo 'Błąd SQLite: '.sqlite_error_string(sqlite_last_error());
   }
 
   sqlite_close($db);
?>

Jedyne, co musimy określić w zapytaniu CREATE INDEX to nazwa indeksu, tabela, na jaką chcemy go założyć, oraz pola, jakich ma on dotyczyć.

Przykład ten ilustruje dodatkowo, jak należy sprawdzać błędy w naszych zapytaniach. Jest to bardzo ważne, ponieważ często nieutworzenie uchwytu do wyników przez funkcję sqlite_query() spowodowane jest właśnie błędem w naszym zapytaniu!

Do góry

Triggery - co to takiego?

Trigger jest to zadanie (jedno, lub więcej zapytań SQL) wykonujące się w tle (czyli programista nawet nie wie, że ono się wywołało), gdy zajdzie określona okoliczność (np. dodanie rekordu do bazy). Daje nam on potężne możliwości. Zaprezentuję je na podstawie dwóch poniższych przykładów. Pierwszy z nich utworzy trigger, natomiast drugi pokaże efekt jego działania :). Pod ogień weźmiemy tabelę products_to_clients oraz clients. Co tu może dziać się ciekawego? Ano, można usunąć klienta z bazy. Wtedy jednak zostaną śmieci w pierwszej tabelce. Mamy dwa wyjścia - albo ręcznie je usunąć, albo utworzyć trigger, który zrobi to za nas bez nas :).

<?php
 
   $db = sqlite_open('./sklep.sqlite');
   
   sqlite_query($db, '
      CREATE TRIGGER delete_orders BEFORE DELETE ON clients
      FOR EACH ROW
      BEGIN
         DELETE FROM products_to_clients WHERE client_id = OLD.client_id;
      END;
   ');
   
   if(!sqlite_last_error($db)){
      echo 'Trigger dodany!';
   }else{
      echo 'Błąd SQLite: '.sqlite_error_string(sqlite_last_error());
   }
   
   sqlite_close($db);
?>

Co my tu mamy? Ano, zdefiniowany trigger "delete_orders", który wykona się przed skasowaniem rekordów z tabeli clients (BEFORE DELETE ON clients). Dla każdego kasowanego rekordu (FOR EACH ROW) wykona on zapytanie DELETE FROM products_to_clients WHERE client_id = OLD.client_id;, które usunie z tabeli products_to_orders odnośniki do produktów zamówionych przez klienta "OLD.client_id". OLD jest aliasem. SQLite daje nam jeszcze jeden taki alias: NEW, a oto ich użycie:

Triggery wykonujące się przy zapytaniach INSERT - dostępny alias NEW określający dane wprowadzane do tabeli.
Triggery wykonujące się przy zapytaniach UPDATE - dostępne oba aliasy. NEW określa nową zawartość rekordu, natomiast OLD starą.
Triggery wykonujące się przy zapytaniach DELETE - tylko OLD określający usuwany rekord.

Polecam zapoznać się ze składnią polecenia CREATE TRIGGER na stronie: http://sqlite.org/lang.html#createtrigger, oraz w ogóle samodzielnie poeksperymentować z tym. Obsługa triggerów jest tematem na cały obszerny artykuł, a ja nie zamierzam się aż tak tu rozpisywać :).

Ale wróćmy do rzeczywistości. Warto pokazać, co nasz trigger takiego potrafi:

<?php
 
   $db = sqlite_open('./proba_sklep.sqlite');
 
   sqlite_query($db, 'DELETE FROM clients WHERE client_id = 2');
   if(!sqlite_last_error($db)){
      echo 'Klient 2 usunięty!';
   }
 
   $r = sqlite_query($db, 'SELECT * FROM products_to_clients');
   echo 'Kontrola zamówień:<br>';
   while($row = sqlite_fetch_array($r, SQLITE_ASSOC)){
      echo 'Klient '.$row['client_id'].' zamówił produkt o ID '.$row['product_id'].'<br>';
   }
   
   sqlite_close($db);
 
?>

Uruchom przykład - co się stało? Usunęliśmy tylko klienta z tabeli "clients", natomiast w tabeli "products_to_orders" nie ma także już informacji o zamówionych przez niego produktach. Po prostu w tle zadziałał trigger, który posprzątał za nas automatycznie.

Do góry

Tworzenie własnych funkcji

Aby stworzyć własną funkcję w PostgreSQL'u, należało być w nim samym nieźle otrzaskanym, gdyż zadanie to jest tam trudniejsze, niż przybicie galaretki do ściany :). Jednak SQLite jest wbudowany w PHP, nic nie stoi zatem na przeszkodzie, by korzystać z... udostępnianych przez niego funkcji. Oto przykład:

<?php
 
   function tralala(){
      return 'Jestem sobie funkcją PHP-SQLite!';
   }
 
   $db = sqlite_open('./sklep.sqlite');
 
   sqlite_create_function($db, 'nowa_funkcja', 'tralala');
 
   $r = sqlite_query($db, 'SELECT nowa_funkcja() AS wynik;');
   $row = sqlite_fetch_array($r, SQLITE_ASSOC);
 
   echo $row['wynik'];
 
   sqlite_close($db);
?>

Utworzyliśmy w PHP funkcję "tralala", a następnie poleceniem sqlite_create_function poinformowaliśmy SQLite, że jeśli natrafi w zapytaniu na funkcję "nowa_funkcja", to ma wykonać stworzoną przez nas "tralala". Niżej z resztą widać, iż sposób ten po prostu działa.

Do góry

Zakończenie

To już koniec artykułu o bazie SQLite. Mam nadzieję, że zainteresowałem Cię tym rozwiązaniem, zwłaszcza, że jest ono tego warte. Nie poprzestawaj na tym. Oto dwa bardzo przydatne adresy, pod którymi znajdziesz więcej informacji: