Desde hace unos días estuve buscando la forma de pasar a un servicio Rest (de Web API) una expresión que pudiera ser útil para consultar tablas en forma dinámica (no se conoce a priori su estructura).
Las expresiones que deseaba pasarle al parser eran del tipo:
GET /Service/Customers?$where=Name eq ‘ACME’
Esto ayuda a independizarse de la base de datos, evitar SQL injection y brindar una api de consumo amigable.
Todas las soluciones que pude encontrar se basan en parsers tipados, solución que quizás funcione para el 90%, pero a mi me tocó estar en el porcentaje restante.
Ejemplo de parser está de OData para Web API
http://blogs.msdn.com/b/alexj/archive/2012/12/06/parsing-filter-and-orderby-using-the-odatauriparser.aspx
Linq2Rest
https://bitbucket.org/jjrdk/linq2rest
Tuve la suerte de encontrarme con la referencia de operadores usada en MongoDB
http://docs.mongodb.org/manual/reference/operators/
Donde usando JSON se puede pasar una expresión de filtro estilo:
where={ $and: [{'gid': {'$gt': 1}}, {'layer': 0}] }
He aquí el código, parece simple, y lo es, pero anda :)
/*
* QueryParser
*
* Autor: Felixls
* Fecha: Diciembre 2012
*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Sample.Models;
using Newtonsoft.Json.Linq;
namespace Sample.Controllers
{
/*
* Parser de consultas con sintaxis de MongoDB
* http://docs.mongodb.org/manual/reference/operators/
*
* Ejemplos:
?where={"age": {"$gt": 20}}
{ 'gid': {'$eq': 1}}
{ 'gid': 1}
{ 'gid': {'$gt': 1}, 'layer': 0}
{ $and: [{'gid': {'$gt': 1}}, {'layer': 0}] }
{ $or: [{'gid': {'$gt': 1}}, {'layer': 0}] }
{ 'handle': 48, $and: [{'gid': {'$gte': 1}}, {'layer': 0}] }
{ 'handle': 48, $or: [{'gid': {'$gte': 1}}, {'layer': 0}] }
{ $and: [{'gid': {'$gt': 1}}, {$or: [{'layer': 0}, {'layer': {'$eq': 1}}]}] }
{ 'gid': {'$gt': 1}, $or: [{'layer': 0}, {'layer': {'$eq': 1}}] }
{ 'gid': {'$in': [1, 5]} }
{ 'fecha': {'$in': ['2012-12-09', '2012-12-10']} }
{ 'fecha': {'$in': ['2012-12-09', '2012-12-10']}, 'tipomov': 2 }
{ 'fecha': {'$in': ['2012-12-09', '2012-12-10']}, $or: {'tipomov': 2} }
{ $and: [{'gid': {'$gt': 1}}, {$or: [{'layer': 0}, {'layer': {'$eq': 1}}]}] }
{ 'gid': {'$gt': 1}, $or: [{'layer': 0}, {'layer': {'$eq': 1}}] }
{ 'gid': {'$in': [1, 5]} }
{ 'fecha': {'$in': ['2012-12-09', '2012-12-10']} }
{ 'fecha': {'$nin': ['2012-12-09', '2012-12-10']}, 'tipomov': 2 }
{ 'fecha': {'$in': ['2012-12-09', '2012-12-10']}, $or: {'tipomov': 2} }
{ 'codigo': {'$gt': 'L49'}, $or: [{'tipomov': 3}, {'tipomov': {'$lt': 2}}, {$and: {'fecha': {'$in': ['2012-12-09', '2012-12-10']}}}] }
{ 'codigo': {'$gt': 'L49'}, $or: [{'tipomov': 3}, {'tipomov': {'$lt': 2}}], $and: {'fecha': {'$nin': ['2012-12-09', '2012-12-10']}} }
* Operadores soportados
$eq =
$ne !=
$lt <
$lte <=
$gt >
$gte >=
$in { qty: { $in: [ 5, 15 ] } }
$nin !$in
$and { $and: [ { price: 1.99 }, { qty: { $lt: 20 } }, { sale: true } ] }
$and implicito { price: 1.99, qty: { $lt: 20 } , sale: true }
$or { price:1.99, $or: [ { qty: { $lt: 20 } }, { sale: true } ] }
* NO SOPORTADOS
$all, $nor, $not.
//TODO: Geoespaciales (a futuro soportadas)
$near { location: { $near: [100,100] } }
$bbox { loc: { $within: { $box: [ [0,0], [100,100] ] } } }
$within { location: { $within: { shape } } }
{ location: { $within: { $box: [[100,0], [120,100]] } } }
{ location: { $within: { $center: [ center, radius } } }
{ location: { $within: { $box: [[100,120], [100,100], [120,100], [240,200]] } } }
$polygon { loc: { $within: { $polygon: [ [0,0], [3,6], [6,0] ] } } }
$center { location: { $within: { $center: [ [0,0], 10 ] } } }
$maxdistance { location: { $near: [100,100], $maxDistance: 10 } }
$nearSphere { loc: { $nearSphere: [0,0] } }
$centerSphere { loc: { $centerSphere: { [0,0], 10 / 3959 } } }
*/
public class QueryParser
{
private List atts;
private dynamic root;
private string result = "";
private int state = 0;
private int level = 0;
public QueryParser(string s, List atts)
{
this.atts = atts;
root = JObject.Parse(s);
result = " where ";
state = 0;
level = 0;
recurse(root);
}
private void recurse(dynamic obj)
{
string p;
foreach (var current in obj)
{
p = ParseAttribute(current);
if (p != null)
{
if (level == 1)
{
if (state == 0)
result += " and ";
if (state == 1)
result += " or ";
}
result += p;
level = 1;
}
else
{
if (current.Name == "$or")
LogicalOperator(current, 1);
else if (current.Name == "$and")
LogicalOperator(current, 0);
else
throw new Exception("Syntax error");
}
}
}
private void LogicalOperator(dynamic current, int theState)
{
if (current.Value is JArray)
{
if (level == 1)
result += " and ";
state = theState;
level = 0;
result += "(";
for (int i = 0; i < current.Value.Count; i++)
{
if (i == 1)
level = 1;
recurse(current.Value[i]);
}
result += ")";
}
else
{
state = theState;
recurse(new List(current.Value));
}
}
public string Where()
{
return result;
}
private string ParseAttribute(dynamic obj)
{
foreach (FeatureAttribute att in this.atts)
{
if (obj.Name == att.Name)
{
var op = obj.Value;
var s = "";
if (op is JValue)
s += att.Name + "='" + op + "'";
else if (op["$eq"] != null)
s += att.Name + "='" + op["$eq"] + "'";
else if (op["$ne"] != null)
s += att.Name + "!='" + op["$ne"] + "'";
else if (op["$lt"] != null)
s += att.Name + "<'" + op["$lt"] + "'";
else if (op["$lte"] != null)
s += att.Name + "<='" + op["$lte"] + "'";
else if (op["$gt"] != null)
s += att.Name + ">'" + op["$gt"] + "'";
else if (op["$gte"] != null)
s += att.Name + ">='" + op["$gte"] + "'";
else if (op["$in"] != null)
{
JArray rango = op["$in"];
if (rango.Count() == 2)
s += att.Name + " between '" + rango[0] + "' and '" + rango[1] + "'";
}
else if (op["$nin"] != null)
{
JArray rango = op["$nin"];
if (rango.Count() == 2)
s += att.Name + " not between '" + rango[0] + "' and '" + rango[1] + "'";
}
else
{
throw new Exception("Syntax error");
}
return s;
}
}
return null;
}
}
}
