I ran into a little gotcha today, using Sequel. I’m writing an importer, you know the kind: read record from database A, apply some transformations, write to database B. No rocket science required. But, Sequel has a little gotcha that stumped me for a bit. My script looked like this:

 1 DBa = Sequel.connect "..."
 2 DBb = Sequel.connect "..."
 4 class APerson < Sequel::Model(DBa[:people])
 5 end
 7 class BContact < Sequel::Model(DBb[:contacts])
 8 end
10 contacts = Hash.new
11 APerson.all.each do |person|
12   contact = BContact.create(
13     :name        => person.last_name + ", " + person.first_name,
14     :tenant_code => ENV["TENANT_CODE"],
15     :updated_by  => "import",
16     :updated_at  => Time.now)
17   contacts[ person.id ] = contact.contact_id
18 end
20 # Now I can map A's IDs to the correct value in database B, such as
21 # for attaching email addresses, phone numbers, etc.

The Contact model in the B database is declared like this:

1 create_table :contacts do
2   column :tenant_code, :integer,       :null => false
3   column :contact_id,  :serial,        :null => false
4   column :name,        "varchar(240)", :null => false
6   primary_key [:tenant_code, :contact_id]
7   foreign_key [:tenant_code], :tenants
8 end

Notice tenant_code and contact_id are part of the primary key. I don’t write to contact_id because I want the sequence’s value to be returned to me. But I must write my own value to the tenant_code column. I was receiving an exception on the #create call:

 1 /Users/francois/.rvm/gems/ruby-1.9.2-p180/gems/sequel-3.25.0/lib/sequel/model/base.rb:1491:in `block in set_restricted': method tenant_code= doesn't exist or access is restricted to it (Sequel::Error)
 2   from /Users/francois/.rvm/gems/ruby-1.9.2-p180/gems/sequel-3.25.0/lib/sequel/model/base.rb:1486:in `each'
 3   from /Users/francois/.rvm/gems/ruby-1.9.2-p180/gems/sequel-3.25.0/lib/sequel/model/base.rb:1486:in `set_restricted'
 4   from /Users/francois/.rvm/gems/ruby-1.9.2-p180/gems/sequel-3.25.0/lib/sequel/model/base.rb:1077:in `set'
 5   from /Users/francois/.rvm/gems/ruby-1.9.2-p180/gems/sequel-3.25.0/lib/sequel/model/base.rb:1456:in `initialize_set'
 6   from /Users/francois/.rvm/gems/ruby-1.9.2-p180/gems/sequel-3.25.0/lib/sequel/model/base.rb:764:in `initialize'
 7   from /Users/francois/.rvm/gems/ruby-1.9.2-p180/gems/sequel-3.25.0/lib/sequel/model/base.rb:134:in `new'
 8   from /Users/francois/.rvm/gems/ruby-1.9.2-p180/gems/sequel-3.25.0/lib/sequel/model/base.rb:134:in `create'
 9   from /Users/francois/.rvm/gems/ruby-1.9.2-p180/gems/sequel-3.25.0/lib/sequel/model/base.rb:248:in `find_or_create'
10   from script/import:65:in `block (2 levels) in <top (required)>'

I was very much stumped, and turned to the excellent documentation. I eventually found my way to #set_all, and changed my code accordingly:

1 APerson.all.each do |person|
2   contact = BContact.new.set_all(
3     :name            => person.last_name + ", " + person.first_name,
4     :tenant_code     => ENV["TENANT_CODE"],
5     :last_updated_by => "import",
6     :last_updated_at => Time.now)
7   contacts[ person.id ] = contact.contact_id
8 end

Even though the Sequel RDoc says #set_all ignores restricted columns, I was still receiving the same exception. I was now doubly stumped, until I found a reference to #unrestrict_primary_key. I added the declaration to BContact and was able to proceed:

1 class BContact < Sequel::Model(DBb[:contacts])
2   unrestrict_primary_key
3 end

You know the drill though: where you import one model, you’ll have more to import shortly. Ruby to the rescue!

1 class Sequel::Model
2   # Open classes win every time!
3   unrestrict_primary_key
4 end

Problem solved!


Your Host

A picture of me

I am François Beausoleil, a Ruby on Rails and Scala developer. During the day, I work on Seevibes, a platform to measure social interactions related to TV shows. At night, I am interested many things. Read my biography.

Top Tags

Books I read and recommend


Projects I work on

Projects I worked on