Check out
http://www.wonderlandlabs.com/modules/wiwimod/index.php?page=CarpenterSqlApi&back=WebProduction for self-writing mySQL classes. PHP 5.0 required.
*
Dynamic Class Instantiation. Every time you change the database, Carpenter rewrites the core table and record classes "on the fly". Carpenter's two tier class base allows for a "superclass" over each "data class" that can be customized, so you don't lose custom code when the core class is overwritten. (you may have to rewrite custom code if your field names change, but that is pretty much unavoidable.)
*
Complete encapsulation of SQL I/O. Records are created with !!MyTable!!->new_record(), saved with a simple save() (that either inserts or updates records) and can be loaded based on criteria with a !!MyTable!!->get_record($pCriteria) call. I rely a lot on XOOPS' excellent criteria class, which is a very flexible way to define a field based query, but straight text based SQL criteria work as well.
*
Guaranteed uniqueness of record objects - as all record are created and loaded through the Table object, there is a 1:1 correlation on any given execution cycle between records and their representative objects. As far as I know it is impossible for one context to create, alter and save a record object while another context has a second object representing that same object with different field values. There is NOT an observbable pattern at work here; just a factory that registers all returned record objects.
*
Utility relationship helpers: Extensions allow for magic methods to get child records or parent records or other associative data without having to manually add associative methods to each record or table class. Just add the appropriate relationship extension to a table superclass and your record class will magically acquire extra methods to facilitate relationships.
*
Direct, filtered field properties: because the fields leverage the 5.0 "Magic" methods, you can refer to them by name. The table class will automatically filter field values based on field type, length, etc. in the background.
*
Dynamic loading of table and record classes: Only the classes needed for any given round of execution are loaded in real time. Even though Carpenter generates code for every table in your database, the core database class allows you to load in real time the table and record classes you need without any include() code required on your part. This is again through the PHP 5.0 system.
*
Allowance for un-indexed tables: Through using pre_save() code you can manually insert primary key values for records on the fly, that have not been set to auto-increment. This allows for instance SugarCRM-style indexes to be generated based on timestamps or what not for parallel databases or tables that are likely to hold huge numbers of records.
*
Optional array-based retrieval of data: the core database allows for criteria based SQL calls that return arrays, single values, etc. so that you do not have to instantiate objects if you want to return a vast number of records, or an array of results with only limited fields etc. This is useful in reports or other situations where read-only, faster results are more important than getting your data in object form.
Note that Carpenter integrates with XOOPS' excellent Criteria system for writing queries. You can pass text based criteria but Carpenter also accepts Criteria objects in record retrival.
