Correct schema drift during 1.0 -> 1.8 upgrades

[Aaron Wells]

We seem to get a lot of bug reports on the forum from people having issues after upgrading from pre-1.5 Mahara instances up to the latest. It seems that the upgrade scripts do not generate a database schema which is consistent with what you'd get from a clean 1.7 installation.

If we want people to update to the latest version of Mahara (and we do!) then we need to make sure that the upgrade process works properly. So, we should test the upgrade process from each of the old versions of Mahara into master, and if it doesn't produce exactly the same DB schema as a fresh install, we need to correct the master upgrade scripts to solve this.

Of course, there's also the matter of data in those tables getting messed up, but that's harder to recognize and fix. We could test for it partway, by making sure we upgrade a non-empty Mahara instance (something with at least a few users, pages, institutions, auth methods, etc).

It might also be a good idea to add a tool that compares the schema against all of the install.xml files, so that users can tell whether they have a problem or not. Not worth writing one from scratch, but Moodle 2 has a script for copying a database, and one of its steps is to check the schema against all the XMLDB files, so we may be able to use that.

[Aaron Wells (u-aaronw)]

See https://reviews.mahara.org/#/c/2744/

[Aaron Wells (u-aaronw)]

Patch 2744 was originally from https://bugs.launchpad.net/mahara/+bug/1081947 , but it actually matches this bug more closely.

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/2744
Committed: http://gitorious.org/mahara/mahara/commit/aee3b546ffdfada6877ef4b8ae2872942177df6c
Submitter: Aaron Wells (<email address hidden>)
Branch: master

commit aee3b546ffdfada6877ef4b8ae2872942177df6c
Author: Robert Lyon <email address hidden>
Date: Tue Nov 26 11:15:14 2013 +1300

Adding in missing keys & indexes

Bug 1174623 For sites that have been upgraded from version 1.0 there are
some missing constraints and indexes on the artefact_access_usr,
artefact_access_role, artefact_attachment, group, grouptype_roles
and view_autocreate_grouptype tables.

Also some fields need to be adjusted to match the referenced field in
another table so that they can be used as a foreign key

Change-Id: Ifb8cd79d4fa9933be02f6086fb5dada18d47bfe2
Signed-off-by: Robert Lyon <email address hidden>
Signed-off-by: Aaron Wells <email address hidden>

[Aaron Wells (u-aaronw)]

Another patch for another discrepancy: https://reviews.mahara.org/#/c/2792/6

[Aaron Wells (u-aaronw)]

Backporting https://reviews.mahara.org/2744 to Mahara 1.8: https://reviews.mahara.org/2815

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/2815
Committed: http://gitorious.org/mahara/mahara/commit/852254aa84038ecb787fb6920a8d8030ba4a9f41
Submitter: Aaron Wells (<email address hidden>)
Branch: 1.8_STABLE

commit 852254aa84038ecb787fb6920a8d8030ba4a9f41
Author: Robert Lyon <email address hidden>
Date: Tue Nov 26 11:15:14 2013 +1300

Adding in missing keys & indexes

Bug 1174623 For sites that have been upgraded from version 1.0 there are
some missing constraints and indexes on the artefact_access_usr,
artefact_access_role, artefact_attachment, group, grouptype_roles
and view_autocreate_grouptype tables.

Also some fields need to be adjusted to match the referenced field in
another table so that they can be used as a foreign key

Change-Id: Ifb8cd79d4fa9933be02f6086fb5dada18d47bfe2
Signed-off-by: Robert Lyon <email address hidden>
Signed-off-by: Aaron Wells <email address hidden>

[Aaron Wells (u-aaronw)]

Looking at this forum post https://mahara.org/interaction/forum/topic.php?id=5986&offset=0&limit=10 ...

It occurs to me that there's a problem in our schema correction code. We're adding foreign keys to existing tables, but we didn't first check to see whether the tables have data that will fit that foreign key. If they have bad data, then this upgrade script will error out and prevent them from upgrading. And since these missing schema elements are not vital, we don't want their failure to block upgrade.

We should have put a "catch (SQLException $e)" block around each attempt to add a foreign key, unique index, and/or primary key, or another other schema element that will fail if their data isn't clean. :-P

I'll spin off another bug for that...

[Aaron Wells (u-aaronw)]

See https://bugs.launchpad.net/mahara/+bug/1262932

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/2792
Committed: http://gitorious.org/mahara/mahara/commit/064bf963c5c81d38442f6d377afbc1426980dbf5
Submitter: Aaron Wells (<email address hidden>)
Branch: master

commit 064bf963c5c81d38442f6d377afbc1426980dbf5
Author: Robert Lyon <email address hidden>
Date: Fri Dec 13 09:28:22 2013 +1300

Adding in possibly missing id column for view_rows_columns (bug 1174623)

The field is currently not being used but because the install.php file
had the id column in it for v1.8 it means sites installed with 1.8
have the column but sites upgraded to 1.8 do not.

Change-Id: If1e52cf68ad18ce730fd2e9be3abf8684a94368d
Signed-off-by: Robert Lyon <email address hidden>