Build a mysql specific INSERT OR UPDATE statement

gistfile1.rb

require_relative "../core_extensions.rb"

module ListDataExchange
  module Sql
    class InsertOrUpdate
      def initialize(quoter, table_name, column_names)
        @quoter = quoter
        @table_name = table_name
        @column_names = column_names
        @insert_sql = ""
        @relationships = {}
        @shared_columns = []
        @shared_insert_values = []
        @shared_update_values = []
      end
      
      def add_relationship(name, relationship_map)
        @relationships[name] = relationship_map
      end
      
      def add_record(attributes)
        @insert_sql << ",\n" unless @insert_sql.blank?
        @insert_sql << insert_sql_for_record(attributes)
      end
      
      def add_shared_value(column_name, insert_value, update_value)
        @shared_columns << column_name
        @shared_insert_values << insert_value
        @shared_update_values << update_value
      end
      
      def add_created_at(value = "NOW()")
        add_shared_value 'created_at', value, nil
      end

      def add_updated_at(value = "NOW()")
        add_shared_value 'updated_at', value, value
      end
      
      def add_timestamps
        add_created_at
        add_updated_at
      end

      def to_sql
        raise Exception.new("No records added") if @insert_sql.blank?
        "INSERT INTO #{@table_name} (#{column_names_sql})\nVALUES #{@insert_sql}\nON DUPLICATE KEY UPDATE #{update_sql}"
      end

      private
      
        def insert_sql_for_record(attributes)
          result = '('
          
          if @relationships
            @relationships.each do |name, relationship_map|
              relationships = (attributes['relationships'] || {})
              existing = relationships[name]
              attributes[name] = relationship_map[existing] if existing
            end
          end
          
          result << @column_names.map do |column_name|
            @quoter.quote(attributes[column_name])
          end.join(",")

          if !@shared_columns.empty?
            result << ',' + @shared_insert_values.join(",")
          end

          result << ')'
          result
        end

        def column_names_sql
          all_column_names.map {|name| "`#{name}`"}.join(',')
        end
        
        def all_column_names
          if @shared_columns.empty?
            @column_names
          else
            (@column_names + @shared_columns)
          end
        end

        def update_sql
          parts = @column_names.map do |column_name|
            "#{column_name}=VALUES(#{column_name})"
          end
          @shared_columns.each_with_index do |column_name, index|
            value = @shared_update_values[index]
            parts << "#{column_name}=#{value}" if value
          end
          
          parts.join(',')
        end
    end
  end
end