1 / 12

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

london
Download Presentation

PHP and SQLite

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 <?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) { .......

  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 don’t 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

More Related