Обсуждение: [HACKERS] Case sensitivity in identifiers
Kurt J. Lidl writes ("Re: Re[2]: [HACKERS] Upper and lower case column names "):
>
> >On Tue, 18 Mar 1997 lsh@lubrizol.com wrote:
> >> >Why is it desirable to do this? Work has to be done to throw out case
> >> >and information is lost with no advantage in db space or server
> >> >speed. What is the advantage for caselessness? Does anyone use a
> >> >single-case terminal anymore?
> >> I wrote a message asking exactly the same thing yesterday.
> >> Somehow I don't think it got posted, or maybe I missed it.
> >> I would also like for someone to explain why this was done
> >> on the first place. I am sure there are a lot of people out
> >> there who use Case-sensitive column names and would find it
> >> almost impossible to migrate from 6.0 to 6.1.
> >
> >The main reason is that since most commercial databases use
> >non-case-sensitive identifiers, many programs that connect to Postgres
> >through layers like ODBC or JDBC will assume that Postgres' identifiers
> >are non-case-sensitive, too. Assuming we want compatibility, it's a
> >lot easier to change Postgres than to get companies like Microsoft,
> >Borland, etc. to change their programs.
>
> Yes, it will be a lot easier to make Postgresql be ansi complient
> than it will be to convince companies to make their products be
> non-ansi complient.
>
> There are a great many things that suck about ansi SQL, but being
> gratuitously different isn't one of the things that should be pursued.
I'd agree with the principle of case insensitivity, particularly in
the interests of ANSI compatibility.
Having just fetched and built 6.1, however, I am appalled at the way
it has been done. Perhaps it had to be that way, but off the top of my
head, I can't see why. I use cases as a matter of style, not syntax,
and 6.1 has thrown away all my cases. Wouldn't it be possible to keep
the identifiers as defined, but ignore the case at run-time, so to
speak? All I want to do is this:
=> create table FOO (ID int4, Field1 char16, FieldTwo varchar(80));
=> \d FOO
Table = FOO
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| ID | int4 | 4 |
| Field1 | char16 | 16 |
| FieldTwo | varchar | 80 |
+----------------------------------+----------------------------------+-------+
or somesuch.
Right now saying '\d FOO' says 'Couldn't find table FOO!' so somebody
missed a bit of case-sensitivity code somewhere, but I'm sure you see
the point: *store* the identifiers as given, but allow access to them
with insensitivity.
Such a change would also mean that third-party apps which read the
identifiers would not break (at least for this reason). In particular
I use PHP/FI, now I know that there's a 'fix' but (i) I was happy with
my tried, tested and tuned httpd, and (ii) there must be other cases
like this; it's not as if PHP/FI was abusing the Postgres95 API
particularly.
While ANSI SQL may be case insensitive, does it insist that everything
is forced to lower case? Does it break compliance to implement what I
have suggested? Isn't this the elegant way to do it? If you'd done it
this way, how many people would have noticed?
Do I hear howling from the implementors here?
Sorry if this has been covered before on this list. I'm about 4000
messages behind right now ;-(
John. _ _
- -- _ | |___| |_ _ _
John Robinson 46 Bank Street, Dumfries DG1 2PA, UK | |_| / . \ ' \| ' \
+44 1387 247249 http://www.intelligent.co.uk/~john/ \___/\___/_||_|_||_|
Aye Oedipus, yer a complex f*cker right enough --Irvine Welsh
------------------------------
> I'd agree with the principle of case insensitivity, particularly in > the interests of ANSI compatibility. > > Having just fetched and built 6.1, however, I am appalled at the way > it has been done. Perhaps it had to be that way, but off the top of my > head, I can't see why. I use cases as a matter of style, not syntax, > and 6.1 has thrown away all my cases. Wouldn't it be possible to keep > the identifiers as defined, but ignore the case at run-time, so to > speak? All I want to do is this: > > => create table FOO (ID int4, Field1 char16, FieldTwo varchar(80)); > => \d FOO > Table = FOO > +----------------------------------+----------------------------------+-------+ > | Field | Type | Length| > +----------------------------------+----------------------------------+-------+ > | ID | int4 | 4 | > | Field1 | char16 | 16 | > | FieldTwo | varchar | 80 | > +----------------------------------+----------------------------------+-------+ > > or somesuch. > > Right now saying '\d FOO' says 'Couldn't find table FOO!' so somebody > missed a bit of case-sensitivity code somewhere, but I'm sure you see I will check on this. Looks like a bug. > the point: *store* the identifiers as given, but allow access to them > with insensitivity. > > Such a change would also mean that third-party apps which read the > identifiers would not break (at least for this reason). In particular > I use PHP/FI, now I know that there's a 'fix' but (i) I was happy with > my tried, tested and tuned httpd, and (ii) there must be other cases > like this; it's not as if PHP/FI was abusing the Postgres95 API > particularly. > > While ANSI SQL may be case insensitive, does it insist that everything > is forced to lower case? Does it break compliance to implement what I > have suggested? Isn't this the elegant way to do it? If you'd done it > this way, how many people would have noticed? > > Do I hear howling from the implementors here? You have a good point here. I can see a value in keeping case when creating fields, but allowing any case to access it. Is that the basis of the argument. Right now, we lowercase arguments when the come in from the parser. We could preserve case for Create-type statements, and force lowercase on all comparisons, but that is a big job, and I think we questioned the value of it. - -- Bruce Momjian maillist@candle.pha.pa.us ------------------------------
Bruce Momjian writes ("Re: [HACKERS] Case sensitivity in identifiers"):
> > Right now saying '\d FOO' says 'Couldn't find table FOO!' so somebody
> > missed a bit of case-sensitivity code somewhere, but I'm sure you see
>
> I will check on this. Looks like a bug.
Cheers. Incidentally I wish \d tablename wouldn't complain if I stuck
a ; on the end, just because I get in to the habit inside psql, but I
know the ; is only supposed to terminate SQL statements which \d isn't.
> You have a good point here. I can see a value in keeping case when
> creating fields, but allowing any case to access it. Is that the basis
> of the argument.
Yes indeed.
> Right now, we lowercase arguments when the come in from the parser. We
> could preserve case for Create-type statements, and force lowercase on
> all comparisons, but that is a big job, and I think we questioned the
> value of it.
I wouldn't have thought it was a particularly big job; you keep these
identifiers in postgres tables anyway: why not a new batch of char
types and operators using strcasecmp instead of strcmp?
I'm suggesting that's not a big job because if you've structured your
code right (of course you have!) you just copy all the files, do a
spot of search and replace, and possibly some minor tweaking. Maybe
even just two new functions (compare ignoring case - or don't you have
it already?) plus a suite of case-insensitive character operators to
sort out indexing.
Some end users might even find such new functionality useful...
John. _ _
- -- _ | |___| |_ _ _
John Robinson 46 Bank Street, Dumfries DG1 2PA, UK | |_| / . \ ' \| ' \
+44 1387 247249 http://www.intelligent.co.uk/~john/ \___/\___/_||_|_||_|
You can waste a whole lifetime just trying to be --Chris Rea
------------------------------
John Robinson writes ("Re: [HACKERS] Case sensitivity in identifiers"):
> Bruce Momjian writes ("Re: [HACKERS] Case sensitivity in identifiers"):
> > Right now, we lowercase arguments when the come in from the parser. We
> > could preserve case for Create-type statements, and force lowercase on
> > all comparisons, but that is a big job, and I think we questioned the
> > value of it.
I should probably restate: the principal value of it would be (i) to
ensure that third party users of PostgreSQL are less likely to notice
the change to ANSI compliance as a problem within their tools
(i.e. only if they relied on same-name alternate-case identifiers,
rather than just not doing force-to-lower themselves), and (ii)
elegance (I think MS Access does it by force-to-lower and ODBC does it
by force-to-upper).
John. _ _
- -- _ | |___| |_ _ _
John Robinson 46 Bank Street, Dumfries DG1 2PA, UK | |_| / . \ ' \| ' \
+44 1387 247249 http://www.intelligent.co.uk/~john/ \___/\___/_||_|_||_|
All those signs I've been missing right there in your eyes --Del Amitri
------------------------------
>
> Bruce Momjian writes ("Re: [HACKERS] Case sensitivity in identifiers"):
> > > Right now saying '\d FOO' says 'Couldn't find table FOO!' so somebody
> > > missed a bit of case-sensitivity code somewhere, but I'm sure you see
> >
> > I will check on this. Looks like a bug.
>
> Cheers. Incidentally I wish \d tablename wouldn't complain if I stuck
> a ; on the end, just because I get in to the habit inside psql, but I
> know the ; is only supposed to terminate SQL statements which \d isn't.
I do the same thing. Would anything break if I removed a trailing ';'
from \d commands? Anyone?
>
> > You have a good point here. I can see a value in keeping case when
> > creating fields, but allowing any case to access it. Is that the basis
> > of the argument.
>
> Yes indeed.
>
> > Right now, we lowercase arguments when the come in from the parser. We
> > could preserve case for Create-type statements, and force lowercase on
> > all comparisons, but that is a big job, and I think we questioned the
> > value of it.
>
> I wouldn't have thought it was a particularly big job; you keep these
> identifiers in postgres tables anyway: why not a new batch of char
> types and operators using strcasecmp instead of strcmp?
>
> I'm suggesting that's not a big job because if you've structured your
> code right (of course you have!) you just copy all the files, do a
> spot of search and replace, and possibly some minor tweaking. Maybe
> even just two new functions (compare ignoring case - or don't you have
> it already?) plus a suite of case-insensitive character operators to
> sort out indexing.
>
> Some end users might even find such new functionality useful...
It is not that easy. We use indexes and cached keys to look up things.
Doing such things case-insensitive is a big job.
- --
Bruce Momjian
maillist@candle.pha.pa.us
------------------------------
Bruce Momjian writes ("Re: [HACKERS] Case sensitivity in identifiers"):
> > I wouldn't have thought it was a particularly big job; you keep these
> > identifiers in postgres tables anyway: why not a new batch of char
> > types and operators using strcasecmp instead of strcmp?
[snip]
>
> It is not that easy. We use indexes and cached keys to look up things.
> Doing such things case-insensitive is a big job.
OK, well I'm sure you realise I don't really know what I'm talking
about, but PostgreSQL seems so easily user-extensible and I thought it
used the same methods internally. I'll get back to you when I do know
what I'm talking about.
In the mean time to help me get over the incompatibilities the current
implementation has thrown up, i.e. revert to case-sensitivity, is it
sufficient to remove the 'tolower' call (loop) in the {identifier}
chunk in scan.l, and revert to strcasecmp in ScanKeywordLookup() in
keywords.c ? If not, if you still have the patch you installed to
implement the current method, could you mail it to me so that I can
(automatically or manually) uninstall the patch, please? Cheers!
I have the released 6.1 version.
John. _ _
- -- _ | |___| |_ _ _
John Robinson 46 Bank Street, Dumfries DG1 2PA, UK | |_| / . \ ' \| ' \
+44 1387 247249 http://www.intelligent.co.uk/~john/ \___/\___/_||_|_||_|
Throw me away, cos I don't mind, I'm still satisfied --Del Amitri
------------------------------
>
> Bruce Momjian writes ("Re: [HACKERS] Case sensitivity in identifiers"):
> > > I wouldn't have thought it was a particularly big job; you keep these
> > > identifiers in postgres tables anyway: why not a new batch of char
> > > types and operators using strcasecmp instead of strcmp?
> [snip]
> >
> > It is not that easy. We use indexes and cached keys to look up things.
> > Doing such things case-insensitive is a big job.
>
> OK, well I'm sure you realise I don't really know what I'm talking
> about, but PostgreSQL seems so easily user-extensible and I thought it
> used the same methods internally. I'll get back to you when I do know
> what I'm talking about.
>
> In the mean time to help me get over the incompatibilities the current
> implementation has thrown up, i.e. revert to case-sensitivity, is it
> sufficient to remove the 'tolower' call (loop) in the {identifier}
> chunk in scan.l, and revert to strcasecmp in ScanKeywordLookup() in
> keywords.c ? If not, if you still have the patch you installed to
> implement the current method, could you mail it to me so that I can
> (automatically or manually) uninstall the patch, please? Cheers!
It was done in pieces, not as one patch. The scan.l is the major part
of it.
However, we needed to make changes to libpq where column and database
names are specified, and any hard-coded table name patterns like Inv*.
- --
Bruce Momjian
maillist@candle.pha.pa.us
------------------------------
It seems that the best way to handle \d is the way many database vendors
do it: through an SQl statement:
In informix, it's INFO command, in RDB is is SHOW...
Something like INFO TABLE; would list all tables,
INFO TABLE sometablename would display info for one table...
INFO INDEX, INFO SEQUENCE....etc...
But I suppose stripping ';' from the end of \d command would be ok..
=+=------------------------/\---------------------------------=+=
Igor Natanzon |**| E-mail: igor@sba.miami.edu
=+=------------------------\/---------------------------------=+=
On Sun, 29 Jun 1997, Bruce Momjian wrote:
> >
> > Bruce Momjian writes ("Re: [HACKERS] Case sensitivity in identifiers"):
> > > > Right now saying '\d FOO' says 'Couldn't find table FOO!' so somebody
> > > > missed a bit of case-sensitivity code somewhere, but I'm sure you see
> > >
> > > I will check on this. Looks like a bug.
> >
> > Cheers. Incidentally I wish \d tablename wouldn't complain if I stuck
> > a ; on the end, just because I get in to the habit inside psql, but I
> > know the ; is only supposed to terminate SQL statements which \d isn't.
>
> I do the same thing. Would anything break if I removed a trailing ';'
> from \d commands? Anyone?
>
> >
> > > You have a good point here. I can see a value in keeping case when
> > > creating fields, but allowing any case to access it. Is that the basis
> > > of the argument.
> >
> > Yes indeed.
> >
> > > Right now, we lowercase arguments when the come in from the parser. We
> > > could preserve case for Create-type statements, and force lowercase on
> > > all comparisons, but that is a big job, and I think we questioned the
> > > value of it.
> >
> > I wouldn't have thought it was a particularly big job; you keep these
> > identifiers in postgres tables anyway: why not a new batch of char
> > types and operators using strcasecmp instead of strcmp?
> >
> > I'm suggesting that's not a big job because if you've structured your
> > code right (of course you have!) you just copy all the files, do a
> > spot of search and replace, and possibly some minor tweaking. Maybe
> > even just two new functions (compare ignoring case - or don't you have
> > it already?) plus a suite of case-insensitive character operators to
> > sort out indexing.
> >
> > Some end users might even find such new functionality useful...
>
> It is not that easy. We use indexes and cached keys to look up things.
> Doing such things case-insensitive is a big job.
>
> --
> Bruce Momjian
> maillist@candle.pha.pa.us
>
------------------------------