Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id C39C39F9C35 for ; Fri, 8 Dec 2006 10:09:13 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 20162-02 for ; Fri, 8 Dec 2006 10:09:07 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.172]) by postgresql.org (Postfix) with ESMTP id C26E19F93A4 for ; Fri, 8 Dec 2006 10:09:12 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so784491ugf for ; Fri, 08 Dec 2006 06:09:10 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=P5xiQ7yYF5dQzDw3IPTCPoFKeQT5nRM8WSW1Ak9b5CM6opXQQs1fRp2pK0pSXsfhKhTA4sugn0b7hbFXDp8uBJhKm85D2gR9A1J4lFex4Kzn3FSsZgYiFzsVEymybWNg3xXVPRvXHiTQmZ/xeeeYyH4vqqck0/kMSCYS3zdm8hE= Received: by 10.78.183.15 with SMTP id g15mr272842huf.1165586950507; Fri, 08 Dec 2006 06:09:10 -0800 (PST) Received: by 10.78.131.12 with HTTP; Fri, 8 Dec 2006 06:09:10 -0800 (PST) Message-ID: Date: Fri, 8 Dec 2006 08:09:10 -0600 From: "Aaron Bono" To: "Ken Johanson" Subject: Re: [HACKERS] Case Preservation disregarding case Cc: "Bruce Momjian" , "Tom Lane" , "Martijn van Oosterhout" , pgsql-sql@postgresql.org In-Reply-To: <457646FA.1020804@kensystem.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_58005_31754790.1165586950179" References: <200612031657.kB3Gv6408322@momjian.us> <457646FA.1020804@kensystem.com> X-Google-Sender-Auth: 4059838831a8fecc X-Archive-Number: 200612/83 X-Sequence-Number: 27039 ------=_Part_58005_31754790.1165586950179 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 12/5/06, Ken Johanson wrote: > > Bruce Momjian wrote: > > Tom Lane wrote: > >> > >> The real bottom line, though, is that this community has little respect > >> for proposals that involve moving away from the SQL spec rather than > >> closer to it; and that's what you're asking us to do. The spec is not > >> at all vague about the case-sensitivity of identifiers. Now certainly > >> we are not doing exactly what the spec says, but what you ask is even > >> less like the spec's requirements. > > > > I think there are two major issues here: > > > > o if you quote identifiers that have any upper-case characters, > > do it both at table creation and use > > o display of non-quoted identifiers is lower-case > > > > I think we are OK making people either always quote, or always not > > quote. What we don't currently have a good answer for is people wanting > > the identifiers displayed using the original case. You can use quotes > > all the time of you want such display, but it is a pain. I think this > > is the crux of the complaints. > > > > Saving the case of the original creation and displaying that does work, > > but then it isn't clear if the identifier needs quotes (is the > > upper-case real or just for display). This gets us into even more > > confusion. > > > > Can someone can think of an answer to all this? > > > > I believe there is no one answer that will solve all cases.. but one > solution that might come close is something like this (I take no credit, > others have described this before me): > > PG would support two modes of operation: > > 1) Standard compliant, and; > > 2) Quirks / compatibility mode (case preserving but case-insensitive) > > I believe (and may be wrong) that the #2 mode-of-operation would only > require: > > a) resultset data to have it's (unquoted) identifiers to be case-folded, > and; > b) queries with (unquoted) identifiers (joins / aliases etc) would > continue in the planner to be case folded, so would work as now (and in > effect be case-insensitive). > c) the table describe syntax would return the case-preserved id name > (which it already does if quoted?), or d: > d) in addition to a), optionally send metadata describing the > case-preserved name; this might someday allow newer drivers to display > (display only, not column lookup) those names in database-designer views > (iSQL types apps) > > If #a is possible, then drivers should not break, even if the DB is > config'd to use setting #2. But I don't know the low-level protocol of > PG to know if that is possible. .. > > Hopefully I'm not missing any points here, please correct me if so... > > I believe what I described above (while not being standard complaint per > se) is identical to how mysql and mssql work (operationally anyway) > right now.. On the other had Oracle and others work as PG does now, but > my point in discussing this, is that the first two DBs have enough > market, that offering a compatibility mode to ease the burden of porting > apps would have substantial value (I know this from experience) > Wasn't the whole reason this came up because someone wanted to do SELECT * FROM some_table and have the returned columns returned in a case that was viewable? This thread has gone on for so long I don't remember for sure and I have not kept up with the thread very well. If this is the "case", no pun intended, then why not create views like this: CREATE VIEW my_view ( "Person's ID", "Person's Name" ) AS SELECT person_id, first_name || ' ' || last_name FROM person ; When I do a SELECT * FROM my_view, I get "Person's ID", not "PERSON'S ID" or "person's id". -- ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ================================================================== ------=_Part_58005_31754790.1165586950179 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 12/5/06, Ken Johanson <pg-user@kensystem.com> wrote:
Bruce Momjian wrote:
> Tom Lane wrote:
>>
>> The real bottom line, though, is that this community has little respect
>> for proposals that involve moving away from the SQL spec rather than
>> closer to it; and that's what you're asking us to do.  The spec is not
>> at all vague about the case-sensitivity of identifiers.  Now certainly
>> we are not doing exactly what the spec says, but what you ask is even
>> less like the spec's requirements.
>
> I think there are two major issues here:
>
>       o  if you quote identifiers that have any upper-case characters,
>          do it both at table creation and use
>       o  display of non-quoted identifiers is lower-case
>
> I think we are OK making people either always quote, or always not
> quote.  What we don't currently have a good answer for is people wanting
> the identifiers displayed using the original case.  You can use quotes
> all the time of you want such display, but it is a pain.  I think this
> is the crux of the complaints.
>
> Saving the case of the original creation and displaying that does work,
> but then it isn't clear if the identifier needs quotes (is the
> upper-case real or just for display).  This gets us into even more
> confusion.
>
> Can someone can think of an answer to all this?
>

