On Wed, Jan 4, 2017 at 9:41 AM, Nico Speleers - Carfac BVBA nico.speleers@... [firebird-support] <firebird-support@yahoogroups.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 it

will show you TABLARASA.  The SQL standard specifies that unquoted identifiers are 

case insensitive.  That part of the standard was written in 1982, when computers were 

much slower and memory constrained than modern parking meters.  A later version of

the standard introduced quoted identifiers which preserve case, allow special characters

(anything other than numbers, letters, $, and _) and allow overlap between key words 

and identifiers.

InterBase (Firebird's ancestor) implemented case insensitive identifiers by upper casing

identifiers wherever they were referenced and, specifically, storing the upper case in

its system tables.  That was a cheap and easy way to handle the problem - and when 

you have (by modern standards) no disk space, no memory, and the computing power

of an abacus, cheap and easy are good. 

For reasons lost in history, when InterBase added quoted identifiers, the developers

decided 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 a

 character 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 not

and preserved the case of unquoted identifiers right then, but they didn't.   Users

complained 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 characters

outside those allowed in unquoted identifiers behaves like an unquoted identifier.  While

Create table "TABLA RASA" <stuff>;

creates a table that can be referenced only as "TABLA RASA" - quotation marks required,

The similar statement without the space

Create 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 in

quoted upper case - example above.

I don't know how that behavior fits the SQL standard, but it's a minor violationcompared with

the behavior of MySQL and not terribly dissimilar to PostgreSQL.  PostgreSQL chose to

use lower case as the default because it was prettier and didn't look as if the code was

SHOUTING at you.  InterBase chose upper case because in some European languages,

classical French for one, the transformation from lower to upper is deterministic, but the

translation from upper to lower can produce several results.  Not that we were all that

far 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 sensitive

on Linux, case insensitive on Windows. Mac/OS had different case rules for different file 

systems.   That looks very natural if you run in a single environment, but it is a bit of a 

bitch if you try to create portable applications.  MySQL also uses different rules for different 

types of identifiers.  You could look it up.  Hell, I'll look it up.

http://dev.mysql.com/doc/refman/5.5/en/identifier-case-sensitivity.html?ff=nopfpls

</rant>

So, in Firebird, preserving the given case of identifiers in the system tables would just be 

a matter of programming and a change to the system tables which is a bigger problem 

but not out of the scope of a major version. The question is whether that effort is worth

the price given the challenges of making Firebird more secure, faster, more reliable, and

more 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 and

look 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 TABLARASATrigInsert1

and you can reference it as either.  Machine generated code can put quotes around every 

name it finds in the system tables and create working ugly code.  The behavior is standard

compliant and works in multiple operating environments.

Good luck,

Ann

You