Changing DB dynamical

Hello there!

I have been tasked with setting up a easy-to-manage system where i can edit sites from different servers though 1 GUI.

This is going to be achrived by selecting which sites DB to connect to and edit the foreign DB directly.

So, I am very new to yii and would like to find out how i can change the db connection dynamically.

PS, I am a novice at yii, so please be explicit about where i need to do what.

PPS, this setup is ment to be able to use gii on the connections and the various databases have identical schemas with different content.

.

Thanks in advance Rasmus W

Wrong button. . …

In Yii protected/config/main.php

You can setup as many db connections as you want.

Then change the Gii so that you will have a selectable list of DBs.

I had the same question couple of days ago, and with the help of the forum I came to a solution that works OK for me.

In my case the database is selected upon login. As soon as the user is authenticated, the database is saved to the session

$session=app()->Yii::app()->getSession();

$session['dbid']=$connectionString;

Then I attached an "OnBeginRequest" event to my application and in that event I put

$session=app()->getSession();

if (!$session['dbid']) return;

$dbconfig = array(

	'class'=>'CDbConnection',

	'connectionString'=>$session['dbid'],

);

Yii::app()->setComponent('db',Yii::CreateComponent($dbconfig));

Hope this can lead you in the right direction.

Good luck!

Yep, i found that i could throw in some more database connections i main.php

And Emil, i am assuming your dbid is comming from the login form, am i right?

This would be an idea but i want to be even more dynamical =P.

I want a user to login and then have a dropdown menu which on selection alters the database connection to the selected db.

as it stands now i can get it to change db but i am unable to decide which db if there is more then one.

Right now the database is changed because my model class overrides getDbConnection() from CActiveRecords which a hardcoded db.

I tried to add a param to getDbConnection() to be able to make a switch but that was a no go due to the overriding function have to be identical.

So my real beef right now is that i some how need to pass along some kinde of paramter that allows me to enter a switch in the overridden getDbConnection() in my model class so that i can controll which db it connects to.

Any suggestions? And thanks for your replies so fare!

Oh and Emil, i looked into the onBeginRequest event.

The dokumentation is less the great for beginners, so where do you place the onBeginRequest method?

Then you need to set it using a helper or a component for it.

Let me give you an overview of it.

When the request is being processed then

your helper component will check the table which have the information regarding the databases that are there in the list.

It will also have information which user can have access to which database.

After system gets the database list based on the user rights, it will then display it.

Now, select the database for further processing.

That sounds like a good solution if I were to have alot of different user access levels but as it stands now there will only be an admin with access to everything and a user which have access to all DB’s. (My bad for not telling how this part should be)

My problem as it stands now is to be able to form a dropdown menu from the DB connections specified in the main.php. If this is not do able ill just make an array with the names of the DB as key and the login information as value.

What i need after that is a way to alter the connection on an event like onSelectionChanged but im very confused by the events.

Is there such an event or do i need to make it my self? If yes, any help would be appriciated as im having a hard time understanding how events are set up and in which classes.

I think still proposed solution works fine. is it not?

My problem as it stands now is to be able to form a dropdown menu from the DB connections specified in the main.php. If this is not do able ill just make an array with the names of the DB as key and the login information as value.

There is no need to have information in about all dbs in the main.php as you are now storing all the information in one db.

Now, what you have to do is to create an SQL Query which will fetch all the information required to connect to the SQL server and present it in any from, radio buttons or select able list or a linked form.

What i need after that is a way to alter the connection on an event like onSelectionChanged but im very confused by the events.

Try the code as given in one of the reply to this topic as

$dbconfig = array(

        'class'=>'CDbConnection',

        'connectionString'=>connection information i.e. server,username,password,database,

);

Yii::app()->setComponent('db',Yii::CreateComponent($dbconfig));

Your right, it is useful, i just didnt want to make my assignment more complex then necessary (Small things can be a big time sink when your new)

This looks great but where at do i put this code?

PS. Thanks alot for the idea about storing the connections in the db, that makes it alot easier to work with

In Gii where you get the list of dbs and want to set the new db.

Im sorry but i am a dumbass at figuring out new new stuff.

Can i bully you into giving me complete code snippet as well as an explicit .php file name where to paste it?

I am trying but its hard as i have little to no idea where i need to do what >.<

//config .php right after appName or in the same level

'onBeginRequest'=>array('Bootstrap','begin')

// /protected/components/Bootstrap.php

class Bootstrap{

   function begin(){

	//the code here

  }

}

Okay, i pasted in the “‘onBeginRequest’=>array(‘Bootstrap’,‘begin’)” in main.php in the config map and made the Bootstrap.php in components.

So what happens now? i filled out the code in begin() with a new connection but when i go to one of model controllers, they get their content from the local server still am i missing something (And truth be told, at this point im getting very very confused )

What you did is the following

You setup an event(class Bootstrap method begin) to execute before any other action (onBeginRequest)

in the method you created a "db" component that will be later (CActiveRecord, Models, etc) used by as main db connection ( by default "db" is the main db connection)

Remove now from the main config the db connection

Works like a charm, thanks!

A few questions: I still dont really get the event idea. Is this event predefined somewhere or does all events just get called everytime anything happens? Having a hard time seeing what defines when the various events get called (I tried reading up on it, but too abstract writeups imo)

Second, now the begin() metod alters the connection as it should. Is there any way i can pass along the parameters from a user choosed database to begin() to be able to pick which database the user should connect to?

Yes, events are predefined, and for example the event ‘onBeginRequest’ will call the ‘beginRequest’ method by default

And that is why you use “beforeSave” method in your models and “afterSave”, these are actions attached to the ‘onBeforeSave()’ and ‘onAfterSave()’ events

You can attach more actions to be execute when an event occurs

The guy that answered you gave some idea, he uses the session to do it

Glad I could help

Cheers

Okay, that did shed some light on it.

Thanks alot for your time and ill give it a crack with the session stuff

I came accross the same issue.

1. I dropped db definition in main/config.php

2. added this:

return array(

‘basePath’=>dirname(FILE).DIRECTORY_SEPARATOR.’…’,

‘name’=>‘E-MS’,

‘onBeginRequest’ => array(‘Bootstrap’, ‘begin’),

…

3. Created protected/components/Bootsrap.php and put this:

function begin(){

$base = ‘BASE_SYS’;

$session=Yii::app()->getSession();

if (isset($session[‘base’]))

$base = ‘compte_client_’ . $session[‘base’];

$dbConfig = array(

‘class’=>‘CDbConnection’,

‘connectionString’=>‘mysql:host=localhost;dbname=’ . $base,

‘username’=>‘root’,

‘password’=>’’

);

Yii::app()->setComponent(‘db’, Yii::createComponent($dbConfig));

}

But i got this error:

The table liste for active record class Liste cannot be found in the database.

everything looks right

check if $base refers to the database you want to set

if not, you should use some logic to get the database you desire

The problem with boostrap onBeginRequest is that i can’t use $session=Yii::app()->getSession();

it’s not available yet.