PHP and SQLite

1
PHP and SQLite

• Using SQLite with PHP
• By Rock Mutchler

2
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

3
What is SQLite ?

• SQLite.org definition
• SQLite is a small C library that implements a
  self-contained, embeddable, zero-configuration
  SQL database engine.

4
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

5
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

6
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

7
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.
8
Basic SQLite connection
lt?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) gt 0)
.......
9
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 dont have to be refreshed from other
  / remote external sources a lot

10
Other Examples

• See code..

11
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

12
Other Resources

• http//sqlite.org/
• http//www.php.net/SQLite
• http//devzone.zend.com/node/view/id/644
• http//devzone.zend.com/node/view/id/645
• http//devzone.zend.com/node/view/id/863