We recently upgraded a series of 1.4.x installations, and found that the resulting database schema was different from the schema coming from a new install. We are using Postgres, so I assumed it was a Postgres-only issue. After a bit more digging, it turns out that while there was a Postgres-specific problem, MySQL schemas are inconsistent as well.
(Actually, there was a Postgres-only bug that alerted us to the problem. The bug is now fixed, but it forced us to get into "let's fix the schema divergence problem for good" mode).
Diagnostics
We came up with a good script that gets rid of the comments, sorts the tables and fields and also removes some minor formatting inconsistencies. We install Moodle 1.4.5+, upgrade to 1.5.2+ and dump the schema, and compare that with a dump of a clean install.
The script works well cleaning up both MySQL and Postgres schemas, but you can't really compare across databases. At least not yet.
After using pg_dump or mysqldump to get the new install and the upgraded schemas, I do:
$ cat upgraded.sql | ~/bin/sqlcleanup.pl --prefix 'mdl_' >
upgraded_clean.sql
$ cat new.sql | ~/bin/sqlcleanup.pl --prefix 'mdl_' >
new_clean.sql
$ diff -u upgraded_clean.sql new_clean.sql
It is hard and errorprone to write db upgrade scripts in perfect sync with the sql files, and even harder to do it on 2 slightly incompatible databases. I'm hoping this script will help catch things earlier.
The script is in cvs:/contrib/devtools , and is the work of Patrick Li and me.
Fixing the schema in 1.5x
We have a series of upgrade blocks that will fix the discrepancies for Postgres. My idea is to commit them with new versions for the corresponding modules/blocks/etc but delay the version bump until the MySQL side is complete. I've attached a diff that shows the outstanding differences in MySQL.
Most of the problems are around defaults, subtle field-length differences, typos and nullability of fields. But there are also fields missing (or not dropped appropriately).