Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-31131

No possible way to create sequence on existing tables

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Won't Fix
    • Affects Version/s: 2.1.3
    • Fix Version/s: None
    • Component/s: Database SQL/XMLDB
    • Labels:
    • Database:
      PostgreSQL
    • Workaround:
      Hide

      $DB->execute("CREATE SEQUENCE

      {game_hangman}_id_seq");
      $DB->execute("ALTER TABLE {game_hangman}

      ALTER COLUMN id set DEFAULT NEXTVAL('

      {game_hangman}_id_seq')");
      $DB->execute("SELECT setval('{game_hangman}

      _id_seq', max(id)) from

      {game_hangman}

      ");

      ...

      or better rename table, create new table, copy data using DB->import_record() and reset sequences at the end using $DB->get_manager()->reset_sequence($table)

      Show
      $DB->execute("CREATE SEQUENCE {game_hangman}_id_seq"); $DB->execute("ALTER TABLE {game_hangman} ALTER COLUMN id set DEFAULT NEXTVAL(' {game_hangman}_id_seq')"); $DB->execute("SELECT setval('{game_hangman} _id_seq', max(id)) from {game_hangman} "); ... — or better rename table, create new table, copy data using DB->import_record() and reset sequences at the end using $DB->get_manager()->reset_sequence($table)
    • Affected Branches:
      MOODLE_21_STABLE

      Description

      I'm working on changes to the game module and have found that the Moodle 1.9 code doesn't use sequences for the game tables. Which is a fatal issue when restoring the a course on the same Moodle because 'null value in column "id" violates not-null constraint'.

      Solution was to add sequences to the affected tables but can't find any ways to do it via DB Manager.

      Tried using :-

      $table = new xmldb_table('game_hangman');
      $field = new xmldb_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE);
      $dbman->change_field_default($table, $field);

      $table = new xmldb_table('game_hangman');
      $field = new xmldb_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE);
      $dbman->change_field_type($table, $field);

      Debug info: ERROR: syntax error at or near "TYPEid"
      LINE 1: ALTER TABLE mdl_game_hangman ALTER COLUMN id TYPEid BIGSERIA...
      ^
      ALTER TABLE mdl_game_hangman ALTER COLUMN id TYPEid BIGSERIAL

      Any ideas?

        Gliffy Diagrams

          Activity

          Hide
          salvetore Michael de Raadt added a comment -

          Do you mean the XMLDB tool in Moodle, or do you mean you are striking a problem when the plugin is installed. If it's the former, I would manually add the change to the XML. If it's the latter, I'm not sure, but Eloy might have an idea.

          Show
          salvetore Michael de Raadt added a comment - Do you mean the XMLDB tool in Moodle, or do you mean you are striking a problem when the plugin is installed. If it's the former, I would manually add the change to the XML. If it's the latter, I'm not sure, but Eloy might have an idea.
          Hide
          tlock Tim Lock added a comment -

          Hi Michael,

          I have updated the install.xml for a new install but needed to implement via upgrade.php.

          I no longer need to create the sequences for my work in the game module, but would be nice to put on the wish list to be able to create sequence, etc per the SQL I suggested.

          Show
          tlock Tim Lock added a comment - Hi Michael, I have updated the install.xml for a new install but needed to implement via upgrade.php. I no longer need to create the sequences for my work in the game module, but would be nice to put on the wish list to be able to create sequence, etc per the SQL I suggested.
          Hide
          skodak Petr Skoda added a comment - - edited

          Hello,

          how did that happen that your db tables did not have sequence on the id column? I suppose author of the game module forgot to add it. The requested new functionality in DML is not there because it was not necessary before.

          Possible workaround is to rename old table, add new table with correct structure and copy data using DB->import_record(), do not forget to reset sequences at the end - see workshop upgrade code for more examples.

          I am going to close this because this requested functionality is not necessary for core and contrib that is using valid table structures. please use the workaround I described above.

          Thanks for the report anyway.

          Show
          skodak Petr Skoda added a comment - - edited Hello, how did that happen that your db tables did not have sequence on the id column? I suppose author of the game module forgot to add it. The requested new functionality in DML is not there because it was not necessary before. Possible workaround is to rename old table, add new table with correct structure and copy data using DB->import_record(), do not forget to reset sequences at the end - see workshop upgrade code for more examples. I am going to close this because this requested functionality is not necessary for core and contrib that is using valid table structures. please use the workaround I described above. Thanks for the report anyway.
          Hide
          tlock Tim Lock added a comment -

          Hi Petr,

          The author had the sub-game data arranged in a one to one relationship via id in both tables. Attempt ID sequenced, sub-game not hence the problem if I was going to sequence the sub-game tables.

          Thanks,
          Tim

          Show
          tlock Tim Lock added a comment - Hi Petr, The author had the sub-game data arranged in a one to one relationship via id in both tables. Attempt ID sequenced, sub-game not hence the problem if I was going to sequence the sub-game tables. Thanks, Tim

            People

            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: