NOTE: Dave Thomas' book Programming Ruby (Pragmatic Programmers' Guide) was my bible during the development of this plug-in so I may have inadvertently borrowed some code from the book, and I most certainly borrowed ideas and coding conventions from the book. Thanks for writing such a great book Dave!

DrySQL v.0.1.0

The idea behind DrySQL is that if you have defined a schema on your database complete with keys, constraints, relationships, and behaviour, then you shouldn't need to re-define any of this in your application code.

DrySQL retrieves metadata from your DB's information schema, and uses this to identify keys, and generate associations and validations for your ActiveRecord::Base subclasses

For an overview of the features and some use cases, please visit the DrySQL project website: drysql.rubyforge.org

How Do I Install It?

Assuming that you have installed RubyGems, on the command line type the following:

gem install drysql

That's it!

How Does It Work?

  1. Loading the DrySQL gem


DrySQL simply extends ActiveRecord, so all you need to do is insert the following statement in your code somewhere where it will be processed before your application needs to use DrySQL's behaviour:

require_gem 'drysql'

Note that DrySQL depends on the ActiveRecord gem, which in turn depends on the ActiveSupport gem, so you will need to have these installed as well.

  1. Mapping classes to tables


DrySQL makes the assumption that your table name and class name will be compatible with Rails naming conventions, or you will specify the table name explicitly using set_table_name inside your class definition.

If your DB tables follow the Rails naming conventions, then you don't need to define classes for them at all. The first time you reference a non-existant class, DrySQL will generate the class for you.

eg. The first time you reference the non-existant class Employee in your Ruby code, DrySQL will generate the class ActiveRecord::Base::Employee in memory. If your DB table is called Employees, then Rails will map the Employee class to the Employees table once you instantiate an Employee object.

If you feel more comfortable defining your class explicitly, it can be as simple as this:

class Employee < ActiveRecord::Base end

If your Employee table is not called Employees, then you will need to define your class in your Ruby application, and call set_table_name inside your class definition:

class Employee < ActiveRecord::Base set_table_name “XYZ123” end

  1. Generating Keys, Associations, and Validations


The first time you instantiate an ActiveRecord::Base subclass (we'll continue to use Employee for these examples), DrySQL will intercept the instantiation, query the DB's information schema for the constraints and column metadata associated with your table, and use this information to identify the keys on the table, and generate associations and validations for the class.

Rather than assume that your primary key is called table_name_id, DrySQL queries the DB to find out exactly what your primary key column is called. The same is true of foreign keys. Thus, DrySQL fully supports your legacy DB tables.

DrySQL can currently generate the following associations: belongs_to has_one has_many has_many :through

Supposing that I find a bulletproof way to identify has_and_belongs_to_many associations from the information schema, I will add functionality for auto-generating these associations.

DrySQL can currently generate the following validations: validates_length_of validates_numericality_of validates_inclusion_of (for boolean columns only, CHECK constraints can be used to enhance this) validates_nullability_of

validates_nullability_of is a new validation introduced in DrySQL. Common convention is to use validates_presence_of on any column with a NOT NULL constraint. There are 2 holes in this approach: 1) For character columns this validation will throw an error if the value of the column is the empty string, even though the DB considers this a perfectly valid value 2) For columns that either have a default value specified or are auto-generated by the DB, we do not want to throw an error if the value is null validates_nullability_of throws an error only if the value is nil, no default value is specified for the column, and the column value is not auto-generated by the DB

DrySQL, by design, does not auto-generate validates_uniqueness_of, because I am not convinced about its usefulness. Validating records before querying the DB is useful because we can identify invalid data and avoid the cost of I/O by attempting to insert/update invalid data into the DB. validates_uniquess_of needs to query the DB in order to perform the validation, so I fail to see the advantage of using this application-side validation over just letting the DB perform the validation and having your app handle the duplicate key error thrown by the DB.

  1. A Note About the Unit Tests


I did not publish the tests with the first release of DrySQL because they are built on a clone of a corporate database, and I need to obfuscate them first. I will publish tests with the next release

Enjoy, and please give me your feedback!