Case with doctrine2, symfony2 and postgresql entities

Question

I have a problem with doctrine2 in symfony2 app with postgres database.

I get error:

SQLSTATE[3F000]: Invalid schema name: 7 ERROR: schema "main" does not exist

Problem is that my schema is Main not main. When I rename it, similar thing happends for table relation:

SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "main.brand_brandid_seq" does not exist

Problem is case sensitivity and I guess maybe it have something to do with quoting or some doctrine configuration.

Entity:

namespace MyB\Entity;

/**
 * MyB\Entity\Brand
 *
 * @orm:Table(name="Main.Brand")
 * @orm:Entity
 */
class Brand
{
    /**
     * @var integer $brandid
     *
     * @orm:Column(name="BrandId", type="integer", nullable=false)
     * @orm:Id
     * @orm:GeneratedValue(strategy="SEQUENCE")
     * @orm:SequenceGenerator(sequenceName="Main.Brand_BrandId_seq", allocationSize="1", initialValue="1")
     */
    private $brandid;

    /**
     * @var string $brandname
     *
     * @orm:Column(name="BrandName", type="string", length=32, nullable=false)
     */
    private $brandname;

    /**
     * Set name.
     *
     * @param string $name
     */
    public function setName($name) {
        $this->brandname = $name;
    }
}

Schema:

SET search_path = "Main", pg_catalog;

CREATE SEQUENCE "Brand_BrandId_seq"
    START WITH 2
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


SET default_tablespace = '';

SET default_with_oids = false;


CREATE TABLE "Brand" (
    "BrandId" integer DEFAULT nextval('"Brand_BrandId_seq"'::regclass) NOT NULL,
    "BrandName" character varying(32) NOT NULL
);

Controller:

        $reseller = new \MyB\Entity\Brand();
        $reseller->setName('Sasa');

        $em = $this->get('doctrine.orm.entity_manager');
        $em->persist($reseller);
        $em->flush();

Any idea?

score 4 · Accepted Answer · edited Apr 12 '11 at 07:51

Try this

namespace MyB\Entity;

/**
 * MyB\Entity\Brand
 *
 * @orm:Table(name="""Main"".""Brand""")
 * @orm:Entity
 */
class Brand
{
    /**
     * @var integer $brandid
     *
     * @orm:Column(name="""BrandId""", type="integer", nullable=false)
     * @orm:Id
     * @orm:GeneratedValue(strategy="SEQUENCE")
     * @orm:SequenceGenerator(sequenceName="""Main"".""Brand_BrandId_seq""", allocationSize="1", initialValue="1")
     */
    private $brandid;

    /**
     * @var string $brandname
     *
     * @orm:Column(name="""BrandName""", type="string", length=32, nullable=false)
     */
    private $brandname;

    /**
     * Set name.
     *
     * @param string $name
     */
    public function setName($name) {
        $this->brandname = $name;
    }
}

In postgres every word case sensitive must be escape.

score 1 · Answer 2 · answered Dec 24 '13 at 11:13

1

When using escaped table names take care about this "bug" : https://github.com/doctrine/doctrine2/pull/615 . Doctrine takes the first character of the table name as aliasprefix and thus a quote is used, crushing all your SQLs

answered Dec 24 '13 at 11:13

user3132304

11
1

score 0 · Answer 3 · answered Dec 02 '15 at 08:56

0

If you are using migration files on Laravel. Change Schema::table to Schema::create. This might help someone.

answered Dec 02 '15 at 08:56

Oladipo

1,579
3
17
33

score 0 · Answer 4 · answered Apr 07 '11 at 06:59

0

I write PpSqlBundle but it isn't finished yet, but you could try, I think it should work. It can generate form db same as in symfony. in config.yml you should have:

doctrine:
dbal:
    default_connection: default
    connections:
        default:
            driver:   
            dbname:   
            host:     
            user:     
            password: 
            driverClass: PgSqlBundle\Doctrine\DBAL\Driver\PDOPgSql\Driver # it's important
            logging:

and you should use command

app/console doctrine:mapping:import Yourbundlename annotation

https://github.com/mstrzele/PgSqlBundle

answered Apr 07 '11 at 06:59

mstrzele

66
3

