Tuesday, November 27, 2007

Here's a thought... ActiveRecord in PL/SQL

With all the talk about putting logic in the DB and hiding SQL behind stored procedures, wouldn't it be really intuitive if Oracle simply provided a default ActiveRecord implementation for every table in the DB? So, instead of returning a vanilla record type, FOO%ROWTYPE would return an Oracle-generated Object type that had accessors/mutators (getters/setters) for all the columns in table FOO and methods to UpdateRecord and DeleteRecord along with a static method to AddRecord. If you want to avoid any collisions with existing syntax, we could simply have a new attribute- something like FOO%OBJECTTYPE.

My experience shows that most reluctance to encapsulate SQL behind PL/SQL programs stems from the fact that people actually have to write (and subsequently maintain!) that wrapper code. People just don't want to do the donkey work (write the same boilerplate code over-and-over agian for each Table). And personally, I don't think a code generator is very good in this case either. The generated code simply breaks down under maintenance (add/modify/drop column from table).

But, what if all that was done by the database?– CREATE the Object Type when a Table is CREATEd and propagate changes whenever the table is ALTERed; plus maintain the accessors (which could very well enforce the integrity constraints declared on the Table)!

It'd be a great way for the Oracle community to show some benevolence towards (lowly! :P) application developers who can't write PL/SQL even if their life depended on it, let alone SQL!

Also, if you left the <Table>%OBJECTTYPE as NOT FINAL, those who can write PL/SQL could extend it to add even more (business) logic in the database! Whopee and horaay!!!