You are currently viewing the 0.10 branch manual. Switch to 0.11

0.10 User Manual

Chapter 3 Basic schema mapping

3.1 Introduction

This chapter and its subchapters tell you how to do basic schema mappings with Doctrine. After you've come in terms with
the concepts of this chapter you'll know how to:

1. Define columns for your record classes
2. Define table options
3. Define indexes
4. Define basic constraints and validators for columns

All column mappings within Doctrine are being done via the hasColumn() method of the Doctrine_Record. The hasColumn takes
4 arguments:

column name String that specifies the column name and optional alias. This is needed for all columns. If you want

to specify an alias for the column name you'll need to use the format '[columnName] as [columnAlias]'

column type String that specifies the column type. See the column types section.

column length Integer that specifies the column length. Some column types depend not only the given portable type

but also on the given length. For example type string with length 1000 will be translated into native type TEXT on mysql.

column constraints and validators An array that specifies the list of constraints and validators applied to given

column.

Note that validators / column constraints and the column length fields are optional. The length may be omitted by using
null for the length argument, allowing doctrine to use a default length and permitting a fourth argument for validation
or column constraints.

Lets take our first example. The following definition defines a class called Email which refers to a table called 'emails'.
The Email class has two columns id (an auto-incremented primary key column) and a string column called address.

Notice how we add two validators / constraints for the address column (notblank and email). The notblank validator assures
that the address column isn't blank (so it must not contain space-characters only) whereas the email validator ensures that
the address is a valid email address.

Listing 3.1

<?php

class Email extends Doctrine_Record
{
    public function setTableDefinition()
    {
        // setting custom table name:
        $this->setTableName('emails');

        $this->hasColumn('address',         // name of the column
                         'string',          // column type
                         '200',             // column length
                         array('notblank' => true,
                               'email'    => true  // validators / constraints
                               )
                         );
    }
}

?>

Here is the same model specified as a YAML schema file

Listing 3.2

---
Email:
  tableName: emails
  columns:
    address:
      type: string(255)
      notblank: true
      email: true

Now lets create an export script for this class:

Listing 3.3

<?php

require_once('Email.php');
require_once('path-to-Doctrine/Doctrine.php');

require_once('path-to-doctrine/lib/Doctrine.php');

spl_autoload_register(array('Doctrine', 'autoload'));

// in order to export we need a database connection
$manager = Doctrine_Manager::getInstance();
$conn    = $manager->openConnection('mysql://user:pass@localhost/test');

$conn->export->exportClasses(array('Email'));

?>

The script would execute the following sql (we are using Mysql here as the database backend):

Listing 3.4

CREATE TABLE emails (id INT NOT NULL AUTO_INCREMENT, address VARCHAR(200) NOT NULL)

3.2 Table and class naming

Doctrine automatically creates table names from the record class names. For this reason, it is recommended to name your
record classes using the following rules:

Use CamelCase naming
Underscores are allowed
The first letter must be capitalized
The class name cannot be one of the following (these keywords are reserved in DQL API):
ALL, AND, ANY, AS, ASC, AVG, BETWEEN, BIT_LENGTH, BY, CHARACTER_LENGTH, CHAR_LENGTH, COUNT, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, DELETE, DESC, DISTINCT, EMPTY, EXISTS, FALSE, FETCH, FROM, GROUP, HAVING, IN, INDEXBY, INNER, IS, JOIN, LEFT, LIKE, LOWER, MAX, MEMBER, MIN, MOD, NEW, NOT, NULL, OBJECT, OF, OR, ORDER, OUTER, POSITION, SELECT, SOME, SUM, TRIM, TRUE, UNKNOWN, UPDATE, UPPER and WHERE.

Example: My_PerfectClass

If you need to use a different naming schema, you can override this using the setTableName() method in the
setTableDefinition() method.

3.3 Table options

Doctrine offers various table options. All table options can be set via Doctrine_Record::option($optionName, $value).

For example if you are using MySQL and want to use INNODB tables it can be done as follows:

Listing 3.5

<?php

class MyInnoDbRecord extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('name', 'string');

        $this->option('type', 'INNODB');
    }
}

?>

Listing 3.6

---
MyInnoDbRecord:
  columns:
    name: string
  options:
    type: INNODB

In the following example we set the collate and character set options:

Listing 3.7

<?php

class MyCustomOptionRecord extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('name', 'string');

        $this->option('collate', 'utf8_unicode_ci');
        $this->option('charset', 'utf8');
    }
}

?>

Listing 3.8

---
MyCustomOptionRecord:
  columns:
    name: string
  options:
    collate: utf8_unicode_ci
    charset: utf8

