Steve, Thank you again for your reply. So your solution is to replace all the associations in the normal model with filtered One-To-Many associations (I am not using Many-To-Many as we cannot put effective dates on the join table). I do think this is a good solution for three reasons I'm afraid: 1) It means that that historical data model will have a different structure to the normal one => this will limit reuse of code for displaying historical and normal data. At the moment the normal and historical databases are identical once the filters have been applied. (modulo class names changes and effective dates) 2) From a DB perspective it would produce a very strange looking data model - where would all the foreign keys go? We would have to have a lot of join tables - which would be very inefficient - and difficult to extract data from with other tools (e.g. for extraction to a data warehouse). 3) Bidirectional relationships would involve two filtered One-To-Many relationships going both ways - How well this would work in practice?
I am not intending to use this 'fix' with second level cache - as none of the historial stuff needs to be in second level cache - but thank you for pointing that out. I am already explicitly clearing the 1st level cache of historical data every time an historical query is made for that reason. I believe a good solution for storing historical data within Hibernate would be a great plus - and after several weeks of slaving away - I am impressed that Hibernate got so close to being able to handle it in a sane and elegant way. (And a way consistent with the way traditional databases store historical data). I'm not sure how many other people have tried to store full historical data (as opposed to just audit changes) in retrievable way with Hibernate. I appreciate that a good, stable solution would be much better than my 'simple fix/hack' - and may require additions to the Hibernate framework to avoid conflicting with existing functionality. If needed I would be happy to put together some suggestions based on my experience. There are several posts on the forum about storing historical data - and Gavin King has consistently said to use filters - which do work. Thank you for your time, Alex -----Original Message---- From: Steve Ebersole [mailto:[EMAIL PROTECTED] Sent: Mon 9/11/2006 10:45 PM To: Alex Bacon Cc: hibernate-dev@lists.jboss.org; Aaron Scott; Jon Nermut; Howard Oettle Subject: RE: Major issue with Hibernate Filters Nowhere in that documentation does it illustrate usage of filters to change the multiplicity of an association. Again, that is explicitly something for which filters are not intended. The intended way this should be done is to attach the filter to the *collection* representing your historical association. That will then have the filter criteria applied to it when initializing it (either lazily or eagerly). BTW, I hope you are not combining your "simple fix" to make this work and use of a second-level cache... So, no, no need for a feature request. -----Original Message----- From: Alex Bacon [mailto:[EMAIL PROTECTED] Sent: Sunday, September 10, 2006 7:17 PM To: Steve Ebersole Cc: hibernate-dev@lists.jboss.org; Aaron Scott; Jon Nermut; Howard Oettle Subject: RE: Major issue with Hibernate Filters Steve, Thank you for your reply - I figure this may turn into a feature request rather than a bug per-se. Here is the Hibernate Doco I was referring to: http://www.hibernate.org/hib_docs/v3/reference/en/html/filters.html#objectstate-filters I am storing a copy of the pretty much the entire database with effective date filters as per the example. The basic problem is that effective date filters (asOfDate in the example) work fine if you get everything in one query - the association between the entities becomes One-To-One, Many-To-One or whatever ONCE the filters have been applied. In the example you may have many different versions of the same department and many different versions of the same employee - but for a given asOfDate there is only one department record that links to a given employee. BUT if you attempt to lazy load the properties - it fails because the SQL generated from the load in EntityJoinWalker explicitly excludes filters - even if they are active. With relatively simple changes to EntityJoinWalker (to generate the correct SQL) and QueryParameters (to set the filter parameters) I have been able to remedy the problem for us - albeit with explicit assumptions that are only valid for our database. It would be fantastic if Hibernate supported historical data - with effective dates not just on rows but across associations - as this is a common use case for historical data - and I don't believe it would be difficult to implement - all it needs is to include filters when processing unique keys (as opposed to IDs). The historical associations are then based on these unique keys. (Unique keys here - in the sense that they are unique for a given point in time) Thank you for your time - would you like me to raise this as a feature request? Alex -----Original Message----- From: Steve Ebersole [mailto:[EMAIL PROTECTED] Sent: Saturday, 9 September 2006 12:25 AM To: Alex Bacon Cc: hibernate-dev@lists.jboss.org Subject: RE: Major issue with Hibernate Filters You need to point me to where we ever created an example of filter usage "in order to achieve uniqueness". That is expressly *not* a usage of filters; filters by definition cannot change the multiplicity of an association. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Alex Bacon Sent: Friday, September 08, 2006 4:51 AM To: hibernate-dev@lists.jboss.org Subject: Major issue with Hibernate Filters Hibernate developers, I'm afraid the forums had nothing about this issue - and I believe I have found the bit of Hibernate coding causing the problem: I have spent the last few weeks trying to get the Filters to work as per the example in the Hibernate reference manual - the problem is that filters are only currently applied for queries - they are not applied when lazy loading entities - this causes major errors when using the historical filters as per your example that need the filters in order to achieve uniqueness. (I cannot realistically eager load everything in the database! Here is the bit of code from AbstractEntityLoader that appears to be causing the problem: public void processFilters(String sql, SessionImplementor session) { if ( session.getEnabledFilters().size()==0 || sql.indexOf(ParserHelper.HQL_VARIABLE_PREFIX)<0 ) { // HELLA IMPORTANT OPTIMIZATION!!! processedPositionalParameterValues = getPositionalParameterValues(); processedPositionalParameterTypes = getPositionalParameterTypes(); processedSQL = sql; } else { Do you agree this is an issue - and if so - I take it I should raise it as a JIRA issue. If not - can you suggest a workaround? Alex -----Original Message----- From: [EMAIL PROTECTED] on behalf of [EMAIL PROTECTED] Sent: Fri 9/8/2006 12:17 PM To: hibernate-dev@lists.jboss.org Subject: hibernate-dev Digest, Vol 3, Issue 32 Send hibernate-dev mailing list submissions to hibernate-dev@lists.jboss.org To subscribe or unsubscribe via the World Wide Web, visit https://lists.jboss.org/mailman/listinfo/hibernate-dev or, via email, send a message with subject or body 'help' to [EMAIL PROTECTED] You can reach the person managing the list at [EMAIL PROTECTED] When replying, please edit your Subject line so it is more specific than "Re: Contents of hibernate-dev digest..." Today's Topics: 1. hibernate-sybase-testsuite Build Completed With Testsuite Errors ([EMAIL PROTECTED]) ---------------------------------------------------------------------- Message: 1 Date: Thu, 7 Sep 2006 22:17:33 -0400 (EDT) From: [EMAIL PROTECTED] Subject: [hibernate-dev] hibernate-sybase-testsuite Build Completed With Testsuite Errors To: hibernate-dev@lists.jboss.org, [EMAIL PROTECTED] Message-ID: <[EMAIL PROTECTED]> Content-Type: text/plain; charset="us-ascii" An HTML attachment was scrubbed... URL: http://lists.jboss.org/pipermail/hibernate-dev/attachments/20060907/4cb1300f/attachment.html ------------------------------ _______________________________________________ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev End of hibernate-dev Digest, Vol 3, Issue 32 ******************************************** _______________________________________________ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev
