<commit_before>package org.sagebionetworks.repo.model.query;

import static org.sagebionetworks.repo.model.query.SQLConstants.ALIAS_ANNO_BLOB;
import static org.sagebionetworks.repo.model.query.SQLConstants.ALIAS_ANNO_OWNER;
import static org.sagebionetworks.repo.model.query.SQLConstants.ALIAS_EXPRESSION;
import static org.sagebionetworks.repo.model.query.SQLConstants.ALIAS_SORT;
import static org.sagebionetworks.repo.model.query.SQLConstants.ANNO_BLOB;
import static org.sagebionetworks.repo.model.query.SQLConstants.ANNO_DOUBLE;
import static org.sagebionetworks.repo.model.query.SQLConstants.ANNO_LONG;
import static org.sagebionetworks.repo.model.query.SQLConstants.ANNO_OWNER;
import static org.sagebionetworks.repo.model.query.SQLConstants.ANNO_STRING;
import static org.sagebionetworks.repo.model.query.SQLConstants.COL_ANNO_ATTRIBUTE;
import static org.sagebionetworks.repo.model.query.SQLConstants.COL_ANNO_IS_PRIVATE;
import static org.sagebionetworks.repo.model.query.SQLConstants.COL_ANNO_VALUE;
import static org.sagebionetworks.repo.model.query.SQLConstants.COL_SUBSTATUS_ANNO_EVALID;
import static org.sagebionetworks.repo.model.query.SQLConstants.COL_SUBSTATUS_ANNO_SUBID;
import static org.sagebionetworks.repo.model.query.SQLConstants.PREFIX_SUBSTATUS;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.sagebionetworks.StackConfiguration;
import org.sagebionetworks.evaluation.dbo.DBOConstants;
import org.sagebionetworks.repo.model.ACCESS_TYPE;
import org.sagebionetworks.repo.model.AccessControlListDAO;
import org.sagebionetworks.repo.model.DatastoreException;
import org.sagebionetworks.repo.model.ObjectType;
import org.sagebionetworks.repo.model.UnauthorizedException;
import org.sagebionetworks.repo.model.UserInfo;
import org.sagebionetworks.repo.model.jdo.KeyFactory;
import org.sagebionetworks.repo.model.query.jdo.SizeLimitRowMapper;
import org.sagebionetworks.repo.model.query.jdo.SqlConstants;
import org.sagebionetworks.repo.web.NotFoundException;
import org.sagebionetworks.schema.adapter.JSONObjectAdapterException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

public class QueryDAOImpl implements QueryDAO {
	
	@Autowired
	AccessControlListDAO accessControlListDAO;
	@Autowired
	private SimpleJdbcTemplate simpleJdbcTemplate;

	private static final long MAX_BYTES_PER_QUERY = 
			StackConfiguration.getMaximumBytesPerQueryResult();
	private static Logger log = LogManager.getLogger(QueryDAOImpl.class);
	
	private static final String ATTRIBUTE_PARAM = "ATTRIBUTE";
	private static final String SCOPE_PARAM = "SCOPE_ID";
	private static final String LONG_ATTR_NAME = "LONG_ATTR";
	private static final String DBBL_ATTR_NAME = "DBBL_ATTR";
	private static final String STRG_ATTR_NAME = "STRG_ATTR";
	
	private static final String FIND_ATTRIBUTE_SQL = 
			"select distinct s."+COL_ANNO_ATTRIBUTE+" '"+STRG_ATTR_NAME+"', l."+COL_ANNO_ATTRIBUTE+" '"+LONG_ATTR_NAME+"', d."+COL_ANNO_ATTRIBUTE+" '"+DBBL_ATTR_NAME+"' from "+
			PREFIX_SUBSTATUS+ANNO_OWNER+" o "+
			"left outer join "+PREFIX_SUBSTATUS+ANNO_STRING+" s on (s."+COL_SUBSTATUS_ANNO_SUBID+"=o."+COL_SUBSTATUS_ANNO_SUBID+" and s."+COL_ANNO_ATTRIBUTE+"=:"+ATTRIBUTE_PARAM+") "+
			"left outer join "+PREFIX_SUBSTATUS+ANNO_LONG+" l on (l."+COL_SUBSTATUS_ANNO_SUBID+"=o."+COL_SUBSTATUS_ANNO_SUBID+" and l."+COL_ANNO_ATTRIBUTE+"=:"+ATTRIBUTE_PARAM+") "+
			"left outer join "+PREFIX_SUBSTATUS+ANNO_DOUBLE+" d on (d."+COL_SUBSTATUS_ANNO_SUBID+"=o."+COL_SUBSTATUS_ANNO_SUBID+" and d."+COL_ANNO_ATTRIBUTE+"=:"+ATTRIBUTE_PARAM+") "+
			"where o."+COL_SUBSTATUS_ANNO_EVALID+"=:"+SCOPE_PARAM+"";
	
