Forums

Separate DBs for site and user

Permalink
I'm building these sites

http://yokosonews.com/
http://japanexp.org/
http://entertainmenttoday.net/ (currently Joomla)

with concrete5.

Each site will consists of more than 20 page types with more than 30 different types of permissions.

I was thinking about using Domain Mapper... However it will be 60 page types within a site.... it will be pain to maintain.. and won't be really stable.

So I started to think about building a separate DB just for users...

In config/site.php

I've added the following constants

define('DB_USER_SERVER', 'SERVER');
define('DB_USER_USERNAME', 'USER');
define('DB_USER_PASSWORD', 'PASSWORD');
define('DB_USER_DATABASE', 'DATABASE');



And changed all of user related model from

$db = Loader::db();

to

$db = Loader::db(DB_USER_SERVER, DB_USER_USERNAME, DB_USER_PASSWORD, DB_USER_DATABASE, true);

wherever appropriate, and added

$db = Loader::db(null, null, null, null, true);

to reverse the DB setting.

For example, recordLogin() will look like...



function recordLogin() {
         $db = Loader::db(DB_USER_SERVER, DB_USER_USERNAME, DB_USER_PASSWORD, DB_USER_DATABASE, true);
         $uLastLogin = $db->getOne("select uLastLogin from Users where uID = ?", array($this->uID));
         $db->query("update Users set uLastLogin = ?, uPreviousLogin = ?, uNumLogins = uNumLogins + 1 where uID = ?", array(time(), $uLastLogin, $this->uID));
         $db = Loader::db(null, null, null, null, true);
      }




recordView($c) will look like

function recordView($c) {
         $db = Loader::db(DB_USER_SERVER, DB_USER_USERNAME, DB_USER_PASSWORD, DB_USER_DATABASE, true);
         $uID = ($this->uID > 0) ? $this->uID : 0;
         $db = Loader::db(null, null, null, null, true);
         $cID = $c->getCollectionID();
         $v = array($cID, $uID);
         $db->query("insert into PageStatistics (cID, uID, date) values (?, ?, NOW())", $v);
         // record a view, arguments are
         // 1. page being viewed
         // 2. user viewing page
         Events::fire('on_page_view', $c, $this);
      }


and etc, etc...

I made this kinda working now on my local test server.



What do you guys think of my approach?
katz515
 
jbsmith969 replied on at Permalink Reply
jbsmith969
Seems to me like you could use the existing functionality of C5's users and groups and setting up default permissions by editing the permissions in the defaults of the page types, but you would need to plan those out carefully. Then again, maybe you are familiar with this and I'm not understanding the complexity of your problem.
Mnkras replied on at Permalink Reply
Mnkras
haha i have been doing the exact same thing! except i was going to make a fallback if it isn't defined,

trying to keep a list of all files iv changed aswell, then i wanna make a diff,
jbsmith969 replied on at Permalink Reply
jbsmith969
You know, I got to thinking about this a little more, and with careful naming of the groups along with making default permissions of the page types, you could get pretty granular with this. The more groups you create, the more granular you can get with this. You probably need to pull out a white board and start mapping all of that out and come up with a good naming convention for your groups. The only thing I can think of that Concrete lacks is adding users to a group from a group interface. It could be pretty cumbersome to add users to a group by going into each user. So instead of creating another DB and remapping everything, I would say, create a new dashboard interface or modify the existing one so you can add users to a group from a group interface.
Mnkras replied on at Permalink Reply
Mnkras
i had an idea while in irc, not sure if it would work, but with the DB model, catch all queries going to specific tables and re-direct them to a different specified db,

not sure if its possible, just an idea,
hereNT replied on at Permalink Reply
hereNT
I like the approach, what I don't like is that it's necessary to fork the core. It would be nice if there was a good way to run multi-site with a shared core and single user base, but I'm not sure what the best way to do it is.

I just know that it's probably more work than I could figure out how to do...