Doctrine offers the ability to turn off foreign key constraints for specific Models.

Listing 3.9

<?php

class MyCustomOptionRecord extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('name', 'string');

        $this->setAttribute(Doctrine::ATTR_EXPORT, Doctrine::EXPORT_ALL ^ Doctrine::EXPORT_CONSTRAINTS);
    }
}

?>

Listing 3.10

---
MyCustomOptionRecord:
  columns:
    name: string
  attributes:
    export: all ^ constraints

3.4 Columns

3.4.1 Column naming

One problem with database compatibility is that many databases differ in their behaviour of how the result set of a
query is returned. MySQL leaves the field names unchanged, which means if you issue a query of the form
"SELECT myField FROM ..." then the result set will contain the field 'myField'.

Unfortunately, this is just the way MySQL and some other databases do it. Postgres for example returns all field names
in lowercase whilst Oracle returns all field names in uppercase. "So what? In what way does this influence me when using
Doctrine?", you may ask. Fortunately, you don't have to bother about that issue at all.

Doctrine takes care of this problem transparently. That means if you define a derived Record class and define a field
called 'myField' you will always access it through $record->myField (or $record['myField'], whatever you prefer) no
matter whether you're using MySQL or Postgres or Oracle etc.

In short: You can name your fields however you want, using under_scores, camelCase or whatever you prefer.

3.4.2 Column aliases

Doctrine offers a way of setting column aliases. This can be very useful when you want to keep the application
logic separate from the database logic. For example if you want to change the name of the database field all you
need to change at your application is the column definition.

Listing 3.11

<?php

class Book extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('bookTitle as title', 'string');
    }
}

$book = new Book();
$book->name = 'Some book';
$book->save();

?>

Listing 3.12

---
Book:
  columns:
    name:
      name: bookTitle as title
      type: string

3.4.3 Default values

Doctrine supports default values for all data types. When default value is attached to a record column this means two of
things. First this value is attached to every newly created Record.

Listing 3.13

<?php

class User extends Doctrine_record
{
    public function setTableDefinition(){
        $this->hasColumn('name', 'string', 50, array('default' => 'default name'));
    }
}

$user = new User();
print $user->name; // default name

?>

Listing 3.14

---
User:
  columns:
    name:
      type: string(50)
      default: default name

Also when exporting record class to database DEFAULT value is attached to column definition statement.

3.4.4 Data types

3.4.4.1 Introduction

All DBMS provide multiple choice of data types for the information that can be stored in their database table fields.
However, the set of data types made available varies from DBMS to DBMS.

To simplify the interface with the DBMS supported by Doctrine, it was defined a base set of data types that applications
may access independently of the underlying DBMS.

The Doctrine applications programming interface takes care of mapping data types when managing database options. It is
also able to convert that is sent to and received from the underlying DBMS using the respective driver.

The following data type examples should be used with Doctrine's createTable() method. The example array at the end of the
data types section may be used with createTable() to create a portable table on the DBMS of choice (please refer to the
main Doctrine documentation to find out what DBMS back ends are properly supported). It should also be noted that the
following examples do not cover the creation and maintenance of indices, this chapter is only concerned with data types
and the proper usage thereof.

It should be noted that the length of the column affects in database level type as well as application level validated
length (the length that is validated with Doctrine validators).

Example 1. Column named 'content' with type 'string' and length 3000 results in database type 'TEXT' of which has database
level length of 4000. However when the record is validated it is only allowed to have 'content' -column with maximum
length of 3000.

Example 2. Column with type 'integer' and length 1 results in 'TINYINT' on many databases.

In general Doctrine is smart enough to know which integer/string type to use depending on the specified length.

3.4.4.2 Type modifiers

Within the Doctrine API there are a few modifiers that have been designed to aid in optimal table design. These are:

The notnull modifiers
The length modifiers
The default modifiers
unsigned modifiers for some field definitions, although not all DBMS's support this modifier for integer field types.
zerofill modifiers (not supported by all drivers)
collation modifiers (not supported by all drivers)
fixed length modifiers for some field definitions.

Building upon the above, we can say that the modifiers alter the field definition to create more specific field types for
specific usage scenarios. The notnull modifier will be used in the following way to set the default DBMS NOT NULL Flag on
the field to true or false, depending on the DBMS's definition of the field value: In PostgreSQL the "NOT NULL"
definition will be set to "NOT NULL", whilst in MySQL (for example) the "NULL" option will be set to "NO". In order to
define a "NOT NULL" field type, we simply add an extra parameter to our definition array (See the examples in the
following section)

Listing 3.15

<?php

