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!"