associations.patch

diff --git a/examples/associations.rb b/examples/associations.rb
new file mode 100755
index 0000000..27d95f5
--- /dev/null
+++ b/examples/associations.rb
@@ -0,0 +1,53 @@
+#!/usr/bin/env ruby
+
+$:.unshift(File.join(File.dirname(__FILE__), '..', 'lib'))
+
+require 'pp'
+require 'swift'
+require 'swift/migrations'
+require 'swift/associations'
+
+class User < Swift::Scheme; end
+class Car < Swift::Scheme
+  store     :cars
+  attribute :id,      Swift::Type::Integer, serial: true, key: true
+  attribute :user_id, Swift::Type::Integer
+  attribute :name,    Swift::Type::String
+
+  has n, :drivers, scheme: User, source_keys: [ :user_id ], target_keys: [ :id ]
+end
+
+class User < Swift::Scheme
+  store     :users
+  attribute :id,       Swift::Type::Integer, serial: true, key: true
+  attribute :name,     Swift::Type::String
+
+  has n, :cars
+end # User
+
+adapter = ARGV.first =~ /mysql/i ? Swift::DB::Mysql : Swift::DB::Postgres
+puts "Using DB: #{adapter}"
+
+Swift.setup :default, adapter, db: 'swift'
+Swift.trace true
+
+puts '-- migrate! --'
+Swift.migrate!
+
+puts '', '-- create --'
+User.create name: 'Apple Arthurton'
+
+puts '', '-- get --'
+pp user = User.get(id: 1)
+
+puts '', '-- create association --'
+user.cars.create(name: 'pontiac aztec - a shit car')
+
+puts '', '-- fetch association --'
+pp user.cars(':name like ?', '%pontiac%').all
+
+puts '', '-- destroy association --'
+user.cars(':name like ?', '%pontiac%').destroy
+
+puts '', '-- fetch association --'
+pp user.cars(':name like ?', '%pontiac%').drivers.all
diff --git a/examples/scheme.rb b/examples/scheme.rb
index 0e9bf64..51a3b8b 100755
--- a/examples/scheme.rb
+++ b/examples/scheme.rb
@@ -42,7 +42,6 @@ pp User.first(':name like ?', '%Arthurton')
 
 puts '', '-- get --'
 pp user = User.get(id: 2)
-pp user = User.get(id: 2)
 
 puts '', '-- update --'
 user.update(name: 'Jimmy Arthurton')
