Section 14.3. SQLite


14.3. SQLite

SQLite is a fully functional relational database system that does not use the traditional client/server database architecture. For example, MySQL has a server running on a machine somewhere, and a client (in the form of PHP, in our examples) connects to that server to perform queries. SQLite, on the other hand, works on local files, with no database server requiredwhen you run queries using SQLite, they are translated into operations on the local files.

From PHP 5 onward, SQLite is bundled and enabled by default, which means that everyone, everywhere, will have it by default. If you are writing an application that needs a data store, you no longer need to worry whether they have Oracle or Microsoft SQL Server installed or, indeed, whether they have any database server installed at all.

14.3.1. Before You Begin

SQLite uses a file for every database you create, which means that it is very easy to keep track of your data, particularly if you want to back up and restore information. However, it also means that this file must be easily available, preferably localusing remote file systems, such as NFS, is not recommended.

There are some unique aspects to SQLite that you should be aware ofthe most important is its handling of field types. SQLite does not distinguish between data types beyond "string" and "number"CHAR(255), for example, is the same as VARCHAR(20), which is the same as TEXT, which makes it typeless like PHP. This boils down to "If your data type has CHAR, TEXT, BLOB, or CLOB in it, it is text; otherwise, it is a number." This is fuzzy matchingVARCHAR has "CHAR" in it; thus, it is considered to be a text field.

There is one exception to this state of affairs, and that is when you want an auto-incrementing primary key value. If you define a field as being INTEGER PRIMARY KEY, it must contain a 32-bit signed integerequivalent to an INT data type in MySQLand, if you do not fill this value when you insert a row, SQLite will automatically fill it with an integer one higher than the highest in there already. If the value is already at 2147483647, which is the highest number it can hold, SQLite will hand out random numbers. Note that the data type must be INTEGER and not INTINT will be treated as a normal number field.

Finally, because SQLite stores its data in files, it is not able to handle multiple simultaneous writes to the same table. Essentially, when a write query comes in, SQLite locks the database (a file), performs the write, then unlocks the fileduring the locked time, no other queries can write to that database. This is a problem if you want your database to scale, or if you are using a system that does not have a reliable file locking mechanism, such as NFS.

14.3.2. Getting Started

Working with SQLite is similar to working with other databases. The syntax is slightly different, and you invariably need to pass in an exact database connection with each call to the library; however, there should be no problem if you have already mastered another SQL dialect.

There's an object-oriented version of SQLite for people who like that sort of thing.


The four key functions to use are sqlite_open( ), sqlite_close( ), sqlite_query( ), and sqlite_fetch_array( ), and they work almost exactly like their MySQL equivalents. The connection function is sqlite_open( ), not sqlite_connect( ), reflecting the lack of client/server architecture.

Here is an example script:

     $dbconn = sqlite_open('phpdb');     if ($dbconn) {             sqlite_query($dbconn, "CREATE TABLE dogbreeds                     (Name VARCHAR(255), MaxAge INT);");             sqlite_query($dbconn, "INSERT INTO dogbreeds VALUES ('Doberman', 15)");             $result = sqlite_query($dbconn, "SELECT Name FROM dogbreeds");             var_dump(sqlite_fetch_array($result, SQLITE_ASSOC));     } else {             print "Connection to database failed!\n";     } 

Connecting to an SQLite database is simply a matter of providing the filename to use as the parameter to sqlite_open( ). Some programmers have adopted the convention of using the filename extension .sqlite for their databases, but you are free to do as you please, as this convention has yet to catch on.

After opening the database, you will notice that sending queries requires passing the database connection as the first parameter, with the query as the second parameter. The queries themselves are standard SQL, so you should be able to take your existing SQL skillset and apply it directly here. There is no sqlite_fetch_assoc( ) function at this time, so the sqlite_fetch_array( ) function is used, specifying SQLITE_ASSOC as parameter two. If you do not do this, sqlite_fetch_array( ) will return each field of data twiceonce with its numeric index, and again with its field name string index.

Other than the minor differences listed above, SQLite works much like MySQL. The advantage of absolute cross-platform compatibility, regardless of whether people have a database server running, makes SQLite a great tool to keep handy in your toolkit.

When calling sqlite_open( ), you can pass in :memory: as the filename to have SQLite create its database in memory. This is substantially faster than working with a disk, but it will be deleted when your script terminates.


14.3.3. Advanced Functions

