From 09bfba4ce38a0d602358837316614c0db0b03616 Mon Sep 17 00:00:00 2001 From: miker Date: Fri, 14 Nov 2008 04:02:01 +0000 Subject: [PATCH] backporting EXISTS/NOT EXISTS/subquery/better-aggregate from trunk git-svn-id: svn://svn.open-ils.org/ILS/branches/rel_1_4@11179 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/c-apps/oils_cstore.c | 119 ++++++++++++++++++++++++++------------ 1 file changed, 82 insertions(+), 37 deletions(-) diff --git a/Open-ILS/src/c-apps/oils_cstore.c b/Open-ILS/src/c-apps/oils_cstore.c index 6aaab599d8..ae10adb9d3 100644 --- a/Open-ILS/src/c-apps/oils_cstore.c +++ b/Open-ILS/src/c-apps/oils_cstore.c @@ -18,6 +18,7 @@ # define MODULENAME "open-ils.cstore" #endif +#define SUBSELECT 4 #define DISABLE_I18N 2 #define SELECT_DISTINCT 1 #define AND_OP_JOIN 0 @@ -59,7 +60,7 @@ static char* searchBETWEENPredicate ( const char*, osrfHash*, jsonObject* ); static char* searchINPredicate ( const char*, osrfHash*, const jsonObject*, const char* ); static char* searchPredicate ( const char*, osrfHash*, jsonObject* ); static char* searchJOIN ( const jsonObject*, osrfHash* ); -static char* searchWHERE ( const jsonObject*, osrfHash*, int ); +static char* searchWHERE ( const jsonObject*, osrfHash*, int, osrfMethodContext* ); static char* buildSELECT ( jsonObject*, jsonObject*, osrfHash*, osrfMethodContext* ); static char* SELECT ( osrfMethodContext*, jsonObject*, jsonObject*, jsonObject*, jsonObject*, jsonObject*, jsonObject*, jsonObject*, int ); @@ -1235,11 +1236,11 @@ static char* searchFieldTransformPredicate (const char* class, osrfHash* field, char* value = NULL; if (!jsonObjectGetKeyConst( node, "value" )) { - value = searchWHERE( node, osrfHashGet( oilsIDL(), class ), AND_OP_JOIN ); + value = searchWHERE( node, osrfHashGet( oilsIDL(), class ), AND_OP_JOIN, NULL ); } else if (jsonObjectGetKeyConst( node, "value" )->type == JSON_ARRAY) { value = searchValueTransform(jsonObjectGetKeyConst( node, "value" )); } else if (jsonObjectGetKeyConst( node, "value" )->type == JSON_HASH) { - value = searchWHERE( jsonObjectGetKeyConst( node, "value" ), osrfHashGet( oilsIDL(), class ), AND_OP_JOIN ); + value = searchWHERE( jsonObjectGetKeyConst( node, "value" ), osrfHashGet( oilsIDL(), class ), AND_OP_JOIN, NULL ); } else if (jsonObjectGetKeyConst( node, "value" )->type != JSON_NULL) { if ( !strcmp(osrfHashGet(field, "primitive"), "number") ) { value = jsonNumberToDBString( field, jsonObjectGetKeyConst( node, "value" ) ); @@ -1570,7 +1571,7 @@ static char* searchJOIN ( const jsonObject* join_hash, osrfHash* leftmeta ) { buffer_add( join_buf, " AND " ); } - char* jpred = searchWHERE( filter, idlClass, AND_OP_JOIN ); + char* jpred = searchWHERE( filter, idlClass, AND_OP_JOIN, NULL ); buffer_fadd( join_buf, " %s", jpred ); free(jpred); free(filter_op); @@ -1601,7 +1602,8 @@ static char* searchJOIN ( const jsonObject* join_hash, osrfHash* leftmeta ) { [ { +class : { -or|-and : [ { field : { op : value }, ... }, ...] ... }, ... }, ... ] */ -static char* searchWHERE ( const jsonObject* search_hash, osrfHash* meta, int opjoin_type ) { + +static char* searchWHERE ( const jsonObject* search_hash, osrfHash* meta, int opjoin_type, osrfMethodContext* ctx ) { growing_buffer* sql_buf = buffer_init(128); @@ -1618,7 +1620,7 @@ static char* searchWHERE ( const jsonObject* search_hash, osrfHash* meta, int op else buffer_add(sql_buf, " AND "); } - char* subpred = searchWHERE( node, meta, opjoin_type ); + char* subpred = searchWHERE( node, meta, opjoin_type, ctx ); buffer_fadd(sql_buf, "( %s )", subpred); free(subpred); } @@ -1641,18 +1643,48 @@ static char* searchWHERE ( const jsonObject* search_hash, osrfHash* meta, int op buffer_fadd(sql_buf, " \"%s\".%s ", search_itr->key + 1, subpred); free(subpred); } else { - char* subpred = searchWHERE( node, osrfHashGet( oilsIDL(), search_itr->key + 1 ), AND_OP_JOIN ); + char* subpred = searchWHERE( node, osrfHashGet( oilsIDL(), search_itr->key + 1 ), AND_OP_JOIN, ctx ); buffer_fadd(sql_buf, "( %s )", subpred); free(subpred); } } else if ( !strcasecmp("-or",search_itr->key) ) { - char* subpred = searchWHERE( node, meta, OR_OP_JOIN ); + char* subpred = searchWHERE( node, meta, OR_OP_JOIN, ctx ); buffer_fadd(sql_buf, "( %s )", subpred); free(subpred); } else if ( !strcasecmp("-and",search_itr->key) ) { - char* subpred = searchWHERE( node, meta, AND_OP_JOIN ); + char* subpred = searchWHERE( node, meta, AND_OP_JOIN, ctx ); buffer_fadd(sql_buf, "( %s )", subpred); free(subpred); + } else if ( !strcasecmp("-exists",search_itr->key) ) { + char* subpred = SELECT( + ctx, + jsonObjectGetKey( node, "select" ), + jsonObjectGetKey( node, "from" ), + jsonObjectGetKey( node, "where" ), + jsonObjectGetKey( node, "having" ), + jsonObjectGetKey( node, "order_by" ), + jsonObjectGetKey( node, "limit" ), + jsonObjectGetKey( node, "offset" ), + SUBSELECT + ); + + buffer_fadd(sql_buf, "EXISTS ( %s )", subpred); + free(subpred); + } else if ( !strcasecmp("-not-exists",search_itr->key) ) { + char* subpred = SELECT( + ctx, + jsonObjectGetKey( node, "select" ), + jsonObjectGetKey( node, "from" ), + jsonObjectGetKey( node, "where" ), + jsonObjectGetKey( node, "having" ), + jsonObjectGetKey( node, "order_by" ), + jsonObjectGetKey( node, "limit" ), + jsonObjectGetKey( node, "offset" ), + SUBSELECT + ); + + buffer_fadd(sql_buf, "NOT EXISTS ( %s )", subpred); + free(subpred); } else { char* class = osrfHashGet(meta, "classname"); @@ -1936,7 +1968,11 @@ static char* SELECT ( if (is_agg->size || (flags & SELECT_DISTINCT)) { - if (!jsonBoolIsTrue( jsonObjectGetKey( selfield, "aggregate" ) )) { + if ( !( + jsonBoolIsTrue( jsonObjectGetKey( selfield, "aggregate" ) ) || + ((int)jsonObjectGetNumber(jsonObjectGetKey( selfield, "aggregate" ))) == 1 // support 1/0 for perl's sake + ) + ) { if (gfirst) { gfirst = 0; } else { @@ -1998,16 +2034,18 @@ static char* SELECT ( buffer_add(sql_buf, " WHERE "); // and it's on the the WHERE clause - char* pred = searchWHERE( search_hash, core_meta, AND_OP_JOIN ); + char* pred = searchWHERE( search_hash, core_meta, AND_OP_JOIN, ctx ); if (!pred) { - osrfAppSessionStatus( - ctx->session, - OSRF_STATUS_INTERNALSERVERERROR, - "osrfMethodException", - ctx->request, - "Severe query error in WHERE predicate -- see error log for more details" - ); + if (ctx) { + osrfAppSessionStatus( + ctx->session, + OSRF_STATUS_INTERNALSERVERERROR, + "osrfMethodException", + ctx->request, + "Severe query error in WHERE predicate -- see error log for more details" + ); + } free(core_class); buffer_free(having_buf); buffer_free(group_buf); @@ -2025,16 +2063,18 @@ static char* SELECT ( buffer_add(sql_buf, " HAVING "); // and it's on the the WHERE clause - char* pred = searchWHERE( having_hash, core_meta, AND_OP_JOIN ); + char* pred = searchWHERE( having_hash, core_meta, AND_OP_JOIN, ctx ); if (!pred) { - osrfAppSessionStatus( - ctx->session, - OSRF_STATUS_INTERNALSERVERERROR, - "osrfMethodException", - ctx->request, - "Severe query error in HAVING predicate -- see error log for more details" - ); + if (ctx) { + osrfAppSessionStatus( + ctx->session, + OSRF_STATUS_INTERNALSERVERERROR, + "osrfMethodException", + ctx->request, + "Severe query error in HAVING predicate -- see error log for more details" + ); + } free(core_class); buffer_free(having_buf); buffer_free(group_buf); @@ -2142,13 +2182,15 @@ static char* SELECT ( // IT'S THE OOOOOOOOOOOLD STYLE! } else { osrfLogError(OSRF_LOG_MARK, "%s: Possible SQL injection attempt; direct order by is not allowed", MODULENAME); - osrfAppSessionStatus( - ctx->session, - OSRF_STATUS_INTERNALSERVERERROR, - "osrfMethodException", - ctx->request, - "Severe query error -- see error log for more details" - ); + if (ctx) { + osrfAppSessionStatus( + ctx->session, + OSRF_STATUS_INTERNALSERVERERROR, + "osrfMethodException", + ctx->request, + "Severe query error -- see error log for more details" + ); + } free(core_class); buffer_free(having_buf); @@ -2213,7 +2255,7 @@ static char* SELECT ( free(string); } - buffer_add(sql_buf, ";"); + if (!(flags & SUBSELECT)) buffer_add(sql_buf, ";"); free(core_class); if (defaultselhash) jsonObjectFree(defaultselhash); @@ -2298,8 +2340,11 @@ static char* buildSELECT ( jsonObject* search_hash, jsonObject* order_hash, osrf if (locale) { char* i18n = osrfHashGet(field, "i18n"); - if (jsonBoolIsTrue(jsonObjectGetKey( order_hash, "no_i18n" ))) - i18n = NULL; + if ( !( + jsonBoolIsTrue( jsonObjectGetKey( order_hash, "no_i18n" ) ) || + ((int)jsonObjectGetNumber(jsonObjectGetKey( order_hash, "no_i18n" ))) == 1 // support 1/0 for perl's sake + ) + ) i18n = NULL; if ( i18n && !strncasecmp("true", i18n, 4)) { char* pkey = osrfHashGet(idlClass, "primarykey"); @@ -2338,7 +2383,7 @@ static char* buildSELECT ( jsonObject* search_hash, jsonObject* order_hash, osrf buffer_add(sql_buf, " WHERE "); - char* pred = searchWHERE( search_hash, meta, AND_OP_JOIN ); + char* pred = searchWHERE( search_hash, meta, AND_OP_JOIN, ctx ); if (!pred) { osrfAppSessionStatus( ctx->session, -- 2.11.0