[EMPIREDB-195] Review PostgreSQL driver's DDL generation for sequences

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: empire-db-2.5.1
• Fix Version/s: empire-db-3.0.0
• Component/s: Core
• Assignee: Jan Glaubitz
• Reporter: Rainer Döbele
• Created: 09/Oct/13 08:02
• Updated: 07/Mar/22 11:52
• Resolved: 07/Mar/22 11:52

Description

On 8.10.2010 Jon Frias <[e-mail]> 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.

Issue Links

• is related to: EMPIREDB-284 Sequences left after dropping my PostGreSQL dataBase
• links to: GitHub Pull Request #6