Short update: I have everything working in the portal with H2 & MySQL using TABLE strategy (the initial_data will be a lot bigger). :) Commits will follow after cleaning up. :)
On 8 September 2011 20:09, Jasha Joachimsthal <[email protected]>wrote: > Hi Jesse, > > > On 8 September 2011 19:23, Ciancetta, Jesse E. <[email protected]> wrote: > >> 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). >> > > +1 > I noticed that page_layout must contain the values from initial_data to let > the portal work. The dropdown of the layout is static, but its values are > looked up in the database. Same for the popup you get when you add a new > tab. These are things that may need some change (populate the dropdown based > on actual data and adding some checks). > > >> 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. >> > > H2 uses a single table to maintain the sequences if you switch to AUTO. > It's a bit different than the current setup. > > >> >> 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. >> > > The override mechanism for the annotation with XML should be documented > when it works again. Then the developer (or dba) can choose if (s)he goes > for the default strategy or a different one that better suits his/her > system. > > Thanks for your reply. > > Jasha > > >> >> --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 >> > >
