Tool or library for end-users to run queries to select (not modify) data?

Posted by Mark Rushakoff on Stack Overflow See other posts from Stack Overflow or by Mark Rushakoff
Published on 2010-03-15T23:21:04Z Indexed on 2010/03/15 23:59 UTC
Read the original article Hit count: 189

Filed under:
|
|
|

For a utility I'm working on, the client would like to be able to generate graphic reports on the data that has been collected. I can already generate a couple canned graphs (using ZedGraph, which is a very nice library); however, the utility would be much more flexible if the graphs were more programmable or configurable by the end-user.

TLDR version

I want users to be able to use something like SQL to safely extract and select data from a List of objects that I provide and can describe. What free tools or libraries will help me accomplish this?

Full version

I've given thought to using IronPython, IronRuby, and LuaInterface, but frankly they're all a bit overpowered for what I want to do. My classes are fairly simple, along the lines of:

class Person:
    string Name;
    int HeightInCm;
    DateTime BirthDate;
    Weight[] WeighIns;

class Weight:
    int WeightInKg;
    DateTime Date;
    Person Owner;

(exact classes have been changed to protect the innocent).

To come up with the data for the graph, the user will choose whether it's a bar graph, scatter plot, etc., and then to actually obtain the data, I would like to obtain some kind of List from the user simply entering something SQL-ish along the lines of

SELECT Name, AVG(WeighIns) FROM People
SELECT WeightInKg, Owner.HeightInCm FROM Weights

And as a bonus, it would be nice if you could actually do operations as well:

SELECT WeightInKg, (Date - Owner.BirthDate) AS Age FROM Weights

The DSL doesn't have to be compliant SQL in any way; it doesn't even have to resemble SQL, but I can't think of a more efficient descriptive language for the task.

I'm fine filling in blanks; I don't expect a library to do everything for me. What I would expect to exist (but haven't been able to find in any way, shape, or form) is something like Fluent NHibernate (which I am already using in the project) where I can declare a mapping, something like

var personRequest = Request<Person>();
personRequest.Item("Name", (p => p.Name));
personRequest.Item("HeightInCm", (p => p.HeightInCm));
personRequest.Item("HeightInInches", (p => p.HeightInCm * CM_TO_INCHES));
// ...
var weightRequest = Request<Weight>();
weightRequest.Item("Owner", (w => w.Owner), personRequest); // Indicate a chain to personRequest
// ...
var people = Table<Person>("People", GetPeopleFromDatabase());
var weights = Table<Weight>("Weights", GetWeightsFromDatabase());
// ...
TryRunQuery(userInputQuery);

LINQ is so close to what I want to do, but AFAIK there's no way to sandbox it. I don't want to expose any unnecessary functionality to the end user; meaning I don't want the user to be able to send in and process:

from p in people select (p => { System.IO.File.Delete("C:\\something\\important"); return p.Name })

So does anyone know of any free .NET libraries that allow something like what I've described above? Or is there some way to sandbox LINQ? cs-script is close too, but it doesn't seem to offer sandboxing yet either. I'd be hesitant to expose the NHibernate interface either, as the user should have a read-only view of the data at this point in the usage.

I'm using C# 3.5, and pure .NET solutions would be preferred.

The bottom line is that I'm really trying to avoid writing my own parser for a subset of SQL that would only apply to this single project.

© Stack Overflow or respective owner

Related posts about .NET

Related posts about LINQ