>Martijn Tonies
>> Along with Eugen Konkov, I would like clarification of where case
>> preservation would cause problems with tools that assume identifiers pulled
>> from metadata can always be quoted. Seems like it should not be a problem.

>It will be a problem.
>Case preserving, and then quoting and re-creating objects leads to
>case sensitive objects.

create table BigTable (...)
if I create the table 'BigTable' (when I use single quotes I just mark
object name in text. This is not mean "quoting" at all)
then extract metadata by some tool, which will quote "BigTable"
create table "BigTable" (...)
both queries original and extracted are the same. (see about)

>Ann W.Harrison
>Sure. You do this:
>create table BigTable (BigField varchar (20000));
>The metadata is stored preserving case. A portable tool does a
>show tables to get its table list and sees
>...
>BigTable
>...
>It then generates a query
>select * from "BigTable";
>Which fails because "BigTable" is not the same as BigTable under
>the ISO SQL Standard.

Why are those not the same?
When executing query 'select * from "BigTable"' the
"BigTable" case sensetively matches BigTable
so both queries
'select * from BigTable' case insensetively matches BigTable
'select * from "BigTable' case sensetively matches BigTable
will be same, will not?


Another question: SQL says that object names must not be case sensetive
so
'create table a (...)' will seccess and second 'create table A (...)' must fail
because of you can not create object with name of object which is already in database
So that is a BUG when FireBird allow to create two objects 'a' and 'A' in the same database

Other cases:
'create table a (...)' will create table 'a' in database
so
'create table A (...)" must fail because of there is the object 'a' in database
'create table "a" (...)" must fail because of there is the object 'a' in database
'create table "A" (...)" must fail because of there is the object 'a' in database

>Doug Chamberlin
>Is there no indicator stored in the database that a tool can use to tell
>whether identifiers were created with un-quoted or quoted style?
>Adam
>eg. RDB$RELATIONS.RDB$RELATION_NAME can have a sister field
>RDB$RELATIONS.RDB$RELATION_DISPLAYNAME
> Geoff Worboys
> If the display name is not the same as the actual name then you will need to
>ensure that tools/options are available to retrieve the
>actual names too

FireBird just return names AS IS and tools must show names AS IS so there will not any ambigious
It is no need any indicator. FIRST of all indicator or additional RDB$ will do application logic more complex
That will force you to remember is name you get RELATION_DISPLAYNAME or
RELATION_NAME and if you foget that fact it may be the cause of a BUG
Let's propose next algorithm to do with quotes
if (objectName.isQuoted) {
//objectName is equal "myTable"
match ( dbObject, objectName ) // matches as is without any convertions: 'myTable' == 'myTable'
}
else {
//objectName is equal to 'myTable' or 'mytable' or 'MYTABLE' all is unquoted
match ( UPPERCASE(dbObjectName), UPPERCASE(objectName) )
// you UPPERCASE database object names and names in query so
// UPPERCASE( 'myTable' ) == UPPERCASE( 'myTable' )
// UPPERCASE( 'myTable' ) == UPPERCASE( 'mytable' )
// UPPERCASE( 'myTable' ) == UPPERCASE( 'MYTABLE' )
// for speed purpose you can cache anywhere uppercased name for database object
match( dbObjectName.uppercased , UPPERCASE(objectName) )
}
CONCLUSION: case-presernig has no effect. so it is safe to use case-preserve instead of UPPERCASE


SECOND. Third party tools must not do any suggestion about queries, names and so on
What will you do if tool suppose 0.0 when you actually get 0?
Why do third party suppose quotes 'select * from "myTable"' when there is actually no any 'select * from myTable'?
I agree with quoting only in one case when they get object name alone. For example,
create table statement:
create table myTable (
fieldID integer
);
that all be OK if I get
create table "myTable" (
"fieldID" integer
);
Here no any changes of query meaning because of both
'fieldID' and "fieldID" will match fieldID case insensetive and case sensetive accordingly
But when in my stored proc, for example if I mean:
BEGIN
....
select fieldID from myTable;
....
END
I DO NOT MEAN CASE SENSETIVE AT ALL!!! notice no quotes

Why tools suppose quoting in this case?
BEGIN
....
select "fieldID" from "myTable";
....
END
So that is a bug of third party tool.
Let see what is going today. All agree with that fact that
select fieldid from mytable; and
select FIELDID from MYTABLE; are the same
so when I write in my stored proc:
BEGIN
....
select FieLdID from MyTaBLE;
....
END
I do not mean "FieLdID" and "MyTaBLE". So third party tools MUST NOT do any suggestion
and MUST NOT return
BEGIN
....
select "FieLdID" from "MyTaBLE";
....
END
because it will fail even now with current behaviour of FireBird.
Therefore you must agree with that fact that third party tools MUST NOT do any sugestion and
MUST return names in expresioins/statements AS IS:
BEGIN
....
select FieLdID from MyTaBLE;
....
END
now if FireBird is case preserving this query in stored proc still match 'fieldID' field of 'myTable' table
CONCLUSION: case-presernig has no effect. so it is safe to use case-preserve instead of UPPERCASE


[Non-text portions of this message have been removed]