X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=sql%2Fbase%2Fbase.sql;h=a333a1b3e4bd31b8bd2bae4956e9d88a7ba57dd3;hp=4e8d5bdf2754e23ae7b5c2dc5c2104061f76459f;hb=46bd95616923cd2de0b1ed6b7a0fd8ffd0b103e2;hpb=2e53c25a974483f4d2519682d38455401cbde08e

diff --git a/sql/base/base.sql b/sql/base/base.sql
index 4e8d5bd..a333a1b 100644
--- a/sql/base/base.sql
+++ b/sql/base/base.sql
@@ -2611,6 +2611,107 @@ CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (
     END;
 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 
+-- example: SELECT * FROM migration_tools.duplicate_templates(3,'{5,6}');
+CREATE OR REPLACE FUNCTION migration_tools.duplicate_templates (INTEGER, INTEGER[]) RETURNS VOID AS $$
+    DECLARE
+        org ALIAS FOR $1;
+        target_event_defs ALIAS FOR $2;
+    BEGIN
+        DROP TABLE IF EXISTS new_atevdefs;
+        CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
+        FOR i IN array_lower(target_event_defs,1) .. array_upper(target_event_defs,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'
+                ,org
+                ,name || ' (clone of '||target_event_defs[i]||')'
+                ,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_defs[i]
+            ;
+            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_defs[i]
+            ;
+            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_defs[i]
+            ;
+        END LOOP;
+        RAISE INFO '-- UPDATE action_trigger.event_definition SET active = TRUE WHERE id in (%);', (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
+    END;
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
+CREATE OR REPLACE FUNCTION migration_tools.reset_event (BIGINT) RETURNS VOID AS $$
+    UPDATE
+        action_trigger.event
+    SET
+         start_time = NULL
+        ,update_time = NULL
+        ,complete_time = NULL
+        ,update_process = NULL
+        ,state = 'pending'
+        ,template_output = NULL
+        ,error_output = NULL
+        ,async_output = NULL
+    WHERE
+        id = $1;
+$$ LANGUAGE SQL;
+
 CREATE OR REPLACE FUNCTION migration_tools.get_marc_leader (TEXT) RETURNS TEXT AS $$
     my ($marcxml) = @_;
 
@@ -3344,6 +3445,54 @@ return $marc_xml->as_xml_record();
 
 $function$;
 
+-- yet another subfield 9 function, this one only adds the $9 and forces
+-- ind1 = 4 if not already ind1 = 1 or 4 and ind2 = 0 if not already ind2 = 0 or 1
+DROP FUNCTION IF EXISTS migration_tools.force_add_sf9(TEXT,TEXT);
+CREATE OR REPLACE FUNCTION migration_tools.force_add_sf9(marc TEXT, new_9 TEXT)
+ RETURNS TEXT
+ LANGUAGE plperlu
+AS $function$
+use strict;
+use warnings;
+
+use MARC::Record;
+use MARC::File::XML (BinaryEncoding => 'utf8');
+
+binmode(STDERR, ':bytes');
+binmode(STDOUT, ':utf8');
+binmode(STDERR, ':utf8');
+
+my $marc_xml = shift;
+my $new_9_to_set = shift;
+
+$marc_xml =~ s/(<leader>.........)./${1}a/;
+
+eval {
+    $marc_xml = MARC::Record->new_from_xml($marc_xml);
+};
+if ($@) {
+    #elog("could not parse $bibid: $@\n");
+    import MARC::File::XML (BinaryEncoding => 'utf8');
+    return $marc_xml;
+}
+
+my @uris = $marc_xml->field('856');
+return $marc_xml->as_xml_record() unless @uris;
+
+foreach my $field (@uris) {
+    my $ind1 = $field->indicator('1');
+    if (!defined $ind1) { next; }
+    if ($ind1 ne '1' && $ind1 ne '4') { $field->set_indicator(1,'4'); }
+    my $ind2 = $field->indicator('2');
+    if (!defined $ind2) { next; }
+    if ($ind2 ne '0' && $ind2 ne '1') { $field->set_indicator(2,'0'); }
+    $field->add_subfields( '9' => $new_9_to_set );
+}
+
+return $marc_xml->as_xml_record();
+
+$function$;
+
 -- alternate adding subfield 9 function in that it adds them to existing tags where the 856$u matches a correct value only
 DROP FUNCTION IF EXISTS migration_tools.add_sf9(TEXT,TEXT,TEXT);
 CREATE OR REPLACE FUNCTION migration_tools.add_sf9(marc TEXT, partial_u TEXT, new_9 TEXT)
@@ -3384,7 +3533,7 @@ foreach my $field (@uris) {
     if (!defined $ind2) { next; }
     if ($ind2 ne '0') { next; }
     if (!defined $sfu) { next; }
-    if ($sfu =~ m/$matching_u_text/) {
+    if ($sfu =~ m/$matching_u_text/ or $matching_u_text eq 'pineapple') {
         $field->add_subfields( '9' => $new_9_to_set );
         last;
     }
@@ -3406,7 +3555,7 @@ BEGIN
 
     EXECUTE 'SELECT marc FROM ' || bib_table || ' WHERE id = ' || bib_id INTO source_xml;
 
-    SELECT add_sf9(source_xml, target_u_text, sf9_text) INTO new_xml;
+    SELECT migration_tools.add_sf9(source_xml, target_u_text, sf9_text) INTO new_xml;
 
     r = FALSE;
 	new_xml = '$_$' || new_xml || '$_$';
@@ -3421,6 +3570,45 @@ BEGIN
 END;
 $BODY$ LANGUAGE plpgsql;
 
+-- strip marc tag
+DROP FUNCTION IF EXISTS migration_tools.strip_tag(TEXT,TEXT);
+CREATE OR REPLACE FUNCTION migration_tools.strip_tag(marc TEXT, tag TEXT)
+ RETURNS TEXT
+ LANGUAGE plperlu
+AS $function$
+use strict;
+use warnings;
+
+use MARC::Record;
+use MARC::File::XML (BinaryEncoding => 'utf8');
+
+binmode(STDERR, ':bytes');
+binmode(STDOUT, ':utf8');
+binmode(STDERR, ':utf8');
+
+my $marc_xml = shift;
+my $tag = shift;
+
+$marc_xml =~ s/(<leader>.........)./${1}a/;
+
+eval {
+    $marc_xml = MARC::Record->new_from_xml($marc_xml);
+};
+if ($@) {
+    #elog("could not parse $bibid: $@\n");
+    import MARC::File::XML (BinaryEncoding => 'utf8');
+    return $marc_xml;
+}
+
+my @fields = $marc_xml->field($tag);
+return $marc_xml->as_xml_record() unless @fields;
+
+$marc_xml->delete_fields(@fields);
+
+return $marc_xml->as_xml_record();
+
+$function$;
+
 -- convenience function for linking to the item staging table
 
 CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$