Doctrine 2 - ORM

Schema problem with Postgresql

Details

  • Type: Bug Bug
  • Status: Resolved Resolved
  • Priority: Major Major
  • Resolution: Invalid
  • Affects Version/s: None
  • Fix Version/s: 2.0.1
  • Component/s: Tools
  • Security Level: All
  • Labels:
    None
  • Environment:
    Windows XP SP 3
    Postgresql 9.0
    PHP 5.3

Description

Hi, I have a problem when using doctrine 2 with schema in Postgresql.

This is my php mapping:

<?php

use Doctrine\ORM\Mapping\ClassMetadataInfo;

$metadata->setInheritanceType(ClassMetadataInfo::INHERITANCE_TYPE_NONE);
$metadata->setPrimaryTable(array(
    'name' => 'sales.mdp',
));

$metadata->setChangeTrackingPolicy(ClassMetadataInfo::CHANGETRACKING_DEFERRED_IMPLICIT);
$metadata->setIdGeneratorType(ClassMetadataInfo::GENERATOR_TYPE_SEQUENCE);
$metadata->setSequenceGeneratorDefinition(array(
        'sequenceName'   => 'sales.mdp_id_seq',
        'allocationSize' => 10,
        'initialValue'   => 1,
    ));
    
$metadata->mapField(array(
   'id'         => true,
   'fieldName'  => 'id',
   'columnName' => 'id',
   'type'       => 'integer',
  ));

I can generate tables successfully with orm:schema-tool:create.
I don't edit anything, and just try to orm:schema-tool:update, the output should be:

Nothing to update. The database is in sync with the current entity metadata.

But I get:

Updating database schema...

  [PDOException]

  SQLSTATE[42P07]: Duplicate table: 7 ERROR:  relation "mdp_id_seq" already exists

my orm:schema-tool:update --dump-sql outputs

CREATE SEQUENCE sales.mdp_id_seq INCREMENT BY 10 MINVALUE 1 START 1;
CREATE TABLE sales.mdp (id INT NOT NULL, rrn VARCHAR(50) NOT NULL, model VARCHAR(255) DEFAULT NULL, tipe VARCHAR(255) DEFAULT NULL, warna VARCHAR(50) DEFAULT NULL, nama_pelanggan VARCHAR(255) DEFAULT NULL, sales VARCHAR(255) DEFAULT NULL, keterangan TEXT DEFAULT NULL, tanggal DATE NOT NULL, created_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, PRIMARY KEY(id));
CREATE UNIQUE INDEX sales_mdp_rrn_uniq ON sales.mdp (rrn)

I upload my files in attachment. Please advice, thanks.

  1. File
    cli-config.php
    13/Dec/10 9:13 PM
    1 kB
    Andy Aja deh
  2. File
    Mdp.php
    13/Dec/10 9:13 PM
    2 kB
    Andy Aja deh
  3. File
    Sales.Domain.Kendaraan.php
    14/Dec/10 10:58 PM
    2 kB
    Andy Aja deh
  4. File
    Sales.Domain.Master.Cabang.php
    14/Dec/10 10:58 PM
    1 kB
    Andy Aja deh
  5. File
    Sales.Domain.Mdp.php
    13/Dec/10 9:13 PM
    2 kB
    Andy Aja deh

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Benjamin Eberlei added a comment -

Are you using RC2? If not please try it with RC2, i fixed a bug regarding this just some days ago.

Show
Benjamin Eberlei added a comment - Are you using RC2? If not please try it with RC2, i fixed a bug regarding this just some days ago.
Hide
Permalink
Andy Aja deh added a comment - - edited

Ic, I updated to RC2 and this issue was solved. Thanks Ben.

But now, I get another problem. When I add a new class & mapping with no schema, it is generated in the previous mapped schema.
Is it the way it should works? and if I want to map to public schema, do I need to explicitly write "public.table_name" ?

Here is my new mapping:

<?php

use Doctrine\ORM\Mapping\ClassMetadataInfo;

$metadata->setInheritanceType(ClassMetadataInfo::INHERITANCE_TYPE_NONE);
$metadata->setPrimaryTable(array(
   'name' => 'cabang',
));

