• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Audited tables

 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
We've recently developed an application that has audit triggers which update an object history. We've implemented Hibernate for object persistence but we've had some trouble with providing an elegant solution to audit table reading. Specifically, we have a table X (a complex object with array-based child attributes) and its audit history X_audit. Creation and updates are pretty simple becuase the triggers fire audit records into X_audit without intervention from Hibernate.

However, the difficulty comes about when selecting time-driven data from X/X-audit. The audit table has a from- and to- date on it, and the X table has a from- on it. So selection is a complex process: for Date (D) if there is an X-audit record with (D) between from- and to- then retrieve this. Otherwise, if X there is an X with from- < (D) then use this.

My question is twofold: (i) what's the best object-relational mapping technique to represent this? I've mapped X as an entity and done the SQL using a createSqlQuery() method. (ii) What's the most elegant way of doing the retrieve ? I don't think it can be done in pure SQL because it requires logical precidence.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic