Module: Sequel::Postgres

Defined in:

lib/sequel_postgresql_triggers.rb,
lib/sequel/extensions/pg_triggers.rb

Defined Under Namespace

Modules: DatabaseMethods, PGTMethods

Constant Summary

PGT_DEFINE =

proc do
  def pgt_counter_cache(main_table, main_table_id_column, counter_column, counted_table, counted_table_id_column, opts={})
    trigger_name = opts[:trigger_name] || "pgt_cc_#{main_table}__#{main_table_id_column}__#{counter_column}__#{counted_table_id_column}"
    function_name = opts[:function_name] || "pgt_cc_#{main_table}__#{main_table_id_column}__#{counter_column}__#{counted_table}__#{counted_table_id_column}"

    table = quote_schema_table(main_table)
    id_column = quote_identifier(counted_table_id_column)
    main_column = quote_identifier(main_table_id_column)
    count_column = quote_identifier(counter_column)

    pgt_trigger(counted_table, trigger_name, function_name, [:insert, :update, :delete], "    BEGIN\n      IF (TG_OP = 'UPDATE' AND (NEW.\#{id_column} = OLD.\#{id_column} OR (OLD.\#{id_column} IS NULL AND NEW.\#{id_column} IS NULL))) THEN\n        RETURN NEW;\n      ELSE\n        IF ((TG_OP = 'INSERT' OR TG_OP = 'UPDATE') AND NEW.\#{id_column} IS NOT NULL) THEN\n          UPDATE \#{table} SET \#{count_column} = \#{count_column} + 1 WHERE \#{main_column} = NEW.\#{id_column};\n        END IF;\n        IF ((TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND OLD.\#{id_column} IS NOT NULL) THEN\n          UPDATE \#{table} SET \#{count_column} = \#{count_column} - 1 WHERE \#{main_column} = OLD.\#{id_column};\n        END IF;\n      END IF;\n\n      IF (TG_OP = 'DELETE') THEN\n        RETURN OLD;\n      END IF;\n      RETURN NEW;\n    END;\n    SQL\n  end\n\n  def pgt_created_at(table, column, opts={})\n    trigger_name = opts[:trigger_name] || \"pgt_ca_\#{column}\"\n    function_name = opts[:function_name] || \"pgt_ca_\#{table}__\#{column}\"\n    col = quote_identifier(column)\n    pgt_trigger(table, trigger_name, function_name, [:insert, :update], <<-SQL)\n    BEGIN\n      IF (TG_OP = 'UPDATE') THEN\n        NEW.\#{col} := OLD.\#{col};\n      ELSIF (TG_OP = 'INSERT') THEN\n        NEW.\#{col} := CURRENT_TIMESTAMP;\n      END IF;\n      RETURN NEW;\n    END;\n    SQL\n  end\n\n  def pgt_immutable(table, *columns)\n    opts = columns.last.is_a?(Hash) ? columns.pop : {}\n    trigger_name = opts[:trigger_name] || \"pgt_im_\#{columns.join('__')}\"\n    function_name = opts[:function_name] || \"pgt_im_\#{columns.join('__')}\"\n    ifs = columns.map do |c|\n      old = \"OLD.\#{quote_identifier(c)}\"\n      new = \"NEW.\#{quote_identifier(c)}\"\n      <<-END\n        IF \#{new} IS DISTINCT FROM \#{old} THEN\n            RAISE EXCEPTION 'Attempted \#{c} update: Old: %, New: %', \#{old}, \#{new};\n        END IF;\n      END\n    end.join(\"\\n\")\n    pgt_trigger(table, trigger_name, function_name, :update, \"BEGIN \#{ifs} RETURN NEW; END;\")\n  end\n\n  def pgt_sum_cache(main_table, main_table_id_column, sum_column, summed_table, summed_table_id_column, summed_column, opts={})\n    trigger_name = opts[:trigger_name] || \"pgt_sc_\#{main_table}__\#{main_table_id_column}__\#{sum_column}__\#{summed_table_id_column}\"\n    function_name = opts[:function_name] || \"pgt_sc_\#{main_table}__\#{main_table_id_column}__\#{sum_column}__\#{summed_table}__\#{summed_table_id_column}__\#{summed_column}\"\n\n    table = quote_schema_table(main_table)\n    id_column = quote_identifier(summed_table_id_column)\n\n    new_table_summed_column = literal(Sequel.deep_qualify(Sequel.lit(\"NEW\"), summed_column))\n    old_table_summed_column = literal(Sequel.deep_qualify(Sequel.lit(\"OLD\"), summed_column))\n    main_column = quote_identifier(main_table_id_column)\n    sum_column = quote_identifier(sum_column)\n\n    pgt_trigger(summed_table, trigger_name, function_name, [:insert, :delete, :update], <<-SQL)\n    BEGIN\n      IF (TG_OP = 'UPDATE' AND NEW.\#{id_column} = OLD.\#{id_column}) THEN\n        UPDATE \#{table} SET \#{sum_column} = \#{sum_column} + \#{new_table_summed_column} - \#{old_table_summed_column} WHERE \#{main_column} = NEW.\#{id_column};\n      ELSE\n        IF ((TG_OP = 'INSERT' OR TG_OP = 'UPDATE') AND NEW.\#{id_column} IS NOT NULL) THEN\n          UPDATE \#{table} SET \#{sum_column} = \#{sum_column} + \#{new_table_summed_column} WHERE \#{main_column} = NEW.\#{id_column};\n        END IF;\n        IF ((TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND OLD.\#{id_column} IS NOT NULL) THEN\n          UPDATE \#{table} SET \#{sum_column} = \#{sum_column} - \#{old_table_summed_column} WHERE \#{main_column} = OLD.\#{id_column};\n        END IF;\n      END IF;\n      IF (TG_OP = 'DELETE') THEN\n        RETURN OLD;\n      END IF;\n      RETURN NEW;\n    END;\n    SQL\n  end\n\n  def pgt_sum_through_many_cache(opts={})\n    main_table = opts.fetch(:main_table)\n    main_table_id_column = opts.fetch(:main_table_id_column, :id)\n    sum_column = opts.fetch(:sum_column)\n    summed_table = opts.fetch(:summed_table)\n    summed_table_id_column = opts.fetch(:summed_table_id_column, :id)\n    summed_column = opts.fetch(:summed_column)\n    join_table = opts.fetch(:join_table)\n    main_table_fk_column = opts.fetch(:main_table_fk_column)\n    summed_table_fk_column = opts.fetch(:summed_table_fk_column)\n\n    summed_column_slug = summed_column.is_a?(String) || summed_column.is_a?(Symbol) ? \"__\#{summed_column}\" : \"\"\n    trigger_name = opts[:trigger_name] || \"pgt_stmc_\#{main_table}__\#{main_table_id_column}__\#{sum_column}__\#{summed_table_id_column}\#{summed_column_slug}__\#{main_table_fk_column}__\#{summed_table_fk_column}\"\n    function_name = opts[:function_name] || \"pgt_stmc_\#{main_table}__\#{main_table_id_column}__\#{sum_column}__\#{summed_table}__\#{summed_table_id_column}\#{summed_column_slug}__\#{join_table}__\#{main_table_fk_column}__\#{summed_table_fk_column}\"\n    join_trigger_name = opts[:join_trigger_name] || \"pgt_stmc_join_\#{main_table}__\#{main_table_id_column}__\#{sum_column}__\#{summed_table_id_column}\#{summed_column_slug}__\#{main_table_fk_column}__\#{summed_table_fk_column}\"\n    join_function_name = opts[:join_function_name] || \"pgt_stmc_join_\#{main_table}__\#{main_table_id_column}__\#{sum_column}__\#{summed_table}__\#{summed_table_id_column}\#{summed_column_slug}__\#{join_table}__\#{main_table_fk_column}__\#{summed_table_fk_column}\"\n\n    orig_summed_table = summed_table\n    orig_join_table = join_table\n\n    main_table = quote_schema_table(main_table)\n    main_table_id_column = quote_schema_table(main_table_id_column)\n    sum_column = quote_schema_table(sum_column)\n\n    general_summed_column = literal(Sequel.deep_qualify(summed_table, summed_column))\n    new_table_summed_column = literal(Sequel.deep_qualify(Sequel.lit(\"NEW\"), summed_column))\n    old_table_summed_column = literal(Sequel.deep_qualify(Sequel.lit(\"OLD\"), summed_column))\n\n    summed_table = quote_schema_table(summed_table)\n    summed_table_id_column = quote_schema_table(summed_table_id_column)\n    join_table = quote_schema_table(join_table)\n    main_table_fk_column = quote_schema_table(main_table_fk_column)\n    summed_table_fk_column = quote_schema_table(summed_table_fk_column)\n\n    pgt_trigger(orig_summed_table, trigger_name, function_name, [:insert, :delete, :update], <<-SQL)\n    BEGIN\n      IF (TG_OP = 'UPDATE' AND NEW.\#{summed_table_id_column} = OLD.\#{summed_table_id_column}) THEN\n        UPDATE \#{main_table} SET \#{sum_column} = \#{sum_column} + \#{new_table_summed_column} - \#{old_table_summed_column} WHERE \#{main_table_id_column} IN (SELECT \#{main_table_fk_column} FROM \#{join_table} WHERE \#{summed_table_fk_column} = NEW.\#{summed_table_id_column});\n      ELSE\n        IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN\n          UPDATE \#{main_table} SET \#{sum_column} = \#{sum_column} + \#{new_table_summed_column} WHERE \#{main_table_id_column} IN (SELECT \#{main_table_fk_column} FROM \#{join_table} WHERE \#{summed_table_fk_column} = NEW.\#{summed_table_id_column});\n        END IF;\n        IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') THEN\n          UPDATE \#{main_table} SET \#{sum_column} = \#{sum_column} - \#{old_table_summed_column} WHERE \#{main_table_id_column} IN (SELECT \#{main_table_fk_column} FROM \#{join_table} WHERE \#{summed_table_fk_column} = OLD.\#{summed_table_id_column});\n        END IF;\n      END IF;\n      IF (TG_OP = 'DELETE') THEN\n        RETURN OLD;\n      END IF;\n      RETURN NEW;\n    END;\n    SQL\n\n    pgt_trigger(orig_join_table, join_trigger_name, join_function_name, [:insert, :delete, :update], <<-SQL)\n    BEGIN\n      IF (NOT (TG_OP = 'UPDATE' AND NEW.\#{main_table_fk_column} = OLD.\#{main_table_fk_column} AND NEW.\#{summed_table_fk_column} = OLD.\#{summed_table_fk_column})) THEN\n        IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN\n          UPDATE \#{main_table} SET \#{sum_column} = \#{sum_column} + (SELECT \#{general_summed_column} FROM \#{summed_table} WHERE \#{summed_table_id_column} = NEW.\#{summed_table_fk_column}) WHERE \#{main_table_id_column} = NEW.\#{main_table_fk_column};\n        END IF;\n        IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') THEN\n          UPDATE \#{main_table} SET \#{sum_column} = \#{sum_column} - (SELECT \#{general_summed_column} FROM \#{summed_table} WHERE \#{summed_table_id_column} = OLD.\#{summed_table_fk_column}) WHERE \#{main_table_id_column} = OLD.\#{main_table_fk_column};\n        END IF;\n      END IF;\n      IF (TG_OP = 'DELETE') THEN\n        RETURN OLD;\n      END IF;\n      RETURN NEW;\n    END;\n    SQL\n  end\n\n  def pgt_touch(main_table, touch_table, column, expr, opts={})\n    trigger_name = opts[:trigger_name] || \"pgt_t_\#{main_table}__\#{touch_table}\"\n    function_name = opts[:function_name] || \"pgt_t_\#{main_table}__\#{touch_table}\"\n    cond = lambda{|source| expr.map{|k,v| \"\#{quote_identifier(k)} = \#{source}.\#{quote_identifier(v)}\"}.join(\" AND \")}\n    same_id = expr.map{|k,v| \"NEW.\#{quote_identifier(v)} = OLD.\#{quote_identifier(v)}\"}.join(\" AND \")\n\n    table = quote_schema_table(touch_table)\n    col = quote_identifier(column)\n    update = lambda{|source| \" UPDATE \#{table} SET \#{col} = CURRENT_TIMESTAMP WHERE \#{cond[source]} AND ((\#{col} <> CURRENT_TIMESTAMP) OR (\#{col} IS NULL));\"}\n\n    sql = <<-SQL\n      BEGIN\n        IF (TG_OP = 'UPDATE' AND (\#{same_id})) THEN\n          \#{update['NEW']}\n        ELSE\n          IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN\n            \#{update['NEW']}\n          END IF;\n          IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') THEN\n            \#{update['OLD']}\n          END IF;\n        END IF;\n\n        IF (TG_OP = 'DELETE') THEN\n          RETURN OLD;\n        END IF;\n        RETURN NEW;\n      END;\n    SQL\n    pgt_trigger(main_table, trigger_name, function_name, [:insert, :delete, :update], sql, :after=>true)\n  end\n\n  def pgt_updated_at(table, column, opts={})\n    trigger_name = opts[:trigger_name] || \"pgt_ua_\#{column}\"\n    function_name = opts[:function_name] || \"pgt_ua_\#{table}__\#{column}\"\n    pgt_trigger(table, trigger_name, function_name, [:insert, :update], <<-SQL)\n    BEGIN\n      NEW.\#{quote_identifier(column)} := CURRENT_TIMESTAMP;\n      RETURN NEW;\n    END;\n    SQL\n  end\n\n  private\n\n  # Add or replace a function that returns trigger to handle the action,\n  # and add a trigger that calls the function.\n  def pgt_trigger(table, trigger_name, function_name, events, definition, opts={})\n    create_function(function_name, definition, :language=>:plpgsql, :returns=>:trigger, :replace=>true)\n    create_trigger(table, trigger_name, function_name, :events=>events, :each_row=>true, :after=>opts[:after])\n  end\nend\n")