general MDL-26440 removed references to uid in SQL queries as its a reserved word...
[moodle.git] / mod / scorm / db / upgrade.php
1 <?php
3 // This file keeps track of upgrades to
4 // the scorm module
5 //
6 // Sometimes, changes between versions involve
7 // alterations to database structures and other
8 // major things that may break installations.
9 //
10 // The upgrade function in this file will attempt
11 // to perform all the necessary actions to upgrade
12 // your older installation to the current version.
13 //
14 // If there's something it cannot do itself, it
15 // will tell you what you need to do.
16 //
17 // The commands in here will all be database-neutral,
18 // using the methods of database_manager class
19 //
20 // Please do not forget to use upgrade_set_timeout()
21 // before any action that may take longer time to finish.
23 /**
24  * @global moodle_database $DB
25  * @param int $oldversion
26  * @return bool
27  */
28 function xmldb_scorm_upgrade($oldversion) {
29     global $CFG, $DB;
31     $dbman = $DB->get_manager();
33 //===== 1.9.0 upgrade line ======//
35     // Adding missing 'whatgrade' field to table scorm
36     if ($oldversion < 2008073000) {
37         $table = new xmldb_table('scorm');
38         $field = new xmldb_field('whatgrade');
39         $field->set_attributes(XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0', 'grademethod');
41         /// Launch add field whatgrade
42         if (!$dbman->field_exists($table,$field)) {
43             $dbman->add_field($table, $field);
44             $whatgradefixed = get_config('scorm', 'whatgradefixed');
45             if (empty($whatgradefixed)) {
46                 /// fix bad usage of whatgrade/grading method.
47                 $scorms = $DB->get_records('scorm');
48                 foreach ($scorms as $scorm) {
49                     $scorm->whatgrade = $scorm->grademethod/10;
50                     $DB->update_record('scorm', $scorm);
51                 }
52             }
53         } else {
54             //dump this config var as it isn't needed anymore.
55             unset_config('whatgradefixed', 'scorm');
56         }
58         upgrade_mod_savepoint(true, 2008073000, 'scorm');
59     }
61      if ($oldversion < 2008082500) {
63     /// Define field scormtype to be added to scorm
64         $table = new xmldb_table('scorm');
65         $field = new xmldb_field('scormtype', XMLDB_TYPE_CHAR, '50', null, XMLDB_NOTNULL, null, 'local', 'name');
67     /// Launch add field scormtype
68         $dbman->add_field($table, $field);
70     /// scorm savepoint reached
71         upgrade_mod_savepoint(true, 2008082500, 'scorm');
72     }
74     if ($oldversion < 2008090300) {
76     /// Define field sha1hash to be added to scorm
77         $table = new xmldb_table('scorm');
78         $field = new xmldb_field('sha1hash', XMLDB_TYPE_CHAR, '40', null, null, null, null, 'updatefreq');
80     /// Launch add field sha1hash
81         $dbman->add_field($table, $field);
83     /// scorm savepoint reached
84         upgrade_mod_savepoint(true, 2008090300, 'scorm');
85     }
87     if ($oldversion < 2008090301) {
89     /// Define field revision to be added to scorm
90         $table = new xmldb_table('scorm');
91         $field = new xmldb_field('revision', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0', 'md5hash');
93     /// Launch add field revision
94         $dbman->add_field($table, $field);
96     /// scorm savepoint reached
97         upgrade_mod_savepoint(true, 2008090301, 'scorm');
98     }
100     if ($oldversion < 2008090302) {
101         $sql = "UPDATE {scorm}
102                    SET scormtype = 'external'
103                  WHERE reference LIKE ? OR reference LIKE ? OR reference LIKE ?";
104         $DB->execute($sql, array('http://%imsmanifest.xml', 'https://%imsmanifest.xml', 'www.%imsmanifest.xml'));
106         $sql = "UPDATE {scorm}
107                    SET scormtype = 'localsync'
108                  WHERE reference LIKE ? OR reference LIKE ? OR reference LIKE ?
109                        OR reference LIKE ? OR reference LIKE ? OR reference LIKE ?";
110         $DB->execute($sql, array('http://%.zip', 'https://%.zip', 'www.%.zip', 'http://%.pif', 'https://%.pif', 'www.%.pif'));
112         $sql = "UPDATE {scorm} SET scormtype = 'imsrepository' WHERE reference LIKE ?";
113         $DB->execute($sql, array('#%'));
115     /// scorm savepoint reached
116         upgrade_mod_savepoint(true, 2008090302, 'scorm');
117     }
119     if ($oldversion < 2008090303) {
120         //remove obsoleted config settings
121         unset_config('scorm_advancedsettings');
122         unset_config('scorm_windowsettings');
124     /// scorm savepoint reached
125         upgrade_mod_savepoint(true, 2008090303, 'scorm');
126     }
128     if ($oldversion < 2008090304) {
129         /////////////////////////////////////
130         /// new file storage upgrade code ///
131         /////////////////////////////////////
133         require_once("$CFG->dirroot/mod/scorm/db/upgradelib.php");
135         $fs = get_file_storage();
137         $sqlfrom = "FROM {scorm} s
138                     JOIN {modules} m ON m.name = 'scorm'
139                     JOIN {course_modules} cm ON (cm.module = m.id AND cm.instance = s.id)";
141         $count = $DB->count_records_sql("SELECT COUNT('x') $sqlfrom");
143         $rs = $DB->get_recordset_sql("SELECT s.id, s.scormtype, s.reference, s.course, cm.id AS cmid $sqlfrom ORDER BY s.course, s.id");
144         if ($rs->valid()) {
146             $pbar = new progress_bar('migratescormfiles', 500, true);
148             $i = 0;
149             foreach ($rs as $scorm) {
150                 $i++;
151                 upgrade_set_timeout(180); // set up timeout, may also abort execution
152                 $pbar->update($i, $count, "Migrating scorm files - $i/$count.");
154                 $context       = get_context_instance(CONTEXT_MODULE, $scorm->cmid);
155                 $coursecontext = get_context_instance(CONTEXT_COURSE, $scorm->course);
157                 if ($scorm->scormtype === 'local' and preg_match('/.*(\.zip|\.pif)$/i', $scorm->reference)) {
158                     // first copy local packages if found - do not delete in case they are shared ;-)
159                     $packagefile = clean_param($scorm->reference, PARAM_PATH);
160                     $pathnamehash = sha1("/$coursecontext->id/course/legacy/0/$packagefile");
161                     if ($file = $fs->get_file_by_hash($pathnamehash)) {
162                         $file_record = array('contextid'=>$context->id, 'component'=>'mod_scorm', 'filearea'=>'package',
163                                              'itemid'=>0, 'filepath'=>'/');
164                         try {
165                             $fs->create_file_from_storedfile($file_record, $file);
166                         } catch (Exception $x) {
167                             // ignore any errors, we can not do much anyway
168                         }
169                         $scorm->reference = $file->get_filepath().$file->get_filename();
171                     } else {
172                         $scorm->reference = '';
173                     }
174                     $DB->update_record('scorm', $scorm);
175                     // the package should be already extracted, we need to move the files there
176                     // just in case somebody modified it directly there
177                     scorm_migrate_moddata_files($scorm, $context);
179                 } else if ($scorm->scormtype === 'local' and preg_match('/.*\/imsmanifest\.xml$/i', $scorm->reference)) {
180                     // ignore imsmanifest in course root because we would be duplicating all course files which is not acceptable
181                     // moddata dir is not used at all, ignore any rubbish there
182                     $manifest = clean_param($scorm->reference, PARAM_PATH);
184                     $pathnamehash = sha1("/$coursecontext->id/course/legacy/0/$manifest");
185                     if ($file = $fs->get_file_by_hash($pathnamehash)) {
186                         $scorm->reference = $file->get_filepath().$file->get_filename();
188                         $manifestdir = '/'.str_ireplace('/imsmanifest.xml', '', $manifest).'/';
189                         $pregmanifestdir = preg_quote($manifestdir, '/');
190                         $file_record = array('contextid'=>$context->id, 'component'=>'mod_scorm', 'filearea'=>'content', 'itemid'=>0);
191                         if ($files = $fs->get_directory_files($coursecontext->id, 'course', 'legacy', 0, $manifestdir, true)) {
192                             foreach ($files as $file) {
193                                 $file_record['filepath'] = preg_replace("/^$pregmanifestdir/", '/', $file->get_filepath());
194                                 try {
195                                     $fs->create_file_from_storedfile($file_record, $file);
196                                 } catch (Exception $x) {
197                                     // ignore any errors, we can not do much anyway
198                                 }
199                             }
200                         }
202                     } else {
203                         $scorm->reference = '';
204                     }
205                     $DB->update_record('scorm', $scorm);
207                 } else {
208                     // just try to migrate anything from moddata
209                     scorm_migrate_moddata_files($scorm, $context);
210                 }
212                 // remove dirs if empty
213                 @rmdir("$CFG->dataroot/$scorm->course/$CFG->moddata/scorm/$scorm->id/");
214                 @rmdir("$CFG->dataroot/$scorm->course/$CFG->moddata/scorm/");
215                 @rmdir("$CFG->dataroot/$scorm->course/$CFG->moddata/");
216             }
217         }
218         $rs->close();
220     /// scorm savepoint reached
221         upgrade_mod_savepoint(true, 2008090304, 'scorm');
222     }
225     if ($oldversion < 2008090305) {
227     /// Define new fields forcecompleted, forcenewattempt, displayattemptstatus, and displaycoursestructure to be added to scorm
228         $table = new xmldb_table('scorm');
229         $field = new xmldb_field('forcecompleted', XMLDB_TYPE_INTEGER, 1, null, XMLDB_NOTNULL, null, 1, 'maxattempt');
230         if (!$dbman->field_exists($table,$field)) {
231             $dbman->add_field($table, $field);
232         }
233         $field = new xmldb_field('forcenewattempt', XMLDB_TYPE_INTEGER, 1, null, XMLDB_NOTNULL, null, 0, 'forcecompleted');
234         if (!$dbman->field_exists($table,$field)) {
235             $dbman->add_field($table, $field);
236         }
237         $field = new xmldb_field('lastattemptlock', XMLDB_TYPE_INTEGER, 1, null, XMLDB_NOTNULL, null, 0, 'forcenewattempt');
238         if (!$dbman->field_exists($table,$field)) {
239             $dbman->add_field($table, $field);
240         }
241         $field = new xmldb_field('displayattemptstatus', XMLDB_TYPE_INTEGER, 1, null, XMLDB_NOTNULL, null, 1, 'lastattemptlock');
242         if (!$dbman->field_exists($table,$field)) {
243             $dbman->add_field($table, $field);
244         }
245         $field = new xmldb_field('displaycoursestructure', XMLDB_TYPE_INTEGER, 1, null, XMLDB_NOTNULL, null, 1, 'displayattemptstatus');
246         if (!$dbman->field_exists($table,$field)) {
247             $dbman->add_field($table, $field);
248         }
250     /// scorm savepoint reached
251         upgrade_mod_savepoint(true, 2008090305, 'scorm');
252     }
255     // remove redundant config values
256     if ($oldversion < 2008090306) {
257         /*
258          * comment this out as it is handled by the update mark 2008090310 below
259          * left for historical documentation as some early adopters may have done
260          * this already.
261          $redundant_config = array(
262                          'scorm_allowapidebug',
263                          'scorm_allowtypeexternal',
264                          'scorm_allowtypeimsrepository',
265                          'scorm_allowtypelocalsync',
266                          'scorm_apidebugmask',
267                          'scorm_frameheight',
268                          'scorm_framewidth',
269                          'scorm_maxattempts',
270                          'scorm_updatetime');
271         foreach ($redundant_config as $rcfg) {
272             if (isset($CFG->$rcfg)) {
273                 unset_config($rcfg);
274             }
275         }
276          */
277         /// scorm savepoint reached
278         upgrade_mod_savepoint(true, 2008090306, 'scorm');
279     }
283     // remove redundant config values
284     if ($oldversion < 2008090307) {
285         /*
286          * comment this out as it is handled by the update mark 2008090310 below
287          * left for historical documentation as some early adopters may have done
288          * this already.
289          $redundant_config = array(
290                          'scorm_allowapidebug',
291                          'scorm_allowtypeexternal',
292                          'scorm_allowtypeimsrepository',
293                          'scorm_allowtypelocalsync',
294                          'scorm_apidebugmask',
295                          'scorm_frameheight',
296                          'scorm_framewidth',
297                          'scorm_maxattempts',
298                          'scorm_updatetime',
299                          'scorm_resizable',
300                          'scorm_scrollbars',
301                          'scorm_directories',
302                          'scorm_location',
303                          'scorm_menubar',
304                          'scorm_toolbar',
305                          'scorm_status',
306                          'scorm_grademethod',
307                          'scorm_maxgrade',
308                          'scorm_whatgrade',
309                          'scorm_popup',
310                          'scorm_skipview',
311                          'scorm_hidebrowse',
312                          'scorm_hidetoc',
313                          'scorm_hidenav',
314                          'scorm_auto',
315                          'scorm_updatefreq'
316          );
317         foreach ($redundant_config as $rcfg) {
318             if (isset($CFG->$rcfg)) {
319                 unset_config($rcfg);
320             }
321         }
322          */
324         /// scorm savepoint reached
325         upgrade_mod_savepoint(true, 2008090307, 'scorm');
326     }
328     if ($oldversion < 2008090308) {
329         $table = new xmldb_table('scorm');
330         $field = new xmldb_field('timeopen', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0', 'height');
331         if (!$dbman->field_exists($table,$field)) {
332             $dbman->add_field($table, $field);
333         }
334         $field = new xmldb_field('timeclose', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0', 'timeopen');
335         if (!$dbman->field_exists($table,$field)) {
336             $dbman->add_field($table, $field);
337         }
339         /// scorm savepoint reached
340         upgrade_mod_savepoint(true, 2008090308, 'scorm');
341     }
344     if ($oldversion < 2008090310) {
345         // take above blocks that delete config and move the values in to config_plugins
347         $redundant_config = array(
348                          'scorm_allowapidebug',
349                          'scorm_allowtypeexternal',
350                          'scorm_allowtypeimsrepository',
351                          'scorm_allowtypelocalsync',
352                          'scorm_apidebugmask',
353                          'scorm_frameheight',
354                          'scorm_framewidth',
355                          'scorm_maxattempts',
356                          'scorm_updatetime',
357                          'scorm_resizable',
358                          'scorm_scrollbars',
359                          'scorm_directories',
360                          'scorm_location',
361                          'scorm_menubar',
362                          'scorm_toolbar',
363                          'scorm_status',
364                          'scorm_grademethod',
365                          'scorm_maxgrade',
366                          'scorm_whatgrade',
367                          'scorm_popup',
368                          'scorm_skipview',
369                          'scorm_hidebrowse',
370                          'scorm_hidetoc',
371                          'scorm_hidenav',
372                          'scorm_auto',
373                          'scorm_updatefreq',
374                          'scorm_displayattemptstatus',
375                          'scorm_displaycoursestructure',
376                          'scorm_forcecompleted',
377                          'scorm_forcenewattempt',
378                          'scorm_lastattemptlock'
379          );
381         foreach ($redundant_config as $rcfg) {
382             if (isset($CFG->$rcfg)) {
383                 $shortname = substr($rcfg, 6);
384                 set_config($shortname, $CFG->$rcfg, 'scorm');
385                 unset_config($rcfg);
386             }
387         }
389         /// scorm savepoint reached
390         upgrade_mod_savepoint(true, 2008090310, 'scorm');
391     }
393     if ($oldversion < 2009042000) {
395     /// Rename field summary on table scorm to intro
396         $table = new xmldb_table('scorm');
397         $field = new xmldb_field('summary', XMLDB_TYPE_TEXT, 'small', null, XMLDB_NOTNULL, null, null, 'reference');
399     /// Launch rename field summary
400         $dbman->rename_field($table, $field, 'intro');
402     /// scorm savepoint reached
403         upgrade_mod_savepoint(true, 2009042000, 'scorm');
404     }
406     if ($oldversion < 2009042001) {
408     /// Define field introformat to be added to scorm
409         $table = new xmldb_table('scorm');
410         $field = new xmldb_field('introformat', XMLDB_TYPE_INTEGER, '4', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0', 'intro');
412     /// Launch add field introformat
413         if (!$dbman->field_exists($table, $field)) {
414             $dbman->add_field($table, $field);
415         }
417         // conditionally migrate to html format in intro
418         if ($CFG->texteditors !== 'textarea') {
419             $rs = $DB->get_recordset('scorm', array('introformat'=>FORMAT_MOODLE), '', 'id,intro,introformat');
420             foreach ($rs as $s) {
421                 $s->intro       = text_to_html($s->intro, false, false, true);
422                 $s->introformat = FORMAT_HTML;
423                 $DB->update_record('scorm', $s);
424                 upgrade_set_timeout();
425             }
426             $rs->close();
427         }
429     /// scorm savepoint reached
430         upgrade_mod_savepoint(true, 2009042001, 'scorm');
431     }
433     if ($oldversion < 2009042002) {
435     /// Define field introformat to be added to scorm
436         $table = new xmldb_table('scorm_scoes');
437         $field = new xmldb_field('launch', XMLDB_TYPE_TEXT, 'small', null, XMLDB_NOTNULL, null, null);
439     /// Launch add field introformat
440         $dbman->change_field_type($table, $field);
442     /// scorm savepoint reached
443         upgrade_mod_savepoint(true, 2009042002, 'scorm');
444     }
445     if ($oldversion < 2010070800) {
446     //check to see if this has already been tidied up by a 1.9 upgrade.
447         $grademethodfixed = get_config('scorm', 'grademethodfixed');
448         if (empty($grademethodfixed)) {
449             /// fix bad usage of whatgrade/grading method.
450             $scorms = $DB->get_records('scorm');
451             if (!empty($scorm)) {
452                 foreach ($scorms as $scorm) {
453                     $scorm->grademethod = $scorm->grademethod%10;
454                     $DB->update_record('scorm', $scorm);
455                 }
456             }
457         } else {
458             //dump this config var as it isn't needed anymore.
459             unset_config('grademethodfixed', 'scorm');
460         }
462     /// scorm savepoint reached
463         upgrade_mod_savepoint(true, 2010070800, 'scorm');
464     }
465     if ($oldversion < 2010092400) {
466         $count = $DB->count_records('scorm', array('scormtype'=>'external'));
467         if (!empty($count)) {
468             set_config('allowtypeexternal', '1', 'scorm');
469         }
470         $count = $DB->count_records('scorm', array('scormtype'=>'localsync'));
471         if (!empty($count)) {
472             set_config('allowtypelocalsync', '1', 'scorm');
473         }
474         $count = $DB->count_records('scorm', array('scormtype'=>'imsrepository'));
475         if (!empty($count)) {
476             set_config('allowtypeimsrepository', '1', 'scorm');
477         }
478         /// scorm savepoint reached
479         upgrade_mod_savepoint(true, 2010092400, 'scorm');
480     }
481     
482     if ($oldversion < 2011011400) {
483         // Fix scorm in the post table after upgrade from 1.9
484         $table = new xmldb_table('scorm');
485         $columns = $DB->get_columns('scorm');
487         // forcecompleted should be bigint(1) NOT NULL DEFAULT '1'
488         // Fixed in earlier upgrade code
489         $field = new xmldb_field('forcecompleted', XMLDB_TYPE_INTEGER, 1, null, XMLDB_NOTNULL, null, 1, 'maxattempt');
490         if ($dbman->field_exists($table, $field)) {
491             $dbman->change_field_precision($table, $field);
492         }
494         if (array_key_exists('forcenewattempt', $columns) && empty($columns['forcenewattempt']->not_null)) {
495             // forcenewattempt should be NOT NULL
496             // Fixed in earlier upgrade code
497             $field = new xmldb_field('forcenewattempt', XMLDB_TYPE_INTEGER, 1, null, XMLDB_NOTNULL, null, 0, 'forcecompleted');
498             if ($dbman->field_exists($table, $field)) {
499                 $dbman->change_field_notnull($table, $field);
500             }
501         }
503         if (array_key_exists('lastattemptlock', $columns) && empty($columns['lastattemptlock']->not_null)) {
504             // lastattemptlock should be NOT NULL
505             // Fixed in earlier upgrade code
506             $field = new xmldb_field('lastattemptlock', XMLDB_TYPE_INTEGER, 1, null, XMLDB_NOTNULL, null, 0, 'forcenewattempt');
507             if ($dbman->field_exists($table, $field)) {
508                 $dbman->change_field_notnull($table, $field);
509             }
510         }
512         if (array_key_exists('displayattemptstatus', $columns) && empty($columns['displayattemptstatus']->not_null)) {
513             // displayattemptstatus should be NOT NULL
514             // Fixed in earlier upgrade code
515             $field = new xmldb_field('displayattemptstatus', XMLDB_TYPE_INTEGER, 1, null, XMLDB_NOTNULL, null, 1, 'lastattemptlock');
516             if ($dbman->field_exists($table, $field)) {
517                 $dbman->change_field_notnull($table, $field);
518             }
519         }
521         if (array_key_exists('displaycoursestructure', $columns) && empty($columns['displaycoursestructure']->not_null)) {
522             // displaycoursestructure should be NOT NULL
523             // Fixed in earlier upgrade code
524             $field = new xmldb_field('displaycoursestructure', XMLDB_TYPE_INTEGER, 1, null, XMLDB_NOTNULL, null, 1, 'displayattemptstatus');
525             if ($dbman->field_exists($table, $field)) {
526                 $dbman->change_field_notnull($table, $field);
527             }
528         }
530         upgrade_mod_savepoint(true, 2011011400, 'scorm');
531     }
533     return true;