I believe there is no one answer that will solve all cases.. but one
solution that might come close is something like this (I take no credit,
others have described this before me):

PG would support two modes of operation:

1) Standard compliant, and;

2) Quirks / compatibility mode (case preserving but case-insensitive)

I believe (and may be wrong) that the #2 mode-of-operation would only
require:

a) resultset data to have it's (unquoted) identifiers to be case-folded,
and;
b) queries with (unquoted) identifiers (joins / aliases etc) would
continue in the planner to be case folded, so would work as now (and in
effect be case-insensitive).
c) the table describe syntax would return the case-preserved id name
(which it already does if quoted?), or d:
d) in addition to a), optionally send metadata describing the
case-preserved name; this might someday allow newer drivers to display
(display only, not column lookup) those names in database-designer views
(iSQL types apps)

If #a is possible, then drivers should not break, even if the DB is
config'd to use setting #2. But I don't know the low-level protocol of
PG to know if that is possible. ..

Hopefully I'm not missing any points here, please correct me if so...

I believe what I described above (while not being standard complaint per
se) is identical to how mysql and mssql work (operationally anyway)
right now.. On the other had Oracle and others work as PG does now, but
my point in discussing this, is that the first two DBs have enough
market, that offering a compatibility mode to ease the burden of porting
apps would have substantial value (I know this from experience)

Wasn't the whole reason this came up because someone wanted to do SELECT * FROM some_table and have the returned columns returned in a case that was viewable?  This thread has gone on for so long I don't remember for sure and I have not kept up with the thread very well.

If this is the "case", no pun intended, then why not create views like this:

CREATE VIEW my_view (
"Person's ID",
"Person's Name"
) AS
SELECT
person_id,
first_name || ' ' || last_name
FROM person
;

When I do a SELECT * FROM my_view, I get "Person's ID", not "PERSON'S ID" or "person's id".

--
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
================================================================== ------=_Part_58005_31754790.1165586950179--