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
* Dieter Maurer (dieter@handshake.de) [010308 14:52]:
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.
I am fairly certain, though I cannot find the ANSI document I'm looking for on the web, that double quotes is OK with the standard, and shouldn't break other databases, though it would break PostgreSQL code.
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.
Well, were would I add this? I'm not familiar enough with the Zope Architecture to actually add this feature to Zope Proper. Especially without risking damaging everyone else's objects.
But I have colleagues... Some prefer all upper case, others all lower case, others mixed case ...
Ahh...okay. I guess that makes sense. I just prefer to do it right the first time....best practises and all that.
....
You can use vanilla DTML to emulate "dtml-sqltest" like for example:
.... "URL"."ID" = '<dtml-var ID sql_quote>'
You don't need the single quotes since ID is an integer.
....
Thereby removing the whole benifit of using ZOPE and ZSQL objects. Why that?
I'm exaggerating. It just negates the dtml-sqlxxxx specials.
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".
<dtml-sqlgroup where> <dtml-sqltest ID type=int optional> <dtml-and> "val_Protocol"."ID" = "URL"."val_ProtocolID" </dtml-sqlgroup> If I make that: <dtml-sqlgroup where> "URL"."ID" = <dtml-var ID sql_quote> <dtml-and> "val_Protocol"."ID" = "URL"."val_ProtocolID" </dtml-sqlgroup> Then ID cannot be optional. I can't use this object as a 'list all' solution. Isn't that the whole point of the dtml-sqltest tag? Does anyone have some good NORMALIZED (say 3rd-4th normal) database examples of using ZSQL Methods and database adapters? Just a really good example would be great. Ciao! -- Thank goodness modern convenience is a thing of the remote future. -- Pogo, by Walt Kelly The Doctor What: Second Baseman http://docwhat.gerf.org/ docwhat@gerf.org KF6VNC
participants (2)
-
Dieter Maurer -
The Doctor What