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 ""
3
4 class APerson < Sequel::Model(DBa[:people])
5 end
6
7 class BContact < Sequel::Model(DBb[:contacts])
8 end
9
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
19
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
5
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)>’
11

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!

Search

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

Links

Projects I work on

Projects I worked on