Introduction to SQlite

Whom was this article written for?

This article was written for the PHP-programmers who showed their interest for the SQLite extension. The article will introduce to the reader functionality which PHP SQLite extension offers and also will treat the advantages of SQLite in comparison with other data bases. The reader is supposed to know the basics of PHP and SQL. Some experience of work with MySQL or PostgreSQL will help you to understand the following article better.

Introduction

Perhaps you’ve heard about the new PHP-extension – SQLite. There are many reasons why SQLite may seem to you the best invention since you’ve learnt to cut bread. SQLite offers well-done SQL-interface to the to the irrelative data base and creates a sophisticated alternative to the bulky interfaces of other data bases without losing functionality or speed as you could expect. In the article we’ll treat this amazing extension and we hope we’ll manage to prove the advantages about which you’ve perhaps heard so many times.

What is SQLite?

SQLite is an integrated library in which many of the standards SQL92 is realized. Its pretension for the fame is the runner on its own and its interface (especially its runner) within one library and also the possibility to store all the data in one file. I suppose the functionality of SQLite is somewhere between MySQL and PostgreSQL. But in fact it shows up that SQLite is 2-3 (and even more) times faster. That is possible due to high-graduated inner architecture and reducing the necessity of connections like ‘server-client’ and ‘client-server’.

All this gathered in one folder is only a little bit larger than client part of MySQL library and that’s of course an impressing progress for the full value data base. Using high-effective infrastructure SQLite may work in a tiny memory volume that is many times less than in any other data base systems. This makes SQLite a very suitable tool that is possible to use almost for every purpose in the data base.

Why should I use SQlite?

Beside speed and effective work SQLite has a lot of other advantages which make it ideal solving for many problems. As the data base of SQLite in fact consists of usual files you have no need in the additional tools of administration which take a lot of time to create a compose structure of access rights for protection of the users’ data bases. All this is already automatically supported by the organization of the access rights within the file system and this also implies (in the limitation of the space) that there is no need in any special rules for control over the filling disc space by the user. The advantage for the user is the possibility to create so many data bases as they like plus total control over all these bases.

The fact that the data base is the only file makes it easy to transfer. Besides that SQLite evades necessity of starting additional service processes which might ‘take away’ larger part of memory and other resources even in the case of the moderate usage of the data base.

SQLite extension

As the latest data base extension SQLite is free from code for the inverse compatibility. This also allows the extension use the latest PHP-decisions to rich the highest level of completing and functionality. The producers made it easy for the users creating the extension easy for the transferring to it from the other systems of data bases, leaving its interface similar to those realized in PHP.

SQLite also supports flexible channel for transmitting resources of the data base in the procedure interfaces making it easy for transferring from MySQL where the resource transmits last and from PostgreSQL where it transmits first.

SQLite also differs with its powerful objective-oriented interface which may be used for effective extracting of data from the base, so you have no necessity in carrying-out your own cover for the procedure interface. As it shows the example below the objective-oriented interface also let us evade transmitting all the resources together.

<?php 
// create new base (OO interface) 
$db = new sqlite_db("db.sqlite"); 

