Hi, If you need any furhter information or I can help somehow, just let me know.
Thanks, Jon 2013/10/9 Francis De Brabandere <[email protected]> > 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 > >> > > > > > > > > >> > > > > > > >> > > > > >> > >
