codenotary / codenotary/immudb
Query Row History
- Dominant language
- Go
- Stars
- 9k
- Forks
- 378
- Avg merge
- 3d 1h
- Merged PRs (30d)
- 3
Description
**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:
```sql
SELECT * FROM (N? (FORWARD|BACKWARD)? UPDATES (OFFSET M)? AT mytable)
```
```sql
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 mytable
```
Contributor guide
Assessment
This issue has not been assessed yet.