Subject Re: [firebird-support] info
Author Ann Harrison
On Wed, Jan 4, 2017 at 9:41 AM, Nico Speleers - Carfac BVBA nico.speleers@... [firebird-support] <> 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.

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.


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,