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

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

A. Pagaltzis wrote:
> You???re asking the wrong question. Sessions are a bad idea in
> general; application state should live on the client, not the
> server. All state on the server should be resource state, ie it
> should have a URI of its own.

I also consider "normal" sessions evil. My subsessions are still ugly, but
at least they keep the state control in the client.

The subsessions were my answer, when I failed to setup the Chained
Dispatcher according to my laziness. And once available, subsessions
became the quick answer to other questions. Woken up by your feedback I'll
give the Chain another try (and actually it seems a lot easier having won
some Catalyst insight during the Subsession efforts).

So the "select a user once" is kind of solved.

Is there an elegant way to keep track of where the user came from? I
consider my Referer-based redirect after edit/delete/add actions a bit
clumsy.

Well, I could add edit/delete/add actions to each context to where I'd
like to call them:

/diary/<diary_id>/edit
/calendar/2007/9/30/diary/<diary_id>/edit
/calendar/2007/9/30/diary/add

But what about the "diary/add" link in my navbar? Does this mean I have to
put a ".../diary/add" action into each context?

And how shall I deal with GET/POST parameters of the referring context?
Say I want to call "/diary/search/<diary_id>/edit" from
/diary/search?notes="regexp"&daybegin="2007-09-01"

Is there a "best practice" for this? Is this something answered by the the
mentioned oReily book?

Rainer

Yuval Kogman wrote:
> sub session {
[...]
> return $self->NEXT::session->{$sub} ||= {};


Zbigniew Lukasiak wrote:
> On 9/27/07, Rainer Clasen <bj@zuto.de> wrote:
> > So I'm searching for something functional similar to:
> > http://paneris.net/cgi-bin/cvsweb.cgi/SubSession/SubSessions.html?rev=1.3
> >
>
> Interesting. I usually use GET parameters for that - but I can
> imagine where it would become not enough.

Thanks to both of you. Your responses helped a lot to sort my Ideas and
hack up my initial "poor man" / proof of concept solution:

- subsession ids are generated with c->generate_session_id from the
  Session Plugin.
- subsession data is stored as $c->session->{SUBSESSION}{$ssid}
- ssid is picked from $c->param
- uri_for automagically sets ssid
- all internal links are constructed with uri_for()
- change of subsession data doesn't yet cause a new ssid to be assigned.
  This comes later. Up to then it has to be done manually with
  subsession_next.
- You cannot change subsession data after you've shown the subsession id
  to the client (i.e. you've used uri_for).

So far this seems to work fine, but it needs a bit more testing (and
documentation).


Rainer

Hello,

there are currently two Problems I could easily solve with subsessions.

After saving some data I want to redirect the user back to where he came.
So I'd like to keep track where the user came from. As I expect the User
to use several Browser windows, neither Cookie based Sessions (incl.
stash) work in all scenarios. Right now I'm using the HTTP Referer, which
I'm also considering a bit clumsy.

Furtermore there are users who have access to other users' data. I'd like
them to select them *once* which user's data they want to work on and keep
this for the current browser window. Again I expect the user to use
multiple browser windows (say for working with multiple users' data at the
same time).

So I'm searching for something functional similar to:
http://paneris.net/cgi-bin/cvsweb.cgi/SubSession/SubSessions.html?rev=1.3

I've had no luck finding something similar for Catalyst. Well,
Catalyst::Plugin::Session::State::URI gives me some Ideas on how I could
implement this, but if possible I'd prefer to avoid re-inventing the
wheel (especially as I expect this to be non-trivial).

Rainer

On Sat, Sep 01, 2001 at 09:07:04AM +0200, Andre Schnabel wrote:
> The following select should do it:

Thanks! This is perfect. And I'm astonished how easy it is.


Rainer

Hi!

I'm currently thinking about building a completely database driven
jukebox. It will integrate with an already existing (well kind of) library
for my music files (and more).

This jukebox should offer a random play mode. It should take into account
when the title was played the last time. My Idea is to submit the query
returning the records ordered by their last time of play, ignore a random
number of records, fetch one record and drop the result set.

I'm wondering wether there is a more elegant way to fetch a random record
from a result.

Rainer