Module: Sequel::Postgres
- Defined in:
- lib/sequel_postgresql_triggers.rb,
lib/sequel/extensions/pg_triggers.rb
Defined Under Namespace
Modules: DatabaseMethods, PGTMethods
Constant Summary collapse
- 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_#{pgt_mangled_table_name(main_table)}__#{main_table_id_column}__#{counter_column}__#{counted_table_id_column}" function_name = opts[:function_name] || "pgt_cc_#{pgt_mangled_table_name(main_table)}__#{main_table_id_column}__#{counter_column}__#{pgt_mangled_table_name(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_\#{pgt_mangled_table_name(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_force_defaults(table, defaults, opts={})\n cols = defaults.keys.sort.join('_')\n trigger_name = opts[:trigger_name] || \"pgt_fd_\#{cols}\"\n function_name = opts[:function_name] || \"pgt_fd_\#{pgt_mangled_table_name(table)}__\#{cols}\"\n lines = defaults.map do |column, v|\n \"NEW.\#{quote_identifier(column)} = \#{literal(v)};\"\n end\n pgt_trigger(table, trigger_name, function_name, [:insert], <<-SQL)\n BEGIN\n \#{lines.join(\"\\n\")}\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_json_audit_log_setup(table, opts={})\n function_name = opts[:function_name] || \"pgt_jal_\#{pgt_mangled_table_name(table)}\"\n create_table(table) do\n Bignum :txid, :null=>false, :index=>true\n DateTime :at, :default=>Sequel::CURRENT_TIMESTAMP, :null=>false\n String :user, :null=>false\n String :schema, :null=>false\n String :table, :null=>false\n String :action, :null=>false\n jsonb :prior, :null=>false\n end\n create_function(function_name, (<<-SQL), {:language=>:plpgsql, :returns=>:trigger, :replace=>true}.merge(opts[:function_opts]||{}))\n BEGIN\n INSERT INTO \#{quote_schema_table(table)} (txid, at, \"user\", \"schema\", \"table\", action, prior) VALUES\n (txid_current(), CURRENT_TIMESTAMP, CURRENT_USER, TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_OP, to_jsonb(OLD));\n IF (TG_OP = 'DELETE') THEN\n RETURN OLD;\n END IF;\n RETURN NEW;\n END;\n SQL\n function_name\n end\n\n def pgt_json_audit_log(table, function, opts={})\n create_trigger(table, (opts[:trigger_name] || \"pgt_jal_\#{pgt_mangled_table_name(table)}\"), function, :events=>[:update, :delete], :each_row=>true, :after=>true)\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_\#{pgt_mangled_table_name(main_table)}__\#{main_table_id_column}__\#{sum_column}__\#{summed_table_id_column}\"\n function_name = opts[:function_name] || \"pgt_sc_\#{pgt_mangled_table_name(main_table)}__\#{main_table_id_column}__\#{sum_column}__\#{pgt_mangled_table_name(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_\#{pgt_mangled_table_name(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_\#{pgt_mangled_table_name(main_table)}__\#{main_table_id_column}__\#{sum_column}__\#{pgt_mangled_table_name(summed_table)}__\#{summed_table_id_column}\#{summed_column_slug}__\#{pgt_mangled_table_name(join_table)}__\#{main_table_fk_column}__\#{summed_table_fk_column}\"\n join_trigger_name = opts[:join_trigger_name] || \"pgt_stmc_join_\#{pgt_mangled_table_name(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_\#{pgt_mangled_table_name(main_table)}__\#{main_table_id_column}__\#{sum_column}__\#{pgt_mangled_table_name(summed_table)}__\#{summed_table_id_column}\#{summed_column_slug}__\#{pgt_mangled_table_name(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_\#{pgt_mangled_table_name(main_table)}__\#{pgt_mangled_table_name(touch_table)}\"\n function_name = opts[:function_name] || \"pgt_t_\#{pgt_mangled_table_name(main_table)}__\#{pgt_mangled_table_name(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_\#{pgt_mangled_table_name(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 def pgt_foreign_key_array(opts={})\n table, column, rtable, rcolumn = opts.values_at(:table, :column, :referenced_table, :referenced_column)\n trigger_name = opts[:trigger_name] || \"pgt_fka_\#{column}\"\n function_name = opts[:function_name] || \"pgt_fka_\#{pgt_mangled_table_name(table)}__\#{column}\"\n rtrigger_name = opts[:referenced_trigger_name] || \"pgt_rfka_\#{column}\"\n rfunction_name = opts[:referenced_function_name] || \"pgt_rfka_\#{pgt_mangled_table_name(table)}__\#{column}\"\n col = quote_identifier(column)\n tab = quote_identifier(table)\n rcol = quote_identifier(rcolumn)\n rtab = quote_identifier(rtable)\n\n pgt_trigger(table, trigger_name, function_name, [:insert, :update], <<-SQL)\n DECLARE\n arr \#{tab}.\#{col}%TYPE;\n temp_count1 int;\n temp_count2 int;\n BEGIN\n arr := NEW.\#{col};\n temp_count1 := array_ndims(arr);\n IF arr IS NULL OR temp_count1 IS NULL THEN\n RETURN NEW;\n END IF;\n\n IF temp_count1 IS DISTINCT FROM 1 THEN\n RAISE EXCEPTION 'Foreign key array \#{tab}.\#{col} has more than 1 dimension: %, dimensions: %', arr, temp_count1;\n END IF;\n\n SELECT count(*) INTO temp_count1 FROM unnest(arr);\n SELECT count(*) INTO temp_count2 FROM (SELECT DISTINCT * FROM unnest(arr)) AS t;\n IF temp_count1 IS DISTINCT FROM temp_count2 THEN\n RAISE EXCEPTION 'Duplicate entry in foreign key array \#{tab}.\#{col}: %', arr;\n END IF;\n\n SELECT COUNT(*) INTO temp_count1 FROM \#{rtab} WHERE \#{rcol} = ANY(arr);\n temp_count2 := array_length(arr, 1);\n IF temp_count1 IS DISTINCT FROM temp_count2 THEN\n RAISE EXCEPTION 'Entry in foreign key array \#{tab}.\#{col} not in referenced column \#{rtab}.\#{rcol}: %', arr;\n END IF;\n\n RETURN NEW;\n END;\n SQL\n\n pgt_trigger(rtable, rtrigger_name, rfunction_name, [:delete, :update], <<-SQL)\n DECLARE\n val \#{rtab}.\#{rcol}%TYPE;\n temp_count int;\n BEGIN\n val := OLD.\#{rcol};\n IF (TG_OP = 'DELETE') OR val IS DISTINCT FROM NEW.\#{rcol} THEN\n SELECT COUNT(*) INTO temp_count FROM \#{tab} WHERE \#{col} @> ARRAY[val];\n IF temp_count IS DISTINCT FROM 0 THEN\n RAISE EXCEPTION 'Entry in referenced column \#{rtab}.\#{rcol} still in foreign key array \#{tab}.\#{col}: %, count: %', val, temp_count;\n END IF;\n END IF;\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\n\n # Mangle the schema name so it can be used in an unquoted_identifier\n def pgt_mangled_table_name(table)\n quote_schema_table(table).gsub('\"', '').gsub(/[^A-Za-z0-9]/, '_').gsub(/_+/, '_')\n end\nend\n")