prerna.auth.utils.SecurityProjectUtils Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of semoss Show documentation
SEMOSS
The newest version!
package prerna.auth.utils;

import java.io.File;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.UUID;
import java.util.stream.Collectors;

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

import com.google.gson.Gson;

import prerna.auth.AccessPermissionEnum;
import prerna.auth.AccessToken;
import prerna.auth.AuthProvider;
import prerna.auth.User;
import prerna.date.SemossDate;
import prerna.engine.api.IHeadersDataRow;
import prerna.engine.api.IRawSelectWrapper;
import prerna.engine.impl.InsightAdministrator;
import prerna.engine.impl.SmssUtilities;
import prerna.engine.impl.rdbms.RDBMSNativeEngine;
import prerna.project.api.IProject;
import prerna.project.impl.ProjectHelper;
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.joins.IRelation;
import prerna.query.querystruct.joins.SubqueryRelationship;
import prerna.query.querystruct.selectors.QueryColumnOrderBySelector;
import prerna.query.querystruct.selectors.QueryColumnSelector;
import prerna.query.querystruct.selectors.QueryFunctionHelper;
import prerna.query.querystruct.selectors.QueryFunctionSelector;
import prerna.query.querystruct.selectors.QueryIfSelector;
import prerna.rdf.engine.wrappers.WrapperManager;
import prerna.sablecc2.PixelUtility;
import prerna.sablecc2.lexer.LexerException;
import prerna.sablecc2.om.PixelDataType;
import prerna.sablecc2.parser.ParserException;
import prerna.util.ConnectionUtils;
import prerna.util.Constants;
import prerna.util.DIHelper;
import prerna.util.ProjectUtils;
import prerna.util.QueryExecutionUtility;
import prerna.util.Settings;
import prerna.util.Utility;
import prerna.util.sql.AbstractSqlQueryUtil;
import prerna.util.sql.RdbmsTypeEnum;

public class SecurityProjectUtils extends AbstractSecurityUtils {

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

	/**
	 * Add an entire project into the security db - Expectation is not to call this method but addProject(projectId, boolean global = true)
	 * @param projectId
	 * 
	 * PLEASE DEFINE GLOBAL {@link #addProject(String, boolean, User)}
	 * @throws Exception 
	 */
	@Deprecated
	public static void addProject(String projectId, User user) throws Exception {
		// default project is not global
		addProject(projectId, false, user);
	}
	
	/**
	 * Add an entire project into the security db
	 * @param appId
	 * @throws Exception 
	 */
	public static void addProject(String projectId, boolean global, User user) throws Exception {
		String smssFile = DIHelper.getInstance().getProjectProperty(projectId + "_" + Constants.STORE) + "";
		Properties prop = Utility.loadProperties(smssFile);

		String projectName = prop.getProperty(Constants.PROJECT_ALIAS);
		if(projectName == null) {
			projectName = projectId;
		}
		
		boolean hasPortal = Boolean.parseBoolean(prop.getProperty(Settings.PUBLIC_HOME_ENABLE));
		String portalName = prop.getProperty(Settings.PORTAL_NAME);
		
		boolean reloadInsights = false;
		if(prop.containsKey(Constants.RELOAD_INSIGHTS)) {
			String booleanStr = prop.get(Constants.RELOAD_INSIGHTS).toString();
			reloadInsights = Boolean.parseBoolean(booleanStr);
		}
		
		// TODO: we do not need cost for project
		String[] typeAndCost = new String[] {prop.getOrDefault(Constants.PROJECT_ENUM_TYPE,"")+"",""};
		boolean projectExists = containsProjectId(projectId);
 		if(projectExists && !reloadInsights) {
			classLogger.info("Security database already contains project with unique id = " + Utility.cleanLogString(SmssUtilities.getUniqueName(prop)));
			return;
		} else if(!projectExists) {
			addProject(projectId, projectName, typeAndCost[0], typeAndCost[1], hasPortal, portalName, global, user);
		} else if(projectExists) {
			// delete values if currently present
			deleteInsightsFromProjectForRecreation(projectId);
			// update project properties anyway ... in case global was shifted for example
			updateProject(projectId, projectName, typeAndCost[0], typeAndCost[1], hasPortal, portalName, global);
		}
		
		classLogger.info("Security database going to add project with alias = " + Utility.cleanLogString(projectName));
		
		// load just the insights database
		// first see if engine is already loaded
		boolean projectLoaded = false;
		RDBMSNativeEngine rne = null;
		if(Utility.projectLoaded(projectId)) {
			rne = Utility.getProject(projectId).getInsightDatabase();
		} else {
			rne  = ProjectHelper.loadInsightsEngine(prop, classLogger);
		}
		
		// i need to delete any current insights for the project
		// before i start to insert new insights
		String deleteQuery = "DELETE FROM INSIGHT WHERE PROJECTID='" + projectId + "'";
		try {
			securityDb.removeData(deleteQuery);
		} catch (SQLException e) {
			classLogger.error(Constants.STACKTRACE, e);
		}
		
		// if we are doing a reload
		// we will want to remove unnecessary insights
		// from the insight permissions
		boolean existingInsightPermissions = true;
		Set insightPermissionIds = null;
		if(reloadInsights) {
			// need to flush out the current insights w/ permissions
			// will keep the same permissions
			// and perform a delta
			classLogger.info("Reloading app. Retrieving existing insights with permissions");
			String insightsWPer = "SELECT INSIGHTID FROM USERINSIGHTPERMISSION WHERE PROJECTID='" + projectId + "'";
			insightPermissionIds = QueryExecutionUtility.flushToSetString(securityDb, insightsWPer, false);
			if(insightPermissionIds.isEmpty()) {
				existingInsightPermissions = true;
			}
		}
		
		AbstractSqlQueryUtil securityQueryUtil = securityDb.getQueryUtil();
		// make a prepared statement
		PreparedStatement ps = null;
		try {
			ps = securityDb.bulkInsertPreparedStatement(
					new String[]{
							// table name
							"INSIGHT", 
							// column names
							"PROJECTID","INSIGHTID","INSIGHTNAME","GLOBAL","EXECUTIONCOUNT","CREATEDON",
							"LASTMODIFIEDON","LAYOUT",
							"CACHEABLE","CACHEMINUTES","CACHECRON","CACHEDON","CACHEENCRYPT",
							"RECIPE", 
							"SCHEMANAME"});
		} catch (SQLException e) {
			classLogger.error(Constants.STACKTRACE, e);
		}
		// keep a batch size so we dont get heapspace
		final int batchSize = 5000;
		int count = 0;
		
		Timestamp timeStamp = Utility.getCurrentSqlTimestampUTC();

//		String query = "SELECT DISTINCT ID, QUESTION_NAME, QUESTION_LAYOUT, HIDDEN_INSIGHT, CACHEABLE FROM QUESTION_ID WHERE HIDDEN_INSIGHT=false";
//		IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(rne, query);

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector(InsightAdministrator.TABLE_NAME + "__" + InsightAdministrator.QUESTION_ID_COL));
		qs.addSelector(new QueryColumnSelector(InsightAdministrator.TABLE_NAME + "__" + InsightAdministrator.QUESTION_NAME_COL));
		qs.addSelector(new QueryColumnSelector(InsightAdministrator.TABLE_NAME + "__" + InsightAdministrator.QUESTION_LAYOUT_COL));
		qs.addSelector(new QueryColumnSelector(InsightAdministrator.TABLE_NAME + "__" + InsightAdministrator.HIDDEN_INSIGHT_COL));
		qs.addSelector(new QueryColumnSelector(InsightAdministrator.TABLE_NAME + "__" + InsightAdministrator.CACHEABLE_COL));
		qs.addSelector(new QueryColumnSelector(InsightAdministrator.TABLE_NAME + "__" + InsightAdministrator.CACHE_MINUTES_COL));
		qs.addSelector(new QueryColumnSelector(InsightAdministrator.TABLE_NAME + "__" + InsightAdministrator.CACHE_CRON_COL));
		qs.addSelector(new QueryColumnSelector(InsightAdministrator.TABLE_NAME + "__" + InsightAdministrator.CACHED_ON_COL));
		qs.addSelector(new QueryColumnSelector(InsightAdministrator.TABLE_NAME + "__" + InsightAdministrator.CACHE_ENCRYPT_COL));
		qs.addSelector(new QueryColumnSelector(InsightAdministrator.TABLE_NAME + "__" + InsightAdministrator.QUESTION_PKQL_COL));
		qs.addSelector(new QueryColumnSelector(InsightAdministrator.TABLE_NAME + "__" + InsightAdministrator.SCHEMA_NAME_COL));
//		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("QUESTION_ID__HIDDEN_INSIGHT", "==", false, PixelDataType.BOOLEAN));
		IRawSelectWrapper wrapper = null;
		try {
			wrapper = WrapperManager.getInstance().getRawWrapper(rne, qs);
			while(wrapper.hasNext()) {
				Object[] row = wrapper.next().getValues();
				try {
					// grab the insight rdbms values
					int index = 0;
					String insightId = row[index++].toString();
					String insightName = row[index++].toString();
					String insightLayout = row[index++].toString();
					Boolean isPrivate = (Boolean) row[index++];
					if(isPrivate == null) {
						isPrivate = false;
					}
					Boolean cacheable = (Boolean) row[index++];
					if(cacheable == null) {
						cacheable = true;
					}
					Integer cacheMinutes = (Integer) row[index++];
					if(cacheMinutes == null) {
						cacheMinutes = -1;
					}
					String cacheCron = (String) row[index++];
					SemossDate cachedOn = (SemossDate) row[index++];
					Boolean cacheEncrypt = (Boolean) row[index++];
					if(cacheEncrypt == null) {
						cacheEncrypt = false;
					}
					Object pixelObject = row[index++];
					String schemaName = (String) row[index++];

					// insert prepared statement into security db
					int parameterIndex = 1;
					ps.setString(parameterIndex++, projectId);
					ps.setString(parameterIndex++, insightId);
					ps.setString(parameterIndex++, insightName);
					ps.setBoolean(parameterIndex++, !isPrivate);
					ps.setLong(parameterIndex++, 0);
					ps.setTimestamp(parameterIndex++, timeStamp);
					ps.setTimestamp(parameterIndex++, timeStamp);
					ps.setString(parameterIndex++, insightLayout);
					ps.setBoolean(parameterIndex++, cacheable);
					ps.setInt(parameterIndex++, cacheMinutes);
					if(cacheCron == null) {
						ps.setNull(parameterIndex++, java.sql.Types.VARCHAR);
					} else {
						ps.setString(parameterIndex++, cacheCron);
					}
					if(cachedOn == null) {
						ps.setNull(parameterIndex++, java.sql.Types.TIMESTAMP);
					} else {
						ps.setTimestamp(parameterIndex++, Utility.getSqlTimestampUTC(cachedOn));
					}

					ps.setBoolean(parameterIndex++, cacheEncrypt);

					// **** WITH RECENT UPDATES - THE RAW WRAPPER SHOULD NOT BE GIVING US BACK A CLOB
					// need to determine if our input is a clob
					// and if the database allows a clob data type
					// use the utility method generated
//					RDBMSUtility.handleInsertionOfClobInput(securityDb, securityQueryUtil, ps, parameterIndex++, pixelObject, securityGson);
					securityQueryUtil.handleInsertionOfClob(ps.getConnection(), ps, pixelObject, parameterIndex++, securityGson);
					
					
					if(schemaName == null) {
						ps.setNull(parameterIndex++, java.sql.Types.VARCHAR);
					} else {
						ps.setString(parameterIndex++, schemaName);
					}
					
					// add to ps
					ps.addBatch();
					// batch commit based on size
					if (++count % batchSize == 0) {
						classLogger.info("Executing batch .... row num = " + count);
						ps.executeBatch();
					}
					
					if(reloadInsights && insightPermissionIds != null && existingInsightPermissions) {
						insightPermissionIds.remove(insightId);
					}
				} catch (SQLException e) {
					classLogger.error(Constants.STACKTRACE, e);
				}
			}
		} catch (Exception e) {
			classLogger.error(Constants.STACKTRACE, e);
		} finally {
			if(wrapper != null) {
				try {
					wrapper.close();
				} catch (IOException e) {
					classLogger.error(Constants.STACKTRACE, e);
				}
			}
		}
		
