MasterDatabaseUtility.java

/*******************************************************************************
 * Copyright 2015 Defense Health Agency (DHA)
 *
 * If your use of this software does not include any GPLv2 components:
 * 	Licensed under the Apache License, Version 2.0 (the "License");
 * 	you may not use this file except in compliance with the License.
 * 	You may obtain a copy of the License at
 *
 * 	  http://www.apache.org/licenses/LICENSE-2.0
 *
 * 	Unless required by applicable law or agreed to in writing, software
 * 	distributed under the License is distributed on an "AS IS" BASIS,
 * 	WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * 	See the License for the specific language governing permissions and
 * 	limitations under the License.
 * ----------------------------------------------------------------------------
 * If your use of this software includes any GPLv2 components:
 * 	This program is free software; you can redistribute it and/or
 * 	modify it under the terms of the GNU General Public License
 * 	as published by the Free Software Foundation; either version 2
 * 	of the License, or (at your option) any later version.
 *
 * 	This program is distributed in the hope that it will be useful,
 * 	but WITHOUT ANY WARRANTY; without even the implied warranty of
 * 	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * 	GNU General Public License for more details.
 *******************************************************************************/
package prerna.masterdatabase.utility;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import java.util.TreeSet;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.javatuples.Pair;

import prerna.engine.api.IHeadersDataRow;
import prerna.engine.api.IRDBMSEngine;
import prerna.engine.api.IRawSelectWrapper;
import prerna.engine.impl.owl.AbstractOwlCreator;
import prerna.engine.impl.owl.AbstractOwlCreator.OwlIndex;
import prerna.query.querystruct.SelectQueryStruct;
import prerna.query.querystruct.filters.AndQueryFilter;
import prerna.query.querystruct.filters.OrQueryFilter;
import prerna.query.querystruct.filters.SimpleQueryFilter;
import prerna.query.querystruct.selectors.QueryColumnOrderBySelector;
import prerna.query.querystruct.selectors.QueryColumnSelector;
import prerna.query.querystruct.selectors.QueryConstantSelector;
import prerna.query.querystruct.selectors.QueryFunctionHelper;
import prerna.query.querystruct.selectors.QueryFunctionSelector;
import prerna.rdf.engine.wrappers.WrapperManager;
import prerna.sablecc2.om.PixelDataType;
import prerna.util.ConnectionUtils;
import prerna.util.Constants;
import prerna.util.QueryExecutionUtility;
import prerna.util.SystemEngineRegistry;
import prerna.util.sql.AbstractSqlQueryUtil;

public class MasterDatabaseUtility {

	private static final Logger classLogger = LogManager.getLogger(MasterDatabaseUtility.class);

	public static void initLocalMaster() throws Exception {
		IRDBMSEngine database = SystemEngineRegistry.getLocalMasterDb();
		LocalMasterOwlCreator owlCreator = new LocalMasterOwlCreator(database.getQueryUtil());
		if (owlCreator.needsRemake(database)) {
			owlCreator.remakeOwl(database);
		}

		Connection conn = null;
		try {
			conn = database.getConnection();
			executeInitLocalMaster(database, conn, owlCreator.getDBSchema());

			if (!conn.getAutoCommit()) {
				conn.commit();
			}
		} catch (SQLException e) {
			classLogger.error("Error initializing local master database schema.", e);
			throw e;
		} finally {
			ConnectionUtils.closeAllConnectionsIfPooling(database, conn, null, null);
		}
	}

	private static void executeInitLocalMaster(IRDBMSEngine engine, Connection conn,
			List<Pair<String, List<Pair<String, String>>>> dbSchema) throws SQLException {
		// create the tables and columns from the OWL creator schema
		AbstractOwlCreator.syncSchema(engine, conn, dbSchema);

		// create the indexes on the tables
		AbstractOwlCreator.syncIndexes(engine, conn, List.of(OwlIndex.of("ENGINE_ID_INDEX", "ENGINE", "ID"),
				OwlIndex.of("ENGINECONCEPT_ENGINE_LOCALCONCEPTID_INDEX", "ENGINECONCEPT", "ENGINE", "LOCALCONCEPTID"),
				OwlIndex.of("ENGINECONCEPT_PHYSICALNAMEID_INDEX", "ENGINECONCEPT", "PHYSICALNAMEID"),
				OwlIndex.of("CONCEPT_ID_INDEX", "CONCEPT", "LOCALCONCEPTID"),
				OwlIndex.of("RELATION_TARGETID_INDEX", "RELATION", "TARGETID"),
				OwlIndex.of("RELATION_SOURCEID_INDEX", "RELATION", "SOURCEID"),
				OwlIndex.of("ENGINERELATION_ENGINE_INDEX", "ENGINERELATION", "ENGINE"),
				OwlIndex.of("ENGINERELATION_TARGETCONCEPTID_INDEX", "ENGINERELATION", "TARGETCONCEPTID"),
				OwlIndex.of("ENGINERELATION_SOURCECONCEPTID_INDEX", "ENGINERELATION", "SOURCECONCEPTID"),
				OwlIndex.of("CONCEPTMETADATA_KEY_INDEX", Constants.CONCEPT_METADATA_TABLE, Constants.LM_META_KEY),
				OwlIndex.of("CONCEPTMETADATA_PHYSICALNAMEID_INDEX", Constants.CONCEPT_METADATA_TABLE,
						Constants.LM_PHYSICAL_NAME_ID)));

		// TBD if we want to keep any XRAY in local master
		{
			String database = engine.getDatabase();
			String schema = engine.getSchema();
			AbstractSqlQueryUtil queryUtil = engine.getQueryUtil();
			boolean allowIfExistsTable = queryUtil.allowsIfExistsTableSyntax();

			// XRAYCONFIGS is not described in the OWL schema - create it explicitly
			final String CLOB_DATATYPE = queryUtil.getClobDataTypeName();
			String[] colNames = new String[] { "FILENAME", "CONFIG" };
			String[] types = new String[] { "varchar(800)", CLOB_DATATYPE };
			if (allowIfExistsTable) {
				String sql = queryUtil.createTableIfNotExists("XRAYCONFIGS", colNames, types);
				classLogger.info("Running sql {}", sql);
				executeSql(conn, sql);
			} else {
				if (!queryUtil.tableExists(engine, "XRAYCONFIGS", database, schema)) {
					String sql = queryUtil.createTable("XRAYCONFIGS", colNames, types);
					classLogger.info("Running sql {}", sql);
					executeSql(conn, sql);
				}
			}
		}
	}

	private static void executeSql(Connection conn, String sql) throws SQLException {
		try (Statement stmt = conn.createStatement()) {
			stmt.execute(sql);
		}
	}

	/**
	 * Return all the logical names for a given conceptual name
	 * 
	 * @param conceptualName
	 * @return
	 */
	public static List<String> getAllLogicalNamesFromConceptualRDBMS(String conceptualName) {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("CONCEPT__LOGICALNAME"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("CONCEPT__CONCEPTUALNAME", "==", conceptualName));
		qs.addOrderBy(new QueryColumnOrderBySelector("CONCEPT__LOGICALNAME"));

		return QueryExecutionUtility.flushToListString(engine, qs);
	}

	/**
	 * Return all the logical names for a given conceptual name
	 * 
	 * @param conceptualName
	 * @return
	 */
	public static List<String> getAllLogicalNamesFromPixelName(List<String> pixelNames) {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("CONCEPT__LOGICALNAME"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__SEMOSSNAME", "==", pixelNames));
		qs.addRelation("CONCEPT__LOCALCONCEPTID", "ENGINECONCEPT__LOCALCONCEPTID", "inner.join");
		qs.addOrderBy(new QueryColumnOrderBySelector("CONCEPT__LOGICALNAME"));

		return QueryExecutionUtility.flushToListString(engine, qs);
	}

	/**
	 * Return all the logical names for a given conceptual name
	 * 
	 * @param conceptualName
	 * @return
	 */
	public static List<String> getLocalConceptIdsFromLogicalName(List<String> logicalNames) {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("CONCEPT__LOCALCONCEPTID"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("CONCEPT__LOGICALNAME", "==", logicalNames));

		return QueryExecutionUtility.flushToListString(engine, qs);
	}

	/**
	 * Return all the logical names for a given conceptual name
	 * 
	 * @param conceptualName
	 * @return
	 */
	public static List<String> getLocalConceptIdsFromPixelName(List<String> pixelNames) {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("CONCEPT__LOCALCONCEPTID"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__SEMOSSNAME", "==", pixelNames));
		qs.addRelation("CONCEPT__LOCALCONCEPTID", "ENGINECONCEPT__LOCALCONCEPTID", "inner.join");

		return QueryExecutionUtility.flushToListString(engine, qs);
	}

	/**
	 * Return all the logical names for a given conceptual name
	 * 
	 * @param conceptualName
	 * @return
	 */
	public static List<String> getConceptualIdsWithSimilarLogicalNames(List<String> conceptualIds) {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("CONCEPT__LOCALCONCEPTID"));

		SelectQueryStruct subQs = new SelectQueryStruct();
		subQs.addSelector(new QueryColumnSelector("CONCEPT__LOGICALNAME"));
		subQs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("CONCEPT__LOCALCONCEPTID", "==", conceptualIds));
		qs.addExplicitFilter(
				SimpleQueryFilter.makeColToValFilter("CONCEPT__LOGICALNAME", "==", subQs, PixelDataType.QUERY_STRUCT));

		return QueryExecutionUtility.flushToListString(engine, qs);
	}