$metadata->setChangeTrackingPolicy(ClassMetadataInfo::CHANGETRACKING_DEFERRED_IMPLICIT);
$metadata->setIdGeneratorType(ClassMetadataInfo::GENERATOR_TYPE_SEQUENCE);
$metadata->setSequenceGeneratorDefinition(array(
        'sequenceName'   => 'cabang_id_seq',
        'allocationSize' => 10,
        'initialValue'   => 1,
    ));

$metadata->mapField(array(
   'id'         => true,
   'fieldName'  => 'id',
   'columnName' => 'id',
   'type'       => 'integer',
   'unsigned'   => false,
  ));


<?php

use Doctrine\ORM\Mapping\ClassMetadataInfo;

$metadata->setInheritanceType(ClassMetadataInfo::INHERITANCE_TYPE_NONE);
$metadata->setPrimaryTable(array(
   'name' => 'public.kendaraan',
));

$metadata->setChangeTrackingPolicy(ClassMetadataInfo::CHANGETRACKING_DEFERRED_IMPLICIT);
$metadata->setIdGeneratorType(ClassMetadataInfo::GENERATOR_TYPE_SEQUENCE);
$metadata->setSequenceGeneratorDefinition(array(
        'sequenceName'   => 'public.kendaraan_id_seq',
        'allocationSize' => 10,
        'initialValue'   => 1,
    ));

$metadata->mapField(array(
   'id'         => true,
   'fieldName'  => 'id',
   'columnName' => 'id',
   'type'       => 'integer',
   'unsigned'   => false,
  ));

I get the following:

The table cabang, is not generated in public schema, but in sales schema (previously mapped sales.mdp), .
The table public.kendaraan is generated correctly,
But even if I write explicitly "public.kendaraan", (I don't edit anything, and just try to orm:schema-tool:update) the --dump-sql still outputs:

CREATE SEQUENCE public.kendaraan_id_seq INCREMENT BY 10 MINVALUE 1 START 1;
CREATE SEQUENCE cabang_id_seq INCREMENT BY 10 MINVALUE 1 START 1;
CREATE TABLE public.kendaraan (id INT NOT NULL, tipe_kendaraan VARCHAR(255) NOT
NULL, warna VARCHAR(255) NOT NULL, no_rangka VARCHAR(255) NOT NULL, no_mesin VARCHAR(255) NOT NULL, tahun INT NOT NULL, rrn VARCHAR(255) NOT NULL, salesman VARCHAR(255) DEFAULT NULL, pelanggan VARCHAR(255) DEFAULT NULL, keterangan VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id));
CREATE UNIQUE INDEX public_kendaraan_no_rangka_uniq ON public.kendaraan (no_rangka);
CREATE UNIQUE INDEX public_kendaraan_no_mesin_uniq ON public.kendaraan (no_mesin);
CREATE UNIQUE INDEX public_kendaraan_rrn_uniq ON public.kendaraan (rrn);
CREATE TABLE cabang (id INT NOT NULL, kode VARCHAR(255) NOT NULL, nama VARCHAR(255) NOT NULL, alamat VARCHAR(255) NOT NULL, PRIMARY KEY(id));
CREATE UNIQUE INDEX cabang_kode_uniq ON cabang (kode);
CREATE UNIQUE INDEX cabang_nama_uniq ON cabang (nama)

Thanks for helping.

