sfDB4toPropelPlugin - 1.0.4

The sfDB4toPropelPlugin allows to transform a DB4 schema into a valid Propel schema.yml file

You are currently browsing
the website for symfony 1

Visit the Symfony2 website


« Back to the Plugins Home

Signin


Forgot your password?
Create an account

Tools

Stats

advanced search
Information Readme Releases Changelog Contribute
Show source

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

  • 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)

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

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

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

SQL Technologies