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!
