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

can you get the mapping from the current database and make the entites? (with php doctrine orm:convert-mapping --from-database yml /path/to/mapping-path-converted-to-yml) http://docs.doctrine-project.org/en/latest/reference/tools.html#reverse-engineering — Gun2sh, Jan 08 '14 at 19:01
I don't use the ORM, only the DBAL component of Doctrine for now. I found out that Doctrine maps every tinyint (regardless its length) to boolean. So far I can't figure out why. — common sense, Jan 09 '14 at 10:04

score 0 · Accepted Answer · answered Jan 11 '14 at 13:32

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

Hi, did you found maybe a solution to this problem? I'm too migrating some legacy DB and would like to use it without any modification (for now) — Konrad Podgórski, Feb 27 '14 at 09:52

score -1 · Answer 2 · answered Feb 27 '14 at 10:37

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

Do assume you are using ORM, do you? I didn't figured out how to use a YAML config file only with the DBAL component of Doctrine. — common sense, Feb 28 '14 at 10:10

Type mapping in Doctrine 2 DBAL

2 Answers2