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

["Jan Glaubitz (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/EMPIREDB-195?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Jan Glaubitz resolved EMPIREDB-195.
-----------------------------------
    Resolution: Fixed

Sequence handling is improved in Empire-db 3.0.

For PostgreSQL set DBMSHandlerPostgreSQL.usePostgresSerialType = true (default!) to use the auto-generated sequences from postgresql. This results in SERIAL/BIGSERIAL types in the DDL Script. No sequences generated by the DDL generator.

Or set DBMSHandlerPostgreSQL.usePostgresSerialType = false to use Empire-db generated seuqnces (custom names possible). This results in INT/BIGINT in the DDL script.

> 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
>    Affects Versions: empire-db-2.5.1
>            Reporter: Rainer Döbele
>            Assignee: Jan Glaubitz
>            Priority: Major
>             Fix For: empire-db-3.0.0
>
>          Time Spent: 10m
>  Remaining Estimate: 0h
>
> 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
(v8.20.1#820001)