Transcript PHP and SQLite

PHP and SQLite
Using SQLite with PHP
By: Rock Mutchler
SQLite in PHP
• What is SQLite ?
• How do I get SQLite for PHP ?
• A Basic PHP Examples
• Where would I use SQLite ?
• Other Examples
• Some Performance Ideas
What is SQLite ?
• SQLite.org definition
– SQLite is a small C library that
implements a self-contained,
embeddable, zero-configuration SQL
database engine.
Features of SQLite
• No configuration
• Implements most of SQL92
– Things not included
• Foreign key constraints (not enforced)
• Complete trigger support (missing some sub
features)
• Some Alter table commands are omitted
• Nested transactions (single level only)
• RIGHT OUTER JOIN or FULL OUTER JOIN are not
implemented
• Views are read-only
• No Grant and Revoke, normal file access is used
Features of SQLite, cont..
• A database is a single file
• Supports databases up to 2
terabytes in size
• Sizes of strings and BLOBs limited
only by available memory.
• ACID
• Very small code footprint
Item to Keep in Mind
• Type less Fields
– Fields in an SQLite database need not be
associated with a specific type, and even if
they are, you can still insert values of
different types into them. (do application
level type checking)
• Database is a single file
– This means writes are database locking
How do I get SQLite for
PHP ?
• SQLite 2
– Comes with PHP5
– PECL extension for PHP4
• SQLite 3
– Only supported in PHP by using the
PDO library
*note: SQLite 3 can read SQLite 2 databases, but in doing so it
converts the database to SQLite 3, making it unusable by SQLite 2.
Basic SQLite connection
<?php
// set path of database file
define('SQLITE_DB', '/usr/local/SQLite/store.db');
// open database file
$handle = sqlite_open(SQLITE_DB);
// the query
$query = "SELECT name FROM users";
// execute query
$result = sqlite_query($handle, $query);
// if rows exist
if (sqlite_num_rows($result) > 0) {
.......
Where to use SQLite
• Hosting where other solutions are
not available.
• Prototyping, using PDO
• High read systems, with low writes
– As a caching mechanism
– Items that don’t have to be refreshed
from other / remote external sources
a lot
Other Examples
• See code…..
Some Performance Ideas
• Use your indexes!
• Performing several updates to the db
– Bundle them into a single transaction
(single write, instead of many, much faster)
• PRAGMA
– Cache_size, if your performing a number of
updates and deletes, setting this high can
help speed things up
• in-memory database
– This is done in PDO by specifying the path
as ‘:memory:’
• Run the VACUUM
Other Resources
• http://sqlite.org/
• http://www.php.net/SQLite
• http://devzone.zend.com/node/vie
w/id/644
• http://devzone.zend.com/node/vie
w/id/645
• http://devzone.zend.com/node/vie
w/id/863