'sometime' = array(
    'type'    => 'time',
    'default' => '12:34:05',
    'notnull' => true,
),

?>

Using the above example, we can also explore the default field operator. Default is set in the same way as the notnull
operator to set a default value for the field. This value may be set in any character set that the DBMS supports for text
fields, and any other valid data for the field's data type. In the above example, we have specified a valid time for the
"Time" data type, '12:34:05'. Remember that when setting default dates and times, as well as datetimes, you should
research and stay within the epoch of your chosen DBMS, otherwise you will encounter difficult to diagnose errors!

Listing 3.16

<?php

'sometext' = array(
    'type'   => 'string',
    'length' => 12,
),

?>

The above example will create a character varying field of length 12 characters in the database table. If the length
definition is left out, Doctrine will create a length of the maximum allowable length for the data type specified, which
may create a problem with some field types and indexing. Best practice is to define lengths for all or most of your fields.

3.4.4.3 Boolean

The boolean data type represents only two values that can be either 1 or 0. Do not assume that these data types are stored
as integers because some DBMS drivers may implement this type with single character text fields for a matter of
efficiency. Ternary logic is possible by using null as the third possible value that may be assigned to fields of this type.

Listing 3.17

<?php

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('booltest', 'boolean');
    }
}

?>

Listing 3.18

---
Test:
  columns:
    booltest: boolean

3.4.4.4 Integer

The integer type is the same as integer type in PHP. It may store integer values as large as each DBMS may handle.

Fields of this type may be created optionally as unsigned integers but not all DBMS support it. Therefore, such option
may be ignored. Truly portable applications should not rely on the availability of this option.

The integer type maps to different database type depending on the column length.

Listing 3.19

<?php

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('integertest', 'integer', 4, array('unsigned' => true));
    }
}

?>

Listing 3.20

---
Test
  columns:
    integertest:
      type: integer(4)
      unsigned: true

3.4.4.5 Float

The float data type may store floating point decimal numbers. This data type is suitable for representing numbers within
a large scale range that do not require high accuracy. The scale and the precision limits of the values that may be
stored in a database depends on the DBMS that it is used.

Listing 3.21

<?php

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('floattest', 'float');
    }
}

?>

Listing 3.22

---
Test:
  columns:
    floattest: float

3.4.4.6 String

The text data type is available with two options for the length: one that is explicitly length limited and another of
undefined length that should be as large as the database allows.

The length limited option is the most recommended for efficiency reasons. The undefined length option allows very large
fields but may prevent the use of indexes, nullability and may not allow sorting on fields of its type.

The fields of this type should be able to handle 8 bit characters. Drivers take care of DBMS specific escaping of
characters of special meaning with the values of the strings to be converted to this type.

By default Doctrine will use variable length character types. If fixed length types should be used can be controlled via
the fixed modifier.

Listing 3.23

<?php

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('stringtest', 'string', 200, array('fixed' => true));
    }
}

?>

Listing 3.24

---
Test:
  stringtest:
    type: string(255)
    fixed: true

3.4.4.7 Array

This is the same as 'array' type in PHP.

Listing 3.25

<?php

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('arraytest', 'array', 10000);
    }
}

?>

Listing 3.26

---
Test:
  columns:
    arraytest:
      type: array(10000)

3.4.4.8 Object

Doctrine supports objects as column types. Basically you can set an object to a field and Doctrine handles automatically
the serialization / unserialization of that object.

Listing 3.27

<?php

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('objecttest', 'object');
    }
}

?>

Listing 3.28

---
Test:
  columns:
    objecttest: object

3.4.4.9 Blob

Blob (Binary Large OBject) data type is meant to store data of undefined length that may be too large to store in text
fields, like data that is usually stored in files.

Blob fields are usually not meant to be used as parameters of query search clause (WHERE) unless the underlying DBMS
supports a feature usually known as "full text search"

Listing 3.29

<?php

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('blobtest', 'blob');
    }
}

?>

Listing 3.30

---
Test:
  columns:
    blobtest: blob

3.4.4.10 Clob

Clob (Character Large OBject) data type is meant to store data of undefined length that may be too large to store in text
fields, like data that is usually stored in files.

Clob fields are meant to store only data made of printable ASCII characters whereas blob fields are meant to store all
types of data.

Clob fields are usually not meant to be used as parameters of query search clause (WHERE) unless the underlying DBMS
supports a feature usually known as "full text search"

Listing 3.31

<?php

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('clobtest', 'clob');
    }
}

?>

Listing 3.32

---
Test:
  columns:
    clobtest: clob

3.4.4.11 Timestamp