Show
Andy Aja deh added a comment - - edited Ic, I updated to RC2 and this issue was solved. Thanks Ben. But now, I get another problem. When I add a new class & mapping with no schema , it is generated in the previous mapped schema. Is it the way it should works? and if I want to map to public schema, do I need to explicitly write "public.table_name" ? Here is my new mapping: <?php use Doctrine\ORM\Mapping\ClassMetadataInfo; $metadata->setInheritanceType(ClassMetadataInfo::INHERITANCE_TYPE_NONE); $metadata->setPrimaryTable(array( 'name' => 'cabang', )); $metadata->setChangeTrackingPolicy(ClassMetadataInfo::CHANGETRACKING_DEFERRED_IMPLICIT); $metadata->setIdGeneratorType(ClassMetadataInfo::GENERATOR_TYPE_SEQUENCE); $metadata->setSequenceGeneratorDefinition(array( 'sequenceName' => 'cabang_id_seq', 'allocationSize' => 10, 'initialValue' => 1, )); $metadata->mapField(array( 'id' => true , 'fieldName' => 'id', 'columnName' => 'id', 'type' => 'integer', 'unsigned' => false , )); <?php use Doctrine\ORM\Mapping\ClassMetadataInfo; $metadata->setInheritanceType(ClassMetadataInfo::INHERITANCE_TYPE_NONE); $metadata->setPrimaryTable(array( 'name' => ' public .kendaraan', )); $metadata->setChangeTrackingPolicy(ClassMetadataInfo::CHANGETRACKING_DEFERRED_IMPLICIT); $metadata->setIdGeneratorType(ClassMetadataInfo::GENERATOR_TYPE_SEQUENCE); $metadata->setSequenceGeneratorDefinition(array( 'sequenceName' => ' public .kendaraan_id_seq', 'allocationSize' => 10, 'initialValue' => 1, )); $metadata->mapField(array( 'id' => true , 'fieldName' => 'id', 'columnName' => 'id', 'type' => 'integer', 'unsigned' => false , )); I get the following: The table cabang , is not generated in public schema, but in sales schema (previously mapped sales.mdp), . The table public.kendaraan is generated correctly, But even if I write explicitly "public.kendaraan", (I don't edit anything, and just try to orm:schema-tool:update ) the --dump-sql still outputs: CREATE SEQUENCE public .kendaraan_id_seq INCREMENT BY 10 MINVALUE 1 START 1; CREATE SEQUENCE cabang_id_seq INCREMENT BY 10 MINVALUE 1 START 1; CREATE TABLE public .kendaraan (id INT NOT NULL, tipe_kendaraan VARCHAR(255) NOT NULL, warna VARCHAR(255) NOT NULL, no_rangka VARCHAR(255) NOT NULL, no_mesin VARCHAR(255) NOT NULL, tahun INT NOT NULL, rrn VARCHAR(255) NOT NULL, salesman VARCHAR(255) DEFAULT NULL, pelanggan VARCHAR(255) DEFAULT NULL, keterangan VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id)); CREATE UNIQUE INDEX public_kendaraan_no_rangka_uniq ON public .kendaraan (no_rangka); CREATE UNIQUE INDEX public_kendaraan_no_mesin_uniq ON public .kendaraan (no_mesin); CREATE UNIQUE INDEX public_kendaraan_rrn_uniq ON public .kendaraan (rrn); CREATE TABLE cabang (id INT NOT NULL, kode VARCHAR(255) NOT NULL, nama VARCHAR(255) NOT NULL, alamat VARCHAR(255) NOT NULL, PRIMARY KEY(id)); CREATE UNIQUE INDEX cabang_kode_uniq ON cabang (kode); CREATE UNIQUE INDEX cabang_nama_uniq ON cabang (nama) Thanks for helping.
Hide
Permalink
Benjamin Eberlei added a comment -

hm, maybe i understand it wrong but arent unqualified tables ALWAYS generated into the public schema?

Executing your code with "CREATE ETABLE kenderaan" (without public. prefix) it still puts it into the public schema, not into sales.

Show
Benjamin Eberlei added a comment - hm, maybe i understand it wrong but arent unqualified tables ALWAYS generated into the public schema? Executing your code with "CREATE ETABLE kenderaan" (without public. prefix) it still puts it into the public schema, not into sales.
Hide
Permalink
Andy Aja deh added a comment -

Yes, like you said. The unqualified tables should always be generated into public schema. And my cases here are:

1. my unqualified table: cabang is not generated in public schema, but in sales schema.

2. my fully qualified table: public.kendaraan is generated correctly in public schema then I don't edit anything, and just try to orm:schema-tool:update), expected --dump-sql is empty, but I get:

CREATE SEQUENCE public.kendaraan_id_seq INCREMENT BY 10 MINVALUE 1 START 1;
CREATE SEQUENCE cabang_id_seq INCREMENT BY 10 MINVALUE 1 START 1;
CREATE TABLE public.kendaraan (id INT NOT NULL, tipe_kendaraan VARCHAR(255) NOT
NULL, warna VARCHAR(255) NOT NULL, no_rangka VARCHAR(255) NOT NULL, no_mesin VARCHAR(255) NOT NULL, tahun INT NOT NULL, rrn VARCHAR(255) NOT NULL, salesman VARCHAR(255) DEFAULT NULL, pelanggan VARCHAR(255) DEFAULT NULL, keterangan VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id));
CREATE UNIQUE INDEX public_kendaraan_no_rangka_uniq ON public.kendaraan (no_rangka);
CREATE UNIQUE INDEX public_kendaraan_no_mesin_uniq ON public.kendaraan (no_mesin);
CREATE UNIQUE INDEX public_kendaraan_rrn_uniq ON public.kendaraan (rrn);
CREATE TABLE cabang (id INT NOT NULL, kode VARCHAR(255) NOT NULL, nama VARCHAR(255) NOT NULL, alamat VARCHAR(255) NOT NULL, PRIMARY KEY(id));
CREATE UNIQUE INDEX cabang_kode_uniq ON cabang (kode);
CREATE UNIQUE INDEX cabang_nama_uniq ON cabang (nama)
Show
Andy Aja deh added a comment - Yes, like you said. The unqualified tables should always be generated into public schema. And my cases here are: 1. my unqualified table: cabang is not generated in public schema, but in sales schema. 2. my fully qualified table: public.kendaraan is generated correctly in public schema then I don't edit anything, and just try to orm:schema-tool:update), expected --dump-sql is empty, but I get: CREATE SEQUENCE public .kendaraan_id_seq INCREMENT BY 10 MINVALUE 1 START 1; CREATE SEQUENCE cabang_id_seq INCREMENT BY 10 MINVALUE 1 START 1; CREATE TABLE public .kendaraan (id INT NOT NULL, tipe_kendaraan VARCHAR(255) NOT NULL, warna VARCHAR(255) NOT NULL, no_rangka VARCHAR(255) NOT NULL, no_mesin VARCHAR(255) NOT NULL, tahun INT NOT NULL, rrn VARCHAR(255) NOT NULL, salesman VARCHAR(255) DEFAULT NULL, pelanggan VARCHAR(255) DEFAULT NULL, keterangan VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id)); CREATE UNIQUE INDEX public_kendaraan_no_rangka_uniq ON public .kendaraan (no_rangka); CREATE UNIQUE INDEX public_kendaraan_no_mesin_uniq ON public .kendaraan (no_mesin); CREATE UNIQUE INDEX public_kendaraan_rrn_uniq ON public .kendaraan (rrn); CREATE TABLE cabang (id INT NOT NULL, kode VARCHAR(255) NOT NULL, nama VARCHAR(255) NOT NULL, alamat VARCHAR(255) NOT NULL, PRIMARY KEY(id)); CREATE UNIQUE INDEX cabang_kode_uniq ON cabang (kode); CREATE UNIQUE INDEX cabang_nama_uniq ON cabang (nama)
Hide
Permalink
Benjamin Eberlei added a comment -

