Re: [Zope] ZBabel translation to Postgres
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 [EMAIL PROTECTED] - Original Message - From: "Olaf Zanger" [EMAIL PROTECTED] To: "Zope Mailinglist" [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] 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:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] http://www.soli-con.com ___ Zope maillist - [EMAIL PROTECTED] 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 )
Re: [ZOPE] ZBabel translation to Postgres
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:[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]
http://www.soli-con.com
phrases.sql
begin:vcard
n:Zanger;Olaf Marc
tel;cell:+41-76-572 9782
tel;work:+41-31-332 9782
x-mozilla-html:FALSE
url:www.soli-con.com
org:soli-con Engineering Zanger
adr:;;Lorrainestrasse 23;Bern;BE;3013;Switzerland
version:2.1
email;internet:[EMAIL PROTECTED]
title:Dipl.-Ing.
note;quoted-printable:IT-Consulting=0D=0AEmbedded Systems=0D=0AEnergy Systems=0D=0AOpen Source Solutions=0D=0A
x-mozilla-cpt:;-32176
fn:Olaf Zanger
end:vcard
Re: [Zope] ZBabel translation to Postgres
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:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] http://www.soli-con.com begin:vcard n:Zanger;Olaf Marc tel;cell:+41-76-572 9782 tel;work:+41-31-332 9782 x-mozilla-html:FALSE url:www.soli-con.com org:soli-con Engineering Zanger adr:;;Lorrainestrasse 23;Bern;BE;3013;Switzerland version:2.1 email;internet:[EMAIL PROTECTED] title:Dipl.-Ing. note;quoted-printable:IT-Consulting=0D=0AEmbedded Systems=0D=0AEnergy Systems=0D=0AOpen Source Solutions=0D=0A x-mozilla-cpt:;-32176 fn:Olaf Zanger end:vcard
