Please use `$$` for comments in DDL instead of `'`

Question

Please use `$$` for comments in DDL instead of `'`

Closed this issue 3 years ago · 10 comments

Currently, in the forward engineered DDL, the normal SQL literal character ' is used, but that creates invalid SQL if the comment contains a quote or any newlines. Using $$ avoids this problem.
https://docs.snowflake.com/en/sql-reference/data-types-text.html#dollar-quoted-string-constants

If this won't work, at the very least please escape single quotes.

Answer 1 · 2021-10-26T09:21:39.000Z

John, this requires both a plugin enhancement and an application enhancement. They will both be released at the end of the week with v5.2.8

Answer 2 · 2021-10-26T11:15:25.000Z

Awesome! thank you!

Answer 3 · 2021-10-29T20:34:21.000Z

John, please upgrade to v5.2.8 of the application and v0.1.40 of Snowflake.

Answer 4 · 2024-09-04T20:33:35.000Z

Hello @pdesmarets I just installed the latest version of the Snowflake plugin 0.2.7 and it's back to using single quotes for comments

Answer 5 · 2024-09-04T21:19:40.000Z

John,

It is still present! But...

The Classic UI is announced as deprecated by Snowflake; "Snowflake suggests and encourages using Snowsight going forward and that's the reason behind removing the button from Trial accounts, so that users can get familiar with the UI. There is no possibility to restore the Classic UI on accounts in Organizations created after May 30, 2023."

And unfortunately snowsight is not backwards compatible when it comes to character encoding...

We have therefore made it as universal for all of our customer by introducing a choice in the plugin between the old ClassicUI for backward compatibility, and the new Snowsight

And in the CLI, it is documented
--format=< format > where the values for Snowflake are now ["snowSight", "classicUI"] with default to snowsight

This, we believe, is the best of both worlds to accommodate Snowflake's lack of backward compatibility of Snowsight for character encoding.

Best regards,

Pascal

Answer 6 · 2024-09-04T21:33:00.000Z

Ah thank you! I was able to use --format to restore the previous behavior. I'm not at all tracking how Snowsight vs. Classic UI affects forward engineered DDL and the use of $$. My comments include apostrophes and Hackolade does not escape them in "Snowsight" mode so I must do manual edits unless I use "Classic UI". I don't actually use Classic UI in Snowflake itself (honestly, I prefer Desktop apps like DBeaver).

Answer 7 · 2024-09-05T05:57:04.000Z

Hi John,

In our Snowsight forward-engineering we do escape single and double apostrophes:

While in ClassicUI we generate this:

Can you please help me understand what we need to do differently?

Answer 8 · 2024-09-05T14:27:44.000Z

I misread my error message! Yes, it is being escaped, but my JOOQ parser didn't understand it.

My question was more about why this is necessary. The Snowflake engine interprets the DDL regardless of what UI is being used, so why is the UI a factor in the escaping? But I'm unstuck with the --format argument so nothing has to change!

Answer 9 · 2024-09-05T14:37:18.000Z

I don't know anything about JOOQ. What I do know is that we started to get tickets from users who have no access to the ClassicUI (probably because they are more recent customers than the Snowflake deprecation date) so we needed to find a solution for them while allowing others to keep the previous encoding approach. Unfortunately this is all due to Snowflake's lack of consistency between their 2 tools. As a matter of fact they have now suppressed the Community exchange that was admitting that much...

Answer 10 · 2024-09-05T14:42:49.000Z

Boo Snowflake. Thank you for the fast responses and clarification.