Mailing-List: contact commits-help@empire-db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: empire-db-dev@empire-db.apache.org
Date: Wed, 9 Oct 2013 08:04:41 +0000 (UTC)
From: =?utf-8?Q?Rainer_D=C3=B6bele_=28JIRA=29?=
 <empire-db-dev@incubator.apache.org>
To: empire-db-commits@incubator.apache.org
Message-ID: <JIRA.12672978.1381305764011.37101.1381305881782@arcas>
In-Reply-To: <JIRA.12672978.1381305764011@arcas>
References: <JIRA.12672978.1381305764011@arcas>
Subject: [jira] [Created] (EMPIREDB-195) Review PostgreSQL driver's DDL
 generation for sequences
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

Rainer D=C3=B6bele created EMPIREDB-195:
--------------------------------------

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


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 ge=
nerating the database schema by the following code:

//generate the script of the database schema DBSQLScript script =3D new DBS=
QLScript(); 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 Po=
stgreSQL, 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 DB=
Record class, it was used the other sequence. That is why the second regist=
ration triggered an error: its value was 1 because this sequence had not be=
en used yet.

I have fixed this problem editing the script generated by empireDB and remo=
ving 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 seque=
nce duplicated.

Furthermore, when a data table is declared in my code, the name of the sequ=
ence 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 =3D addColumn("user_id", DataType.INTEGER, 0, DataMode.Auto=
Generated, "us_user_user_id_seq");
        FIRST_NAME =3D addColumn("first_name", DataType.CLOB, 0, DataMode.N=
otNull);
        LAST_NAME =3D addColumn("last_name", DataType.CLOB, 0, DataMode.Not=
Null);
        USERNAME =3D addColumn("username", DataType.CLOB, 0, DataMode.NotNu=
ll);
        PASSWORD =3D addColumn("password", DataType.CLOB, 0, DataMode.NotNu=
ll);
        USER_ACCOUNT_STATE =3D addColumn("user_account_state", DataType.BOO=
L, 10, DataMode.NotNull);
        METAMODEL_ID =3D addColumn("mm_id", DataType.INTEGER, 0, DataMode.N=
otNull);

        setPrimaryKey(USER_ID);

        DBColumn[] uniqueFields =3D new DBColumn[1];
        uniqueFields[0] =3D USERNAME;

        addIndex("index_username_unique", true, uniqueFields);
    }

The name of the sequence  "us_user_user_id_seq" is the same as that one gen=
erated by PostgreSQL.


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