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);
}
}
}
