Hi there, i'm using ZPyGreSQL successfully for my database. To keep it simple i don't want to use MySQL for ZBabel. ZBabel though uses some MySQL specific syntax which i am unsuccessfully translating to PostgreSQL syntax: Description "md5Hash" are the same fore entries with different "lang" fields. They are used to translate depending on selected language. SELECT Phrases.* FROM Phrases LEFT JOIN Phrases as b ON Phrases.md5Hash = b.md5Hash AND Phrases.lang <> b.lang WHERE b.md5Hash is NULL AND Phrases.lang=<dtml-sqlvar srcLang type=nb> What does the whole thing do anyway? How can you join "Phrases.md5Hash = b.mdf5Hash", while "md5Hash is NULL"? What is different in the LEFT JOIN ... ON ... statement to a normal WHERE clause? My try for translation to PostgreSQL (which I assume is completely ignorant): SELECT Phrases.* FROM Phrases as b WHERE Phrases.md5Hash *= b.md5Hash AND Phrases.lang <> b.lang AND b.md5Hash is NULL AND Phrases.lang=<dtml-sqlvar srcLang type=nb> Has anybody ported ZBabel already? Thanks for help Olaf -- soli-con Engineering Zanger Dipl.-Ing. (FH) Olaf Marc Zanger Lorrainestrasse 23 3013 Bern / Switzerland Fon: +41-31-332 9782 Mob: +41-76-572 9782 mailto:info@soli-con.com mailto:olaf.zanger@soli-con.com http://www.soli-con.com
Olaf, The left join is a notation that says to get all records from the left hand side of the join and only those records where the criteria matches from the right hand side. It is btw a part of most RDBMS engines I've ever used, so it's not that unusual a syntax. You can also do something similar by using unions. hth Phil phil.harris@zope.co.uk ----- Original Message ----- From: "Olaf Zanger" <olaf.zanger@soli-con.com> To: "Zope Mailinglist" <zope@zope.org> Cc: <akm@theinternet.com.au> Sent: Thursday, January 04, 2001 9:50 AM Subject: [Zope] ZBabel translation to Postgres
Hi there,
i'm using ZPyGreSQL successfully for my database. To keep it simple i don't want to use MySQL for ZBabel.
ZBabel though uses some MySQL specific syntax which i am unsuccessfully translating to PostgreSQL syntax: Description "md5Hash" are the same fore entries with different "lang" fields. They are used to translate depending on selected language.
SELECT Phrases.*
FROM Phrases
LEFT JOIN Phrases as b
ON Phrases.md5Hash = b.md5Hash AND Phrases.lang <> b.lang
WHERE b.md5Hash is NULL AND Phrases.lang=<dtml-sqlvar srcLang type=nb>
What does the whole thing do anyway? How can you join "Phrases.md5Hash = b.mdf5Hash", while "md5Hash is NULL"? What is different in the LEFT JOIN ... ON ... statement to a normal WHERE clause? My try for translation to PostgreSQL (which I assume is completely ignorant):
SELECT Phrases.*
FROM Phrases as b
WHERE Phrases.md5Hash *= b.md5Hash AND Phrases.lang <> b.lang AND
b.md5Hash is NULL AND Phrases.lang=<dtml-sqlvar srcLang type=nb>
Has anybody ported ZBabel already? Thanks for help
Olaf
-- soli-con Engineering Zanger Dipl.-Ing. (FH) Olaf Marc Zanger Lorrainestrasse 23 3013 Bern / Switzerland Fon: +41-31-332 9782 Mob: +41-76-572 9782 mailto:info@soli-con.com mailto:olaf.zanger@soli-con.com http://www.soli-con.com
hi Phil,
The left join is a notation that says to get all records from the left hand side of the join and only those records where the criteria matches from the right hand side.
It is btw a part of most RDBMS engines I've ever used, so it's not that unusual a syntax.
You can also do something similar by using unions. thanks a lot for that,
do you have any suggestion (principally) how this union would look like thanks olaf -- soli-con Engineering Zanger Dipl.-Ing. (FH) Olaf Marc Zanger Lorrainestrasse 23 3013 Bern / Switzerland Fon: +41-31-332 9782 Mob: +41-76-572 9782 mailto:info@soli-con.com mailto:olaf.zanger@soli-con.com http://www.soli-con.com
+-------[ Olaf Zanger ]---------------------- | Hi there, ... | What does the whole thing do anyway? Finds all the phrases that don't have a translation for the language selected. It's hard to find things that don't exist in databases without nested selects which MySQL did not have at the time. MySQL was chosen precisely because it is restricted, and is basically the lowest common denominator when it comes to SQL. I do recall however, that postgres only implements INNER and OUTER joins although I don't know the status of the JOIN stuff in postgres at this time. | Has anybody ported ZBabel already? Someone was going to send me patches for postgres, but, I never received them. I'll happily integrate patches, although, automatically selecting the right query based on database type will be required so that several releases aren't required. I don't think that that's a hard thing to achieve. -- Totally Holistic Enterprises Internet| P:+61 7 3870 0066 | Andrew Milton The Internet (Aust) Pty Ltd | F:+61 7 3870 4477 | ACN: 082 081 472 ABN: 83 082 081 472 | M:+61 416 022 411 | Carpe Daemon PO Box 837 Indooroopilly QLD 4068 |akm@theinternet.com.au|
Hi! On Thu, 4 Jan 2001, Andrew Kenneth Milton wrote:
I do recall however, that postgres only implements INNER and OUTER joins although I don't know the status of the JOIN stuff in postgres at this time.
AFAIR they (especially Bruce Momjian) always suggested ti emulate LEFT JOIN with UNION. They said it'd pretty easy... Oleg. ---- Oleg Broytmann http://www.zope.org/Members/phd/ phd@phd.pp.ru Programmers don't die, they just GOSUB without RETURN.
The following references from search of huge numbers for "left join" at www.postgresql.org may be helpful: http://www.postgresql.org/mhonarc/pgsql-sql/2000-03/msg00023.html http://www.postgresql.org/mhonarc/pgsql-hackers/2000-12/msg00634.html http://www.postgresql.org/mhonarc/pgsql-hackers/2000-12/msg00656.html http://www.postgresql.org/mhonarc/pgsql-general/2000-12/msg00194.html Summary: 1) Easy to work around 2) There was a bug 3) It's been fixed 4) Comprehensive implementation in 7.1(devel) Many more messages not looked at so above summary may be wrong -----Original Message----- From: zope-admin@zope.org [mailto:zope-admin@zope.org]On Behalf Of Andrew Kenneth Milton Sent: Thursday, January 04, 2001 9:59 PM To: Olaf Zanger Cc: Zope Mailinglist; akm@theinternet.com.au Subject: [Zope] Re: ZBabel translation to Postgres +-------[ Olaf Zanger ]---------------------- | Hi there, ... | What does the whole thing do anyway? Finds all the phrases that don't have a translation for the language selected. It's hard to find things that don't exist in databases without nested selects which MySQL did not have at the time. MySQL was chosen precisely because it is restricted, and is basically the lowest common denominator when it comes to SQL. I do recall however, that postgres only implements INNER and OUTER joins although I don't know the status of the JOIN stuff in postgres at this time. | Has anybody ported ZBabel already? Someone was going to send me patches for postgres, but, I never received them. I'll happily integrate patches, although, automatically selecting the right query based on database type will be required so that several releases aren't required. I don't think that that's a hard thing to achieve. -- Totally Holistic Enterprises Internet| P:+61 7 3870 0066 | Andrew Milton The Internet (Aust) Pty Ltd | F:+61 7 3870 4477 | ACN: 082 081 472 ABN: 83 082 081 472 | M:+61 416 022 411 | Carpe Daemon PO Box 837 Indooroopilly QLD 4068 |akm@theinternet.com.au| _______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
hi andrew, thanks for the description. i figured it as far as that: you need in postgresql (this is tested only from zope with zsql method, since some other things don't work in the ZBabel Produkt with postgresql): sqlGetPhrasesToUpdate: select distinct Phrases.* WHERE md5Hash not in ( select c.md5Hash from Phrases as c, Phrases as d where c.md5Hash = d.md5Hash and c.lang <> d.lang and c.lang='<dtml-var srcLang>' ) and lang <>'<dtml-var srcLang>' sqlNewPhrase: insert into Phrases (phrase_id,lang,md5Hash,renderPath,renderText) values ( nextval('phrases_phrase_id_seq'), <dtml-sqlvar lang type=nb>, <dtml-sqlvar md5Hash type=nb>, <dtml-sqlvar renderPath type=nb>, <dtml-sqlvar renderText type=nb> ) all other sqlFunctions seam to work (out of zope ZSQL method) i attache the ZBabel.sql for postgres. the examples are handgenerated so the hashes are fake. TAKE CARE until now this just fakes the ability of ZBabel on Postgres on a ZSQL Method level for testing purposes. I get errors: when i first want to view a page with the <dtml-babel src=uk dst=de> test</dtml-babel> pg.error error value: error: attribute 'phrases_phrase_id_seq' not found probably that helps anybody olaf
| What does the whole thing do anyway?
Finds all the phrases that don't have a translation for the language selected. It's hard to find things that don't exist in databases without nested selects which MySQL did not have at the time.
MySQL was chosen precisely because it is restricted, and is basically the lowest common denominator when it comes to SQL.
I do recall however, that postgres only implements INNER and OUTER joins although I don't know the status of the JOIN stuff in postgres at this time.
| Has anybody ported ZBabel already?
Someone was going to send me patches for postgres, but, I never received them.
I'll happily integrate patches, although, automatically selecting the right query based on database type will be required so that several releases aren't required. I don't think that that's a hard thing to achieve.
-- soli-con Engineering Zanger Dipl.-Ing. (FH) Olaf Marc Zanger Lorrainestrasse 23 3013 Bern / Switzerland Fon: +41-31-332 9782 Mob: +41-76-572 9782 mailto:info@soli-con.com mailto:olaf.zanger@soli-con.com http://www.soli-con.com
participants (5)
-
Albert Langer -
Andrew Kenneth Milton -
Olaf Zanger -
Oleg Broytmann -
Phil Harris