Thanks, but it throws: The given 'driverClass' PgSqlBundle\Doctrine\DBAL\Driver\PDOPgSql\Driver has to implement the \Doctrine\DBAL\Driver interface. – umpirsky Apr 07 '11 at 07:38
And it does. To install you should have i to register namespace: – mstrzele Apr 07 '11 at 07:54
for example: 'PgSqlBundle' => __DIR__.'/../src', and register bundle: new PgSqlBundle\PgSqlBundle(), – mstrzele Apr 07 '11 at 07:55
I use symfony2 pr10 with vendors – mstrzele Apr 07 '11 at 07:56
Yes, thanks. But there is no gSqlBundle\PgSqlBundle class on https://github.com/mstrzele/PgSqlBundle. – umpirsky Apr 07 '11 at 08:12
After adding it I got: Warning: Missing argument 1 for PgSqlBundle\Doctrine\DBAL\Schema\PostgreSqlSchemaManager::initConfig(), Warning: Missing argument 2 for PgSqlBundle\Doctrine\DBAL\Schema\PostgreSqlSchemaManager::initConfig(), Notice: Undefined variable: name in PgSqlBundle\Doctrine\DBAL\Schema\PostgreSqlSchemaManager.php on line 414 [Doctrine\DBAL\DBALException] Unknown column type datetimepgsql requested. – umpirsky Apr 07 '11 at 08:16
Cause you don't have good file. Now it is in github. It is my fault I forggot to push it. – mstrzele Apr 07 '11 at 08:21
Thanks. Now I got [Doctrine\DBAL\Schema\SchemaException] Index '' does not exist on table 'Main"."SomeTable'. Seems like my table: -- Table: "Main"."SomeTable" -- DROP TABLE "Main"."SomeTable"; CREATE TABLE "Main"."SomeTable" ( "SomeTableId" integer NOT NULL DEFAULT nextval('"Main"."SomeTable_SomeTable_seq"'::regclass), "ProductId" integer, "SomeTableId" integer ) WITH ( OIDS=FALSE ); ALTER TABLE "Main"."SomeTable" OWNER TO postgres; is bad. – umpirsky Apr 07 '11 at 08:25
In doctrine2 you must have indexes on every column with foreign key and primary key must be on every table, even in many to many tables,it that case you have primary key on two column. – mstrzele Apr 07 '11 at 08:29
As I see you don't have primary key on "Main"."SomeTable" – mstrzele Apr 07 '11 at 08:35
Added it, now I get : Importing mapping information from "default" entity manager > writing MyB/Entity/Base/System.SomeTableMappedSuperclass.php > writing MyB/Entity/System.SomeTable.php Notice: Undefined property: Doctrine\ORM\Mapping\ClassMetadataInfo::$tableName in PgSqlBundle\Doctrine\ORM\Tools\EntityGenerator.php on line 871 Parse error: syntax error, unexpected '.', expecting '{' in MyB\Entity\Base\System.SomeTableMappedSuperclass.php on line 10 – umpirsky Apr 07 '11 at 08:39
Looks like class names are in format Schema.TableNameMappedSuperclass, and this dot is what causing an error. Isn't it better to have namespace under Entity namespage for each schema? Any suggestion how to fix this? P.S. Thank you very much for the effort! – umpirsky Apr 07 '11 at 08:51
In one file was bug in case sensitive. On github is fixed – mstrzele Apr 07 '11 at 08:52
Notice: Undefined property: Doctrine\ORM\Mapping\ClassMetadataInfo::$tableName in PgSqlBundle\Doctrine\ORM\Tools\EntityGenerator.php on line 871 I can send you generated entity if you prefer. Parse error: syntax error, unexpected '.', expecting '{' in MyB\Entity\Base\System.SomeTableMappedSuperclass.php on line 10 – umpirsky Apr 07 '11 at 09:02
Great, thanks. Now it generates entities, but I get a number of notices like this: Notice: Undefined property: Doctrine\ORM\Mapping\ClassMetadataInfo::$tableName in PgSqlBundle\Doctrine\ORM\Tools\EntityGenerator.php on line 871 – umpirsky Apr 07 '11 at 09:56
And some of my tables and schemas have names that are PHP keywords like global and switch :) Example: public function setSwitchid(\MyB\Entity\Global\Switch $switchid) { $this->switchid = $switchid; } and then I get error for that line: Parse error: syntax error, unexpected T_SWITCH, expecting T_STRING in MyB\Entity\System\Base\SomeTableMappedSuperclass.php on line 176 and it stops generating! – umpirsky Apr 07 '11 at 09:57
And some namespace declaration are invalid, because they contain keywords. Can that be escaped somehow? – umpirsky Apr 07 '11 at 09:58
It's may git-plugin in eclipse I had some problems. How it's up to date. – mstrzele Apr 07 '11 at 10:01
Can I send you some files via email or somehow? – umpirsky Apr 07 '11 at 10:05
Parse error: syntax error, unexpected T_SWITCH, expecting T_STRING in MyB\Entity\System\Base\SomeTableSuperclass.php on line 176, and line 176 says: public function setSwitchid(\MyB\Entity\System\Switch $switchid); Class name is PHP keyword, that is the problem. – umpirsky Apr 07 '11 at 10:07
Yes, it reached me. Word global and switch are keywords and you cannot use it in classname namespace nor method name. The thing what it should be done is use naming strategy in generating process. Doctrine2 don't have such machanizm, but my plugin will be have in next week. Before that everything what you can do is change manualy your entities or make change in your database. I notice that it is problem with sequence name in your entities but I am fixing that and for the moment fix will be in github. – mstrzele Apr 07 '11 at 10:42
Can you just tell me where can I apply changes to your code to change anmespaces to have name SchemaSchemaName/TableNameEntity? Thanks. – umpirsky Apr 07 '11 at 11:17
PgSqlBundle/Doctrine/ORM/Mapping/Driver/DatabaseDriver.php, line 108 change $className = Inflector::classify(strtolower($tableName)); to – mstrzele Apr 07 '11 at 11:27
$className = 'Schema'. Inflector::classify(strtolower($tableName)) . 'Entity'; – mstrzele Apr 07 '11 at 11:27
But shortly will be posibility in config to bind custom naming strategy – mstrzele Apr 07 '11 at 11:28
sequencers name are now fixed in github – mstrzele Apr 07 '11 at 11:29
Argh, now it throws PDOException SQLSTATE[42602]: Invalid name: 7 ERROR: invalid name syntax LINE 1: SELECT NEXTVAL('"Main"."DeviceType"_"DeviceTypeId"_seq') when I try to write to db :( – umpirsky Apr 07 '11 at 12:00
This error happends when I remove driverClass: PgSqlBundle\Doctrine\DBAL\Driver\PDOPgSql\Driver from config. I though this is just needed for entity generation. Thanks again to @mstrzele for valuable help. Now my entities work. – umpirsky Apr 07 '11 at 12:35

Case with doctrine2, symfony2 and postgresql entities

4 Answers4

Linked