It's All Geek to Me!

« How does Playboy support software development? | Main | Vishy's Indian English Dictionary: load shedding »

December 18, 2005

HOWTO: Get Ruby on Rails to map nonstandard column types correctly into your model

I have been trying my hand at Ruby on Rails, the latest and greatest Web development platform to hit the block. Ruby on Rails is often breathlessly praised by the bloggerati for its 'configuration by convention' philosophy and has quickly become a Web 2.0 buzzword. Heck, some even tout it as the beginnings of a hitherto unspoken conversation about how to keep programmers happy. The Net is fulminating with impressive videos that show you how to build a blog application with Rails in 15 minutes. I find that a lot of this material is directed at absolute beginners; beyond that, anyone who wants to meddle in Rails is presented mostly with a steaming pile of API documentation (much to the credit of the Rails developers, I have rarely seen a better-documented framework). Although I find API documentation useful, I, along with many others, learn a lot better from HOWTOs and tutorials. This HOWTO is written in that spirit.

Rails' wow! moment comes when it successfully generates basic CRUD pages for your data model using a single line of code, via a process known as scaffolding. Based on the data types of the columns in your database table, it successfully figures out how to display and accept input for the values of that column. For example, a varchar column in your database table would result in the automatic generation of a HTML text field, whereas a text/ntext/clob field would result in an HTML textarea. This works more than 80% of the time, because there aren't that many standard column types. However, powerful databases like PostgreSQL allow the database user to define custom types and reference them in table definitions. As you would expect, Rails doesn't map these custom types correctly to user interface elements in the scaffold.

Let's say you're building a Web application that is concerned with some domain object, say a widget. Let's suppose that every widget instance has an attribute, :widget_blick, of a nonstandard data type, blick. The actual representation of blick values in the database is irrelevant. Let's assume though that the database provides TextToBlick and BlickToText functions that convert back and forth between a blick and some textual representation of a blick. After you create the widgets table and use Rails' script/generate model Widget, you'll get something like so in widget.rb

class Widget < ActiveRecord::Base
end

Now, you want each widget instance's :widget_blick attribute to return the textual representation of a blick and also to accept values in this representation and perform translations between this representation and blicks using the functions provided by the database. First you need to override the default accessor and mutator functions for the :widget_blick attribute

class Widget < ActiveRecord::Base
        def widget_blick=(textrepresentation)
           # call TextToBlick
        end

        def widget_blick
          # call BlickToText
        end
end

To call the database-provided functions, each model class derived from ActiveRecord::Base provides a find_by_sql method which nominally returns an object of the same type as the model class. In other words, Widget#find_by_sql would return the result of the database function call wrapped in a Widget instance. All you need to do is to reach inside this Widget instance and grab the underlying value to make your accessor and mutator methods functional. Your code would look something like so

class Widget < ActiveRecord::Base
        def widget_blick=(textrepresentation)
           write_attribute(:widget_blick, 
           Widget.find_by_sql(["SELECT TextToBlick(?) 
as value", textrepresentation]).first.value)
        end

        def widget_blick
          Widget.find_by_sql(["SELECT BlickToText(widget_blick) as value 
FROM widgets WHERE id = ?", id]).first.value
        end
end

To verify that this works, fire up the really handy script/console. You should be able to see the following sequence (type things in at the ">>" prompt. The console's responses are marked with a '=>')

$ ./script/console
Loading development environment
>> w = Widget.find_first
=> #
>> w.widget_blick
=> "BLICK(0, 1)" <-- This is the textual representation
>> w.widget_blick = 'BLICK(999, 1000)'
=> "BLICK(999, 1000)"
>> w.save
=> true

Now, check your database table to see that w's widget_blick property indeed got updated to "BLICK(999, 1000)".

We successfully got our model to print as output and accept as input the textual representation for a blick and convert it into a blick behind the scenes for us. For what it's worth, I found this procedure necessary when getting Rails to talk successfully to PostGIS, which uses PostgreSQL's custom types to implement spatial and geographic extensions to the core PostgreSQL database. This HOWTO was made possible with the help of "argv[0]" on the channel #rubyonrails at Freenode IRC.

Posted by Vishy at December 18, 2005 12:00 PM

Comments

Posted by: Punya at December 18, 2005 03:01 PM