Results 1 to 2 of 2

Thread: Introduction to SQlite

  1. #1
    Senior Member
    Join Date
    Sep 2010
    Posts
    178

    Introduction to SQlite

    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.

  2. #2
    SQLite is an embedded relational database engine. Its developers call it a self-contained, serverless, zero-configuration and transactional SQL database engine.

    thanks to share this forum,,its really very help full to me..

Similar Threads

  1. Introduction into PHP and MySQL
    By Meer in forum Programming
    Replies: 2
    Last Post: 06-13-2013, 12:33 PM
  2. introduction
    By westchester0 in forum Introduction
    Replies: 0
    Last Post: 08-11-2012, 08:00 AM
  3. Introduction
    By Horor in forum Introduction
    Replies: 0
    Last Post: 07-20-2012, 01:45 AM
  4. My Introduction,,
    By HEATHROWPARKING in forum Introduction
    Replies: 1
    Last Post: 07-06-2012, 08:25 PM
  5. Introduction to PHP 5
    By Meer in forum Programming
    Replies: 0
    Last Post: 10-19-2010, 06:26 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •