Thread: [Haskelldb-users] Dynamic queries with HaskellDB

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

Hello,

        Does HaskellDB support dynamic queries, that is, queries which are
        generated at runtime based on some user input?

        Thanks in advanced,
        Keren.

In what way do you mean "dynamic"? Do you mean the tables & columns
used in the query, or the conditions (i.e. whatever is in the "where"
clause)?

In the first case, not easily. The columns in the result of a query
are carried in the type, which means you could only specify that at
runtime with some Template Haskell magic. In the second case, easily.

If you want to be more specific I'm sure I (or someone) can help you out.

2009/7/19 Keren Lenz <ker...@gm...>:
> Hello,
>
>         Does HaskellDB support dynamic queries, that is, queries which are
>         generated at runtime based on some user input?
>
>         Thanks in advanced,
>         Keren.
>
> ------------------------------------------------------------------------------
> Enter the BlackBerry Developer Challenge
> This is your chance to win up to $100,000 in prizes! For a limited time,
> vendors submitting new applications to BlackBerry App World(TM) will have
> the opportunity to enter the BlackBerry Developer Challenge. See full prize
> details at: http://p.sf.net/sfu/Challenge
> _______________________________________________
> Haskelldb-users mailing list
> Has...@li...
> https://lists.sourceforge.net/lists/listinfo/haskelldb-users
>
>

Hello,

2009/7/19 Keren Lenz <ker...@gm...>:
>         Does HaskellDB support dynamic queries, that is, queries which are
>         generated at runtime based on some user input?

HaskellDB query construction is carried out in a monad. Monads are first-class
in the sense that you can pass monadic computation to a function, return it
from a function, etc. So I guess you can support some dynamic queries easily:
actually, I used to invoke HaskellDB in GHCi toploop, that is, interactively and
dynamically. :-)

Can you provide an example of what you're trying to accomplish? Maybe we
could help you.

Cheers,
Artyom Shalkhakov.

Nope, that really wouldn't work with HaskellDB. I've CC'ed the list
but I am pretty sure on this one.

On Wed, Jul 22, 2009 at 7:16 AM, Keren Lenz<ker...@gm...> wrote:
> Thank you very much for your help.
> I'm comparing features of HaskellDB with those of other libraries/solutions
> for type safe generation of queries.
> Here is an example (in imperative pseudo code) of what I mean by dynamic
> query:
> Query generate(bool cond1, bool cond2) {
>     Colums c = null;
>     if(cond1)
>         c.add(col1);
>     if(cond3)
>         c.add(col2);
>     Query q = SELECT + c + FROM table_name;
>     return q;
> }
>
> That is, the selected columns are chosen based on runtime conditions.
> Is that possible with HaskellDB?
>
> Thanks again,
> Keren.
>
>
>
>
> On Mon, Jul 20, 2009 at 6:17 PM, Justin Bailey <jgb...@gm...> wrote:
>>
>> In what way do you mean "dynamic"? Do you mean the tables & columns
>> used in the query, or the conditions (i.e. whatever is in the "where"
>> clause)?
>>
>> In the first case, not easily. The columns in the result of a query
>> are carried in the type, which means you could only specify that at
>> runtime with some Template Haskell magic. In the second case, easily.
>>
>> If you want to be more specific I'm sure I (or someone) can help you out.
>>
>> 2009/7/19 Keren Lenz <ker...@gm...>:
>> > Hello,
>> >
>> >         Does HaskellDB support dynamic queries, that is, queries which
>> > are
>> >         generated at runtime based on some user input?
>> >
>> >         Thanks in advanced,
>> >         Keren.
>> >
>> >
>> > ------------------------------------------------------------------------------
>> > Enter the BlackBerry Developer Challenge
>> > This is your chance to win up to $100,000 in prizes! For a limited time,
>> > vendors submitting new applications to BlackBerry App World(TM) will
>> > have
>> > the opportunity to enter the BlackBerry Developer Challenge. See full
>> > prize
>> > details at: http://p.sf.net/sfu/Challenge
>> > _______________________________________________
>> > Haskelldb-users mailing list
>> > Has...@li...
>> > https://lists.sourceforge.net/lists/listinfo/haskelldb-users
>> >
>> >
>
>

Hi,

