I have two auditing tables: Trip_aud and Event_aud. They were created in Envers, but I'm querying them with SQL. Their are basically the same as the domain tables, except for a revision value which is incremented every time there is a change and some tuple is inserted in an auditing table.
When Trip changes from a certain status(PLANNING -> EXECUTING), I store its current revision, so after that I can compare what was executed (say, leaving time) with what was planned. These events (leaving, stopping, waiting...) are stored in Event, with a pointer to Trip. Events, too, are audited.
Envers works like a CVS system: if I query for some element at a given revision, it searches for the tuple which has the maximum revision less than the given revision. The revision I'm interested is the one at Trip, stored when it changes state. How can I select all events from a trip in a given revision?
Here's how the tables look like. org_rev is the trip revision where the status changes.
Trip_aud
id | rev | status | org_rev | other columns...
----|-----|----------|---------|---------------
1 | 1 |CREATED | NULL |
1 | 2 |OPTIMIZING| NULL |
1 | 3 |PLANNED | NULL |
1 | ... | ... | NULL |
1 | 44 |EXECUTING | 44 |
1 | 58 |FINISHED | 44 |
Event_aud
id | trip_id | rev | start_time | other columns...
----|---------|-----|------------|---------------
1 | 1 | 1 | 02:35:12 |
2 | 1 | 1 | 03:14:84 |
3 | 1 | 1 | 12:31:02 |
1 | 1 | 2 | 04:00:00 |
2 | 1 | 5 | 03:00:15 |
2 | 1 | 10 | 05:49:59 |
1 | 1 | 40 | 06:00:00 |
1 | 1 | 58 | 06:07:39 |
If I want the trip and events at revision 3, I get
Trip_aud
id | rev | status | org_rev | other columns...
----|-----|----------|---------|---------------
1 | 3 |PLANNED | NULL | ...
Event_aud
id | trip_id | rev | start_time | other columns...
----|---------|-----|------------|---------------
1 | 1 | 2 | 04:00:00 |
2 | 1 | 1 | 03:14:84 |
3 | 1 | 1 | 12:31:02 |
In revision 44, when planning was finished, it is
Trip_aud
id | rev | status | org_rev | other columns...
----|-----|----------|---------|---------------
1 | 44 |EXECUTING | 44 |
Event_aud
id | trip_id | rev | start_time | other columns...
----|---------|-----|------------|---------------
1 | 1 | 40 | 06:00:00 |
2 | 1 | 10 | 05:49:59 |
3 | 1 | 1 | 12:31:02 |
I have made the following query to compare planning and executed, but it returns nothing! It makes a self join in EVENT_AUD, evicts tuple duplicates that differ only on their revision order and tries to select the maximum rev less than org_rev at trip.
select t.id, planned.start_time, realized.start_time
from
TRIP t
inner join EVENT realized on realized.trip_id = t.id
inner join EVENT_AUD planned on planned.id = realized.id
where
planned.id in
(
select ea1.id
from
EVENT_AUD ea1
inner join EVENT_AUD ea2 on ea1.id = ea2.id
where
ea1.rev > ea2.rev
group by ea1.id
having max(ea1.rev) < t.org_rev
)
and t.id = {something given outside}
Curiously, if I replace t.org_rev with 44, it works! What am I doing wrong?
Thanks for any help!
META: is it expected to provide a little database example in CSV, XML, INCLUDE INTOs or whatever so people can test the SQL I'm asking for? How can I attach to the question?
I think that you need to join ea1 and ea2 on trip id too, because this way max gives maximum rev for all events. This join is also missing between event and event_aud. Correlated subquery needs
where ea1.trip_id = t.id
UPDATE:
I did not understand logic in planned in (select ...) so I changed it to not exists:
select t.id TripID,
planned.id pid,
planned.rev,
planned.start_time pst,
realized.start_time rst
from
TRIP t
inner join EVENT realized
on realized.trip_id = t.id
inner join EVENT_AUD planned
on planned.id = realized.id
and realized.trip_id = planned.trip_id
-- Eliminate higher revisions
and planned.rev < t.org_rev
where not exists (select null
from event_aud ea
where ea.trip_id = planned.trip_id
and ea.id = planned.id
-- Eliminate higher revisions
and ea.rev < t.org_rev
-- If there is higher revision than current not exists evaluates to false
and ea.rev > planned.rev)
and t.id = 1
order by 1, 2
Full query is at Sql Fiddle