2 - sfDB4toPropel tutorial


Well for this tutorial, you will need a clean pear / symfony 1.2 installation (or a sf 1.2 sandbox) (please check the symfony website if you are not familiar with creating a new project). I will presume that you have a successful "Project created !" page. I have called my project sfDB4ToPropelPlugin, very original isn't it ? So for the rest of the tutorial we will assume the main root of the project is /var/www/sfDB4toPropelPlugin. So create this project and an empty frontend application.

2.1 - Plugin installation


First let's install the plugin, this time we will install it with a help of svn (you can also install it with the symfony command line)

$ cd /var/www/sfDB4toPropelPlugin/plugins

$ svn co http://svn.symfony-project.com/plugins/sfDB4toPropelPlugin/branches/1.2/ sfDB4toPropelPlugin

(or http://svn.symfony-project.com/plugins/sfDB4toPropelPlugin/branches/1.1/ if you are using symfony 1.1.x)

$ ./symfony cc

So at this point you have a sfDB4ToPropelPlugin in your plugins directory. In this one you only have a lib and bin folder. (we'll check this last one later)

2.2 - DB4 schema creation


Now let's create a very basic DB4 schema file. Open DB4, create a new schema, add a table 'posts' (very original again) containing the following fields: 

 * id, integer, pk, auto-increment
 * title, varchar(255)
 * content, text
 * created_at, datetime
 * updated_at, datetime


Save the schema into /doc/database/db4.xml, this file is the default one used by the plugin. Your db4 schema should look like this:


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 options available. We'll check all those options in detail later. So now let's try the basic conversion. (note that to use this basic conversion you must sabe your db4 schema in /doc/database/db4.xml file as it is the default db4 file path)

$ ./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.

2.3 - DB4 schema tuning


Ok we have our basic schema, so let's see what we can do at the db4 level.

2.3.1 - propel connection name


We can see that the propel name used is 'db4' witch is wrong. (it's the default configuration of my 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'.

2.3.2 - 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. :)

2.3.2 - Columns comments


As a good developper, 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. Comments are reported into the yml schema.

2.3.3 - Default columns values


You can also put default values. Same thing, double click on the 'posts' table, add a default value for the 'title' 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 for this schema.

2.3.4 - 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://root@localhost/db4
propel.database.url        = mysql://root@localhost/db4


databases.yml: 

all:
  propel:
    class:          sfPropelDatabase
    param:
      dsn:          mysql://root@localhost/db4


Ok now run propel-build-all.

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

Check you lib folder, you should have this:


2.3.5 - 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 call 'posts_i18n', by adding '_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:


2.3.6 - 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', phpName = myPostComment, 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 pratice 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.

2.4 - Task options


The task has 5 optional options (application option is mandatory)

2.4.1 - 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 !


2.4.2 - file_dir


This option allows you to specify where is stores your db4 schema.
Default value : /doc/database

2.4.3 - file


This option allows you to specify the name of your db4 schema.
Default value : db4.xml

2.4.4 - output


This option allows you to specify the name of the file that will be output by the task. For example if you specify db4_tutorial_schema, the generated file will be db4_tutorial_schema.yml (or xml) Default value: schema

2.4.5 - output_dir


This option allows you to specify where will be output the converted file. Be careful, if you do not leave /config for this option, the yml can't be done (and will not be done). Generally you won't have to change this option.
Default value : /config

2.5 - 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.

Comments and feedbacks are welcome. See you. COil :)