Attachment 143647 Details for Bug 31028 – Bug 31028: Add `tickets` and `ticket_updates` tables

[patch] Bug 31028: Add `tickets` and `ticket_updates` tables

Bug-31028-Add-tickets-and-ticketupdates-tables.patch (text/plain), 6.75 KB, created by Martin Renvoize (ashimema) on 2022-11-10 13:46:45 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Martin Renvoize (ashimema)

Created: 2022-11-10 13:46:45 UTC

Size: 6.75 KB

patch

obsolete

>From 8080a5f93139dc46dd7046537dc5400542378646 Mon Sep 17 00:00:00 2001
>From: Martin Renvoize <martin.renvoize@ptfs-europe.com>
>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 54ed708304..e69842f475 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -5626,6 +5626,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.20.1

Actions: View | Diff | Splinter Review

Attachments on bug 31028: 137412 | 139222 | 139223 | 139224 | 139225 | 139226 | 139227 | 139228 | 139229 | 139230 | 139231 | 139232 | 139233 | 139234 | 139235 | 139236 | 139237 | 139238 | 139572 | 139573 | 139574 | 139575 | 139576 | 139577 | 139578 | 139579 | 139580 | 139581 | 139582 | 139583 | 139584 | 139585 | 139586 | 139587 | 139588 | 139589 | 139590 | 139591 | 140289 | 140290 | 140291 | 140292 | 140293 | 140294 | 140295 | 140296 | 140297 | 140298 | 140299 | 140300 | 140301 | 140302 | 140303 | 140304 | 140305 | 140306 | 140307 | 140308 | 140436 | 140437 | 140438 | 140439 | 140440 | 140441 | 140442 | 140443 | 140444 | 140445 | 140446 | 140447 | 140448 | 140449 | 140450 | 140451 | 140452 | 140453 | 140454 | 140455 | 140764 | 140765 | 140766 | 140767 | 140768 | 140769 | 140770 | 140771 | 140772 | 140773 | 140774 | 140775 | 140776 | 140777 | 140778 | 140779 | 140780 | 140781 | 140782 | 140783 | 140793 | 140929 | 140930 | 140931 | 140932 | 140933 | 140934 | 140935 | 140936 | 140937 | 140938 | 140939 | 140940 | 140941 | 140942 | 140943 | 140944 | 140945 | 140946 | 140947 | 140948 | 140949 | 140950 | 141133 | 141134 | 141135 | 141136 | 141137 | 141138 | 141139 | 141140 | 141141 | 141142 | 141143 | 141144 | 141145 | 141146 | 141147 | 141148 | 141149 | 141150 | 141151 | 141152 | 141153 | 141154 | 141440 | 141441 | 141442 | 141443 | 141444 | 141445 | 141446 | 141447 | 141448 | 141449 | 141450 | 141451 | 141452 | 141453 | 141454 | 141455 | 141456 | 141457 | 141458 | 141459 | 141460 | 142164 | 142165 | 142166 | 142167 | 142168 | 142169 | 142170 | 142171 | 142172 | 142173 | 142174 | 142175 | 142177 | 142179 | 142180 | 142181 | 142182 | 142183 | 142184 | 142185 | 142186 | 142187 | 142188 | 142189 | 142190 | 142191 | 142616 | 142617 | 142618 | 142619 | 142620 | 142621 | 142622 | 142623 | 142624 | 142625 | 142626 | 142627 | 142932 | 142933 | 142934 | 142935 | 142936 | 142937 | 142938 | 142939 | 142940 | 142941 | 142942 | 142943 | 142944 | 142945 | 142946 | 142947 | 142948 | 142949 | 143238 | 143239 | 143240 | 143241 | 143242 | 143243 | 143244 | 143245 | 143246 | 143247 | 143248 | 143249 | 143250 | 143251 | 143252 | 143253 | 143254 | 143255 | 143256 | 143257 | 143302 | 143303 | 143304 | 143305 | 143306 | 143307 | 143308 | 143309 | 143310 | 143311 | 143312 | 143313 | 143314 | 143315 | 143316 | 143317 | 143318 | 143319 | 143320 | 143321 | 143322 | 143323 | 143324 | 143325 | 143326 | 143327 | 143328 | 143329 | 143330 | 143331 | 143332 | 143333 | 143334 | 143335 | 143336 | 143337 | 143338 | 143339 | 143340 | 143341 | 143470 | 143471 | 143472 | 143473 | 143474 | 143475 | 143476 | 143477 | 143478 | 143479 | 143480 | 143481 | 143482 | 143483 | 143484 | 143485 | 143486 | 143487 | 143488 | 143489 | 143490 | 143647 | 143648 | 143649 | 143650 | 143651 | 143652 | 143653 | 143654 | 143655 | 143656 | 143657 | 143658 | 143659 | 143660 | 143661 | 143662 | 143663 | 143664 | 143665 | 143666 | 143667 | 143708 | 143709 | 143710 | 143711 | 143712 | 143713 | 143714 | 143715 | 143716 | 143717 | 143718 | 143719 | 143720 | 143721 | 143722 | 143723 | 143724 | 143725 | 143726 | 143727 | 144647 | 144648 | 144649 | 144650 | 144651 | 144652 | 144653 | 144654 | 144655 | 144656 | 144657 | 144658 | 144659 | 144660 | 144661 | 144662 | 144663 | 144664 | 144665 | 144666 | 144667 | 144668 | 145542 | 146214 | 146215 | 146216 | 146217 | 146218 | 146219 | 146220 | 146221 | 146222 | 146223 | 146224 | 146225 | 146226 | 146227 | 146228 | 146229 | 146230 | 146231 | 146232 | 146233 | 146234 | 146235 | 146236 | 146237 | 146238 | 146239 | 146240 | 146241 | 146242 | 146243 | 146244 | 146245 | 146246 | 146247 | 146248 | 146249 | 146250 | 146251 | 146252 | 146253 | 146254 | 146255 | 146256 | 146257 | 146258 | 146259 | 146260 | 146261 | 146262 | 146697 | 146870 | 146871 | 146872 | 146873 | 146874 | 146875 | 146876 | 146877 | 146878 | 146879 | 146880 | 146881 | 146882 | 146883 | 146884 | 146885 | 146886 | 146887 | 146888 | 146889 | 146890 | 146891 | 146892 | 146893 | 146894 | 146895 | 146896 | 147797 | 147840 | 147841 | 147844 | 147859 | 147860 | 147861 | 147866 | 148673 | 149931