On Thu, 10 Jan 2002, Tim Bunce wrote:
>On Wed, Jan 09, 2002 at 09:41:28AM -0800, Michael Peppler wrote:
>> Tim Bunce writes:
>> > On Wed, Jan 09, 2002 at 09:57:27AM +0000, Simon Oliver wrote:
>> > > Whether the DBD determines/sets this value on a per connection
>> > > basis, when the identifier_delimitor method is called or each
>> > > time quoted_identifiers is called would be up to the implementor
>> > > of the DBD.
>> >
>> > I'm not keen. I just don't see a need. The driver should know (or
>> > be able to work out for itself) if it should be quoting identifiers
>> > on any given connection. No application visible API is needed.
After several iterations on the body of this email, I'm going to start
with an obvious question to which the answer is no longer visible.
What urgent issue that wasn't apparent in DBI 1.14 is
quote_identifier addressing?
Many identifiers do not need to be quoted - a name such as 'asinine' is
not a keyword in standard SQL dialects, and can be returned unchanged by
quote_identifier on all SQL systems I'm aware of - even the asinine
ones. Identifiers that are all alphanumeric and start with a letter
don't need to be quoted unless they are also SQL keywords. I think that
applies to all DBMS -- unless the identifier is case-sensitive.
With delimited identifiers, you also get case sensitivity! Now, that's
a bag of worms. If I call quote_identifier with a string ASININE and
the quote_identifier method slaps quotes around it but the DBMS stores
the names in lower case, then you are going to get into problems. Or
the problem can be reversed - you supply asinine but the DBMS stores
ASININE and you run into problems. The SQL standard (ISO 9075-2:1999)
mandates in section 5.4 'Names and Identifiers', syntax rule 2:
2) An <SQL language identifier> is equivalent to an <SQL language
identifier> in which every letter that is a lower-case letter is
replaced by the equivalent upper-case letter or letters. [...]
There is also similar wording in section 5.2 '<token> and <separator>',
syntax rule 23 for <regular identifier> and syntax rule 26 which
describes the equivalence between <regular identifier> and <delimited
identifier>.
>> Unless I'm really missing something this would mean (at least for me)
>> parsing the SQL and figuring out if some of the identifiers are
>> keywords (or include spaces) and then turning the quoted_identifier
>> property for that connection on.
>>
>> Seems non-trivial to me...
>
>No. The SQL should have been constructed by the application calling
>quote_identifier() on any identifiers it contains.
I've managed to resist adding comments that won't lead to constructive
debate. Suffice to say, I disagree.
>The issue is how your DBD's quote_identifier implementation knows
>whether it should quote or not.
>
>And, as I said in my previous email:
>> (A middle-road approach might be to have an option to quote_identifier
>> that says only quote the identifier if it needs it. If a database
>> don't support quoting identifiers then either quoting or not-quoting
>> an identifier that needs it will generate an error anyway.)
>
>I've now implemented that.
I have a feeling that we're looking at this from one side only -- the
DBI and DBD implementers side -- and not from the DBI/DBD user's side.
If the DBI user wants to write portable code, they are going to have a
tough time of it, especially with quote_identifier in use. I think this
is making their life much more difficult - not easier. As far as I can
see, they have to write database-dependent calls to the quote_identifier
function to get the 'right' answer. If that's right, then Ick!
I don't see how quote_identifier helps a user of DBI write more portable
code.
--
Jonathan Leffler #include <disclaimer.h>
STSM, IBM Data Management Solutions. Phone: +1 650-926-6921
Email: [EMAIL PROTECTED], [EMAIL PROTECTED]
Guardian of DBD::Informix v1.00.PC1 -- http://dbi.perl.org
."I don't suffer from insanity; I enjoy every minute of it!"
