On 1/6/2011 5:50 PM, Kenneth Tilton wrote:
[a meta-question for all the below is "what's a
good link for hairy SQL"?]
A while ago I worked on a project where we had some hairy SQL
collapsing multiple rows of pseudo-rdf triples (columns
subject,predicate, and object) into one flattened row in which a
hard-coded case/max (I forget the exact syntax) plucked out a
value for a column "X" by looking for a row with the predicate
"X". So we'd find all the rows for one subject (a logical row
ID) and collapse it into a single row of conventional columns.
I now want to do something similar, but combining a conventional
XY table with columns X and Y and a separate change-history
table with columns row-id, column-name, value, and
last-time-in-effect, to which old values get pushed by an update
trigger on XY when X or Y change.
The goal is to specify a time in the past and recreate a row of
XY as of that time, by starting from the current row and
replacing X and Y if necessary by the value that would have been
current then, ie with the max last-time-in-effect <= the
specified time.
My thought was to read XY for a desired row-id with a left join
to the change-history table where last-time-in-effect <= the
specified time (pretty obvious) but the stumper is the clause
for the x-then and y-then columns. A useless start:
case when column_name = "X" then change_history.value else
XY.X end case as x-then
I had some luck with that, but I also want to pick out the most
recent change up to the target time where there were multiple
changes.
btw, I have this (actual names with a "states" table and changes
held in a "triple" table modelled after rdf where s=row-id,
p=column-name, and ostr = the prior value):
select max (case when p='name' then ostr else 'n' end) as xx
from states left outer join triple on states.id=s
where s=1 and created < '2011-01-06 15:52:30';
That happens to produce the right result but only because the rows
are coming back in order created...hmm. Can I rely on that? Meaning
the left join on the "triple" table will always return them in the
order created?
kt
kt
ps. Is this a crazy way to achieve our requirement, which is to
see the DB as it was at a point in the past? Our data is
incredibly stable, btw. kt
|
