Attachment #92057 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;

--

  CONSTRAINT `account_offsets_ibfk_t` FOREIGN KEY (`type`) REFERENCES `account_offset_types` (`type`) ON DELETE CASCADE ON UPDATE CASCADE
) 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 2621-2626 CREATE TABLE `accountlines` ( Link Here

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

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	}