class Rapids::Batch::CreateTrigger

Public Class Methods

new(model,batch_definition,options = {}) click to toggle source
# File lib/rapids/batch/create_trigger.rb, line 9
def initialize(model,batch_definition,options = {})
  @model = model
  @batch = batch_definition
  @options = options
end

Public Instance Methods

to_sql() click to toggle source
# File lib/rapids/batch/create_trigger.rb, line 15
      def to_sql
        declares = @batch.find_or_creates.map do |find_or_create|
          "declare #{variable_name(find_or_create.name,[])} integer;"
        end.join("\n")

        columns_helper = ColumnsHelper.new(@model,@batch)
        main_columns = columns_helper.find_all{|column,path| path == []}

        insert_header = (main_columns.map(&:first) + criteria_columns(@model,association_find_or_creates.map(&:name))).map{|a|sql_column_name(a,[])}
        insert_values = main_columns.map{|a|"new.#{sql_column_name(a.first,[])}"} + association_find_or_creates.map(&:name).map{|name|variable_name(name,[])}

        <<-TRIGGER_SQL
          create trigger `#{batch_table_name}_trigger` after insert on `#{batch_table_name}` for each row
          begin
            #{declares}
            
            #{find_or_create_sql(@model,@batch)}
            
            #{@options[:replace] ? "replace" : "insert"} into `#{@model.table_name}` (#{insert_header.join(",")})
                                        values (#{insert_values.join(",")});
            #{updates_sql(@model,@batch)}
          end
        TRIGGER_SQL
      end

Private Instance Methods

association_find_or_creates(model = @model,find_or_creates = @batch.find_or_creates) click to toggle source
# File lib/rapids/batch/create_trigger.rb, line 41
def association_find_or_creates(model = @model,find_or_creates = @batch.find_or_creates)
  find_or_creates.reject{|foc|model.reflections[foc.name].nil?}
end
criteria_columns(model,criteria_array) click to toggle source
# File lib/rapids/batch/create_trigger.rb, line 170
def criteria_columns(model,criteria_array)
  criteria_array.map do |column_or_association_name_or_hash|
    unless column_or_association_name_or_hash.is_a?(Hash)
      lookup_column_by_name(model,column_or_association_name_or_hash)
    end
  end
end
find_or_create_sql(model,batch,recursion_path = []) click to toggle source
# File lib/rapids/batch/create_trigger.rb, line 45
def find_or_create_sql(model,batch,recursion_path = [])
  columns_helper = ColumnsHelper.new(model,batch)
  
  batch.find_or_creates.map do |find_or_create|
    name,criteria = find_or_create.name,find_or_create.find_columns
    if model.reflections[name]
      sub_model = model.reflections[name].klass
      association_table_name = sub_model.table_name
      sub_model_columns = columns_helper.find_all{|column,path| path == [name]}
    
      where_sql = foc_where_sql(sub_model,criteria,[name])

      "select id from `#{association_table_name}` where #{where_sql} into #{variable_name(name,recursion_path)};
       if #{variable_name(name,recursion_path)} is null then
         insert into #{association_table_name} (#{sub_model_columns.map{|a|sql_column_name(a.first,[])}.join(",")})
                                        values (#{sub_model_columns.map{|a|"new.#{sql_column_name(a.first,a.last)}"}.join(",")});
       
         select last_insert_id() into #{variable_name(name,recursion_path)};
       
         #{sub_inserts(sub_model,criteria,[name],variable_name(name,recursion_path))}
       end if;
      "
    elsif name.is_a?(String) && Kernel.const_get(name)
      sub_model = Kernel.const_get(name)
      association_table_name = sub_model.table_name
      sub_model_columns = columns_helper.find_all{|column,path| path == [name]}
    
      where_sql = foc_where_sql(sub_model,criteria,[name])
      
      "select id from `#{association_table_name}` where #{where_sql} into #{variable_name(name,recursion_path)};
       if #{variable_name(name,recursion_path)} is null then
         insert into #{association_table_name} (#{sub_model_columns.map{|a|sql_column_name(a.first,[])}.join(",")})
                                        values (#{sub_model_columns.map{|a|"new.#{sql_column_name(a.first,a.last)}"}.join(",")});
       
         #{sub_inserts(sub_model,criteria,[name],variable_name(name,recursion_path))}
       end if;
      "
    end
  end.join("\n")
end
foc_where_sql(model,criteria_array,path) click to toggle source
# File lib/rapids/batch/create_trigger.rb, line 90
def foc_where_sql(model,criteria_array,path)
  and_comparisons = criteria_array.map do |column_or_association_name_or_hash|
    if column_or_association_name_or_hash.is_a?(Hash)
      sub_comparisons = column_or_association_name_or_hash.map do |association_name,sub_criteria|
        association = model.reflections[association_name]
        if association.collection?
          sub_criteria.map do |column_name|
            column = lookup_column_by_name(association.klass,column_name)
            "new.#{sql_column_name(column,path+[association_name])} = (select GROUP_CONCAT(#{sql_column_name(column,[])} ORDER BY #{sql_column_name(column,[])})
                  from #{association.quoted_table_name}
                  where `#{model.table_name}`.id = `#{association.primary_key_name}`)
            "
          end.join(" and ")
        else
          "true"
        end
      end
      
      "("+sub_comparisons.join(" and ")+")"
    else
      if column_or_association_name_or_hash.is_a?(Array)
        destination_column = lookup_column_by_name(model,column_or_association_name_or_hash.first)
        source_column = lookup_column_by_name(model,column_or_association_name_or_hash.last)
      else
        destination_column = lookup_column_by_name(model,column_or_association_name_or_hash)
        source_column = lookup_column_by_name(model,column_or_association_name_or_hash)
      end

      "#{sql_column_name(destination_column,[])} <=> new.#{sql_column_name(destination_column,path)}"
    end
  end
  
  and_comparisons.join(" and ")
end
lookup_column_by_name(model,column_name) click to toggle source
# File lib/rapids/batch/create_trigger.rb, line 154
def lookup_column_by_name(model,column_name)
  column = model.columns.detect{|c|c.name == column_name.to_s}

  unless column
    # doesn't match a column name look for an assocation instead then
    association = model.reflections[column_name]

    column = if association.nil? || association.collection?
      nil #TODO implement
    else
      model.columns.detect{|c|c.name == association.primary_key_name}
    end
  end
  column
end
sub_inserts(model,criteria_array,path,key_variable) click to toggle source
# File lib/rapids/batch/create_trigger.rb, line 125
def sub_inserts(model,criteria_array,path,key_variable)
  criteria_array.map do |column_or_association_name_or_hash|
    if column_or_association_name_or_hash.is_a?(Hash)
      column_or_association_name_or_hash.map do |association_name,sub_criteria|
        association = model.reflections[association_name]
        column = lookup_column_by_name(association.klass,sub_criteria.first)
        "begin
           DECLARE cur_position INT DEFAULT 1;
           DECLARE remainder VARCHAR(255);
           DECLARE cur_string VARCHAR(255);

           SET remainder = new.#{sql_column_name(column,path+[association_name])};

           WHILE CHAR_LENGTH(remainder) > 0 AND cur_position > 0 DO
             SET cur_position = INSTR(remainder, ',');
             IF cur_position = 0 THEN
               SET cur_string = remainder;
             ELSE
               SET cur_string = LEFT(remainder, cur_position - 1);
             END IF;
             INSERT INTO #{association.quoted_table_name} (#{["`#{association.primary_key_name}`",sql_column_name(column,[])].join(",")}) VALUES (#{[key_variable,'cur_string'].join(",")});
             SET remainder = SUBSTRING(remainder, cur_position + 1);
           END WHILE;
         end;"
      end.join("\n")
    end
  end.join("\n")
end
updates_sql(model,batch) click to toggle source
# File lib/rapids/batch/create_trigger.rb, line 178
def updates_sql(model,batch)
  columns_helper = ColumnsHelper.new(model,batch)
  
  batch.updates.map do |update|
    association = model.reflections[update.name]
    update_model = association.klass
    
    update_columns = columns_helper.find_all{|column,path| path == update.path}.map do |pair|
      column,path = *pair
      "`#{column.name}` = IFNULL(new.#{sql_column_name(*pair)},`#{column.name}`)" #TODO should later add support for setting null in this update
    end

    "update `#{update_model.table_name}` set #{update_columns.join(",")} where `#{update_model.table_name}`.id = new.`#{association.primary_key_name}`;"
  end.join("\n")
end
variable_name(name,recursion_path) click to toggle source
# File lib/rapids/batch/create_trigger.rb, line 86
def variable_name(name,recursion_path)
  "find_or_create$#{(recursion_path + [name]).join("$")}"
end