		// well, we are done looping through now
		classLogger.info("Executing final batch .... row num = " + count);
		try {
			ps.executeBatch();
		} catch (SQLException e) {
			classLogger.error(Constants.STACKTRACE, e);
		}
		// commit
		try {
			if(!ps.getConnection().getAutoCommit()) {
				ps.getConnection().commit();
			}
		} catch (SQLException e) {
			classLogger.error(Constants.STACKTRACE, e);
		} finally {
			ConnectionUtils.closeAllConnectionsIfPooling(securityDb, ps);
		}
		
		count = 0;
		// same for insight meta
		// i need to delete any current insights for the app
		// before i start to insert new insights
		deleteQuery = "DELETE FROM INSIGHTMETA WHERE PROJECTID='" + projectId + "'";
		try {
			securityDb.removeData(deleteQuery);
		} catch (SQLException e) {
			classLogger.error(Constants.STACKTRACE, e);
		}
		
		try {
			ps = securityDb.bulkInsertPreparedStatement(
					new String[]{"INSIGHTMETA","PROJECTID","INSIGHTID","METAKEY","METAVALUE","METAORDER"});
		} catch (SQLException e1) {
			classLogger.error(Constants.STACKTRACE, e1);
		}
		
		qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("INSIGHTMETA__INSIGHTID"));
		qs.addSelector(new QueryColumnSelector("INSIGHTMETA__METAKEY"));
		qs.addSelector(new QueryColumnSelector("INSIGHTMETA__METAVALUE"));
		qs.addSelector(new QueryColumnSelector("INSIGHTMETA__METAORDER"));
		try {
			wrapper = WrapperManager.getInstance().getRawWrapper(rne, qs);
			while(wrapper.hasNext()) {
				IHeadersDataRow data = wrapper.next();
				Object[] row = data.getValues();
				Object[] raw = data.getRawValues();
				try {
					int parameterIndex = 1;
					ps.setString(parameterIndex++, projectId);
					ps.setString(parameterIndex++, row[0].toString());
					ps.setString(parameterIndex++, row[1].toString());

					// need to determine if our input is a clob
					// and if the database allows a clob data type
					// use the utility method generated
					Object metaValue = raw[2];
//					RDBMSUtility.handleInsertionOfClobInput(securityDb, securityQueryUtil, ps, parameterIndex++, metaValue, securityGson);
					securityQueryUtil.handleInsertionOfClob(ps.getConnection(), ps, metaValue, parameterIndex++, securityGson);
					
					// add the order
					ps.setInt(parameterIndex++, ((Number) row[3]).intValue());
					
					// add to ps
					ps.addBatch();
					// batch commit based on size
					if (++count % batchSize == 0) {
						classLogger.info("Executing batch .... row num = " + count);
						ps.executeBatch();
					}
				} catch (SQLException e) {
					classLogger.error(Constants.STACKTRACE, e);
				}
			}
		} catch (Exception e) {
			classLogger.error(Constants.STACKTRACE, e);
		} finally {
			if(wrapper != null) {
				try {
					wrapper.close();
				} catch (IOException e) {
					classLogger.error(Constants.STACKTRACE, e);
				}
			}
		}
		
		// well, we are done looping through now
		classLogger.info("Executing final batch .... row num = " + count);
		try {
			ps.executeBatch();
		} catch (SQLException e) {
			classLogger.error(Constants.STACKTRACE, e);
		}
		try {
			if(!ps.getConnection().getAutoCommit()) {
				ps.getConnection().commit();
			}
		} catch (SQLException e) {
			classLogger.error(Constants.STACKTRACE, e);
		} finally {
			ConnectionUtils.closeAllConnectionsIfPooling(securityDb, ps);
		}
		
		// close the connection to the insights
		// if the engine is not already loaded 
		// since the open method will load it
		if(!projectLoaded && rne != null) {
			rne.close();
		}
		
		if(reloadInsights) {
			classLogger.info("Modifying force reload to false");
			try {
				Utility.changePropertiesFileValue(smssFile, Constants.RELOAD_INSIGHTS, "false");
			} catch (IOException e) {
				classLogger.error(Constants.STACKTRACE, e);
			}	
			
			// need to remove existing insights w/ permissions that do not exist anymore
			if(existingInsightPermissions && !insightPermissionIds.isEmpty()) {
				
				//TODO:
				//TODO:
				//TODO:
				//TODO:
				
				classLogger.info("Removing insights with permissions that no longer exist");
				String deleteInsightPermissionQuery = "DELETE FROM USERINSIGHTPERMISSION "
					+ "WHERE PROJECTID='" + projectId + "'"
					+ " AND INSIGHTID " + createFilter(insightPermissionIds);
				try {
					securityDb.removeData(deleteInsightPermissionQuery);
					securityDb.commit();
				} catch (SQLException e) {
					classLogger.error(Constants.STACKTRACE, e);
				}
			}
		}
		
		classLogger.info("Finished adding project = " + Utility.cleanLogString(projectId));
	}
	
	/**
	 * 
	 * @param projectId
	 * @param projectName
	 * @param projectType
	 * @param projectCost
	 * @param hasPortal
	 * @param portalName
	 * @param global
	 * @param user
	 */
	public static void addProject(String projectId, String projectName, 
			String projectType, String projectCost, 
			boolean hasPortal, String portalName,
			boolean global, User user) {
		String query = "INSERT INTO PROJECT (PROJECTID, PROJECTNAME, TYPE, COST, GLOBAL, DISCOVERABLE, CREATEDBY, CREATEDBYTYPE, DATECREATED, HASPORTAL, PORTALNAME) "
				+ "VALUES (?,?,?,?,?,?,?,?,?,?,?)";

		PreparedStatement ps = null;
		try {
			ps = securityDb.getPreparedStatement(query);
			int parameterIndex = 1;
			ps.setString(parameterIndex++, projectId);
			ps.setString(parameterIndex++, projectName);
			ps.setString(parameterIndex++, projectType);
			ps.setString(parameterIndex++, projectCost);
			ps.setBoolean(parameterIndex++, global);
			ps.setBoolean(parameterIndex++, false);
			if(user != null) {
				AuthProvider ap = user.getPrimaryLogin();
				AccessToken token = user.getAccessToken(ap);
				ps.setString(parameterIndex++, token.getId());
				ps.setString(parameterIndex++, ap.toString());
			} else {
				ps.setNull(parameterIndex++, java.sql.Types.VARCHAR);
				ps.setNull(parameterIndex++, java.sql.Types.VARCHAR);
			}
			ps.setTimestamp(parameterIndex++, Utility.getCurrentSqlTimestampUTC());
			ps.setBoolean(parameterIndex++, hasPortal);
			if(portalName != null) {
				ps.setString(parameterIndex++, portalName);
			} else {
				ps.setNull(parameterIndex++, java.sql.Types.VARCHAR);
			}
			ps.execute();
			if(!ps.getConnection().getAutoCommit()) {
				ps.getConnection().commit();
			}
		} catch (SQLException e) {
			classLogger.error(Constants.STACKTRACE, e);
		} finally {
			ConnectionUtils.closeAllConnectionsIfPooling(securityDb, ps);
		}
	}
	
	public static void addProjectOwner(User user, String projectId, String userId) {
		Pair userDetails = User.getPrimaryUserIdAndTypePair(user);

		String query = "INSERT INTO PROJECTPERMISSION (USERID, PERMISSION, PROJECTID, VISIBILITY, PERMISSIONGRANTEDBY, PERMISSIONGRANTEDBYTYPE, DATEADDED) VALUES (?,?,?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			ps = securityDb.getPreparedStatement(query);
			int parameterIndex = 1;
			ps.setString(parameterIndex++, userId);
			ps.setInt(parameterIndex++, AccessPermissionEnum.OWNER.getId());
			ps.setString(parameterIndex++, projectId);
			ps.setBoolean(parameterIndex++, true);
			ps.setString(parameterIndex++, userDetails.getValue0());
			ps.setString(parameterIndex++, userDetails.getValue1());
			ps.setTimestamp(parameterIndex++, Utility.getCurrentSqlTimestampUTC());
			ps.execute();
			if(!ps.getConnection().getAutoCommit()) {
				ps.getConnection().commit();
			}
		} catch (SQLException e) {
			classLogger.error(Constants.STACKTRACE, e);
		} finally {
			ConnectionUtils.closeAllConnectionsIfPooling(securityDb, ps);
		}
	}
	
	public static void updateProject(String projectID, String projectName, String projectType, String projectCost, boolean hasPortal, String portalName, boolean global) {
		String query = "UPDATE PROJECT SET PROJECTNAME=?, TYPE=?, COST=?, GLOBAL=?, HASPORTAL=?, PORTALNAME=? WHERE PROJECTID=?";

		PreparedStatement ps = null;
		try {
			ps = securityDb.getPreparedStatement(query);
			int parameterIndex = 1;
			ps.setString(parameterIndex++, projectName);
			ps.setString(parameterIndex++, projectType);
			ps.setString(parameterIndex++, projectCost);
			ps.setBoolean(parameterIndex++, global);
			ps.setBoolean(parameterIndex++, hasPortal);
			if(portalName != null) {
				ps.setString(parameterIndex++, portalName);
			} else {
				ps.setNull(parameterIndex++, java.sql.Types.VARCHAR);
			}
			ps.setString(parameterIndex++, projectID);
			ps.execute();
			if(!ps.getConnection().getAutoCommit()) {
				ps.getConnection().commit();
			}
		} catch (SQLException e) {
			classLogger.error(Constants.STACKTRACE, e);
		} finally {
			ConnectionUtils.closeAllConnectionsIfPooling(securityDb, ps);
		}
	}
	
	/**
	 * Delete just the insights for a project
	 * @param appId
	 */
	public static void deleteInsightsFromProjectForRecreation(String projectId) {
		String deleteQuery = "DELETE FROM INSIGHT WHERE PROJECTID=?";
		PreparedStatement ps = null;
		try {
			ps = securityDb.getPreparedStatement(deleteQuery);
			int parameterIndex = 1;
			ps.setString(parameterIndex++, projectId);
			ps.execute();
			if(!ps.getConnection().getAutoCommit()) {
				ps.getConnection().commit();
			}
		} catch(Exception e) {
			classLogger.error(Constants.STACKTRACE, e);
			throw new IllegalArgumentException("An error occurred deleting the insights for project " + projectId);
		} finally {
			ConnectionUtils.closeAllConnectionsIfPooling(securityDb, ps);
		}
	}
	
	/**
	 * 
	 * @param projectId
	 * @return
	 * @throws Exception 
	 */
	public static File createInsightsDatabase(String projectId, String folderPath) throws Exception {
		
		// TODO: potentially take into consideration playsheet legacy insights
		
		IProject project = Utility.getProject(projectId);
		RdbmsTypeEnum insightType = project.getInsightDatabase().getQueryUtil().getDbType();
		
		RDBMSNativeEngine newInsightDatabase = ProjectUtils.generateInsightsDatabase(projectId, insightType, folderPath);
		ProjectUtils.runInsightCreateTableQueries(newInsightDatabase);
		
		InsightAdministrator admin = new InsightAdministrator(newInsightDatabase);
		{
			boolean error = false;
			PreparedStatement insertPs = null;
			
			String iprefix = "INSIGHT__";
			SelectQueryStruct qs = new SelectQueryStruct();
			qs.addSelector(new QueryColumnSelector(iprefix+"INSIGHTID"));
			qs.addSelector(new QueryColumnSelector(iprefix+"INSIGHTNAME"));
			qs.addSelector(new QueryColumnSelector(iprefix+"LAYOUT"));
			qs.addSelector(new QueryColumnSelector(iprefix+"CREATEDON"));
			qs.addSelector(new QueryColumnSelector(iprefix+"LASTMODIFIEDON"));
			qs.addSelector(new QueryColumnSelector(iprefix+"GLOBAL"));
			qs.addSelector(new QueryColumnSelector(iprefix+"CACHEABLE"));
			qs.addSelector(new QueryColumnSelector(iprefix+"CACHEMINUTES"));
			qs.addSelector(new QueryColumnSelector(iprefix+"CACHECRON"));
			qs.addSelector(new QueryColumnSelector(iprefix+"CACHEDON"));
			qs.addSelector(new QueryColumnSelector(iprefix+"CACHEENCRYPT"));
			qs.addSelector(new QueryColumnSelector(iprefix+"RECIPE"));
			qs.addSelector(new QueryColumnSelector(iprefix+"SCHEMANAME"));
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter(iprefix+"PROJECTID", "==", projectId));
			IRawSelectWrapper wrapper = null;
			try {
				insertPs = admin.getAddInsightPreparedStatement();

				wrapper = WrapperManager.getInstance().getRawWrapper(securityDb, qs);
	
				while(wrapper.hasNext()) {
					Object[] row = wrapper.next().getValues();
					
					int index = 0;
					String insightId = (String) row[index++];
					String insightName = (String) row[index++];
					String insightLayout = (String) row[index++];
					SemossDate createdOn = (SemossDate) row[index++];
					SemossDate lastModifiedOn = (SemossDate) row[index++];
					Boolean global = (Boolean) row[index++];
					Boolean cacheable = (Boolean) row[index++];
					int cacheMinutes = ((Number) row[index++]).intValue();
					String cacheCron = (String) row[index++];
					SemossDate sdCachedOn = (SemossDate) row[index++];
					LocalDateTime cachedOn = null;
					if(sdCachedOn != null) {
						cachedOn = sdCachedOn.getLocalDateTime();
					}
					boolean cacheEncrypt = (Boolean) row[index++];
					String pixelRecipe = (String) row[index++];
					String schemaName = (String) row[index++];
					
					List pixelList = null;
	
					if(pixelRecipe != null && !pixelRecipe.isEmpty() && !pixelRecipe.equals("null")) {
						List pixel = securityGson.fromJson(pixelRecipe, List.class);
						int pixelSize = pixel.size();
						pixelList = new ArrayList<>(pixelSize);
						for(int i = 0; i < pixelSize; i++) {
							String pixelString = pixel.get(i).toString();
							List breakdown;
							try {
								breakdown = PixelUtility.parsePixel(pixelString);
								pixelList.addAll(breakdown);
							} catch (ParserException | LexerException | IOException e) {
								classLogger.error(Constants.STACKTRACE, e);
								throw new IllegalArgumentException("Error occurred parsing the pixel expression");
							}
						}
					} else {
						classLogger.warn("Cannot write insight id '"+insightId+"' with no pixel recipe");
						continue;
					}
	
					admin.batchInsight(insertPs, insightId, insightName, insightLayout, pixelList, global, cacheable, cacheMinutes, cacheCron, cachedOn, cacheEncrypt, schemaName);
				}
				
				insertPs.executeBatch();
				if(!insertPs.getConnection().getAutoCommit()) {
					insertPs.getConnection().commit();
				}
			} catch(Exception e) {
				error = true;
				classLogger.error(Constants.STACKTRACE, e);
				throw new IllegalArgumentException("Error occured creating the insights database");
			} finally {
				if(wrapper != null) {
					try {
						wrapper.close();
					} catch (IOException e) {
						classLogger.error(Constants.STACKTRACE, e);
					}
				}
				ConnectionUtils.closeAllConnectionsIfPooling(securityDb, insertPs);
				if(error) {
					try {
						newInsightDatabase.close();
					} catch (Exception e) {
						classLogger.error(Constants.STACKTRACE, e);
					}
					String databaseFileLocation = newInsightDatabase.getSmssProp().getProperty(AbstractSqlQueryUtil.HOSTNAME);
					File databaseFile = new File(databaseFileLocation);
					if(databaseFile.exists() && databaseFile.isFile()) {
						databaseFile.delete();
					}
				}
			}
		}
		{
			PreparedStatement insertPs = null;

			String iprefix = "INSIGHTMETA__";
			SelectQueryStruct qs = new SelectQueryStruct();
			qs.addSelector(new QueryColumnSelector(iprefix+"INSIGHTID"));
			qs.addSelector(new QueryColumnSelector(iprefix+"METAKEY"));
			qs.addSelector(new QueryColumnSelector(iprefix+"METAVALUE"));
			qs.addSelector(new QueryColumnSelector(iprefix+"METAORDER"));
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter(iprefix+"PROJECTID", "==", projectId));

			IRawSelectWrapper wrapper = null;
			try {
				insertPs = admin.getAddInsightMetaPreparedStatement();
				
				wrapper = WrapperManager.getInstance().getRawWrapper(securityDb, qs);
	
				while(wrapper.hasNext()) {
					Object[] row = wrapper.next().getValues();
					
					int index = 0;
					String insightId = (String) row[index++];
					String metaKey = (String) row[index++];
					String metaValue = (String) row[index++];
					int metaOrder = ((Number) row[index++]).intValue();

					admin.batchInsightMetadata(insertPs, insightId, metaKey, metaValue, metaOrder);
				}
				
				insertPs.executeBatch();
				if(!insertPs.getConnection().getAutoCommit()) {
					insertPs.getConnection().commit();
				}
			} catch(Exception e) {
				// insight metadata is not as important, log the error
				classLogger.error(Constants.STACKTRACE, e);
			} finally {
				if(wrapper != null) {
					try {
						wrapper.close();
					} catch (IOException e) {
						classLogger.error(Constants.STACKTRACE, e);
					}
				}
				ConnectionUtils.closeAllConnectionsIfPooling(securityDb, insertPs);
			}
		}
		
		// close the db so we can move it
		newInsightDatabase.close();

		String databaseFileLocation = newInsightDatabase.getSmssProp().getProperty(AbstractSqlQueryUtil.HOSTNAME);
		File databaseFile = new File(databaseFileLocation);
		return databaseFile;
	}
	
	/**
	 * Try to reconcile and get the engine id
	 * @return
	 */
	public static String testUserProjectIdForAlias(User user, String potentialId) {
		List ids = new ArrayList();
		
//		String userFilters = getUserFilters(user);
//		String query = "SELECT DISTINCT PROJECTPERMISSION.PROJECTID "
//				+ "FROM PROJECTPERMISSION INNER JOIN PROJECT ON PROJECT.PROJECTID=PROJECTPERMISSION.PROJECTID "
//				+ "WHERE PROJECT.PROJECTNAME='" + potentialId + "' AND PROJECTPERMISSION.USERID IN " + userFilters;
//		
//		IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(securityDb, query);
		
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("PROJECTPERMISSION__PROJECTID"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("PROJECT__PROJECTNAME", "==", potentialId));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("PROJECTPERMISSION__USERID", "==", getUserFiltersQs(user)));
		qs.addRelation("PROJECT", "PROJECTPERMISSION", "inner.join");

		ids = QueryExecutionUtility.flushToListString(securityDb, qs);
		if(ids.isEmpty()) {
//			query = "SELECT DISTINCT PROJECT.PROJECTID FROM PROJECT WHERE PROJECT.PROJECTNAME='" + potentialId + "' AND PROJECT.GLOBAL=TRUE";

			qs = new SelectQueryStruct();
			qs.addSelector(new QueryColumnSelector("PROJECT__PROJECTID"));
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("PROJECT__PROJECTNAME", "==", potentialId));
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("PROJECT__GLOBAL", "==", true, PixelDataType.BOOLEAN));
			
			ids = QueryExecutionUtility.flushToListString(securityDb, qs);
		}
		
		if(ids.size() == 1) {
			potentialId = ids.get(0);
		} else if(ids.size() > 1) {
			throw new IllegalArgumentException("There are 2 projects with the name " + potentialId + ". Please pass in the correct id to know which source you want to load from");
		}
		
		return potentialId;
	}
	
	/**
	 * Get the engine alias for a id
	 * @return
	 */
	public static String getProjectAliasForId(String id) {
//		String query = "SELECT PROJECTNAME FROM PROJECT WHERE PROJECTID='" + id + "'";
//		IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(securityDb, query);
		
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("PROJECT__PROJECTNAME"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("PROJECT__PROJECTID", "==", id));
		List results = QueryExecutionUtility.flushToListString(securityDb, qs);
		if(results.isEmpty()) {
			return null;
		}
		return results.get(0);
	}
	
	/**
	 * Get user databases + global databases 
	 * @param userId
	 * @return
	 */
	public static List getFullUserProjectIds(User user) {
		List databaseList = SecurityUserProjectUtils.getFullUserProjectIds(user);
		databaseList.addAll(getGlobalProjectIds());
		return databaseList.stream().distinct().sorted().collect(Collectors.toList());
	}
	
	/**
	 * Get global databases
	 * @return
	 */
	public static Set getGlobalProjectIds() {
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("PROJECT__PROJECTID"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("PROJECT__GLOBAL", "==", true, PixelDataType.BOOLEAN));
		return QueryExecutionUtility.flushToSetString(securityDb, qs, false);
	}

	/**
	 * Get what permission the user has for a given app
	 * @param userId
	 * @param projectId
	 * @param insightId
	 * @return
	 */
	public static String getActualUserProjectPermission(User user, String projectId) {
		return SecurityUserProjectUtils.getActualUserProjectPermission(user, projectId);
	}
	
	/**
	 * Get a list of the project ids
	 * @return
	 */
	public static List getAllProjectIds() {
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("PROJECT__PROJECTID"));
		return QueryExecutionUtility.flushToListString(securityDb, qs);
	}
	
	/**
	 * 
	 * @param projectId
	 * @return
	 */
	public static Map getProjectPortalDetailsMap(String projectId) {
		Map portalDetails = new HashMap<>();

		IProject project = Utility.getProject(projectId);
		boolean hasPortal = project.isHasPortal();
		portalDetails.put("project_has_portal", hasPortal);
		portalDetails.put("project_is_published", project.isPublished());
		// TODO: old - will remove once confirmed from FE
		portalDetails.put("isPublished", project.isPublished());
		if(hasPortal) {
			String url = Utility.getApplicationUrl() + "/" + Utility.getPublicHomeFolder() + "/" + projectId + "/" + Constants.PORTALS_FOLDER + "/";
			portalDetails.put("project_portal_url", url);
			// TODO: old - will remove once confirmed from FE
			portalDetails.put("url", url);
		}
		return portalDetails;
	}

	/**
	 * Get markdown for a given project
	 * @param user
	 * @param projectId
	 * @return
	 */
	public static String getProjectMarkdown(User user, String projectId) {
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("PROJECTMETA__METAVALUE"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("PROJECTMETA__METAKEY", "==", Constants.MARKDOWN));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("PROJECTMETA__PROJECTID", "==", projectId));
		{
			SelectQueryStruct qs1 = new SelectQueryStruct();
			qs1.addSelector(new QueryColumnSelector("PROJECT__PROJECTID"));
			{
				OrQueryFilter orFilter = new OrQueryFilter();
				orFilter.addFilter(SimpleQueryFilter.makeColToValFilter("PROJECT__DISCOVERABLE", "==", Arrays.asList(true, null), PixelDataType.BOOLEAN));
				orFilter.addFilter(SimpleQueryFilter.makeColToValFilter("PROJECTPERMISSION__USERID", "==", getUserFiltersQs(user)));
				qs1.addExplicitFilter(orFilter);
			}
			qs1.addRelation("PROJECT", "PROJECTPERMISSION", "join");
			IRelation subQuery = new SubqueryRelationship(qs1, "PROJECT", "join", new String[] {"PROJECT__PROJECTID", "PROJECTMETA__PROJECTID", "="});
			qs.addRelation(subQuery);
		}
		return QueryExecutionUtility.flushToString(securityDb, qs);
	}
	
	/**
	 * Get the project permissions for a specific user
	 * @param singleUserId
	 * @param projectId
	 * @return
	 */
	public static Integer getUserProjectPermission(String singleUserId, String projectId) {
		return SecurityUserProjectUtils.getUserProjectPermission(singleUserId, projectId);
	}
	
	/**
	 * Get the project permissions for a specific user
	 * @param singleUserId
	 * @param projectId
	 * @return
	 */
	public static Map getUserProjectPermissions(List userIds, String projectId) {
		Map retMap = new HashMap();
		IRawSelectWrapper wrapper = null;
		try {
			wrapper = getUserProjectPermissionsWrapper(userIds, projectId);
			while(wrapper.hasNext()) {
				Object[] data = wrapper.next().getValues();
				String userId = (String) data[0];
				Integer permission = (Integer) data[1];
				retMap.put(userId, permission);
			}
		} catch (Exception e) {
			classLogger.error(Constants.STACKTRACE, e);
		} finally {
			if(wrapper != null) {
				try {
					wrapper.close();
				} catch (IOException e) {
					classLogger.error(Constants.STACKTRACE, e);
				}
			}
		}
		return retMap;
	}
	
	/**
	 * Get the project permissions for a specific user
	 * @param singleUserId
	 * @param projectId
	 * @return
	 */
	public static IRawSelectWrapper getUserProjectPermissionsWrapper(List userIds, String projectId) throws Exception {
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("PROJECTPERMISSION__USERID"));
		qs.addSelector(new QueryColumnSelector("PROJECTPERMISSION__PERMISSION"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("PROJECTPERMISSION__PROJECTID", "==", projectId));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("PROJECTPERMISSION__USERID", "==", userIds));
		IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(securityDb, qs);
		return wrapper;
	}
	
	/**
	 * See if project exists
	 * @return
	 */
	public static boolean projectExists(String projectId) {
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("PROJECT__PROJECTID"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("PROJECT__PROJECTID", "==", projectId));
		IRawSelectWrapper wrapper = null;
		try {
			wrapper = WrapperManager.getInstance().getRawWrapper(securityDb, qs);
			if(wrapper.hasNext()) {
				return true;
			}
		} catch (Exception e) {
			classLogger.error(Constants.STACKTRACE, e);
		} finally {
			if(wrapper != null) {
				try {
					wrapper.close();
				} catch (IOException e) {
					classLogger.error(Constants.STACKTRACE, e);
				}
			}
		}
		return false;
	}

	/**
	 * See if specific project is global
	 * @return
	 */
	public static boolean projectIsGlobal(String projectId) {
		//		String query = "SELECT ENGINEID FROM ENGINE WHERE GLOBAL=TRUE and ENGINEID='" + engineId + "'";
		//		IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(securityDb, query);

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("PROJECT__PROJECTID"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("PROJECT__GLOBAL", "==", true, PixelDataType.BOOLEAN));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("PROJECT__PROJECTID", "==", projectId));
		IRawSelectWrapper wrapper = null;
		try {
			wrapper = WrapperManager.getInstance().getRawWrapper(securityDb, qs);
			if(wrapper.hasNext()) {
				return true;
			}
		} catch (Exception e) {
			classLogger.error(Constants.STACKTRACE, e);
		} finally {
			if(wrapper != null) {
				try {
					wrapper.close();
				} catch (IOException e) {
					classLogger.error(Constants.STACKTRACE, e);
				}
			}
		}
		return false;
	}
	
	/**
	 * See if specific project is global
	 * @return
	 */
	public static boolean projectHasPortal(String projectId) {
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("PROJECT__PROJECTID"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("PROJECT__HASPORTAL", "==", true, PixelDataType.BOOLEAN));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("PROJECT__PROJECTID", "==", projectId));
		IRawSelectWrapper wrapper = null;
		try {
			wrapper = WrapperManager.getInstance().getRawWrapper(securityDb, qs);
			if(wrapper.hasNext()) {
				return true;
			}
		} catch (Exception e) {
			classLogger.error(Constants.STACKTRACE, e);
		} finally {
			if(wrapper != null) {
				try {
					wrapper.close();
				} catch (IOException e) {
					classLogger.error(Constants.STACKTRACE, e);
				}
			}
		}
		return false;
	}

	/**
	 * 
	 * @param projectId
	 * @return
	 */
	public static SemossDate getPortalPublishedTimestamp(String projectId) {
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("PROJECT__PORTALPUBLISHED"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("PROJECT__PROJECTID", "==", projectId));
		IRawSelectWrapper wrapper = null;
		try {
			wrapper = WrapperManager.getInstance().getRawWrapper(securityDb, qs);
			if(wrapper.hasNext()) {
				return (SemossDate) wrapper.next().getValues()[0];
			}
		} catch (Exception e) {
			classLogger.error(Constants.STACKTRACE, e);
		} finally {
			if(wrapper != null) {
				try {
					wrapper.close();
				} catch (IOException e) {
					classLogger.error(Constants.STACKTRACE, e);
				}
			}
		}
		return null;
	}
	
	public static void setPortalPublish(User user, String projectId) {
		AccessToken token = user.getAccessToken(user.getPrimaryLogin());
		String updateQ = "UPDATE PROJECT SET PORTALPUBLISHED=?, PORTALPUBLISHEDUSER=?, PORTALPUBLISHEDTYPE=? WHERE PROJECTID=?";
		PreparedStatement ps = null;
		try {
			ps = securityDb.getPreparedStatement(updateQ);
			int i = 1;
			ps.setTimestamp(i++, Utility.getCurrentSqlTimestampUTC());
			ps.setString(i++, token.getId());
			ps.setString(i++, token.getProvider().toString());
			ps.setString(i++, projectId);
			ps.execute();
			if(!ps.getConnection().getAutoCommit()) {
				ps.getConnection().commit();
			}
		} catch(Exception e) {
			classLogger.error(Constants.STACKTRACE, e);
		} finally {
			ConnectionUtils.closeAllConnectionsIfPooling(securityDb, ps);
		}
	}
	
	/**
	 * 
	 * @param projectId
	 * @return
	 */
	public static SemossDate getReactorCompilationTimestamp(String projectId) {
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("PROJECT__REACTORSCOMPILED"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("PROJECT__PROJECTID", "==", projectId));
		IRawSelectWrapper wrapper = null;
		try {
			wrapper = WrapperManager.getInstance().getRawWrapper(securityDb, qs);
			if(wrapper.hasNext()) {
				return (SemossDate) wrapper.next().getValues()[0];
			}
		} catch (Exception e) {
			classLogger.error(Constants.STACKTRACE, e);
		} finally {
			if(wrapper != null) {
				try {
					wrapper.close();
				} catch (IOException e) {
					classLogger.error(Constants.STACKTRACE, e);
				}
			}
		}
		return null;
	}
	
	public static void setReactorCompilation(User user, String projectId) {
		AccessToken token = user.getAccessToken(user.getPrimaryLogin());
		String updateQ = "UPDATE PROJECT SET REACTORSCOMPILED=?, REACTORSCOMPILEDUSER=?, REACTORSCOMPILEDTYPE=? WHERE PROJECTID=?";
		PreparedStatement ps = null;
		try {
			ps = securityDb.getPreparedStatement(updateQ);
			int i = 1;
			ps.setTimestamp(i++, Utility.getCurrentSqlTimestampUTC());
			ps.setString(i++, token.getId());
			ps.setString(i++, token.getProvider().toString());
			ps.setString(i++, projectId);
			ps.execute();
			if(!ps.getConnection().getAutoCommit()) {
				ps.getConnection().commit();
			}
		} catch(Exception e) {
			classLogger.error(Constants.STACKTRACE, e);
		} finally {
			ConnectionUtils.closeAllConnectionsIfPooling(securityDb, ps);
		}
	}

	
	/**
	 * Determine if the user is the owner of a project
	 * @param userFilters
	 * @param engineId
	 * @return
	 */
	public static boolean userIsOwner(User user, String projectId) {
		return SecurityUserProjectUtils.userIsOwner(getUserFiltersQs(user), projectId)
				|| SecurityGroupProjectUtils.userGroupIsOwner(user, projectId);
	}

	/**
	 * Determine if a user can view a project
	 * @param user
	 * @param projectId
	 * @return
	 */
	public static boolean userCanViewProject(User user, String projectId) {
		return SecurityUserProjectUtils.userCanViewProject(user, projectId)
				|| SecurityGroupProjectUtils.userGroupCanViewProject(user, projectId);
	}

	/**
	 * Determine if the user can modify the database
	 * @param projectId
	 * @param userId
	 * @return
	 */
	public static boolean userCanEditProject(User user, String projectId) {
		return SecurityUserProjectUtils.userCanEditProject(user, projectId)
				|| SecurityGroupProjectUtils.userGroupCanEditProject(user, projectId);
	}
	
	/**
	 * Get the request pending database permission for a specific user
	 * @param singleUserId
	 * @param projectId
	 * @return
	 */
	public static Integer getUserAccessRequestProjectPermission(String userId, String projectId) {
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("PROJECTACCESSREQUEST__PERMISSION"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("PROJECTACCESSREQUEST__REQUEST_USERID", "==", userId));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("PROJECTACCESSREQUEST__PROJECTID", "==", projectId));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("PROJECTACCESSREQUEST__APPROVER_DECISION", "==", "NEW_REQUEST"));
		return QueryExecutionUtility.flushToInteger(securityDb, qs);
	}

	/**
	 * Get Project max permission for a user
	 * @param userId
	 * @param projectId
	 * @return
	 */
	static int getMaxUserProjectPermission(User user, String projectId) {
		//		String userFilters = getUserFilters(user);
		//		// query the database
		//		String query = "SELECT DISTINCT ENGINEPERMISSION.PERMISSION FROM ENGINEPERMISSION "
		//				+ "WHERE ENGINEID='" + engineId + "' AND USERID IN " + userFilters + " ORDER BY PERMISSION";
		//		IRawSelectWrapper wrapper = WrapperManager.getInstance().getRawWrapper(securityDb, query);

		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("PROJECTPERMISSION__PERMISSION"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("PROJECTPERMISSION__PROJECTID", "==", projectId));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("PROJECTPERMISSION__USERID", "==", getUserFiltersQs(user)));
		qs.addOrderBy(new QueryColumnOrderBySelector("PROJECTPERMISSION__PERMISSION"));
		IRawSelectWrapper wrapper = null;
		try {
			wrapper = WrapperManager.getInstance().getRawWrapper(securityDb, qs);
			while(wrapper.hasNext()) {
				Object val = wrapper.next().getValues()[0];
				if(val == null) {
					return AccessPermissionEnum.READ_ONLY.getId();
				}
				int permission = ((Number) val).intValue();
				return permission;
			}
		} catch (Exception e) {
			classLogger.error(Constants.STACKTRACE, e);
		} finally {
			if(wrapper != null) {
				try {
					wrapper.close();
				} catch (IOException e) {
					classLogger.error(Constants.STACKTRACE, e);
				}
			}
		}		
		return AccessPermissionEnum.READ_ONLY.getId();
	}

	/**
	 * Retrieve the list of users for a given project with parameters
	 * @param user
	 * @param projectId
	 * @param searchParam
	 * @param permission
	 * @param limit
	 * @param offset
	 * @return
	 * @throws IllegalAccessException
	 */
	public static List> getProjectUsers(User user, String projectId, String searchParam, String permission, long limit, long offset) throws IllegalAccessException {
		if(!userCanViewProject(user, projectId)) {
			throw new IllegalAccessException("The user does not have access to view this project");
		}
		return SecurityUserProjectUtils.getProjectUsers(projectId, searchParam, permission, limit, offset);
	}
	
	public static long getProjectUsersCount(User user, String projectId, String searchParam, String permission) throws IllegalAccessException {
		if(!userCanViewProject(user, projectId)) {
			throw new IllegalAccessException("The user does not have access to view this project");
		}
		boolean hasSearchParam = searchParam != null && !(searchParam=searchParam.trim()).isEmpty();
		boolean hasPermission = permission != null && !(permission=permission.trim()).isEmpty();
		SelectQueryStruct qs = new SelectQueryStruct();
		QueryFunctionSelector fSelector = new QueryFunctionSelector();
        fSelector.setAlias("count");
        fSelector.setFunction(QueryFunctionHelper.COUNT);
        fSelector.addInnerSelector(new QueryColumnSelector("SMSS_USER__ID"));
        qs.addSelector(fSelector);
        qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("PROJECTPERMISSION__PROJECTID", "==", projectId));
		if (hasSearchParam) {
			OrQueryFilter or = new OrQueryFilter();
			or.addFilter(SimpleQueryFilter.makeColToValFilter("PROJECTPERMISSION__USERID", "?like", searchParam));
			or.addFilter(SimpleQueryFilter.makeColToValFilter("SMSS_USER__NAME", "?like", searchParam));
			or.addFilter(SimpleQueryFilter.makeColToValFilter("SMSS_USER__USERNAME", "?like", searchParam));
			or.addFilter(SimpleQueryFilter.makeColToValFilter("SMSS_USER__EMAIL", "?like", searchParam));
			qs.addExplicitFilter(or);
		}
		if (hasPermission) {
			qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("PROJECTPERMISSION__PERMISSION", "==", AccessPermissionEnum.getIdByPermission(permission)));
		}
		qs.addRelation("SMSS_USER", "PROJECTPERMISSION", "inner.join");
		qs.addRelation("PROJECTPERMISSION", "PERMISSION", "inner.join");
		return QueryExecutionUtility.flushToLong(securityDb, qs);
	}
	
	/**
	 * 
	 * @param user
	 * @param newUserId
	 * @param projectId
	 * @param permission
	 * @return
	 * @throws IllegalAccessException 
	 */
	public static void addProjectUser(User user, String newUserId, String projectId, String permission, String endDate) throws IllegalAccessException {
		Pair userDetails = User.getPrimaryUserIdAndTypePair(user);

		// make sure user can edit the app
		int userPermissionLvl = getMaxUserProjectPermission(user, projectId);
		if(!AccessPermissionEnum.isEditor(userPermissionLvl)) {
			throw new IllegalAccessException("Insufficient privileges to modify this project's permissions.");
		}
				
		// make sure user doesn't already exist for this insight
		if(getUserProjectPermission(newUserId, projectId) != null) {
			// that means there is already a value
			throw new IllegalArgumentException("This user already has access to this project. Please edit the existing permission level.");
		}
		
		// if i am not an owner
		// then i need to check if i can edit this users permission
		if(!AccessPermissionEnum.isOwner(userPermissionLvl)) {
			int newPermissionLvl = AccessPermissionEnum.getIdByPermission(permission);

			// cannot give some owner permission if i am just an editor
			if(AccessPermissionEnum.OWNER.getId() == newPermissionLvl) {
				throw new IllegalAccessException("Cannot give owner level access to this project since you are not currently an owner.");
			}
		}
		
		Timestamp startDate = Utility.getCurrentSqlTimestampUTC();
		Timestamp verifiedEndDate = null;
		if (endDate != null) {
			verifiedEndDate = AbstractSecurityUtils.calculateEndDate(endDate);
		}
		
		PreparedStatement ps = null;
		try {
			ps = securityDb.getPreparedStatement("INSERT INTO PROJECTPERMISSION (USERID, PROJECTID, VISIBILITY, PERMISSION, PERMISSIONGRANTEDBY, PERMISSIONGRANTEDBYTYPE, DATEADDED, ENDDATE) VALUES(?,?,?,?,?,?,?,?)");
			int parameterIndex = 1;
			ps.setString(parameterIndex++, newUserId);
			ps.setString(parameterIndex++, projectId);
			ps.setBoolean(parameterIndex++, true);
			ps.setInt(parameterIndex++, AccessPermissionEnum.getIdByPermission(permission));
			ps.setString(parameterIndex++, userDetails.getValue0());
			ps.setString(parameterIndex++, userDetails.getValue1());
			ps.setTimestamp(parameterIndex++, startDate);
			if (verifiedEndDate != null) {
				ps.setTimestamp(parameterIndex++, verifiedEndDate);
			} else {
				ps.setNull(parameterIndex++, java.sql.Types.TIMESTAMP);
			}
			ps.execute();
			if(!ps.getConnection().getAutoCommit()) {
				ps.getConnection().commit();
			}
		} catch(Exception e) {
			classLogger.error(Constants.STACKTRACE, e);
			throw new IllegalArgumentException("An error occurred adding user permissions for this project");
		} finally {
			ConnectionUtils.closeAllConnectionsIfPooling(securityDb, ps);
		}
	}

	/**
	 * 
	 * @param user
	 * @param existingUserId
	 * @param projectId
	 * @param newPermission
	 * @return
	 * @throws IllegalAccessException 
	 */
	public static void editProjectUserPermission(User user, String existingUserId, String projectId, String newPermission, String endDate) throws IllegalAccessException {
		Pair userDetails = User.getPrimaryUserIdAndTypePair(user);

		// make sure user can edit the app
		int userPermissionLvl = getMaxUserProjectPermission(user, projectId);
		if(!AccessPermissionEnum.isEditor(userPermissionLvl)) {
			throw new IllegalAccessException("Insufficient privileges to modify this project's permissions.");
		}

		// make sure we are trying to edit a permission that exists
		Integer existingUserPermission = getUserProjectPermission(existingUserId, projectId);
		if(existingUserPermission == null) {
			throw new IllegalAccessException("Attempting to modify project permission for a user who does not currently have access to the project");
		}

		int newPermissionLvl = AccessPermissionEnum.getIdByPermission(newPermission);

		// if i am not an owner
		// then i need to check if i can edit this users permission
		if(!AccessPermissionEnum.isOwner(userPermissionLvl)) {
			// not an owner, check if trying to edit an owner or an editor/reader
			// get the current permission
			if(AccessPermissionEnum.OWNER.getId() == existingUserPermission) {
				throw new IllegalAccessException("The user doesn't have the high enough permissions to modify this users project permission.");
			}

			// also, cannot give some owner permission if i am just an editor
			if(AccessPermissionEnum.OWNER.getId() == newPermissionLvl) {
				throw new IllegalAccessException("Cannot give owner level access to this project since you are not currently an owner.");
			}
		}
		
		Timestamp startDate = Utility.getCurrentSqlTimestampUTC();
		Timestamp verifiedEndDate = null;
		if (endDate != null) {
			verifiedEndDate = AbstractSecurityUtils.calculateEndDate(endDate);
		}
		
		PreparedStatement ps = null;
		try {
			ps = securityDb.getPreparedStatement("UPDATE PROJECTPERMISSION SET PERMISSION=?, PERMISSIONGRANTEDBY=?, PERMISSIONGRANTEDBYTYPE=?, DATEADDED=?, ENDDATE=? WHERE USERID=? AND PROJECTID=?");
			int parameterIndex = 1;
			//SET
			ps.setInt(parameterIndex++, newPermissionLvl);
			ps.setString(parameterIndex++, userDetails.getValue0());
			ps.setString(parameterIndex++, userDetails.getValue1());
			ps.setTimestamp(parameterIndex++, startDate);
			ps.setTimestamp(parameterIndex++, verifiedEndDate);
			//WHERE
			ps.setString(parameterIndex++, existingUserId);
			ps.setString(parameterIndex++, projectId);
			ps.execute();
			if(!ps.getConnection().getAutoCommit()) {
				ps.getConnection().commit();
			}
		} catch(Exception e) {
			classLogger.error(Constants.STACKTRACE, e);
			throw new IllegalArgumentException("An error occurred updating the user permissions for this project");
		} finally {
			ConnectionUtils.closeAllConnectionsIfPooling(securityDb, ps);
		}
	}
	
	/**
	 * 
	 * @param user
	 * @param existingUserId
	 * @param projectId
	 * @param newPermission
	 * @return
	 * @throws IllegalAccessException 
	 */
	public static void editProjectUserPermissions(User user, String projectId, List> requests, String endDate) throws IllegalAccessException {
		Pair userDetails = User.getPrimaryUserIdAndTypePair(user);

		// make sure user can edit the database
		int userPermissionLvl = getMaxUserProjectPermission(user, projectId);
		if(!AccessPermissionEnum.isEditor(userPermissionLvl)) {
			throw new IllegalAccessException("Insufficient privileges to modify this project's permissions.");
		}
		
		
		// get userid of all requests
		List existingUserIds = new ArrayList();
	    for(Map i:requests){
	    	existingUserIds.add(i.get("userid"));
	    }
			    
		// get user permissions to edit
		Map existingUserPermission = SecurityProjectUtils.getUserProjectPermissions(existingUserIds, projectId);
		
		// make sure all users to edit currently has access to database
		Set toRemoveUserIds = new HashSet(existingUserIds);
		toRemoveUserIds.removeAll(existingUserPermission.keySet());
		if (!toRemoveUserIds.isEmpty()) {
			throw new IllegalArgumentException("Attempting to modify user permission for the following users who do not currently have access to the project: "+String.join(",", toRemoveUserIds));
		}
		
		// if user is not an owner, check to make sure they are not editting owner access
		if(!AccessPermissionEnum.isOwner(userPermissionLvl)) {
			List permissionList = new ArrayList(existingUserPermission.values());
			if(permissionList.contains(AccessPermissionEnum.OWNER.getId())) {
				throw new IllegalArgumentException("As a non-owner, you cannot edit access of an owner.");
			}
			
			// also make sure, you are not adding an owner
			for(Map req : requests) {
				if(AccessPermissionEnum.OWNER.getId() == AccessPermissionEnum.getIdByPermission(req.get("permission"))) {
					throw new IllegalArgumentException("As a non-owner, you cannot give a user access as an owner.");
				}
			}
		}
		
		Timestamp startDate = Utility.getCurrentSqlTimestampUTC();
		Timestamp verifiedEndDate = null;
		if (endDate != null) {
			verifiedEndDate = AbstractSecurityUtils.calculateEndDate(endDate);
		}
		
		// update user permissions in bulk
		PreparedStatement ps = null;
		try {
			ps = securityDb.getPreparedStatement("UPDATE PROJECTPERMISSION SET PERMISSION = ?, PERMISSIONGRANTEDBY = ?, PERMISSIONGRANTEDBYTYPE = ?, DATEADDED = ?, ENDDATE = ? WHERE USERID = ? AND PROJECTID = ?");
			for(int i=0; i deletes = new ArrayList<>();
		deletes.add("DELETE FROM PROJECT WHERE PROJECTID=?");
		deletes.add("DELETE FROM INSIGHT WHERE PROJECTID=?");
		deletes.add("DELETE FROM PROJECTPERMISSION WHERE PROJECTID=?");
		deletes.add("DELETE FROM PROJECTMETA WHERE PROJECTID=?");
		deletes.add("DELETE FROM WORKSPACEENGINE WHERE PROJECTID=?");
		deletes.add("DELETE FROM ASSETENGINE WHERE PROJECTID=?");
		// TODO: add the other tables...

		for(String deleteQuery : deletes) {
			PreparedStatement ps = null;
			try {
				ps = securityDb.getPreparedStatement(deleteQuery);
				ps.setString(1, projectId);
				ps.execute();
				if(!ps.getConnection().getAutoCommit()) {
					ps.getConnection().commit();
				}
			} catch (SQLException e) {
				classLogger.error(Constants.STACKTRACE, e);
			} finally {
				ConnectionUtils.closeAllConnectionsIfPooling(securityDb, ps);
			}
		}
	}



	/**
	 * 
	 * @param user
	 * @param editedUserId
	 * @param projectId
	 * @return
	 * @throws IllegalAccessException 
	 */
	public static void removeProjectUser(User user, String existingUserId, String projectId) throws IllegalAccessException {
		// make sure user can edit the app
		int userPermissionLvl = getMaxUserProjectPermission(user, projectId);
		if(!AccessPermissionEnum.isEditor(userPermissionLvl)) {
			throw new IllegalAccessException("Insufficient privileges to modify this project's permissions.");
		}

		// make sure we are trying to edit a permission that exists
		Integer existingUserPermission = getUserProjectPermission(existingUserId, projectId);
		if(existingUserPermission == null) {
			throw new IllegalArgumentException("Attempting to modify user permission for a user who does not currently have access to the project");
		}

		// if i am not an ownerId
		// then i need to check if i can remove this users permission
		if(!AccessPermissionEnum.isOwner(userPermissionLvl)) {
			// not an owner, check if trying to edit an owner or an editor/reader
			// get the current permission
			if(AccessPermissionEnum.OWNER.getId() == existingUserPermission) {
				throw new IllegalAccessException("The user doesn't have the high enough permissions to modify this users project permission.");
			}
		}

		String[] deletes = new String[] {
				"DELETE FROM PROJECTPERMISSION WHERE USERID=? AND PROJECTID=?",
				"DELETE FROM USERINSIGHTPERMISSION WHERE USERID=? AND PROJECTID=?"
		};
		for(String deleteQuery : deletes) {
			PreparedStatement ps = null;
			try {
				ps = securityDb.getPreparedStatement(deleteQuery);
				int parameterIndex = 1;
				ps.setString(parameterIndex++, existingUserId);
				ps.setString(parameterIndex++, projectId);
				ps.execute();
				if(!ps.getConnection().getAutoCommit()) {
					ps.getConnection().commit();
				}
			} catch (SQLException e) {
				classLogger.error(Constants.STACKTRACE, e);
				throw new IllegalArgumentException("An error occurred removing the user permissions for the project and insights of this project");
			} finally {
				ConnectionUtils.closeAllConnectionsIfPooling(securityDb, ps);
			}
		}
	}
	
	/**
	 * 
	 * @param userId
	 * @param projectId
	 * @return
	 */
	public static void removeExpiredProjectUser(String userId, String projectId) {
		String deleteQuery = "DELETE FROM PROJECTPERMISSION WHERE USERID=? AND PROJECTID=?";
		PreparedStatement ps = null;
		try {
			ps = securityDb.getPreparedStatement(deleteQuery);
			int parameterIndex = 1;
			ps.setString(parameterIndex++, userId);
			ps.setString(parameterIndex++, projectId);
			ps.execute();
			if(!ps.getConnection().getAutoCommit()) {
				ps.getConnection().commit();
			}
		} catch (SQLException e) {
			throw new IllegalArgumentException("An error occurred removing the user permissions for this project");
		} finally {
			ConnectionUtils.closeAllConnectionsIfPooling(securityDb, ps);
		}
	}


	/**
	 * Set if the project is public to all users on this instance
	 * @param user
	 * @param projectId
	 * @param global
	 * @return
	 * @throws IllegalAccessException 
	 */
	public static boolean setProjectGlobal(User user, String projectId, boolean global) throws IllegalAccessException {
		if(!SecurityUserProjectUtils.userIsOwner(user, projectId)) {
			throw new IllegalAccessException("The user doesn't have the permission to set this project as global. Only the owner or an admin can perform this action.");
		}
		
		PreparedStatement ps = null;
		try {
			ps = securityDb.getPreparedStatement("UPDATE PROJECT SET GLOBAL=? WHERE PROJECTID=?");
			ps.setBoolean(1, global);
			ps.setString(2, projectId);
			ps.execute();
			if(!ps.getConnection().getAutoCommit()) {
				ps.getConnection().commit();
			}
		} catch(Exception e) {
			classLogger.error(Constants.STACKTRACE, e);
		} finally {
			ConnectionUtils.closeAllConnectionsIfPooling(securityDb, ps);
		}
		return true;
	}
	
	/**
	 * Set a project and all its insights to be global
	 * @param projectId
	 */
	public static void setProjectCompletelyGlobal(String projectId) {
		{
			String update1 = "UPDATE PROJECT SET GLOBAL=? WHERE PROJECTID=?";
			PreparedStatement ps = null;
			try {
				ps = securityDb.getPreparedStatement(update1);
				int parameterIndex = 1;
				ps.setBoolean(parameterIndex++, true);
				ps.setString(parameterIndex++, projectId);
				ps.execute();
				if(!ps.getConnection().getAutoCommit()) {
					ps.getConnection().commit();
				}
			} catch (SQLException e) {
				classLogger.error(Constants.STACKTRACE, e);
			} finally {
				ConnectionUtils.closeAllConnectionsIfPooling(securityDb, ps);
			}
		}
		
		{
			String update1 = "UPDATE INSIGHT SET GLOBAL=? WHERE PROJECTID=?";
			PreparedStatement ps = null;
			try {
				ps = securityDb.getPreparedStatement(update1);
				int parameterIndex = 1;
				ps.setBoolean(parameterIndex++, true);
				ps.setString(parameterIndex++, projectId);
				ps.execute();
				if(!ps.getConnection().getAutoCommit()) {
					ps.getConnection().commit();
				}
			} catch (SQLException e) {
				classLogger.error(Constants.STACKTRACE, e);
			} finally {
				ConnectionUtils.closeAllConnectionsIfPooling(securityDb, ps);
			}
		}
	}
	
	/**
	 * Set project discoverable
	 * @param user
	 * @param projectId
	 * @param discoverable
	 * @return
	 * @throws IllegalAccessException
	 */
	public static boolean setProjectDiscoverable(User user, String projectId, boolean discoverable) throws IllegalAccessException {
		if(!SecurityProjectUtils.userIsOwner(user, projectId)) {
			throw new IllegalAccessException("The user doesn't have the permission to set this project as discoverable. Only the owner or an admin can perform this action.");
		}
		
		PreparedStatement ps = null;
		try {
			ps = securityDb.getPreparedStatement("UPDATE PROJECT SET DISCOVERABLE=? WHERE PROJECTID=?");
			ps.setBoolean(1, discoverable);
			ps.setString(2, projectId);
			ps.execute();
			if(!ps.getConnection().getAutoCommit()) {
				ps.getConnection().commit();
			}
		} catch(Exception e) {
			classLogger.error(Constants.STACKTRACE, e);
		} finally {
			ConnectionUtils.closeAllConnectionsIfPooling(securityDb, ps);
		}
		return true;
	}

	/**
	 * update the project name
	 * @param user
	 * @param projectId
	 * @param isPublic
	 * @return
	 * @throws IllegalAccessException 
	 */
	public static boolean setProjectName(User user, String projectId, String newProjectName) throws IllegalAccessException {
		if(!SecurityUserProjectUtils.userIsOwner(user, projectId)) {
			throw new IllegalAccessException("The user doesn't have the permission to change the project name. Only the owner or an admin can perform this action.");
		}
		PreparedStatement ps = null;
		try {
			ps = securityDb.getPreparedStatement("UPDATE PROJECT SET PROJECTNAME=? WHERE PROJECTID=?");
			int parameterIndex = 1;
			// SET
			ps.setString(parameterIndex++, newProjectName);
			// WHERE
			ps.setString(parameterIndex++, projectId);
			ps.execute();
			if(!ps.getConnection().getAutoCommit()) {
				ps.getConnection().commit();
			}
		} catch(Exception e) {
			classLogger.error(Constants.STACKTRACE, e);
			throw new IllegalArgumentException("An error occurred updating the project name");
		} finally {
			ConnectionUtils.closeAllConnectionsIfPooling(securityDb, ps);
		}
		return true;
	}

	
	/*
	 * Project Dependencies
	 */
	
	/**
	 * Update the project dependencies
	 * Will delete existing values and then perform a bulk insert
	 * @param user
	 * @param projectId
	 * @param dependentEngineIds
	 */
	public static void updateProjectDependencies(User user, String projectId, Collection dependentEngineIds) {
		// first do a delete
		String deleteQ = "DELETE FROM PROJECTDEPENDENCIES WHERE PROJECTID=?";
		PreparedStatement deletePs = null;
		try {
			deletePs = securityDb.getPreparedStatement(deleteQ);
			int parameterIndex = 1;
			deletePs.setString(parameterIndex++, projectId);
			deletePs.execute();
			ConnectionUtils.commitConnection(deletePs.getConnection());
		} catch(Exception e) {
			classLogger.error(Constants.STACKTRACE, e);
		} finally {
			ConnectionUtils.closeAllConnectionsIfPooling(securityDb, deletePs);
		}
		
		if(dependentEngineIds != null && !dependentEngineIds.isEmpty()) {
			AccessToken token = user.getPrimaryLoginToken();
			java.sql.Timestamp timestamp = Utility.getCurrentSqlTimestampUTC();
			// now we do the new insert with the order of the tags
			String query = securityDb.getQueryUtil().createInsertPreparedStatementString("PROJECTDEPENDENCIES", new String[]{"PROJECTID", "ENGINEID", "USERID", "TYPE", "DATEADDED"});
			PreparedStatement ps = null;
			try {
				ps = securityDb.getPreparedStatement(query);
				for(String depEngineId : dependentEngineIds) {
					int parameterIndex = 1;
					ps.setString(parameterIndex++, projectId);
					ps.setString(parameterIndex++, depEngineId);
					ps.setString(parameterIndex++, token.getId());
					ps.setString(parameterIndex++, token.getProvider().getLabel());
					ps.setTimestamp(parameterIndex++, timestamp);
					ps.addBatch();
				}
				ps.executeBatch();
				ConnectionUtils.commitConnection(ps.getConnection());
			} catch(Exception e) {
				classLogger.error(Constants.STACKTRACE, e);
			} finally {
				ConnectionUtils.closeAllConnectionsIfPooling(securityDb, ps);
			}
		}
	}
	
	/**
	 * 
	 * @param user
	 * @param projectId
	 * @return
	 */
	public static List getProjectDependencies(String projectId) {
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("PROJECTDEPENDENCIES__ENGINEID"));
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("PROJECTDEPENDENCIES__PROJECTID", "==", projectId));
		return QueryExecutionUtility.flushToListString(securityDb, qs);
	}
	
	/**
	 * 
	 * @param user
	 * @param projectId
	 * @return
	 */
	public static List> getProjectDependencyDetails(String projectId) {
		SelectQueryStruct qs = new SelectQueryStruct();
		qs.addSelector(new QueryColumnSelector("PROJECTDEPENDENCIES__ENGINEID", "engine_id"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINENAME", "engine_name"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINETYPE", "engine_type"));
		qs.addSelector(new QueryColumnSelector("ENGINE__ENGINESUBTYPE", "engine_subtype"));
		qs.addSelector(new QueryColumnSelector("ENGINE__DATECREATED", "engine_date_created"));
		qs.addSelector(new QueryColumnSelector("ENGINE__DISCOVERABLE", "engine_discoverable"));
		qs.addSelector(new QueryColumnSelector("ENGINE__GLOBAL", "engine_global"));
		qs.addRelation("PROJECTDEPENDENCIES__ENGINEID", "ENGINE__ENGINEID", "inner.join");
		qs.addExplicitFilter(SimpleQueryFilter.makeColToValFilter("PROJECTDEPENDENCIES__PROJECTID", "==", projectId));
		return QueryExecutionUtility.flushRsToMap(securityDb, qs);
	}

	/*
	 * Project Metadata
	 */

	/**
	 * Update the project metadata
	 * Will delete existing values and then perform a bulk insert
	 * @param projectId
	 * @param metadata
	 */
	public static void updateProjectMetadata(String projectId, Map metadata) {
		// first do a delete
		String deleteQ = "DELETE FROM PROJECTMETA WHERE METAKEY=? AND PROJECTID=?";
		PreparedStatement deletePs = null;
		try {
			deletePs = securityDb.getPreparedStatement(deleteQ);
			for(String field : metadata.keySet()) {
				int parameterIndex = 1;
				deletePs.setString(parameterIndex++, field);
				deletePs.setString(parameterIndex++, projectId);
				deletePs.addBatch();
			}
			deletePs.executeBatch();
			if(!deletePs.getConnection().getAutoCommit()) {
				deletePs.getConnection().commit();
			}
		} catch(Exception e) {
			classLogger.error(Constants.STACKTRACE, e);
		} finally {
			ConnectionUtils.closeAllConnectionsIfPooling(securityDb, deletePs);
		}
		
		// now we do the new insert with the order of the tags
		String query = securityDb.getQueryUtil().createInsertPreparedStatementString("PROJECTMETA", new String[]{"PROJECTID", "METAKEY", "METAVALUE", "METAORDER"});
		PreparedStatement ps = null;
		try {
			ps = securityDb.getPreparedStatement(query);
			for(String field : metadata.keySet()) {
				Object val = metadata.get(field);
				List