edu.emory.cci.aiw.umls.UMLSDatabaseConnection Maven / Gradle / Ivy
/*
* #%L
* UMLSQuery
* %%
* Copyright (C) 2012 - 2013 Emory University
* %%
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
* #L%
*/
package edu.emory.cci.aiw.umls;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Queue;
import java.util.Set;
import java.util.logging.Level;
import org.apache.commons.lang3.StringUtils;
import org.arp.javautil.sql.DatabaseAPI;
import org.arp.javautil.sql.InvalidConnectionSpecArguments;
/**
* A database-baked implementation of the {@link UMLSQueryExecutor} interface.
* An instance is obtained by calling the static method {@link #getConnection}
* with the parameters for accessing the database. Additionally, the caller must
* pass in the database API type. Once an instance has been obtained, any of the
* queries defined in the {@link UMLSQueryExecutor} interface may be executed.
*/
public class UMLSDatabaseConnection implements UMLSQueryExecutor {
private Connection conn;
private final DatabaseAPI api;
private final String url;
private final String user;
private final String password;
private static void log(Level level, String msg) {
UMLSUtil.logger().log(level, msg);
}
private UMLSDatabaseConnection(DatabaseAPI api, String url, String user,
String password) {
this.api = api;
this.url = url;
this.user = user;
this.password = password;
}
/**
* Returns a
* UMLSDatabaseConnection for querying a UMLS database. Callers
* must specify the location and access information for the database, as
* well as the database API type.
*
* @param api the Java database API to use. An instance of the
* {@link DatabaseAPI} enum, which provides the
* {@link java.sql.DriverMananger} and {@link javax.sql.DataSource} methods.
* @param url the location of the database
* @param user the username to access the database
* @param password the password that goes with the username to access the
* database
* @return a UMLSDatabaseConnection accessed by the specified
* parameters
*/
public static UMLSDatabaseConnection getConnection(DatabaseAPI api,
String url, String user, String password) {
return new UMLSDatabaseConnection(api, url, user, password);
}
private void setupConn() throws UMLSQueryException {
log(Level.FINE, "Attempting to establish database connection...");
try {
conn = api.newConnectionSpecInstance(url, user, password)
.getOrCreate();
log(Level.FINE, "Connection established with " + url);
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
} catch (InvalidConnectionSpecArguments icsa) {
throw new UMLSQueryException(icsa);
}
}
private void tearDownConn() throws UMLSQueryException {
if (conn != null) {
log(Level.FINE, "Attempting to disconnect from the database...");
try {
conn.close();
log(Level.FINE, "Disconnected from database " + url);
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
}
}
}
/*
* (non-Javadoc)
*
* @see
* edu.emory.cci.aiw.umls.UMLSQueryExecutor#getCUI(edu.emory.cci.aiw.umls
* .CUIQuerySearchUID, edu.emory.cci.aiw.umls.SABValue, boolean)
*/
@Override
public List getCUI(CUIQuerySearchUID uid, List sabs,
boolean caseSensitive) throws UMLSQueryException {
try {
setupConn();
StringBuilder sql = new StringBuilder(
"select distinct(CUI) from MRCONSO where ");
sql.append(uid.getKeyName());
sql.append(" = ");
if (caseSensitive) {
sql.append("BINARY ");
}
sql.append("?");
if (sabs != null && !sabs.isEmpty()) {
sql.append(" and ");
sql.append(singletonOrSetClause(sabs.get(0).getKeyName(),
sabs.size()));
}
log(Level.FINE, sql.toString());
List params = new ArrayList();
params.add(uid);
if (sabs != null) {
params.addAll(sabs);
}
ResultSet r = executeAndLogQuery(substParams(sql.toString(), params));
List cuis = new ArrayList();
while (r.next()) {
cuis.add(ConceptUID.fromString(r.getString(1)));
}
return cuis;
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
} catch (MalformedUMLSUniqueIdentifierException muuie) {
throw new UMLSQueryException(muuie);
} finally {
tearDownConn();
}
}
private ResultSet getCUIMult(List uids,
List sabs, boolean caseSensitive) throws SQLException {
StringBuilder sql = new StringBuilder("select distinct(CUI), ");
sql.append(uids.get(0).getKeyName());
sql.append(" from MRCONSO where");
sql.append(caseSensitive ? "BINARY " : " ");
sql.append(singletonOrSetClause(uids.get(0).getKeyName(), uids.size()));
if (sabs != null && !sabs.isEmpty()) {
sql.append(" and ");
sql.append(singletonOrSetClause(sabs.get(0).getKeyName(),
sabs.size()));
}
log(Level.FINE, sql.toString());
List params = new ArrayList();
params.addAll(uids);
if (sabs != null) {
params.addAll(sabs);
}
return executeAndLogQuery(substParams(sql.toString(), params));
}
/*
* (non-Javadoc)
*
* @see
* edu.emory.cci.aiw.umls.UMLSQueryExecutor#getCUIMultByCUI(java.util.List,
* java.util.List, boolean)
*/
@Override
public Map> getCUIMultByCUI(
List cuis, List sabs, boolean caseSensitive)
throws UMLSQueryException {
Map> result = new HashMap>();
try {
setupConn();
ResultSet rs = getCUIMult(cuis, sabs, caseSensitive);
while (rs.next()) {
ConceptUID cui = ConceptUID.fromString(rs.getString(1));
ConceptUID byCui = ConceptUID.fromString(rs.getString(2));
if (!result.containsKey(byCui)) {
result.put(byCui, new ArrayList());
}
result.get(byCui).add(cui);
}
return result;
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
} catch (MalformedUMLSUniqueIdentifierException muuie) {
throw new UMLSQueryException(muuie);
} finally {
tearDownConn();
}
}
/*
* (non-Javadoc)
*
* @see
* edu.emory.cci.aiw.umls.UMLSQueryExecutor#getCUIMultByAUI(java.util.List,
* java.util.List, boolean)
*/
@Override
public Map> getCUIMultByAUI(List auis,
List sabs, boolean caseSensitive) throws UMLSQueryException {
Map> result = new HashMap>();
try {
setupConn();
ResultSet rs = getCUIMult(auis, sabs, caseSensitive);
while (rs.next()) {
ConceptUID cui = ConceptUID.fromString(rs.getString(1));
AtomUID byAui = AtomUID.fromString(rs.getString(2));
if (!result.containsKey(byAui)) {
result.put(byAui, new ArrayList());
}
result.get(byAui).add(cui);
}
return result;
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
} catch (MalformedUMLSUniqueIdentifierException muuie) {
throw new UMLSQueryException(muuie);
} finally {
tearDownConn();
}
}
/*
* (non-Javadoc)
*
* @see
* edu.emory.cci.aiw.umls.UMLSQueryExecutor#getCUIMultByLUI(java.util.List,
* java.util.List, boolean)
*/
@Override
public Map> getCUIMultByLUI(
List luis, List sabs, boolean caseSensitive)
throws UMLSQueryException {
Map> result = new HashMap>();
try {
setupConn();
ResultSet rs = getCUIMult(luis, sabs, caseSensitive);
while (rs.next()) {
ConceptUID cui = ConceptUID.fromString(rs.getString(1));
LexicalUID byLui = LexicalUID.fromString(rs.getString(2));
if (!result.containsKey(byLui)) {
result.put(byLui, new ArrayList());
}
result.get(byLui).add(cui);
}
return result;
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
} catch (MalformedUMLSUniqueIdentifierException muuie) {
throw new UMLSQueryException(muuie);
} finally {
tearDownConn();
}
}
/*
* (non-Javadoc)
*
* @see
* edu.emory.cci.aiw.umls.UMLSQueryExecutor#getCUIMultByString(java.util
* .List, java.util.List, boolean)
*/
@Override
public Map> getCUIMultByString(
List strings, List sabs,
boolean caseSensitive) throws UMLSQueryException {
Map> result = new HashMap>();
try {
setupConn();
ResultSet rs = getCUIMult(strings, sabs, caseSensitive);
while (rs.next()) {
ConceptUID cui = ConceptUID.fromString(rs.getString(1));
UMLSQueryStringValue byString = UMLSQueryStringValue
.fromString(rs.getString(2));
if (!result.containsKey(byString)) {
result.put(byString, new ArrayList());
}
result.get(byString).add(cui);
}
return result;
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
} catch (MalformedUMLSUniqueIdentifierException muuie) {
throw new UMLSQueryException(muuie);
} finally {
tearDownConn();
}
}
/*
* (non-Javadoc)
*
* @see
* edu.emory.cci.aiw.umls.UMLSQueryExecutor#getCUIMultBySUI(java.util.List,
* java.util.List, boolean)
*/
@Override
public Map> getCUIMultBySUI(
List suis, List sabs, boolean caseSensitive)
throws UMLSQueryException {
Map> result = new HashMap>();
try {
setupConn();
ResultSet rs = getCUIMult(suis, sabs, caseSensitive);
while (rs.next()) {
ConceptUID cui = ConceptUID.fromString(rs.getString(1));
StringUID bySui = StringUID.fromString(rs.getString(2));
if (!result.containsKey(bySui)) {
result.put(bySui, new ArrayList());
}
result.get(bySui).add(cui);
}
return result;
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
} catch (MalformedUMLSUniqueIdentifierException muuie) {
throw new UMLSQueryException(muuie);
} finally {
tearDownConn();
}
}
/*
* (non-Javadoc)
*
* @see
* edu.emory.cci.aiw.umls.UMLSQueryExecutor#getAUI(edu.emory.cci.aiw.umls
* .AUIQuerySearchUID, edu.emory.cci.aiw.umls.SABValue)
*/
@Override
public List getAUI(AUIQuerySearchUID uid, SAB sab)
throws UMLSQueryException {
try {
setupConn();
StringBuilder sql = new StringBuilder(
"select distinct(AUI) from MRCONSO where ");
sql.append(uid.getKeyName());
sql.append(" = ?");
if (sab != null) {
sql.append(" and ");
sql.append(sab.getKeyName());
sql.append(" = ?");
}
log(Level.FINE, sql.toString());
List params = new ArrayList();
params.add(uid);
if (sab != null) {
params.add(sab);
}
ResultSet r = executeAndLogQuery(substParams(sql.toString(), params));
List auis = new ArrayList();
while (r.next()) {
auis.add(AtomUID.fromString(r.getString(1)));
}
return auis;
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
} catch (MalformedUMLSUniqueIdentifierException muuie) {
throw new UMLSQueryException(muuie);
} finally {
tearDownConn();
}
}
/*
* (non-Javadoc)
*
* @see
* edu.emory.cci.aiw.umls.UMLSQueryExecutor#getSTR(edu.emory.cci.aiw.umls
* .STRQuerySearchUID, edu.emory.cci.aiw.umls.SABValue,
* edu.emory.cci.aiw.umls.LATValue, edu.emory.cci.aiw.umls.UMLSPreferred)
*/
@Override
public List getSTR(STRQuerySearchUID uid, SAB sab,
LAT lat, UMLSPreferred preferred) throws UMLSQueryException {
try {
setupConn();
StringBuilder sql = new StringBuilder(
"select distinct(STR) from MRCONSO where ");
sql.append(uid.getKeyName());
sql.append(" = ?");
if (preferred != null && preferred.equals(UMLSPreferred.PREFERRED)) {
sql.append(" and TS = 'P' and STT = 'PF' and ISPREF= 'Y'");
}
if (sab != null) {
sql.append(" and ");
sql.append(sab.getKeyName());
sql.append(" = ?");
}
if (lat != null) {
sql.append(" and ");
sql.append(lat.getKeyName());
sql.append(" = ?");
}
log(Level.FINE, sql.toString());
List params = new ArrayList();
params.add(uid);
if (sab != null) {
params.add(sab);
}
if (lat != null) {
params.add(lat);
}
ResultSet r = executeAndLogQuery(substParams(sql.toString(), params));
List strings = new ArrayList();
while (r.next()) {
strings.add(UMLSQueryStringValue.fromString(r.getString(1)));
}
return strings;
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
} finally {
tearDownConn();
}
}
/*
* (non-Javadoc)
*
* @see
* edu.emory.cci.aiw.umls.UMLSQueryExecutor#getTUI(edu.emory.cci.aiw.umls
* .TUIQuerySearchUID, edu.emory.cci.aiw.umls.SABValue)
*/
@Override
public List getSemanticType(TUIQuerySearchUID uid, SAB sab)
throws UMLSQueryException {
try {
setupConn();
StringBuilder sql = new StringBuilder(
"select distinct(TUI), STY from MRCONSO a, MRSTY b "
+ "where a.CUI = b.CUI and a.");
sql.append(uid.getKeyName());
sql.append(" = ?");
if (sab != null) {
sql.append(" and ");
sql.append(sab.getKeyName());
sql.append(" = ?");
}
log(Level.FINE, sql.toString());
List params = new ArrayList();
params.add(uid);
if (sab != null) {
params.add(sab);
}
ResultSet r = executeAndLogQuery(substParams(sql.toString(), params));
List types = new ArrayList();
while (r.next()) {
types.add(SemanticType.withTUIAndType(
TermUID.fromString(r.getString(1)), r.getString(2)));
}
return types;
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
} catch (MalformedUMLSUniqueIdentifierException muuie) {
throw new UMLSQueryException(muuie);
} finally {
tearDownConn();
}
}
/*
* (non-Javadoc)
*
* @see
* edu.emory.cci.aiw.umls.UMLSQueryExecutor#getSemanticTypeForTerm(edu.emory
* .cci.aiw.umls.TerminologyCode)
*/
@Override
public SemanticType getSemanticTypeForTerm(TerminologyCode code)
throws UMLSQueryException {
try {
validateCode(code);
setupConn();
SemanticType result = null;
String sql = "select distinct(TUI), STY from MRCONSO a, MRSTY b where"
+ " a.CUI = b.CUI and a.CODE = ? and a.SAB = ?";
List params = new ArrayList();
params.add(queryStr(code.getCode()));
params.add(code.getSab());
ResultSet r = executeAndLogQuery(substParams(sql, params));
if (r.next()) {
result = SemanticType.withTUIAndType(
TermUID.fromString(r.getString(1)), r.getString(2));
}
return result;
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
} finally {
tearDownConn();
}
}
/*
* (non-Javadoc)
*
* @see
* edu.emory.cci.aiw.umls.UMLSQueryExecutor#getSAB(edu.emory.cci.aiw.umls
* .SABQuerySearchUID)
*/
@Override
public List getSAB(SABQuerySearchUID uid) throws UMLSQueryException {
try {
setupConn();
StringBuilder sql = new StringBuilder(
"select distinct(SAB) from MRCONSO where ");
sql.append(uid.getKeyName());
sql.append(" = ?");
log(Level.FINE, sql.toString());
List params = new ArrayList();
params.add(uid);
ResultSet r = executeAndLogQuery(substParams(sql.toString(), params));
List sabs = new ArrayList();
while (r.next()) {
sabs.add(SAB.withName(r.getString(1)));
}
return sabs;
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
} finally {
tearDownConn();
}
}
private ResultSet mapToId(String phrase, IdType idType, List sabs)
throws SQLException {
StringBuilder sql = new StringBuilder("select distinct(").append(
idType.getIdType()).append("), STR from MRCONSO where STR = ?");
if (sabs != null && !sabs.isEmpty()) {
sql.append(" and ");
sql.append(singletonOrSetClause(sabs.get(0).getKeyName(),
sabs.size()));
}
log(Level.FINE, sql.toString());
List params = new ArrayList();
// capitalize the first letter of the phrase
params.add(UMLSQueryStringValue.fromString(new StringBuilder(phrase
.substring(0, 1).toUpperCase()).append(phrase.substring(1))
.toString()));
if (sabs != null) {
params.addAll(sabs);
}
return executeAndLogQuery(substParams(sql.toString(), params));
}
private Map> matches(String phrase, ResultSet rs)
throws SQLException {
Map> matches = new HashMap>();
while (rs.next()) {
String uid = rs.getString(1);
String str = rs.getString(2);
if (matches.containsKey(phrase)) {
boolean seen = false;
for (String s : matches.get(phrase)) {
if (s.equals(str)) {
seen = true;
}
}
if (!seen) {
matches.get(phrase).add(uid);
matches.get(phrase).add(str);
}
} else {
matches.put(phrase, new ArrayList());
matches.get(phrase).add(uid);
matches.get(phrase).add(str);
}
}
return matches;
}
private List permutations(String[] strings) {
List result = new ArrayList();
PermutationGenerator pg = new PermutationGenerator(strings.length);
while (pg.hasMore()) {
String[] words = new String[strings.length];
int[] indices = pg.getNext();
for (int i = 0; i < indices.length; i++) {
words[i] = strings[indices[i]];
}
result.add(words);
}
return result;
}
private List allLengthPermutations(String[] strings) {
List result = new ArrayList();
for (String[] p : permutations(strings)) {
String[] r = java.util.Arrays.copyOf(p, p.length);
while (r.length > 0) {
result.add(r);
r = java.util.Arrays.copyOf(r, r.length - 1);
}
}
return result;
}
/*
* (non-Javadoc)
*
* @see edu.emory.cci.aiw.umls.UMLSQueryExecutor#mapToAUI(java.lang.String,
* java.util.List)
*/
@Override
public Map> mapToAUI(String phrase,
List sab) throws UMLSQueryException {
Map> result = new HashMap>();
try {
setupConn();
Map> matches = matches(phrase,
mapToId(phrase, IdType.AUI_IDTYPE, sab));
if (matches.containsKey(phrase)) {
for (Map.Entry> entry : matches.entrySet()) {
result.put(
entry.getKey(),
MapToIdResult.fromUidAndStr(AtomUID
.fromString(entry.getValue().get(0)),
UMLSQueryStringValue.fromString(entry
.getValue().get(1))));
}
return result;
} else {
String[] words = phrase.split("\\s");
for (String[] p : allLengthPermutations(words)) {
if (p.length == 1 && p[0].length() < 4) {
continue;
}
String permutedString = StringUtils.join(p, ' ');
matches = (matches(permutedString,
mapToId(permutedString, IdType.AUI_IDTYPE, sab)));
for (Map.Entry> entry : matches
.entrySet()) {
result.put(entry.getKey(), MapToIdResult
.fromUidAndStr(AtomUID
.fromString(entry.getValue().get(0)),
UMLSQueryStringValue.fromString(entry
.getValue().get(1))));
}
}
return result;
}
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
} catch (MalformedUMLSUniqueIdentifierException muuie) {
throw new UMLSQueryException(muuie);
} finally {
tearDownConn();
}
}
/*
* (non-Javadoc)
*
* @see edu.emory.cci.aiw.umls.UMLSQueryExecutor#mapToCUI(java.lang.String,
* java.util.List)
*/
@Override
public Map> mapToCUI(String phrase,
List sab) throws UMLSQueryException {
Map> result = new HashMap>();
try {
setupConn();
Map> matches = matches(phrase,
mapToId(phrase, IdType.CUI_IDTYPE, sab));
if (matches.containsKey(phrase)) {
for (Map.Entry> entry : matches.entrySet()) {
result.put(entry.getKey(), MapToIdResult
.fromUidAndStr(ConceptUID
.fromString(entry.getValue().get(0)),
UMLSQueryStringValue.fromString(entry
.getValue().get(1))));
}
return result;
} else {
String[] words = phrase.split("\\s");
for (String[] p : allLengthPermutations(words)) {
if (p.length == 1 && p[0].length() < 4) {
continue;
}
String permutedString = StringUtils.join(p, ' ');
matches = (matches(permutedString,
mapToId(permutedString, IdType.CUI_IDTYPE, sab)));
for (Map.Entry> entry : matches
.entrySet()) {
result.put(entry.getKey(), MapToIdResult
.fromUidAndStr(ConceptUID
.fromString(entry.getValue().get(0)),
UMLSQueryStringValue.fromString(entry
.getValue().get(1))));
}
}
return result;
}
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
} catch (MalformedUMLSUniqueIdentifierException muuie) {
throw new UMLSQueryException(muuie);
} finally {
tearDownConn();
}
}
/*
* (non-Javadoc)
*
* @see edu.emory.cci.aiw.umls.UMLSQueryExecutor#mapToLUI(java.lang.String,
* java.util.List)
*/
@Override
public Map> mapToLUI(String phrase,
List sab) throws UMLSQueryException {
Map> result = new HashMap>();
try {
setupConn();
Map> matches = matches(phrase,
mapToId(phrase, IdType.LUI_IDTYPE, sab));
if (matches.containsKey(phrase)) {
for (Map.Entry> entry : matches.entrySet()) {
result.put(entry.getKey(), MapToIdResult
.fromUidAndStr(LexicalUID
.fromString(entry.getValue().get(0)),
UMLSQueryStringValue.fromString(entry
.getValue().get(1))));
}
return result;
} else {
String[] words = phrase.split("\\s");
for (String[] p : allLengthPermutations(words)) {
if (p.length == 1 && p[0].length() < 4) {
continue;
}
String permutedString = StringUtils.join(p, ' ');
matches = (matches(permutedString,
mapToId(permutedString, IdType.LUI_IDTYPE, sab)));
for (Map.Entry> entry : matches
.entrySet()) {
result.put(entry.getKey(), MapToIdResult
.fromUidAndStr(LexicalUID
.fromString(entry.getValue().get(0)),
UMLSQueryStringValue.fromString(entry
.getValue().get(1))));
}
}
return result;
}
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
} catch (MalformedUMLSUniqueIdentifierException muuie) {
throw new UMLSQueryException(muuie);
} finally {
tearDownConn();
}
}
/*
* (non-Javadoc)
*
* @see edu.emory.cci.aiw.umls.UMLSQueryExecutor#mapToSUI(java.lang.String,
* java.util.List)
*/
@Override
public Map> mapToSUI(String phrase,
List sab) throws UMLSQueryException {
Map> result = new HashMap>();
try {
setupConn();
Map> matches = matches(phrase,
mapToId(phrase, IdType.CUI_IDTYPE, sab));
if (matches.containsKey(phrase)) {
for (Map.Entry> entry : matches.entrySet()) {
result.put(entry.getKey(), MapToIdResult
.fromUidAndStr(StringUID
.fromString(entry.getValue().get(0)),
UMLSQueryStringValue.fromString(entry
.getValue().get(1))));
}
return result;
} else {
String[] words = phrase.split("\\s");
for (String[] p : allLengthPermutations(words)) {
if (p.length == 1 && p[0].length() < 4) {
continue;
}
String permutedString = StringUtils.join(p, ' ');
matches = (matches(permutedString,
mapToId(permutedString, IdType.CUI_IDTYPE, sab)));
for (Map.Entry> entry : matches
.entrySet()) {
result.put(entry.getKey(), MapToIdResult
.fromUidAndStr(StringUID
.fromString(entry.getValue().get(0)),
UMLSQueryStringValue.fromString(entry
.getValue().get(1))));
}
}
return result;
}
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
} catch (MalformedUMLSUniqueIdentifierException muuie) {
throw new UMLSQueryException(muuie);
} finally {
tearDownConn();
}
}
/*
* (non-Javadoc)
*
* @see
* edu.emory.cci.aiw.umls.UMLSQueryExecutor#getParents(edu.emory.cci.aiw
* .umls.ParentsQuerySearchUID, java.lang.String,
* edu.emory.cci.aiw.umls.SABValue)
*/
@Override
public Map getParents(ParentsQuerySearchUID uid, String rela,
SAB sab) throws UMLSQueryException {
Map result = new HashMap();
List params = new ArrayList();
try {
setupConn();
StringBuilder sql = new StringBuilder(
"select distinct(PTR), PAUI from MRHIER where ");
sql.append(uid.getKeyName());
sql.append(" = ?");
params.add(uid);
if (sab != null) {
sql.append(" and SAB = ?");
params.add(sab);
}
if (rela != null && !rela.equals("")) {
sql.append(" and RELA = ?");
params.add(UMLSQueryStringValue.fromString(rela));
}
log(Level.FINE, sql.toString());
ResultSet rs = executeAndLogQuery(substParams(sql.toString(),
params));
while (rs.next()) {
PTR ptr = new PTR(rs.getString(1), uid);
AtomUID aui = AtomUID.fromString(rs.getString(2));
result.put(ptr, aui);
}
return result;
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
} catch (MalformedUMLSUniqueIdentifierException muuie) {
throw new UMLSQueryException(muuie);
} finally {
tearDownConn();
}
}
/*
* (non-Javadoc)
*
* @see
* edu.emory.cci.aiw.umls.UMLSQueryExecutor#getParentsMultByAUI(java.util
* .List, java.lang.String, edu.emory.cci.aiw.umls.SABValue)
*/
@Override
public Map> getParentsMultByAUI(
List auis, String rela, SAB sab) throws UMLSQueryException {
Map> result = new HashMap>();
List params = new ArrayList();
try {
setupConn();
StringBuilder sql = new StringBuilder(
"select distinct(PTR), PAUI, ");
sql.append(auis.get(0).getKeyName());
sql.append(" from MRHIER where");
sql.append(singletonOrSetClause(auis.get(0).getKeyName(),
auis.size()));
params.addAll(auis);
if (sab != null) {
sql.append(" and SAB = ?");
params.add(sab);
}
if (rela != null && !rela.equals("")) {
sql.append(" and RELA = ?");
params.add(UMLSQueryStringValue.fromString(rela));
}
log(Level.FINE, sql.toString());
ResultSet rs = executeAndLogQuery(substParams(sql.toString(),
params));
while (rs.next()) {
AtomUID paui = AtomUID.fromString(rs.getString(2));
AtomUID byAui = AtomUID.fromString(rs.getString(3));
PTR ptr = new PTR(rs.getString(1), byAui);
result.put(byAui, new HashMap());
result.get(byAui).put(ptr, paui);
}
return result;
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
} catch (MalformedUMLSUniqueIdentifierException muuie) {
throw new UMLSQueryException(muuie);
} finally {
tearDownConn();
}
}
/*
* (non-Javadoc)
*
* @see
* edu.emory.cci.aiw.umls.UMLSQueryExecutor#getParentsMultByCUI(java.util
* .List, java.lang.String, edu.emory.cci.aiw.umls.SABValue)
*/
@Override
public Map> getParentsMultByCUI(
List cuis, String rela, SAB sab)
throws UMLSQueryException {
Map> result = new HashMap>();
List params = new ArrayList();
try {
setupConn();
StringBuilder sql = new StringBuilder(
"select distinct(PTR), PAUI, ");
sql.append(cuis.get(0).getKeyName());
sql.append(" from MRHIER where");
sql.append(singletonOrSetClause(cuis.get(0).getKeyName(),
cuis.size()));
params.addAll(cuis);
if (sab != null) {
sql.append(" and SAB = ?");
params.add(sab);
}
if (rela != null && !rela.equals("")) {
sql.append(" and RELA = ?");
params.add(UMLSQueryStringValue.fromString(rela));
}
log(Level.FINE, sql.toString());
ResultSet rs = executeAndLogQuery(substParams(sql.toString(),
params));
while (rs.next()) {
AtomUID paui = AtomUID.fromString(rs.getString(2));
ConceptUID byCui = ConceptUID.fromString(rs.getString(3));
PTR ptr = new PTR(rs.getString(1), byCui);
result.put(byCui, new HashMap());
result.get(byCui).put(ptr, paui);
}
return result;
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
} catch (MalformedUMLSUniqueIdentifierException muuie) {
throw new UMLSQueryException(muuie);
} finally {
tearDownConn();
}
}
private static class ParentListComparator implements Comparator {
/*
* (non-Javadoc)
*
* @see java.util.Comparator#compare(java.lang.Object, java.lang.Object)
*/
@Override
public int compare(PTR o1, PTR o2) {
Comparator c = new UMLSUIDComparator();
return o1.compareTo(o2);
}
}
@Override
public CommonParent getCommonParent(
T uid1, T uid2, String rela, SAB sab) throws UMLSQueryException {
Comparator c = new ParentListComparator();
List aui1Parents = new ArrayList();
aui1Parents.addAll(getParents(uid1, rela, sab).keySet());
Collections.sort(aui1Parents, c);
List aui2Parents = new ArrayList();
aui2Parents.addAll(getParents(uid2, rela, sab).keySet());
Collections.sort(aui2Parents, c);
for (PTR p : aui1Parents) {
for (PTR k : aui2Parents) {
for (int i = p.asList().size() - 1; i >= 0; i--) {
for (int j = k.asList().size() - 1; j >= 0; j--) {
if (p.asList().get(i).equals(k.asList().get(j))) {
return new CommonParent(p.asList().get(i), uid1,
uid2, p.asList().size() - i - 1, k.asList()
.size() - j - 1);
}
}
}
}
}
return null;
}
/*
* (non-Javadoc)
*
* @see
* edu.emory.cci.aiw.umls.UMLSQueryExecutor#getChildren(edu.emory.cci.aiw
* .umls.ConceptUID, java.lang.String, edu.emory.cci.aiw.umls.SABValue)
*/
@Override
public List getChildren(ConceptUID cui, String rela, SAB sab)
throws UMLSQueryException {
List params = new ArrayList();
try {
setupConn();
StringBuilder sql = new StringBuilder(
"select distinct(m2.CUI) from MRHIER, MRCONSO as m1, MRCONSO as m2 where MRHIER.PAUI = m1.AUI and m1.CUI = ?");
params.add(cui);
sql.append(" and MRHIER.AUI = m2.AUI");
if (sab != null) {
sql.append(" and MRHIER.SAB = ?");
params.add(sab);
}
if (rela != null && !rela.equals("")) {
sql.append(" and MRHIER.RELA = ?");
params.add(UMLSQueryStringValue.fromString(rela));
}
List children = new ArrayList();
ResultSet rs = executeAndLogQuery(substParams(sql.toString(),
params));
while (rs.next()) {
children.add(ConceptUID.fromString(rs.getString(1)));
}
return children;
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
} catch (MalformedUMLSUniqueIdentifierException muuie) {
throw new UMLSQueryException(muuie);
} finally {
tearDownConn();
}
}
/*
* (non-Javadoc)
*
* @see
* edu.emory.cci.aiw.umls.UMLSQueryExecutor#getChilrdren(edu.emory.cci.aiw
* .umls.AtomUID, java.lang.String, edu.emory.cci.aiw.umls.SABValue)
*/
@Override
public List getChildren(AtomUID aui, String rela, SAB sab)
throws UMLSQueryException {
List params = new ArrayList();
try {
setupConn();
StringBuilder sql = new StringBuilder(
"select distinct(AUI) from MRHIER where PAUI = ?");
params.add(aui);
if (sab != null) {
sql.append(" and SAB = ?");
params.add(sab);
}
if (rela != null) {
sql.append(" and RELA = ?");
params.add(UMLSQueryStringValue.fromString(rela));
}
List children = new ArrayList();
ResultSet rs = executeAndLogQuery(substParams(sql.toString(),
params));
while (rs.next()) {
children.add(AtomUID.fromString(rs.getString(1)));
}
return children;
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
} catch (MalformedUMLSUniqueIdentifierException muuie) {
throw new UMLSQueryException(muuie);
} finally {
tearDownConn();
}
}
/*
* (non-Javadoc)
*
* @see
* edu.emory.cci.aiw.umls.UMLSQueryExecutor#getCommonChild(edu.emory.cci
* .aiw.umls.AtomUID, edu.emory.cci.aiw.umls.AtomUID, java.lang.String,
* edu.emory.cci.aiw.umls.SABValue)
*/
@Override
public AtomUID getCommonChild(AtomUID aui1, AtomUID aui2, String rela,
SAB sab) throws UMLSQueryException {
List c1 = getChildren(aui1, rela, sab);
List c2 = getChildren(aui2, rela, sab);
Comparator cmp = new UMLSUIDComparator();
Collections.sort(c1, cmp);
Collections.sort(c2, cmp);
for (AtomUID a : c1) {
for (AtomUID b : c2) {
if (a.equals(b)) {
return a;
}
}
}
return null;
}
/*
* (non-Javadoc)
*
* @see
* edu.emory.cci.aiw.umls.UMLSQueryExecutor#getCommonChild(edu.emory.cci
* .aiw.umls.ConceptUID, edu.emory.cci.aiw.umls.ConceptUID,
* java.lang.String, edu.emory.cci.aiw.umls.SABValue)
*/
@Override
public ConceptUID getCommonChild(ConceptUID cui1, ConceptUID cui2,
String rela, SAB sab) throws UMLSQueryException {
List c1 = getChildren(cui1, rela, sab);
List c2 = getChildren(cui2, rela, sab);
Comparator cmp = new UMLSUIDComparator();
Collections.sort(c1, cmp);
Collections.sort(c2, cmp);
for (ConceptUID a : c1) {
for (ConceptUID b : c2) {
if (a.equals(b)) {
return a;
}
}
}
return null;
}
/*
* (non-Javadoc)
*
* @see
* edu.emory.cci.aiw.umls.UMLSQueryExecutor#getAvailableSAB(java.lang.String
* )
*/
@Override
public Set getAvailableSAB(String description)
throws UMLSQueryException {
try {
setupConn();
StringBuilder sql = new StringBuilder("select RSAB, SON from MRSAB");
if (description != null) {
sql.append(" where UPPER(SON) like UPPER(?)");
}
PreparedStatement query = conn.prepareStatement(sql.toString());
if (description != null) {
query.setString(1, "%" + description + "%");
}
ResultSet rs = executeAndLogQuery(query);
Set result = new HashSet();
while (rs.next()) {
SAB sab = SAB.withNameAndDescription(rs.getString(1),
rs.getString(2));
result.add(sab);
}
return result;
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
} finally {
tearDownConn();
}
}
/*
* (non-Javadoc)
*
* @see
* edu.emory.cci.aiw.umls.UMLSQueryExecutor#getDistBF(edu.emory.cci.aiw.
* umls.ConceptUID, edu.emory.cci.aiw.umls.ConceptUID, java.lang.String,
* edu.emory.cci.aiw.umls.SABValue, int)
*/
@Override
public int getDistBF(ConceptUID cui1, ConceptUID cui2, String rela,
SAB sab, int maxR) throws UMLSQueryException {
Queue cuiQue = new LinkedList();
Set visited = new HashSet();
Map radiusIdx = new HashMap();
int queIdx = 0;
int r = 0;
radiusIdx.put(r, 0);
if (maxR <= 0) {
maxR = 3;
}
try {
setupConn();
cuiQue.add(cui1);
visited.add(cui1);
List params = new ArrayList();
StringBuilder sql = new StringBuilder(
"select distinct(CUI2) from MRREL where CUI1 = ? and (rel='PAR' or rel='CHD')");
params.add(ConceptUID.EMPTY_CUI);
if (sab != null) {
sql.append(" and SAB = ?");
params.add(sab);
}
if (rela != null && !rela.equals("")) {
sql.append(" and RELA = ?");
params.add(UMLSQueryStringValue.fromString(rela));
}
while (!cuiQue.isEmpty()) {
ConceptUID node = cuiQue.remove();
params.set(0, node);
if (node.equals(cui2)) {
return r;
}
List adjNodes = new ArrayList();
ResultSet rs = executeAndLogQuery(substParams(sql.toString(),
params));
while (rs.next()) {
ConceptUID c2 = ConceptUID.fromString(rs.getString(1));
if (!visited.contains(c2)) {
adjNodes.add(c2);
}
}
if (!radiusIdx.containsKey(r + 1)) {
radiusIdx.put(r + 1, queIdx + cuiQue.size());
}
radiusIdx.put(r + 1, adjNodes.size());
if (queIdx == radiusIdx.get(r)) {
r++;
}
queIdx++;
for (ConceptUID c : adjNodes) {
visited.add(c);
cuiQue.add(c);
}
if (r > maxR) {
return r;
}
}
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
} catch (MalformedUMLSUniqueIdentifierException muuie) {
throw new UMLSQueryException(muuie);
} finally {
tearDownConn();
}
log(Level.FINEST, "Returning -1");
return -1;
}
/*
* (non-Javadoc)
*
* @see
* edu.emory.cci.aiw.umls.UMLSQueryExecutor#getNeighbors(edu.emory.cci.aiw
* .umls.NeighborQuerySearchUID, java.lang.String,
* edu.emory.cci.aiw.umls.SABValue, java.lang.String)
*/
@Override
public List getNeighbors(NeighborQuerySearchUID ui,
String rela, SAB sab, String rel) throws UMLSQueryException {
List params = new ArrayList();
try {
setupConn();
StringBuilder sql = new StringBuilder(
"select distinct(CUI2) from MRREL where "
+ ui.getKeyName() + " = ?");
params.add(ui);
if (sab != null) {
sql.append(" and SAB = ?");
params.add(sab);
}
if (rela != null && !rela.equals("")) {
sql.append(" and RELA = ?");
params.add(UMLSQueryStringValue.fromString(rela));
}
if (rel != null && !rel.equals("")) {
sql.append(" and REL = ?");
params.add(UMLSQueryStringValue.fromString(rel));
}
ResultSet rs = executeAndLogQuery(substParams(sql.toString(),
params));
List result = new ArrayList();
while (rs.next()) {
ConceptUID c2 = ConceptUID.fromString(rs.getString(1));
if (!c2.equals(ui)) {
result.add(c2);
}
}
return result;
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
} catch (MalformedUMLSUniqueIdentifierException muuie) {
throw new UMLSQueryException(muuie);
} finally {
tearDownConn();
}
}
/*
* (non-Javadoc)
*
* @see
* edu.emory.cci.aiw.umls.UMLSQueryExecutor#codeToUID(edu.emory.cci.aiw.
* umls.TerminologyCode)
*/
@Override
public ConceptUID codeToUID(TerminologyCode code) throws UMLSQueryException {
if (code == null || code == null || code.getCode().equals("")
|| code.getSab() == null) {
throw new UMLSQueryException("The code and SAB must not be null");
}
try {
setupConn();
StringBuilder sql = new StringBuilder(
"select distinct(CUI) from MRCONSO where CODE = ? and SAB = ?");
List params = new ArrayList();
params.add(UMLSQueryStringValue.fromString(code.getCode()));
params.add(code.getSab());
ResultSet rs = executeAndLogQuery(substParams(sql.toString(),
params));
if (rs.next()) {
return ConceptUID.fromString(rs.getString(1));
} else {
return null;
}
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
} finally {
tearDownConn();
}
}
/*
* (non-Javadoc)
*
* @see
* edu.emory.cci.aiw.umls.UMLSQueryExecutor#uidToCode(edu.emory.cci.aiw.
* umls.ConceptUID, edu.emory.cci.aiw.umls.SAB)
*/
@Override
public List uidToCode(CodeQuerySearchUID uid, SAB sab)
throws UMLSQueryException {
if (uid == null || sab == null) {
throw new UMLSQueryException("The UID and SAB must be non-null");
}
try {
setupConn();
StringBuilder sql = new StringBuilder(
"select distinct(CODE) from MRCONSO where ");
sql.append(uid.getKeyName());
sql.append(" = ? and SAB = ?");
List params = new ArrayList();
params.add(uid);
params.add(sab);
ResultSet rs = executeAndLogQuery(substParams(sql.toString(),
params));
List result = new ArrayList();
while (rs.next()) {
result.add(TerminologyCode.fromStringAndSAB(rs.getString(1),
sab));
}
return result;
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
} finally {
tearDownConn();
}
}
/*
* (non-Javadoc)
*
* @see
* edu.emory.cci.aiw.umls.UMLSQueryExecutor#translateCode(edu.emory.cci.
* aiw.umls.TerminologyCode, edu.emory.cci.aiw.umls.SAB)
*/
@Override
public List translateCode(TerminologyCode from, SAB to)
throws UMLSQueryException {
if (from == null || from.getCode() == null || from.getCode().equals("")
|| from.getSab() == null || to == null) {
throw new UMLSQueryException("Code and SAB must not be null");
}
try {
setupConn();
StringBuilder sql = new StringBuilder(
"select b.CODE from MRCONSO a, MRCONSO b ");
sql.append("where a.CODE = ? and a.SAB = ? and b.SAB = ? and a.CUI = b.CUI");
List params = new ArrayList();
params.add(queryStr(from.getCode()));
params.add(from.getSab());
params.add(to);
ResultSet rs = executeAndLogQuery(substParams(sql.toString(),
params));
List result = new ArrayList();
while (rs.next()) {
result.add(TerminologyCode.fromStringAndSAB(rs.getString(1), to));
}
return result;
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
} finally {
tearDownConn();
}
}
@Override
public List getChildrenByCode(TerminologyCode code)
throws UMLSQueryException {
validateCode(code);
setupConn();
List childCodes = new ArrayList();
List childCuis = getChildren(codeToUID(code), "",
code.getSab());
for (ConceptUID cui : childCuis) {
childCodes.addAll(uidToCode(cui, code.getSab()));
}
tearDownConn();
return childCodes;
}
@Override
public List getParentsByCode(TerminologyCode code)
throws UMLSQueryException {
validateCode(code);
setupConn();
List parentCodes = new ArrayList();
Map parentAuis = getParents(codeToUID(code), "",
code.getSab());
for (AtomUID aui : parentAuis.values()) {
for (ConceptUID cui : getCUI(aui,
Collections.singletonList(code.getSab()), false)) {
parentCodes.addAll(uidToCode(cui, code.getSab()));
}
}
tearDownConn();
return parentCodes;
}
/*
* (non-Javadoc)
*
* @see
* edu.emory.cci.aiw.umls.UMLSQueryExecutor#getPreferredName(edu.emory.cci
* .aiw.umls.TerminologyCode)
*/
@Override
public String getPreferredName(TerminologyCode code)
throws UMLSQueryException {
try {
validateCode(code);
setupConn();
String result = "";
String sql = new String(
"select MRCONSO.STR from MRRANK, MRCONSO where "
+ "MRRANK.TTY = MRCONSO.TTY and MRRANK.SAB = MRCONSO.SAB and "
+ "MRCONSO.CODE = ? and MRCONSO.SAB = ? having max(MRRANK.RANK)");
List params = new ArrayList();
params.add(queryStr(code.getCode()));
params.add(code.getSab());
ResultSet rs = executeAndLogQuery(substParams(sql, params));
if (rs.next()) {
result = rs.getString(1);
}
return result;
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
} finally {
tearDownConn();
}
}
/*
* (non-Javadoc)
*
* @see
* edu.emory.cci.aiw.umls.UMLSQueryExecutor#getTermDefinition(edu.emory.
* cci.aiw.umls.TerminologyCode)
*/
@Override
public String getTermDefinition(TerminologyCode code)
throws UMLSQueryException {
try {
validateCode(code);
setupConn();
String result = "";
String sql = "select distinct(MRDEF.DEF) from MRDEF, MRCONSO where "
+ "MRDEF.CUI = MRCONSO.CUI and MRDEF.SAB = MRCONSO.SAB and "
+ "MRCONSO.SAB = ? and MRCONSO.CODE = ?";
List params = new ArrayList();
params.add(code.getSab());
params.add(queryStr(code.getCode()));
ResultSet rs = executeAndLogQuery(substParams(sql, params));
if (rs.next()) {
result = rs.getString(1);
}
return result;
} catch (SQLException sqle) {
throw new UMLSQueryException(sqle);
} finally {
tearDownConn();
}
}
/*
* (non-Javadoc)
*
* @see
* edu.emory.cci.aiw.umls.UMLSQueryExecutor#getTermSubsumption(edu.emory
* .cci.aiw.umls.TerminologyCode)
*/
public List getTermSubsumption(TerminologyCode code)
throws UMLSQueryException, UMLSNoSuchTermException {
validateCode(code);
if (!codeExists(code)) {
throw new UMLSNoSuchTermException("No such terminology code: "
+ code);
}
List result = new ArrayList();
// stores the unexpanded children
Queue descendants = new LinkedList();
result.add(code);
descendants.addAll(getChildrenByCode(code));
// loop through all children until the queue is empty, like BFS/DFS
while (!descendants.isEmpty()) {
// dequeue from the descendants and set as current term
TerminologyCode current = descendants.remove();
// add the current child under examination to the result set
result.add(current);
// get all of the current term's children and them to the queue
List curChildren = getChildrenByCode(current);
if (!curChildren.isEmpty()) {
descendants.addAll(curChildren);
}
}
return result;
}
private void validateCode(TerminologyCode code) throws UMLSQueryException {
if (code == null || code.getCode().equals("") || code.getSab() == null) {
throw new UMLSQueryException("Code and SAB must not be null");
}
}
private boolean codeExists(TerminologyCode code) throws UMLSQueryException {
return codeToUID(code) != null;
}
private UMLSQueryStringValue queryStr(String str) {
return UMLSQueryStringValue.fromString(str);
}
private String singletonOrSetClause(String uidKeyName, int setSize) {
if (setSize > 1) {
StringBuilder clause = new StringBuilder(uidKeyName + " in (");
for (int i = 0; i < setSize - 1; i++) {
clause.append("?, ");
}
clause.append("?)");
return clause.toString();
} else {
return uidKeyName + " = ?";
}
}
private PreparedStatement substParams(String sql,
List params) throws SQLException {
PreparedStatement query = conn.prepareStatement(sql);
for (int i = 0; i < params.size(); i++) {
query.setString(1 + i, params.get(i).getValue());
}
return query;
}
private ResultSet executeAndLogQuery(PreparedStatement query)
throws SQLException {
log(Level.FINE, "Executing query: " + query);
return query.executeQuery();
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy