All Your Database Are Belong To Me

Merits of Defining your DB Schema in your Application?

2007-08-25T16:14:00.000-04:00

Late last year I developed and released a Ruby plugin called DrySQL that aims to eliminate any traces of DB schema re-definition in your application. The philosophy behind this effort is that the DB is indeed the place that table structures, constraints, and relationships should be defined, and that it is redundant to re-define any of these artifacts in an application or anywhere else.

While my colleagues in the enterprise space generally shared my philosophy, I have heard some suggest that DrySQL's approach "sounded backwards" and I know of at least one project out there that aims to do exactly the opposite of what DrySQL does: it aims to support defining the DB schema entirely on the application side rather than on the DB.

I don't yet understand the attraction of this approach, but I'll be interested to hear the opinions of those who support it. I have difficulty grasping the logic behind ignoring established best practices, no matter how small your business is, but I suppose that just about anything could make sense given the right (or wrong) set of requirements and restrictions.

In considering my case for the DB schema living on the DB and nowhere else, my goal is to satisfy 2 main concerns.

1) Controlling The Cost of Change

To maximally reduce the cost of change in an environment consisting of one or more applications interacting with one or more databases, each database schema should be defined in one place only. If the schema is defined in more than one place, schema changes become more difficult (and thus more unattractive) to perform, as they must be cascaded across multiple definitions. If synchronizing multiple definitions requires human intervention, this approach is obviously prone to human error, not to mention tedium and annoyance. This situation is described as "inviting a maintenance nightmare" in The Pragmatic Programmer. Minimizing barriers to change is important to me because I want my software infrastructure to support evolution, and even encourage it.

2) Maintaining Data Integrity

Given that the DB schema is to live in one place only, what place can guarantee the integrity of your data? Only the database itself. There are no back doors through the constraints defined on your database (except perhaps a bug in your DBMS if you are incredibly unlucky). Constraints defined elsewhere cannot be guaranteed to be enforced before data is written to your DB. Even in an environment where only one application interacts with your DB, it would be naive to think that your data would be safe without constraints on the DB. All it takes is one developer or tester (maybe even you?) to take a shortcut, execute a few "harmless" SQL queries through your database's command line interface (or any other interface for that matter), and your data is corrupted. The worst part of all is that the corruption might not be obvious immediately. Sure, an adequate backup schedule and a DB with some decent rollback/rollforward functionality will let you re-stabilize your data, but why pass up a frontline data protection mechanism that is so effective and accessible?

If you have an opinion on this subject -- particularly if it differs from mine -- please share it with me.

DB2 iSeries Adapter for Rails

2007-02-01T16:29:00.000-05:00

I recently released a new version of DrySQL (a Ruby plug-in) that supports DB2 iSeries. I didn't feel it was appropriate to post this on IBM's DB2 Rails Forum, so hopefully people who are interested in trying out this functionality will find this post.

iSeries is not currently supported by IBM's DB2 Rails adapter, so I created extensions to the IBM adapter and released them as part of DrySQL. As far as I know, this is the only generally available iSeries adapter for Ruby at the moment. Those who do not care to use the DrySQL plug-in can simply unpack the gem, pull out the DB2 adapter extensions, and use them however they wish to. I expect to add the code to the DrySQL SVN repository on RubyForge shortly, which should make this easier.

Note that you will need to install DB2 Connect and the IBM DB2 Rails adapter in order to use this functionality.

You can find more information about this on the DrySQL homepage. If anyone needs help getting it up and running or wants to report bugs, please reply to this post and/or post to the DrySQL forums on RubyForge.

Update (May 29, 2007):
IBM's ibm_db adapter now supports iSeries, so I have removed the custom support from DrySQL. The latest code from the DrySQL SVN repository on RubyForge works correctly with the ibm_db adapter.

Multiple Cascade Paths Error in SQL Server

2006-11-17T14:12:00.000-05:00

Working on DrySQL has given me an opportunity to learn about the subtle differences between many DBMSs.

As my project is designed to run on a number of DBMSs, I crafted a reasonably complex test DB schema that I create on each DBMS in order to run my unit tests.

Though my test schema can be created without error on MySQL, PostgreSQL, and DB2, when I attempted to create it on SQL Server Express 2005 recently, it errored on the attempted creation of a FOREIGN KEY constraint with cascaded deletes and updates:

