Rainer Clasen (b...@zuto.de)

Ah, that's similar to what I had before. Well, yours shows a bit more
experience ;) I haven't thought about indices, yet, and I auto-populated
the date table from a trigger (quite bad Idea as a user could fill my
disks by specifying a date that's waaaaaay in the future/past). Basically
I did:

SELECT date_trunc('week', day) AS day,
avg(value)
FROM dates s
  LEFT JOIN data d
ON s.day = d.day
GROUP BY day;

But I found no easy way to date_trunc() to user-specified time-steps like
"4 weeks" so I switched to using the stored procedure directly.

Seems I'll better go back to the old aproach and think a bit about indices
and a custom date_trunc/date_part/whatever.

Thanks a lot and sorry for turning things complicated (as I quite often
tend to :( )

Rainer

Hello,

within my current project, some value is collected up to once a day:

CREATE TABLE a_value {
  day date PRIMARY KEY,
  other_values integer NOT NULL,
  value integer
  another_value integer
);

Data comes in a bit sporadic - so I cannot rely each day having an entry.
Actually there also be longer periods (weeks/month/??) without data.

I'm currently a bit at a loss on how to "properly" cook up this data to
easily display it in fixed time steps. I'm thinking of a list of *all*
days/weeks/month/... in a certain timerange. Such a list would allow the
view easy access to present the data (say as html table with one row per
time step or as input for GD::Graph).

This means there are basically two tasks:
- aggregate the data for each time step: No-brainer with DBIx::Class.
- get NULL entries for time steps without data: The intersting part.

I can come up the following solutions to generate the NULL entries:

- use a SQL stored procedure or temp table with the start-dates of the
  desired time-steps, do an outer join and stuff this in a DBIC
  result_source as described in the DBIC cookbook under "arbitrary SQL".

  example query for ->name():
SELECT
  d.id,
  steps AS day,
  d.value,
  COALESCE( d.other_value, $4 ) AS other_value
FROM
  timeseries( $1, $2, $3) AS steps
  LEFT JOIN ( SELECT * FROM data WHERE other_value = $4 ) d
   ON ( d.day >= $2 AND d.day + $1 < $3;
  $1 = time steps. eg. '1 day'
  $2 = start date. eg. '2007-11-1'
  $3 = end date. eg '2007-11-30'
  $4 = other_value to filter on.
  timeseries(step,start,end) = stored procedure that returns the 
start-dates of the time-steps within the specified time-range.

  pro: 
  - also usable without catalyst / DBIC.
  - easy data aggregation
  - same interface as non-cooked data
  con: 
  - Outer joins tend to be slow.
  - the NULL entries have to be transfered from DB->application
  - the "arbitrary SQL" example makes me feel a bit sick.
  - the "WHERE other_value..." subquery needs to be different in certain
    cases.

- Catalyst Model, that creates a DBIC resultset and feeds it to a plain
  perl "aggregation module" (which returns the aggregated data in the
  desired time steps).

  pro:
  - aggregation module is quite easy to write
  -  "            "    is also usable without catalyst / DBIC
  - allows easy tweaking of the resultset
  con:
  - still a bit tricky to teach the DB about the time-steps for data
    aggregation (think of non trivial steps like '4 days').  
  - alternatively the aggregation has to be done "manually"
  - same applies for selecting the actual columns I want to
    retrieve/aggregate.

Which do you consider the more elegant way? Am I missing the "obvious" and
therefore trivial solution (as usual)? Am I completely nuts?

Rainer

With Chaining you have to specify where you actually chain to (even if
it's "."). With ":Local" you automgagically end up in the current
namespace, no need to bother making "." available.

> CaptureArgs you verify further up your chain, in a common method that just
> handles the <athlete> bit.

Correct, but it still has to be verified on each Request as it comes from
the user and cannot be trusted (in contrast do what the server keeps in
the session data).

> uri_for($action_object, \@captures, @args
>
> continues to work perfectly (it supports regex as well, even)

Of course it keeps working. But specifying the right @captures at each and
every place you use uri_for *IS* more hassle than overloading uri_for to
automagically include the subsession ID as query parameter once.

Well, all of these "issues" are by design - and of course they are far
away from show-stoppers. But as Catalyst is the "free choice" MVC
Framework, I was just wondering if there are alternatives. Actually I was
puzzled to see how easy I could implement subsessions on my own (what I
didn't plan to do).


BTW: Right now I'm wondering if one couldn't make uri_for easier to use if
one could pass a hash instead of @captures. An acction attribute could
specify which keys from this hash should be used. This would make changes
to the code / URI layout a lot more flexible and simplifies deriving URIs
from the current one.

action declaration:

sub foo :Chained('.') :NCaptureArgs(year,month,day) {}

uri construction:

$cap = $c->req->ncaptures; # or something similar stored in stash
$cap->{day}++;
uri_for( $foo_action_object, $cap, @args);

This is just an Idea. I don't actually expect you to like/comment/...
this.  :)


Rainer

With Chaining you have to specify where you actually chain to (even if
it's "."). With ":Local" you automgagically end up in the current
namespace, no need to bother making "." available.

> CaptureArgs you verify further up your chain, in a common method that just
> handles the <athlete> bit.

Correct, but it still has to be verified on each Request as it comes from
the user and cannot be trusted (in contrast do what the server keeps in
the session data).

> uri_for($action_object, \@captures, @args
>
> continues to work perfectly (it supports regex as well, even)

Of course it keeps working. But specifying the right @captures at each and
every place you use uri_for *IS* more hassle than overloading uri_for to
automagically include the subsession ID as query parameter once.

Well, all of these "issues" are by design - and of course they are far
away from show-stoppers. But as Catalyst is the "free choice" MVC
Framework, I was just wondering if there are alternatives. Actually I was
puzzled to see how easy I could implement subsessions on my own (what I
didn't plan to do).


BTW: Right now I'm wondering if one couldn't make uri_for easier to use if
one could pass a hash instead of @captures. An acction attribute could
specify which keys from this hash should be used. This would make changes
to the code / URI layout a lot more flexible and simplifies deriving URIs
from the current one.

action declaration:

sub foo :Chained('.') :NCaptureArgs(year,month,day) {}

uri construction:

$cap = $c->req->ncaptures; # or something similar stored in stash
$cap->{day}++;
uri_for( $foo_action_object, $cap, @args);

This is just an Idea. I don't actually expect you to like/comment/...
this.  :)


Rainer

well no. I looked at the Chained stuff and was distracted.

With the input from the list I've ditched the Subsessions and went for
Chained because it seems to be the "RightThing" - although Subsessions are
a quite comfortable solution: Once setup, they're available everywhere
within the application, no need to worry anymore.

That's quite the opposite of Chained. Nearly all URIs in this project look
like /<athlete>/diary/list or /<athlete>/exercise/add. This means all
actions have to be setup for chaining manually, CaptureArgs have to be
verified on each request, uri_for becomes a hassle to use and last but not
least the chance to break the natural URI to Controller namespace mapping
is quite likely.


Rainer