|
|
Snippets |
|
Multiple databases is not really the strongest area of Propel when using symfony, for this it is better recomanded the use of sfDoctrine plugin.
Still, some propel-symfony based projects may require that one or many modules will have to use different database handles, and to do that, first you need to define each database into your database.yml file.
You can have as many dbs you like and on any combination you want, but for this example we will have one mysql db and one sqlite db.
Your database.yml file should look like this:
all:
database1:
class: sfPropelDatabase
param:
phptype: mysql
hostspec: localhost
database: mysql_databse
username: my_username
database2:
class: sfPropelDatabase
param:
phptype: sqlite
database: ../data/sqlite_database.db
Once you have the databases defined you need to write the schema for each database you have defined and with this step you can decide which table (model) will use which handle.
In our case we'll have two schema.yml files because we have two dbs and they will look like this:
database1.schema.yml
---
database1:
table_1:
_attributes:
idMethod: native
id:
type: INTEGER
required: true
autoIncrement: true
primaryKey: true
value:
type: VARCHAR
size: 32
required: true
table_2:
_attributes:
idMethod: native
id:
type: INTEGER
required: true
autoIncrement: true
primaryKey: true
value:
type: VARCHAR
size: 32
required: true
database2.schema.yml
---
database2:
table_3:
_attributes:
idMethod: native
id:
type: INTEGER
required: true
autoIncrement: true
primaryKey: true
value:
type: VARCHAR
size: 32
required: true
Now is time to build the models
$ symfony propel-build-model
If you want to use other symfony commands that uses the database directly like:
$ symfony propel-build-sql
$ symfony propel-insert-sql
You'll have to configure the dbs connection into propel.ini.
Here we have a problem because you can't use both databases with propel.ini, so you'll have to do this one at a time.
database1 on propel.ini
propel.database = mysql propel.database.createUrl = mysql://localhost/ propel.database.url = mysql://localhost/mysql_database
database2 on propel.ini
propel.database = sqlite propel.database.createUrl = sqlite://./../data/sqlite_database.db propel.database.url = sqlite://./../data/sqlite_database.db
Once you have the models generated and the data loaded into your databases, you can play with your databases.yml file.
If you generate the databases for every handle you'll like, you can switch a model from a handle to another.
Comments on this snippet
Love this tutorial!!
I do have a question about it. I am trying this using two mysql databases.When I end up doing the "propel-insert-sql", I find that: "table_1" and "table_2" will exists in "database_2"; and "table_3" ends up existing in "database_1".
Is there a way to not have this happen?
Realized my comment may be a bit confusing... All three tables show up in both dbs. I am wanting to have "table_1" and "table_2" only exist in "databsae_1"; and have "table_3" only exist in "database_2".
Hi! Got a problem!
I have to use 2 mysql data bases...
When I put their entries in the propel.ini file and wanna use the symfony propel-build-model command, it only recognizes the last database I have especified...
This occurs if I do this in the propel.ini:
propel.database = mysql propel.database.createUrl = mysql://localhost/ propel.database.url = mysql://localhost/mysql_database
propel.database = mysql propel.database.createUrl = mysql://localhost/ propel.database.url = mysql://localhost/mysql_database2
or if I do this:
propel.database = mysql propel.database.createUrl = mysql://localhost/ propel.database.url = mysql://localhost/mysql_database propel.database.url = mysql://localhost/mysql_database2
Any ideas??
Thanks!
Hi, I am using 2 database and I have make same database.yml as above. all:
database1: class: sfPropelDatabase param: phptype: mysql hostspec: localhost database: mysql_databse username: my_username
database2: class: sfPropelDatabase phptype: mysql hostspec: localhost database: mysql_databse username: my_username
While using such configuration in databases.yml i am facing error like "No connection set for propel".
Please Guide
Hi, I am using 2 database and I have make same database.yml as above. all:
database1: class: sfPropelDatabase param: phptype: mysql hostspec: localhost database: mysql_databse username: my_username
database2: class: sfPropelDatabase phptype: mysql hostspec: localhost database: mysql_databse username: my_username
While using such configuration in databases.yml i am facing error like "No connection set for propel".
Please Guide
The trick to getting all this to work is NOT to run 'symfony propel-build-all'
Instead run the 'symfony propel-build-model' and the 'symfony propel-build-sql' commands.
Afterwards, execute the sql commands yourself on the correct databases. The propel.ini file only handles a single database connection. The connection string in the databases.yml file are for connecting to the databases during the running of your application. The database config in the propel.ini file in this case is only used during database creation.
Some trick that help not ending up with all table in one database:
Use the (hidden) shortcut: propel.database.url = mysql://root@localhost/@DB@
Hop that help someone !
@jignesh vishavadia I had the same error
In database.yml, add the data source parameter. So in my case, it would look like:
all: pbx: class: sfPropelDatabase param: dsn: '<Your DSN>' datasource: pdx
propel: class: sfPropelDatabase param: dsn: '<Your DSN>'