Search in sources :

Example 1 with StoredProcedureColumn

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);
    }
}
Also used : SQLException(java.sql.SQLException) ColumnMode(io.syndesis.connector.sql.common.stored.ColumnMode) ResultSet(java.sql.ResultSet) ArrayList(java.util.ArrayList) DatabaseMetaData(java.sql.DatabaseMetaData) StoredProcedureColumn(io.syndesis.connector.sql.common.stored.StoredProcedureColumn) StoredProcedureMetadata(io.syndesis.connector.sql.common.stored.StoredProcedureMetadata)

Example 2 with StoredProcedureColumn

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);
}
Also used : Connection(java.sql.Connection) ResultSet(java.sql.ResultSet) DatabaseMetaData(java.sql.DatabaseMetaData) StoredProcedureColumn(io.syndesis.connector.sql.common.stored.StoredProcedureColumn) StoredProcedureMetadata(io.syndesis.connector.sql.common.stored.StoredProcedureMetadata) Test(org.junit.Test)

Example 3 with StoredProcedureColumn

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);
}
Also used : Connection(java.sql.Connection) ResultSet(java.sql.ResultSet) DatabaseMetaData(java.sql.DatabaseMetaData) StoredProcedureColumn(io.syndesis.connector.sql.common.stored.StoredProcedureColumn) StoredProcedureMetadata(io.syndesis.connector.sql.common.stored.StoredProcedureMetadata) Test(org.junit.Test)

Example 4 with StoredProcedureColumn

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);
}
Also used : HashMap(java.util.HashMap) ArrayList(java.util.ArrayList) PropertyPair(io.syndesis.connector.support.verifier.api.PropertyPair) StoredProcedureColumn(io.syndesis.connector.sql.common.stored.StoredProcedureColumn) DataShape(io.syndesis.common.model.DataShape) StoredProcedureMetadata(io.syndesis.connector.sql.common.stored.StoredProcedureMetadata) SyndesisMetadata(io.syndesis.connector.support.verifier.api.SyndesisMetadata) ObjectSchema(com.fasterxml.jackson.module.jsonSchema.types.ObjectSchema) ArrayList(java.util.ArrayList) List(java.util.List) HashMap(java.util.HashMap) Map(java.util.Map) JsonProcessingException(com.fasterxml.jackson.core.JsonProcessingException)

Aggregations

StoredProcedureColumn (io.syndesis.connector.sql.common.stored.StoredProcedureColumn)4 StoredProcedureMetadata (io.syndesis.connector.sql.common.stored.StoredProcedureMetadata)4 DatabaseMetaData (java.sql.DatabaseMetaData)3 ResultSet (java.sql.ResultSet)3 Connection (java.sql.Connection)2 ArrayList (java.util.ArrayList)2 Test (org.junit.Test)2 JsonProcessingException (com.fasterxml.jackson.core.JsonProcessingException)1 ObjectSchema (com.fasterxml.jackson.module.jsonSchema.types.ObjectSchema)1 DataShape (io.syndesis.common.model.DataShape)1 ColumnMode (io.syndesis.connector.sql.common.stored.ColumnMode)1 PropertyPair (io.syndesis.connector.support.verifier.api.PropertyPair)1 SyndesisMetadata (io.syndesis.connector.support.verifier.api.SyndesisMetadata)1 SQLException (java.sql.SQLException)1 HashMap (java.util.HashMap)1 List (java.util.List)1 Map (java.util.Map)1