From: Rogan Hamby <rhamby@equinoxinitiative.org>
Date: Wed, 27 May 2020 15:52:37 +0000 (-0400)
Subject: adding m_ for koha functions and an update_leader function
X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=commitdiff_plain;h=8bf3f76bd343179da9aa93f88f1c37a018b74ac4

adding m_ for koha functions and an update_leader function
---

diff --git a/sql/koha/general_functions.sql b/sql/koha/general_functions.sql
index 22ea79b..8dd0817 100644
--- a/sql/koha/general_functions.sql
+++ b/sql/koha/general_functions.sql
@@ -1,7 +1,7 @@
-DROP FUNCTION IF EXISTS split_string;
+DROP FUNCTION IF EXISTS m_split_string;
 DELIMITER $
 CREATE FUNCTION 
-   split_string (s TEXT, del VARCHAR(10), i INT)
+   m_split_string (s TEXT, del VARCHAR(10), i INT)
    RETURNS TEXT
    DETERMINISTIC
     BEGIN
@@ -16,10 +16,10 @@ CREATE FUNCTION
 $
 DELIMITER ;
 
-DROP FUNCTION IF EXISTS string_segment_count;
+DROP FUNCTION IF EXISTS m_string_segment_count;
 DELIMITER $
 CREATE FUNCTION 
-   string_segment_count(s TEXT, del VARCHAR(10))
+   m_string_segment_count(s TEXT, del VARCHAR(10))
    RETURNS TEXT
    DETERMINISTIC
     BEGIN
@@ -30,10 +30,10 @@ CREATE FUNCTION
 $
 DELIMITER ;
 
-DROP FUNCTION IF EXISTS remove_bracketed_text;
+DROP FUNCTION IF EXISTS m_remove_bracketed_text;
 DELIMITER $
 CREATE FUNCTION 
-   remove_bracketed_text(str TEXT)
+   m_remove_bracketed_text(str TEXT)
    RETURNS TEXT
    DETERMINISTIC
     BEGIN
diff --git a/sql/koha/marc_functions.sql b/sql/koha/marc_functions.sql
new file mode 100644
index 0000000..08d5e8e
--- /dev/null
+++ b/sql/koha/marc_functions.sql
@@ -0,0 +1,27 @@
+
+-- pass it the biblionumber, the position of the character to start in the leader starting at 0 
+-- and the text to replace, single characer or multiple 
+DROP FUNCTION IF EXISTS m_update_leader;
+DELIMITER $
+CREATE FUNCTION
+   m_update_leader(bnumber INTEGER, ldr_position SMALLINT, new_value TEXT)
+   RETURNS BOOLEAN
+   DETERMINISTIC
+    BEGIN
+        DECLARE ldr TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL;
+        DECLARE new_value_length SMALLINT DEFAULT 1;
+        SET ldr_position = ldr_position + 1;
+        SET new_value_length = LENGTH(new_value);
+        
+        SELECT ExtractValue(metadata, '//leader') INTO ldr FROM biblio_metadata WHERE biblionumber = bnumber;
+        SET ldr = INSERT(ldr,ldr_position,new_value_length,new_value);
+        SET ldr = CONCAT('<leader>',ldr,'</leader>');
+
+        UPDATE biblio_metadata SET metadata = UpdateXML(metadata, '//leader', ldr) where biblionumber = bnumber;
+        RETURN TRUE;
+        
+    END
+$
+DELIMITER ;
+
+