From e141a2f598aa3abd1fc5a329d378727119c032ee Mon Sep 17 00:00:00 2001 From: Martin Renvoize Date: Tue, 25 Oct 2022 12:59:10 +0100 Subject: [PATCH] Bug 31028: Add `tickets` and `ticket_updates` tables This patch adds the new `tickets` and `ticket_updates` tables for tracking catalog concern tickets. --- .../data/mysql/atomicupdate/bug_31028.pl | 48 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 43 +++++++++++++++++ 2 files changed, 91 insertions(+) create mode 100644 installer/data/mysql/atomicupdate/bug_31028.pl diff --git a/installer/data/mysql/atomicupdate/bug_31028.pl b/installer/data/mysql/atomicupdate/bug_31028.pl new file mode 100644 index 0000000000..79f6ccd5e3 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_31028.pl @@ -0,0 +1,48 @@ +use Modern::Perl; + +return { + bug_number => "31028", + description => "Add a way to record users concerns about catalog records", + up => sub { + my ($args) = @_; + my ($dbh, $out) = @$args{qw(dbh out)}; + unless ( TableExists('tickets') ) { + $dbh->do(q{ + CREATE TABLE IF NOT EXISTS `tickets` ( + `id` int(11) NOT NULL auto_increment COMMENT 'primary key', + `reporter_id` int(11) NOT NULL DEFAULT 0 COMMENT 'id of the patron who reported the ticket', + `reported_date` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'date and time this ticket was reported', + `title` text NOT NULL COMMENT 'ticket title', + `body` text NOT NULL COMMENT 'ticket details', + `resolver_id` int(11) DEFAULT NULL COMMENT 'id of the user who resolved the ticket', + `resolved_date` datetime DEFAULT NULL COMMENT 'date and time this ticket was resolved', + `biblio_id` int(11) DEFAULT NULL COMMENT 'id of biblio linked', + PRIMARY KEY(`id`), + CONSTRAINT `tickets_ibfk_1` FOREIGN KEY (`reporter_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `tickets_ibfk_2` FOREIGN KEY (`resolver_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `tickets_ibfk_3` FOREIGN KEY (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + }); + + say $out "Added new table 'tickets'"; + } + + unless ( TableExists('ticket_updates') ) { + $dbh->do(q{ + CREATE TABLE IF NOT EXISTS `ticket_updates` ( + `id` int(11) NOT NULL auto_increment COMMENT 'primary key', + `ticket_id` int(11) NOT NULL COMMENT 'id of catalog ticket the update relates to', + `user_id` int(11) NOT NULL DEFAULT 0 COMMENT 'id of the user who logged the update', + `public` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'boolean flag to denote whether this update is public', + `date` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'date and time this update was logged', + `message` text NOT NULL COMMENT 'update message content', + PRIMARY KEY(`id`), + CONSTRAINT `ticket_updates_ibfk_1` FOREIGN KEY (`ticket_id`) REFERENCES `tickets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `ticket_updates_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + }); + + say $out "Added new table 'ticket_updates'"; + } + } +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 7015a95ff3..7f5373ba81 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -5641,6 +5641,49 @@ CREATE TABLE `tags_index` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; +-- +-- Table structure for table `tickets` +-- + +DROP TABLE IF EXISTS `tickets`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `tickets` ( + `id` int(11) NOT NULL auto_increment COMMENT 'primary key', + `reporter_id` int(11) NOT NULL DEFAULT 0 COMMENT 'id of the patron who reported the ticket', + `reported_date` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'date and time this ticket was reported', + `title` text NOT NULL COMMENT 'ticket title', + `body` text NOT NULL COMMENT 'ticket details', + `resolver_id` int(11) DEFAULT NULL COMMENT 'id of the user who resolved the ticket', + `resolved_date` datetime DEFAULT NULL COMMENT 'date and time this ticket was resolved', + `biblio_id` int(11) DEFAULT NULL COMMENT 'id of biblio linked', + PRIMARY KEY(`id`), + CONSTRAINT `tickets_ibfk_1` FOREIGN KEY (`reporter_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `tickets_ibfk_2` FOREIGN KEY (`resolver_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `tickets_ibfk_3` FOREIGN KEY (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `ticket_updates` +-- + +DROP TABLE IF EXISTS `ticket_updates`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `ticket_updates` ( + `id` int(11) NOT NULL auto_increment COMMENT 'primary key', + `ticket_id` int(11) NOT NULL COMMENT 'id of catalog ticket the update relates to', + `user_id` int(11) NOT NULL DEFAULT 0 COMMENT 'id of the user who logged the update', + `public` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'boolean flag to denote whether this update is public', + `date` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'date and time this update was logged', + `message` text NOT NULL COMMENT 'update message content', + PRIMARY KEY(`id`), + CONSTRAINT `ticket_updates_ibfk_1` FOREIGN KEY (`ticket_id`) REFERENCES `tickets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `ticket_updates_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + -- -- Table structure for table `tmp_holdsqueue` -- -- 2.30.2