Using a secondary database

[Rob]

I'd like to integrate an existing database, so basically it'd be nice if I could use the DatabaseQuerySelect and other classes but pointing to another non-PW database.

I tried just using $this->fuel('db')->select_db('my_db_name'); and then using DatabseQuerySelect but it's clearly then trying to look at the PW database and it gives me an error 'Exception: Table 'web_v2.modules' doesn't exist...'.

From the code it looks like the DB classes all refer to fuel('db') so perhaps I can temporarily change that or create a seondary db fuel object and somehow use that?  I'd welcome suggestions just so that I can keep things consistent and not have to use one set of DB code for PW and another for our custom work.

[Pete]

Not sure whether it will help you, but the database connection is kicked off in wire/core/ProcessWire.php around line 90 with this code:

if($config->dbSocket) {
		$db = new Database($config->dbHost, $config->dbUser, $config->dbPass, $config->dbName, $config->dbPort, $config->dbSocket);
	} else {
		$db = new Database($config->dbHost, $config->dbUser, $config->dbPass, $config->dbName, $config->dbPort);
	}

	Wire::setFuel('db', $db);

I'm not sure how you would set up another connection inside a module or template, as I doubt you would have access to the Database object, but something like this might work at the beginning of the template (untested):

$mydb = new Database('localhost', 'username', 'password', 'db_name', 3306);

or maybe (again, untested):

$mydb = new Wire::Database('localhost', 'username', 'password', 'db_name', 3306);

If either works let me know. The idea would then be to use $mydb instead of $this->fuel('db') for the queries.

[Rob]

Thanks Pete.

I've managed to create a Database object and make SQL calls to a separate DB, but the thing is that I'd like to be able to use the DatabaseQuerySelect class, and other API classes, but they seem to be hardcoded to use $fuel['db'] and I can't see how I can override that.

Also, at some point in the execution pipeline of using DatabaseQuerySelect there are module-related DB queries which obviously won't work if I've flipped the standard $fuel['db'] to be pointing to a separate DB.  I'm not sure why these calsl to fetch module-related information are necessary but if they are then it may make it difficult to do anything with 3rd part DBs with the PW DB API.

[Soma]

Let me search it for you....  

ok maybe not exactly what you need.

http://processwire.com/talk/index.php/topic,34.0.html

Why not just connect to your db manually using db->query?

http://processwire.com/talk/index.php/topic,23.0.html

ok I'm stupid...

But I'm sure there's a way to create a new db object with other DB like Pete example.

[ryan]

Those DatabaseQuery classes were mostly designed around the needs of the PageFinder, so I'm not sure how much utility it would really have outside of that. Personally, I don't like abstracting on top of DB queries (ActiveRecord and the like) but this was one case where multiple classes had to work on the same query, so it would have been dumb to do it any other way – that's where those DatabaseQuery classes came from. If you find them useful for something else, that's great, but these aren't meant as a real framework DB/query wrapper, so you are likely to come across limitations if you want to stretch them. But if you want to use them, then extend the class and  override the fuel() function to point 'db' to your own. If there is more demand to provide a real framework DB query wrapper, we can add a setDb() method or something like that (after expanding the utility of the classes). But because these classes are just built for the internal needs of PW, I would look at including something like CodeIgniter's ActiveRecord if you are needing a full blown DB query wrapper.