{"id":31,"date":"2009-04-24T15:35:48","date_gmt":"2009-04-24T07:35:48","guid":{"rendered":"http:\/\/karyleong.net\/?p=31"},"modified":"2009-05-23T22:58:04","modified_gmt":"2009-05-23T14:58:04","slug":"doctrine-schema","status":"publish","type":"post","link":"https:\/\/karyleong.net\/?p=31","title":{"rendered":"Doctrine Schema"},"content":{"rendered":"

Data Types<\/h2>\n

Doctrine offers several column data types. When you specify the portable Doctrine type it is automatically converted to the appropriate type of the DBMS you are using. Below is a list of the available column types that can be used as well as the type it is translated to when using the MySQL DBMS engine.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Type<\/th>\nMySQL Type<\/th>\n<\/tr>\n<\/thead>\n
integer<\/td>\ninteger<\/td>\n<\/tr>\n
float<\/td>\ndouble<\/td>\n<\/tr>\n
decimal<\/td>\ndecimal<\/td>\n<\/tr>\n
string<\/td>\nvarchar<\/td>\n<\/tr>\n
array<\/td>\ntext<\/td>\n<\/tr>\n
object<\/td>\ntext<\/td>\n<\/tr>\n
blob<\/td>\nlongblob<\/td>\n<\/tr>\n
clob<\/td>\nlongtext<\/td>\n<\/tr>\n
timestamp<\/td>\ndatetime<\/td>\n<\/tr>\n
time<\/td>\ntime<\/td>\n<\/tr>\n
date<\/td>\ndate<\/td>\n<\/tr>\n
enum<\/td>\nvarchar\/enum<\/td>\n<\/tr>\n
gzip<\/td>\ntext<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

If you want to use MySQL built-in enum type, you need enable the native enum support in the config file.<\/p>\n

Now lets specify the use_native_enum<\/code> attribute on our connection so that Doctrine knows to generate the native enum sql for your DBMS.<\/p>\n

all:\r\n  doctrine:\r\n    class: sfDoctrineDatabase\r\n    param:\r\n      dsn: 'mysql:host=localhost;dbname=symfony12doctrine'\r\n      username: user\r\n      attributes:\r\n        use_native_enum: true<\/pre>\n
Below is a sample yaml schema file that implements each of the different column types.<\/p>\n
User:\r\n  columns:\r\n    id:\r\n      type: integer(4)\r\n      primary: true\r\n      autoincrement: true\r\n    username: string(255)\r\n    password: string(255)\r\n    latitude: float\r\n    longitude: float\r\n    hourly_rate:\r\n      type: decimal\r\n      scale: 2\r\n    groups_array: array\r\n    session_object: object\r\n    description: clob\r\n    profile_image_binary_data: blob\r\n    created_at: timestamp\r\n    time_last_available: time\r\n    date_last_available: date\r\n    roles:\r\n      type: enum\r\n      values: [administrator, moderator, normal]\r\n      default: normal\r\n    html_header: gzip<\/pre>\n
Indexes<\/h2>\n
You can optimize your database by defining indexes on columns which are used in conditions on your queries. Below is an example of indexing the username column of a user table since it is common to do lookups on the table by the users username.<\/p>\n
User:\r\n  columns:\r\n    username: string(255)\r\n    password: string(255)\r\n  indexes:\r\n    username_index:\r\n      fields: [username]\r\n      type: unique<\/pre>\n
Relationships<\/h2>\n
Doctrine offers the ability to map the relationships which exist in your database to the ORM so that it can be the most help when working with your data.<\/p>\n
<\/a><\/p>\n
One to One<\/h3>\n
Here is a simple example of how to define a one-to-one relation between a User and Profile model.<\/p>\n
Profile:\r\n  columns:\r\n    user_id: integer\r\n    name: string(255)\r\n    email_address:\r\n      type: string(255)\r\n      email: true\r\n  relations:\r\n    User:\r\n      local: user_id\r\n      foreign: id\r\n      type: one\r\n      foreignType: one<\/pre>\n
One to Many<\/h3>\n
Here is a simple example of how to define a one-to-many relation between a User and Phonenumber model.<\/p>\n
Phonenumber:\r\n  columns:\r\n    user_id: integer\r\n    phonenumber: string(255)\r\n  relations:\r\n    User:\r\n      foreignAlias: Phonenumbers\r\n      local: user_id\r\n      foreign: id\r\n      type: one<\/pre>\n
Many to Many<\/h3>\n
Here is a simple example of how to define a many-to-many relation between a BlogPost and Tag model.<\/p>\n
BlogPost:\r\n  columns:\r\n    user_id: integer\r\n    title: string(255)\r\n    body: clob\r\n  relations:\r\n    User:\r\n      local: user_id\r\n      foreign: id\r\n      type: one\r\n      foreignType: one\r\n      foreignAlias: BlogPosts\r\n    Tags:\r\n      class: Tag\r\n      foreignAlias: BlogPosts\r\n      refClass: BlogPostTag\r\n      local: blog_post_id\r\n      foreign: tag_id\r\n\r\nTag:\r\n  columns:\r\n    name: string(255)\r\n\r\nBlogPostTag:\r\n  columns:\r\n    blog_post_id:\r\n      type: integer\r\n      primary: true\r\n    tag_id:\r\n      type: integer\r\n      primary: true\r\n  relations:\r\n    BlogPost:\r\n      local: blog_post_id\r\n      foreign: id\r\n      foreignAlias: BlogPostTags\r\n    Tag:\r\n      local: tag_id\r\n      foreign: id\r\n      foreignAlias: BlogPostTags\r\n      foreignType: many<\/pre>\n
Behaviors<\/h2>\n
One great feature of Doctrine is the ability to have plug n’ play behavior. These behaviors can be easily included in your model definitions and you inherit functionality automatically.<\/p>\n
<\/a><\/p>\n
Core Behaviors<\/h3>\n
Here is a list of behavior bundled with Doctrine core. You can use any of the behaviors in your models without writing any code.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
Name<\/th>\nDescription<\/th>\n<\/tr>\n<\/thead>\n
Geographical<\/td>\nAdds latitude and longitude to your model and offers functionality for calculating miles\/kilometers between records.<\/td>\n<\/tr>\n
I18n<\/td>\nAdds internationalization capabilities to your models.<\/td>\n<\/tr>\n
NestedSet<\/td>\nTurn your models in to a traversable tree.<\/td>\n<\/tr>\n
Searchable<\/td>\nIndex all the data in your models and make it searchable.<\/td>\n<\/tr>\n
Sluggable<\/td>\nAdd a slug<\/code> field to your models and have it automatically create a slug based on your configuration.<\/td>\n<\/tr>\n
SoftDelete<\/td>\nNever really delete a record. Will simply set a deleted flag instead and filter all deleted records from select queries.<\/td>\n<\/tr>\n
Timestampable<\/td>\nAdd a created_at<\/code> and updated_at<\/code> column to your models have Doctrine set them when inserting and updating records.<\/td>\n<\/tr>\n
Versionable<\/td>\nTurn your models in to an audit log and record all changes. Offers the ability to revert back to previous versions easily<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Sluggable:<\/h3>\n
BlogPost:\r\n  actAs:\r\n    Sluggable:\r\n      fields: [title]\r\n      unique: true\r\n  columns:\r\n    user_id: integer\r\n    title: string(255)\r\n    body: clob<\/pre>\n
Nesting Behaviors<\/h3>\n
Gallery:\r\n  actAs:\r\n    I18n:\r\n      fields: [title, description]\r\n      actAs:\r\n        Sluggable:\r\n          fields:  [title]\r\n  columns:\r\n    title: string(255)\r\n    description: clob<\/pre>\n","protected":false},"excerpt":{"rendered":"
Data Types Doctrine offers several column data types. When you specify the portable Doctrine type it is automatically converted to the appropriate type of the DBMS you are using. Below is a list of the available column types that can be used as well as the type it is translated to when using the MySQL … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[3],"tags":[15,16,70],"_links":{"self":[{"href":"https:\/\/karyleong.net\/index.php?rest_route=\/wp\/v2\/posts\/31"}],"collection":[{"href":"https:\/\/karyleong.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/karyleong.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/karyleong.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/karyleong.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=31"}],"version-history":[{"count":5,"href":"https:\/\/karyleong.net\/index.php?rest_route=\/wp\/v2\/posts\/31\/revisions"}],"predecessor-version":[{"id":45,"href":"https:\/\/karyleong.net\/index.php?rest_route=\/wp\/v2\/posts\/31\/revisions\/45"}],"wp:attachment":[{"href":"https:\/\/karyleong.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=31"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/karyleong.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=31"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/karyleong.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=31"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}