Using Datamapper On Legacy Databases

Yesterday I had to map a legacy database schema to some Ruby classes. Just before that I had been looking into the advantages of DataMapper, and it looked like the perfect match for my current “challenge”.

from the DataMapper site:

DataMapper only issues updates or creates for the properties it knows about. So it plays well with others. You can use it in an Integration Database without worrying that your application will be a bad actor causing trouble for all of your other processes.

The system

The system is running with 2 databases. One database holds all the information for the legacy app, the other database holds all information for system management.

mysql app
mysql> desc cpClients;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| clientID     | int(255)     | NO   | PRI | NULL    | auto_increment | 
| login        | varchar(255) | NO   | UNI |         |                | 
| pass         | varchar(255) | NO   |     |         |                | 
| type         | int(2)       | NO   |     | 0       |                | 
| traffic      | bigint(255)  | NO   |     | 0       |                | 
<snip>
| VAT          | varchar(255) | YES  |     | NULL    |                | 
+--------------+--------------+------+-----+---------+----------------+
mysql> desc cpURL;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| url      | varchar(255) | NO   | PRI |         |       | 
| clientID | int(255)     | NO   |     | 0       |       | 
+----------+--------------+------+-----+---------+-------+
> exit
mysql management
mysql> desc ftp_users;
+--------------+------------------+------+-----+-------------------+-------+
| Field        | Type             | Null | Key | Default           | Extra |
+--------------+------------------+------+-----+-------------------+-------+
| username     | varchar(60)      | NO   | PRI | NULL              |       | 
| password     | varchar(30)      | YES  |     | NULL              |       | 
<snip>
| homedir      | text             | YES  |     | NULL              |       | 
| shell        | varchar(15)      | NO   |     | /bin/false        |       | 
+--------------+------------------+------+-----+-------------------+-------+

One Client has many URL’s, One client also has many ftp_users, but the link in that can’t be captured in a simple has many relationship.

Solving it with DataMapper

Setting up the connections

Setting up 2 connections to the databases was quite easy and well documented. I chose the app database to be the default connection, and management as another.

DataMapper.setup(:default, 'mysql://localhost/app')
DataMapper.setup(:management, 'mysql://localhost/management')
Having different database names

The first problem I had was that I wanted the cpClient table to be mapped to the User class, and cpURL to be mapped to a Domain class. This can be solved with storage_names.

class User
    include DataMapper::Resource
    storage_names[:default] = 'cpClients'
end

class Domain
    include DataMapper::Resource
    storage_names[:default] = 'cpURL'
end
Defining primary keys

Defining the primary key of my cpURL table was quite easy since DataMapper does natural keys out of the box.

class Domain
  include DataMapper::Resource
  storage_names[:default] = 'cpURL'

  property :url, String, :key => true
end

Defining the primary key of the cpClients table was a tad more difficult since it was poorly documented. I wanted the property to be called ID, but it should still use clientID in the database. The magic option to this is :field, which allows you to set a custom field for a propery.

class User
    include DataMapper::Resource
    storage_names[:default] = 'cpClients'

    property :id,                    Serial, :field => 'clientID'
end
Defining associations

The hardest part was defining associations. When I’d just define them with has n without extra options, DataMapper would look for user_id, so I’d have to tell which key was used. Since it wasn’t very well documented I had some failed attempts

has n, :domains, :child_key => 'clientID'
# => +options[:child_key]+ should be Array, but was String (ArgumentError)

has n, :domains, :child_key => ['clientID']
# => ArgumentError: +name+ should be Symbol, but was String 

has n, :domains, :child_key => [:clientID]
# => MysqlError: Unknown column 'client_i_d' in 'field list' (mysql_error_code=0001)

So I need to use a symbol, but apparently DataMapper does some automatic magic to translate keys. When you have a key like me you can disable this magic by changing field_naming_convention’s proc to your own. In the proc we return the stringified value’s name (to_s is needed because the key is given as a symbol)

repository(:default).adapter.field_naming_convention = lambda { |value| value.name.to_s }

And then the association worked. Do note the :child_key option needs to be set at both sides.

class User
  has n, :domains, :child_key => [:clientID]
end

class Domain
  belongs_to :user, :child_key => [:clientID]
end
Setting models to automatically connect to another database

The FtpUser model automatically needs to connect to the :management database, and there doesn’t seem to be a function to set this. If you temporarily want to connect to another database you can do it with the repository command.

repository(:management) { FtpUser.first }

However, if you want a model to always connect to a database other then the default, defining self.default_repository_name with the correct repository works very well. (Databases are referred to as repositories)

class FtpUser
  include DataMapper::Resource

  def self.default_repository_name
      :services
  end
end

All of this was needed to get my legacy database mapped, I hope some of you find this information useful. If you see things I can do better / different, do let me know in the comments. I’ve only used DataMapper for about 2 hours so some of my assumptions may be way of.