/*************************************************************************************** * IBM Confidential * * OCO Source Materials * * IBM Cognos Products: Moser * * (C) Copyright IBM Corp. 2020 * * The source code for this program is not published or otherwise * divested of its trade secrets, irrespective of what has been * deposited with the U.S. Copyright Office. * ***************************************************************************************/ package com.ibm.bi.platform.modeling.sdk.examples; import static com.ibm.bi.platform.modeling.sdk.examples.ModelingRESTEndpoints.datasourcesURL; import static com.ibm.bi.platform.modeling.sdk.examples.ModelingRESTEndpoints.metadataImportURL; import java.io.IOException; import com.ibm.bi.platform.modeling.sdk.examples.internal.UnexpectedHTTPResponseException; import com.ibm.bi.platform.moser.core.tasks.TaskState; import com.ibm.json.java.JSONArray; import com.ibm.json.java.JSONObject; /** * Example to demonstrate how to load a data source schema * * - Load metadata * - Load metadata with selected tables * - Reload metadata * */ public class SchemaImportHelper extends AsynchronousTasksRunner { /** * @param origin product url origin */ public SchemaImportHelper(String origin) { super(origin); } /** * Load schema metadata * * @param datasourceId * datasource store id * @param connectionId * datasource connection store id * @param signonId * signon object store id * @param importOptions * json schema import spec * @return schema id or null * @throws UnexpectedHTTPResponseException * @throws IOException */ public String loadMetadata(String datasourceId, String connectionId, String signonId, String importOptions) throws UnexpectedHTTPResponseException, IOException { String schemaId = createSchemaImportOptions(datasourceId, connectionId, signonId, importOptions); String response = importSchema(datasourceId, connectionId, signonId, schemaId); // response contains base module store id if successful, error message if failed System.out.println("Schema import is finished. Final response: " + response ); return schemaId; } /** * Reload metadata example. Will change import options of already imported schema * @param datasourceId * datasource store id * @param connectionId * datasource connection store id * @param signonId * signon object store id * @param schemaId * schema store id * @param importOptions * schema import options * @param signonSpec * optional signon spec for multiple or prompted signons * @throws UnexpectedHTTPResponseException */ public void reloadMetadata(String datasourceId, String connectionId, String signonId, String schemaId, String importOptions) throws UnexpectedHTTPResponseException { updateSchemaImportOptions(datasourceId, connectionId, signonId, schemaId, importOptions); String response = importSchema(datasourceId, connectionId, signonId, schemaId); // response contains base module store id if successful, error message if failed System.out.println("Schema import is finished. Final response: " + response ); } /** * Post schema definition describing what to import. * * @param datasourceId * datasource store id * @param connectionId * datasource connection store id * @param signonId * signon object store id * @param definition * the schema definition , e.g. * {"schema":"FOODMART","catalog":"null","type":"dataSourceSchema","schemaType":"user","defaultName":"FOODMART"} * * or { "schema": "Person", "catalog": "AdventureWorks2014", * "schemaType": "user", "specification": { "dataStatistics": * "none", "excludedTables": [], "dataSamplingSize": 1000, * "version": "1.0", "importPrimaryForeignKeys": true }, * "defaultName": "AdventureWorks2014/Person", "type":"dataSourceSchema" } * @throws UnexpectedHTTPResponseException * @throws IOException */ protected String createSchemaImportOptions(String datasourceId, String connectionId, String signonId, String definition) throws UnexpectedHTTPResponseException, IOException { String url = getOrigin() + datasourcesURL + datasourceId + "/connections/" + connectionId + "/signons/" + signonId + "/schemas"; String schemaId = getSchemaId(executePostRequest(url, definition, new Integer(201))); return schemaId; } /** * @param executePostRequest * @return * @throws IOException */ private static String getSchemaId(String response) throws IOException { JSONObject rsp = JSONObject.parse(response); JSONArray data = (JSONArray) rsp.get("data"); JSONObject schema = (JSONObject) data.get(0); return (String) schema.get("id"); } /** * Put schema definition describing what to re-import. Results of the old import options will be overridden by new * * @param datasourceId * datasource store id * @param connectionId * datasource connection store id * @param signonId * signon object store id * @param schemaId * schema CM id * @param definition * the schema definition , e.g. * {"schema":"FOODMART","catalog":"null","type":"dataSourceSchema","schemaType":"user","defaultName":"FOODMART"} * * or { "schema": "Person", "catalog": "AdventureWorks2014", * "schemaType": "user", "specification": { "dataStatistics": * "none", "excludedTables": [], "dataSamplingSize": 1000, * "version": "1.0", "importPrimaryForeignKeys": true }, * "defaultName": "AdventureWorks2014/Person", "type":"dataSourceSchema" } * @throws UnexpectedHTTPResponseException * @return response body */ protected String updateSchemaImportOptions(String datasourceId, String connectionId, String signonId, String schemaId, String definition) throws UnexpectedHTTPResponseException { String url = getOrigin() + datasourcesURL + datasourceId + "/connections/" + connectionId + "/signons/" + signonId + "/schemas/" + schemaId; String response = executePutRequest(url, definition, new Integer(201)); return response; } /** * Import schema * * @param datasourceId * datasource store id * @param connectionId * datasource connection store id * @param signonId * signon object store id * @param schemaId * schema CM id * * @return schema's base module artifact id in content manager database * @throws UnexpectedHTTPResponseException */ protected String importSchema(String datasourceId, String connectionId, String signonId, String schemaId) throws UnexpectedHTTPResponseException { JSONObject parameters = new JSONObject(); parameters.put("datasource_id", datasourceId); parameters.put("connection_id", connectionId); parameters.put("signon_id", signonId); parameters.put("schema_id", schemaId); String url = getOrigin() + metadataImportURL; // start schema import - base module definition creation // initiate asynchronous schema import String task = executePostRequest(url, parameters.toString(), Integer.valueOf(202)); JSONObject response = executeAsychronousTask(task); if (TaskState.SUCCESS.equals(response.get("state"))) { // return base module id JSONArray ids = (JSONArray) ((JSONObject) response.get("response")).get("ids"); return ids.get(0).toString(); } // return error message return response.toString(); } /** * Return schema json specification, which can be used to import schema * * @param qualifiedSchemaName * string in the format "database:catalog:null:schema" * @param connectionSpec null or a connection json spec for multiple or prompted signons * @return response json , e.g. { "data": [ { "schema": "CUSTAGGR", * "catalog": null, "schemaType": "user", "specification": { * "cleanColumnLabels": true, "dataStatistics": "all", * "excludedTables": [], "cleanTableLabels": true, "version": "1.0", * "importPrimaryForeignKeys": true, "dataSamplingSize": 1000 }, * "id": "Foodmart_DB2:Foodmart_DB2:null:CUSTAGGR", "type": * "dataSourceSchema", "defaultName": "CUSTAGGR", "status": * "not_loaded" } ] } * @throws UnexpectedHTTPResponseException */ public JSONObject getSchemaSpecification(String datasourceId, String connectionId, String signonId, String qualifiedSchemaName) throws UnexpectedHTTPResponseException { // /datasources/{datasourceid}/connections/{connectionid}/signons/{signonid}/schemas/{qualifiedNameEncoded} String url = getOrigin() + datasourcesURL + datasourceId + "/connections/" + connectionId + "/signons/" + signonId + "/schemas/" + encodeURL(qualifiedSchemaName); String response = executeGetRequest(url, Integer.valueOf(200), null); try { JSONObject schema = JSONObject.parse(response); return schema; } catch (IOException e) { e.printStackTrace(); } return null; } /** * Search and return schema object store id, provided schema and catalog name * * @param connectionId datasource connection object store id * @param connectionSpec optional connection spec for multiple or prompted signons * @param schema_name schema name, can be null, depending on datasource * @param catalog schema catalog, can be null, depending on datasource * @return schema id * @throws UnexpectedHTTPResponseException */ public String getSchemaId(String datasourceId, String connectionId, String signonId, String schema_name, String catalog_name) throws UnexpectedHTTPResponseException{ // nothing to match if(schema_name == null && catalog_name == null){ return null; } String url = getOrigin() + datasourcesURL + datasourceId + "/connections/" + connectionId + "/signons/" + signonId + "/schemas"; String response = executeGetRequest(url, Integer.valueOf(200), null); try { JSONObject data = JSONObject.parse(response); JSONArray data_items = (JSONArray) data.get("schemas"); for (Object item : data_items) { JSONObject descriptor = (JSONObject) item; if (descriptor.get("status").equals("loaded")) { String schema = (String) descriptor.get("schema"); String catalog = (String) descriptor.get("catalog"); if (schema_name == null && catalog_name.equals(catalog)) { return descriptor.get("id").toString(); } else if (catalog_name == null && schema_name.equals(schema)) { return descriptor.get("id").toString(); } else if (schema_name.equals(schema) && catalog_name.equals(catalog)) { return descriptor.get("id").toString(); } } } } catch (IOException e) { e.printStackTrace(); } return null; } /** * Return an array of tables for the schema * * @param qualifiedSchemaName * string in the format "datasource:connection:catalog:schema" * @param connectionSpec * null or a connection json spec for multiple or prompted * signons * @return response json array of tables, e.g. * {"data":["BOSTONNY_SALES_AGGR","CANADA_YEAR_SALES_AGGR", * "COUNTRY_YEAR_SALES_AGGR","FACTTABLE","OTTAWATORONTO_SALES_AGGR", * "REGION","SALES_AGGR","TIME","US_YEAR_SALES_AGGR","YEAR_SALES_AGGR"]} * @throws UnexpectedHTTPResponseException */ public JSONArray getSchemaTables(String datasourceId, String connectionId, String signonId, String qualifiedSchemaName) throws UnexpectedHTTPResponseException { // /datasources/{datasourceid}/connections/{connectionid}/signons/{signonid}/schemas/tables?id={qualifiedNameEncoded} String url = getOrigin() + datasourcesURL + datasourceId + "/connections/" + connectionId + "/signons/" + signonId + "/schemas/tables" + "?schemaid=" + encodeURL(qualifiedSchemaName); String response = executeGetRequest(url, Integer.valueOf(200), null); try { JSONObject data = JSONObject.parse(response); return (JSONArray) data.get("tables"); } catch (IOException e) { e.printStackTrace(); } return null; } /** scenarios **/ /** * Reload metadata example. Loads all tables for the given schema. Schema * import options are pre-configured and loaded from file * * @param datasourceId * datasource store ID * @param connectionId * datasource connection store ID * @param signonId * signon object store ID * @param importOptions * import option specification json string * @return schema content store id * @throws UnexpectedHTTPResponseException * @throws IOException */ public String runReLoadMetadataExample(String datasourceId, String connectionId, String signonId, String importOptions) throws UnexpectedHTTPResponseException, IOException { JSONObject importSpec = JSONObject.parse(importOptions); String schema_name = (String) importSpec.get("schema"), catalog_name = (String) importSpec.get("catalog"); String schemaId = getSchemaId(datasourceId, connectionId, signonId, schema_name, catalog_name); reloadMetadata(datasourceId, connectionId, signonId, schemaId, importOptions); return schemaId; } /** * Load metadata example. Loads selected tables for the given schema. Schema * import options are configured during example execution * * @param datasourceId * datasource store id * @param connectionId * datasource connection store id * @param signonId * signon object store id * @param importOptions * json schema import spec * * @param datasource_name * datasource name * @param connection_name * datasource connection name * @param catalog_name * catalog name or null, note both schema and catalog can not be * null * @param schema_name * schema name or null, note both schema and catalog can not be * null * * @return schema content store id * @throws UnexpectedHTTPResponseException * @throws IOException */ public String runLoadMetadataWithSelectedTablesExample(String datasourceId, String connectionId, String signonId, String datasource_name, String connection_name, String catalog_name, String schema_name) throws UnexpectedHTTPResponseException, IOException { String qualifiedSchemaName = datasource_name + ":" + connection_name + ":" + catalog_name + ":" + schema_name; JSONObject importOptions = getSchemaSpecification(datasourceId, connectionId, signonId, qualifiedSchemaName); if (importOptions == null) { return null; } JSONObject specification = (JSONObject) importOptions.get("specification"); JSONArray tables = getSchemaTables(datasourceId, connectionId, signonId, qualifiedSchemaName); if (tables == null) { return null; } JSONArray tablesToExclude = (JSONArray) specification.get("excludedTables"); for (int i = tables.size() / 2; i < tables.size(); i++) { tablesToExclude.add(tables.get(i)); } importOptions.remove("id"); importOptions.put("status", "pending"); return loadMetadata(datasourceId, connectionId, signonId, importOptions.toString()); } /** * Load metadata example. Loads all tables for the given schema. Schema * import options are pre-configured and loaded from file * * @param datasourceId * datasource store id * @param connectionId * datasource connection store id * @param signonId * signon object store id * @param importOptions * json schema import spec * @return schema content store id * @throws UnexpectedHTTPResponseException * @throws IOException */ public String runLoadMetadataExample(String datasourceId, String connectionId, String signonId, String importOptions) throws UnexpectedHTTPResponseException, IOException { return loadMetadata(datasourceId, connectionId, signonId, importOptions); } /** * Removes a dataSourceSchema object from CM - clears import * * @throws UnexpectedHTTPResponseException */ public void clearSchema(String schemaId) throws UnexpectedHTTPResponseException{ if(schemaId == null){ return; } String url = getOrigin() + schemasURL + schemaId; executeDeleteRequest(url, Integer.valueOf(200), null); } /* (non-Javadoc) * @see com.ibm.bi.platform.modeling.sdk.examples.AsynchronousTasksRunner#getTasksURL() */ @Override public String getTasksURL() { return ModelingRESTEndpoints.metadataImportTasksURL; } }