Multiple Databases in Catalyst

[code-ninja]

ok... apologies for yet another Catalyst doubt but I'm actually learning stuff these days... so, you know.

Anyways, I'm trying to connect to multiple databases depending on the user, for example: if user_1 logs in to my application, db_user_1_id should be automatically selected. Also, if a new user signs-up with my application, I'll create a model on the fly . To do this, I've taken the following approach, is there a better way?

My approach

1. If a new user signs-up, run a script (a bash or Perl script) that creates a MySql database for the new user. I'll call this new database DB_new_user. Now, I run another script that creates a model using the catalyst's helper script like:

$ script/myapp_create.pl model DB_new_user's_id DBIC::Schema myapp::Schema create=static dbi:mysql:DB_new_user '<Mysql username>' '<Mysql password>'

2. If an existing user (I'll call this user, user_n) logs in, a catalyst controller (say, `messages') can then select a specific database by saying:

sub messages :Local {
    my($self, $c) = @_;
    # somehow fetch user_n's id
    $c->stash(messages => [$c->model('DB_user_n::Messages')->all]);
}

The codes shown are not necessarily correct, they are just an abstraction of what I'm doing/trying to achieve. :)

Update Just going through the Catalyst book again and it does say: If you need to use a different database, you can change the connection information in one file, restart your application, and the change will take effect everywhere, automatically.

No way... I love the concept of TIMTOWTDI in Perl. Still working on this.

[Jenda]

So when you find out you need to add something to the schema, you end up having to make a change in many databases, right? Don't do this unless you have some very very specific needs!

[code-ninja]

I'll elucidate my project a bit. I'm trying to make a small "social network" for my particular dormitory in my college. I'm taking this approach because this way, every user will have a personalized database and hence I can write terse controller scripts that are generic, if you understand what I mean. :/

I considered using LDAP too because the starting chapter of the Catalyst book says that I can use LDAP for handling multiple databases, but AFAIK, LDAP is only for directories right?

[sundialsvc4]

I personally think that this would be very hard to pull off.   (If your web site goes viral and suddenly has 2.5 million users, it’s gonna be really hard to pull off.)   Your proposed solution is not only serious over-kill, in my humble, but it does not scale up.   At all.

Pragmatically speaking, you need to include a user_id column in every table, then index that column, and then design the data-model layer (perhaps using some kind of base class?) to be certain that user_id is always part of the requisite criteria that is always used for those tables and/or views.   A base-class could “elegantly” ensure that user_id is always part of any where clause, and always inserted as a non-NULL value in inserts.

[code-ninja]

Yea, I asked the same doubt on the Catalyst's IRC and they were all saying similar things. My model doesn't scale well: <quote>S'pose you have a million users one day and you need to change some table, then you'll have to make changes that would become a nightmare</quote> someone said that and I couldn't agree more... anyways, back to one schema with many tables. Its easier that way. Thanks for the help monk(s) :).