Skip to content

Loading…

DDC-932: Schema problem with Postgresql #5461

Closed
doctrinebot opened this Issue · 10 comments

1 participant

@doctrinebot

Jira issue originally created by user andyajadeh:

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.

@doctrinebot

Comment created by @beberlei:

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

@doctrinebot

Comment created by andyajadeh:

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.

@doctrinebot

Comment created by @beberlei:

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.

@doctrinebot

Comment created by andyajadeh:

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)
@doctrinebot

Comment created by @beberlei:

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

@doctrinebot

Comment created by andyajadeh:

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

Thanks a lot for your help.

@doctrinebot

Comment created by andyajadeh:

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

@doctrinebot

Issue was closed with resolution "Invalid"

@doctrinebot doctrinebot added this to the 2.0.1 milestone
@doctrinebot doctrinebot closed this
@doctrinebot doctrinebot added the Bug label
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.