	private static ObjectType getObjectTypeFromQueryObjectType(QueryObjectType qot) {
		if (qot==QueryObjectType.EVALUATION) {
			return ObjectType.EVALUATION;
		} else {
			throw new IllegalArgumentException(qot.name());
		}
	}
	
	@Override
	public QueryTableResults executeQuery(BasicQuery userQuery, UserInfo userInfo)
			throws DatastoreException, NotFoundException, JSONObjectAdapterException, 
			DataAccessException {
		// Validate incoming query
		nullCheckQuery(userQuery);
		
		// Determine query object type and access rights
		QueryObjectType objType = QueryTools.getQueryObjectType(userQuery);
		String objId = QueryTools.getQueryObjectId(userQuery);
		if (!canAccess(userInfo, objId, objType)) {
			throw new UnauthorizedException(
					"Insufficient permissions to read from Synapse object " + objId);
		}
		boolean includePrivate = canAccessPrivate(userInfo, objId, objType);
		
		FieldType sortFieldType = null;
		if (userQuery.getSort()!=null) {
			sortFieldType = findFieldTypeForAttribute(Long.parseLong(objId), userQuery.getSort());
			// if the sort-by attribute doesn't occur, then we simply don't sort
			if (sortFieldType==null) userQuery.setSort(null);
		}
		
		// Build the SQL queries
		Map<String, Object> queryParams = new HashMap<String, Object>();
		StringBuilder countQuery = new StringBuilder();
		StringBuilder fullQuery = new StringBuilder();
		buildQueryStrings(userQuery, objType, objId, userInfo, includePrivate, 
				countQuery, fullQuery, queryParams, sortFieldType);
		
		String countQueryString = countQuery.toString();
		String fullQueryString = fullQuery.toString();
		
		// Execute the count query
		long count = simpleJdbcTemplate.queryForLong(countQueryString, queryParams);
		if (count == 0) {
			// no results
			QueryTableResults results = new QueryTableResults();
			results.setHeaders(new ArrayList<String>());
			results.setRows(new ArrayList<Row>());
			results.setTotalNumberOfResults(0L);
			return results;
		}
		
		// Execute the full query
		SizeLimitRowMapper sizeLimitMapper = new SizeLimitRowMapper(MAX_BYTES_PER_QUERY);
		List<Map<String, Object>> results = simpleJdbcTemplate.query(
				fullQueryString, sizeLimitMapper, queryParams);
		Long userId = userInfo.getId();
		
		// Log query stats
		if (log.isDebugEnabled()) {
			log.debug("user: " + userId + " query: " + fullQueryString);
			log.debug("user: " + userId + " parameters: " + queryParams);
		}
		if (log.isInfoEnabled()) {
			log.info("user: " + userId +  " query bytes returned: " + sizeLimitMapper.getBytesUsed());
		}

		// Create the results
		return QueryTools.translateResults(results, count, userQuery.getSelect(), includePrivate);
	}
	
