Integrate with deedle

[forki]

type sql = SqlDataProvider<"Data Source=SFORKMANN;Initial Catalog=UWW;Integrated Security=True">

let ctx = sql.GetDataContext()


let debtData = 
    query {for x in ctx.``[dbo].[Test$USDebt]`` do
             select (x.Year,x.Debt) }
    |> Frame.ofRecords
    |> Frame.indexColsWith ["Year"; "Debt"]
    |> Frame.indexRowsInt "Year"
    |> Frame.dropSeries "Year"

It would be super duper awesome if we could find a way for Deedle to infer the column names. Frame.indexColsWith ["Year"; "Debt"] feels a bit strange.

// cc @tpetricek

PS: this type provider ROCKKKS!

[pezipink]

The subsequent functions have to have "Year" anyway. How would you set it working ideally? How does it work normally? It's just a tuple of value types you are dealing with at this stage..

[pezipink]

See*

[forki]

yep I know it's a tuple and Deedle cannot infer the names.
Better would something like C#'s anonymous types.

[tpetricek]

If the query returned something like IQueryable<T> (not sure?) then Frame.ofRecords could try to look at the select statement (it should be possible to analyze the quotation, right?) and pick the names from there (and if x.Year was somehow marked as primary key, it could even use that information).

(Probably not something that Frame.ofRecords should do, but perhaps we could try adding Frame.ofQuery?)

[forki]

@tpetricek that would totally rock this!

[pezipink]

IQueryable is almost never a good idea, avoid it all costs! I think we might be overcomplicating this - I have not played with Deedle yet so forgive my ignorance.

It seems to me the real problem here is that you need a way of labelling the data - we can address this problem directly. I can introduce a static parameter that will cause either all the column properties to return a tuple where the first item is the column name, or for it to generate sister properties for each column (perhaps suffixed "WithName" or similar) which would return said tuple.

I would then further suggest that the new Frame.ofQuery function assumes the first tuple in the sequence to always be the primary key, or if one is not always required then have this is a bool argument which when true will use the first element as the primary key. You could then strip the labels out and remember them or do whatever you like with them.

Would something like this work ?

[tpetricek]

The idea is that we somehow need to get the names of the properties. This will work fine if you do:

let debtData = 
    query {for x in ctx.``[dbo].[Test$USDebt]`` do
             where (...) }
    |> Frame.ofRecords

... because Deedle uses reflection to generate data frame with all properties of the objects it gets. However, when you add select and get just a tuple back, the names will be Item1, Item2, ... The idea was that if the source is a query, we could look at the tuple construction and extract the names from there (without any additional syntactic noise).

You could make that work by doing something like:

let debtData = 
    query {for x in ctx.``[dbo].[Test$USDebt]`` do
             select (series ["Year" => x.Year, "Debt" => x.Debt]) }
    |> Frame.ofRows

.. but then, this makes the syntax uglier... But perhaps there is some better way for attaching the names to the results?

[pezipink]

Does what I suggested not solve that problem then? You'd just not use reflection in this case but extract the labels from the expected tuple instead ?

Analyzing of the select statement is already happening to work out which columns to select, if you wanted to go down that route I'd suggest some custom version of the provider rather than trying to return another IQueryable and doing the work afterwards. (Saying that, analysing the projection tree is pretty tough to get it working in all cases, as it can be literally any F# expression of any size. And the various linq-generated tuples from earlier in the query are hard to disambiguate from user defined ones. I'm pretty sure what I have done will break in certain crazy circumstances ;) )

I think Tomas is saying all the work would happen on the deedle side, for any IQueryable input where the expression tree has enough expression content to allow him to fetch inside and infer some labels. It seems like a nice idea.

[pezipink]

Yes - I understand that. It's just that exact same work is already being performed inside the provider, and to get the select to return an IQueryable would require a custom change anyway (and likely not a simple one!). Therefore, this seems overkill when we could simply return the labels directly (and still transparently with no syntax noise) and not have to have Deedle mess about with expression tress - I am probably missing something :)

I'd like to see a prototype!

[ptrelford]

Deedle's pretty print for data frames would be a killer feature for me, e.g.

/// Pretty print   
let print (xs:seq<'a>) = (Deedle.Frame.ofRecords xs).Print()

query { for row in ctx.``[database].[table]`` do 
            select (row.Field1, row.Field2) } |> print

Which prints the results in a highly readable tabular format:

      Item1   Item2               
0  -> A       6f8f8022-9997-49ce-b7dd-cf6fc2885c0b 
1  -> B       014c05ac-4c03-40e9-a141-f348064bbb3b 

I think it would be nice if we could either:

• find a way for Deedle to get the column names
• or perhaps more simply provide a pretty print function for query results in the SQLProvider library

[tpetricek]

We could get ctx.``[database].[table]``|> Frame.ofObjects to work quite easily if the type representing row implemented IDictionary<string, obj> or something like that.

It is not built in yet, but the following does the trick and could be added to Deedle:

let ofObjects objects = 
  [ for o in objects -> series ["?" => o]]
  |> Frame.ofRowsOrdinal
  |> Frame.expandAllCols 1
  |> Frame.mapColKeys (fun k -> k.Substring(2))

Then you can write:

> [ for i in 0 .. 10 ->
      dict ["Number", i; "Sqare", i*i] ]
  |> ofObjects

Which prints:

val it : Frame<int,string> =  
      Number Sqare 
0  -> 0      0     
1  -> 1      1     
2  -> 2      4    

This relies on the fact that the objects implement IDictionary, which is not the case for tuples. I still think doing this for tuples would be doable (by analyzing the IQueryable returned by query { .. }) but it is harder (and ugly).

[ptrelford]

I like the IDictionary idea for integration and raise you System.Dynamic.DynamicObject which would allow queries (albeit without types) to be consumed from IronPython, VB.Net, C#, etc. as well as Deedle.

[pezipink]

Great ideas for both IDictionary and DynamicObject, both of these should be fairly easy to add to the SqlEntity, as it is essentially just a dictionary.

Also +1 for having a decent pretty print for normal FSI use with just the SQL provider - whilst you can easily 2 way databind to a grid in a ~4 lines of code it would be nice to have a better FSI experience.