analytics/sdk/modeling/java/examples/source/SchemaImportHelper.java

/***************************************************************************************
 * 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;
	}
}