honeysql

Discussion of https://github.com/seancorfield/honeysql :slightly_smiling_face:
plexus 2020-10-15T07:18:56.178500Z

what are people's opinion on the helpers vs literal syntax? when I first used honeysql I used the helpers a lot and didn't pay too close attention to the data structure, but these days I rarely use the helpers, except when I want to modify pre-existing queries

sandqvist 2020-10-15T07:30:07.178600Z

I only use the helpers. We compose queries quite a bit and think it looks nicer.

sandqvist 2020-10-15T07:32:07.178700Z

The fact that they are generated so Cursive cannot autocomplete them is a daily annoyance.

orestis 2020-10-15T08:55:16.179300Z

I used the data structure to start with, and after getting it wrong one-time-too-many I switched to the helpers instead.

orestis 2020-10-15T08:55:40.179900Z

The errors you get if get the data structure wrong are super unhelpful, which is something I look forward in v2 -> having specs

2020-10-15T11:47:20.181200Z

I prefer to start with a map and use the merge-join, merge-select, etc. helpers. It keeps me familiar with what the data structure should look like for debugging, and I find the merge-* helpers convenient.

littleli 2020-10-15T13:36:09.183500Z

@seancorfield Is there any chance honeysql could be used with something non-jdbc based like BigQuery in the future?

seancorfield 2020-10-15T16:26:16.184600Z

@ales.najmann Can you provide more context/detail? HoneySQL just transforms data structures into SQL and parameters -- it has no connection to JDBC other than "SQL".

seancorfield 2020-10-15T16:29:07.188Z

@codonnell The merge-* helpers are all gone in v2 -- the non-merge versions "do the right thing" now. @plexus We pretty much only use helpers at work, rather than the bare data structure. @sandqvist The helpers are actual functions now in v2, not generated via macros. @orestis I'm planning to work on Specs for all of the built-in DSL clauses, but it's a) a lot more work than I realized and b) may not be as helpful as you perhaps think since they are still, in general, very general data structures.

littleli 2020-10-15T16:37:39.188100Z

It's great actually. I'm sorry I got it wrong. I was thinking it is related only to JDBC. Probably because like 100 % of examples etc show use with traditional dbs. BigQuery is build around jobs although that's to large degree just an implementation detail. Also BigQuery while it's compliant with SQL 2011 (or 13, not sure), there is a plenty of custom SQL construct like:

SELECT * EXCEPT (col1, col5) 
WHERE...
or SAFE. function prefix (to prevent exceptions)
SELECT
  SAFE.DIV(null, null) AS case1,
  SAFE.DIV(1, 0) AS case2
There is probably a lot more, but I don't know it all. I wonder if I can somehow help with development of such dialect?

orestis 2020-10-15T16:51:30.189600Z

I donโ€™t care about specs per se - mainly validation and sanity about what should be a vector vs a vector of vectors etc.

orestis 2020-10-15T16:52:05.190400Z

Documentation and simplifying could go a long way. I wouldnโ€™t mind a more verbose and less ambiguous format.

seancorfield 2020-10-15T17:00:15.190500Z

Can you open an issue on the repo with link(s) to the BigQuery docs?

seancorfield 2020-10-15T17:01:58.190700Z

That second example is already possible:

user=> (-> (select [[:safe.div nil nil] :case1] [[:safe.div 1 0] :case2]) (h/format))
["SELECT SAFE.DIV(NULL, NULL) AS case1, SAFE.DIV(?, ?) AS case2" 1 0]
user=> 

๐Ÿ‘ 1
seancorfield 2020-10-15T17:02:36.190900Z

For EXCEPT, I'd need to add a new clause, but that would be pretty straightforward.

seancorfield 2020-10-15T17:05:52.193500Z

I'm mostly trying hard to preserve the data DSL "as-is" although I've made a few small changes (e.g., allowing an alias to be omitted in select when calling a function -- because that was much uglier in v1). I'm simplifying the helpers, removing macros where possible, making extension easier. It's hard work, and there are definitely things that v1 does that are currently very hard to implement in v2 with the approach I've been taking so far ๐Ÿ˜

sandqvist 2020-10-15T17:21:50.193600Z

I think you are fixing my main annoyances with v1. Thank you for your work on this and next.jdbc.

littleli 2020-10-15T17:51:41.193900Z

Also quoting is different in BigQuery. They use backticks, but a bit different than it is in MySQL. Example:

SELECT
  COUNT(*) AS count
FROM
  `bigquery-public-data.covid19_symptom_search.counties_weekly_2020
`

littleli 2020-10-15T17:52:47.194100Z

I'm going to fill these requests. Should I do it in separate?

seancorfield 2020-10-15T17:53:21.194300Z

Different in that it doesn't split names on .? Does it still work in BigQuery if you do this:

`bigquery-public-data`.`covid19_symptom_search`.`counties_weekly_2020
`

โœ”๏ธ 1
seancorfield 2020-10-15T17:53:28.194500Z

It can all be one ticket.

๐Ÿ‘ 1
seancorfield 2020-10-15T17:54:18.194700Z

I'll create a :bigquery dialect that adds any BigQuery-specific clauses and adjusts any specific behaviors in other parts of the SQL generation.

littleli 2020-10-15T17:59:57.195100Z

It works too, although it's not how BigQuery console sends query to 'Query editor' click 'Query table' button. Not sure if it's controversial or anything.

seancorfield 2020-10-15T18:14:20.195300Z

I think v1 has an option to not split dotted names but I don't want to replicate that really -- so if quoting each segment works in BigQuery I'd prefer to leave it that way. I'm trying to get the SQL generator as small and simple as possible (but it's already a bit gnarly in a few places ๐Ÿ˜ž )

littleli 2020-10-15T23:19:15.195500Z

I'm sorry @seancorfield I filled quite general issue here https://github.com/seancorfield/honeysql/issues/281 because I don't think SELECT * EXCEPT is that important, but at the same time there is a lot of other important properties of BigQuery dialect. I can understand if you want to pass, but at the same time I think it can with design in v2 (maybe)

seancorfield 2020-10-15T23:29:06.195800Z

Thank you!