ErlSQL has the expression 'call' for function calls in SQL. So,
{select, {call, foo, [1,2,3]}}
would be translated to
"SELECT foo(1,2,3)"
I think we should use it and add another expression on top of it as a
special case for date-related functions so they can be translated to
the proper dialiect. E.g.
{select, {date_part, year, created}}
would be translated to the intermediate
{select, {call, year, [created]}} (in MySQL) or
{select, {call, date_part, [<<"year">>, created]}} (in Postgres).
It would be an easy change to make. Without changing any ErlyDB code,
you could use this expression in SELECTs as follows:
entry:find({{date_part, year, created},'=',2008}).
This would call "SELECT * FROM entry WHERE year(created) = 2008".
Would that satisfy your requirements?
Btw, using list_to_atom is dangerous because atoms aren't garbage
collected. Although in this case the number of possible atoms is
limited, it's still better to avoid list_to_atom.
Yariv
On Jan 9, 2008 11:54 AM, maddiin <[EMAIL PROTECTED]> wrote:
>
> Just want to know how this looks to you, if I should move on or if its
> just weird. At least you know now what I try to achieve and I would be
> happy to see something the like in ErlyWeb, as archive pages are a
> common use case (blog, news-sites, etc).
>
> It generates queries like (for mysql):
>
> 1>entry:find_date(created, [year]).
> mysql_conn:426: fetch <<"SELECT DISTINCT year(created) FROM entry">>
> (id <0.73.0>)
> [{entry,false,2007},{entry,false,2008}]
> 2>entry:find_date(created, ['YEAR','MONTH']).
> mysql_conn:426: fetch <<"SELECT DISTINCT YEAR(created),MONTH(created)
> FROM entry">> (id <0.73.0>)
> [{entry,false,2007,12},{entry,false,2008,1}]
>
> For postgres a query would look like:
> <<"SELECT DISTINCT
> date_part('year',created),date_part('month',created),date_part('day',created)
> FROM entry">>
>
> find_date(Module, Field, Dates, Where, Extras) ->
> do_find_date(Module, Field, Dates, Where, Extras).
>
> do_find_date(Module, Field, Dates, Where, Extras) ->
> do_find_date(Module, Field, Dates, Where, Extras, true).
>
> do_find_date(Module, Field, Dates, Where, Extras, AsModule) ->
> select(Module, make_find_query_for_date(Module, Field, Dates,
> Where, Extras), AsModule).
>
> make_find_query_for_date(Module, Field, Dates, Where, Extras) ->
> {Driver,_} = Module:driver(),
> case Driver of
> erlydb_mysql ->
> {esql,
> {select, distinct, [list_to_atom(atom_to_list(Date)++"("+
> +atom_to_list(Field)++")") || Date <- Dates], {from,
> db_table(Module)},
> make_where_expr(Module, Where),
> Extras}};
> erlydb_psql ->
> {esql,
> {select, distinct, [list_to_atom("date_part('"+
> +atom_to_list(Date)++"',"++atom_to_list(Field)++")") || Date <-
> Dates], {from, db_table(Module)},
> make_where_expr(Module, Where),
> Extras}};
> erlydb_mnesia ->
> mnesia_not_yet_added;
> _ ->
> unknown_driver
> end.
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"erlyweb" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/erlyweb?hl=en
-~----------~----~----~----~------~----~------~--~---
