MOJA PASJA - PROGRAMOWANIE
   Dzisiaj jest Wtorek, 13 maja, 2008r. Ostatnia aktualzacja miała miejsce: 10 grudnia 2006r. Homepage

Programowanie: Artykuły * FAQ * Download * Komponenty * Książki WWW: Artykuły * Narzędzia * Kursy * Darmowe * FAQ * Skrypty * Ksiązki Off-Topic: Aforyzmy * Humor Inne: Forum * Wiki * Liczniki * Linki * Chat * Grafika * Video * Inne


Poznajemy SQLite

w aspekcje języka PHP zorientowanego strukturalnie

Autor: y00da    


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 [; .

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.

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.

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


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 [; ).

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.

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() 
<?
   sqlite_open('/sciezka_do_bazy_danych/books.db');
?>
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.

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. 
<?
   sqlite_query($uchwyt_db,"Zapytanie");
?>
Połączenie z bazą danych zamykamy funkcją sqlite_close();

Całość może wyglądać np. tak: 
<?
   $db = sqlite_open('/sciezka_do_bazy_danych/books.db');
   sqlite_query($db,"CREATE TABLE books(autor VARCHAR(100), tytul VARCHAR(100)) ");
   sqlite_close($db);
?>
Wstawianie danych

Wstawianie danych odbywa się za pomocą SQL'owego INSERT'a 
<?
sqlite_query($uchwyt_db,"INSERT INTO books VALUES('Sienkiewicz','Krzyzacy') ");
?>
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: 
<?
   $db = sqlite_open('/sciezka_do_bazy_danych/books.db');
 
   $autor = sqlite_escape_string ($autor);
   $tytul = sqlite_escape_string($tytul);
 
   sqlite_query($uchwyt_db,"INSERT INTO books VALUES('$autor','$tytul') ");
 
   sqlite_close($db);
?>
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: 
<?
   $query = sqlite_query($uchwyt_db,"SELECT * FROM books");
   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. 
<?
   $table = sqlite_array_query($uchwyt_db,"SELECT * FROM books");
   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: 
<?
   $query = sqlite_query($uchwyt_db,"SELECT * FROM books");
   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
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 dane przechowywane w polach nie mogą się powtarzać(np. PESEL, ID), możemy oznaczyć je jako unikatowe. SQLite, oprócz tego, że będzie "pilnował", aby zawartość pól się niepowtarzała, sprawi także, że pole oznaczone typem UNIQUE, będzie automatycznie indeksowane. 
<?
   sqlite_query($uchwyt_db,"CREATE TABLE books(autor VARCHAR(100) UNIQUE, tytul VARCHAR(100)) ");
?>
Nadawanie indeksów na już istniejące pola odbywa się poprzez instrukcje CREATE INDEX. Pokazuje to poniższy przykład: 
<?
   sqlite_query($uchwyt_db,"CREATE INDEX books_autor_index ON books(autor)" );
?>
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. 
<?
   sqlite_query($uchwyt_db,"CREATE TABLE books(bookid INTEGER PRIMARY KEY , autor VARCHAR(100) , tytul VARCHAR(100) ") );
?>
Powyższy przykład utworzy tabele z takimi kolumnami:

bookidautortytul


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: 
<?
   sqlite_query( $uchwyt_db,"INSERT INTO books VALUES(NULL,'Sienkiewicz','Krzyzacy') ");
?>
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.

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: 
<?
   $sql = 'BEGIN;';
 
   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: 
<?
   sqlite_exec ($uchwyt_db,
   "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; "
);
?>
Jak zapewne zauważyliście w powyższym przykładzie do wykonania tego seryjnego zapytania została zastosowana funkcja sqlite_exec(). Główna różnica między obydwiema funkcjami (sqlite_exec() i sqlite_query()) polega na odmiennym "zachowaniu", wobec wielorotnych zapytań. Pierwsza z nich traktuje swój drugi parametr jako zapytanie seryjne tylko wtedy gdy dane przez nią zwrócone nie są używanie (w przypadku gdy są wykonuje zapytanie tylko do znaku końca wiersza, w przypadku SQL średnika), natomiast funkcja sqlite_exec(), swój drugi parametr z góry traktuje jako serie zapytań. Więcej na ten temat znajdziecie na: http://pl.php.net/manual/pl/function.sqlite-query.php w sekcji notatki.

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. 
<?
   sqlite_open(':memory:');
?>
Od tej chwili wszystkie wyniki zapytań będą zapisywane w pamięci operacyjnej.

Przykład: 
<?
   sqlite_open(':memory:');
   sqlite_query("CREATE TABLE books(autor VARCHAR(100) , tytul VARCHAR(100))"  );
?>
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: 
<?
   function add_a ( $string )
   {
     $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.

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: 
<?
   if( sqlite_query($_uchwyt_db, $sql) )
   {
      // dalsze operacje
   }
   else
      die( sqlite_error_string( sqlite_last_error($uchwyt_db) ) );
?>
albo: 
<?
   sqlite_create_function($uchwyt_db, 'add', 'add_a') or die( sqlite_error_string( sqlite_last_error($uchwyt_db) )  );
?>
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. 
<?
   sqlite_open('/sciezka_do_bazy_danych/books.db',0666,$sqlite_error) or die($sqlite_error);
?>
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.

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

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_Reference_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/

   Wasze opinie:
   Średnia ocena: 6/10 (12 głosów)
   
   Liczba komentarzy: 3Skomentuj !   

Autor: taData dodania: 2008-03-13
W C# !!??!! Czlowieku co ty piszesz..., skad masz takie bzdurne informacje... Napisana jest w C... Co do wstepu to SQLite nie jest konkurentem MySql-a, przynamniej nie w zalozeniach projektu... Autorowi proponuje jeszcze raz zapoznac sie z informacjami z zamieszczonej przez niego samego strony: http://www.sqlite.org/ (czytaj ze zrozumieniem)

Autor: epijData dodania: 2008-01-30
Nie drzew binarnych, a B-drzew, których stopień jest wyższy niż 2!

Autor: Joda12Data dodania: 2007-11-01
SQLite to biblioteka napisana w języku C# - bzdura

Stronę przygotował: Kacper Cieśla (comboy). Wszelkie prawa zastrzeżone.
Reklama * Zgłoś błąd * Kontakt * Hosting * O stronie * Sponsoring
Czas generowania strony: 0.72s