From 100bc026f9815cf5dd716cfa5e6aec96bd919cd8 Mon Sep 17 00:00:00 2001
From: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com>
Date: Thu, 28 Nov 2019 19:06:35 -0300
Subject: [PATCH] Bug 13897: Use XML files for installer data

This patch modifies C4/Installer.pm to add support
for loading XML files into database (MySQL only).

As an example of the functionality, optional
auth_val.sql file is replaced by auth_val.xml

The rationale behind this feature is to enable the
translation of the data that is loaded into the
database. That will be addressed in another bug.

But taking into account that goal, non translatable
values are put into XML attributes, translatable ones
into elements which can already be captured by
translate script.

To test:
0) Do a clean install with all optional data,
   then dump authorised_values table, reserve.
1) Apply the patch
2) Do a clean install in English (marc21/unimarc)
3) On optional data check for description of auth_val
   "Some basic default authorised values for ..."
4) Select all optional data
5) Finish installation
6) Dump again authorised_values table and compare with that
   of point '0'. There must be no differences.
---
 C4/Installer.pm                               |  30 +++-
 installer/data/mysql/en/optional/auth_val.sql |  86 ----------
 installer/data/mysql/en/optional/auth_val.xml | 234 ++++++++++++++++++++++++++
 3 files changed, 255 insertions(+), 95 deletions(-)
 delete mode 100644 installer/data/mysql/en/optional/auth_val.sql
 create mode 100644 installer/data/mysql/en/optional/auth_val.xml

diff --git a/C4/Installer.pm b/C4/Installer.pm
index 0a6fca8..5c3bbd5 100644
--- a/C4/Installer.pm
+++ b/C4/Installer.pm
@@ -21,6 +21,7 @@ use Modern::Perl;
 
 use Encode qw( encode is_utf8 );
 use DBIx::RunSQL;
+use XML::LibXML;
 use C4::Context;
 use C4::Installer::PerlModules;
 use DBI;
