Attachment #93253 for bug #19618

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




$DBversion = 'XXX'; # will be replaced by the RM
if( CheckVersion( $DBversion ) ) {

    $dbh->do(q|
        CREATE TABLE IF NOT EXISTS club_holds (
            id        INT(11) NOT NULL AUTO_INCREMENT,
            club_id   INT(11) NOT NULL, -- id for the club the hold was generated for
            biblio_id INT(11) NOT NULL, -- id for the bibliographic record the hold has been placed against
            item_id   INT(11) NULL DEFAULT NULL, -- If item-level, the id for the item the hold has been placed agains
            date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- Timestamp for the placed hold
            PRIMARY KEY (id),
            -- KEY club_id (club_id),
            CONSTRAINT clubs_holds_ibfk_1 FOREIGN KEY (club_id)   REFERENCES clubs  (id) ON DELETE CASCADE ON UPDATE CASCADE,
            CONSTRAINT clubs_holds_ibfk_2 FOREIGN KEY (biblio_id) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE,
            CONSTRAINT clubs_holds_ibfk_3 FOREIGN KEY (item_id)   REFERENCES items  (itemnumber) ON DELETE CASCADE ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    |);

    $dbh->do(q|
        CREATE TABLE IF NOT EXISTS club_holds_to_patron_holds (
            id              INT(11) NOT NULL AUTO_INCREMENT,
            club_hold_id    INT(11) NOT NULL,
            patron_id       INT(11) NOT NULL,
            hold_id         INT(11),
            error_code      ENUM ( 'damaged', 'ageRestricted', 'itemAlreadyOnHold',
                                'tooManyHoldsForThisRecord', 'tooManyReservesToday',
                                'tooManyReserves', 'notReservable', 'cannotReserveFromOtherBranches',
                                'libraryNotFound', 'libraryNotPickupLocation', 'cannotBeTransferred'
                            ) NULL DEFAULT NULL,
            error_message   varchar(100) NULL DEFAULT NULL,
            PRIMARY KEY (id),
            -- KEY club_hold_id (club_hold_id),
            CONSTRAINT clubs_holds_paton_holds_ibfk_1 FOREIGN KEY (club_hold_id) REFERENCES club_holds (id) ON DELETE CASCADE ON UPDATE CASCADE,
            CONSTRAINT clubs_holds_paton_holds_ibfk_2 FOREIGN KEY (patron_id) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
            CONSTRAINT clubs_holds_paton_holds_ibfk_3 FOREIGN KEY (hold_id) REFERENCES reserves (reserve_id) ON DELETE CASCADE ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    |);

    # Always end with this (adjust the bug info)
    SetVersion( $DBversion );
    print "Upgrade to $DBversion done (Bug 19618 - add club_holds tables)\n";
}




  CONSTRAINT clubs_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table 'club_holds'
--

CREATE TABLE IF NOT EXISTS club_holds (
    id        INT(11) NOT NULL AUTO_INCREMENT,
    club_id   INT(11) NOT NULL, -- id for the club the hold was generated for
    biblio_id INT(11) NOT NULL, -- id for the bibliographic record the hold has been placed against
    item_id   INT(11) NULL DEFAULT NULL, -- If item-level, the id for the item the hold has been placed agains
    date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- Timestamp for the placed hold
    PRIMARY KEY (id),
    -- KEY club_id (club_id),
    CONSTRAINT clubs_holds_ibfk_1 FOREIGN KEY (club_id)   REFERENCES clubs  (id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT clubs_holds_ibfk_2 FOREIGN KEY (biblio_id) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT clubs_holds_ibfk_3 FOREIGN KEY (item_id)   REFERENCES items  (itemnumber) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table 'club_holds_to_patron_holds'
--

CREATE TABLE IF NOT EXISTS club_holds_to_patron_holds (
    id              INT(11) NOT NULL AUTO_INCREMENT,
    club_hold_id    INT(11) NOT NULL,
    patron_id       INT(11) NOT NULL,
    hold_id         INT(11),
    error_code      ENUM ( 'damaged', 'ageRestricted', 'itemAlreadyOnHold',
                        'tooManyHoldsForThisRecord', 'tooManyReservesToday',
                        'tooManyReserves', 'notReservable', 'cannotReserveFromOtherBranches',
                        'libraryNotFound', 'libraryNotPickupLocation', 'cannotBeTransferred'
                    ) NULL DEFAULT NULL,
    error_message   varchar(100) NULL DEFAULT NULL,
    PRIMARY KEY (id),
    -- KEY club_hold_id (club_hold_id),
    CONSTRAINT clubs_holds_paton_holds_ibfk_1 FOREIGN KEY (club_hold_id) REFERENCES club_holds (id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT clubs_holds_paton_holds_ibfk_2 FOREIGN KEY (patron_id) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT clubs_holds_paton_holds_ibfk_3 FOREIGN KEY (hold_id) REFERENCES reserves (reserve_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table 'club_enrollments'
--
- 

Return to bug 19618

Lines 4007-4012 CREATE TABLE IF NOT EXISTS clubs ( Link Here

(-)a/installer/data/mysql/kohastructure.sql (-1 / +39 lines)
4007	CONSTRAINT clubs_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode)	4007	CONSTRAINT clubs_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode)
4008	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;	4008	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
4009		4009
		4010	--
		4011	-- Table structure for table 'club_holds'
		4012	--
		4013
		4014	CREATE TABLE IF NOT EXISTS club_holds (
		4015	id INT(11) NOT NULL AUTO_INCREMENT,
		4016	club_id INT(11) NOT NULL, -- id for the club the hold was generated for
		4017	biblio_id INT(11) NOT NULL, -- id for the bibliographic record the hold has been placed against
		4018	item_id INT(11) NULL DEFAULT NULL, -- If item-level, the id for the item the hold has been placed agains
		4019	date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- Timestamp for the placed hold
		4020	PRIMARY KEY (id),
		4021	-- KEY club_id (club_id),
		4022	CONSTRAINT clubs_holds_ibfk_1 FOREIGN KEY (club_id) REFERENCES clubs (id) ON DELETE CASCADE ON UPDATE CASCADE,
		4023	CONSTRAINT clubs_holds_ibfk_2 FOREIGN KEY (biblio_id) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE,
		4024	CONSTRAINT clubs_holds_ibfk_3 FOREIGN KEY (item_id) REFERENCES items (itemnumber) ON DELETE CASCADE ON UPDATE CASCADE
		4025	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
		4026
		4027	--
		4028	-- Table structure for table 'club_holds_to_patron_holds'
		4029	--
		4030
		4031	CREATE TABLE IF NOT EXISTS club_holds_to_patron_holds (
		4032	id INT(11) NOT NULL AUTO_INCREMENT,
		4033	club_hold_id INT(11) NOT NULL,
		4034	patron_id INT(11) NOT NULL,
		4035	hold_id INT(11),
		4036	error_code ENUM ( 'damaged', 'ageRestricted', 'itemAlreadyOnHold',
		4037	'tooManyHoldsForThisRecord', 'tooManyReservesToday',
		4038	'tooManyReserves', 'notReservable', 'cannotReserveFromOtherBranches',
		4039	'libraryNotFound', 'libraryNotPickupLocation', 'cannotBeTransferred'
		4040	) NULL DEFAULT NULL,
		4041	error_message varchar(100) NULL DEFAULT NULL,
		4042	PRIMARY KEY (id),
		4043	-- KEY club_hold_id (club_hold_id),
		4044	CONSTRAINT clubs_holds_paton_holds_ibfk_1 FOREIGN KEY (club_hold_id) REFERENCES club_holds (id) ON DELETE CASCADE ON UPDATE CASCADE,
		4045	CONSTRAINT clubs_holds_paton_holds_ibfk_2 FOREIGN KEY (patron_id) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
		4046	CONSTRAINT clubs_holds_paton_holds_ibfk_3 FOREIGN KEY (hold_id) REFERENCES reserves (reserve_id) ON DELETE CASCADE ON UPDATE CASCADE
		4047	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
		4048
4010	--	4049	--
4011	-- Table structure for table 'club_enrollments'	4050	-- Table structure for table 'club_enrollments'
4012	--	4051	--
4013	-

Line 0 Link Here

(-)a/installer/data/mysql/atomicupdate/club_holds.perl (+42 lines)
	1	$DBversion = 'XXX'; # will be replaced by the RM
	2	if( CheckVersion( $DBversion ) ) {
	3
	4	$dbh->do(q\|
	5	CREATE TABLE IF NOT EXISTS club_holds (
	6	id INT(11) NOT NULL AUTO_INCREMENT,
	7	club_id INT(11) NOT NULL, -- id for the club the hold was generated for
	8	biblio_id INT(11) NOT NULL, -- id for the bibliographic record the hold has been placed against
	9	item_id INT(11) NULL DEFAULT NULL, -- If item-level, the id for the item the hold has been placed agains
	10	date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- Timestamp for the placed hold
	11	PRIMARY KEY (id),
	12	-- KEY club_id (club_id),
	13	CONSTRAINT clubs_holds_ibfk_1 FOREIGN KEY (club_id) REFERENCES clubs (id) ON DELETE CASCADE ON UPDATE CASCADE,
	14	CONSTRAINT clubs_holds_ibfk_2 FOREIGN KEY (biblio_id) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE,
	15	CONSTRAINT clubs_holds_ibfk_3 FOREIGN KEY (item_id) REFERENCES items (itemnumber) ON DELETE CASCADE ON UPDATE CASCADE
	16	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
	17	\|);
	18
	19	$dbh->do(q\|
	20	CREATE TABLE IF NOT EXISTS club_holds_to_patron_holds (
	21	id INT(11) NOT NULL AUTO_INCREMENT,
	22	club_hold_id INT(11) NOT NULL,
	23	patron_id INT(11) NOT NULL,
	24	hold_id INT(11),
	25	error_code ENUM ( 'damaged', 'ageRestricted', 'itemAlreadyOnHold',
	26	'tooManyHoldsForThisRecord', 'tooManyReservesToday',
	27	'tooManyReserves', 'notReservable', 'cannotReserveFromOtherBranches',
	28	'libraryNotFound', 'libraryNotPickupLocation', 'cannotBeTransferred'
	29	) NULL DEFAULT NULL,
	30	error_message varchar(100) NULL DEFAULT NULL,
	31	PRIMARY KEY (id),
	32	-- KEY club_hold_id (club_hold_id),
	33	CONSTRAINT clubs_holds_paton_holds_ibfk_1 FOREIGN KEY (club_hold_id) REFERENCES club_holds (id) ON DELETE CASCADE ON UPDATE CASCADE,
	34	CONSTRAINT clubs_holds_paton_holds_ibfk_2 FOREIGN KEY (patron_id) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
	35	CONSTRAINT clubs_holds_paton_holds_ibfk_3 FOREIGN KEY (hold_id) REFERENCES reserves (reserve_id) ON DELETE CASCADE ON UPDATE CASCADE
	36	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
	37	\|);
	38
	39	# Always end with this (adjust the bug info)
	40	SetVersion( $DBversion );
	41	print "Upgrade to $DBversion done (Bug 19618 - add club_holds tables)\n";
	42	}