From 4e44407e9244b4e31fca7990a3db8fea74d0cb9e Mon Sep 17 00:00:00 2001 From: Martin Renvoize Date: Thu, 28 Dec 2023 15:02:36 +0000 Subject: [PATCH] Bug 35657: Add assignee_id to tickets and ticket_updates This patch updates the tickets and ticket_updates tables to include a assignee_id. --- installer/data/mysql/kohastructure.sql | 10 ++++++++-- 1 file changed, 8 insertions(+), 2 deletions(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 7728badcdff..52f4ceef205 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -6267,6 +6267,7 @@ CREATE TABLE `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', + `assignee_id` int(11) DEFAULT NULL COMMENT 'id of the user who this ticket was assigned to at this 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', @@ -6274,8 +6275,10 @@ CREATE TABLE `ticket_updates` ( PRIMARY KEY (`id`), KEY `ticket_updates_ibfk_1` (`ticket_id`), KEY `ticket_updates_ibfk_2` (`user_id`), + KEY `ticket_updates_ibfk_3` (`assignee_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 + CONSTRAINT `ticket_updates_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `ticket_updates_ibfk_3` FOREIGN KEY (`assignee_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 */; @@ -6293,6 +6296,7 @@ CREATE TABLE `tickets` ( `title` text NOT NULL COMMENT 'ticket title', `body` text NOT NULL COMMENT 'ticket details', `status` varchar(80) DEFAULT NULL COMMENT 'current status of the ticket', + `assignee_id` int(11) DEFAULT NULL COMMENT 'id of the user who this ticket is assigned to', `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', @@ -6300,9 +6304,11 @@ CREATE TABLE `tickets` ( KEY `tickets_ibfk_1` (`reporter_id`), KEY `tickets_ibfk_2` (`resolver_id`), KEY `tickets_ibfk_3` (`biblio_id`), + KEY `tickets_ibfk_4` (`assignee_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 + CONSTRAINT `tickets_ibfk_3` FOREIGN KEY (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `tickets_ibfk_4` FOREIGN KEY (`assignee_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 */; -- 2.43.0