Fork me on GitHub
#sql
<
2019-09-25
>
gklijs06:09:49

I'm refactoring some code, moving more of the logic to SQL, doing things with one execution. Typically relaxing a find and update, with a custom update. Using just jdbc.next now for SQL. There are now several long SQL strings in the code I would like to get rid of. Would hugsql be the easiest way, or is there something lighter that would work? I read (for clojure jdbc) just slurping a .sql file would not work?

seancorfield15:09:32

HugSQL 0.5.1 has a builtin adapter for next.jdbc so that's probably the way I'd go if you are happy to stay with raw SQL.

Ben Hammond14:09:14

I'm looking at a bit of code that says

(jdbc/with-db-transaction [tx db]
  (do-this-thing tx)
  (do-that-thing tx)
  (do-tother-thing tx))
and I've refactored it into
(jdbc/with-db-transaction [tx db]
  {:this-thing (do-this-thing tx)
   :that-thing (do-that-thing tx)
   :tother-thing (do-tother-thing tx)})
because it is more convenient to work with from the repl. I'm pretty sure that returning in a map will not affect the sequence in which the functions get called but now I've developed a nagging doubt, and I'm not absolutely certain that it will never affect the sequencing of the (do-thing calls I mean the sub expressions are always evaluated first, right and presumably evaluated in order (maybe I've been reading too much Java Memory Model, and it's addled my brains)

Ben Hammond14:09:40

can any of you reassure me?

seancorfield15:09:13

I can't reassure you. I'd probably use let here. @bronsa might know for sure?

jaihindhreddy15:09:02

http://clojure.org/reference/evaluation says:

Vector elements are evaluated left to right, Sets and Maps are evaluated in an undefined order. The same is true of metadata maps.

πŸ‘ 8
jaihindhreddy15:09:34

So relying on that behavior is a bad idea (regardless of whether it behaves that way rn)

jaihindhreddy15:09:05

Might as well us a let if you want fixed evaluation order. Or a zipmap because vectors are evaluated left-to-right as well.

noisesmith17:09:40

into on a vector of vectors would be the thing that looks closest to the original form while preserving order

noisesmith17:09:18

(into {}
  [[:this-thing (do-this-thing tx)]
   [... ...]
   ...])

seancorfield15:09:13
replied to a thread:can any of you reassure me?

I can't reassure you. I'd probably use let here. @bronsa might know for sure?

bronsa15:09:02

yeah ordering of evaluation in literal maps is not guaranteed

bronsa15:09:02

user=> {1 (println 1) 2 (println 2) 3 (println 3) 4 (println 4) 5 (println 5) 6 (println 6) 7 (println 7) 8 (println 8) 9 (println 9)}
7
1
4
6
3
2
9
5
8
{7 nil, 1 nil, 4 nil, 6 nil, 3 nil, 2 nil, 9 nil, 5 nil, 8 nil}

ghadi15:09:18

@bronsa which repl are you using?

Ben Hammond15:09:14

oh I have a corollory to that

Ben Hammond15:09:23

(class {1 (println 1) 2 (println 2) 3 (println 3)})
1
2
3
=> clojure.lang.PersistentArrayMap

Ben Hammond15:09:49

If I stay in persistent arraymap territtory then its okay for small maps

Ben Hammond15:09:53

but a timebomb for Those Who Come After Me

bronsa15:09:02

yeah, maps preserve insertion order up to the first 8 elements, but that's an implementation detail

bronsa15:09:14

and there's no guarantee anywhere that the reader will read a literal map as a PAM

bronsa15:09:19

so don't do that

πŸ‘ 4
ghadi15:09:21

maps don’t have order. relying on it is wrong

bronsa15:09:18

if you're really trying to avoid a let, switching from {..} to (hash-map ..) will preserve evaluation order

βž• 4
Ben Hammond15:09:42

?? really ?? I'm confused?

Ben Hammond15:09:09

oh right I see

Ben Hammond15:09:20

that is subtle_

Ben Hammond15:09:07

yeah I prefer to avoid subtlety

Ben Hammond15:09:25

> make your code as vulgar as possible

Ben Hammond15:09:23

(let has got to be the least surprising thing to do

βž• 12
noisesmith17:09:00

I don't find the behavior of the hash-map function subtle or odd - all clojure functions evaluate args left to right, and {} literals are not functions and don't have that guarantee

Charles Fourdrignier19:09:58

My question is very generic but relates on Clojure and SQL. I can't find a better place to ask it. In this presentation (https://www.youtube.com/watch?v=oOON--g1PyU&amp;t=1659s), Stuart Halloway describes his one-shot ETL PostgreSQL to Datomic. For the extractor part, he explains that he generates UUIDs instead of using auto-increment identifiers. At first glance it seems easy, but I trying to apply this idea in my day job, I can't see a good strategy to manage join tables. First, he store extracted entities into edn files. But to find a previously generated id, you must to search by "old-id". What's the best ? Store the uuid in the PostgreSQL (as a source should be read only), store old-id to new-id relation in hashmap ? in in-memory database ? Second, you need to process in the right order (users before comments before articles). May be you'll need to do multiple pass (one user has another one as sponsors). Did I miss something one this point ? Is there some magic idea I didn't see ?

seancorfield19:09:40

Since it was a one-shot ETL, I suspect he built it to depend on key order chains -- and he's glossing over a lot of the complexity in the presentation, I'm sure.

shaun-mahood20:09:54

@charles.fourdrignier This isn't exactly the answer, but I've got something in place that was at least inspired by that presentation. It consists of a local SQL database that is still running and in use, and a Datomic cloud database that is the data source for all new functionality. There is a set of common information that is copied from the SQL database to the Datomic database, but never from Datomic to SQL - the expectation is that this will be the case for multiple years. The SQL database has a bunch of join tables that form the composite keys that uniquely identify different properties. What I ended up doing was storing the IDs for everything in datomic as :table/legacy-id, and built an ETL job that first identified all the top level properties (Municipalities in this case) and copied those over to Datomic. Since the I could reference :municipality/legacy-id from both databases, I could then build the next import and run that, and so on until everything was there (each table as a separate import job). In order to keep the Datomic database current with the SQL database, if the expected data doesn't exist in Datomic then my application will check the SQL database and import that one set of missing info in a single batch. It starts with the child of the join level of detail, then queries for the existence of the parent table in Datomic - if it doesn't exist either, then it follows the same import behaviour all the way up the chain until it reaches the root or finds something in Datomic, then goes back down the chain importing each parent in order (each row as a separate import job).

Charles Fourdrignier20:09:08

Thank you for this detailed answer. A good point to start !

shaun-mahood20:09:40

Glad it might help - I was thinking it was getting a little long and rambling. πŸ™‚ If you run into any more questions feel free to reach out.

seancorfield20:09:26

Yeah, I have to wonder if Stu built the extractor incrementally like that too...? We've done similar migrations but mostly within Percona/MySQL, and we've generally migrated some data up front and then built incremental migrations on top of that, mostly working "on-demand".

seancorfield20:09:15

We also migrated a lot of stuff from MySQL to MongoDB the same way -- and later back from MongoDB to Percona/MySQL once we decided to abandon MongoDB. I'd forgotten about that until Shaun's long message reminded me πŸ™‚

shaun-mahood20:09:11

If I were doing it again I would probably only build the incremental extractor, and maybe build in a multimethod to tell it "don't worry about checking or creating the parent" to use when bulk-loading. I mostly did it the other way so that I could think about a single level of data modeling at a time since this was my first Datomic project that actually matters to other people.