Uploaded image for project: 'Empire-DB'
  1. Empire-DB
  2. EMPIREDB-195

Review PostgreSQL driver's DDL generation for sequences

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Resolved
    • Major
    • Resolution: Fixed
    • empire-db-2.5.1
    • empire-db-3.0.0
    • Core
    • None

    Description

      On 8.10.2010 Jon Frias <friasmeister@gmail.com> wrote:

      the error I have is caused by the fact that when I create the script for generating the database schema by the following code:

      //generate the script of the database schema DBSQLScript script = new DBSQLScript(); db.getCreateDDLScript(driver, script); try{
      script.run(driver, conn, false);
      }

      the generated script is like this:

      – 1 - it generates the sequences
      – creating sequence for column us_user.user_id – CREATE SEQUENCE us_user_user_id_seq INCREMENT BY 1 START WITH 1 MINVALUE 0;

      etc.

      – 2 - it generates the tables
      – creating table us_user –
      CREATE TABLE us_user (
      user_id SERIAL NOT NULL,
      first_name TEXT NOT NULL,
      last_name TEXT NOT NULL,
      username TEXT NOT NULL,
      password TEXT NOT NULL,
      user_account_state BOOLEAN NOT NULL,
      PRIMARY KEY (user_id));

      CREATE UNIQUE INDEX index_username_unique ON us_user (username);

      etc

      – 3 - it creates the FK dependencies between tables

      etc.

      And, in the case of PostgreSQL, as you can read in this link:
      http://www.neilconway.org/docs/sequences/, when a database is created in PostgreSQL, it creates the all sequences it needs on its own.

      So, I have checked that I had all the sequences duplicated in my database, that is, I had the following sequences doing the same:
      us_user_user_id_seq
      us_user_user_id_seq1

      So, when I executed my inital_information_loading script, one sequence was used whereas when I created new registers (users in this case) using the DBRecord class, it was used the other sequence. That is why the second registration triggered an error: its value was 1 because this sequence had not been used yet.

      I have fixed this problem editing the script generated by empireDB and removing the creation of the sequences (part 1 in my previous description).
      This way, postgreSQL generates all of them on its own and there is no sequence duplicated.

      Furthermore, when a data table is declared in my code, the name of the sequence must be the same as the one which will be generated by PostgreSQL.
      All sequences follow this pattern name in PostgreSQL:
      [name_of_the_dataTable]_[name_of_the_column]_seq

      For example, my class for the Users data table is as follows:

      public UsUser(DBDatabase db)

      { super("us_user", db); USER_ID = addColumn("user_id", DataType.INTEGER, 0, DataMode.AutoGenerated, "us_user_user_id_seq"); FIRST_NAME = addColumn("first_name", DataType.CLOB, 0, DataMode.NotNull); LAST_NAME = addColumn("last_name", DataType.CLOB, 0, DataMode.NotNull); USERNAME = addColumn("username", DataType.CLOB, 0, DataMode.NotNull); PASSWORD = addColumn("password", DataType.CLOB, 0, DataMode.NotNull); USER_ACCOUNT_STATE = addColumn("user_account_state", DataType.BOOL, 10, DataMode.NotNull); METAMODEL_ID = addColumn("mm_id", DataType.INTEGER, 0, DataMode.NotNull); setPrimaryKey(USER_ID); DBColumn[] uniqueFields = new DBColumn[1]; uniqueFields[0] = USERNAME; addIndex("index_username_unique", true, uniqueFields); }

      The name of the sequence "us_user_user_id_seq" is the same as that one generated by PostgreSQL.

      Attachments

        Issue Links

          Activity

            People

              derjan Jan Glaubitz
              doebele Rainer Döbele
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 0.5h
                  0.5h