Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-19303 META: XMLDB Editor various bugs/improvements
  3. MDL-9271

XMLDB: Keys are lost when changing table settings


    • Icon: Sub-task Sub-task
    • Resolution: Won't Do
    • Icon: Minor Minor
    • None
    • 1.8
    • Database SQL/XMLDB
    • None
    • Tested on current MOODLE_18_STABLE, Postgres 8.1.4
    • PostgreSQL

      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.

            Unassigned Unassigned
            quen Sam Marshall
            0 Vote for this issue
            3 Start watching this issue


                Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.