is your database user named "cabang"? Unqualified is defined by some include path equivalent in PostgreSQL.

Show
Benjamin Eberlei added a comment - is your database user named "cabang"? Unqualified is defined by some include path equivalent in PostgreSQL.
Hide
Permalink
Benjamin Eberlei added a comment -

See 5.7.3 here http://www.postgresql.org/docs/current/static/ddl-schemas.html

Show
Benjamin Eberlei added a comment - See 5.7.3 here http://www.postgresql.org/docs/current/static/ddl-schemas.html
Hide
Permalink
Andy Aja deh added a comment -

Oic, my database username is "sales", so the unqualified schema is generated in "sales" schema.

Thanks a lot for your help.

Show
Andy Aja deh added a comment - Oic, my database username is "sales", so the unqualified schema is generated in "sales" schema. Thanks a lot for your help.
Hide
Permalink
Andy Aja deh added a comment -

This is not doctrine's bug, It is my miss understanding about schema search path behavior in postgresql. Thanks.

Show
Andy Aja deh added a comment - This is not doctrine's bug, It is my miss understanding about schema search path behavior in postgresql. Thanks.

People

  • Assignee:
    Roman S. Borschel
    Reporter:
    Andy Aja deh
Vote (0)
Watch (0)

Dates

  • Created:
    Updated:
    Resolved: