Clojurians Log v2
honeysql
I'm working with a sqlite db where the table and column names have "-" in the name (which is my own fault). For the most part, I can use :quoted and it works, but referencing columns with :a-table/a-column doesn't seem to work as expected:
(sql/format
{:select [:a-table/a-column]
:from :a-table}
{:quoted true})
;; ["SELECT \"a_table\".\"a-column\" FROM \"a-table\""]:a-table is quoted the way I want in the :from clause, but is converted to snake case in the :select clause. This behavior seems to be consistent for other clauses like :left-join and :where. I'm not sure if this is intended or if I'm just doing something goofy. If this isn't intended, I can create an issue on github if that's helpful.
Thanks!
:foo/bar and :foo.bar have slightly different semantics (deliberately) but you can do:
user=> (sql/format
{:select [:a-table.a-column]
:from :a-table}
{:quoted true})
["SELECT \"a-table\".\"a-column\" FROM \"a-table\""]Awesome! That's great.
Out of curiousity, what is the difference?
Note that there is also this option:
user=> (sql/format
{:select [:a-table/a-column]
:from :a-table}
{:quoted true :quoted-snake true})
["SELECT \"a_table\".\"a_column\" FROM \"a_table\""]In my particular case, I'm trying to avoid snakecase.
It's somewhat historical. Hyphens in column names seemed common enough to make that work for quoting "by default" with :quoted-snake to reverse that, but hyphens in table names were uncommon so the dotted form has one behavior and the / form has another.
ah ok. I was under the impression that they were interchangeable. I see that is mentioned in the General Reference.
The joys of having so many users with different "preferences" in how they set up their databases... unfortunately 😞
and there are also a dozen sql dialects with random variations and requirements
🎉
I was recently trying h2 for the first time and their version of SQL has several interesting changes that seem arbitrary.
Yeah, and then the behavior of each DB's JDBC driver is different in various ways too.
The jTDS and MS SQL Server drivers behave differently, even when connected to the same SQL Server instance. MariaDB's driver is different to MySQL's -- again, even connected to the same DB. Oracle's driver doesn't implement some things, MS's doesn't implement some other things, PG's issues additional SQL queries behind the scenes (that other drivers do not).
I'm doing some generic query writing. Is there a way to programmatically construct a fully qualified column name given a table and a column? Obviously, I can use string manipulation to construct a keyword in the form :table.col, but I was wondering if there was already a utility or idiom for this.
https://cljdoc.org/d/com.github.seancorfield/honeysql/2.6.1203/doc/getting-started/sql-special-syntax-#dot- sounds like what you want?
Yep, thanks!