Using Multiple DBMS (databases) as One

Posted by on Aug 6, 2010 in Blog | 0 comments

How to extend models from different databases

Using PhpBURN you can extend a MySQL model using a MSSQL model… how? Let me show you:

In system/config/model.php you can configure as many packages you want with lots of information such as host, database, username, password, dialect, etc…

So let’s use an example using two packages, the first one will be a MySQL package running in our localhost machine and the second one will be a MSSQL package running on 192.168.0.100.

$thisConfig = array(
		/**
		 * Database configuration ( Default )
		 * 
		 * This configuration will be used by defaul when
		 * package configs are not setted up
		 */
		'dialect' => 'MySQL',
		'database' => 'mysqlBurnTest',
		'user' => 'phpburn',
		'password' => 'phpburn',
		'port' => '3306',
		'host' => 'localhost',

		/**
		 * Structure configuration
		 */
		'class_path' => SYS_MODEL_PATH,

		/**
		 * database-options changes according to the database used
		 * So if you want to know what options have each database please
		 * look at documentation in section Dialects
		 * 
		 * OPTIONAL
		 */
		'database_options' => array(),

		/**
		 * options are general configs for the project
		 * See Configuration section at documentation for more information
		 * 
		 * OPTIONAL
		 */
		'options' => array(),
			
			'packages' => array(
				'mysqlPackage',
                'mssqlPackage' => array(
					'dialec' => 'MSSQL',
                    'database' => 'mssqlBurnTest',
					'host' => 192.168.0.100,
					'username' => 'sa',
					'password' => 'phpburn' //note that i dont need to put here a data that is already configured on default configs ( above ) but i will set it anyway
                )
			)
);

Well now we have our database configured and all packages configureds let’s go to system/model/mysqlPackage/ and create our model PersonalData:

	class Personaldata extends PhpBURN_Core {
	public $_tablename = "PersonalData";
	public $_package = "mysqlPackage";
	
	
	  public $idPersonalData; 
	  public $name; 
	  public $lastName; 
	  public $birthday;
	  public $createdAt;
	
	
	public function _mapping() {
	     $this->getMap()->addField( "idPersonalData","idPersonalData", "int", 11, array("primary" => 1, "not_null" => 1, "auto_increment" => 1) );
	     $this->getMap()->addField( "name","name", "varchar", 45, array("not_null" => 1) );
	     $this->getMap()->addField( "lastName","lastName", "varchar", 45, array("not_null" => 1) );
		 $this->getMap()->addField( "birthday","birthday", "datetime", null, array("not_null" => 1) );
	     $this->getMap()->addField( "createdAt","createdAt", "timestamp", null, array("not_null" => 1, "default_value" => 'CURRENT_TIMESTAMP') );
	}
}

Well, now we have all set for MySQL model, let’s extend it using a MSSQL model and persist all data transparently it means you will use two models at same time, connecting to different tables in different database in different hosts, etc….

Creating MSSQL Model Users at system/model/mssqlPackage/

	class Users extends PersonalData {
	public $_tablename = "Users";
	public $_package = "mssqlPackage";
	
	
	  public $idUsers; 
	  public $login; 
	  public $password;
	  public $idPersonalData;
	  public $createdAt;
	
	
	public function _mapping() {
	     $this->getMap()->addField( "idUsers","idUsers", "int", 11, array("primary" => 1, "not_null" => 1, "auto_increment" => 1) );
		 $this->getMap()->addField( "idPersonalData","idPersonalData", "int", 11, array("not_null" => 1) );
	     $this->getMap()->addField( "login","login", "varchar", 255, array("not_null" => 1) );
	     $this->getMap()->addField( "password","password", "varchar", 255, array("not_null" => 1) );
	     $this->getMap()->addField( "createdAt","createdAt", "datetime", null, array("not_null" => 1, "default_value" => 'NOW()') );
		
		 $this->getMap()->addParentField('idPersonalData'); //Here the magic happens
	}
}

Now if you just do:

$user = new Users();

$user->login = 'klederson';
$user->password = 'phpburn';
$user->name = 'Klederson';
$user->lastName = 'Bueno';
$user->birthday = '1986-05-17';

$user->save();

It will save in both tables, reference one to another and if you do a $user->get($pk); it will bring all information as you are working in one table. It can be pretty handy on Legacy systems or even in integration between different systems using different databases.

That’s all for now… Be happy 🙂

No Responses to “Using Multiple DBMS (databases) as One”

Trackbacks/Pingbacks

  1. Tweets that mention Using Multiple DBMS (databases) as One | PhpBURN - The Kick-Ass PHP Framework -- Topsy.com - [...] This post was mentioned on Twitter by klederson, phpBURN. phpBURN said: News in #PhpBURN site : Using Multiple DBMS…

Leave a Reply

You must be logged in to post a comment.