Hi Rainer,
I have been researching this issue with a colleague and we have found a
solution. Moreover, I don't know if this issue is caused by a potential bug
of empireDB; you may confirm this aspect.
First of all, I am using:
empireDB 2.4.2-rc1 version.
PostgreSQL database engine and DBDatabaseDriverPostgreSQL driver.
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.
I don't know if this is the same what was happening to you using Oracle
database engine. But this solutions works perfect in PostgreSQL.
If you need further information in case this is a fixable bug, just let me
know.
Thanks a lot for your support in any case, and best regards.
Jon
2013/10/8 Rainer Döbele <[email protected]>
> Hi Jon,
>
> is simply recreate the sequences like this:
>
> DROP SEQUENCE MY_SEQUENCE;
> CREATE SEQUENCE MY_SEQUENCE INCREMENT BY 1 START WITH ?;
>
> Where ? is the highest id+1 in the table.
>
> But there may be better ways.
>
> Regards
> Rainer
>
> > from: Jon Frias [mailto:[email protected]]
> > to: [email protected]
> > re: Re: empire db 2.4.1: running scripts and autoGenerated fields
> >
> > Hi Rainer,
> >
> > Thanks for your help.
> >
> > It is exactly what happens to me.
> >
> > Could you please tell me how you do it in Oracle? Then I can look for the
> > corresponding code for PostgreSQL.
> >
> > Thanks a lot.
> >
> > Best regards,
> >
> > Jon
> >
> >
> >
> >
> > 2013/10/8 Rainer Döbele <[email protected]>
> >
> > > Hi Jon,
> > >
> > > OK, if I get it right, you insert records manually with your initial
> > > load script and you then get an error once you insert the first record
> > > using DBRecord.update()?
> > >
> > > Looks to me that you are not adjusting the sequence value after you
> > > have inserted your initial records.
> > > Internally the PostgreSQL driver obtains a new user-id by calling
> > > SELECT nextval('us_user_user_id_seq ').
> > > If you have inserted values but did not adjust the sequence value then
> > > SELECT nextval('us_user_user_id_seq ') will return 1 - just as if no
> > > records were in the table.
> > >
> > > I need to do this in Oracle too when I perform initial loads.
> > > But I can't tell you how to adjust a sequence value in Postgre.
> > >
> > > Regards
> > > Rainer
> > >
> > >
> > >
> > > > from: Jon Frias [mailto:[email protected]]
> > > > to: [email protected]
> > > > re: Re: empire db 2.4.1: running scripts and autoGenerated fields
> > > >
> > > > Hi Rainer,
> > > >
> > > > Thank you for your quick response. I will answer to your questions
> > > > by
> > > bullets,
> > > > if it's ok for you.
> > > >
> > > > 1- yes. It works fine
> > > >
> > > > 2- I use the DBScript instance to init the database information,
> > > > that
> > > is, to
> > > > register the information by default such as the user-by-default, the
> > > admin
> > > > user role, guest user role, etc. And this script works fine also. No
> > > error is
> > > > triggered here.
> > > >
> > > > 3- Once this is done, I receive an event from another program and I
> > > execute
> > > > the "createUser" function. To do this, I use the DBRecord instance
> > > > (as I
> > > said in
> > > > my previous email). When the record is created, the
> > "record.update(conn)"
> > > > instruction triggers the unique_key_constraint_violation error. Not
> > > because
> > > > of the "username" field, the origin of the error is the "user_id"
> > > > field,
> > > that is,
> > > > the autoGenerated one by the sequence.
> > > >
> > > > This happens because when I init the database information using the
> > > > DBScript, the data table has this information:
> > > >
> > > > user_id | first_name | last_name | username | password |
> > > > user_account_state
> > > > |
> > > >
> > > > 1 | john | doe | admin | 1234 | true
> > > >
> > > > And when I create the next one using the DBRecord, the record is
> > > > trying
> > > to
> > > > register
> > > >
> > > > *1* | new | user | newUser | 5678 | true
> > > >
> > > > Instead of
> > > >
> > > > *2* | new | user | newUser | 5678 | true
> > > >
> > > > As you can see, the violation error is triggered because of the
> "user_id"
> > > > field.
> > > >
> > > > It seems that when I execute the script to perform the INSERT
> > > instructions,
> > > > the sequences of the auto generated fields (the ids of each data
> > > > table)
> > > don't
> > > > start correctly ("us_user_user_id_seq" in this case). So when I am
> > > creating
> > > > new ones later, the sequence starts and generates an error because
> > > > there are two different users with the same user_id.
> > > >
> > > > Thanks for your help
> > > >
> > > > Best regards,
> > > >
> > > > Jon
> > > >
> > > >
> > > >
> > > > 2013/10/8 Rainer Döbele <[email protected]>
> > > >
> > > > > Hi Jon,
> > > > >
> > > > > thanks for the info.
> > > > > I am a bit confused in what you are trying to achieve.
> > > > >
> > > > > 1. To create the database schema (DDL) you correctly use:
> > > > > //To create the database schema
> > > > > DBSQLScript script = new DBSQLScript();
> > > > > db.getCreateDDLScript(driver, script);
> > > > > script.run(driver, conn, false);
> > > > > db.commit();
> > > > >
> > > > > Does this work?
> > > > >
> > > > > 2. Then - as I understand it- you are using a new DBScript
> > > > > instance to add one statement containing all inserts and execute
> it.
> > > > > This is where you are getting your "unique key constraint is
> > > > > violated", right?
> > > > >
> > > > > First you don't need a DBScript for that, you may as well simply
> call
> > > > > db.executeSQL(stmt, null, conn, null) But I am not sure,
> > > > > whether it can handle multiple inserts at a time.
> > > > > Ideally you would execute each insert separately this way.
> > > > >
> > > > > Second if you get a unique key constraint is violated I assume
> > > > > that this is because of your Unique index "index_username_unique".
> > > > > Are you sure all usernames in your insert statements are unique?
> > > > > (If you execute them one by one you will find out more easily)
> > > > >
> > > > > Finally I am missing the generation of the user-id form the
> sequence.
> > > > > As I understand it there should be something like SELECT nextval('
> > > > > us_user_user_id_seq ') somewhere.
> > > > > But unfortunately I am not using PostgreSQL and I cannot really
> > > > > say how this works there.
> > > > >
> > > > > I hope my comments help with your problem.
> > > > > Regards
> > > > > Rainer
> > > > >
> > > > >
> > > > > > from: Jon Frias [mailto:[email protected]]
> > > > > > to: [email protected]
> > > > > > re: Re: empire db 2.4.1: running scripts and autoGenerated
> > > > > > fields
> > > > > >
> > > > > > Hi Rainer,
> > > > > >
> > > > > > Thanks for your response and sorry for the missing information.
> > > > > >
> > > > > > 1- The database I am using is PostgreSQL and the driver is
> > > > > > DBDatabaseDriverPostgreSQL
> > > > > >
> > > > > > 2- The data tables (40+-) are similars to each others, so here
> > > > > > you have
> > > > > an
> > > > > > example:
> > > > > >
> > > > > > Data table Users: user_id | first_name | last_name |
> username |
> > > > > > password | user_account_state |
> > > > > >
> > > > > > declared as follows in its corresponding class:
> > > > > >
> > > > > > 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);
> > > > > >
> > > > > > setPrimaryKey(USER_ID);
> > > > > >
> > > > > > DBColumn[] uniqueFields = new DBColumn[1];
> > > > > > uniqueFields[0] = USERNAME;
> > > > > >
> > > > > > addIndex("index_username_unique", true, uniqueFields); }
> > > > > >
> > > > > > 3- the script line to insert a new user at the initialization
> > > > > > phase is
> > > > > this:
> > > > > > INSERT INTO us_user (first_name, last_name, username, password,
> > > > > > user_account_state) VALUES ('john', 'doe', 'admin', '1234',
> > > > > > true);
> > > > > >
> > > > > > 4- the code to generate the new user in the normal execution
> > > > > > (not in the initialization phase)
> > > > > > NOTE: userSettings is a class of "user" with getters and setters
> > > > > > to
> > > > > get/set the
> > > > > > user information
> > > > > >
> > > > > > DBRecord record = new DBRecord(); record.create(db.USUSER);
> > > > > > record.setValue(db.USUSER.FIRST_NAME,
> > > > > > userSettings.getFirstName());
> > > > > > record.setValue(db.USUSER.LAST_NAME,
> > > > > > userSettings.getLastName());
> > record.setValue(db.USUSER.USERNAME,
> > > > > > userSettings.getUserName());
> > record.setValue(db.USUSER.PASSWORD,
> > > > > > userSettings.getPassword());
> > > > > > record.setValue(db.USUSER.USER_ACCOUNT_STATE,
> > > > > > userSettings.getUserAccountState());
> > > > > > record.update(conn);
> > > > > >
> > > > > >
> > > > > > If you need any furhter information, just let me know.
> > > > > >
> > > > > >
> > > > > > 5- In the examples you mentioned, in order to create a column in
> > > > > > an
> > > > > existing
> > > > > > data table, the code is as follows:
> > > > > >
> > > > > > DBSQLScript script = new DBSQLScript();
> > > > > > db.getDriver().getDDLScript(DBCmdType.CREATE, C_FOO, script);
> > > > > >
> > > > > > In my first email, my code to generate the script in order to
> > > > > > initialize
> > > > > the
> > > > > > database was like this:
> > > > > >
> > > > > > DBSQLScript script = new DBSQLScript(); script.addStmt(sql); try{
> > > > > > script.run(driver, conn, false);
> > > > > > db.commit(conn);
> > > > > > }
> > > > > >
> > > > > > Where, "sql" is a string variable containing all the INSERTS
> > > > > > instructions (similars to 3rd bullet at this email); it may
> > > > > > contain
> > > > > > 20 insert
> > > > > instructions more
> > > > > > or less.
> > > > > >
> > > > > > I noticed that I haven't included the
> "db.getDriver().getDDLScript"
> > > > > > code
> > > > > line.
> > > > > > Even if the script executes the insert instructions correctly,
> > > > > > could
> > > > > this be the
> > > > > > error I have? If so, how it should be done? One Insert at a
> > > > > > time? Or I
> > > > > can
> > > > > > execute all of them at the same time? And the second parameter,
> > > > > > what should it be?
> > > > > >
> > > > > > Thanks a lot.
> > > > > >
> > > > > > Jon
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > 2013/10/8 Rainer Döbele <[email protected]>
> > > > > >
> > > > > > > Hello Jon,
> > > > > > >
> > > > > > > from your description I cannot really figure out what your
> > > > > > > problem
> > > is.
> > > > > > > First thing you should tell us is what database i.e. DBMS you
> > > > > > > are using and which Empire-db database driver
> > > > > > > (DBDatabaseDriver...)
> > > > > > >
> > > > > > > Next thing is, that we need to know what your database / table
> > > > > > > definition looks like and what SQL is generated when you
> > > > > > > insert /
> > > > > update
> > > > > > records.
> > > > > > > The latter should be in the logs or you can set a breakpoint
> > > > > > > in
> > > > > > > DBRowSet.updateRecord() - around line 812.
> > > > > > >
> > > > > > > Finally our examples - which by default use HSQLDB - do the
> > > > > > > same thing and you may want to look at what is different.
> > > > > > >
> > > > > > > In any case it should not have anything to do with DBSQLScript
> > > > > > > at
> > > all.
> > > > > > >
> > > > > > > Regards
> > > > > > > Rainer
> > > > > > >
> > > > > > >
> > > > > > > > from: Jon Frias [mailto:[email protected]]
> > > > > > > > to: [email protected]
> > > > > > > > re: empire db 2.4.1: running scripts and autoGenerated
> > > > > > > > fields
> > > > > > > >
> > > > > > > > Hi all,
> > > > > > > >
> > > > > > > > I have the following problem and I don't know if this is
> > > > > > > > normal or I am
> > > > > > > doing
> > > > > > > > something wrong. Can anybody help?
> > > > > > > >
> > > > > > > > Once I have a database schema created I want to initialize
> > > > > > > > the first
> > > > > > > registers.
> > > > > > > > To do this, I am using an script (I load it and run it).
> > > > > > > > Until now
> > > > > > > everything
> > > > > > > > works fine (the database schema and the first registers are
> > > > > > > > created correctly).
> > > > > > > >
> > > > > > > > The problem comes after this.
> > > > > > > >
> > > > > > > > All the autoGenerated fields (id fields) are not working
> > > > > > > > properly and
> > > > > > > they
> > > > > > > > don't take into account the registers created by the script,
> > > > > > > > so every
> > > > > > > time I
> > > > > > > > create a new record after this using DBRecord class, etc. an
> > > > > > > > error
> > > > > > > message is
> > > > > > > > triggered saying that the "unique key constraint is
> violated".
> > > > > > > >
> > > > > > > > I don't know if I am doing something wrong or if this is
> > > > > > > > normal and I
> > > > > > > have to
> > > > > > > > avoid using the script. Because if I initialize the first
> > > > > > > > registers by
> > > > > > > code (using
> > > > > > > > DBRecord class -- the same registers information),
> > > > > > > > everything works
> > > > > fine.
> > > > > > > >
> > > > > > > > The source code is the following:
> > > > > > > >
> > > > > > > > //To create the database schema DBSQLScript script = new
> > > > > > > > DBSQLScript(); db.getCreateDDLScript(driver, script); try{
> > > > > > > > script.run(driver, conn, false);
> > > > > > > > db.commit();
> > > > > > > > }
> > > > > > > >
> > > > > > > > //To execute the script to initialize the database
> > > > > > > > information
> > > > > > > DBSQLScript
> > > > > > > > script = new DBSQLScript(); script.addStmt(sql); try{
> > > > > > > > script.run(driver, conn, false);
> > > > > > > > db.commit(conn);
> > > > > > > > }
> > > > > > > >
> > > > > > > > I have also tried the script step with this code but the
> > > > > > > > result obtained
> > > > > > > was the
> > > > > > > > same
> > > > > > > >
> > > > > > > > driver.executeSQL(sql, null, conn, null); //driver is the
> > > > > > > > DatabaseDriver instance
> > > > > > > >
> > > > > > > >
> > > > > > > > Can anybody tell me if I am doing something wrong?
> > > > > > > >
> > > > > > > > Thanks a lot for your help.
> > > > > > > > Best regards,
> > > > > > > >
> > > > > > > > Jon
> > > > > > >
> > > > >
> > >
>
