Querying a Multiple Databases in the Garden framework?

Artamon

I have been toying around with the vanilla installation, and cannot, for the life of me find a way to query multiple databases within 1 application. Any ideas? Gdn::Database('DB2') should have done it, any ideas?

businessdad

As far as I know, Vanilla only instantiates one Database class, which uses the MySQL driver and the settings defined in the config.php file. To query another database, you would have to do the following:
1. Implement the appropriate driver class for the database you would like to use. For example, you could call it Gdn_DB2Driver. Currently, Vanilla only includes a MySQL driver.
2. Instantiate Gdn_Database class, passing the appropriate configuration to it. Such configuration is parsed in Gdn_Database::Init() and "understands" the following parameters:
- Engine
- User
- Password
- ConnectionOptions
- DatabasePrefix
- ExtendedProperties
3. Use your Gdn_Database instance to build and run your queries, as you would normally do.

In short, most of the work is implementing the appropriate driver class, so that resulting queries are compatible with the target database.

Artamon

and on to my solution:

Set up the conf/config.php

$Configuration['DB2']['Name'] = 'db2name';
$Configuration['DB2']['Host'] = 'db2host';
$Configuration['DB2']['User'] = 'db2user'; //etc... (must place all db parameters)

then in the code whenever you want to query DB2 instead of the 'default' database you do:

$db = new Gdn_Database('DB2');
$db->Sql()->Select(X)->From(X)->Get()->resultArray()

Thanks for the info businessdad, I had actually delved through the source code and solved it. My conclusion: this framework is very tuned for 'Vanilla' and falls short for anything else compared to whats out there. I am actually developing a 'Gamification' system integration with Vanilla and need to query an external database for user points and levels.

businessdad

@Artamon said:

$db = new Gdn_Database('DB2');
$db->Sql()->Select(X)->From(X)->Get()->resultArray()

Thanks for the info businessdad, I had actually delved through the source code and solved it.

Just make sure you test everything, as the resulting SQL might not be fully compatible with DB2 (MySQL has things such as LIMIT, which are part of its dialect and Vanilla uses).

My conclusion: this framework is very tuned for 'Vanilla' and falls short for anything else compared to whats out there.

I would say that Garden was probably born as the foundation of Vanilla, therefore the separation between the two is not so clear. However, it's not so hard to extend it, as you have seen.

I am actually developing a 'Gamification' system integration with Vanilla and need to query an external database for user points and levels.

Wow! That would be the fifth, although they are not all publicly available. @422 developed his own, @GaryFunk did something similar with FourSquare, @peregrine released a Badges plugin today and I'm working on one myself (plus a different one for a Customer, but that one follows completely custom logic and doesn't count).

Artamon

Are you serious? I'm not too concerned with not publishing my source code. Maybe I can add to what is already there and we can get something working together. I will check out @peregrine's work. I am have extensive experience in developing gamification/achievements/levelling systems, but not much experience in the forums arena. So I was going to make a plug-in of sorts to 'shoot off' events to an existing platform I have.

p.s. when I said 'db2' just referring to a generic second database, not the driver. The second database is mysql :P

businessdad

@Artamon said:
Are you serious?

I'm not sure what you are referring to, but I was serious about all topics, so it doesn't really matter.

I'm not too concerned with not publishing my source code. Maybe I can add to what is already there and we can get something working together. I will check out peregrine's work. I am have extensive experience in developing gamification/achievements/levelling systems, but not much experience in the forums arena. So I was going to make a plug-in of sorts to 'shoot off' events to an existing platform I have.

That's good. It's quite easy to develop under the Garden framework, you will pick it up in no time. Integration with external sources is also relatively new in Vanilla, your contributions will certainly be appreciated.

p.s. when I said 'db2' just referring to a generic second database, not the driver. The second database is mysql :P

Ah, now I get it. By coincidence, you used a "keyword" that, in my DBA mind, automatically translates to IBM DB2.

GaryFunk

@businessdad said:
Ah, now I get it. By coincidence, you used a "keyword" that, in my DBA mind, automatically translates to IBM DB2.

Ha. That was the first thing that entered my mind. I work with a iSeries everyday and half of my SQL code is DB2. The fun part is finding I write MySQL and pass it to DB2, then wonder why it isn't working.

peregrine

this may or may not be of use to you.

http://vanillaforums.org/discussion/comment/156694/#Comment_156694