2 -- Table structure for table 'club_templates'
5 CREATE TABLE IF NOT EXISTS club_templates (
6 id int(11) NOT NULL AUTO_INCREMENT,
7 `name` tinytext NOT NULL,
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) 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',
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 COLLATE=utf8_unicode_ci;
21 -- Table structure for table 'clubs'
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,
29 date_start date DEFAULT NULL,
30 date_end date DEFAULT NULL,
31 branchcode varchar(10) NULL DEFAULT NULL,
32 date_created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
33 date_updated timestamp NULL DEFAULT NULL,
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 COLLATE=utf8_unicode_ci;
42 -- Table structure for table 'club_enrollments'
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) NULL DEFAULT NULL,
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 COLLATE=utf8_unicode_ci;
64 -- Table structure for table 'club_template_enrollment_fields'
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,
72 authorised_value_category varchar(16) DEFAULT NULL,
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 COLLATE=utf8_unicode_ci;
79 -- Table structure for table 'club_enrollment_fields'
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,
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 COLLATE=utf8_unicode_ci;
95 -- Table structure for table 'club_template_fields'
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,
103 authorised_value_category varchar(16) DEFAULT NULL,
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 COLLATE=utf8_unicode_ci;
110 -- Table structure for table 'club_fields'
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,
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 COLLATE=utf8_unicode_ci;
125 INSERT IGNORE INTO userflags (bit, flag, flagdesc, defaulton) VALUES (21, 'clubs', 'Patron clubs', '0');
127 INSERT IGNORE 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')