Doctrine - Doctrine ORM for PHP

Columns

One problem with database compatibility is that many databases differ in their behavior 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.

In Doctrine columns and column aliases are case sensitive. So when you are using columns in your DQL queries, the column/field names must match the case in your model definition.

Column Lengths

In Doctrine column length is an 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.

The length is different depending on the type of column you are using:

integer - Length is the the number of bytes the integer occupies.
string - Number of the characters allowed in the string.
float/decimal - Total number of characters allowed excluding the decimal.
enum - If using native enum length does not apply but if using emulated enums then it is just the string length of the column value.

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.

// models/Book.php

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

Here is the same example in YAML format. You can read more about YAML in the YAML Schema Files chapter:

---
# schema.yml

# ...
Book:
  columns:
    bookTitle:
      name: bookTitle as title
      type: string

Now the column in the database is named bookTitle but you can access the property on your objects using title.

// test.php

// ...
$book = new Book();
$book->title = 'Some book';
$book->save();

Default values

Doctrine supports default values for all data types. When default value is attached to a record column this means two things. First this value is attached to every newly created Record and when Doctrine creates your database tables it includes the default value in the create table statement.

// models/generated/BaseUser.php

class User extends BaseUser
{
    public function setTableDefinition()
    {
        $this->hasColumn('username', 'string', 255, array('default' => 'default username'));

        // ...
    }

    // ...
}

Here is the same example in YAML format. You can read more about YAML in the YAML Schema Files chapter:

---
# schema.yml

# ...
User:
# ...
  columns:
    username:
      type: string(255)
      default: default username
# ...

Now when you print the name on a brand new User record it will print the default value:

// test.php

// ...
$user = new User();
echo $user->username; // default username

Data types

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, a base set of data types was defined. Applications may access them 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.

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.
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)

'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!

'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.

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.

The next several examples are not meant for you to use and give them a try. They are simply for demonstrating purposes to show you how to use the different Doctrine data types using PHP code or YAML schema files.

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

Here is the same example in YAML format. You can read more about YAML in the YAML Schema Files chapter:

---
Test:
  columns:
    booltest: boolean

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.

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

Here is the same example in YAML format. You can read more about YAML in the YAML Schema Files chapter:

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

Float

The float data type may store floating point decimal numbers. This data type is suitable for representing numbers withina 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.

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

Here is the same example in YAML format. You can read more about YAML in the YAML Schema Files chapter:

---
Test:
  columns:
    floattest: float

Decimal

The decimal data type may store fixed precision decimal numbers. This data type is suitable for representing numbers that require high precision and accuracy.

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

Here is the same example in YAML format. You can read more about YAML in the YAML Schema Files chapter:

---
Test:
  columns:
    decimaltest: decimal

You can specify the length of the decimal just like you would set the length of any other column and you can specify the scale as an option in the third argument:

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('decimaltest', 'decimal', 18, array(
                'scale' => 2
            )
        );
    }
}

Here is the same example in YAML format. You can read more about YAML in the YAML Schema Files chapter:

---
Test:
  columns:
    decimaltest:
      type: decimal(18)
      scale: 2

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.

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

Here is the same example in YAML format. You can read more about YAML in the YAML Schema Files chapter:

---
Test:
  columns:
    stringtest:
      type: string(200)
      fixed: true

Array

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

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

Here is the same example in YAML format. You can read more about YAML in the YAML Schema Files chapter:

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

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.

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

Here is the same example in YAML format. You can read more about YAML in the YAML Schema Files chapter:

---
Test:
  columns:
    objecttest: object

The array and object types simply serialize the data when persisting to the database and unserialize the data when pulling from the database.

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".

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

Here is the same example in YAML format. You can read more about YAML in the YAML Schema Files chapter:

---
Test:
  columns:
    blobtest: blob

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".

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

Here is the same example in YAML format. You can read more about YAML in the YAML Schema Files chapter:

---
Test:
  columns:
    clobtest: clob

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.

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

Here is the same example in YAML format. You can read more about YAML in the YAML Schema Files chapter:

---
Test:
  columns:
    timestamptest: timestamp

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.

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

Here is the same example in YAML format. You can read more about YAML in the YAML Schema Files chapter:

