Hi Jon, thanks for letting us know and I am glad that you found a solution.
This may indeed be a bug in the PostgreSQL driver as this issue is specific for each database. As I do not have PostgreSQL myself I cannot really fix and test it. But I have created an issue in JIRA for that and attached your description: https://issues.apache.org/jira/browse/EMPIREDB-195 Someone with PostgreSQL should review and correct this. You may as well change the class DBDatabaseDriverPostgreSQL yourself either by copying it to your project or subclassing it and then send us your changes either as a patch file or the whole class by attaching it to the JIRA issue. Cheers, Rainer > from: Jon Frias [mailto:[email protected]] > to: [email protected] > re: Re: empire db 2.4.1: running scripts and autoGenerated fields > > 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 > > > > > > > > > > > > > > > > > > > >
