Databases: Default and NOT NULL check for 1.9

Databases: Default and NOT NULL check for 1.9

by Lorenzo Nicora -
Number of replies: 6

I implemented an additional check, like bigints and indexes checks, for Admin -> Miscellaneous -> XMLDB Editor.

It for 1.9.x.
Not for 2.0!

The check fixes fields that are NULL in the actual db and defined as NOT NULL in XMLDB definitions.
Never vice-versa (NOT NULL to NULL).

It also adds missing DEFAULTs to fields.
It does not remove existing DEFAULTs, nor changes DEFAULTs different from defined in XMLDB.

I excluded some cases for robustness.


I used the messages I found in standard lang files for this check.
This check was probably planned but not implemented (I suppose).
They should be slightly changed to reflect the actual behaviour.


The attached zip contains 2 files.
One defines a new check_default XMLDBAction.
The second replaces main_view action, just adding the new check to the XMLDB Editor page.

Files should be unzipped in the main installation directory.

I tested it on MySQL and PostgreSQL (it is disabled for other DBMS).
I suggest not to use it on production sites without some tests. Consider it experimental.

Please let me know if you find any problem.


Lorenzo

Average of ratings: -
In reply to Lorenzo Nicora

Re: Databases: Default and NOT NULL check for 1.9

by Lorenzo Nicora -

I need to add some code.
UPDATE fields before setting to NOT NULL, if no DEFAULT has been defined...

This is no good, as the updated value must be arbitrary, but I see no other way.

I forgot to mention that this check (and fix) is needed to upgrade to 2.0 old sites (installed using < 1.7 and then upgraded)

Average of ratings: -
In reply to Lorenzo Nicora

Re: Databases: Default and NOT NULL check for 1.9

by Lorenzo Nicora -

I fixed problem of the previous patch.

Now it generates UPDATE statements to fix fields containing null, before setting them to NOT NULL.
I tested it on some production site's db with success.

As I wrote in the previous post, this patch adds a new check to Admin -> Miscellaneous -> XMLDB Editor.
The new check fixes NOT NULLs and DEFAULTs not corresponding to XMLDB definition of the installation.
This may occur when the site has been upgraded from previous versions in past times and is a blocking issue on upgrading to 2.0

Lorenzo

Average of ratings: -
In reply to Lorenzo Nicora

Re: Databases: Default and NOT NULL check for 1.9

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
Thanks for this... I actually identified this problem way back, coded it, and it was never included in the stable branch for some weird reason I forget. I'm not bitter wink

It matters now, though!

Anyway, I've added it to the 'see also' in the Upgrading Moodle 2.0 doc page - http://docs.moodle.org/en/Upgrading_to_Moodle_2.0
Average of ratings: -
In reply to Lorenzo Nicora

Ang: Re: Default and NOT NULL check for 1.9

by Bente Olsen -
Picture of Testers Picture of Translators

Hi Lorenzo

Howard made me aware of your path, so I gave it a try, while I have a lot of NULL values in my mdl_course_modules table. But it did not seem to do any difference, so I might be in lack of some basic understanding of what to do. I just copied the files to my admin folder and entered mydomain/admin in my browser like when I update moodle. I use Moodle 1.9.10

Average of ratings: -
In reply to Bente Olsen

Ang: Re: Default and NOT NULL check for 1.9

by Bente Olsen -
Picture of Testers Picture of Translators

Hi Lorenzo

Just thinking that maybe I should have used your files together with an update to latest stable Moodle?

Average of ratings: -
In reply to Bente Olsen

Re: Ang: Re: Default and NOT NULL check for 1.9

by Lorenzo Nicora -

Hi Bente

This patch must be installed in 1.9.10, and the generated SQL must be executed before upgrading.

After installing the patch, do you see the "Check Defaults" in XMLDB Editor page?
If you don't, maybe you put files in the wrong directory or you failed to overwrite the original main_view.class.php

If you see and ran the check, did yoi execute the generated SQL code directly on your db?
This new check, like other XMLDB checks, does not change anything in the db. It generates the SQL code you must run using your prefered db client.

Lorenzo

Average of ratings: -