codenotary / codenotary/immudb

Query Row History

Open
#930 7 comments 11 reactions 1 assignee Claimed by @jeroiraz View on GitHub
enhancement
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.