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:
> symfony propel-build-sql
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
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
symfony propel-insert-sql command to do this automatically.
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-load-datatask and the YAML fixture files.
8.8.2. Generating a YAML Data Model from an Existing Database
Symfony can use the Creole database access layer 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
> symfony propel-build-schema
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
> 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.