public static BinaryCondition equalTo(Object value1, Object value2){ if(value1 != null && value1.getClass().equals(String.class)){ value1 = escapeChars((String) value1); } if(value2 != null && value2.getClass().equals(String.class)){ value2 = escapeChars((String) value2); } return BinaryCondition.equalTo(value1, value2); }
private JoinTo(JoinType joinType, SqlObject toTable, List<? extends Column> fromColumns, List<? extends Column> toColumns) { this(joinType, toTable, ComboCondition.and()); // create join condition ComboCondition onCondition = (ComboCondition)_onCondition; for(int i = 0; i < fromColumns.size(); ++i) { onCondition.addCondition( BinaryCondition.equalTo(fromColumns.get(i), toColumns.get(i))); } }
private Condition generateStringConditionForVariantDatabaseField(MedSavantConditionViewGenerator.DatabaseFieldStruct s, String encoding) { /*if (StringConditionEncoder.encodesAll(encoding)) { return BinaryCondition.equalTo(1, 1); } else if (StringConditionEncoder.encodesNone(encoding)) { return BinaryCondition.equalTo(1, 0); }*/ List<String> selected = StringConditionEncoder.unencodeConditions(encoding); if (selected.isEmpty()) { return BinaryCondition.equalTo(1, 0); // always false } DbColumn col = ProjectController.getInstance().getCurrentVariantTableSchema().getDBColumn(field.getColumnName()); Condition[] conditions = new Condition[selected.size()]; int i = 0; for (String select : selected) { conditions[i++] = BinaryCondition.equalTo(col, select); } return ComboCondition.or(conditions); }
private static int[] getFileIds(String sessionId, int projectID, int referenceID, int updateID) throws SQLException, SessionExpiredException { TableSchema table = MedSavantDatabase.VariantFileTableSchema; SelectQuery sq = new SelectQuery(); sq.addFromTable(table.getTable()); sq.addColumns(table.getDBColumn(VariantFileTableSchema.COLUMNNAME_OF_FILE_ID)); sq.addCondition(BinaryCondition.equalTo(table.getDBColumn(VariantFileTableSchema.COLUMNNAME_OF_REFERENCE_ID), referenceID)); sq.addCondition(BinaryCondition.equalTo(table.getDBColumn(VariantFileTableSchema.COLUMNNAME_OF_PROJECT_ID), projectID)); sq.addCondition(BinaryCondition.equalTo(table.getDBColumn(VariantFileTableSchema.COLUMNNAME_OF_UPLOAD_ID), updateID)); ResultSet rs = ConnectionController.executeQuery(sessionId, sq.toString()); List<Integer> fileIds = new ArrayList<Integer>(); while (rs.next()) { fileIds.add(rs.getInt(VariantFileTableSchema.COLUMNNAME_OF_FILE_ID)); } return ArrayUtils.toPrimitive(fileIds.toArray(new Integer[fileIds.size()])); }
private synchronized String buildDeleteStatement(int writePartition) { String key = "delete_" + writePartition; if (cachedSQL.containsKey(key)) { return cachedSQL.get(key); } DeleteQuery delete = new DeleteQuery(tableName); QueryPreparer.MultiPlaceHolder placeHolder = (new QueryPreparer()).getNewMultiPlaceHolder(); // Only delete in the write partition. delete.addCondition(BinaryCondition.equalTo(new CustomSql(PARTITION_COLUMN_NAME), writePartition)); // Set placeholders for the arguments. for (String colName : argCols) { delete.addCondition(BinaryCondition.equalTo(new CustomSql(colName), placeHolder)); } String sql = delete.validate().toString(); cachedSQL.put(key, sql); return sql; }
private synchronized String buildPartitionMoveStatement(int oldPartition, int newPartition) { String key = "movePartition_" + oldPartition + "_" + newPartition; if (cachedSQL.containsKey(key)) { return cachedSQL.get(key); } UpdateQuery update = new UpdateQuery(tableName); update.addCondition(BinaryCondition.equalTo(new CustomSql(PARTITION_COLUMN_NAME), oldPartition)); update.addCustomSetClause(new CustomSql(PARTITION_COLUMN_NAME), newPartition); String sql = update.validate().toString(); cachedSQL.put(key, sql); return sql; }
private int getTotalNumStarred(String sid, int projectId, int referenceId) throws SQLException, SessionExpiredException { TableSchema table = MedSavantDatabase.VariantStarredTableSchema; SelectQuery q = new SelectQuery(); q.addFromTable(table.getTable()); q.addCustomColumns(FunctionCall.countAll()); q.addCondition(BinaryCondition.equalTo(table.getDBColumn(VariantStarredTableSchema.COLUMNNAME_OF_PROJECT_ID), projectId)); q.addCondition(BinaryCondition.equalTo(table.getDBColumn(VariantStarredTableSchema.COLUMNNAME_OF_REFERENCE_ID), referenceId)); ResultSet rs = ConnectionController.executeQuery(sid, q.toString()); rs.next(); return rs.getInt(1); }
private void addFilterConditions(Formula filterFormula, SelectQuery query, Map<Atom, String> tableAliases) { if (filterFormula instanceof Atom) { CustomSql valueColumn = new CustomSql(tableAliases.get((Atom)filterFormula) + "." + PredicateInfo.VALUE_COLUMN_NAME); query.addCondition(BinaryCondition.greaterThan(valueColumn, 0.0)); } else if (filterFormula instanceof Negation) { Atom atom = (Atom)(((Negation)filterFormula).getFormula()); CustomSql valueColumn = new CustomSql(tableAliases.get(atom) + "." + PredicateInfo.VALUE_COLUMN_NAME); query.addCondition(BinaryCondition.equalTo(valueColumn, 0.0)); } else if (filterFormula instanceof Conjunction) { Conjunction conjunction = (Conjunction)filterFormula; for (int i = 0; i < conjunction.length(); i++) { addFilterConditions(conjunction.get(i), query, tableAliases); } } else { throw new IllegalStateException("Unexpected formula type: " + filterFormula.getClass().getName()); } }
@Override public void removeVariantComments(String sessID, List<VariantComment> comments) throws SQLException, SessionExpiredException { TableSchema table = MedSavantDatabase.VariantStarredTableSchema; Connection c = ConnectionController.connectPooled(sessID); c.setAutoCommit(false); for (VariantComment vc : comments) { DeleteQuery q = new DeleteQuery(table.getTable()); q.addCondition(BinaryCondition.equalTo(table.getDBColumn(VariantStarredTableSchema.COLUMNNAME_OF_PROJECT_ID), vc.getProjectId())); q.addCondition(BinaryCondition.equalTo(table.getDBColumn(VariantStarredTableSchema.COLUMNNAME_OF_REFERENCE_ID), vc.getReferenceId())); q.addCondition(BinaryCondition.equalTo(table.getDBColumn(VariantStarredTableSchema.COLUMNNAME_OF_UPLOAD_ID), vc.getUploadId())); q.addCondition(BinaryCondition.equalTo(table.getDBColumn(VariantStarredTableSchema.COLUMNNAME_OF_FILE_ID), vc.getFileId())); q.addCondition(BinaryCondition.equalTo(table.getDBColumn(VariantStarredTableSchema.COLUMNNAME_OF_VARIANT_ID), vc.getVariantId())); q.addCondition(BinaryCondition.equalTo(table.getDBColumn(VariantStarredTableSchema.COLUMNNAME_OF_USER), vc.getUser())); q.addCondition(BinaryCondition.equalTo(table.getDBColumn(VariantStarredTableSchema.COLUMNNAME_OF_TIMESTAMP), vc.getTimestamp())); ConnectionController.executeUpdate(sessID, q.toString()); } c.commit(); c.setAutoCommit(true); c.close(); }
@Override public SimpleVariantFile[] getUploadedFiles(String sessID, int projID, int refID) throws SQLException, RemoteException, SessionExpiredException { TableSchema fileTable = MedSavantDatabase.VariantFileIBTableSchema; SelectQuery query = new SelectQuery(); query.addFromTable(fileTable.getTable()); query.setIsDistinct(true); query.addColumns(fileTable.getDBColumn(VariantFileTableSchema.COLUMNNAME_OF_UPLOAD_ID), fileTable.getDBColumn(VariantFileIBTableSchema.COLUMNNAME_OF_FILE_ID), fileTable.getDBColumn(VariantFileTableSchema.COLUMNNAME_OF_FILE_NAME)); query.addCondition(BinaryCondition.equalTo(fileTable.getDBColumn(VariantFileIBTableSchema.COLUMNNAME_OF_PROJECT_ID), projID)); query.addCondition(BinaryCondition.equalTo(fileTable.getDBColumn(VariantFileIBTableSchema.COLUMNNAME_OF_REFERENCE_ID), refID)); ResultSet rs = ConnectionController.executeQuery(sessID, query.toString()); LOG.info("Getting variant tables: " + query.toString()); List<SimpleVariantFile> result = new ArrayList<SimpleVariantFile>(); while (rs.next()) { // upload, file, path result.add(new SimpleVariantFile(rs.getInt(1), rs.getInt(2), rs.getString(3))); } return result.toArray(new SimpleVariantFile[0]); }
@Override public void updatePatient(String sid, int projectId, int patientID, List<CustomField> cols, List<String> values) throws SQLException, RemoteException, SessionExpiredException { String tablename = getPatientTableName(sid, projectId); TableSchema table = CustomTables.getInstance().getCustomTableSchema(sid, tablename); UpdateQuery query = new UpdateQuery(table.getTable()); query.addCondition(BinaryCondition.equalTo(table.getDBColumn(PATIENT_ID), patientID)); for (int i = 0; i < Math.min(cols.size(), values.size()); i++) { query.addSetClause(table.getDBColumn(cols.get(i).getColumnName()), values.get(i)); } ConnectionController.executeUpdate(sid, query.toString()); }
public void addSubsetInfoToMap(String sessID, int projID, int refID, int updID, String subTableName, float multiplier) throws SQLException, SessionExpiredException { TableSchema table = MedSavantDatabase.VarianttablemapTableSchema; UpdateQuery query = new UpdateQuery(table.getTable()); query.addCustomSetClause(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_VARIANT_SUBSET_TABLENAME), subTableName); query.addCustomSetClause(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_SUBSET_MULTIPLIER), multiplier); query.addCondition(BinaryCondition.equalTo(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PROJECT_ID), projID)); query.addCondition(BinaryCondition.equalTo(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_REFERENCE_ID), refID)); query.addCondition(BinaryCondition.equalTo(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_UPDATE_ID), updID)); ConnectionController.executeUpdate(sessID, query.toString()); }
@Override public void dropRolesForUser(String sessID, String user, Set<UserRole> roles) throws RemoteException, SessionExpiredException, SQLException, SecurityException { checkAdmin(sessID); //Check if any of the roles given are already assigned, and if so remove them from the //roles to register. Set<UserRole> assignedRoles = getRolesForUser(sessID, user); if (assignedRoles.containsAll(roles)) { return; } else if (assignedRoles.size() > 0) { roles.removeAll(assignedRoles); } //register the remaining roles. TableSchema raTable = MedSavantDatabase.UserRoleAssignmentTableSchema; for (UserRole role : roles) { DeleteQuery dq = new DeleteQuery(raTable.getTableName()); dq.addCondition(BinaryCondition.equalTo(raTable.getDBColumn(MedSavantDatabase.UserRoleAssignmentTableSchema.COLUMNNAME_OF_USERNAME), user)); dq.addCondition(BinaryCondition.equalTo(raTable.getDBColumn(MedSavantDatabase.UserRoleAssignmentTableSchema.COLUMNNAME_OF_ROLE_ID), role.getRoleId())); ConnectionController.executeUpdate(sessID, dq.toString()); } }
@Override public void removeSetting(String sid, String key) throws SQLException, SessionExpiredException{ TableSchema table = MedSavantDatabase.SettingsTableSchema; DeleteQuery query = new DeleteQuery(table.getTable()); query.addCondition(BinaryCondition.equalTo(table.getDBColumn(SettingsTableSchema.COLUMNNAME_OF_KEY), key)); ConnectionController.executeUpdate(sid, query.toString()); }
public void deleteComment(String sessID, int userCommentId) throws SessionExpiredException, SQLException, RemoteException, SecurityException { if (!isAuthorizedForUserComments(sessID)) { throw new SecurityException("This user does not have access to view comments"); } TableSchema lcTable = MedSavantDatabase.UserCommentTableSchema; UpdateQuery uq = new UpdateQuery(lcTable.getTable()); uq.addSetClause(MedSavantDatabase.UserCommentTableSchema.getDBColumn(MedSavantDatabase.UserCommentTableSchema.COLUMNNAME_OF_DELETED), true); uq.addCondition(BinaryCondition.equalTo(MedSavantDatabase.UserCommentTableSchema.getDBColumn(MedSavantDatabase.UserCommentTableSchema.COLUMNNAME_OF_USER_COMMENT_ID), userCommentId)); ConnectionController.executeUpdate(sessID, uq.toString()); }
private void removeEntryFromFileTable(String sessID, int fileID) throws SQLException, SessionExpiredException { TableSchema table = MedSavantDatabase.VariantFileTableSchema; DeleteQuery q = new DeleteQuery(table.getTable()); q.addCondition(ComboCondition.and(new Condition[]{ //BinaryCondition.equalTo(table.getDBColumn(VariantFileTableSchema.COLUMNNAME_OF_UPLOAD_ID), uploadID), BinaryCondition.equalTo(table.getDBColumn(VariantFileTableSchema.COLUMNNAME_OF_FILE_ID), fileID) })); ConnectionController.executeUpdate(sessID, q.toString()); }
@Override public String getFamilyIDOfPatient(String sessID, int projID, int patID) throws SQLException, RemoteException, SessionExpiredException { String tablename = getPatientTableName(sessID, projID); TableSchema table = CustomTables.getInstance().getCustomTableSchema(sessID, tablename); SelectQuery q1 = new SelectQuery(); q1.addFromTable(table.getTable()); q1.addColumns(table.getDBColumn(FAMILY_ID)); q1.addCondition(BinaryCondition.equalTo(table.getDBColumn(PATIENT_ID), patID)); ResultSet rs1 = ConnectionController.executeQuery(sessID, q1.toString()); if (!rs1.next()) { return null; } return rs1.getString(1); }
private void updateCommentStatus(String sessID, int commentId, boolean isApproved, boolean isIncluded, boolean isDeleted) throws SQLException, SessionExpiredException { TableSchema lcTable = MedSavantDatabase.UserCommentTableSchema; UpdateQuery uq = new UpdateQuery(lcTable.getTable()); uq.addCondition(BinaryCondition.equalTo(MedSavantDatabase.UserCommentTableSchema.getDBColumn(MedSavantDatabase.UserCommentTableSchema.COLUMNNAME_OF_USER_COMMENT_ID), commentId)); uq.addSetClause(MedSavantDatabase.UserCommentTableSchema.getDBColumn(MedSavantDatabase.UserCommentTableSchema.COLUMNNAME_OF_APPROVED), isApproved); uq.addSetClause(MedSavantDatabase.UserCommentTableSchema.getDBColumn(MedSavantDatabase.UserCommentTableSchema.COLUMNNAME_OF_INCLUDE), isIncluded); uq.addSetClause(MedSavantDatabase.UserCommentTableSchema.getDBColumn(MedSavantDatabase.UserCommentTableSchema.COLUMNNAME_OF_DELETED), isDeleted); ConnectionController.executeUpdate(sessID, uq.toString()); }
@Override public int getVariantCountInRange(String sid, int projectId, int referenceId, Condition[][] conditions, String chrom, long start, long end) throws SQLException, RemoteException, SessionExpiredException { String name = ProjectManager.getInstance().getVariantTableName(sid, projectId, referenceId, true); TableSchema table = CustomTables.getInstance().getCustomTableSchema(sid, name); Condition[] rangeConditions = new Condition[]{ BinaryCondition.equalTo(table.getDBColumn(CHROM), chrom), BinaryCondition.greaterThan(table.getDBColumn(START_POSITION), start, true), BinaryCondition.lessThan(table.getDBColumn(START_POSITION), end, false) }; Condition[] c1 = new Condition[conditions.length]; for (int i = 0; i < conditions.length; i++) { c1[i] = ComboCondition.and(conditions[i]); } Condition[] finalCondition = new Condition[]{ComboCondition.and(ComboCondition.and(rangeConditions), ComboCondition.or(c1))}; return getFilteredVariantCount(sid, projectId, referenceId, new Condition[][]{finalCondition}); }