Dynamic Queries

[rkosafo]

Is there a way to support dynamic queries?

i.e.

Given type Read = SQL<"""Select * from MyTable"""> and let query = ["Id", 5L; "Scope", "Demo"]

Is there a way to intercept Read and "add or update the where clause" instead of having to create a different type for each scenario - type ReadById, type ReadByScope, type ReadByIdAndScope?

I'm work a large number of fields and currently experimenting with how to make the queries with large optional fields a bit dynamic.

Any tips, tricks, workaround?

[piaste]

This is a classic question that comes up when you start using parameterized queries, with a classic answer to go with it.

select *
from MyTable
where
    (@id    is null or @id    = id)
and (@scope is null or @scope = scope)
-- etc.

I haven't tried it yet, but apparently Rezoom.SQL is smart enough to figure out if you care about nullability or not, so the above query will take in an int option and a string option parameters, becaus it compares them with null. Simply pass in None for every parameter that isn't used in the query.

[rspeele]

Once again, piaste has it right.

The pattern of @param is null or predicate_involving(@param) is currently the only way to do this. For many queries, it would be the best approach to take anyway.

However, I am aware that sometimes it may result in suboptimal performance since SQL Server will generate only one query plan for the statement, when really it is better to have a different query plan for each set of filters involved. Also, right now there's no good way to have a dynamic "order by" clause -- in particular, it's impossible to choose whether the ordering is ascending or descending at runtime. So I would like to improve these situations.

I'm going to create some issues for planned features, seeking feedback on the designs I have in mind.

[rkosafo]

Ok. The pattern described works when using = but cannot be used when there are dynamic operators involved. The query therefore is almost static.

The expression currently looks this

type FilterExpr
  = Eq of field:string * value:obj
  | Gt of field:string * value:obj
  | Lt of field:string * value:obj
  | StartsWith of field:string * value:string
  | EndsWith of field:string * value:string
  | Contains of field:string * value:string
  | In of field:string * values:obj list
  | Not of field: string * value: obj

A parsed query looks like

[ Eq ("scope", "demo"); Gt ("id", 5)]

[rspeele]

Sounds like what you're doing is very dynamic. If #7 was done, I believe it would satisfy your use case.

Right now, you can cheat by using the dynamic API that the type provider wraps. It's not pretty though, and I won't promise that this API will be stable.

open Rezoom
open Rezoom.SQL
open Rezoom.SQL.Mapping
open System.Data

let cmdData =
    {   ConnectionName = "rzsql" // should match the one in rzsql.json/App.config
        Fragments =
            [|  CommandText "select * from MyTable where "
                CommandText "SomeColumn = "
                Parameter 0 // index into parameters array
                CommandText " and SomeOtherColumn = "
                Parameter 1 // index into parameters array
            |]
        // next parameters don't really matter if you're not using Rezoom plans
        // since there won't be batching/caching anyway. If you are using plans,
        // you might want to tweak these
        Identity = "my made-up command"
        DependencyMask = BitMask.Full
        InvalidationMask = BitMask.Full
        Cacheable = false
        ResultSetCount = None // not statically known
    }

let cmdParameters =
    [|  ScalarParameter(DbType.String, "first parameter")
        ScalarParameter(DbType.DateTime, DateTime.UtcNow)
    |]

type ResultShape = // write this type to match the result of the query
    {   SomeColumn : string
        SomeOtherColum : DateTime
    }

let cmd =
    CommandConstructor.Command1<ResultShape IReadOnlyList>(cmdData, cmdParameters)

[rkosafo]

Tried it and it works as expected. Will push it a bit more through various scenarios. Closing this in favour of #7.