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!
