Type mapping in Doctrine 2 DBAL

Question

I am using only the DBAL part of Doctrine for my project. So I have to care over the mapping myself. I figured out that Doctrine remaps database types to a generic PHP type as you can read here [1].

Now I came across the fact that Doctrine maps the DB type TinyInt(4) to Boolean. According to the MySQL manual only TinyInt(1) is equivalent to Boolean [2]. Since my project is a legacy one which I like to move from mysqli to Doctrine, I need to stick on the native DB Types.

My question is now how I get the native db type out of Doctrine?

Thanks in advance for your help.

Stefano

[1] http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/types.html [2] http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

Answer 1

I opend up a report in Doctrine issue tracker to figure out why Doctrine works like that. The answer is simple:

The length of TinyInt doesn't say anything about the size of the value which could be stored. Its in any case 1 Byte, but it effects the filling zeros in case you use this option for your field.

Doctrine maps his own boolean type to the mysql tinyint type because MySQL don't provide an own boolean type.

For more informations you can read more on the ticket:

http://www.doctrine-project.org/jira/browse/DBAL-781

Answer 2

For those who map legacy mysql db to the doctrine I've found solution for common problem that is easy to miss when dealing with TINYINT(4)

TL;DR

change default: '1' in field mapping to

is_active:
    type: boolean
    options:
        default: true
    nullable: true

Long version

I had the following db structure in legacy DB, please notice tinyint(4) in is_active column

CREATE TABLE IF NOT EXISTS `foo_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
...
  `is_active` tinyint(4) DEFAULT '1',
...

after running the following commands

php app/console doctrine:mapping:import --force AcmeDemoBundle yml
php app/console doctrine:generate:entities AcmeDemoBundle

and then while running php app/console doctrine:schema:update --dump-sql I ended up with diff while

ALTER TABLE foo_table CHANGE is_active is_active TINYINT(1) DEFAULT NULL;

I couldn't make any modification to the existing DB (but even doing so didn't solve problem, I was still getting this diff)

yml mapping had following field definition

is_active:
    type: boolean
    nullable: true
    default: '1'
    column: is_active

I tried with

is_active:
    columnDefinition: "TINYINT(4)"
    nullable: true
    default: '1'
    column: is_active

without success, then following very common rule in programming "If you really don't anything wrong with the code then the problem is somewhere else" I found the solution

Solution

is_active:
    type: boolean
    options:
        default: true
    nullable: true

now, even with using simply type: boolean it recognizes tinyint(4) correctly