ca.carleton.gcrc.search.Searches Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of nunaliit2-search Show documentation
Show all versions of nunaliit2-search Show documentation
Servlet API to perform predefined searches in a database.
/*
Copyright (c) 2010, Geomatics and Cartographic Research Centre, Carleton
University
All rights reserved.
Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions are met:
- Redistributions of source code must retain the above copyright notice,
this list of conditions and the following disclaimer.
- Redistributions in binary form must reproduce the above copyright notice,
this list of conditions and the following disclaimer in the documentation
and/or other materials provided with the distribution.
- Neither the name of the Geomatics and Cartographic Research Centre,
Carleton University nor the names of its contributors may be used to
endorse or promote products derived from this software without specific
prior written permission.
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE
LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
POSSIBILITY OF SUCH DAMAGE.
$Id$
*/
package ca.carleton.gcrc.search;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Properties;
import java.util.Vector;
import java.util.HashMap;
import java.util.Map;
import javax.servlet.ServletException;
import org.json.JSONArray;
import org.json.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public class Searches {
final protected Logger logger = LoggerFactory.getLogger(this.getClass());
static final public String PROPERTIES_KEY_CONTRIBUTIONS_TABLE_NAME = "contributions.tableName";
static final public String PROPERTIES_KEY_CONTRIBUTIONS_ID_COLUMN_NAME = "contributions.idColumnName";
static final public String PROPERTIES_KEY_CONTRIBUTIONS_SELECT_FIELDS = "contributions.selectFields";
static final public String PROPERTIES_KEY_CONTRIBUTIONS_SELECT_TYPES = "contributions.selectTypes";
static final public String PROPERTIES_KEY_CONTRIBUTIONS_SEARCH_FIELDS = "contributions.searchFields";
static final public String PROPERTIES_KEY_FEATURES_TABLE_NAME = "features.tableName";
static final public String PROPERTIES_KEY_FEATURES_ID_COLUMN_NAME = "features.idColumnName";
static final public String PROPERTIES_KEY_FEATURES_SELECT_FIELDS = "features.selectFields";
static final public String PROPERTIES_KEY_FEATURES_SELECT_TYPES = "features.selectTypes";
static final public String PROPERTIES_KEY_FEATURES_SEARCH_FIELDS = "features.searchFields";
static final public String DEFAULT_CONTRIBUTIONS_TABLE_NAME = "contributions";
static final public String DEFAULT_CONTRIBUTIONS_ID_COLUMN_NAME = "id";
static final public String DEFAULT_KEY_CONTRIBUTIONS_SELECT_FIELDS = "filename,mimetype,related_to";
static final public String DEFAULT_KEY_CONTRIBUTIONS_SELECT_TYPES = "string,string,integer";
static final public String DEFAULT_KEY_CONTRIBUTIONS_SEARCH_FIELDS = "title,notes";
static final public String DEFAULT_FEATURES_TABLE_NAME = "names";
static final public String DEFAULT_FEATURES_ID_COLUMN_NAME = "id";
static final public String DEFAULT_KEY_FEATURES_SELECT_FIELDS = "";
static final public String DEFAULT_KEY_FEATURES_SELECT_TYPES = "";
static final public String DEFAULT_KEY_FEATURES_SEARCH_FIELDS = "placename,syllabics,meaning,alt_name,moreinfo,questions,entity,source";
private Connection connection;
static private SimpleDateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd");
private String contributionsTableName;
private String contributionsIdColumnName;
private List contributionsSelectFields = new Vector();
private List contributionsSelectTypes = new Vector();
private List contributionsSearchFields = new Vector();
private String featuresTableName;
private String featuresIdColumnName;
private List featuresSelectFields = new Vector();
private List featuresSelectTypes = new Vector();
private List featuresSearchFields = new Vector();
public Searches(Properties props, Connection connection) {
this.connection = connection;
readProperties(props);
}
private void readProperties(Properties props) {
contributionsTableName = props.getProperty(PROPERTIES_KEY_CONTRIBUTIONS_TABLE_NAME, DEFAULT_CONTRIBUTIONS_TABLE_NAME);
contributionsIdColumnName = props.getProperty(PROPERTIES_KEY_CONTRIBUTIONS_ID_COLUMN_NAME, DEFAULT_CONTRIBUTIONS_ID_COLUMN_NAME);
featuresTableName = props.getProperty(PROPERTIES_KEY_FEATURES_TABLE_NAME, DEFAULT_FEATURES_TABLE_NAME);
featuresIdColumnName = props.getProperty(PROPERTIES_KEY_CONTRIBUTIONS_ID_COLUMN_NAME, DEFAULT_CONTRIBUTIONS_ID_COLUMN_NAME);
// Load search and select fields
{
String selectFieldsParams = props.getProperty(PROPERTIES_KEY_CONTRIBUTIONS_SELECT_FIELDS, DEFAULT_KEY_CONTRIBUTIONS_SELECT_FIELDS);
String[] selectFieldArray = selectFieldsParams.split(",");
for(int loop=0; loop searchFields,
String returnTag,
List selectFields,
List selectTypes,
boolean includeContribInfo_hack) throws Exception {
List searchFieldsList = new Vector();
List selectFieldsList = new Vector();
List scoreFieldsList = new Vector();
searchFieldsList.add( new SelectedColumn(SelectedColumn.Type.INTEGER, idField) );
searchFieldsList.add( new SelectedColumn(SelectedColumn.Type.INTEGER, "place_id") );
if (includeContribInfo_hack) {
searchFieldsList.add( new SelectedColumn(SelectedColumn.Type.INTEGER, "contributor_id"));
}
for(String searchField : searchFields) {
searchFieldsList.add( new SelectedColumn(SelectedColumn.Type.STRING, searchField) );
}
if (null != selectFields) {
for (int i=0; i 0 ) {
if( first ) {
first = false;
} else {
pw.print(",");
}
String scoreString = computeSelectScore(searchFields);
pw.print(scoreString);
pw.print(" AS score");
scoreFieldsList.add( new SelectedColumn(SelectedColumn.Type.INTEGER, "score") );
}
pw.print(" FROM ");
pw.print(tableName);
if( searchFields.size() > 0 ) {
String whereString = computeWhereFragment(searchFields);
pw.print(whereString);
}
if( searchFields.size() > 0 ) {
String orderString = computeOrderFragment(searchFields);
pw.print(orderString);
}
pw.print(";");
pw.flush();
sqlStatement = sw.toString();
}
logger.info("Search SQL for tag (" + returnTag + "): " + sqlStatement);
PreparedStatement stmt = connection.prepareStatement(sqlStatement);
int index = 1;
for(int loop=0; loop searchFields) throws Exception {
StringWriter sw = new StringWriter();
PrintWriter pw = new PrintWriter(sw);
if( 0 == searchFields.size() ) {
throw new Exception("Must supply at least one search field");
} else if( 1 == searchFields.size() ) {
pw.print("coalesce(nullif(position(lower(?) IN lower(");
pw.print(searchFields.get(0));
pw.print(")), 0), 9999)");
} else {
int loop;
for(loop=0; loop searchFields) throws Exception {
StringWriter sw = new StringWriter();
PrintWriter pw = new PrintWriter(sw);
boolean first = true;
for(int loop=0; loop searchFields) throws Exception {
if( 0 == searchFields.size() ) {
return "";
}
return " ORDER BY " + computeSelectScore(searchFields);
}
/**
* This method executes a prepared SQL statement and returns a JSON
* array that contains the result.
* @param stmt Prepared SQL statement to execute
* @param selectFields Column to return in results
* @return A JSONArray containing the requested information
* @throws Exception
*/
private JSONArray executeStatementToJson(
PreparedStatement stmt,
List selectFields) throws Exception {
//logger.info("about to execute: " + stmt.toString());
if( stmt.execute() ) {
// There's a ResultSet to be had
ResultSet rs = stmt.getResultSet();
JSONArray array = new JSONArray();
try {
Map contributorMap = new HashMap();
while (rs.next()) {
JSONObject obj = new JSONObject();
int index = 1;
for(int loop=0; loop selectFields = new Vector();
selectFields.add( new SelectedColumn(SelectedColumn.Type.INTEGER, "place_id") );
selectFields.add( new SelectedColumn(SelectedColumn.Type.STRING, "hover_audio") );
PreparedStatement stmt = connection.prepareStatement("SELECT place_id,hover_audio FROM " + featuresTable + " WHERE place_id = ?;");
stmt.setInt(1, Integer.parseInt(place_id));
JSONArray array = executeStatementToJson(stmt, selectFields);
JSONObject result = new JSONObject();
result.put("media", array);
return result;
}
public JSONObject searchContributionsFromContent(String searchString) throws Exception {
return(searchTableFieldsFromContent(
searchString,
getContributionsTableName(),
getContributionsIdColumnName(),
contributionsSearchFields,
"contributions",
contributionsSelectFields,
contributionsSelectTypes,
true));
}
private JSONObject fetchContributorFromIdWithCache(int contributor_id, Map cache) throws Exception {
JSONObject userInfo = null;
// Convert contributor id into user info
Integer contId = new Integer(contributor_id);
if( cache.containsKey(contId) ) {
userInfo = cache.get(contId);
} else {
try {
userInfo = fetchContributorFromId(contributor_id);
} catch(Exception e) {
// Just ignore
}
// cache
cache.put(contId, userInfo);
}
return userInfo;
}
private JSONObject fetchContributorFromId(int contributor_id) throws Exception {
JSONObject userInfo = null;
try {
PreparedStatement pstmt = connection.prepareStatement("SELECT name,group_id FROM users WHERE id=?;");
pstmt.setInt(1, contributor_id);
if( pstmt.execute() ) {
ResultSet prs = pstmt.getResultSet();
if( prs.next() ) {
userInfo = new JSONObject();
userInfo.put("display", prs.getString(1));
userInfo.put("anonymous", (prs.getInt(2)==0));
}
}
} catch(Exception e) {
// Just ignore
}
return userInfo;
}
/**
* Finds and returns all audio media associated with a place id.
* @param place_id Id of associated place
* @return A JSON object containing all audio media
* @throws Exception
*/
public JSONObject getAudioMediaFromPlaceId(String place_id) throws Exception {
List selectFields = new Vector();
selectFields.add( new SelectedColumn(SelectedColumn.Type.INTEGER, "id") );
selectFields.add( new SelectedColumn(SelectedColumn.Type.INTEGER, "place_id") );
selectFields.add( new SelectedColumn(SelectedColumn.Type.STRING, "filename") );
selectFields.add( new SelectedColumn(SelectedColumn.Type.STRING, "mimetype") );
selectFields.add( new SelectedColumn(SelectedColumn.Type.STRING, "title") );
PreparedStatement stmt = connection.prepareStatement(
"SELECT id,place_id,filename,mimetype,title " +
"FROM contributions " +
"WHERE mimetype LIKE 'audio/%' AND place_id = ?;"
);
stmt.setInt(1, Integer.parseInt(place_id));
JSONArray array = executeStatementToJson(stmt, selectFields);
JSONObject result = new JSONObject();
result.put("media", array);
return result;
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy