New strings from Ethem Evlice (ethem AT evlice DOT com)
[moodle.git] / lib / db / mysql.php
CommitLineData
31f0900c 1<?PHP //$Id$
2//
3// This file keeps track of upgrades to Moodle.
10ee08cc 4//
5// Sometimes, changes between versions involve
6// alterations to database structures and other
7// major things that may break installations.
31f0900c 8//
9// The upgrade function in this file will attempt
10// to perform all the necessary actions to upgrade
11// your older installtion to the current version.
12//
10ee08cc 13// If there's something it cannot do itself, it
31f0900c 14// will tell you what you need to do.
15//
16// Versions are defined by /version.php
17//
18// This file is tailored to MySQL
19
e7311a0a 20function main_upgrade($oldversion=0) {
31f0900c 21
4d744a22 22 global $CFG, $THEME, $db;
7beb45d8 23
047d30d1 24 $result = true;
25
31f0900c 26 if ($oldversion == 0) {
27 execute_sql("
28 CREATE TABLE `config` (
29 `id` int(10) unsigned NOT NULL auto_increment,
30 `name` varchar(255) NOT NULL default '',
31 `value` varchar(255) NOT NULL default '',
32 PRIMARY KEY (`id`),
33 UNIQUE KEY `name` (`name`)
34 ) COMMENT='Moodle configuration variables';");
35 notify("Created a new table 'config' to hold configuration data");
36 }
37 if ($oldversion < 2002073100) {
38 execute_sql(" DELETE FROM `modules` WHERE `name` = 'chat' ");
39 }
40 if ($oldversion < 2002080200) {
41 execute_sql(" ALTER TABLE `modules` DROP `fullname` ");
42 execute_sql(" ALTER TABLE `modules` DROP `search` ");
43 }
44 if ($oldversion < 2002080300) {
45 execute_sql(" ALTER TABLE `log_display` CHANGE `table` `mtable` VARCHAR( 20 ) NOT NULL ");
46 execute_sql(" ALTER TABLE `user_teachers` CHANGE `authority` `authority` TINYINT( 3 ) DEFAULT '3' NOT NULL ");
47 }
48 if ($oldversion < 2002082100) {
49 execute_sql(" ALTER TABLE `course` CHANGE `guest` `guest` TINYINT(2) UNSIGNED DEFAULT '0' NOT NULL ");
50 }
51 if ($oldversion < 2002082101) {
52 execute_sql(" ALTER TABLE `user` ADD `maildisplay` TINYINT(2) UNSIGNED DEFAULT '2' NOT NULL AFTER `mailformat` ");
53 }
54 if ($oldversion < 2002090100) {
55 execute_sql(" ALTER TABLE `course_sections` CHANGE `summary` `summary` TEXT NOT NULL ");
56 }
57 if ($oldversion < 2002090701) {
58 execute_sql(" ALTER TABLE `user_teachers` CHANGE `authority` `authority` TINYINT( 10 ) DEFAULT '3' NOT NULL ");
59 execute_sql(" ALTER TABLE `user_teachers` ADD `role` VARCHAR(40) NOT NULL AFTER `authority` ");
60 }
61 if ($oldversion < 2002090800) {
62 execute_sql(" ALTER TABLE `course` ADD `teachers` VARCHAR( 100 ) DEFAULT 'Teachers' NOT NULL AFTER `teacher` ");
63 execute_sql(" ALTER TABLE `course` ADD `students` VARCHAR( 100 ) DEFAULT 'Students' NOT NULL AFTER `student` ");
64 }
65 if ($oldversion < 2002091000) {
66 execute_sql(" ALTER TABLE `user` CHANGE `personality` `secret` VARCHAR( 15 ) DEFAULT NULL ");
67 }
68 if ($oldversion < 2002091400) {
69 execute_sql(" ALTER TABLE `user` ADD `lang` VARCHAR( 3 ) DEFAULT 'en' NOT NULL AFTER `country` ");
70 }
71 if ($oldversion < 2002091900) {
72 notify("Most Moodle configuration variables have been moved to the database and can now be edited via the admin page.");
73 notify("Although it is not vital that you do so, you might want to edit <U>config.php</U> and remove all the unused settings (except the database, URL and directory definitions). See <U>config-dist.php</U> for an example of how your new slim config.php should look.");
74 }
75 if ($oldversion < 2002092000) {
76 execute_sql(" ALTER TABLE `user` CHANGE `lang` `lang` VARCHAR(5) DEFAULT 'en' NOT NULL ");
77 }
78 if ($oldversion < 2002092100) {
79 execute_sql(" ALTER TABLE `user` ADD `deleted` TINYINT(1) UNSIGNED DEFAULT '0' NOT NULL AFTER `confirmed` ");
80 }
0095d5cd 81 if ($oldversion < 2002101001) {
82 execute_sql(" ALTER TABLE `user` ADD `htmleditor` TINYINT(1) UNSIGNED DEFAULT '1' NOT NULL AFTER `maildisplay` ");
83 }
2a439ba7 84 if ($oldversion < 2002101701) {
85 execute_sql(" ALTER TABLE `reading` RENAME `resource` "); // Small line with big consequences!
10ee08cc 86 execute_sql(" DELETE FROM `log_display` WHERE module = 'reading'");
2a439ba7 87 execute_sql(" INSERT INTO log_display VALUES ('resource', 'view', 'resource', 'name') ");
88 execute_sql(" UPDATE log SET module = 'resource' WHERE module = 'reading' ");
89 execute_sql(" UPDATE modules SET name = 'resource' WHERE name = 'reading' ");
90 }
31f0900c 91
7beb45d8 92 if ($oldversion < 2002102503) {
f82c2d42 93 execute_sql(" ALTER TABLE `course` ADD `modinfo` TEXT NOT NULL AFTER `format` ");
7beb45d8 94 require_once("$CFG->dirroot/mod/forum/lib.php");
95 require_once("$CFG->dirroot/course/lib.php");
96
97 if (! $module = get_record("modules", "name", "forum")) {
98 notify("Could not find forum module!!");
99 return false;
100 }
101
102 // First upgrade the site forums
103 if ($site = get_site()) {
104 print_heading("Making News forums editable for main site (moving to section 1)...");
105 if ($news = forum_get_course_forum($site->id, "news")) {
106 $mod->course = $site->id;
107 $mod->module = $module->id;
108 $mod->instance = $news->id;
109 $mod->section = 1;
110 if (! $mod->coursemodule = add_course_module($mod) ) {
111 notify("Could not add a new course module to the site");
112 return false;
113 }
114 if (! $sectionid = add_mod_to_section($mod) ) {
115 notify("Could not add the new course module to that section");
116 return false;
117 }
118 if (! set_field("course_modules", "section", $sectionid, "id", $mod->coursemodule)) {
119 notify("Could not update the course module with the correct section");
120 return false;
121 }
122 }
123 }
124
125
126 // Now upgrade the courses.
127 if ($courses = get_records_sql("SELECT * FROM course WHERE category > 0")) {
128 print_heading("Making News and Social forums editable for each course (moving to section 0)...");
129 foreach ($courses as $course) {
130 if ($course->format == "social") { // we won't touch them
131 continue;
132 }
133 if ($news = forum_get_course_forum($course->id, "news")) {
134 $mod->course = $course->id;
135 $mod->module = $module->id;
136 $mod->instance = $news->id;
137 $mod->section = 0;
138 if (! $mod->coursemodule = add_course_module($mod) ) {
139 notify("Could not add a new course module to the course '$course->fullname'");
140 return false;
141 }
142 if (! $sectionid = add_mod_to_section($mod) ) {
143 notify("Could not add the new course module to that section");
144 return false;
145 }
146 if (! set_field("course_modules", "section", $sectionid, "id", $mod->coursemodule)) {
147 notify("Could not update the course module with the correct section");
148 return false;
149 }
150 }
151 if ($social = forum_get_course_forum($course->id, "social")) {
152 $mod->course = $course->id;
153 $mod->module = $module->id;
154 $mod->instance = $social->id;
155 $mod->section = 0;
156 if (! $mod->coursemodule = add_course_module($mod) ) {
157 notify("Could not add a new course module to the course '$course->fullname'");
158 return false;
159 }
160 if (! $sectionid = add_mod_to_section($mod) ) {
161 notify("Could not add the new course module to that section");
162 return false;
163 }
164 if (! set_field("course_modules", "section", $sectionid, "id", $mod->coursemodule)) {
165 notify("Could not update the course module with the correct section");
166 return false;
167 }
168 }
169 }
170 }
171 }
172
d897cae4 173 if ($oldversion < 2002111003) {
174 execute_sql(" ALTER TABLE `course` ADD `modinfo` TEXT NOT NULL AFTER `format` ");
175 if ($courses = get_records_sql("SELECT * FROM course")) {
176 require_once("$CFG->dirroot/course/lib.php");
177 foreach ($courses as $course) {
10ee08cc 178
d897cae4 179 $modinfo = serialize(get_array_of_activities($course->id));
180
181 if (!set_field("course", "modinfo", $modinfo, "id", $course->id)) {
182 notify("Could not cache module information for course '$course->fullname'!");
183 }
184 }
185 }
186 }
187
3635d82f 188 if ($oldversion < 2002111100) {
189 print_simple_box_start("CENTER", "", "#FFCCCC");
190 echo "<FONT SIZE=+1>";
191 echo "<P>Changes have been made to all built-in themes, to add the new popup navigation menu.";
192 echo "<P>If you have customised themes, you will need to edit theme/xxxx/header.html as follows:";
193 echo "<UL><LI>Change anywhere it says <B>$"."button</B> to say <B>$"."menu</B>";
194 echo "<LI>Add <B>$"."button</B> elsewhere (eg at the end of the navigation bar)</UL>";
195 echo "<P>See the standard themes for examples, eg: theme/standard/header.html";
196 print_simple_box_end();
197 }
198
13beeacb 199 if ($oldversion < 2002111200) {
200 execute_sql(" ALTER TABLE `course` ADD `showrecent` TINYINT(5) UNSIGNED DEFAULT '1' NOT NULL AFTER `numsections` ");
201 }
202
f8ef7d66 203 if ($oldversion < 2002111400) {
204 // Rebuild all course caches, because some may not be done in new installs (eg site page)
205 if ($courses = get_records_sql("SELECT * FROM course")) {
206 require_once("$CFG->dirroot/course/lib.php");
207 foreach ($courses as $course) {
10ee08cc 208
f8ef7d66 209 $modinfo = serialize(get_array_of_activities($course->id));
210
211 if (!set_field("course", "modinfo", $modinfo, "id", $course->id)) {
212 notify("Could not cache module information for course '$course->fullname'!");
213 }
214 }
215 }
216 }
217
277d2d9f 218 if ($oldversion < 2002112000) {
219 set_config("guestloginbutton", 1);
220 }
221
ebc3bd2b 222 if ($oldversion < 2002122300) {
223 execute_sql("ALTER TABLE `log` CHANGE `user` `userid` INT(10) UNSIGNED DEFAULT '0' NOT NULL ");
224 execute_sql("ALTER TABLE `user_admins` CHANGE `user` `userid` INT(10) UNSIGNED DEFAULT '0' NOT NULL ");
225 execute_sql("ALTER TABLE `user_students` CHANGE `user` `userid` INT(10) UNSIGNED DEFAULT '0' NOT NULL ");
226 execute_sql("ALTER TABLE `user_teachers` CHANGE `user` `userid` INT(10) UNSIGNED DEFAULT '0' NOT NULL ");
227 execute_sql("ALTER TABLE `user_students` CHANGE `start` `timestart` INT(10) UNSIGNED DEFAULT '0' NOT NULL ");
228 execute_sql("ALTER TABLE `user_students` CHANGE `end` `timeend` INT(10) UNSIGNED DEFAULT '0' NOT NULL ");
229 }
230
9d378732 231 if ($oldversion < 2002122700) {
05a6c03e 232 if (! record_exists("log_display", "module", "user", "action", "view")) {
a3eea09c 233 execute_sql("INSERT INTO {$CFG->prefix}log_display VALUES ('user', 'view', 'user', 'CONCAT(firstname,' ',lastname)') ");
234 }
235 }
db70b54b 236 if ($oldversion < 2003010101) {
14e4a496 237 delete_records("log_display", "module", "user");
238 $new->module = "user";
239 $new->action = "view";
240 $new->mtable = "user";
db70b54b 241 $new->field = "CONCAT(firstname,\" \",lastname)";
14e4a496 242 insert_record("log_display", $new);
243
244 delete_records("log_display", "module", "course");
245 $new->module = "course";
246 $new->action = "view";
247 $new->mtable = "course";
248 $new->field = "fullname";
249 insert_record("log_display", $new);
250 $new->action = "update";
251 insert_record("log_display", $new);
db70b54b 252 $new->action = "enrol";
253 insert_record("log_display", $new);
14e4a496 254 }
d897cae4 255
811c88b3 256 if ($oldversion < 2003012200) {
718ad19f 257 // execute_sql(" ALTER TABLE `log_display` CHANGE `module` `module` VARCHAR( 20 ) NOT NULL ");
258 // Commented out - see below where it's done properly
811c88b3 259 }
260
dcd338ff 261 if ($oldversion < 2003032500) {
262 modify_database("", "CREATE TABLE `prefix_user_coursecreators` (
2fdb54b8 263 `id` int(10) unsigned NOT NULL auto_increment,
264 `userid` int(10) unsigned NOT NULL default '0',
265 PRIMARY KEY (`id`),
266 UNIQUE KEY `id` (`id`)
267 ) TYPE=MyISAM COMMENT='One record per course creator';");
dcd338ff 268 }
269 if ($oldversion < 2003032602) {
264d4e0b 270 // Redoing it because of no prefix last time
271 execute_sql(" ALTER TABLE `{$CFG->prefix}log_display` CHANGE `module` `module` VARCHAR( 20 ) NOT NULL ");
272 // Add some indexes for speed
273 execute_sql(" ALTER TABLE `{$CFG->prefix}log` ADD INDEX(course) ");
274 execute_sql(" ALTER TABLE `{$CFG->prefix}log` ADD INDEX(userid) ");
275 }
10ee08cc 276
dcd338ff 277 if ($oldversion < 2003041400) {
1acfbce5 278 table_column("course_modules", "", "visible", "integer", "1", "unsigned", "1", "not null", "score");
279 }
1924074c 280
dcd338ff 281 if ($oldversion < 2003042104) { // Try to update permissions of all files
718ad19f 282 if ($files = get_directory_list($CFG->dataroot)) {
283 echo "Attempting to update permissions for all files... ignore any errors.";
284 foreach ($files as $file) {
839f2456 285 echo "$CFG->dataroot/$file<br />";
b369ff55 286 @chmod("$CFG->dataroot/$file", $CFG->directorypermissions);
718ad19f 287 }
288 }
289 }
290
13df5aee 291 if ($oldversion < 2003042400) {
292 // Rebuild all course caches, because of changes to do with visible variable
293 if ($courses = get_records_sql("SELECT * FROM {$CFG->prefix}course")) {
294 require_once("$CFG->dirroot/course/lib.php");
295 foreach ($courses as $course) {
296 $modinfo = serialize(get_array_of_activities($course->id));
297
298 if (!set_field("course", "modinfo", $modinfo, "id", $course->id)) {
299 notify("Could not cache module information for course '$course->fullname'!");
300 }
301 }
302 }
303 }
304
10ee08cc 305 if ($oldversion < 2003042500) {
306 // Convert all usernames to lowercase.
307 $users = get_records_sql("SELECT id, username FROM {$CFG->prefix}user");
047d30d1 308 $cerrors = "";
309 $rarray = array();
310
311 foreach ($users as $user) { // Check for possible conflicts
312 $lcname = trim(moodle_strtolower($user->username));
313 if (in_array($lcname, $rarray)) {
10ee08cc 314 $cerrors .= $user->id."->".$lcname.'<br/>' ;
047d30d1 315 } else {
316 array_push($rarray,$lcname);
317 }
318 }
319
320 if ($cerrors != '') {
10ee08cc 321 notify("Error: Cannot convert usernames to lowercase.
322 Following usernames would overlap (id->username):<br/> $cerrors .
323 Please resolve overlapping errors.");
047d30d1 324 $result = false;
325 }
326
327 $cerrors = "";
839f2456 328 echo "Checking userdatabase:<br />";
047d30d1 329 foreach ($users as $user) {
330 $lcname = trim(moodle_strtolower($user->username));
331 if ($lcname != $user->username) {
332 $convert = set_field("user" , "username" , $lcname, "id", $user->id);
333 if (!$convert) {
334 if ($cerrors){
335 $cerrors .= ", ";
10ee08cc 336 }
047d30d1 337 $cerrors .= $item;
338 } else {
339 echo ".";
10ee08cc 340 }
047d30d1 341 }
342 }
343 if ($cerrors != '') {
10ee08cc 344 notify("There were errors when converting following usernames to lowercase.
047d30d1 345 '$cerrors' . Sorry, but you will need to fix your database by hand.");
346 $result = false;
347 }
348 }
349
1ba39602 350 if ($oldversion < 2003042600) {
f9ce68ee 351 /// Some more indexes - we need all the help we can get on the logs
352 //execute_sql(" ALTER TABLE `{$CFG->prefix}log` ADD INDEX(module) ");
353 //execute_sql(" ALTER TABLE `{$CFG->prefix}log` ADD INDEX(action) ");
354 }
355
356 if ($oldversion < 2003042700) {
357 /// Changing to multiple indexes
358 execute_sql(" ALTER TABLE `{$CFG->prefix}log` DROP INDEX module ", false);
359 execute_sql(" ALTER TABLE `{$CFG->prefix}log` DROP INDEX action ", false);
360 execute_sql(" ALTER TABLE `{$CFG->prefix}log` DROP INDEX course ", false);
361 execute_sql(" ALTER TABLE `{$CFG->prefix}log` DROP INDEX userid ", false);
362 execute_sql(" ALTER TABLE `{$CFG->prefix}log` ADD INDEX coursemoduleaction (course,module,action) ");
363 execute_sql(" ALTER TABLE `{$CFG->prefix}log` ADD INDEX courseuserid (course,userid) ");
6459f225 364 }
365
b86fc0e2 366 if ($oldversion < 2003042801) {
367 execute_sql("CREATE TABLE `{$CFG->prefix}course_display` (
368 `id` int(10) unsigned NOT NULL auto_increment,
369 `course` int(10) unsigned NOT NULL default '0',
370 `userid` int(10) unsigned NOT NULL default '0',
371 `display` int(10) NOT NULL default '0',
372 PRIMARY KEY (`id`),
373 UNIQUE KEY `id` (`id`),
374 KEY `courseuserid` (course,userid)
375 ) TYPE=MyISAM COMMENT='Stores info about how to display the course'");
376 }
377
7d99d695 378 if ($oldversion < 2003050400) {
379 table_column("course_sections", "", "visible", "integer", "1", "unsigned", "1", "", "");
380 }
381
7e6b0b3b 382 if ($oldversion < 2003050900) {
383 table_column("modules", "", "visible", "integer", "1", "unsigned", "1", "", "");
384 }
385
5867bfb5 386 if ($oldversion < 2003050902) {
387 if (get_records("modules", "name", "pgassignment")) {
7adf6787 388 print_simple_box("Note: the pgassignment module has been removed (it will be replaced later by the workshop module). Go to the new 'Manage Modules' page and DELETE IT from your system", "center", "50%", "$THEME->cellheading", "20", "noticebox");
5867bfb5 389 }
390 }
391
7adf6787 392 if ($oldversion < 2003051600) {
393 print_simple_box("Thanks for upgrading!<p>There are many changes since the last release. Please read the release notes carefully. If you are using CUSTOM themes you will need to edit them. You will also need to check your site's config.php file.", "center", "50%", "$THEME->cellheading", "20", "noticebox");
394 }
395
7f2a3e67 396 if ($oldversion < 2003052300) {
397 table_column("user", "", "autosubscribe", "integer", "1", "unsigned", "1", "", "htmleditor");
398 }
399
a6d82c3f 400 if ($oldversion < 2003072100) {
401 table_column("course", "", "visible", "integer", "1", "unsigned", "1", "", "marker");
402 }
403
dd0bd508 404 if ($oldversion < 2003072101) {
405 table_column("course_sections", "sequence", "sequence", "text", "", "", "", "", "");
406 }
407
a8fa25d1 408 if ($oldversion < 2003072800) {
409 print_simple_box("The following database index improves performance, but can be quite large - if you are upgrading and you have problems with a limited quota you may want to delete this index later from the '{$CFG->prefix}log' table in your database", "center", "50%", "$THEME->cellheading", "20", "noticebox");
1dbb6e50 410 flush();
a8fa25d1 411 execute_sql(" ALTER TABLE `{$CFG->prefix}log` ADD INDEX timecoursemoduleaction (time,course,module,action) ");
412 execute_sql(" ALTER TABLE `{$CFG->prefix}user_students` ADD INDEX courseuserid (course,userid) ");
413 execute_sql(" ALTER TABLE `{$CFG->prefix}user_teachers` ADD INDEX courseuserid (course,userid) ");
414 }
415
c2cb4545 416 if ($oldversion < 2003072803) {
417 table_column("course_categories", "", "description", "text", "", "", "");
418 table_column("course_categories", "", "parent", "integer", "10", "unsigned");
419 table_column("course_categories", "", "sortorder", "integer", "10", "unsigned");
420 table_column("course_categories", "", "courseorder", "text", "", "", "");
421 table_column("course_categories", "", "visible", "integer", "1", "unsigned", "1");
422 table_column("course_categories", "", "timemodified", "integer", "10", "unsigned");
423 }
424
d2b6ba70 425 if ($oldversion < 2003080400) {
426 table_column("course_categories", "courseorder", "courseorder", "integer", "10", "unsigned");
427 table_column("course", "", "sortorder", "integer", "10", "unsigned", "0", "", "category");
428 }
429
430 if ($oldversion < 2003080700) {
431 notify("Cleaning up categories and course ordering...");
6bc502cc 432 fix_course_sortorder();
d2b6ba70 433 }
434
50b5487c 435 if ($oldversion < 2003081001) {
436 table_column("course", "format", "format", "varchar", "10", "", "topics");
437 }
438
02ebf404 439 if ($oldversion < 2003081500) {
5fcd1b52 440// print_simple_box("Some important changes have been made to how course creators work. Formerly, they could create new courses and assign teachers, and teachers could edit courses. Now, ordinary teachers can no longer edit courses - they <b>need to be a teacher of a course AND a course creator</b>. A new site-wide configuration variable allows you to choose whether to allow course creators to create new courses as well (by default this is off). <p>The following update will automatically convert all your existing teachers into course creators, to maintain backward compatibility. Make sure you look at your upgraded site carefully and understand these new changes.", "center", "50%", "$THEME->cellheading", "20", "noticebox");
10ee08cc 441
5fcd1b52 442// $count = 0;
443// $errorcount = 0;
444// if ($teachers = get_records("user_teachers")) {
445// foreach ($teachers as $teacher) {
446// if (! record_exists("user_coursecreators", "userid", $teacher->userid)) {
447// $creator = NULL;
448// $creator->userid = $teacher->userid;
449// if (!insert_record("user_coursecreators", $creator)) {
450// $errorcount++;
451// } else {
452// $count++;
453// }
454// }
455// }
456// }
457// print_simple_box("$count teachers were upgraded to course creators (with $errorcount errors)", "center", "50%", "$THEME->cellheading", "20", "noticebox");
02ebf404 458
459 }
460
37eef3ed 461 if ($oldversion < 2003081501) {
462 execute_sql(" CREATE TABLE `{$CFG->prefix}scale` (
463 `id` int(10) unsigned NOT NULL auto_increment,
464 `courseid` int(10) unsigned NOT NULL default '0',
465 `userid` int(10) unsigned NOT NULL default '0',
466 `name` varchar(255) NOT NULL default '',
467 `scale` text NOT NULL,
468 `description` text NOT NULL,
469 `timemodified` int(10) unsigned NOT NULL default '0',
470 PRIMARY KEY (id)
471 ) TYPE=MyISAM COMMENT='Defines grading scales'");
37eef3ed 472
6f4f04df 473 }
11402bbd 474
475 if ($oldversion < 2003081503) {
6f4f04df 476 table_column("forum", "", "scale", "integer", "10", "unsigned", "0", "", "assessed");
11402bbd 477 get_scales_menu(0); // Just to force the default scale to be created
478 }
479
73047f2f 480 if ($oldversion < 2003081600) {
481 table_column("user_teachers", "", "editall", "integer", "1", "unsigned", "1", "", "role");
482 table_column("user_teachers", "", "timemodified", "integer", "10", "unsigned", "0", "", "editall");
483 }
11402bbd 484
9936fe81 485 if ($oldversion < 2003081900) {
486 table_column("course_categories", "courseorder", "coursecount", "integer", "10", "unsigned", "0");
487 }
488
fff79722 489 if ($oldversion < 2003082001) {
490 table_column("course", "", "showgrades", "integer", "2", "unsigned", "1", "", "format");
491 }
492
3052e775 493 if ($oldversion < 2003082101) {
494 execute_sql(" ALTER TABLE `{$CFG->prefix}course` ADD INDEX category (category) ");
495 }
d0117715 496 if ($oldversion < 2003082702) {
497 execute_sql(" INSERT INTO {$CFG->prefix}log_display (module, action, mtable, field) VALUES ('course', 'user report', 'user', 'CONCAT(firstname,\" \",lastname)') ");
498 }
3052e775 499
aac94fd0 500 if ($oldversion < 2003091400) {
501 table_column("course_modules", "", "indent", "integer", "5", "unsigned", "0", "", "score");
502 }
503
4909e176 504 if ($oldversion < 2003092900) {
505 table_column("course", "", "maxbytes", "integer", "10", "unsigned", "0", "", "marker");
506 }
507
4d744a22 508 if ($oldversion < 2003102700) {
509 table_column("user_students", "", "timeaccess", "integer", "10", "unsigned", "0", "", "time");
510 table_column("user_teachers", "", "timeaccess", "integer", "10", "unsigned", "0", "", "timemodified");
511
4d744a22 512 $db->debug = false;
29b25b04 513 $CFG->debug = 0;
514 notify("Calculating access times. Please wait - this may take a long time on big sites...", "green");
515 flush();
516
517 if ($courses = get_records_select("course", "category > 0")) {
518 foreach ($courses as $course) {
519 notify("Processing $course->fullname ...", "green");
520 flush();
10ee08cc 521 if ($users = get_records_select("user_teachers", "course = '$course->id'",
29b25b04 522 "id", "id, userid, timeaccess")) {
523 foreach ($users as $user) {
524 $loginfo = get_record_sql("SELECT id, time FROM {$CFG->prefix}log WHERE course = '$course->id' and userid = '$user->userid' ORDER by time DESC");
525 if (empty($loginfo->time)) {
526 $loginfo->time = 0;
527 }
10ee08cc 528 execute_sql("UPDATE {$CFG->prefix}user_teachers SET timeaccess = '$loginfo->time'
29b25b04 529 WHERE userid = '$user->userid' AND course = '$course->id'", false);
10ee08cc 530
29b25b04 531 }
532 }
533
10ee08cc 534 if ($users = get_records_select("user_students", "course = '$course->id'",
29b25b04 535 "id", "id, userid, timeaccess")) {
536 foreach ($users as $user) {
10ee08cc 537 $loginfo = get_record_sql("SELECT id, time FROM {$CFG->prefix}log
538 WHERE course = '$course->id' and userid = '$user->userid'
29b25b04 539 ORDER by time DESC");
540 if (empty($loginfo->time)) {
541 $loginfo->time = 0;
542 }
10ee08cc 543 execute_sql("UPDATE {$CFG->prefix}user_students
544 SET timeaccess = '$loginfo->time'
29b25b04 545 WHERE userid = '$user->userid' AND course = '$course->id'", false);
10ee08cc 546
29b25b04 547 }
548 }
549 }
4d744a22 550 }
29b25b04 551 notify("All courses complete.", "green");
4d744a22 552 $db->debug = true;
553 }
554
3f125001 555 if ($oldversion < 2003103100) {
556 table_column("course", "", "showreports", "integer", "4", "unsigned", "0", "", "maxbytes");
557 }
558
f374fb10 559 if ($oldversion < 2003121600) {
0da33e07 560 modify_database("", "CREATE TABLE `prefix_groups` (
f374fb10 561 `id` int(10) unsigned NOT NULL auto_increment,
562 `courseid` int(10) unsigned NOT NULL default '0',
563 `name` varchar(254) NOT NULL default '',
564 `description` text NOT NULL,
565 `lang` varchar(10) NOT NULL default 'en',
566 `picture` int(10) unsigned NOT NULL default '0',
567 `timecreated` int(10) unsigned NOT NULL default '0',
568 `timemodified` int(10) unsigned NOT NULL default '0',
569 PRIMARY KEY (`id`),
570 KEY `courseid` (`courseid`)
571 ) TYPE=MyISAM COMMENT='Each record is a group in a course.'; ");
572
0da33e07 573 modify_database("", "CREATE TABLE `prefix_groups_members` (
f374fb10 574 `id` int(10) unsigned NOT NULL auto_increment,
575 `groupid` int(10) unsigned NOT NULL default '0',
576 `userid` int(10) unsigned NOT NULL default '0',
577 `timeadded` int(10) unsigned NOT NULL default '0',
578 PRIMARY KEY (`id`),
579 KEY `groupid` (`groupid`)
580 ) TYPE=MyISAM COMMENT='Lists memberships of users in groups'; ");
581 }
582
583 if ($oldversion < 2003121800) {
584 table_column("course", "modinfo", "modinfo", "longtext", "", "", "");
585 }
586
587 if ($oldversion < 2003122600) {
588 table_column("course", "", "groupmode", "integer", "4", "unsigned", "0", "", "showreports");
589 table_column("course", "", "groupmodeforce", "integer", "4", "unsigned", "0", "", "groupmode");
590 }
591
ddc7afad 592 if ($oldversion < 2004010900) {
593 table_column("course_modules", "", "groupmode", "integer", "4", "unsigned", "0", "", "visible");
594 }
595
5fba04fb 596 if ($oldversion < 2004011700) {
597 modify_database("", "CREATE TABLE `prefix_event` (
598 `id` int(10) unsigned NOT NULL auto_increment,
599 `name` varchar(255) NOT NULL default '',
600 `description` text NOT NULL,
601 `courseid` int(10) unsigned NOT NULL default '0',
602 `groupid` int(10) unsigned NOT NULL default '0',
603 `userid` int(10) unsigned NOT NULL default '0',
604 `modulename` varchar(20) NOT NULL default '',
605 `instance` int(10) unsigned NOT NULL default '0',
606 `eventtype` varchar(20) NOT NULL default '',
607 `timestart` int(10) unsigned NOT NULL default '0',
608 `timeduration` int(10) unsigned NOT NULL default '0',
609 `timemodified` int(10) unsigned NOT NULL default '0',
610 PRIMARY KEY (`id`),
611 UNIQUE KEY `id` (`id`),
612 KEY `courseid` (`courseid`),
613 KEY `userid` (`userid`)
614 ) TYPE=MyISAM COMMENT='For everything with a time associated to it'; ");
615 }
616
70812e39 617 if ($oldversion < 2004012800) {
618 modify_database("", "CREATE TABLE `prefix_user_preferences` (
619 `id` int(10) unsigned NOT NULL auto_increment,
620 `userid` int(10) unsigned NOT NULL default '0',
621 `name` varchar(50) NOT NULL default '',
622 `value` varchar(255) NOT NULL default '',
623 PRIMARY KEY (`id`),
624 UNIQUE KEY `id` (`id`),
625 KEY `useridname` (userid,name)
626 ) TYPE=MyISAM COMMENT='Allows modules to store arbitrary user preferences'; ");
627 }
628
ba39fa10 629 if ($oldversion < 2004012900) {
630 table_column("config", "value", "value", "text", "", "", "");
631 }
70812e39 632
69d79bc3 633 if ($oldversion < 2004013101) {
634 table_column("log", "", "cmid", "integer", "10", "unsigned", "0", "", "module");
4da1a0a1 635 set_config("upgrade", "logs");
69d79bc3 636 }
637
b3153e4b 638 if ($oldversion < 2004020900) {
639 table_column("course", "", "lang", "varchar", "5", "", "", "", "groupmodeforce");
640 }
641
45121ffb 642 if ($oldversion < 2004020903) {
643 modify_database("", "CREATE TABLE `prefix_cache_text` (
d363047e 644 `id` int(10) unsigned NOT NULL auto_increment,
645 `md5key` varchar(32) NOT NULL default '',
646 `formattedtext` longtext NOT NULL,
647 `timemodified` int(10) unsigned NOT NULL default '0',
648 PRIMARY KEY (`id`),
649 KEY `md5key` (`md5key`)
650 ) TYPE=MyISAM COMMENT='For storing temporary copies of processed texts';");
651 }
652
d523d2ea 653 if ($oldversion < 2004021000) {
654 $textfilters = array();
655 for ($i=1; $i<=10; $i++) {
656 $variable = "textfilter$i";
657 if (!empty($CFG->$variable)) { /// No more filters
658 if (is_readable("$CFG->dirroot/".$CFG->$variable)) {
659 $textfilters[] = $CFG->$variable;
660 }
661 }
662 }
663 $textfilters = implode(',', $textfilters);
664 if (empty($textfilters)) {
665 $textfilters = 'mod/glossary/dynalink.php';
666 }
667 set_config('textfilters', $textfilters);
668 }
669
f1d604cb 670 if ($oldversion < 2004021201) {
671 modify_database("", "CREATE TABLE `prefix_cache_filters` (
672 `id` int(10) unsigned NOT NULL auto_increment,
673 `filter` varchar(32) NOT NULL default '',
674 `version` int(10) unsigned NOT NULL default '0',
675 `md5key` varchar(32) NOT NULL default '',
676 `rawtext` text NOT NULL,
677 `timemodified` int(10) unsigned NOT NULL default '0',
678 PRIMARY KEY (`id`),
679 KEY `filtermd5key` (filter,md5key)
680 ) TYPE=MyISAM COMMENT='For keeping information about cached data';");
681 }
682
3c0561cf 683 if ($oldversion < 2004021500) {
684 table_column("groups", "", "hidepicture", "integer", "2", "unsigned", "0", "", "picture");
685 }
10ee08cc 686
9e353ce7 687 if ($oldversion < 2004021700) {
688 if (!empty($CFG->textfilters)) {
689 $CFG->textfilters = str_replace("tex_filter.php", "filter.php", $CFG->textfilters);
690 $CFG->textfilters = str_replace("multilang.php", "filter.php", $CFG->textfilters);
691 $CFG->textfilters = str_replace("censor.php", "filter.php", $CFG->textfilters);
692 $CFG->textfilters = str_replace("mediaplugin.php", "filter.php", $CFG->textfilters);
693 $CFG->textfilters = str_replace("algebra_filter.php", "filter.php", $CFG->textfilters);
828aeff2 694 $CFG->textfilters = str_replace("dynalink.php", "filter.php", $CFG->textfilters);
9e353ce7 695 set_config("textfilters", $CFG->textfilters);
696 }
697 }
d523d2ea 698
8199e3f0 699 if ($oldversion < 2004022000) {
700 table_column("user", "", "emailstop", "integer", "1", "unsigned", "0", "not null", "email");
701 }
702
828aeff2 703 if ($oldversion < 2004022200) { /// Final renaming I hope. :-)
704 if (!empty($CFG->textfilters)) {
705 $CFG->textfilters = str_replace("/filter.php", "", $CFG->textfilters);
a4dda51f 706 $CFG->textfilters = str_replace("mod/glossary/dynalink.php", "mod/glossary", $CFG->textfilters);
828aeff2 707 $textfilters = explode(',', $CFG->textfilters);
708 foreach ($textfilters as $key => $textfilter) {
709 $textfilters[$key] = trim($textfilter);
710 }
711 set_config("textfilters", implode(',',$textfilters));
712 }
713 }
714
b4443d8f 715 if ($oldversion < 2004030702) { /// Because of the renaming of Czech language pack
4bf58667 716 execute_sql("UPDATE {$CFG->prefix}user SET lang = 'cs' WHERE lang = 'cz'");
717 execute_sql("UPDATE {$CFG->prefix}course SET lang = 'cs' WHERE lang = 'cz'");
7277e306 718 }
719
0f3fe4b6 720 if ($oldversion < 2004041800) { /// Integrate Block System from contrib
721 table_column("course", "", "blockinfo", "varchar", "255", "", "", "not null", "modinfo");
722 }
723
822ede92 724 if ($oldversion < 2004042600) { /// Rebuild course caches for resource icons
725 include_once("$CFG->dirroot/course/lib.php");
726 rebuild_course_cache();
727 }
728
cda21d48 729 if ($oldversion < 2004042700) { /// Increase size of lang fields
37aaf074 730 table_column("user", "lang", "lang", "varchar", "10", "", "en");
731 table_column("groups", "lang", "lang", "varchar", "10", "", "");
732 table_column("course", "lang", "lang", "varchar", "10", "", "");
733 }
734
009cc726 735 if ($oldversion < 2004042701) { /// Add hiddentopics field to control hidden topics behaviour
736 table_column("course", "", "hiddentopics", "integer", "1", "unsigned", "0", "not null", "visible");
737 }
738
cda21d48 739 if ($oldversion < 2004042702) { /// add a format field for the description
740 table_column("event", "", "format", "integer", "4", "unsigned", "0", "not null", "description");
741 }
742
10ee08cc 743 if ($oldversion < 2004042900) {
744 execute_sql(" ALTER TABLE `{$CFG->prefix}course` DROP `showrecent` ");
745 }
746
464fd803 747 if ($oldversion < 2004043001) { /// Change hiddentopics to hiddensections
748 table_column("course", "hiddentopics", "hiddensections", "integer", "2", "unsigned", "0", "not null");
749 }
750
dcd338ff 751 if ($oldversion < 2004050400) { /// add a visible field for events
752 table_column("event", "", "visible", "tinyint", "1", "", "1", "not null", "timeduration");
753 if ($events = get_records('event')) {
754 foreach($events as $event) {
755 if ($moduleid = get_field('modules', 'id', 'name', $event->modulename)) {
756 if (get_field('course_modules', 'visible', 'module', $moduleid, 'instance', $event->instance) == 0) {
757 set_field('event', 'visible', 0, 'id', $event->id);
758 }
759 }
760 }
761 }
762 }
763
3cac987a 764 if ($oldversion < 2004052800) { /// First version tagged "1.4 development", version.php 1.227
e02c35b2 765 set_config('siteblocksadded', true); /// This will be used later by the block upgrade
3cac987a 766 }
767
62b80756 768 if ($oldversion < 2004053000) { /// set defaults for site course
769 $site = get_site();
a7e07837 770 set_field('course', 'numsections', 0, 'id', $site->id);
62b80756 771 set_field('course', 'groupmodeforce', 1, 'id', $site->id);
772 set_field('course', 'teacher', get_string('administrator'), 'id', $site->id);
773 set_field('course', 'teachers', get_string('administrators'), 'id', $site->id);
774 set_field('course', 'student', get_string('user'), 'id', $site->id);
775 set_field('course', 'students', get_string('users'), 'id', $site->id);
776 }
777
cc21211e 778 if ($oldversion < 2004060100) {
779 set_config('digestmailtime', 0);
780 table_column('user', "", 'maildigest', 'tinyint', '1', '', '0', 'not null', 'mailformat');
cc21211e 781 }
782
faef9f7b 783 if ($oldversion < 2004062400) {
784 table_column('user_teachers', "", 'timeend', 'int', '10', 'unsigned', '0', 'not null', 'editall');
785 table_column('user_teachers', "", 'timestart', 'int', '10', 'unsigned', '0', 'not null', 'editall');
786 }
787
838ee71b 788 if ($oldversion < 2004062401) {
789 table_column('course', '', 'idnumber', 'varchar', '100', '', '', 'not null', 'shortname');
790 execute_sql('UPDATE '.$CFG->prefix.'course SET idnumber = shortname'); // By default
791 }
792
8c5c6133 793 if ($oldversion < 2004062600) {
794 table_column('course', '', 'cost', 'varchar', '10', '', '', 'not null', 'lang');
795 }
796
18763fd4 797 if ($oldversion < 2004072900) {
798 table_column('course', '', 'enrolperiod', 'int', '10', 'unsigned', '0', 'not null', 'startdate');
799 }
800
f28db22d 801 if ($oldversion < 2004072901) { // Fixing error in schema
802 if ($record = get_record('log_display', 'module', 'course', 'action', 'update')) {
803 delete_records('log_display', 'module', 'course', 'action', 'update');
804 insert_record('log_display', $record, false, 'module');
805 }
806 }
838ee71b 807
9fb216e8 808 if ($oldversion < 2004081200) { // Fixing version errors in some blocks
809 set_field('blocks', 'version', 2004081200, 'name', 'admin');
810 set_field('blocks', 'version', 2004081200, 'name', 'calendar_month');
811 set_field('blocks', 'version', 2004081200, 'name', 'course_list');
812 }
813
4e11ad4f 814 if ($oldversion < 2004081500) { // Adding new "auth" field to user table to allow more flexibility
815 table_column('user', '', 'auth', 'varchar', '20', '', 'manual', 'not null', 'id');
816
817 execute_sql("UPDATE {$CFG->prefix}user SET auth = 'manual'"); // Set everyone to 'manual' to be sure
818
819 if ($admins = get_admins()) { // Set all the NON-admins to whatever the current auth module is
820 $adminlist = array();
821 foreach ($admins as $user) {
822 $adminlist[] = $user->id;
823 }
824 $adminlist = implode(',', $adminlist);
825 execute_sql("UPDATE {$CFG->prefix}user SET auth = '$CFG->auth' WHERE id NOT IN ($adminlist)");
826 }
827 }
f950af3c 828
829 if ($oldversion < 2004082200) { // Making admins teachers on site course
830 $site = get_site();
831 $admins = get_admins();
832 foreach ($admins as $admin) {
833 add_teacher($admin->id, $site->id);
834 }
835 }
9fb216e8 836
c14964b0 837 if ($oldversion < 2004082600) {
838 //update auth-fields for external users
839 include_once ($CFG->dirroot."/auth/".$CFG->auth."/lib.php");
840 if (function_exists('auth_get_userlist')) {
841 $externalusers = auth_get_userlist();
842 if (!empty($externalusers)){
843 $externalusers = '\''. implode('\',\'',$externalusers).'\'';
844 execute_sql("UPDATE {$CFG->prefix}user SET auth = '$CFG->auth' WHERE username IN ($externalusers)");
845 }
846 }
847 }
848
3cac987a 849 if ($oldversion < 2004082900) { // Make sure guest is "manual" too.
850 set_field('user', 'auth', 'manual', 'username', 'guest');
851 }
01e2ea5f 852
d35757eb 853 /* Commented out unused guid-field code
01e2ea5f 854 if ($oldversion < 2004090300) { // Add guid-field used in user syncronization
855 table_column('user', '', 'guid', 'varchar', '128', '', '', '', 'auth');
856 execute_sql("ALTER TABLE {$CFG->prefix}user ADD INDEX authguid (auth, guid)");
857 }
d35757eb 858 */
859
860 if ($oldversion < 2004091900) { // modify idnumber to hold longer values
052fe105 861 table_column('user', 'idnumber', 'idnumber', 'varchar', '64', '', '', '', '');
17f3e7d0 862 execute_sql("ALTER TABLE {$CFG->prefix}user DROP INDEX user_idnumber",false); // added in case of conflicts with upgrade from 14stable
863 execute_sql("ALTER TABLE {$CFG->prefix}user DROP INDEX user_auth",false); // added in case of conflicts with upgrade from 14stable
864
d35757eb 865 execute_sql("ALTER TABLE {$CFG->prefix}user ADD INDEX idnumber (idnumber)");
866 execute_sql("ALTER TABLE {$CFG->prefix}user ADD INDEX auth (auth)");
867 }
3cac987a 868
42b90599 869 if ($oldversion < 2004093001) { // add new table for sessions storage
870 execute_sql(" CREATE TABLE `{$CFG->prefix}sessions` (
871 `sesskey` char(32) NOT null,
872 `expiry` int(11) unsigned NOT null,
873 `expireref` varchar(64),
874 `data` text NOT null,
875 PRIMARY KEY (`sesskey`),
876 KEY (`expiry`)
877 ) TYPE=MyISAM COMMENT='Optional database session storage, not used by default';");
878 }
879
bb043e38 880 if ($oldversion < 2004111500) { // Update any users/courses using wrongly-named lang pack
881 execute_sql("UPDATE {$CFG->prefix}user SET lang = 'mi_nt' WHERE lang = 'ma_nt'");
882 execute_sql("UPDATE {$CFG->prefix}course SET lang = 'mi_nt' WHERE lang = 'ma_nt'");
883 }
884
17f3e7d0 885 if ($oldversion < 2004111700) { // add indexes. - drop them first silently to avoid conflicts when upgrading.
886 execute_sql(" ALTER TABLE `{$CFG->prefix}course` DROP INDEX idnumber;",false);
887 execute_sql(" ALTER TABLE `{$CFG->prefix}course` DROP INDEX shortname;",false);
888 execute_sql(" ALTER TABLE `{$CFG->prefix}user_students` DROP INDEX userid;",false);
889 execute_sql(" ALTER TABLE `{$CFG->prefix}user_teachers` DROP INDEX userid;",false);
890
891 execute_sql(" ALTER TABLE `{$CFG->prefix}course` ADD INDEX idnumber (idnumber);");
892 execute_sql(" ALTER TABLE `{$CFG->prefix}course` ADD INDEX shortname (shortname);");
893 execute_sql(" ALTER TABLE `{$CFG->prefix}user_students` ADD INDEX userid (userid);");
894 execute_sql(" ALTER TABLE `{$CFG->prefix}user_teachers` ADD INDEX userid (userid);");
895 }
896
897 if ($oldversion < 2004111700) {// add an index to event for timestart and timeduration. - drop them first silently to avoid conflicts when upgrading.
275c630a 898 execute_sql("ALTER TABLE {$CFG->prefix}event DROP INDEX timestart;",false);
899 execute_sql("ALTER TABLE {$CFG->prefix}event DROP INDEX timeduration;",false);
17f3e7d0 900
901 modify_database('','ALTER TABLE prefix_event ADD INDEX timestart (timestart);');
902 modify_database('','ALTER TABLE prefix_event ADD INDEX timeduration (timeduration);');
903 }
904
905 if ($oldversion < 2004111700) { //add indexes on modules and course_modules. - drop them first silently to avoid conflicts when upgrading.
275c630a 906 execute_sql("ALTER TABLE {$CFG->prefix}course_modules drop key visible;",false);
907 execute_sql("ALTER TABLE {$CFG->prefix}course_modules drop key course;",false);
908 execute_sql("ALTER TABLE {$CFG->prefix}course_modules drop key module;",false);
909 execute_sql("ALTER TABLE {$CFG->prefix}course_modules drop key instance;",false);
910 execute_sql("ALTER TABLE {$CFG->prefix}course_modules drop key deleted;",false);
911 execute_sql("ALTER TABLE {$CFG->prefix}modules drop key name;",false);
17f3e7d0 912
913 modify_database('','ALTER TABLE prefix_course_modules add key visible(visible);');
914 modify_database('','ALTER TABLE prefix_course_modules add key course(course);');
915 modify_database('','ALTER TABLE prefix_course_modules add key module(module);');
916 modify_database('','ALTER TABLE prefix_course_modules add key instance (instance);');
917 modify_database('','ALTER TABLE prefix_course_modules add key deleted (deleted);');
918 modify_database('','ALTER TABLE prefix_modules add key name(name);');
919 }
920
921 if ($oldversion < 2004111700) { // add an index on the groups_members table. - drop them first silently to avoid conflicts when upgrading.
275c630a 922 execute_sql("ALTER TABLE {$CFG->prefix}groups_members DROP INDEX userid;",false);
17f3e7d0 923
924 modify_database('','ALTER TABLE prefix_groups_members ADD INDEX userid (userid);');
925 }
926
927 if ($oldversion < 2004111700) { // add an index on user students timeaccess (used for sorting)- drop them first silently to avoid conflicts when upgrading
275c630a 928 execute_sql("ALTER TABLE {$CFG->prefix}user_students DROP INDEX timeaccess;",false);
17f3e7d0 929
930 modify_database('','ALTER TABLE prefix_user_students ADD INDEX timeaccess (timeaccess);');
931 }
932
933 if ($oldversion < 2004111700) { // add indexes on faux-foreign keys. - drop them first silently to avoid conflicts when upgrading.
275c630a 934 execute_sql("ALTER TABLE {$CFG->prefix}scale DROP INDEX courseid;",false);
935 execute_sql("ALTER TABLE {$CFG->prefix}user_admins DROP INDEX userid;",false);
936 execute_sql("ALTER TABLE {$CFG->prefix}user_coursecreators DROP INDEX userid;",false);
17f3e7d0 937
938 modify_database('','ALTER TABLE prefix_scale ADD INDEX courseid (courseid);');
939 modify_database('','ALTER TABLE prefix_user_admins ADD INDEX userid (userid);');
940 modify_database('','ALTER TABLE prefix_user_coursecreators ADD INDEX userid (userid);');
941 }
ffa5f71b 942
943 if ($oldversion < 2004111700) { // replace index on course
944 fix_course_sortorder(0,0,1);
275c630a 945 execute_sql("ALTER TABLE `{$CFG->prefix}course` DROP KEY category",false);
ffa5f71b 946
275c630a 947 execute_sql("ALTER TABLE `{$CFG->prefix}course` DROP KEY category_sortorder;",false);
ffa5f71b 948 modify_database('', "ALTER TABLE `prefix_course` ADD UNIQUE KEY category_sortorder(category,sortorder)");
949
275c630a 950 execute_sql("ALTER TABLE `{$CFG->prefix}user` DROP INDEX {$CFG->prefix}user_deleted_idx;",false);
951 execute_sql("ALTER TABLE `{$CFG->prefix}user` DROP INDEX {$CFG->prefix}user_confirmed_idx;",false);
952 execute_sql("ALTER TABLE `{$CFG->prefix}user` DROP INDEX {$CFG->prefix}user_firstname_idx;",false);
953 execute_sql("ALTER TABLE `{$CFG->prefix}user` DROP INDEX {$CFG->prefix}user_lastname_idx;",false);
954 execute_sql("ALTER TABLE `{$CFG->prefix}user` DROP INDEX {$CFG->prefix}user_city_idx;",false);
955 execute_sql("ALTER TABLE `{$CFG->prefix}user` DROP INDEX {$CFG->prefix}user_country_idx;",false);
956 execute_sql("ALTER TABLE `{$CFG->prefix}user` DROP INDEX {$CFG->prefix}user_lastaccess_idx;",false);
ffa5f71b 957
958 modify_database("", "ALTER TABLE `prefix_user` ADD INDEX prefix_user_deleted_idx (deleted)");
959 modify_database("", "ALTER TABLE `prefix_user` ADD INDEX prefix_user_confirmed_idx (confirmed)");
960 modify_database("", "ALTER TABLE `prefix_user` ADD INDEX prefix_user_firstname_idx (firstname)");
961 modify_database("", "ALTER TABLE `prefix_user` ADD INDEX prefix_user_lastname_idx (lastname)");
962 modify_database("", "ALTER TABLE `prefix_user` ADD INDEX prefix_user_city_idx (city)");
963 modify_database("", "ALTER TABLE `prefix_user` ADD INDEX prefix_user_country_idx (country)");
964 modify_database("", "ALTER TABLE `prefix_user` ADD INDEX prefix_user_lastaccess_idx (lastaccess)");
965 }
966
967 if ($oldversion < 2004111700) { // one more index for email (for sorting)
275c630a 968 execute_sql("ALTER TABLE `{$CFG->prefix}user` DROP INDEX {$CFG->prefix}user_email_idx;",false);
ffa5f71b 969 modify_database('','ALTER TABLE `prefix_user` ADD INDEX prefix_user_email_idx (email);');
970 }
971
5cd79686 972 if ($oldversion < 2004112200) { // new 'enrol' field for enrolment tables
973 table_column('user_students', '', 'enrol', 'varchar', '20', '', '', 'not null');
974 table_column('user_teachers', '', 'enrol', 'varchar', '20', '', '', 'not null');
975 execute_sql("ALTER TABLE `{$CFG->prefix}user_students` ADD INDEX enrol (enrol);");
976 execute_sql("ALTER TABLE `{$CFG->prefix}user_teachers` ADD INDEX enrol (enrol);");
977 }
17f3e7d0 978
b110348b 979 if ($oldversion < 2004112400) {
980 /// Delete duplicate enrolments
981 /// and then tell the database course,userid is a unique combination
982 if ($users = get_records_select("user_students", "userid > 0 GROUP BY course, userid ".
983 "HAVING count(*) > 1", "", "max(id) as id, userid, course ,count(*)")) {
984 foreach ($users as $user) {
985 delete_records_select("user_students", "userid = '$user->userid' ".
986 "AND course = '$user->course' AND id <> '$user->id'");
987 }
988 }
989 flush();
990
991 modify_database('','ALTER TABLE prefix_user_students DROP INDEX courseuserid;');
992 modify_database('','ALTER TABLE prefix_user_students ADD UNIQUE INDEX courseuserid(course,userid);');
993
994 /// Delete duplicate teacher enrolments
995 /// and then tell the database course,userid is a unique combination
996 if ($users = get_records_select("user_teachers", "userid > 0 GROUP BY course, userid ".
997 "HAVING count(*) > 1", "", "max(id) as id, userid, course ,count(*)")) {
998 foreach ($users as $user) {
999 delete_records_select("user_teachers", "userid = '$user->userid' ".
1000 "AND course = '$user->course' AND id <> '$user->id'");
1001 }
1002 }
1003 flush();
1004 modify_database('','ALTER TABLE prefix_user_teachers DROP INDEX courseuserid;');
1005 modify_database('','ALTER TABLE prefix_user_teachers ADD UNIQUE INDEX courseuserid(course,userid);');
1006 }
027a1604 1007
1008 if ($oldversion < 2004112900) {
1009 table_column('user', '', 'policyagreed', 'integer', '1', 'unsigned', '0', 'not null', 'confirmed');
1010 }
1011
1a5bc046 1012 if ($oldversion < 2004121400) {
1013 table_column('groups', '', 'password', 'varchar', '50', '', '', 'not null', 'description');
1014 }
86f092d2 1015
1016 if ($oldversion < 2004121500) {
1017 modify_database('',"CREATE TABLE prefix_dst_preset (
1018 id int(10) NOT NULL auto_increment,
1019 name char(48) default '' NOT NULL,
1020
1021 apply_offset tinyint(3) default '0' NOT NULL,
1022
1023 activate_index tinyint(1) default '1' NOT NULL,
1024 activate_day tinyint(1) default '1' NOT NULL,
1025 activate_month tinyint(2) default '1' NOT NULL,
1026 activate_time char(5) default '03:00' NOT NULL,
1027
1028 deactivate_index tinyint(1) default '1' NOT NULL,
1029 deactivate_day tinyint(1) default '1' NOT NULL,
1030 deactivate_month tinyint(2) default '2' NOT NULL,
1031 deactivate_time char(5) default '03:00' NOT NULL,
1032
1033 last_change int(10) default '0' NOT NULL,
1034 next_change int(10) default '0' NOT NULL,
1035 current_offset tinyint(3) default '0' NOT NULL,
1036
1037 PRIMARY KEY (id))");
1038 }
ffce79c0 1039
0eacb23c 1040 if ($oldversion < 2004122800) {
1041 execute_sql("DROP TABLE {$CFG->prefix}message", false);
1042 execute_sql("DROP TABLE {$CFG->prefix}message_read", false);
1043 execute_sql("DROP TABLE {$CFG->prefix}message_contacts", false);
1044
ffce79c0 1045 modify_database('',"CREATE TABLE `prefix_message` (
1046 `id` int(10) unsigned NOT NULL auto_increment,
1047 `useridfrom` int(10) NOT NULL default '0',
1048 `useridto` int(10) NOT NULL default '0',
1049 `message` text NOT NULL,
0eacb23c 1050 `timecreated` int(10) NOT NULL default '0',
ffce79c0 1051 `messagetype` varchar(50) NOT NULL default '',
1052 PRIMARY KEY (`id`),
1053 KEY `useridfrom` (`useridfrom`),
1054 KEY `useridto` (`useridto`)
1055 ) TYPE=MyISAM COMMENT='Stores all unread messages';");
1056
1057 modify_database('',"CREATE TABLE `prefix_message_read` (
1058 `id` int(10) unsigned NOT NULL auto_increment,
1059 `useridfrom` int(10) NOT NULL default '0',
1060 `useridto` int(10) NOT NULL default '0',
1061 `message` text NOT NULL,
0eacb23c 1062 `timecreated` int(10) NOT NULL default '0',
1063 `timeread` int(10) NOT NULL default '0',
ffce79c0 1064 `messagetype` varchar(50) NOT NULL default '',
1065 `mailed` tinyint(1) NOT NULL default '0',
1066 PRIMARY KEY (`id`),
1067 KEY `useridfrom` (`useridfrom`),
1068 KEY `useridto` (`useridto`)
1069 ) TYPE=MyISAM COMMENT='Stores all messages that have been read';");
0eacb23c 1070
1071 modify_database('',"CREATE TABLE `prefix_message_contacts` (
1072 `id` int(10) unsigned NOT NULL auto_increment,
1073 `userid` int(10) unsigned NOT NULL default '0',
1074 `contactid` int(10) unsigned NOT NULL default '0',
1075 `blocked` tinyint(1) unsigned NOT NULL default '0',
1076 PRIMARY KEY (`id`),
1077 UNIQUE KEY `usercontact` (`userid`,`contactid`)
1078 ) TYPE=MyISAM COMMENT='Maintains lists of relationships between users';");
1079
1080 modify_database('', "INSERT INTO prefix_log_display VALUES ('message', 'write', 'user', 'CONCAT(firstname,\" \",lastname)'); ");
1081 modify_database('', "INSERT INTO prefix_log_display VALUES ('message', 'read', 'user', 'CONCAT(firstname,\" \",lastname)'); ");
ffce79c0 1082 }
0eacb23c 1083
e53c030b 1084 if ($oldversion < 2004122801) {
1085 table_column('message', '', 'format', 'integer', '4', 'unsigned', '0', 'not null', 'message');
1086 table_column('message_read', '', 'format', 'integer', '4', 'unsigned', '0', 'not null', 'message');
1087 }
1088
36f01e7b 1089 if ($oldversion < 2005010100) {
1090 modify_database('', "INSERT INTO prefix_log_display VALUES ('message', 'add contact', 'user', 'CONCAT(firstname,\" \",lastname)'); ");
1091 modify_database('', "INSERT INTO prefix_log_display VALUES ('message', 'remove contact', 'user', 'CONCAT(firstname,\" \",lastname)'); ");
1092 modify_database('', "INSERT INTO prefix_log_display VALUES ('message', 'block contact', 'user', 'CONCAT(firstname,\" \",lastname)'); ");
1093 modify_database('', "INSERT INTO prefix_log_display VALUES ('message', 'unblock contact', 'user', 'CONCAT(firstname,\" \",lastname)'); ");
1094 }
2765411a 1095
1096 if ($oldversion < 2005011000) { // Create a .htaccess file in dataroot, just in case
1097 if (!file_exists($CFG->dataroot.'/.htaccess')) {
1098 if ($handle = fopen($CFG->dataroot.'/.htaccess', 'w')) { // For safety
1099 @fwrite($handle, "deny from all\r\n");
1100 @fclose($handle);
1101 notify("Created a default .htaccess file in $CFG->dataroot");
1102 }
1103 }
1104 }
b61efafb 1105
2765411a 1106
b61efafb 1107 if ($oldversion < 2005012500) {
483b7158 1108 /*
b61efafb 1109 // add new table for meta courses.
1110 modify_database("","CREATE TABLE `prefix_meta_course` (
1111 `id` int(1) unsigned NOT NULL auto_increment,
1112 `parent_course` int(10) NOT NULL default 0,
1113 `child_course` int(10) NOT NULL default 0,
1114 PRIMARY KEY (`id`),
1115 KEY `parent_course` (parent_course),
1116 KEY `child_course` (child_course)
1117 );");
1118 // add flag to course field
1119 table_column('course','','meta_course','integer','1','','0','not null');
483b7158 1120 */ // taking this OUT for upgrade from 1.4 to 1.5 (those tracking head will have already seen it)
b61efafb 1121 }
047d30d1 1122
5f37b628 1123 if ($oldversion < 2005012501) {
1124 execute_sql("DROP TABLE {$CFG->prefix}meta_course",false); // drop silently
1125 execute_sql("ALTER TABLE {$CFG->prefix}course DROP COLUMN meta_course",false); // drop silently
1126
1127 // add new table for meta courses.
1128 modify_database("","CREATE TABLE `prefix_course_meta` (
a22fd258 1129 `id` int(10) unsigned NOT NULL auto_increment,
5f37b628 1130 `parent_course` int(10) NOT NULL default 0,
1131 `child_course` int(10) NOT NULL default 0,
1132 PRIMARY KEY (`id`),
1133 KEY `parent_course` (parent_course),
1134 KEY `child_course` (child_course)
1135 );");
1136 // add flag to course field
1137 table_column('course','','metacourse','integer','1','','0','not null');
1138 }
1139
a22fd258 1140 if ($oldversion < 2005012800) {
1141 // fix a typo (int 1 becomes int 10)
1142 table_column('course_meta','id','id','integer','10','','0','not null');
1143 }
1144
4c3911f9 1145 if ($oldversion < 2005020100) {
1146 fix_course_sortorder(0, 1, 1);
1147 }
1148
8a4e77ae 1149
1150 if ($oldversion < 2005020101) {
1151 // hopefully this is the LAST TIME we need to do this ;)
1152 if ($rows = count_records("course_meta")) {
1153 // we need to upgrade
1154 modify_database("","CREATE TABLE `prefix_course_meta_tmp` (
1155 `parent_course` int(10) NOT NULL default 0,
1156 `child_course` int(10) NOT NULL default 0);");
1157
1158 execute_sql("INSERT INTO {$CFG->prefix}course_meta_tmp (parent_course,child_course)
1159 SELECT {$CFG->prefix}course_meta.parent_course, {$CFG->prefix}course_meta.child_course
1160 FROM {$CFG->prefix}course_meta");
1161 $insertafter = true;
1162 }
1163
1164 execute_sql("DROP TABLE {$CFG->prefix}course_meta");
1165
1166 modify_database("","CREATE TABLE `prefix_course_meta` (
1167 `id` int(10) unsigned NOT NULL auto_increment,
1168 `parent_course` int(10) unsigned NOT NULL default 0,
1169 `child_course` int(10) unsigned NOT NULL default 0,
1170 PRIMARY KEY (`id`),
1171 KEY `parent_course` (parent_course),
1172 KEY `child_course` (child_course));");
1173
1174 if (!empty($insertafter)) {
1175 execute_sql("INSERT INTO {$CFG->prefix}course_meta (parent_course,child_course)
1176 SELECT {$CFG->prefix}course_meta_tmp.parent_course, {$CFG->prefix}course_meta_tmp.child_course
1177 FROM {$CFG->prefix}course_meta_tmp");
1178
1179 execute_sql("DROP TABLE {$CFG->prefix}course_meta_tmp");
1180 }
1181 }
1182
915602ea 1183 if ($oldversion < 2005020800) { // Expand module column to max 20 chars
78345312 1184 table_column('log','module','module','varchar','20','','','not null');
1185 }
1186
915602ea 1187 if ($oldversion < 2005021000) { // New fields for theme choices
1188 table_column('course', '', 'theme', 'varchar', '50', '', '', '', 'lang');
1189 table_column('groups', '', 'theme', 'varchar', '50', '', '', '', 'lang');
1190 table_column('user', '', 'theme', 'varchar', '50', '', '', '', 'lang');
1191 }
e89ff6c6 1192
1193 if ($oldversion < 2005021600) { // course.idnumber should be varchar(100)
1194 table_column('course', 'idnumber', 'idnumber', 'varchar', '100', '', '', '', '');
1195 }
915602ea 1196
41b7678d 1197 if ($oldversion < 2005021700) {
1198 table_column('user', '', 'dstpreset', 'int', '10', '', '0', 'not null', 'timezone');
1199 }
1200
ceff9307 1201 if ($oldversion < 2005021800) { // For database debugging, not for normal use
1202 modify_database(""," CREATE TABLE `adodb_logsql` (
1203 `created` datetime NOT NULL,
1204 `sql0` varchar(250) NOT NULL,
1205 `sql1` text NOT NULL,
1206 `params` text NOT NULL,
1207 `tracer` text NOT NULL,
1208 `timer` decimal(16,6) NOT NULL
1209 );");
1210 }
1211
13222d99 1212 if ($oldversion < 2005022400) {
1213 // Add more visible digits to the fields
1214 table_column('dst_preset', 'activate_index', 'activate_index', 'tinyint', '2', '', '0', 'not null');
1215 table_column('dst_preset', 'activate_day', 'activate_day', 'tinyint', '2', '', '0', 'not null');
1216 // Add family and year fields
1217 table_column('dst_preset', '', 'family', 'varchar', '100', '', '', 'not null', 'name');
1218 table_column('dst_preset', '', 'year', 'int', '10', '', '0', 'not null', 'family');
1219 }
1220
766d2bf3 1221 if ($oldversion < 2005030501) {
1222 table_column('user', '', 'msn', 'varchar', '50', '', '', '', 'icq');
1223 table_column('user', '', 'aim', 'varchar', '50', '', '', '', 'icq');
1224 table_column('user', '', 'yahoo', 'varchar', '50', '', '', '', 'icq');
1225 table_column('user', '', 'skype', 'varchar', '50', '', '', '', 'icq');
1226 }
1227
1789c548 1228 if ($oldversion < 2005032300) {
1229 table_column('user', 'dstpreset', 'timezonename', 'varchar', '100');
1230 execute_sql('UPDATE `'.$CFG->prefix.'user` SET timezonename = \'\'');
1231 }
1232
66336c5f 1233 if ($oldversion < 2005032600) {
1234 execute_sql('DROP TABLE '.$CFG->prefix.'dst_preset', false);
1235 modify_database('',"CREATE TABLE `prefix_timezone` (
1236 `id` int(10) NOT NULL auto_increment,
1237 `name` varchar(100) NOT NULL default '',
1238 `year` int(11) NOT NULL default '0',
1239 `rule` varchar(20) NOT NULL default '',
1240 `gmtoff` int(11) NOT NULL default '0',
1241 `dstoff` int(11) NOT NULL default '0',
1242 `dst_month` tinyint(2) NOT NULL default '0',
1243 `dst_startday` tinyint(3) NOT NULL default '0',
1244 `dst_weekday` tinyint(3) NOT NULL default '0',
1245 `dst_skipweeks` tinyint(3) NOT NULL default '0',
1246 `dst_time` varchar(5) NOT NULL default '00:00',
1247 `std_month` tinyint(2) NOT NULL default '0',
1248 `std_startday` tinyint(3) NOT NULL default '0',
1249 `std_weekday` tinyint(3) NOT NULL default '0',
1250 `std_skipweeks` tinyint(3) NOT NULL default '0',
1251 `std_time` varchar(5) NOT NULL default '00:00',
1252 PRIMARY KEY (`id`)
1253 ) TYPE=MyISAM COMMENT='Rules for calculating local wall clock time for users';");
1254 }
1255
b61efafb 1256 return $result;
31f0900c 1257}
1258
1259?>