@@ -145,7 +146,7 @@ sub marc_framework_sql_list {
 
     foreach my $requirelevel (@listdir) {
         opendir( MYDIR, "$dir/$requirelevel" );
-        my @listname = grep { !/^\./ && -f "$dir/$requirelevel/$_" && $_ =~ m/\.sql$/ } readdir(MYDIR);
+        my @listname = grep { !/^\./ && -f "$dir/$requirelevel/$_" && $_ =~ m/\.(sql|xml)$/ } readdir(MYDIR);
         closedir MYDIR;
         my %cell;
         my @frameworklist;
@@ -222,7 +223,7 @@ sub sample_data_sql_list {
 
     foreach my $requirelevel (@listdir) {
         opendir( MYDIR, "$dir/$requirelevel" );
-        my @listname = grep { !/^\./ && -f "$dir/$requirelevel/$_" && $_ =~ m/\.sql$/ } readdir(MYDIR);
+        my @listname = grep { !/^\./ && -f "$dir/$requirelevel/$_" && $_ =~ m/\.(sql|xml)$/ } readdir(MYDIR);
         closedir MYDIR;
         my %cell;
         my @frameworklist;
@@ -439,7 +440,7 @@ sub set_version_syspref {
 
   my $error = $installer->load_sql($filename);
 
-Runs a the specified SQL file using a sql loader DBIx::RunSQL
+Runs the specified input file using a sql loader DBIx::RunSQL, or a xml loader
 Returns any strings sent to STDERR
 
 # FIXME This should be improved: sometimes the caller and load_sql warn the same
@@ -459,12 +460,23 @@ sub load_sql {
         local *STDERR;
         open STDERR, ">>", \$dup_stderr;
 
-        eval {
-            DBIx::RunSQL->run_sql_file(
-                dbh     => $dbh,
-                sql     => $filename,
-            );
-        };
+        if ( $filename =~ /sql$/ ) {                                                        # SQL files
+            eval {
+                DBIx::RunSQL->run_sql_file(
+                    dbh     => $dbh,
+                    sql     => $filename,
+                );
+            };
+        }
+        else {                                                                              # XML files
+            eval {
+                my $dom     = XML::LibXML->load_xml( location => $filename );               # Load XML
+                my $table   = $dom->documentElement->nodeName;                              # Find table name
+                my $query   = "LOAD XML LOCAL INFILE '".$filename."' INTO TABLE ".$table;   # MySQL only
+                my $loadxml = $dbh->prepare($query);
+                $loadxml->execute();
+            };
+        }
     };
     #   errors thrown while loading installer data should be logged
     if( $dup_stderr ) {
diff --git a/installer/data/mysql/en/optional/auth_val.sql b/installer/data/mysql/en/optional/auth_val.sql
deleted file mode 100644
index 8d4821a..0000000
--- a/installer/data/mysql/en/optional/auth_val.sql
+++ /dev/null
@@ -1,86 +0,0 @@
--- Reasons for acceptance or rejection of suggestions in acquisitions
-INSERT INTO authorised_values (category, authorised_value, lib) VALUES ('SUGGEST','BSELL','Bestseller');
-INSERT INTO authorised_values (category, authorised_value, lib) VALUES ('SUGGEST','SCD','Shelf Copy Damaged');
-INSERT INTO authorised_values (category, authorised_value, lib) VALUES ('SUGGEST','LCL','Library Copy Lost');
-INSERT INTO authorised_values (category, authorised_value, lib) VALUES ('SUGGEST','AVILL','Available via ILL');
-
--- Desired formats for requesting new materials
-INSERT INTO authorised_values (category, authorised_value, lib, lib_opac) VALUES ('SUGGEST_FORMAT', 'BOOK', 'Book', 'Book');
-INSERT INTO authorised_values (category, authorised_value, lib, lib_opac) VALUES ('SUGGEST_FORMAT', 'LP', 'Large print', 'Large print');
-INSERT INTO authorised_values (category, authorised_value, lib, lib_opac) VALUES ('SUGGEST_FORMAT', 'EBOOK', 'EBook', 'Ebook');
-INSERT INTO authorised_values (category, authorised_value, lib, lib_opac) VALUES ('SUGGEST_FORMAT', 'AUDIOBOOK', 'Audiobook', 'Audiobook');
-INSERT INTO authorised_values (category, authorised_value, lib, lib_opac) VALUES ('SUGGEST_FORMAT', 'DVD', 'DVD', 'DVD');
-
--- availability statuses
-INSERT INTO `authorised_values`  (category, authorised_value, lib) VALUES ('LOST','2','Long Overdue (Lost)');
-INSERT INTO `authorised_values` (category, authorised_value, lib) VALUES ('LOST','1','Lost');
-INSERT INTO `authorised_values`  (category, authorised_value, lib ) VALUES ('LOST','3','Lost and Paid For');
-INSERT INTO `authorised_values`  (category, authorised_value, lib )VALUES ('LOST','4','Missing');
-
--- damaged status of an item
-INSERT INTO `authorised_values` (category, authorised_value, lib) VALUES ('DAMAGED','1','Damaged');
-
--- location qualification for an item, departments are linked by default to items.location
-INSERT INTO `authorised_values` (category, authorised_value, lib) VALUES ('LOC','FIC','Fiction');
-INSERT INTO `authorised_values` (category, authorised_value, lib) VALUES ('LOC','CHILD','Children\'s Area');
-INSERT INTO `authorised_values` (category, authorised_value, lib) VALUES ('LOC','DISPLAY','On Display');
-INSERT INTO `authorised_values` (category, authorised_value, lib) VALUES ('LOC','NEW','New Materials Shelf');
-INSERT INTO `authorised_values` (category, authorised_value, lib) VALUES ('LOC','STAFF','Staff Office');
-INSERT INTO `authorised_values` (category, authorised_value, lib) VALUES ('LOC','GEN','General Stacks');
-INSERT INTO `authorised_values` (category, authorised_value, lib) VALUES ('LOC','AV','Audio Visual');
-INSERT INTO `authorised_values` (category, authorised_value, lib) VALUES ('LOC','REF','Reference');
-INSERT INTO `authorised_values` (category, authorised_value, lib) VALUES ('LOC','CART','Book Cart');
-INSERT INTO `authorised_values` (category, authorised_value, lib) VALUES ('LOC','PROC','Processing Center');
-
--- collection codes for an item
-INSERT INTO `authorised_values` (category, authorised_value, lib) VALUES ('CCODE','FIC','Fiction');
-INSERT INTO `authorised_values` (category, authorised_value, lib) VALUES ('CCODE','REF','Reference');
-INSERT INTO `authorised_values` (category, authorised_value, lib) VALUES ('CCODE','NFIC','Non-fiction');
-
--- withdrawn status of an item, linked to items.withdrawn
-INSERT INTO `authorised_values` (category, authorised_value, lib) VALUES ('WITHDRAWN','1','Withdrawn');
-
--- loanability status of an item, linked to items.notforloan
-INSERT INTO `authorised_values` (category, authorised_value, lib) VALUES ('NOT_LOAN','-1','Ordered');
-INSERT INTO `authorised_values` (category, authorised_value, lib) VALUES ('NOT_LOAN','1','Not For Loan');
-INSERT INTO `authorised_values` (category, authorised_value, lib) VALUES ('NOT_LOAN','2','Staff Collection');
-
--- restricted status of an item, linked to items.restricted
-INSERT INTO `authorised_values` (category, authorised_value, lib) VALUES ('RESTRICTED','1','Restricted Access');
-
--- custom borrower notes
-INSERT INTO `authorised_values` (category, authorised_value, lib) VALUES ('BOR_NOTES','ADDR','Address Notes');
-
--- OPAC Suggestions reasons
-INSERT INTO authorised_values (category,authorised_value,lib,lib_opac) VALUES ('OPAC_SUG','damaged','The copy on the shelf is damaged','The copy on the shelf is damaged');
-INSERT INTO authorised_values (category,authorised_value,lib,lib_opac) VALUES ('OPAC_SUG','bestseller','Upcoming title by popular author','Upcoming title by popular author');
-
--- Report groups
-INSERT INTO authorised_values (category, authorised_value, lib) VALUES ('REPORT_GROUP', 'CIRC', 'Circulation');
-INSERT INTO authorised_values (category, authorised_value, lib) VALUES ('REPORT_GROUP', 'CAT', 'Catalog');
-INSERT INTO authorised_values (category, authorised_value, lib) VALUES ('REPORT_GROUP', 'PAT', 'Patrons');
-INSERT INTO authorised_values (category, authorised_value, lib) VALUES ('REPORT_GROUP', 'ACQ', 'Acquisitions');
-INSERT INTO authorised_values (category, authorised_value, lib) VALUES ('REPORT_GROUP', 'ACC', 'Accounts');
-INSERT INTO authorised_values (category, authorised_value, lib) VALUES ('REPORT_GROUP', 'SER', 'Serials');
-
--- SIP2 media types
-INSERT INTO authorised_values (category, authorised_value, lib) VALUES ('SIP_MEDIA_TYPE', '000', 'Other');
-INSERT INTO authorised_values (category, authorised_value, lib) VALUES ('SIP_MEDIA_TYPE', '001', 'Book');
-INSERT INTO authorised_values (category, authorised_value, lib) VALUES ('SIP_MEDIA_TYPE', '002', 'Magazine');
-INSERT INTO authorised_values (category, authorised_value, lib) VALUES ('SIP_MEDIA_TYPE', '003', 'Bound journal');
-INSERT INTO authorised_values (category, authorised_value, lib) VALUES ('SIP_MEDIA_TYPE', '004', 'Audio tape');
-INSERT INTO authorised_values (category, authorised_value, lib) VALUES ('SIP_MEDIA_TYPE', '005', 'Video tape');
-INSERT INTO authorised_values (category, authorised_value, lib) VALUES ('SIP_MEDIA_TYPE', '006', 'CD/CDROM');
-INSERT INTO authorised_values (category, authorised_value, lib) VALUES ('SIP_MEDIA_TYPE', '007', 'Diskette');
-INSERT INTO authorised_values (category, authorised_value, lib) VALUES ('SIP_MEDIA_TYPE', '008', 'Book with diskette');
-INSERT INTO authorised_values (category, authorised_value, lib) VALUES ('SIP_MEDIA_TYPE', '009', 'Book with CD');
-INSERT INTO authorised_values (category, authorised_value, lib) VALUES ('SIP_MEDIA_TYPE', '010', 'Book with audio tape');
-
--- order cancellation reasons
-INSERT INTO authorised_values (category, authorised_value, lib) VALUES ('ORDER_CANCELLATION_REASON', 0, 'No reason provided');
-INSERT INTO authorised_values (category, authorised_value, lib) VALUES ('ORDER_CANCELLATION_REASON', 1, 'Out of stock');
-INSERT INTO authorised_values (category, authorised_value, lib) VALUES ('ORDER_CANCELLATION_REASON', 2, 'Restocking');
-
--- return claims
-INSERT INTO authorised_values (category, authorised_value, lib) VALUES ('RETURN_CLAIM_RESOLUTION', 'RET_BY_PATRON', 'Returned by patron');
-INSERT INTO authorised_values (category, authorised_value, lib) VALUES ('RETURN_CLAIM_RESOLUTION', 'FOUND_IN_LIB',  'Found in library');
diff --git a/installer/data/mysql/en/optional/auth_val.xml b/installer/data/mysql/en/optional/auth_val.xml
new file mode 100644
index 0000000..b43aec5
--- /dev/null
+++ b/installer/data/mysql/en/optional/auth_val.xml
@@ -0,0 +1,234 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+   Some basic default authorised values for library locations, item lost status, etc.
+   You can change these at any time after installation.
+
+   Copyright 2019 Koha Development Team
+
+   This file is part of Koha.
+
+   Koha is free software; you can redistribute it and/or modify it under the
+   terms of the GNU General Public License as published by the Free Software
+   Foundation; either version 2 of the License, or (at your option) any later
+   version.
+
+   Koha is distributed in the hope that it will be useful, but WITHOUT ANY
+   WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
+   A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
+
+   You should have received a copy of the GNU General Public License along
+   with Koha; if not, write to the Free Software Foundation, Inc.,
+   51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
+-->
+
+<authorised_values xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
+
+    <!-- Reasons for acceptance or rejection of suggestions in acquisitions -->
+    <row category="SUGGEST" authorised_value="BSELL">
+        <field name="lib">Bestseller</field>
+    </row>;
+    <row category="SUGGEST" authorised_value="SCD">
+        <field name="lib">Shelf Copy Damaged</field>
+    </row>;
+    <row category="SUGGEST" authorised_value="LCL">
+        <field name="lib">Library Copy Lost</field>
+    </row>;
+    <row category="SUGGEST" authorised_value="AVILL">
+        <field name="lib">Available via ILL</field>
+    </row>;
+
+    <!-- Desired formats for requesting new materials -->
+    <row category="SUGGEST_FORMAT" authorised_value="BOOK">
+        <field name="lib">Book</field>
+        <field name="lib_opac">Book</field>
+    </row>;
+    <row category="SUGGEST_FORMAT" authorised_value="LP">
+        <field name="lib">Large print</field>
+        <field name="lib_opac">Large print</field>
+    </row>;
+    <row category="SUGGEST_FORMAT" authorised_value="EBOOK">
+        <field name="lib">EBook</field>
+        <field name="lib_opac">Ebook</field>
+    </row>;
+    <row category="SUGGEST_FORMAT" authorised_value="AUDIOBOOK">
+        <field name="lib">Audiobook</field>
+        <field name="lib_opac">Audiobook</field>
+    </row>;
+    <row category="SUGGEST_FORMAT" authorised_value="DVD">
+        <field name="lib">DVD</field>
+        <field name="lib_opac">DVD</field>
+    </row>;
+
+    <!-- availability statuses -->
+    <row category="LOST" authorised_value="2">
+        <field name="lib">Long Overdue (Lost)</field>
+    </row>;
+    <row category="LOST" authorised_value="1">
+        <field name="lib">Lost</field>
+    </row>;
+    <row category="LOST" authorised_value="3">
+        <field name="lib">Lost and Paid For</field>
+    </row>;
+    <row category="LOST" authorised_value="4">
+        <field name="lib">Missing</field>
+    </row>;
+
+    <!-- damaged status of an item -->
+    <row category="DAMAGED" authorised_value="1">
+        <field name="lib">Damaged</field>
+    </row>;
+
+    <!-- location qualification for an item, departments are linked by default to items.location -->
+    <row category="LOC" authorised_value="FIC">
+        <field name="lib">Fiction</field>
+    </row>;
+    <row category="LOC" authorised_value="CHILD">
+        <field name="lib">Children&apos;s Area</field>
+    </row>;
+    <row category="LOC" authorised_value="DISPLAY">
+        <field name="lib">On Display</field>
+    </row>;
+    <row category="LOC" authorised_value="NEW">
+        <field name="lib">New Materials Shelf</field>
+    </row>;
+    <row category="LOC" authorised_value="STAFF">
+        <field name="lib">Staff Office</field>
+    </row>;
+    <row category="LOC" authorised_value="GEN">
+        <field name="lib">General Stacks</field>
+    </row>;
+    <row category="LOC" authorised_value="AV">
+        <field name="lib">Audio Visual</field>
+    </row>;
+    <row category="LOC" authorised_value="REF">
+        <field name="lib">Reference</field>
+    </row>;
+    <row category="LOC" authorised_value="CART">
+        <field name="lib">Book Cart</field>
+    </row>;
+    <row category="LOC" authorised_value="PROC">
+        <field name="lib">Processing Center</field>
+    </row>;
+
+    <!-- collection codes for an item -->
+    <row category="CCODE" authorised_value="FIC">
+        <field name="lib">Fiction</field>
+    </row>;
+    <row category="CCODE" authorised_value="REF">
+        <field name="lib">Reference</field>
+    </row>;
+    <row category="CCODE" authorised_value="NFIC">
+        <field name="lib">Non-fiction</field>
+    </row>;
+
+    <!-- withdrawn status of an item, linked to items.withdrawn -->
+    <row category="WITHDRAWN" authorised_value="1">
+        <field name="lib">Withdrawn</field>
+    </row>;
+
+    <!-- loanability status of an item, linked to items.notforloan -->
+    <row category="NOT_LOAN" authorised_value="-1">
+        <field name="lib">Ordered</field>
+    </row>;
+    <row category="NOT_LOAN" authorised_value="1">
+        <field name="lib">Not For Loan</field>
+    </row>;
+    <row category="NOT_LOAN" authorised_value="2">
+        <field name="lib">Staff Collection</field>
+    </row>;
+
+    <!-- restricted status of an item, linked to items.restricted -->
+    <row category="RESTRICTED" authorised_value="1">
+        <field name="lib">Restricted Access</field>
+    </row>;
+
+    <!-- custom borrower notes -->
+    <row category="BOR_NOTES" authorised_value="ADDR">
+        <field name="lib">Address Notes</field>
+    </row>;
+
+    <!-- OPAC Suggestions reasons -->
+    <row category="OPAC_SUG" authorised_value="damaged">
+        <field name="lib">The copy on the shelf is damaged</field>
+        <field name="lib_opac">The copy on the shelf is damaged</field>
+    </row>;
+    <row category="OPAC_SUG" authorised_value="bestseller">
+        <field name="lib">Upcoming title by popular author</field>
+        <field name="lib_opac">Upcoming title by popular author</field>
+    </row>;
+
+    <!-- Report groups -->
+    <row category="REPORT_GROUP" authorised_value="CIRC">
+        <field name="lib">Circulation</field>
+    </row>;
+    <row category="REPORT_GROUP" authorised_value="CAT">
+        <field name="lib">Catalog</field>
+    </row>;
+    <row category="REPORT_GROUP" authorised_value="PAT">
+        <field name="lib">Patrons</field>
+    </row>;
+    <row category="REPORT_GROUP" authorised_value="ACQ">
+        <field name="lib">Acquisitions</field>
+    </row>;
+    <row category="REPORT_GROUP" authorised_value="ACC">
+        <field name="lib">Accounts</field>
+    </row>;
+    <row category="REPORT_GROUP" authorised_value="SER">
+        <field name="lib">Serials</field>
+    </row>;
+
+    <!-- SIP2 media types -->
+    <row category="SIP_MEDIA_TYPE" authorised_value="000">
+        <field name="lib">Other</field>
+    </row>;
+    <row category="SIP_MEDIA_TYPE" authorised_value="001">
+        <field name="lib">Book</field>
+    </row>;
+    <row category="SIP_MEDIA_TYPE" authorised_value="002">
+        <field name="lib">Magazine</field>
+    </row>;
+    <row category="SIP_MEDIA_TYPE" authorised_value="003">
+        <field name="lib">Bound journal</field>
+    </row>;
+    <row category="SIP_MEDIA_TYPE" authorised_value="004">
+        <field name="lib">Audio tape</field>
+    </row>;
+    <row category="SIP_MEDIA_TYPE" authorised_value="005">
+        <field name="lib">Video tape</field>
+    </row>;
+    <row category="SIP_MEDIA_TYPE" authorised_value="006">
+        <field name="lib">CD/CDROM</field>
+    </row>;
+    <row category="SIP_MEDIA_TYPE" authorised_value="007">
+        <field name="lib">Diskette</field>
+    </row>;
+    <row category="SIP_MEDIA_TYPE" authorised_value="008">
+        <field name="lib">Book with diskette</field>
+    </row>;
+    <row category="SIP_MEDIA_TYPE" authorised_value="009">
+        <field name="lib">Book with CD</field>
+    </row>;
+    <row category="SIP_MEDIA_TYPE" authorised_value="010">
+        <field name="lib">Book with audio tape</field>
+    </row>;
+
+    <!-- order cancellation reasons -->
+    <row category="ORDER_CANCELLATION_REASON" authorised_value="0">
+        <field name="lib">No reason provided</field>
+    </row>;
+    <row category="ORDER_CANCELLATION_REASON" authorised_value="1">
+        <field name="lib">Out of stock</field>
+    </row>;
+    <row category="ORDER_CANCELLATION_REASON" authorised_value="2">
+        <field name="lib">Restocking</field>
+    </row>;
+
+    <!-- return claims -->
+    <row category="RETURN_CLAIM_RESOLUTION" authorised_value="RET_BY_PATRON">
+        <field name="lib">Returned by patron</field>
+    </row>;
+    <row category="RETURN_CLAIM_RESOLUTION" authorised_value="FOUND_IN_LIB">
+        <field name="lib">Found in library</field>
+    </row>;
+</authorised_values>
+
-- 
2.7.4