1 ###############################################################
2 # Database upgrade SQL from ATutor 1.0-Stable to ATutor 1.1
3 ###############################################################
5 ##################################
8 ALTER TABLE `news` ADD `formatting` TINYINT DEFAULT '0' NOT NULL AFTER `date`;
9 UPDATE `news` SET formatting=1;
12 ##################################
14 ALTER TABLE `courses` ADD `preferences` TEXT NOT NULL ,
15 ADD `header` TEXT NOT NULL ,
16 ADD `footer` TEXT NOT NULL ,
17 ADD `copyright` TEXT NOT NULL ,
18 ADD `tracking` ENUM( 'on', 'off' ) DEFAULT 'off' NOT NULL ;
20 ##################################
22 ALTER TABLE `g_click_data` ADD `course_id` MEDIUMINT UNSIGNED NOT NULL AFTER `member_id` ;
23 ALTER TABLE `g_click_data` CHANGE `timestamp` `timestamp` INT UNSIGNED NOT NULL ;
24 ALTER TABLE `g_click_data` ADD `duration` DOUBLE UNSIGNED NOT NULL ;
26 ##################################
30 g_id tinyint(4) default NULL,
31 reference varchar(65) default NULL,
38 INSERT INTO g_refs VALUES (1, 'Users Online');
39 INSERT INTO g_refs VALUES (2, 'Local Menu');
40 INSERT INTO g_refs VALUES (3, 'Global Menu');
41 INSERT INTO g_refs VALUES (4, 'Related topic');
42 INSERT INTO g_refs VALUES (5, 'Jump');
43 INSERT INTO g_refs VALUES (6, 'Top/#bypass anchor');
44 INSERT INTO g_refs VALUES (7, 'Sequence');
45 INSERT INTO g_refs VALUES (8, 'Within sitemap');
46 INSERT INTO g_refs VALUES (9, 'Global Home link');
47 INSERT INTO g_refs VALUES (10, 'Breadcrumb');
48 INSERT INTO g_refs VALUES (11, 'Headings');
49 INSERT INTO g_refs VALUES (12, 'Embedded links');
50 INSERT INTO g_refs VALUES (13, 'Table of contents');
51 INSERT INTO g_refs VALUES (14, 'Home');
52 INSERT INTO g_refs VALUES (15, 'Tools');
53 INSERT INTO g_refs VALUES (16, 'Resources');
54 INSERT INTO g_refs VALUES (17, 'Discussions');
55 INSERT INTO g_refs VALUES (18, 'Help');
56 INSERT INTO g_refs VALUES (19, 'Logout');
57 INSERT INTO g_refs VALUES (20, 'Preferences');
58 INSERT INTO g_refs VALUES (21, 'Inbox');
59 INSERT INTO g_refs VALUES (22, 'Local major topic');
60 INSERT INTO g_refs VALUES (23, 'To sitemap');
61 INSERT INTO g_refs VALUES (24, 'Embedded glossary');
62 INSERT INTO g_refs VALUES (25, 'Menu glossary');
63 INSERT INTO g_refs VALUES (26, 'Local Home link');
64 INSERT INTO g_refs VALUES (27, 'Print Compiler');
65 INSERT INTO g_refs VALUES (28, 'My Tracker');
66 INSERT INTO g_refs VALUES (29, 'Links DB');
67 INSERT INTO g_refs VALUES (30, 'Session Start');
69 ##################################
70 # `instructor_approvals`
71 ALTER TABLE `instructor_approvals` ADD `request_date` DATETIME NOT NULL ,
72 ADD `notes` TEXT NOT NULL ;
75 ##################################
77 ALTER TABLE `messages` ADD INDEX ( `to_member_id` );
79 ##################################
81 CREATE TABLE preferences (
82 member_id mediumint(8) unsigned NOT NULL default '0',
83 course_id mediumint(8) unsigned NOT NULL default '0',
84 preferences text NOT NULL,
85 PRIMARY KEY (member_id,course_id)
88 ##################################
91 test_id mediumint(8) unsigned NOT NULL auto_increment,
92 course_id mediumint(8) unsigned NOT NULL default '0',
93 title varchar(100) NOT NULL default '',
94 format tinyint(4) NOT NULL default '0',
95 start_date datetime NOT NULL default '0000-00-00 00:00:00',
96 end_date datetime NOT NULL default '0000-00-00 00:00:00',
97 randomize_order tinyint(4) NOT NULL default '0',
98 num_questions tinyint(3) unsigned NOT NULL default '0',
99 instructions text NOT NULL,
100 PRIMARY KEY (test_id)
104 ##################################
106 CREATE TABLE tests_answers (
107 result_id mediumint(8) unsigned NOT NULL default '0',
108 question_id mediumint(8) unsigned NOT NULL default '0',
109 member_id mediumint(8) unsigned NOT NULL default '0',
110 answer text NOT NULL,
111 score varchar(5) NOT NULL default '',
113 PRIMARY KEY (result_id,question_id,member_id)
117 ##################################
119 CREATE TABLE tests_questions (
120 question_id mediumint(8) unsigned NOT NULL auto_increment,
121 test_id mediumint(8) unsigned NOT NULL default '0',
122 course_id mediumint(8) unsigned NOT NULL default '0',
123 ordering tinyint(3) unsigned NOT NULL default '0',
124 type tinyint(3) unsigned NOT NULL default '0',
125 weight tinyint(3) unsigned NOT NULL default '0',
126 required tinyint(4) NOT NULL default '0',
127 feedback text NOT NULL,
128 question text NOT NULL,
129 choice_0 varchar(255) NOT NULL default '',
130 choice_1 varchar(255) NOT NULL default '',
131 choice_2 varchar(255) NOT NULL default '',
132 choice_3 varchar(255) NOT NULL default '',
133 choice_4 varchar(255) NOT NULL default '',
134 choice_5 varchar(255) NOT NULL default '',
135 choice_6 varchar(255) NOT NULL default '',
136 choice_7 varchar(255) NOT NULL default '',
137 choice_8 varchar(255) NOT NULL default '',
138 choice_9 varchar(255) NOT NULL default '',
139 answer_0 tinyint(4) NOT NULL default '0',
140 answer_1 tinyint(4) NOT NULL default '0',
141 answer_2 tinyint(4) NOT NULL default '0',
142 answer_3 tinyint(4) NOT NULL default '0',
143 answer_4 tinyint(4) NOT NULL default '0',
144 answer_5 tinyint(4) NOT NULL default '0',
145 answer_6 tinyint(4) NOT NULL default '0',
146 answer_7 tinyint(4) NOT NULL default '0',
147 answer_8 tinyint(4) NOT NULL default '0',
148 answer_9 tinyint(4) NOT NULL default '0',
149 answer_size tinyint(4) NOT NULL default '0',
150 PRIMARY KEY (question_id),
151 KEY test_id (test_id)
154 ##################################
155 # table `tests_results`
156 CREATE TABLE tests_results (
157 result_id mediumint(8) unsigned NOT NULL auto_increment,
158 test_id mediumint(8) unsigned NOT NULL default '0',
159 member_id mediumint(8) unsigned NOT NULL default '0',
160 date_taken datetime NOT NULL default '0000-00-00 00:00:00',
161 final_score char(5) NOT NULL default '',
162 PRIMARY KEY (result_id),
163 KEY test_id (test_id)
166 ##################################
168 CREATE TABLE theme_settings (
169 theme_id tinyint(4) unsigned NOT NULL auto_increment,
170 name varchar(50) NOT NULL default '',
171 preferences text NOT NULL,
172 PRIMARY KEY (theme_id)
175 # data for `theme_settings`
176 INSERT INTO theme_settings VALUES (1, 'Accessbility',
178 'a:16:{s:19:"PREF_MAIN_MENU_SIDE";i:2;s:14:"PREF_MAIN_MENU";i:0;s:10:"PREF_THEME";i:0;s:12:"PREF_DISPLAY";i:0;s:9:"PREF_TIPS";i:0;s:8:"PREF_SEQ";i:1;s:8:"PREF_TOC";i:2;s:14:"PREF_NUMBERING";i:0;s:11:"PREF_ONLINE";i:0;s:14:"PREF_SEQ_ICONS";i:2;s:14:"PREF_NAV_ICONS";i:2;s:16:"PREF_LOGIN_ICONS";i:2;s:13:"PREF_HEADINGS";i:0;s:16:"PREF_BREADCRUMBS";i:0;s:9:"PREF_FONT";i:0;s:15:"PREF_STYLESHEET";i:0;}');
179 INSERT INTO theme_settings VALUES (2, 'Icons only', 'a:4:{s:14:"PREF_SEQ_ICONS";i:1;s:14:"PREF_NAV_ICONS";i:1;s:16:"PREF_LOGIN_ICONS";i:1;s:16:"PREF_BREADCRUMBS";i:1;}');
180 INSERT INTO theme_settings VALUES (3, 'Both icons and text', 'a:5:{s:14:"PREF_MAIN_MENU";i:1;s:14:"PREF_SEQ_ICONS";i:0;s:14:"PREF_NAV_ICONS";i:0;s:16:"PREF_LOGIN_ICONS";i:0;s:16:"PREF_BREADCRUMBS";i:1;}');
181 INSERT INTO theme_settings VALUES (4, 'ATutor Defaults', 'a:17:{s:10:\"PREF_STACK\";a:5:{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\";}s:14:\"PREF_MAIN_MENU\";i:1;s:9:\"PREF_MENU\";i:1;s:19:\"PREF_MAIN_MENU_SIDE\";i:2;s:8:\"PREF_SEQ\";i:3;s:8:\"PREF_TOC\";i:2;s:14:\"PREF_SEQ_ICONS\";i:0;s:14:\"PREF_NAV_ICONS\";i:0;s:16:\"PREF_LOGIN_ICONS\";i:0;s:9:\"PREF_FONT\";i:0;s:15:\"PREF_STYLESHEET\";i:0;s:14:\"PREF_NUMBERING\";i:0;s:13:\"PREF_HEADINGS\";i:0;s:16:\"PREF_BREADCRUMBS\";i:1;s:13:\"PREF_OVERRIDE\";i:0;s:9:\"PREF_HELP\";i:1;s:14:\"PREF_MINI_HELP\";i:1;}');