You plugin folder should now look at this now:

plugin structure

2 - Creating the database

2.1 - Create your database: (using UTF8) (or with PhpMyadmin)

   $ mysqladmin -uroot -pmYsEcret create jobeet

2.2 - Set up the database settings:

   $ php symfony configure:database "mysql:host=localhost;dbname=jobeet" root mYsEcret

(you can leave the password blank)

3 - Using the DB4 tool

3.1 - Install it, check out the instructions on the DB4 website:

Now we are ready to design our database.

3.2 - Updating the database name

1st we need to change the name of the connection that will be used by symfony. The default database name is propel, to use this setting, in DB4 -> main menu -> options -> model option, in this screen put propel for the model name option.

3.3 - Creating the tables

Add a table using the following button (the one at the top)

Create table button

And name this table 'jobs' (we will use plural for tables names but singular for the phpNames like in the official tutorial). Now enter the list of the fields, note that you can:

  • Tell if the field is an auto-increment one
  • Put the default value of the field
  • Add a comment to describe the field

Now let's modify the phpName of the table, fot this, just enter the phpName in the comment field of the table: JobeetJob (if you leave this field blank the default phpName will be used)
Save into the /doc/database/db4.xml file (relative to the root of your project), this is the default path that will use the plugin, so let's keep this path for now. Your jobs table should look like this now:

Fields of the jon table

Note the phpName entered at the bottom.

3.4 - Others tables

Now repeat the operation for the category and affiliate tables. (don't forget the phpName of each table) Your schema should look like this now:

Tables without relations

3.5 - Setting the relations between tables

Now that we have our 3 main tables, it's time to set up the relations. (buttons on the left)

  • Create a 1-n relation from the category table to the job one
  • Create a n-m relation between the category and affiliate tables
  • Double click on the relation between job and category and rename the field to category_id (you can also put a comment and set the onUpdate and onDelete instructions)
  • Rename the relations of the categories_affiliates table to category_id and affiliate_id

Your schema should look like this now:

All Tables

That's it, finally we have our DB4 schema. :) Now let's see how to use it with symfony.

3.6 Generating our schema.yml

Now that we have our db4.xml file we want to generate our schema.yml that will be used by symfony and propel (that's the goal of this tutorial) Launch the following script (chmod +x the file before)

   $ ./plugins/sfDB4toPropelPlugin/bin/

In fact this batch will help us re-generating all that we need after a modification on the DB4 schema. But it just does the following task:

  • Converts the db4.xml file into a schema.yml file
  • Launch the propel:build-all-load command
  • Clears the cache

Now open your /config/schema.yml file it should look like the one provided by Fabien but there are some differences:

  • There is an additional section, in the one of fabien, it is not written because the default values are used.
       defaultIdMethod: native
       package: lib.model
  • If you have put comments in your fields, then you also have them in you schema.yml (usefull to quicky remember the use of a field)

   is_public: { type: BOOLEAN, required: true, default: '1', description: 'Tells if the job is public or not' }
  • The id declaration for our 3 main tables is not the same, once again it's a symfony convention, the ~ tells that we will use default settings.
  • Physical tables names are not the same, that's true but that's not a problem, because in your application we will always use the phpNames of the tables and not is physical names.
  • OMG ! No.. There is a big mistake, we don't have unique index on the email field of the Affiliate table and neither on the token field of the Job table, let's see how to correct this.

3.7 - Setting up the index

Open the Job table details with DB4, in the Indices section at the bottom, click on the add button, name your index 'jobs_unique_token' for example. Now drag and drop the token field to the columns section at the bottom right. Save. Launch our db4 batch. If you open your schema.yml you will see a new section for the Job table:

   _uniques: { jobs_unique_token: [token] }

Now repeat the operation for Affiliate email field. Launch the script. Check the results.

4 - Conclusion

As we have seen, with the help of this plugin we were able to create a clean schema.yml file for our project without having to write a single line of code. I am using this plugin for almost 2 years (the 1.0 version was never published), it just works well, especially when you start a new project from scratch. What is great is even if you want to do major modifications on your database at an early stage of your development, you can do it and very quickly. You will save a lot of time.
For the next version, i'd like to see what is possible to do with MySQL Workbench, i did not tested it yet for now but it looks really nice. (the creator of DB4 is now in the MySQL Workbench team) Moreover it would be also nice to be able to do the same thing for Doctrine... If anyone wants to help, your are welcome. ;)

Related posts:

PS: I have published this tutorial quiet quickly, please help me by reporting typos and errors. ;)