Semi/automatic escaping for reserved words as tables or columns

[NPCRUS]

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]

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]

@NPCRUS is this fixed by #57?

[NPCRUS]

@lihaoyi nope, in that one custom schema were applied to different query parts, this one is about escaping

[NPCRUS]

@lihaoyi if the way i describe it should be fixed doesn't contradict any of your ideas for this lib - let me know and i will give it a go

[lihaoyi]

@NPCRUS I think what you say sounds reasonable. Feel free to open a PR and we can see what others think

[lihaoyi]

Maybe we could use a blacklist of reserved words, to avoid escaping in the common case? That would minimize the amount of changed SQL

[ckipp01]

Current workaround is to set explicit schema name on table like this:

override def schemaName: String = "schemaName"

One thing to note with this workaround because I just hit on it, this works fine for SELECTs, but if you attempt to do an update this seems to not work. For example it will create sql that looks like this:

UPDATE public.user SET something = ? WHERE (user.someid = ?)

where the first use of user is created with public.user so there is no issue, but it will fail on the WHERE clause since it generates user.whatever instead of public.user.whatever.