	/**
	 * When we sort by a given attribute we need to determine what type its values are
	 * @param scopeId the scope of the attribute (e.g. the Evaluation)
	 * @param attribute
	 * @return the FieldType of the attributes or null if the attribute doesn't appear in the given scope
	 */
	public FieldType findFieldTypeForAttribute(Long scopeId, String attribute) {
		MapSqlParameterSource args = new MapSqlParameterSource();
		args.addValue(ATTRIBUTE_PARAM, attribute);
		args.addValue(SCOPE_PARAM, scopeId);
			List<FieldTypeTable> results = simpleJdbcTemplate.query(FIND_ATTRIBUTE_SQL, new RowMapper<FieldTypeTable>(){
				@Override
				public FieldTypeTable mapRow(ResultSet rs, int rowNum)
						throws SQLException {
					FieldTypeTable ftt = new FieldTypeTable();
					ftt.foundInLong = (rs.getString(LONG_ATTR_NAME)!=null);
					ftt.foundInDouble = (rs.getString(DBBL_ATTR_NAME)!=null);
					ftt.foundInString = (rs.getString(STRG_ATTR_NAME)!=null);
					return ftt;
				}}, args);

		if (results.isEmpty()) return null;
		FieldTypeTable nonNullFtt = null;
		for (FieldTypeTable ftt : results) {
			if (ftt.foundInLong || ftt.foundInDouble || ftt.foundInString) {
				if (nonNullFtt!=null) throw new IllegalStateException("Expected 0-1 non-null rows but found multiple.");
				nonNullFtt = ftt;
			}
		}
		if (nonNullFtt==null) return null;
		// OK to have multiple rows as long as only one is not all nulls
		if (nonNullFtt.foundInLong) return FieldType.LONG_ATTRIBUTE;
		if (nonNullFtt.foundInDouble) return FieldType.DOUBLE_ATTRIBUTE;
		if (nonNullFtt.foundInString) return FieldType.STRING_ATTRIBUTE;
		// should never reach this point, but must have a return statement
		return null;
	}
	
	// simple class to hold the table in which an attribute is found
	private class FieldTypeTable {
		public boolean foundInLong=false;
		public boolean foundInDouble=false;
		public boolean foundInString=false;
	}

