org.apache.oodt.cas.filemgr.util.SqlParser Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of cas-filemgr Show documentation
Show all versions of cas-filemgr Show documentation
The file management component of a Catalog and Archive Service. This component
purposefully separates data stores and metadata stores as standard interfaces, and
provides some out of the box backend implementations of them (including one based
on the javax.sql.DataSource interface). This component provides everything that you need
to catalog, archive and manage files, and directories, and their associated metadata.
/*
* Licensed to the Apache Software Foundation (ASF) under one or more
* contributor license agreements. See the NOTICE file distributed with
* this work for additional information regarding copyright ownership.
* The ASF licenses this file to You 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.
*/
package org.apache.oodt.cas.filemgr.util;
//OODT imports
import org.apache.oodt.cas.filemgr.structs.BooleanQueryCriteria;
import org.apache.oodt.cas.filemgr.structs.QueryCriteria;
import org.apache.oodt.cas.filemgr.structs.RangeQueryCriteria;
import org.apache.oodt.cas.filemgr.structs.TermQueryCriteria;
import org.apache.oodt.cas.filemgr.structs.exceptions.QueryFormulationException;
import org.apache.oodt.cas.filemgr.structs.query.ComplexQuery;
import org.apache.oodt.cas.filemgr.structs.query.QueryFilter;
import org.apache.oodt.cas.filemgr.structs.query.filter.FilterAlgor;
//JDK imports
import java.util.Arrays;
import java.util.LinkedList;
import java.util.List;
import java.util.Stack;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
*
* @author bfoster
* @version $Revision$
*
*
* A fairly robust SQL parser, based on the Shunting yard
* algorithm
*
*
*
* Evaluates SQL like string statements contained in a string. The SQL
* statement should be enclosed within: SQL ({@literal }
* ) { {@literal } . the {@literal } can
* be either FORMAT, SORT_BY, or FILTER. Syntax: SQL (FORMAT='',SORT_BY='', FILTER=', , , ') { SELECT
* FROM
* WHERE
* } Here is an example SQL statement: SQL (FORMAT='FileLocation/Filename',
* SORT_BY='FileSize',FILTER== '2007-12-01T00:00:00.000000Z' } This example
* would query the cas-filemgr for metadata values:
* FileLocation,Filename,FileSize for any data file where the
* ProductType == IASI_L1C and the ProductionDateTime >=
* 2007-12-01T00:00:00.000000Z. It would then combine the return data
* files metadata via the specified FORMAT. Each data file's metadata
* will be formated to a string representation of (with the actual
* values replaced in the location of the metadata keys):
* FileLocation/Filename. They will be concatenated together, in
* FileSize order.
*
*/
public class SqlParser {
private SqlParser() {
}
public static ComplexQuery parseSqlQueryMethod(String sqlStringQueryMethod)
throws QueryFormulationException {
if (!Pattern.matches("((?:SQL)|(?:sql))\\s*(.*)\\s*\\{\\s*SELECT.*FROM.*(?:WHERE.*){0,1}\\}", sqlStringQueryMethod))
throw new QueryFormulationException("Malformed SQL method");
try {
ComplexQuery complexQuery = parseSqlQuery(stripOutSqlDefinition(sqlStringQueryMethod));
for (Expression expr : getSqlStatementArgs(sqlStringQueryMethod)) {
if (expr.getKey().toUpperCase().equals("FORMAT"))
complexQuery.setToStringResultFormat(expr.getValue());
else if (expr.getKey().toUpperCase().equals("SORT_BY"))
complexQuery.setSortByMetKey(expr.getValue());
else if (expr.getKey().toUpperCase().equals("FILTER"))
complexQuery.setQueryFilter(createFilter(expr));
}
return complexQuery;
}catch (Exception e) {
e.printStackTrace();
throw new QueryFormulationException("Failed to parse SQL method : " + e.getMessage());
}
}
public static ComplexQuery parseSqlQuery(String sqlStringQuery)
throws QueryFormulationException {
String[] splitSqlStatement = sqlStringQuery
.split("((?:SELECT)|(?:FROM)|(?:WHERE))");
String[] selectValues = (splitSqlStatement[1].trim() + ",").split(",");
String[] fromValues = (splitSqlStatement[2].trim() + ",").split(",");
ComplexQuery sq = new ComplexQuery();
List selectValuesList = Arrays.asList(selectValues);
if (!selectValuesList.contains("*"))
sq.setReducedMetadata(Arrays.asList(selectValues));
List fromValuesList = Arrays.asList(fromValues);
if (!fromValuesList.contains("*"))
sq.setReducedProductTypeNames(fromValuesList);
if (splitSqlStatement.length > 3)
sq.addCriterion(parseStatement(toPostFix(splitSqlStatement[3]
.trim())));
return sq;
}
public static QueryCriteria parseSqlWhereClause(String sqlWhereClause)
throws QueryFormulationException {
return parseStatement(toPostFix(sqlWhereClause.trim()));
}
public static String unparseSqlQuery(ComplexQuery complexQuery) throws QueryFormulationException {
LinkedList outputArgs = new LinkedList();
if (complexQuery.getToStringResultFormat() != null)
outputArgs.add("FORMAT = '" + complexQuery.getToStringResultFormat() + "'");
if (complexQuery.getSortByMetKey() != null)
outputArgs.add("SORT_BY = '" + complexQuery.getSortByMetKey() + "'");
if (complexQuery.getQueryFilter() != null) {
String filterString = "FILTER = '"
+ complexQuery.getQueryFilter().getStartDateTimeMetKey() + ","
+ complexQuery.getQueryFilter().getEndDateTimeMetKey() + ","
+ complexQuery.getQueryFilter().getPriorityMetKey() + ","
+ complexQuery.getQueryFilter().getFilterAlgor().getClass().getCanonicalName() + ","
+ complexQuery.getQueryFilter().getFilterAlgor().getEpsilon();
outputArgs.add(filterString + "'");
}
String sqlQueryString = getInfixCriteriaString(complexQuery.getCriteria());
if (sqlQueryString != null && sqlQueryString.startsWith("(") && sqlQueryString.endsWith(")"))
sqlQueryString = sqlQueryString.substring(1, sqlQueryString.length() - 1);
return "SQL ("
+ listToString(outputArgs)
+ ") { SELECT " + listToString(complexQuery.getReducedMetadata())
+ " FROM " + (complexQuery.getReducedProductTypeNames() != null ? listToString(complexQuery.getReducedProductTypeNames()) : "*")
+ (sqlQueryString != null ? " WHERE " + sqlQueryString : "") + " }";
}
public static String getInfixCriteriaString(List criteriaList) throws QueryFormulationException {
if (criteriaList.size() > 1)
return getInfixCriteriaString(new BooleanQueryCriteria(criteriaList, BooleanQueryCriteria.AND));
else if (criteriaList.size() == 1)
return getInfixCriteriaString(criteriaList.get(0));
else
return null;
}
public static String getInfixCriteriaString(QueryCriteria criteria) {
String returnString = "";
if (criteria instanceof BooleanQueryCriteria) {
BooleanQueryCriteria bqc = (BooleanQueryCriteria) criteria;
List terms = bqc.getTerms();
switch(bqc.getOperator()){
case 0:
returnString = "(" + getInfixCriteriaString((QueryCriteria) terms.get(0));
for (int i = 1; i < terms.size(); i++)
returnString += " AND " + getInfixCriteriaString((QueryCriteria) terms.get(i));
returnString += ")";
break;
case 1:
returnString = "(" + getInfixCriteriaString((QueryCriteria) terms.get(0));
for (int i = 1; i < terms.size(); i++)
returnString += " OR " + getInfixCriteriaString((QueryCriteria) terms.get(i));
returnString += ")";
break;
case 2:
QueryCriteria qc = bqc.getTerms().get(0);
if (qc instanceof TermQueryCriteria) {
TermQueryCriteria tqc = (TermQueryCriteria) qc;
returnString = tqc.getElementName() + " != '" + tqc.getValue() + "'";
}else {
returnString = "NOT(" + getInfixCriteriaString(qc) + ")";
}
break;
}
}else if (criteria instanceof RangeQueryCriteria) {
RangeQueryCriteria rqc = (RangeQueryCriteria) criteria;
String opString = rqc.getInclusive() ? "=" : "";
if (rqc.getStartValue() != null) {
opString = ">" + opString + " '" + rqc.getStartValue() + "'";
}else
opString = "<" + opString + " '" + rqc.getEndValue() + "'";
returnString = rqc.getElementName() + " " + opString;
}else if (criteria instanceof TermQueryCriteria) {
TermQueryCriteria tqc = (TermQueryCriteria) criteria;
returnString = tqc.getElementName() + " == '" + tqc.getValue() + "'";
}
return returnString;
}
private static String stripOutSqlDefinition(String sqlStringQueryMethod) {
return sqlStringQueryMethod.trim().replaceAll("((?:SQL)|(?:sql))\\s*(.*)\\s*\\{", "").replaceAll("}$", "").trim();
}
private static List getSqlStatementArgs(String sqlStringQueryMethod) throws QueryFormulationException {
boolean inExpr = false;
int startArgs = 0;
for (int i = 0; i < sqlStringQueryMethod.length(); i++) {
char curChar = sqlStringQueryMethod.charAt(i);
switch (curChar) {
case '(':
startArgs = i + 1;
break;
case ')':
if (!inExpr) {
String[] args = sqlStringQueryMethod.substring(startArgs, i).trim().split("'\\s*,");
LinkedList argsList = new LinkedList();
for (String arg : args)
argsList.add(new Expression((arg = arg.trim()).endsWith("'") ? arg : (arg + "'")));
return argsList;
} else {
break;
}
case '\'':
inExpr = !inExpr;
break;
}
}
throw new QueryFormulationException("Failed to read in args");
}
private static QueryFilter createFilter(Expression expr) throws InstantiationException, IllegalAccessException, ClassNotFoundException {
String[] filterArgs = expr.getValue().split(",");
FilterAlgor filterAlgor = (FilterAlgor) Class.forName(filterArgs[3]).newInstance();
QueryFilter qf = new QueryFilter(filterArgs[0], filterArgs[1], filterArgs[2], filterAlgor);
filterAlgor.setEpsilon(Integer.parseInt(filterArgs[4]));
return qf;
}
/**
* Uses "Shunting yard algorithm" (see:
* http://en.wikipedia.org/wiki/Shunting_yard_algorithm)
*/
private static LinkedList toPostFix(String statement) {
LinkedList postFix = new LinkedList();
Stack stack = new Stack();
for (int i = 0; i < statement.length(); i++) {
char curChar = statement.charAt(i);
switch (curChar) {
case '(':
stack.push(new String("("));
break;
case ')':
String value = null;
while (!(value = stack.pop()).equals("("))
postFix.add(value);
if (stack.peek().equals("NOT"))
postFix.add(stack.pop());
break;
case ' ':
break;
default:
if (statement.substring(i, i + 3).equals("AND")) {
while (!stack.isEmpty()
&& (stack.peek().equals("AND")))
postFix.add(stack.pop());
stack.push("AND");
i += 2;
} else if (statement.substring(i, i + 2).equals("OR")) {
while (!stack.isEmpty()
&& (stack.peek().equals("AND") || stack.peek()
.equals("OR")))
postFix.add(stack.pop());
stack.push("OR");
i += 1;
} else if (statement.substring(i, i + 3).equals("NOT")) {
stack.push("NOT");
i += 2;
} else {
int endIndex = statement.indexOf('\'', statement.indexOf(
'\'', i) + 1) + 1;
postFix.add(statement.substring(i, endIndex));
i = endIndex - 1;
}
}
}
while (!stack.isEmpty())
postFix.add(stack.pop());
return postFix;
}
private static QueryCriteria parseStatement(LinkedList postFixStatement)
throws QueryFormulationException {
Stack stack = new Stack();
for (String item : postFixStatement) {
if (item.equals("AND")) {
BooleanQueryCriteria bQC = new BooleanQueryCriteria();
bQC.addTerm(stack.pop());
bQC.addTerm(stack.pop());
stack.push(bQC);
} else if (item.equals("OR")) {
BooleanQueryCriteria bQC = new BooleanQueryCriteria();
bQC.setOperator(BooleanQueryCriteria.OR);
bQC.addTerm(stack.pop());
bQC.addTerm(stack.pop());
stack.push(bQC);
} else if (item.equals("NOT")) {
BooleanQueryCriteria bQC = new BooleanQueryCriteria();
bQC.setOperator(BooleanQueryCriteria.NOT);
bQC.addTerm(stack.pop());
stack.push(bQC);
} else {
stack.push(new Expression(item).convertToQueryCriteria());
}
}
return stack.pop();
}
private static String listToString(List list) {
String arrayString = "";
if (list.size() > 0) {
arrayString = list.get(0);
for (int i = 1; i < list.size(); i++)
arrayString += "," + list.get(i);
}
return arrayString;
}
private static class Expression {
public static final short GREATER_THAN = 12;
public static final short LESS_THAN = 3;
public static final short EQUAL_TO = 9;
public static final short NOT_EQUAL_TO = 15;
public static final short GREATER_THAN_OR_EQUAL_TO = 13;
public static final short LESS_THAN_OR_EQUAL_TO = 11;
public static final short NOT = 6;
private String[] stringValues = new String[] { "`", "`", "`", "<", "`",
"`", "!", "`", "`", "=", "`", "<=", ">", ">=", "`", "!=" };
private String expression;
private String key;
private String val;
private int op;
public Expression(String expression) {
this.parseExpression(this.expression = expression);
}
public Expression(String key, int op, String val) {
this.key = key.trim();
this.op = op;
this.val = this.removeTickBounds(val.trim());
}
private void parseExpression(String expression) {
Matcher matcher = Pattern.compile("((?:>=)|(?:<=)|(?:==)|(?:!=)|(?:=)|(?:>)|(?:<))").matcher(expression);
matcher.find();
this.key = expression.substring(0, matcher.start()).trim();
this.val = this.removeTickBounds(expression.substring(matcher.end()).trim());
String opString = matcher.group();
for (char c : opString.toCharArray())
this.op = this.op | this.getShortValueForOp(c);
}
private String removeTickBounds(String value) {
if (value.startsWith("'") && value.endsWith("'"))
value = value.substring(1, value.length() - 1);
return value;
}
private int getShortValueForOp(char op) {
switch (op) {
case '>':
return GREATER_THAN;
case '<':
return LESS_THAN;
case '=':
return EQUAL_TO;
case '!':
return NOT;
default:
return 0;
}
}
public QueryCriteria convertToQueryCriteria()
throws QueryFormulationException {
switch (this.op) {
case GREATER_THAN:
return new RangeQueryCriteria(this.key, this.val, null, false);
case LESS_THAN:
return new RangeQueryCriteria(this.key, null, this.val, false);
case EQUAL_TO:
return new TermQueryCriteria(this.key, this.val);
case NOT_EQUAL_TO:
BooleanQueryCriteria notEqBQC = new BooleanQueryCriteria();
notEqBQC.setOperator(BooleanQueryCriteria.NOT);
notEqBQC.addTerm(new TermQueryCriteria(this.key, this.val));
return notEqBQC;
case GREATER_THAN_OR_EQUAL_TO:
return new RangeQueryCriteria(this.key, this.val, null, true);
case LESS_THAN_OR_EQUAL_TO:
return new RangeQueryCriteria(this.key, null, this.val, true);
}
throw new QueryFormulationException(
"Was not able to form query . . . probably an invalid operator -- "
+ this.toString());
}
public String getKey() {
return this.key;
}
public String getValue() {
return this.val;
}
public int getOp() {
return this.op;
}
public String getExpression() {
return this.expression;
}
public String toString() {
return this.key + " " + this.stringValues[this.op] + " " + this.val;
}
}
public static void main(String[] args) throws QueryFormulationException {
String query = "SELECT * FROM IASI_L1C WHERE one == '1' AND two == '2' OR NOT(five == '5') OR three == '3' AND four == '4'";
System.out.println("query: " + query);
System.out.println("query after : " + unparseSqlQuery(parseSqlQuery(query)));
query = "SELECT * FROM IASI_L1C";
System.out.println("query: " + query);
System.out.println("query after : " + unparseSqlQuery(parseSqlQuery(query)));
query = "SELECT * FROM *";
System.out.println("query: " + query);
System.out.println("query after : " + unparseSqlQuery(parseSqlQuery(query)));
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy