Clojurians Log v2

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

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

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

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

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

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.

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

@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".

@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.

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

SELECT
  SAFE.DIV(null, null) AS case1,
  SAFE.DIV(1, 0) AS case2

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

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

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

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

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

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 😐

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

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

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

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

It can all be one ticket.

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

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.

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 😞 )

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)

Thank you!