Database abstraction, total OO refactoring, and more!!!

Alright guys, I can’t contain myself any longer. I have found an incredible framework called Propel (http://propel.phpdb.org), based on the Apache Turbine project, which is a complete system for generating an object model from a database, which can then be used with any supported database platform. They currently support the following DB’s:

• MySQL
• PostgreSQL
• SQLite
• MS SQL Server

And they are currently completing development of Oracle support.

It has PHP 4 and PHP 5 runtimes and can generate the object model for either target runtime, so we’d have to have separate packages for PHP 4 and PHP 5, but it does support both.

In addition, I spent the weekend setting up Propel and two related projects which I will be talking more about in the upcoming weeks, Phing, a build tool based on the industry-standard Java build tool Ant, and Creole which provides Propel with with many cool features, including automatic DB->XML schema generation, XML->SQL, and many other useful data transformation tools. Phing would be perfect for preparing automated builds that take care of everything from running unit tests, to generating/transferring bootstrap installer files, to transferring the builds to proper download areas. And it’s as easy as writing XML (or you can extend Phing with PHP classes, known as Tasks). Creole will be handy for doing things like automatically generating create and upgrade scripts for the DB, and for each platform.

I’m customizing the Propel runtime and some of the generated classes to take advantage of some MODx infrastructure, working on loading classes on demand for performance sake, and testing the in’s and out’s of the framework. I’ve already completed a proof of concept; here’s what I’ve done:

* reverse engineered the current 0.9.1 DB using Creole, generating an XML schema of the structure
* used Propel to generate a complete PHP 5 object model for our core persistent objects
* modified the PHP 5 runtime and generated classes to respect our include_path restrictions (e.g. prefixed paths with the base_path setting)
* introduced new functions in DocumentParser to initialize the Propel engine and load classes from the model on demand, just before using them (so the whole object model does not have to be loaded on every request)
* replaced all the DB calls in the DocumentParser class with the appropriate object API calls and have it working with the default site content with the PHP 5 runtime, at roughly the exact same performance as 0.9.1

Here’s what I have planned next:

* refactor the XML schema to provide proper foreign key relationships, cascading delete flags, and indexes, then regenerate the MODx object model so the proper class relationships are generated by Propel
* refactor the entire core, moving API method implementations to the appropriate classes, and turning the core API calls into wrappers for those implementations for backwards compatability
* repeat everything for the Propel PHP 4 runtime
* create a Phing build process to prepare the PHP 4 and PHP 5 packages of MODx for distribution
* create a Phing build task to generate the appropriate SQL files for database creation AND upgrades, for every supported Propel platform

And more. Victor and I have been talking about how this system will allow us to incorporate (automated) unit testing, and additional automation for the bootstrap installer system. I haven’t been this excited about something in quite a while; complete DB abstraction, an OO framework in seconds, and build automation all in one fell swoop.

Thoughts, comments, gasps, or whatever are appreciated.

Jason, outstanding stuff. If we’re running at current speeds with current API, then the new API/parser should hopefully show similar improvments.

Will this tool help us to migrate exsting sites to theoretically better and merged database structure where snippets/plugins and chunks/templates are merged? Will it help migrate/merge the users and still work with existing sites?

Oh... and KILLER work .

And finally, for folks that are using custom database tables and the DB APIs, how will this affect that, if it will... ?

Quote from: rthrash at Dec 07, 2005, 12:08 AM

Jason, outstanding stuff. If we’re running at current speeds with current API, then the new API/parser should hopefully show similar improvments.

Will this tool help us to migrate exsting sites to theoretically better and merged database structure where snippets/plugins and chunks/templates are merged? Will it help migrate/merge the users and still work with existing sites?

It will likely be faster and take less memory, as I can move the whole API to the appropriate objects in the system and only load them as they are needed. It will result in a few more includes, but from what I can tell, the SQL performance is the same and no significant increase in parser time can be seen either.

Re: migration, I certainly don’t have all the answers to this yet, but I imagine generating upgrade scripts from any SVN revision of our XML schema representation, which is used to generate both the SQL and the PHP classes, would be fairly easy, and probably something we could easily automate from the Phing build process.

Quote from: rthrash at Dec 07, 2005, 12:11 AM

And finally, for folks that are using custom database tables and the DB APIs, how will this affect that, if it will... ?

Well, the DBAPI will still work (though we’d probably want to think about how it might be made to use the Propel infrastructure to provide developers with the same features for their custom persistent objects as we have in our core objects), but as always, anyone who wrote SQL directly against a specific version of the MODx database will be at risk during any upgrades. But moving forward, if they can use the API when dealing with core persistent objects, they will have no further upgrade issues unless we make API functions incompatible, in which case, we’ll need to inform developers.

Hmmm,

I’ve looked at propel and I must say it’s a cool way of doing things but propel adds another complex layer to the system.


Example to simple add a record to the books table one would have to include 5 separate classes

BaseBookPeer
BaseBook
BookMapBuilder
BookPeer
Book

While it does support DB abstraction I’m concerned about the build process. Do we have to build classes for each supporting database?

IMO propel should be an extension to the DBAPI. It’s not very often that web developers needs to write to core tables but we can use propel (or something simplier) to allow web developers to add records to core tables.

I don’t believe we should ever use Propel inside the core at all. This layer of complexity will have an impact on performance. It will nt be noticable on small scale but it will be on large scale.

Propel is good for a new feature that I’ll be working on call Tables but it’s kinda an over kill IMO. The problem here is that propel does not easily support dynamic table creation since every table will have to be builded/converted to an object.

I would rather see something like:

$cms->db->getObject("Books");
or
$cms->db->getTable("Books");

This way developers can make use of propel objects or use the dbapi select() functions.

IMO I think it would be much easier to use extensions that were designed to work with MODx:



Just my two cents

Until I actually touch it and get a taste for it, I can’t really comment. Performance is one of my main concerns, as is the ease of ongoing maintenance.

Adding a lot of complexity (I still don’t really understand it, and I pretty much do as it stands now ... but that took about 6 months there too!), causes me to pause. 5 classes per DB table sounds like a boatload of extra overhead!

Given that though, some of the benefits it sounds like it would bring are incredible and very mature in their proven track record of making for a truly enterprise level of quality assurance to the project: unit tests and automated builds/deployments. Supporting multiple DBs is very, very cool too, even though I’d never use more than MySQL personally. I can see MS SQL getting jumped all over in Windows shops.

However if that DB abstraction is a sub-optimized least common denominator feature parity implementation, it might just loose some of it’s luster. I’d really like to see a stored procedures/triggers implmentation which is now supported in Postgres, MySQL, MS SQL Oracle and others. MySQL just introduced this to the table, so does Propel handle this now?

Most importantly, if developing snippets and applications becomes more difficult than it is now, I think we’re going to loose some users and potential userbase. Case in point: if it requires going to the class-based stuff that the MemberCheck snippet, then we’re in trouble. I could never have coded that, and it took many hours to get to work in the class implmentation. I personally (!) had the function version working in about an hour (granted it didn’t check the member groups). I still don’t follow "true/hardcore" OOP, and if MODx/Eto had required it to work, I’d never have stepped foot into the project. So I hope this isn’t the case here!

Thoughts?

So Raymond, you think it would be better to roll our own complete DB abstraction system rather than take advantage of one based on a tried and tested OO project (Propel was written by many of the authors of the Java-based Apache Turbine project)? I totally disagree; I think it would attract good developers to our project and provide exactly the kind of persistence abstraction, and consistent, simple object model that we need. I can understand this infinitely more than our current code base, because it is based on real OO design principles and patterns; in fact this is so close to what I had been sketching out myself to implement inside MODx, that I’m still poking myself to wake up from the dream.

In reality, loading the 5 classes has added no overhead to the parsing. Even running alongside all the existing code, because I’m only loading the classes as I use them. The build would require two packages to be created, one with the PHP 4 Propel runtime and MODx generated classes, and one with the PHP 5 Propel runtime and MODx generated classes.

Though it increases the complexity of the build process, it will actually reduce the level of complexity of the entire system, by a factor of 1000’s IMO. Instead of haphazard, inconsistent coding styles, procedural code that you can’t follow at all, we’d have very consistent API’s for each object, the API methods would be better logically grouped (by attaching them to the objects they are actually methods for, e.g. getTemplateVarOutput() would be on the SiteTemplateVar object), and you wouldnt have to load the entire API for every request. And loading these objects on demand is absolutely the way to go for optimal performance.

DB abstraction is a sub-optimized least common denominator feature parity implementation

Huh??!! Stored procedures and triggers are ok (though often overused, rather than carefully designed and implemented where most preactical), but you will need a build system like Phing to manage a project that would use platform-dependent procedures and triggers to produce the SQL create and update files for each platform; and find ways to emulate those when deploying to platforms that don’t support it.

Make programming snippets more difficult? Not hardly. And Managing change would be easier, though would require some automation to be completed up front, and some standard dev environments for the core team. The biggest benefit would be the consistent API to program against, attracting quality OO developers to, and keeping the hackers away from the project, which would mean more components, more community, and more attention from enterprise-level CMS consumers.

Victor is 100% behind this move and believes every one of the steps I’m taking and the benfits of it are exactly what we need. And after spending only a week with this framework, I really believe this is the only path we can take and stay competitive in the future CMS market. Rolling our own solution would be shooting ourselves in the foot (or dare I say, like IE in the browser standards market). Being true OO is the only hesitance I had about adopting Eto/MODx in the first place, cause there was no good true OO PHP CMS framework, so we may have conflicting motivations here.

Propel is good for a new feature that I’ll be working on call Tables but it’s kinda an over kill IMO. The problem here is that propel does not easily support dynamic table creation since every table will have to be builded/converted to an object.

This is somethign I wanted too, but my vision involves a module where users design an object model using a tree taxonomy, just like the site taxonomy, to define objects, their attributes, their behaviors, and their relationships. Then we can use the propel generation code to automatically generate the required classes/stubs and they have instant objects they can use in MODx to create complex, custom web applications. Or we could find a way to implement a generic class and store such details in a database. Either way is trivial to add with the Propel infrastructure.