Using Metadata – Martin Fowler
The most time spent working on a site under exhibition was the model or database layer. So before really getting into the rewrite I wanted to see what else was out there that could simplify this. Obviously I was turned to Fowler’s Patterns of Enterprise Application Architecture and ActiveRecord (rails), Hibernate (java) etc. One common element with every ORM solution I have run across is that they partially violate the DRY principle. Those that don’t, or that give you the option of completely defining your schema in a format native to the ORM are not expressive enough to use the full power offered by the database. This is when I remembered that PostgreSQL implements an extension to the SQL standard information schema that allows you to add an arbitrary comment to nearly any database object, tables and columns inclusive. After some poking around I found that MySQL supports the same, although it is not nearly as well documented. SQLite does not share this non-standard extension. So, my thought at current is to express whether relations are one-to-one, many-to-one or many-to-many directly in my table declarations. The only drawback I can see is a lack of portability, but when it comes to PHP, how many people use anything other than PostgreSQL or MySQL? SQLite should be used a great deal more, in my opinion, but in practice I do not believe it is. If at some point I must be portable to something other than the common case, well, in that event I guess I can always just do what all of the ORM’s are already doing.
References:
http://www.postgresql.org/docs/8.2/interactive/sql-comment.html
http://dev.mysql.com/doc/refman/5.0/en/information-schema.html
http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
If you want to programmatically determine the relationships between tables (many-to-many, one-to-many, etc), you can also query the constraints tables in your DB’s information schema. These tables contain metadata on all the constraints declared in your DB, and can be used to infer relationships.
For example, if table A has a foreign key into table B, then there exists a many-to-one relationship from A to B. If the source of this FK is a unique key within A, then there exists a one-to-one relationship. Of course, this method makes the assumption that the DB schema is “properly” crafted.
If DRY ORM is what you’re after, I wrote an extension to ActiveRecord called DrySQL that you may find interesting.
http://drysql.rubyforge.org/
Cheers.
– Bryan
Bryan, thanks for the input. I had a blog entry more recently ( http://evilcode.net/sjg/blog/2007/02/introspecting-postgresql-and-mysql.html ) in which I threw out the bottom of the rung entities in information_schema and some of the differences between MySQL and PostgreSQL. Introspection-based programmatic generation of relationships is most definitely the way to go, I plan to use comment fields only for hinting.
Congrats on DrySQL, it definitely looks like you are moving in the right direction on that front.