protected ProcParameter getProcParameter(Database catalog_db, Procedure catalog_proc, int idx) { assertNotNull(catalog_db); assert (idx >= 0) : "Invalid ProcParameter index for " + catalog_proc + ": " + idx; assert (idx < catalog_proc.getParameters().size()) : "Invalid ProcParameter index for " + catalog_proc + ": " + idx; ProcParameter catalog_param = catalog_proc.getParameters().get(idx); assertNotNull("Null ProcParameter index for " + catalog_proc + ": " + idx, catalog_param); return (catalog_param); }
@Test public void testVirtualProcedure() throws Exception { String ddl = "CREATE VIRTUAL PROCEDURE myProc(OUT p1 boolean, p2 varchar, INOUT p3 decimal) " + "RETURNS (r1 varchar, r2 decimal) " + "OPTIONS(RANDOM 'any', UUID 'uuid', NAMEINSOURCE 'nis', ANNOTATION 'desc', UPDATECOUNT '2') " + "AS /*+ cache */ BEGIN select * from foo; END"; Schema s = helpParse(ddl, "model").getSchema(); Procedure proc = s.getProcedure("myProc"); assertNotNull(proc); assertTrue(proc.isVirtual()); assertFalse(proc.isFunction()); assertEquals(3, proc.getParameters().size()); assertEquals("p1", proc.getParameters().get(0).getName()); assertEquals("boolean", proc.getParameters().get(0).getDatatype().getName()); assertEquals(ProcedureParameter.Type.Out, proc.getParameters().get(0).getType()); assertEquals("p2", proc.getParameters().get(1).getName()); assertEquals("string", proc.getParameters().get(1).getDatatype().getName()); assertEquals(ProcedureParameter.Type.In, proc.getParameters().get(1).getType()); assertEquals("p3", proc.getParameters().get(2).getName()); assertEquals("bigdecimal", proc.getParameters().get(2).getDatatype().getName()); assertEquals(ProcedureParameter.Type.InOut, proc.getParameters().get(2).getType()); ColumnSet<Procedure> ret = proc.getResultSet(); assertNotNull(ret); assertEquals(2, ret.getColumns().size()); assertEquals("r1", ret.getColumns().get(0).getName()); assertEquals("string", ret.getColumns().get(0).getDatatype().getName()); assertEquals("r2", ret.getColumns().get(1).getName()); assertEquals("bigdecimal", ret.getColumns().get(1).getDatatype().getName()); assertEquals("uuid", proc.getUUID()); assertEquals("nis", proc.getNameInSource()); assertEquals("desc", proc.getAnnotation()); assertEquals(2, proc.getUpdateCount()); assertEquals("any", proc.getProperties().get("RANDOM")); assertEquals("/*+ cache */ BEGIN\nSELECT * FROM foo;\nEND", proc.getQueryPlan()); }
private void visit(Procedure procedure) { if (this.filter != null && !filter.matcher(procedure.getName()).matches()) { return; } append(CREATE).append(SPACE); if (procedure.isVirtual()) { append(VIRTUAL); } else { append(FOREIGN); } append(SPACE) .append(procedure.isFunction() ? FUNCTION : PROCEDURE) .append(SPACE) .append(SQLStringVisitor.escapeSinglePart(procedure.getName())); append(LPAREN); boolean first = true; for (ProcedureParameter pp : procedure.getParameters()) { if (first) { first = false; } else { append(COMMA).append(SPACE); } visit(pp); } append(RPAREN); if (procedure.getResultSet() != null) { append(SPACE).append(RETURNS); appendOptions(procedure.getResultSet()); append(SPACE).append(TABLE).append(SPACE); addColumns(procedure.getResultSet().getColumns(), true); } /* The parser treats the RETURN clause as optional for a procedure if using the RESULT param for (ProcedureParameter pp: procedure.getParameters()) { if (pp.getType().equals(Type.ReturnValue)) { append(SPACE).append(RETURNS).append(SPACE); appendColumn(buffer, pp, false, true); break; } }*/ // options String options = buildProcedureOptions(procedure); if (!options.isEmpty()) { append(NEWLINE).append(OPTIONS).append(SPACE).append(LPAREN).append(options).append(RPAREN); } // block if (procedure.isVirtual()) { append(NEWLINE).append(SQLConstants.Reserved.AS).append(NEWLINE); String plan = procedure.getQueryPlan(); append(plan); } append(SEMICOLON); }
@Test public void testAlterProcedureOptions() throws Exception { String ddl = "CREATE FOREIGN PROCEDURE myProc(OUT p1 boolean, p2 varchar, INOUT p3 decimal) " + "RETURNS (r1 varchar, r2 decimal)" + "OPTIONS(RANDOM 'any', UUID 'uuid', NAMEINSOURCE 'nis', ANNOTATION 'desc', UPDATECOUNT '2');" + "ALTER FOREIGN PROCEDURE myProc OPTIONS(SET NAMEINSOURCE 'x')" + "ALTER FOREIGN PROCEDURE myProc ALTER PARAMETER p2 OPTIONS (ADD x 'y');" + "ALTER FOREIGN PROCEDURE myProc OPTIONS(DROP UPDATECOUNT);"; Schema s = helpParse(ddl, "model").getSchema(); Procedure proc = s.getProcedure("myProc"); assertNotNull(proc); assertEquals("x", proc.getNameInSource()); assertEquals("p2", proc.getParameters().get(1).getName()); assertEquals("y", proc.getParameters().get(1).getProperty("x", false)); assertEquals(1, proc.getUpdateCount()); }
private static void buildFunctionImports(MetadataStore metadataStore, List<Builder> edmSchemas) { for (Schema schema : metadataStore.getSchemaList()) { EdmSchema.Builder odataSchema = findSchema(edmSchemas, schema.getName()); EdmEntityContainer.Builder entityContainer = findEntityContainer(edmSchemas, schema.getName()); // procedures for (Procedure proc : schema.getProcedures().values()) { EdmFunctionImport.Builder edmProcedure = EdmFunctionImport.newBuilder(); edmProcedure.setName(proc.getName()); String httpMethod = "POST"; for (ProcedureParameter pp : proc.getParameters()) { if (pp.getName().equals("return")) { httpMethod = "GET"; edmProcedure.setReturnType( ODataTypeManager.odataType(pp.getDatatype().getRuntimeTypeName())); continue; } EdmFunctionParameter.Builder param = EdmFunctionParameter.newBuilder(); param.setName(pp.getName()); param.setType(ODataTypeManager.odataType(pp.getDatatype().getRuntimeTypeName())); if (pp.getType() == ProcedureParameter.Type.In) { param.setMode(Mode.In); } else if (pp.getType() == ProcedureParameter.Type.InOut) { param.setMode(Mode.InOut); } else if (pp.getType() == ProcedureParameter.Type.Out) { param.setMode(Mode.Out); } param.setNullable(pp.getNullType() == NullType.Nullable); edmProcedure.addParameters(param); } // add a complex type for return resultset. ColumnSet<Procedure> returnColumns = proc.getResultSet(); if (returnColumns != null) { httpMethod = "GET"; EdmComplexType.Builder complexType = EdmComplexType.newBuilder(); complexType.setName(proc.getName() + "_" + returnColumns.getName()); complexType.setNamespace(schema.getName()); for (Column c : returnColumns.getColumns()) { EdmProperty.Builder property = EdmProperty.newBuilder(c.getName()) .setType(ODataTypeManager.odataType(c.getDatatype().getRuntimeTypeName())) .setNullable(c.getNullType() == NullType.Nullable); if (c.getDatatype() .getRuntimeTypeName() .equals(DataTypeManager.DefaultDataTypes.STRING)) { property .setFixedLength(c.isFixedLength()) .setMaxLength(c.getLength()) .setUnicode(true); } complexType.addProperties(property); } odataSchema.addComplexTypes(complexType); edmProcedure.setIsCollection(true); edmProcedure.setReturnType( EdmCollectionType.newBuilder() .setCollectionType(complexType) .setKind(CollectionKind.Collection)); } edmProcedure.setHttpMethod(httpMethod); entityContainer.addFunctionImports(edmProcedure); } } }