I think I went off the deep end in trying to understand what you were
saying Frans. After looking at your examples and re-reading what
you're saying, it seems much simpler than what I thought you meant.
It now sounds like the function definition is basically just the
pattern. Any {x} members are just replaced by whatever was in that
argument slot, be they other function calls, parameters, subqueries,
or constants. Is that right now?
Interestingly, I had assumed that was exactly what SQLFunctionTemplate
already did. It doesn't seem very intuitive that I can't make a
template like (?1 + ?1 = ?2) where it basically just replaces the ?x
parameters with whatever was generated by the original function
arguments.
As suggested, I've produced a unit test patch that demonstrates the
failure using the existing trim function.
http://216.121.112.228/browse/NH-2318
Though I haven't analyzed it much, perhaps it would be possible to
reuse ISQLFunction by passing a special set of placeholder args like ?
1, ?2, and ?3 to ISQLFunction.Render. The result would then be post-
processed and the placeholders could be replaced with whatever was
supposed to be there.
Patrick Earl
On Aug 30, 4:43 am, "Frans Bouma" <[email protected]> wrote:
> > I'm trying to understand the template definition described here.
> > Would it be the case that the template defines the format string and a
> list
> > of "extra objects" that are utilized by the code processing the template?
>
> > For example, a template for IsTrue(x) might be:
> > {0} = :trueValue
> > Then, the list of objects would have just a named parameter:
> > NamedParam("trueValue", true)
>
> > Is this what was meant?
>
> No, you specify the SQL fragment and everywhere where you need to
> specify something that's produced by the query producer, e.g. parameter, or
> another function call (so you can nest function calls) you specify a {x}
> placeholder, where x is the index in the list of objects contained by the
> function definition.
>
>
>
> > Regarding the comments on Linq... would the idea be to use this same
> > definition style to convert from a particular LINQ signature into a
> > particular HQL signature? It basically allows you to define the mapping
> in
> > a similar way to the conversion from HQL to SQL?
>
> It's a way, previously discussed here, where you define a .NET
> method which is mapped onto a piece of sql (or HQL, whatever rocks your
> boat).
>
> FB
>
>
>
>
>
>
>
> > Sorry if I'm slow in understanding.
>
> > Patrick Earl
>
> > On Aug 29, 3:20 am, "Frans Bouma" <[email protected]> wrote:
> > > > I think the original intention for ISQLFunction was to call
> > > > (dialect-
> > > > specific) user-defined functions (hence the name), not to create
> > > > arbitrary snippets of SQL. Since the implementation has used
> > > > positional parameters
> > > I
> > > > don't think you can generate the SQL you're wanting.
>
> > > > I think the two options you suggested are viable: Create a new
> > > > interface/implementation that allows arbitrary hand-cut SQL from a
> > > parameter
> > > > list, or change the current implementation to use named parameters
> > > > (which would only be needed in this specific case, and we'd need to
> > > > be careful to generate a unique parameter name for each instance of
> > > > the function call within a single query). Either one could be added
> > > > to JIRA as a potential improvement.
>
> > > > I'm guessing (but I haven't tried it) that a simpler option might be
> > > > to
> > > just
> > > > render the actual parameter values into the SQL string (i.e., not
> > > > parameterised). Notwithstanding that the simplest option, if it's
> > > > open to you, is to create a user-defined function in the database
> > > > that you can
>
> > > call.
>
> > > One solution could be similar to what console.writeline does
> > > and what I do in my framework as well: use {} placeholders. So if
> > > someone wants to call a function Foo(@a, @b), they specify the
> > > function as Foo({0}, {1}), and the function definition object has
> > > besides this string also a list of objects, which are transformed into
> > > parameters or pieces of this string (e.g. values, another function,
> etc.).
>
> > > This way, the dialect or at least the part where db specific
> > > parameters are created, can deal with the parameter creation by
> > > transforming the list of objects in the function definition object to
> > > parameters or snippets, place them back in the list of objects and a
> > > generic piece of code can then transform the function definition
> > > object to a SQL snippet. So if you have to re-use a parameter:
> > "FooBar({0}, {1}) <> {0}", you can.
>
> > > This system is also usable for creating case statements and
> > > for example function mappings in linq.
>
> > > FB
>
> > > > Regards,
> > > > Richard
>
> > > > From: Fabio Maulo <mailto:[email protected]>
> > > > Sent: Sunday, August 29, 2010 5:05 AM
> > > > To: [email protected]
> > > > Subject: Re: [nhibernate-development] Re: ISQLFunction with
> > > > Repeated Arguments
>
> > > > Which is the problem you have found implementing
> > > > NullSafeEquals(p1,p2) ?
>
> > > > On Sat, Aug 28, 2010 at 6:13 PM, Patrick Earl <[email protected]>
> > > > wrote:
>
> > > > Thanks for your feedback Fabio. The function would be
> > > similar
> > > > to
> > > > EqOrNullExpression but would offer the following additional
> > > > functionality:
>
> > > > 1. It would work for database columns and subselects as
> > > well,
> > > > not
> > > > just parameters passed from .NET.
> > > > 3. The HQL function would make the code easy to read and
> > > > eliminate
> > > > the possibility of error in this area where errors are
> > > common.
> > > > 2. The function could easily be mapped from a LINQ
> > > > method call.
>
> > > > For example, NullSafeEquals(x.Prop1, x.Prop2). I see
> > > > that a method
> > > > similar to EqOrNullExpression could be implemented to
> > > provide
> > > > support
> > > > for comparing projections in the criteria API. However,
> > > > my immediate
> > > > problem was HQL based, and I would like this to be
> > > > solvable with LINQ
> > > > as well. Alternatives include implementing something
> > > > like
> > > "IS
> > > > DISTINCT FROM" as a built-in HQL operation and then
> > > > having a LINQ
> > > > method map to that type of node. The dialect would then
> > > > specifically
> > > > handle the SQL implementation using the SQL-99 IS [NOT]
> > > > DISTINCT FROM,
> > > > MySQL <=>, or the complicated clause as needed.
>
> > > > Patrick Earl
>
> > > > On Aug 28, 1:13 pm, Fabio Maulo <[email protected]>
> > > wrote:
> > > > > Personally I didn't understand which is the function
> > > > you
> > > are
> > > > needing.
> > > > > Can you explain a usecase ?
>
> > > > > In criteria base queries with a property compared to a
> > > > parameter that
> > > > > function is unneeded... perhaps you are thinking
> > > > something else than
> > > > > EqOrNullExpression... I'll wait your explication.
>
> > > > > On Sat, Aug 28, 2010 at 4:02 PM, Patrick Earl
> > > > <[email protected]> wrote:
> > > > > > Solution #1 (duplicate positional parameters based on
> > > > template
> > > > > > contents) looks to be somewhat complicated to
> implement.
> > > > > > Solution #2 (manipulate HQL AST and parameter list)
> > > > doesn't work for
> > > > > > ICriteria / QueryOver.
> > > > > > Solution #3 (replace positional parameters with
> > > > named) seems
> > > > > > potentially viable.
>
> > > > > > I have two "big" questions:
>
> > > > > > 1. Are there cases where positional parameters are
> > > > required, or is it
> > > > > > reasonable to always pre-process positional
> > > > parameters into named
> > > > > > parameters? Along with this, what kind of parameters
> > > > do the criteria
> > > > > > API and Linq generate? Are they always named
> > > parameters?
> > > > > > 2. Why is it required that the type of a null
> > > > parameter is known. In
> > > > > > other words, why is it necessary to call SetParameter
> > > with
> > > > a type
> > > > > > overload? Is there any way to say "put NULL in the
> > > > DB query and
> > > > > > disregard the type?"
>
> > > > > > I'll do some digging, but if anyone knows this stuff
> > > off-
> > > > hand, that'd
> > > > > > be great to know.
>
> > > > > > Patrick Earl
>
> > > > > > On Aug 28, 2:55 am, Patrick Earl <[email protected]>
> > > wrote:
> > > > > > > I'm interested in implementing a function to
> > > > duplicate the behavior of
> > > > > > > "IS NOT DISTINCT FROM" on SQL Server. On
> > > > PostgreSQL, I'd simply use ?
> > > > > > > 1 IS NOT DISTINCT FROM ?2 as the function template.
> > > On
> > > > SQL Server
> > > > > > > though, the template involves repeating the same
> > > > argument: (?1 IS NULL
> > > > > > > AND ?2 IS NULL) OR (?1 IS NOT NULL AND ?2 IS NOT
> > > > NULL AND ?1 = ?2)
>
> > > > > > > Unfortunately, when used with positional
> > > > parameters,
> > > the
> > > > function
> > > > > > > template is not processed correctly. The system
> > > > duplicates the ?
> > > > > > > within the query, but it doesn't add another copy
> > > > of
> > > the
> > > > parameter to
> > > > > > > the parameter list. Here are some possible
> > > > techniques for dealing
> > > > > > > with this issue:
>
> > > > > > > 1. Create another interface like
> > > > ISQLParameterizedFunction that
> > > > > > > allows modification of the parameter list as well.
> > > > > > > 2. Allow dialects to access the HQL AST and
> > > > parameter list and modify
> > > > > > > them directly.
> > > > > > > 3. Replace the positional parameters with named
> > > > parameters.
>
> > > > > > > Sadly, I'm too tired for my brain to work properly.
> > > > I just wanted to
> > > > > > > get these thoughts out there to see if anyone had
> > > > any feedback or work-
> > > > > > > arounds.
>
> > > > > > > Patrick Earl
>
> > > > > --
> > > > > Fabio Maulo
>
> > > > --
> > > > Fabio Maulo
