module Upsert::MergeFunction::Mysql

@private

Public Class Methods

included(klass) click to toggle source
# File lib/upsert/merge_function/mysql.rb, line 5
def self.included(klass)
  klass.extend ClassMethods
end

Public Instance Methods

create!() click to toggle source

stackoverflow.com/questions/11371479/how-to-translate-postgresql-merge-db-aka-upsert-function-into-mysql/

# File lib/upsert/merge_function/mysql.rb, line 21
def create!
  Upsert.logger.info "[upsert] Creating or replacing database function #{name.inspect} on table #{table_name.inspect} for selector #{selector_keys.map(&:inspect).join(', ')} and setter #{setter_keys.map(&:inspect).join(', ')}"
  selector_column_definitions = column_definitions.select { |cd| selector_keys.include?(cd.name) }
  setter_column_definitions = column_definitions.select { |cd| setter_keys.include?(cd.name) }
  update_column_definitions = setter_column_definitions.select { |cd| cd.name !~ CREATED_COL_REGEX }
  quoted_name = connection.quote_ident name
  connection.execute "DROP PROCEDURE IF EXISTS #{quoted_name}"
  connection.execute(%{
    CREATE PROCEDURE #{quoted_name}(#{(selector_column_definitions.map(&:to_selector_arg) + setter_column_definitions.map(&:to_setter_arg)).join(', ')})
    BEGIN
      DECLARE done BOOLEAN;
      REPEAT
        BEGIN
          -- If there is a unique key constraint error then 
          -- someone made a concurrent insert. Reset the sentinel
          -- and try again.
          DECLARE ER_DUP_UNIQUE CONDITION FOR 23000;
          DECLARE ER_INTEG CONDITION FOR 1062;
          DECLARE CONTINUE HANDLER FOR ER_DUP_UNIQUE BEGIN
            SET done = FALSE;
          END;
          
          DECLARE CONTINUE HANDLER FOR ER_INTEG BEGIN
            SET done = TRUE;
          END;

          SET done = TRUE;
          SELECT COUNT(*) INTO @count FROM #{quoted_table_name} WHERE #{selector_column_definitions.map(&:to_selector).join(' AND ')};
          -- Race condition here. If a concurrent INSERT is made after
          -- the SELECT but before the INSERT below we'll get a duplicate
          -- key error. But the handler above will take care of that.
          IF @count > 0 THEN 
            -- UPDATE table_name SET b = b_SET WHERE a = a_SEL;
            UPDATE #{quoted_table_name} SET #{update_column_definitions.map(&:to_setter).join(', ')} WHERE #{selector_column_definitions.map(&:to_selector).join(' AND ')};
          ELSE
            -- INSERT INTO table_name (a, b) VALUES (k, data);
            INSERT INTO #{quoted_table_name} (#{setter_column_definitions.map(&:quoted_name).join(', ')}) VALUES (#{setter_column_definitions.map(&:to_setter_value).join(', ')});
          END IF;
        END;
      UNTIL done END REPEAT;
    END
  })
end