The timestamp data type is a mere combination of the date and the time of the day data types. The representation of values
of the time stamp type is accomplished by joining the date and time string values in a single string joined by a space.
Therefore, the format template is YYYY-MM-DD HH:MI:SS. The represented values obey the same rules and ranges described for
the date and time data types

Listing 3.33

<?php

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('timestamptest', 'timestamp');
    }
}

?>

Listing 3.34

---
Test:
  columns:
    timestamptest: timestamp

3.4.4.12 Time

The time data type may represent the time of a given moment of the day. DBMS independent representation of the time of the
day is also accomplished by using text strings formatted according to the ISO-8601 standard.

The format defined by the ISO-8601 standard for the time of the day is HH:MI:SS where HH is the number of hour the day from
00 to 23 and MI and SS are respectively the number of the minute and of the second from 00 to 59. Hours, minutes and
seconds numbered below 10 should be padded on the left with 0.

Some DBMS have native support for time of the day formats, but for others the DBMS driver may have to represent them as
integers or text values. In any case, it is always possible to make comparisons between time values as well sort query
results by fields of this type.

Listing 3.35

<?php

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('timetest', 'time');
    }
}

?>

Listing 3.36

---
Test:
  columns:
    timetest: time

3.4.4.13 Date

The date data type may represent dates with year, month and day. DBMS independent representation of dates is accomplished
by using text strings formatted according to the IS0-8601 standard.

The format defined by the ISO-8601 standard for dates is YYYY-MM-DD where YYYY is the number of the year (Gregorian calendar),
MM is the number of the month from 01 to 12 and DD is the number of the day from 01 to 31. Months or days numbered below
10 should be padded on the left with 0.

Some DBMS have native support for date formats, but for others the DBMS driver may have to represent them as integers or
text values. In any case, it is always possible to make comparisons between date values as well sort query results by
fields of this type.

Listing 3.37

<?php

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('datetest', 'date');
    }
}

?>

Listing 3.38

---
Test:
  columns:
    datetest: date

3.4.4.14 Enum

Doctrine has a unified enum type. Enum typed columns automatically convert the string values into index numbers and vice
versa. The possible values for the column can be specified with Doctrine_Record::setEnumValues(columnName, array values).

Listing 3.39

<?php

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('enumtest', 'enum', 4,
                         array(
                            'values' => array(
                                        'php',
                                        'java',
                                        'python'
                                        )
                               )
        );
    }
}

?>

Listing 3.40

---
Test:
  columns:
    enumtest:
      type: enum
      values: [php, java, python]

3.4.4.15 Gzip

Gzip datatype is the same as string except that its automatically compressed when persisted and uncompressed when fetched.
This datatype can be useful when storing data with a large compressibility ratio, such as bitmap images.

Listing 3.41

<?php

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('gziptest', 'gzip');
    }
}

?>

Listing 3.42

---
Test:
  columns:
    gziptest: gzip

3.4.4.16 Examples

Consider the following definition:

Listing 3.43

<?php

class Example extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumns(array(
                        'id' => array(
                            'type'     => 'string',
                            'length'   => 32,
                            'fixed' => true,
                        ),
                        'someint' => array(
                            'type'     => 'integer',
                            'length'   => 10,
                            'unsigned' => true,
                        ),
                        'sometext' => array(
                            'type'   => 'string',
                            'length' => 12,
                        ),
                        'somedate' => array(
                            'type' => 'date',
                        ),
                        'sometimestamp' => array(
                            'type' => 'timestamp',
                        ),
                        'someboolean' => array(
                            'type' => 'boolean',
                        ),
                        'somedecimal' => array(
                            'type' => 'decimal',
                        ),
                        'somefloat' => array(
                            'type' => 'float',
                        ),
                        'sometime' => array(
                            'type'    => 'time',
                            'default' => '12:34:05',
                            'notnull' => true,
                        ),
                        'someclob' => array(
                            'type' => 'clob',
                        ),
                        'someblob' => array(
                            'type' => 'blob',
                        ));
}

?>

Listing 3.44

Example:
  id:
    type: string(32)
    fixed: true
  someint:
    type: integer(10)
    unsigned: true
  sometext:
    type: string(12)
  somedate: date
  sometimestamp: timestamp
  someboolean: boolean
  somedecimal: decimal
  somefloat: float
  sometime:
    type: time
    default: 12:34:05
    notnull: true
  someclob: blob
  someblob: blob
  somedate: date

The above example will create a database table as such in Pgsql:

Column	Type	Not Null	Default	comment
id	character(32)
somename	character	varying(12)
somedate	date
sometimestamp	timestamp without time zone
someboolean	boolean
somedecimal	numeric(18,2)
somefloat	double precision
sometime	time without time zone	NOT NULL	'12:34:05'
someclob	text
someblob	bytea