Server: Msg 1785, Level 16, State 1, Line 1 Introducing FOREIGN KEY constraint 'PAYMT_APP_FK2' on table 'PAYMENT_APPLICATION' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Server: Msg 1750, Level 16, State 1, Line 1 Could not create constraint. See previous errors.

I'll explain the error using the following table layout.

Note that all the FOREIGN KEYs were created with cascaded delete and update options.

The constraint definition that produced the error above was in my CREATE TABLE statement for the PAYMENT_APPLICATION table.

[PAYMENT_APPLICATION.BILL_ID...]FOREIGN KEY REFERENCES MONTHLY_BILL.ID CASCADE DELETE CASCADE UPDATE

According to Microsoft the above error occurs because "a table cannot appear more than one time in a list of all the cascading referential actions that are started by either a DELETE or an UPDATE statement". So, my constraint definition produces an error because the cascading delete path for INVOICE would like this if the constraint were defined:

My PAYMENT_APPLICATION table will appear twice on INVOICE's cascading delete/update path:

PAYMENT_APPLICATION has an FK reference to INVOICE with cascading delete/update options

PAYMENT_APPLICATION has an FK reference to MONTHLY_BILL with cascading delete/update options, and MONTHLY_BILL has an FK reference to INVOICE with cascading delete/update options

As I mentioned earlier, SQL Server stands alone (to my knowledge) as the only DBMS that considers such constraints to be illegal. A simple solution to my problem would be to remove the cascade options from my PAYMENT_APPLICATION-->INVOICE Foreign Key constraint. The deletion/update of a referenced INVOICE or MONTHLY_BILL will still trigger a cascade onto the related PAYMENT_APPLICATION record, which is ultimately the behaviour that I want.

I can still create a DB schema that meets my needs in SQL Server, but it's annoying that I need to define my constraints differently than I do with other databases (although fairly typical of Microsoft software). I can't help wondering if this is a feature or a limitation.

If anyone else has an opinion about this, I'll be interested to hear it.
...And if anyone else experiences this error and struggles to figure out why, hopefully you find this explanantion helpful.

Generating ActiveRecord Data Validations Automatically

2006-11-07T13:13:00.000-05:00

Having recently released DrySQL, which automatically generates validations for ActiveRecord data classes that enforce the constraints declared on your DB, I thought it might be useful to start a discussion about how this works (or should work).

My (possibly naive) belief is that the value of application-side validations that enforce DB constraints is twofold:

Allows the application to provide immediate feedback to UI/forms/whatever about validity of input without the performance overhead of querying the DB

Avoids wasting application and DB cycles trying to save/update invalid records

That said, let's look at the ActiveRecord validation methods, how they apply to enforcing DB constraints, and how DrySQL generates them automatically.

validates_uniqueness_of
I don't understand how this validation is useful, but will be interested to hear others' opinions about it. To validate that a particular value is unique in a particular table requires a DB query. Why do this on the application side rather than just letting the DB perform the validation and handling the duplicate key error your DB returns? This validation can be generated automatically based on the unique key information in the DB's information schema, but I'm not sure that it should be.

validates_length_of
validates_numericality_of
These validations can be generated automatically by querying the information schema's columns table. The columns table contains the restrictions that were declared for the column when it was defined.

validates_inclusion_of
validates_exclusion_of
These validations can be generated automatically for databases that support check constraints, but DrySQL does not yet implement this. Currently, validates_inclusion_of is generated automatically only for boolean columns since we know the value must be in the set [true, false].

validates_presence_of
This validation can be generated automatically by querying the information schema's columns table and checking the column's NOT NULL constraint. Unfortunately, the implementation of this validation in ActiveRecord does not accurately mirror the DB's NOT NULL constraint. In situations where the column contains character data and has the NOT NULL constraint set, validates_presence_of will reject an empty string value even though the DB would consider this value to be valid. DrySQL does not generate this validation, but rather generates a new validation, validates_nullability_of, where appropriate.

validates_nullability_of
This constraint is new, and was developed as part of DrySQL. It mirrors your DB's handling of NOT NULL constraints. This validation will reject a null value unless the column is autogenerated or has a default value specified, because your DB wouldn't reject a NULL in these situations either.

DrySQL: Answering the Challenge

2006-11-04T00:24:00.000-05:00

After developing DrySQL to support some DB tooling at the financial organization that employs me, I was happy to learn that the functionality provided by DrySQL is actually the subject of Dave Thomas's (of Pragmatic fame) challenge to the Rails community at RailsConf 2006.

The video of the keynote is available here.

