· 9 years ago · Nov 28, 2016, 05:28 PM
1SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
2SET time_zone = "+00:00";
3CREATE DATABASE IF NOT EXISTS `eventsHD` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
4USE `eventsHD`;
5
6CREATE TABLE `data_sources` (
7 `source_id` int(11) NOT NULL,
8 `name` varchar(255) NOT NULL,
9 `service_id` int(11) NOT NULL
10) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=latin1;
11
12INSERT INTO `data_sources` (`source_id`, `name`, `service_id`) VALUES
13(1, 'Blackbaud Athletic Calendar', 1),
14(2, 'Blackbaud School Announcements', 1),
15(3, 'Upcoming Events', 2),
16(4, 'Upcoming Sports', 2),
17(11, 'School Events', 1);
18
19CREATE TABLE `data_source_settings` (
20 `source_setting_id` int(11) NOT NULL,
21 `data_source_id` int(11) NOT NULL,
22 `field_id` int(11) NOT NULL,
23 `value` varchar(255) NOT NULL
24) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=latin1;
25
26INSERT INTO `data_source_settings` (`source_setting_id`, `data_source_id`, `field_id`, `value`) VALUES
27(7, 1, 1, 'montgomerybell'),
28(8, 1, 2, 'firesign'),
29(9, 1, 3, 'M0n7G0m3Ry'),
30(10, 1, 4, 'http://webservice.whipplehill.com/athletics/schedule.asmx?WSDL'),
31(11, 1, 5, 'GetAthleticSchedule'),
32(13, 2, 1, 'montgomerybell'),
33(14, 2, 2, 'firesign'),
34(15, 2, 3, 'M0n7G0m3Ry'),
35(16, 2, 4, 'http://webservice.whipplehill.com/content/announcements.asmx?WSDL'),
36(17, 2, 5, 'GetSchoolAnnouncements'),
37(25, 3, 7, 'http://www.bdcs.org/calendar/calendar_362.ics'),
38(26, 4, 7, 'http://www.bdcs.org/calendar/calendar_354_gmt.ics'),
39(35, 11, 1, 'montgomerybell'),
40(36, 11, 2, 'firesign'),
41(37, 11, 3, 'M0n7G0m3Ry'),
42(38, 11, 4, 'http://webservice.whipplehill.com/content/events.asmx?WSDL'),
43(39, 11, 5, 'GetSchoolEvents'),
44(63, 22, 1, 'School Name'),
45(64, 22, 2, 'Username'),
46(65, 22, 3, 'Password'),
47(66, 22, 4, 'WSDL'),
48(67, 22, 5, 'GetSchoolEvents');
49
50CREATE TABLE `groups` (
51 `id` mediumint(8) unsigned NOT NULL,
52 `name` varchar(20) NOT NULL,
53 `description` varchar(100) NOT NULL
54) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
55
56INSERT INTO `groups` (`id`, `name`, `description`) VALUES
57(1, 'admin', 'Administrator'),
58(2, 'members', 'General User'),
59(3, 'iw', 'Industry Weapon Employees');
60
61CREATE TABLE `login_attempts` (
62 `id` int(11) unsigned NOT NULL,
63 `ip_address` varchar(15) NOT NULL,
64 `login` varchar(100) NOT NULL,
65 `time` int(11) unsigned DEFAULT NULL
66) ENGINE=InnoDB DEFAULT CHARSET=utf8;
67
68CREATE TABLE `services` (
69 `service_id` int(11) NOT NULL,
70 `name` varchar(255) NOT NULL
71) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
72
73INSERT INTO `services` (`service_id`, `name`) VALUES
74(1, 'Blackbaud'),
75(2, 'iCal');
76
77CREATE TABLE `service_fields` (
78 `field_id` int(11) NOT NULL,
79 `service_id` int(11) NOT NULL,
80 `field_type` varchar(255) NOT NULL DEFAULT 'input',
81 `field_name` varchar(255) NOT NULL,
82 `display_name` varchar(255) NOT NULL
83) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
84
85INSERT INTO `service_fields` (`field_id`, `service_id`, `field_type`, `field_name`, `display_name`) VALUES
86(1, 1, 'input', 'schoolname', 'School Name'),
87(2, 1, 'input', 'username', 'Blackbaud Username'),
88(3, 1, 'input', 'password', 'Blackbaud Password'),
89(4, 1, 'input', 'wsdl', 'Blackbaud WSDL File'),
90(5, 1, 'dropdown', 'requestType', 'Blackbaud Request Type'),
91(7, 2, 'input', 'feedUrl', 'iCal Feed URL');
92
93CREATE TABLE `service_field_values` (
94 `id` int(11) NOT NULL,
95 `field_id` int(11) NOT NULL,
96 `value` varchar(255) NOT NULL,
97 `name` varchar(255) NOT NULL
98) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
99
100INSERT INTO `service_field_values` (`id`, `field_id`, `value`, `name`) VALUES
101(1, 5, 'GetSchoolEvents', 'School Events'),
102(2, 5, 'GetAthleticSchedule', 'Athletic Schedule'),
103(3, 5, 'GetSchoolAnnouncements', 'School Announcements');
104
105CREATE TABLE `slides` (
106 `slide_id` int(11) NOT NULL,
107 `name` varchar(255) NOT NULL,
108 `title` varchar(255) NOT NULL,
109 `template_name` varchar(255) NOT NULL,
110 `template_folder` varchar(255) NOT NULL,
111 `source_id` int(11) NOT NULL,
112 `bg_color` varchar(255) NOT NULL,
113 `primary_color` varchar(255) NOT NULL,
114 `secondary_color` varchar(255) NOT NULL,
115 `primary_font_color` varchar(255) NOT NULL,
116 `secondary_font_color` varchar(255) NOT NULL,
117 `header_font_color` varchar(255) NOT NULL,
118 `header_bg_color` varchar(255) NOT NULL,
119 `last_modified` int(11) NOT NULL,
120 `cchd_record` varchar(255) NOT NULL,
121 `title_slug` varchar(255) NOT NULL
122) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=latin1;
123
124INSERT INTO `slides` (`slide_id`, `name`, `title`, `template_name`, `template_folder`, `source_id`, `bg_color`, `primary_color`, `secondary_color`, `primary_font_color`, `secondary_font_color`, `header_font_color`, `header_bg_color`, `last_modified`, `cchd_record`, `title_slug`) VALUES
125(2, 'Blackbaud Athletic Calendar', 'Athletic Results', 'Blackbaud Athletic Results', 'results', 1, 'rgb(0, 0, 0)', 'rgb(0, 0, 0)', 'rgb(0, 0, 0)', 'rgb(0, 0, 0)', 'rgb(0, 0, 0)', 'rgb(0, 0, 0)', 'rgb(0, 0, 0)', 1480350193, '00000000/00000000/00000000/01010002/5401200A/4544080A/00140B8E/91CDF8DC', 'athletic-results'),
126(3, 'Blackbaud School Announcements', 'News & Announcements', 'Blackbaud News', 'news', 2, 'rgb(0, 0, 0)', 'rgb(0, 0, 0)', 'rgb(0, 0, 0)', 'rgb(0, 0, 0)', 'rgb(0, 0, 0)', 'rgb(0, 0, 0)', 'rgb(0, 0, 0)', 1480350208, '00000000/00000000/00000000/01010002/50140008/44118A2A/51440386/D4DF58F6', 'news-and-announcements'),
127(4, '', 'Athletic Calendar', 'Calendar', 'calendar', 1, '', '', '', '', '', '', '', 0, '00000000/00000000/00000000/01010002/0415202A/50010028/1414098C/C1E4F04B', 'athletic-calendar'),
128(5, 'Sports Calendar', 'Upcoming Sports', 'General Purpose', 'general', 4, '', '', '', '', '', '', '', 0, '00000000/00000000/00000000/01010002/111022A2/01112208/54052B04/BAF0DC78', 'upcoming-sports'),
129(6, 'General Feed', 'Upcoming Events', 'General Purpose', 'general', 3, '', '', '', '', '', '', '', 0, '00000000/00000000/00000000/01010002/45412A82/444520A2/540521AC/EAB65EF4', 'upcoming-events'),
130(21, 'Test Slide', 'Slide Title Calendar', 'Blackbaud News', 'news', 11, 'rgb(255, 0, 0)', 'rgb(0, 255, 0)', 'rgb(171, 186, 186)', 'rgb(239, 254, 234)', 'rgb(16, 5, 170)', 'rgb(0, 0, 0)', 'rgb(0, 0, 255)', 1480352413, '', 'slide-title-calendar');
131
132CREATE TABLE `users` (
133 `id` int(11) unsigned NOT NULL,
134 `ip_address` varchar(45) NOT NULL,
135 `username` varchar(100) DEFAULT NULL,
136 `password` varchar(255) NOT NULL,
137 `salt` varchar(255) DEFAULT NULL,
138 `email` varchar(100) NOT NULL,
139 `activation_code` varchar(40) DEFAULT NULL,
140 `forgotten_password_code` varchar(40) DEFAULT NULL,
141 `forgotten_password_time` int(11) unsigned DEFAULT NULL,
142 `remember_code` varchar(40) DEFAULT NULL,
143 `created_on` int(11) unsigned NOT NULL,
144 `last_login` int(11) unsigned DEFAULT NULL,
145 `active` tinyint(1) unsigned DEFAULT NULL,
146 `first_name` varchar(50) DEFAULT NULL,
147 `last_name` varchar(50) DEFAULT NULL,
148 `company` varchar(100) DEFAULT NULL,
149 `phone` varchar(20) DEFAULT NULL
150) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
151
152INSERT INTO `users` (`id`, `ip_address`, `username`, `password`, `salt`, `email`, `activation_code`, `forgotten_password_code`, `forgotten_password_time`, `remember_code`, `created_on`, `last_login`, `active`, `first_name`, `last_name`, `company`, `phone`) VALUES
153(1, '127.0.0.1', 'administrator', '$2a$07$SeBknntpZror9uyftVopmu61qg0ms8Qv1yV6FG.kQOSM.9QhmTo36', '', 'admin@admin.com', '', NULL, NULL, NULL, 1268889823, 1478109234, 1, 'Admin', 'istrator', 'ADMIN', '0'),
154(2, '10.16.13.71', 'steven.chester@industryweapon.com', '$2y$08$sO9A.Qe9M0T/Or64nnXmyuFiwS2TnPXTVP2tWrOWu01qUsgBKlZJy', NULL, 'steven.chester@industryweapon.com', NULL, NULL, NULL, NULL, 1478106498, 1480351448, 1, 'Test', 'User', NULL, NULL),
155(3, '10.16.13.71', 'user@user.com', '$2y$08$HCXArj7/lZK3kyWYlMyJCepzTY0hXEpBhQyOXbzjAO4VpPhxEtMju', NULL, 'user@user.com', NULL, NULL, NULL, NULL, 1478109023, 1478109056, 1, 'Test', 'User 2', NULL, NULL),
156(4, '10.16.13.25', 'cedric.viguilla@industryweapon.com', '$2y$08$3cImpm8RE.p6tRldIkzhkOJehbgVYSlPODzhhilqehSuvQf/jYTtO', NULL, 'cedric.viguilla@industryweapon.com', NULL, NULL, NULL, NULL, 1479932586, 1479933352, 1, 'Cedric', 'Viguilla', NULL, NULL),
157(5, '10.16.13.25', 'ryan.lange@industryweapon.com', '$2y$08$q0gsyr9Ep1KtanNOp5aPj.qxRfvd8w34kxw/czkVUSwnx2FPP.65W', NULL, 'ryan.lange@industryweapon.com', NULL, NULL, NULL, NULL, 1480351415, 1480352109, 1, 'Ryan', 'Lange', NULL, NULL);
158
159CREATE TABLE `users_groups` (
160 `id` int(11) unsigned NOT NULL,
161 `user_id` int(11) unsigned NOT NULL,
162 `group_id` mediumint(8) unsigned NOT NULL
163) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;
164
165INSERT INTO `users_groups` (`id`, `user_id`, `group_id`) VALUES
166(1, 1, 1),
167(2, 1, 2),
168(9, 1, 3),
169(15, 2, 1),
170(16, 2, 2),
171(17, 3, 2),
172(19, 4, 1),
173(20, 4, 2),
174(22, 5, 1),
175(23, 5, 2);
176
177
178ALTER TABLE `data_sources`
179 ADD PRIMARY KEY (`source_id`);
180
181ALTER TABLE `data_source_settings`
182 ADD PRIMARY KEY (`source_setting_id`);
183
184ALTER TABLE `groups`
185 ADD PRIMARY KEY (`id`);
186
187ALTER TABLE `login_attempts`
188 ADD PRIMARY KEY (`id`);
189
190ALTER TABLE `services`
191 ADD PRIMARY KEY (`service_id`);
192
193ALTER TABLE `service_fields`
194 ADD PRIMARY KEY (`field_id`);
195
196ALTER TABLE `service_field_values`
197 ADD PRIMARY KEY (`id`);
198
199ALTER TABLE `slides`
200 ADD PRIMARY KEY (`slide_id`);
201
202ALTER TABLE `users`
203 ADD PRIMARY KEY (`id`);
204
205ALTER TABLE `users_groups`
206 ADD PRIMARY KEY (`id`),
207 ADD UNIQUE KEY `uc_users_groups` (`user_id`,`group_id`),
208 ADD KEY `fk_users_groups_users1_idx` (`user_id`),
209 ADD KEY `fk_users_groups_groups1_idx` (`group_id`);
210
211
212ALTER TABLE `data_sources`
213 MODIFY `source_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=23;
214ALTER TABLE `data_source_settings`
215 MODIFY `source_setting_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=68;
216ALTER TABLE `groups`
217 MODIFY `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=4;
218ALTER TABLE `login_attempts`
219 MODIFY `id` int(11) unsigned NOT NULL AUTO_INCREMENT;
220ALTER TABLE `services`
221 MODIFY `service_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=3;
222ALTER TABLE `service_fields`
223 MODIFY `field_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=8;
224ALTER TABLE `service_field_values`
225 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=4;
226ALTER TABLE `slides`
227 MODIFY `slide_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=23;
228ALTER TABLE `users`
229 MODIFY `id` int(11) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=6;
230ALTER TABLE `users_groups`
231 MODIFY `id` int(11) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=24;
232
233ALTER TABLE `users_groups`
234 ADD CONSTRAINT `fk_users_groups_groups1` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
235 ADD CONSTRAINT `fk_users_groups_users1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;