And the following table in Mysql:

Field	Type	Collation	Attributes	Null	Default	comment
id	char(32)			YES
somename	varchar(12)	latin1_swedish_ci		YES
somedate	date			YES
sometimestamp	timestamp without time zone			YES
someboolean	tinyint(1)			YES
somedecimal	decimal(18,2)			YES
somefloat	double			YES
sometime	time			NO	12:34:05
someclob	longtext	latin1_swedish_ci		YES
someblob	longblob		binary	YES

3.5 Constraints and validators

3.5.1 Introduction

From PostgreSQL Documentation:

Data types are a way to limit the kind of data that can be stored in a table. For many applications, however, the

constraint they provide is too coarse. For example, a column containing a product price should probably only accept
positive values. But there is no standard data type that accepts only positive numbers. Another issue is that you might
want to constrain column data with respect to other columns or rows. For example, in a table containing product
information, there should be only one row for each product number.

Doctrine allows you to define *portable* constraints on columns and tables. Constraints give you as much control over the
data in your tables as you wish. If a user attempts to store data in a column that would violate a constraint, an error
is raised. This applies even if the value came from the default value definition.

Doctrine constraints act as database level constraints as well as application level validators. This means double
security: the database doesn't allow wrong kind of values and neither does the application.

Here is a full list of available validators within Doctrine:

validator(arguments)	constraints	description
notnull	NOT NULL	Ensures the 'not null' constraint in both application and database level
email		Checks if value is valid email.
notblank	NOT NULL	Checks if value is not blank.
notnull		Checks if value is not null.
nospace		Checks if value has no space chars.
past	CHECK constraint	Checks if value is a date in the past.
future		Checks if value is a date in the future.
minlength(length)		Checks if value satisfies the minimum length.
country		Checks if value is a valid country code.
ip		Checks if value is valid IP (internet protocol) address.
htmlcolor		Checks if value is valid html color.
range(min, max)	CHECK constraint	Checks if value is in range specified by arguments.
unique	UNIQUE constraint	Checks if value is unique in its database table.
regexp(expression)		Checks if value matches a given regexp.
creditcard		Checks whether the string is a well formated credit card number
digits(int, frac)	Precision and scale	Checks if given value has int number of integer digits and frac number of fractional digits

3.5.2 Notnull

A not-null constraint simply specifies that a column must not assume the null value. A not-null constraint is always
written as a column constraint.

The following definition uses a notnull constraint for column name. This means that the specified column doesn't
accept null values.

Listing 3.45

<?php

class User extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('name', 'string', 200, array('notnull' => true,
                                                      'primary' => true));
    }
}

?>

Listing 3.46

---
User:
  columns:
    name:
      type: string(255)
      notnull: true
      primary: true

When this class gets exported to database the following SQL statement would get executed (in MySQL):

Listing 3.47

CREATE TABLE user (name VARCHAR(200) NOT NULL, PRIMARY KEY(name))

The notnull constraint also acts as an application level validator. This means that if Doctrine validators are turned
on, Doctrine will automatically check that specified columns do not contain null values when saved.

If those columns happen to contain null values Doctrine_Validator_Exception is raised.

3.5.3 Unique

Unique constraints ensure that the data contained in a column or a group of columns is unique with respect to all the
rows in the table.

In general, a unique constraint is violated when there are two or more rows in the table where the values of all of the
columns included in the constraint are equal. However, two null values are not considered equal in this comparison.
That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null
value in at least one of the constrained columns. This behavior conforms to the SQL standard, but some databases do
not follow this rule. So be careful when developing applications that are intended to be portable.

The following definition uses a unique constraint for column name.

Listing 3.48

<?php

Search

Table of Contents

0.10 User Manual

0.10 User Manual

Chapter 3 Basic schema mapping

3.1 Introduction

3.2 Table and class naming

3.3 Table options

3.4 Columns

3.4.1 Column naming

3.4.2 Column aliases

3.4.3 Default values

3.4.4 Data types

3.4.4.1 Introduction

3.4.4.2 Type modifiers

3.4.4.3 Boolean

3.4.4.4 Integer

3.4.4.5 Float

3.4.4.6 String

3.4.4.7 Array

3.4.4.8 Object

3.4.4.9 Blob

3.4.4.10 Clob

3.4.4.11 Timestamp

3.4.4.12 Time

3.4.4.13 Date

3.4.4.14 Enum

3.4.4.15 Gzip

3.4.4.16 Examples

3.5 Constraints and validators

3.5.1 Introduction

3.5.2 Notnull

3.5.3 Unique