[phylr] r52 commited - Move SQL statements from code to prop file
codesite...@google.com
Author: dazhi.jiao
Date: Wed Jul 29 13:28:23 2009
Log: Move SQL statements from code to prop file
http://code.google.com/p/phylr/source/detail?r=52
Modified:
/trunk/phylr-gsoc/src/main/java/org/nescent/phylr/relational/BioSQLQueryTranslator.java
/trunk/phylr-gsoc/src/main/resources/BioSQL.props
=======================================
---
/trunk/phylr-gsoc/src/main/java/org/nescent/phylr/relational/BioSQLQueryTranslator.java
Wed Jul 29 07:01:00 2009
+++
/trunk/phylr-gsoc/src/main/java/org/nescent/phylr/relational/BioSQLQueryTranslator.java
Wed Jul 29 13:28:23 2009
@@ -1,6 +1,10 @@
package org.nescent.phylr.relational;
+import java.util.Enumeration;
+import java.util.HashMap;
+import java.util.Map;
import java.util.Properties;
+import java.util.StringTokenizer;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
@@ -17,8 +21,9 @@
public class BioSQLQueryTranslator implements CqlQueryTranslator {
private static Log log= LogFactory.getLog(BioSQLQueryTranslator.class);
SRWRelationalDatabase database = null;
-
- private String outSql = "select tree_id, xmlagg(xvalue) from ( " +
+ Map<String, String> indexMapping = null;
+
+ private String outTreeSql = "select tree_id, xmlagg(xvalue) from ( " +
"SELECT tree_id, xmlforest(tq.value as \"dc:identifier\") as
xvalue " +
"FROM tree_qualifier_value tq, term te " +
"WHERE tq.term_id = te.term_id and te.name='dc.identifier' " +
@@ -37,30 +42,31 @@
") as tab " +
"WHERE tree_id in (?) " +
"GROUP BY tree_id";
-
- private String ftSql = "select tq.tree_id from tree_qualifier_value as
tq, term as te " +
- "where to_tsvector('english', value) @@ to_tsquery('english', ?) " +
- "and tq.term_id = te.term_id and te.name=?";
-
- private String eqSql = "select tq.tree_id from tree_qualifier_value as
tq, term as te " +
- "where value=? " +
- "and tq.term_id = te.term_id and te.name=?";
-
-// Not legal until JDK 6 @Override
+ private String outNodeSql = "";
+
public void init(Properties properties, SRWRelationalDatabase ldb)
throws InstantiationException {
this.database = ldb;
+ this.indexMapping = new HashMap<String, String>();
+
+ Enumeration enumer=properties.propertyNames();
+ while(enumer.hasMoreElements()) {
+ String prop=(String)enumer.nextElement();
+ if(prop.startsWith("qualifier.")) {
+ indexMapping.put(prop.substring(10),
properties.getProperty(prop));
+ }
+ }
+
+ this.outTreeSql =
properties.getProperty("SRWRelationalDatabase.query.tree");
+ this.outNodeSql =
properties.getProperty("SRWRelationalDatabase.query.node");
}
-// Not legal until JDK 6 @Override
public String makeQuery(CQLNode node, SortTool sortTool) throws
SRWDiagnostic {
- String sql = outSql;
StringBuffer sb =new StringBuffer();
makeSQLQuery(node, sb);
- sql = sql.replaceFirst("\\?", sb.toString());
- return sql;
+ return outTreeSql.replaceFirst("\\?", sb.toString());
}
- public void makeSQLQuery(CQLNode node, StringBuffer sb) {
+ public void makeSQLQuery(CQLNode node, StringBuffer sb) throws
SRWDiagnostic {
if(node instanceof CQLBooleanNode) {
CQLBooleanNode cbn=(CQLBooleanNode)node;
makeSQLQuery(cbn.left, sb);
@@ -72,32 +78,31 @@
sb.append(" UNION ");
else sb.append(" UnknownBoolean("+cbn+") ");
makeSQLQuery(cbn.right, sb);
- }
- else if(node instanceof CQLTermNode) {
+ } else if(node instanceof CQLTermNode) {
String sql = null;
CQLTermNode ctn=(CQLTermNode)node;
String index=ctn.getIndex();
- if(index.equals(""))
- index = "dc.title"; // set default field to dc:title
+ if(index.equals("") || index.equals("cql.serverChoice")) {
+ index = "dc.title"; // set default field to dc:title
+ }
String term = ctn.getTerm();
-
+ if (!indexMapping.containsKey(index)) {
+ throw new SRWDiagnostic(SRWDiagnostic.UnsupportedIndex,
index);
+ }
+ sql = indexMapping.get(index);
if(ctn.getRelation().getBase().equals("=") ||
ctn.getRelation().getBase().equals("scr")) {
- sql = eqSql.replaceFirst("\\?", "'" + term + "'");
+ sql = sql.replaceFirst("\\?", "'" + term + "'");
+ } else if(ctn.getRelation().getBase().equals("any")) {
+ sql = sql.replaceFirst("\\?", "'" +
StringUtils.join(StringUtils.split(term), " | ") + "'");
+ } else if(ctn.getRelation().getBase().equals("all")) {
+ sql = sql.replaceFirst("\\?", "'" +
StringUtils.join(StringUtils.split(term), " & ") + "'");
+ } else if (ctn.getRelation().getBase().equals("exact")) {
+ sql = sql.replaceFirst("\\?", "'" + term + "'");
} else {
- sql = ftSql;
- if(ctn.getRelation().getBase().equals("any")) {
- sql = sql.replaceFirst("\\?", "'" +
StringUtils.join(StringUtils.split(term), " | ") + "'");
- }
- else if(ctn.getRelation().getBase().equals("all")) {
- sql = sql.replaceFirst("\\?", "'" +
StringUtils.join(StringUtils.split(term), " & ") + "'");
- } else if (ctn.getRelation().getBase().equals("exact")) {
- sql = sql.replaceFirst("\\?", "'" + term + "'");
- } else
- sql = "Unsupported
Relation: "+ctn.getRelation().getBase();
- }
- sql = sql.replaceFirst("\\?", "'" + index + "'");
+ sql = "Unsupported Relation: "+ctn.getRelation().getBase();
+ }
sb.append(sql);
}
else sb.append("UnknownCQLNode("+node+")");
=======================================
--- /trunk/phylr-gsoc/src/main/resources/BioSQL.props Wed Jul 29 07:51:42
2009
+++ /trunk/phylr-gsoc/src/main/resources/BioSQL.props Wed Jul 29 13:28:23
2009
@@ -1,16 +1,20 @@
databaseInfo.title=Phylr
-databaseInfo.description=Phylr contains a copy of the BioSQL data.
-databaseInfo.author=Phylr subgroup from the Nescent Database
Interoperability Hackathon
-databaseInfo.contact=rsch...@nescent.org
+databaseInfo.description=Phylr Service that contains a BioSQL database,
migrated from TreeBase
+databaseInfo.author=Google Summer of Coder
+databaseInfo.contact=dazhi...@gmail.com
configInfo.maximumRecords=50
-# Mapping between the CQL indexes (query fields) and searchable fields
-# within the Lucene index.
-qualifier.dc.identifier=dc.identifier
-qualifier.dc.contributor=dc.contributor
-qualifier.dc.title=dc.title
-qualifier.dc.abstract=dc.abstract
+SRWRelationalDatabase.query.tree = select tree_id, xmlagg(xvalue) from (
SELECT tree_id, xmlforest(tq.value as "dc:identifier") as xvalue FROM
tree_qualifier_value tq, term te WHERE tq.term_id = te.term_id and
te.name='dc.identifier' UNION ALL SELECT tq.tree_id, xmlforest(tq.value
as "dc:title") as xvalue FROM tree_qualifier_value tq, term te WHERE
tq.term_id = te.term_id and te.name='dc.title' UNION ALL SELECT tq.tree_id,
xmlforest(tq.value as "dc:abstract") as xvalue FROM tree_qualifier_value
tq, term te WHERE tq.term_id = te.term_id and te.name='dc.abstract' UNION
ALL SELECT tq.tree_id, xmlforest(tq.value as "dc:contributor") as xvalue
FROM tree_qualifier_value tq, term te WHERE tq.term_id = te.term_id and
te.name='dc.contributor' ) as tab WHERE tree_id in (?) GROUP BY tree_id
+SRWRelationalDatabase.query.node =
+
+SRWRelationalDatabase.query.tree.indexes = dc.identifier dc.contributor
dc.title dc.abstract
+
+# Mapping between the CQL indexes (query fields) and SQL queries for
tree_id
+qualifier.dc.identifier=select tq.tree_id from tree_qualifier_value as tq,
term as te where to_tsvector('english', value) @@ to_tsquery('english', ?)
and tq.term_id = te.term_id and te.name='dc.identifier'
+qualifier.dc.contributor=select tq.tree_id from tree_qualifier_value as
tq, term as te where to_tsvector('english', value) @@
to_tsquery('english', ?) and tq.term_id = te.term_id and
te.name='dc.contributor'
+qualifier.dc.title=select tq.tree_id from tree_qualifier_value as tq, term
as te where to_tsvector('english', value) @@ to_tsquery('english', ?) and
tq.term_id = te.term_id and te.name='dc.title'
+qualifier.dc.abstract=select tq.tree_id from tree_qualifier_value as tq,
term as te where to_tsvector('english', value) @@ to_tsquery('english', ?)
and tq.term_id = te.term_id and te.name='dc.abstract'
# Database Connection Settings
SRWRelationalDatabase.driver=org.postgresql.Driver