Leon Katsnelson, who is involved with the development of the official IBM Rails DB2 adapter, has also expressed the desire for this functionality on the DB2onRails blog here.

Leon and Dave, if you ever happen to visit my blog or come across DrySQL somehow, I will be interested to hear your feedback about it.

Introducing DrySQL

2006-11-03T18:28:00.000-05:00

After complaining about Object-Relational Mapping frameworks for years, I finally decided to do something about it.

The Problem
My primary problem with today's ORM frameworks is that I end up defining my DB schema twice: once on my DB itself, and once again in my application either in class definitions or ORM config files. Obviously, if I make structural changes to my DB I need to make corresponding changes in my application code or config files. I know that there are round-trip engineering solutions out there, as I mentioned in my previous ORM rant, but I have yet to come across a solution that I consider clean, efficient, simple, or remotely DRY (I would be interested in hearing about some though).

The Solution: ActiveRecord?
After experimenting with ActiveRecord and Ruby, I became convinced that I was onto something.

Mapping Classes to Tables & Instvars to Columns
ActiveRecord does the work of mapping classes to tables and instance variables to columns dynamically. If I change the columns in my table, my ActiveRecord-enabled app picks up these changes on restart. This is a step in the right direction (for my needs).

Associations
ActiveRecord also provides support for developer-specified associations, which are basically smart accessor methods on your class that will retrieve associated objects through join queries.

Validations
Lastly, ActiveRecord provides support for developer-specified validations, which are basically application-side enforcement of DB constraints. These are useful for providing feedback to a UI without querying the DB (and taking a performance hit for DB I/O, and wasting DB cycles).

What's Missing?
ActiveRecord is fantastic (I might go so far as to call it groundbreaking), but unfortunately it doesn't quite meet my needs. AR makes assumptions about naming conventions for tables, primary keys, foreign keys, and breaks down in numerous situations when the DB schema is complex.
AR does provide support for explicitly declaring my table names, keys, etc., but I have a fundamental problem with declaring these things in my application code when they are already declared in my DB schema. As well, I don't want to write code to specify relationships between DB records or constraints on them, as I have already done this on my DB itself.

DrySQL
DrySQL is an extension to ActiveRecord that provides a truly DRY Object-Relational Mapping solution. As long as DrySQL is able to map your class to a table (either using AR naming conventions, or a call to set_table_name inside your class definition), you need not define any properties of your table. DrySQL will query the DB's information schema and automatically identify keys & constraints, and generate your class's associations and validations. It then delegates the instvar-column mapping duties to ActiveRecord. The result is that all the Object-Relational Mapping logic is abstracted away from your application, and is completely dynamic. Any changes you make to the structure and behaviour of your DB are dynamically picked up by your application at startup.

DrySQL doesn't make assumptions about your naming conventions, or the nature of your application (Rails web app, Ruby desktop, etc). As a result, "legacy" DB tables can get the benefit of DrySQL's ORM magic, and DrySQL can be used in any Ruby application.

If you do have the freedom to follow ActiveRecord's naming conventions in your DB, there is no need to even define your data classes. DrySQL will generate them in memory the first time you reference them. And then it will generate the associations and validations, and identify the keys and constraints the first time you create an instance of your class.

What this means is that you can ditch all the code you write today to map, describe, and constrain DB data in your application.

I will dig into the gory details of how DrySQL works in subsequent posts, but in the meantime please check out the DrySQL project homepage on RubyForge for more details about the features of DrySQL, how to use it, and how to download it.

Some Interesting MySQL facts

2006-10-24T10:11:00.000-04:00

I've been doing a lot of research into the internals of MySQL in order to develop DrySQL, and here are some useful bits of information that I came across:

1) Check constraints are not supported in the latest version of MySQL (5.1.x). The syntax for check constraints is supported, but any check constraints in your alter/create table syntax are just ignored.
Official MySQL documentation.

2) The REFERENTIAL_CONSTRAINTS table was added to the INFORMATION_SCHEMA database as of MySQL 5.1.10.
Bug Report/Feature Request
Official MySQL documentation

This is in compliance with the ISO SQL:2003 standard. This table is useful to those who want information about the "ON UPDATE" and "ON DELETE" rules associated with a table constraint. Currently, the only solution for retrieving this info is to parse the SHOW CREATE TABLE output, which is a pretty ugly hack and a performance hit compared to retrieving the constraint info directly from the INFORMATION_SCHEMA DB.

