Update: The sequence table doesn't seem to be necessary at all. If you set the generationtype to IDENTITY you get an autoincrement in MySQL and H2 will create an internal sequence table for this. Also PostgreSQL seems to accept it :) I had to rewrite the initial_data.sql. Got almost everything working except the population of the DB in MySQL. When I'm done I'll create a patch so others can review before I might break things in DB systems I don't have on my machine.
On 6 September 2011 19:58, Jasha Joachimsthal <[email protected]>wrote: > > On 6 September 2011 17:10, Ciancetta, Jesse E. <[email protected]> wrote: > >> >> >> >-----Original Message----- >> >From: Jasha Joachimsthal [mailto:[email protected]] >> >Sent: Tuesday, September 06, 2011 8:21 AM >> >To: [email protected] >> >Subject: Re: JPA sequence generation & MySQL >> > >> >Bit weird but to reply to myself: >> > >> >On 5 September 2011 11:32, Jasha Joachimsthal >> ><[email protected]>wrote: >> > >> >> Hi, >> >> >> >> So far Rave has only used the in memory H2 database. I want to >> configure >> >> Rave to use a MySQL database. The sequence generation annotation we >> >use is >> >> not compatible with MySQL. >> >> >> >> As an example the o.a.r.portal.model.Page: >> >> @SequenceGenerator(name="pageIdSeq", sequenceName = >> >"page_id_seq") >> >> /* ... */ >> >> public class Page implements BasicEntity, Serializable { >> >> /* ... */ >> >> @Id @Column(name="id") >> >> @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = >> >"pageIdSeq" >> >> private Long id; >> >> >> >> This is the kind of error you get: >> >> >> >> Error instantiating named sequence "page_id_seq": Your database >> >dictionary does not support native sequences. To tell the dictionary how >> to >> >select sequence values, use: >> >> openjpa.jdbc.DBDictionary: NextSequenceQuery="SELECT NEXT VALUE FOR >> >"page_id_seq" >> >> >> >> One way to solve this is to create a orm.xml file that overrides the >> >> GeneratedValue strategy: >> >> <entity class="org.apache.rave.portal.model.Page"> >> >> <attributes> >> >> <id name="id"> >> >> <generated-value strategy="SEQUENCE" generator=""/> >> >> >> > >> >This should be strategy="IDENTITY" to use the autoincrement in MySQL >> > >> > >> >> </id> >> >> </attributes> >> >> </entity> >> >> >> >> But this means that if you want to deploy Rave against a MySQL database >> >you >> >> have to check all the beans if they use the @SequenceGenerator and >> >update >> >> your orm.xml. >> >> >> > >> >Plus that for some unknown reason it doesn't work as expected. The >> >database >> >schema is created, all tables have an autoincrement id field, which means >> >the generated-value from the orm.xml has been read and used. However >> >when I >> >want to add a new user, I get the following error: >> >"<openjpa-2.1.1-r422266:1148538 fatal user error> >> >org.apache.openjpa.persistence.ArgumentException: Error instantiating >> >named >> >sequence "user_id_seq": Your database dictionary does not support native >> >sequences. To tell the dictionary how to select sequence values, use: >> >openjpa.jdbc.DBDictionary: NextSequenceQuery="SELECT NEXT VALUE FOR >> >"user_id_seq" >> >Where the above string is replaced with the proper SQL for your >> database." >> > >> >This is strange because during creation of the schema it used the orm.xml >> >but when the entitymanager persists, it suddenly falls back to the >> >annotation. I created a sample app but haven't found this behaviour. I'm >> a >> >bit clueless now. >> > >> > >> > >> >> >> >> Another way is to remove the @SequenceGenerator and the reference in >> >> @GeneratedValue (generator="...") >> >> So are you saying that you'd end up with just the bare @GeneratedValue >> annotation and JPA would use a default strategy of GenerationType.AUTO? >> > > I'm still puzzled why overriding the annotation value in the orm.xml > doesn't always work in Rave, but I'm not an expert on JPA so maybe I've > missed some tiny detail. > > >> >> If that works it seems like it would be ok to me (although I was just >> talking with Matt and he tells me AUTO doesn't work well with Oracle). >> > > Every database comes with its own limitations :( I don't have an Oracle > database to test, do you? > > >> If that doesn't work then I think the only other alternative which is >> guaranteed to be database agnostic would be to use GenerationType.TABLE like >> this: >> >> public class Foo { >> @Id >> @GeneratedValue(strategy=GenerationType.TABLE, >> generator="FooIdGenerator") >> @TableGenerator(name="FooIdGenerator", table="SEQUENCE", >> pkColumnName="SEQ_NAME", >> valueColumnName="SEQ_COUNT", pkColumnValue="Foo", >> allocationSize = 1, initialValue = 1) >> @Column(name = "FOO_ID", nullable = false) >> private Long fooId; >> >> ... >> } >> >> public class Bar { >> @Id >> @GeneratedValue(strategy=GenerationType.TABLE, >> generator="BarIdGenerator") >> @TableGenerator(name="BarIdGenerator", table="SEQUENCE", >> pkColumnName="SEQ_NAME", >> valueColumnName="SEQ_COUNT", pkColumnValue="Bar", >> allocationSize = 1, initialValue = 1) >> @Column(name = "BAR_ID", nullable = false) >> private Long barId; >> >> ... >> } >> >> With that configuration you end up with a SEQUENCE table with a SEQ_NAME >> and SEQ_COUNT column and a row in that table for each entity we're >> generating id's for. >> > > I'll give that a try tomorrow, thanks. It shouldn't be necessary for MySQL > using autoincrement, but I'm not sure about other databases. So far the > attempt to add an abstraction layer in JPA... > > >> >> Actually -- I just went to look something up in the Javadoc for >> TableGenerator and noticed they have a complete example there too: >> >> >> http://java.sun.com/javaee/5/docs/api/javax/persistence/TableGenerator.html >> >> I was going to take another look at the allocationSize attribute -- we >> might need to think about tuning it if we were to go with the table based >> strategy. With an allocation size of 1 the entity manager ends up having to >> query/increment the count on every new insert which could be a performance >> issue for high volume inserts (which I don't think we actually have though), >> but with a larger allocation size you end up potentially using up id numbers >> quicker (I assume the entity manager does something like reads the value at >> initialization, increments it by the allocation size and then writes it back >> thereby reserving a block of id's -- so if that's the default of 50 then >> after the first insert the counter is at 50 -- then if you shut down the >> application before you use up the other 49 id's they end up wasted). >> > > Good point. The default OPENJPA_SEQUENCE_TABLE uses a TINYINT in MySQL to > store the sequence value which has a maximum value of 127 > (onehundredtwentyseven). So it took me 1 restart and 2 tables to crash my > application. I'll continue tomorrow, but thanks for the help! > > >> >> >> >> >> Is there a reason why we explicitly specify the @SequenceGenerator? If >> >not, >> >> can we remove this from all the beans that use it? >> >> >> > >> >The H2 demo content doesn't load (and all tests fail) if the strategy is >> set >> >to INCREMENT. >> > >> >Has someone else successfully run Rave against a different DB than H2 >> >(preferably MySQL)? >> > >> >Jasha >> > >
