First file is the script i am trying to execute

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

relevant dependencies

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

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

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

@emccue ^

yep the second regex worked

why was a newline breaking it?

that doesnt make all that much sense to me

well, whatever

it works now

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

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

I know how to get a flat row structure

joins seem to do that

but i want to group the joined things together

so its in the format i would work with in code

...if that makes sense?

The newline broke things because you ended up calling

(jdbc/db-do-commands db "
")

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

like

-- :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

that sql

it will give me the report

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

and for every file pointer it will have a start line

what i want is

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

reduce the result set.

kinda re-de-normalize it

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

thats sad

Why?

SQL is about flat data structures: tables with rows.

in storage sure that makes sense

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

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

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

Flat rows is pretty much the definition of relational algebra.

so how do ORMs do stuff like this?

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

orms seem to "magic" over it

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

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

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

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.

(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"

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)

Libraries like pathom / walkable try and help fill that gap

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

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

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

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

thats kinda the thing with nosql

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

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

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