Hi, I can test this but need to find the time, thanks for reporting Jon and indeed a patch is always welcome :-)
Cheers, Francis On 9 October 2013 10:11, Rainer Döbele <[email protected]> wrote: > 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 >> > > > > > > > >> > > > > > >> > > > >> >
