Uploaded image for project: 'Plugins'
  1. Plugins
  2. CONTRIB-1153

Oracle conflicts with "public" field

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.7, 1.8, 1.9
    • Fix Version/s: 1.9.6, 2.0
    • Component/s: Module: Questionnaire
    • Labels:
      None
    • Environment:
      Apache / PHP 5 / Solaris / Oracle 10gR2
    • Database:
      Oracle
    • Difficulty:
      Easy
    • Affected Branches:
      MOODLE_17_STABLE, MOODLE_18_STABLE, MOODLE_19_STABLE
    • Fixed Branches:
      MOODLE_19_STABLE, MOODLE_20_STABLE

      Description

      The public field in questionnaire_survey and questionnaire_question tables is problematic because it's a reserved word for Oracle. Solve this problem is easy because only it's necessary to change public for ispublic (for instance) int the install.xml file:

      <TABLE NAME="questionnaire_survey" COMMENT="questionnaire_survey table retrofitted from MySQL" PREVIOUS="questionnaire" NEXT="questionnaire_attempts">
      ...
      <FIELD NAME="realm" TYPE="char" LENGTH="64" NOTNULL="true" SEQUENCE="false" ENUM="false" PREVIOUS="owner" NEXT="ispublic"/>
      <FIELD NAME="ispublic" TYPE="char" LENGTH="1" NOTNULL="true" DEFAULT="y" SEQUENCE="false" ENUM="true" ENUMVALUES="'y', 'n'" PREVIOUS="realm" NEXT="status"/>
      <FIELD NAME="status" TYPE="int" LENGTH="10" NOTNULL="true" UNSIGNED="true" DEFAULT="0" SEQUENCE="false" ENUM="false" PREVIOUS="ispublic" NEXT="title"/>
      ...
      <TABLE NAME="questionnaire_question" COMMENT="questionnaire_question table retrofitted from MySQL" PREVIOUS="questionnaire_attempts" NEXT="questionnaire_quest_choice">
      ...
      <FIELD NAME="deleted" TYPE="char" LENGTH="1" NOTNULL="true" DEFAULT="n" SEQUENCE="false" ENUM="true" ENUMVALUES="'y', 'n'" PREVIOUS="required" NEXT="ispublic"/>
      <FIELD NAME="ispublic" TYPE="char" LENGTH="1" NOTNULL="true" DEFAULT="y" SEQUENCE="false" ENUM="true" ENUMVALUES="'y', 'n'" PREVIOUS="deleted"/>
      ...

      Add the following lines in upgrade.php file:

      if ($CFG->dbtype=='mysql'){
      $result &= execute_sql("ALTER TABLE {$CFG->prefix}questionnaire_survey CHANGE public ispublic enum('y','n') NOT NULL default 'y'");
      $result &= execute_sql("ALTER TABLE {$CFG->prefix}questionnaire_question CHANGE public ispublic enum('y','n') NOT NULL default 'y'");
      } else{
      $result &= execute_sql("ALTER TABLE {$CFG->prefix}questionnaire_survey RENAME COLUMN \"public\" TO \"ISPUBLIC\"");
      $result &= execute_sql("ALTER TABLE {$CFG->prefix}questionnaire_question RENAME COLUMN \"public\" TO ISPUBLIC");
      }

      (there is a rename_field function on dmllib.php but it doesn't work for us on MySQL neither Oracle. That's why we need to use the SQL sentence to rename the column for existing installations).

      You will need to edit also some other files to change public attribute references for ispublic:

      • The questiontypes/questiontypes.class.php . For instance:
        var $ispublic = 'n';
      • The backuplib.php and restorelib.php. For instance:
        fwrite ($bf,full_tag('ISPUBLIC',5,false,$survey->ispublic));

      And that's all! We attach our questionnaire version that is based in the questionnaire version for Moodle 1.9 and revised to make it work with Moodle 1.8. If you want, you can use it for what you need.

        Attachments

          Activity

            People

            Assignee:
            mchurch Mike Churchward
            Reporter:
            sarjona Sara Arjona (@sarjona)
            Participants:
            Component watchers:
            Mike Churchward
            Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:
              Fix Release Date:
              21/Oct/09