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

["jan (JIRA)" <em...@incubator.apache.org>]

    [ https://issues.apache.org/jira/browse/EMPIREDB-195?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16338937#comment-16338937 ] 

jan commented on EMPIREDB-195:
------------------------------

I had the same issue and worked around it like Jon did.

I looked into the PostgreSQL documentation (https://www.postgresql.org/docs/current/static/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE) and found this method:
|{{pg_get_serial_sequence(_{{table_name}}_, _{{column_name}}_)}}|{{text}}|get name of the sequence that a serial or identity column uses|

 

I think we could remove CREATE SEQUENCEs from the PostgreDDLGenerator and override getColumnAutoValue in DBDatabaseDriverPostgreSQL, and use SELECT currval(pg_get_serial_sequence('sometable', 'id')); (an example from the documentation) to obtain the nextval from the correct sequence.

 

What do you think? It might break backward-compatibility.

 

> 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
>            Priority: Major
>
> 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
(v7.6.3#76005)