From f81185c7d52bf487a5b51e219cf3325c930c654b Mon Sep 17 00:00:00 2001
From: Magnus Enger <magnus@libriotech.no>
Date: Sat, 11 Nov 2017 14:02:22 +0000
Subject: [PATCH] Bug 18591 - Database updates

Signed-off-by: andrew.isherwood@ptfs-europe.com
---
 .../mysql/atomicupdate/bug18591-ill-comments.perl  | 26 ++++++++++++++++++++++
 installer/data/mysql/kohastructure.sql             | 20 +++++++++++++++++
 2 files changed, 46 insertions(+)
 create mode 100644 installer/data/mysql/atomicupdate/bug18591-ill-comments.perl

diff --git a/installer/data/mysql/atomicupdate/bug18591-ill-comments.perl b/installer/data/mysql/atomicupdate/bug18591-ill-comments.perl
new file mode 100644
index 0000000000..574211883a
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug18591-ill-comments.perl
@@ -0,0 +1,26 @@
+$DBversion = 'XXX';
+if( CheckVersion( $DBversion ) ) {
+    unless (TableExists('illcomments')) {
+        $dbh->do(q{
+            CREATE TABLE illcomments (
+                illcomment_id int(11) NOT NULL AUTO_INCREMENT, -- Unique ID of the comment
+                illrequest_id bigint(20) unsigned NOT NULL,    -- ILL request number
+                borrowernumber integer DEFAULT NULL,           -- Link to the user who made the comment (could be librarian, patron or ILL partner library)
+                comment text DEFAULT NULL,                     -- The text of the comment
+                timestamp timestamp DEFAULT CURRENT_TIMESTAMP, -- Date and time when the comment was made
+                PRIMARY KEY  ( illcomment_id ),
+                CONSTRAINT illcomments_bnfk
+                  FOREIGN KEY ( borrowernumber )
+                  REFERENCES  borrowers  ( borrowernumber )
+                  ON UPDATE CASCADE ON DELETE CASCADE,
+                CONSTRAINT illcomments_ifk
+                  FOREIGN KEY (illrequest_id)
+                  REFERENCES illrequests ( illrequest_id )
+                  ON UPDATE CASCADE ON DELETE CASCADE
+            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+        });
+    }
+
+    SetVersion( $DBversion );
+    print "Upgrade to $DBversion done (Bug 18591 - Add comments to ILL requests)\n";
+}
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 95c36175d2..834e6368c1 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -4206,6 +4206,26 @@ CREATE TABLE `oauth_access_tokens` (
     `client_id`    VARCHAR(191) NOT NULL, -- the client id the access token belongs to
     `expires`      INT NOT NULL,          -- expiration time in seconds
     PRIMARY KEY (`access_token`)
+
+-- Table structure for table illcomments
+--
+
+DROP TABLE IF EXISTS illcomments;
+CREATE TABLE illcomments (
+    illcomment_id int(11) NOT NULL AUTO_INCREMENT, -- Unique ID of the comment
+    illrequest_id bigint(20) unsigned NOT NULL,    -- ILL request number
+    borrowernumber integer DEFAULT NULL,           -- Link to the user who made the comment (could be librarian, patron or ILL partner library)
+    comment text DEFAULT NULL,                     -- The text of the comment
+    timestamp timestamp DEFAULT CURRENT_TIMESTAMP, -- Date and time when the comment was made
+    PRIMARY KEY  ( illcomment_id ),
+    CONSTRAINT illcomments_bnfk
+      FOREIGN KEY ( borrowernumber )
+      REFERENCES  borrowers  ( borrowernumber )
+      ON UPDATE CASCADE ON DELETE CASCADE,
+    CONSTRAINT illcomments_ifk
+      FOREIGN KEY (illrequest_id)
+      REFERENCES illrequests ( illrequest_id )
+      ON UPDATE CASCADE ON DELETE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
-- 
2.11.0