diff --git a/lib/swift/associations.rb b/lib/swift/associations.rb
new file mode 100644
index 0000000..48a6285
--- /dev/null
+++ b/lib/swift/associations.rb
@@ -0,0 +1,115 @@
+require_relative 'inflect'
+require_relative 'associations/crud'
+
+module Swift
+  module Associations
+    class Relationship
+      attr_accessor :source, :target, :source_keys, :target_keys, :chains
+      attr_reader   :source_scheme, :target_scheme, :conditions, :bind
+
+      def initialize opts = {}
+        @chains        = opts.fetch(:chains, [])
+        @source        = opts[:source] or raise ArgumentError, '+source+ required'
+        @target        = opts[:target] or raise ArgumentError, '+target+ required'
+        @source_scheme = source.kind_of?(Scheme) ? source.scheme : source
+        @target_scheme = target.kind_of?(Scheme) ? target.scheme : target
+
+        source_single  = Inflect.singular(source_scheme.store.to_s)
+        @source_keys   = opts[:source_keys] || source_scheme.header.keys
+        @target_keys   = opts[:target_keys] || source_keys.map {|k| '%s_%s' % [ source_single, k ] }
+
+        @conditions    = opts.fetch(:condition, [])
+        @bind          = opts.fetch(:bind, [])
+        @conditions    = [ conditions ] unless conditions.kind_of?(Array)
+      end
+
+      def load
+        Swift.db.associations_fetch(target, self)
+      end
+
+      def self.parse_options args
+          options = args.last.is_a?(Hash) ? args.pop : {}
+          options[:condition] = args.shift unless args.empty?
+          options[:bind]      = args       unless args.empty?
+          options
+      end
+
+      def create args={}
+        if source.kind_of?(Scheme)
+          defaults = Hash[*target_keys.zip(source.tuple.values_at(*source_keys)).flatten]
+          target.create(args.merge(defaults))
+        else
+          raise NoMethodError, 'undefined method create in %s' % self
+        end
+      end
+      def destroy *args
+        Swift.db.associations_destroy(target, self)
+      end
+    end # Relationship
+
+    class OneToMany < Relationship
+      def all
+        self.load.to_a
+      end
+      def self.install source, accessor, options
+        source.send(:define_method, accessor) do |*args|
+          args = OneToMany.parse_options(args)
+          options = {source: self, target: options.delete(:scheme)}.merge(options)
+          OneToMany.new(options.merge(args))
+        end
+        source.send(:define_singleton_method, accessor) do |*args|
+          args = OneToMany.parse_options(args)
+          options = {source: source, target: options.delete(:scheme)}.merge(options)
+          OneToMany.new(options.merge(args))
+        end
+      end
+
+      def method_missing name, *args
+        args << { chains: [ self ] + chains }
+        if target.respond_to?(name)
+          target.send(name, *args)
+        else
+          raise NoMethodError, 'undefined method %s in %s' % [ name, self ]
+        end
+      end
+    end # OneToMany
+
+    class OneToOne < Relationship
+      def self.install source, accessor, options
+        source.send(:define_method, accessor) do
+          if rel = instance_variable_get("@#{accessor}")
+            rel.load.first
+          else
+            options = {source: source, target: options.delete(:scheme)}.merge(options)
+            rel = OneToOne.new(options)
+            instance_variable_set("@#{accessor}", rel)
+            rel.load.first
+          end
+        end
+      end
+    end # OneToOne
+
+    module Helpers
+      Infinity = 1/0.0
+
+      def n; Infinity end
+
+      def has count, name, options={}
+        scheme = self.const_get(Inflect.singular(name.to_s).capitalize) rescue nil
+        scheme = options.fetch(:scheme, scheme) or raise ArgumentError, 'Unable to deduce target scheme.'
+        case count
+          when 1
+            OneToOne.install(self, name, options.merge({scheme: scheme}))
+          when Infinity
+            OneToMany.install(self, name, options.merge({scheme: scheme}))
+          else
+            raise ArgumentError, 'Unsupported +count+'
+        end
+      end
+    end
+  end # Associations
+
+  class Scheme
+    extend Associations::Helpers
+  end # Scheme
+end
diff --git a/lib/swift/associations/crud.rb b/lib/swift/associations/crud.rb
new file mode 100644
index 0000000..95e827e
--- /dev/null
+++ b/lib/swift/associations/crud.rb
@@ -0,0 +1,68 @@
+module Swift
+  class Adapter
+    class Associations
+      def all relationship
+        sql = 'select t1.* from %s' % join(relationship, 't1', 't2')
+        unless relationship.chains.empty?
+          sql += ' join %s' % relationship.chains.map.with_index do |r, idx|
+            join_with(r, 't%d' % (idx+2), 't%d' % (idx+3))
+          end.join(' join ')
+        end
+
+        where, bind = conditions(relationship, 't1', 't2')
+        relationship.chains.each_with_index do |r, idx|
+          w, b  = conditions(r, 't%d' % (idx+2), 't%d' % (idx+3))
+          where += w
+          bind  += b
+        end
+
+        sql += ' where %s' % where.join(' and ') unless where.empty?
+        [ sql, bind ]
+      end
+
+      def join rel, alias1, alias2
+        condition = rel.target_keys.zip(rel.source_keys)
+        condition = condition.map {|t,s| '%s.%s = %s.%s' % [alias1, t, alias2, s] }.join(' and ')
+        '%s %s join %s %s on (%s)' % [ rel.target_scheme.store, alias1, rel.source_scheme.store, alias2, condition ]
+      end
+
+      def join_with rel, alias1, alias2
+        condition = rel.target_keys.zip(rel.source_keys)
+        condition = condition.map {|t,s| '%s.%s = %s.%s' % [alias1, t, alias2, s] }.join(' and ')
+        '%s %s on (%s)' % [ rel.source_scheme.store, alias2, condition ]
+      end
+
+      def conditions rel, alias1, alias2
+        bind   = rel.bind
+        clause = rel.conditions.map{|c| c.gsub(/:(\w+)/){ '%s.%s' % [ alias1, rel.target.send($1).field ] } }
+        if rel.source.kind_of?(Scheme)
+          clause << '(%s)' % rel.source_keys.map{|k| '%s.%s = ?' % [alias2, k] }.join(' and ')
+          bind   += rel.source.tuple.values_at(*rel.source_keys)
+        end
+        [ clause.map{|c| '(%s)' % c}, bind ]
+      end
+    end # Associations
+
+    def associations
+      @associations ||= Associations.new
+    end
+
+    def associations_fetch scheme, relationship
+      sql, bind = associations.all(relationship)
+      prepare(scheme, sql).execute(*bind)
+    end
+
+    def associations_destroy scheme, relationship
+      target = relationship.target
+      if target.header.keys.length > 1
+        assocations_fetch(scheme, relationship).each {|r| r.destroy }
+      else
+        key = target.header.keys.first
+        sql, bind = associations.all(relationship)
+        sql.sub!(/t1\.\*/, 't1.%s' % key)
+        sql = 'delete from %s where %s in (%s)' % [ target.store, key, sql ]
+        prepare(scheme, sql).execute(*bind)
+      end
+    end
+  end # Adapter
+end # Swift
diff --git a/lib/swift/inflect.rb b/lib/swift/inflect.rb
new file mode 100644
index 0000000..f466ec6
--- /dev/null
+++ b/lib/swift/inflect.rb
@@ -0,0 +1,288 @@
+module Swift
+  #
+  # Stolen from http://github.com/rubyworks/english/raw/master/lib/english/inflect.rb
+  #
+  # = Noun Number Inflections
+  #
+  # This module provides english singular <-> plural noun inflections.
+  module Inflect
+
+    @singular_of = {}
+    @plural_of = {}
+
+    @singular_rules = []
+    @plural_rules = []
+
+    # This class provides the DSL for creating inflections, you can add additional rules.
+    # Examples:
+    #
+    #   word "ox", "oxen"
+    #   word "octopus", "octopi"
+    #   word "man", "men"
+    #
+    #   rule "lf", "lves"
+    #
+    #   word "equipment"
+    #
+    # Rules are evaluated by size, so rules you add to override specific cases should be longer than the rule
+    # it overrides. For instance, if you want "pta" to pluralize to "ptas", even though a general purpose rule
+    # for "ta" => "tum" already exists, simply add a new rule for "pta" => "ptas", and it will automatically win
+    # since it is longer than the old rule.
+    #
+    # Also, single-word exceptions win over general words ("ox" pluralizes to "oxen", because it's a single word
+    # exception, even though "fox" pluralizes to "foxes")
+    class << self
+      # Define a general two-way exception.
+      #
+      # This also defines a general rule, so foo_child will correctly become
+      # foo_children.
+      #
+      # Whole words also work if they are capitalized (Goose => Geese).
+      def word(singular, plural=nil)
+        plural = singular unless plural
+        singular_word(singular, plural)
+        plural_word(singular, plural)
+        rule(singular, plural)
+      end
+
+      # Define a singularization exception.
+      def singular_word(singular, plural)
+        @singular_of[plural] = singular
+        @singular_of[plural.capitalize] = singular.capitalize
+      end
+
+      # Define a pluralization exception.
+      def plural_word(singular, plural)
+        @plural_of[singular] = plural
+        @plural_of[singular.capitalize] = plural.capitalize
+      end
+
+      # Define a general rule.
+      def rule(singular, plural)
+        singular_rule(singular, plural)
+        plural_rule(singular, plural)
+      end
+
+      # Define a singularization rule.
+      def singular_rule(singular, plural)
+        @singular_rules << [singular, plural]
+      end
+
+      # Define a plurualization rule.
+      def plural_rule(singular, plural)
+        @plural_rules << [singular, plural]
+      end
+
+      # Read prepared singularization rules.
+      def singularization_rules
+        if defined?(@singularization_regex) && @singularization_regex
+          return [@singularization_regex, @singularization_hash]
+        end
+        # No sorting needed: Regexen match on longest string
+        @singularization_regex = Regexp.new("(" + @singular_rules.map {|s,p| p}.join("|") + ")$", "i")
+        @singularization_hash  = Hash[*@singular_rules.flatten].invert
+        [@singularization_regex, @singularization_hash]
+      end
+
+      # Read prepared singularization rules.
+      #def singularization_rules
+      #  return @singularization_rules if @singularization_rules
+      #  sorted = @singular_rules.sort_by{ |s, p| "#{p}".size }.reverse
+      #  @singularization_rules = sorted.collect do |s, p|
+      #    [ /#{p}$/, "#{s}" ]
+      #  end
+      #end
+
+      # Read prepared pluralization rules.
+      def pluralization_rules
+        if defined?(@pluralization_regex) && @pluralization_regex
+          return [@pluralization_regex, @pluralization_hash]
+        end
+        @pluralization_regex = Regexp.new("(" + @plural_rules.map {|s,p| s}.join("|") + ")$", "i")
+        @pluralization_hash = Hash[*@plural_rules.flatten]
+        [@pluralization_regex, @pluralization_hash]
+      end
+
+      # Read prepared pluralization rules.
+      #def pluralization_rules
+      #  return @pluralization_rules if @pluralization_rules
+      #  sorted = @plural_rules.sort_by{ |s, p| "#{s}".size }.reverse
+      #  @pluralization_rules = sorted.collect do |s, p|
+      #    [ /#{s}$/, "#{p}" ]
+      #  end
+      #end
+
+      #
+      def singular_of ; @singular_of ; end
+
+      #
+      def plural_of   ; @plural_of   ; end
+
+      # Convert an English word from plurel to singular.
+      #
+      #   "boys".singular      #=> boy
+      #   "tomatoes".singular  #=> tomato
+      #
+      def singular(word)
+        return "" if word == ""
+        if result = singular_of[word]
+          return result.dup
+        end
+        result = word.dup
+
+        regex, hash = singularization_rules
+        result.sub!(regex) {|m| hash[m]}
+        singular_of[word] = result
+        return result
+        #singularization_rules.each do |(match, replacement)|
+        #  break if result.gsub!(match, replacement)
+        #end
+        #return result
+      end
+
+      # Alias for #singular (a Railism).
+      #
+      alias_method(:singularize, :singular)
+
+      # Convert an English word from singular to plurel.
+      #
+      #   "boy".plural     #=> boys
+      #   "tomato".plural  #=> tomatoes
+      #
+      def plural(word)
+        return "" if word == ""
+        if result = plural_of[word]
+          return result.dup
+        end
+        #return self.dup if /s$/ =~ self # ???
+        result = word.dup
+
+        regex, hash = pluralization_rules
+        result.sub!(regex) {|m| hash[m]}
+        plural_of[word] = result
+        return result
+        #pluralization_rules.each do |(match, replacement)|
+        #  break if result.gsub!(match, replacement)
+        #end
+        #return result
+      end
+
+      # Alias for #plural (a Railism).
+      alias_method(:pluralize, :plural)
+
+      # Clear all rules.
+      def clear(type = :all)
+        if type == :singular || type == :all
+          @singular_of = {}
+          @singular_rules = []
+          @singularization_rules, @singularization_regex = nil, nil
+        end
+        if type == :plural || type == :all
+          @singular_of = {}
+          @singular_rules = []
+          @singularization_rules, @singularization_regex = nil, nil
+        end
+      end
+    end
+
+    # One argument means singular and plural are the same.
+
+    word 'equipment'
+    word 'information'
+    word 'money'
+    word 'species'
+    word 'series'
+    word 'fish'
+    word 'sheep'
+    word 'moose'
+    word 'hovercraft'
+    word 'news'
+    word 'rice'
+    word 'plurals'
+
+    # Two arguments defines a singular and plural exception.
+
+    word 'Swiss'     , 'Swiss'
+    word 'alias'     , 'aliases'
+    word 'analysis'  , 'analyses'
+    #word 'axis'      , 'axes'
+    word 'basis'     , 'bases'
+    word 'buffalo'   , 'buffaloes'
+    word 'child'     , 'children'
+    #word 'cow'       , 'kine'
+    word 'crisis'    , 'crises'
+    word 'criterion' , 'criteria'
+    word 'datum'     , 'data'
+    word 'goose'     , 'geese'
+    word 'hive'      , 'hives'
+    word 'index'     , 'indices'
+    word 'life'      , 'lives'
+    word 'louse'     , 'lice'
+    word 'man'       , 'men'
+    word 'matrix'    , 'matrices'
+    word 'medium'    , 'media'
+    word 'mouse'     , 'mice'
+    word 'movie'     , 'movies'
+    word 'octopus'   , 'octopi'
+    word 'ox'        , 'oxen'
+    word 'person'    , 'people'
+    word 'potato'    , 'potatoes'
+    word 'quiz'      , 'quizzes'
+    word 'shoe'      , 'shoes'
+    word 'status'    , 'statuses'
+    word 'testis'    , 'testes'
+    word 'thesis'    , 'theses'
+    word 'thief'     , 'thieves'
+    word 'tomato'    , 'tomatoes'
+    word 'torpedo'   , 'torpedoes'
+    word 'vertex'    , 'vertices'
+    word 'virus'     , 'viri'
+    word 'wife'      , 'wives'
+
+    # One-way singularization exception (convert plural to singular).
+
+    singular_word 'cactus', 'cacti'
+
+    # One-way pluralizaton exception (convert singular to plural).
+
+    plural_word 'axis', 'axes'
+
+    # General rules.
+
+    rule 'rf'     , 'rves'
+    rule 'ero'    , 'eroes'
+    rule 'ch'     , 'ches'
+    rule 'sh'     , 'shes'
+    rule 'ss'     , 'sses'
+    #rule 'ess'  , 'esses'
+    rule 'ta'     , 'tum'
+    rule 'ia'     , 'ium'
+    rule 'ra'     , 'rum'
+    rule 'ay'     , 'ays'
+    rule 'ey'     , 'eys'
+    rule 'oy'     , 'oys'
+    rule 'uy'     , 'uys'
+    rule 'y'      , 'ies'
+    rule 'x'      , 'xes'
+    rule 'lf'     , 'lves'
+    rule 'ffe'    , 'ffes'
+    rule 'af'     , 'aves'
+    rule 'us'     , 'uses'
+    rule 'ouse'   , 'ouses'
+    rule 'osis'   , 'oses'
+    rule 'ox'     , 'oxes'
+    rule ''       , 's'
+
+    # One-way singular rules.
+
+    singular_rule 'of' , 'ofs' # proof
+    singular_rule 'o'  , 'oes' # hero, heroes
+    #singular_rule 'f'  , 'ves'
+
+    # One-way plural rules.
+
+    plural_rule 's'   , 'ses'
+    plural_rule 'ive' , 'ives' # don't want to snag wife
+    plural_rule 'fe'  , 'ves'  # don't want to snag perspectives
+  end
+end

I don't wanna discourage you but you are going to have a very hard time selling this mainly because it's SQL specific and the awful DM association definition. Just off the top of my head how about just the ability to chain conditions? It'll be much simpler to write and not SQL specific. It's still ugly since you'll have to ask the current scoped adapter how to join conditions etc.

The syntax could be something like:

class User
  # ...
  def cars 
    Swift::Statement::Join.new(Car).all(':user_id = ?', id) # Join any number of prepares on the returned object.
  end
end

User.get(1).cars.all(':age > ?', 12)
# Join all the collected prepared statements via Swift.db.prepare(Swift.db.join(statements)).execute(binds)

I still think it's total overkill. Personally I'd rather just define a few extra methods in the right places instead of chaining conditions.

yikes thats crazy. don't think defining the associations in itself is hard, its the adapter side of things, chaining, sql merging etc. i think you can safely abstract the relationship definition parts from the sql generation and then drop another query generator class for non-sql stores (like mongo, etc). this is just a quick hack working prototype - i'll see if i can refine it more.

I knew you'd be defensive but I was trying to compromise. I do not want assocations though, they are a mistake and any SQL generation is a mistake. If you want these sorts of things you want veritas and the next version of DM.