DMZ 1.7.1 (DataMapper OverZealous Edition)

[eluser]Cord[/eluser]
Hi guys,

I'm very new to DMZ but as a developer, I am really loving working with it. The classes are well thought out and superbly implemented. Good job Phil!

I'm trying to doing something a little unusual and am not sure if I am going to be able to achieve my goal within the limitations of the framework. I have a database (call it db_system) with two tables: users and databases. There is a many-to-many relationship between them, databases_users, which defines which databases a user has access to.

By default, CI is configured to automatically connect to the db_system database. When a user logs in to the main website and is authenticated against db_system, I want to fetch the list of other databases that the user can connect to, and then dynamically connect to each one and extract the information that is relevant to that user. Obviously, I could put all the information currently in the separate databases into a single database and reference each user's data by user_id -- but my client wants to keep them all separate because the parts of his business are discrete and he wants to be able to move any database onto a new database server with a minimum amount of fuss.

Let me explain by way of an example and hope that it's not too confusing. Assume that we have a db_system database, and two other databases: db_cars and db_houses. The structures look something like this:

db_system:
databases
database_id int unsigned auto_increment
host varchar(60)
schema varchar(40)
username varchar(40)
password varchar(30)
dbdriver varchar(10)
dbprefix varchar(20)
pconnect bit(1)
db_debug bit(1)
cache_on bit(1)
cachedir varchar(255)
char_set varchar(10)
dbcollat varchar(20)

users
user_id bigint unsigned auto_increment
username varchar(64)
password varchar(40)

databases_users
database_id int unsigned
user_id bigint unsigned

db_cars:
sales
sale_id bigint unsigned auto_increment
user_id bigint unsigned
item_id int unsigned
...

purchases
purchase_id bigint unsigned auto_increment
user_id bigint unsigned
item_id int unsigned
...

db_houses:
sales
sale_id bigint unsigned auto_increment
user_id bigint unsigned
item_id int unsigned
...

purchases
purchase_id bigint unsigned auto_increment
user_id bigint unsigned
item_id int unsigned
...

The example above is a little contrived but I think you should be able to see what I'm trying to do. A user can belong to one or more databases, and can have different information in each. The databases can reside on different servers and I want to consolidate each user's information by dynamically connecting to each database and displaying that on a single page.

I know I can use $db_params in each of the data-mapped classes but I assume that DMZ automatically connects to the database when the data-mapped object is created. I want to be able to do something like this:

Code:

$u = new User();
$u->where('username', 'is_some_user')->get();

$u->database->get();

foreach ($u->database->all as $d)
{
  // Connect to the selected database
  $user_d = some_dmz_db_connect(
              $d->host,
              $d->schema,
              $d->username,
              $d->password,
              $d->dbdriver,
              $d->dbprefix,
              $d->pconnect,
              $d->db_debug,
              $d->cache_on,
              $d->cachedir,
              $d->char_set,
              $d->dbcollat
            );

  // Now do some stuff with a user's connection
  $s = new $user_d->Sales()
  $s->where('user_id', $u->user_id)->get();
  ...
}

Does the above make sense? Is it possible to achieve what I'm trying to do or am I just barking up the wrong tree?

Thanks,
Cord