Databases and Metadata

Following on from my previous post let’s take a closer look at the kinds of metadata that you might put into a database and how an Object-Relational Mapping layer (like Class::DBI, ActiveRecord or Hibernate) might make use of it. We’ll start with the most obvious stuff and work our way down to less common features.

List of Tables
Any database system will be able to give you a list of the tables contained in a given database. This means that given a database connection, an ORM will know which classes it needs to model.

List of Columns in a Table
Given the name of a table, the database will be able to give you a list of the columns contained in that table. The ORM can use this data to know the names of the attributes that it needs to create for each class.

Data Types of Columns
Usually, the same database query that gives you a list of the column names for a table will also tell you the data types for those columns. The ORM can use that information to carry out basic validation on the values given to the corresponding attributes.

Relationships
A database isn’t much use unless there are relationships between the tables. These relationships are modelled as primary keys and foreign keys. The ORM can query this information and use it to infer relationships between the classes that it is creating. Some databases also use these relationships to enforce the integrity of the data and will return an error if you try to transform the data in a way that breaks that integrity (for example, if you try to add a record for an order that references a customer which doesn’t already exist). The ORM should look for these errors and handle them appropriately.

Constraints
Many databases allow you to define constraints on the data that is stored in a column. For example a column containing a percentage might be constrained to only accept numb ers between 0 and 100. Or a date column might only accept dates after a given date. Or a string column might only accept a certain set of strings. This basically further constrains the datatypes that we saw above and the ORM should be able to use them in the same way.

Triggers
A trigger is a piece of procedural code that is run whenever a certain event occurs in the database. Most commonly they are run when a row of data in a table is inserted, updated or deleted. The trigger is given the data values before and after the update and can use these values to run further validation on the update or even to run further updates. In some cases the trigger can prevent the update from taking place. This code shouldn’t effect what the ORM does, but it needs to be aware of the potential return values and handle them appropriately, for example, realising when an update hasn’t taken place.

Stored Procedures
A stored procedure is a piece of procedural code which can be run at any time by any user connected to the database. Again, this should be largely outside of the jurisdiction of the ORM as stored procedures are usually not associated with a particular table (i.e. class) but it would be good if it provided a simple way to call stored procedures and retrieve any results.

Now, I don’t believe that any ORM has all of these features yet, but I think it’s a reasonable set of targets to aim at. you should be able to point your ORM at a database and a complete set of classes back. Having to define the relationships between the tables using a text file or in hand-crafted class definitions (as you do with ActiveRecord) is just a waste of time. We should expect powerful tools. And if we don’t get them, then we should help to improve the ones we have.

Which means, I suppose, that I should be helping to ensure that Class::DBI has all of those features.