How can one check an identifier for unescaped validity in most RDBMSs?

I'd like my application to work on several databases. While I'm not going to explicitly support them right now, I'd like to be able to define my schemata such that I'm not using reserved identifiers. The reason? Identifier delimiters are not standard between databases. For instance, MySQL uses backticks (`) to quote identifiers, while MSSQL uses brackets ([]开发者_如何转开发).

However, I'm not sure what subset of names are "safe". Is there an easy way to check this?

Main Answer

An easy way? I don't think so. So, NO.

A way to do it

The only way is to look up the docs for each supported RDBMS.

The reason

You can always look up the ANSI SQL for reserved words but, at the end, each RDBMS will have its particularities.

A workaround

Let's look at this at a different angle: instead of having your SQL queries avoid reserved names, you can have part of your code escape the columns accordingly to the RDBMS.

Other problems to face

Column naming is not your only problem. Heck, it's not even your main problem!

If you are familiar with GROUP_CONCAT in MySQL you know what I mean. Its alternative in SQL Server is using XML PATH. Firebird has the LIST built-in aggregate function. In the end, you will end up having a different query per RDBMS. I know that because of...

My experience

My "Been there, done that" anecdote: I've worked in a company whose main enterprise product was supposed to support the client's existing RBDMS, which could be Oracle, SQL Server or Informix (don't ask).

In the end it worked like that:

• Is it a simple query? Try to write it as to work on all RDBMS;
• Is that not possible (meaning: too hard / will take a lot of time)? There will be an IF in your code. There's no escape (BWAHAHA!), you'll have one query per RDBMS.
• Everything we tested, we tested 3 times: one per RDBMS.

Conclusion

Having your application support multiple RDBMS is a great feature and a very persuasive sales argument. However, it's just not that easy to maintain. It takes work, really.

You might think now that you are never going to use RDBMS specific queries. But then comes the day when an odd query is very easily resolved by both SQL Server and Oracle, but in each's own way. However you will try to write the same query using ANSI SQL (of course, why wouldn't you?) and that will take you hours and may drive you a little crazy. Again, "been there, done that". But don't take my word alone: talk to people and, most importantly, prototype!

• Stick to alphanumerics (ASCII A-Z, 0-9) plus underscore.
• Don't start with an underscore (and you must start with an alphabetic).
• Don't require delimited identifiers - assume case-insensitive.
• You may need to research length limits - old SQL standards (SQL-86, SQL-89) limited names to 18 characters, but most DBMS allow at least 31 these days.
• Avoid keywords (but the list of keywords is immense - for each DBMS separately, and the union of all keywords across all DBMS).

Overall I think this is not a good strategy, but to answer your specific question about identifiers, I expect you will need to determine them on a per-case basis if you want to avoid escaping completely.

You could also consider using ANSI_QUOTES mode in MySQL - then all your identifiers for SQL Server, Oracle and MySQL could be quoted with double-quotes - the ANSI standard.

For instance, SQL Server 2000 "Regular Indentifiers" are undelimited: http://msdn.microsoft.com/en-us/library/aa223962%28v=sql.80%29.aspx

Same docs for SQL Server 2005: http://msdn.microsoft.com/en-us/library/ms175874%28v=sql.90%29.aspx

SQL Server 2005 Reserved Word List: http://msdn.microsoft.com/en-us/library/ms189822%28v=SQL.90%29.aspx

Equivalents for Oracle 10g: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements008.htm

Reserved words in MySQL: http://dev.mysql.com/doc/refman/5.6/en/reserved-words.html