[Zope] SQLTEST is case insensitive?

[Dieter Maurer]

The Doctor What writes:
 > * Dieter Maurer (dieter@handshake.de) [010307 16:39]:
 > > FThe Doctor What writes:
 > >  > The problem is two fold:
 > >  > 1) ID = 1 should be "ID" = 1 so that PostgreSQL will treat it as a
 > >  > case sensitive string.
 > > This was discussed some time ago (--> list archives).
 > > Changing "dtml-sqltest" to enclose the column name in double quotes
 > > would break lots of code (as many people rely on the
 > > case insensitive interpretation).
 > 
 > But consider.  A feature is *missing* currently (the ability to use
 > sqltest and other dtml-sql* vars) with case sensitive databases.
Only, if the database requires special syntax to make it
case sensitive.
 > Would adding a toggle that says "Use double quotes" be a bad thing?
I, personnaly, would not mind unless I had to use it in my code.

 > Frankly, I can't imagine anyone relying on having all their queries
 > dropped to lowercase.  Why would someone, who avoided using cases
 > suddenly switch to throwing upper case characters into their sql
 > queries?
It is the other way round!

I designed the database and used mixed case names to improve
readability.
And my code originally used this same case as documented.
But the database did not care about case. It converted silently
all may mixed case names into upper case.
Finally, I recognized this fact the hard way. I chased a
tricky error for several hours to recognized it being caused
by the database's upper casing.
>From then on, my code used all upper case names.
But I have colleagues... Some prefer all upper case,
others all lower case, others mixed case ...

If some demon would make "dtml-sqltext" (and friends) case
sensitive, we would have to rework several hundred Z SQL methods.
A nightmare...

 > ....
 > > You can use vanilla DTML to emulate "dtml-sqltest"
 > > like for example:
 > > 
 > >      ....
 > >      "URL"."ID" = '<dtml-var ID sql_quote>'
 > >      ....
 > 
 > Thereby removing the whole benifit of using ZOPE and ZSQL objects.
Why that?

You would use the vanilla DTML inside the ZSQL *TEMPLATE*!
You can use *ALL* DTML commands in a ZSQL template
plus several special commands only available in these
templates "dtml-sqlXXXX".


Dieter