|Subject||Re: [firebird-support] info|
On Wed, Jan 4, 2017 at 9:41 AM, Nico Speleers - Carfac BVBA nico.speleers@... [firebird-support] <email@example.com> wrote:We want to create table- and fieldnames (Firebird 3) in camelcase. If we want to write a select statement we have to use quotes around a tablename. This is not userfriendly. Is there a way that we don't have to use the quotes in SQL-statements ?
We don't want to create our table- and fieldnames in lowercase or uppercase.OK. Here's how it works. You can create a table called TablaRasa with a create table statement.Create table TablaRasa <more stuff>;You can then reference TablaRasa in any SQL statement like this:Insert into TablaRasa <more stuff>;Select from TablaRasA <more stuff>;Update TAblaRAsa <more stuff>;Delete from"TABLARASA" <more stuff>;Drop table TABLARASA;You can use any capitalization you want. However, if you ask Firebird to Show Tables itwill show you TABLARASA. The SQL standard specifies that unquoted identifiers arecase insensitive. That part of the standard was written in 1982, when computers weremuch slower and memory constrained than modern parking meters. A later version ofthe standard introduced quoted identifiers which preserve case, allow special characters(anything other than numbers, letters, $, and _) and allow overlap between key wordsand identifiers.InterBase (Firebird's ancestor) implemented case insensitive identifiers by upper casingidentifiers wherever they were referenced and, specifically, storing the upper case inits system tables. That was a cheap and easy way to handle the problem - and whenyou have (by modern standards) no disk space, no memory, and the computing powerof an abacus, cheap and easy are good.For reasons lost in history, when InterBase added quoted identifiers, the developersdecided just to store the quoted value - without quotes, of course - in the system tables.Deciding whether an identifier was quoted or not was simple - a name that contains acharacter that isn't legal in an unquoted identifier must be a quoted identifier.Myself, I would have added a field that indicates whether the name is quoted or notand preserved the case of unquoted identifiers right then, but they didn't. Userscomplained about the ugliness of upper case even then in the 90's. (Yes, children,there were people who cared about appearances that long ago.)That solution produced the anomaly that a quoted identifier that contains no charactersoutside those allowed in unquoted identifiers behaves like an unquoted identifier. WhileCreate table "TABLA RASA" <stuff>;creates a table that can be referenced only as "TABLA RASA" - quotation marks required,The similar statement without the spaceCreate table "TABLARASA" <stuff>;creates a table that can be referenced as tablarasa, TablaRasa, TAblaRAsa, etc. And,inversely, if you create a table with an unquoted identifier, you can reference it inquoted upper case - example above.I don't know how that behavior fits the SQL standard, but it's a minor violationcompared withthe behavior of MySQL and not terribly dissimilar to PostgreSQL. PostgreSQL chose touse lower case as the default because it was prettier and didn't look as if the code wasSHOUTING at you. InterBase chose upper case because in some European languages,classical French for one, the transformation from lower to upper is deterministic, but thetranslation from upper to lower can produce several results. Not that we were all thatfar into internationalization in '83, but we were thinking about it.<rant>MySQL chose to use the case rules for the environment it runs in - case sensitiveon Linux, case insensitive on Windows. Mac/OS had different case rules for different filesystems. That looks very natural if you run in a single environment, but it is a bit of abitch if you try to create portable applications. MySQL also uses different rules for differenttypes of identifiers. You could look it up. Hell, I'll look it up.</rant>So, in Firebird, preserving the given case of identifiers in the system tables would just bea matter of programming and a change to the system tables which is a bigger problembut not out of the scope of a major version. The question is whether that effort is worththe price given the challenges of making Firebird more secure, faster, more reliable, andmore conformant with new SQL features, given the limited programming resources available.In sum, handwritten code can use upper and lower case in identifiers without quotes andlook pretty. If you want to generate names for triggers and so on from the original format,that works too. Trigger TablaRasaTrigInsert1 is exactly equivalent to TABLARASATrigInsert1and you can reference it as either. Machine generated code can put quotes around everyname it finds in the system tables and create working ugly code. The behavior is standardcompliant and works in multiple operating environments.Good luck,AnnYou