Somewhat complex database setup required

[eluser]dgreenhouse[/eluser]
Hello,

First...

I'm very new to CI, but have lots of dev experience.

Next...

I'm working on an application that has some unique restraints.

The application (as designed) requires at times 3 connections to separate databases.

This is due to database size limitations at this client's current hosting provider.
(They're paid up till 2012 and have 3 sites - which will all pull from this data.)

One of the databases will house approx. 200MB of data which is core inventory data.

Another is for metrics.

And the other of course would be for CI's use (users, cache, etc.).

There maybe a fourth if necessary.

I'm wondering if anyone has successfully implemented such a switching strategy using CI.

I can write it from scratch, but I'd rather use CI or another framework to avoid the grunt coding.

I could also just open connections in controllers unbeknownst to CI and leave it at that.

(If I have to open connections unbeknownst to CI, I would still like to use CI to render pages for the user - ergo... my query.)

This is going to be a bear, but a fun bear! :-)

Thanks...

Darrell

[eluser]NMTeck[/eluser]
I've worked together to build a CMS that constantly uses 2 dbs. What type of web app is it exactly and how often will you be using all of the databases? Also I have a CMS that is built on a basic CI framework which may help you with getting started.

http://cms.nmteck.net

Let me know if it helps you.

[eluser]dgreenhouse[/eluser]
[quote author="NMTeck" date="1248567300"]I've worked together to build a CMS that constantly uses 2 dbs. What type of web app is it exactly and how often will you be using all of the databases? Also I have a CMS that is built on a basic CI framework which may help you with getting started.

http://cms.nmteck.net

Let me know if it helps you.[/quote]

Thanks for posting...

The application is an eCommerce portal for a fine arts distributer.

There are over 250k items (qty fluctuates) for review and purchase by retail clients, consultants, designers, businesses & government offices. There is a fairly rich fabric of functionality required and yes - all of the databases (separate machines) will be in use constantly.

I may use views & stored procedures for some queries and encapsulate those in class abstraction patterns for reusability and resilience. I'm currently tuning the schema, "Photoshoping" the UI and working up the project management details.

There will be some very rich UI widgets and 'eye-candy' (progressively enhanced) into the interface.

It's turning into a lot of fun - a real flag-ship project.

I've poked around under the hood of CI since my first posting and think I might know the areas to target for managing "multi-connection" switching, but it's too early to tell if I'm on the right track. "I have to incorporate multi-switching because there's no other way to implement the architecture due to non-functional constraints. The data demands are estimated between 500MB and 2GB and I may resort to sharding some parts of it."

Any pointers would be greatly appreciated and if I move forward with CI for this project, I'll share my successes [! failures ;-)] with the community.

Thanks again and all the best,

Darrell

[eluser]jedd[/eluser]
Howdi,

It's a shame that your technology decisions are being so grossly affected by previous bad business decisions, but on the upside there's plenty of stories around the forums from people who've happily connected to multiple databases using CI. Performance might be more of an issue for you if they're hosted a good way from each other (most of the questions I've seen are from people wanting to connect to DB's on the same LAN).

Something you didn't mention, and might not have considered -- and indeed might not be appropriate if you aren't using MySQL (I don't think you said what DB breed you're running) -- is using [url="http://forge.mysql.com/wiki/MySQL_Proxy"]MySQL Proxy[/url] as the sole 'virtual' database that your CI app will talk to. I've not played with this software yet, but what I've read suggests that it might be capable of front-ending your extant and new DB's, as you've described them, fairly seamlessly. Obviously it'll take you some time to assess what it's capable of, and whether the magic you need to perform with its configuration would be worth the effort.

[eluser]dgreenhouse[/eluser]
[quote author="jedd" date="1248666257"]Howdi,

It's a shame that your technology decisions are being so grossly affected by previous bad business decisions, but on the upside there's plenty of stories around the forums from people who've happily connected to multiple databases using CI. Performance might be more of an issue for you if they're hosted a good way from each other (most of the questions I've seen are from people wanting to connect to DB's on the same LAN).

Something you didn't mention, and might not have considered -- and indeed might not be appropriate if you aren't using MySQL (I don't think you said what DB breed you're running) -- is using [url="http://forge.mysql.com/wiki/MySQL_Proxy"]MySQL Proxy[/url] as the sole 'virtual' database that your CI app will talk to. I've not played with this software yet, but what I've read suggests that it might be capable of front-ending your extant and new DB's, as you've described them, fairly seamlessly. Obviously it'll take you some time to assess what it's capable of, and whether the magic you need to perform with its configuration would be worth the effort.[/quote]

jedd,

Thanks for posting...

I can't say this was a bad business decision on the part of the client; it's just what they already had in place. Anyway, I think I've figured the direction to go in. I'll post back on the progress as I move forward so others can benefit.

BTW/ the database machines are physically close together and are connected by fiber. MySQL Proxy wouldn't work in this situation as I can't run what may be an unapproved binary package (I look into it later).

That's about it for now. I have to figure out if CI does some DB stuff while I have the main DBC switched out and making calls to the view. I wouldn't think so since you can run CI without a database.

Because of the complexity of the application, I considered using something else, but when I factored in the speed of CI, the simplicity of the API while still affording the use of some fairly solid framework code, I decided to stay.

Oh Ya... I'm switching DBC’s in my controllers and switching back before returning.
I've also extended the DB configuration array to include the credentials of the two additional DB's I need so the values were in one place.
(i.e.
$active_group = "default";
$active_record = TRUE;

$db['default']['hostname'] = "host1";
…
$db['artwork']['hostname'] = "host2"; // Making calls separate from the API for now…
…
$db['gallery']['hostname'] = "host3";
…
)

I’ll be conducting some experiments to see how to use CI’s DB API while switched out, but I think that’s an area where I’d run into trouble. I can make it work by not letting CI know anything about the other DB, but I’d like to use CI’s infrastructure code. Obviously, I’ll have to dig deep into CI’s code base to see what it’s doing behind the scenes.

All the best...

[eluser]dgreenhouse[/eluser]
Just looked at Jedidia's video and I it helped a lot.

http://jedidiahreeser.com/blog/2009/07/a...deigniter/

[eluser]dgreenhouse[/eluser]
Yippie! It will definitely work. It helps to RTFM! :-)