This might be a case where we could define a method in the DataMapperQuery interface that does the SQL update, given the name of the query and the parameters for the update. The implementation of the Query could look up the update in metadata and if not found, indicate that no query was found. Then the caller could drop into "slow but universal" mode.
Craig On Aug 31, 2006, at 5:03 PM, Allen Gilliland wrote:
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
Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp!
smime.p7s
Description: S/MIME cryptographic signature
