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',
32 # --------------------------------------------------------
33 # Table structure for table `assignments`
36 CREATE TABLE `assignments` (
37 `assignment_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
38 `course_id` MEDIUMINT UNSIGNED NOT NULL default 0,
39 `title` VARCHAR(240) NOT NULL default '',
40 `assign_to` MEDIUMINT UNSIGNED default 0,
41 `date_due` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
42 `date_cutoff` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
43 `multi_submit` TINYINT DEFAULT '0',
44 PRIMARY KEY (`assignment_id`),
48 # --------------------------------------------------------
49 # Table structure for table `backups`
52 CREATE TABLE `backups` (
53 `backup_id` mediumint(8) unsigned NOT NULL auto_increment,
54 `course_id` mediumint(8) unsigned NOT NULL default '0',
55 `date` TIMESTAMP NOT NULL,
57 `file_size` int(10) unsigned NOT NULL default 0,
58 `system_file_name` varchar(50) NOT NULL default '',
61 PRIMARY KEY (`backup_id`),
62 KEY `course_id` (`course_id`)
66 # --------------------------------------------------------
67 ## Table for `blog_posts`
69 CREATE TABLE `blog_posts` (
70 `post_id` mediumint(8) unsigned NOT NULL auto_increment,
71 `member_id` mediumint(8) unsigned NOT NULL default '0',
72 `owner_type` tinyint(3) unsigned NOT NULL default '0',
73 `owner_id` mediumint(8) unsigned NOT NULL default '0',
74 `private` tinyint(3) unsigned NOT NULL default '0',
75 `date` TIMESTAMP NOT NULL,
76 `num_comments` tinyint(3) unsigned NOT NULL default '0',
77 `title` VARCHAR(255) NOT NULL,
79 PRIMARY KEY (`post_id`)
83 # --------------------------------------------------------
84 ## Table for `blog_posts_comments`
86 CREATE TABLE `blog_posts_comments` (
87 `comment_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,
88 `post_id` MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL ,
89 `member_id` MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL ,
90 `date` TIMESTAMP NOT NULL,
91 `private` TINYINT UNSIGNED DEFAULT '0' NOT NULL ,
93 PRIMARY KEY ( `comment_id` ) ,
97 # --------------------------------------------------------
98 # Table structure for table `config`
101 CREATE TABLE `config` (
102 `name` CHAR( 30 ) NOT NULL default '',
104 PRIMARY KEY ( `name` )
107 # --------------------------------------------------------
108 # Table structure for table `content`
110 CREATE TABLE `content` (
111 `content_id` mediumint(8) unsigned NOT NULL auto_increment,
112 `course_id` mediumint(8) unsigned NOT NULL default '0',
113 `content_parent_id` mediumint(8) unsigned NOT NULL default '0',
114 `ordering` mediumint(8) NOT NULL default '0',
115 `last_modified` TIMESTAMP NOT NULL,
116 `revision` tinyint(3) unsigned NOT NULL default '0',
117 `formatting` tinyint(4) NOT NULL default '0',
118 `release_date` datetime NOT NULL default '0000-00-00 00:00:00',
120 `content_path` TEXT ,
121 `title` VARCHAR(255) NOT NULL ,
124 `use_customized_head` TINYINT(4) NOT NULL,
126 `allow_test_export` TINYINT(1) UNSIGNED NOT NULL,
127 `content_type` TINYINT(1) UNSIGNED NOT NULL,
128 PRIMARY KEY (`content_id`),
129 KEY `course_id` (`course_id`)
132 # --------------------------------------------------------
133 # Table structure for table `course_access`
135 CREATE TABLE `course_access` (
136 `password` char(8) NOT NULL ,
137 `course_id` mediumint(8) unsigned NOT NULL ,
138 `expiry_date` timestamp NOT NULL ,
139 `enabled` tinyint(4) NOT NULL ,
140 PRIMARY KEY ( `password` ) ,
144 # --------------------------------------------------------
145 # Table structure for table `course_cats`
147 CREATE TABLE `course_cats` (
148 `cat_id` mediumint(8) unsigned NOT NULL auto_increment,
149 `cat_name` VARCHAR(255) NOT NULL ,
150 `cat_parent` mediumint(8) unsigned NOT NULL default '0',
151 `theme` VARCHAR(30) NOT NULL default '',
152 PRIMARY KEY (`cat_id`)
155 # --------------------------------------------------------
156 # Table structure for table `course_tests_assoc`
158 CREATE TABLE `content_tests_assoc` (
159 `content_id` INTEGER UNSIGNED NOT NULL,
160 `test_id` INTEGER UNSIGNED NOT NULL,
161 PRIMARY KEY (`content_id`, `test_id`)
166 # --------------------------------------------------------
167 # Table structure for table `content_forums_assoc`
169 CREATE TABLE `content_forums_assoc` (
170 `content_id` INTEGER UNSIGNED NOT NULL,
171 `forum_id` INTEGER UNSIGNED NOT NULL,
172 PRIMARY KEY ( `content_id` , `forum_id` )
176 # --------------------------------------------------------
177 # Table structure for table `course_enrollment`
179 CREATE TABLE `course_enrollment` (
180 `member_id` mediumint(8) unsigned NOT NULL default '0',
181 `course_id` mediumint(8) unsigned NOT NULL default '0',
182 `approved` enum('y','n','a') NOT NULL default 'n',
183 `privileges` INT(10) unsigned NOT NULL default '0',
184 `role` varchar(35) NOT NULL default '',
185 `last_cid` mediumint(8) unsigned NOT NULL default '0',
186 PRIMARY KEY (`member_id`,`course_id`)
191 # --------------------------------------------------------
192 # Table structure for table `course_stats`
194 CREATE TABLE `course_stats` (
195 `course_id` mediumint(8) unsigned NOT NULL default '0',
196 `login_date` date NOT NULL default '0000-00-00',
197 `guests` mediumint(8) unsigned NOT NULL default '0',
198 `members` mediumint(8) unsigned NOT NULL default '0',
199 PRIMARY KEY (`course_id`,`login_date`)
202 # --------------------------------------------------------
203 # Table structure for table `courses`
205 CREATE TABLE `courses` (
206 `course_id` mediumint(8) unsigned NOT NULL auto_increment,
207 `member_id` mediumint(8) unsigned NOT NULL default '0',
208 `cat_id` mediumint(8) unsigned NOT NULL default '0',
209 `content_packaging` enum('none','top','all') NOT NULL default 'top',
210 `access` enum('public','protected','private') NOT NULL default 'public',
211 `created_date` datetime NOT NULL default '0000-00-00 00:00:00',
212 `title` VARCHAR(255) NOT NULL ,
214 `course_dir_name` VARCHAR(255) NOT NULL,
215 `notify` tinyint(4) NOT NULL default '0',
216 `max_quota` varchar(30) NOT NULL default '',
217 `max_file_size` varchar(30) NOT NULL default '',
218 `hide` tinyint(4) NOT NULL default '0',
220 `primary_language` varchar(5) NOT NULL default '',
221 `rss` tinyint NOT NULL default 0,
222 `icon` varchar(75) NOT NULL default '',
225 `side_menu` VARCHAR( 255 ) NOT NULL default '',
226 `release_date` datetime NOT NULL default '0000-00-00 00:00:00',
227 `end_date` datetime NOT NULL default '0000-00-00 00:00:00',
229 `home_view` tinyint NOT NULL DEFAULT 1,
230 PRIMARY KEY (`course_id`)
233 # --------------------------------------------------------
234 # Table structure for table `faq_topics`
236 CREATE TABLE `faq_topics` (
237 `topic_id` mediumint(8) NOT NULL auto_increment,
238 `course_id` mediumint(8) unsigned NOT NULL default '0',
240 KEY `course_id` (`course_id`),
241 PRIMARY KEY (`topic_id`)
244 # --------------------------------------------------------
245 # Table structure for table `faq_entries`
246 CREATE TABLE `faq_entries` (
247 `entry_id` mediumint(8) NOT NULL auto_increment,
248 `topic_id` mediumint(8) NOT NULL default '0',
249 `revised_date` TIMESTAMP NOT NULL,
250 `approved` tinyint(4) NOT NULL default '0',
253 PRIMARY KEY (`entry_id`)
256 # --------------------------------------------------------
257 # Table structure for table `feeds`
258 CREATE TABLE `feeds` (
259 `feed_id` mediumint(8) unsigned NOT NULL auto_increment,
260 `url` varchar(255) NOT NULL default '',
261 PRIMARY KEY (`feed_id`)
264 # --------------------------------------------------------
267 # Table structure for table `file_storage_groups`
270 CREATE TABLE `file_storage_groups` (
271 `group_id` MEDIUMINT UNSIGNED NOT NULL default 0,
272 PRIMARY KEY ( `group_id` )
276 # Table structure for table `files`
279 CREATE TABLE `files` (
280 `file_id` mediumint(8) unsigned NOT NULL auto_increment,
281 `owner_type` tinyint(3) unsigned NOT NULL default '0',
282 `owner_id` mediumint(8) unsigned NOT NULL default '0',
283 `member_id` mediumint(8) unsigned NOT NULL default '0',
284 `folder_id` mediumint(8) unsigned NOT NULL default '0',
285 `parent_file_id` mediumint(8) unsigned NOT NULL default '0',
286 `date` TIMESTAMP NOT NULL,
287 `num_comments` tinyint(3) unsigned NOT NULL default '0',
288 `num_revisions` tinyint(3) unsigned NOT NULL default '0',
289 `file_name` varchar(80) NOT NULL default '',
290 `file_size` int(11) NOT NULL default '0',
292 PRIMARY KEY (`file_id`)
295 # --------------------------------------------------------
298 # Table structure for table `files_comments`
301 CREATE TABLE `files_comments` (
302 `comment_id` mediumint(8) unsigned NOT NULL auto_increment,
303 `file_id` mediumint(8) unsigned NOT NULL default '0',
304 `member_id` mediumint(8) unsigned NOT NULL default '0',
305 `date` TIMESTAMP NOT NULL,
307 PRIMARY KEY (`comment_id`)
310 # --------------------------------------------------------
313 # Table structure for table `folders`
316 CREATE TABLE `folders` (
317 `folder_id` mediumint(8) unsigned NOT NULL auto_increment,
318 `parent_folder_id` mediumint(8) unsigned NOT NULL default '0',
319 `owner_type` tinyint(3) unsigned NOT NULL default '0',
320 `owner_id` mediumint(8) unsigned NOT NULL default '0',
321 `title` varchar(120) NOT NULL default '',
322 PRIMARY KEY (`folder_id`)
326 # --------------------------------------------------------
327 # Table structure for table `forums`
329 CREATE TABLE `forums` (
330 `forum_id` mediumint(8) unsigned NOT NULL auto_increment,
331 `title` varchar(240) NOT NULL default '',
333 `num_topics` MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL ,
334 `num_posts` MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL ,
335 `last_post` TIMESTAMP NOT NULL,
336 `mins_to_edit` SMALLINT UNSIGNED NOT NULL DEFAULT '0',
337 PRIMARY KEY (`forum_id`)
341 # --------------------------------------------------------
342 # Table structure for table `forums_accessed`
344 CREATE TABLE `forums_accessed` (
345 `post_id` mediumint(8) unsigned NOT NULL default '0',
346 `member_id` mediumint(8) unsigned NOT NULL default '0',
347 `last_accessed` timestamp NOT NULL,
348 `subscribe` tinyint(4) NOT NULL default '0',
349 PRIMARY KEY (`post_id`,`member_id`)
352 # --------------------------------------------------------
353 # Table structure for table `forums_courses`
355 CREATE TABLE `forums_courses` (
356 `forum_id` MEDIUMINT UNSIGNED NOT NULL default '0',
357 `course_id` MEDIUMINT UNSIGNED NOT NULL default '0',
358 PRIMARY KEY (`forum_id`,`course_id`),
359 KEY `course_id` (`course_id`)
363 # --------------------------------------------------------
364 # Table structure for table `forums_groups`
366 CREATE TABLE `forums_groups` (
367 `forum_id` mediumint( 8 ) unsigned NOT NULL default '0',
368 `group_id` mediumint( 8 ) unsigned NOT NULL default '0',
369 PRIMARY KEY ( `forum_id` , `group_id` ) ,
370 KEY `group_id` ( `group_id` )
373 # --------------------------------------------------------
374 # Table structure for table `forums_subscriptions`
377 CREATE TABLE `forums_subscriptions` (
378 forum_id mediumint(8) unsigned NOT NULL default '0',
379 member_id mediumint(8) unsigned NOT NULL default '0',
380 PRIMARY KEY (`forum_id`,`member_id`)
384 # --------------------------------------------------------
385 # Table structure for table `forums_threads`
387 CREATE TABLE `forums_threads` (
388 `post_id` mediumint(8) unsigned NOT NULL auto_increment,
389 `parent_id` mediumint(8) unsigned NOT NULL default '0',
390 `member_id` mediumint(8) unsigned NOT NULL default '0',
391 `forum_id` mediumint(8) unsigned NOT NULL default '0',
392 `last_comment` TIMESTAMP NOT NULL,
393 `num_comments` mediumint(8) unsigned NOT NULL default '0',
394 `subject` VARCHAR(255) NOT NULL ,
396 `date` TIMESTAMP NOT NULL,
397 `locked` tinyint(4) NOT NULL default '0',
398 `sticky` tinyint(4) NOT NULL default '0',
399 PRIMARY KEY (`post_id`)
402 # --------------------------------------------------------
403 # Table structure for table `glossary`
405 CREATE TABLE `glossary` (
406 `word_id` mediumint(8) unsigned NOT NULL auto_increment,
407 `course_id` mediumint(8) unsigned NOT NULL default '0',
408 `word` varchar(240) NOT NULL default '',
410 `related_word_id` mediumint(8) unsigned NOT NULL default '0',
411 PRIMARY KEY (`word_id`),
412 KEY `course_id` (`course_id`)
415 # --------------------------------------------------------
416 # Table structure for table `groups`
418 CREATE TABLE `groups` (
419 `group_id` mediumint(8) unsigned NOT NULL auto_increment,
420 `type_id` mediumint(8) unsigned NOT NULL default '0',
421 `title` varchar(80) NOT NULL default '',
423 `modules` varchar(255) NOT NULL default '',
424 PRIMARY KEY (`group_id`)
428 # --------------------------------------------------------
429 # Table structure for table `groups_members`
431 CREATE TABLE `groups_members` (
432 `group_id` MEDIUMINT UNSIGNED NOT NULL default '0',
433 `member_id` MEDIUMINT UNSIGNED NOT NULL default '0',
434 PRIMARY KEY (`group_id`,`member_id`)
437 # --------------------------------------------------------
438 # Table structure for table `groups_types` (since 1.5.3)
440 CREATE TABLE `groups_types` (
441 `type_id` mediumint(8) unsigned NOT NULL auto_increment,
442 `course_id` mediumint(8) unsigned NOT NULL default '0',
443 `title` VARCHAR(80) NOT NULL ,
444 PRIMARY KEY (`type_id`),
445 KEY `course_id` (`course_id`)
448 # --------------------------------------------------------
449 # Table structure for table `guests` (since 1.6.2)
450 CREATE TABLE `guests` (
451 `guest_id` VARCHAR(10) NOT NULL,
453 `organization` VARCHAR(255),
454 `location` VARCHAR(255),
456 `focus` VARCHAR(255),
457 PRIMARY KEY (`guest_id`)
460 # --------------------------------------------------------
461 # Table structure for table `handbook_notes`
463 CREATE TABLE `handbook_notes` (
464 `note_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,
465 `date` TIMESTAMP NOT NULL,
466 `section` VARCHAR( 15 ) NOT NULL default '',
467 `page` VARCHAR( 50 ) NOT NULL default '',
468 `approved` tinyint NOT NULL default 0,
469 `email` VARCHAR( 50 ) NOT NULL default '',
471 PRIMARY KEY ( `note_id` )
475 # --------------------------------------------------------
476 # Table structure for table `instructor_approvals`
478 CREATE TABLE `instructor_approvals` (
479 `member_id` mediumint(8) unsigned NOT NULL default '0',
480 `request_date` TIMESTAMP NOT NULL,
482 PRIMARY KEY (`member_id`)
486 CREATE TABLE `languages` (
487 `language_code` varchar(20) NOT NULL default '',
488 `char_set` varchar(80) NOT NULL default '',
489 `direction` varchar(16) NOT NULL default '',
490 `reg_exp` varchar(124) NOT NULL default '',
491 `native_name` varchar(80) NOT NULL default '',
492 `english_name` varchar(80) NOT NULL default '',
493 `status` TINYINT UNSIGNED DEFAULT '0' NOT NULL,
494 PRIMARY KEY (`language_code`,`char_set`)
498 # Dumping data for table `languages`
501 INSERT INTO `languages` VALUES ('en', 'utf-8', 'ltr', 'en([-_][[:alpha:]]{2})?|english', 'English', 'English', 3);
504 # Table structure for table `links_categories`
506 CREATE TABLE `links_categories` (
507 `cat_id` mediumint(8) unsigned NOT NULL auto_increment,
508 `owner_type` tinyint(4) NOT NULL default '0',
509 `owner_id` mediumint(8) unsigned NOT NULL default '0',
510 `name` VARCHAR(255) NOT NULL ,
511 `parent_id` mediumint(8) unsigned default NULL,
512 PRIMARY KEY (`cat_id`),
513 KEY `owner_id` (`owner_id`)
517 # --------------------------------------------------------
518 # Table structure for table `links`
520 CREATE TABLE `links` (
521 `link_id` mediumint(8) unsigned NOT NULL auto_increment,
522 `cat_id` mediumint(8) unsigned NOT NULL default '0',
523 `Url` varchar(255) NOT NULL default '',
524 `LinkName` varchar(64) NOT NULL default '',
526 `Approved` tinyint(8) default '0',
527 `SubmitName` varchar(64) NOT NULL default '',
528 `SubmitEmail` varchar(64) NOT NULL default '',
529 `SubmitDate` date NOT NULL default '0000-00-00',
530 `hits` int(11) default '0',
531 PRIMARY KEY (`link_id`)
534 # --------------------------------------------------------
535 # Table structure for table `language_pages`
537 CREATE TABLE `language_pages` (
538 `term` varchar(30) NOT NULL default '',
539 `page` varchar(50) NOT NULL default '',
540 PRIMARY KEY (`term`,`page`)
543 # --------------------------------------------------------
544 # Table structure for table `master_list`
546 CREATE TABLE `master_list` (
547 `public_field` CHAR( 30 ) NOT NULL default '',
548 `hash_field` CHAR( 40 ) NOT NULL default '',
549 `member_id` MEDIUMINT UNSIGNED NOT NULL default 0,
550 PRIMARY KEY ( `public_field` )
553 # --------------------------------------------------------
554 # Table structure for table `members`
556 CREATE TABLE `members` (
557 `member_id` mediumint(8) unsigned NOT NULL auto_increment,
558 `login` varchar(20) NOT NULL default '',
559 `password` varchar(40) NOT NULL default '',
560 `email` varchar(50) NOT NULL default '',
561 `website` varchar(200) NOT NULL default '',
562 `first_name` VARCHAR(100) NOT NULL ,
563 `second_name` varchar(100) NOT NULL default '',
564 `last_name` VARCHAR(100) NOT NULL ,
565 `dob` date NOT NULL default '0000-00-00',
566 `gender` enum('m','f','n') NOT NULL default 'n',
568 `postal` varchar(15) NOT NULL default '',
569 `city` varchar(100) NOT NULL default '',
570 `province` varchar(100) NOT NULL default '',
571 `country` varchar(100) NOT NULL default '',
572 `phone` varchar(15) NOT NULL default '',
573 `status` tinyint(4) NOT NULL default '0',
575 `creation_date` TIMESTAMP NOT NULL,
576 `language` varchar(5) NOT NULL default '',
577 `inbox_notify` tinyint(3) unsigned NOT NULL default '0',
578 `private_email` TINYINT DEFAULT '1' NOT NULL,
579 `last_login` TIMESTAMP NOT NULL,
580 PRIMARY KEY (`member_id`),
581 UNIQUE KEY `login` (`login`)
584 # --------------------------------------------------------
585 # Table structure for table `member_track`
587 CREATE TABLE `member_track` (
588 `member_id` mediumint(8) unsigned NOT NULL default '0',
589 `course_id` mediumint(8) unsigned NOT NULL default '0',
590 `content_id` mediumint(8) unsigned NOT NULL default '0',
591 `counter` mediumint(8) unsigned NOT NULL default '0',
592 `duration` mediumint(8) unsigned NOT NULL default '0',
593 `last_accessed` TIMESTAMP NULL,
594 KEY `member_id` (`member_id`),
595 KEY `content_id` (`content_id`)
598 # --------------------------------------------------------
599 # Table structure for table `messages`
601 CREATE TABLE `messages` (
602 `message_id` mediumint(8) unsigned NOT NULL auto_increment,
603 `course_id` mediumint(8) unsigned NOT NULL default '0',
604 `from_member_id` mediumint(8) unsigned NOT NULL default '0',
605 `to_member_id` mediumint(8) unsigned NOT NULL default '0',
606 `date_sent` TIMESTAMP NOT NULL,
607 `new` tinyint(4) NOT NULL default '0',
608 `replied` tinyint(4) NOT NULL default '0',
609 `subject` VARCHAR(255) NOT NULL ,
611 PRIMARY KEY (`message_id`),
612 KEY `to_member_id` (`to_member_id`)
615 # --------------------------------------------------------
616 # Table structure for table `messages_sent` (since 1.5.4)
618 CREATE TABLE `messages_sent` (
619 `message_id` mediumint( 8 ) unsigned NOT NULL AUTO_INCREMENT ,
620 `course_id` mediumint( 8 ) unsigned NOT NULL default '0',
621 `from_member_id` mediumint( 8 ) unsigned NOT NULL default '0',
622 `to_member_id` mediumint( 8 ) unsigned NOT NULL default '0',
623 `date_sent` timestamp NOT NULL ,
624 `subject` VARCHAR(255) NOT NULL ,
626 PRIMARY KEY ( `message_id` ) ,
627 KEY `from_member_id` ( `from_member_id` )
630 # --------------------------------------------------------
631 # Table structure for table `modules` (since 1.5.2)
633 CREATE TABLE `modules` (
634 `dir_name` VARCHAR( 50 ) NOT NULL default '',
635 `status` TINYINT NOT NULL default 0,
636 `privilege` INT UNSIGNED NOT NULL default 0,
637 `admin_privilege` MEDIUMINT UNSIGNED NOT NULL default 0,
638 `cron_interval` SMALLINT UNSIGNED DEFAULT '0' NOT NULL ,
639 `cron_last_run` INT UNSIGNED DEFAULT '0' NOT NULL,
640 PRIMARY KEY ( `dir_name` )
643 INSERT INTO `modules` VALUES ('_core/properties', 2, 1, 0, 0, 0);
644 INSERT INTO `modules` VALUES ('_standard/statistics', 2, 1, 0, 0, 0);
645 INSERT INTO `modules` VALUES ('_core/content', 2, 2, 0, 0, 0);
646 INSERT INTO `modules` VALUES ('_core/glossary', 2, 4, 0, 0, 0);
647 INSERT INTO `modules` VALUES ('_standard/tests', 2, 8, 0, 0, 0);
648 INSERT INTO `modules` VALUES ('_standard/chat', 2, 16, 0, 0, 0);
649 INSERT INTO `modules` VALUES ('_core/file_manager', 2, 32, 0, 0, 0);
650 INSERT INTO `modules` VALUES ('_standard/links', 2, 64, 0, 0, 0);
651 INSERT INTO `modules` VALUES ('_standard/forums', 2, 128, 16, 0, 0);
652 INSERT INTO `modules` VALUES ('_standard/course_tools', 2, 256, 0, 0, 0);
653 INSERT INTO `modules` VALUES ('_core/enrolment', 2, 512, 512, 0, 0);
654 INSERT INTO `modules` VALUES ('_standard/course_email', 2, 1024, 0, 0, 0);
655 INSERT INTO `modules` VALUES ('_standard/announcements', 2, 2048, 0, 0, 0);
656 INSERT INTO `modules` VALUES ('_standard/polls', 2, 16384, 0, 0, 0);
657 INSERT INTO `modules` VALUES ('_standard/faq', 2, 32768, 0, 0, 0);
658 INSERT INTO `modules` VALUES ('_core/groups', 2, 65536, 0, 0, 0);
659 INSERT INTO `modules` VALUES ('_standard/reading_list', 2, 131072, 0, 0, 0);
660 INSERT INTO `modules` VALUES ('_standard/file_storage', 2, 262144, 0, 0, 0);
661 INSERT INTO `modules` VALUES ('_standard/assignments', 2, 524288, 0, 0, 0);
662 INSERT INTO `modules` VALUES ('_standard/gradebook', 2, 1048576, 4096, 0, 0);
663 INSERT INTO `modules` VALUES ('_standard/student_tools', 2, 2097152, 0, 0, 0);
664 INSERT INTO `modules` VALUES ('_standard/farchive', 2, 4194304, 0, 0, 0);
665 INSERT INTO `modules` VALUES ('_standard/social', 2, 8388608, 0, 0, 0);
666 INSERT INTO `modules` VALUES ('_standard/photos', 2, 16777216, 0, 0, 0);
667 INSERT INTO `modules` VALUES ('_standard/flowplayer', 2, 33554432, 0, 0, 0);
668 INSERT INTO `modules` VALUES ('_core/users', 2, 0, 2, 0, 0);
669 INSERT INTO `modules` VALUES ('_core/courses', 2, 0, 4, 0, 0);
670 INSERT INTO `modules` VALUES ('_core/backups', 2, 1, 8, 0, 0);
671 INSERT INTO `modules` VALUES ('_core/cats_categories', 2, 0, 32, 0, 0);
672 INSERT INTO `modules` VALUES ('_core/languages', 2, 0, 64, 1440, 0);
673 INSERT INTO `modules` VALUES ('_core/themes', 2, 0, 128, 0, 0);
674 INSERT INTO `modules` VALUES ('_standard/rss_feeds', 2, 0, 256, 0, 0);
675 INSERT INTO `modules` VALUES ('_standard/directory', 2, 0, 0, 0, 0);
676 INSERT INTO `modules` VALUES ('_standard/tile_search', 2, 0, 0, 0, 0);
677 INSERT INTO `modules` VALUES ('_standard/sitemap', 2, 0, 0, 0, 0);
678 INSERT INTO `modules` VALUES ('_standard/tracker', 2, 0, 0, 0, 0);
679 INSERT INTO `modules` VALUES ('_core/content_packaging', 2, 0, 0, 0, 0);
680 INSERT INTO `modules` VALUES ('_standard/google_search', 2, 0, 0, 0, 0);
681 INSERT INTO `modules` VALUES ('_standard/blogs', 2, 0, 0, 0, 0);
682 INSERT INTO `modules` VALUES ('_standard/profile_pictures', 2, 0, 0, 0, 0);
683 INSERT INTO `modules` VALUES ('_standard/patcher', 2, 0, 1024, 0, 0);
684 INSERT INTO `modules` VALUES ('_standard/support_tools', 2, 0, 2048, 0, 0);
685 # added by Bologna CC. Please check if it is the right position to insert it!
686 INSERT INTO `modules` VALUES ('_core/tool_manager', 2, 0, 0, 0, 0);
687 INSERT INTO `modules` VALUES ('_core/modules', 2, 0, 8192, 0, 0);
692 # --------------------------------------------------------
693 # Table structure for table `news`
695 CREATE TABLE `news` (
696 `news_id` mediumint(8) unsigned NOT NULL auto_increment,
697 `course_id` mediumint(8) unsigned NOT NULL default '0',
698 `member_id` mediumint(8) unsigned NOT NULL default '0',
699 `date` TIMESTAMP NOT NULL,
700 `formatting` tinyint(4) NOT NULL default '0',
701 `title` VARCHAR(200) NOT NULL ,
703 PRIMARY KEY (`news_id`)
706 # --------------------------------------------------------
708 # Table structure for table `polls`
709 CREATE TABLE `polls` (
710 `poll_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,
711 `course_id` MEDIUMINT UNSIGNED NOT NULL default 0,
712 `question` VARCHAR(255) NOT NULL ,
713 `created_date` TIMESTAMP NOT NULL,
714 `total` SMALLINT UNSIGNED NOT NULL default '0',
715 `choice1` VARCHAR(255) NOT NULL ,
716 `count1` SMALLINT UNSIGNED NOT NULL default '0',
717 `choice2` VARCHAR(255) NOT NULL ,
718 `count2` SMALLINT UNSIGNED NOT NULL default '0',
719 `choice3` VARCHAR(255) NOT NULL ,
720 `count3` SMALLINT UNSIGNED NOT NULL default '0',
721 `choice4` VARCHAR(255) NOT NULL ,
722 `count4` SMALLINT UNSIGNED NOT NULL default '0',
723 `choice5` VARCHAR(255) NOT NULL ,
724 `count5` SMALLINT UNSIGNED NOT NULL default '0',
725 `choice6` VARCHAR(255) NOT NULL ,
726 `count6` SMALLINT UNSIGNED NOT NULL default '0',
727 `choice7` VARCHAR(255) NOT NULL ,
728 `count7` SMALLINT UNSIGNED NOT NULL default '0',
729 PRIMARY KEY ( `poll_id` ) ,
730 INDEX ( `course_id` )
733 # --------------------------------------------------------
734 # Table structure for table `mail_queue`
737 CREATE TABLE `mail_queue` (
738 `mail_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,
739 `to_email` VARCHAR( 50 ) NOT NULL default '',
740 `to_name` VARCHAR( 50 ) NOT NULL default '',
741 `from_email` VARCHAR( 50 ) NOT NULL default '',
742 `from_name` VARCHAR( 50 ) NOT NULL default '',
743 `char_set` VARCHAR( 20 ) NOT NULL default '',
744 `subject` VARCHAR(255) NOT NULL ,
746 PRIMARY KEY ( `mail_id` )
749 # --------------------------------------------------------
751 # Table structure for table `polls_members`
753 CREATE TABLE `polls_members` (
754 `poll_id` MEDIUMINT UNSIGNED NOT NULL default '0',
755 `member_id` MEDIUMINT UNSIGNED NOT NULL default '0',
756 PRIMARY KEY ( `poll_id` , `member_id` )
759 # --------------------------------------------------------
761 # Table structure for table `related_content`
762 CREATE TABLE `related_content` (
763 `content_id` mediumint(8) unsigned NOT NULL default '0',
764 `related_content_id` mediumint(8) unsigned NOT NULL default '0',
765 PRIMARY KEY (`content_id`,`related_content_id`)
768 # --------------------------------------------------------
770 # Table structure for table `reading_list`
772 CREATE TABLE `reading_list` (
773 `reading_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
774 `course_id` MEDIUMINT UNSIGNED NOT NULL default 0,
775 `resource_id` MEDIUMINT UNSIGNED NOT NULL default 0,
776 `required` enum('required','optional') NOT NULL DEFAULT 'required',
777 `date_start` DATE NOT NULL DEFAULT '0000-00-00',
778 `date_end` DATE NOT NULL DEFAULT '0000-00-00',
780 PRIMARY KEY (`reading_id`),
785 # Table structure for table `external_resources`
787 CREATE TABLE `external_resources` (
788 `resource_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
789 `course_id` MEDIUMINT UNSIGNED NOT NULL default 0,
790 `type` TINYINT UNSIGNED NOT NULL DEFAULT 0,
791 `title` VARCHAR(255) NOT NULL ,
792 `author` VARCHAR(150) NOT NULL ,
793 `publisher` VARCHAR(150) NOT NULL ,
794 `date` varchar(20) NOT NULL DEFAULT '',
796 `id` varchar(50) NOT NULL DEFAULT '',
797 `url` varchar(255) NOT NULL DEFAULT '',
798 PRIMARY KEY (`resource_id`),
803 # --------------------------------------------------------
804 # Table structure for table `tests`
806 CREATE TABLE `tests` (
807 `test_id` mediumint(8) unsigned NOT NULL auto_increment,
808 `course_id` mediumint(8) unsigned NOT NULL default '0',
809 `title` VARCHAR(255) NOT NULL ,
810 `format` tinyint(4) NOT NULL default '0',
811 `start_date` datetime NOT NULL default '0000-00-00 00:00:00',
812 `end_date` datetime NOT NULL default '0000-00-00 00:00:00',
813 `randomize_order` tinyint(4) NOT NULL default '0',
814 `num_questions` tinyint(3) unsigned NOT NULL default '0',
815 `instructions` TEXT ,
816 `content_id` mediumint(8) NOT NULL default '0',
817 `result_release` tinyint(4) unsigned NOT NULL default '0',
818 `random` tinyint(4) unsigned NOT NULL default '0',
819 `difficulty` tinyint(4) unsigned NOT NULL default '0',
820 `num_takes` tinyint(4) unsigned NOT NULL default '0',
821 `anonymous` tinyint(4) NOT NULL default '0',
822 `out_of` varchar(4) NOT NULL default '',
823 `guests` TINYINT NOT NULL DEFAULT '0',
824 `display` TINYINT NOT NULL DEFAULT '0',
826 `passscore` MEDIUMINT NOT NULL default '0',
827 `passpercent` MEDIUMINT NOT NULL default '0',
830 `show_guest_form` TINYINT(1) UNSIGNED NOT NULL default '0',
831 PRIMARY KEY (`test_id`)
835 # --------------------------------------------------------
836 # Table structure for table `tests_answers`
838 CREATE TABLE `tests_answers` (
839 `result_id` mediumint(8) unsigned NOT NULL default '0',
840 `question_id` mediumint(8) unsigned NOT NULL default '0',
841 `member_id` mediumint(8) unsigned NOT NULL default '0',
843 `score` varchar(5) NOT NULL default '',
845 PRIMARY KEY (`result_id`,`question_id`,`member_id`)
849 # --------------------------------------------------------
850 # Table structure for table `tests_groups`
852 CREATE TABLE `tests_groups` (
853 `test_id` MEDIUMINT UNSIGNED NOT NULL default '0',
854 `group_id` MEDIUMINT UNSIGNED NOT NULL default '0',
855 PRIMARY KEY (`test_id`,`group_id`),
856 KEY `test_id` (`test_id`)
860 # --------------------------------------------------------
861 # Table structure for table `tests_questions`
863 CREATE TABLE `tests_questions` (
864 `question_id` mediumint(8) unsigned NOT NULL auto_increment,
865 `category_id` mediumint(8) unsigned NOT NULL default '0',
866 `course_id` mediumint(8) unsigned NOT NULL default '0',
867 `type` tinyint(3) unsigned NOT NULL default '0',
880 `answer_0` tinyint(4) NOT NULL default '0',
881 `answer_1` tinyint(4) NOT NULL default '0',
882 `answer_2` tinyint(4) NOT NULL default '0',
883 `answer_3` tinyint(4) NOT NULL default '0',
884 `answer_4` tinyint(4) NOT NULL default '0',
885 `answer_5` tinyint(4) NOT NULL default '0',
886 `answer_6` tinyint(4) NOT NULL default '0',
887 `answer_7` tinyint(4) NOT NULL default '0',
888 `answer_8` tinyint(4) NOT NULL default '0',
889 `answer_9` tinyint(4) NOT NULL default '0',
900 `properties` tinyint(4) NOT NULL default '0',
901 `content_id` mediumint(8) NOT NULL,
902 PRIMARY KEY (`question_id`),
903 KEY `category_id` (category_id)
906 # --------------------------------------------------------
907 # Table structure for table `tests_questions_assoc`
909 CREATE TABLE `tests_questions_assoc` (
910 `test_id` mediumint(8) unsigned NOT NULL default '0',
911 `question_id` mediumint(8) unsigned NOT NULL default '0',
912 `weight` varchar(4) NOT NULL default '',
913 `ordering` mediumint(8) unsigned NOT NULL default '0',
914 `required` tinyint(3) unsigned NOT NULL default '0',
915 PRIMARY KEY (`test_id`,`question_id`)
918 # --------------------------------------------------------
919 # Table structure for table `tests_questions_categories`
921 CREATE TABLE `tests_questions_categories` (
922 `category_id` mediumint(8) unsigned NOT NULL auto_increment,
923 `course_id` mediumint(8) unsigned NOT NULL default '0',
924 `title` char(200) NOT NULL default '',
925 PRIMARY KEY (`category_id`),
926 KEY `course_id` (`course_id`)
929 # --------------------------------------------------------
930 # Table structure for table `tests_results`
932 CREATE TABLE `tests_results` (
933 `result_id` mediumint(8) unsigned NOT NULL auto_increment,
934 `test_id` mediumint(8) unsigned NOT NULL default '0',
935 `member_id` VARCHAR(10) NOT NULL default '',
936 `date_taken` TIMESTAMP NOT NULL,
937 `final_score` char(5) NOT NULL default '',
938 `status` TINYINT NOT NULL DEFAULT '0',
939 `end_time` TIMESTAMP NOT NULL ,
940 `max_pos` TINYINT UNSIGNED NOT NULL DEFAULT '0',
941 PRIMARY KEY (`result_id`),
942 KEY `test_id` (`test_id`)
945 # --------------------------------------------------------
946 # Table structure for table `themes`
949 CREATE TABLE `themes` (
950 `title` varchar(80) NOT NULL default '',
951 `version` varchar(10) NOT NULL default '',
952 `dir_name` varchar(20) NOT NULL default '',
953 `type` varchar(20) NOT NULL default 'Desktop',
954 `last_updated` date NOT NULL default '0000-00-00',
956 `status` tinyint(3) unsigned NOT NULL default '1',
957 PRIMARY KEY (`title`)
961 # --------------------------------------------------------
962 # Table structure for table `patches`
965 CREATE TABLE `patches` (
966 `patches_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
967 `atutor_patch_id` VARCHAR(20) NOT NULL default '',
968 `applied_version` VARCHAR(10) NOT NULL default '',
969 `patch_folder` VARCHAR(250) NOT NULL default '',
971 `available_to` VARCHAR(250) NOT NULL default '',
972 `sql_statement` TEXT,
973 `status` varchar(20) NOT NULL default '',
974 `remove_permission_files` TEXT,
977 `author` VARCHAR(255) NOT NULL,
978 `installed_date` datetime NOT NULL,
979 PRIMARY KEY (`patches_id`)
983 # --------------------------------------------------------
984 # Table structure for table `patches_files`
987 CREATE TABLE `patches_files` (
988 `patches_files_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
989 `patches_id` MEDIUMINT UNSIGNED NOT NULL default 0,
990 `action` VARCHAR(20) NOT NULL default '',
992 `location` VARCHAR(250) NOT NULL default '',
993 PRIMARY KEY (`patches_files_id`)
996 # --------------------------------------------------------
997 # Table structure for table `patches_files_actions`
1000 CREATE TABLE `patches_files_actions` (
1001 `patches_files_actions_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
1002 `patches_files_id` MEDIUMINT UNSIGNED NOT NULL default 0,
1003 `action` VARCHAR(20) NOT NULL default '',
1006 PRIMARY KEY (`patches_files_actions_id`)
1011 # --------------------------------------------------------
1012 # New tables for patch creator
1014 CREATE TABLE `myown_patches` (
1015 `myown_patch_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
1016 `atutor_patch_id` VARCHAR(20) NOT NULL default '',
1017 `applied_version` VARCHAR(10) NOT NULL default '',
1019 `sql_statement` TEXT,
1020 `status` varchar(20) NOT NULL default '',
1021 `last_modified` datetime NOT NULL,
1022 PRIMARY KEY (`myown_patch_id`)
1025 CREATE TABLE `myown_patches_dependent` (
1026 `myown_patches_dependent_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
1027 `myown_patch_id` MEDIUMINT UNSIGNED NOT NULL,
1028 `dependent_patch_id` VARCHAR(50) NOT NULL default '',
1029 PRIMARY KEY (`myown_patches_dependent_id`)
1032 CREATE TABLE `myown_patches_files` (
1033 `myown_patches_files_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
1034 `myown_patch_id` MEDIUMINT UNSIGNED NOT NULL,
1035 `action` VARCHAR(20) NOT NULL default '',
1036 `name` VARCHAR(250) NOT NULL,
1037 `location` VARCHAR(250) NOT NULL default '',
1040 `uploaded_file` TEXT,
1041 PRIMARY KEY (`myown_patches_files_id`)
1048 # insert the default theme
1049 INSERT INTO `themes` VALUES ('ATutor', '2.0', '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);
1050 INSERT INTO `themes` VALUES ('Fluid', '2.0', 'fluid', 'Desktop', NOW(), 'Theme that implements the Fluid reorderer used to drag-and-drop the menu from side-to-side.', 1);
1051 INSERT INTO `themes` VALUES ('ATutor Classic', '2.0', '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);
1052 INSERT INTO `themes` VALUES ('Blumin', '2.0', 'blumin', 'Desktop', NOW(), 'This is the plone look-alike theme.', 1);
1053 INSERT INTO `themes` VALUES ('Greenmin', '2.0', 'greenmin', 'Desktop', NOW(), 'This is the plone look-alike theme in green.', 1);
1054 INSERT INTO `themes` VALUES ('ATutor 1.5', '2.0', 'default15', 'Desktop', NOW(), 'This is the 1.5 series default theme.', 1);
1055 INSERT INTO `themes` VALUES ('ATutor 1.6', '2.0', 'default16', 'Desktop', NOW(), 'This is the 1.6 series default theme.', 1);
1056 INSERT INTO `themes` VALUES ('Mobile', '2.0', 'mobile', 'Mobile', NOW(), 'This is the default theme for mobile devices.', 3);
1058 # --------------------------------------------------------
1059 # Table structure for table `users_online`
1061 CREATE TABLE `users_online` (
1062 `member_id` mediumint(8) unsigned NOT NULL default '0',
1063 `course_id` mediumint(8) unsigned NOT NULL default '0',
1064 `login` varchar(255) NOT NULL default '',
1065 `expiry` int(10) unsigned NOT NULL default '0',
1066 PRIMARY KEY (`member_id`)
1067 ) ENGINE=HEAP MAX_ROWS=500;
1069 # --------------------------------------------------------
1070 # Table structure for table `auto_enroll`
1072 CREATE TABLE `auto_enroll` (
1073 `auto_enroll_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
1074 `associate_string` VARCHAR(10) NOT NULL,
1075 `name` VARCHAR( 50 ) NOT NULL default '',
1076 PRIMARY KEY ( `auto_enroll_id` )
1079 # --------------------------------------------------------
1080 # Table structure for table `auto_enroll_courses`
1082 CREATE TABLE `auto_enroll_courses` (
1083 `auto_enroll_courses_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
1084 `auto_enroll_id` MEDIUMINT UNSIGNED NOT NULL default 0,
1085 `course_id` MEDIUMINT UNSIGNED NOT NULL default 0,
1086 PRIMARY KEY ( `auto_enroll_courses_id` )
1090 #Setup Table for Access4All
1091 CREATE TABLE `primary_resources` (
1092 `primary_resource_id` mediumint(8) unsigned NOT NULL auto_increment,
1093 `content_id` mediumint(8) unsigned NOT NULL default '0',
1095 `language_code` varchar(20) default NULL,
1096 PRIMARY KEY (`primary_resource_id`)
1099 CREATE TABLE `primary_resources_types` (
1100 `primary_resource_id` mediumint(8) unsigned NOT NULL,
1101 `type_id` mediumint(8) unsigned NOT NULL,
1102 PRIMARY KEY (`primary_resource_id`,`type_id`)
1105 CREATE TABLE `resource_types` (
1106 `type_id` mediumint(8) unsigned NOT NULL auto_increment,
1108 PRIMARY KEY (`type_id`)
1111 CREATE TABLE `secondary_resources` (
1112 `secondary_resource_id` mediumint(8) unsigned NOT NULL auto_increment,
1113 `primary_resource_id` mediumint(8) unsigned NOT NULL,
1114 `secondary_resource` TEXT,
1115 `language_code` varchar(20) default NULL,
1116 PRIMARY KEY (`secondary_resource_id`)
1119 CREATE TABLE `secondary_resources_types` (
1120 `secondary_resource_id` mediumint(8) unsigned NOT NULL,
1121 `type_id` mediumint(8) unsigned NOT NULL,
1122 PRIMARY KEY (`secondary_resource_id`,`type_id`)
1125 INSERT INTO `resource_types` VALUES
1127 (2, 'sign_language'),
1131 INSERT INTO `config` (`name`, `value`) VALUES('encyclopedia', 'http://www.wikipedia.org');
1132 INSERT INTO `config` (`name`, `value`) VALUES('dictionary', 'http://dictionary.reference.com/');
1133 INSERT INTO `config` (`name`, `value`) VALUES('thesaurus', 'http://thesaurus.reference.com/');
1134 INSERT INTO `config` (`name`, `value`) VALUES('atlas', 'http://maps.google.ca/');
1135 INSERT INTO `config` (`name`, `value`) VALUES('calculator', 'http://www.calculateforfree.com/');
1136 INSERT INTO `config` (`name`, `value`) VALUES('note_taking', 'http://www.aypwip.org/webnote/');
1137 INSERT INTO `config` (`name`, `value`) VALUES('abacas', 'http://www.mandarintools.com/abacus.html');
1138 INSERT INTO `config` (`name`, `value`) VALUES('transformable_uri', 'http://localhost/transformable/');
1139 INSERT INTO `config` (`name`, `value`) VALUES('transformable_web_service_id', '90c3cd6f656739969847f3a99ac0f3c7');
1140 INSERT INTO `config` (`name`, `value`) VALUES('transformable_oauth_expire', '93600');
1142 #End Access4All setup
1144 # sql file for gradebook module
1146 CREATE TABLE `grade_scales` (
1147 `grade_scale_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
1148 `member_id` mediumint(8) unsigned NOT NULL default '0',
1149 `scale_name` VARCHAR(255) NOT NULL default '',
1150 `created_date` datetime NOT NULL default '0000-00-00 00:00:00',
1151 PRIMARY KEY ( `grade_scale_id` )
1154 CREATE TABLE `grade_scales_detail` (
1155 `grade_scale_id` mediumint(8) unsigned NOT NULL,
1156 `scale_value` VARCHAR(50) NOT NULL default '',
1157 `percentage_from` MEDIUMINT NOT NULL default '0',
1158 `percentage_to` MEDIUMINT NOT NULL default '0',
1159 PRIMARY KEY (`grade_scale_id`, `scale_value`)
1162 CREATE TABLE `gradebook_tests` (
1163 `gradebook_test_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
1164 `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.',
1165 `type` VARCHAR(50) NOT NULL default '' COMMENT 'Values: ATutor Test, ATutor Assignment, External',
1166 `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.',
1167 `title` VARCHAR(255) NOT NULL default '' COMMENT 'Values: Null or test name. Always null if ATutor internal assignments/tests/surveys.',
1168 `due_date` datetime NOT NULL default '0000-00-00 00:00:00',
1169 `grade_scale_id` mediumint(8) unsigned NOT NULL default '0',
1170 PRIMARY KEY ( `gradebook_test_id` )
1173 CREATE TABLE `gradebook_detail` (
1174 `gradebook_test_id` mediumint(8) unsigned NOT NULL,
1175 `member_id` mediumint(8) unsigned NOT NULL default '0',
1176 `grade` VARCHAR(255) NOT NULL default '',
1177 PRIMARY KEY (`gradebook_test_id`, `member_id`)
1180 INSERT INTO `grade_scales` (grade_scale_id, member_id, scale_name, created_date) values (1, 0, 'Letter Grade', now());
1181 INSERT INTO `grade_scales` (grade_scale_id, member_id, scale_name, created_date) values (2, 0, 'Competency 1', now());
1182 INSERT INTO `grade_scales` (grade_scale_id, member_id, scale_name, created_date) values (3, 0, 'Competency 2', now());
1184 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (1, 'A+', 90, 100);
1185 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (1, 'A', 80, 89);
1186 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (1, 'B', 70, 79);
1187 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (1, 'C', 60, 69);
1188 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (1, 'D', 50, 59);
1189 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (1, 'E', 0, 49);
1191 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (2, 'Pass', 75, 100);
1192 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (2, 'Fail', 0, 74);
1194 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (3, 'Excellent', 80, 100);
1195 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (3, 'Good', 70, 79);
1196 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (3, 'Adequate', 60, 69);
1197 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (3, 'Inadequate', 0, 59);
1201 # Create table for standalone student tools page
1203 CREATE TABLE `fha_student_tools` (
1204 `course_id` mediumint(8) unsigned NOT NULL,
1206 `home_view` tinyint NOT NULL DEFAULT 1,
1207 PRIMARY KEY ( `course_id` )
1211 # Setup tables for Social Networking module
1213 CREATE TABLE `social_activities` (
1214 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1215 `member_id` INTEGER UNSIGNED NOT NULL,
1216 `application_id` INTEGER UNSIGNED NOT NULL,
1218 `created_date` TIMESTAMP NOT NULL,
1223 # Applications/ Gagdets table
1224 CREATE TABLE `social_applications` (
1225 `id` INTEGER UNSIGNED,
1226 `url` VARCHAR(255) NOT NULL DEFAULT '',
1227 `title` VARCHAR(255) NOT NULL,
1228 `height` INTEGER UNSIGNED,
1229 `scrolling` INTEGER UNSIGNED,
1230 `screenshot` VARCHAR(255) NOT NULL,
1231 `thumbnail` VARCHAR(255) NOT NULL,
1232 `author` VARCHAR(255) NOT NULL,
1233 `author_email` VARCHAR(128) NOT NULL,
1237 `last_updated` TIMESTAMP NOT NULL,
1242 # Application Settings, like storing the perference string.
1243 CREATE TABLE `social_application_settings` (
1244 `application_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1245 `member_id` INTEGER UNSIGNED NOT NULL,
1246 `name` VARCHAR(255) NOT NULL,
1248 PRIMARY KEY (`application_id`, `member_id`, `name`)
1252 # Application members mapping
1253 CREATE TABLE `social_members_applications` (
1254 `member_id` INTEGER UNSIGNED NOT NULL,
1255 `application_id` INTEGER UNSIGNED NOT NULL,
1256 PRIMARY KEY (`member_id`, `application_id`)
1261 CREATE TABLE `social_friends` (
1262 `member_id` INTEGER UNSIGNED NOT NULL,
1263 `friend_id` INTEGER UNSIGNED NOT NULL,
1264 `relationship` INTEGER UNSIGNED NOT NULL,
1265 PRIMARY KEY (`member_id`, `friend_id`)
1269 # Friend requests table
1270 CREATE TABLE `social_friend_requests` (
1271 `member_id` INTEGER UNSIGNED NOT NULL,
1272 `friend_id` INTEGER UNSIGNED NOT NULL,
1273 `relationship` INTEGER UNSIGNED NOT NULL,
1274 PRIMARY KEY (`member_id`, `friend_id`)
1278 # Person Positions (jobs)
1279 CREATE TABLE `social_member_position` (
1280 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1281 `member_id` INTEGER UNSIGNED NOT NULL,
1282 `title` VARCHAR(255) NOT NULL,
1283 `company` VARCHAR(255) NOT NULL,
1284 `from` VARCHAR(10) NOT NULL DEFAULT 0,
1285 `to` VARCHAR(10) NOT NULL DEFAULT 0,
1292 CREATE TABLE `social_member_education` (
1293 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1294 `member_id` INTEGER UNSIGNED NOT NULL,
1295 `university` VARCHAR(255) NOT NULL,
1296 `country` VARCHAR(128),
1297 `province` VARCHAR(128),
1298 `degree` VARCHAR(64),
1299 `field` VARCHAR(64),
1300 `from` VARCHAR(10) NOT NULL DEFAULT 0,
1301 `to` VARCHAR(10) NOT NULL DEFAULT 0,
1307 # Person related web sites
1308 CREATE TABLE `social_member_websites` (
1309 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1310 `member_id` INTEGER UNSIGNED NOT NULL,
1311 `url` VARCHAR(255) NOT NULL,
1312 `site_name` VARCHAR(255),
1317 # Tracks visitor counts
1318 CREATE TABLE `social_member_track` (
1319 `member_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1320 `visitor_id` INTEGER UNSIGNED NOT NULL,
1321 `timestamp` TIMESTAMP NOT NULL,
1322 PRIMARY KEY (`member_id`, `visitor_id`, `timestamp`)
1326 # Person additional information cojoint with the members table
1327 CREATE TABLE `social_member_additional_information` (
1328 `member_id` INTEGER UNSIGNED NOT NULL,
1329 `expertise` VARCHAR(255) NOT NULL,
1331 `associations` TEXT,
1334 PRIMARY KEY (`member_id`)
1339 CREATE TABLE `social_member_contact` (
1340 `contact_id` int(10) unsigned NOT NULL auto_increment,
1341 `member_id` int(10) unsigned NOT NULL,
1342 `con_name` varchar(200) NOT NULL,
1343 `con_phone` varchar(15) NOT NULL,
1344 `con_email` varchar(50) NOT NULL,
1345 `con_address` text NOT NULL,
1346 PRIMARY KEY (`contact_id`)
1350 CREATE TABLE `social_member_representation` (
1351 `rep_id` int(10) unsigned NOT NULL auto_increment,
1352 `member_id` int(10) unsigned NOT NULL,
1353 `rep_name` varchar(200) NOT NULL,
1354 `rep_title` varchar(50) NOT NULL,
1355 `rep_phone` varchar(15) NOT NULL,
1356 `rep_email` varchar(50) NOT NULL,
1357 `rep_address` text NOT NULL,
1358 PRIMARY KEY (`rep_id`)
1363 CREATE TABLE `social_member_personal` (
1364 `per_id` int(10) unsigned NOT NULL auto_increment,
1365 `member_id` int(10) unsigned NOT NULL,
1366 `per_weight` varchar(200) NOT NULL,
1367 `per_height` varchar(50) NOT NULL,
1368 `per_hair` varchar(15) NOT NULL,
1369 `per_eyes` varchar(50) NOT NULL,
1370 `per_ethnicity` varchar(50) NOT NULL,
1371 `per_languages` varchar(255) NOT NULL,
1372 `per_disabilities` varchar(255) NOT NULL,
1373 PRIMARY KEY (`per_id`)
1376 # Privacy Control Preferences
1377 CREATE TABLE `social_privacy_preferences` (
1378 `member_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1380 PRIMARY KEY (`member_id`)
1384 # Social Group tables
1385 CREATE TABLE `social_groups` (
1386 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1387 `member_id` INTEGER UNSIGNED NOT NULL,
1388 `type_id` INTEGER UNSIGNED NOT NULL,
1389 `privacy` INTEGER UNSIGNED NOT NULL,
1390 `name` VARCHAR(255) NOT NULL,
1391 `logo` VARCHAR(255) NOT NULL,
1393 `created_date` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
1394 `last_updated` TIMESTAMP NOT NULL,
1399 CREATE TABLE `social_groups_activities` (
1400 `activity_id` INTEGER UNSIGNED NOT NULL,
1401 `group_id` INTEGER UNSIGNED NOT NULL,
1402 PRIMARY KEY (`activity_id`, `group_id`)
1406 CREATE TABLE `social_groups_members` (
1407 `group_id` INTEGER UNSIGNED NOT NULL,
1408 `member_id` INTEGER UNSIGNED NOT NULL,
1409 PRIMARY KEY (`group_id`, `member_id`)
1413 CREATE TABLE `social_groups_invitations` (
1414 `sender_id` INTEGER UNSIGNED NOT NULL,
1415 `member_id` INTEGER UNSIGNED NOT NULL,
1416 `group_id` INTEGER UNSIGNED NOT NULL,
1417 PRIMARY KEY (`sender_id`, `member_id`, `group_id`)
1421 CREATE TABLE `social_groups_requests` (
1422 `sender_id` INTEGER UNSIGNED NOT NULL,
1423 `member_id` INTEGER UNSIGNED NOT NULL,
1424 `group_id` INTEGER UNSIGNED NOT NULL,
1425 PRIMARY KEY (`sender_id`, `member_id`, `group_id`)
1429 CREATE TABLE `social_groups_types` (
1430 `type_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1431 `title` VARCHAR(127) NOT NULL,
1432 PRIMARY KEY (`type_id`)
1436 # CREATE TABLE `social_groups_forums` (
1437 # `group_id` INTEGER UNSIGNED NOT NULL,
1438 # `forum_id` INTEGER UNSIGNED NOT NULL,
1439 # PRIMARY KEY (`group_id`, `forum_id`)
1443 # Groups message board
1444 CREATE TABLE `social_groups_board` (
1445 `id` int(10) unsigned NOT NULL auto_increment,
1446 `member_id` int(10) unsigned NOT NULL,
1447 `group_id` int(10) unsigned NOT NULL,
1449 `created_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1455 CREATE TABLE `social_user_settings` (
1456 `member_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1457 `app_settings` TEXT,
1458 PRIMARY KEY (`member_id`)
1463 #====== Initial Data ========
1464 INSERT INTO social_groups_types SET title='business', type_id=1;
1465 INSERT INTO social_groups_types SET title='common_interest', type_id=2;
1466 INSERT INTO social_groups_types SET title='entertainment_arts', type_id=3;
1467 INSERT INTO social_groups_types SET title='geography', type_id=4;
1468 INSERT INTO social_groups_types SET title='internet_technology', type_id=5;
1469 INSERT INTO social_groups_types SET title='organization', type_id=6;
1470 INSERT INTO social_groups_types SET title='music', type_id=7;
1471 INSERT INTO social_groups_types SET title='sports_recreation', type_id=8;
1473 # END Social Networking setup
1475 # Login attempt control table
1476 CREATE TABLE `member_login_attempt` (
1477 `login` varchar(20) NOT NULL,
1478 `attempt` tinyint(3) unsigned default NULL,
1479 `expiry` int(10) unsigned default NULL,
1480 PRIMARY KEY (`login`)
1483 # --------------------------------------------------------
1484 # Adding feature of blog subsription
1485 # Table structure for table `blog_subscription`
1487 CREATE TABLE `blog_subscription` (
1488 `group_id` MEDIUMINT NOT NULL ,
1489 `member_id` MEDIUMINT NOT NULL ,
1490 PRIMARY KEY (group_id,member_id)
1493 # END Adding feature of blog subsription
1495 # --------------------------------------------------------
1496 # Adding feature of content pre-requisites
1497 # Table structure for table `content_prerequisites`
1499 CREATE TABLE `content_prerequisites` (
1500 `content_id` MEDIUMINT NOT NULL,
1501 `type` varchar(50) NOT NULL DEFAULT '',
1502 `item_id` MEDIUMINT NOT NULL,
1503 PRIMARY KEY (content_id,type, item_id)
1506 # END Adding feature of content pre-requisites
1508 # --------------------------------------------------------
1509 # Adding feature of oauth client
1510 # Table structure for table `oauth_client_servers`
1513 CREATE TABLE `oauth_client_servers` (
1514 `oauth_server_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
1515 `oauth_server` VARCHAR(255) NOT NULL default '',
1516 `consumer_key` TEXT NOT NULL ,
1517 `consumer_secret` TEXT NOT NULL ,
1518 `expire_threshold` INT NOT NULL default 0,
1519 `create_date` datetime NOT NULL,
1520 PRIMARY KEY ( `oauth_server_id` ),
1521 UNIQUE INDEX idx_consumer ( `oauth_server` )
1524 # --------------------------------------------------------
1525 # Table structure for table `oauth_client_tokens`
1528 CREATE TABLE `oauth_client_tokens` (
1529 `oauth_server_id` MEDIUMINT UNSIGNED NOT NULL,
1530 `token` VARCHAR(50) NOT NULL default '',
1531 `token_type` VARCHAR(50) NOT NULL NOT NULL default '',
1532 `token_secret` TEXT NOT NULL,
1533 `member_id` mediumint(8) unsigned NOT NULL ,
1534 `assign_date` datetime NOT NULL,
1535 PRIMARY KEY ( `oauth_server_id`, `token` )
1538 # END Adding feature of oauth client
1541 # -------------- Photo Album Module Setup ----------------
1544 CREATE TABLE `pa_albums` (
1545 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1546 `name` VARCHAR(255) NOT NULL,
1547 `location` VARCHAR(255) NOT NULL,
1548 `description` TEXT NOT NULL,
1549 `permission` TINYINT(1) UNSIGNED NOT NULL,
1550 `member_id` INTEGER UNSIGNED NOT NULL,
1551 `photo_id` INTEGER UNSIGNED NOT NULL,
1552 `type_id` TINYINT(1) UNSIGNED NOT NULL,
1553 `created_date` DATETIME NOT NULL,
1554 `last_updated` DATETIME NOT NULL,
1560 CREATE TABLE `pa_photos` (
1561 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1562 `name` VARCHAR(255) NOT NULL,
1565 `member_id` INTEGER UNSIGNED NOT NULL,
1566 `album_id` INTEGER UNSIGNED NOT NULL,
1567 `ordering` SMALLINT UNSIGNED NOT NULL,
1568 `created_date` DATETIME NOT NULL,
1569 `last_updated` DATETIME NOT NULL,
1574 # Course Album Table
1575 CREATE TABLE `pa_course_album` (
1576 `course_id` INTEGER UNSIGNED,
1577 `album_id` INTEGER UNSIGNED,
1578 PRIMARY KEY (`course_id`, `album_id`)
1582 # Photo Album Comments
1583 CREATE TABLE `pa_album_comments` (
1584 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1585 `album_id` INTEGER UNSIGNED NOT NULL,
1586 `member_id` INTEGER UNSIGNED NOT NULL,
1587 `comment` TEXT NOT NULL,
1588 `created_date` DATETIME NOT NULL,
1594 CREATE TABLE `pa_photo_comments` (
1595 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1596 `photo_id` INTEGER UNSIGNED NOT NULL,
1597 `member_id` INTEGER UNSIGNED NOT NULL,
1598 `comment` TEXT NOT NULL,
1599 `created_date` DATETIME NOT NULL,
1605 INSERT INTO `config` VALUES ('pa_max_memory_per_member', '50');
1607 # -------------- Photo Album Module Ends -----------------