Fork me on GitHub
#xtdb
<
2022-04-08
>
genekim04:04:45

Public Service Announcement: when using XTDB (and really, probably all databases), set your JVM timezone to UTC. I’ve resisted doing this, but something happened today that has convinced me that doing otherwise is pretty stupid. I was on a flight and on the Gogo in-flight wifi, crossing timezones. Several times, my transactions weren’t showing up — because macOS was (incredibly) updating my laptop time zones as I was flying from east to west. (!!!) Funny. But man, timezones are tricky!!!! (Was thinking of you as this happened, @seancorfield, as I think it was your UTC advice I was disregarding!! 😆

7
harold 3
thinking-face 2
👍 1
😂 1
seancorfield05:04:26

Dates and times are hard... timezones doubly so... 🙂

seancorfield05:04:19

We have all our servers set to UTC, and our databases, and our JVMs, and we also add serverTimezone=UTC to the JDBC URL just to be sure when using connection pooling software 🙂

2
Hukka05:04:58

JDBC treats times as local, ignoring the tz?!

jarohen08:04:08

yep, has always kept us on our toes 🙂 most recently https://github.com/xtdb/xtdb/issues/1716 - thanks @genekim for the report 🙏

jarohen08:04:36

for XT, setting serverTimezone looks like this:

{:xtdb.jdbc/connection-pool {:dialect 'xtdb.jdbc.mysql/->dialect
                             :db-spec {:dbname "xtdbtest", :user "root", :password "my-secret-pw"
                                       :serverTimezone "UTC"}}}

jarohen08:04:49

> JDBC treats times as local, ignoring the tz?! unfortunately I don't think it's consistent between different JDBC drivers (well, at least, if it is consistent, I don't understand its rules...)

refset10:04:34

Thank you for the experience reports - I'm going to add Sean's advice to the docs ✔️

🙌 1
richiardiandrea15:04:29

does serverTimezone works on any "dialect" - aka, is it a driver setting (we are using JDBC)?

seancorfield18:04:01

I suspect it may be MySQL-specific based on a quick Bing search...

❤️ 1
richiardiandrea20:04:16

Thank you Sean, will google the JDBC one

genekim16:04:22

Thank you so much to @mitesh for helping me figure out how to join two entities together. At the risk of putting this out there without any context, maybe this will save some tears for someone trying to do something similar:

; rec is the zoom recording record
; meeting is the zoom meeting record (which is where you can find the meeting participants

; challenge is how to "join" them together -- this is what we came up with

(let [[rec mtg] (->> (xt/q (xt/db db/xtdb-node)
                         '{:find                           
                           [(pull re [*])
                            (pull m  [*])]    ; <--- the amazing trick
                           :in    [id]
                           :where [[re :type :zoom-recording]
                                   [re :uuid id]
                                   [re :uuid meeting-uuid]
                                   [m :type :zoom-meeting]
                                   [m :uuid meeting-uuid]
                                   [m :participants participants]]}
                         "hH+aQL9jdddddddIJryOgFA==")
                       first)]
    (assoc rec :meeting mtg))
Is there a better way to do this? (The two records are joined by the :uuid field.)

1
💚 1
📝 1
genekim16:04:36

Is there a way to do this using one pull? I simply could not figure out how to pull in the Zoom meeting map into the Zoom recording map. Thank you!

Hukka16:04:24

I guess not, since they are not really pointing to each other, but to some thing that is not an entity in itself

Hukka16:04:46

If meeting-uuid were an entity, I think reverse direction navigation would work

genekim16:04:14

Thank you for your response — I’m totally trying to understand this. So m is an entity…. Oh…. Are you suggesting I should “patch” all the recording entities, and assoc recording :meeting-entity m? And then it could be joined? (Lightbulb moment dawning?)

Hukka16:04:27

Well then definitely yes, but I meant that I assume you cannot put meeting-uuid in the entity position, since there is not document that has xt/id of meeting-uuid

Hukka17:04:52

If you did have that document, you could maybe (pull that-document [{:_uuid [*]}])

Hukka17:04:11

But I'm not sure if reverse navigation and * pulls will work together

Hukka17:04:13

If you had a direct link from re to m, via :meeting-entity, then you could (pull re [{:meeting-entity [*]}])

Hukka17:04:14

I'm a bit puzzled about the query though, does it really need to have :uuid defined to be both id and meeting-uuid?

genekim17:04:50

That is freaking awesome — I totally get it now. I couldn’t quite get my head around why the pull didn’t work, even though I feel like I had done similar things in datomic. Right. They weren’t entities, they were merely strings, so couldn’t be joined. Duh. Thanks again! (Umm, on id vs. uuuid: I was wondering that too. I think in Zoom, id is bound to the user, and uuid is bound to the meeting. In my data, there are six unique ids, and 253 unique uuids, which matches the # of recordings.)

genekim17:04:30

To learn that, it was super great to just explore the data REPL, because frankly there’s no way I could have known that, even after having read docs.

Hukka17:04:31

I mean that in your query, you have id as an :in param. Then you define that re :uuid must be id, but also you give it a logic var name meeting-uuid which you then use with m. Would

:where [[re :type :zoom-recording]
        [re :uuid id]
        [m :type :zoom-meeting]
        [m :uuid id]
        [m :participants participants]]
work?

richiardiandrea20:04:25

wow, didn't know you could have two (pull that's amazing

oxalorg (Mitesh)08:04:41

> since there is not document that has xt/id of meeting-uuid Ah this was the confusion Gene and I hit. I was a bit confused if reverse lookups should work without xt/id, but I guess that makes sense that they don't (Similar to datomics :ref) > wow, didn't know you could have two (pull that's amazing Yeah this was a neat little trick I discovered up a while ago, super cool usage especially if you combine it with :keys So something like:

{:find [(pull e) (pull m)]
 :keys [event meeting]
 ...} 
This will return a map {:event ... :meeting ...}

genekim20:04:48

@U8ZQ1J1RR @mitesh Confirmed! I added a :meeting-entity that has the :xt/id, and the query (pull re [* {:meeting-entity [*]}) indeed worked. Super cool. (The problem is that when I created the zoom-recording entity, that zoom-meeting entity didn't exist yet... So that's how I got into that situation...) Thanks, all!

🙌 1
Hukka05:04:20

Working with Fulcro teaches one to have their entity relationships in strict order 😉

sheluchin17:04:33

@U8ZQ1J1RR It doesn't cover the pull fn specifically. It's using some function in the projection spec like the application of pr-str here, I think...

(xt/pull db (pr-str [:film/name :film/year]) :spectre)

Hukka17:04:43

True, the documentation doesn't really specify what the source says at https://github.com/xtdb/xtdb/blob/f8ac847301212b53988a0a4b239a7a1f645868f0/core/src/xtdb/api/IXtdbDatasource.java#L61: both String and a Clojure structure are ok

sheluchin19:04:39

Thanks @U8ZQ1J1RR. It's not so clear to me how it works or how to use it. I don't really understand it by looking at the https://github.com/xtdb/xtdb/pull/1369/files either.

Hukka07:04:01

Have you read the linked EQL site? I recall seeing you at pathom and fulcro channels too, so the syntax should be pretty much the same

Steven Deobald17:04:14

@genekim @mitesh As always, I'll do a small pester to encourage duplicating any fun discoveries / ideas / conversations to http://discuss.xtdb.com so they're searchable (+editable) for future users. If you have the time. 🙂

🙌 1
Hukka17:04:20

I don't have any fun discoveries, only those that make my head hit the desk 😕

Steven Deobald17:04:57

Painful discoveries also very welcome on http://discuss.xtdb.com. 😉

genekim17:04:32

I know I know! I’ll post the timezone story, as well as a link to Slack. I keep trying, but the problem is that this Slack community is so good and usually so fast, it’s my go to place. 🙂

😅 1
🙏 3
Steven Deobald20:04:13

hehe... maybe we can at least sneak you onto Zulip, as an alternative. 😉 But yeah, it's hard to escape the Slacks. If you post a link to the thread archived on https://clojurians-log.clojureverse.org/xtdb that might be preferable (or maybe in addition to?). There's little guarantee that Slack, Inc. will provide history to Clojurians indefinitely.