---
Test:
  columns:
    timetest: time

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.

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

Here is the same example in YAML format. You can read more about YAML in the YAML Schema Files chapter:

---
Test:
  columns:
    datetest: date

Enum

Doctrine has a unified enum type. The possible values for the column can be specified on the column definition with Doctrine_Record::hasColumn()

If you wish to use native enum types for your DBMS if it supports it then you must set the following attribute:

$conn->setAttribute(Doctrine_Core::ATTR_USE_NATIVE_ENUM, true);

Here is an example of how to specify the enum values:

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

Here is the same example in YAML format. You can read more about YAML in the YAML Schema Files chapter:

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

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.

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

Here is the same example in YAML format. You can read more about YAML in the YAML Schema Files chapter:

---
Test:
  columns:
    gziptest: gzip

The family of php functions for compressing are used internally for compressing and uncompressing the contents of the gzip column type.

Examples

Consider the following definition:

class Example extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('id', 'string', 32, array(
                'type' => 'string',
                'fixed' => 1,
                'primary' => true,
                'length' => '32'
            )
        );

        $this->hasColumn('someint', 'integer', 10, array(
                'type' => 'integer',
                'unsigned' => true,
                'length' => '10'
            )
        );

        $this->hasColumn('sometime', 'time', 25, array(
                'type' => 'time',
                'default' => '12:34:05',
                'notnull' => true,
                'length' => '25'
            )
        );

        $this->hasColumn('sometext', 'string', 12, array(
                'type' => 'string',
                'length' => '12'
            )
        );

        $this->hasColumn('somedate', 'date', 25, array(
                'type' => 'date',
                'length' => '25'
            )
        );

        $this->hasColumn('sometimestamp', 'timestamp', 25, array(
                'type' => 'timestamp',
                'length' => '25'
            )
        );

        $this->hasColumn('someboolean', 'boolean', 25, array(
                'type' => 'boolean',
                'length' => '25'
            )
        );

        $this->hasColumn('somedecimal', 'decimal', 18, array(
                'type' => 'decimal',
                'length' => '18'
            )
        );

        $this->hasColumn('somefloat', 'float', 2147483647, array(
                'type' => 'float',
                'length' => '2147483647'
            )
        );

        $this->hasColumn('someclob', 'clob', 2147483647, array(
                'type' => 'clob',
                'length' => '2147483647'
            )
        );

        $this->hasColumn('someblob', 'blob', 2147483647, array(
                'type' => 'blob',
                'length' => '2147483647'
            )
        );
    }
}

Here is the same example in YAML format. You can read more about YAML in the YAML Schema Files chapter:

Example:
  tableName: example
  columns:
    id:
      type: string(32)
      fixed: true
      primary: true
    someint:
      type: integer(10)
      unsigned: true
    sometime:
      type: time(25)
      default: '12:34:05'
      notnull: true
    sometext: string(12)
    somedate: date(25)
    sometimestamp: timestamp(25)
    someboolean: boolean(25)
    somedecimal: decimal(18)
    somefloat: float(2147483647)
    someclob: clob(2147483647)
    someblob: blob(2147483647)

The above example will create the following database table in Pgsql:

Column	Type
`id`	`character(32)`
`someint`	`integer`
`sometime`	`time` without time zone
`sometext`	`character` or `varying(12)`
`somedate`	`date`
`sometimestamp`	`timestamp` without time zone
`someboolean`	`boolean`
`somedecimal`	`numeric(18,2)`
`somefloat`	`double` precision
`someclob`	`text`
`someblob`	`bytea`

The schema will create the following database table in Mysql:

Field	Type
`id`	`char(32)`
`someint`	`integer`
`sometime`	`time`
`sometext`	`varchar(12)`
`somedate`	`date`
`sometimestamp`	`timestamp`
`someboolean`	`tinyint(1)`
`somedecimal`	`decimal(18,2)`
`somefloat`	`double`
`someclob`	`longtext`
`someblob`	`longblob`

Relationships >>

Questions and Feedback

If you find a problem with the documentation or have a suggestion, please register and open a ticket.

If you need support or have a technical question, you can post to the user mailing-list.

Columns

Table of Contents

Columns

Questions and Feedback