immudb
immudb copied to clipboard
Question: Get history list
Hi,
I'm new to immudb and just tried it with go code using database/sql
but need some insight.
Let's say i have product table and i update the price of product id 3 times.
- How can i get history list of the product id so i can later compare when the update happened and what was changed.
- When using
BEFORE or AFTER TX 5
how can i know the TX number?
Thank you.
Hi @iambudi,
a single field update in a table doesn't mean a separate TX as you can execute many SQL statements in one transaction. Therefore its often not possible to pin an update to a transaction.
To see all updates you can either use SINCE TX @initialTx
or a date range SELECT * FROM products SINCE '2022-01-06 11:38' UNTIL '2022-01-06 12:00'
You can find more here: https://docs.immudb.io/master/develop/sql/querying.html#time-trave
Hi @dzlabsch,
- What is
@initialTx
? i get errorrpc error: code = Unknown desc = missing parameter(initialtx)
is it built in parameter or should i replace with a certain TX number? - Using
SINCE
andUNTIL
does not return the historical list, only 1 row with latest update ($6.20).
My expectation it returns 3 rows with different price values as i update the price 3 times before. Am i wrong understanding this?UPDATE products SET price = '$5.80' WHERE id = 1; UPDATE products SET price = '$6.00' WHERE id = 1; UPDATE products SET price = '$6.20' WHERE id = 1; SELECT * FROM products SINCE '2022-01-06 11:38' UNTIL '2022-08-07 12:00' WHERE id = 1;
Thank you.
Hi @dzlabsch,
What is
@initialTx
? i get errorrpc error: code = Unknown desc = missing parameter(initialtx)
is it built in parameter or should i replace with a certain TX number?Using
SINCE
andUNTIL
does not return the historical list, only 1 row with latest update ($6.20).UPDATE products SET price = '$5.80' WHERE id = 1; UPDATE products SET price = '$6.00' WHERE id = 1; UPDATE products SET price = '$6.20' WHERE id = 1; SELECT * FROM products SINCE '2022-01-06 11:38' UNTIL '2022-08-07 12:00' WHERE id = 1;
My expectation it returns 3 rows with different price values as i update the price 3 times before. Am i wrong understanding this?
Thank you.
Hi @iambudi, it's a good point. Currently the SQL queries are not returning the history, restricting the period based on transaction IDs or timestamp will still resolve to the latest value assigned in such period.
There is an open request I wrote but we haven't yet implemented, would you like to take a look at it and provide feedback? https://github.com/codenotary/immudb/issues/930