FuelPHP Forums

Lukasz

Hi All,

I'm currently working on a project where it makes more sense to use separate databases for certain users (I keep this information in the session for each user).

By default, models initiate connection to defalt db so I can do something like this:

$result = \DB::select();

If I want to use DB in scenario described above, I need to initialise connection to specified database each time

$db = \DB::instance('database_1234');
$result = $db->select();

Is there any way to override default connection without need to reinitialise connection each time ?

I had an idea to create top level model where such connection would be initialised and then on each follwing model just inheritate from it, but dont know how to override the default (or maybe there is another simpler solution to this problem)

Thank you

Harro

# make sure the db config is loaded
\Config::load('db', true);

# make this users' db active
\Config::set('db.active', 'database_1234');

Note that if you have your sessions also in the database, this causes all DB static calls to switch to a new connection instance, so your session table isn't reachable anymore. If this is the case, you need to configure "db.database" in your session.php config file so that your session table remains accessable.

Lukasz

It looks like having 2 database instance objects don't really work. Connection is still made to the default one, hovever the trick with setting active db connection seems to be ok in this case, but it is not quite handy and may be difficult to have persistent connection to 2 dbs same time :

$default     = \DB::instance('default');
$db2         = \DB::instance('database2');

$res = $db2->select(array('*'))->from('users')
                                            ->where('status', '=', 1)
                                            ->execute()
                                            ->as_array();

Now, var_dump on these objects shows correct connection details so I assume this is fine, but
performing a query (as in example above) gives an error (it tries to open table that doesn't exist in that database).

Adding \Config::set() before executing solves the problem, but then if I have this DB set as a read_only => true, I can't use this featue ( array_rand() expects parameter 1 to be array, boolean given in here).

56        if ( ! $writable and ($readonly = \Config::get('db.'.$name.'.readonly', false)))
57        {
58            ! isset(static::$_readonly[$name]) and static::$_readonly[$name] = \Arr::get($readonly, array_rand($readonly));
59            $name = static::$_readonly[$name];
60        }

Lukasz

Problem is still there :(

Harro

All static DB calls use Database_Connection::instance() to fetch the DB instance on which the query should be executed.

If Database_Connection::instance() is called without parameters, $name will be null, and

        if ($name === null)
        {
            // Use the default instance name
            $name = \Config::get('db.active');
        }

will load the name of the active database from the config. If you haven't set it, either in your app config or using Config::set(), it will contain the value 'default'.

After this, it will create the instance defined by that name if it doesn't exist, and return it. Or it will return a previously created instance.

So using

Config::set('db.active', 'some-other-db');

will switch all static DB calls, and all code using DB for which no specific DB connection name is configured (Models for example have this feature).

So I'm not entirely sure what your problem is.

As I understand it, you have multiple databases. Some tables exist in one, some in the other. And you want to access them both at the same time.

They only way to do that, is to make sure the queries for the database that is NOT defined as active end up using the correct instance. There are multiple options, one you used:

$db2         = \DB::instance('database2');

$res = $db2->select(array('*'))
                  ->from('users')
                  ->where('status', '=', 1)
                  ->execute()
                  ->as_array();

the other one is:

$res = DB::select()
                  ->from('users')
                  ->where('status', '=', 1)
                  ->execute('database2')
                  ->as_array();

so explicitly define you want this executed on database2, and not on the default one.

Lukasz

Basically what I'm trying to do is to definie and initialise 2 separate DB connections, to completly different databases (this whole code should perform data migration + some additional processing).

So I do the following:

$default         = \DB::instance('default');
$db2             = \DB::instance('db2');

I fairly understand the concept of static calls and how setting one of the databases affects this functionality, however this is not a case.

As far as I understand, the code above should give me 2 separate database connection object so using one of them should give me an access to certain database:

print_r( $default->list_tables() );
print_r( $db2->list_tables() );

So far, all works as expected. I can see list of tables from both databases, so established connection on these objects is fine.

Now, when I try to do something like this:

$res = $db2->select(array('*'))->from('users')
                                            ->where('status', '=', 1)
                                            ->execute()
                                            ->as_array();

I get a SQL error "table doesn't exist" just like it tried to connect to the first one for some reason.
But then, even when I try to use same method on other object, I still get same error (this is really odd!).

Only when I change database name in the config ('default') to the one containing the table, I get the results.

This is really confusing now...

Harro

What Fuel version are you using?

Lukasz

1.8
Both connections to mysqli driver.
Tried to change it to different ones but no change.

Harro

Think you bumped into a bug that's there since day 1.

execute() does:

        if ($this->_connection !== null and $db === null)
        {
            $db = $this->_connection;
        }

        if ( ! is_object($db))
        {
            // Get the database instance. If this query is a instance of
            // Database_Query_Builder_Select then use the slave connection if configured
            $db = \Database_Connection::instance($db, null, ! $this instanceof \Database_Query_Builder_Select);
        }

which means that if no database name is passed to execute(), and no connection is set on the object, it will use the default database instance. This is because the correct instance is never passed to the Query builder. :-(

I'll have to think about this, give me a few hours.

Harro

If I'm right, this should work:

$res = DB::select()
                  ->set_connection($db2)
                  ->from('users')
                  ->where('status', '=', 1)
                  ->execute()
                  ->as_array();

or

$res = DB::select()
                  ->set_connection('database2')
                  ->from('users')
                  ->where('status', '=', 1)
                  ->execute()
                  ->as_array();

could you verify that?

Lukasz

Yes, they are both fine.

Harro

Ok, thanks, I'll try to work out a solution today.

Lukasz

Thank you, I really appreciate.

Harro

Can you check if this works?

$res = $db2->query("SELECT * FROM users WHERE status = 1")
->execute()
->as_array();

It is processed differently, so if this gives the same error, I need to come up with a fix for this too.

Lukasz

This one doesn't work,

For query method (MySQLi driver) there are 2 more params required so correct call looks like this:

$res = $db2->query( \DB::SELECT ,"SELECT * FROM users WHERE status = 1", false )
->execute()
->as_array();

But then I get : Call to undefined method Fuel\Core\Database_MySQLi_Result::execute()

Something like this works fine:

$db2->query( \DB::SELECT ,"SELECT * FROM users WHERE status = 1", false )->as_array();

Harro

Sorry, was trying to type a command I never use from the top of my head. Bad idea. ;-)

I pushed a fix: https://github.com/fuel/core/commit/e8f23453e315ee6a0572837bd853e2e5d3d20ee2

Perhaps you can backport it, and see if it solves your problem? If so, I'll release it as a 1.8.0 hotfix later today.

Lukasz

Works well boss!
Thank you! :)

Harro

Cool, thanks for the feedback. Expect a hotfix later today.

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.

Howdy, Stranger!

Categories

In this Discussion