	/**
	 * Build the two query strings and prepare the query parameters.
	 */
	public static void buildQueryStrings(BasicQuery userQuery, QueryObjectType objType, String objId,
			UserInfo userInfo, boolean includePrivate, StringBuilder countQuery, 
			StringBuilder fullQuery, Map<String, Object> queryParams, FieldType sortFieldType) throws DatastoreException {		
		List<String> aliases = new ArrayList<String>();

		// <select>
		String selectCount = buildSelect(true);
		String selectId = buildSelect(false);
		
		// <from>
		StringBuilder from = buildFrom(objType, objId, aliases, userQuery, sortFieldType);

		// <where>
		StringBuilder where = buildWhere(objType, aliases, userQuery, queryParams, includePrivate);<commit_msg>PLFM-2778<commit_after>package org.sagebionetworks.repo.model.query;

import static org.sagebionetworks.repo.model.query.SQLConstants.ALIAS_ANNO_BLOB;
import static org.sagebionetworks.repo.model.query.SQLConstants.ALIAS_ANNO_OWNER;
import static org.sagebionetworks.repo.model.query.SQLConstants.ALIAS_EXPRESSION;
import static org.sagebionetworks.repo.model.query.SQLConstants.ALIAS_SORT;
import static org.sagebionetworks.repo.model.query.SQLConstants.ANNO_BLOB;
import static org.sagebionetworks.repo.model.query.SQLConstants.ANNO_DOUBLE;
import static org.sagebionetworks.repo.model.query.SQLConstants.ANNO_LONG;
import static org.sagebionetworks.repo.model.query.SQLConstants.ANNO_OWNER;
import static org.sagebionetworks.repo.model.query.SQLConstants.ANNO_STRING;
import static org.sagebionetworks.repo.model.query.SQLConstants.COL_ANNO_ATTRIBUTE;
import static org.sagebionetworks.repo.model.query.SQLConstants.COL_ANNO_IS_PRIVATE;
import static org.sagebionetworks.repo.model.query.SQLConstants.COL_ANNO_VALUE;
import static org.sagebionetworks.repo.model.query.SQLConstants.COL_SUBSTATUS_ANNO_EVALID;
import static org.sagebionetworks.repo.model.query.SQLConstants.COL_SUBSTATUS_ANNO_SUBID;
import static org.sagebionetworks.repo.model.query.SQLConstants.PREFIX_SUBSTATUS;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.sagebionetworks.StackConfiguration;
import org.sagebionetworks.evaluation.dbo.DBOConstants;
import org.sagebionetworks.repo.model.ACCESS_TYPE;
import org.sagebionetworks.repo.model.AccessControlListDAO;
import org.sagebionetworks.repo.model.DatastoreException;
import org.sagebionetworks.repo.model.ObjectType;
import org.sagebionetworks.repo.model.UnauthorizedException;
import org.sagebionetworks.repo.model.UserInfo;
import org.sagebionetworks.repo.model.jdo.KeyFactory;
import org.sagebionetworks.repo.model.query.jdo.SizeLimitRowMapper;
import org.sagebionetworks.repo.model.query.jdo.SqlConstants;
import org.sagebionetworks.repo.web.NotFoundException;
import org.sagebionetworks.schema.adapter.JSONObjectAdapterException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

public class QueryDAOImpl implements QueryDAO {
	
	@Autowired
	AccessControlListDAO accessControlListDAO;
	@Autowired
	private SimpleJdbcTemplate simpleJdbcTemplate;

	private static final long MAX_BYTES_PER_QUERY = 
			StackConfiguration.getMaximumBytesPerQueryResult();
	private static Logger log = LogManager.getLogger(QueryDAOImpl.class);
	
	private static final String ATTRIBUTE_PARAM = "ATTRIBUTE";
	private static final String SCOPE_PARAM = "SCOPE_ID";
	private static final String LONG_ATTR_NAME = "LONG_ATTR";
	private static final String DBBL_ATTR_NAME = "DBBL_ATTR";
	private static final String STRG_ATTR_NAME = "STRG_ATTR";
	
	private static final String FIND_ATTRIBUTE_SQL = 
			"select distinct s."+COL_ANNO_ATTRIBUTE+" '"+STRG_ATTR_NAME+"', l."+COL_ANNO_ATTRIBUTE+" '"+LONG_ATTR_NAME+"', d."+COL_ANNO_ATTRIBUTE+" '"+DBBL_ATTR_NAME+"' from "+
			PREFIX_SUBSTATUS+ANNO_OWNER+" o "+
			"left outer join "+PREFIX_SUBSTATUS+ANNO_STRING+" s on (s."+COL_SUBSTATUS_ANNO_SUBID+"=o."+COL_SUBSTATUS_ANNO_SUBID+" and s."+COL_ANNO_ATTRIBUTE+"=:"+ATTRIBUTE_PARAM+") "+
			"left outer join "+PREFIX_SUBSTATUS+ANNO_LONG+" l on (l."+COL_SUBSTATUS_ANNO_SUBID+"=o."+COL_SUBSTATUS_ANNO_SUBID+" and l."+COL_ANNO_ATTRIBUTE+"=:"+ATTRIBUTE_PARAM+") "+
			"left outer join "+PREFIX_SUBSTATUS+ANNO_DOUBLE+" d on (d."+COL_SUBSTATUS_ANNO_SUBID+"=o."+COL_SUBSTATUS_ANNO_SUBID+" and d."+COL_ANNO_ATTRIBUTE+"=:"+ATTRIBUTE_PARAM+") "+
			"where o."+COL_SUBSTATUS_ANNO_EVALID+"=:"+SCOPE_PARAM+"";
	
	private static ObjectType getObjectTypeFromQueryObjectType(QueryObjectType qot) {
		if (qot==QueryObjectType.EVALUATION) {
			return ObjectType.EVALUATION;
		} else {
			throw new IllegalArgumentException(qot.name());
		}
	}
	
	@Override
	public QueryTableResults executeQuery(BasicQuery userQuery, UserInfo userInfo)
			throws DatastoreException, NotFoundException, JSONObjectAdapterException, 
			DataAccessException {
		// Validate incoming query
		nullCheckQuery(userQuery);
		
		// Determine query object type and access rights
		QueryObjectType objType = QueryTools.getQueryObjectType(userQuery);
		String objId = QueryTools.getQueryObjectId(userQuery);
		if (!canAccess(userInfo, objId, objType)) {
			throw new UnauthorizedException(
					"Insufficient permissions to read from Synapse object " + objId);
		}
		boolean includePrivate = canAccessPrivate(userInfo, objId, objType);
		
		FieldType sortFieldType = null;
		if (userQuery.getSort()!=null) {
			sortFieldType = findFieldTypeForAttribute(Long.parseLong(objId), userQuery.getSort());
			// if the sort-by attribute doesn't occur, then we simply don't sort
			if (sortFieldType==null) userQuery.setSort(null);
		}
		
		// Build the SQL queries
		Map<String, Object> queryParams = new HashMap<String, Object>();
		StringBuilder countQuery = new StringBuilder();
		StringBuilder fullQuery = new StringBuilder();
		buildQueryStrings(userQuery, objType, objId, userInfo, includePrivate, 
				countQuery, fullQuery, queryParams, sortFieldType);
		
		String countQueryString = countQuery.toString();
		String fullQueryString = fullQuery.toString();
		
		// Execute the count query
		long count = simpleJdbcTemplate.queryForLong(countQueryString, queryParams);
		if (count == 0) {
			// no results
			QueryTableResults results = new QueryTableResults();
			results.setHeaders(new ArrayList<String>());
			results.setRows(new ArrayList<Row>());
			results.setTotalNumberOfResults(0L);
			return results;
		}
		
		// Execute the full query
		SizeLimitRowMapper sizeLimitMapper = new SizeLimitRowMapper(MAX_BYTES_PER_QUERY);
		List<Map<String, Object>> results = simpleJdbcTemplate.query(
				fullQueryString, sizeLimitMapper, queryParams);
		Long userId = userInfo.getId();
		
		// Log query stats
		if (log.isDebugEnabled()) {
			log.debug("user: " + userId + " query: " + fullQueryString);
			log.debug("user: " + userId + " parameters: " + queryParams);
		}
		if (log.isInfoEnabled()) {
			log.info("user: " + userId +  " query bytes returned: " + sizeLimitMapper.getBytesUsed());
		}

		// Create the results
		return QueryTools.translateResults(results, count, userQuery.getSelect(), includePrivate);
	}
	
	/**
	 * When we sort by a given attribute we need to determine what type its values are
	 * @param scopeId the scope of the attribute (e.g. the Evaluation)
	 * @param attribute
	 * @return the FieldType of the attributes or null if the attribute doesn't appear in the given scope
	 */
	public FieldType findFieldTypeForAttribute(Long scopeId, String attribute) {
		MapSqlParameterSource args = new MapSqlParameterSource();
		args.addValue(ATTRIBUTE_PARAM, attribute);
		args.addValue(SCOPE_PARAM, scopeId);
		final Map<String,Boolean> map = new HashMap<String,Boolean>();
		simpleJdbcTemplate.query(FIND_ATTRIBUTE_SQL, new RowMapper<Object>(){
			@Override
			public Object mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				if (rs.getString(LONG_ATTR_NAME)!=null) map.put(LONG_ATTR_NAME, true);
				if (rs.getString(DBBL_ATTR_NAME)!=null) map.put(DBBL_ATTR_NAME, true);
				if (rs.getString(STRG_ATTR_NAME)!=null) map.put(STRG_ATTR_NAME, true);
				return null;
			}}, args);
		if (map.get(LONG_ATTR_NAME)!=null) return FieldType.LONG_ATTRIBUTE;
		if (map.get(DBBL_ATTR_NAME)!=null) return FieldType.DOUBLE_ATTRIBUTE;
		if (map.get(STRG_ATTR_NAME)!=null) return FieldType.STRING_ATTRIBUTE;
		return null;
	}


	/**
	 * Build the two query strings and prepare the query parameters.
	 */
	public static void buildQueryStrings(BasicQuery userQuery, QueryObjectType objType, String objId,
			UserInfo userInfo, boolean includePrivate, StringBuilder countQuery, 
			StringBuilder fullQuery, Map<String, Object> queryParams, FieldType sortFieldType) throws DatastoreException {		
		List<String> aliases = new ArrayList<String>();

		// <select>
		String selectCount = buildSelect(true);
		String selectId = buildSelect(false);
		
		// <from>
		StringBuilder from = buildFrom(objType, objId, aliases, userQuery, sortFieldType);

		// <where>
		StringBuilder where = buildWhere(objType, aliases, userQuery, queryParams, includePrivate);
{'fraction_non_alphanumeric': 0.1018262313226342, 'fraction_numerical': 0.000774764803541782, 'mean_word_length': 5.796163971417826, 'pattern_counts': {'":': 0, '<': 32, '<?xml version=': 0, '>': 32, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3406624', 'n_tokens_mistral': 6086, 'n_tokens_neox': 5711, 'n_words': 1179}