SqlExp and SqlQuery

[Thorium]

We are currently modelling our SQL-expressions with quite simple context:

SqlExp =
    | BaseTable    of alias * Table                      // name of the initiating IQueryable table - this isn't always the ultimate table that is selected
    | SelectMany   of alias * alias * SelectData * SqlExp  // from alias, to alias and join data including to and from table names. Note both the select many and join syntax end up here
    | FilterClause of Condition * SqlExp                 // filters from the where clause(es)
    | HavingClause of Condition * SqlExp                 // filters from the where clause(es)
    | Projection   of Expression * SqlExp                // entire LINQ projection expression tree
    | Distinct     of SqlExp                             // distinct indicator
    | OrderBy      of alias * string * bool * SqlExp     // alias and column name, bool indicates ascending sort
    | Union        of bool * string * SqlExp             // true = "union all", false = "union", and subquery
    | Skip         of int * SqlExp
    | Take         of int * SqlExp
    | Count        of SqlExp
    | AggregateOp  of AggregateOperation * alias * string * SqlExp

and SqlQuery =
    { Filters       : Condition list
      HavingFilters : Condition list
      Links         : (alias * LinkData * alias) list
      Aliases       : Map<string, Table>
      Ordering      : (alias * string * bool) list
      Projection    : Expression list
      Grouping      : (list<alias * string> * list<AggregateOperation * alias * string>) list //key columns, aggregate columns
      Distinct      : bool
      UltimateChild : (string * Table) option
      Skip          : int option
      Take          : int option
      Union         : (bool*string) option
      Count         : bool 
      AggregateOp   : (AggregateOperation * alias * string) list }

The problem is that we will fail more complex SQL:s, e.g. if you have multiple items like Skips:

select x from xs
skip 1
skip 1
take 1

That code should take 3 from [1;2;3;4] but actually would take 2, but we know this and fail runtime. But then again, the SQL can be more weird, where we can't fail but we expect the typical execution order while the real results should be something else:

select x from xs
take 5
where x > 3
join y in ys

We use our model to generate model -> SQL. There are multiple efforts to do the other way, parse SQL-queries to F# data structures SQL -> model.

Could we, instead of having our own model, steal some model from those efforts, and reverse the functionality to generate SQL?

[pezipink]

Quite possibly. I originally designed it that way to make it as simple as possible to generate the SQL, since I wanted the project to actually reach an alpha :) Am happy for it to be replaced it with something else.

[Thorium]

As yesterday's presentation of MBrace.Sql, this could be something:
https://github.com/colinbull/Sql/blob/master/Sql.fsx