Hi Jasha, As you mentioned below not being able to load the initial data outside of the pre-packaged demo environment that we provide probably isn't that big of a deal. If someone is using an alternate database then they're probably setting up their own environment anyway and our initial sample data probably isn't going to be all that useful for them (but could possibly serve as a starting point for them to build their own initial data population scripts).
So if we agree on that then I think we just need to find a solution for ID generation that: 1) Works across all databases (or at least the "big" ones if we care to try to define that, but it would be nice to be able to just say "all"). 2) Can be made to work with H2 and our initial data population strategy for demo and testing purposes (whatever strategy that may be -- currently low level SQL). So I think this could potentially be as simple as changing the strategy to AUTO and leaving the initial data as is, assuming AUTO results in sequences being used under the covers with H2 (although we'd likely have to figure out the names of the auto generated H2 sequences and update initial data accordingly). We could also add a comment to initial data stating that it was written and tested with H2 and that it may not work with other databases. On the other hand -- I don't think I really like the ID generation strategy being different depending on the database used under the covers -- it just doesn't feel "right" to me... So I think I'd maybe lean towards using the table based strategy just to achieve consistency across all databases, but I wouldn't argue if people wanted to use AUTO to try it out and see how it works in practice. --Jesse >-----Original Message----- >From: Jasha Joachimsthal [mailto:[email protected]] >Sent: Thursday, September 08, 2011 12:13 PM >To: [email protected] >Subject: JPA issues & solutions > >As I mailed before I'm facing several issues with using a different database >setup that works on multiple platforms. > >The current setup: >1) is configured for H2 database >2) has 1 set of data for test purposes and for the demo portal >3) uses sequences to maintain ID's in tables >4) uses low level SQL queries to populate the database > >1) is easy to fix with placeholders in the Spring configuration > >2) is easy to fix if we test against content in src/test/resources instead >of src/main/resources. As a starter we can copy the initial_data.sql from >main to test. > >3) is an issue with MySQL because it does not support sequences. According >to http://bit.ly/n5YsNF only some databases support this. It should be >possible to override the strategy in a file named orm.xml. If we can >override the strategy for MySQL, the initial data will not be loaded but >that's not a major issue (for deployment on a server you probably want your >own set of data). > >> In Rave this override works for creating the schema but it is ignored when >you insert data through JPA. Can someone with more knowledge over JPA >check >(and fix) the override mechanism with orm.xml? > >There are other ID strategies: >- AUTO: probably the "cleanest" (leave it to the DB). Works on all databases >but we need to rewrite the initial data (see #4). >- TABLE: Works on all databases but we need to rewrite the initial data (see >#4). >- INCREMENTAL: works on H2, MySQL and PostgreSQL, but (probably) not on >Oracle. I have a working initial_data.sql hat works for both H2 and MySQL >with this strategy. > >> INCREMENTAL can be a (short term) working solution to support MySQL if >there is no current demand to support Oracle. > >4) the low level SQL queries are error prone and they only work for the >current database + ID strategy. Using a different ID strategy will mean a >complete rewrite of all SQL queries with simulation of JPA's behaviour if a >separate table is being used to maintain sequences. I've already created an >issue to replace this mechanism with a vendor neutral mechanism >https://issues.apache.org/jira/browse/RAVE-258 which relates to >https://issues.apache.org/jira/browse/RAVE-257 entered by Scott. > >> How should this mechanism work? >> In which format should we import/export (XML, JSON, ...)? >> Is someone already working on this? If not, who can help me writing such a >mechanism? > > >Jasha Joachimsthal > >Europe - Amsterdam - Oosteinde 11, 1017 WT Amsterdam - +31(0)20 522 4466 >US - Boston - 1 Broadway, Cambridge, MA 02142 - +1 877 414 4776 (toll free) > >www.onehippo.com
