From 7bf567e8101c5dc25986c3a3c2a548b5b71d8a89 Mon Sep 17 00:00:00 2001 From: Martin Renvoize Date: Tue, 16 Jul 2019 15:16:18 +0100 Subject: [PATCH] Bug 23321: Add cash_registers table Signed-off-by: Maryse Simard --- installer/data/mysql/atomicupdate/bug_23321.perl | 30 ++++++++++++++++++++++++ installer/data/mysql/kohastructure.sql | 22 ++++++++++++++++- 2 files changed, 51 insertions(+), 1 deletion(-) create mode 100644 installer/data/mysql/atomicupdate/bug_23321.perl diff --git a/installer/data/mysql/atomicupdate/bug_23321.perl b/installer/data/mysql/atomicupdate/bug_23321.perl new file mode 100644 index 0000000..1dd0c1a --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_23321.perl @@ -0,0 +1,30 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if ( CheckVersion($DBversion) ) { + + $dbh->do(qq{ +CREATE TABLE `cash_registers` ( + `id` int(11) NOT NULL auto_increment, -- unique identifier for each account register + `name` varchar(24) NOT NULL, -- the user friendly identifier for each account register + `description` longtext NOT NULL, -- the user friendly description for each account register + `branch` varchar(10) NOT NULL, -- the foreign key the library this account register belongs + `branch_default` tinyint(1) NOT NULL DEFAULT 0, -- boolean flag to denote that this till is the branch default + `starting_float` decimal(28, 6), -- the starting float this account register should be assigned + `archived` tinyint(1) NOT NULL DEFAULT 0, -- boolean flag to denote if this till is archived or not + PRIMARY KEY (`id`), + UNIQUE KEY `name` (`name`,`branch`), + CONSTRAINT cash_registers_branch FOREIGN KEY (branch) REFERENCES branches (branchcode) ON UPDATE CASCADE ON DELETE CASCADE +) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci; + }); + + unless ( column_exists( 'accountlines', 'register_id' ) ) { + $dbh->do(qq{ALTER TABLE `accountlines` ADD `register_id` int(11) NULL DEFAULT NULL AFTER `manager_id`}); + $dbh->do(qq{ + ALTER TABLE `accountlines` + ADD CONSTRAINT `accountlines_ibfk_registers` FOREIGN KEY (`register_id`) + REFERENCES `cash_registers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE + }); + } + + SetVersion($DBversion); + print "Upgrade to $DBversion done (Bug 23321 - Add cash_registers table)\n"; +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index cd7d7df..ce1deaf 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2618,6 +2618,7 @@ CREATE TABLE `accountlines` ( `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `note` MEDIUMTEXT NULL default NULL, `manager_id` int(11) NULL DEFAULT NULL, + `register_id` int(11) NULL DEFAULT NULL, `interface` VARCHAR(16) NOT NULL, `branchcode` VARCHAR( 10 ) NULL DEFAULT NULL, -- the branchcode of the library where a payment was made, a manual invoice created, etc. PRIMARY KEY (`accountlines_id`), @@ -2629,7 +2630,8 @@ CREATE TABLE `accountlines` ( CONSTRAINT `accountlines_ibfk_borrowers` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `accountlines_ibfk_items` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `accountlines_ibfk_borrowers_2` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, - CONSTRAINT `accountlines_ibfk_branches` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE + CONSTRAINT `accountlines_ibfk_branches` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `accountlines_ibfk_registers` FOREIGN KEY (`register_id`) REFERENCES `cash_registers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- @@ -2661,6 +2663,24 @@ CREATE TABLE `account_offsets` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- +-- Table structure for table `cash_registers` +-- + +DROP TABLE IF EXISTS `cash_registers`; +CREATE TABLE `cash_registers` ( + `id` int(11) NOT NULL auto_increment, -- unique identifier for each account register + `name` varchar(24) NOT NULL, -- the user friendly identifier for each account register + `description` longtext NOT NULL, -- the user friendly description for each account register + `branch` varchar(10) NOT NULL, -- the foreign key the library this account register belongs + `branch_default` tinyint(1) NOT NULL DEFAULT 0, -- boolean flag to denote that this till is the branch default + `starting_float` decimal(28, 6), -- the starting float this account register should be assigned + `archived` tinyint(1) NOT NULL DEFAULT 0, -- boolean flag to denote if this till is archived or not + PRIMARY KEY (`id`), + UNIQUE KEY `name` (`name`,`branch`), + CONSTRAINT cash_registers_branch FOREIGN KEY (branch) REFERENCES branches (branchcode) ON UPDATE CASCADE ON DELETE CASCADE +) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci; + +-- -- Table structure for table `action_logs` -- -- 2.7.4