[Catalyst] Usage of Catalyst::Plugin::Singleton

[Brandon Black]

> On 1/23/06, Alex Kavanagh <alex at tinwood.homelinux.org> wrote:
>
> 1st Question:
> -------------
>
> For a simple case, imagine that you have a table of Categories
> (columns 'id' and 'name').
>
> Now you want any Category to be related to any other category. Call
> that, say CategoryAssocs and give it two columes 'id1' and 'id2'.
>
> Now to relate any two categories you simply put the id of each
> category into id1 and id2.
>
> I couldn't work out how to get DBIx to do this for me and ensure that
> there were unique entries for relations.  i.e. for two ids (a) and (b)
> you don't get entries of (a,b) AND (b,a) in the table.
>
> Thus I wrote some code into the CategoryAssocs class to handle all
> this and just provided the meta functions (add, delete, search, etc.).
>
> How would you implement this?
>

Yes, we are getting a bit off topic here. 
dbix-class at lists.rawmode.org is a good place for these things, but a
lot of people in one list are in the other anyways.

You've touched on a rather messy issue there.  In the general sense,
SQL isn't very good at self-referential relationships (although it
does support them, only certain vendors have the extension to do
recursive queries to resolve through multiple self-referential links).
 And more specifically, it doesn't support "undirected m:n"
relationships very well at all, where the two sides of the
relationship have no specific differentiable meaning.

Or in other words, if CategoryAssocs's fields looked more like
"parent_id, child_id", then it would make sense: it would be a
directed relationship and you'd always be specifically looking for a
given Category's child, or parent.

But if there is no functional distinction between the two columns in
CategoryAssocs, you have the problem you've run into here.

One way to solve some of the issues back at the SQL level is to put a
Trigger function in your database that triggers on INSERTs and UPDATEs
of CategoryAssocs, and always re-arranges the two columns so that the
leftmost (id1) is the lowest numbered id.  This prevents duplicate
associations.  Then at some level (as a sql function, or perhaps a
specialized view, or perhaps in your model), you'll have to cover
doing something along the lines of "SELECT id1, id2 FROM
CategoryAssocs WHERE id1 = 'xxx' OR id2 = 'xxx'", and then filtering
the results to collapse the two columns into a single column of
associate categories, and filter 'xxx' itself from the results.

But AFAIK, there just is no clean general solution for this problem in
the SQL world in general, DBIx::Class specifics aside.  If someone
knows of one, speak up :)

> 2nd Question:
>
> I'm using Data::FormValidator to validate my forms and I find that I
> need to call a function in the model to check for a unique username
> (during an add user) for example.  i.e. I want to do all of the form
> checking in the $c->form method.
> [....]

I don't really use D::FV, so I'm not much help here, but my gut
feeling is that this constraint about unique usernames belongs in the
database rather than in D::FV.  I would probalby put a "UNIQUE"
constraint on the table itself in the database, then not bother
checking for uniqueness during D::FV, and instead check for a failed
insert due to violating the database-level unique constraint.  But
then again, I've been known to make shockingly bad design decisions at
times (just ask around here :) ).

> Thanks again for your help - it's definitely started me re-thinking
> the design decisions.
>

no prob :)

-- Brandon