Topic awaiting preservation: One Project - want to set up on multiple databases - where do I start (Page 1 of 1)

Ok so our web application is doing pretty good - however we are interested in setting it up so that its spread over more than one database. The reason being is that since its a service based web application the database holds two kinds of information - one is basically configuration and utilitiy information that almost hardly changes unless we change it ourselves the other is user related information like contact information. We're thinking of having it set up so that we have one single database which contains all tables that are utility related for running the application. Users when they would sign up the would enter details of a database server or we could provide that for them and for each user who signs up they would have their own database which would contain all information that they'd enter themselves like for example our application has the following tables:

Basic utility tables which are fixed for the system: modules, sub modules, services

User specific: Contacts, Events, Files

We wish the latter set of tables be in one datbase whilst the former be in another.

We're currently using Php5 MySQL and seriously how can this be done - how do we need to restructure the web application tables etc...or is there a better way to do this?

well, basically all you need to do is to split your database abstraction layer - either into multiple global objects (if that's what you've been using so far), or multiple different query functions (that each use the appropriate link).

You just need to choose a place to store the user database data.

But on the 'does this make sense'-end of the question: Your users would provide you
a database server that's remotly accessible via the internet? Highly unlikely, in my
humble security opinion. MySQL is virtually never exposed to the net, just the internal
network. (But go ahead on *providing* different databases for different users - seperating
data is a good thing!)

so long,

->Tyberius Prime

Database abstraction layer? Uh ok I'm lost now on teh terminology here but I might have worked on it but cant understand what it refers to? How would my queries actually look like then - I mean how would I be able to lets say do something like a JOIN on two tables from two different databases and stuff like that?

quote:

---

JOIN on two tables from two different databases

---

as far as i know that is not possible. You connect to a Database and execute your query on that connection. I doubt php can execute one query on 2 connections.

Joining multiple databases within one connection shouldn't be a problem - you simply prefix the tables with the databasename (like db.table).

You certainly have some kind of wrapper around your database. Be it that you have put a layer on top of the usual mysql_query functions, or have a $db object where you call $db->query...(*) you'll need to either split that up into to seperate query functions
(one for each database - but if you join across them (why would you join application configuration and user data?), you have
to prefix the tablenames) - or you could prefix all table names.


(*) if you don't or if you're still using "string concatenation" to build queries, now is the time to change that to parameterized queries.
Seriously, protect yourself from all kinds of nasty database injections.

OK I think I udnerstand that part for now I have a simple db class which has a few helper db functions the most basic is teh db->query() which takes a string SQL statement and executes it using a mysql_query() function call.

The thing is that my system is set up so that all major objects contain a refernce to a db object i.e. my address class which adds contacts addresses is in this format for example:

class cAddress{
  private db;

  function __construct(){
    $this->db = new cDB();
  }

  function addContact($oneContact)
  {
    //build sql query
    $sql = 'SQL build from $oneContact variable'
    $this->db->query($sql);
  }

}

When you said parameterised queries what do you mean by those - I know theres better ways to get teh work done and frankly the db abstraction layer I built [now I understand what it actually is] is very simple and just takes string sql statements - so I have to make sure the sql strings I create in my other classes are all clear.

ABout parameterized queries? The queries I use are very fairly complex though...

By the way I just had a thought - in such a case what if we need to make an update to the database. Like lets say we need to update table structures in teh client databases - how do we do that - considering in a case that we have multiple databases here.

(Edited by paritycheck on 01-05-2009 09:23)

I'd presume you either replace your new cDB() calls with a factory pattern (i.e. a function GetDBForCustomer() and getDBForSystem()) where appropriate.

Parameterized queries basically take the variables out of the query string...
ie. query("select shu where sha = '{$sham}'") becomes
query("select shu where sha = %1", array ($sham)), and the query handling class makes sure that $sham
actually does not break the query (inserts quotes, escapes, possibly reformates dates, etc).

The updating is usually either done by hand, or by a bunch of update scripts that look at what database version is
on the system (bloody good idea to number your revisions here!), and executes appropriate table changing/adding/dropping
sql statements (be careful to eat customer data though).

so long,

->Tyberius Prime

Hmmm is there any open source library that demonstrates this concept - I'm kinda low on time here and not much in the mood to redo everything from scratch.