How to write a Propel schema with the alternative syntax
Overview
As of symfony 1.1, you can choose to describe your model's relational structure in a new YAML syntax. Symfony recognizes schema.yml
files written either with the syntax described in Chapter 8 of the symfony Book, or with the syntax described below. The alternative syntax is more object-oriented and makes the merging process of several schemas easier to understand.
Base example
Consider the following schema, using the current syntax:
propel:
_attributes: { noXsd: false, defaultIdMethod: none, package: lib.model }
ab_group:
_attributes: { phpName: Group, package: foo.bar.lib.model }
id:
name: varchar(50)
cd_user:
_attributes: { phpName: User, isI18N: true, i18nTable: cd_user_i18n }
first_name: { type: varchar, size: 255, default: "Anonymous" }
last_name: varchar(50)
age: { type: integer, required: true, index: true }
ab_group_id:
created_at:
cd_user_i18n:
description: longvarchar
ef_article:
title: { type: longvarchar, required: true, index: unique }
stripped_title: { type: longvarchar, required: true, primaryKey: true, sequence: my_custom_sequence_name }
user_id:
my_group: { type: integer, foreignTable: ab_group, foreignReference: id, onDelete: setnull }
created_at: timestamp
updated_at:
ij_article:
_attributes: { phpName: Article }
title: varchar(50)
user_id: { type: integer }
_foreignKeys:
-
foreignTable: cd_user
onDelete: cascade
references:
- { local: user_id, foreign: id }
created_at:
_indexes:
my_index: [title, user_id]
_uniques:
my_other_index: [created_at]
_behaviors:
paranoid: { column: deleted_at }
ab_group_i18n:
motto: longvarchar
Alternative syntax
Base example, with alternative syntax
Here is how to write exactly the same structure as the one listed above with the alternative syntax:
connection: propel
noXsd: false
defaultIdMethod: none
package: lib.model
classes:
Group:
tableName: ab_group
package: foo.bar.lib.model
columns:
id:
name: varchar(50)
User:
tableName: cd_user
isI18N: true
i18nTable: cd_user_i18n
columns:
first_name: { type: varchar, size: 255, default: "Anonymous" }
last_name: varchar(50)
age: { type: integer, required: true, index: true }
ab_group_id:
created_at:
CdUserI18n:
columns:
description: longvarchar
EfArticle:
columns:
title: { type: longvarchar, required: true, index: unique }
stripped_title: { type: longvarchar, required: true, primaryKey: true, sequence: my_custom_sequence_name }
user_id:
my_group: { type: integer, foreignClass: Group, foreignReference: id, onDelete: setnull }
created_at: timestamp
updated_at:
Article:
tableName: ij_article
columns:
title: varchar(50)
user_id: { type: integer }
created_at:
foreignKeys:
-
foreignTable: cd_user
onDelete: cascade
references:
- { local: user_id, foreign: id }
indexes:
my_index: [title, user_id]
uniques:
my_other_index: [created_at]
behaviors:
paranoid: { column: deleted_at }
AbGroupI18n:
columns:
motto: longvarchar
The main difference is that you declare classes, not tables, using the table phpName
as a key.
This alternative syntax is also more explicit, since you must create entries for classes
and columns
. But it gets rid of the ugly _attributes
hack of the current syntax, so a schema.yml
doesn't try to mimick an XML syntax.
Last but not least, all the 'magic' of the classic syntax is still there (auto definition of primary keys, foreign keys, i18n tables, etc.).
Connection settings
Instead of being defined as _attributes
of the connection, the connection settings, together with the connection name, are all level-1 keys:
connection: propel
noXsd: false
defaultIdMethod: none
package: lib.model
All these keys are optional, including the connection
one. If it is not set, symfony will take propel
as a default value.
Classes
A class definition lists the table name in the database, the columns, foreign keys, indexes and behaviors in a natural key/values syntax:
Article:
tableName: ij_article
columns:
title: varchar(50)
user_id: { type: integer }
created_at:
foreignKeys:
-
foreignTable: cd_user
onDelete: cascade
references:
- { local: user_id, foreign: id }
indexes:
my_index: [title, user_id]
uniques:
my_other_index: [created_at]
behaviors:
paranoid: { column: deleted_at }
Note that you can define foreign keys either with the usual foreignTable
attribute, which expects a table name, or via the new foreignClass
attribute, which expects a class name.
Mixed schemas
You can have, in a project, schemas with mixed current and alternative syntax.
The schema extension system, described in Chapter 17 of the symfony book, works whatever the original schema syntax and whatever the custom schemas syntax. This means that you can customize an existing schema with the classic syntax using a custom schema with the alternative syntax, and vice-versa. Symfony will do the conversion internally so that the merge is always possible.
Note that the schema merge is easier to understand when considering the alternative syntax for both the original and the custom schema. In fact, this is the internal format used by symfony for the merge. The following listing shows how schemas are merged:
# Original schema, in plugins/myPlugin/config/schema.yml
classes:
User:
tableName: cd_user
columns:
first_name: { type: varchar, size: 255, default: "Anonymous" }
last_name: varchar(50)
age: { type: integer, required: true, index: true }
created_at:
Article:
tableName: ij_article
columns:
title: varchar(50)
user_id: { type: integer }
created_at:
foreignKeys:
-
foreignTable: cd_user
onDelete: cascade
references:
- { local: user_id, foreign: id }
# Custom schema, in myPlugin_schema.custom.yml
connection: myConnection
classes:
Group:
tableName: ab_group
package: foo.bar.lib.model
behaviors: [paranoid]
columns:
id:
name: varchar(50)
User:
tableName: ef_user
isI18N: true
i18nTable: cd_user_i18n
columns:
ab_group_id:
Article:
columns:
updated_at:
# Resulting schema, merged internally and used for model and sql generation
connection: myConnection
classes:
Group:
tableName: ab_group
package: foo.bar.lib.model
behaviors: [paranoid]
columns:
id:
name: varchar(50)
User:
tableName: cd_user
isI18N: true
i18nTable: cd_user_i18n
columns:
first_name: { type: varchar, size: 255, default: "Anonymous" }
last_name: varchar(50)
age: { type: integer, required: true, index: true }
ab_group_id:
created_at:
Article:
tableName: ij_article
columns:
title: varchar(50)
user_id: { type: integer }
created_at:
updated_at:
foreignKeys:
-
foreignTable: cd_user
onDelete: cascade
references:
- { local: user_id, foreign: id }
For clarity, it is recommended to use the alternative schema syntax as much as possible.