Query Row History
What would you like to be added or enhanced
immutability provides a new perspective about changes, actually immutability makes changes explicit as no in-place mutations are made but appended. The final state may be interpreted as the latest appended change or in general, as a function of all updates made to a same entity.
Note: immudb is not just immutable but cryptographically verifiable.
At the SQL layer, immudb provides a special statement for updating rows based on its primary key i.e. UPSERT
Currently, it's only possible to query the latest row state using standard SQL grammar thus providing a way to query historical row values may be required in order to fetch all the updates made to a given row.
The following illustrative examples may describe a possible approach to solve this problem:
SELECT * FROM (N? (FORWARD|BACKWARD)? UPDATES (OFFSET M)? AT mytable)SELECT * FROM mytable WHERE name = @name
-- would be equivalent to
SELECT * FROM (1 UPDATES AT mytable) WHERE name = @name
-- or
SELECT * FROM (1 BACKWARD UPDATES AT mytable) WHERE name = @name
-- or
SELECT * FROM (1 BACKWARD UPDATES OFFSET 0 AT mytable) WHERE name = @name
-- return latest row value
1 BACKWARD UPDATES OFFSET 0 AT mytable
-- return first row value
1 FORWARD UPDATES OFFSET 0 AT mytable
-- return second row value
1 FORWARD UPDATES OFFSET 1 AT mytable
-- return first two row values
2 FORWARD UPDATES OFFSET 0 AT mytable
-- or
2 FORWARD UPDATES AT mytable
-- return all row values from newst to older
BACKWARD UPDATES OFFSET 0 AT mytable
-- return all row values from older to newst
FORWARD UPDATES OFFSET 0 AT mytable
-- return N row values from newst to older
N BACKWARD UPDATES OFFSET 0 AT mytable
-- return N row values from older to newst
N FORWARD UPDATES OFFSET 0 AT mytableSource: codenotary/immudb