Clean association definitions with Ruby Sequel

Question

I am using Jeremy Evan's Sequel to populate an (SQLite) database with data I scrape from web pages. The database involves a number of many_to_many relationships that I express with Associations. The associations are created in class definitions, which are always evaluated when the script is run. Importantly, the association class definitions need to have the necessary tables in place. Thus the table creation methods should be in the top level with the association definitions. Here is an example:

module Thing
db = Sequel.Sqlite('data.sqlite')

db.create_table(:clients)
    String :client_id, :primary_key => true
    String :client_data
end

db.create_table(:orders)
    String :order_id, :primary_key => true
    String :order_data
end

db.create_table(:orders_clients)
    String :order_id
    String :client_id
    primary_key [:order_id,:client_id]
end

class Person < Sequel::Model
    unrestrict_primary_key
    many_to_many :orders
end

class Order < Sequel::Model
    unrestrict_primary_key
    many_to_many :orders
end
end

First of all, I think that this is a rather dirty solution, since my method calls and class definitions sit in the same namespace. If I try to separate the class definitions, I get No database associated with Sequel::Model error (which makes sense, but I want to defer the evaluation of the association definitions, having those after the table calls, whenever they might happen).

I want to be able to create the tables and associations in a method call. Thus, I could for example pass the name of the new database file:

def create_tables_and_schema (database_name)
    db = Sequel.Sqlite(database_name)
    db.create_table... #three of those, as above

class Person < Sequel::Model
    unrestrict_primary_key
    many_to_many :orders
end

class Order < Sequel::Model
    unrestrict_primary_key
    many_to_many :orders
end
end

What I think is needed is a different way to express table relations.

Any suggestions on approach and style are appreciated. Please ask for clarifications if the explanation is confusing.

Answer 1

Your method calls and class definitions do not need to sit in the same namespace, it's just that the tables need to be created before the model classes. An easy way to separate them is to move the table creation to a separate file. Also, usually you assign the database object to a constant.

create_tables.rb:

DB.create_table(:clients)
  String :client_id, :primary_key => true
  String :client_data
end

DB.create_table(:orders)
  String :order_id, :primary_key => true
  String :order_data
end

DB.create_table(:orders_clients)
  String :order_id
  String :client_id
  primary_key [:order_id,:client_id]
end

models.rb:

DB = Sequel.sqlite('data.sqlite')
require 'create_tables'

class Person < Sequel::Model
  unrestrict_primary_key
  many_to_many :orders
end

class Order < Sequel::Model
  unrestrict_primary_key
  many_to_many :orders
end

You mentioned that you want to create the tables and associations in a method call, but that doesn't make sense if you are creating classes with constants. The main reason to create them via a method call is to allow for multiple databases at runtime, but that wouldn't work with your model classes since they are defined with constant names.

If you don't need multiple databases at runtime, the example above should work if you just want to separate the table creation from the model creation.

If you do need multiple databases at runtime, then creating the tables and models via a method call makes sense, but you need to create anonymous model classes, as otherwise you will have problems.