From 684592b4bee8cdcf0351cd7c54c31bf912a7421b Mon Sep 17 00:00:00 2001 From: Martin Renvoize Date: Fri, 13 Sep 2019 14:11:37 +0100 Subject: [PATCH] Bug 23355: Link cash_register_actions to cash_registers by foreign key Signed-off-by: Kyle M Hall --- .../data/mysql/atomicupdate/bug_23355.perl | 21 ++++++++++--------- 1 file changed, 11 insertions(+), 10 deletions(-) diff --git a/installer/data/mysql/atomicupdate/bug_23355.perl b/installer/data/mysql/atomicupdate/bug_23355.perl index 6517d5361c..72bca27942 100644 --- a/installer/data/mysql/atomicupdate/bug_23355.perl +++ b/installer/data/mysql/atomicupdate/bug_23355.perl @@ -2,16 +2,17 @@ $DBversion = 'XXX'; # will be replaced by the RM if ( CheckVersion($DBversion) ) { $dbh->do(qq{ -CREATE TABLE `cash_register_actions` ( - `id` int(11) NOT NULL auto_increment, -- unique identifier for each account register action - `code` varchar(24) NOT NULL, -- action code denoting the type of action recorded (enum), - `manager_id` int(11) NOT NULL, -- staff member performing the action - `amount` decimal(28,6) DEFAULT NULL, -- amount recorded in action (signed) - `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, - PRIMARY KEY (`id`), - CONSTRAINT `cash_register_actions_manager` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, - CONSTRAINT `cash_register_actions_register` FOREIGN KEY (`register_id`) REFERENCES `cash_registers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci; + CREATE TABLE IF NOT EXISTS `cash_register_actions` ( + `id` int(11) NOT NULL auto_increment, -- unique identifier for each account register action + `code` varchar(24) NOT NULL, -- action code denoting the type of action recorded (enum), + `register_id` int(11) NOT NULL, -- id of cash_register this action belongs to, + `manager_id` int(11) NOT NULL, -- staff member performing the action + `amount` decimal(28,6) DEFAULT NULL, -- amount recorded in action (signed) + `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY (`id`), + CONSTRAINT `cash_register_actions_manager` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `cash_register_actions_register` FOREIGN KEY (`register_id`) REFERENCES `cash_registers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE + ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci; }); SetVersion($DBversion); -- 2.20.1