Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 1 of 1
  1. #1
    Senior Member rosland's Avatar
    Join Date
    Jul 2003
    Location
    Norway
    Posts
    1,944
    Member #
    2096
    SQLite:

    As of PHP 5, a new flat file DB engine comes bundled with the parser. The new DB engine is named SQLite. It is a scaled down version of traditional databases. For most sites SQLite will be sufficient to cover most of your needs.
    Even though the name implies simplicity, it incorporates most of the features traditionally used in DB interactions. It even supports transactions(!), though it’s hardly optimized for this.

    Most DB systems that supports transactions have the capability of locking the individual row or at least the individual table, when a transaction is taking place. SQLite locks the whole database (all tables) during these operations. For medium to high traffic sites, it would make read/write access dramatically slow.
    Actually it locks the whole DB for any type of write operations, which makes it less than ideal for DB’s requiring a lot of write operations.

    Then again, that’s not its intended use. For normal data retrieval (read) operations, it’s the fastest on the market. For a typical BLOG (lots of read requests, and few write), it’s ideal.
    For a site like WDF, it would fall short, as there are numerous write operations going on at the same time people are trying to access information. WDF’s database is also huge with posts beyond the 100 000 mark, user information, different access privileges, etc. This is beyond the scope of SQLite.

    SQLite differs a lot from traditional DB systems, as it does not require any client/server model but is embedded in the PHP source itself. It does not require any user/password privileges, just read/write access to the database flat file.
    As it’s totally independent of external services, it’s very easy to integrate.

    The new DB solution has both strengths and weaknesses. Here’s a short list:

    Strengths:
    • Self contained, no server required
    • Setting up a new DB is easy, and does not require any intervention from server administrators.
    • If you have PHP5 installed, then you also have SQLite available. No additional packages are required to install to make it work.
    • It has very little “overhead”, and hence is as fast or faster than (the second fastest DB) MySQL for most types of ordinary queries.
    • It supports both a procedural and an OO approach. The OO interface makes for less code, and is in most cases faster than the procedural way.

    Weaknesses:
    • The lack of a server also limits it somehow. More advanced features like file locking, concurrency issues (multitasking, interleafing computations), persistent query caching, and scalability (handling large data volumes), are lacking. DB sharing across networks are only possible by granting access to the database file itself. This is much slower than sending queries through a network socket, and also less reliable.
    • It does not handle binary data natively. The data has to be encoded and decoded upon retrieval, which makes it binary unsafe.
    • As mentioned, transactional requests will lock the whole database instead of the affected row, making it unsuitable for systems dealing primarily with transactions.

    Recommended area of use:
    Web-hosting environments, private small scale use. (I.e. not dedicated servers like the ones banks, airlines, large e-commerce sites, etc use).

    In a traditional setup, the web host has to keep separate files holding credentials for DB access. DB’s are also outside the customers assigned storage space, and must hence be limited through other means. SQLite does not require DB server access, as it writes directly to files stored in the customers assigned web space. As long as you have read/write privileges to a file, you can manage the SQLite DB. All storage takes place within the assigned web space on the server.

    The typical web site has a lot of read requests, and little write requests. This is where SQLite excels. It is lightening fast when retrieving data, and supports all normal query types (adhering to the SQL92 standard). Meaning you can port your SQL knowledge to SQLite. You don’t need any additional training to start using it.

    There are some differences regarding access and communication. I will try to cover the most common next, but visit www.php.net for complete access to all features (there are too many to be covered in a tutorial).

    When communicating with the DB, you have two options (like most operations when talking PHP5), the procedural way and the OO way. I’ll list the most common transactions below, comparing the procedural syntax and the OO syntax. All later examples are OO style.

    (The below was set up in a table to compare one with the other, but since VBulletin doesn't support tables, I had to list one below the other. They're in their individual order though, so the second procedural way has its equivalent in the second row of the object oriented way.)


    Procedural
    1. $db = sqlite_open($table)
    2. Sqlite_close($db)
    3. $r = sqlite_query($db, $sql)
    4. $r = sqlite_query_array($db, $sql)
    5. $r = sqlite_query_unbuffered($db, $sql)
    6. Sqlite_fetch_array($r)
    7. Sqlite_fetch_single($r)
    8. $safe = sqlite_escape_string($s)
    9. $id = sqlite_last_insert_rowid($r)

    Object Oriented
    1. $db = new SQLiteDatabase($table)
    2. Unset($db)
    3. $r = $db->query($sql)
    4. $r = $db->arrayQuery($sql)
    5. $r = $db->unbufferedQuery($sql)
    6. $r = fetch()
    7. $r->fetchSingle()
    8. $safe = $db->escapeString($s)
    9. $id = $db->lastInsertRowid($r)

    To exemplify the most common use, I’ve created an example table (which requires the creation of a DB), and then an example of how to enter information and retrieve it. Obviously, I’m not going to use any time explaining SQL syntax, as this is adhering to the established standard (that you’re all familiar with). Focus will be on the communication part.


    Let’s say you want to create a simple table containing name and address. Your table will contain three columns; ID, NAME, and ADDRESS.

    On all DB connections you use, the syntax is: (pseudo-code) “open this(name) DB”.
    SQLite, opens the database if it exists, but more importantly, creates the DB if it can’t find a match!

    As of now, SQLite ignores the mode (access code) and defaults to 0666. You may therefore drop it when creating the DB. With all parameters present, the opening line belw would look like:
    $db=new SQLiteDatabase('demo.db', 0666, $sqliteerror);

    Furthermore, SQLite has even fewer datatypes than PHP. Everything is a string. If you define a column as INT, SQLite won't complain if you insert "James" into that column. It only matters if you are executing a "sort" query. If you tell SQLite that the data in a particular column is to be treated as integers, it wil sort correctly.
    Also the VARCHAR definition is surplus. It's only to hint that the column is supposed to hold text. It doesn't really matter to SQLite what you call it. It can be wise to use some standard naming conventions though, to help you remember what data types are supposed to be in the different columns.
    You can add the parameter PRIMARY KEY after the 'id' definition. If you do, SQLite will auto increment that column. Remember to add NULL as the first value in your VALUES() definition when you insert rows in a table with an auto incremented first column.

    The error handling syntax used in the query, consists of two parts:
    lastError() which returns a reference error number, and
    sqlite_error_string() which converts the error to a readable message.

    PHP Code:
    $db=new SQLiteDatabase("demo.db"); //Creates the DB if not found, and instantiates the object $db.
    $sql="CREATE TABLE test (
      id int,
      name varchar,
      address varchar
      )"
    ;

    $db->query($sql) or die (sqlite_error_string($db->lastError())); // see explanatory text later.

    $sql="INSERT INTO test  VALUES(‘1’, ‘Fred’, ‘codeStreet 2’) ";
    $db->query($sql) or die("sqlite insert error");
    unset(
    $db); // destroys the object ála mysql_close($dbhandle). Will be destroyed anyway as script ends. 
    Now you have created a table named test in the database demo. It contains one row.

    Next you want to retrieve the data:
    PHP Code:
    $db=new SQLiteDatabase("demo.db"); 

    $sql="select * from test";
    $res=$db->query($sql);

    //the below is just a demo of extracting column names in addition to data
    $num=$res->numFields(); //number of columns

    for($i=0$i<$num$i++)
    {
        echo 
    strtoupper($res->fieldName($i))." | "//capitalizes and prints all column names
    }

    echo 
    "<p>"//next comes the data

    //The below will extrat all rows from the table, and display them on separate lines.
    while($row=$res->fetch()) //fetch() is only one of many ways to retrieve data.
    {
        for(
    $i=0$i<$num$i++)
        {
            if(
    $i==($num-1))
            {
                print 
    $row[$i]."<br>\n";
            }
            else
            {            
                print 
    $row[$i]." | ";
            }
        }
    }

    unset(
    $db); 


    This was a superfical introduction to the fundementals of creating a DB, table, inserting and retrieving data.

    There are quite many more features to SQLite, that differs from MySQL.
    Visit php.net for all details.

    If anone have any specific requests, I can try to expand this tutorial.
    S. Rosland

  2.  


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 10:18 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com