Affects Version/s: 1.8
Fix Version/s: 2.0.10
Component/s: Database SQL/XMLDB
Environment:Tested on current MOODLE_18_STABLE, Postgres 8.1.4
The following code is suggested by the xmldb editor to change a field type:
/// Changing sign of field id on table ouwiki_links to unsigned
$table = new XMLDBTable('ouwiki_links');
$field = new XMLDBField('id');
$field->setAttributes(XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null, null);
/// Launch change of sign for field id
$result = $result && change_field_unsigned($table, $field);
When you do this on Postgres, keys related to that field (in this case the primary key) are lost. This is an unexpected side-effect.
Here is another example that exhibits the same problem:
/// Changing nullability of field userid on table ouwiki_comments to null
$table = new XMLDBTable('ouwiki_comments');
$field = new XMLDBField('userid');
$field->setAttributes(XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, null, null, null, null, null, 'xhtml');
/// Launch change of nullability for field userid
$result = $result && change_field_notnull($table, $field);
In this case, a foreign key (and its corresponding index) was lost.
There are two possible answers to this:
1) (Ideally) This is not expected behaviour, the keys/constraints should be preserved [in other words this should generate an ALTER TABLE type command rather than dropping the whole field and making a new one].
2) This is expected behaviour because the keys need to be marked on the field before calling the function.
In the case of #2, the code necessary to set up the keys so that they are retained should be included when you ask the xmldb editor to generate the php code for you. Also the fact that all keys etc. will be lost unless you specifically add them should be mentioned in the function phpdoc comments.