DB_Table goes “beta” in new 0.22.0 release

I just released DB_Table 0.22.0 beta. This is the first new release since July 7, and the first beta release. It has been pretty well tested against MySQL and PostgreSQL, but should work with Oracle, MS-SQL, and SQLite as well. I’d be very happy if MS-SQL or Oracle users could provide feedback on how well DB_Table works on those systems; please email me if you would like to provide a report.

The ideas behind DB_Table are simple: embed your column definitions and baseline queries within the PHP class. Then you can call select(‘myQuery’) and get your results, or call insert($myColumns) to insert a new row, and so on. Becuase the class knows the column definitions, it can validate the new data before even connecting to the database; you can extend the validation method to cover all kinds of data. This also lets you automatically create a form based on the column definitions with HTML_QuickForm. DB_Table automates table creation; if a table does not exist in the database when you call a DB_Table object, DB_Table can create it right then, along with indexes — all because the column definitions are embedded in the class.

DB_Table also provides a poor-man’s version of data type abstraction. This element is somewhat controversial. We all know that different database systems keep date and time data in different formats, and it becomes an … interesting … task to create portable SQL statements that will work properly against those different systems. Metabase and its cousin MDB do this by providing translation functions to convert the native RDBMS format to a common format on retrieval and insert/update, but they are not exactly fully automated.

The DB_Table abstraction method (such as it is) instead forces the database to store the date and time as fixed-length strings in ISO-standard format. While this is going to give most database administrators a case of the screaming rants, it is a very workable solution for low- and mid-range applications where portability, not 100% optimization and efficiency among multiple systems, is the paramount concern. This is particularly good for those of us who write applications intended for distribution and need to know for sure that the app will work on a wide range of databases (with minimal effort). For example, YaWiki uses DB_Table to great effect.

You can read the full DB_Table documentation here.

Are you stuck with a legacy PHP application? You should buy my book because it gives you a step-by-step guide to improving your codebase, all while keeping it running the whole time.

2 thoughts on “DB_Table goes “beta” in new 0.22.0 release

  1. The DB_Table abstraction method .. forces the database to store the date and time as fixed-length strings in ISO-standard format.

    Does this prevent querying (filtering and sorting) by date/time?
    I imagine this abstraction severely limits the ability to use the date/time functionality provided by many RDBMSs.

  2. Hi, John —

    Not as much as you might think. E.g., date is stored as ‘2004-10-28’. It always sorts properly, because it’s alphabetically in order from earliest year to latest. If you want only a certain year, you query for (“datecol >= ‘2004’ and datecol <= ‘2005’”). The only strict limitation I have found is that you cannot do date-math inside the database; you have to that beforehand and put it in the query explicitly, or pull the data and then massage it afterwards. Of course, this take more space than the native date format, but it eludes any restrictions on date ranges (you can store from 0000-01-01 to 9999-12-31).

    If you need to use complex native date math in your SQL queries, well, you’re probably not looking for database abstraction in the first place; those date-math commands vary from system to system.

Leave a Reply

Your email address will not be published. Required fields are marked *