Just entered the
zend coding contest, writing a
SQLite admin module. Learned some interesting stuff about SQLite, and tried to make the code as clean as possible.
SQLite actually has some quite useful features. Supports triggers, subselects, views, all of which are features I've never attempted to use in MySQL. Also, a database is stored in a single file, which I kind of like. Definitely made my admin tool easier, because you can just upload your database file to the app, then download it when you're done editing.
One limitation of SQLite is that it doesn't support the
ALTER TABLE syntax. So, to make structural changes to a table, you end up needing to create a temp table with the contents of your table, add the new table, and bring over the data from the old table. Error-prone? Quite possibly. I created a PHP SQLiteTable class to represent a table in SQLite, which allows you to add or modify the table name and columns, then apply the changes, which does everything for you. It ends up being quite nice. Since the table and columns keep track of what their old names are, re-importing the data from the tmp table is easy.
Also refined my sql hitlist class for this project. SQLHitlist is a class that accepts at minimum an SQL query, and displays the results as an HTML table. Simple, right? Where things get interesting is when you want clickable table headers to sort on the keys, or want to page through batches on a very large found set, or apply custom formatting to table cells. Paging is difficult, because if you ust a LIMIT clause in your query, you have no way of knowing how many total records would have been returned without the LIMIT (although there is a MySQL feature that does provide this in MySQL 4).
As for the actual UI, that part could use a little cleanup, but I finally standardized a way to handle forms. A single form page is broken into three parts:
[perform action] analyze the request, and call the appropriate functions. This part should be kept nice and short, and readable.
[functions] Various page-specific functions which implement the functionality in the first section. If an action is successful, do a redirect to the appropriate page.
[output] Display the form output, assuming no redirect was invoked in the perform action part of the page.
update
Well, I didn't run in E_STRICT mode. Never even heard of it! SQLite Admin Module is officially a dead project now. Yet another orphaned php codebase, waiting for... for... for another good idea.
