Clojurians Log v2

Hey @farcaller the trick is joining on _system_from https://play.xtdb.com/?version=2.0.0-beta3&type=sql&txs=W3sic3lzdGVtLXRpbWUiOm51bGwsInR4cyI6IklOU0VSVCBJTlRPIGRvY3MgKF9pZCwgY29sMSkgVkFMVUVTICgxLCAnZm9vJyk7In0seyJ0eHMiOiJJTlNFUlQgSU5UTyBkb2NzIChfaWQsIGNvbDEpIFZBTFVFUyAoMiwgJ2JhcicpOyIsInN5c3RlbS10aW1lIjpudWxsfSx7InR4cyI6IklOU0VSVCBJTlRPIGRvY3MgKF9pZCwgY29sMSkgVkFMVUVTICgzLCAnYmF6Jyk7Iiwic3lzdGVtLXRpbWUiOm51bGx9XQ%3D%3D&query=U0VMRUNUIGRvY3MuX2lkLCBkb2NzLl9zeXN0ZW1fZnJvbSwgeHQudHhzLl9pZCBBUyB0eF9pZCBGUk9NIGRvY3MgSk9JTiB4dC50eHMgT04geHQudHhzLnN5c3RlbV90aW1lID0gZG9jcy5fc3lzdGVtX2Zyb20%3D

ah, I see. So the _system_from is effectively the long-form transaction id and it's unique. Is there anything like xt/open-tx-log to see what else happened during that transaction?

Yes _system_from should always be unique, and we even now disallow transactions happening in the same microsecond (...but that's not relevant for quite some time unless hardware improves dramatically 🙂) Discovering changes across all tables isn't easy right now - you have to enumerate all the tables (e.g. by querying information_schema.tables) and do a UNION ALL while joining on the _system_from. I would really like to make this simpler but we haven't discussed potential designs in much depth yet. If you need those lookups to be fast though then you may want to consider maintaining your own metadata of tables have been changed by the current transaction (stored in some user-defined table or column, similar to https://discuss.xtdb.com/t/corrections-table-pattern-for-tracking-changes/467). What's the requirement you're hoping to solve?

I'm just tinkering for now. The conceptual problem I'm solving is "what were the transaction submitted between A and B and which records they affected?" I have somewhat a "blockchain" proof of concept (a bastardized storage solution that removes all the insanity of a distributed consensus and basically turns it into a transaction log and a computed state). I don't like it and I really want to get rid of all that and looking for options. Insofar v1's xt/open-tx-log does almost what I want (sans tracking the failed transactions which is something I can work around with functions, I think). datomic's log api also seems to do something very similar—I'm not fully sold yet on if I need actual bitemporality for my case as I just need to know "where we are now" and "how we got here".

Thanks for the added context - always useful! Is it a very routine / 'hot path' query to be running in your application? Does it need to be fast? Or just possible?

"just possible" at the moment

and yeah, I know that it's a broad answer. I could do "just possible" with sqlite and lots of code 🙂 I'd prefer the db layer to do it for me, though

Bitemporality is particularly useful when the "transactions submitted" and "records affected" are happening in upstream systems, rather than the one that XTDB is underpinning

ah, that actually makes more sense when you put it like that!

okay, how about this: https://play.xtdb.com/?version=2.0.0-beta3&type=sql&txs=W3sic3lzdGVtLXRpbWUiOm51bGwsInR4cyI6IklOU0VSVCBJTlRPIGRvY3MgKF9pZCwgY29sMSkgVkFMVUVTICgxLCAnZm9vJyk7XG5JTlNFUlQgSU5UTyBkb2NzMiAoX2lkLCBjb2wxKSBWQUxVRVMgKDIsICdiYXInKTtcbklOU0VSVCBJTlRPIGRvY3MzIChfaWQsIGNvbDEpIFZBTFVFUyAoMywgJ2JheicpO1xuSU5TRVJUIElOVE8gZG9jcyAoX2lkLCBjb2wxKSBWQUxVRVMgKDQsICdmb28yJyk7In1d&query=V0lUSCB0eHNfZmlsdGVyZWQgQVMgKAogICAgU0VMRUNUIF9zeXN0ZW1fZnJvbQogICAgRlJPTSB4dC50eHMKICAgIFdIRVJFIF9pZCA9IDAKKQpTRUxFQ1QKICAgIE5FU1RfTUFOWShTRUxFQ1QgKiBGUk9NIGRvY3MgV0hFUkUgZG9jcy5fc3lzdGVtX2Zyb20gSU4gKFNFTEVDVCBfc3lzdGVtX2Zyb20gRlJPTSB0eHNfZmlsdGVyZWQpKSBBUyByb3dzX2NoYW5nZWQsCiAgICAnZG9jcycgQVMgdGFibGVfbmFtZQpVTklPTiBBTEwKU0VMRUNUCiAgICBORVNUX01BTlkoU0VMRUNUICogRlJPTSBkb2NzMiBXSEVSRSBkb2NzMi5fc3lzdGVtX2Zyb20gSU4gKFNFTEVDVCBfc3lzdGVtX2Zyb20gRlJPTSB0eHNfZmlsdGVyZWQpKSBBUyByb3dzX2NoYW5nZWQsCiAgICAnZG9jczInIEFTIHRhYmxlX25hbWUKVU5JT04gQUxMClNFTEVDVAogICAgTkVTVF9NQU5ZKFNFTEVDVCAqIEZST00gZG9jczMgV0hFUkUgZG9jczMuX3N5c3RlbV9mcm9tIElOIChTRUxFQ1QgX3N5c3RlbV9mcm9tIEZST00gdHhzX2ZpbHRlcmVkKSkgQVMgcm93c19jaGFuZ2VkLAogICAgJ2RvY3MzJyBBUyB0YWJsZV9uYW1lOw%3D%3D

Incidentally there was a https://discuss.xtdb.com/t/relate-given-transaction-to-affected-documents/410/4 thread roughly about this already (by @stefcoetzee, no less), so I just updated that with this example too 🙂

A document history in XTQL focused on one table (`store`):

(defn doc-history
  "History of document with `:xt/id` of `xt-id` from `store`,
  in descending order from most recent to oldest version."
  [store xt-id]
  (mapv #(into (sorted-map) %)
        (pt/q (xt/template
               (-> (from ~store
                         {:bind [{:xt/id ~xt-id}
                                 * xt/valid-from xt/system-from]
                          :for-valid-time :all-time})
                   (order-by {:val xt/valid-from
                              :dir :desc}))))))

Should add, where pt/q is defined as:

(defn q [query & opts]
  (with-open [node (xtc/start-client http-node-url)]
    (xt/q node query opts)))