I think I brought this up during the 2.0 release, but the situation has
happened again. There is a bit of semi-complex sql which needs to be
executed as part of the 3.0 database upgrade sequence and I'm unsure of
what our ideal approach should be.
The sql that's required simply sets the value of the new
weblogentry.locale column to the value of website.locale for each entry.
this can easily (and efficiently) be done in a single statement on
many databases, but since it requires an update and a query in the same
statement the syntax varies for each database. i know these 2 examples ...
mysql:
update weblogentry,website set weblogentry.locale = website.locale where
weblogentry.websiteid = website.id and weblogentry.locale is NULL;
postgres:
update weblogentry set locale = website.locale from website where
weblogentry.websiteid = website.id and weblogentry.locale is NULL;
so, the question is, in this scenario how do we want to handle this?
there are 2 options ...
1. use our standard UpgradeDatabase class and do the operation in a
generic sql fashion that would work on all databases? the benefit is
that it's a single method that works on all dbs, the drawback is that
it's slow. the method to do this would require doing a couple queries
to fetch the set of website locales and entries first, then iterate over
the entries and do a sql update for each one.
2. use a database specific sql statement to do the job? the benefit is
speed, and the drawback is that we then need to figure out this
statement for each db we support and put it in the specific db scripts.
either way works, but i suppose #1 is potentially less painful. also
take into consideration that the occurrence of these situations is
limited and most likely only happens for major releases. presumably if
we have someone on the list actually testing on each db then it should
be fairly easy to get a variant of the statement for each db pretty quickly.
thoughts? opinions?
-- Allen
