[
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 <[email protected]> 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)
