Upgrade Dokeos 1.6.x to Dokeos 1.8

From Dokeos

Jump to: navigation, search

Contents

Introduction

This article will hold whatever has been, is or will be considered during the development of the upgrade script. This is not a holder for a changelog.

What should be considered in this migration script is essentially three things: - improve the procedure (compared to previous scripts of this kind) - update the database structure and migrate the data it contains - update the directories structures correctly

Bearing in mind...

  • All the way, we have to bear in mind that some users use a unique database while others use a multiple databases installation.
  • We will only provide upgrade from 1.6.x. The previous versions should first upgrade to 1.6.5, then upgrade to 1.8. Since 1.6.0 got out one and a half year ago, it is probably safe to guess that most people would have upgraded from 1.5.x to 1.6 since then.
  • The definition of integer fields in MySQL doesn't give any usefulness to the length of these definitions. int(11) is actually equivalent, in terms of PHP use, to int on its own.

Improving the procedure

Introduction

The biggest problem we had so far is that the upgrade procedure is not automatised enough, meaning that for big installations, you are forced to do the upgrade step by step in a browser, which is certainly not the most secure, but also might create problems when you have 300 of those portals and want to do the migration in one step. The only problem though, is that we rely on certain web environment variables in the construction of the upgrade script. So for this time, even if a complete automation cannot be reached, we will at least make things much easier by providing standard SQL scripts as much as possible to create and upgrade databases and tables. Such scripts are easily usable by both a PHP script and a Shell script.

The data, though, will not be easily migrated via an SQL file, but hopefully a set of SQL files will allow us to make it all easier.

Borrowing the idea from another open source project, we will have scripts which names tell us which version it can migrate from and to, like migrate-db-1.6.0-1.8.0-pre.sql and migrate-db-1.6.0-1.8.0-post.sql (before and after data migration script).

Rules

There are several rules that we should use in cleaning the upgrade/install scripts:

  • the fields should be named with their english meaning. course_login_date rather than login_course_date
  • the integer fields should not define the length of the integer. While this might be needed in other databases systems, in fact, doing it that way in MySQL is useless and rather confusing. The size of the integer field is only used for padding purposes when directly displaying in a MySQL client, not via PHP. Besides, int(11) will not be an integer on 11 digits or 11 bytes, it will still be an integer on 4 bytes. To get integers on 1 byte, use tinyint, to get integers on 2 bytes, use smallint.
  • every record mentioning a foreign key should use the _id suffix, excepted for the course_code fields

Logging

