Fork me on GitHub
#sql
<
2019-05-11
>
emccue17:05:55

First file is the script i am trying to execute

emccue17:05:51

[org.clojure/clojure "1.10.0"]
[org.clojure/java.jdbc "0.7.9"]
[org.xerial/sqlite-jdbc "3.27.2.1"]

emccue17:05:03

relevant dependencies

seancorfield18:05:54

OK, it's the newline leftover at the end.

seancorfield18:05:41

Hmm, maybe not. Let me try something in the REPL

seancorfield18:05:51

Yeah, it's the newline. Split on #";.*\n" or #";\s*\n"

emccue18:05:05

yep the second regex worked

emccue18:05:33

why was a newline breaking it?

emccue18:05:25

that doesnt make all that much sense to me

emccue19:05:31

well, whatever

emccue19:05:34

it works now

emccue19:05:50

another question that falls more in the realm of just general sql stuff

emccue19:05:43

how can i write a query that would give me (with those tables above) info about a report with info about all of the file pointers as a list

emccue19:05:53

I know how to get a flat row structure

emccue19:05:02

joins seem to do that

emccue19:05:12

but i want to group the joined things together

emccue19:05:31

so its in the format i would work with in code

emccue19:05:39

...if that makes sense?

seancorfield19:05:33

The newline broke things because you ended up calling

(jdbc/db-do-commands db "
")
and that's an illegal SQL statement -- SQLite's error is less than helpful.

seancorfield19:05:25

@emccue I don't understand your question about the row structure

emccue19:05:18

-- :name get-all-reports
-- :command :query
-- :result :many
SELECT r.id,
       r.blob,
       r.explanation,
       fp.start_line
FROM REPORTS r
       LEFT JOIN FILE_POINTERS fp
                  ON r.id == fp.report

emccue19:05:26

it will give me the report

seancorfield19:05:28

SQL is always just going to give you a sequence of rows.

emccue19:05:37

and for every file pointer it will have a start line

emccue19:05:41

what i want is

emccue19:05:13

{:id ... :blob ... :explanation ... :files [ {:start_line ... } ]}

seancorfield19:05:51

reduce the result set.

emccue19:05:05

kinda re-de-normalize it

seancorfield19:05:15

SQL produces a sequence of rows. You want something fancier, do it in Clojure.

seancorfield19:05:11

SQL is about flat data structures: tables with rows.

emccue19:05:26

in storage sure that makes sense

emccue19:05:46

SQL was made to optimize storage space and fit a formal description of relational data

seancorfield19:05:04

No, SQL is a relational query language. It has nothing to do with storage.

emccue19:05:07

but when i write queries it out its wierd that it needs to be flat rows

seancorfield19:05:31

Flat rows is pretty much the definition of relational algebra.

emccue20:05:06

so how do ORMs do stuff like this?

emccue20:05:28

my bigggest gripe with sql has always been that I couldn't represent "many of a thing"

emccue20:05:37

orms seem to "magic" over it

emccue20:05:58

which made me think that there might be some sql query i could do to manifest "many of a thing"

dominicm20:05:23

The easiest answer to that is to turn on verbose mode in an. Orm

dominicm20:05:21

I'm not aware of any that document their internals well, I think they just do many queries or the reduce, depending on optimizations

dominicm20:05:59

You might be able to do something clever using the json features in postgres, I think a colleague did so, but can't recall the details.

emccue20:05:18

(part of this is coming from a "wow it would be really cool to not have to figure out all my access patterns for mongo/dynamo to support a graphql schema"

lilactown21:05:07

There always going to have to be some domain-specific glue code when going from a table-centric view (sql) to a graph view (graphql)

lilactown21:05:57

Libraries like pathom / walkable try and help fill that gap

dominicm21:05:55

We did a lot of work to get postgres to return the whole gql query in one, I believe it used the json features.

emccue22:05:33

not there yet, but i would love to read a blog post on that

didibus22:05:12

Mongo/Dynamo don't use SQL, and aren't relational though.

emccue22:05:43

yes, but they require alot of upfront design to optimize for your access patterns

emccue22:05:04

thats kinda the thing with nosql

didibus22:05:11

What ORMs do is exactly what @seancorfield mentioned. They post-process the resultset by reducing over it and transforming it into an Object hierarchy.

didibus22:05:07

Yeah, because document stores are designed for horizontal scale out. In theory, you could build them to be optimal on many queries, but no one build vertically scaling DBs anymore

emccue22:05:25

yeah - i am still very much on the early part of the learning curve