Attachment #92467 for bug #23321

View | Details | Raw Unified | Return to bug 23321
Collapse All | Expand All




$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";
}




  `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`),

  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_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;

--

) 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`
--

- 

Return to bug 23321

Lines 2618-2623 CREATE TABLE `accountlines` ( Link Here

(-)a/installer/data/mysql/kohastructure.sql (-2 / +21 lines)
2618	`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,	2618	`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2619	`note` MEDIUMTEXT NULL default NULL,	2619	`note` MEDIUMTEXT NULL default NULL,
2620	`manager_id` int(11) NULL DEFAULT NULL,	2620	`manager_id` int(11) NULL DEFAULT NULL,
		2621	`register_id` int(11) NULL DEFAULT NULL,
2621	`interface` VARCHAR(16) NOT NULL,	2622	`interface` VARCHAR(16) NOT NULL,
2622	`branchcode` VARCHAR( 10 ) NULL DEFAULT NULL, -- the branchcode of the library where a payment was made, a manual invoice created, etc.	2623	`branchcode` VARCHAR( 10 ) NULL DEFAULT NULL, -- the branchcode of the library where a payment was made, a manual invoice created, etc.
2623	PRIMARY KEY (`accountlines_id`),	2624	PRIMARY KEY (`accountlines_id`),
Lines 2629-2635 CREATE TABLE `accountlines` ( Link Here
2629	CONSTRAINT `accountlines_ibfk_borrowers` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,	2630	CONSTRAINT `accountlines_ibfk_borrowers` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2630	CONSTRAINT `accountlines_ibfk_items` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE,	2631	CONSTRAINT `accountlines_ibfk_items` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2631	CONSTRAINT `accountlines_ibfk_borrowers_2` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,	2632	CONSTRAINT `accountlines_ibfk_borrowers_2` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2632	CONSTRAINT `accountlines_ibfk_branches` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE	2633	CONSTRAINT `accountlines_ibfk_branches` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE,
		2634	CONSTRAINT `accountlines_ibfk_registers` FOREIGN KEY (`register_id`) REFERENCES `cash_registers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
2633	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;	2635	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2634		2636
2635	--	2637	--
Lines 2661-2666 CREATE TABLE `account_offsets` ( Link Here
2661	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;	2663	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2662		2664
2663	--	2665	--
		2666	-- Table structure for table `cash_registers`
		2667	--
		2668
		2669	DROP TABLE IF EXISTS `cash_registers`;
		2670	CREATE TABLE `cash_registers` (
		2671	`id` int(11) NOT NULL auto_increment, -- unique identifier for each account register
		2672	`name` varchar(24) NOT NULL, -- the user friendly identifier for each account register
		2673	`description` longtext NOT NULL, -- the user friendly description for each account register
		2674	`branch` varchar(10) NOT NULL, -- the foreign key the library this account register belongs
		2675	`branch_default` tinyint(1) NOT NULL DEFAULT 0, -- boolean flag to denote that this till is the branch default
		2676	`starting_float` decimal(28, 6), -- the starting float this account register should be assigned
		2677	`archived` tinyint(1) NOT NULL DEFAULT 0, -- boolean flag to denote if this till is archived or not
		2678	PRIMARY KEY (`id`),
		2679	UNIQUE KEY `name` (`name`,`branch`),
		2680	CONSTRAINT cash_registers_branch FOREIGN KEY (branch) REFERENCES branches (branchcode) ON UPDATE CASCADE ON DELETE CASCADE
		2681	) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
		2682
		2683	--
2664	-- Table structure for table `action_logs`	2684	-- Table structure for table `action_logs`
2665	--	2685	--
2666		2686
2667	-

Line 0 Link Here

(-)a/installer/data/mysql/atomicupdate/bug_23321.perl (+30 lines)
	1	$DBversion = 'XXX'; # will be replaced by the RM
	2	if ( CheckVersion($DBversion) ) {
	3
	4	$dbh->do(qq{
	5	CREATE TABLE `cash_registers` (
	6	`id` int(11) NOT NULL auto_increment, -- unique identifier for each account register
	7	`name` varchar(24) NOT NULL, -- the user friendly identifier for each account register
	8	`description` longtext NOT NULL, -- the user friendly description for each account register
	9	`branch` varchar(10) NOT NULL, -- the foreign key the library this account register belongs
	10	`branch_default` tinyint(1) NOT NULL DEFAULT 0, -- boolean flag to denote that this till is the branch default
	11	`starting_float` decimal(28, 6), -- the starting float this account register should be assigned
	12	`archived` tinyint(1) NOT NULL DEFAULT 0, -- boolean flag to denote if this till is archived or not
	13	PRIMARY KEY (`id`),
	14	UNIQUE KEY `name` (`name`,`branch`),
	15	CONSTRAINT cash_registers_branch FOREIGN KEY (branch) REFERENCES branches (branchcode) ON UPDATE CASCADE ON DELETE CASCADE
	16	) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
	17	});
	18
	19	unless ( column_exists( 'accountlines', 'register_id' ) ) {
	20	$dbh->do(qq{ALTER TABLE `accountlines` ADD `register_id` int(11) NULL DEFAULT NULL AFTER `manager_id`});
	21	$dbh->do(qq{
	22	ALTER TABLE `accountlines`
	23	ADD CONSTRAINT `accountlines_ibfk_registers` FOREIGN KEY (`register_id`)
	24	REFERENCES `cash_registers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
	25	});
	26	}
	27
	28	SetVersion($DBversion);
	29	print "Upgrade to $DBversion done (Bug 23321 - Add cash_registers table)\n";
	30	}