Author: shawkins Date: 2010-12-03 15:32:45 -0500 (Fri, 03 Dec 2010) New Revision: 2756 Modified: trunk/engine/src/main/java/org/teiid/query/resolver/ProcedureContainerResolver.java trunk/engine/src/main/java/org/teiid/query/validator/UpdateValidator.java trunk/engine/src/main/java/org/teiid/query/validator/ValidationVisitor.java trunk/engine/src/main/resources/org/teiid/query/i18n.properties trunk/engine/src/test/java/org/teiid/query/processor/TestTriggerActions.java trunk/engine/src/test/java/org/teiid/query/validator/TestUpdateValidator.java Log: TEIID-1351 differentiating between update procedures and trigger actions Modified: trunk/engine/src/main/java/org/teiid/query/resolver/ProcedureContainerResolver.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/resolver/ProcedureContainerResolver.java 2010-12-03 18:49:01 UTC (rev 2755) +++ trunk/engine/src/main/java/org/teiid/query/resolver/ProcedureContainerResolver.java 2010-12-03 20:32:45 UTC (rev 2756) @@ -249,24 +249,23 @@ } public static UpdateInfo getUpdateInfo(GroupSymbol group, QueryMetadataInterface metadata) throws QueryMetadataException, TeiidComponentException, QueryResolverException { - //if this is not inherently updatable, just return null - if(group.isTempGroupSymbol() || !metadata.isVirtualGroup(group.getMetadataID()) || ( - metadata.getUpdatePlan(group.getMetadataID()) != null && - metadata.getDeletePlan(group.getMetadataID()) != null && - metadata.getInsertPlan(group.getMetadataID()) != null)) { + //if this is not a view, just return null + if(group.isTempGroupSymbol() || !metadata.isVirtualGroup(group.getMetadataID())) { return null; } + String updatePlan = metadata.getUpdatePlan(group.getMetadataID()); + String deletePlan = metadata.getDeletePlan(group.getMetadataID()); + String insertPlan = metadata.getInsertPlan(group.getMetadataID()); + + UpdateInfo info = (UpdateInfo)metadata.getFromMetadataCache(group.getMetadataID(), "UpdateInfo"); //$NON-NLS-1$ if (info == null) { - List<ElementSymbol> elements = ResolverUtil.resolveElementsInGroup(group, metadata); - - UpdateValidator validator = new UpdateValidator(metadata, - metadata.getUpdatePlan(group.getMetadataID()) == null, - metadata.getDeletePlan(group.getMetadataID()) == null, - metadata.getInsertPlan(group.getMetadataID()) == null); - validator.validate(UpdateProcedureResolver.getQueryTransformCmd(group, metadata), elements); + UpdateValidator validator = new UpdateValidator(metadata, updatePlan, deletePlan, insertPlan); info = validator.getUpdateInfo(); + if (info.isInherentDelete() || info.isInherentInsert() || info.isInherentUpdate()) { + validator.validate(UpdateProcedureResolver.getQueryTransformCmd(group, metadata), elements); + } metadata.addToMetadataCache(group.getMetadataID(), "UpdateInfo", info); //$NON-NLS-1$ } return info; Modified: trunk/engine/src/main/java/org/teiid/query/validator/UpdateValidator.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/validator/UpdateValidator.java 2010-12-03 18:49:01 UTC (rev 2755) +++ trunk/engine/src/main/java/org/teiid/query/validator/UpdateValidator.java 2010-12-03 20:32:45 UTC (rev 2756) @@ -30,13 +30,17 @@ import org.teiid.api.exception.query.QueryMetadataException; import org.teiid.core.TeiidComponentException; +import org.teiid.core.util.StringUtil; +import org.teiid.language.SQLConstants; import org.teiid.query.QueryPlugin; import org.teiid.query.metadata.QueryMetadataInterface; import org.teiid.query.metadata.SupportConstants; import org.teiid.query.resolver.util.ResolverUtil; import org.teiid.query.sql.lang.Command; import org.teiid.query.sql.lang.Query; +import org.teiid.query.sql.lang.SetQuery; import org.teiid.query.sql.lang.UnaryFromClause; +import org.teiid.query.sql.lang.SetQuery.Operation; import org.teiid.query.sql.symbol.ElementSymbol; import org.teiid.query.sql.symbol.Expression; import org.teiid.query.sql.symbol.GroupSymbol; @@ -51,6 +55,21 @@ */ public class UpdateValidator { + public enum UpdateType { + /** + * The default handling should be used + */ + INHERENT, + /** + * A procedure handler has been defined + */ + UPDATE_PROCEDURE, + /** + * An instead of trigger (TriggerAction) has been defined + */ + INSTEAD_OF + } + public static class UpdateMapping { private GroupSymbol group; private GroupSymbol correlatedName; @@ -83,11 +102,11 @@ private Map<String, UpdateMapping> updatableGroups = new HashMap<String, UpdateMapping>(); private boolean isSimple = true; private UpdateMapping deleteTarget; - private boolean inherentUpdate; + private UpdateType updateType; private boolean updateValidationError; - private boolean inherentDelete; + private UpdateType deleteType; private boolean deleteValidationError; - private boolean inherentInsert; + private UpdateType insertType; private boolean insertValidationError; private Query view; @@ -104,17 +123,25 @@ } public boolean isInherentDelete() { - return inherentDelete; + return deleteType == UpdateType.INHERENT; } public boolean isInherentInsert() { - return inherentInsert; + return insertType == UpdateType.INHERENT; } public boolean isInherentUpdate() { - return inherentUpdate; + return updateType == UpdateType.INHERENT; } + public UpdateType getUpdateType() { + return updateType; + } + + public UpdateType getDeleteType() { + return deleteType; + } + public UpdateMapping findUpdateMapping(Collection<ElementSymbol> updateCols, boolean insert) { for (UpdateMapping entry : getUpdatableGroups().values()) { if (((insert && entry.insertAllowed) || (!insert && entry.updateAllowed)) && entry.updatableViewSymbols.keySet().containsAll(updateCols)) { @@ -146,12 +173,24 @@ private ValidatorReport report = new ValidatorReport(); private UpdateInfo updateInfo = new UpdateInfo(); - public UpdateValidator(QueryMetadataInterface qmi, boolean inherentUpdate, boolean inherentDelete, boolean inherentInsert) { + public UpdateValidator(QueryMetadataInterface qmi, String updatePlan, String deletePlan, String insertPlan) { this.metadata = qmi; - this.updateInfo.inherentDelete = inherentDelete; - this.updateInfo.inherentInsert = inherentInsert; - this.updateInfo.inherentUpdate = inherentUpdate; + this.updateInfo.deleteType = determineType(deletePlan); + this.updateInfo.insertType = determineType(insertPlan); + this.updateInfo.updateType = determineType(updatePlan); } + + private UpdateType determineType(String plan) { + UpdateType type = UpdateType.INHERENT; + if (plan != null) { + if (StringUtil.startsWithIgnoreCase(plan, SQLConstants.Reserved.CREATE)) { + type = UpdateType.UPDATE_PROCEDURE; + } else { + type = UpdateType.INSTEAD_OF; + } + } + return type; + } public UpdateInfo getUpdateInfo() { return updateInfo; @@ -169,6 +208,24 @@ } public void validate(Command command, List<ElementSymbol> viewSymbols) throws QueryMetadataException, TeiidComponentException { + if (command instanceof SetQuery) { + SetQuery setQuery = (SetQuery)command; + if (setQuery.getLimit() != null) { + handleValidationError(QueryPlugin.Util.getString("ERR.015.012.0013"), true, true, true); //$NON-NLS-1$ + return; + } + if (setQuery.getOperation() != Operation.UNION || !setQuery.isAll()) { + handleValidationError(QueryPlugin.Util.getString("ERR.015.012.0001"), true, true, true); //$NON-NLS-1$ + return; + } + //TOOD each branch should return it's own info? + /*this.validate(setQuery.getLeftQuery(), viewSymbols); + this.validate(setQuery.getRightQuery(), viewSymbols); + if (!this.updateInfo.insertValidationError) { + handleValidationError(QueryPlugin.Util.getString("ERR.015.012.0018"), false, true, false); //$NON-NLS-1$ + }*/ + } + if (!(command instanceof Query)) { handleValidationError(QueryPlugin.Util.getString("ERR.015.012.0001"), true, true, true); //$NON-NLS-1$ return; @@ -191,8 +248,6 @@ return; } - updateInfo.view = query; - if (query.getLimit() != null) { handleValidationError(QueryPlugin.Util.getString("ERR.015.012.0013"), true, true, true); //$NON-NLS-1$ return; @@ -203,6 +258,8 @@ return; } + updateInfo.view = query; + List<SingleElementSymbol> projectedSymbols = query.getSelect().getProjectedSymbols(); for (int i = 0; i < projectedSymbols.size(); i++) { @@ -256,7 +313,7 @@ if (!allGroups.isEmpty()) { setUpdateFlags(allGroups.iterator().next()); } - } else if (this.updateInfo.inherentUpdate || this.updateInfo.inherentDelete) { + } else if (this.updateInfo.updateType == UpdateType.INHERENT || this.updateInfo.deleteType == UpdateType.INHERENT) { for (GroupSymbol groupSymbol : allGroups) { UpdateMapping info = updateInfo.updatableGroups.get(groupSymbol.getCanonicalName()); if (info == null) { @@ -280,13 +337,13 @@ updatable |= info.updateAllowed; insertable |= info.insertAllowed; } - if ((this.updateInfo.inherentInsert && !insertable)) { + if ((this.updateInfo.insertType == UpdateType.INHERENT && !insertable)) { handleValidationError(QueryPlugin.Util.getString("ERR.015.012.0015"), false, true, false); //$NON-NLS-1$ } - if (this.updateInfo.inherentUpdate && !updatable) { + if (this.updateInfo.updateType == UpdateType.INHERENT && !updatable) { handleValidationError(QueryPlugin.Util.getString("ERR.015.012.0005"), true, false, true); //$NON-NLS-1$ } - if (this.updateInfo.inherentDelete && this.updateInfo.deleteTarget == null) { + if (this.updateInfo.deleteType == UpdateType.INHERENT && this.updateInfo.deleteTarget == null) { if (this.updateInfo.isSimple) { this.updateInfo.deleteTarget = this.updateInfo.updatableGroups.values().iterator().next(); } else { @@ -330,7 +387,7 @@ || metadata.elementSupports(element.getMetadataID(), SupportConstants.Element.AUTO_INCREMENT)) { return true; } - if (this.updateInfo.inherentInsert) { + if (this.updateInfo.insertType == UpdateType.INHERENT) { report.handleValidationError(QueryPlugin.Util.getString("ERR.015.012.0010", element, element.getGroupSymbol())); //$NON-NLS-1$ } return false; Modified: trunk/engine/src/main/java/org/teiid/query/validator/ValidationVisitor.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/validator/ValidationVisitor.java 2010-12-03 18:49:01 UTC (rev 2755) +++ trunk/engine/src/main/java/org/teiid/query/validator/ValidationVisitor.java 2010-12-03 20:32:45 UTC (rev 2756) @@ -76,6 +76,7 @@ import org.teiid.query.sql.lang.Option; import org.teiid.query.sql.lang.OrderBy; import org.teiid.query.sql.lang.OrderByItem; +import org.teiid.query.sql.lang.ProcedureContainer; import org.teiid.query.sql.lang.Query; import org.teiid.query.sql.lang.QueryCommand; import org.teiid.query.sql.lang.SPParameter; @@ -137,6 +138,7 @@ import org.teiid.query.sql.visitor.SQLStringVisitor; import org.teiid.query.sql.visitor.ValueIteratorProviderCollectorVisitor; import org.teiid.query.validator.UpdateValidator.UpdateInfo; +import org.teiid.query.validator.UpdateValidator.UpdateType; import org.teiid.query.xquery.saxon.SaxonXQueryExpression; import org.teiid.translator.SourceSystemFunctions; @@ -201,24 +203,20 @@ validateNoXMLUpdates(obj); GroupSymbol group = obj.getGroup(); validateGroupSupportsUpdate(group); - if (obj.getUpdateInfo() == null || !obj.getUpdateInfo().isInherentDelete()) { - Criteria crit = obj.getCriteria(); - validateVirtualUpdate(group, crit); - } + Criteria crit = obj.getCriteria(); + validateVirtualUpdate(obj, crit); } - private void validateVirtualUpdate(GroupSymbol group, + private void validateVirtualUpdate(ProcedureContainer container, Criteria crit) { - if (crit == null) { - return; + if (crit == null || container.getUpdateInfo() == null) { + return; } - try { - if (getMetadata().isVirtualGroup(group.getMetadataID()) && - !ValueIteratorProviderCollectorVisitor.getValueIteratorProviders(crit).isEmpty()) { + if ((container.getType() == Command.TYPE_UPDATE && container.getUpdateInfo().getUpdateType() == UpdateType.UPDATE_PROCEDURE) + || (container.getType() == Command.TYPE_DELETE && container.getUpdateInfo().getDeleteType() == UpdateType.UPDATE_PROCEDURE)) { + if (!ValueIteratorProviderCollectorVisitor.getValueIteratorProviders(crit).isEmpty()) { handleValidationError(QueryPlugin.Util.getString("ValidationVisitor.virtual_update_subquery"), crit); //$NON-NLS-1$ } - } catch (TeiidException e) { - handleException(e); } } @@ -335,9 +333,7 @@ validateNoXMLUpdates(obj); validateGroupSupportsUpdate(obj.getGroup()); validateUpdate(obj); - if (obj.getUpdateInfo() == null || !obj.getUpdateInfo().isInherentUpdate()) { - validateVirtualUpdate(obj.getGroup(), obj.getCriteria()); - } + validateVirtualUpdate(obj, obj.getCriteria()); } public void visit(Into obj) { @@ -911,7 +907,6 @@ protected void validateUpdate(Update update) { try { UpdateInfo info = update.getUpdateInfo(); - boolean updatableView = info != null && info.isInherentUpdate(); // list of elements that are being updated for (SetClause entry : update.getChangeList().getClauses()) { @@ -942,7 +937,7 @@ if(((Constant)value).isNull() && ! getMetadata().elementSupports(elementID.getMetadataID(), SupportConstants.Element.NULL)) { handleValidationError(QueryPlugin.Util.getString("ERR.015.012.0060", SQLStringVisitor.getSQLString(elementID)), elementID); //$NON-NLS-1$ }// end of if - } else if (!updatableView && getMetadata().isVirtualGroup(update.getGroup().getMetadataID()) && !EvaluatableVisitor.willBecomeConstant(value)) { + } else if (info != null && info.getUpdateType() == UpdateType.UPDATE_PROCEDURE && getMetadata().isVirtualGroup(update.getGroup().getMetadataID()) && !EvaluatableVisitor.willBecomeConstant(value)) { // If this is an update on a virtual group, verify that no elements are in the right side Collection<ElementSymbol> elements = ElementCollectorVisitor.getElements(value, false); for (ElementSymbol element : elements) { @@ -952,7 +947,7 @@ } } } - if (updatableView) { + if (info != null && info.isInherentUpdate()) { Set<ElementSymbol> updateCols = update.getChangeList().getClauseMap().keySet(); if (info.findUpdateMapping(updateCols, false) == null) { handleValidationError(QueryPlugin.Util.getString("ValidationVisitor.nonUpdatable", updateCols), update); //$NON-NLS-1$ Modified: trunk/engine/src/main/resources/org/teiid/query/i18n.properties =================================================================== --- trunk/engine/src/main/resources/org/teiid/query/i18n.properties 2010-12-03 18:49:01 UTC (rev 2755) +++ trunk/engine/src/main/resources/org/teiid/query/i18n.properties 2010-12-03 20:32:45 UTC (rev 2756) @@ -182,6 +182,7 @@ ERR.015.012.0015 = The query defining an updatable view has no valid target for INSERTs. ERR.015.012.0016 = Variable {0} not assigned any value in this procedure. ERR.015.012.0017 = Variables declared the procedure''s DeclareStatement cannot be one of the special variables: {0}, {1} and {2}. +ERR.015.012.0018 = Inherent INSERT is not possible on a view defined by a UNION. ERR.015.012.0019 = TranslateCriteria cannot be used in on an if or while statement. ERR.015.012.0020 = Elements used on the criteria of an if or while statement should be variables declared in the procedure, virtual group elements can be used in the HAS Criteria ERR.015.012.0021 = Element being translated in the WITH clause not among the elements on the ON clause of the TranslateCriteria. Modified: trunk/engine/src/test/java/org/teiid/query/processor/TestTriggerActions.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/processor/TestTriggerActions.java 2010-12-03 18:49:01 UTC (rev 2755) +++ trunk/engine/src/test/java/org/teiid/query/processor/TestTriggerActions.java 2010-12-03 20:32:45 UTC (rev 2756) @@ -120,5 +120,26 @@ helpProcess(plan, context, dm, expected); assertEquals("UPDATE pm1.g1 SET e2 = 5 WHERE e2 = 2", dm.getQueries().get(0)); } + + @Test public void testUpdateWithNonConstant() throws Exception { + TransformationMetadata metadata = TestUpdateValidator.example1(); + TestUpdateValidator.createView("select 1 as x, 2 as y", metadata, "gx"); + Table t = metadata.getMetadataStore().getSchemas().get("vm1").getTables().get("gx"); + t.setDeletePlan(""); + t.setUpdatePlan("FOR EACH ROW BEGIN update pm1.g1 set e2 = new.y where e2 = old.y; END"); + t.setInsertPlan(""); + + String sql = "update gx set y = x"; + + FakeDataManager dm = new FakeDataManager(); + FakeDataStore.addTable("pm1.g1", dm, metadata); + + CommandContext context = createCommandContext(); + BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); + ProcessorPlan plan = TestProcessor.helpGetPlan(TestResolver.helpResolve(sql, metadata, null), metadata, new DefaultCapabilitiesFinder(caps), context); + List[] expected = new List[] {Arrays.asList(1)}; + helpProcess(plan, context, dm, expected); + assertEquals("UPDATE pm1.g1 SET e2 = 1 WHERE e2 = 2", dm.getQueries().get(0)); + } } Modified: trunk/engine/src/test/java/org/teiid/query/validator/TestUpdateValidator.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/validator/TestUpdateValidator.java 2010-12-03 18:49:01 UTC (rev 2755) +++ trunk/engine/src/test/java/org/teiid/query/validator/TestUpdateValidator.java 2010-12-03 20:32:45 UTC (rev 2756) @@ -61,7 +61,7 @@ String vGroup = "gx"; Command command = createView(sql, md, vGroup); - UpdateValidator uv = new UpdateValidator(md, true, true, true); + UpdateValidator uv = new UpdateValidator(md, null, null, null); GroupSymbol gs = new GroupSymbol(vGroup); ResolverUtil.resolveGroup(gs, md); uv.validate(command, ResolverUtil.resolveElementsInGroup(gs, md));