Geek Answers Handbook

Version control algorithm

I have a database where I store objects. I have the following (simplified) scheme

CREATE TABLE MyObjects ( UniqueIdentifier Id; BigInt GenerationId; BigInt Value; Bit DeleteAction; )

Each object has a unique identifier ("Id") and a property (set) ("Value"). Each time a property value for an object changes, I enter a new row in this table with a new generation identifier ("GenerationId", which monotonously increases). If the object is deleted, then I record this fact by setting the "DeleteAction" bit to true.

At any point in time (generation), I would like to get the status of all my active objects!

Here is an example:

 Id GenerationId Value DeleteAction 1 1 99 false 2 1 88 false 1 2 77 false 2 3 88 true

Generational Objects:

  1: 1 {99}, 2 {88} 2: 1 {77}, 2 {88} 3: 1 {77}

Key: how can I find out a row for each unique object that has a generation identifier closest (but not exceeding) to a given generation identifier ? Then I can do a post-filter to delete all rows where the DeleteAction field is true.

+3
source share
4 answers

Here's the working version:

 SELECT MyObjects.Id,Value FROM Myobjects INNER JOIN ( SELECT Id, max(GenerationId) as LastGen FROM MyObjects WHERE GenerationId <= @TargetGeneration Group by Id ) T1 ON MyObjects.Id = T1.Id AND MyObjects.GenerationId = LastGen WHERE DeleteAction = 'False'
+1
source

It works in MS SQL

 SELECT id,value FROM Myobjects INNER JOIN ( SELECT id, max(GenerationID) as LastGen FROM MyObjects WHERE GenerationID <= @Wantedgeneration Group by ID) On GenerationID = LastGen WHERE DelectedAction = false
+4
source

My version uses a join of the MyObjects table with a subset of itself created by a subquery and containing only the latter for each object:

 SELECT O.id,generation,value FROM MyObjects O, (SELECT id,max(generation) AS max_generation FROM MyObjects WHERE generation <= $GENERATION_ID GROUP BY id) AS TheMax WHERE TheMax.max_generation = generation AND O.deleted is False ORDER BY generation DESC;

In the above query, GENERATION_ID is tightly bound. The way parametrize is to write a function:

 CREATE OR REPLACE FUNCTION generation_objects(INTEGER) RETURNS SETOF MyObjects AS 'SELECT O.id,generation,value,deleted FROM MyObjects O, (SELECT id,max(generation) AS max_generation FROM MyObjects WHERE generation <= $1 GROUP BY id) AS TheMax WHERE TheMax.max_generation = generation AND O.deleted is False;' LANGUAGE SQL;

Now it works. Using this table:

 > SELECT * FROM MyObjects; id | generation | value | deleted ----+------------+-------+--------- 1 | 1 | 99 | f 2 | 2 | 88 | f 1 | 3 | 77 | f 2 | 4 | 88 | t 3 | 5 | 33 | f 4 | 6 | 22 | f 3 | 7 | 11 | f 2 | 8 | 11 | f

I get:

 > SELECT * FROM generation_objects(1) ORDER by generation DESC; id | generation | value | deleted ----+------------+-------+--------- 1 | 1 | 99 | f > SELECT * FROM generation_objects(2) ORDER by generation DESC; id | generation | value | deleted ----+------------+-------+--------- 2 | 2 | 88 | f 1 | 1 | 99 | f > SELECT * FROM generation_objects(3) ORDER by generation DESC; id | generation | value | deleted ----+------------+-------+--------- 1 | 3 | 77 | f 2 | 2 | 88 | f

And then, in the next generation, object 2 is deleted:

 > SELECT * FROM generation_objects(4) ORDER by generation DESC; id | generation | value | deleted ----+------------+-------+--------- 1 | 3 | 77 | f
+2
source

Not sure if this is standard SQL, but in Postgres you can use the LIMIT flag:

  select GenerationId,Value,DeleteAction from MyObjects where Id=1 and GenerationId < 3 order by GenerationId limit 1;
0
source

More articles:


All Articles