	/**
	 * Get a list of arrays containing [table, column, type] for a given database
	 * 
	 * @param databaseId
	 * @return
	 */
	public static List<Object[]> getAllTablesAndColumns(String databaseId) {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PARENTSEMOSSNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__SEMOSSNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PROPERTY_TYPE"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PK"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PHYSICALNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PARENTPHYSICALNAME"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__ENGINE", "==", databaseId));
		qs.addExplicitFilter(
				SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__IGNORE_DATA", "==", false, PixelDataType.BOOLEAN));
		qs.addOrderBy("ENGINECONCEPT__PARENTSEMOSSNAME");
		qs.addOrderBy("ENGINECONCEPT__PK");
		qs.addOrderBy("ENGINECONCEPT__SEMOSSNAME");

		List<Object[]> ret = new ArrayList<>();

		try (IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(engine, qs)) {
			while (wrapper.hasNext()) {
				Object[] data = wrapper.next().getValues();
				boolean isPk = (boolean) data[3];
				if (isPk) {
					data[0] = data[1];
				}
				Object type = data[2];
				if (type != null && (type.equals("DOUBLE") || type.equals("INT"))) {
					data[2] = "NUMBER";
				}
				ret.add(data);
			}
		} catch (Exception e) {
			classLogger.error("Error retrieving tables and columns for database {}.", databaseId, e);
		}

		return ret;
	}

	/**
	 * Get a list of arrays containing [table, column, type] for a given database
	 * 
	 * @param engineId
	 * @return
	 */
	public static List<Object[]> getAllTablesAndColumns(Collection<String> databaseIds) {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__ENGINE"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PARENTSEMOSSNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__SEMOSSNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PROPERTY_TYPE"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PK"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__ENGINE", "==", databaseIds));
		qs.addExplicitFilter(
				SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__IGNORE_DATA", "==", false, PixelDataType.BOOLEAN));
		qs.addOrderBy("ENGINECONCEPT__ENGINE");
		qs.addOrderBy("ENGINECONCEPT__PARENTSEMOSSNAME");
		qs.addOrderBy("ENGINECONCEPT__SEMOSSNAME");

		List<Object[]> ret = new ArrayList<>();

		try (IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(engine, qs)) {
			while (wrapper.hasNext()) {
				Object[] data = wrapper.next().getValues();
				boolean isPk = (boolean) data[4];
				if (isPk) {
					data[1] = data[2];
				}
				Object type = data[3];
				if (type != null && (type.equals("DOUBLE") || type.equals("INT"))) {
					data[3] = "NUMBER";
				}
				ret.add(data);
			}
		} catch (Exception e) {
			classLogger.error("Error retrieving tables and columns for database ids {}.", databaseIds, e);
		}

		return ret;
	}

	public static List<String[]> getRelationships(Collection<String> databaseIds) {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINERELATION__ENGINE"));
		qs.addSelector(new QueryColumnSelector("ENGINERELATION__SOURCEPROPERTY"));
		qs.addSelector(new QueryColumnSelector("ENGINERELATION__TARGETPROPERTY"));
		qs.addSelector(new QueryColumnSelector("ENGINERELATION__RELATIONNAME"));
		if (databaseIds != null && !databaseIds.isEmpty()) {
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINERELATION__ENGINE", "==", databaseIds));
		}

		return QueryExecutionUtility.flushRsToListOfStrArray(engine, qs);
	}

	/**
	 * Get a list of connections for a given logical name
	 * 
	 * @param localConceptIds
	 * @param databaseFilter
	 * @return
	 */
	public static List<Map<String, Object>> getDatabaseConnections(List<String> localConceptIds,
			List<String> databaseFilter) {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		List<Map<String, Object>> returnData = new ArrayList<>();

		/*
		 * Grab all the matching tables and columns based on the logical names Once we
		 * have those, we will grab all the relationships for the tables and all the
		 * other columns that we can traverse to
		 */

		// store a list of the parent ids to the Object[] of results
		// that were matches as something
		// that is a possible join
		Map<String, Object[]> parentEquivMap = new HashMap<>();
		Set<String> parentIds = new HashSet<String>();
		List<String> idsForRelationships = new ArrayList<>();
		List<String> idsForProperties = new ArrayList<>();

		// this will give me all the tables that have the logical name or
		// have a column with the logical name
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PARENTSEMOSSNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PARENTPHYSICALNAMEID"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__SEMOSSNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PHYSICALNAMEID"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PK"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__IGNORE_DATA"));
		qs.addExplicitFilter(
				SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__LOCALCONCEPTID", "==", localConceptIds));
		if (databaseFilter != null && !databaseFilter.isEmpty()) {
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__ENGINE", "==", databaseFilter));
		}

		try (IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(engine, qs)) {
			while (wrapper.hasNext()) {
				IHeadersDataRow row = wrapper.next();
				Object[] data = row.getValues();
				// for the purposes of query
				// if it is ignore_data (i.e. the table name matches but not a column)
				// i do not know how to join
				// so we will be ignoring those results for right now
				boolean ignore = (boolean) data[5];
				if (ignore) {
					continue;
				}

				String parentName = (String) data[0];
				String parentId = (String) data[1];
				String columnName = (String) data[2];
				String columnId = (String) data[3];
				boolean pk = (boolean) data[4];

				if (parentId != null) {
					// let me take your parent (table)
					// and see what i can join to from the parent
					// and add the properties as well
					idsForRelationships.add(parentId);
					idsForProperties.add(parentId);
					// and the join for this parent is the column that matches
					parentEquivMap.put(parentId, new Object[] { parentName, columnName, pk });

					// i also want to be able to join to this table directly (this is for rdf/graph)
					parentIds.add(parentId);
				}

				if (parentId == null && pk) {
					// if you are a true concept
					// i can join to you directly
					// and attach to your properties
					// or to your relationships
					idsForRelationships.add(columnId);
					idsForProperties.add(columnId);
					// and the join is the concept itself
					parentEquivMap.put(columnId, new Object[] { columnName, columnName, pk });
				}
			}
		} catch (Exception e) {
			classLogger.error(
					"Error retrieving candidate table/column mappings for local concept ids {} and database filter {}.",
					localConceptIds, databaseFilter, e);
		}

		// let me add in all the concepts that are my parent
		qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINENAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__ENGINE"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__SEMOSSNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PHYSICALNAMEID"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PROPERTY_TYPE"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PK"));
		if (databaseFilter != null && !databaseFilter.isEmpty()) {
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__ENGINE", "==", databaseFilter));
		}
		qs.addExplicitFilter(
				SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__PK", "==", true, PixelDataType.BOOLEAN));
		qs.addExplicitFilter(
				SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__IGNORE_DATA", "==", false, PixelDataType.BOOLEAN));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__PHYSICALNAMEID", "==", parentIds));
		qs.addRelation("ENGINE__ID", "ENGINECONCEPT__ENGINE", "inner.join");
		qs.addOrderBy("ENGINE__ENGINENAME");
		qs.addOrderBy("ENGINECONCEPT__PARENTSEMOSSNAME");
		qs.addOrderBy("ENGINECONCEPT__IGNORE_DATA");
		qs.addOrderBy("ENGINECONCEPT__PK");
		qs.addOrderBy("ENGINECONCEPT__SEMOSSNAME");

		try (IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(engine, qs)) {
			while (wrapper.hasNext()) {
				IHeadersDataRow row = wrapper.next();
				Object[] data = row.getValues();

				String engineName = (String) data[0];
				String engineId = (String) data[1];
				String column = (String) data[2];
				String columnId = (String) data[3];
				String type = (String) data[4];
				boolean pk = (boolean) data[5];

				// these will all have column ids based on the query
				// i will just grab the details
				Object[] equivTableCol = parentEquivMap.get(columnId);

				// if we passed the above test, add the valid connection
				Map<String, Object> mapRow = new HashMap<String, Object>();
				mapRow.put("database_id", engineId);
				mapRow.put("database_name", engineName);
				mapRow.put("table", column);
				mapRow.put("pk", pk);
				mapRow.put("dataType", type);
				mapRow.put("type", "property");
				mapRow.put("equivTable", equivTableCol[0]);
				mapRow.put("equivColumn", equivTableCol[1]);
				mapRow.put("equivPk", equivTableCol[2]);
				returnData.add(mapRow);
			}
		} catch (Exception e) {
			classLogger.error("Error retrieving parent concept connections for database filter {}.", databaseFilter, e);
		}

		// now let me query for all the properties
		qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINENAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__ENGINE"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PARENTSEMOSSNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PARENTPHYSICALNAMEID"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__SEMOSSNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PHYSICALNAMEID"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PROPERTY_TYPE"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PK"));
		if (databaseFilter != null && !databaseFilter.isEmpty()) {
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__ENGINE", "==", databaseFilter));
		}
		qs.addExplicitFilter(
				SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__PARENTPHYSICALNAMEID", "==", idsForProperties));
		qs.addRelation("ENGINE__ID", "ENGINECONCEPT__ENGINE", "inner.join");
		qs.addOrderBy("ENGINE__ENGINENAME");
		qs.addOrderBy("ENGINECONCEPT__PARENTSEMOSSNAME");
		qs.addOrderBy("ENGINECONCEPT__IGNORE_DATA");
		qs.addOrderBy("ENGINECONCEPT__PK");
		qs.addOrderBy("ENGINECONCEPT__SEMOSSNAME");

		try (IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(engine, qs)) {
			while (wrapper.hasNext()) {
				IHeadersDataRow row = wrapper.next();
				Object[] data = row.getValues();

				String engineName = (String) data[0];
				String engineId = (String) data[1];
				String parent = (String) data[2];
				String parentId = (String) data[3];
				String column = (String) data[4];
				String columnId = (String) data[5];
				String type = (String) data[6];
				boolean pk = (boolean) data[7];
//				boolean ignore = (boolean) data[8];

				// these will all have parent ids based on the query
				// i will just grab the details
				Object[] equivTableCol = parentEquivMap.get(parentId);

				// if the property we get is one where the table will be joined on
				// we have to ignore it
				if (equivTableCol[1].equals(column) && parent.equals(equivTableCol[0])) {
					continue;
				}

				// if we passed the above test, add the valid connection
				Map<String, Object> mapRow = new HashMap<>();
				mapRow.put("database_id", engineId);
				mapRow.put("database_name", engineName);
				mapRow.put("table", parent);
				mapRow.put("column", column);
				mapRow.put("pk", pk);
				mapRow.put("dataType", type);
				mapRow.put("type", "property");
				mapRow.put("equivTable", equivTableCol[0]);
				mapRow.put("equivColumn", equivTableCol[1]);
				mapRow.put("equivPk", equivTableCol[2]);
				returnData.add(mapRow);
			}
		} catch (Exception e) {
			classLogger.error("Error retrieving property connections for database filter {}.", databaseFilter, e);
		}

		// let me find up and downstream connections for my equivalent concepts
		qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINENAME"));
		qs.addSelector(new QueryColumnSelector("ENGINERELATION__ENGINE"));
		qs.addSelector(new QueryColumnSelector("ENGINERELATION__SOURCECONCEPTID"));
		qs.addSelector(new QueryColumnSelector("ENGINERELATION__TARGETCONCEPTID"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PARENTSEMOSSNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__SEMOSSNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PK"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__IGNORE_DATA"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PROPERTY_TYPE"));
		qs.addSelector(new QueryColumnSelector("ENGINERELATION__RELATIONNAME"));
		if (databaseFilter != null && !databaseFilter.isEmpty()) {
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINERELATION__ENGINE", "==", databaseFilter));
		}
		qs.addExplicitFilter(
				SimpleQueryFilter.makeColToValFilter("ENGINERELATION__SOURCECONCEPTID", "==", idsForRelationships));
		qs.addRelation("ENGINE__ID", "ENGINERELATION__ENGINE", "inner.join");
		qs.addRelation("ENGINERELATION__TARGETCONCEPTID", "ENGINECONCEPT__PHYSICALNAMEID", "inner.join");
		qs.addOrderBy("ENGINERELATION__ENGINE");

		Map<String, Object[]> relationshipEquivMap = new HashMap<>();

		try (IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(engine, qs)) {
			while (wrapper.hasNext()) {
				IHeadersDataRow row = wrapper.next();
				Object[] data = row.getValues();

				String sourceId = (String) data[2];
				String downstreamId = (String) data[3];
				boolean downstreamIgnore = (boolean) data[7];

				Object[] equivTableCol = parentEquivMap.get(sourceId);
				if (downstreamIgnore) {
					relationshipEquivMap.put(downstreamId, equivTableCol);
					continue;
				}

				String databaseName = (String) data[0];
				String databaseId = (String) data[1];
				String downstreamParent = (String) data[4];
				String downstreamName = (String) data[5];
				boolean downstreamPK = (boolean) data[6];
				String type = (String) data[8];
				String relName = (String) data[9];

				// the downstream nodes
				// mean that the source is the equivalent concept

				// if we passed the above test, add the valid connection
				Map<String, Object> mapRow = new HashMap<>();
				mapRow.put("database_id", databaseId);
				mapRow.put("database_name", databaseName);
				if (downstreamParent == null) {
					mapRow.put("table", downstreamName);
				} else {
					mapRow.put("table", downstreamParent);
					mapRow.put("column", downstreamName);
				}
				mapRow.put("pk", downstreamPK);
				mapRow.put("dataType", type);
				mapRow.put("type", "downstream");
				mapRow.put("relName", relName);
				mapRow.put("equivTable", equivTableCol[0]);
				mapRow.put("equivColumn", equivTableCol[1]);
				mapRow.put("equivPk", equivTableCol[2]);
				returnData.add(mapRow);
			}
		} catch (Exception e) {
			classLogger.error("Error retrieving downstream relationship connections for database filter {}.",
					databaseFilter, e);
		}

//		// let me pull all the relationships that are ignore
//		// this means i use the relationship to pull in any query
//		if(!relationshipEquivMap.isEmpty()) {
//			qs = new SelectQueryStruct();
//			qs.addSelector(new QueryColumnSelector("ENGINE__ENGINENAME"));
//			qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__ENGINE"));
//			qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PARENTSEMOSSNAME"));
//			qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__SEMOSSNAME"));
//			qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PROPERTY_TYPE"));
//			qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PARENTPHYSICALNAMEID"));
//			if(engineFilter != null && !engineFilter.isEmpty()) {
//				qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__ENGINE", "==", engineFilter));
//			}
//			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__PARENTPHYSICALNAMEID", "==", new Vector<String>(relationshipEquivMap.keySet())));
//			qs.addRelation("ENGINE__ID", "ENGINECONCEPT__ENGINE", "inner.join");
//			qs.addOrderBy("ENGINECONCEPT__ENGINE");
//			try {
//				wrapper = WrapperManager.getInstance().getRawWrapper(engine, qs);
//				while(wrapper.hasNext()) {
//					IHeadersDataRow row = wrapper.next();
//					Object[] data = row.getValues();
//					
//					String engineName = (String) data[0];
//					String engineId = (String) data[1];
//					String parentName = (String) data[2];
//					String name = (String) data[3];
//					String type = (String) data[4];
//					String parentId = (String) data[5];
//		
//					Object[] equivTableCol = relationshipEquivMap.get(parentId);
//					
//					// if we passed the above test, add the valid connection
//					Map<String, Object> mapRow = new HashMap<>();
//					mapRow.put("app_id", engineId);
//					mapRow.put("app_name", engineName);
//					mapRow.put("table", parentName);
//					mapRow.put("column", name);
//					mapRow.put("pk", false);
//					mapRow.put("dataType", type);
//					mapRow.put("type", "downstream");
////					mapRow.put("relName", relName);
//					mapRow.put("equivTable", equivTableCol[0]);
//					mapRow.put("equivColumn", equivTableCol[1]);
//					mapRow.put("equivPk", equivTableCol[2]);
//					returnData.add(mapRow);
//				}
//			} catch (Exception e) {
// logger.error("{}", e.getMessage(), e);
//			} finally {
//				if(wrapper != null) {
//					wrapper.cleanUp();
//				}
//			}
//		}
		relationshipEquivMap.clear();

		// let me find up and upstream connections for my equivalent concepts
		qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINENAME"));
		qs.addSelector(new QueryColumnSelector("ENGINERELATION__ENGINE"));
		qs.addSelector(new QueryColumnSelector("ENGINERELATION__TARGETCONCEPTID"));
		qs.addSelector(new QueryColumnSelector("ENGINERELATION__SOURCECONCEPTID"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PARENTSEMOSSNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__SEMOSSNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PK"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__IGNORE_DATA"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PROPERTY_TYPE"));
		qs.addSelector(new QueryColumnSelector("ENGINERELATION__RELATIONNAME"));
		if (databaseFilter != null && !databaseFilter.isEmpty()) {
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINERELATION__ENGINE", "==", databaseFilter));
		}
		qs.addExplicitFilter(
				SimpleQueryFilter.makeColToValFilter("ENGINERELATION__TARGETCONCEPTID", "==", idsForRelationships));
		qs.addRelation("ENGINE__ID", "ENGINERELATION__ENGINE", "inner.join");
		qs.addRelation("ENGINERELATION__SOURCECONCEPTID", "ENGINECONCEPT__PHYSICALNAMEID", "inner.join");
		qs.addOrderBy("ENGINERELATION__ENGINE");

		try (IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(engine, qs)) {
			while (wrapper.hasNext()) {
				IHeadersDataRow row = wrapper.next();
				Object[] data = row.getValues();

				String targetId = (String) data[2];
				String upstreamId = (String) data[3];
				boolean upstreamIgnore = (boolean) data[7];

				Object[] equivTableCol = parentEquivMap.get(targetId);
				if (upstreamIgnore) {
					relationshipEquivMap.put(upstreamId, equivTableCol);
					continue;
				}

				String databaseName = (String) data[0];
				String databaseId = (String) data[1];
				String upstreamParent = (String) data[4];
				String upstreamName = (String) data[5];
				boolean upstreamPK = (boolean) data[6];
				String type = (String) data[8];
				String relName = (String) data[9];

				// the downstream nodes
				// mean that the source is the equivalent concept

				// if we passed the above test, add the valid connection
				Map<String, Object> mapRow = new HashMap<>();
				mapRow.put("database_id", databaseId);
				mapRow.put("database_name", databaseName);
				if (upstreamParent == null) {
					mapRow.put("table", upstreamName);
				} else {
					mapRow.put("table", upstreamParent);
					mapRow.put("column", upstreamName);
				}
				mapRow.put("pk", upstreamPK);
				mapRow.put("dataType", type);
				mapRow.put("type", "upstream");
				mapRow.put("relName", relName);
				mapRow.put("equivTable", equivTableCol[0]);
				mapRow.put("equivColumn", equivTableCol[1]);
				mapRow.put("equivPk", equivTableCol[2]);
				returnData.add(mapRow);
			}
		} catch (Exception e1) {
			classLogger.error("Error retrieving upstream relationship connections for database filter {}.",
					databaseFilter, e1);
		}

//		// let me pull all the relationships that are ignore
//		// this means i use the relationship to pull in any query
//		if(!relationshipEquivMap.isEmpty()) {
//			qs = new SelectQueryStruct();
//			qs.addSelector(new QueryColumnSelector("ENGINE__ENGINENAME"));
//			qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__ENGINE"));
//			qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PARENTSEMOSSNAME"));
//			qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__SEMOSSNAME"));
//			qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PROPERTY_TYPE"));
//			qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PARENTPHYSICALNAMEID"));
//			if(engineFilter != null && !engineFilter.isEmpty()) {
//				qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__ENGINE", "==", engineFilter));
//			}
//			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__PARENTPHYSICALNAMEID", "==", new Vector<String>(relationshipEquivMap.keySet())));
//			qs.addRelation("ENGINE__ID", "ENGINECONCEPT__ENGINE", "inner.join");
//			qs.addOrderBy("ENGINECONCEPT__ENGINE");
//			try {
//				wrapper = WrapperManager.getInstance().getRawWrapper(engine, qs);
//				while(wrapper.hasNext()) {
//					IHeadersDataRow row = wrapper.next();
//					Object[] data = row.getValues();
//					
//					String engineName = (String) data[0];
//					String engineId = (String) data[1];
//					String parentName = (String) data[2];
//					String name = (String) data[3];
//					String type = (String) data[4];
//					String parentId = (String) data[5];
//		
//					Object[] equivTableCol = relationshipEquivMap.get(parentId);
//					
//					// if we passed the above test, add the valid connection
//					Map<String, Object> mapRow = new HashMap<>();
//					mapRow.put("app_id", engineId);
//					mapRow.put("app_name", engineName);
//					mapRow.put("table", parentName);
//					mapRow.put("column", name);
//					mapRow.put("pk", false);
//					mapRow.put("dataType", type);
//					mapRow.put("type", "upstream");
////					mapRow.put("relName", relName);
//					mapRow.put("equivTable", equivTableCol[0]);
//					mapRow.put("equivColumn", equivTableCol[1]);
//					mapRow.put("equivPk", equivTableCol[2]);
//					returnData.add(mapRow);
//				}
//			} catch (Exception e) {
// logger.error("{}", e.getMessage(), e);
//			} finally {
//				if(wrapper != null) {
//					wrapper.cleanUp();
//				}
//			}
//		}
		relationshipEquivMap.clear();

		return returnData;
	}

	/**
	 * Get the metamodel
	 * 
	 * @param databaseId
	 * @param includeDataTypes
	 * @return
	 */
	public static Map<String, Object> getMetamodelRDBMS(String databaseId, boolean includeDataTypes) {
		// TODO: should setup to return the physical name ids
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		// idHash - physical ID to the name of the node
		Map<String, MetamodelVertex> nodeHash = new HashMap<>();

		Map<String, String> physicalDataTypes = new HashMap<>();
		Map<String, String> dataTypes = new HashMap<>();
		Map<String, String> additionalDataTypes = new HashMap<>();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__SEMOSSNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PARENTSEMOSSNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PHYSICALNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PARENTPHYSICALNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__IGNORE_DATA"));
		if (includeDataTypes) {
			qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__ORIGINAL_TYPE"));
			qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PROPERTY_TYPE"));
			qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__ADDITIONAL_TYPE"));
		}
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__ENGINE", "==", databaseId));

		try (IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(engine, qs)) {
			while (wrapper.hasNext()) {
				Object[] row = wrapper.next().getValues();

				String semossName = (String) row[0];
				String physicalName = (String) row[2];
				String parentSemossName = (String) row[1];
				String parentPhysicalName = (String) row[3];
				boolean ignoreData = (boolean) row[4];

				MetamodelVertex node = null;
				// if already there, should we still add it ?
				if (parentSemossName != null) {
					// this has a parent
					if (nodeHash.containsKey(parentSemossName)) {
						node = nodeHash.get(parentSemossName);
					} else {
						node = new MetamodelVertex(parentSemossName);
						nodeHash.put(parentSemossName, node);
					}
				} else {
					// this is the parent
					if (nodeHash.containsKey(semossName)) {
						node = nodeHash.get(semossName);
					} else {
						node = new MetamodelVertex(semossName);
						nodeHash.put(semossName, node);
					}
				}

				String uniqueName = semossName;
				if (parentSemossName != null) {
					uniqueName = parentSemossName + "__" + uniqueName;
					node.addProperty(semossName);
				}

				if (includeDataTypes && !ignoreData) {
					if (row[5] != null) {
						String origType = row[5].toString();
						if (origType.contains("TYPE:")) {
							origType = origType.replace("TYPE:", "");
						}
						physicalDataTypes.put(uniqueName, origType);
					}
					if (row[6] != null) {
						String cleanType = row[6].toString();
						dataTypes.put(uniqueName, cleanType);
					}
					if (row[7] != null) {
						String additionalType = row[7].toString();
						additionalDataTypes.put(uniqueName, additionalType);
					}
				}
			}
		} catch (Exception e1) {
			classLogger.error("Error retrieving metamodel nodes for database {}.", databaseId, e1);
		}

		Map<String, Map<String, String>> edgeHash = new Hashtable<>();
		qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINERELATION__SOURCEPROPERTY"));
		qs.addSelector(new QueryColumnSelector("ENGINERELATION__TARGETPROPERTY"));
		qs.addSelector(new QueryColumnSelector("ENGINERELATION__RELATIONNAME"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINERELATION__ENGINE", "==", databaseId));
		try (IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(engine, qs)) {
			while (wrapper.hasNext()) {
				Object[] row = wrapper.next().getValues();
				String startName = row[0].toString();
				String endName = row[1].toString();
				String relName = row[2].toString();

				Map<String, String> newEdge = new Hashtable<>();
				// need to check to see if the idHash has it else put it in
				newEdge.put("source", startName);
				newEdge.put("target", endName);
				newEdge.put("relation", relName);
				if (relName.contains(".")) {
					String[] split = relName.split("[.]");
					if (split.length == 4) {
						if (startName.equals(split[0])) {
							newEdge.put("sourceColumn", split[1]);
							newEdge.put("targetColumn", split[3]);
						} else {
							newEdge.put("sourceColumn", split[3]);
							newEdge.put("targetColumn", split[1]);
						}
					} else if (split.length == 6) {
						if (startName.equals(split[1])) {
							newEdge.put("sourceColumn", split[2]);
							newEdge.put("targetColumn", split[5]);
						} else {
							newEdge.put("sourceColumn", split[5]);
							newEdge.put("targetColumn", split[2]);
						}
					}
				}
				edgeHash.put(endName + "-" + endName + "-" + relName, newEdge);
			}
		} catch (Exception e) {
			classLogger.error("Error retrieving metamodel edges for database {}.", databaseId, e);
		}

		Map<String, Object> finalHash = new Hashtable<>();
		finalHash.put("nodes", nodeHash.values().toArray());
		finalHash.put("edges", edgeHash.values().toArray());
		if (includeDataTypes) {
			finalHash.put("physicalTypes", physicalDataTypes);
			finalHash.put("dataTypes", dataTypes);
			finalHash.put("additionalDataTypes", additionalDataTypes);
		}
		return finalHash;
	}

	/**
	 * Get the properties for a given concept for a specific database THIS IS THE
	 * SAME QUERY AS {@link #getConceptPropertiesRDBMS} BUT DIFFERENT RETURN
	 * 
	 * @param conceptName
	 * @param engineId
	 * @return
	 */
	public static Map<String, List<String>> getConceptProperties(List<String> logicalNames, String databaseFilter) {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__ENGINE"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PARENTSEMOSSNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PARENTPHYSICALNAMEID"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__SEMOSSNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PHYSICALNAMEID"));
		if (databaseFilter != null && !databaseFilter.isEmpty()) {
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__ENGINE", "==", databaseFilter));
		}
		{
			SelectQueryStruct subQs = new SelectQueryStruct();
			// store first and fill in sub query after
			qs.addExplicitFilter(
					SimpleQueryFilter.makeColToSubQuery("ENGINECONCEPT__PARENTPHYSICALNAMEID", "==", subQs));

			// fill in the sub query with the necessary column output + filters
			subQs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PARENTPHYSICALNAMEID"));
			// we have a sub query again
			SelectQueryStruct subQs2 = new SelectQueryStruct();
			subQs.addExplicitFilter(SimpleQueryFilter.makeColToSubQuery("ENGINECONCEPT__LOCALCONCEPTID", "==", subQs2));

			// fill in the second sub query with the necessary column output + filters
			subQs2.addSelector(new QueryColumnSelector("CONCEPT__LOCALCONCEPTID"));
			subQs2.addExplicitFilter(
					SimpleQueryFilter.makeColToValFilter("CONCEPT__CONCEPTUALNAME", "==", logicalNames));
		}
		qs.addOrderBy(new QueryColumnOrderBySelector("ENGINECONCEPT__ENGINE"));
		qs.addOrderBy(new QueryColumnOrderBySelector("ENGINECONCEPT__PK"));

		Map<String, List<String>> queryData = new HashMap<>();

		try (IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(engine, qs)) {
			while (wrapper.hasNext()) {
				IHeadersDataRow data = wrapper.next();
				// keeps the id to the concept name
				Object[] row = data.getValues();

				String engineId = (String) row[0];
				String parentName = (String) row[1];
				String parentPhysicalId = (String) row[2];
				String columnName = (String) row[3];
				String columnPhysicalId = (String) row[4];

				// get or create the vertex
				List<String> propList = null;
				if (queryData.containsKey(parentName)) {
					propList = queryData.get(parentName);
				} else {
					propList = new ArrayList<>();
					// add to the engine map
					queryData.put(parentName, propList);
				}

				// add the property conceptual name
				propList.add(columnName);
			}
		} catch (Exception e) {
			classLogger.error("Error retrieving concept properties for logical names {} and database filter {}.",
					logicalNames, databaseFilter, e);
		}

		return queryData;
	}

	/**
	 * Get the properties for a given concept across all the databases THIS IS THE
	 * SAME QUERY AS {@link #getConceptProperties} BUT DIFFERENT RETURN
	 * 
	 * @param conceptName
	 * @param engineId    optional filter for the properties
	 * @return
	 */
	public static Map<String, Object[]> getConceptProperties(List<String> logicalNames, List<String> databaseFilter) {
		// query to get all the physical name ids that tie to the parent
		// and then pull all of their properties

		Map<String, Object[]> returnHash = new TreeMap<>();
		Map<String, Map<String, MetamodelVertex>> queryData = new TreeMap<>();

		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();
		{
			// for tabular databases
			// we grab the parent
			SelectQueryStruct qs = new SelectQueryStruct();
			qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__ENGINE"));
			qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PARENTSEMOSSNAME"));
			qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PARENTPHYSICALNAMEID"));
			qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__SEMOSSNAME"));
			qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PHYSICALNAMEID"));
			if (databaseFilter != null && !databaseFilter.isEmpty()) {
				qs.addExplicitFilter(
						SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__ENGINE", "==", databaseFilter));
			}
			{
				SelectQueryStruct subQs = new SelectQueryStruct();
				// store first and fill in sub query after
				qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__IGNORE_DATA", "==", true,
						PixelDataType.BOOLEAN));
				qs.addExplicitFilter(
						SimpleQueryFilter.makeColToSubQuery("ENGINECONCEPT__PARENTPHYSICALNAMEID", "==", subQs));

				// fill in the sub query with the necessary column output + filters
				// NOTE::: THIS IS THE MAIN DIFFERENCE FROM THE BELOW
				// THIS REQUIRED THE PARENT TO BE THE PARENTPHYSICALNAMEID
				// SINCE THERE IS ALWAYS A PARENT ID IN LOCAL MASTER
				subQs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PARENTPHYSICALNAMEID"));
				// we have a sub query again
				SelectQueryStruct subQs2 = new SelectQueryStruct();
				subQs.addExplicitFilter(
						SimpleQueryFilter.makeColToSubQuery("ENGINECONCEPT__LOCALCONCEPTID", "==", subQs2));

				// fill in the second sub query with the necessary column output + filters
				subQs2.addSelector(new QueryColumnSelector("CONCEPT__LOCALCONCEPTID"));
				subQs2.addExplicitFilter(
						SimpleQueryFilter.makeColToValFilter("CONCEPT__CONCEPTUALNAME", "==", logicalNames));
			}
			qs.addOrderBy(new QueryColumnOrderBySelector("ENGINECONCEPT__ENGINE"));
			qs.addOrderBy(new QueryColumnOrderBySelector("ENGINECONCEPT__PK"));

			try (IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(engine, qs)) {
				while (wrapper.hasNext()) {
					IHeadersDataRow data = wrapper.next();
					// keeps the id to the concept name
					Object[] row = data.getValues();

					String databaseId = (String) row[0];
					String parentName = (String) row[1];
					String parentPhysicalId = (String) row[2];
					String columnName = (String) row[3];
					String columnPhysicalId = (String) row[4];

					Map<String, MetamodelVertex> databaseMap = null;
					if (queryData.containsKey(databaseId)) {
						databaseMap = queryData.get(databaseId);
					} else {
						databaseMap = new TreeMap<>();
						// add to query data map
						queryData.put(databaseId, databaseMap);
					}

					// get or create the vertex
					MetamodelVertex vert = null;
					if (databaseMap.containsKey(parentName)) {
						vert = databaseMap.get(parentName);
					} else {
						vert = new MetamodelVertex(parentName);
						// add to the engine map
						databaseMap.put(parentName, vert);
					}

					// add the property conceptual name
					vert.addProperty(columnName);
				}
			} catch (Exception e) {
				classLogger.error(
						"Error retrieving tabular concept properties for logical names {} and database filter {}.",
						logicalNames, databaseFilter, e);
			}
		}
		{
			// for graph/RDF databases
			// we grab the node itself where it is not ignored
			SelectQueryStruct qs = new SelectQueryStruct();
			qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__ENGINE"));
			qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PARENTSEMOSSNAME"));
			qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PARENTPHYSICALNAMEID"));
			qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__SEMOSSNAME"));
			qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PHYSICALNAMEID"));
			if (databaseFilter != null && !databaseFilter.isEmpty()) {
				qs.addExplicitFilter(
						SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__ENGINE", "==", databaseFilter));
			}
			{
				SelectQueryStruct subQs = new SelectQueryStruct();
				// store first and fill in sub query after
				qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__IGNORE_DATA", "==", false,
						PixelDataType.BOOLEAN));
				qs.addExplicitFilter(
						SimpleQueryFilter.makeColToSubQuery("ENGINECONCEPT__PARENTPHYSICALNAMEID", "==", subQs));

				// fill in the sub query with the necessary column output + filters
				// NOTE::: THIS IS THE MAIN DIFFERENCE FROM THE ABOVE
				// THIS REQUIRED THE PARENT TO BE THE PHYSICALNAMEID
				// SINCE NODES/CONCEPTS DO NOT HAVE PARENTS IN LOCAL MASTER
				subQs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PHYSICALNAMEID"));
				// we have a sub query again
				SelectQueryStruct subQs2 = new SelectQueryStruct();
				subQs.addExplicitFilter(
						SimpleQueryFilter.makeColToSubQuery("ENGINECONCEPT__LOCALCONCEPTID", "==", subQs2));

				// fill in the second sub query with the necessary column output + filters
				subQs2.addSelector(new QueryColumnSelector("CONCEPT__LOCALCONCEPTID"));
				subQs2.addExplicitFilter(
						SimpleQueryFilter.makeColToValFilter("CONCEPT__CONCEPTUALNAME", "==", logicalNames));
			}
			qs.addOrderBy(new QueryColumnOrderBySelector("ENGINECONCEPT__ENGINE"));
			qs.addOrderBy(new QueryColumnOrderBySelector("ENGINECONCEPT__PK"));

			try (IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(engine, qs)) {
				while (wrapper.hasNext()) {
					IHeadersDataRow data = wrapper.next();
					// keeps the id to the concept name
					Object[] row = data.getValues();

					String databaseId = (String) row[0];
					String parentName = (String) row[1];
					String parentPhysicalId = (String) row[2];
					String columnName = (String) row[3];
					String columnPhysicalId = (String) row[4];

					Map<String, MetamodelVertex> databaseMap = null;
					if (queryData.containsKey(databaseId)) {
						databaseMap = queryData.get(databaseId);
					} else {
						databaseMap = new TreeMap<>();
						// add to query data map
						queryData.put(databaseId, databaseMap);
					}

					// get or create the vertex
					MetamodelVertex vert = null;
					if (databaseMap.containsKey(parentName)) {
						vert = databaseMap.get(parentName);
					} else {
						vert = new MetamodelVertex(parentName);
						// add to the engine map
						databaseMap.put(parentName, vert);
					}

					// add the property conceptual name
					vert.addProperty(columnName);
				}
			} catch (Exception e) {
				classLogger.error(
						"Error retrieving graph concept properties for logical names {} and database filter {}.",
						logicalNames, databaseFilter, e);
			}
		}

		for (String databaseName : queryData.keySet()) {
			returnHash.put(databaseName, queryData.get(databaseName).values().toArray());
		}

		return returnHash;
	}

	/**
	 * Get the list of connected concepts for a given concept
	 * 
	 * Direction upstream/downstream is always in reference to the node being
	 * searched For example, if the relationship in the direction Title -> Genre The
	 * result would be { upstream -> [Genre] } because Title is upstream of Genre
	 * 
	 * @param conceptType
	 * @return
	 */
	@Deprecated
	public static Map getConnectedConceptsRDBMS(List<String> conceptLogicalNames, List<String> databaseFilters) {
		// I technically need to do 3 queries
		// first one is get the localconceptid / physicalids for all of these
		// second is the upstream
		// third is the downstream

		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		// select e.enginename, ec.engine, c.logicalname, ec.physicalnameid from concept
		// c, engineconcept ec, engine e where c.logicalname in ('Title') and
		// c.localconceptid=ec.localconceptid and e.id = ec.engine
//		String conceptMasterQuery = "select ec.engine, c.conceptualname, ec.physicalnameid, ec.physicalname "
//				+ "from concept c, engineconcept ec "
//				+ "where c.logicalname in " + conceptString
//				+ (engineFilters != null ? (" and ec.engine in " + engineString) + " " : "")
//				+ "and c.localconceptid=ec.localconceptid";

		// id to concept
		Hashtable<String, String> idToName = new Hashtable<>();

		// this is the final return object
		// engine > concept > downstream > items
		// retMap > conceptSpecific > stream
		Map<String, Map> retMap = new TreeMap<>();

		{
			SelectQueryStruct qs = new SelectQueryStruct();
			qs.addSelector(new QueryColumnOrderBySelector("ENGINECONCEPT__ENGINE"));
			qs.addSelector(new QueryColumnOrderBySelector("CONCEPT__CONCEPTUALNAME"));
			qs.addSelector(new QueryColumnOrderBySelector("ENGINECONCEPT__PHYSICALNAMEID"));
			qs.addSelector(new QueryColumnOrderBySelector("ENGINECONCEPT__PHYSICALNAME"));
			if (databaseFilters != null && !databaseFilters.isEmpty()) {
				qs.addExplicitFilter(
						SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__ENGINE", "==", databaseFilters));
			}
			qs.addExplicitFilter(
					SimpleQueryFilter.makeColToValFilter("CONCEPT__LOGICALNAME", "==", conceptLogicalNames));
			qs.addRelation("CONCEPT__LOCALCONCEPTID", "ENGINECONCEPT__LOCALCONCEPTID", "inner.join");

			try (IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(engine, qs)) {
				while (wrapper.hasNext()) {
					Object[] row = wrapper.next().getValues();

					String engineId = row[0].toString();
					String conceptualName = row[1].toString();
					String physicalNameId = row[2].toString();
					String equivalentConcept = row[3].toString();

					// put the id for future reference
					// no reason why we cannot cache but..
					idToName.put(physicalNameId, conceptualName);

					Map<String, Object> conceptSpecific = null;
					if (retMap.containsKey(engineId)) {
						conceptSpecific = retMap.get(engineId);
					} else {
						conceptSpecific = new TreeMap<>();
					}
					retMap.put(engineId, conceptSpecific);

					Hashtable<String, String> stream = new Hashtable<>();
					stream.put("equivalentConcept", equivalentConcept);

					conceptSpecific.put(conceptualName, stream);
					retMap.put(engineId, conceptSpecific);
				}
			} catch (Exception e) {
				classLogger.error(
						"Error retrieving base concept mappings for logical names {} and database filters {}.",
						conceptLogicalNames, databaseFilters, e);
			}
		}

		// select distinct e.enginename, 'Title', 'downstream' as downstream,
		// er.relationname, c.logicalname , er.engine, er.targetconceptid,
		// ec.physicalname from enginerelation er, engineconcept ec, concept c, engine e
		// where er.sourceconceptid in (select physicalnameid from engineconcept where
		// localconceptid in (select localconceptid from concept where logicalname in
		// ('Title')))
		// and ec.physicalnameid=er.targetconceptid and
		// c.localconceptid=ec.localconceptid and e.id=er.engine;

		// now time to run the upstream and downstream queries
//		String downstreamQuery = "select distinct ec.engine, er.sourceconceptid, 'upstream' as upstream, "
//				+ "er.relationname, c.conceptualname , er.engine, er.targetconceptid, ec.physicalname "
//				+ "from enginerelation er, engineconcept ec, concept c "
//				+ "where "
//				+ (engineFilters != null ? (" ec.engine in " + engineString + " and ") : "")
//				+ "er.sourceconceptid in (select physicalnameid from engineconcept where localconceptid in "
//				+ "(select localconceptid from concept where logicalname in " + conceptString + ")) "
//				+ "and ec.physicalnameid=er.targetconceptid and c.localconceptid=ec.localconceptid;";

		{
			SelectQueryStruct downQs = new SelectQueryStruct();
			downQs.addSelector(new QueryColumnSelector("ENGINECONCEPT__ENGINE"));
			downQs.addSelector(new QueryColumnSelector("ENGINERELATION__SOURCECONCEPTID"));
			downQs.addSelector(new QueryConstantSelector("upstream"));
			downQs.addSelector(new QueryColumnSelector("ENGINERELATION__RELATIONNAME"));
			downQs.addSelector(new QueryColumnSelector("CONCEPT__CONCEPTUALNAME"));
			downQs.addSelector(new QueryColumnSelector("ENGINERELATION__ENGINE"));
			downQs.addSelector(new QueryColumnSelector("ENGINERELATION__TARGETCONCEPTID"));
			downQs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PHYSICALNAME"));
			if (databaseFilters != null && !databaseFilters.isEmpty()) {
				downQs.addExplicitFilter(
						SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__ENGINE", "==", databaseFilters));
			}
			{
				SelectQueryStruct subQs = new SelectQueryStruct();
				downQs.addExplicitFilter(
						SimpleQueryFilter.makeColToSubQuery("ENGINERELATION__SOURCECONCEPTID", "==", subQs));

				// fill in sub query selector + filter
				subQs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PHYSICALNAMEID"));
				SelectQueryStruct subQs2 = new SelectQueryStruct();
				subQs.addExplicitFilter(
						SimpleQueryFilter.makeColToSubQuery("ENGINECONCEPT__LOCALCONCEPTID", "==", subQs2));

				// fill in second sub query selector + filter
				subQs2.addSelector(new QueryColumnOrderBySelector("CONCEPT__LOCALCONCEPTID"));
				subQs2.addExplicitFilter(
						SimpleQueryFilter.makeColToValFilter("CONCEPT__LOGICALNAME", "==", conceptLogicalNames));
			}
			downQs.addRelation("ENGINECONCEPT__PHYSICALNAMEID", "ENGINERELATION__TARGETCONCEPTID", "inner.join");
			downQs.addRelation("CONCEPT__LOCALCONCEPTID", "ENGINECONCEPT__LOCALCONCEPTID", "inner.join");

			try (IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(engine, downQs)) {
				while (wrapper.hasNext()) {
					Object[] row = wrapper.next().getValues();

					String databaseId = row[0].toString();
					String coreConceptId = row[1].toString();
					String relationName = row[3].toString();
					String streamConceptName = row[4].toString();
					String streamPhysicalName = row[7].toString();

					// this is the main concept
					String coreConceptName = idToName.get(coreConceptId);

					Map<String, Map> databaseSpecific = retMap.get(databaseId);
					Map<String, Object> conceptSpecific = databaseSpecific.get(coreConceptName);

					Set<String> downstreams = new TreeSet<>();
					Set<String> physicalNames = new TreeSet<>();

					if (conceptSpecific.containsKey("upstream")) {
						downstreams = (Set<String>) conceptSpecific.get("upstream");
					}
					downstreams.add(streamConceptName);

					if (conceptSpecific.containsKey("physical")) {
						physicalNames = (Set<String>) conceptSpecific.get("physical");
					}
					physicalNames.add(streamPhysicalName);
					conceptSpecific.put("upstream", downstreams);
					conceptSpecific.put("physical", physicalNames);
					databaseSpecific.put(coreConceptName, conceptSpecific);
					retMap.put(databaseId, databaseSpecific);
				}
			} catch (Exception e) {
				classLogger.error(
						"Error retrieving upstream concept connections for logical names {} and database filters {}.",
						conceptLogicalNames, databaseFilters, e);
			}
		}

		// now time to run the upstream and downstream queries
//		String upstreamQuery = "select distinct ec.engine, er.targetconceptid, 'downstream' as downstream,  "
//				+ "er.relationname,  c.conceptualname , er.engine, er.sourceconceptid, ec.physicalname "
//				+ "from enginerelation er, engineconcept ec, concept c "
//				+ "where "
//				+ (engineFilters != null ? (" ec.engine in " + engineString + " and ") : "")
//				+ "er.targetconceptid in (select physicalnameid from engineconcept where localconceptid in "
//				+ "(select localconceptid from concept where logicalname in " + conceptString + ")) "
//				+ "and ec.physicalnameid=er.sourceconceptid and c.localconceptid=ec.localconceptid";

		{
			SelectQueryStruct upQs = new SelectQueryStruct();
			upQs.addSelector(new QueryColumnSelector("ENGINECONCEPT__ENGINE"));
			upQs.addSelector(new QueryColumnSelector("ENGINERELATION__TARGETCONCEPTID"));
			upQs.addSelector(new QueryConstantSelector("downstream"));
			upQs.addSelector(new QueryColumnSelector("ENGINERELATION__RELATIONNAME"));
			upQs.addSelector(new QueryColumnSelector("CONCEPT__CONCEPTUALNAME"));
			upQs.addSelector(new QueryColumnSelector("ENGINERELATION__ENGINE"));
			upQs.addSelector(new QueryColumnSelector("ENGINERELATION__SOURCECONCEPTID"));
			upQs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PHYSICALNAME"));
			if (databaseFilters != null && !databaseFilters.isEmpty()) {
				upQs.addExplicitFilter(
						SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__ENGINE", "==", databaseFilters));
			}
			{
				SelectQueryStruct subQs = new SelectQueryStruct();
				upQs.addExplicitFilter(
						SimpleQueryFilter.makeColToSubQuery("ENGINERELATION__TARGETCONCEPTID", "==", subQs));

				// fill in sub query selector + filter
				subQs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PHYSICALNAMEID"));
				SelectQueryStruct subQs2 = new SelectQueryStruct();
				subQs.addExplicitFilter(
						SimpleQueryFilter.makeColToSubQuery("ENGINECONCEPT__LOCALCONCEPTID", "==", subQs2));

				// fill in second sub query selector + filter
				subQs2.addSelector(new QueryColumnOrderBySelector("CONCEPT__LOCALCONCEPTID"));
				subQs2.addExplicitFilter(
						SimpleQueryFilter.makeColToValFilter("CONCEPT__LOGICALNAME", "==", conceptLogicalNames));
			}
			upQs.addRelation("ENGINECONCEPT__PHYSICALNAMEID", "ENGINERELATION__SOURCECONCEPTID", "inner.join");
			upQs.addRelation("CONCEPT__LOCALCONCEPTID", "ENGINECONCEPT__LOCALCONCEPTID", "inner.join");

			try (IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(engine, upQs)) {
				while (wrapper.hasNext()) {
					Object[] row = wrapper.next().getValues();

					String databaseId = row[0].toString();
					String coreConceptId = row[1].toString();
					String relationName = row[3].toString();
					String streamConceptName = row[4].toString();
					String streamPhysicalName = row[7].toString();

					String coreConceptName = idToName.get(coreConceptId);

					Map<String, Map> databaseSpecific = retMap.get(databaseId);
					Map<String, Object> conceptSpecific = databaseSpecific.get(coreConceptName);

					Set<String> upstreams = new TreeSet<>();
					Set<String> physicalNames = new TreeSet<>();

					if (conceptSpecific.containsKey("downstream")) {
						upstreams = (Set<String>) conceptSpecific.get("downstream");
					}
					upstreams.add(streamConceptName);

					if (conceptSpecific.containsKey("physical")) {
						physicalNames = (Set<String>) conceptSpecific.get("physical");
					}

					physicalNames.add(streamPhysicalName);
					conceptSpecific.put("downstream", upstreams);
					conceptSpecific.put("physical", physicalNames);
					databaseSpecific.put(coreConceptName, conceptSpecific);
					retMap.put(databaseId, databaseSpecific);
				}
			} catch (Exception e) {
				classLogger.error(
						"Error retrieving downstream concept connections for logical names {} and database filters {}.",
						conceptLogicalNames, databaseFilters, e);
			}
		}

		return retMap;
	}

	/**
	 * Get the list of unique engine ids
	 * 
	 * @return
	 */
	public static List<String> getAllDatabaseIds() {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINE__ID"));
		return QueryExecutionUtility.flushToListString(engine, qs);
	}

	/**
	 * Get an engine alias for an id
	 * 
	 * @return
	 */
	public static String getDatabaseAliasForId(String id) {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINENAME"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__ID", "==", id));
		return QueryExecutionUtility.flushToString(engine, qs);
	}

	/**
	 * Get engine id to engine name map
	 * 
	 * @return
	 */
	public static Map<String, String> getDatabaseIdToAliasMap() {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINE__ID"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINENAME"));

		Map<String, String> retMap = new HashMap<>();
		try (IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(engine, qs)) {
			while (wrapper.hasNext()) {
				Object[] row = wrapper.next().getValues();
				retMap.put(row[0] + "", row[1] + "");
			}
		} catch (Exception e) {
			classLogger.error("Error retrieving database id to alias map.", e);
		}

		return retMap;
	}

	/**
	 * Get the list of concepts for a given engine
	 * 
	 * @param databaseId
	 * @return
	 */
	public static Set<String> getConceptsWithinDatabaseRDBMS(String databaseId) {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__SEMOSSNAME"));
		qs.addExplicitFilter(
				SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__PK", "==", true, PixelDataType.BOOLEAN));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__ENGINE", "==", databaseId));
		qs.addOrderBy(new QueryColumnOrderBySelector("ENGINECONCEPT__SEMOSSNAME"));
		return QueryExecutionUtility.flushToSetString(engine, qs, true);
	}

	/**
	 * Get the list of concepts for a given engine
	 * 
	 * @param databaseId
	 * @return
	 */
	public static Collection<String> getSelectorsWithinDatabaseRDBMS(String databaseId) {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PARENTSEMOSSNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__SEMOSSNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PK"));
		qs.addExplicitFilter(
				SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__IGNORE_DATA", "==", false, PixelDataType.BOOLEAN));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__ENGINE", "==", databaseId));
		qs.addOrderBy(new QueryColumnOrderBySelector("ENGINECONCEPT__PK", "desc"));

		Set<String> selectors = new TreeSet<>();
		try (IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(engine, qs)) {
			while (wrapper.hasNext()) {
				Object[] row = wrapper.next().getValues();
				if (row[0] == null) {
					selectors.add(row[1].toString());
				} else {
					selectors.add(row[0] + "__" + row[1].toString());
				}
			}
		} catch (Exception e) {
			classLogger.error("Error retrieving selectors for database {}.", databaseId, e);
		}

		return selectors;
	}

	/**
	 * Get the data type
	 * 
	 * @param databaseId
	 * @param pixelName
	 * @param parentPixelName
	 * @return
	 */
	public static String getBasicDataType(String databaseId, String pixelName, String parentPixelName) {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PROPERTY_TYPE"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__ENGINE", "==", databaseId));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__SEMOSSNAME", "==", pixelName));
		if (parentPixelName != null && !parentPixelName.isEmpty()) {
			// additional filters
			qs.addExplicitFilter(
					SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__PARENTSEMOSSNAME", "==", parentPixelName));
		}

		return QueryExecutionUtility.flushToString(engine, qs);
	}

	/**
	 * Get the additional data type
	 * 
	 * @param databaseId
	 * @param conceptualName
	 * @param parentConceptualName
	 * @return
	 */
	public static String getAdditionalDataType(String databaseId, String conceptualName, String parentConceptualName) {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__ADDITIONAL_TYPE"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__ENGINE", "==", databaseId));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__SEMOSSNAME", "==", conceptualName));
		if (parentConceptualName != null && !parentConceptualName.isEmpty()) {
			// additional filters
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__PARENTSEMOSSNAME", "==",
					parentConceptualName));
		}

		return QueryExecutionUtility.flushToString(engine, qs);
	}

	/**
	 * 
	 * @param databaseId
	 * @return
	 */

	public static Map<String, List<String>> getDatabaseLogicalNames(String databaseId) {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		Map<String, List<String>> engineLogicalNames = new HashMap<>();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PARENTSEMOSSNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__SEMOSSNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PK"));
		qs.addSelector(new QueryColumnSelector("CONCEPTMETADATA__METAVALUE"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__ENGINE", "==", databaseId));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("CONCEPTMETADATA__METAKEY", "==", "logical"));
		qs.addRelation("CONCEPTMETADATA__PHYSICALNAMEID", "ENGINECONCEPT__PHYSICALNAMEID", "inner.join");

		try (IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(engine, qs)) {
			while (wrapper.hasNext()) {
				Object[] row = wrapper.next().getValues();
				String parentName = (String) row[0];
				String name = (String) row[1];
				boolean pk = (boolean) row[2];
				String logicalName = (String) row[3];

				String uniqueName = name;
				if (!pk) {
					uniqueName = parentName + "__" + name;
				}

				List<String> logicalNames = null;
				if (engineLogicalNames.containsKey(uniqueName)) {
					logicalNames = engineLogicalNames.get(uniqueName);
				} else {
					logicalNames = new ArrayList<>();
					// store in the map
					engineLogicalNames.put(uniqueName, logicalNames);
				}
				// add the new value
				logicalNames.add(logicalName);
			}
		} catch (Exception e) {
			classLogger.error("Error retrieving logical names for database {}.", databaseId, e);
		}

		return engineLogicalNames;
	}

	public static Map<String, String> getDatabaseDescriptions(String databaseId) {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		Map<String, String> engineDescriptions = new HashMap<>();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PARENTSEMOSSNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__SEMOSSNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PK"));
		qs.addSelector(new QueryColumnSelector("CONCEPTMETADATA__METAVALUE"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__ENGINE", "==", databaseId));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("CONCEPTMETADATA__METAKEY", "==", "description"));
		qs.addRelation("CONCEPTMETADATA__PHYSICALNAMEID", "ENGINECONCEPT__PHYSICALNAMEID", "inner.join");

		try (IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(engine, qs)) {
			while (wrapper.hasNext()) {
				Object[] row = wrapper.next().getValues();
				String parentName = (String) row[0];
				String name = (String) row[1];
				boolean pk = (boolean) row[2];
				String description = (String) row[3];

				String uniqueName = name;
				if (!pk) {
					uniqueName = parentName + "__" + name;
				}
				engineDescriptions.put(uniqueName, description);
			}
		} catch (Exception e) {
			classLogger.error("Error retrieving descriptions for database {}.", databaseId, e);
		}

		return engineDescriptions;
	}

	/**
	 * Get the properties for a given concept
	 * 
	 * @param conceptName
	 * @param databaseId  the database to get the properties for
	 * @return
	 */
	public static List<String> getSpecificConceptProperties(String parentName, String databaseId) {
		if (databaseId == null || databaseId.isEmpty()) {
			throw new IllegalArgumentException("Must define a valid engine id");
		}

		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__SEMOSSNAME"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__ENGINE", "==", databaseId));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__PARENTSEMOSSNAME", "==", parentName));
		qs.addOrderBy(new QueryColumnOrderBySelector("ENGINECONCEPT__SEMOSSNAME"));

		return QueryExecutionUtility.flushToListString(engine, qs);
	}

	/**
	 * Get the queryable pixel selectors for the table If RDBMS this will have all
	 * the properties If RDF/Graph this will have the concept and all its properties
	 * Return will be in TABLE__COLUMN format
	 * 
	 * @param parentName String with the name of the concept
	 * @param databaseId String with the engine to get the selectors for
	 * @return
	 */
	public static List<String> getConceptPixelSelectors(String parentName, String databaseId) {
		if (databaseId == null || databaseId.isEmpty()) {
			throw new IllegalArgumentException("Must define a valid engine id");
		}

		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PARENTSEMOSSNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__SEMOSSNAME"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__ENGINE", "==", databaseId));
		// grab either
		// 1) all rows where the parent is what is passed in
		// or
		// 2) the parent itself has data (i.e column name is the parent + pk = true +
		// ignoreData=false)
		OrQueryFilter orFilter = new OrQueryFilter();
		qs.addExplicitFilter(orFilter);
		orFilter.addFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__PARENTSEMOSSNAME", "==", parentName));
		// add an and filter to the or
		AndQueryFilter andFilter = new AndQueryFilter();
		orFilter.addFilter(andFilter);
		andFilter.addFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__SEMOSSNAME", "==", parentName));
		andFilter.addFilter(
				SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__PK", "==", true, PixelDataType.BOOLEAN));
		andFilter.addFilter(
				SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__IGNORE_DATA", "==", false, PixelDataType.BOOLEAN));
		qs.addOrderBy(new QueryColumnOrderBySelector("ENGINECONCEPT__SEMOSSNAME"));

		List<String> retArr = new ArrayList<>();

		try (IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(engine, qs)) {
			while (wrapper.hasNext()) {
				Object[] row = wrapper.next().getValues();
				if (row[0] != null) {
					retArr.add(row[0] + "__" + row[1]);
				} else {
					retArr.add(row[1].toString());
				}
			}
		} catch (Exception e) {
			classLogger.error("Error retrieving pixel selectors for parent {} in database {}.", parentName, databaseId,
					e);
		}

		return retArr;
	}

	/**
	 * Get the physical concept id for a concept given the engine id + conceptual
	 * name
	 * 
	 * @param databaseId
	 * @param conceptualName
	 * @return
	 */
	public static String getPhysicalConceptId(String databaseId, String conceptualName) {
		// SELECT engineconcept.physicalnameid FROM engineconcept INNER JOIN concept ON
		// concept.localconceptid=engineconcept.localconceptid WHERE
		// engineconcept.engine='' AND concept.conceptualname='Title'
		// String query = "SELECT engineconcept.physicalnameid "
		// + "FROM engineconcept "
		// + "INNER JOIN concept ON concept.localconceptid=engineconcept.localconceptid
		// "
		// + "WHERE engineconcept.engine='" + engineId + "' "
		// + "AND concept.conceptualname='" + conceptualName + "';";

		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PHYSICALNAMEID"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__ENGINE", "==", databaseId));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("CONCEPT__CONCEPTUALNAME", "==", conceptualName));
		qs.addRelation("CONCEPT__LOCALCONCEPTID", "ENGINECONCEPT__LOCALCONCEPTID", "inner.join");

		return QueryExecutionUtility.flushToString(engine, qs);
	}

	/**
	 * Get the physical concept id for a concept given the engine id + pixel name
	 * 
	 * @param databaseId
	 * @param conceptualName
	 * @return
	 */
	public static String getPhysicalConceptIdFromPixelName(String databaseId, String pixelName) {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PHYSICALNAMEID"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__ENGINE", "==", databaseId));
		if (pixelName.contains("__")) {
			String[] split = pixelName.split("__");
			qs.addExplicitFilter(
					SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__PARENTSEMOSSNAME", "==", split[0]));
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__SEMOSSNAME", "==", split[1]));
		} else {
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__SEMOSSNAME", "==", pixelName));
		}

		return QueryExecutionUtility.flushToString(engine, qs);
	}

	/**
	 * Get concept metadata value for a key
	 * 
	 * @param databaseId
	 * @param concept
	 * @param key
	 * @return
	 */
	public static String getMetadataValue(String databaseId, String concept, String key) {
		// String query = "select " + Constants.VALUE + " from " +
		// Constants.CONCEPT_METADATA_TABLE
		// + " where localconceptid in (select localconceptid from concept "
		// + "where localconceptid in (select localconceptid from engineconcept "
		// + "where engine='" + engineId + "') "
		// + "and conceptualname='" + concept + "') and " + Constants.KEY + "='" + key +
		// "';";

		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector(Constants.CONCEPT_METADATA_TABLE + "__" + Constants.VALUE));
		qs.addExplicitFilter(SimpleQueryFilter
				.makeColToValFilter(Constants.CONCEPT_METADATA_TABLE + "__" + Constants.KEY, "==", key));
		{
			SelectQueryStruct subQs = new SelectQueryStruct();
			// store first and fill in sub query after
			qs.addExplicitFilter(SimpleQueryFilter
					.makeColToSubQuery(Constants.CONCEPT_METADATA_TABLE + "__LOCALCONCEPTID", "==", subQs));

			// fill in the sub query with the necessary column output + filters
			subQs.addSelector(new QueryColumnSelector("CONCEPT__LOCALCONCEPTID"));
			subQs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("CONCEPT__CONCEPTUALNAME", "==", concept));
			// we have a sub query again
			SelectQueryStruct subQs2 = new SelectQueryStruct();
			subQs.addExplicitFilter(SimpleQueryFilter.makeColToSubQuery("CONCEPT__LOCALCONCEPTID", "==", subQs2));

			// fill in the second sub query with the necessary column output + filters
			subQs2.addSelector(new QueryColumnSelector("ENGINECONCEPT__LOCALCONCEPTID"));
			subQs2.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__ENGINE", "==", databaseId));
		}

		return QueryExecutionUtility.flushToString(engine, qs);
	}

	/**
	 * Get all engine alias to id combinations
	 * 
	 * @return
	 */
	public static List<String> getDatabaseIdsForAlias(String alias) {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINE__ID"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINENAME"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__ENGINENAME", "==", alias));

		return QueryExecutionUtility.flushToListString(engine, qs);
	}

	/**
	 * Get an engine type for an id
	 * 
	 * @return
	 */
	public static String getDatabaseTypeForId(String id) {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINE__TYPE"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINE__ID", "==", id));
		return QueryExecutionUtility.flushToString(engine, qs);
	}

	/**
	 * Try to reconcile and get the database id
	 * 
	 * @param databaseId
	 * @return
	 */
	public static String testDatabaseIdIfAlias(String databaseId) {
		List<String> databaseIds = MasterDatabaseUtility.getDatabaseIdsForAlias(databaseId);
		if (databaseIds.size() == 1) {
			// actually received a database name
			databaseId = databaseIds.get(0);
		} else if (databaseIds.size() > 1) {
			throw new IllegalArgumentException("There are 2 databases with the name " + databaseId
					+ ". Please pass in the correct id to know which source you want to load from");
		}

		// i guess the input was the actual id
		return databaseId;
	}

	/**
	 * Get a list of the conceptual names
	 * 
	 * @param databaseFilters optional filter based on engines
	 * @return
	 */
	public static Collection<String> getAllConceptualNames(Collection<String> databaseFilters) {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("CONCEPT__CONCEPTUALNAME"));
		QueryFunctionSelector fun = new QueryFunctionSelector();
		fun.addInnerSelector(new QueryColumnSelector("CONCEPT__CONCEPTUALNAME"));
		fun.setFunction(QueryFunctionHelper.LOWER);
		fun.setAlias("LNAME");
		qs.addSelector(fun);
		if (databaseFilters != null && !databaseFilters.isEmpty()) {
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__ENGINE", "==", databaseFilters));
			qs.addRelation("ENGINECONCEPT", "CONCEPT", "inner.join");
		}
		qs.addOrderBy(new QueryColumnOrderBySelector("LNAME"));

		return QueryExecutionUtility.flushToListString(engine, qs);
	}

	/**
	 * Get a list of the conceptual names that are primary keys for a db
	 * 
	 * @param databaseId
	 * @return
	 */
	public static Collection<String> getPKColumnsWithData(String databaseId) {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__SEMOSSNAME"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__ENGINE", "==", databaseId));
		qs.addExplicitFilter(
				SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__PK", "==", true, PixelDataType.BOOLEAN));
		qs.addExplicitFilter(
				SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__IGNORE_DATA", "==", false, PixelDataType.BOOLEAN));

		return QueryExecutionUtility.flushToListString(engine, qs);
	}

	/**
	 * Get the conceptual names for a collection of physical name ids
	 * 
	 * @param physicalNameIds
	 * @return
	 */
	public static List<String> getConceptualNamesFromPhysicalIds(List<String> physicalNameIds) {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("CONCEPT__CONCEPTUALNAME"));
		qs.addExplicitFilter(
				SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__PHYSICALNAMEID", "==", physicalNameIds));
		qs.addRelation("CONCEPT__LOCALCONCEPTID", "ENGINECONCEPT__LOCALCONCEPTID", "inner.join");

		return QueryExecutionUtility.flushToListString(engine, qs);
	}

	/**
	 * Get connections to other datasources based on similar conceptual names
	 * 
	 * @param conceptualNames
	 * @param dbFilters
	 * @return
	 */
	public static List<String[]> getConceptualConnections(List<String> conceptualNames, Collection<String> dbFilters) {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		List<String[]> results = new ArrayList<>();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__ENGINE"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__SEMOSSNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PARENTSEMOSSNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PK"));
		if (dbFilters != null && !dbFilters.isEmpty()) {
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__ENGINE", "==", dbFilters));
		}
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("CONCEPT__CONCEPTUALNAME", "==", conceptualNames));
		qs.addRelation("CONCEPT__LOCALCONCEPTID", "ENGINECONCEPT__LOCALCONCEPTID", "inner.join");

		try (IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(engine, qs)) {
			while (wrapper.hasNext()) {
				Object[] data = wrapper.next().getValues();

				String[] row = new String[2];
				row[0] = data[0].toString();
				if ((Boolean) data[3]) {
					row[1] = data[1].toString();
				} else {
					row[1] = data[2] + "__" + data[1];
				}

				results.add(row);
			}
		} catch (Exception e) {
			classLogger.error(
					"Error retrieving conceptual connections for conceptual names {} and database filters {}.",
					conceptualNames, dbFilters, e);
		}

		return results;
	}

	/**
	 * Get the CLP model
	 * 
	 * @param conceptualNames
	 * @param databaseFilters
	 * @return
	 */
	public static List<String[]> getConceptualToLogicalToPhysicalModel(List<String> conceptualNames,
			Collection<String> databaseFilters) {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();

		List<String[]> results = new ArrayList<>();

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("CONCEPT__CONCEPTUALNAME"));
		qs.addSelector(new QueryColumnSelector("CONCEPT__LOGICALNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__SEMOSSNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PARENTSEMOSSNAME"));
		qs.addSelector(new QueryColumnSelector("ENGINECONCEPT__PK"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINENAME"));
		if (databaseFilters != null && !databaseFilters.isEmpty()) {
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("ENGINECONCEPT__ENGINE", "==", databaseFilters));
		}
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("CONCEPT__CONCEPTUALNAME", "==", conceptualNames));
		qs.addRelation("CONCEPT__LOCALCONCEPTID", "ENGINECONCEPT__LOCALCONCEPTID", "inner.join");
		qs.addRelation("ENGINECONCEPT__ENGINE", "ENGINE__ID", "inner.join");

		try (IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(engine, qs)) {
			while (wrapper.hasNext()) {
				Object[] data = wrapper.next().getValues();

				String[] row = new String[4];
				row[0] = data[0].toString();
				row[1] = data[1].toString();
				if ((Boolean) data[4]) {
					row[2] = data[2].toString();
				} else {
					row[2] = data[3] + "__" + data[2];
				}
				row[3] = data[5].toString();

				results.add(row);
			}
		} catch (Exception e) {
			classLogger.error("Error retrieving CLP model for conceptual names {} and database filters {}.",
					conceptualNames, databaseFilters, e);
		}

		return results;
	}

	/**
	 * Maps each physical name of a source database to the physical names of a
	 * target database that share its logical name. The rows are read in one read on
	 * the shared executor.
	 *
	 * <p>
	 * A failure acquiring or preparing the local master connection throws. A
	 * failure while the query runs, or while the connection is released, is logged
	 * and the mappings read before it are returned.
	 *
	 * @param sourceDB id of the database whose physical names are the keys
	 * @param targetDB id of the database whose physical names are the values
	 * @return source physical names to the matching target physical names; empty
	 *         when nothing matches or the query fails before the first row
	 * @throws IllegalArgumentException when the local master connection cannot be
	 *                                  acquired
	 */
	public static Map<String, List<String>> databaseTranslator(String sourceDB, String targetDB) {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();
		// select logicalname from concept where conceptualname='MovieBudget'
		// and conceptualname != logicalname
		// select distinct c.conceptualname, ec.physicalname from concept c,
		// engineconcept ec, engine e where ec.localconceptid=c.localconceptid
		// and ec.physicalname in ('Title', 'Actor');
		Map<String, List<String>> map = new HashMap<>();
		String query = "SELECT e.engineName as sourceEngine, c.conceptualName as sourceConceptual, ec.physicalName as sourcePhysical, c.logicalName, "
				+ "targetEngine, targetConceptual, targetPhysical from engine e, engineconcept ec, concept c  "
				+ "INNER JOIN (SELECT e.engineName as targetEngine, c.conceptualName as targetConceptual, "
				+ "ec.physicalName as targetPhysical, c.logicalName as targetLogical "
				+ "from engine e, engineconcept ec, concept c WHERE e.id=ec.engine and ec.localConceptID = c.localConceptID and e.id = ?"
				+ " and c.conceptualName != c.logicalName) ON c.logicalName = targetLogical "
				+ "WHERE e.id=ec.engine and ec.localConceptID = c.localConceptID and e.id = ?"
				+ " and c.conceptualName != c.logicalName";
		Exception failure;
		try {
			failure = readLocalMasterStage(engine, conn -> {
				try (PreparedStatement stmt = conn.prepareStatement(query)) {
					stmt.setString(1, targetDB);
					stmt.setString(2, sourceDB);
					try (ResultSet rs = stmt.executeQuery()) {
						while (rs.next()) {
							String sourcePhysical = rs.getString(3);
							String targetPhysical = rs.getString(7);

							List<String> targetPhysicals = new ArrayList<>();
							if (map.containsKey(sourcePhysical)) {
								targetPhysicals = map.get(sourcePhysical);
							}
							targetPhysicals.add(targetPhysical);

							map.put(sourcePhysical, targetPhysicals);
						}
					}
				}
				return null;
			});
		} catch (RuntimeException e) {
			throw e;
		} catch (Exception e) {
			classLogger.error("Error connecting to local master engine {} for database translation from {} to {}.",
					engine.getEngineName(), sourceDB, targetDB, e);
			throw new IllegalArgumentException("Unable to connect to local master engine '" + engine.getEngineName()
					+ "' for database translation.");
		}
		if (failure != null) {
			classLogger.error("Error translating database schema mappings from {} to {}.", sourceDB, targetDB, failure);
		}
		return map;
	}

	/*
	 * X-RAY Stuff
	 */

	/**
	 * Lists the distinct X-ray config file names. The names are read in one read on
	 * the shared executor.
	 *
	 * <p>
	 * A failure acquiring or preparing the local master connection throws. A failed
	 * query leaves {@code configList} out of the result without logging, since the
	 * XRAYCONFIGS table does not exist until a config is saved.
	 *
	 * @return a map holding {@code configList}, a list of maps with a
	 *         {@code fileName} each, when the query completes; empty otherwise
	 * @throws IllegalArgumentException when the local master connection cannot be
	 *                                  acquired
	 */
	public static HashMap<String, Object> getXrayConfigList() {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();
		HashMap<String, Object> configMap = new HashMap<>();
		String query = "select distinct filename FROM xrayconfigs;";
		Exception failure;
		try {
			failure = readLocalMasterStage(engine, conn -> {
				try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(query)) {
					ArrayList<HashMap<String, Object>> configList = new ArrayList<>();
					while (rs.next()) {
						HashMap<String, Object> rsMap = new HashMap<>();
						String fileName = rs.getString(1);
						rsMap.put("fileName", fileName);
						configList.add(rsMap);
					}
					configMap.put("configList", configList);
				}
				return null;
			});
		} catch (RuntimeException e) {
			throw e;
		} catch (Exception e) {
			classLogger.error("Error connecting to local master engine {} while listing xray configs.",
					engine.getEngineName(), e);
			throw new IllegalArgumentException("Unable to connect to local master engine '" + engine.getEngineName()
					+ "' while listing xray configs.");
		}
		if (failure instanceof RuntimeException) {
			throw (RuntimeException) failure;
		}
		// Don't print stack trace... xrayConfigList table is missing if no config file
		// exists
		return configMap;
	}

	/**
	 * Reads one X-ray config file. The config is read in one read on the shared
	 * executor.
	 *
	 * <p>
	 * A failure acquiring or preparing the local master connection throws. A
	 * failure while the query runs, or while the connection is released, is logged
	 * and the config read before it, if any, is returned.
	 *
	 * @param filename name the config was saved under
	 * @return the config text of the last matching row; empty when there is no such
	 *         config or the query fails before reading it
	 * @throws IllegalArgumentException when the local master connection cannot be
	 *                                  acquired
	 */
	public static String getXrayConfigFile(String filename) {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();
		String[] configFile = { "" };
		String query = "select config from xrayconfigs where filename = ?";
		Exception failure;
		try {
			failure = readLocalMasterStage(engine, conn -> {
				try (PreparedStatement stmt = conn.prepareStatement(query)) {
					stmt.setString(1, filename);
					try (ResultSet rs = stmt.executeQuery()) {
						while (rs.next()) {
							configFile[0] = rs.getString(1);
						}
					}
				}
				return null;
			});
		} catch (RuntimeException e) {
			throw e;
		} catch (Exception e) {
			classLogger.error("Error connecting to local master engine {} while reading xray config {}.",
					engine.getEngineName(), filename, e);
			throw new IllegalArgumentException("Unable to connect to local master database while reading xray config '"
					+ filename + "'. See logs for details.");
		}
		if (failure != null) {
			classLogger.error("Error retrieving xray config file {}.", filename, failure);
		}
		return configFile[0];
	}

	/**
	 * Builds the X-ray metamodel of one database: its nodes, named
	 * {@code engine-concept}, and the edges between them. The nodes and the edges
	 * are read in two separate reads on the shared executor, so a failed node read
	 * still lets the edge read run.
	 *
	 * <p>
	 * A failure acquiring or preparing the local master connection for either read
	 * throws. A failed node read is logged and the edges are matched against the
	 * nodes read before it. A failed edge read is logged and the result is left
	 * empty, unless the edge read had already completed before the connection
	 * release failed.
	 *
	 * @param engineName name of the database whose metamodel is built
	 * @return {@code nodes} and {@code edges} when the edge read completes; empty
	 *         otherwise
	 * @throws IllegalArgumentException when the local master connection cannot be
	 *                                  acquired
	 */
	public static Map<String, Object> getXrayExisitingMetamodelRDBMS(String engineName) {
		// this needs to be moved to the name server
		// and this needs to be based on local master database
		// need this to be a simple OWL data
		// I dont know if it is worth it to load the engine at this point ?
		// or should I just load it ?
		// need to get local master and pump out the metamodel

		// need to get all the concepts first
		// get the edges next

		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();
		// creates e-c-p node names for fe to parse
		String delim = "-";
		Map<String, Object> finalHash = new Hashtable<>();

		// idHash - physical ID to the name of the node
		Hashtable<String, String> idHash = new Hashtable<>();
		Hashtable<String, MetamodelVertex> nodeHash = new Hashtable<>();

		String nodeQuery = "SELECT c.conceptualname, ec.physicalname, ec.localconceptid, ec.physicalnameid, ec.parentphysicalid, ec.property FROM "
				+ "engineconcept ec, concept c, engine e " + "WHERE ec.engine=e.id " + "AND e.enginename=? "
				+ "AND c.localconceptid=ec.localconceptid ORDER BY ec.property";
		Exception nodeFailure;
		try {
			nodeFailure = readLocalMasterStage(engine, conn -> {
				try (PreparedStatement statement = conn.prepareStatement(nodeQuery)) {
					statement.setString(1, engineName);
					try (ResultSet rs = statement.executeQuery()) {
						while (rs.next()) {
							String conceptualName = rs.getString(1);
							String physicalId = rs.getString(4);
							String parentPhysicalId = rs.getString(5);

							// sets the physical id to conceptual name
							idHash.put(physicalId, conceptualName);

							MetamodelVertex node = null;

							// gets the conceptual name
							String conceptName = idHash.get(physicalId);

							// because it is ordered by property, this would already be
							// there
							String parentName = idHash.get(parentPhysicalId);

							// if already there, should we still add it ?
							if (nodeHash.containsKey(engineName + delim + parentName)) {
								node = nodeHash.get(engineName + delim + parentName);
							} else {
								node = new MetamodelVertex(engineName + delim + parentName);
								nodeHash.put(engineName + delim + conceptualName, node);
							}

							// might be this or might not be
							node.addProperty(conceptName);
						}
					}
				}
				return null;
			});
		} catch (RuntimeException e) {
			throw e;
		} catch (Exception e) {
			throw xrayMetamodelConnectionFailure(engine, engineName, e);
		}
		if (nodeFailure instanceof RuntimeException) {
			throw (RuntimeException) nodeFailure;
		}
		if (nodeFailure != null) {
			classLogger.error("Error retrieving xray metamodel nodes for engine {}.", engineName, nodeFailure);
		}

		// get the edges next
		// SELECT er.sourceconceptid, er.targetconceptid FROM ENGINERELATION
		// er, engine e where e.id = er.engine and e.enginename = 'Mv1'
		String edgeQuery = "SELECT er.sourceconceptid, er.targetconceptid FROM ENGINERELATION er, engine e WHERE e.id=er.engine AND "
				+ "e.enginename = ?";
		Exception edgeFailure;
		try {
			edgeFailure = readLocalMasterStage(engine, conn -> {
				try (PreparedStatement statement = conn.prepareStatement(edgeQuery)) {
					statement.setString(1, engineName);
					try (ResultSet rs = statement.executeQuery()) {
						Hashtable<String, Hashtable> edgeHash = new Hashtable<>();
						while (rs.next()) {
							String startId = rs.getString(1);
							String endId = rs.getString(2);

							Hashtable newEdge = new Hashtable();
							// need to check to see if the idHash has it else put it in
							String sourceName = idHash.get(startId);
							String targetName = idHash.get(endId);
							newEdge.put("source", engineName + delim + sourceName + delim + sourceName);
							newEdge.put("target", engineName + delim + targetName + delim + targetName);

							boolean foundNode = true;
							if (!nodeHash.containsKey(engineName + delim + sourceName)) {
								foundNode = false;
								classLogger.debug("Unable to find node {}", sourceName);
							}
							if (!nodeHash.containsKey(engineName + delim + targetName)) {
								foundNode = false;
								classLogger.debug("Unable to find node {}", targetName);
							}

							if (foundNode) {
								edgeHash.put(engineName + delim + sourceName + delim + sourceName + delim + engineName
										+ delim + targetName + delim + targetName, newEdge);
							}
						}
						finalHash.put("nodes", nodeHash);
						finalHash.put("edges", edgeHash);
					}
				}
				return null;
			});
		} catch (RuntimeException e) {
			throw e;
		} catch (Exception e) {
			throw xrayMetamodelConnectionFailure(engine, engineName, e);
		}
		if (edgeFailure instanceof RuntimeException) {
			throw (RuntimeException) edgeFailure;
		}
		if (edgeFailure != null) {
			classLogger.error("Error retrieving xray metamodel edges for engine {}.", engineName, edgeFailure);
		}

		return finalHash;
	}

	/**
	 * Logs a failure to acquire the local master connection for an X-ray metamodel
	 * read and builds the exception the caller throws.
	 *
	 * @param engine     the local master database
	 * @param engineName name of the database whose metamodel is read
	 * @param cause      the acquisition failure
	 * @return the exception reporting that the connection is unavailable
	 */
	private static IllegalArgumentException xrayMetamodelConnectionFailure(IRDBMSEngine engine, String engineName,
			Exception cause) {
		classLogger.error("Error connecting to local master engine {} while loading xray metamodel for {}.",
				engine.getEngineName(), engineName, cause);
		return new IllegalArgumentException(
				"Unable to connect to local master database while loading xray metamodel for engine '" + engineName
						+ "'. See logs for details.");
	}

	/**
	 * Runs one query-only stage of a local master read on the shared executor and
	 * keeps a connection that cannot be acquired apart from a query that fails. The
	 * work fills its caller's collections as it reads, so the values read before a
	 * failure stay with the caller.
	 *
	 * @param engine the local master database
	 * @param work   the stage's query-only work
	 * @return null when the stage completes; otherwise the failure raised while the
	 *         work ran or while the executor ended the read and released the
	 *         connection
	 * @throws Exception the failure raised before the work started, while the
	 *                   connection was acquired or prepared
	 */
	private static Exception readLocalMasterStage(IRDBMSEngine engine, QueryExecutionUtility.JdbcWork<?> work)
			throws Exception {
		boolean[] started = { false };
		try {
			QueryExecutionUtility.read(engine, conn -> {
				started[0] = true;
				return work.run(conn);
			});
			return null;
		} catch (Exception e) {
			if (!started[0]) {
				throw e;
			}
			return e;
		}
	}

	/**
	 * Get the date for a given engine
	 *
	 * @param engineId
	 * @return
	 */
	public static Date getEngineDate(String engineId) {
		// Retain an already materialized date if a later cursor/cleanup step fails.
		Date[] retDate = new Date[1];
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();
		try {
			return QueryExecutionUtility.read(engine, conn -> {
				String query = "select modifieddate from engine e where e.id = ?";
				try (PreparedStatement stmt = conn.prepareStatement(query)) {
					stmt.setString(1, engineId);
					try (ResultSet rs = stmt.executeQuery()) {
						while (rs.next()) {
							retDate[0] = new Date(rs.getTimestamp(1).getTime());
						}
					}
				}
				return retDate[0];
			});
		} catch (Exception ex) {
			classLogger.error("Error retrieving engine modified date for engine id {}.", engineId, ex);
		}
		return retDate[0];
	}

	/**
	 * 
	 * @param databaseId
	 * @param positions
	 */
	public static void saveMetamodelPositions(String databaseId, Map<String, Object> positions) {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();
		try {
			QueryExecutionUtility.write(engine, conn -> {
				saveMetamodelPositions(databaseId, positions, conn);
				return null;
			});
		} catch (Exception e) {
			classLogger.error("Error saving metamodel positions for database {}.", databaseId, e);
		}
	}

	/**
	 * It is your responsibility to close the connection object if connection
	 * pooling and using this method
	 * 
	 * @param databaseId
	 * @param positions
	 * @param conn
	 * @throws Exception
	 */
	public static void saveMetamodelPositions(String databaseId, Map<String, Object> positions, Connection conn)
			throws Exception {
		String removeExisting = "DELETE FROM METAMODELPOSITION where ENGINEID = ?";
		String insertStatement = "INSERT INTO METAMODELPOSITION VALUES (?, ?, ?, ?)";

		try (PreparedStatement remove = conn.prepareStatement(removeExisting);
				PreparedStatement add = conn.prepareStatement(insertStatement)) {
			remove.setString(1, databaseId);
			remove.execute();

			for (String x : positions.keySet()) {
				int i = 1;
				add.setString(i++, databaseId);
				add.setString(i++, x);
				Map<String, Object> topLeft = (Map<String, Object>) positions.get(x);
				Float left = convertToFloat(topLeft.get("left"));
				Float top = convertToFloat(topLeft.get("top"));
				add.setFloat(i++, left);
				add.setFloat(i++, top);
				add.addBatch();
			}

			add.executeBatch();
		} catch (Exception e) {
			classLogger.error("Could not save metamodel positions for database {}.", databaseId, e);
			throw e;
		}
	}

	public static Map<String, Object> getMetamodelPositions(String databaseId) {
		IRDBMSEngine engine = SystemEngineRegistry.getLocalMasterDb();
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("METAMODELPOSITION__TABLENAME"));
		qs.addSelector(new QueryColumnSelector("METAMODELPOSITION__XPOS"));
		qs.addSelector(new QueryColumnSelector("METAMODELPOSITION__YPOS"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("METAMODELPOSITION__ENGINEID", "==", databaseId));

		List<Object[]> objs = QueryExecutionUtility.flushRsToListOfObjArray(engine, qs);

		Map<String, Object> map = new HashMap<>();
		for (Object[] x : objs) {
			String tn = (String) x[0];
			Map<String, Object> position = new HashMap<>();
			position.put("left", x[1]);
			position.put("top", x[2]);
			map.put(tn, position);
		}
		return map;
	}

	private static Float convertToFloat(Object object) {
		if (object instanceof Double) {
			Double db = (Double) object;
			return db.floatValue();
		} else if (object instanceof Integer) {
			return ((Integer) object).floatValue();
		} else {
			return Float.valueOf(object.toString());
		}
	}

}