aSqlite - a way to deal with SQLite databases

"For SQLite the whole philosophy of PHP is to have a really simple tool for users to use. PHP should be as simple as possible for everything. And SQLite fits perfectly into that. MySQL is getting more and more complex. We can easily use MySQL at Yahoo, but still, at the very low end, as you get more complex, adding transaction views and triggers, people have a hard time implementing solutions. SQLite is very nice in the sense it's just flat file management with an intelligent interface on top of it. The Web is inherently concurrent. You're going to have two concurrent requests that are both going to write to the file. Oops, then what? Then you have to lock one and the other one has to sit and wait on the lock. What if you forget to unlock it? Putting some sort of marshalling layer in between the Web application and the flat file is perfect. We don't really think of SQLite as an SQL database, it is an intelligent layer on top of a flat file. You can then take your SQL statements and migrate your application to a real SQL database at some point. The migration path should be quite simple."
(source : Interview with Rasmus Lerdorf by Tecnetra)

As I soon found out, working with SQLite databases in PHP 5 , it seemed a practical tool which could take away the burden of managing user accounts' access to the MySQL server and services. You simply were able to set up their own databases, SQLite-style.
With regards to portability though quite a lot of burdens showed up. Take for instance the following SQL statement :
CREATE TABLE nonsense (id INTEGER PRIMARY KEY, name VARCHAAAR(128), salary DOUBLE(12,2), employer TEXT)
This will perfectly work well in SQLite, but you might have noticed that VARCHAAAR is not very SQL92 compliant.

Taken the goals of SQLite as described by its creator, R. Richard Hipp, it has off course enormous potential to serve exactly the intended purpose.

Being who I am, I want to create a tool to manage SQLite databases with added restrictions to maintain protability and stay as close as possible to the intended purpose of portability.

The restrictions I enforce on myself (and you, the eventual user) for the moment are the following :

The roadplan is to extend this to SQLite version 3 in the near future,but first I want to have the following items dealt with :
  1. create, delete and vacuum databases : done
  2. create, delete and update tables : create and delete done, update needs to be done still
  3. create, update and delete indexes : needs to be done
  4. browse, update and delete data in a table : browse and delete done, update needs to be done
  5. have a query interface on the database : done

Please download the project from the SVN repository. This is currently the only place which holds the latest files.

(latest update : 2006-10-03)