sfDB4toPropelPlugin for symfony 1.2.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...)

Installation
------------

* Install the plugin
 
        > symfony plugin-install sDB4toPropelPlugin
 
Or check out from the svn repository: [http://svn.symfony-project.com/plugins/sfDB4toPropelPlugin/branches/1.2](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

![](http://gallery.coilblog.com/images/tuto-db4/blog-db4-02.jpg)

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)    

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: 

![](http://gallery.coilblog.com/images/tuto-db4/blog-db4-03.jpg)

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:

![](http://gallery.coilblog.com/images/tuto-db4/blog-db4-04.jpg)

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:

![](http://gallery.coilblog.com/images/tuto-db4/blog-db4-05.jpg)

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 files:

    # propel.ini:
    propel.database.createUrl  = mysql:dbname=db4;host=localhost
    propel.database.url        = mysql:dbname=db4;host=localhost

    # databases.yml:
    all:
      propel:
        class:          sfPropelDatabase
        param:
          dsn:          mysql:dbname=db4;host=localhost

Ok now run propel-build-all:

    $ ./symfony propel:build-all-load frontend

Check you lib folder, it should look like this:

![](http://gallery.coilblog.com/images/tuto-db4/blog-db4-07.jpg)

#### 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:

![](http://gallery.coilblog.com/images/tuto-db4/blog-db4-08.jpg)

#### 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:

![](http://gallery.coilblog.com/images/tuto-db4/blog-db4-09.jpg)

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
    $ ./symfony propel:db4-to-propel frontend --package=lib.sfDB4toPropel
    $ ./symfony propel:build-all-load frontend

Refresh, now we have the following directories in our lib folder. Also check that
we have our 3 tables in the db4 database.

![](http://gallery.coilblog.com/images/tuto-db4/blog-db4-10.jpg)

#### 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

![](http://gallery.coilblog.com/images/tuto-db4/blog-db4-11.jpg)
 
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
----------

* [Full tutorial on my blog](http://www.strangebuzz.com/index.php/2008/09/08/36-new-symfony-11-plugin-tutorial-sfdb4topropelplugin).
* [The Jobeet Day 3 tutorial using the sfDB4toPropelPlugin](http://www.strangebuzz.com/index.php/2008/12/07/38-jobeet-an-alternative-tutorial-for-day-3).

TODO
====

Support
=======

Please write on the [official blog post](http://www.strangebuzz.com/index.php/2008/09/08/36-new-symfony-11-plugin-tutorial-sfdb4topropelplugin).
I can also answer if you ask on the symfony mailing list.

Changelog
=========

>**Note**
>Check the changelog TAB

Bugs
====

----

See you. [COil](http://www.strangebuzz.com) :)

----

This plugin is sponsored by [SQL Technologies](http://www.sqltechnologies.com)

 ![SQL Technologies](http://www.php-debug.com/images/sql.gif)