sfDB4toPropelPlugin for symfony 1.2.x, 1.3.x, 1.4.x
Introduction
The sDB4toPropelPlugin is a plugin that adds to symfony a new task: propel:db4-to-propel
that allows you to convert a DB4 schema (a DBDesigner 4 schema) into a valid Propel
schema.yml file.
It also allows you to use tables that are defined in other plugins or other schemas
(for example you want a define a FK to the sf_guard_user table in one of your tables)
With this plugin you can just forget the boring work of building your schema.yml by hand
and stay focused on the development of your application. It is very useful at the
the beginning of a project when your model is not totally defined and may change
quiet often. (new tables, i18n, new columns, refactoring, tests...)
Be carefull, using a symfony 1.4 sandbox doesnt' seems to work, better to start
on a new generated project:
$symfony generate:project myPropelProject --orm=Propel
Installation
Or check out from the svn repository: http://svn.symfony-project.com/plugins/sfDB4toPropelPlugin/branches/1.2
Clear you cache
> symfony cc
Usage
You need a symfony project witch contains at least an application (here
the frontend application is used)
DB4 schema creation
Let's create a very basic DB4 schema file. Open DB4, create a new schema, add a
posts table containing the following fields:
- id, integer, pk, auto-increment
- title, varchar(255)
- content, text
- created_at, datetime
- updated_at, datetime
Now let's try the new task. First we can check the help and available options
with the following command:
$ ./symfony help propel:db4-to-propel
You can see all available options. We'll check all those options in detail later.
So now let's try the basic conversion. (note that to use this basic syntax
you must save your db4 schema in the /doc/database/db4.xml file as it is the
default value used by the plugin)
$ ./symfony propel:db4-to-propel frontend
A schema.yml is now in your /config directory. Let's open it, it should look like this:
db4:
_attributes:
defaultIdMethod: native
package: lib.model
posts:
id: { type: INTEGER, primaryKey: true, required: true, autoIncrement: true }
title: { type: VARCHAR, size: '255' }
content: { type: LONGVARCHAR }
created_at: { type: TIMESTAMP }
updated_at: { type: TIMESTAMP }
So, here is our 1st schema.yml generated by the plugin. If you look at the
command output you will see that the schema.xml is generated and then removed
after the yml conversion. That's the default behavior of the plugin. Also note
that if you tell the command to output the xml in another directory that /config,
the yml conversion will not be done.
DB4 schema tuning
Ok, we have our basic schema, so let's see what we can do at the db4 level.
The propel connection name
We can see that the propel name used is db4 witch is wrong. (it's the default
configuration of db4). To change this option, open
"db4 -> options -> model options". Put propel for the model name like this:
Now launch the task and check your schema.yml, you should have propel as the
connection name instead of db4.
Tables phpNames
We also have the possibility to change the phpName of the table. Double click on
the posts table. Put myPost in the comment section of the table like this:
Save, launch task, check the result. We have the good phpName for our posts
table now.
Columns comments
As a good developer, you are commenting almost everything in your project.
So let's comment the columns of our posts table. Double click on the posts
table and fill some comments in the last right column of the field, with
something like this:
Save, launch task, check the result... Ok, we have customized our schema even it
is still very basic. Now let's try to build the database from this schema.
Creating the database
Well if your are used to symfony, it will be very fast. Create a db4 database
and edit the following configuration files:
# propel.ini:
propel.database.url = mysql:dbname=db4;host=localhost
propel.database.creole.url = ${propel.database.url}
propel.disableIdentifierQuoting = true
# databases.yml:
all:
propel:
class: sfPropelDatabase
param:
classname: PropelPDO
dsn: mysql:dbname=db4;host=localhost
username: root
password: null
encoding: utf8
persistent: true
pooling: true
Ok now run propel-build-all:
$ ./symfony propel:build-all-load --no-confirmation
Check you lib folder, it should look like this:
I18n & foreing keys (1-n)
The task can also handles i18n tables automatically. Let's add a posts_i18n
table witch will store the title of the post in several cultures. So create a
table called posts_i18n, by adding the suffix _i18n, the task will automatically
detect that it is a i18n table. Create a 1-n relation from posts to posts_i18n,
in this last table add 2 fields:
- culture, varchar(7)
- title, varchar(255)
Add the culture to the Pk index and switch it to Pk status. Then remove the title
fields of the post table. At this point, your db4 schema should look like this:
Foreing keys, 1-n
Now let's add a simple posts_comments table which will store a list of comments
for each post. Create the table posts_comments with the phpName myPostComment and
the following fields:
- id: integer, pk, auto-increment
- comment: varchar(255)
- created_at: datetime
Then add the 1-n relation from posts to this table. A good practice here is to
name the fk, with the singular name (not the phpName) of the table, so here we
have post and then add _id, so the relation is called post_id. Your table
should now look like this:
Ok, we've just seen what we are able to do through the db4 schema, now let's check
the options of the task.
Task options
The task has 5 optional options (the application option is mandatory)
package
This option allows you to specify a different package for you model classes.
Let's try to change it to something else. First delete the old model files.
Default value : lib.model
$ rm -rf lib/model
$ rm -rf lib/forms
$ rm -rf lib/filters
$ ./symfony propel:db4-to-propel frontend --package=lib.sfDB4toPropel
$ ./symfony propel:build-all-load
Refresh, now we have the following directories in our lib folder. Also check that
we have our 3 tables in the db4 database.
file_dir
This option allows you to specify where is stored your db4 schema.
Default value : /doc/database
file
This option allows you to specify the name of your db4 schema.
Default value : db4.xml
output
This option allows you to specify the name of the file that will be generated by the task.
For exemple if you specify db4_tutorial_schema, the generated file will be
db4_tutorial_schema.yml (or xml)
Default value: schema
output_dir
This option allows you to specify where will be output the converted file.
Be careful, if you don't leave /config for this option, the yml can't be done.
Generally you won't have to change this option.
Default value : /config
External tables
This option allows you to declare tables as external, this means that you
schema contains this table but in fact this table is managed by another
schema or plugin. Therefore it must be deleted from you generated shema.
This option is very practical if you want to create a foreign key to a plugin table.
The value of the option is a coma separated list of all external tables that uses
your schema:
$ ./symfony propel:db4-to-propel frontend --external_tables=sf_user_guard,other_external_table
Default value : null
Task shell
In the /bin folder of the plugin you have a small sh script called db4.sh that
allows you to run all the tasks without to take care of the arguments. Copy this
file at the root of your project. Chmod +x the file. Modify the script with
your own arguments for the task and then launch it, enjoy.
(remove the -t option of the symfony command to hide the CLI debug infos)
$ cp plugins/sfDB4toPropelPlugin/bin/db4.sh .
$ chmod +x db4.sh
$ ./db4.sh
Now each time that you modify your db4 schema, just launch this script.
References
TODO
Support
Please write on the official blog post.
I can also answer if you ask on the symfony mailing list.
Changelog
Check the changelog TAB
Bugs
See you. COil :)
This plugin is sponsored by SQL Technologies
Vernet Loïc 