Creative Associations

Relationships and Marriage Problems

Michael Schürig

Many Relationsships

Viele Assoziationen zwischen Person und Film
  • Each relationship is represented by its own join table.
  • For each new relationship code and database have to be changed.
  • Tough questions
    • Who participated in this movie?
    • Which movies did this person participate in?

One Relationship: On the Role

Eine Assoziation mit Rollentyp zwischen Person und Film
  • Ternary association: person, movie, type
  • Somer queries become more complicated:
    • Who's the director of this movie?
  • Some become easier:
    • Who participated in this movie?

Marital Symmetry

I am married to you
You are married to me

  • Example of symmetric relationship.
  • Serial monogamy: The association is tied to a period of time.

More than one way...

Gay marriage: the database engineering perspective

Acting

class Movie < ActiveRecord::Base
  has_and_belongs_to_many :actors, :class_name => 'Person'
  # ... same for director, camera, cutter, makeup, ...
end
That's not the way..

Roles to the Rescue!

class Movie < ActiveRecord::Base
  has_many :roles
  has_many :participants, :through => :roles,
    :source => :person
end

class Role < ActiveRecord::Base
  validates_presence_of :role_type
  belongs_to :person
  belongs_to :movie
end

How do I get a Role?

movie.participants.find(:all, :conditions => ...)
That hurts!

Extended Associations

class Movie < ActiveRecord::Base
  has_many :roles do
    def as_actor
      self.scoped(
        :joins => 'CROSS JOIN roles',
        :conditions => { :roles => { :role_type => "actor" } }
      )
    end
    def as_director
      ...
    end
  end

  has_many :participants, :through => :roles,
      :source => :person do
    ...
  end
end
  • Who am I?
    self
    AssociationProxy, behaves almost like the target object.
    proxy_owner
    Returns the object the association is part of.
    proxy_reflection
    Returns the reflection object that describes the association.
    proxy_target
    Returns the associated object for belongs_to and has_one, or the collection of associated objects for has_many and has_and_belongs_to_many.

Am I Repeating Myself?

YES!

Dynamically Talking Less...

class Movie < ActiveRecord::Base
  has_many :roles do
    def as(role_type)
      self.scoped(
        :joins => 'CROSS JOIN roles',
        :conditions => { :roles => { :role_type => role_type }}
      )
    end
  end
end

...While Telling Everything

class Movie < ActiveRecord::Base
  has_many :roles do
    def as(role_type)
      ...
    end

    [:actor, :director].each do |role_type|
      define_method("as_#{role_type}") do
        as(role_type)
      end
    end
  end
end
  • Where do the types come from?

Roles, Actors, Directors

Don't miss them:
class Movie < ActiveRecord::Base
  has_many :roles do
    ...
  end
  has_many :participants, :through => :roles,
      :source => :person do
    def as(role_type)
      ...
    end
    [:actor, :director].each do |role_type|
      define_method ...
    end
  end
end
Is this necessary?

Drought

No.
class Movie < ActiveRecord::Base
  has_many :roles, :extend => RoleTypeExtension
  has_many :participants, :through => :roles,
    :source => :person,
    :extend => RoleTypeExtension
end
module RoleTypeExtension
  def as(role_type)
    ...
  end
  [:actor, :director].each do |role_type|
    define_method ...
  end
end
  • Associations can be extended through modules.
  • Even better, if these extensions fit in multiple places.

Serendipity

class Person < ActiveRecord::Base
  has_many :roles, :extend => RoleTypeExtension
  has_many :movies, :through => :roles,
    :extend => RoleTypeExtension
end

Restricted Groups

