xtdb 2021-06-22 | Slack Archive

bocaj01:06:31

I’m prototyping Crux as a way to detect new rows that are added to a flat file. The flat file is usually everything currently valid in another system (e.g. current employees). So far, Sqlite is easy to get working. 2 questions: How would you query all (any entity) the transactions that occur after a time (that is each new or updated record since 2 days ago)? Also, what does compaction do when tx and doc are stored in Sqlite. Can I trigger compaction? After looking through how crux stores tx and docs in sqlite, I think it will work for my use case. I want to “watch” a flat file that changes each day. I think I can

refset10:06:51

Hey @U068BQFJ9 good question 🙂 > the transactions that occur after a time This should be broadly possible by using the open-tx-log API, however, if you want to annotate the transactions further you could create a reified "transaction metadata" entity that gets updated alongside each actual transaction. You can then use entity-history and do whatever manual joins you might need to as separate queries. Is the flat file stored on the filesystem? Or is that being stored in Sqlite also? > Can I trigger compaction? That should be safe, yep!

bocaj15:06:09

Thanks. Reify looks best right now I can just query metadata (content hash and transaction time) If you have an example in mind, let me know

bocaj18:06:36

Using entity-history I’m working on understanding the :start and :end opts. If I put in all future dates, and I know there are no valid-time or transaction-time in the future, what should be returned? Right now, I get the same result as if no start and end date were given. e.g. `(crux/entity-history (crux/db node) “123” :asc {:with-docs? true :start {:crux.tx/tx-time #inst “2022-06-23T00:00:00.000" :crux.db/valid-time #inst “2022-06-23T00:00:00.000”} :end {:crux.tx/tx-time #inst “2022-06-23T00:00:00.000" :crux.db/valid-time #inst “2022-06-23T00:00:00.000”}})`

refset21:06:28

Ah, I think we may have some stale information in our API docs here...I think we've failed to update our Clojure doc strings properly, which probably needed to be done in this PR with the relevant internal changes https://github.com/juxt/crux/issues/1205 😕 sorry about this! Please could you try again with the following and check that you get what you expect

(crux/entity-history (crux/db node)
                       "123"
                       :asc
                       {:with-docs? true
                        :start-tx-time #inst "2022-06-23T00:00:00.000"
                        :start-valid-time #inst "2022-06-23T00:00:00.000"}
                        :end-tx-time #inst "2022-06-23T00:00:00.000"
                        :end-valid-time #inst "2022-06-23T00:00:00.000"}})

bocaj18:06:13

Maybe a better question: I’d like to get a “slice” of the db, most often as something like all transaction over the last 2 days.

refset21:06:30

Hopefully the approach already discussed can help with this, but I'm curious - what do you need this slice for? Is it for exporting to another system?

bocaj21:06:08

Yeah, right on. Exporting the doc + content hash and tx time to a sql server

👌 2

2021-06-22

Channels