From 2db3025cacf2d60d016ae0759ff2ac39f36a3d92 Mon Sep 17 00:00:00 2001
From: Jason Etheridge <jason@esilibrary.com>
Date: Tue, 3 Feb 2015 12:13:21 -0500
Subject: [PATCH] proc to clone a/t event definitions

ubuntudeskfx=# SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
INFO:  created atevdef with id = 112
INFO:  created atevdef with id = 113
INFO:  -- UPDATE action_trigger.event_definition SET active = CASE WHEN id = 5 THEN FALSE ELSE TRUE END WHERE id in (5,112,113);
 duplicate_template
--------------------

(1 row)

ubuntudeskfx=# UPDATE action_trigger.event_definition SET active = CASE WHEN id = 5 THEN FALSE ELSE TRUE END WHERE id in (5,112,113);
UPDATE 3

Signed-off-by: Jason Etheridge <jason@esilibrary.com>
---
 sql/base/base.sql |   86 +++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 files changed, 86 insertions(+), 0 deletions(-)

diff --git a/sql/base/base.sql b/sql/base/base.sql
index c61f758..ca9a568 100644
--- a/sql/base/base.sql
+++ b/sql/base/base.sql
@@ -1934,3 +1934,89 @@ BEGIN
 END
 $func$
 LANGUAGE PLPGSQL;
+
+-- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
+CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
+    DECLARE
+        target_event_def ALIAS FOR $1;
+        orgs ALIAS FOR $2;
+    BEGIN
+        DROP TABLE IF EXISTS new_atevdefs;
+        CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
+        FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
+            INSERT INTO action_trigger.event_definition (
+                active
+                ,owner
+                ,name
+                ,hook
+                ,validator
+                ,reactor
+                ,cleanup_success
+                ,cleanup_failure
+                ,delay
+                ,max_delay
+                ,usr_field
+                ,opt_in_setting
+                ,delay_field
+                ,group_field
+                ,template
+                ,granularity
+                ,repeat_delay
+            ) SELECT
+                'f'
+                ,orgs[i]
+                ,name || ' (clone of '||target_event_def||')'
+                ,hook
+                ,validator
+                ,reactor
+                ,cleanup_success
+                ,cleanup_failure
+                ,delay
+                ,max_delay
+                ,usr_field
+                ,opt_in_setting
+                ,delay_field
+                ,group_field
+                ,template
+                ,granularity
+                ,repeat_delay
+            FROM
+                action_trigger.event_definition
+            WHERE
+                id = target_event_def
+            ;
+            RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
+            INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
+            INSERT INTO action_trigger.environment (
+                event_def
+                ,path
+                ,collector
+                ,label
+            ) SELECT
+                currval('action_trigger.event_definition_id_seq')
+                ,path
+                ,collector
+                ,label
+            FROM
+                action_trigger.environment
+            WHERE
+                event_def = target_event_def
+            ;
+            INSERT INTO action_trigger.event_params (
+                event_def
+                ,param
+                ,value
+            ) SELECT
+                currval('action_trigger.event_definition_id_seq')
+                ,param
+                ,value
+            FROM
+                action_trigger.event_params
+            WHERE
+                event_def = target_event_def
+            ;
+        END LOOP;
+        RAISE INFO '-- UPDATE action_trigger.event_definition SET active = CASE WHEN id = % THEN FALSE ELSE TRUE END WHERE id in (%,%);', target_event_def, target_event_def, (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
+    END;
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
-- 
1.7.2.5