Logging of the upgrade process will be done in the system log (called via PHP's error_log() function). Additionally, files will be added in the main/garbage directory to allow reversing the SCORM upgrade procedure. Although this shouldn't be necessary as a backup copy should always be made before an upgrade, it may prove useful if a bug is only spotted later on, when the platform has already been used for a while.

File structure

The file structure did change in that the claroline/ directory has been renamed to main/ There are two possible ways to tackle this:

  • remove the claroline/ directory and put the new one instead
  • rename the claroline/ directory into main/ and overwrite the scripts so we get to keep all the important data in claroline/upload/ and claroline/garbage/

Most of the file structure will already be handled by the overwriting an admin does when upgrading. In fact, depending on how the admin upgrades, different things can happen

  • if the admin uses the same directory, there will be coexisting claroline/ and main/ directories
  • if the admin uses another directory, then there won't be any claroline directory/ to remove

In both cases, claroline/garbage can be dropped but the files contained in claroline/upload and claroline/inc/conf/ need to be saved and passed into the new directories. The problem of claroline/inc/conf is reduced by the fact that the configuration file is regenerated by the upgrade script, taking into account the settings from the old configuration.

Database structure changes

Benefit from scripting

Before, the procedure was quite tiring to actually get the differences between two versions. An effort has been made a long time ago to write a script (claroline/install/compare_db.php) that compares two databases to extract the differences, however, it was limited to showing the tables that changed between two versions.

The new version of this script is made to first display the changes, then generate a set of SQL commands that will only require a human check before they can be used in a SQL file as a database structure upgrade script.

This will, of course, considerably reduce the amount of time spent checking the scripts to generate this upgrade script.

However, you still need to generate the bases in your database system to be able to compare them, as a complete product.

This still can thus only be considered as an aid, not an upgrade script generator.

The lows of the scripting solution

We should keep an eye on the following difficulties:

  • the SQL upgrade script needs to be done in two bits - one "pre" upgrade (to create new tables, rename fields, etc), one "post" upgrade (drop deprecated tables, fields)
  • the complete commands for the tables creation cannot be retrieved easily from the existing scripts. It has to come from the database (and this is not done yet, but you can get them with SHOW CREATE TABLE table_name in MySQL)
  • the database structure might have been modified between minor versions of Dokeos (which has not been the case, luckily, between 1.6.0 and 1.6.5)

The highs of the scripting solution

  • By using the database as a source, we don't have to care about the format of the initial scripts and retrieving the info, we can focus on the resource that is the most reusable - the database itself.
  • Using the database allows a very quick pre-analysis of the upgrade necessities.

Things not taken into account

Soon...


The upgrade/install scripts

This document would not be complete without a short description of the files located in the install directory:

  • index.php - the starting point for both install and upgrade procedures. It also manages the display of several of the install procedure Steps.
  • install_functions.inc.php - functions library used by both the install and the upgrade procedures
  • compare_db.php - script that compares the status of two databases. This helps during development of the different upgrade scripts.
  • configuration.dist.php - template script for the main Dokeos configuration file
  • htaccess.dist - template htaccess file
  • install_db.inc.php - included by index.php - installs the database by loading SQL files and sending them to the database server
  • install_files.inc.php - included by index.php - installs the directories and files to use for Dokeos
  • install_upgrade.lib.php - functions library for use by the install and upgrade scripts. Considering its contents, it could as well be merged with install_functions.inc.php
  • INSTALL.txt - documentation file redirecting to the general documentation
  • update_courses.php - script upgrading courses separately from the install process
  • update_db.inc.php - script dealing with the database update
  • update_files.inc.php - script dealing with the directory structure update
  • migrate-db-scorm-1.6.x-1.8.0.inc.php - processes the SCORM database and learnpath tables upgrade, by moving their contents (both SCORM and learnpath) into a unique set of tables in the course database. This script also deals with homepage tools renaming.


  • dokeos_main.sql - SQL script of the main database tables definition (to install)
  • migrate-db-1.6.x-1.8.0-pre.sql - template SQL file to help migrate the database from 1.6 to 1.8. This file has to be processed, it should not be executed "as is". These instructions are exectuded *before* the data migration to prepare the database to collect new info.
  • migrate-db-1.6.x-1.8.0-post.sql - template SQL file to help migrate the database from 1.6 to 1.8. This file has to be processed, it should not be executed "as is". These instructions are exectuded *after* the data migration to clean the database and remove useless tables.


  • country_data.csv - list of countries to insert into the database on install
  • language_data.csv - data to fill the language table
  • setting_current_data.csv - data to fill the current_settings table
  • setting_option_data.csv - data to fill the current_setting_options table

Data migration and structure updates

OK, let's tackle the data migration problem database by database and tool by tool...

Main database

Some fields have been added, so we have to run a SQL script with these. The pre-migration changes should all be contained in migrate-db-1.6.x-1.8.0-pre.sql, so all we have to do is read this file and execute the queries (after a small update to name the tables correctly).

The change of forum tool involves some PHP data processing here (dealt with by update_db.inc.php)

User database

Only one field added. No data processing needed.

Stats database

Two additional tables: track_e_attempt and track_e_course. Several fields types change (mostly assigned default). No data processing needed.

Course databases

A lot of changes here. Several tables were added. Mostly newscorm-, survey- and roles-related. A lot of fields were changed. The addition of tables for the newscorm tool, bound to the "deprecation" of the SCORM database, means that a lot of processing has to be done in PHP to merge data from learnpath_* tables and the SCORM database into lp_* tables in the course database. This is done by upgrade-db-scorm-1.6.x-1.8.0.inc.php, which was previously residing in dokeos/main/newscorm/

SCORM database

This database will be removed in the long run, but it will stay here as long as we're not a 100% sure that we got everything right in the migration process

Funny things to tell our grand-children

(...or just to remember for later on in the process)

Testing

This section describes the tests that have been undertaken to see if the upgrade script did work well enough

The test platform is setup as follows...

  • 165orig is the original installable Dokeos 1.6.5 directory. It can just be copied to another directory to start a new 1.6.5 install.
  • 165start is a pre-installed 1.6.5. Actually, it contains a backup of the following install (including SQL dump to restore the database quickly)
  • 165change is the install that is going to be upgraded
  • 18change is the directory where the 165change is going to be migrated
  • 18orig is an original installable Dokeos 1.8 (beta) directory. It can be copied over into 18change

The strategy is the following

  • create content in 165change
  • back it up in 165start
  • migrate using 18change
  • check 18change for mistakes
  • destroy 165change and 168change, including databases

This last loop is repeated over and over as long as errors are found.

At the moment, the upgrade script only deals with several databases mode, so it doesn't handle the table prefix for one-database mode. This will be added soon.

Considering what has changed, structure-wise, we will test the following things:

  • migration of learning paths
  • migration of SCORM paths with their tracking data (multi-user) - done
  • migration of the forums - done
  • language of courses - done
  • migration of exercises
  • new tables all created
  • deprecated tables deleted (except SCORM and learnpath tables which will be kept for a little bit longer considering how vital this is to most organisations)
Retrieved from "http://www.dokeos.com/wiki/index.php/Upgrade_Dokeos_1.6.x_to_Dokeos_1.8"
Personal tools