RE: [Zope-dev] ZSQL and Normalized databases (or why ZSQL sucks)
Title: RE: [Zope-dev] ZSQL and Normalized databases (or why ZSQL sucks)
May be I am misunderstanding your problem here, but are you suggesting that
SELECT tab1.col1 col1, tab2.col1 col2
FROM ... etc ...
Does not expose 'col1' and 'col2' in the namespace for you? This is not my experience. Ok, I only have about 50 ZSQL methods on my intranet but they are all complex queries with column naming clashes. If it failed to work as you are suggesting, I would not have continued using Zope - Database connectivity was one of my criteria.
The normality (or abnormality) of your databases should make no difference, (Except to how complex your individual SQL statements become). What database are you running against? Maybe you have hit a database connector bug?
I have the following query that our sales-reps use to check how much product we sold in the last 'n' days/weeks/months etc...
-
PARAMS: dDate, sOffsetSize, nOffset
SELECT DISTINCT
Order_.Shipped_Date Date,
Region.Region_Name Region,
Employee.Number_ ID,
Territory.Territory_Name Territory,
Currency_Exchange_Rate.From_Currency_Code Currency,
SUM(Order_.Sub_Total - Order_.Order_Discount_Amount) Local,
Currency_Exchange_Rate.Current_Exchange_Rate * SUM(Order_.Sub_Total - Order_.Order_Discount_Amount) Dollar
FROM Region
INNER JOIN Territory ON Region.Region_Id = Territory.Region_Id
INNER JOIN Company ON Territory.Territory_ID = Company.Territory_ID
INNER JOIN Order_ ON Company.Company_ID = Order_.Bill_To_Company_ID
INNER JOIN Currency_Exchange_Rate ON Order_.Currency_ID = Currency_Exchange_Rate.From_Currency_ID
INNER JOIN Employee ON Order_.Account_Manager = Employee.Employee_Id
dtml-sqlgroup where
(Currency_Exchange_Rate.To_Currency_Code = 'USD')
dtml-and
(Order_.Status IN ('Invoiced', 'Billed', 'Shipped'))
dtml-and
(Order_.Shipped_Date IS NOT NULL)
dtml-and
(Order_.Sub_Total 0)
dtml-and
(datediff(day, Order_.Shipped_Date, 'dtml-var dDate') = 0)
dtml-and
(datediff(dtml-var sOffsetSize, Order_.Shipped_Date, 'dtml-var dDate') dtml-var nOffset)
/dtml-sqlgroup
GROUP BY Order_.Shipped_Date, Region.Region_Name, Employee.Number_, Territory.Territory_Name, Currency_Exchange_Rate.From_Currency_Code, Currency_Exchange_Rate.Current_Exchange_Rate
ORDER BY Region.Region_Name, Employee.Number_, Territory.Territory_Name, Currency_Exchange_Rate.From_Currency_Code
-
This exposes 'Date', 'Region', 'ID', 'Territory', 'Currency', 'Local' and 'Dollar' to the calling script. I use this from a Python product, python scripts and from DTML all the time (10's or 100's of times per day at least).
As far as the 'foo=0' example, if you set foo to 0, and do nothing in between to change it, of course a test for not equal to 0 will fail, what would you expect?
Adrian...
-Original Message-
From: The Doctor What [mailto:[EMAIL PROTECTED]]
Sent: Thursday, 19 April 2001 23:22
To: Paul Erickson
Cc: [EMAIL PROTECTED]
Subject: Re: [Zope-dev] ZSQL and Normalized databases (or why ZSQL
sucks)
* Paul Erickson ([EMAIL PROTECTED]) [010419 17:02]:
The Doctor What wrote:
* Loosing the variable between the form and dtml-if
I don't understand this. I'm assuming that you are losing values that
are not in your argument list. All you have to do is add the arguments.
That isn't what I mean. Try this (typed in, so it may need to be
adjusted):
paramfoo=0/param
dtml-if expr=foo != 0Life is good/dtml-if
You'll NEVER get Life is good to print out. EVER. This is because
ZSQL is setting foo to 0. But dtml-var and dtml-sqlvar both work.
* Inability to handle table.field names for variables
* And enough flexibility to work around the above problems
This makes ZSQL extreamely nasty, and impossible to use with a
normalized database.
Database normalization isn't really an issue. It sounds like you're
really just having problems with the syntax of joins.
Nope, can do joins. Been doing joins (mainly inner). Not the
problem. The problem is that if I have tables like:
Table1
--
id
name
desc
Table2
--
id
Table1ID -- Foreign Key thrown in for fun.
name
desc
And I join them, then I MUST rename all the selects using AS:
select
Table1.id as id1
Table1.name as name1
...etc
Because I can't have zsql put the variables in the caller's
namespace as Table1.id. It puts them in as id (without the AS).
Fortunately, I found the column for sqltest (which is the other end
of ZSQL):
dtml-sqltest id1 column=Table1.id
What's the point? ZSQL sucks, how do I talk to the DB directly?
Grab the Python database adapter and write some python classes or
external methods to do what you need.
Can I use them from a python script? If not, what's the point? I
mean: External methods are nice to have when you have *no other
choice*, but they aren't something I'd want to debug and deal with
for object.
This makes things work MUCH better. So there are work arounds.
This isn't a work-around, it is the way that it is intended to be used
Re: [Zope-dev] ZSQL and Normalized databases (or why ZSQL sucks)
* Adrian Hungate ([EMAIL PROTECTED]) [010420 05:06]: May be I am misunderstanding your problem here, but are you suggesting that SELECT tab1.col1 col1, tab2.col1 col2 FROM ... etc ... Does not expose 'col1' and 'col2' in the namespace for you? This is not my experience. Ok, I only have about 50 ZSQL methods on my intranet but they are all complex queries with column naming clashes. If it failed to work as you are suggesting, I would not have continued using Zope - Database connectivity was one of my criteria. I mean that: SELECT tab1.col1, tab2.col1 FROM ... etc ... Does not expose 'tab1.col1' and 'tab2.col1'. In otherwords, it doesn't gracefully handle name clashes. I realize that calling out the variable 'tab1.col1' out of the namespace is a pain, but (hopefully) in the future it would be made easier. Perhaps like so: dtml-var name='tab1.col1' (note single quotes) The normality (or abnormality) of your databases should make no difference, (Except to how complex your individual SQL statements become). What database are you running against? Maybe you have hit a database connector bug? Postgresql. I've tried all three of the DAs: Psyco, Popy and PostresDA [Nice Example Snipped] What DB was that? The syntax isn't what I expect from Postgres, Tandem and MySQL. As far as the 'foo=0' example, if you set foo to 0, and do nothing in between to change it, of course a test for not equal to 0 will fail, what would you expect? Here is the example again, but better: paramfoo:int=0/param dtml-if expr="foo 0"Not Zerodtml-elseZero/dtml-if If called like so: dtml-var expr="zsql_example(foo=200)" It will ALWAYS print 'Zero'. This makes it hard to do something with a param that defaults to nothing like: paramcategory_id=""/param SELECT url.id, url.name dtml-if category_id, xref_url_category.categoryid as category_id, /dtml-if FROM url dtml-if category_id, xref_url_category /dtml-if dtml-sqlgroup where dtml-if category_id dtml-sqltest category_id column="xref_url_category.categoryid" type="int" dtml-and xref_url_category.urlid = url.id /dtml-if /dtml-sqlgroup You can't even replace the param with "category_id:int=0" and then check for category_id = 0 in the dtml-ifs. I finished my project, so I'm happy for the moment. I have another ZSQL project (a DVD, Comic Book and Video tracker for my Manga and Anime), so I'll be back. Thanks to everyone who spent time to help out. Ciao! -- Who are you going to believe, me or your own eyes? -- Groucho Marx The Doctor What: Guru to the Godshttp://docwhat.gerf.org/ [EMAIL PROTECTED] KF6VNC PGP signature
RE: [Zope-dev] ZSQL and Normalized databases (or why ZSQL sucks)
...I mean that: SELECT tab1.col1, tab2.col1 FROM ... etc ... Does not expose 'tab1.col1' and 'tab2.col1'. In otherwords, it doesn't gracefully handle name clashes. I realize that calling out the variable 'tab1.col1' out of the namespace is a pain, but (hopefully) in the future it would be made easier I havent followed the entire conversation so forgive me if we are on the same page but in different books. Have you tried to alias the column names? e.g.: SELECT tab1.col1 AS COL1_A, tab2.col1 AS COL1_B FROM etc... -Original Message- From: The Doctor What [mailto:[EMAIL PROTECTED]] Sent: Friday, April 20, 2001 10:21 AM To: [EMAIL PROTECTED] Subject: Re: [Zope-dev] ZSQL and Normalized databases (or why ZSQL sucks) * Adrian Hungate ([EMAIL PROTECTED]) [010420 05:06]: May be I am misunderstanding your problem here, but are you suggesting that SELECT tab1.col1 col1, tab2.col1 col2 FROM ... etc ... Does not expose 'col1' and 'col2' in the namespace for you? This is not my experience. Ok, I only have about 50 ZSQL methods on my intranet but they are all complex queries with column naming clashes. If it failed to work as you are suggesting, I would not have continued using Zope - Database connectivity was one of my criteria. I mean that: SELECT tab1.col1, tab2.col1 FROM ... etc ... Does not expose 'tab1.col1' and 'tab2.col1'. In otherwords, it doesn't gracefully handle name clashes. I realize that calling out the variable 'tab1.col1' out of the namespace is a pain, but (hopefully) in the future it would be made easier. Perhaps like so: dtml-var name='tab1.col1' (note single quotes) The normality (or abnormality) of your databases should make no difference, (Except to how complex your individual SQL statements become). What database are you running against? Maybe you have hit a database connector bug? Postgresql. I've tried all three of the DAs: Psyco, Popy and PostresDA [Nice Example Snipped] What DB was that? The syntax isn't what I expect from Postgres, Tandem and MySQL. As far as the 'foo=0' example, if you set foo to 0, and do nothing in between to change it, of course a test for not equal to 0 will fail, what would you expect? Here is the example again, but better: paramfoo:int=0/param dtml-if expr="foo 0"Not Zerodtml-elseZero/dtml-if If called like so: dtml-var expr="zsql_example(foo=200)" It will ALWAYS print 'Zero'. This makes it hard to do something with a param that defaults to nothing like: paramcategory_id=""/param SELECT url.id, url.name dtml-if category_id, xref_url_category.categoryid as category_id, /dtml-if FROM url dtml-if category_id, xref_url_category /dtml-if dtml-sqlgroup where dtml-if category_id dtml-sqltest category_id column="xref_url_category.categoryid" type="int" dtml-and xref_url_category.urlid = url.id /dtml-if /dtml-sqlgroup You can't even replace the param with "category_id:int=0" and then check for category_id = 0 in the dtml-ifs. I finished my project, so I'm happy for the moment. I have another ZSQL project (a DVD, Comic Book and Video tracker for my Manga and Anime), so I'll be back. Thanks to everyone who spent time to help out. Ciao! -- Who are you going to believe, me or your own eyes? -- Groucho Marx The Doctor What: Guru to the Godshttp://docwhat.gerf.org/ [EMAIL PROTECTED] KF6VNC ___ Zope-Dev maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope-dev ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope )
RE: [Zope-dev] ZSQL and Normalized databases (or why ZSQL sucks)
On Fri, 20 Apr 2001, Bryan Baszczewski wrote: ...I mean that: SELECT tab1.col1, tab2.col1 FROM ... etc ... Does not expose 'tab1.col1' and 'tab2.col1'. In otherwords, it doesn't gracefully handle name clashes. I realize that calling out the variable 'tab1.col1' out of the namespace is a pain, but (hopefully) in the future it would be made easier I havent followed the entire conversation so forgive me if we are on the same page but in different books. Have you tried to alias the column names? e.g.: SELECT tab1.col1 AS COL1_A, tab2.col1 AS COL1_B FROM etc... Aliasing works for me. (ZOracleDA). -- _ peter sabaini, mailto: [EMAIL PROTECTED] - ___ Zope-Dev maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope-dev ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope )
Re: [Zope-dev] ZSQL and Normalized databases (or why ZSQL sucks)
The Doctor What wrote: * Adrian Hungate ([EMAIL PROTECTED]) [010420 05:06]: May be I am misunderstanding your problem here, but are you suggesting that SELECT tab1.col1 col1, tab2.col1 col2 FROM ... etc ... Does not expose 'col1' and 'col2' in the namespace for you? This is not my experience. Ok, I only have about 50 ZSQL methods on my intranet but they are all complex queries with column naming clashes. If it failed to work as you are suggesting, I would not have continued using Zope - Database connectivity was one of my criteria. I mean that: SELECT tab1.col1, tab2.col1 FROM ... etc ... Does not expose 'tab1.col1' and 'tab2.col1'. In otherwords, it doesn't gracefully handle name clashes. I realize that calling out the variable 'tab1.col1' out of the namespace is a pain, but (hopefully) in the future it would be made easier. I suspect it is mainly a problem with SQL ;) amphora2=# select item.item_id, item.ordinal from item where item_id = 15; item_id | ordinal -+- 15 | 0 (1 row) amphora2=# select item.item_type_id, item_type.item_type_id from item,item_type amphora2-# where item_id = 15 and item.item_type_id= item_type.item_type_id; item_type_id | item_type_id --+-- 1 |1 (1 row) The above example is with postgreSQL, but IIRC it is how the SQL standard defines it to behave. Except that perhaps it may forbid one from writing such queries if there is a possibility of both item_type_id's not being the same. - Hannu ___ Zope-Dev maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope-dev ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope )
RE: [Zope-dev] ZSQL and Normalized databases (or why ZSQL sucks)
Title: RE: [Zope-dev] ZSQL and Normalized databases (or why ZSQL sucks) I am using ZODBC talking to an MS-SQL7 database (Yack spit), so the syntax is a little different. As for the unchangeable defaults, I use defaults a lot, and I don't think I've ever seen your problem. What you appear to be trying to do with the ZSQL Method in your example strikes me as 'A Bad Idea'(tm). Having the schema of the returned data change depending on the value of a parameter seems to be asking for problems (IMHO). If you look back at the my last example, and replace the PARAMS with: dDate=2001/03/31 sOffsetSize=month nOffset=1 This certainly does work, I just tested it. Interestingly, so does: PARAMS: testval:int=0 dtml-if testval select count(shipped_date) cnt dtml-else select count(shipped_date)/2 halfcnt /dtml-if from order_ If you call it with testval=1, you get a column called cnt, with testval=0 you get a column called halfcnt. Isn't this just what you are saying doesn't work? Adrian... -- Adrian Hungate Manager, European I.S. Acucorp UK Limited -Original Message- From: The Doctor What [mailto:[EMAIL PROTECTED]] Sent: Friday, 20 April 2001 15:21 To: [EMAIL PROTECTED] Subject: Re: [Zope-dev] ZSQL and Normalized databases (or why ZSQL sucks) * Adrian Hungate ([EMAIL PROTECTED]) [010420 05:06]: May be I am misunderstanding your problem here, but are you suggesting that SELECT tab1.col1 col1, tab2.col1 col2 FROM ... etc ... Does not expose 'col1' and 'col2' in the namespace for you? This is not my experience. Ok, I only have about 50 ZSQL methods on my intranet but they are all complex queries with column naming clashes. If it failed to work as you are suggesting, I would not have continued using Zope - Database connectivity was one of my criteria. I mean that: SELECT tab1.col1, tab2.col1 FROM ... etc ... Does not expose 'tab1.col1' and 'tab2.col1'. In otherwords, it doesn't gracefully handle name clashes. I realize that calling out the variable 'tab1.col1' out of the namespace is a pain, but (hopefully) in the future it would be made easier. Perhaps like so: dtml-var name='tab1.col1' (note single quotes) The normality (or abnormality) of your databases should make no difference, (Except to how complex your individual SQL statements become). What database are you running against? Maybe you have hit a database connector bug? Postgresql. I've tried all three of the DAs: Psyco, Popy and PostresDA [Nice Example Snipped] What DB was that? The syntax isn't what I expect from Postgres, Tandem and MySQL. As far as the 'foo=0' example, if you set foo to 0, and do nothing in between to change it, of course a test for not equal to 0 will fail, what would you expect? Here is the example again, but better: paramfoo:int=0/param dtml-if expr=foo 0Not Zerodtml-elseZero/dtml-if If called like so: dtml-var expr=zsql_example(foo=200) It will ALWAYS print 'Zero'. This makes it hard to do something with a param that defaults to nothing like: paramcategory_id=/param SELECT url.id, url.name dtml-if category_id, xref_url_category.categoryid as category_id, /dtml-if FROM url dtml-if category_id, xref_url_category /dtml-if dtml-sqlgroup where dtml-if category_id dtml-sqltest category_id column=xref_url_category.categoryid type=int dtml-and xref_url_category.urlid = url.id /dtml-if /dtml-sqlgroup You can't even replace the param with category_id:int=0 and then check for category_id = 0 in the dtml-ifs. I finished my project, so I'm happy for the moment. I have another ZSQL project (a DVD, Comic Book and Video tracker for my Manga and Anime), so I'll be back. Thanks to everyone who spent time to help out. Ciao! -- Who are you going to believe, me or your own eyes? -- Groucho Marx The Doctor What: Guru to the Gods http://docwhat.gerf.org/ [EMAIL PROTECTED] KF6VNC
[Zope-dev] ZSQL and Normalized databases (or why ZSQL sucks)
Does any one have an example of ZSQL being used witha normalized database? Or is ZSQL just useless? Near as I can tell, between: * Broken type marshalling * Loosing the variable between the form and dtml-if * Inability to handle table.field names for variables * And enough flexibility to work around the above problems This makes ZSQL extreamely nasty, and impossible to use with a normalized database. What's the point? ZSQL sucks, how do I talk to the DB directly? Yeah, I keep almost getting this to be useful. But damn it if I don't keep hitting a brick wall. And there are no complete examples or demos that I can find to load up. Irritatedly yours, DocWhat -- I don't want to achieve immortality through my work... I want to achieve it through not dying. -- Woody Allen The Doctor What: A really hip dude http://docwhat.gerf.org/ [EMAIL PROTECTED] KF6VNC PGP signature
Re: [Zope-dev] ZSQL and Normalized databases (or why ZSQL sucks)
* The Doctor What ([EMAIL PROTECTED]) [010419 11:57]: Does any one have an example of ZSQL being used witha normalized database? Or is ZSQL just useless? Near as I can tell, between: * Broken type marshalling * Loosing the variable between the form and dtml-if * Inability to handle table.field names for variables * And enough flexibility to work around the above problems This makes ZSQL extreamely nasty, and impossible to use with a normalized database. What's the point? ZSQL sucks, how do I talk to the DB directly? Yeah, I keep almost getting this to be useful. But damn it if I don't keep hitting a brick wall. And there are no complete examples or demos that I can find to load up. Irritatedly yours, DocWhat I would like to apologize for being particularly pissy. Things are quite as bad as I say up there... My third point is only half true. I can have SQLTEST specify a column name (aka a field): dtml-sqltest somevarname column="SQLTABLE.sqlfield" type... This makes things work MUCH better. So there are work arounds. But this doesn't excuse this not working: SELECT table.field1, table.field2 FROM and then: dtml-var name="table.field1" --doesn't work dtml-var name="field1" -- does work I know that the '.' has a special meaning, but there should be ways around this if the use wants. I still would love some examples. Do people end up with 4 ZSQL objects per thing they manipulate in their database?: UPDATE, SELECT, INSERT and DELETE? Or do they mix them somehow? Ciao! -- Who are you going to believe, me or your own eyes? -- Groucho Marx The Doctor What: Need I say more?http://docwhat.gerf.org/ [EMAIL PROTECTED] KF6VNC PGP signature
Re: [Zope-dev] ZSQL and Normalized databases (or why ZSQL sucks)
--On 19 April 2001 13:00 -0500 The Doctor What [EMAIL PROTECTED] wrote: * The Doctor What ([EMAIL PROTECTED]) [010419 11:57]: Does any one have an example of ZSQL being used witha normalized database? Or is ZSQL just useless? [intemperate stuff snipped] I wouldn't dream of posting to zope-dev myself until I'd read around a bit. And I've been reading around for more than a year and I still haven't posted ... I still would love some examples. Do people end up with 4 ZSQL objects per thing they manipulate in their database?: UPDATE, SELECT, INSERT and DELETE? Or do they mix them somehow? check out the following: http://www.zope.org/Members/michel/ZB/RelationalDatabases.dtml and http://www.zope.org/Documentation/Guides/ZSQL (the latter is now deprecated but you will find http://www.zope.org/Documentation/Guides/ZSQL-HTML/ZSQL.1.4.4.html useful in the present context - look for sql_delimiter) Paul -- The Library, Tyndall Avenue, Univ. of Bristol, Bristol, BS8 1TJ, UK E-mail: [EMAIL PROTECTED] URL: http://www.bris.ac.uk/ ___ Zope-Dev maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope-dev ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope )
Re: [Zope-dev] ZSQL and Normalized databases (or why ZSQL sucks)
Whats the problem with ZSQL? It calls a sql db with the sql statement, what more could you want. If you want more, use python. Cheers. -- Andy McKay. - Original Message - From: "Paul Browning" [EMAIL PROTECTED] To: "The Doctor What" [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, April 19, 2001 11:48 AM Subject: Re: [Zope-dev] ZSQL and Normalized databases (or why ZSQL sucks) --On 19 April 2001 13:00 -0500 The Doctor What [EMAIL PROTECTED] wrote: * The Doctor What ([EMAIL PROTECTED]) [010419 11:57]: Does any one have an example of ZSQL being used witha normalized database? Or is ZSQL just useless? [intemperate stuff snipped] I wouldn't dream of posting to zope-dev myself until I'd read around a bit. And I've been reading around for more than a year and I still haven't posted ... I still would love some examples. Do people end up with 4 ZSQL objects per thing they manipulate in their database?: UPDATE, SELECT, INSERT and DELETE? Or do they mix them somehow? check out the following: http://www.zope.org/Members/michel/ZB/RelationalDatabases.dtml and http://www.zope.org/Documentation/Guides/ZSQL (the latter is now deprecated but you will find http://www.zope.org/Documentation/Guides/ZSQL-HTML/ZSQL.1.4.4.html useful in the present context - look for sql_delimiter) Paul -- The Library, Tyndall Avenue, Univ. of Bristol, Bristol, BS8 1TJ, UK E-mail: [EMAIL PROTECTED] URL: http://www.bris.ac.uk/ ___ Zope-Dev maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope-dev ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope ) ___ Zope-Dev maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope-dev ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope )
Re: [Zope-dev] ZSQL and Normalized databases (or why ZSQL sucks)
* Andy McKay ([EMAIL PROTECTED]) [010419 14:26]: Whats the problem with ZSQL? It calls a sql db with the sql statement, what more could you want. If you want more, use python. How?!?! Documentation? Examples? Ciao! -- A fail-safe circuit will destroy others. -- Klipstein The Doctor What: Guru to the Godshttp://docwhat.gerf.org/ [EMAIL PROTECTED] KF6VNC PGP signature
Re: [Zope-dev] ZSQL and Normalized databases (or why ZSQL sucks)
The Doctor What wrote: * The Doctor What ([EMAIL PROTECTED]) [010419 11:57]: Does any one have an example of ZSQL being used witha normalized database? Or is ZSQL just useless? Near as I can tell, between: * Broken type marshalling * Loosing the variable between the form and dtml-if I don't understand this. I'm assuming that you are losing values that are not in your argument list. All you have to do is add the arguments. * Inability to handle table.field names for variables * And enough flexibility to work around the above problems This makes ZSQL extreamely nasty, and impossible to use with a normalized database. Database normalization isn't really an issue. It sounds like you're really just having problems with the syntax of joins. What's the point? ZSQL sucks, how do I talk to the DB directly? Grab the Python database adapter and write some python classes or external methods to do what you need. Yeah, I keep almost getting this to be useful. But damn it if I don't keep hitting a brick wall. And there are no complete examples or demos that I can find to load up. Irritatedly yours, DocWhat I would like to apologize for being particularly pissy. Things are quite as bad as I say up there... My third point is only half true. I can have SQLTEST specify a column name (aka a field): dtml-sqltest somevarname column="SQLTABLE.sqlfield" type... This makes things work MUCH better. So there are work arounds. This isn't a work-around, it is the way that it is intended to be used. But this doesn't excuse this not working: SELECT table.field1, table.field2 FROM and then: dtml-var name="table.field1" --doesn't work dtml-var name="field1" -- does work I know that the '.' has a special meaning, but there should be ways around this if the use wants. Again, I think that it's a matter of understanding how ZSQL works, rather than a limitation of ZSQL. If you have column name conflicts, you can always use something like: SELECT a.field as a_field, b.field as b_field FROM ... Then refer to them in dtml like dtml-a_field; or dtml-var name="a_field" I still would love some examples. Do people end up with 4 ZSQL objects per thing they manipulate in their database?: UPDATE, SELECT, INSERT and DELETE? Or do they mix them somehow? I typically wind up with a ZSQL method for each of the CRUD operations. Sometimes I have separate selects methods for more complex joins, if the DTML code gets too ugly when I try to combine them into a single method. Example of a fairly typical join statement (for MySQL database): Arguments- begin_date:date end_date:date="2037-01-01" SELECT search_string, search_result, search_date, result_category, legal_category.name FROM search_log LEFT JOIN legal_category ON search_log.result_category=legal_category.code dtml-sqlgroup required where dtml-sqltest name="begin_date" op="gt" type="nb" column="search_date" dtml-and dtml-sqltest name="end_date" type="nb" op="lt" column="search_date" optional /dtml-sqlgroup Good Luck -Paul ___ Zope-Dev maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope-dev ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope )
Re: [Zope-dev] ZSQL and Normalized databases (or why ZSQL sucks)
* Paul Erickson ([EMAIL PROTECTED]) [010419 17:02]: The Doctor What wrote: * Loosing the variable between the form and dtml-if I don't understand this. I'm assuming that you are losing values that are not in your argument list. All you have to do is add the arguments. That isn't what I mean. Try this (typed in, so it may need to be adjusted): paramfoo=0/param dtml-if expr="foo != 0"Life is good/dtml-if You'll NEVER get Life is good to print out. EVER. This is because ZSQL is setting foo to 0. But dtml-var and dtml-sqlvar both work. * Inability to handle table.field names for variables * And enough flexibility to work around the above problems This makes ZSQL extreamely nasty, and impossible to use with a normalized database. Database normalization isn't really an issue. It sounds like you're really just having problems with the syntax of joins. Nope, can do joins. Been doing joins (mainly inner). Not the problem. The problem is that if I have tables like: Table1 -- id name desc Table2 -- id Table1ID -- Foreign Key thrown in for fun. name desc And I join them, then I MUST rename all the selects using AS: select Table1.id as id1 Table1.name as name1 ...etc Because I can't have zsql put the variables in the caller's namespace as "Table1.id". It puts them in as "id" (without the AS). Fortunately, I found the column for sqltest (which is the other end of ZSQL): dtml-sqltest id1 column="Table1.id" What's the point? ZSQL sucks, how do I talk to the DB directly? Grab the Python database adapter and write some python classes or external methods to do what you need. Can I use them from a python script? If not, what's the point? I mean: External methods are nice to have when you have *no other choice*, but they aren't something I'd want to debug and deal with for object. This makes things work MUCH better. So there are work arounds. This isn't a work-around, it is the way that it is intended to be used. I didn't see a single example of it. The Zope Book didn't mention this at all (except as a single line reference). I would *never* have gone looking for the word column. Why? It's a field! Again, I think that it's a matter of understanding how ZSQL works, rather than a limitation of ZSQL. If you have column name conflicts, you can always use something like: SELECT a.field as a_field, b.field as b_field FROM ... Then refer to them in dtml like dtml-a_field; or dtml-var name="a_field" Right, but you still have to do extra work to get them back into a ZSQL object. column lets you do that. It's got no examples to help out with. It's documented once. I didn't see it. Life is better now. I still would love some examples. Do people end up with 4 ZSQL objects per thing they manipulate in their database?: UPDATE, SELECT, INSERT and DELETE? Or do they mix them somehow? I typically wind up with a ZSQL method for each of the CRUD operations. Sometimes I have separate selects methods for more complex joins, if the DTML code gets too ugly when I try to combine them into a single method. Example of a fairly typical join statement (for MySQL database): Arguments- begin_date:date end_date:date="2037-01-01" SELECT search_string, search_result, search_date, result_category, legal_category.name FROM search_log LEFT JOIN legal_category ON search_log.result_category=legal_category.code dtml-sqlgroup required where dtml-sqltest name="begin_date" op="gt" type="nb" column="search_date" dtml-and dtml-sqltest name="end_date" type="nb" op="lt" column="search_date" optional /dtml-sqlgroup Hey! An example! Good Luck Thanks. So I'm still having trouble. I can't get any of the list examples to work. I build a select multiple list, and then try to dtml-in on it. It doesn't seem to work. REQUEST shows in it th other and form namespaces, as a list, but I can't actually dtml-var it or anything. Example: qdtml-var dalistp br dtml-in VirtualRootPhysicalPath ddtml-var sequence-itemb /dtml-in hr dtml-var REQUEST If you call this with this URL (substitute as needed): http://docwhat.gerf.org:9673/links/test?dalist%0D%0A%3Alist=1dalist%0D%0A%3Alist=3dalist%0D%0A%3Alist=8 Which is the URL pattern formed by a SELECT name="dalist:list" multiple Then you get errors on dalist. The examples at http://www.zope.org/Members/AlexR/SelectionLists Don't work as exactl above. Help? Ciao! -- Outside of a dog, a book is a man's best friend. Inside a dog it's too dark to read. -- Groucho Marx The Doctor What: Need I say more?http://docwhat.gerf.org/ [EMAIL PROTECTED] KF6VNC PGP signature
Re: [Zope-dev] ZSQL and Normalized databases (or why ZSQL sucks)
Thanks. So I'm still having trouble. I can't get any of the list examples to work. I build a select multiple list, and then try to dtml-in on it. It doesn't seem to work. REQUEST shows in it th other and form namespaces, as a list, but I can't actually dtml-var it or anything. Example: qdtml-var dalistp br dtml-in VirtualRootPhysicalPath ddtml-var sequence-itemb /dtml-in hr dtml-var REQUEST If you call this with this URL (substitute as needed): http://docwhat.gerf.org:9673/links/test?dalist%0D%0A%3Alist=1dalist%0D%0A%3Alist=3dalist%0D%0A%3Alist=8 Which is the URL pattern formed by a SELECT name="dalist:list" multiple Then you get errors on dalist. The examples at http://www.zope.org/Members/AlexR/SelectionLists Don't work as exactl above. Help? My problem. I decoded the %0D and %0A (newline and space) and figured out that I foo-barred a bit of code. I'm happy again. It works great with only the %3A. It *is* weird that it looked correct on the REQUEST output. :-S Thanks anyway. Ciao! -- If you want to make God laugh, tell him about your plans. -- Woody Allen The Doctor What: Not that 'who' guy http://docwhat.gerf.org/ [EMAIL PROTECTED] KF6VNC PGP signature
Re: [Zope-dev] ZSQL and Normalized databases (or why ZSQL sucks)
The Doctor What wrote: * Paul Erickson ([EMAIL PROTECTED]) [010419 17:02]: The Doctor What wrote: * Loosing the variable between the form and dtml-if I don't understand this. I'm assuming that you are losing values that are not in your argument list. All you have to do is add the arguments. That isn't what I mean. Try this (typed in, so it may need to be adjusted): paramfoo=0/param dtml-if expr="foo != 0"Life is good/dtml-if You'll NEVER get Life is good to print out. EVER. This is because ZSQL is setting foo to 0. But dtml-var and dtml-sqlvar both work. Reason: foo=0 is actually foo='0'. foo:int=0 should work like you want. Database normalization isn't really an issue. It sounds like you're really just having problems with the syntax of joins. Nope, can do joins. Been doing joins (mainly inner). Not the problem. The problem is that if I have tables like: Table1 -- id name desc Table2 -- id Table1ID -- Foreign Key thrown in for fun. name desc And I join them, then I MUST rename all the selects using AS: select Table1.id as id1 Table1.name as name1 ...etc Because I can't have zsql put the variables in the caller's namespace as "Table1.id". It puts them in as "id" (without the AS). Fortunately, I found the column for sqltest (which is the other end of ZSQL): dtml-sqltest id1 column="Table1.id" This is a function of your field naming convention more than anything. Granted you don't always have control over that. I am surprised that name="spam.eggs" doesn't work on sqltests (it works everywhere else). Are you explicitly saying name="..."? that could be the problem, bare quoting assumes expr="..." where periods are significant. Can I use them from a python script? If not, what's the point? I mean: External methods are nice to have when you have *no other choice*, but they aren't something I'd want to debug and deal with for object. Python scripts are quite helpful for calling ZSQL methods. Just remember to pass REQUEST or the arguments explicitly. hth, -- | Casey Duncan | Kaivo, Inc. | [EMAIL PROTECTED] `-- ___ Zope-Dev maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope-dev ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope )
Re: [Zope-dev] ZSQL and Normalized databases (or why ZSQL sucks)
* Casey Duncan ([EMAIL PROTECTED]) [010419 17:45]: Reason: foo=0 is actually foo='0'. foo:int=0 should work like you want. Same behaviour. I opened a bug in the collector a while ago about this: http://classic.zope.org:8080/Collector/2053/view A related bug: http://classic.zope.org:8080/Collector/2045/view This is a function of your field naming convention more than anything. Granted you don't always have control over that. I am surprised that name="spam.eggs" doesn't work on sqltests (it works everywhere else). Are you explicitly saying name="..."? that could be the problem, bare quoting assumes expr="..." where periods are significant. It works with sqltest, actually. But since a lot of people have examples of chaining the namespaces, this breaks that and adds more work since you can't alias (via AS) to dotted names. Can I use them from a python script? If not, what's the point? I mean: External methods are nice to have when you have *no other choice*, but they aren't something I'd want to debug and deal with for object. Python scripts are quite helpful for calling ZSQL methods. Just remember to pass REQUEST or the arguments explicitly. Yeah, I like that, but it's still not perfect, and the docs are incomplete and scattered. We need a system to pull together like documents at zope.org Ciao! -- Line Printer paper is strongest at the perforations. The Doctor What: Not that 'who' guy http://docwhat.gerf.org/ [EMAIL PROTECTED] KF6VNC PGP signature
