Hi everybody,
I would like to share my journey on Moodle upgrading and the certificate module:
work done:
We have a Moodle 1.9.9 enviroment that needs to be upgraded to the latest Moodle release (2.3.1 at this time). The 1.9.9 enviroment has the certificate module installed. After upgrading we will do course backups with user data and restore them in a Fresh Moodle 2.3.1 installation. First we upgraded to Moodle 2.2 with the certificate module version for 2.2, then upgraded this to 2.3.1 with the certificate module for 2.3.1
The issue:
After upgrade the issued certificates were visible in the Moodle 2.3.1 installation (I will call this the upgraded Moodle 2.3 environment from now). After creating a course backup and restoring with all user data into our fresh Moodle 2.3 installation we did not see any received certificates. Comparing the mdl_certificate_issues tables of the upgraded Moodle 2.3 environment and the fresh Moodle 2.3 installation it was clear there were some missing table columns.
Upon inspecting the upgrade.php it was clear upgrading the certificate module would have upgraded the tables if we would have kept the Moodle up to date. I think we missed some database actions that should have been performed.
The upgraded 2.3 installation had only these columns in the mdl_certificate_issues table:
id, certificateid, userid, timecreated, code
The freshly installed 2.3 installation had these columns:
id, certificateid, userid, timecreated, studentname, code, classname, certdate, reportgrade, mailed
The table contents in the upgraded 2.3 environments has the userid columns, userid's in these columns pointed to the id column in table mdl_user.
After restoring the coursebackup with userdata in the fresh moodle 2.3 enviroment new users were created, the process created new account in the mdl_user table. Each account is then assigen a new uniq id in the table mdl_user.
The course restore did fill the mdl_certificate_issues table but data was incomplete and the userid column no longer pointed to the correct user id's.
The fix:
To solve these issues a couple of queries were created:
First of all, we need the certdate column. This can be filled with the same value as the timecreated table
For this I used this query:
"UPDATE mdl_certificate_issues as ci set certdate=timecreated";
Second I want the certificate_issues table to point to the correct user. I need the user details of the upgraded moodle 2.3 environment. So I created a backup of the mdl_user table there. I opened this backup file in a text editor and replace all instances of mdl_user into mdl_user_old. Then the backup was restored in my fresh Moodle 2.3 environment.
Using this table I can fix the mdl_certificate_issues table:
First I add the users firstname and latnam to the studentname column
"UPDATE mdl_certificate_issues as ci LEFT JOIN mdl_user_old as mo ON ci.userid = mo.id LEFT JOIN mdl_user as mu ON mo.username = mu.username SET ci.studentname = concat (mu.firstname , ' ' , mu.lastname);"
Second I update the userid table:
"UPDATE mdl_certificate_issues as ci LEFT JOIN mdl_user_old as mo ON ci.userid = mo.id LEFT JOIN mdl_user as mu ON mo.username = mu.username SET ci.userid = mu.id";
Make sure you perform the last SQL code just once. If you want it more often add a Where clause that will prevent re-updating the userid.
With these actions done the certificate issues showed up in our fresh Moodle 2.3 enviroment. Hope this helps for others doing this type of upgrade!