There are three extra functions for SQLite that you are likely to find helpful. First, the equivalent function of mysql_insert_id( ) is sqlite_last_insert_rowid( ), which requires the connection resource as its only parameter. Creating auto-incrementing fields in SQLite requires you to declare them as "INTEGER PRIMARY KEY"the AUTO_INCREMENT keyword is not required. The sqlite_last_insert_rowid( ) function will return the auto-increment ID number that was used for the last INSERT query you sent.

Second, the functional equivalent of PEAR::DB's getOne( ) is sqlite_fetch_single( ). This will return the first column of the first row of the result of your query, and you pass the return value of sqlite_query( ) into sqlite_fetch_single( ) as its only parameter.

Finally, the function sqlite_array_query( ) is a very powerful function that returns an array of all the rows returned. For example:

     $dbconn = sqlite_open('phpdb');     if ($dbconn) {             // this assumes you created the dogbreeds table using the previous             script!             sqlite_query($dbconn, "INSERT INTO dogbreeds VALUES                     ('Poodle', 14)");             sqlite_query($dbconn, "INSERT INTO dogbreeds VALUES                     ('Jack Russell', 16)");             sqlite_query($dbconn, "INSERT INTO dogbreeds VALUES                     ('Yorkshire Terrier', 13)");             var_dump(sqlite_array_query($dbconn, "SELECT * FROM                     dogbreeds", SQLITE_ASSOC));     } else {             print "Connection to database failed!\n";     } 

The first three INSERT queries make the data more interesting. The key line is where sqlite_array_query( ) is called. The function basically works as a combination of sqlite_query( ) and repeated calls to sqlite_fetch_array( ), so it requires the database connection as parameter one, and the query to execute as parameter two. In the example, SQLITE_ASSOC is also passed in, as we would normally do when calling sqlite_fetch_array( ).

Here is the output that script generates, when used immediately after the script that created the dogbreeds table:

     array(4) {             [0]=>             array(2) {                     ["Name"]=>                     string(8) "Doberman"                     ["MaxAge"]=>                     string(2) "15"             }             [1]=>             array(2) {                     ["Name"]=>                     string(6) "Poodle"                     ["MaxAge"]=>                     string(2) "14"             }             [2]=>             array(2) {                     ["Name"]=>                     string(12) "Jack Russell"                     ["MaxAge"]=>                     string(2) "16"             }             [3]=>                     array(2) {                     ["Name"]=>                     string(17) "Yorkshire Terrier"                     ["MaxAge"]=>                     string(2) "13"             }     } 

Each row in the table became an element in the returned array value, and each element was, in fact, an array in its own right, containing the names and values of each of the fields of that array. Using sqlite_array_query( ) is a very fast, very optimized way to extract lots of data from your database with just one call.

14.3.4. Mixing SQLite and PHP

It is possible to make PHP and SQLite work together to filter data. For example, this next code creates a PHP function that gets used in an SQLite query:

     mysql_connect("localhost", "phpuser", "alm65z");     mysql_select_db("phpdb");     mysql_query("CREATE TABLE sqlite_test (ID INT NOT NULL AUTO_INCREMENT             PRIMARY KEY, Name VARCHAR(255));");     mysql_query("INSERT INTO sqlite_test (Name) VALUES ('Peter Hutchinson');");     mysql_query("INSERT INTO sqlite_test (Name) VALUES ('Jeanette Shieldes');");     $conn = sqlite_open("employees");     sqlite_query($conn, "CREATE TABLE employees (ID INTEGER NOT NULL PRIMARY KEY, Name     VARCHAR(255));");     sqlite_query($conn, "INSERT INTO employees (Name) VALUES ('James Fisher');");     sqlite_query($conn, "INSERT INTO employees (Name) VALUES ('Peter Hutchinson');");     sqlite_query($conn, "INSERT INTO employees (Name) VALUES ('Richard Hartis');");     function ExistsInBoth($name) {             $result = mysql_query("SELECT ID FROM sqlite_test WHERE Name = '$name';");             if (mysql_num_rows($result)) {                     return 1;             } else {                     return 0;             }     }     sqlite_create_function($conn, "EXISTS_IN_BOTH", "ExistsInBoth");     $query = sqlite_query($conn, "SELECT Name FROM employees WHERE EXISTS_IN_BOTH(Name)");     while($row = sqlite_fetch_array($query, SQLITE_ASSOC)) {             extract($row);             print "$Name is in both databases\n";     } 

The call to sqlite_create_function( ) takes an SQLite connection as its first parameter, the name you want to give the function inside SQLite as its second, and the actual PHP function name as its third.



PHP in a Nutshell
Ubuntu Unleashed
ISBN: 596100671
EAN: 2147483647
Year: 2003
Pages: 249

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net