Fork me on GitHub
#sql
<
2020-08-26
>
practicalli-johnny13:08:44

I am assuming that using Migratus or Ragtime is a more common approach to creating multiple tables in a database, rather than using a 'builder' style function (i.e. a builder function creates a table from a schema passed as an argument). I only have a few tables, so was just planning to create a separate function to create each table to start with, until I adopt Migratus (probably). Does that make sense?

synthomat14:08:01

actually both projects felt quite lightweight to me so far; maybe there’s less effort to adopt to that than you might think

dpsutton14:08:49

we use migratus and it is quite pleasant to work with

lukasz14:08:44

+1 on ragtime (migratus looks v similar). Managing table structure without proper migrations (event if they are lightweight) very quickly becomes a pain - I'm actually migrating 😉 one project from this approach

dpsutton14:08:57

migratus uses timestamps for migration names rather than integers so it is far easier to merge branches which each have migrations. i've never used ragtime so can't speak to it but migratus has been rock solid for us

lukasz14:08:51

I think in ragtime the migration IDs just have to be ordered - we have a small shell script which generates up/down files and uses unix timestamp as the ID (just like Rails does)

Jan Winkler17:08:10

hi. I'm playing with jdbc-next. I can qualify a column with a namespace using as, but I can't read it in the reduction:

(into #{}
      (map (juxt keys
                 (comp namespace first keys)
                 (comp name first keys)
                 :qualified/column))
      (jdbc/plan ds ["select 'foo' as \"qualified/column\""]))
=> #{[(:qualified/column) "qualified" "column" nil]}
I suspect sneaking in the namespace the way I did is silly, and probably works only by accident. am I right or should this (somehow) work?

seancorfield18:08:13

@jan.wnklr SQL doesn't know about namespace-qualification so you've created a column label that is "qualified/column" -- if you use (get row "qualified/column") it would probably work. But, basically, don't do that 🙂

Jan Winkler18:08:41

I've tried that too, still getting nil. but I'd feel filthy if it worked. 🙂

seancorfield18:08:27

Also, plan is working with the raw result set, not a hash map, so it is expected that you use simple (unqualified) keywords for column access.

seancorfield18:08:55

Although you're using keys which realizes the result set as a hash map so you're getting a weird combination of data.

Jan Winkler18:08:54

interestingly, using qualified kws for columns that come from a table works

seancorfield18:08:44

user=> (into #{} (map #(get % "qualified/column")) (jdbc/plan ds ["select 'foo' as `qualified/column`"]))
#{"foo"}

seancorfield18:08:59

(that's with MySQL hence the backtick quoting)

seancorfield18:08:43

If you use a qualified keyword in lookup access when you reduce-over-plan, the code only uses the name portion.

seancorfield18:08:20

user=> (into #{} (map :ignore/qualified/column) (jdbc/plan ds ["select 'foo' as `qualified/column`"]))
#{"foo"}

Jan Winkler18:08:02

oh, it works now, I must have brainfarted somewhere

seancorfield18:08:30

Since you are using keys, and realizing the result set, you are losing all the performance benefit of plan

seancorfield18:08:56

(although you can still use it to stream very large result sets, even if you realize each row)

Jan Winkler18:08:57

my intention, originally, was to convert a string column value to keyword using (map #(update % :that/column keyword)). would that also result in row realization?

Jan Winkler18:08:32

how wouldn't it, right? thanks, I needed a little kick. 🙂

seancorfield18:08:26

Compare to this (where there is a table name to do the qualification):

user=> (into #{} (map #(update % :status/name keyword)) (jdbc/plan ds ["select id,name from status"]))
#{#:status{:id 3, :name :rejected} #:status{:id 2, :name :new} #:status{:id 1, :name :approved}}
user=> (into #{} (map (comp keyword :name)) (jdbc/plan ds ["select id,name from status"]))
#{:rejected :new :approved}
user=> 
In the second case, we are not realizing a row -- we're just selecting the name column and we can use an unqualified keyword; in the first case we are realizing the row (because update needs a hash map) so we must use a qualified keyword.

seancorfield18:08:37

But in that second case, we could use a qualified keyword -- the qualifier is simply ignored:

user=> (into #{} (map (comp keyword :status/name)) (jdbc/plan ds ["select id,name from status"]))
#{:rejected :new :approved}
user=> (into #{} (map (comp keyword :this-part-is-ignored/name)) (jdbc/plan ds ["select id,name from status"]))
#{:rejected :new :approved}

seancorfield18:08:50

I hope that clarifies the behavior @jan.wnklr?

Jan Winkler18:08:01

yup, I get it now. this was very helpful and incredibly quick response. thank you.