1 #####################################################
2 # Database setup SQL for a new install of ATutor
3 #####################################################
6 # --------------------------------------------------------
7 # Table structure for table `admin_log`
10 CREATE TABLE `admins` (
11 `login` VARCHAR( 30 ) NOT NULL default '',
12 `password` VARCHAR( 40 ) NOT NULL default '',
13 `real_name` VARCHAR( 120 ) NOT NULL default '',
14 `email` VARCHAR( 50 ) NOT NULL default '',
15 `language` varchar(5) NOT NULL default '',
16 `privileges` MEDIUMINT UNSIGNED NOT NULL default 0,
17 `last_login` TIMESTAMP NOT NULL default 0,
18 PRIMARY KEY ( `login` )
21 CREATE TABLE `admin_log` (
22 `login` varchar(30) NOT NULL default '',
23 `time` TIMESTAMP NOT NULL,
24 `operation` varchar(20) NOT NULL default '',
25 `table` varchar(30) NOT NULL default '',
26 `num_affected` tinyint(3) NOT NULL default '0',
31 # --------------------------------------------------------
32 # Table structure for table `assignments`
35 CREATE TABLE `assignments` (
36 `assignment_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
37 `course_id` MEDIUMINT UNSIGNED NOT NULL default 0,
38 `title` VARCHAR(240) NOT NULL default '',
39 `assign_to` MEDIUMINT UNSIGNED default 0,
40 `date_due` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
41 `date_cutoff` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
42 `multi_submit` TINYINT DEFAULT '0',
43 PRIMARY KEY (`assignment_id`),
47 # --------------------------------------------------------
48 # Table structure for table `backups`
51 CREATE TABLE `backups` (
52 `backup_id` mediumint(8) unsigned NOT NULL auto_increment,
53 `course_id` mediumint(8) unsigned NOT NULL default '0',
54 `date` TIMESTAMP NOT NULL,
56 `file_size` int(10) unsigned NOT NULL default 0,
57 `system_file_name` varchar(50) NOT NULL default '',
60 PRIMARY KEY (`backup_id`),
61 KEY `course_id` (`course_id`)
64 # --------------------------------------------------------
65 ## Table for `blog_posts`
67 CREATE TABLE `blog_posts` (
68 `post_id` mediumint(8) unsigned NOT NULL auto_increment,
69 `member_id` mediumint(8) unsigned NOT NULL default '0',
70 `owner_type` tinyint(3) unsigned NOT NULL default '0',
71 `owner_id` mediumint(8) unsigned NOT NULL default '0',
72 `private` tinyint(3) unsigned NOT NULL default '0',
73 `date` TIMESTAMP NOT NULL,
74 `num_comments` tinyint(3) unsigned NOT NULL default '0',
75 `title` VARCHAR(255) NOT NULL,
77 PRIMARY KEY (`post_id`)
80 # --------------------------------------------------------
81 ## Table for `blog_posts_comments`
83 CREATE TABLE `blog_posts_comments` (
84 `comment_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,
85 `post_id` MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL ,
86 `member_id` MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL ,
87 `date` TIMESTAMP NOT NULL,
88 `private` TINYINT UNSIGNED DEFAULT '0' NOT NULL ,
90 PRIMARY KEY ( `comment_id` ) ,
94 # --------------------------------------------------------
95 # Table structure for table `config`
98 CREATE TABLE `config` (
99 `name` CHAR( 30 ) NOT NULL default '',
101 PRIMARY KEY ( `name` )
104 # --------------------------------------------------------
105 # Table structure for table `content`
107 CREATE TABLE `content` (
108 `content_id` mediumint(8) unsigned NOT NULL auto_increment,
109 `course_id` mediumint(8) unsigned NOT NULL default '0',
110 `content_parent_id` mediumint(8) unsigned NOT NULL default '0',
111 `ordering` mediumint(8) NOT NULL default '0',
112 `last_modified` TIMESTAMP NOT NULL,
113 `revision` tinyint(3) unsigned NOT NULL default '0',
114 `formatting` tinyint(4) NOT NULL default '0',
115 `release_date` datetime NOT NULL default '0000-00-00 00:00:00',
117 `content_path` TEXT ,
118 `title` VARCHAR(255) NOT NULL ,
121 `use_customized_head` TINYINT(4) NOT NULL,
123 `allow_test_export` TINYINT(1) UNSIGNED NOT NULL,
124 `content_type` TINYINT(1) UNSIGNED NOT NULL,
125 PRIMARY KEY (`content_id`),
126 KEY `course_id` (`course_id`)
129 # --------------------------------------------------------
130 # Table structure for table `course_access`
132 CREATE TABLE `course_access` (
133 `password` char(8) NOT NULL ,
134 `course_id` mediumint(8) unsigned NOT NULL ,
135 `expiry_date` timestamp NOT NULL ,
136 `enabled` tinyint(4) NOT NULL ,
137 PRIMARY KEY ( `password` ) ,
141 # --------------------------------------------------------
142 # Table structure for table `course_cats`
144 CREATE TABLE `course_cats` (
145 `cat_id` mediumint(8) unsigned NOT NULL auto_increment,
146 `cat_name` VARCHAR(255) NOT NULL ,
147 `cat_parent` mediumint(8) unsigned NOT NULL default '0',
148 `theme` VARCHAR(30) NOT NULL default '',
149 PRIMARY KEY (`cat_id`)
152 # --------------------------------------------------------
153 # Table structure for table `course_tests_assoc`
156 CREATE TABLE `content_tests_assoc` (
157 `content_id` INTEGER UNSIGNED NOT NULL,
158 `test_id` INTEGER UNSIGNED NOT NULL,
159 PRIMARY KEY (`content_id`, `test_id`)
163 # --------------------------------------------------------
164 # Table structure for table `content_forums_assoc`
166 CREATE TABLE `content_forums_assoc` (
167 `content_id` INTEGER UNSIGNED NOT NULL,
168 `forum_id` INTEGER UNSIGNED NOT NULL,
169 PRIMARY KEY ( `content_id` , `forum_id` )
172 # --------------------------------------------------------
173 # Table structure for table `course_enrollment`
175 CREATE TABLE `course_enrollment` (
176 `member_id` mediumint(8) unsigned NOT NULL default '0',
177 `course_id` mediumint(8) unsigned NOT NULL default '0',
178 `approved` enum('y','n','a') NOT NULL default 'n',
179 `privileges` INT(10) unsigned NOT NULL default '0',
180 `role` varchar(35) NOT NULL default '',
181 `last_cid` mediumint(8) unsigned NOT NULL default '0',
182 PRIMARY KEY (`member_id`,`course_id`)
185 # --------------------------------------------------------
186 # Table structure for table `course_stats`
188 CREATE TABLE `course_stats` (
189 `course_id` mediumint(8) unsigned NOT NULL default '0',
190 `login_date` date NOT NULL default '0000-00-00',
191 `guests` mediumint(8) unsigned NOT NULL default '0',
192 `members` mediumint(8) unsigned NOT NULL default '0',
193 PRIMARY KEY (`course_id`,`login_date`)
196 # --------------------------------------------------------
197 # Table structure for table `courses`
199 CREATE TABLE `courses` (
200 `course_id` mediumint(8) unsigned NOT NULL auto_increment,
201 `member_id` mediumint(8) unsigned NOT NULL default '0',
202 `cat_id` mediumint(8) unsigned NOT NULL default '0',
203 `content_packaging` enum('none','top','all') NOT NULL default 'top',
204 `access` enum('public','protected','private') NOT NULL default 'public',
205 `created_date` datetime NOT NULL default '0000-00-00 00:00:00',
206 `title` VARCHAR(255) NOT NULL ,
208 `course_dir_name` VARCHAR(255) NOT NULL,
209 `notify` tinyint(4) NOT NULL default '0',
210 `max_quota` varchar(30) NOT NULL default '',
211 `max_file_size` varchar(30) NOT NULL default '',
212 `hide` tinyint(4) NOT NULL default '0',
214 `primary_language` varchar(5) NOT NULL default '',
215 `rss` tinyint NOT NULL default 0,
216 `icon` varchar(75) NOT NULL default '',
219 `side_menu` VARCHAR( 255 ) NOT NULL default '',
220 `release_date` datetime NOT NULL default '0000-00-00 00:00:00',
221 `end_date` datetime NOT NULL default '0000-00-00 00:00:00',
223 `home_view` tinyint NOT NULL DEFAULT 1,
224 PRIMARY KEY (`course_id`)
227 # --------------------------------------------------------
228 # Table structure for table `faq_topics`
230 CREATE TABLE `faq_topics` (
231 `topic_id` mediumint(8) NOT NULL auto_increment,
232 `course_id` mediumint(8) unsigned NOT NULL default '0',
234 KEY `course_id` (`course_id`),
235 PRIMARY KEY (`topic_id`)
238 # --------------------------------------------------------
239 # Table structure for table `faq_entries`
241 CREATE TABLE `faq_entries` (
242 `entry_id` mediumint(8) NOT NULL auto_increment,
243 `topic_id` mediumint(8) NOT NULL default '0',
244 `revised_date` TIMESTAMP NOT NULL,
245 `approved` tinyint(4) NOT NULL default '0',
248 PRIMARY KEY (`entry_id`)
251 # --------------------------------------------------------
252 # Table structure for table `feeds`
254 CREATE TABLE `feeds` (
255 `feed_id` mediumint(8) unsigned NOT NULL auto_increment,
256 `url` varchar(255) NOT NULL default '',
257 PRIMARY KEY (`feed_id`)
260 # --------------------------------------------------------
261 # Table structure for table `file_storage_groups`
264 CREATE TABLE `file_storage_groups` (
265 `group_id` MEDIUMINT UNSIGNED NOT NULL default 0,
266 PRIMARY KEY ( `group_id` )
269 # --------------------------------------------------------
270 # Table structure for table `files`
273 CREATE TABLE `files` (
274 `file_id` mediumint(8) unsigned NOT NULL auto_increment,
275 `owner_type` tinyint(3) unsigned NOT NULL default '0',
276 `owner_id` mediumint(8) unsigned NOT NULL default '0',
277 `member_id` mediumint(8) unsigned NOT NULL default '0',
278 `folder_id` mediumint(8) unsigned NOT NULL default '0',
279 `parent_file_id` mediumint(8) unsigned NOT NULL default '0',
280 `date` TIMESTAMP NOT NULL,
281 `num_comments` tinyint(3) unsigned NOT NULL default '0',
282 `num_revisions` tinyint(3) unsigned NOT NULL default '0',
283 `file_name` varchar(80) NOT NULL default '',
284 `file_size` int(11) NOT NULL default '0',
286 PRIMARY KEY (`file_id`)
289 # --------------------------------------------------------
290 # Table structure for table `files_comments`
293 CREATE TABLE `files_comments` (
294 `comment_id` mediumint(8) unsigned NOT NULL auto_increment,
295 `file_id` mediumint(8) unsigned NOT NULL default '0',
296 `member_id` mediumint(8) unsigned NOT NULL default '0',
297 `date` TIMESTAMP NOT NULL,
299 PRIMARY KEY (`comment_id`)
302 # --------------------------------------------------------
305 # Table structure for table `folders`
308 CREATE TABLE `folders` (
309 `folder_id` mediumint(8) unsigned NOT NULL auto_increment,
310 `parent_folder_id` mediumint(8) unsigned NOT NULL default '0',
311 `owner_type` tinyint(3) unsigned NOT NULL default '0',
312 `owner_id` mediumint(8) unsigned NOT NULL default '0',
313 `title` varchar(120) NOT NULL default '',
314 PRIMARY KEY (`folder_id`)
317 # --------------------------------------------------------
318 # Table structure for table `forums`
320 CREATE TABLE `forums` (
321 `forum_id` mediumint(8) unsigned NOT NULL auto_increment,
322 `title` varchar(240) NOT NULL default '',
324 `num_topics` MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL ,
325 `num_posts` MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL ,
326 `last_post` TIMESTAMP NOT NULL,
327 `mins_to_edit` SMALLINT UNSIGNED NOT NULL DEFAULT '0',
328 PRIMARY KEY (`forum_id`)
331 # --------------------------------------------------------
332 # Table structure for table `forums_accessed`
334 CREATE TABLE `forums_accessed` (
335 `post_id` mediumint(8) unsigned NOT NULL default '0',
336 `member_id` mediumint(8) unsigned NOT NULL default '0',
337 `last_accessed` timestamp NOT NULL,
338 `subscribe` tinyint(4) NOT NULL default '0',
339 PRIMARY KEY (`post_id`,`member_id`)
342 # --------------------------------------------------------
343 # Table structure for table `forums_courses`
345 CREATE TABLE `forums_courses` (
346 `forum_id` MEDIUMINT UNSIGNED NOT NULL default '0',
347 `course_id` MEDIUMINT UNSIGNED NOT NULL default '0',
348 PRIMARY KEY (`forum_id`,`course_id`),
349 KEY `course_id` (`course_id`)
352 # --------------------------------------------------------
353 # Table structure for table `forums_groups`
355 CREATE TABLE `forums_groups` (
356 `forum_id` mediumint( 8 ) unsigned NOT NULL default '0',
357 `group_id` mediumint( 8 ) unsigned NOT NULL default '0',
358 PRIMARY KEY ( `forum_id` , `group_id` ) ,
359 KEY `group_id` ( `group_id` )
362 # --------------------------------------------------------
363 # Table structure for table `forums_subscriptions`
366 CREATE TABLE `forums_subscriptions` (
367 forum_id mediumint(8) unsigned NOT NULL default '0',
368 member_id mediumint(8) unsigned NOT NULL default '0',
369 PRIMARY KEY (`forum_id`,`member_id`)
372 # --------------------------------------------------------
373 # Table structure for table `forums_threads`
375 CREATE TABLE `forums_threads` (
376 `post_id` mediumint(8) unsigned NOT NULL auto_increment,
377 `parent_id` mediumint(8) unsigned NOT NULL default '0',
378 `member_id` mediumint(8) unsigned NOT NULL default '0',
379 `forum_id` mediumint(8) unsigned NOT NULL default '0',
380 `last_comment` TIMESTAMP NOT NULL,
381 `num_comments` mediumint(8) unsigned NOT NULL default '0',
382 `subject` VARCHAR(255) NOT NULL ,
384 `date` TIMESTAMP NOT NULL,
385 `locked` tinyint(4) NOT NULL default '0',
386 `sticky` tinyint(4) NOT NULL default '0',
387 PRIMARY KEY (`post_id`)
390 # --------------------------------------------------------
391 # Table structure for table `glossary`
393 CREATE TABLE `glossary` (
394 `word_id` mediumint(8) unsigned NOT NULL auto_increment,
395 `course_id` mediumint(8) unsigned NOT NULL default '0',
396 `word` varchar(240) NOT NULL default '',
398 `related_word_id` mediumint(8) unsigned NOT NULL default '0',
399 PRIMARY KEY (`word_id`),
400 KEY `course_id` (`course_id`)
403 # --------------------------------------------------------
404 # Table structure for table `groups`
406 CREATE TABLE `groups` (
407 `group_id` mediumint(8) unsigned NOT NULL auto_increment,
408 `type_id` mediumint(8) unsigned NOT NULL default '0',
409 `title` varchar(80) NOT NULL default '',
411 `modules` varchar(255) NOT NULL default '',
412 PRIMARY KEY (`group_id`)
415 # --------------------------------------------------------
416 # Table structure for table `groups_members`
418 CREATE TABLE `groups_members` (
419 `group_id` MEDIUMINT UNSIGNED NOT NULL default '0',
420 `member_id` MEDIUMINT UNSIGNED NOT NULL default '0',
421 PRIMARY KEY (`group_id`,`member_id`)
424 # --------------------------------------------------------
425 # Table structure for table `groups_types` (since 1.5.3)
427 CREATE TABLE `groups_types` (
428 `type_id` mediumint(8) unsigned NOT NULL auto_increment,
429 `course_id` mediumint(8) unsigned NOT NULL default '0',
430 `title` VARCHAR(80) NOT NULL ,
431 PRIMARY KEY (`type_id`),
432 KEY `course_id` (`course_id`)
435 # --------------------------------------------------------
436 # Table structure for table `guests` (since 1.6.2)
438 CREATE TABLE `guests` (
439 `guest_id` VARCHAR(10) NOT NULL,
441 `organization` VARCHAR(255),
442 `location` VARCHAR(255),
444 `focus` VARCHAR(255),
445 PRIMARY KEY (`guest_id`)
448 # --------------------------------------------------------
449 # Table structure for table `handbook_notes`
451 CREATE TABLE `handbook_notes` (
452 `note_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,
453 `date` TIMESTAMP NOT NULL,
454 `section` VARCHAR( 15 ) NOT NULL default '',
455 `page` VARCHAR( 50 ) NOT NULL default '',
456 `approved` tinyint NOT NULL default 0,
457 `email` VARCHAR( 50 ) NOT NULL default '',
459 PRIMARY KEY ( `note_id` )
462 # --------------------------------------------------------
463 # Table structure for table `instructor_approvals`
465 CREATE TABLE `instructor_approvals` (
466 `member_id` mediumint(8) unsigned NOT NULL default '0',
467 `request_date` TIMESTAMP NOT NULL,
469 PRIMARY KEY (`member_id`)
472 CREATE TABLE `languages` (
473 `language_code` varchar(20) NOT NULL default '',
474 `char_set` varchar(80) NOT NULL default '',
475 `direction` varchar(16) NOT NULL default '',
476 `reg_exp` varchar(124) NOT NULL default '',
477 `native_name` varchar(80) NOT NULL default '',
478 `english_name` varchar(80) NOT NULL default '',
479 `status` TINYINT UNSIGNED DEFAULT '0' NOT NULL,
480 PRIMARY KEY (`language_code`,`char_set`)
484 # Dumping data for table `languages`
487 INSERT INTO `languages` VALUES ('en', 'utf-8', 'ltr', 'en([-_][[:alpha:]]{2})?|english', 'English', 'English', 3);
489 # Table structure for table `links_categories`
491 CREATE TABLE `links_categories` (
492 `cat_id` mediumint(8) unsigned NOT NULL auto_increment,
493 `owner_type` tinyint(4) NOT NULL default '0',
494 `owner_id` mediumint(8) unsigned NOT NULL default '0',
495 `name` VARCHAR(255) NOT NULL ,
496 `parent_id` mediumint(8) unsigned default NULL,
497 PRIMARY KEY (`cat_id`),
498 KEY `owner_id` (`owner_id`)
501 # --------------------------------------------------------
502 # Table structure for table `links`
504 CREATE TABLE `links` (
505 `link_id` mediumint(8) unsigned NOT NULL auto_increment,
506 `cat_id` mediumint(8) unsigned NOT NULL default '0',
507 `Url` varchar(255) NOT NULL default '',
508 `LinkName` varchar(64) NOT NULL default '',
510 `Approved` tinyint(8) default '0',
511 `SubmitName` varchar(64) NOT NULL default '',
512 `SubmitEmail` varchar(64) NOT NULL default '',
513 `SubmitDate` date NOT NULL default '0000-00-00',
514 `hits` int(11) default '0',
515 PRIMARY KEY (`link_id`)
518 # --------------------------------------------------------
519 # Table structure for table `language_pages`
521 CREATE TABLE `language_pages` (
522 `term` varchar(30) NOT NULL default '',
523 `page` varchar(50) NOT NULL default '',
524 PRIMARY KEY (`term`,`page`)
527 # --------------------------------------------------------
528 # Table structure for table `master_list`
530 CREATE TABLE `master_list` (
531 `public_field` CHAR( 30 ) NOT NULL default '',
532 `hash_field` CHAR( 40 ) NOT NULL default '',
533 `member_id` MEDIUMINT UNSIGNED NOT NULL default 0,
534 PRIMARY KEY ( `public_field` )
537 # --------------------------------------------------------
538 # Table structure for table `members`
540 CREATE TABLE `members` (
541 `member_id` mediumint(8) unsigned NOT NULL auto_increment,
542 `login` varchar(20) NOT NULL default '',
543 `password` varchar(40) NOT NULL default '',
544 `email` varchar(50) NOT NULL default '',
545 `website` varchar(200) NOT NULL default '',
546 `first_name` VARCHAR(100) NOT NULL ,
547 `second_name` varchar(100) NOT NULL default '',
548 `last_name` VARCHAR(100) NOT NULL ,
549 `dob` date NOT NULL default '0000-00-00',
550 `gender` enum('m','f','n') NOT NULL default 'n',
552 `postal` varchar(15) NOT NULL default '',
553 `city` varchar(100) NOT NULL default '',
554 `province` varchar(100) NOT NULL default '',
555 `country` varchar(100) NOT NULL default '',
556 `phone` varchar(15) NOT NULL default '',
557 `status` tinyint(4) NOT NULL default '0',
559 `creation_date` TIMESTAMP NOT NULL,
560 `language` varchar(5) NOT NULL default '',
561 `inbox_notify` tinyint(3) unsigned NOT NULL default '0',
562 `private_email` TINYINT DEFAULT '1' NOT NULL,
563 `last_login` TIMESTAMP NOT NULL,
564 PRIMARY KEY (`member_id`),
565 UNIQUE KEY `login` (`login`)
568 # --------------------------------------------------------
569 # Table structure for table `member_track`
571 CREATE TABLE `member_track` (
572 `member_id` mediumint(8) unsigned NOT NULL default '0',
573 `course_id` mediumint(8) unsigned NOT NULL default '0',
574 `content_id` mediumint(8) unsigned NOT NULL default '0',
575 `counter` mediumint(8) unsigned NOT NULL default '0',
576 `duration` mediumint(8) unsigned NOT NULL default '0',
577 `last_accessed` TIMESTAMP NULL,
578 KEY `member_id` (`member_id`),
579 KEY `content_id` (`content_id`)
582 # --------------------------------------------------------
583 # Table structure for table `messages`
585 CREATE TABLE `messages` (
586 `message_id` mediumint(8) unsigned NOT NULL auto_increment,
587 `course_id` mediumint(8) unsigned NOT NULL default '0',
588 `from_member_id` mediumint(8) unsigned NOT NULL default '0',
589 `to_member_id` mediumint(8) unsigned NOT NULL default '0',
590 `date_sent` TIMESTAMP NOT NULL,
591 `new` tinyint(4) NOT NULL default '0',
592 `replied` tinyint(4) NOT NULL default '0',
593 `subject` VARCHAR(255) NOT NULL ,
595 PRIMARY KEY (`message_id`),
596 KEY `to_member_id` (`to_member_id`)
599 # --------------------------------------------------------
600 # Table structure for table `messages_sent` (since 1.5.4)
602 CREATE TABLE `messages_sent` (
603 `message_id` mediumint( 8 ) unsigned NOT NULL AUTO_INCREMENT ,
604 `course_id` mediumint( 8 ) unsigned NOT NULL default '0',
605 `from_member_id` mediumint( 8 ) unsigned NOT NULL default '0',
606 `to_member_id` mediumint( 8 ) unsigned NOT NULL default '0',
607 `date_sent` timestamp NOT NULL ,
608 `subject` VARCHAR(255) NOT NULL ,
610 PRIMARY KEY ( `message_id` ) ,
611 KEY `from_member_id` ( `from_member_id` )
614 # --------------------------------------------------------
615 # Table structure for table `modules` (since 1.5.2)
617 CREATE TABLE `modules` (
618 `dir_name` VARCHAR( 50 ) NOT NULL default '',
619 `status` TINYINT NOT NULL default 0,
620 `privilege` BIGINT UNSIGNED NOT NULL default 0,
621 `admin_privilege` MEDIUMINT UNSIGNED NOT NULL default 0,
622 `cron_interval` SMALLINT UNSIGNED DEFAULT '0' NOT NULL ,
623 `cron_last_run` INT UNSIGNED DEFAULT '0' NOT NULL,
624 PRIMARY KEY ( `dir_name` )
627 INSERT INTO `modules` VALUES ('_core/properties', 2, 1, 0, 0, 0);
628 INSERT INTO `modules` VALUES ('_standard/statistics', 2, 1, 0, 0, 0);
629 INSERT INTO `modules` VALUES ('_core/content', 2, 2, 0, 0, 0);
630 INSERT INTO `modules` VALUES ('_core/glossary', 2, 4, 0, 0, 0);
631 INSERT INTO `modules` VALUES ('_standard/tests', 2, 8, 0, 0, 0);
632 INSERT INTO `modules` VALUES ('_standard/chat', 2, 16, 0, 0, 0);
633 INSERT INTO `modules` VALUES ('_core/file_manager', 2, 32, 0, 0, 0);
634 INSERT INTO `modules` VALUES ('_standard/links', 2, 64, 0, 0, 0);
635 INSERT INTO `modules` VALUES ('_standard/forums', 2, 128, 16, 0, 0);
636 INSERT INTO `modules` VALUES ('_standard/course_tools', 2, 256, 0, 0, 0);
637 INSERT INTO `modules` VALUES ('_core/enrolment', 2, 512, 512, 0, 0);
638 INSERT INTO `modules` VALUES ('_standard/course_email', 2, 1024, 0, 0, 0);
639 INSERT INTO `modules` VALUES ('_standard/announcements', 2, 2048, 0, 0, 0);
640 INSERT INTO `modules` VALUES ('_standard/polls', 2, 16384, 0, 0, 0);
641 INSERT INTO `modules` VALUES ('_standard/faq', 2, 32768, 0, 0, 0);
642 INSERT INTO `modules` VALUES ('_core/groups', 2, 65536, 0, 0, 0);
643 INSERT INTO `modules` VALUES ('_standard/reading_list', 2, 131072, 0, 0, 0);
644 INSERT INTO `modules` VALUES ('_standard/file_storage', 2, 262144, 0, 0, 0);
645 INSERT INTO `modules` VALUES ('_standard/assignments', 2, 524288, 0, 0, 0);
646 INSERT INTO `modules` VALUES ('_standard/gradebook', 2, 1048576, 4096, 0, 0);
647 INSERT INTO `modules` VALUES ('_standard/student_tools', 2, 2097152, 0, 0, 0);
648 INSERT INTO `modules` VALUES ('_standard/farchive', 2, 4194304, 0, 0, 0);
649 INSERT INTO `modules` VALUES ('_standard/social', 2, 8388608, 0, 0, 0);
650 INSERT INTO `modules` VALUES ('_standard/photos', 2, 16777216, 0, 0, 0);
651 INSERT INTO `modules` VALUES ('_standard/flowplayer', 2, 33554432, 0, 0, 0);
652 INSERT INTO `modules` VALUES ('_standard/basiclti', 2, 67108864, 16384, 0, 0);
653 INSERT INTO `modules` VALUES ('_standard/assignment_dropbox', 2, 134217728, 0, 0, 0);
654 INSERT INTO `modules` VALUES ('_core/users', 2, 0, 2, 0, 0);
655 INSERT INTO `modules` VALUES ('_core/courses', 2, 0, 4, 0, 0);
656 INSERT INTO `modules` VALUES ('_core/backups', 2, 1, 8, 0, 0);
657 INSERT INTO `modules` VALUES ('_core/cats_categories', 2, 0, 32, 0, 0);
658 INSERT INTO `modules` VALUES ('_core/languages', 2, 0, 64, 1440, 0);
659 INSERT INTO `modules` VALUES ('_core/themes', 2, 0, 128, 0, 0);
660 INSERT INTO `modules` VALUES ('_standard/rss_feeds', 2, 0, 256, 0, 0);
661 INSERT INTO `modules` VALUES ('_standard/directory', 2, 0, 0, 0, 0);
662 INSERT INTO `modules` VALUES ('_standard/tile_search', 2, 0, 0, 0, 0);
663 INSERT INTO `modules` VALUES ('_standard/sitemap', 2, 0, 0, 0, 0);
664 INSERT INTO `modules` VALUES ('_standard/tracker', 2, 0, 0, 0, 0);
665 INSERT INTO `modules` VALUES ('_core/content_packaging', 2, 0, 0, 0, 0);
666 INSERT INTO `modules` VALUES ('_standard/google_search', 2, 0, 0, 0, 0);
667 INSERT INTO `modules` VALUES ('_standard/blogs', 2, 0, 0, 0, 0);
668 INSERT INTO `modules` VALUES ('_standard/profile_pictures', 2, 0, 0, 0, 0);
669 INSERT INTO `modules` VALUES ('_standard/patcher', 2, 0, 1024, 0, 0);
670 INSERT INTO `modules` VALUES ('_standard/support_tools', 2, 0, 2048, 0, 0);
671 # added by Bologna CC. Please check if it is the right position to insert it!
672 INSERT INTO `modules` VALUES ('_core/tool_manager', 2, 0, 0, 0, 0);
673 INSERT INTO `modules` VALUES ('_core/modules', 2, 0, 8192, 0, 0);
674 INSERT INTO `modules` VALUES('_standard/vimeo', 2, 0, 1, 0, 0);
676 # --------------------------------------------------------
677 # Table structure for table `news`
679 CREATE TABLE `news` (
680 `news_id` mediumint(8) unsigned NOT NULL auto_increment,
681 `course_id` mediumint(8) unsigned NOT NULL default '0',
682 `member_id` mediumint(8) unsigned NOT NULL default '0',
683 `date` TIMESTAMP NOT NULL,
684 `formatting` tinyint(4) NOT NULL default '0',
685 `title` VARCHAR(200) NOT NULL ,
687 PRIMARY KEY (`news_id`)
690 # --------------------------------------------------------
692 # Table structure for table `polls`
694 CREATE TABLE `polls` (
695 `poll_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,
696 `course_id` MEDIUMINT UNSIGNED NOT NULL default 0,
697 `question` VARCHAR(255) NOT NULL ,
698 `created_date` TIMESTAMP NOT NULL,
699 `total` SMALLINT UNSIGNED NOT NULL default '0',
700 `choice1` VARCHAR(255) NOT NULL ,
701 `count1` SMALLINT UNSIGNED NOT NULL default '0',
702 `choice2` VARCHAR(255) NOT NULL ,
703 `count2` SMALLINT UNSIGNED NOT NULL default '0',
704 `choice3` VARCHAR(255) NOT NULL ,
705 `count3` SMALLINT UNSIGNED NOT NULL default '0',
706 `choice4` VARCHAR(255) NOT NULL ,
707 `count4` SMALLINT UNSIGNED NOT NULL default '0',
708 `choice5` VARCHAR(255) NOT NULL ,
709 `count5` SMALLINT UNSIGNED NOT NULL default '0',
710 `choice6` VARCHAR(255) NOT NULL ,
711 `count6` SMALLINT UNSIGNED NOT NULL default '0',
712 `choice7` VARCHAR(255) NOT NULL ,
713 `count7` SMALLINT UNSIGNED NOT NULL default '0',
714 PRIMARY KEY ( `poll_id` ) ,
715 INDEX ( `course_id` )
718 # --------------------------------------------------------
719 # Table structure for table `mail_queue`
722 CREATE TABLE `mail_queue` (
723 `mail_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,
724 `to_email` VARCHAR( 50 ) NOT NULL default '',
725 `to_name` VARCHAR( 50 ) NOT NULL default '',
726 `from_email` VARCHAR( 50 ) NOT NULL default '',
727 `from_name` VARCHAR( 50 ) NOT NULL default '',
728 `char_set` VARCHAR( 20 ) NOT NULL default '',
729 `subject` VARCHAR(255) NOT NULL ,
731 PRIMARY KEY ( `mail_id` )
734 # --------------------------------------------------------
735 # Table structure for table `polls_members`
737 CREATE TABLE `polls_members` (
738 `poll_id` MEDIUMINT UNSIGNED NOT NULL default '0',
739 `member_id` MEDIUMINT UNSIGNED NOT NULL default '0',
740 PRIMARY KEY ( `poll_id` , `member_id` )
743 # --------------------------------------------------------
744 # Table structure for table `related_content`
745 CREATE TABLE `related_content` (
746 `content_id` mediumint(8) unsigned NOT NULL default '0',
747 `related_content_id` mediumint(8) unsigned NOT NULL default '0',
748 PRIMARY KEY (`content_id`,`related_content_id`)
751 # --------------------------------------------------------
753 # Table structure for table `reading_list`
755 CREATE TABLE `reading_list` (
756 `reading_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
757 `course_id` MEDIUMINT UNSIGNED NOT NULL default 0,
758 `resource_id` MEDIUMINT UNSIGNED NOT NULL default 0,
759 `required` enum('required','optional') NOT NULL DEFAULT 'required',
760 `date_start` DATE NOT NULL DEFAULT '0000-00-00',
761 `date_end` DATE NOT NULL DEFAULT '0000-00-00',
763 PRIMARY KEY (`reading_id`),
768 # Table structure for table `external_resources`
770 CREATE TABLE `external_resources` (
771 `resource_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
772 `course_id` MEDIUMINT UNSIGNED NOT NULL default 0,
773 `type` TINYINT UNSIGNED NOT NULL DEFAULT 0,
774 `title` VARCHAR(255) NOT NULL ,
775 `author` VARCHAR(150) NOT NULL ,
776 `publisher` VARCHAR(150) NOT NULL ,
777 `date` varchar(20) NOT NULL DEFAULT '',
779 `id` varchar(50) NOT NULL DEFAULT '',
780 `url` varchar(255) NOT NULL DEFAULT '',
781 PRIMARY KEY (`resource_id`),
786 # --------------------------------------------------------
787 # Table structure for table `tests`
789 CREATE TABLE `tests` (
790 `test_id` mediumint(8) unsigned NOT NULL auto_increment,
791 `course_id` mediumint(8) unsigned NOT NULL default '0',
792 `title` VARCHAR(255) NOT NULL ,
793 `format` tinyint(4) NOT NULL default '0',
794 `start_date` datetime NOT NULL default '0000-00-00 00:00:00',
795 `end_date` datetime NOT NULL default '0000-00-00 00:00:00',
796 `randomize_order` tinyint(4) NOT NULL default '0',
797 `num_questions` tinyint(3) unsigned NOT NULL default '0',
798 `instructions` TEXT ,
799 `content_id` mediumint(8) NOT NULL default '0',
800 `result_release` tinyint(4) unsigned NOT NULL default '0',
801 `random` tinyint(4) unsigned NOT NULL default '0',
802 `difficulty` tinyint(4) unsigned NOT NULL default '0',
803 `num_takes` tinyint(4) unsigned NOT NULL default '0',
804 `anonymous` tinyint(4) NOT NULL default '0',
805 `out_of` varchar(4) NOT NULL default '',
806 `guests` TINYINT NOT NULL DEFAULT '0',
807 `display` TINYINT NOT NULL DEFAULT '0',
809 `passscore` MEDIUMINT NOT NULL default '0',
810 `passpercent` MEDIUMINT NOT NULL default '0',
813 `show_guest_form` TINYINT(1) UNSIGNED NOT NULL default '0',
814 PRIMARY KEY (`test_id`)
817 # --------------------------------------------------------
818 # Table structure for table `tests_answers`
820 CREATE TABLE `tests_answers` (
821 `result_id` mediumint(8) unsigned NOT NULL default '0',
822 `question_id` mediumint(8) unsigned NOT NULL default '0',
823 `member_id` mediumint(8) unsigned NOT NULL default '0',
825 `score` varchar(5) NOT NULL default '',
827 PRIMARY KEY (`result_id`,`question_id`,`member_id`)
830 # --------------------------------------------------------
831 # Table structure for table `tests_groups`
833 CREATE TABLE `tests_groups` (
834 `test_id` MEDIUMINT UNSIGNED NOT NULL default '0',
835 `group_id` MEDIUMINT UNSIGNED NOT NULL default '0',
836 PRIMARY KEY (`test_id`,`group_id`),
837 KEY `test_id` (`test_id`)
840 # --------------------------------------------------------
841 # Table structure for table `tests_questions`
843 CREATE TABLE `tests_questions` (
844 `question_id` mediumint(8) unsigned NOT NULL auto_increment,
845 `category_id` mediumint(8) unsigned NOT NULL default '0',
846 `course_id` mediumint(8) unsigned NOT NULL default '0',
847 `type` tinyint(3) unsigned NOT NULL default '0',
860 `answer_0` tinyint(4) NOT NULL default '0',
861 `answer_1` tinyint(4) NOT NULL default '0',
862 `answer_2` tinyint(4) NOT NULL default '0',
863 `answer_3` tinyint(4) NOT NULL default '0',
864 `answer_4` tinyint(4) NOT NULL default '0',
865 `answer_5` tinyint(4) NOT NULL default '0',
866 `answer_6` tinyint(4) NOT NULL default '0',
867 `answer_7` tinyint(4) NOT NULL default '0',
868 `answer_8` tinyint(4) NOT NULL default '0',
869 `answer_9` tinyint(4) NOT NULL default '0',
880 `properties` tinyint(4) NOT NULL default '0',
881 `content_id` mediumint(8) NOT NULL,
882 PRIMARY KEY (`question_id`),
883 KEY `category_id` (category_id)
886 # --------------------------------------------------------
887 # Table structure for table `tests_questions_assoc`
889 CREATE TABLE `tests_questions_assoc` (
890 `test_id` mediumint(8) unsigned NOT NULL default '0',
891 `question_id` mediumint(8) unsigned NOT NULL default '0',
892 `weight` varchar(4) NOT NULL default '',
893 `ordering` mediumint(8) unsigned NOT NULL default '0',
894 `required` tinyint(3) unsigned NOT NULL default '0',
895 PRIMARY KEY (`test_id`,`question_id`)
898 # --------------------------------------------------------
899 # Table structure for table `tests_questions_categories`
901 CREATE TABLE `tests_questions_categories` (
902 `category_id` mediumint(8) unsigned NOT NULL auto_increment,
903 `course_id` mediumint(8) unsigned NOT NULL default '0',
904 `title` char(200) NOT NULL default '',
905 PRIMARY KEY (`category_id`),
906 KEY `course_id` (`course_id`)
909 # --------------------------------------------------------
910 # Table structure for table `tests_results`
912 CREATE TABLE `tests_results` (
913 `result_id` mediumint(8) unsigned NOT NULL auto_increment,
914 `test_id` mediumint(8) unsigned NOT NULL default '0',
915 `member_id` VARCHAR(10) NOT NULL default '',
916 `date_taken` TIMESTAMP NOT NULL,
917 `final_score` char(5) NOT NULL default '',
918 `status` TINYINT NOT NULL DEFAULT '0',
919 `end_time` TIMESTAMP NOT NULL ,
920 `max_pos` TINYINT UNSIGNED NOT NULL DEFAULT '0',
921 PRIMARY KEY (`result_id`),
922 KEY `test_id` (`test_id`)
925 # --------------------------------------------------------
926 # Table structure for table `themes`
929 CREATE TABLE `themes` (
930 `title` varchar(80) NOT NULL default '',
931 `version` varchar(10) NOT NULL default '',
932 `dir_name` varchar(20) NOT NULL default '',
933 `type` varchar(20) NOT NULL default 'Desktop',
934 `last_updated` date NOT NULL default '0000-00-00',
936 `status` tinyint(3) unsigned NOT NULL default '1',
937 PRIMARY KEY (`title`)
940 # --------------------------------------------------------
941 # Table structure for table `patches`
944 CREATE TABLE `patches` (
945 `patches_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
946 `atutor_patch_id` VARCHAR(20) NOT NULL default '',
947 `applied_version` VARCHAR(10) NOT NULL default '',
948 `patch_folder` VARCHAR(250) NOT NULL default '',
950 `available_to` VARCHAR(250) NOT NULL default '',
951 `sql_statement` TEXT,
952 `status` varchar(20) NOT NULL default '',
953 `remove_permission_files` TEXT,
956 `author` VARCHAR(255) NOT NULL,
957 `installed_date` datetime NOT NULL,
958 PRIMARY KEY (`patches_id`)
961 # --------------------------------------------------------
962 # Table structure for table `patches_files`
965 CREATE TABLE `patches_files` (
966 `patches_files_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
967 `patches_id` MEDIUMINT UNSIGNED NOT NULL default 0,
968 `action` VARCHAR(20) NOT NULL default '',
970 `location` VARCHAR(250) NOT NULL default '',
971 PRIMARY KEY (`patches_files_id`)
974 # --------------------------------------------------------
975 # Table structure for table `patches_files_actions`
978 CREATE TABLE `patches_files_actions` (
979 `patches_files_actions_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
980 `patches_files_id` MEDIUMINT UNSIGNED NOT NULL default 0,
981 `action` VARCHAR(20) NOT NULL default '',
984 PRIMARY KEY (`patches_files_actions_id`)
987 # --------------------------------------------------------
988 # New tables for patch creator
991 CREATE TABLE `myown_patches` (
992 `myown_patch_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
993 `atutor_patch_id` VARCHAR(20) NOT NULL default '',
994 `applied_version` VARCHAR(10) NOT NULL default '',
996 `sql_statement` TEXT,
997 `status` varchar(20) NOT NULL default '',
998 `last_modified` datetime NOT NULL,
999 PRIMARY KEY (`myown_patch_id`)
1002 CREATE TABLE `myown_patches_dependent` (
1003 `myown_patches_dependent_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
1004 `myown_patch_id` MEDIUMINT UNSIGNED NOT NULL,
1005 `dependent_patch_id` VARCHAR(50) NOT NULL default '',
1006 PRIMARY KEY (`myown_patches_dependent_id`)
1009 CREATE TABLE `myown_patches_files` (
1010 `myown_patches_files_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
1011 `myown_patch_id` MEDIUMINT UNSIGNED NOT NULL,
1012 `action` VARCHAR(20) NOT NULL default '',
1013 `name` VARCHAR(250) NOT NULL,
1014 `location` VARCHAR(250) NOT NULL default '',
1017 `uploaded_file` TEXT,
1018 PRIMARY KEY (`myown_patches_files_id`)
1021 # insert the default theme
1022 INSERT INTO `themes` VALUES ('ATutor', '2.0.2', 'default', 'Desktop', NOW(), 'This is the default ATutor theme and cannot be deleted as other themes inherit from it. Please do not alter this theme directly as it would complicate upgrading. Instead, create a new theme derived from this one.', 2);
1023 INSERT INTO `themes` VALUES ('Fluid', '2.0.2', 'fluid', 'Desktop', NOW(), 'Theme that implements the Fluid reorderer used to drag-and-drop the menu from side-to-side.', 1);
1024 INSERT INTO `themes` VALUES ('ATutor Classic', '2.0.2', 'default_classic', 'Desktop', NOW(), 'This is the ATutor Classic theme which makes use of the custom Header and logo images. To customize those images you must edit the <code>theme.cfg.php</code> in this themes directory.', 1);
1025 INSERT INTO `themes` VALUES ('Blumin', '2.0.2', 'blumin', 'Desktop', NOW(), 'This is the plone look-alike theme.', 1);
1026 INSERT INTO `themes` VALUES ('Greenmin', '2.0.2', 'greenmin', 'Desktop', NOW(), 'This is the plone look-alike theme in green.', 1);
1027 INSERT INTO `themes` VALUES ('ATutor 1.5', '2.0.2', 'default15', 'Desktop', NOW(), 'This is the 1.5 series default theme.', 1);
1028 INSERT INTO `themes` VALUES ('ATutor 1.6', '2.0.2', 'default16', 'Desktop', NOW(), 'This is the 1.6 series default theme.', 1);
1029 INSERT INTO `themes` VALUES ('IDI Theme', '2.0.2', 'idi', 'Desktop', '2010-12-02', 'The theme created for the IDI course server.', '1');
1030 INSERT INTO `themes` VALUES ('Mobile', '2.0.2', 'mobile', 'Mobile', NOW(), 'This is the default theme for mobile devices.', 3);
1032 # --------------------------------------------------------
1033 # Table structure for table `users_online`
1035 CREATE TABLE `users_online` (
1036 `member_id` mediumint(8) unsigned NOT NULL default '0',
1037 `course_id` mediumint(8) unsigned NOT NULL default '0',
1038 `login` varchar(255) NOT NULL default '',
1039 `expiry` int(10) unsigned NOT NULL default '0',
1040 PRIMARY KEY (`member_id`)
1041 ) ENGINE=HEAP MAX_ROWS=500;
1043 # --------------------------------------------------------
1044 # Table structure for table `auto_enroll`
1046 CREATE TABLE `auto_enroll` (
1047 `auto_enroll_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
1048 `associate_string` VARCHAR(10) NOT NULL,
1049 `name` VARCHAR( 50 ) NOT NULL default '',
1050 PRIMARY KEY ( `auto_enroll_id` )
1053 # --------------------------------------------------------
1054 # Table structure for table `auto_enroll_courses`
1056 CREATE TABLE `auto_enroll_courses` (
1057 `auto_enroll_courses_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
1058 `auto_enroll_id` MEDIUMINT UNSIGNED NOT NULL default 0,
1059 `course_id` MEDIUMINT UNSIGNED NOT NULL default 0,
1060 PRIMARY KEY ( `auto_enroll_courses_id` )
1063 # Setup Table for Access4All
1065 CREATE TABLE `primary_resources` (
1066 `primary_resource_id` mediumint(8) unsigned NOT NULL auto_increment,
1067 `content_id` mediumint(8) unsigned NOT NULL default '0',
1069 `language_code` varchar(20) default NULL,
1070 PRIMARY KEY (`primary_resource_id`)
1073 CREATE TABLE `primary_resources_types` (
1074 `primary_resource_id` mediumint(8) unsigned NOT NULL,
1075 `type_id` mediumint(8) unsigned NOT NULL,
1076 PRIMARY KEY (`primary_resource_id`,`type_id`)
1079 CREATE TABLE `resource_types` (
1080 `type_id` mediumint(8) unsigned NOT NULL auto_increment,
1082 PRIMARY KEY (`type_id`)
1085 CREATE TABLE `secondary_resources` (
1086 `secondary_resource_id` mediumint(8) unsigned NOT NULL auto_increment,
1087 `primary_resource_id` mediumint(8) unsigned NOT NULL,
1088 `secondary_resource` TEXT,
1089 `language_code` varchar(20) default NULL,
1090 PRIMARY KEY (`secondary_resource_id`)
1093 CREATE TABLE `secondary_resources_types` (
1094 `secondary_resource_id` mediumint(8) unsigned NOT NULL,
1095 `type_id` mediumint(8) unsigned NOT NULL,
1096 PRIMARY KEY (`secondary_resource_id`,`type_id`)
1099 INSERT INTO `resource_types` VALUES
1101 (2, 'sign_language'),
1105 INSERT INTO `config` (`name`, `value`) VALUES('encyclopedia', 'http://www.wikipedia.org');
1106 INSERT INTO `config` (`name`, `value`) VALUES('dictionary', 'http://dictionary.reference.com/');
1107 INSERT INTO `config` (`name`, `value`) VALUES('thesaurus', 'http://thesaurus.reference.com/');
1108 INSERT INTO `config` (`name`, `value`) VALUES('atlas', 'http://maps.google.ca/');
1109 INSERT INTO `config` (`name`, `value`) VALUES('calculator', 'http://www.calculateforfree.com/');
1110 INSERT INTO `config` (`name`, `value`) VALUES('note_taking', 'http://www.aypwip.org/webnote/');
1111 INSERT INTO `config` (`name`, `value`) VALUES('abacas', 'http://www.mandarintools.com/abacus.html');
1112 INSERT INTO `config` (`name`, `value`) VALUES('transformable_uri', 'http://localhost/transformable/');
1113 INSERT INTO `config` (`name`, `value`) VALUES('transformable_web_service_id', '90c3cd6f656739969847f3a99ac0f3c7');
1114 INSERT INTO `config` (`name`, `value`) VALUES('transformable_oauth_expire', '93600');
1116 # End Access4All setup
1118 # Tables for gradebook module
1120 CREATE TABLE `grade_scales` (
1121 `grade_scale_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
1122 `member_id` mediumint(8) unsigned NOT NULL default '0',
1123 `scale_name` VARCHAR(255) NOT NULL default '',
1124 `created_date` datetime NOT NULL default '0000-00-00 00:00:00',
1125 PRIMARY KEY ( `grade_scale_id` )
1128 CREATE TABLE `grade_scales_detail` (
1129 `grade_scale_id` mediumint(8) unsigned NOT NULL,
1130 `scale_value` VARCHAR(50) NOT NULL default '',
1131 `percentage_from` MEDIUMINT NOT NULL default '0',
1132 `percentage_to` MEDIUMINT NOT NULL default '0',
1133 PRIMARY KEY (`grade_scale_id`, `scale_value`)
1136 CREATE TABLE `gradebook_tests` (
1137 `gradebook_test_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
1138 `id` mediumint(8) unsigned NOT NULL default '0' COMMENT 'Values: 0, tests.test_id or assignments.assignment_id. 0 for external tests/assignments. tests.test_id for ATutor tests, assignments.assignment_id for ATutor assignments.',
1139 `type` VARCHAR(50) NOT NULL default '' COMMENT 'Values: ATutor Test, ATutor Assignment, External',
1140 `course_id` mediumint(8) unsigned NOT NULL default '0' COMMENT 'Values: 0 or courses.course_id. Only has value for external tests/assignments. When ATutor internal assignments/tests/surveys, always 0.',
1141 `title` VARCHAR(255) NOT NULL default '' COMMENT 'Values: Null or test name. Always null if ATutor internal assignments/tests/surveys.',
1142 `due_date` datetime NOT NULL default '0000-00-00 00:00:00',
1143 `grade_scale_id` mediumint(8) unsigned NOT NULL default '0',
1144 PRIMARY KEY ( `gradebook_test_id` )
1147 CREATE TABLE `gradebook_detail` (
1148 `gradebook_test_id` mediumint(8) unsigned NOT NULL,
1149 `member_id` mediumint(8) unsigned NOT NULL default '0',
1150 `grade` VARCHAR(255) NOT NULL default '',
1151 PRIMARY KEY (`gradebook_test_id`, `member_id`)
1154 INSERT INTO `grade_scales` (grade_scale_id, member_id, scale_name, created_date) values (1, 0, 'Letter Grade', now());
1155 INSERT INTO `grade_scales` (grade_scale_id, member_id, scale_name, created_date) values (2, 0, 'Competency 1', now());
1156 INSERT INTO `grade_scales` (grade_scale_id, member_id, scale_name, created_date) values (3, 0, 'Competency 2', now());
1158 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (1, 'A+', 90, 100);
1159 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (1, 'A', 80, 89);
1160 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (1, 'B', 70, 79);
1161 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (1, 'C', 60, 69);
1162 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (1, 'D', 50, 59);
1163 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (1, 'E', 0, 49);
1165 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (2, 'Pass', 75, 100);
1166 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (2, 'Fail', 0, 74);
1168 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (3, 'Excellent', 80, 100);
1169 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (3, 'Good', 70, 79);
1170 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (3, 'Adequate', 60, 69);
1171 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (3, 'Inadequate', 0, 59);
1175 # Tables for standalone student tools page
1177 CREATE TABLE `fha_student_tools` (
1178 `course_id` mediumint(8) unsigned NOT NULL,
1180 `home_view` tinyint NOT NULL DEFAULT 1,
1181 PRIMARY KEY ( `course_id` )
1184 # Tables for Social Networking module
1186 CREATE TABLE `social_activities` (
1187 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1188 `member_id` INTEGER UNSIGNED NOT NULL,
1189 `application_id` INTEGER UNSIGNED NOT NULL,
1191 `created_date` TIMESTAMP NOT NULL,
1195 # Applications/ Gagdets table
1196 CREATE TABLE `social_applications` (
1197 `id` INTEGER UNSIGNED,
1198 `url` VARCHAR(255) NOT NULL DEFAULT '',
1199 `title` VARCHAR(255) NOT NULL,
1200 `height` INTEGER UNSIGNED,
1201 `scrolling` INTEGER UNSIGNED,
1202 `screenshot` VARCHAR(255) NOT NULL,
1203 `thumbnail` VARCHAR(255) NOT NULL,
1204 `author` VARCHAR(255) NOT NULL,
1205 `author_email` VARCHAR(128) NOT NULL,
1209 `last_updated` TIMESTAMP NOT NULL,
1213 # Application Settings, like storing the perference string.
1214 CREATE TABLE `social_application_settings` (
1215 `application_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1216 `member_id` INTEGER UNSIGNED NOT NULL,
1217 `name` VARCHAR(255) NOT NULL,
1219 PRIMARY KEY (`application_id`, `member_id`, `name`)
1222 # Application members mapping
1223 CREATE TABLE `social_members_applications` (
1224 `member_id` INTEGER UNSIGNED NOT NULL,
1225 `application_id` INTEGER UNSIGNED NOT NULL,
1226 PRIMARY KEY (`member_id`, `application_id`)
1230 CREATE TABLE `social_friends` (
1231 `member_id` INTEGER UNSIGNED NOT NULL,
1232 `friend_id` INTEGER UNSIGNED NOT NULL,
1233 `relationship` INTEGER UNSIGNED NOT NULL,
1234 PRIMARY KEY (`member_id`, `friend_id`)
1237 # Friend requests table
1238 CREATE TABLE `social_friend_requests` (
1239 `member_id` INTEGER UNSIGNED NOT NULL,
1240 `friend_id` INTEGER UNSIGNED NOT NULL,
1241 `relationship` INTEGER UNSIGNED NOT NULL,
1242 PRIMARY KEY (`member_id`, `friend_id`)
1245 # Person Positions (jobs)
1246 CREATE TABLE `social_member_position` (
1247 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1248 `member_id` INTEGER UNSIGNED NOT NULL,
1249 `title` VARCHAR(255) NOT NULL,
1250 `company` VARCHAR(255) NOT NULL,
1251 `from` VARCHAR(10) NOT NULL DEFAULT 0,
1252 `to` VARCHAR(10) NOT NULL DEFAULT 0,
1258 CREATE TABLE `social_member_education` (
1259 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1260 `member_id` INTEGER UNSIGNED NOT NULL,
1261 `university` VARCHAR(255) NOT NULL,
1262 `country` VARCHAR(128),
1263 `province` VARCHAR(128),
1264 `degree` VARCHAR(64),
1265 `field` VARCHAR(64),
1266 `from` VARCHAR(10) NOT NULL DEFAULT 0,
1267 `to` VARCHAR(10) NOT NULL DEFAULT 0,
1272 # Person related web sites
1273 CREATE TABLE `social_member_websites` (
1274 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1275 `member_id` INTEGER UNSIGNED NOT NULL,
1276 `url` VARCHAR(255) NOT NULL,
1277 `site_name` VARCHAR(255),
1281 # Tracks visitor counts
1282 CREATE TABLE `social_member_track` (
1283 `member_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1284 `visitor_id` INTEGER UNSIGNED NOT NULL,
1285 `timestamp` TIMESTAMP NOT NULL,
1286 PRIMARY KEY (`member_id`, `visitor_id`, `timestamp`)
1289 # Person additional information cojoint with the members table
1290 CREATE TABLE `social_member_additional_information` (
1291 `member_id` INTEGER UNSIGNED NOT NULL,
1292 `expertise` VARCHAR(255) NOT NULL,
1294 `associations` TEXT,
1297 PRIMARY KEY (`member_id`)
1301 CREATE TABLE `social_member_contact` (
1302 `contact_id` int(10) unsigned NOT NULL auto_increment,
1303 `member_id` int(10) unsigned NOT NULL,
1304 `con_name` varchar(200) NOT NULL,
1305 `con_phone` varchar(15) NOT NULL,
1306 `con_email` varchar(50) NOT NULL,
1308 PRIMARY KEY (`contact_id`)
1311 CREATE TABLE `social_member_representation` (
1312 `rep_id` int(10) unsigned NOT NULL auto_increment,
1313 `member_id` int(10) unsigned NOT NULL,
1314 `rep_name` varchar(200) NOT NULL,
1315 `rep_title` varchar(50) NOT NULL,
1316 `rep_phone` varchar(15) NOT NULL,
1317 `rep_email` varchar(50) NOT NULL,
1319 PRIMARY KEY (`rep_id`)
1322 CREATE TABLE `social_member_personal` (
1323 `per_id` int(10) unsigned NOT NULL auto_increment,
1324 `member_id` int(10) unsigned NOT NULL,
1325 `per_weight` varchar(200) NOT NULL,
1326 `per_height` varchar(50) NOT NULL,
1327 `per_hair` varchar(15) NOT NULL,
1328 `per_eyes` varchar(50) NOT NULL,
1329 `per_ethnicity` varchar(50) NOT NULL,
1330 `per_languages` varchar(255) NOT NULL,
1331 `per_disabilities` varchar(255) NOT NULL,
1332 PRIMARY KEY (`per_id`)
1335 # Privacy Control Preferences
1336 CREATE TABLE `social_privacy_preferences` (
1337 `member_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1339 PRIMARY KEY (`member_id`)
1342 # Social Group tables
1343 CREATE TABLE `social_groups` (
1344 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1345 `member_id` INTEGER UNSIGNED NOT NULL,
1346 `type_id` INTEGER UNSIGNED NOT NULL,
1347 `privacy` INTEGER UNSIGNED NOT NULL,
1348 `name` VARCHAR(255) NOT NULL,
1349 `logo` VARCHAR(255) NOT NULL,
1351 `created_date` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
1352 `last_updated` TIMESTAMP NOT NULL,
1356 CREATE TABLE `social_groups_activities` (
1357 `activity_id` INTEGER UNSIGNED NOT NULL,
1358 `group_id` INTEGER UNSIGNED NOT NULL,
1359 PRIMARY KEY (`activity_id`, `group_id`)
1362 CREATE TABLE `social_groups_members` (
1363 `group_id` INTEGER UNSIGNED NOT NULL,
1364 `member_id` INTEGER UNSIGNED NOT NULL,
1365 PRIMARY KEY (`group_id`, `member_id`)
1368 CREATE TABLE `social_groups_invitations` (
1369 `sender_id` INTEGER UNSIGNED NOT NULL,
1370 `member_id` INTEGER UNSIGNED NOT NULL,
1371 `group_id` INTEGER UNSIGNED NOT NULL,
1372 PRIMARY KEY (`sender_id`, `member_id`, `group_id`)
1375 CREATE TABLE `social_groups_requests` (
1376 `sender_id` INTEGER UNSIGNED NOT NULL,
1377 `member_id` INTEGER UNSIGNED NOT NULL,
1378 `group_id` INTEGER UNSIGNED NOT NULL,
1379 PRIMARY KEY (`sender_id`, `member_id`, `group_id`)
1382 CREATE TABLE `social_groups_types` (
1383 `type_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1384 `title` VARCHAR(127) NOT NULL,
1385 PRIMARY KEY (`type_id`)
1388 # CREATE TABLE `social_groups_forums` (
1389 # `group_id` INTEGER UNSIGNED NOT NULL,
1390 # `forum_id` INTEGER UNSIGNED NOT NULL,
1391 # PRIMARY KEY (`group_id`, `forum_id`)
1392 # ) ENGINE = MyISAM;
1394 # Groups message board
1395 CREATE TABLE `social_groups_board` (
1396 `id` int(10) unsigned NOT NULL auto_increment,
1397 `member_id` int(10) unsigned NOT NULL,
1398 `group_id` int(10) unsigned NOT NULL,
1400 `created_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1405 CREATE TABLE `social_user_settings` (
1406 `member_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1407 `app_settings` TEXT,
1408 PRIMARY KEY (`member_id`)
1411 #====== Initial Data ========
1412 INSERT INTO social_groups_types SET title='business', type_id=1;
1413 INSERT INTO social_groups_types SET title='common_interest', type_id=2;
1414 INSERT INTO social_groups_types SET title='entertainment_arts', type_id=3;
1415 INSERT INTO social_groups_types SET title='geography', type_id=4;
1416 INSERT INTO social_groups_types SET title='internet_technology', type_id=5;
1417 INSERT INTO social_groups_types SET title='organization', type_id=6;
1418 INSERT INTO social_groups_types SET title='music', type_id=7;
1419 INSERT INTO social_groups_types SET title='sports_recreation', type_id=8;
1421 # END Social Networking setup
1423 # Login attempt control table
1424 CREATE TABLE `member_login_attempt` (
1425 `login` varchar(20) NOT NULL,
1426 `attempt` tinyint(3) unsigned default NULL,
1427 `expiry` int(10) unsigned default NULL,
1428 PRIMARY KEY (`login`)
1431 # --------------------------------------------------------
1432 # Adding feature of blog subsription
1433 # Table structure for table `blog_subscription`
1435 CREATE TABLE `blog_subscription` (
1436 `group_id` MEDIUMINT NOT NULL ,
1437 `member_id` MEDIUMINT NOT NULL ,
1438 PRIMARY KEY (group_id,member_id)
1441 # END Adding feature of blog subsription
1443 # --------------------------------------------------------
1444 # Adding feature of content pre-requisites
1445 # Table structure for table `content_prerequisites`
1447 CREATE TABLE `content_prerequisites` (
1448 `content_id` MEDIUMINT NOT NULL,
1449 `type` varchar(50) NOT NULL DEFAULT '',
1450 `item_id` MEDIUMINT NOT NULL,
1451 PRIMARY KEY (content_id,type, item_id)
1454 # END Adding feature of content pre-requisites
1456 # --------------------------------------------------------
1457 # Adding feature of oauth client
1458 # Table structure for table `oauth_client_servers`
1461 CREATE TABLE `oauth_client_servers` (
1462 `oauth_server_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
1463 `oauth_server` VARCHAR(255) NOT NULL default '',
1464 `consumer_key` TEXT,
1465 `consumer_secret` TEXT,
1466 `expire_threshold` INT NOT NULL default 0,
1467 `create_date` datetime NOT NULL,
1468 PRIMARY KEY ( `oauth_server_id` ),
1469 UNIQUE INDEX idx_consumer ( `oauth_server` )
1472 # --------------------------------------------------------
1473 # Table structure for table `oauth_client_tokens`
1476 CREATE TABLE `oauth_client_tokens` (
1477 `oauth_server_id` MEDIUMINT UNSIGNED NOT NULL,
1478 `token` VARCHAR(50) NOT NULL default '',
1479 `token_type` VARCHAR(50) NOT NULL NOT NULL default '',
1480 `token_secret` TEXT,
1481 `member_id` mediumint(8) unsigned NOT NULL ,
1482 `assign_date` datetime NOT NULL,
1483 PRIMARY KEY ( `oauth_server_id`, `token` )
1486 # END Adding feature of oauth client
1488 # -------------- Photo Album Module Setup ----------------
1491 CREATE TABLE `pa_albums` (
1492 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1493 `name` VARCHAR(255) NOT NULL,
1494 `location` VARCHAR(255) NOT NULL,
1496 `permission` TINYINT(1) UNSIGNED NOT NULL,
1497 `member_id` INTEGER UNSIGNED NOT NULL,
1498 `photo_id` INTEGER UNSIGNED NOT NULL,
1499 `type_id` TINYINT(1) UNSIGNED NOT NULL,
1500 `created_date` DATETIME NOT NULL,
1501 `last_updated` DATETIME NOT NULL,
1506 CREATE TABLE `pa_photos` (
1507 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1508 `name` VARCHAR(255) NOT NULL,
1511 `member_id` INTEGER UNSIGNED NOT NULL,
1512 `album_id` INTEGER UNSIGNED NOT NULL,
1513 `ordering` SMALLINT UNSIGNED NOT NULL,
1514 `created_date` DATETIME NOT NULL,
1515 `last_updated` DATETIME NOT NULL,
1519 # Course Album Table
1520 CREATE TABLE `pa_course_album` (
1521 `course_id` INTEGER UNSIGNED,
1522 `album_id` INTEGER UNSIGNED,
1523 PRIMARY KEY (`course_id`, `album_id`)
1526 # Photo Album Comments
1527 CREATE TABLE `pa_album_comments` (
1528 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1529 `album_id` INTEGER UNSIGNED NOT NULL,
1530 `member_id` INTEGER UNSIGNED NOT NULL,
1532 `created_date` DATETIME NOT NULL,
1537 CREATE TABLE `pa_photo_comments` (
1538 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1539 `photo_id` INTEGER UNSIGNED NOT NULL,
1540 `member_id` INTEGER UNSIGNED NOT NULL,
1542 `created_date` DATETIME NOT NULL,
1546 # A mapping table between photo album and atutor groups
1547 #######################
1548 # This table is not currently being used, to be implemented later
1549 #######################
1550 CREATE TABLE `pa_groups` (
1551 `group_id` INTEGER UNSIGNED NOT NULL,
1552 `album_id` INTEGER UNSIGNED NOT NULL,
1553 PRIMARY KEY (`group_id`, `album_id`)
1558 INSERT INTO `config` VALUES ('pa_max_memory_per_member', '50');
1560 # -------------- Photo Album Module Ends -----------------
1562 # -------------- External Tools/BasicLTI Starts -----------------
1563 CREATE TABLE `basiclti_tools` (
1564 `id` mediumint(10) NOT NULL AUTO_INCREMENT,
1565 `toolid` varchar(32) NOT NULL,
1566 `course_id` mediumint(10) NOT NULL DEFAULT '0',
1567 `title` varchar(255) NOT NULL,
1568 `description` varchar(1024),
1569 `timecreated` TIMESTAMP,
1570 `timemodified` TIMESTAMP,
1571 `toolurl` varchar(1023) NOT NULL,
1572 `resourcekey` varchar(1023) NOT NULL,
1573 `password` varchar(1023) NOT NULL,
1574 `preferheight` mediumint(4) NOT NULL DEFAULT '0',
1575 `allowpreferheight` mediumint(1) NOT NULL DEFAULT '0',
1576 `sendname` mediumint(1) NOT NULL DEFAULT '0',
1577 `sendemailaddr` mediumint(1) NOT NULL DEFAULT '0',
1578 `acceptgrades` mediumint(1) NOT NULL DEFAULT '0',
1579 `allowroster` mediumint(1) NOT NULL DEFAULT '0',
1580 `allowsetting` mediumint(1) NOT NULL DEFAULT '0',
1581 `allowcustomparameters` mediumint(1) NOT NULL DEFAULT '0',
1582 `customparameters` text,
1583 `organizationid` varchar(64),
1584 `organizationurl` varchar(255),
1585 `organizationdescr` varchar(255),
1586 `launchinpopup` mediumint(1) NOT NULL DEFAULT '0',
1587 `debuglaunch` mediumint(1) NOT NULL DEFAULT '0',
1588 PRIMARY KEY ( `id`, `toolid` )
1591 CREATE TABLE `basiclti_content` (
1592 `id` mediumint(10) NOT NULL AUTO_INCREMENT,
1593 `content_id` mediumint(10) NOT NULL DEFAULT '0',
1594 `course_id` mediumint(10) NOT NULL DEFAULT '0',
1595 `toolid` varchar(32) NOT NULL DEFAULT '',
1596 `preferheight` mediumint(4) NOT NULL DEFAULT '0',
1597 `sendname` mediumint(1) NOT NULL DEFAULT '0',
1598 `sendemailaddr` mediumint(1) NOT NULL DEFAULT '0',
1599 `gradebook_test_id` mediumint(10) NOT NULL DEFAULT '0',
1600 `allowroster` mediumint(1) NOT NULL DEFAULT '0',
1601 `allowsetting` mediumint(1) NOT NULL DEFAULT '0',
1602 `customparameters` text,
1603 `launchinpopup` mediumint(1) NOT NULL DEFAULT '0',
1604 `debuglaunch` mediumint(1) NOT NULL DEFAULT '0',
1605 `placementsecret` varchar(1023),
1606 `timeplacementsecret` mediumint(10) NOT NULL DEFAULT '0',
1607 `oldplacementsecret` varchar(1023),
1608 `setting` text(8192),
1609 `xmlimport` text(16384),
1610 PRIMARY KEY ( `id`, `course_id`, `content_id` )
1612 # -------------- External Tools/BasicLTI Ends -----------------