Propel Multiple Database Modeling

Question

I've just started working with Propel and I love it, but I have a question regarding how to utilize multiple database connections. I know I can set stuff up in my schema to connect to multiple different databases, but I'm curious how to handle this in code.

The issue I have is multiple databases, and each of them has slightly different schemas with no data warehousing. As a result I have things resembling the following:

databaseName: westCoastUsers
table: users
column1: email
column2: password
column3: FirstName

databaseName: eastCoastUsers
table: users
column1: email
column2: password
column3: firstName
column4: lastName

Right now in PHP non-Propel version, I'm doing all this by hand, and switching databases manually as required. I'm hoping to streamline things a bit, and I'm curious how to model this. Is there a way I can just have something like eastCoastUser and westCoastUser models that each refer to the proper database/etc or am I trying to wedge in something not supported?

I read this: How to use two database in propel but am uncertain how to actually execute that in code.

Thanks for the help

Answer 1

In your schema files, you can provide a name for the class that represents your table. They do not have to use the same name as the table. You do this with the phpName attribute on the table element.

For example your schema.xml could contain something like this

<database name="westCoastUsers">
  <table name="users" phpName="WestCoastUser">
    ...columns here...
  </table>
  ...
</database>
<database name="eastCoastUsers">
  <table name="users" phpName="EastCoastUser">
    ...columns here...
  </table>
</database>

(edit, note that the name="westCoastUser" on the database element refers to the name of the database, not the classes with similar names)

Then at build time, propel will generate WestCoastUser, WestCoastUserQuery, WestCoastUserPeer, EastCoastUser, EastCoastUserQuery and, EastCoastUserPeer. Each class will connect using the database it was defined under in your schema.

Answer 2

I wrote this originally for symfony 1.2, but I believe it all applies.

I’m using Symfony 1.2.4 for this example.I have two databases, master and slave

If you are going to use multiple databases, there are a few things that you are going to need to do.

You will need separate schema files for both (master.schema.yml and slave.schema.yml)
To use build-sql and insert-sql, you will need multiple propel.ini files
You will need to add an attribute to your schema files to get them to build right

Step 1

Create the databases.yml with two separate connections:

dev:
  propel:
  param:
  classname: DebugPDO

test:
  propel:
  param:
  classname: DebugPDO

all:
  propel:
  class: sfPropelDatabase
  param:
  classname: PropelPDO
  dsn: mysql:dbname=master;host=xxx.xxx.xxx.xxx
  username: uname
  password: pass
  encoding: utf8
  persistent: true
  pooling: true

master:
  class: sfPropelDatabase
  param:
  classname: PropelPDO
  dsn: mysql:dbname=slave;host=xxx.xxx.xxx.xxx
  username: uname
  password: pass
  encoding: utf8
  persistent: true
  pooling: true

Step 2

As mentioned you will need two schema files. Please notice that you will need to define a package attribute for the database that matches up to the tables, and in this case it is ‘lib.model.master’ for the master connection.

master.schema.yml

master:
  _attributes:
  package: lib.model.master
  defaultIdMethod: native
my_table:
  _attributes: { package: lib.model.master }
  my_id: { type: INTEGER, size: '11', primaryKey: true, autoIncrement: true, required: true }

etc.....

slave.schema.yml

slave:
 _attributes:
 package: lib.model.slave
 defaultIdMethod: native
 auctionp:
 _attributes: { package: lib.model.slave }

etc.....

Step 3

You will need to create separate propel.ini files. In this example I used propel-master.ini and propel-slave.ini. Each of these files need to be configured for their respective databases.

Step 4

You will need a good batch file to build your your databases using the propel tools. Mine looks like this:

From the application root: symfony build-model; cp config/slave-propel.ini config/propel.ini; symfony propel:build-sql; symfony propel:insert-sql --no-confirmation; cp config/propel-master.ini config/propel.ini; symfony propel:build-sql; symfony propel:insert-sql --no-confirmation;

Step 5

You will need to clean out /lib/model if you already built your model using one database and are now doing a split. Deleting the files in the “map” and “om” directories and the root directory will help you avoid conflicts.

Step 6

To use the two databases in code, you will need to add a bit to the connection, like the following:

Example 1:

$object = self::doSelect($c, Propel::getConnection('master'));

Example 2:

$newObject->save(Propel::getConnection('slave'));

Example 3:

$con = Propel::getConnection("propel");
$sql = "ALTER TABLE runlinhp CHANGE class class_rat varchar(15)";
$stmt = $con->prepare($sql);
$stmt->execute();