On Wed, Jan 4, 2017 at 9:41 AM, Nico Speleers - Carfac BVBA
nico.spele...@carfac.com [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
