[jira] [Created] (EMPIREDB-195) Review PostgreSQL driver's DDL generation for sequences

["Rainer Döbele (JIRA)" <em...@incubator.apache.org>]

Rainer Döbele created EMPIREDB-195:
--------------------------------------

             Summary: Review PostgreSQL driver's DDL generation for sequences
                 Key: EMPIREDB-195
                 URL: https://issues.apache.org/jira/browse/EMPIREDB-195
             Project: Empire-DB
          Issue Type: Bug
          Components: Core
            Reporter: Rainer Döbele


On 8.10.2010 Jon Frias <fr...@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.



--
This message was sent by Atlassian JIRA
(v6.1#6144)