3) MySQL creates implicit default values for columns if no default is specified. If the column is nullable, then MySQL sets the default value to NULL. If the column is not nullable, the default is value is set the MySQL's implicit default value for that column type (check this link for details about implicit defaults).

Suppose I create the following table:

CREATE TABLE TEST (ID CHAR(11) NOT NULL)

If I query the information schema's COLUMNS table to find out whether a default value has been specified for the ID column in the TEST table, MySQL will return to me the empty string as the default value. Does this mean that the empty string was defined as the default value for this column, or that no default was specified at all? Unfortunately, there is no way to make this determination in the latest version of MySQL, which is a pain for those of us developing DB tools against MySQL.

Errata: As of version 5.0.2, MySQL no longer creates implicit default values when no default is specified

4) Roland Bouman created an excellent clickable diagram of the MySQL Information Schema, which can be found here.

ORM Frameworks...the horror! (And a solution)

2006-10-22T13:04:00.000-04:00

I've used a lot of Object-Relational Mapping frameworks over the years in the enterprise space. They're supposed to make it easier to objectify relational data, and they do. However, they all seem to force me to contravene one of the most important principles (IMO) of software development: Don't Repeat Yourself.

In my experience in the enterprise space, a database schema is carefully crafted to ensure data integrity. The database can then be used by a number of applications, none of whom need to worry about data integrity, right? Then why, after I define referential constraints, unique constraints, check constraints, not-null conditions, and default values in my DB schema, do I need to define many of these again in a config file used by my ORM framework? What happens if I change the structure of my DB?

Hibernate
Hibernate is the ORM framework I have used most often in the workplace, and it is great in a number of areas. Handling DB table changes in a dynamic way is not one of them. If I change my underlying DB structure, I need to change associated config files, re-generate data classes, re-build and re-deploy my app(s). I may be jaded from my experience using Smalltalk, but I find this lack of dynamicism unacceptable. I'll admit that the Smalltalk ORM frameworks I've used are no better (worse, in fact), but I can see that the language itself allows for a much more dynamic framework to be built. I'm not sure that's the case with Java, but would love to hear (politely, of course) that I'm wrong (yes, I've heard of Trails, but I'm not sold on it at present).

There are a number of tools available that support some degree of round-trip engineering with Hibernate. XDoclet, MiddleGen, and even the Hibernate tools package can be very useful for autogenerating code and config files. However, I personally don't want to have to use 4 different frameworks to support round-trip engineering. And I don't want to have to re-build my app after a DB table change.

Ruby on Rails
So after complaining about these sorts of problems for awhile (read: years), I decided to work on a solution. I've been experimenting with Ruby and ActiveRecord (the part of the Rails framework that interfaces with databases) for a few months now. Ruby has the dynamic, reflective nature of Smalltalk, which won me over immediately. And ActiveRecord sure makes ORM a lot more simple and pleasant than most other frameworks. It is not without weaknesses, though.

On any given table, I can change the names of the columns, remove/add columns, and my Rails app will handle these changes without requiring changes to the code. I just need to re-start my app. However, ActiveRecord uses naming conventions to infer properties of tables, and even table names themselves. For example, unless I specifically call set_primary_key in my class definition, ActiveRecord will assume that the primary key column for my Invoices table is invoice_id. Thankfully, I can override such assumptions in my class definitions, but I shouldn't have to. My primary key is defined in my database schema in the form of a table constraint, and can be retrieved from the information_schema tables (or system catalogs, or whatever terminology applies to the metadata schema in your DB of choice).

To summarize my long-winded rant:

1) I personally can gain a lot of productivity from having a very dynamic, reflective ORM framework that doesn't require me to define my DB schema anywhere but in my DB itself.

2) I have found that Ruby/Rails/ActiveRecord comes closest to giving me what I need, but could really use some enhancements to better support legacy DB tables, and really use the DB schema to its full potential

A Solution
I have nearly completed a solution to this "problem" in the form of a Ruby Gem called DrySQL that is an extension to ActiveRecord. I will explain DrySQL fully in a subsequent post, but put simply your data class can look like this:

class Invoice
end

--> or if your DB table isn't named Invoices, your class would look like this:

class Invoice
set_table_name "XYZ123"
end

You do not need to specify primary/foreign keys, associations, or validations. DrySQL will retrieve all your table's constraints and metadata from the DB, and generate the necessary logic in your Ruby app.

This is extremely useful to me, and I hope that others will find it useful as well.