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

[Alex Kavanagh]

At Mon, 23 Jan 2006 15:58:49 -0600,
Brandon Black wrote:
> 
> > 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. 

Whoops, sorry about that.  I've joined the dbix-class list, but I
thought I'd do one final post here and then stop myself ...

> 
> 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.

Ah, I sort of thought that, but it's nice to have it confirmed! :-)  I
started of with the parent <-> child relationship (because I could
implement it) but it didn't describe what I wanted to do.

> 
> 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.

So, in essence, either hide the nasty stuff in SQL, or in the
database, or in a module, but either way there's no nice, easy way of
achieving this.  I was sort of hoping that this was a general case
that had been solved! :-)

I'll have to do some more research and look into whether I can pull
this off.  One problem is that I'm doing most of the work on a
laptop using SQLite but I plan to actually implement on MySQL and I
was trying to not be database specific if at all possible.  Perhaps I
should've mentioned that - sorry.

> 
> 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 :) ).

This kind of makes me think of the pros and cons of different ways of
designing apps.

I tend to go for check everything before trying to do something.
i.e. validate to death and then do the insert - any errors at this
point indicate either a disaster or a logic error.  The contrary
position is attempt the insert and pick up the pieces if that fails.

There *is* less work involved in the latter :-)

I do already have the UNIQUE on the column in the table (paranoid that
I am) and I was using D::FV so that I could give a nice error message
back to the user.  Perhaps I'm just making this too complicated; it's
not been unknown ...

Thanks again
Cheers
Alex.