use of io.syndesis.connector.sql.common.stored.StoredProcedureColumn in project syndesis by syndesisio.
the class SqlSupport method getStoredProcedureMetadata.
public static StoredProcedureMetadata getStoredProcedureMetadata(final Connection connection, final String catalog, final String schema, final String procedureName) {
final StoredProcedureMetadata storedProcedureMetadata = new StoredProcedureMetadata();
storedProcedureMetadata.setName(procedureName);
try {
final DatabaseMetaData meta = connection.getMetaData();
try (ResultSet columnSet = DatabaseMetaDataHelper.fetchProcedureColumns(meta, catalog, schema, procedureName)) {
final List<StoredProcedureColumn> columnList = new ArrayList<>();
while (columnSet.next()) {
final ColumnMode mode = ColumnMode.valueOf(columnSet.getInt("COLUMN_TYPE"));
if (ColumnMode.IN == mode || ColumnMode.OUT == mode || ColumnMode.INOUT == mode) {
final StoredProcedureColumn column = new StoredProcedureColumn();
column.setName(columnSet.getString("COLUMN_NAME"));
column.setMode(mode);
column.setJdbcType(JDBCType.valueOf(columnSet.getInt("DATA_TYPE")));
columnList.add(column);
}
}
final String template = columnList.stream().map(StoredProcedureColumn::toProcedureParameterString).collect(Collectors.joining(", ", procedureName + "(", ")"));
storedProcedureMetadata.setTemplate(template);
storedProcedureMetadata.setColumnList(columnList);
return storedProcedureMetadata;
}
} catch (final SQLException e) {
throw new IllegalStateException(e);
}
}
use of io.syndesis.connector.sql.common.stored.StoredProcedureColumn in project syndesis by syndesisio.
the class SqlStoredConnectorMetaDataExtensionTest method shouldFetchStoredProcedureMetadataWithSingleParameter.
@Test
public void shouldFetchStoredProcedureMetadataWithSingleParameter() throws SQLException {
final Connection connection = mock(Connection.class);
final DatabaseMetaData databaseMetaData = mock(DatabaseMetaData.class);
when(connection.getMetaData()).thenReturn(databaseMetaData);
when(databaseMetaData.getDatabaseProductName()).thenReturn("POSTGRESQL");
final ResultSet result = mock(ResultSet.class);
when(databaseMetaData.getFunctionColumns("catalog", "schema", "procedureName", null)).thenReturn(result);
when(result.next()).thenReturn(true, false);
when(result.getString("COLUMN_NAME")).thenReturn("A");
when(result.getInt("COLUMN_TYPE")).thenReturn(ColumnMode.IN.ordinal());
when(result.getInt("DATA_TYPE")).thenReturn(JDBCType.INTEGER.getVendorTypeNumber());
final StoredProcedureMetadata metadata = SqlSupport.getStoredProcedureMetadata(connection, "catalog", "schema", "procedureName");
final StoredProcedureColumn columnA = new StoredProcedureColumn();
columnA.setJdbcType(JDBCType.INTEGER);
columnA.setName("A");
columnA.setOrdinal(0);
columnA.setMode(ColumnMode.IN);
assertThat(metadata.getName()).isEqualTo("procedureName");
assertThat(metadata.getTemplate()).isEqualTo("procedureName(INTEGER ${body[A]})");
final List<StoredProcedureColumn> columnList = metadata.getColumnList();
assertThat(columnList.get(0)).isEqualToComparingFieldByField(columnA);
}
use of io.syndesis.connector.sql.common.stored.StoredProcedureColumn in project syndesis by syndesisio.
the class SqlStoredConnectorMetaDataExtensionTest method shouldFetchStoredProcedureMetadata.
@Test
public void shouldFetchStoredProcedureMetadata() throws SQLException {
final Connection connection = mock(Connection.class);
final DatabaseMetaData databaseMetaData = mock(DatabaseMetaData.class);
when(connection.getMetaData()).thenReturn(databaseMetaData);
when(databaseMetaData.getDatabaseProductName()).thenReturn("POSTGRESQL");
final ResultSet result = mock(ResultSet.class);
when(databaseMetaData.getFunctionColumns("catalog", "schema", "procedureName", null)).thenReturn(result);
when(result.next()).thenReturn(true, true, true, false);
when(result.getString("COLUMN_NAME")).thenReturn("A", "B", "C");
when(result.getInt("COLUMN_TYPE")).thenReturn(ColumnMode.IN.ordinal(), ColumnMode.IN.ordinal(), ColumnMode.OUT.ordinal());
when(result.getInt("DATA_TYPE")).thenReturn(JDBCType.INTEGER.getVendorTypeNumber(), JDBCType.INTEGER.getVendorTypeNumber(), JDBCType.INTEGER.getVendorTypeNumber());
final StoredProcedureMetadata metadata = SqlSupport.getStoredProcedureMetadata(connection, "catalog", "schema", "procedureName");
final StoredProcedureColumn columnA = new StoredProcedureColumn();
columnA.setJdbcType(JDBCType.INTEGER);
columnA.setName("A");
columnA.setOrdinal(0);
columnA.setMode(ColumnMode.IN);
final StoredProcedureColumn columnB = new StoredProcedureColumn();
columnB.setJdbcType(JDBCType.INTEGER);
columnB.setName("B");
columnB.setOrdinal(0);
columnB.setMode(ColumnMode.IN);
final StoredProcedureColumn columnC = new StoredProcedureColumn();
columnC.setJdbcType(JDBCType.INTEGER);
columnC.setName("C");
columnC.setOrdinal(0);
columnC.setMode(ColumnMode.OUT);
assertThat(metadata.getName()).isEqualTo("procedureName");
assertThat(metadata.getTemplate()).isEqualTo("procedureName(INTEGER ${body[A]}, INTEGER ${body[B]}, OUT INTEGER C)");
final List<StoredProcedureColumn> columnList = metadata.getColumnList();
assertThat(columnList.get(0)).isEqualToComparingFieldByField(columnA);
assertThat(columnList.get(1)).isEqualToComparingFieldByField(columnB);
assertThat(columnList.get(2)).isEqualToComparingFieldByField(columnC);
}
use of io.syndesis.connector.sql.common.stored.StoredProcedureColumn in project syndesis by syndesisio.
the class SqlMetadataRetrieval method adaptForStoredSql.
@SuppressWarnings({ "PMD.CyclomaticComplexity", "PMD.StdCyclomaticComplexity", "PMD.ModifiedCyclomaticComplexity" })
public SyndesisMetadata adaptForStoredSql(final String actionId, final Map<String, Object> properties, final MetaData metadata) {
final Map<String, List<PropertyPair>> enrichedProperties = new HashMap<>();
if (isPresentAndNonNull(properties, PROCEDURE_NAME)) {
// fetch metadata for the named procedure
final List<PropertyPair> ppList = new ArrayList<>();
@SuppressWarnings("unchecked") final Map<String, StoredProcedureMetadata> procedureMap = (Map<String, StoredProcedureMetadata>) metadata.getPayload();
final String procedureName = (String) properties.get(PROCEDURE_NAME);
final StoredProcedureMetadata storedProcedure = procedureMap.get(procedureName);
ppList.add(new PropertyPair(storedProcedure.getTemplate(), PROCEDURE_TEMPLATE));
enrichedProperties.put(PROCEDURE_TEMPLATE, ppList);
// build the input and output schemas
final ObjectSchema builderIn = new ObjectSchema();
builderIn.set$schema("http://json-schema.org/schema#");
builderIn.setTitle(procedureName + "_IN");
final ObjectSchema builderOut = new ObjectSchema();
builderOut.setTitle(procedureName + "_OUT");
builderOut.set$schema("http://json-schema.org/schema#");
if (storedProcedure.getColumnList() != null && !storedProcedure.getColumnList().isEmpty()) {
for (final StoredProcedureColumn column : storedProcedure.getColumnList()) {
if (column.getMode().equals(ColumnMode.IN) || column.getMode().equals(ColumnMode.INOUT)) {
builderIn.putProperty(column.getName(), schemaFor(column.getJdbcType()));
}
if (column.getMode().equals(ColumnMode.OUT) || column.getMode().equals(ColumnMode.INOUT)) {
builderOut.putProperty(column.getName(), schemaFor(column.getJdbcType()));
}
}
}
try {
DataShape.Builder inDataShapeBuilder = new DataShape.Builder().type(builderIn.getTitle());
if (builderIn.getProperties().isEmpty()) {
inDataShapeBuilder.kind(DataShapeKinds.NONE);
} else {
inDataShapeBuilder.kind(DataShapeKinds.JSON_SCHEMA).name(procedureName + " Parameter").description(String.format("Parameters of Stored Procedure '%s'", procedureName)).specification(Json.writer().writeValueAsString(builderIn));
}
DataShape.Builder outDataShapeBuilder = new DataShape.Builder().type(builderOut.getTitle());
if (builderOut.getProperties().isEmpty()) {
outDataShapeBuilder.kind(DataShapeKinds.NONE);
} else {
outDataShapeBuilder.kind(DataShapeKinds.JSON_SCHEMA).name(procedureName + " Return").description(String.format("Return value of Stored Procedure '%s'", procedureName)).specification(Json.writer().writeValueAsString(builderOut));
}
return new SyndesisMetadata(enrichedProperties, inDataShapeBuilder.build(), outDataShapeBuilder.build());
} catch (JsonProcessingException e) {
throw new IllegalStateException(e);
}
}
// return list of stored procedures in the database
@SuppressWarnings("unchecked") final Map<String, StoredProcedureMetadata> procedureMap = (Map<String, StoredProcedureMetadata>) metadata.getPayload();
if (isPresentAndNonNull(properties, PATTERN) && FROM_PATTERN.equalsIgnoreCase(String.valueOf(properties.get(PATTERN)))) {
enrichedProperties.put(PROCEDURE_NAME, obtainFromProcedureList(procedureMap));
} else {
enrichedProperties.put(PROCEDURE_NAME, obtainToProcedureList(procedureMap));
}
return new SyndesisMetadata(enrichedProperties, null, null);
}
Aggregations