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(100) 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 ('_core/users', 2, 0, 2, 0, 0);
667 INSERT INTO `modules` VALUES ('_core/courses', 2, 0, 4, 0, 0);
668 INSERT INTO `modules` VALUES ('_core/backups', 2, 1, 8, 0, 0);
669 INSERT INTO `modules` VALUES ('_core/cats_categories', 2, 0, 32, 0, 0);
670 INSERT INTO `modules` VALUES ('_core/languages', 2, 0, 64, 1440, 0);
671 INSERT INTO `modules` VALUES ('_core/themes', 2, 0, 128, 0, 0);
672 INSERT INTO `modules` VALUES ('_standard/rss_feeds', 2, 0, 256, 0, 0);
673 INSERT INTO `modules` VALUES ('_standard/directory', 2, 0, 0, 0, 0);
674 INSERT INTO `modules` VALUES ('_standard/tile_search', 2, 0, 0, 0, 0);
675 INSERT INTO `modules` VALUES ('_standard/sitemap', 2, 0, 0, 0, 0);
676 INSERT INTO `modules` VALUES ('_standard/tracker', 2, 0, 0, 0, 0);
677 INSERT INTO `modules` VALUES ('_core/content_packaging', 2, 0, 0, 0, 0);
678 INSERT INTO `modules` VALUES ('_standard/google_search', 2, 0, 0, 0, 0);
679 INSERT INTO `modules` VALUES ('_standard/blogs', 2, 0, 0, 0, 0);
680 INSERT INTO `modules` VALUES ('_standard/profile_pictures', 2, 0, 0, 0, 0);
681 INSERT INTO `modules` VALUES ('_standard/patcher', 2, 0, 1024, 0, 0);
682 INSERT INTO `modules` VALUES ('_standard/support_tools', 2, 0, 2048, 0, 0);
683 # added by Bologna CC. Please check if it is the right position to insert it!
684 INSERT INTO `modules` VALUES ('_core/tool_manager', 2, 0, 0, 0, 0);
688 # --------------------------------------------------------
689 # Table structure for table `news`
691 CREATE TABLE `news` (
692 `news_id` mediumint(8) unsigned NOT NULL auto_increment,
693 `course_id` mediumint(8) unsigned NOT NULL default '0',
694 `member_id` mediumint(8) unsigned NOT NULL default '0',
695 `date` TIMESTAMP NOT NULL,
696 `formatting` tinyint(4) NOT NULL default '0',
697 `title` VARCHAR(200) NOT NULL ,
699 PRIMARY KEY (`news_id`)
702 # --------------------------------------------------------
704 # Table structure for table `polls`
705 CREATE TABLE `polls` (
706 `poll_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,
707 `course_id` MEDIUMINT UNSIGNED NOT NULL default 0,
708 `question` VARCHAR(255) NOT NULL ,
709 `created_date` TIMESTAMP NOT NULL,
710 `total` SMALLINT UNSIGNED NOT NULL default '0',
711 `choice1` VARCHAR(255) NOT NULL ,
712 `count1` SMALLINT UNSIGNED NOT NULL default '0',
713 `choice2` VARCHAR(255) NOT NULL ,
714 `count2` SMALLINT UNSIGNED NOT NULL default '0',
715 `choice3` VARCHAR(255) NOT NULL ,
716 `count3` SMALLINT UNSIGNED NOT NULL default '0',
717 `choice4` VARCHAR(255) NOT NULL ,
718 `count4` SMALLINT UNSIGNED NOT NULL default '0',
719 `choice5` VARCHAR(255) NOT NULL ,
720 `count5` SMALLINT UNSIGNED NOT NULL default '0',
721 `choice6` VARCHAR(255) NOT NULL ,
722 `count6` SMALLINT UNSIGNED NOT NULL default '0',
723 `choice7` VARCHAR(255) NOT NULL ,
724 `count7` SMALLINT UNSIGNED NOT NULL default '0',
725 PRIMARY KEY ( `poll_id` ) ,
726 INDEX ( `course_id` )
729 # --------------------------------------------------------
730 # Table structure for table `mail_queue`
733 CREATE TABLE `mail_queue` (
734 `mail_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,
735 `to_email` VARCHAR( 50 ) NOT NULL default '',
736 `to_name` VARCHAR( 50 ) NOT NULL default '',
737 `from_email` VARCHAR( 50 ) NOT NULL default '',
738 `from_name` VARCHAR( 50 ) NOT NULL default '',
739 `char_set` VARCHAR( 20 ) NOT NULL default '',
740 `subject` VARCHAR(255) NOT NULL ,
742 PRIMARY KEY ( `mail_id` )
745 # --------------------------------------------------------
747 # Table structure for table `polls_members`
749 CREATE TABLE `polls_members` (
750 `poll_id` MEDIUMINT UNSIGNED NOT NULL default '0',
751 `member_id` MEDIUMINT UNSIGNED NOT NULL default '0',
752 PRIMARY KEY ( `poll_id` , `member_id` )
755 # --------------------------------------------------------
757 # Table structure for table `related_content`
758 CREATE TABLE `related_content` (
759 `content_id` mediumint(8) unsigned NOT NULL default '0',
760 `related_content_id` mediumint(8) unsigned NOT NULL default '0',
761 PRIMARY KEY (`content_id`,`related_content_id`)
764 # --------------------------------------------------------
766 # Table structure for table `reading_list`
768 CREATE TABLE `reading_list` (
769 `reading_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
770 `course_id` MEDIUMINT UNSIGNED NOT NULL default 0,
771 `resource_id` MEDIUMINT UNSIGNED NOT NULL default 0,
772 `required` enum('required','optional') NOT NULL DEFAULT 'required',
773 `date_start` DATE NOT NULL DEFAULT '0000-00-00',
774 `date_end` DATE NOT NULL DEFAULT '0000-00-00',
776 PRIMARY KEY (`reading_id`),
781 # Table structure for table `external_resources`
783 CREATE TABLE `external_resources` (
784 `resource_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
785 `course_id` MEDIUMINT UNSIGNED NOT NULL default 0,
786 `type` TINYINT UNSIGNED NOT NULL DEFAULT 0,
787 `title` VARCHAR(255) NOT NULL ,
788 `author` VARCHAR(150) NOT NULL ,
789 `publisher` VARCHAR(150) NOT NULL ,
790 `date` varchar(20) NOT NULL DEFAULT '',
792 `id` varchar(50) NOT NULL DEFAULT '',
793 `url` varchar(255) NOT NULL DEFAULT '',
794 PRIMARY KEY (`resource_id`),
799 # --------------------------------------------------------
800 # Table structure for table `tests`
802 CREATE TABLE `tests` (
803 `test_id` mediumint(8) unsigned NOT NULL auto_increment,
804 `course_id` mediumint(8) unsigned NOT NULL default '0',
805 `title` VARCHAR(255) NOT NULL ,
806 `format` tinyint(4) NOT NULL default '0',
807 `start_date` datetime NOT NULL default '0000-00-00 00:00:00',
808 `end_date` datetime NOT NULL default '0000-00-00 00:00:00',
809 `randomize_order` tinyint(4) NOT NULL default '0',
810 `num_questions` tinyint(3) unsigned NOT NULL default '0',
811 `instructions` TEXT ,
812 `content_id` mediumint(8) NOT NULL default '0',
813 `result_release` tinyint(4) unsigned NOT NULL default '0',
814 `random` tinyint(4) unsigned NOT NULL default '0',
815 `difficulty` tinyint(4) unsigned NOT NULL default '0',
816 `num_takes` tinyint(4) unsigned NOT NULL default '0',
817 `anonymous` tinyint(4) NOT NULL default '0',
818 `out_of` varchar(4) NOT NULL default '',
819 `guests` TINYINT NOT NULL DEFAULT '0',
820 `display` TINYINT NOT NULL DEFAULT '0',
822 `passscore` MEDIUMINT NOT NULL default '0',
823 `passpercent` MEDIUMINT NOT NULL default '0',
826 `show_guest_form` TINYINT(1) UNSIGNED NOT NULL default '0',
827 PRIMARY KEY (`test_id`)
831 # --------------------------------------------------------
832 # Table structure for table `tests_answers`
834 CREATE TABLE `tests_answers` (
835 `result_id` mediumint(8) unsigned NOT NULL default '0',
836 `question_id` mediumint(8) unsigned NOT NULL default '0',
837 `member_id` mediumint(8) unsigned NOT NULL default '0',
839 `score` varchar(5) NOT NULL default '',
841 PRIMARY KEY (`result_id`,`question_id`,`member_id`)
845 # --------------------------------------------------------
846 # Table structure for table `tests_groups`
848 CREATE TABLE `tests_groups` (
849 `test_id` MEDIUMINT UNSIGNED NOT NULL default '0',
850 `group_id` MEDIUMINT UNSIGNED NOT NULL default '0',
851 PRIMARY KEY (`test_id`,`group_id`),
852 KEY `test_id` (`test_id`)
856 # --------------------------------------------------------
857 # Table structure for table `tests_questions`
859 CREATE TABLE `tests_questions` (
860 `question_id` mediumint(8) unsigned NOT NULL auto_increment,
861 `category_id` mediumint(8) unsigned NOT NULL default '0',
862 `course_id` mediumint(8) unsigned NOT NULL default '0',
863 `type` tinyint(3) unsigned NOT NULL default '0',
876 `answer_0` tinyint(4) NOT NULL default '0',
877 `answer_1` tinyint(4) NOT NULL default '0',
878 `answer_2` tinyint(4) NOT NULL default '0',
879 `answer_3` tinyint(4) NOT NULL default '0',
880 `answer_4` tinyint(4) NOT NULL default '0',
881 `answer_5` tinyint(4) NOT NULL default '0',
882 `answer_6` tinyint(4) NOT NULL default '0',
883 `answer_7` tinyint(4) NOT NULL default '0',
884 `answer_8` tinyint(4) NOT NULL default '0',
885 `answer_9` tinyint(4) NOT NULL default '0',
896 `properties` tinyint(4) NOT NULL default '0',
897 `content_id` mediumint(8) NOT NULL,
898 PRIMARY KEY (`question_id`),
899 KEY `category_id` (category_id)
902 # --------------------------------------------------------
903 # Table structure for table `tests_questions_assoc`
905 CREATE TABLE `tests_questions_assoc` (
906 `test_id` mediumint(8) unsigned NOT NULL default '0',
907 `question_id` mediumint(8) unsigned NOT NULL default '0',
908 `weight` varchar(4) NOT NULL default '',
909 `ordering` mediumint(8) unsigned NOT NULL default '0',
910 `required` tinyint(3) unsigned NOT NULL default '0',
911 PRIMARY KEY (`test_id`,`question_id`)
914 # --------------------------------------------------------
915 # Table structure for table `tests_questions_categories`
917 CREATE TABLE `tests_questions_categories` (
918 `category_id` mediumint(8) unsigned NOT NULL auto_increment,
919 `course_id` mediumint(8) unsigned NOT NULL default '0',
920 `title` char(200) NOT NULL default '',
921 PRIMARY KEY (`category_id`),
922 KEY `course_id` (`course_id`)
925 # --------------------------------------------------------
926 # Table structure for table `tests_results`
928 CREATE TABLE `tests_results` (
929 `result_id` mediumint(8) unsigned NOT NULL auto_increment,
930 `test_id` mediumint(8) unsigned NOT NULL default '0',
931 `member_id` VARCHAR(10) NOT NULL default '',
932 `date_taken` TIMESTAMP NOT NULL,
933 `final_score` char(5) NOT NULL default '',
934 `status` TINYINT NOT NULL DEFAULT '0',
935 `end_time` TIMESTAMP NOT NULL ,
936 `max_pos` TINYINT UNSIGNED NOT NULL DEFAULT '0',
937 PRIMARY KEY (`result_id`),
938 KEY `test_id` (`test_id`)
941 # --------------------------------------------------------
942 # Table structure for table `themes`
945 CREATE TABLE `themes` (
946 `title` varchar(80) NOT NULL default '',
947 `version` varchar(10) NOT NULL default '',
948 `dir_name` varchar(20) NOT NULL default '',
949 `last_updated` date NOT NULL default '0000-00-00',
951 `status` tinyint(3) unsigned NOT NULL default '1',
952 PRIMARY KEY (`title`)
956 # --------------------------------------------------------
957 # Table structure for table `patches`
960 CREATE TABLE `patches` (
961 `patches_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
962 `atutor_patch_id` VARCHAR(20) NOT NULL default '',
963 `applied_version` VARCHAR(10) NOT NULL default '',
964 `patch_folder` VARCHAR(250) NOT NULL default '',
966 `available_to` VARCHAR(250) NOT NULL default '',
967 `sql_statement` TEXT,
968 `status` varchar(20) NOT NULL default '',
969 `remove_permission_files` TEXT,
972 `author` VARCHAR(255) NOT NULL,
973 `installed_date` datetime NOT NULL,
974 PRIMARY KEY (`patches_id`)
978 # --------------------------------------------------------
979 # Table structure for table `patches_files`
982 CREATE TABLE `patches_files` (
983 `patches_files_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
984 `patches_id` MEDIUMINT UNSIGNED NOT NULL default 0,
985 `action` VARCHAR(20) NOT NULL default '',
987 `location` VARCHAR(250) NOT NULL default '',
988 PRIMARY KEY (`patches_files_id`)
991 # --------------------------------------------------------
992 # Table structure for table `patches_files_actions`
995 CREATE TABLE `patches_files_actions` (
996 `patches_files_actions_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
997 `patches_files_id` MEDIUMINT UNSIGNED NOT NULL default 0,
998 `action` VARCHAR(20) NOT NULL default '',
1001 PRIMARY KEY (`patches_files_actions_id`)
1006 # --------------------------------------------------------
1007 # New tables for patch creator
1009 CREATE TABLE `myown_patches` (
1010 `myown_patch_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
1011 `atutor_patch_id` VARCHAR(20) NOT NULL default '',
1012 `applied_version` VARCHAR(10) NOT NULL default '',
1014 `sql_statement` TEXT,
1015 `status` varchar(20) NOT NULL default '',
1016 `last_modified` datetime NOT NULL,
1017 PRIMARY KEY (`myown_patch_id`)
1020 CREATE TABLE `myown_patches_dependent` (
1021 `myown_patches_dependent_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
1022 `myown_patch_id` MEDIUMINT UNSIGNED NOT NULL,
1023 `dependent_patch_id` VARCHAR(50) NOT NULL default '',
1024 PRIMARY KEY (`myown_patches_dependent_id`)
1027 CREATE TABLE `myown_patches_files` (
1028 `myown_patches_files_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
1029 `myown_patch_id` MEDIUMINT UNSIGNED NOT NULL,
1030 `action` VARCHAR(20) NOT NULL default '',
1031 `name` VARCHAR(250) NOT NULL,
1032 `location` VARCHAR(250) NOT NULL default '',
1035 `uploaded_file` TEXT,
1036 PRIMARY KEY (`myown_patches_files_id`)
1043 # insert the default theme
1044 INSERT INTO `themes` VALUES ('ATutor', '1.6.3', 'default', 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);
1045 INSERT INTO `themes` VALUES ('Fluid', '1.6.3', 'fluid', NOW(), 'Theme that implements the Fluid reorderer used to drag-and-drop the menu from side-to-side.', 1);
1046 INSERT INTO `themes` VALUES ('ATutor Classic', '1.6.3', 'default_classic', 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);
1049 INSERT INTO `themes` VALUES ('Blumin', '1.6.3', 'blumin', NOW(), 'This is the plone look-alike theme.', 1);
1050 INSERT INTO `themes` VALUES ('Greenmin', '1.6.3', 'greenmin', NOW(), 'This is the plone look-alike theme in green.', 1);
1051 INSERT INTO `themes` VALUES ('ATutor 1.5', '1.6.3', 'default15', NOW(), 'This is the 1.5 series default theme.', 1);
1052 INSERT INTO `themes` VALUES ('Mobile', '1.6.4', 'mobile', NOW(), 'This is the default theme for mobile devices.', 1);
1054 # --------------------------------------------------------
1055 # Table structure for table `users_online`
1057 CREATE TABLE `users_online` (
1058 `member_id` mediumint(8) unsigned NOT NULL default '0',
1059 `course_id` mediumint(8) unsigned NOT NULL default '0',
1060 `login` varchar(255) NOT NULL default '',
1061 `expiry` int(10) unsigned NOT NULL default '0',
1062 PRIMARY KEY (`member_id`)
1063 ) TYPE=HEAP MAX_ROWS=500;
1065 # --------------------------------------------------------
1066 # Table structure for table `auto_enroll`
1068 CREATE TABLE `auto_enroll` (
1069 `auto_enroll_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
1070 `associate_string` VARCHAR(10) NOT NULL,
1071 `name` VARCHAR( 50 ) NOT NULL default '',
1072 PRIMARY KEY ( `auto_enroll_id` )
1075 # --------------------------------------------------------
1076 # Table structure for table `auto_enroll_courses`
1078 CREATE TABLE `auto_enroll_courses` (
1079 `auto_enroll_courses_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
1080 `auto_enroll_id` MEDIUMINT UNSIGNED NOT NULL default 0,
1081 `course_id` MEDIUMINT UNSIGNED NOT NULL default 0,
1082 PRIMARY KEY ( `auto_enroll_courses_id` )
1086 #Setup Table for Access4All
1087 CREATE TABLE `primary_resources` (
1088 `primary_resource_id` mediumint(8) unsigned NOT NULL auto_increment,
1089 `content_id` mediumint(8) unsigned NOT NULL default '0',
1091 `language_code` varchar(20) default NULL,
1092 PRIMARY KEY (`primary_resource_id`)
1095 CREATE TABLE `primary_resources_types` (
1096 `primary_resource_id` mediumint(8) unsigned NOT NULL,
1097 `type_id` mediumint(8) unsigned NOT NULL,
1098 PRIMARY KEY (`primary_resource_id`,`type_id`)
1101 CREATE TABLE `resource_types` (
1102 `type_id` mediumint(8) unsigned NOT NULL auto_increment,
1104 PRIMARY KEY (`type_id`)
1107 CREATE TABLE `secondary_resources` (
1108 `secondary_resource_id` mediumint(8) unsigned NOT NULL auto_increment,
1109 `primary_resource_id` mediumint(8) unsigned NOT NULL,
1110 `secondary_resource` TEXT,
1111 `language_code` varchar(20) default NULL,
1112 PRIMARY KEY (`secondary_resource_id`)
1115 CREATE TABLE `secondary_resources_types` (
1116 `secondary_resource_id` mediumint(8) unsigned NOT NULL,
1117 `type_id` mediumint(8) unsigned NOT NULL,
1118 PRIMARY KEY (`secondary_resource_id`,`type_id`)
1121 INSERT INTO `resource_types` VALUES
1123 (2, 'sign_language'),
1127 INSERT INTO `config` (`name`, `value`) VALUES('encyclopedia', 'http://www.wikipedia.org');
1128 INSERT INTO `config` (`name`, `value`) VALUES('dictionary', 'http://dictionary.reference.com/');
1129 INSERT INTO `config` (`name`, `value`) VALUES('thesaurus', 'http://thesaurus.reference.com/');
1130 INSERT INTO `config` (`name`, `value`) VALUES('atlas', 'http://maps.google.ca/');
1131 INSERT INTO `config` (`name`, `value`) VALUES('calculator', 'http://www.calculateforfree.com/');
1132 INSERT INTO `config` (`name`, `value`) VALUES('note_taking', 'http://www.aypwip.org/webnote/');
1133 INSERT INTO `config` (`name`, `value`) VALUES('abacas', 'http://www.mandarintools.com/abacus.html');
1135 #End Access4All setup
1137 # sql file for gradebook module
1139 CREATE TABLE `grade_scales` (
1140 `grade_scale_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
1141 `member_id` mediumint(8) unsigned NOT NULL default '0',
1142 `scale_name` VARCHAR(255) NOT NULL default '',
1143 `created_date` datetime NOT NULL default '0000-00-00 00:00:00',
1144 PRIMARY KEY ( `grade_scale_id` )
1147 CREATE TABLE `grade_scales_detail` (
1148 `grade_scale_id` mediumint(8) unsigned NOT NULL,
1149 `scale_value` VARCHAR(50) NOT NULL default '',
1150 `percentage_from` MEDIUMINT NOT NULL default '0',
1151 `percentage_to` MEDIUMINT NOT NULL default '0',
1152 PRIMARY KEY (`grade_scale_id`, `scale_value`)
1155 CREATE TABLE `gradebook_tests` (
1156 `gradebook_test_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
1157 `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.',
1158 `type` VARCHAR(50) NOT NULL default '' COMMENT 'Values: ATutor Test, ATutor Assignment, External',
1159 `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.',
1160 `title` VARCHAR(255) NOT NULL default '' COMMENT 'Values: Null or test name. Always null if ATutor internal assignments/tests/surveys.',
1161 `due_date` datetime NOT NULL default '0000-00-00 00:00:00',
1162 `grade_scale_id` mediumint(8) unsigned NOT NULL default '0',
1163 PRIMARY KEY ( `gradebook_test_id` )
1166 CREATE TABLE `gradebook_detail` (
1167 `gradebook_test_id` mediumint(8) unsigned NOT NULL,
1168 `member_id` mediumint(8) unsigned NOT NULL default '0',
1169 `grade` VARCHAR(255) NOT NULL default '',
1170 PRIMARY KEY (`gradebook_test_id`, `member_id`)
1173 INSERT INTO `grade_scales` (grade_scale_id, member_id, scale_name, created_date) values (1, 0, 'Letter Grade', now());
1174 INSERT INTO `grade_scales` (grade_scale_id, member_id, scale_name, created_date) values (2, 0, 'Competency 1', now());
1175 INSERT INTO `grade_scales` (grade_scale_id, member_id, scale_name, created_date) values (3, 0, 'Competency 2', now());
1177 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (1, 'A+', 90, 100);
1178 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (1, 'A', 80, 89);
1179 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (1, 'B', 70, 79);
1180 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (1, 'C', 60, 69);
1181 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (1, 'D', 50, 59);
1182 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (1, 'E', 0, 49);
1184 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (2, 'Pass', 75, 100);
1185 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (2, 'Fail', 0, 74);
1187 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (3, 'Excellent', 80, 100);
1188 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (3, 'Good', 70, 79);
1189 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (3, 'Adequate', 60, 69);
1190 INSERT INTO `grade_scales_detail` (grade_scale_id, scale_value, percentage_from, percentage_to) values (3, 'Inadequate', 0, 59);
1194 # Create table for standalone student tools page
1196 CREATE TABLE `fha_student_tools` (
1197 `course_id` mediumint(8) unsigned NOT NULL,
1199 `home_view` tinyint NOT NULL DEFAULT 1,
1200 PRIMARY KEY ( `course_id` )
1204 # Setup tables for Social Networking module
1206 CREATE TABLE `social_activities` (
1207 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1208 `member_id` INTEGER UNSIGNED NOT NULL,
1209 `application_id` INTEGER UNSIGNED NOT NULL,
1211 `created_date` TIMESTAMP NOT NULL,
1216 # Applications/ Gagdets table
1217 CREATE TABLE `social_applications` (
1218 `id` INTEGER UNSIGNED,
1219 `url` VARCHAR(255) NOT NULL DEFAULT '',
1220 `title` VARCHAR(255) NOT NULL,
1221 `height` INTEGER UNSIGNED,
1222 `scrolling` INTEGER UNSIGNED,
1223 `screenshot` VARCHAR(255) NOT NULL,
1224 `thumbnail` VARCHAR(255) NOT NULL,
1225 `author` VARCHAR(255) NOT NULL,
1226 `author_email` VARCHAR(128) NOT NULL,
1230 `last_updated` TIMESTAMP NOT NULL,
1235 # Application Settings, like storing the perference string.
1236 CREATE TABLE `social_application_settings` (
1237 `application_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1238 `member_id` INTEGER UNSIGNED NOT NULL,
1239 `name` VARCHAR(255) NOT NULL,
1241 PRIMARY KEY (`application_id`, `member_id`, `name`)
1245 # Application members mapping
1246 CREATE TABLE `social_members_applications` (
1247 `member_id` INTEGER UNSIGNED NOT NULL,
1248 `application_id` INTEGER UNSIGNED NOT NULL,
1249 PRIMARY KEY (`member_id`, `application_id`)
1254 CREATE TABLE `social_friends` (
1255 `member_id` INTEGER UNSIGNED NOT NULL,
1256 `friend_id` INTEGER UNSIGNED NOT NULL,
1257 `relationship` INTEGER UNSIGNED NOT NULL,
1258 PRIMARY KEY (`member_id`, `friend_id`)
1262 # Friend requests table
1263 CREATE TABLE `social_friend_requests` (
1264 `member_id` INTEGER UNSIGNED NOT NULL,
1265 `friend_id` INTEGER UNSIGNED NOT NULL,
1266 `relationship` INTEGER UNSIGNED NOT NULL,
1267 PRIMARY KEY (`member_id`, `friend_id`)
1271 # Person Positions (jobs)
1272 CREATE TABLE `social_member_position` (
1273 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1274 `member_id` INTEGER UNSIGNED NOT NULL,
1275 `title` VARCHAR(255) NOT NULL,
1276 `company` VARCHAR(255) NOT NULL,
1277 `from` VARCHAR(10) NOT NULL DEFAULT 0,
1278 `to` VARCHAR(10) NOT NULL DEFAULT 0,
1285 CREATE TABLE `social_member_education` (
1286 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1287 `member_id` INTEGER UNSIGNED NOT NULL,
1288 `university` VARCHAR(255) NOT NULL,
1289 `country` VARCHAR(128),
1290 `province` VARCHAR(128),
1291 `degree` VARCHAR(64),
1292 `field` VARCHAR(64),
1293 `from` VARCHAR(10) NOT NULL DEFAULT 0,
1294 `to` VARCHAR(10) NOT NULL DEFAULT 0,
1300 # Person related web sites
1301 CREATE TABLE `social_member_websites` (
1302 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1303 `member_id` INTEGER UNSIGNED NOT NULL,
1304 `url` VARCHAR(255) NOT NULL,
1305 `site_name` VARCHAR(255),
1310 # Tracks visitor counts
1311 CREATE TABLE `social_member_track` (
1312 `member_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1313 `visitor_id` INTEGER UNSIGNED NOT NULL,
1314 `timestamp` TIMESTAMP NOT NULL,
1315 PRIMARY KEY (`member_id`, `visitor_id`, `timestamp`)
1319 # Person additional information cojoint with the members table
1320 CREATE TABLE `social_member_additional_information` (
1321 `member_id` INTEGER UNSIGNED NOT NULL,
1322 `expertise` VARCHAR(255) NOT NULL,
1324 `associations` TEXT,
1327 PRIMARY KEY (`member_id`)
1332 CREATE TABLE `social_member_contact` (
1333 `contact_id` int(10) unsigned NOT NULL auto_increment,
1334 `member_id` int(10) unsigned NOT NULL,
1335 `con_name` varchar(200) NOT NULL,
1336 `con_phone` varchar(15) NOT NULL,
1337 `con_email` varchar(50) NOT NULL,
1338 `con_address` text NOT NULL,
1339 PRIMARY KEY (`contact_id`)
1343 CREATE TABLE `social_member_representation` (
1344 `rep_id` int(10) unsigned NOT NULL auto_increment,
1345 `member_id` int(10) unsigned NOT NULL,
1346 `rep_name` varchar(200) NOT NULL,
1347 `rep_title` varchar(50) NOT NULL,
1348 `rep_phone` varchar(15) NOT NULL,
1349 `rep_email` varchar(50) NOT NULL,
1350 `rep_address` text NOT NULL,
1351 PRIMARY KEY (`rep_id`)
1356 CREATE TABLE `social_member_personal` (
1357 `per_id` int(10) unsigned NOT NULL auto_increment,
1358 `member_id` int(10) unsigned NOT NULL,
1359 `per_weight` varchar(200) NOT NULL,
1360 `per_height` varchar(50) NOT NULL,
1361 `per_hair` varchar(15) NOT NULL,
1362 `per_eyes` varchar(50) NOT NULL,
1363 `per_ethnicity` varchar(50) NOT NULL,
1364 `per_languages` varchar(255) NOT NULL,
1365 `per_disabilities` varchar(255) NOT NULL,
1366 PRIMARY KEY (`per_id`)
1369 # Privacy Control Preferences
1370 CREATE TABLE `social_privacy_preferences` (
1371 `member_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1373 PRIMARY KEY (`member_id`)
1377 # Social Group tables
1378 CREATE TABLE `social_groups` (
1379 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1380 `member_id` INTEGER UNSIGNED NOT NULL,
1381 `type_id` INTEGER UNSIGNED NOT NULL,
1382 `privacy` INTEGER UNSIGNED NOT NULL,
1383 `name` VARCHAR(255) NOT NULL,
1384 `logo` VARCHAR(255) NOT NULL,
1386 `created_date` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
1387 `last_updated` TIMESTAMP NOT NULL,
1392 CREATE TABLE `social_groups_activities` (
1393 `activity_id` INTEGER UNSIGNED NOT NULL,
1394 `group_id` INTEGER UNSIGNED NOT NULL,
1395 PRIMARY KEY (`activity_id`, `group_id`)
1399 CREATE TABLE `social_groups_members` (
1400 `group_id` INTEGER UNSIGNED NOT NULL,
1401 `member_id` INTEGER UNSIGNED NOT NULL,
1402 PRIMARY KEY (`group_id`, `member_id`)
1406 CREATE TABLE `social_groups_invitations` (
1407 `sender_id` INTEGER UNSIGNED NOT NULL,
1408 `member_id` INTEGER UNSIGNED NOT NULL,
1409 `group_id` INTEGER UNSIGNED NOT NULL,
1410 PRIMARY KEY (`sender_id`, `member_id`, `group_id`)
1414 CREATE TABLE `social_groups_requests` (
1415 `sender_id` INTEGER UNSIGNED NOT NULL,
1416 `member_id` INTEGER UNSIGNED NOT NULL,
1417 `group_id` INTEGER UNSIGNED NOT NULL,
1418 PRIMARY KEY (`sender_id`, `member_id`, `group_id`)
1422 CREATE TABLE `social_groups_types` (
1423 `type_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1424 `title` VARCHAR(127) NOT NULL,
1425 PRIMARY KEY (`type_id`)
1429 # CREATE TABLE `social_groups_forums` (
1430 # `group_id` INTEGER UNSIGNED NOT NULL,
1431 # `forum_id` INTEGER UNSIGNED NOT NULL,
1432 # PRIMARY KEY (`group_id`, `forum_id`)
1436 # Groups message board
1437 CREATE TABLE `social_groups_board` (
1438 `id` int(10) unsigned NOT NULL auto_increment,
1439 `member_id` int(10) unsigned NOT NULL,
1440 `group_id` int(10) unsigned NOT NULL,
1442 `created_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1448 CREATE TABLE `social_user_settings` (
1449 `member_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1450 `app_settings` TEXT,
1451 PRIMARY KEY (`member_id`)
1456 #====== Initial Data ========
1457 INSERT INTO social_groups_types SET title='business', type_id=1;
1458 INSERT INTO social_groups_types SET title='common_interest', type_id=2;
1459 INSERT INTO social_groups_types SET title='entertainment_arts', type_id=3;
1460 INSERT INTO social_groups_types SET title='geography', type_id=4;
1461 INSERT INTO social_groups_types SET title='internet_technology', type_id=5;
1462 INSERT INTO social_groups_types SET title='organization', type_id=6;
1463 INSERT INTO social_groups_types SET title='music', type_id=7;
1464 INSERT INTO social_groups_types SET title='sports_recreation', type_id=8;
1466 # END Social Networking setup
1468 # Login attempt control table
1469 CREATE TABLE `member_login_attempt` (
1470 `login` varchar(20) NOT NULL,
1471 `attempt` tinyint(3) unsigned default NULL,
1472 `expiry` int(10) unsigned default NULL,
1473 PRIMARY KEY (`login`)
1476 # --------------------------------------------------------
1477 # Adding feature of blog subsription
1478 # Table structure for table `blog_subscription`
1480 CREATE TABLE `blog_subscription` (
1481 `group_id` MEDIUMINT NOT NULL ,
1482 `member_id` MEDIUMINT NOT NULL ,
1483 PRIMARY KEY (group_id,member_id)
1486 # END Adding feature of blog subsription
1488 # --------------------------------------------------------
1489 # Adding feature of content pre-requisites
1490 # Table structure for table `content_prerequisites`
1492 CREATE TABLE `content_prerequisites` (
1493 `content_id` MEDIUMINT NOT NULL,
1494 `type` varchar(50) NOT NULL DEFAULT '',
1495 `item_id` MEDIUMINT NOT NULL,
1496 PRIMARY KEY (content_id,type, item_id)
1499 # END Adding feature of content pre-requisites