The definitive guide of Symfony 1.2

8.8. Don't Create the Model Twice

The trade-off of using an ORM is that you must define the data structure twice: once for the database, and once for the object model. Fortunately, symfony offers command-line tools to generate one based on the other, so you can avoid duplicate work.

8.8.1. Building a SQL Database Structure Based on an Existing Schema

If you start your application by writing the schema.yml file, symfony can generate a SQL query that creates the tables directly from the YAML data model. To use the query, go to your root project directory and type this:

> php symfony propel:build-sql

A lib.model.schema.sql file will be created in myproject/data/sql/. Note that the generated SQL code will be optimized for the database system defined in the phptype parameter of the propel.ini file.

You can use the schema.sql file directly to build the tables. For instance, in MySQL, type this:

> mysqladmin -u root -p create blog
> mysql -u root -p blog < data/sql/lib.model.schema.sql

The generated SQL is also helpful to rebuild the database in another environment, or to change to another DBMS. If the connection settings are properly defined in your propel.ini, you can even use the php symfony propel:insert-sql command to do this automatically.

Tip The command line also offers a task to populate your database with data based on a text file. See Chapter 16 for more information about the propel:data-load task and the YAML fixture files.

8.8.2. Generating a YAML Data Model from an Existing Database

Symfony can use Propel to generate a schema.yml file from an existing database, thanks to introspection (the capability of databases to determine the structure of the tables on which they are operating). This can be particularly useful when you do reverse-engineering, or if you prefer working on the database before working on the object model.

In order to do this, you need to make sure that the project propel.ini file points to the correct database and contains all connection settings, and then call the propel:build-schema command:

> php symfony propel:build-schema

A brand-new schema.yml file built from your database structure is generated in the config/ directory. You can build your model based on this schema.

The schema-generation command is quite powerful and can add a lot of database-dependent information to your schema. As the YAML format doesn't handle this kind of vendor information, you need to generate an XML schema to take advantage of it. You can do this simply by adding an xml argument to the build-schema task:

> php symfony propel:build-schema  --xml

Instead of generating a schema.yml file, this will create a schema.xml file fully compatible with Propel, containing all the vendor information. But be aware that generated XML schemas tend to be quite verbose and difficult to read.