[phylr] r53 commited - More indexes

[codesite...@google.com]

Revision: 53
Author: dazhi.jiao
Date: Wed Jul 29 13:48:57 2009
Log: More indexes
http://code.google.com/p/phylr/source/detail?r=53

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/sql/TreeBaseBioSQLMig.sql

=======================================
---
/trunk/phylr-gsoc/src/main/java/org/nescent/phylr/relational/BioSQLQueryTranslator.java
Wed Jul 29 13:28:23 2009
+++
/trunk/phylr-gsoc/src/main/java/org/nescent/phylr/relational/BioSQLQueryTranslator.java
Wed Jul 29 13:48:57 2009
@@ -93,13 +93,13 @@
sql = indexMapping.get(index);
if(ctn.getRelation().getBase().equals("=") ||
ctn.getRelation().getBase().equals("scr")) {
- sql = sql.replaceFirst("\\?", "'" + term + "'");
+ sql = sql.replaceFirst("\\?", term);
} else if(ctn.getRelation().getBase().equals("any")) {
- sql = sql.replaceFirst("\\?", "'" +
StringUtils.join(StringUtils.split(term), " | ") + "'");
+ sql = sql.replaceFirst("\\?",
StringUtils.join(StringUtils.split(term), " | ") );
} else if(ctn.getRelation().getBase().equals("all")) {
- sql = sql.replaceFirst("\\?", "'" +
StringUtils.join(StringUtils.split(term), " & ") + "'");
+ sql = sql.replaceFirst("\\?",
StringUtils.join(StringUtils.split(term), " & "));
} else if (ctn.getRelation().getBase().equals("exact")) {
- sql = sql.replaceFirst("\\?", "'" + term + "'");
+ sql = sql.replaceFirst("\\?", term);
} else {
sql = "Unsupported Relation: "+ctn.getRelation().getBase();
}
=======================================
--- /trunk/phylr-gsoc/src/main/resources/BioSQL.props Wed Jul 29 13:28:23
2009
+++ /trunk/phylr-gsoc/src/main/resources/BioSQL.props Wed Jul 29 13:48:57
2009
@@ -11,10 +11,12 @@
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'
+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'
+qualifier.tree.identifier=select tree_id from tree where
to_tsvector('english', identifier) @@ to_tsquery('english', '?')
+qualifier.tree.name=select tree_id from tree where to_tsvector('english',
name) @@ to_tsquery('english', '?')

# Database Connection Settings
SRWRelationalDatabase.driver=org.postgresql.Driver
=======================================
--- /trunk/phylr-gsoc/src/main/sql/TreeBaseBioSQLMig.sql Wed Jul 29
07:01:00 2009
+++ /trunk/phylr-gsoc/src/main/sql/TreeBaseBioSQLMig.sql Wed Jul 29
13:48:57 2009
@@ -119,6 +119,8 @@

-- Create full text index
CREATE INDEX ft_tree_qualifier_value_idx ON tree_qualifier_value USING
gin(to_tsvector('english', value))
+CREATE INDEX ft_tree_name_idx ON tree USING gin(to_tsvector('english',
name))
+CREATE INDEX ft_tree_identifier_idx ON tree USING
gin(to_tsvector('english', identifier))

CREATE INDEX term_name_idx ON term USING btree (name);