Attachment #92746 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 3987-3992 CREATE TABLE IF NOT EXISTS clubs ( Link Here

(-)a/installer/data/mysql/kohastructure.sql (-1 / +39 lines)
3987	CONSTRAINT clubs_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode)	3987	CONSTRAINT clubs_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode)
3988	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;	3988	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
3989		3989
		3990	--
		3991	-- Table structure for table 'club_holds'
		3992	--
		3993
		3994	CREATE TABLE IF NOT EXISTS club_holds (
		3995	id INT(11) NOT NULL AUTO_INCREMENT,
		3996	club_id INT(11) NOT NULL, -- id for the club the hold was generated for
		3997	biblio_id INT(11) NOT NULL, -- id for the bibliographic record the hold has been placed against
		3998	item_id INT(11) NULL DEFAULT NULL, -- If item-level, the id for the item the hold has been placed agains
		3999	date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- Timestamp for the placed hold
		4000	PRIMARY KEY (id),
		4001	-- KEY club_id (club_id),
		4002	CONSTRAINT clubs_holds_ibfk_1 FOREIGN KEY (club_id) REFERENCES clubs (id) ON DELETE CASCADE ON UPDATE CASCADE,
		4003	CONSTRAINT clubs_holds_ibfk_2 FOREIGN KEY (biblio_id) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE,
		4004	CONSTRAINT clubs_holds_ibfk_3 FOREIGN KEY (item_id) REFERENCES items (itemnumber) ON DELETE CASCADE ON UPDATE CASCADE
		4005	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
		4006
		4007	--
		4008	-- Table structure for table 'club_holds_to_patron_holds'
		4009	--
		4010
		4011	CREATE TABLE IF NOT EXISTS club_holds_to_patron_holds (
		4012	id INT(11) NOT NULL AUTO_INCREMENT,
		4013	club_hold_id INT(11) NOT NULL,
		4014	patron_id INT(11) NOT NULL,
		4015	hold_id INT(11),
		4016	error_code ENUM ( 'damaged', 'ageRestricted', 'itemAlreadyOnHold',
		4017	'tooManyHoldsForThisRecord', 'tooManyReservesToday',
		4018	'tooManyReserves', 'notReservable', 'cannotReserveFromOtherBranches',
		4019	'libraryNotFound', 'libraryNotPickupLocation', 'cannotBeTransferred'
		4020	) NULL DEFAULT NULL,
		4021	error_message varchar(100) NULL DEFAULT NULL,
		4022	PRIMARY KEY (id),
		4023	-- KEY club_hold_id (club_hold_id),
		4024	CONSTRAINT clubs_holds_paton_holds_ibfk_1 FOREIGN KEY (club_hold_id) REFERENCES club_holds (id) ON DELETE CASCADE ON UPDATE CASCADE,
		4025	CONSTRAINT clubs_holds_paton_holds_ibfk_2 FOREIGN KEY (patron_id) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
		4026	CONSTRAINT clubs_holds_paton_holds_ibfk_3 FOREIGN KEY (hold_id) REFERENCES reserves (reserve_id) ON DELETE CASCADE ON UPDATE CASCADE
		4027	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
		4028
3990	--	4029	--
3991	-- Table structure for table 'club_enrollments'	4030	-- Table structure for table 'club_enrollments'
3992	--	4031	--
3993	-

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	}