class Person < ActiveRecord::Base
  named_scope :actors, {
    :joins =>
      'INNER JOIN roles ON roles.person_id = people.id',
    :conditions => { :roles => { :role_type => 'actor' }
  }
end
  • We have ways to find actors, directors for a specificmovie. How do we find them all?
  • Taking a step back, there's more than associations.
  • But there's repetition lurking again.

Dynamic Groups

class Person < ActiveRecord::Base
  ['actor', 'director'] do |role_type|
    named_scope role_type.pluralize,
      {
        :joins =>
          'INNER JOIN roles ON roles.person_id = people.id',
        :conditions => { :roles => { :role_type => role_type }
      }
    end
  end
end

Oops, we did it again

['actor', 'director'] do |role_type|

Everything in its Place

class Role < ActiveRecord::Base
  ROLE_TYPES = %w(actor director).freeze
  def self.each_role_type(&block)
    ROLE_TYPES.each(&block)
  end
end

class Person < ActiveRecord::Base
  Role.each_role_type do |role_type|
    named_scope role_type.pluralize,
      ...

Only Looking?

movie.participants.add(... ? ...)

movie.participants.remove(... ? ...)
  • So far we can only browse existing relationships.
  • We can't add or remove objects.

Give and Take (1)

class Movie < ActiveRecord::Base
  has_many :roles, :extend => RoleTypeExtension
  has_many :participants, :through => :roles,
    :source => :person, :extend => ParticipantsExtension

  module ParticipantsExtension
    include RoleTypeExtension
    ...
  end
end

module RoleTypeExtension
  ...
end

Give and Take (2)

module ParticipantsExtension
  include RoleTypeExtension
  def add(role_type, person)
    proxy_owner.roles.build(
      :person    => person,
      :role_type => role_type)
  end
  def remove(role_type, person)
    role = Role.find(:first,
      :joins => :roles,
      :conditions => {
        :person_id => person,
        :movie_id  => proxy_owner,
        :role_type => role_type })
    proxy_owner.roles.delete(role)
  end
end
  • Treachery: Changes to associations aren't immediately visible on all associated objects.

A Visit to the Control Room

class MoviesController < ApplicationController
  def create
    @movie = Movie.new(params[:movie])
    respond_to do |format|
      ...
    end
  end

  def update
    @movie = Movie.find(params[:id])
    @movie.update_attributes(params[:movie])
    respond_to do |format|
      ...
    end
  end
end
  • When creating a new movie we'd like to assign actors and director at the same time. Similarly, when updating a movie.
  • "Participation" in a movie is not a resource of its own.
  • Movies and their attaches participations are an aggregate with the movie as the root.

Always at Your Service

What the client sends:
{
  "id" => 4217,
  "movie" => {
    "title"        => "Tramping Along the Rails",
    "actor_ids"    => ["1", "2", "3", "5", "8"],
    "director_ids" => ["13"]
  }
}

Complicated Customer

A request with extras:
{
  "id" => 4217,
  "movie" => {
    "title"     => "Tramping Along the Rails",
    "actors"    => [
      { "person_id" => "1", "credited_as" => "Will Shatter", 
        "character" => "Captain Krak" }
      { "person_id" => "7", "credited_as" => "Lemur Nerode",
        "character" => "Mr Conehead" }
    ],
    ...
  }
}

Have we (I) been too clever?

Railway Worker

We convince the customer to send their data in a more palatable form
{
  "id" => 4217,
  "movie" => {
    "title"            => "Tramping Along the Rails",
    "roles_attributes" => [
      { "id" => "1", "credited_as" => "Will Shatter", 
        "character" => "Captain Krak", "role_type" => "actor" }
      { "id" => "13", "_delete" => "1" }
    ],
    ...
  }
}

Rails to the Rescue

Let Rails do the job
class Movie < ActiveRecord::Base
  has_many :roles, :extend => RoleTypeExtension
  accepts_nested_attributes_for :roles, :allow_destroy => true
end

Intermission

Marriage Contract

Asymmetry

"Oh, we are married?"
class Person < ActiveRecord::Base
  has_many :marriages
  has_many :spouses, :through => :marriages, :source => :spouse
end

class Marriage < ActiveRecord::Base
  belongs_to :person
  belongs_to :spouse, :class_name => 'Person'
end

me.marriages.create(:spouse => you)
you.spouses == ?
  • Unusable for associations and queries.

Polygamy

class Person < ActiveRecord::Base
  has_and_belongs_to_many :marriages
  def spouses
    marriages.map(&:people).flatten - [self]
  end
end

class Marriage < ActiveRecord::Base
  has_and_belongs_to_many :people
end

me.marriages.create(:spouse => you)
me.spouses.include?(you) # => true
you.spouses.include?(you) # => true
you.marriages.create(:spouse => no3)
...
  • Problem: How to guarantee the right number of partners?
  • Possibly by locking (optimistic/pessimistic) the marriage object.
  • Complicated: has_many :spouses, :through => :marriages is not possible

Inconsistency

class Person < ActiveRecord::Base
  has_many :marriages
  has_many :spouses, :through => :marriages, :source => :spouse
end

class Marriage < ActiveRecord::Base
  validates_presence_of :start_date
  belongs_to :person
  belongs_to :spouse, :class_name => 'Person'
  after_create  { |m| Marriage.create(:person => m.spouse,
                                      :spouse => m.person) }
  after_destroy { |m| Marriage.delete_all(:conditions => ...) }
end
  • Redundancy begets inconsistency.

Mirror, Mirror

create_table :marriages_internal do |t|
  t.belongs_to :person1, :null => false
  t.belongs_to :person2, :null => false
end
add_index :marriages_internal, [:person1_id, :person2_id],
  :unique => true
create_view :marriages,
  %{SELECT id, person1_id, person2_id FROM marriages_internal
    UNION
    SELECT id, person2_id, person1_id FROM marriages_internal
  } do |v|
  v.column :id
  v.column :person_id
  v.column :spouse_id
end
http://github.com/aeden/rails_sql_views
  • Omitted attributes: start_date, end_date, lock_version.

With Class

class Marriage < ActiveRecord::Base
  belongs_to :person
  belongs_to :spouse, :class_name => 'Person'
  validates_presence_of :person, :spouse
end

class Person < ActiveRecord::Base
  has_one :marriage, :conditions => 'end_date IS NULL'
  has_one :spouse, :through => :marriage
end
  • The table (actuall: view) marriages contains rows with duplicate ids, but ActiveRecord doesn't care.
  • validates_presence_of: yes, that's become correct in the meantime.

Rules for a Good Marriage

CREATE RULE
  • Add rules using execute in a migration.
  • MySQL can't handle UNION in updatable views.
  • Statement triggers with BEFORE/AFTER are not enough.

Rules (1): Wedding

CREATE RULE marriages_ins AS ON INSERT TO marriages DO INSTEAD
INSERT INTO marriages_internal (person1_id, person2_id,
                                start_date, end_date)
VALUES (LEAST(NEW.person_id, NEW.spouse_id), 
        GREATEST(NEW.person_id, NEW.spouse_id))
RETURNING id, person1_id, person2_id, start_date, end_date;
  • Ids are inserted in canonical order, person1_id < person2_id.
  • RETURNING: view on the inserted row ActiveRecord only takes id.
  • Reduced: lock_version not shown.

Regeln (2): Divorce

CREATE RULE marriages_upd AS ON UPDATE TO marriages DO INSTEAD
UPDATE marriages_internal
SET start_date   = NEW.start_date,
    end_date     = NEW.end_date
WHERE (id = OLD.id);

Regeln (3): Oblivion

CREATE RULE marriages_del AS ON DELETE TO marriages DO INSTEAD
DELETE FROM marriages_internal
WHERE (id = OLD.id);

Any Objections?

class Marriage < ActiveRecord::Base
  def before_validation
    self.start_date ||= Date.today
  end

  def validate_on_create
    errors.add_to_base("...") if person == spouse
  end

  def validate
    if end_date && end_date < start_date
      errors.add(:end_date, "...")
    end
    validate_unmarried(person, :person_id)
    validate_unmarried(spouse, :spouse_id)
  end
  ...

Marital Status

  ...
  def period
    today = Date.today
    ((start_date || today)..(end_date || today))
  end

  def overlaps?(other_period)
    period.overlaps?(other_period)
  end

  def validate_unmarried(person, attribute)
    others = person.marriages.during(period) - [self]
    unless others.empty?
      errors.add(attribute, "Is already married at that time.")
    end
  end
end

What Did You Do?

class Person
  has_many :marriages do
    def during(dates)
      self.select { |marriage| marriage.overlaps?(dates) }
    end
  end
end
  • Avoid DB access through #exists?. The marriage association will be loaded completely sooner or later anyway.

Con Artist

Process1Process2
Marriage.create(
:person => p1,
:spouse => p2)
Marriage.create(
:person => p1,
:spouse => p3)
validate
validate
save
save
  • TOCTTOU
  • Transactions (alone) don't help!

Serial Monogamy (pessimistic)

So:
class Marriage < ActiveRecord::Base
  def before_validation
    ...
    Person.find(:all, 
      :conditions => { :id => [person, spouse].compact },
      :lock => true)
  end
end
    
Not:
  def before_validation
    ...
    person.try(:lock!)
    spouse.try(:lock!) # There be deadlocks
  end
  • #compact, because person/spouse may be nil.
  • Individual #lock! risk deadlock.
  • Use persons as mutexes for their attaches marriages.
  • ActiveRecord#lock!

Serial Monogamy (optimistic)

class Marriage < ActiveRecord::Base
  belongs_to :person, :touch => true
  belongs_to :spouse, :class_name => 'Person', :touch => true
end

Intermission

Brad - Jennifer - Angelina

date = movie.release_date
movie.participants.select do |brad|
  jennifer = brad.marriages.ended_before(date).last.spouse
  angelina = brad.marriages.started_after(date).first.spouse
  jennifer && angelina && movie.participants.include?(angelina)
end
  • Funny -- but inefficient.
  • For real use write custom SQL.

Archisignation?

Have we been doing

Architecture: Where — Controller

  • may take help from (Rack) middleware

Architecture: Where — Model

Design: What

  • Objekts and their roles

Implementation: How

That's it

More about and by me:

http://www.schuerig.de/michael

This presentation:
http://www.schuerig.de/michael/pres/kreative-assoziationen/