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( 30 ) NOT NULL default '',
13 `real_name` VARCHAR( 30 ) 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',
27 `details` TEXT NOT NULL,
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(60) 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,
56 `description` varchar(100) NOT NULL default '',
57 `file_size` int(10) unsigned NOT NULL default 0,
58 `system_file_name` varchar(50) NOT NULL default '',
59 `file_name` varchar(150) NOT NULL default '',
60 `contents` TEXT NOT NULL ,
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(100) NOT NULL default '',
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 ,
92 `comment` TEXT 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 '',
103 `value` CHAR( 255 ) 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',
119 `keywords` varchar(100) NOT NULL default '',
120 `content_path` varchar(100) NOT NULL default '',
121 `title` varchar(150) NOT NULL default '',
122 `text` text NOT NULL ,
123 PRIMARY KEY (`content_id`),
124 KEY `course_id` (`course_id`)
128 # --------------------------------------------------------
129 # Table structure for table `course_access`
131 CREATE TABLE `course_access` (
132 `password` char(8) NOT NULL ,
133 `course_id` mediumint(8) unsigned NOT NULL ,
134 `expiry_date` timestamp NOT NULL ,
135 `enabled` tinyint(4) NOT NULL ,
136 PRIMARY KEY ( `password` ) ,
140 # --------------------------------------------------------
141 # Table structure for table `course_cats`
143 CREATE TABLE `course_cats` (
144 `cat_id` mediumint(8) unsigned NOT NULL auto_increment,
145 `cat_name` varchar(100) NOT NULL default '',
146 `cat_parent` mediumint(8) unsigned NOT NULL default '0',
147 `theme` VARCHAR(30) NOT NULL default '',
148 PRIMARY KEY (`cat_id`)
152 # --------------------------------------------------------
153 # Table structure for table `course_enrollment`
155 CREATE TABLE `course_enrollment` (
156 `member_id` mediumint(8) unsigned NOT NULL default '0',
157 `course_id` mediumint(8) unsigned NOT NULL default '0',
158 `approved` enum('y','n','a') NOT NULL default 'n',
159 `privileges` INT(10) unsigned NOT NULL default '0',
160 `role` varchar(35) NOT NULL default '',
161 `last_cid` mediumint(8) unsigned NOT NULL default '0',
162 PRIMARY KEY (`member_id`,`course_id`)
167 # --------------------------------------------------------
168 # Table structure for table `course_stats`
170 CREATE TABLE `course_stats` (
171 `course_id` mediumint(8) unsigned NOT NULL default '0',
172 `login_date` date NOT NULL default '0000-00-00',
173 `guests` mediumint(8) unsigned NOT NULL default '0',
174 `members` mediumint(8) unsigned NOT NULL default '0',
175 PRIMARY KEY (`course_id`,`login_date`)
178 # --------------------------------------------------------
179 # Table structure for table `courses`
181 CREATE TABLE `courses` (
182 `course_id` mediumint(8) unsigned NOT NULL auto_increment,
183 `member_id` mediumint(8) unsigned NOT NULL default '0',
184 `cat_id` mediumint(8) unsigned NOT NULL default '0',
185 `content_packaging` enum('none','top','all') NOT NULL default 'top',
186 `access` enum('public','protected','private') NOT NULL default 'public',
187 `created_date` date NOT NULL default '0000-00-00',
188 `title` varchar(100) NOT NULL default '',
189 `description` text NOT NULL ,
190 `notify` tinyint(4) NOT NULL default '0',
191 `max_quota` varchar(30) NOT NULL default '',
192 `max_file_size` varchar(30) NOT NULL default '',
193 `hide` tinyint(4) NOT NULL default '0',
194 `preferences` text NOT NULL ,
195 `header` text NOT NULL ,
196 `footer` text NOT NULL ,
197 `copyright` text NOT NULL ,
198 `banner_text` text NOT NULL ,
199 `banner_styles` text NOT NULL ,
200 `primary_language` varchar(5) NOT NULL default '',
201 `rss` tinyint NOT NULL default 0,
202 `icon` varchar(20) NOT NULL default '',
203 `home_links` text NOT NULL ,
204 `main_links` text NOT NULL ,
205 `side_menu` VARCHAR( 255 ) NOT NULL default '',
206 `release_date` datetime NOT NULL default '0000-00-00 00:00:00',
207 `banner` TEXT NOT NULL ,
208 PRIMARY KEY (`course_id`)
211 # --------------------------------------------------------
212 # Table structure for table `faq_topics`
214 CREATE TABLE `faq_topics` (
215 `topic_id` mediumint(8) NOT NULL auto_increment,
216 `course_id` mediumint(8) unsigned NOT NULL default '0',
217 `name` varchar(250) NOT NULL default '',
218 KEY `course_id` (`course_id`),
219 PRIMARY KEY (`topic_id`)
222 # --------------------------------------------------------
223 # Table structure for table `faq_entries`
224 CREATE TABLE `faq_entries` (
225 `entry_id` mediumint(8) NOT NULL auto_increment,
226 `topic_id` mediumint(8) NOT NULL default '0',
227 `revised_date` TIMESTAMP NOT NULL,
228 `approved` tinyint(4) NOT NULL default '0',
229 `question` varchar(250) NOT NULL default '',
230 `answer` text NOT NULL ,
231 PRIMARY KEY (`entry_id`)
234 # --------------------------------------------------------
235 # Table structure for table `feeds`
236 CREATE TABLE `feeds` (
237 `feed_id` mediumint(8) unsigned NOT NULL auto_increment,
238 `url` varchar(255) NOT NULL default '',
239 PRIMARY KEY (`feed_id`)
242 # --------------------------------------------------------
245 # Table structure for table `file_storage_groups`
248 CREATE TABLE `file_storage_groups` (
249 `group_id` MEDIUMINT UNSIGNED NOT NULL default 0,
250 PRIMARY KEY ( `group_id` )
254 # Table structure for table `files`
257 CREATE TABLE `files` (
258 `file_id` mediumint(8) unsigned NOT NULL auto_increment,
259 `owner_type` tinyint(3) unsigned NOT NULL default '0',
260 `owner_id` mediumint(8) unsigned NOT NULL default '0',
261 `member_id` mediumint(8) unsigned NOT NULL default '0',
262 `folder_id` mediumint(8) unsigned NOT NULL default '0',
263 `parent_file_id` mediumint(8) unsigned NOT NULL default '0',
264 `date` TIMESTAMP NOT NULL,
265 `num_comments` tinyint(3) unsigned NOT NULL default '0',
266 `num_revisions` tinyint(3) unsigned NOT NULL default '0',
267 `file_name` varchar(80) NOT NULL default '',
268 `file_size` int(11) NOT NULL default '0',
269 `description` text NOT NULL ,
270 PRIMARY KEY (`file_id`)
273 # --------------------------------------------------------
276 # Table structure for table `files_comments`
279 CREATE TABLE `files_comments` (
280 `comment_id` mediumint(8) unsigned NOT NULL auto_increment,
281 `file_id` mediumint(8) unsigned NOT NULL default '0',
282 `member_id` mediumint(8) unsigned NOT NULL default '0',
283 `date` TIMESTAMP NOT NULL,
284 `comment` text NOT NULL ,
285 PRIMARY KEY (`comment_id`)
288 # --------------------------------------------------------
291 # Table structure for table `folders`
294 CREATE TABLE `folders` (
295 `folder_id` mediumint(8) unsigned NOT NULL auto_increment,
296 `parent_folder_id` mediumint(8) unsigned NOT NULL default '0',
297 `owner_type` tinyint(3) unsigned NOT NULL default '0',
298 `owner_id` mediumint(8) unsigned NOT NULL default '0',
299 `title` varchar(30) NOT NULL default '',
300 PRIMARY KEY (`folder_id`)
304 # --------------------------------------------------------
305 # Table structure for table `forums`
307 CREATE TABLE `forums` (
308 `forum_id` mediumint(8) unsigned NOT NULL auto_increment,
309 `title` varchar(60) NOT NULL default '',
310 `description` text NOT NULL ,
311 `num_topics` MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL ,
312 `num_posts` MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL ,
313 `last_post` TIMESTAMP NOT NULL,
314 `mins_to_edit` SMALLINT UNSIGNED NOT NULL DEFAULT '0',
315 PRIMARY KEY (`forum_id`)
319 # --------------------------------------------------------
320 # Table structure for table `forums_accessed`
322 CREATE TABLE `forums_accessed` (
323 `post_id` mediumint(8) unsigned NOT NULL default '0',
324 `member_id` mediumint(8) unsigned NOT NULL default '0',
325 `last_accessed` timestamp NOT NULL,
326 `subscribe` tinyint(4) NOT NULL default '0',
327 PRIMARY KEY (`post_id`,`member_id`)
330 # --------------------------------------------------------
331 # Table structure for table `forums_courses`
333 CREATE TABLE `forums_courses` (
334 `forum_id` MEDIUMINT UNSIGNED NOT NULL default '0',
335 `course_id` MEDIUMINT UNSIGNED NOT NULL default '0',
336 PRIMARY KEY (`forum_id`,`course_id`),
337 KEY `course_id` (`course_id`)
341 # --------------------------------------------------------
342 # Table structure for table `forums_groups`
344 CREATE TABLE `forums_groups` (
345 `forum_id` mediumint( 8 ) unsigned NOT NULL default '0',
346 `group_id` mediumint( 8 ) unsigned NOT NULL default '0',
347 PRIMARY KEY ( `forum_id` , `group_id` ) ,
348 KEY `group_id` ( `group_id` )
351 # --------------------------------------------------------
352 # Table structure for table `forums_subscriptions`
355 CREATE TABLE `forums_subscriptions` (
356 forum_id mediumint(8) unsigned NOT NULL default '0',
357 member_id mediumint(8) unsigned NOT NULL default '0',
358 PRIMARY KEY (`forum_id`,`member_id`)
362 # --------------------------------------------------------
363 # Table structure for table `forums_threads`
365 CREATE TABLE `forums_threads` (
366 `post_id` mediumint(8) unsigned NOT NULL auto_increment,
367 `parent_id` mediumint(8) unsigned NOT NULL default '0',
368 `member_id` mediumint(8) unsigned NOT NULL default '0',
369 `forum_id` mediumint(8) unsigned NOT NULL default '0',
370 `login` varchar(20) NOT NULL default '',
371 `last_comment` TIMESTAMP NOT NULL,
372 `num_comments` mediumint(8) unsigned NOT NULL default '0',
373 `subject` varchar(100) NOT NULL default '',
374 `body` text NOT NULL ,
375 `date` TIMESTAMP NOT NULL,
376 `locked` tinyint(4) NOT NULL default '0',
377 `sticky` tinyint(4) NOT NULL default '0',
378 PRIMARY KEY (`post_id`)
381 # --------------------------------------------------------
382 # Table structure for table `glossary`
384 CREATE TABLE `glossary` (
385 `word_id` mediumint(8) unsigned NOT NULL auto_increment,
386 `course_id` mediumint(8) unsigned NOT NULL default '0',
387 `word` varchar(60) NOT NULL default '',
388 `definition` text NOT NULL ,
389 `related_word_id` mediumint(8) unsigned NOT NULL default '0',
390 PRIMARY KEY (`word_id`),
391 KEY `course_id` (`course_id`)
394 # --------------------------------------------------------
395 # Table structure for table `groups`
397 CREATE TABLE `groups` (
398 `group_id` mediumint(8) unsigned NOT NULL auto_increment,
399 `type_id` mediumint(8) unsigned NOT NULL default '0',
400 `title` varchar(20) NOT NULL default '',
401 `description` text NOT NULL ,
402 `modules` varchar(100) NOT NULL default '',
403 PRIMARY KEY (`group_id`)
407 # --------------------------------------------------------
408 # Table structure for table `groups_members`
410 CREATE TABLE `groups_members` (
411 `group_id` MEDIUMINT UNSIGNED NOT NULL default '0',
412 `member_id` MEDIUMINT UNSIGNED NOT NULL default '0',
413 PRIMARY KEY (`group_id`,`member_id`)
416 # --------------------------------------------------------
417 # Table structure for table `groups_types` (since 1.5.3)
419 CREATE TABLE `groups_types` (
420 `type_id` mediumint(8) unsigned NOT NULL auto_increment,
421 `course_id` mediumint(8) unsigned NOT NULL default '0',
422 `title` varchar(80) NOT NULL default '',
423 PRIMARY KEY (`type_id`),
424 KEY `course_id` (`course_id`)
427 # --------------------------------------------------------
428 # Table structure for table `handbook_notes`
430 CREATE TABLE `handbook_notes` (
431 `note_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,
432 `date` TIMESTAMP NOT NULL,
433 `section` VARCHAR( 15 ) NOT NULL default '',
434 `page` VARCHAR( 50 ) NOT NULL default '',
435 `approved` tinyint NOT NULL default 0,
436 `email` VARCHAR( 50 ) NOT NULL default '',
437 `note` TEXT NOT NULL ,
438 PRIMARY KEY ( `note_id` )
442 # --------------------------------------------------------
443 # Table structure for table `instructor_approvals`
445 CREATE TABLE `instructor_approvals` (
446 `member_id` mediumint(8) unsigned NOT NULL default '0',
447 `request_date` TIMESTAMP NOT NULL,
448 `notes` text NOT NULL ,
449 PRIMARY KEY (`member_id`)
453 CREATE TABLE `languages` (
454 `language_code` varchar(5) NOT NULL default '',
455 `char_set` varchar(20) NOT NULL default '',
456 `direction` varchar(4) NOT NULL default '',
457 `reg_exp` varchar(31) NOT NULL default '',
458 `native_name` varchar(20) NOT NULL default '',
459 `english_name` varchar(20) NOT NULL default '',
460 `status` TINYINT UNSIGNED DEFAULT '0' NOT NULL,
461 PRIMARY KEY (`language_code`,`char_set`)
465 # Dumping data for table `languages`
468 INSERT INTO `languages` VALUES ('en', 'iso-8859-1', 'ltr', 'en([-_][[:alpha:]]{2})?|english', 'English', 'English', 3);
471 # Table structure for table `links_categories`
473 CREATE TABLE `links_categories` (
474 `cat_id` mediumint(8) unsigned NOT NULL auto_increment,
475 `owner_type` tinyint(4) NOT NULL default '0',
476 `owner_id` mediumint(8) unsigned NOT NULL default '0',
477 `name` varchar(100) NOT NULL default '',
478 `parent_id` mediumint(8) unsigned default NULL,
479 PRIMARY KEY (`cat_id`),
480 KEY `owner_id` (`owner_id`)
484 # --------------------------------------------------------
485 # Table structure for table `links`
487 CREATE TABLE `links` (
488 `link_id` mediumint(8) unsigned NOT NULL auto_increment,
489 `cat_id` mediumint(8) unsigned NOT NULL default '0',
490 `Url` varchar(255) NOT NULL default '',
491 `LinkName` varchar(64) NOT NULL default '',
492 `Description` varchar(255) NOT NULL default '',
493 `Approved` tinyint(8) default '0',
494 `SubmitName` varchar(64) NOT NULL default '',
495 `SubmitEmail` varchar(64) NOT NULL default '',
496 `SubmitDate` date NOT NULL default '0000-00-00',
497 `hits` int(11) default '0',
498 PRIMARY KEY (`link_id`)
501 # --------------------------------------------------------
502 # Table structure for table `language_pages`
504 CREATE TABLE `language_pages` (
505 `term` varchar(30) NOT NULL default '',
506 `page` varchar(50) NOT NULL default '',
507 PRIMARY KEY (`term`,`page`)
510 # --------------------------------------------------------
511 # Table structure for table `master_list`
513 CREATE TABLE `master_list` (
514 `public_field` CHAR( 30 ) NOT NULL default '',
515 `hash_field` CHAR( 40 ) NOT NULL default '',
516 `member_id` MEDIUMINT UNSIGNED NOT NULL default 0,
517 PRIMARY KEY ( `public_field` )
520 # --------------------------------------------------------
521 # Table structure for table `members`
523 CREATE TABLE `members` (
524 `member_id` mediumint(8) unsigned NOT NULL auto_increment,
525 `login` varchar(20) NOT NULL default '',
526 `password` varchar(20) NOT NULL default '',
527 `email` varchar(50) NOT NULL default '',
528 `website` varchar(200) NOT NULL default '',
529 `first_name` varchar(100) NOT NULL default '',
530 `second_name` varchar(30) NOT NULL default '',
531 `last_name` varchar(100) NOT NULL default '',
532 `dob` date NOT NULL default '0000-00-00',
533 `gender` enum('m','f','n') NOT NULL default 'n',
534 `address` varchar(255) NOT NULL default '',
535 `postal` varchar(15) NOT NULL default '',
536 `city` varchar(50) NOT NULL default '',
537 `province` varchar(50) NOT NULL default '',
538 `country` varchar(50) NOT NULL default '',
539 `phone` varchar(15) NOT NULL default '',
540 `status` tinyint(4) NOT NULL default '0',
541 `preferences` text NOT NULL ,
542 `creation_date` TIMESTAMP NOT NULL,
543 `language` varchar(5) NOT NULL default '',
544 `inbox_notify` tinyint(3) unsigned NOT NULL default '0',
545 `private_email` TINYINT DEFAULT '1' NOT NULL,
546 `last_login` TIMESTAMP NOT NULL,
547 PRIMARY KEY (`member_id`),
548 UNIQUE KEY `login` (`login`)
551 # --------------------------------------------------------
552 # Table structure for table `member_track`
554 CREATE TABLE `member_track` (
555 `member_id` mediumint(8) unsigned NOT NULL default '0',
556 `course_id` mediumint(8) unsigned NOT NULL default '0',
557 `content_id` mediumint(8) unsigned NOT NULL default '0',
558 `counter` mediumint(8) unsigned NOT NULL default '0',
559 `duration` mediumint(8) unsigned NOT NULL default '0',
560 `last_accessed` TIMESTAMP NULL,
561 KEY `member_id` (`member_id`),
562 KEY `content_id` (`content_id`)
565 # --------------------------------------------------------
566 # Table structure for table `messages`
568 CREATE TABLE `messages` (
569 `message_id` mediumint(8) unsigned NOT NULL auto_increment,
570 `course_id` mediumint(8) unsigned NOT NULL default '0',
571 `from_member_id` mediumint(8) unsigned NOT NULL default '0',
572 `to_member_id` mediumint(8) unsigned NOT NULL default '0',
573 `date_sent` TIMESTAMP NOT NULL,
574 `new` tinyint(4) NOT NULL default '0',
575 `replied` tinyint(4) NOT NULL default '0',
576 `subject` varchar(150) NOT NULL default '',
577 `body` text NOT NULL ,
578 PRIMARY KEY (`message_id`),
579 KEY `to_member_id` (`to_member_id`)
582 # --------------------------------------------------------
583 # Table structure for table `messages_sent` (since 1.5.4)
585 CREATE TABLE `messages_sent` (
586 `message_id` mediumint( 8 ) unsigned NOT NULL AUTO_INCREMENT ,
587 `course_id` mediumint( 8 ) unsigned NOT NULL default '0',
588 `from_member_id` mediumint( 8 ) unsigned NOT NULL default '0',
589 `to_member_id` mediumint( 8 ) unsigned NOT NULL default '0',
590 `date_sent` timestamp NOT NULL ,
591 `subject` varchar( 150 ) NOT NULL default '',
592 `body` text NOT NULL ,
593 PRIMARY KEY ( `message_id` ) ,
594 KEY `from_member_id` ( `from_member_id` )
597 # --------------------------------------------------------
598 # Table structure for table `modules` (since 1.5.2)
600 CREATE TABLE `modules` (
601 `dir_name` VARCHAR( 50 ) NOT NULL default '',
602 `status` TINYINT NOT NULL default 0,
603 `privilege` INT UNSIGNED NOT NULL default 0,
604 `admin_privilege` MEDIUMINT UNSIGNED NOT NULL default 0,
605 `cron_interval` SMALLINT UNSIGNED DEFAULT '0' NOT NULL ,
606 `cron_last_run` INT UNSIGNED DEFAULT '0' NOT NULL,
607 PRIMARY KEY ( `dir_name` )
610 INSERT INTO `modules` VALUES ('_core/properties', 2, 1, 0, 0, 0);
611 INSERT INTO `modules` VALUES ('_standard/statistics', 2, 1, 0, 0, 0);
612 INSERT INTO `modules` VALUES ('_core/content', 2, 2, 0, 0, 0);
613 INSERT INTO `modules` VALUES ('_core/glossary', 2, 4, 0, 0, 0);
614 INSERT INTO `modules` VALUES ('_standard/tests', 2, 8, 0, 0, 0);
615 INSERT INTO `modules` VALUES ('_standard/chat', 2, 16, 0, 0, 0);
616 INSERT INTO `modules` VALUES ('_core/file_manager', 2, 32, 0, 0, 0);
617 INSERT INTO `modules` VALUES ('_standard/links', 2, 64, 0, 0, 0);
618 INSERT INTO `modules` VALUES ('_standard/forums', 2, 128, 16, 0, 0);
619 INSERT INTO `modules` VALUES ('_standard/student_tools', 2, 256, 0, 0, 0);
620 INSERT INTO `modules` VALUES ('_core/enrolment', 2, 512, 512, 0, 0);
621 INSERT INTO `modules` VALUES ('_standard/course_email', 2, 1024, 0, 0, 0);
622 INSERT INTO `modules` VALUES ('_standard/announcements', 2, 2048, 0, 0, 0);
623 # INSERT INTO `modules` VALUES ('acollab', 2, 8192+4096, 0, 0, 0);
624 INSERT INTO `modules` VALUES ('_standard/polls', 2, 16384, 0, 0, 0);
625 INSERT INTO `modules` VALUES ('_standard/faq', 2, 32768, 0, 0, 0);
626 INSERT INTO `modules` VALUES ('_core/groups', 2, 65536, 0, 0, 0);
627 INSERT INTO `modules` VALUES ('_standard/reading_list', 2, 131072, 0, 0, 0);
628 INSERT INTO `modules` VALUES ('_standard/file_storage', 2, 262144, 0, 0, 0);
629 INSERT INTO `modules` VALUES ('_standard/assignments', 2, 524288, 0, 0, 0);
630 INSERT INTO `modules` VALUES ('_core/users', 2, 0, 2, 0, 0);
631 INSERT INTO `modules` VALUES ('_core/courses', 2, 0, 4, 0, 0);
632 INSERT INTO `modules` VALUES ('_core/backups', 2, 1, 8, 0, 0);
633 INSERT INTO `modules` VALUES ('_core/cats_categories', 2, 0, 32, 0, 0);
634 INSERT INTO `modules` VALUES ('_core/languages', 2, 0, 64, 1440, 0);
635 INSERT INTO `modules` VALUES ('_core/themes', 2, 0, 128, 0, 0);
636 INSERT INTO `modules` VALUES ('_standard/rss_feeds', 2, 0, 256, 0, 0);
637 INSERT INTO `modules` VALUES ('_standard/directory', 2, 0, 0, 0, 0);
638 INSERT INTO `modules` VALUES ('_standard/tile_search', 2, 0, 0, 0, 0);
639 INSERT INTO `modules` VALUES ('_standard/sitemap', 2, 0, 0, 0, 0);
640 INSERT INTO `modules` VALUES ('_standard/tracker', 2, 0, 0, 0, 0);
641 INSERT INTO `modules` VALUES ('_core/content_packaging', 2, 0, 0, 0, 0);
642 INSERT INTO `modules` VALUES ('_standard/google_search', 2, 0, 0, 0, 0);
643 INSERT INTO `modules` VALUES ('_standard/blogs', 2, 0, 0, 0, 0);
644 INSERT INTO `modules` VALUES ('_standard/profile_pictures', 2, 0, 0, 0, 0);
647 # --------------------------------------------------------
648 # Table structure for table `news`
650 CREATE TABLE `news` (
651 `news_id` mediumint(8) unsigned NOT NULL auto_increment,
652 `course_id` mediumint(8) unsigned NOT NULL default '0',
653 `member_id` mediumint(8) unsigned NOT NULL default '0',
654 `date` TIMESTAMP NOT NULL,
655 `formatting` tinyint(4) NOT NULL default '0',
656 `title` varchar(100) NOT NULL default '',
657 `body` text NOT NULL ,
658 PRIMARY KEY (`news_id`)
661 # --------------------------------------------------------
663 # Table structure for table `polls`
664 CREATE TABLE `polls` (
665 `poll_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,
666 `course_id` MEDIUMINT UNSIGNED NOT NULL default 0,
667 `question` VARCHAR( 100 ) NOT NULL default '',
668 `created_date` TIMESTAMP NOT NULL,
669 `total` SMALLINT UNSIGNED NOT NULL default '0',
670 `choice1` VARCHAR( 100 ) NOT NULL default '',
671 `count1` SMALLINT UNSIGNED NOT NULL default '0',
672 `choice2` VARCHAR( 100 ) NOT NULL default '',
673 `count2` SMALLINT UNSIGNED NOT NULL default '0',
674 `choice3` VARCHAR( 100 ) NOT NULL default '',
675 `count3` SMALLINT UNSIGNED NOT NULL default '0',
676 `choice4` VARCHAR( 100 ) NOT NULL default '',
677 `count4` SMALLINT UNSIGNED NOT NULL default '0',
678 `choice5` VARCHAR( 100 ) NOT NULL default '',
679 `count5` SMALLINT UNSIGNED NOT NULL default '0',
680 `choice6` VARCHAR( 100 ) NOT NULL default '',
681 `count6` SMALLINT UNSIGNED NOT NULL default '0',
682 `choice7` VARCHAR( 100 ) NOT NULL default '',
683 `count7` SMALLINT UNSIGNED NOT NULL default '0',
684 PRIMARY KEY ( `poll_id` ) ,
685 INDEX ( `course_id` )
688 # --------------------------------------------------------
689 # Table structure for table `mail_queue`
692 CREATE TABLE `mail_queue` (
693 `mail_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,
694 `to_email` VARCHAR( 50 ) NOT NULL default '',
695 `to_name` VARCHAR( 50 ) NOT NULL default '',
696 `from_email` VARCHAR( 50 ) NOT NULL default '',
697 `from_name` VARCHAR( 50 ) NOT NULL default '',
698 `char_set` VARCHAR( 20 ) NOT NULL default '',
699 `subject` VARCHAR( 200 ) NOT NULL default '',
700 `body` TEXT NOT NULL ,
701 PRIMARY KEY ( `mail_id` )
704 # --------------------------------------------------------
706 # Table structure for table `polls_members`
708 CREATE TABLE `polls_members` (
709 `poll_id` MEDIUMINT UNSIGNED NOT NULL default '0',
710 `member_id` MEDIUMINT UNSIGNED NOT NULL default '0',
711 PRIMARY KEY ( `poll_id` , `member_id` )
714 # --------------------------------------------------------
716 # Table structure for table `related_content`
717 CREATE TABLE `related_content` (
718 `content_id` mediumint(8) unsigned NOT NULL default '0',
719 `related_content_id` mediumint(8) unsigned NOT NULL default '0',
720 PRIMARY KEY (`content_id`,`related_content_id`)
723 # --------------------------------------------------------
725 # Table structure for table `reading_list`
727 CREATE TABLE `reading_list` (
728 `reading_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
729 `course_id` MEDIUMINT UNSIGNED NOT NULL default 0,
730 `resource_id` MEDIUMINT UNSIGNED NOT NULL default 0,
731 `required` enum('required','optional') NOT NULL DEFAULT 'required',
732 `date_start` DATE NOT NULL DEFAULT '0000-00-00',
733 `date_end` DATE NOT NULL DEFAULT '0000-00-00',
734 `comment` text NOT NULL ,
735 PRIMARY KEY (`reading_id`),
740 # Table structure for table `external_resources`
742 CREATE TABLE `external_resources` (
743 `resource_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
744 `course_id` MEDIUMINT UNSIGNED NOT NULL default 0,
745 `type` TINYINT UNSIGNED NOT NULL DEFAULT 0,
746 `title` varchar(255) NOT NULL DEFAULT '',
747 `author` varchar(150) NOT NULL DEFAULT '',
748 `publisher` varchar(150) NOT NULL DEFAULT '',
749 `date` varchar(20) NOT NULL DEFAULT '',
750 `comments` varchar(255) NOT NULL DEFAULT '',
751 `id` varchar(50) NOT NULL DEFAULT '',
752 `url` varchar(255) NOT NULL DEFAULT '',
753 PRIMARY KEY (`resource_id`),
758 # --------------------------------------------------------
759 # Table structure for table `tests`
761 CREATE TABLE `tests` (
762 `test_id` mediumint(8) unsigned NOT NULL auto_increment,
763 `course_id` mediumint(8) unsigned NOT NULL default '0',
764 `title` varchar(100) NOT NULL default '',
765 `format` tinyint(4) NOT NULL default '0',
766 `start_date` datetime NOT NULL default '0000-00-00 00:00:00',
767 `end_date` datetime NOT NULL default '0000-00-00 00:00:00',
768 `randomize_order` tinyint(4) NOT NULL default '0',
769 `num_questions` tinyint(3) unsigned NOT NULL default '0',
770 `instructions` text NOT NULL ,
771 `content_id` mediumint(8) NOT NULL default '0',
772 `result_release` tinyint(4) unsigned NOT NULL default '0',
773 `random` tinyint(4) unsigned NOT NULL default '0',
774 `difficulty` tinyint(4) unsigned NOT NULL default '0',
775 `num_takes` tinyint(4) unsigned NOT NULL default '0',
776 `anonymous` tinyint(4) NOT NULL default '0',
777 `out_of` varchar(4) NOT NULL default '',
778 `guests` TINYINT NOT NULL DEFAULT '0',
779 PRIMARY KEY (`test_id`)
783 # --------------------------------------------------------
784 # Table structure for table `tests_answers`
786 CREATE TABLE `tests_answers` (
787 `result_id` mediumint(8) unsigned NOT NULL default '0',
788 `question_id` mediumint(8) unsigned NOT NULL default '0',
789 `member_id` mediumint(8) unsigned NOT NULL default '0',
790 `answer` text NOT NULL ,
791 `score` varchar(5) NOT NULL default '',
792 `notes` text NOT NULL ,
793 PRIMARY KEY (`result_id`,`question_id`,`member_id`)
797 # --------------------------------------------------------
798 # Table structure for table `tests_groups`
800 CREATE TABLE `tests_groups` (
801 `test_id` MEDIUMINT UNSIGNED NOT NULL default '0',
802 `group_id` MEDIUMINT UNSIGNED NOT NULL default '0',
803 PRIMARY KEY (`test_id`,`group_id`),
804 KEY `test_id` (`test_id`)
808 # --------------------------------------------------------
809 # Table structure for table `tests_questions`
811 CREATE TABLE `tests_questions` (
812 `question_id` mediumint(8) unsigned NOT NULL auto_increment,
813 `category_id` mediumint(8) unsigned NOT NULL default '0',
814 `course_id` mediumint(8) unsigned NOT NULL default '0',
815 `type` tinyint(3) unsigned NOT NULL default '0',
816 `feedback` text NOT NULL ,
817 `question` text NOT NULL ,
818 `choice_0` varchar(255) NOT NULL default '',
819 `choice_1` varchar(255) NOT NULL default '',
820 `choice_2` varchar(255) NOT NULL default '',
821 `choice_3` varchar(255) NOT NULL default '',
822 `choice_4` varchar(255) NOT NULL default '',
823 `choice_5` varchar(255) NOT NULL default '',
824 `choice_6` varchar(255) NOT NULL default '',
825 `choice_7` varchar(255) NOT NULL default '',
826 `choice_8` varchar(255) NOT NULL default '',
827 `choice_9` varchar(255) NOT NULL default '',
828 `answer_0` tinyint(4) NOT NULL default '0',
829 `answer_1` tinyint(4) NOT NULL default '0',
830 `answer_2` tinyint(4) NOT NULL default '0',
831 `answer_3` tinyint(4) NOT NULL default '0',
832 `answer_4` tinyint(4) NOT NULL default '0',
833 `answer_5` tinyint(4) NOT NULL default '0',
834 `answer_6` tinyint(4) NOT NULL default '0',
835 `answer_7` tinyint(4) NOT NULL default '0',
836 `answer_8` tinyint(4) NOT NULL default '0',
837 `answer_9` tinyint(4) NOT NULL default '0',
838 `option_0` VARCHAR( 255 ) NOT NULL default '',
839 `option_1` VARCHAR( 255 ) NOT NULL default '',
840 `option_2` VARCHAR( 255 ) NOT NULL default '',
841 `option_3` VARCHAR( 255 ) NOT NULL default '',
842 `option_4` VARCHAR( 255 ) NOT NULL default '',
843 `option_5` VARCHAR( 255 ) NOT NULL default '',
844 `option_6` VARCHAR( 255 ) NOT NULL default '',
845 `option_7` VARCHAR( 255 ) NOT NULL default '',
846 `option_8` VARCHAR( 255 ) NOT NULL default '',
847 `option_9` VARCHAR( 255 ) NOT NULL default '',
848 `properties` tinyint(4) NOT NULL default '0',
849 `content_id` mediumint(8) NOT NULL,
850 PRIMARY KEY (`question_id`),
851 KEY `category_id` (category_id)
854 # --------------------------------------------------------
855 # Table structure for table `tests_questions_assoc`
857 CREATE TABLE `tests_questions_assoc` (
858 `test_id` mediumint(8) unsigned NOT NULL default '0',
859 `question_id` mediumint(8) unsigned NOT NULL default '0',
860 `weight` varchar(4) NOT NULL default '',
861 `ordering` mediumint(8) unsigned NOT NULL default '0',
862 `required` tinyint(3) unsigned NOT NULL default '0',
863 PRIMARY KEY (`test_id`,`question_id`),
864 KEY `test_id` (`test_id`)
867 # --------------------------------------------------------
868 # Table structure for table `tests_questions_categories`
870 CREATE TABLE `tests_questions_categories` (
871 `category_id` mediumint(8) unsigned NOT NULL auto_increment,
872 `course_id` mediumint(8) unsigned NOT NULL default '0',
873 `title` char(50) NOT NULL default '',
874 PRIMARY KEY (`category_id`),
875 KEY `course_id` (`course_id`)
878 # --------------------------------------------------------
879 # Table structure for table `tests_results`
881 CREATE TABLE `tests_results` (
882 `result_id` mediumint(8) unsigned NOT NULL auto_increment,
883 `test_id` mediumint(8) unsigned NOT NULL default '0',
884 `member_id` mediumint(8) unsigned NOT NULL default '0',
885 `date_taken` TIMESTAMP NOT NULL,
886 `final_score` char(5) NOT NULL default '',
887 PRIMARY KEY (`result_id`),
888 KEY `test_id` (`test_id`)
891 # --------------------------------------------------------
892 # Table structure for table `themes`
895 CREATE TABLE `themes` (
896 `title` varchar(20) NOT NULL default '',
897 `version` varchar(10) NOT NULL default '',
898 `dir_name` varchar(20) NOT NULL default '',
899 `last_updated` date NOT NULL default '0000-00-00',
900 `extra_info` varchar(255) NOT NULL default '',
901 `status` tinyint(3) unsigned NOT NULL default '1',
902 PRIMARY KEY (`title`)
905 # insert the default theme
906 INSERT INTO `themes` VALUES ('Atutor', '1.5.2', '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);
907 INSERT INTO `themes` VALUES ('Atutor Classic', '1.5.2', '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 theme\'s directory.', 1);
908 INSERT INTO `themes` VALUES ('Blumin', '1.5.3', 'blumin', NOW(), 'This is the plone look-alike theme.', 1);
912 # --------------------------------------------------------
913 # Table structure for table `users_online`
915 CREATE TABLE `users_online` (
916 `member_id` mediumint(8) unsigned NOT NULL default '0',
917 `course_id` mediumint(8) unsigned NOT NULL default '0',
918 `login` varchar(255) NOT NULL default '',
919 `expiry` int(10) unsigned NOT NULL default '0',
920 PRIMARY KEY (`member_id`)
921 ) TYPE=HEAP MAX_ROWS=500;