use of org.folio.cql2pgjson.exception.QueryValidationException in project raml-module-builder by folio-org.
the class CQL2PgJSON method toSql.
/**
* Convert the CQL query into a SQL query and return the WHERE and the ORDER BY clause.
* @param cql the query to convert
* @return SQL query
* @throws QueryValidationException
*/
public SqlSelect toSql(String cql) throws QueryValidationException {
try {
CQLParser parser = new CQLParser();
CQLNode node = parser.parse(cql);
return toSql(node);
} catch (IOException | CQLParseException e) {
throw new QueryValidationException(e);
}
}
use of org.folio.cql2pgjson.exception.QueryValidationException in project raml-module-builder by folio-org.
the class CQL2PgJSON method arrayNode.
private String arrayNode(String index, CQLTermNode node, CqlModifiers modifiers, List<Modifier> relationModifiers, Index schemaIndex, IndexTextAndJsonValues incomingvals, Table targetTable) throws QueryValidationException {
StringBuilder sqlAnd = new StringBuilder();
StringBuilder sqlOr = new StringBuilder();
// avoid recursion
modifiers.setRelationModifiers(new LinkedList<>());
for (Modifier relationModifier : relationModifiers) {
final String modifierName = relationModifier.getType().substring(1);
final String modifierValue = relationModifier.getValue();
String foundModifier = lookupModifier(schemaIndex, modifierName);
if (foundModifier == null) {
throw new QueryValidationException("CQL: Unsupported relation modifier " + relationModifier.getType());
}
if (modifierValue == null) {
if (sqlOr.length() == 0) {
sqlOr.append("(");
} else {
sqlOr.append(" or ");
}
IndexTextAndJsonValues vals = new IndexTextAndJsonValues();
vals.setIndexText(SqlUtil.Cql2PgUtil.cqlNameAsSqlText("t.c", foundModifier));
sqlOr.append(indexNode(index, this.dbTable, node, vals, modifiers));
} else {
final String comparator = relationModifier.getComparison();
if (!"=".equals(comparator)) {
throw new QueryValidationException("CQL: Unsupported comparison for relation modifier " + relationModifier.getType());
}
sqlAnd.append(" and ");
sqlAnd.append(queryByFt(SqlUtil.Cql2PgUtil.cqlNameAsSqlText("t.c", foundModifier), modifierValue, comparator, schemaIndex, targetTable));
}
}
if (sqlOr.length() > 0) {
sqlOr.append(")");
} else {
String modifiersSubfield = null;
if (schemaIndex != null) {
modifiersSubfield = schemaIndex.getArraySubfield();
}
if (modifiersSubfield == null) {
throw new QueryValidationException("CQL: No arraySubfield defined for index " + index);
}
IndexTextAndJsonValues vals = new IndexTextAndJsonValues();
vals.setIndexText(SqlUtil.Cql2PgUtil.cqlNameAsSqlText("t.c", modifiersSubfield));
sqlOr.append(indexNode(index, this.dbTable, node, vals, modifiers));
}
return "id in (select t.id" + " from (select id as id, " + " jsonb_array_elements(" + incomingvals.getIndexJson() + ") as c" + " ) as t" + " where " + sqlOr.toString() + sqlAnd.toString() + ")";
}
use of org.folio.cql2pgjson.exception.QueryValidationException in project raml-module-builder by folio-org.
the class CQL2PgJSON method cql2pgJson.
/**
* Return an SQL WHERE clause for the CQL expression.
* @param cql CQL expression to convert
* @return SQL WHERE clause, without leading "WHERE ", may contain "ORDER BY" clause
* @throws QueryValidationException when parsing or validating cql fails
*
* @deprecated use toSql instead
*/
@Deprecated
public String cql2pgJson(String cql) throws QueryValidationException {
try {
CQLParser parser = new CQLParser();
CQLNode node = parser.parse(cql);
return pg(node);
} catch (IOException | CQLParseException e) {
throw new QueryValidationException(e);
}
}
use of org.folio.cql2pgjson.exception.QueryValidationException in project raml-module-builder by folio-org.
the class CQL2PgJSONTest method select.
/**
* @param expectedNames the semicolon+space separated list of expected names, or -- if there should
* be an exception -- the expected substring of the error message prepended by an exclamation mark.
*/
public void select(CQL2PgJSON aCql2pgJson, String sqlFile, String cql, String expectedNames) {
if (!cql.contains(" sortBy ")) {
cql += " sortBy name";
}
String sql = null;
try {
String blob = "user_data";
String tablename = "users";
// this mess up
if ("instances.jsonb".equals(aCql2pgJson.getjsonField())) {
blob = "jsonb";
tablename = "instances";
}
SqlSelect sqlSelect = aCql2pgJson.toSql(cql);
if (rejectLower) {
assertThat(sqlSelect.getWhere().toLowerCase(Locale.ROOT), not(containsString("lower")));
}
sql = "select " + blob + "->'name' from " + tablename + " " + sqlSelect;
logger.info("select: CQL --> SQL: " + cql + " --> " + sql);
runSqlFile(sqlFile);
logger.fine("select: sqlfile done");
String actualNames = "";
try (Statement statement = conn.createStatement();
ResultSet result = statement.executeQuery(sql)) {
while (result.next()) {
if (!actualNames.isEmpty()) {
actualNames += "; ";
}
actualNames += result.getString(1).replace("\"", "");
}
}
if (!expectedNames.equals(actualNames)) {
logger.fine("select: Test FAILURE on " + cql + "#" + expectedNames);
}
logger.fine("select: Got names [" + actualNames + "], expected [" + expectedNames + "]");
assertEquals("CQL: " + cql + ", SQL: " + sql, expectedNames, actualNames);
} catch (QueryValidationException | SQLException e) {
logger.fine("select: " + e.getClass().getSimpleName() + " for query " + cql + " : " + e.getMessage());
if (!expectedNames.startsWith("!")) {
throw new RuntimeException(sql != null ? sql : cql, e);
}
assertThat(e.toString(), containsString(expectedNames.substring(1).trim()));
}
logger.fine("select: done with " + cql);
}
use of org.folio.cql2pgjson.exception.QueryValidationException in project raml-module-builder by folio-org.
the class PgUtil method getWithOptimizedSql.
/**
* Run the cql query using optimized SQL (if possible) or standard SQL.
* <p>
* PostgreSQL has no statistics about a field within a JSONB resulting in bad performance.
* <p>
* This method requires that the sortField has a b-tree index (non-unique) and caseSensitive=false
* and removeAccents=true, and that the cql query is supported by a full text index.
* <p>
* This method starts a full table scan until getOptimizedSqlSize() records have been scanned.
* Then it assumes that there are only a few result records and uses the full text match.
* If the requested number of records have been found it stops immediately.
* @param table
* @param clazz
* @param cql
* @param queryTimeout query timeout in milliseconds, or 0 for no timeout
* @param okapiHeaders
* @param vertxContext
* @param responseDelegateClass
* @return
*/
public static <T, C> Future<Response> getWithOptimizedSql(String table, Class<T> clazz, Class<C> collectionClazz, String sortField, String cql, int offset, int limit, int queryTimeout, Map<String, String> okapiHeaders, Context vertxContext, Class<? extends ResponseDelegate> responseDelegateClass) {
final Method respond500;
try {
respond500 = responseDelegateClass.getMethod(RESPOND_500_WITH_TEXT_PLAIN, Object.class);
} catch (Exception e) {
logger.error(e.getMessage(), e);
return response(e.getMessage(), null, null);
}
final Method respond200;
final Method respond400;
try {
respond200 = responseDelegateClass.getMethod(RESPOND_200_WITH_APPLICATION_JSON, collectionClazz);
respond400 = responseDelegateClass.getMethod(RESPOND_400_WITH_TEXT_PLAIN, Object.class);
} catch (Exception e) {
logger.error(e.getMessage(), e);
return response(e.getMessage(), respond500, respond500);
}
try {
CQL2PgJSON cql2pgJson = new CQL2PgJSON(table + "." + JSON_COLUMN);
CQLWrapper cqlWrapper = new CQLWrapper(cql2pgJson, cql, limit, offset);
PreparedCQL preparedCql = new PreparedCQL(table, cqlWrapper, okapiHeaders);
String sql = generateOptimizedSql(sortField, preparedCql, offset, limit);
if (sql == null) {
// the cql is not suitable for optimization, generate simple sql
return get(preparedCql, clazz, collectionClazz, okapiHeaders, vertxContext, responseDelegateClass);
}
logger.info("Optimized SQL generated. Source CQL: " + cql);
Promise<Response> promise = Promise.promise();
PostgresClient postgresClient = postgresClient(vertxContext, okapiHeaders);
postgresClient.select(sql, queryTimeout, reply -> {
try {
if (reply.failed()) {
Throwable cause = reply.cause();
logger.error("Optimized SQL failed: " + cause.getMessage() + ": " + sql, cause);
response(cause.getMessage(), respond500, respond500).onComplete(promise);
return;
}
C collection = collection(clazz, collectionClazz, reply.result(), offset, limit);
response(collection, respond200, respond500).onComplete(promise);
} catch (Exception e) {
logger.error(e.getMessage(), e);
response(e.getMessage(), respond500, respond500).onComplete(promise);
}
});
return promise.future();
} catch (FieldException | QueryValidationException e) {
logger.error(e.getMessage(), e);
return response(e.getMessage(), respond400, respond500);
} catch (Exception e) {
logger.error(e.getMessage(), e);
return response(e.getMessage(), respond500, respond500);
}
}
Aggregations