|
Line 0
Link Here
|
|
|
1 |
-- |
| 2 |
-- Table structure for table 'club_templates' |
| 3 |
-- |
| 4 |
|
| 5 |
CREATE TABLE IF NOT EXISTS club_templates ( |
| 6 |
id int(11) NOT NULL AUTO_INCREMENT, |
| 7 |
`name` tinytext NOT NULL, |
| 8 |
description text, |
| 9 |
is_enrollable_from_opac tinyint(1) NOT NULL DEFAULT '0', |
| 10 |
is_email_required tinyint(1) NOT NULL DEFAULT '0', |
| 11 |
`branchcode` varchar(10) COLLATE utf8_unicode_ci NULL DEFAULT NULL, |
| 12 |
date_created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| 13 |
date_updated timestamp NULL DEFAULT NULL, |
| 14 |
is_deletable tinyint(1) NOT NULL DEFAULT '1', |
| 15 |
PRIMARY KEY (id), |
| 16 |
KEY ct_branchcode (branchcode), |
| 17 |
CONSTRAINT `club_templates_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE |
| 18 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 19 |
|
| 20 |
-- |
| 21 |
-- Table structure for table 'clubs' |
| 22 |
-- |
| 23 |
|
| 24 |
CREATE TABLE IF NOT EXISTS clubs ( |
| 25 |
id int(11) NOT NULL AUTO_INCREMENT, |
| 26 |
club_template_id int(11) NOT NULL, |
| 27 |
`name` tinytext NOT NULL, |
| 28 |
description text, |
| 29 |
date_start date DEFAULT NULL, |
| 30 |
date_end date DEFAULT NULL, |
| 31 |
`branchcode` varchar(10) COLLATE utf8_unicode_ci NULL DEFAULT NULL, |
| 32 |
date_created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| 33 |
date_updated timestamp NULL DEFAULT NULL, |
| 34 |
PRIMARY KEY (id), |
| 35 |
KEY club_template_id (club_template_id), |
| 36 |
KEY branchcode (branchcode), |
| 37 |
CONSTRAINT clubs_ibfk_1 FOREIGN KEY (club_template_id) REFERENCES club_templates (id) ON DELETE CASCADE ON UPDATE CASCADE, |
| 38 |
CONSTRAINT clubs_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) |
| 39 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 40 |
|
| 41 |
-- |
| 42 |
-- Table structure for table 'club_enrollments' |
| 43 |
-- |
| 44 |
|
| 45 |
CREATE TABLE IF NOT EXISTS club_enrollments ( |
| 46 |
id int(11) NOT NULL AUTO_INCREMENT, |
| 47 |
club_id int(11) NOT NULL, |
| 48 |
borrowernumber int(11) NOT NULL, |
| 49 |
date_enrolled timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| 50 |
date_canceled timestamp NULL DEFAULT NULL, |
| 51 |
date_created timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', |
| 52 |
date_updated timestamp NULL DEFAULT NULL, |
| 53 |
`branchcode` varchar(10) COLLATE utf8_unicode_ci NULL DEFAULT NULL, |
| 54 |
PRIMARY KEY (id), |
| 55 |
KEY club_id (club_id), |
| 56 |
KEY borrowernumber (borrowernumber), |
| 57 |
KEY branchcode (branchcode), |
| 58 |
CONSTRAINT club_enrollments_ibfk_1 FOREIGN KEY (club_id) REFERENCES clubs (id) ON DELETE CASCADE ON UPDATE CASCADE, |
| 59 |
CONSTRAINT club_enrollments_ibfk_2 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE, |
| 60 |
CONSTRAINT club_enrollments_ibfk_3 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE SET NULL ON UPDATE CASCADE |
| 61 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 62 |
|
| 63 |
-- |
| 64 |
-- Table structure for table 'club_template_enrollment_fields' |
| 65 |
-- |
| 66 |
|
| 67 |
CREATE TABLE IF NOT EXISTS club_template_enrollment_fields ( |
| 68 |
id int(11) NOT NULL AUTO_INCREMENT, |
| 69 |
club_template_id int(11) NOT NULL, |
| 70 |
`name` tinytext NOT NULL, |
| 71 |
description text, |
| 72 |
authorised_value_category varchar(16) DEFAULT NULL, |
| 73 |
PRIMARY KEY (id), |
| 74 |
KEY club_template_id (club_template_id), |
| 75 |
CONSTRAINT club_template_enrollment_fields_ibfk_1 FOREIGN KEY (club_template_id) REFERENCES club_templates (id) ON DELETE CASCADE ON UPDATE CASCADE |
| 76 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 77 |
|
| 78 |
-- |
| 79 |
-- Table structure for table 'club_enrollment_fields' |
| 80 |
-- |
| 81 |
|
| 82 |
CREATE TABLE IF NOT EXISTS club_enrollment_fields ( |
| 83 |
id int(11) NOT NULL AUTO_INCREMENT, |
| 84 |
club_enrollment_id int(11) NOT NULL, |
| 85 |
club_template_enrollment_field_id int(11) NOT NULL, |
| 86 |
`value` text NOT NULL, |
| 87 |
PRIMARY KEY (id), |
| 88 |
KEY club_enrollment_id (club_enrollment_id), |
| 89 |
KEY club_template_enrollment_field_id (club_template_enrollment_field_id), |
| 90 |
CONSTRAINT club_enrollment_fields_ibfk_1 FOREIGN KEY (club_enrollment_id) REFERENCES club_enrollments (id) ON DELETE CASCADE ON UPDATE CASCADE, |
| 91 |
CONSTRAINT club_enrollment_fields_ibfk_2 FOREIGN KEY (club_template_enrollment_field_id) REFERENCES club_template_enrollment_fields (id) ON DELETE CASCADE ON UPDATE CASCADE |
| 92 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 93 |
|
| 94 |
-- |
| 95 |
-- Table structure for table 'club_template_fields' |
| 96 |
-- |
| 97 |
|
| 98 |
CREATE TABLE IF NOT EXISTS club_template_fields ( |
| 99 |
id int(11) NOT NULL AUTO_INCREMENT, |
| 100 |
club_template_id int(11) NOT NULL, |
| 101 |
`name` tinytext NOT NULL, |
| 102 |
description text, |
| 103 |
authorised_value_category varchar(16) DEFAULT NULL, |
| 104 |
PRIMARY KEY (id), |
| 105 |
KEY club_template_id (club_template_id), |
| 106 |
CONSTRAINT club_template_fields_ibfk_1 FOREIGN KEY (club_template_id) REFERENCES club_templates (id) ON DELETE CASCADE ON UPDATE CASCADE |
| 107 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 108 |
|
| 109 |
-- |
| 110 |
-- Table structure for table 'club_fields' |
| 111 |
-- |
| 112 |
|
| 113 |
CREATE TABLE IF NOT EXISTS club_fields ( |
| 114 |
id int(11) NOT NULL AUTO_INCREMENT, |
| 115 |
club_template_field_id int(11) NOT NULL, |
| 116 |
club_id int(11) NOT NULL, |
| 117 |
`value` text, |
| 118 |
PRIMARY KEY (id), |
| 119 |
KEY club_template_field_id (club_template_field_id), |
| 120 |
KEY club_id (club_id), |
| 121 |
CONSTRAINT club_fields_ibfk_3 FOREIGN KEY (club_template_field_id) REFERENCES club_template_fields (id) ON DELETE CASCADE ON UPDATE CASCADE, |
| 122 |
CONSTRAINT club_fields_ibfk_4 FOREIGN KEY (club_id) REFERENCES clubs (id) ON DELETE CASCADE ON UPDATE CASCADE |
| 123 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 124 |
|
| 125 |
INSERT INTO userflags (bit, flag, flagdesc, defaulton) VALUES (21, 'clubs', 'Patron clubs', '0'); |
| 126 |
|
| 127 |
INSERT INTO permissions (module_bit, code, description) VALUES |
| 128 |
(21, 'edit_templates', 'Create and update club templates'), |
| 129 |
(21, 'edit_clubs', 'Create and update clubs'), |
| 130 |
(21, 'enroll', 'Enroll patrons in clubs') |
| 131 |
; |