// create table foo and insert something as an example 
$db->query("BEGIN; 
        CREATE TABLE foo(id INTEGER PRIMARY KEY, name CHAR(255)); 
        INSERT INTO foo (name) VALUES("Ilia"); 
        INSERT INTO foo (name) VALUES("Ilia2"); 
        INSERT INTO foo (name) VALUES("Ilia3"); 
        COMMIT;"); 

// execute the query 
$result = $db->query("SELECT * FROM foo"); 
while ($result->hasMore()) { 
    // get the current cell
    $row = $result->current();      
    print_r($row); 
// pass to the next cell
    $result->next(); 
} 
unset($db); 

?>

Installing of SQLite

In PHP 5.0 installing of SQLite has its specialty as the extension and library are connected together, so all you need to do is add with-sqlite in the line of configuration. I would also recommend you to install SQLite but only in the case if you deal with compiled binary file that let you open the base and manage it without using PHP. This is useful enough for the completing different simultaneous commands and also for the testing of the inquiry code. In the future it’ll show up rather often that that connected library of SQLite is a little ‘out of date’,so the connection of your PHP with the outer library will profit you from the latest corrections and innovations SQLite. It’ll also let you update your SQLite without recompilation PHP.

To gather SQLite extension as an outer library you are simply to use –with-sqlite=/path/to/lib/.

It’s also to mention that SQLite extension is tested thoughtfully for the objective-oriented and procedure interfaces as well. Every single function and every single method supported by SQLite is tested. It’s a wonderful source of examples not only for each method’s and each function’s of SQLite work but also for the expected consequence that lets us see the final result of each operation.

Using SQLite

The procedure interface of SQLite is nearly the same as the one of MySQL and other data base extensions. Transfer to SQLite will mostly demand only changing the mysql/pq/etc… prefix of the function to sqlite.

<?php 
// create the database (procedure interface) 
$db = sqlite_open("db.sqlite"); 

// create the table foo 
sqlite_query($db, "CREATE TABLE foo (id INTEGER PRIMARY KEY, name CHAR(255))"); 

// add something as an example
sqlite_query($db, "INSERT INTO foo (name) VALUES ("Ilia")"); 
sqlite_query($db, "INSERT INTO foo (name) VALUES ("Ilia2")"); 
sqlite_query($db, "INSERT INTO foo (name) VALUES ("Ilia3")"); 

// execute the query
$result = sqlite_query($db, "SELECT * FROM foo"); 
while ($row = sqlite_fetch_array($result)) { 
    print_r($row); 
     /every result will look like: 
    Array 
    ( 
        [0] => 1 
        [id] => 1 
        [1] => Ilia 
        [name] => Ilia 
    ) 
*/ 
} 

// close the connection 
sqlite_close($db); 

?>

The most striking differences between SQLite and other data bases are in the runner itself. Unlike other data bases SQLite is not tied to the types; all the data is saved as lines with end-symbol NULL that is better than binary presentation of the data in the columns of a special type. Due to the combinative ability SQLite supports specification type in constructions CREATE TABLE such as INT, CHAR, FLOAT, TEXT etc., but doesn’t use them in fact. Inside of the base SQLite only makes difference between line- and digital data during the sorting. So, if you are not going to sort the data you are not to indicate special type of columns by creation of tables in SQLite.

‘Non-type nature’ of SQLite makes sorting and comparing data a little bit slower as every time SQLite will need to define the type of the data and use either the line-mechanism of sorting/comparison or the digital one. SQL tables often demand automatically given key for the rapid access to the columns implying return of the reference to the last added column. This syntax is useless for SQLite. To create such a table you’ll need to announce the field as INTEGER PRIMARY KEY what is more comfortable than the indication of specialized type or getting additional qualities which show that the field is autoincremental.

Connected requests

As you may expect, SQLite bears a lot of new specialties that improve discharge and widen functionality. One of such specialties is possibility to complete connected requests which imply doing multi-inquiries by means of a function of completing one inquiry. This reduces the quantity of the PHP-functions involved and thus increases the script working speed. It also lets you form the blocks of requests inside transactions easily that improves discharge in the future. Such opportunity may serve a valuable factor during record multi-requests on the base. But there are some rather peculiar specialties which are not to forget. If some request in SQLite uses something that user has inserted directly, you are to take some extra precautionary measures to check such input to reject undesired request. Unlike MySQL where such action may be followed only by misunderstanding by requesting data base and show up an error, in SQLite this allows the attacker to complete some request on your server with possible miserable follows. If you add notes through the request block and would like to return the identificator, sqlite_last_insert_rowid() will solve this problem well, but returns only the identificator of the last note. On the other hand, trying to find out what quantity of columns was changed by means of sqlite_changes() we’ll get a result containing general quantity of columns which were changed by all completed requests. If your request block contains SELECT, make sure that it’s the first request, or your final selection won’t contain the columns returned by the given request.

New functions

Beside its new inner specialties, SQLite offers a row of new functions which make extracting data from the base more simple and rapid.

<?php 
$db = new sqlite_db("db.sqlite"); 
$result_array = $db->array_query("SELECT * FROM foo", SQLITE_ASSOC); 
print_r($result_array); 
?>

This lets complete request and extract data by calling out one function reducing all the spare work of PHP. The PHP-script itself becomes more simple due to the usage only one function in the case where you otherwise would have to use sqlite_single_query() that at once returns the line or the massive of the lines depending on the number of columns from which the information is got.

<?php 

$db = sqlite_open("db.sqlite"); 
// Get the column id
$id = sqlite_single_query($db, "SELECT id FROM foo WHERE name="Ilia""); 
var_dump($id); //string(1) 

//The result is an array if there are more than one matches  
$ids = sqlite_single_query($db, "SELECT id FROM foo WHERE name LIKE "I%""); 
var_dump($ids); // array(3) 

?>

As in the case with other specialties you may use it, but not too much. In case of selection of all the data by the request at one jump, you are to remember that all the results will be saved in the memory. If the result of selection contains big amount of data the costs of all memory expenses will, of course, reduce to nothing all the advantages got by reducing the number of functions’ requests. It means, you are to save the usage of these functions for the cases of selection of little data amounts.

Iterators in SQLite

There is another way of data selection by means of inquiry using iterators.

<?php 

$db = new sqlite_db("db.sqlite"); 
// execute the query without caching
$res = $db->unbuffered_query("SELECT * FROM foo"); 
foreach ($res as $row) { // get the result in the cycle 
        // output code 
        print_r($row); 
} 

?>

Getting results in cycle works exactly like getting through the massive in cycle using foreach(), except at that time you’ve got no access to the keys and the meaning represents a massive containing data in special columns. Thus the iterators are not the functions but the inner indicators in the runner, they demand less resources in comparison with functions sqlite_fetch_*() and they don’t demand to cash results in the memory. The final result will be extremely fast; this is simpler and more available way of getting the data. There are no drawbacks in using iterators objects in SQLite, and whenever you need to get the result as a plenty of lines, you are to remember about this opportunity at any case.

Auxiliary functions

SQLite also differs through the row of auxiliary functions which may be useful during the work with data base. One of these functions sqlite_num_fields() is possible to use for the clearing up the number of columns in the special results of selection. As a variant, if you’re going to get data, you are simply to use count() with the first result which will bring you back the same number. If the number- and figure keys are extracted, you’ll need to separate the result, as there are so many inputs in the resultative massive as many fields the table has. This number may be important if your script needs to get titles of the fields inside of some table. In this case you could use sqlite_field_name() in cycle for the access to this information as it’s showed by the example below.

<?php 

$db = new sqlite_db("db.sqlite"); 
$res = $db->unbuffered_query("SELECT * FROM foo LIMIT 1"); 
// get the field number 
$n_fields = $res->num_fields(); 
while ($i < $n_fields) { 
     // extract separate fields
$field_name = $res->field_name($i++); 
    echo $field_name."\n"; 
} 

?>

Of course, this way of getting names for the columns from the table is not ideal, simply as it fails in case when the table doesn’t contain any columns, and also as it’ll demand from you getting data which you’re not going to use. It will be much better decision to use function sqlite_fetch_column_types() which gets columns with their types from the table and doesn’t depend on the presence and absence of data.

The advantage of bufferization

In the most cases because of implementation and usage of the memory you wouldn’t like to use requests without cashing. This may lead to a little lose of functionality that can be necessary in definite cases for which requests without cashing aren’t always the best choice.

For example, you’d like to find out real number of the columns chosen by your request. In request without cashing you’ll have to make a selection of every single column before you can determine it. By the time cashed requests are the simplest way that consists in usage of sqlite_num_rows() function which will get this information easily from the result of the selection. Requests without cashing are also limited in the direct selection of the data, implying that you have to select data from all the columns at one passage. There are no such limits in the cashed requests, you may use sqlite_seek() for the shifting at any column and the data selection from it or even for the access to the columns in the inverse order, if necessary.

<?php 

$db = new sqlite_db("db.sqlite"); 
$res = $db->query("SELECT * FROM foo"); 
$n_rows = $res->num_rows(); // get the amount of the fetch cells
$res->seek($n_rows - 1); 
do { 
    $data = $res->current(SQLITE_ASSOC); // get the cell's 
    print_r($data); 
} 
while ($res->hasPrev()&& $res->prev()); 
?>

Users’ functions

One of the most interesting opportunities is that SQLite offers a table for the possibility of creation your own functions used within SQL. It’s possible due to the fact that SQLite contains in one library connected with PHP the interface and the base runner as well. Using sqlite_create_function() you can create functions which further may be applied to the result of the selection or used within some WHERE-condition.

<?php 
function char_compare($db_str, $user_str) { 
    return similar_text($db_str, $user_str); 
} 

$db = new sqlite_db("db.sqlite"); 

/* Create the char_compare() function inside the SQLite based on the char_compare() PHP function. */ 
$db->create_function("char_compare", "char_compare", 2); 
         
/* Execute the query where char_compare() is used for name and indicated strinf comparing */ 
$res = $db->array_query("SELECT name, char_compare(name, "Il2") AS sim_index FROM foo", SQLITE_ASSOC); 

print_r($res); 

?>

The possibility of usage PHP in SQL lets you make the given script simpler making it available to the greater developers’ audience. It lets use PHP as the pattern runner which will insert HTML-code in the data from base. In many cases it can simplify the code so much that there is no need in the usage of additional pattern runner. Beside simplifying the code it also improves execution of the program and reduces script’s expenses on the memory because of absence of any actions with data in the users’ space.

Don’t forget that in case you work with data containing binary code you are to use the function sqlite_udf_decode_binary() for transferring them from inner codification in which they are stored in SQLite before staring work with them. If you did so, you would need to code binary data using sqlite_udf_encode_binary() for making sure that the data won’t be damaged by the following access.

Review

Now having examined the work of SQLite and what it can offer to us, you’ll perhaps pay your attention to it for using in your current and further supplements. I hope, this short review has given you some idea about functionality of SQLite and hasn’t spoiled the good impression that you’d had about it before.

As any other tool, SQLite has its advantages and disadvantages. Being ideal solution for small or/and mostly meant for reading data supplements, it doesn’t suit well enough for the large-scale supplements often executing record in the base. This limitation is the consequence of the fact that the SQLite architecture is based on the only file that doesn’t imply distribution of data between repeatedly created files and also uses block system on the record.