2009/7/22 Keren Lenz <ker...@gm...>:
> Here is an example (in imperative psudo code of what I mean by dynamic
> query:
> Query generate(bool cond1, bool cond2) {
>     Colums c = null;
>     if(cond1)
>         c.add(col1);
>     if(cond3)
>         c.add(col2);
>     Query q = SELECT + c + FROM table_name;
>     return q;
> }

> That is, the selected columns are chosen based on runtime conditions.
> Is that possible with HaskellDB?

I would like to stress how HaskellDB works. You are given atomic building
blocks (relations, tuples and fields) and some combinators ("glue" that helps
you combine your building blocks: relational and query operations). What you
are really doing with HaskellDB is manipulating an abstract syntax tree of a DSL
embedded in Haskell.

A simple example would be something along these lines [1]:

> q val = do
>             s <- table stock
>             restrict (fromNull (constant "") (s!company_name)
>                    `like` constant ("%"++val++"%"))
>             r <- project (company_name << s!company_name #
>                     ticker << s!ticker)
>             return r

It is already somewhat "dynamic". Can we do anything to make it more "dynamic"?
Yes, we can:

> q flds val = do
>           s <- table stock
>           restrict (fromNull (constant "") (s!company_name)
>                  `like` constant ("%"++val++"%"))
>           r <- project (flds s)
>           return r

where flds is simply

> flds s = company_name << s!company_name # ticker << s!ticker)

So now you can do:

> flds cond1 cond2 s = let a = if cond1
>                                                     then (vendor_name << s!vendor_name) # fld
>                                                     else fld
>                                         b = if cond2
>                                                     then (customer_id << s!customer_id) # a
>                                                     else a
>                                    in b
>          where
>              fld = company_name << s!company_name # ticker << s!ticker

(don't forget to change the definition of q!)

As the number of conditions grows, this style becomes very inconvenient to
program in, so you'd need a writer monad over a monoid or something I suppose.

Also, I would like to note that I haven't tested the code given above,
so I don't
know if there are any bugs or not. But feel free to ask if there are,
anyway. :-)

Cheers,
Artyom Shalkhakov.

[1] http://pseudofish.com/blog/2008/05/11/haskelldb-basics/

I don't think the code below can compile. One important aspect of
HaskellDB is that the "shape" of the query is shown in the *type* of
the query.

Looking at your example:

>> q val = do
>>             s <- table stock
>>             restrict (fromNull (constant "") (s!company_name)
>>                    `like` constant ("%"++val++"%"))
>>             r <- project (company_name << s!company_name #
>>                     ticker << s!ticker)
>>             return r

The type of q would be based on the columns projected. Assuming
company_name and ticker are both strings, you would get

   q :: Query (Rel (RecCons (CompanyName (Expr String)) (RecCons
(Ticker (Expr String)) RecNil)

That is, a type-level tuple which indicates that the query has two
columns, CompanyName and Ticker, both with type String.

Simplying your next example a little bit, I can show that it won't type check:

>> flds cond2 s = let a = (vendor_name << s!vendor_name) # fld
>>                           b = if cond2
>>                                  then (customer_id << s!customer_id) # a
>>                                  else a
>>                           in b
>>          where
>>              fld = company_name << s!company_name # ticker << s!ticker

What is the type of b? We have to look at the query created. When
cond2 is true, then b is:

  (customer_id << s!customer_id) #  (vendor_name << s!vendor_name) #
company_name << s!company_name # ticker << s!ticker

However, if cond2 is false then b is:

  (vendor_name << s!vendor_name) # company_name << s!company_name #
ticker << s!ticker

If cond2 is true, then the type of b can determined by the query shape:

  b :: RecCons (CustomerId (Expr Int)) (RecCons (VendorName (Expr
String)) (RecCons (CompanyName (Expr String) (...))

However, if cond2 is false then the type is

  b :: RecCons (VendorName (Expr String)) (RecCons (CompanyName (Expr
String) (...))

Since the type of the "then" and "else" arms need to agree, this won't
compile. Its like writing

  b = if True then "a" else 1

On Thu, Jul 23, 2009 at 1:37 AM, Artyom
Shalkhakov<art...@gm...> wrote:
> Hi,
>
> 2009/7/22 Keren Lenz <ker...@gm...>:
>> Here is an example (in imperative psudo code of what I mean by dynamic
>> query:
>> Query generate(bool cond1, bool cond2) {
>>     Colums c = null;
>>     if(cond1)
>>         c.add(col1);
>>     if(cond3)
>>         c.add(col2);
>>     Query q = SELECT + c + FROM table_name;
>>     return q;
>> }
>
>
>
>> That is, the selected columns are chosen based on runtime conditions.
>> Is that possible with HaskellDB?
>
> I would like to stress how HaskellDB works. You are given atomic building
> blocks (relations, tuples and fields) and some combinators ("glue" that helps
> you combine your building blocks: relational and query operations). What you
> are really doing with HaskellDB is manipulating an abstract syntax tree of a DSL
> embedded in Haskell.
>
> A simple example would be something along these lines [1]:
>
>> q val = do
>>             s <- table stock
>>             restrict (fromNull (constant "") (s!company_name)
>>                    `like` constant ("%"++val++"%"))
>>             r <- project (company_name << s!company_name #
>>                     ticker << s!ticker)
>>             return r
>
> It is already somewhat "dynamic". Can we do anything to make it more "dynamic"?
> Yes, we can:
>
>> q flds val = do
>>           s <- table stock
>>           restrict (fromNull (constant "") (s!company_name)
>>                  `like` constant ("%"++val++"%"))
>>           r <- project (flds s)
>>           return r
>
> where flds is simply
>
>> flds s = company_name << s!company_name # ticker << s!ticker)
>
> So now you can do:
>
>> flds cond1 cond2 s = let a = if cond1
>>                                                     then (vendor_name << s!vendor_name) # fld
>>                                                     else fld
>>                                         b = if cond2
>>                                                     then (customer_id << s!customer_id) # a
>>                                                     else a
>>                                    in b
>>          where
>>              fld = company_name << s!company_name # ticker << s!ticker
>
> (don't forget to change the definition of q!)
>
> As the number of conditions grows, this style becomes very inconvenient to
> program in, so you'd need a writer monad over a monoid or something I suppose.
>
> Also, I would like to note that I haven't tested the code given above,
> so I don't
> know if there are any bugs or not. But feel free to ask if there are,
> anyway. :-)
>
> Cheers,
> Artyom Shalkhakov.
>
> [1] http://pseudofish.com/blog/2008/05/11/haskelldb-basics/
>
> ------------------------------------------------------------------------------
> _______________________________________________
> Haskelldb-users mailing list
> Has...@li...
> https://lists.sourceforge.net/lists/listinfo/haskelldb-users
>

Hi Justin,

It's a pity I can't test the code ATM, but isn't there a way
to find "the most general type" of the query?

Anyway, thanks for clarifying that. Next time, I will have to check
the code I post.

2009/7/23 Justin Bailey <jgb...@gm...>:
> Looking at your example:
>
>>> q val = do
>>>             s <- table stock
>>>             restrict (fromNull (constant "") (s!company_name)
>>>                    `like` constant ("%"++val++"%"))
>>>             r <- project (company_name << s!company_name #
>>>                     ticker << s!ticker)
>>>             return r
>
>
> The type of q would be based on the columns projected. Assuming
> company_name and ticker are both strings, you would get
>
>   q :: Query (Rel (RecCons (CompanyName (Expr String)) (RecCons
> (Ticker (Expr String)) RecNil)
>
> That is, a type-level tuple which indicates that the query has two
> columns, CompanyName and Ticker, both with type String.
>
> Simplying your next example a little bit, I can show that it won't type check:
>
>>> flds cond2 s = let a = (vendor_name << s!vendor_name) # fld
>>>                           b = if cond2
>>>                                  then (customer_id << s!customer_id) # a
>>>                                  else a
>>>                           in b
>>>          where
>>>              fld = company_name << s!company_name # ticker << s!ticker
>
> What is the type of b? We have to look at the query created. When
> cond2 is true, then b is:
>
>  (customer_id << s!customer_id) #  (vendor_name << s!vendor_name) #
> company_name << s!company_name # ticker << s!ticker
>
> However, if cond2 is false then b is:
>
>  (vendor_name << s!vendor_name) # company_name << s!company_name #
> ticker << s!ticker
>
> If cond2 is true, then the type of b can determined by the query shape:
>
>  b :: RecCons (CustomerId (Expr Int)) (RecCons (VendorName (Expr
> String)) (RecCons (CompanyName (Expr String) (...))
>
> However, if cond2 is false then the type is
>
>  b :: RecCons (VendorName (Expr String)) (RecCons (CompanyName (Expr
> String) (...))
>
> Since the type of the "then" and "else" arms need to agree, this won't
> compile. Its like writing
>
>  b = if True then "a" else 1
>
> On Thu, Jul 23, 2009 at 1:37 AM, Artyom
> Shalkhakov<art...@gm...> wrote:
>> Hi,
>>
>> 2009/7/22 Keren Lenz <ker...@gm...>:
>>> Here is an example (in imperative psudo code of what I mean by dynamic
>>> query:
>>> Query generate(bool cond1, bool cond2) {
>>>     Colums c = null;
>>>     if(cond1)
>>>         c.add(col1);
>>>     if(cond3)
>>>         c.add(col2);
>>>     Query q = SELECT + c + FROM table_name;
>>>     return q;
>>> }
>>
>>
>>
>>> That is, the selected columns are chosen based on runtime conditions.
>>> Is that possible with HaskellDB?
>>
>> I would like to stress how HaskellDB works. You are given atomic building
>> blocks (relations, tuples and fields) and some combinators ("glue" that helps
>> you combine your building blocks: relational and query operations). What you
>> are really doing with HaskellDB is manipulating an abstract syntax tree of a DSL
>> embedded in Haskell.
>>
>> A simple example would be something along these lines [1]:
>>
>>> q val = do
>>>             s <- table stock
>>>             restrict (fromNull (constant "") (s!company_name)
>>>                    `like` constant ("%"++val++"%"))
>>>             r <- project (company_name << s!company_name #
>>>                     ticker << s!ticker)
>>>             return r
>>
>> It is already somewhat "dynamic". Can we do anything to make it more "dynamic"?
>> Yes, we can:
>>
>>> q flds val = do
>>>           s <- table stock
>>>           restrict (fromNull (constant "") (s!company_name)
>>>                  `like` constant ("%"++val++"%"))
>>>           r <- project (flds s)
>>>           return r
>>
>> where flds is simply
>>
>>> flds s = company_name << s!company_name # ticker << s!ticker)
>>
>> So now you can do:
>>
>>> flds cond1 cond2 s = let a = if cond1
>>>                                                     then (vendor_name << s!vendor_name) # fld
>>>                                                     else fld
>>>                                         b = if cond2
>>>                                                     then (customer_id << s!customer_id) # a
>>>                                                     else a
>>>                                    in b
>>>          where
>>>              fld = company_name << s!company_name # ticker << s!ticker
>>
>> (don't forget to change the definition of q!)
>>
>> As the number of conditions grows, this style becomes very inconvenient to
>> program in, so you'd need a writer monad over a monoid or something I suppose.
>>
>> Also, I would like to note that I haven't tested the code given above,
>> so I don't
>> know if there are any bugs or not. But feel free to ask if there are,
>> anyway. :-)
>>
>> Cheers,
>> Artyom Shalkhakov.
>>
>> [1] http://pseudofish.com/blog/2008/05/11/haskelldb-basics/
>>
>> ------------------------------------------------------------------------------
>> _______________________________________________
>> Haskelldb-users mailing list
>> Has...@li...
>> https://lists.sourceforge.net/lists/listinfo/haskelldb-users
>>
>

Yes, don't declare a type for the query, let the type checker infer it.

/Bjorn

2009/7/24 Artyom Shalkhakov <art...@gm...>:
> Hi Justin,
>
> It's a pity I can't test the code ATM, but isn't there a way
> to find "the most general type" of the query?
>
> Anyway, thanks for clarifying that. Next time, I will have to check
> the code I post.
>
> 2009/7/23 Justin Bailey <jgb...@gm...>:
>> Looking at your example:
>>
>>>> q val = do
>>>>             s <- table stock
>>>>             restrict (fromNull (constant "") (s!company_name)
>>>>                    `like` constant ("%"++val++"%"))
>>>>             r <- project (company_name << s!company_name #
>>>>                     ticker << s!ticker)
>>>>             return r
>>
>>
>> The type of q would be based on the columns projected. Assuming
>> company_name and ticker are both strings, you would get
>>
>>   q :: Query (Rel (RecCons (CompanyName (Expr String)) (RecCons
>> (Ticker (Expr String)) RecNil)
>>
>> That is, a type-level tuple which indicates that the query has two
>> columns, CompanyName and Ticker, both with type String.
>>
>> Simplying your next example a little bit, I can show that it won't type check:
>>
>>>> flds cond2 s = let a = (vendor_name << s!vendor_name) # fld
>>>>                           b = if cond2
>>>>                                  then (customer_id << s!customer_id) # a
>>>>                                  else a
>>>>                           in b
>>>>          where
>>>>              fld = company_name << s!company_name # ticker << s!ticker
>>
>> What is the type of b? We have to look at the query created. When
>> cond2 is true, then b is:
>>
>>  (customer_id << s!customer_id) #  (vendor_name << s!vendor_name) #
>> company_name << s!company_name # ticker << s!ticker
>>
>> However, if cond2 is false then b is:
>>
>>  (vendor_name << s!vendor_name) # company_name << s!company_name #
>> ticker << s!ticker
>>
>> If cond2 is true, then the type of b can determined by the query shape:
>>
>>  b :: RecCons (CustomerId (Expr Int)) (RecCons (VendorName (Expr
>> String)) (RecCons (CompanyName (Expr String) (...))
>>
>> However, if cond2 is false then the type is
>>
>>  b :: RecCons (VendorName (Expr String)) (RecCons (CompanyName (Expr
>> String) (...))
>>
>> Since the type of the "then" and "else" arms need to agree, this won't
>> compile. Its like writing
>>
>>  b = if True then "a" else 1
>>
>> On Thu, Jul 23, 2009 at 1:37 AM, Artyom
>> Shalkhakov<art...@gm...> wrote:
>>> Hi,
>>>
>>> 2009/7/22 Keren Lenz <ker...@gm...>:
>>>> Here is an example (in imperative psudo code of what I mean by dynamic
>>>> query:
>>>> Query generate(bool cond1, bool cond2) {
>>>>     Colums c = null;
>>>>     if(cond1)
>>>>         c.add(col1);
>>>>     if(cond3)
>>>>         c.add(col2);
>>>>     Query q = SELECT + c + FROM table_name;
>>>>     return q;
>>>> }
>>>
>>>
>>>
>>>> That is, the selected columns are chosen based on runtime conditions.
>>>> Is that possible with HaskellDB?
>>>
>>> I would like to stress how HaskellDB works. You are given atomic building
>>> blocks (relations, tuples and fields) and some combinators ("glue" that helps
>>> you combine your building blocks: relational and query operations). What you
>>> are really doing with HaskellDB is manipulating an abstract syntax tree of a DSL
>>> embedded in Haskell.
>>>
>>> A simple example would be something along these lines [1]:
>>>
>>>> q val = do
>>>>             s <- table stock
>>>>             restrict (fromNull (constant "") (s!company_name)
>>>>                    `like` constant ("%"++val++"%"))
>>>>             r <- project (company_name << s!company_name #
>>>>                     ticker << s!ticker)
>>>>             return r
>>>
>>> It is already somewhat "dynamic". Can we do anything to make it more "dynamic"?
>>> Yes, we can:
>>>
>>>> q flds val = do
>>>>           s <- table stock
>>>>           restrict (fromNull (constant "") (s!company_name)
>>>>                  `like` constant ("%"++val++"%"))
>>>>           r <- project (flds s)
>>>>           return r
>>>
>>> where flds is simply
>>>
>>>> flds s = company_name << s!company_name # ticker << s!ticker)
>>>
>>> So now you can do:
>>>
>>>> flds cond1 cond2 s = let a = if cond1
>>>>                                                     then (vendor_name << s!vendor_name) # fld
>>>>                                                     else fld
>>>>                                         b = if cond2
>>>>                                                     then (customer_id << s!customer_id) # a
>>>>                                                     else a
>>>>                                    in b
>>>>          where
>>>>              fld = company_name << s!company_name # ticker << s!ticker
>>>
>>> (don't forget to change the definition of q!)
>>>
>>> As the number of conditions grows, this style becomes very inconvenient to
>>> program in, so you'd need a writer monad over a monoid or something I suppose.
>>>
>>> Also, I would like to note that I haven't tested the code given above,
>>> so I don't
>>> know if there are any bugs or not. But feel free to ask if there are,
>>> anyway. :-)
>>>
>>> Cheers,
>>> Artyom Shalkhakov.
>>>
>>> [1] http://pseudofish.com/blog/2008/05/11/haskelldb-basics/
>>>
>>> ------------------------------------------------------------------------------
>>> _______________________________________________
>>> Haskelldb-users mailing list
>>> Has...@li...
>>> https://lists.sourceforge.net/lists/listinfo/haskelldb-users
>>>
>>
>
> ------------------------------------------------------------------------------
> _______________________________________________
> Haskelldb-users mailing list
> Has...@li...
> https://lists.sourceforge.net/lists/listinfo/haskelldb-users
>