ColumnSet cs = new ColumnSet(); cs.setUUID(rsId); procRd.setResultSet(cs);
ColumnSet cs = new ColumnSet(); cs.setUUID(rsId); procRd.setResultSet(cs);
private void addProc(TransformationMetadata metadata, String name, String query, String[] rsCols, String[] rsTypes, String[] params, String[] paramTypes) { Schema pm1 = metadata.getMetadataStore().getSchema("PM1"); //$NON-NLS-1$ pm1.getProcedures().remove(name.toUpperCase()); ColumnSet<Procedure> rs2 = RealMetadataFactory.createResultSet("rs1", rsCols, rsTypes); QueryNode sq2n1 = new QueryNode(query); //$NON-NLS-1$ ArrayList<ProcedureParameter> procParams = new ArrayList<ProcedureParameter>(params.length); for (int i = 0; i < params.length; i++) { procParams.add(RealMetadataFactory.createParameter(params[i], SPParameter.IN, paramTypes[i])); } Procedure sq1 = RealMetadataFactory.createVirtualProcedure(name, pm1, procParams, sq2n1); //$NON-NLS-1$ sq1.setResultSet(rs2); }
/** * Add a procedure resultset column to the given procedure. * @param name * @param type should be one of {@link TypeFacility.RUNTIME_NAMES} * @param procedure * @return * @throws MetadataException */ public Column addProcedureResultSetColumn(String name, String type, Procedure procedure) { if (procedure.getResultSet() == null) { ColumnSet<Procedure> resultSet = new ColumnSet<Procedure>(); resultSet.setParent(procedure); resultSet.setName("RSParam"); //$NON-NLS-1$ setUUID(resultSet); procedure.setResultSet(resultSet); } return addColumn(name, type, procedure.getResultSet()); }
@Test public void testInsertTempTableCreation1() { MetadataStore metadataStore = new MetadataStore(); Schema v1 = RealMetadataFactory.createVirtualModel("v1", metadataStore); //$NON-NLS-1$ QueryNode n1 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN insert into #temp (var1) values (1); select 2 as var1 into #temp; select #temp.var1 from #temp; END"); //$NON-NLS-1$ //$NON-NLS-2$ ColumnSet<Procedure> rs = RealMetadataFactory.createResultSet("rs", new String[] { "var1" }, new String[] { DataTypeManager.DefaultDataTypes.INTEGER}); //$NON-NLS-1$ //$NON-NLS-2$ Procedure vp = RealMetadataFactory.createVirtualProcedure("vp", v1, null, n1); //$NON-NLS-1$ vp.setResultSet(rs); QueryMetadataInterface metadata = RealMetadataFactory.createTransformationMetadata(metadataStore, "foo"); ProcessorPlan plan = helpGetPlan("exec v1.vp()", metadata); //$NON-NLS-1$ List[] expected = new List[] { Arrays.asList(new Object[] { new Integer(1) }), Arrays.asList(new Object[] { new Integer(2) }) }; helpProcess(plan, new FakeDataManager(), expected); }
/** * Create fake metadata for this case. Need a physical stored procedure and * a virtual stored procedure which calls the physical one. * @return */ private TransformationMetadata helpCreateCase4237VirtualProcedureMetadata() { MetadataStore metadataStore = new MetadataStore(); Schema physicalModel = RealMetadataFactory.createPhysicalModel("pm1", metadataStore); //$NON-NLS-1$ ColumnSet<Procedure> resultSet = RealMetadataFactory.createResultSet("pm1.rs", new String[] { "e1", "e2" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER }); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ ProcedureParameter inParam = RealMetadataFactory.createParameter("in", ParameterInfo.IN, DataTypeManager.DefaultDataTypes.STRING); //$NON-NLS-1$ Procedure storedProcedure = RealMetadataFactory.createStoredProcedure("sp", physicalModel, Arrays.asList(inParam)); //$NON-NLS-1$ //$NON-NLS-2$ storedProcedure.setResultSet(resultSet); Schema virtualModel = RealMetadataFactory.createVirtualModel("vm1", metadataStore); //$NON-NLS-1$ ColumnSet<Procedure> virtualResultSet = RealMetadataFactory.createResultSet("vm1.rs", new String[] { "e1", "e2" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER }); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ ProcedureParameter virtualInParam = RealMetadataFactory.createParameter("in1", ParameterInfo.IN, DataTypeManager.DefaultDataTypes.STRING); //$NON-NLS-1$ QueryNode queryNode = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN EXEC pm1.sp(vm1.sp.in1); END"); //$NON-NLS-1$ //$NON-NLS-2$ Procedure virtualStoredProcedure = RealMetadataFactory.createVirtualProcedure("sp", virtualModel, Arrays.asList(virtualInParam), queryNode); //$NON-NLS-1$ virtualStoredProcedure.setResultSet(virtualResultSet); return RealMetadataFactory.createTransformationMetadata(metadataStore, "case4237"); }
@Test public void testExecWithDuplicateNames() { MetadataStore metadataStore = new MetadataStore(); Schema pm1 = RealMetadataFactory.createPhysicalModel("pm1", metadataStore); ColumnSet<Procedure> rs2 = RealMetadataFactory.createResultSet("rs2", new String[] { "in", "e2" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER }); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ ProcedureParameter rs2p2 = RealMetadataFactory.createParameter("in", ParameterInfo.IN, DataTypeManager.DefaultDataTypes.STRING); //$NON-NLS-1$ Procedure sq2 = RealMetadataFactory.createStoredProcedure("sq2", pm1, Arrays.asList(rs2p2)); //$NON-NLS-1$ sq2.setResultSet(rs2); QueryMetadataInterface metadata = RealMetadataFactory.createTransformationMetadata(metadataStore, "example1"); helpResolveException("select * from pm1.sq2", metadata, "TEIID30114 Cannot access procedure pm1.sq2 using table semantics since the parameter and result set column names are not all unique."); //$NON-NLS-1$ //$NON-NLS-2$ }
@Test public void testInsertTempTableCreation() { MetadataStore metadataStore = new MetadataStore(); Schema v1 = RealMetadataFactory.createVirtualModel("v1", metadataStore); //$NON-NLS-1$ QueryNode n1 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN insert into #temp (var1) values (1); select #temp.var1 from #temp; END"); //$NON-NLS-1$ //$NON-NLS-2$ ColumnSet<Procedure> rs = RealMetadataFactory.createResultSet("rs", new String[] { "var1" }, new String[] { DataTypeManager.DefaultDataTypes.INTEGER}); //$NON-NLS-1$ //$NON-NLS-2$ Procedure vp = RealMetadataFactory.createVirtualProcedure("vp", v1, null, n1); //$NON-NLS-1$ vp.setResultSet(rs); QueryMetadataInterface metadata = RealMetadataFactory.createTransformationMetadata(metadataStore, "foo"); ProcessorPlan plan = helpGetPlan("exec v1.vp()", metadata); //$NON-NLS-1$ List[] expected = new List[] { Arrays.asList(new Object[] { new Integer(1) }) }; helpProcess(plan, new FakeDataManager(), expected); }
vt1.setResultSet(rs1); sp1.setResultSet(rs3);
vt1.setResultSet(rs1); vt2.setResultSet(RealMetadataFactory.createResultSet("v1.rs1", new String[] {"e1"}, new String[] { DataTypeManager.DefaultDataTypes.STRING })); //$NON-NLS-1$ //$NON-NLS-2$
vt1.setResultSet(rs1); vt2.setResultSet(RealMetadataFactory.createResultSet("v1.rs1", new String[] {"e1"}, new String[] { DataTypeManager.DefaultDataTypes.STRING }));
resultRecord.setName(RecordFactory.getShortName(resultRecord.getName())); loadColumnSetRecords(resultRecord, null); procedureRecord.setResultSet(resultRecord);
resultRecord.setName(RecordFactory.getShortName(resultRecord.getName())); loadColumnSetRecords(resultRecord, null); procedureRecord.setResultSet(resultRecord);
+ "execute string 'SELECT a, b FROM MultiModel.Phys where SOURCE_NAME = Virt.sq1.in'; END"); //$NON-NLS-1$ Procedure sq1 = createVirtualProcedure("sq1", virtModel, Arrays.asList(rs2p2), sq2n1); //$NON-NLS-1$ sq1.setResultSet(rs2); rs3p3.setNullType(org.teiid.metadata.BaseColumn.NullType.Nullable); Procedure sq2 = createStoredProcedure("proc", physModel, Arrays.asList(rs3p2, rs3p3)); sq2.setResultSet(rs3); return createTransformationMetadata(metadataStore, "multiBinding");
public static TransformationMetadata exampleBitwise() { MetadataStore store = new MetadataStore(); Schema phys = createPhysicalModel("phys", store); //$NON-NLS-1$ Table t = createPhysicalGroup("t", phys); //$NON-NLS-1$ createElements(t, new String[] { "ID", "Name", "source_bits" }, //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ new String[] { DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER }); Schema virt = createVirtualModel("virt", store); //$NON-NLS-1$ ColumnSet<Procedure> rs = createResultSet("rs", new String[] { "ID", "Name", "source_bits" }, new String[] { DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER }); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ QueryNode qn = new QueryNode("CREATE VIRTUAL PROCEDURE " //$NON-NLS-1$ + "BEGIN " //$NON-NLS-1$ + " DECLARE integer VARIABLES.BITS;" //$NON-NLS-1$ + " create local temporary table #temp (id integer, name string, bits integer);" //$NON-NLS-1$ + " LOOP ON (SELECT DISTINCT phys.t.ID, phys.t.Name FROM phys.t) AS idCursor" //$NON-NLS-1$ + " BEGIN" //$NON-NLS-1$ + " VARIABLES.BITS = 0;" //$NON-NLS-1$ + " LOOP ON (SELECT phys.t.source_bits FROM phys.t WHERE phys.t.ID = idCursor.id) AS bitsCursor" //$NON-NLS-1$ + " BEGIN" //$NON-NLS-1$ + " VARIABLES.BITS = bitor(VARIABLES.BITS, bitsCursor.source_bits);" //$NON-NLS-1$ + " END" //$NON-NLS-1$ + " SELECT idCursor.id, idCursor.name, VARIABLES.BITS INTO #temp;" //$NON-NLS-1$ + " END" //$NON-NLS-1$ + " SELECT ID, Name, #temp.BITS AS source_bits FROM #temp;" //$NON-NLS-1$ + "END"); //$NON-NLS-1$ Procedure proc = createVirtualProcedure("agg", virt, null, qn); //$NON-NLS-1$ proc.setResultSet(rs); return createTransformationMetadata(store, "bitwise"); }
QueryNode sq1n1 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN SELECT e1, e2 FROM pm1.g1; END"); //$NON-NLS-1$ //$NON-NLS-2$ Procedure sq1 = RealMetadataFactory.createVirtualProcedure("sq1", pm1, Collections.EMPTY_LIST, sq1n1); //$NON-NLS-1$ sq1.setResultSet(rs1);
QueryNode sq1n1 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN SELECT e1, e2 FROM pm1.g1; END"); //$NON-NLS-1$ //$NON-NLS-2$ Procedure sq1 = createVirtualProcedure("sq1", pm1, null, sq1n1); //$NON-NLS-1$ sq1.setResultSet(rs1); QueryNode sq2n1 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN SELECT e1, e2 FROM pm1.g1 WHERE e1=pm1.sq2.in; END"); //$NON-NLS-1$ //$NON-NLS-2$ Procedure sq2 = createVirtualProcedure("sq2", pm1, Arrays.asList(rs2p2), sq2n1); //$NON-NLS-1$ sq2.setResultSet(rs2); QueryNode sq3n1 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN SELECT e1, e2 FROM pm1.g1 WHERE e1=pm1.sq3.in UNION ALL SELECT e1, e2 FROM pm1.g1 WHERE e2=pm1.sq3.in2; END"); //$NON-NLS-1$ //$NON-NLS-2$ Procedure sq3 = createVirtualProcedure("sq3", pm1, Arrays.asList(rs5p2, rs5p3), sq3n1); //$NON-NLS-1$ sq3.setResultSet(rs5); QueryNode sq3n1a = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN SELECT e1, e2 FROM pm1.g1 WHERE e1=pm1.sq3a.in UNION ALL SELECT e1, e2 FROM pm1.g1 WHERE e2=pm1.sq3a.in2; END"); //$NON-NLS-1$ //$NON-NLS-2$ Procedure sq3a = createVirtualProcedure("sq3a", pm1, Arrays.asList(rs5p1a, rs5p2a), sq3n1a); //$NON-NLS-1$ sq3a.setResultSet(rs5a); QueryNode sq3n1b = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN SELECT e1, e2 FROM pm1.g1 WHERE e1=pm1.sq3b.in UNION ALL SELECT e1, e2 FROM pm1.g1 WHERE e2=pm1.sq3b.in2; END"); //$NON-NLS-1$ //$NON-NLS-2$ Procedure sq3b = createVirtualProcedure("sq3b", pm1, Arrays.asList(rs5p2b, rs5p3b, rs5p4b), sq3n1b); //$NON-NLS-1$ sq3b.setResultSet(rs5b); rsParameterTime ), sqDefaultsNode); sqDefaults.setResultSet(rsDefaults); vardic.setVarArg(true); Procedure nativeProc = createStoredProcedure("native", pm1, Arrays.asList(nativeparam,vardic)); //$NON-NLS-1$ //$NON-NLS-2$ nativeProc.setResultSet(nativeProcResults); sp1.setResultSet(rs3);
vardic.setVarArg(true); Procedure nativeProc = createStoredProcedure("native", bqt1, Arrays.asList(nativeparam,vardic)); //$NON-NLS-1$ //$NON-NLS-2$ nativeProc.setResultSet(nativeProcResults); ColumnSet<Procedure> rs1 = createResultSet("rs1", new String[] { "IntKey", "StringKey" }, new String[] { DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.STRING }); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ Procedure spTest5 = createStoredProcedure("spTest5", pm1, Arrays.asList(rs1p1)); //$NON-NLS-1$ //$NON-NLS-2$ spTest5.setResultSet(rs1); ColumnSet<Procedure> rs2 = createResultSet("rs2", new String[] { "IntKey", "StringKey"}, new String[] { DataTypeManager.DefaultDataTypes.INTEGER , DataTypeManager.DefaultDataTypes.STRING }); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ Procedure spTest8 = createStoredProcedure("spTest8", pm2, Arrays.asList(rs2p1, rs2p2)); //$NON-NLS-1$ //$NON-NLS-2$ spTest8.setResultSet(rs2); spTest8a.setResultSet(rs2a); ColumnSet<Procedure> rs3 = createResultSet("rs3", new String[] { "IntKey", "StringKey"}, new String[] { DataTypeManager.DefaultDataTypes.INTEGER , DataTypeManager.DefaultDataTypes.STRING }); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ Procedure spTest11 = createStoredProcedure("spTest11", pm3, Arrays.asList(rs3p1, rs3p2)); //$NON-NLS-1$ //$NON-NLS-2$ spTest11.setResultSet(rs3); QueryNode vspqn1 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN DECLARE integer x; LOOP ON (SELECT intkey FROM bqt1.smallA) AS intKeyCursor BEGIN x= intKeyCursor.intkey - 1; END SELECT stringkey FROM bqt1.smalla where intkey=x; END"); //$NON-NLS-1$ //$NON-NLS-2$ Procedure vsp1 = createVirtualProcedure("MMSP1", mmspTest1, null, vspqn1); //$NON-NLS-1$ vsp1.setResultSet(vsprs1); vsp2.setResultSet(vsprs2); vsp3.setResultSet(vsprs3); vsp4.setResultSet(vsprs4); QueryNode vspqn5 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN SELECT 0; END"); //$NON-NLS-1$ //$NON-NLS-2$
QueryNode sp1qn = new QueryNode("/*+ cache */ CREATE VIRTUAL PROCEDURE BEGIN SELECT x as StringKey from matsrc where x = param1; END"); //$NON-NLS-1$ //$NON-NLS-2$ Procedure vsp5 = createVirtualProcedure("sp1", sp, Arrays.asList(param), sp1qn); //$NON-NLS-1$ vsp5.setResultSet(rs);