1 ###############################################################
2 # Database upgrade SQL from ATutor 1.4.2 to ATutor 1.4.3
3 ###############################################################
5 CREATE TABLE `languages` (
6 `language_code` varchar(5) NOT NULL default '',
7 `char_set` varchar(20) NOT NULL default '',
8 `direction` varchar(4) NOT NULL default '',
9 `reg_exp` varchar(31) NOT NULL default '',
10 `native_name` varchar(20) NOT NULL default '',
11 `english_name` varchar(20) NOT NULL default '',
12 `status` TINYINT UNSIGNED DEFAULT '0' NOT NULL,
13 PRIMARY KEY (`language_code`,`char_set`)
16 INSERT INTO `languages` VALUES ('en', 'iso-8859-1', 'ltr', 'en([-_][[:alpha:]]{2})?|english', 'English', 'English', 3);
18 # --------------------------------------------------------
19 # Table structure for table `language_pages`
21 CREATE TABLE `language_pages` (
22 `term` varchar(30) NOT NULL default '',
23 `page` varchar(50) NOT NULL default '',
24 PRIMARY KEY (`term`,`page`)
27 CREATE TABLE `themes` (
28 `title` varchar(20) NOT NULL default '',
29 `version` varchar(10) NOT NULL default '',
30 `dir_name` varchar(20) NOT NULL default '',
31 `last_updated` date NOT NULL default '0000-00-00',
32 `extra_info` varchar(40) NOT NULL default '',
33 `status` tinyint(3) unsigned NOT NULL default '1',
37 # insert the default theme
38 INSERT INTO themes VALUES ('Atutor', '1.4.3', 'default', NOW(), 'This is the default Atutor theme.', 2);
42 CREATE TABLE `backups` (
43 `backup_id` mediumint(8) unsigned NOT NULL auto_increment,
44 `course_id` mediumint(8) unsigned NOT NULL default '0',
45 `date` datetime NOT NULL default '0000-00-00 00:00:00',
46 `description` varchar(100) NOT NULL default '',
47 `file_size` int(10) unsigned NOT NULL default '0',
48 `system_file_name` varchar(50) NOT NULL default '',
49 `file_name` varchar(50) NOT NULL default '',
50 `contents` TEXT NOT NULL default '',
51 PRIMARY KEY (`backup_id`),
52 KEY `course_id` (`course_id`)
56 ##### changes to the `forums_*` tables #####
58 # the new course forums table
59 CREATE TABLE `forums_courses` (
60 `forum_id` MEDIUMINT UNSIGNED NOT NULL default '0',
61 `course_id` MEDIUMINT UNSIGNED NOT NULL default '0',
62 PRIMARY KEY (`forum_id`,`course_id`),
63 KEY `course_id` (`course_id`)
66 # insert the current forums into the new table
67 INSERT INTO forums_courses SELECT forum_id, course_id FROM `forums`;
69 # remove the old course_id from the forums table and forums_threads
70 ALTER TABLE `forums` DROP `course_id`;
71 ALTER TABLE `forums_threads` DROP `course_id`;
73 DROP TABLE forums_subscriptions;
75 # setup forum subscription
76 CREATE TABLE `forums_subscriptions` (
77 forum_id mediumint(8) unsigned NOT NULL default '0',
78 member_id mediumint(8) unsigned NOT NULL default '0',
79 PRIMARY KEY (`forum_id`,`member_id`)
83 ALTER TABLE `forums_accessed` ADD `subscribe` TINYINT NOT NULL ;
87 ALTER TABLE `course_enrollment` CHANGE `approved` `approved` ENUM( 'y', 'n', 'a' ) DEFAULT 'n' NOT NULL;
89 UPDATE `theme_settings` SET `preferences` = 'a:25:{s:10:"PREF_STACK";a:8:{i:0;s:1:"0";i:1;s:1:"1";i:2;s:1:"2";i:3;s:1:"3";i:4;s:1:"4";i:5;s:1:"5";i:6;s:1:"6";i:7;s:1:"7";}s:19:"PREF_MAIN_MENU_SIDE";i:2;s:8:"PREF_SEQ";i:3;s:14:"PREF_NUMBERING";i:1;s:8:"PREF_TOC";i:1;s:14:"PREF_SEQ_ICONS";i:1;s:14:"PREF_NAV_ICONS";i:0;s:16:"PREF_LOGIN_ICONS";i:0;s:13:"PREF_HEADINGS";i:1;s:16:"PREF_BREADCRUMBS";i:1;s:9:"PREF_HELP";i:1;s:14:"PREF_MINI_HELP";i:1;s:18:"PREF_CONTENT_ICONS";i:0;s:14:"PREF_MAIN_MENU";i:1;s:11:"PREF_ONLINE";i:1;s:9:"PREF_MENU";i:1;s:10:"PREF_THEME";s:7:"default";s:9:"PREF_EDIT";i:1;s:18:"PREF_JUMP_REDIRECT";i:0;s:10:"PREF_LOCAL";i:1;s:12:"PREF_RELATED";i:1;s:13:"PREF_GLOSSARY";i:1;s:11:"PREF_SEARCH";i:1;s:10:"PREF_POSTS";i:1;s:9:"PREF_POLL";i:1;}' WHERE `theme_id` = '4';
91 ###### changes to the `tests_*` tables #####
92 CREATE TABLE `tests_questions_assoc` (
93 `test_id` mediumint(8) unsigned NOT NULL default '0',
94 `question_id` mediumint(8) unsigned NOT NULL default '0',
95 `weight` varchar(4) NOT NULL default '',
96 `ordering` tinyint(3) unsigned NOT NULL default '0',
97 `required` tinyint(3) unsigned NOT NULL default '0',
98 PRIMARY KEY (`test_id`,`question_id`),
99 KEY `test_id` (`test_id`)
102 CREATE TABLE `tests_questions_categories` (
103 `category_id` mediumint(8) unsigned NOT NULL auto_increment,
104 `course_id` mediumint(8) unsigned NOT NULL default '0',
105 `title` char(50) NOT NULL default '',
106 PRIMARY KEY (`category_id`),
107 KEY `course_id` (`course_id`)
110 ALTER TABLE `tests` ADD INDEX ( `course_id` );
112 INSERT INTO `tests_questions_assoc` SELECT test_id, question_id, weight, ordering, required FROM `tests_questions`;
114 ALTER TABLE `tests_questions` CHANGE `test_id` `category_id` MEDIUMINT( 8 ) UNSIGNED DEFAULT '0' NOT NULL;
115 ALTER TABLE `tests_questions` CHANGE `answer_size` `properties` TINYINT( 4 ) DEFAULT '0' NOT NULL;
116 ALTER TABLE `tests_questions` DROP `ordering`, DROP `required`, DROP `weight`;
117 UPDATE `tests_questions` SET `category_id`=0;
119 ##### new `groups_*` tables #####
120 CREATE TABLE `groups` (
121 `group_id` MEDIUMINT UNSIGNED NOT NULL auto_increment,
122 `course_id` MEDIUMINT UNSIGNED NOT NULL default '0',
123 `title` varchar(20) NOT NULL default '',
124 PRIMARY KEY ( `group_id` ),
125 KEY `course_id` (`course_id`)
129 CREATE TABLE `groups_members` (
130 `group_id` MEDIUMINT UNSIGNED NOT NULL default '0',
131 `member_id` MEDIUMINT UNSIGNED NOT NULL default '0',
132 PRIMARY KEY (`group_id`,`member_id`)
136 CREATE TABLE `tests_groups` (
137 `test_id` MEDIUMINT UNSIGNED NOT NULL default '0',
138 `group_id` MEDIUMINT UNSIGNED NOT NULL default '0',
139 PRIMARY KEY (`test_id`,`group_id`),
140 KEY `test_id` (`test_id`)
144 # Add tracking g for the search tool
145 INSERT INTO g_refs VALUES (37, 'g_search');
147 # Change automark to selective release field
148 ALTER TABLE `tests` CHANGE `automark` `result_release` TINYINT( 4 ) UNSIGNED DEFAULT '0' NOT NULL;
149 ALTER TABLE `tests` ADD `out_of` VARCHAR( 5 ) NOT NULL ;
151 UPDATE tests SET result_release=0;
153 DROP TABLE `lang_base`;
155 # add RSS feeds to courses
156 ALTER TABLE `courses` ADD `rss` TINYINT DEFAULT '0' NOT NULL ;