Moodle
  1. Moodle
  2. MDL-31131

No possible way to create sequence on existing tables

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor 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
    • Rank:
      37562

      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?

        Activity

        Hide
        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
        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
        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
        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
        Petr Škoda 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
        Petr Škoda 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
        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
        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: