Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Semi/automatic escaping for reserved words as tables or columns #53

Open
NPCRUS opened this issue Dec 26, 2024 · 1 comment
Open

Semi/automatic escaping for reserved words as tables or columns #53

NPCRUS opened this issue Dec 26, 2024 · 1 comment

Comments

@NPCRUS
Copy link
Contributor

NPCRUS commented Dec 26, 2024

I have a table called order, which is a reserved word in sql. So the query compiles into something like:

SELECT * FROM order

the desired query(Postgres) would be:

SELECT * from "order"

Current workaround is to set explicit schema name on table like this:
override def schemaName: String = "schemaName"

Obviously the same would apply for columns, so we need mechanism to escape column and table names

@NPCRUS
Copy link
Contributor Author

NPCRUS commented Jan 7, 2025

So my thoughts on this issue:

  1. seems like identifier delimit char is ultimately a " at least in currently supported databases + Microsoft SQL server, if another char for a different db appears, we could do it as a part of Dialect I think, so for now we can hardcode it
  2. what needs to be delimited is reference to tables, columns and schemas(when applied explicitly in query)
  3. it's seems that it's recommended pretty much to 100% escape or not escape at all table/column identifier
  4. there are certain quirks when escaping table/column identifiers we need to keep in mind, eg: in sqlite table names are normally capitalized: USERS, and when you not escape them, sqlite will translate unescaped users -> USERS, however when you escape the table, sqlite will not perform this trick anymore, so it will be "users" -> "users" and therefore table will not be found, so in this case we will need to do capitalizing ourselves or maybe leave it to Config.tableNameMapper. There can be more stuff like this

In the end, I plan to use existing Table.resolve and make the same for Column, lots of resulting sql will be changed. Probably quite tedious job, but I need it in my project, so there's no other way

@lihaoyi please let me know if I forgot anything or maybe if you see it differently, otherwise I would like to start with implementation

lihaoyi pushed a commit that referenced this issue Jan 8, 2025
Hello folks, here I tried to add support for `schemaName` into `INSERT`,
`UPDATE`, `DELETE` and some sub variants of those. I feel 90% confused
about most of the stuff I see in the repo so far, but I wanted to start
somewhere, so I can gradually get a better understanding of what's going
on.

Here I implemented a `Table.resolve` function that should make a fully
qualified table name + schema if present + apply mapping from config,
maybe it can also be used further for #53
I also thought it's a good idea whenever `Table.Base` becomes `String`
to be used as a fully qualified name to not do any further processing
and mapping of this string
 Fixes #54

---------

Co-authored-by: nikitaglushchenko <[email protected]>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant