SQLite as a PHP Database

Webphotations is the first PHP webapp project where I used SQLite as the database for the website. I have used SQLite for some embedded apps, however.  Normally, I stick with MySQL or Postgresql for PHP.  I decided to go with SQLite because of how few the number of concurrent users this app will have.  Given the fact that this app is designed for "mini" crowds, there isn't a need for an industrial database.  Since there isn't a need, I decided to take advantage of SQLite's ease of setup.  To install Webphotations, you have to create a simple PHP config file.  That config file specifies the path to the (soon to be created) database file and the path to the photographs.  On first hit, the database, the schema and the initial data are populated.

I reused a programming pattern that I have used for  SQLite with C and for Java apps connecting to various database types: I bundle my creation and CRUD operations as .sql files.  I use the PDO abstraction layer to create a database handler.  If the schema does not exist, I pass in the return value of file_get_contents( 'sql/init.sql' ) to PDO::exec() to add the schema.  For each CRUD operation, I pass in the return value of file_get_contents( 'sql/createUser.sql' ) into PDO::prepare(), then call PDOStatement::execute() with an array of values to parameter bind in.  I always use '?' characters for the parameter binding because that is the universal parameter bind stub.  Some databases support naming the parameter bind using ':1' and ':2'.  Although this is superior to '?', it is also not as supported.  Although I hardcode the use of SQLite, all the CRUD .sql is ANSI SQL compliant, so switching out the database would require very little effort.  Even though I have no plans of ever switching it out, I have drilled it into myself that I must maintain compatibility.

This is far easier than creating a new daemon, and acting as a database administrator.  No Muss, no fuss.  This app should be easy to install and start using on a home server.  This project is part of my personal cloud initiative.