From 65685d89a1c48fa109dbfb73f61ef2cb778daf95 Mon Sep 17 00:00:00 2001
From: Rogan Hamby <rhamby@equinoxinitiative.org>
Date: Tue, 2 Jun 2020 09:49:23 -0400
Subject: [PATCH] adding the start of base sql and marc functions for koha to mig-init

---
 kmig.d/sql/init/020-string_functions.sql |   43 ++++++++++++++++++++++++++++++
 kmig.d/sql/init/030-marc_functions.sql   |   27 ++++++++++++++++++
 2 files changed, 70 insertions(+), 0 deletions(-)
 create mode 100644 kmig.d/sql/init/020-string_functions.sql
 create mode 100644 kmig.d/sql/init/030-marc_functions.sql

diff --git a/kmig.d/sql/init/020-string_functions.sql b/kmig.d/sql/init/020-string_functions.sql
new file mode 100644
index 0000000..22469cd
--- /dev/null
+++ b/kmig.d/sql/init/020-string_functions.sql
@@ -0,0 +1,43 @@
+DELIMITER $
+CREATE FUNCTION
+   m_remove_bracketed_text(str TEXT)
+   RETURNS TEXT
+   DETERMINISTIC
+    BEGIN
+    RETURN REPLACE(str, SUBSTRING(str, LOCATE('(', str), LENGTH(str) - LOCATE(')', REVERSE(str)) - LOCATE('(', str) + 2), '');
+    END
+$
+DELIMITER ;
+
+DROP FUNCTION IF EXISTS m_split_string;
+DELIMITER $
+CREATE FUNCTION 
+   m_split_string (s TEXT, del VARCHAR(10), i INT)
+   RETURNS TEXT
+   DETERMINISTIC
+    BEGIN
+        DECLARE n INT ;
+        SET n = LENGTH(s) - LENGTH(REPLACE(s, del, '')) + 1;
+        IF i > n THEN
+            RETURN NULL ;
+        ELSE
+            RETURN SUBSTRING_INDEX(SUBSTRING_INDEX(s, del, i) , del , -1 ) ;        
+        END IF;
+    END
+$
+DELIMITER ;
+
+DROP FUNCTION IF EXISTS m_string_segment_count;
+DELIMITER $
+CREATE FUNCTION 
+   m_string_segment_count(s TEXT, del VARCHAR(10))
+   RETURNS TEXT
+   DETERMINISTIC
+    BEGIN
+        DECLARE n INT ;
+        SET n = LENGTH(s) - LENGTH(REPLACE(s, del, '')) + 1;
+        RETURN n;
+    END
+$
+DELIMITER ;
+
diff --git a/kmig.d/sql/init/030-marc_functions.sql b/kmig.d/sql/init/030-marc_functions.sql
new file mode 100644
index 0000000..08d5e8e
--- /dev/null
+++ b/kmig.d/sql/init/030-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 ;
+
+
-- 
1.7.2.5