DDC-191: Case sensitive columnName's don't work anymore #2577

Closed
doctrinebot opened this Issue Dec 3, 2009 · 12 comments

2 participants

@doctrinebot

Jira issue originally created by user nicokaiser:

Since the Schema refactoring, misc case column names do not work again.

<?php

/****
 * @Entity @Table(name="user")
 */
class User
{ 
    /*** @Id @Column(name="userId", type="integer") @GeneratedValue(strategy="AUTO")  **/
    public $id;

    /*** @Column(name="name", type="string")  **/
    public $name;
}

This crashes the SchemaTool when creating the database schema:

SchemaTool: exception 'Doctrine\DBAL\Schema\SchemaException' with message 'An unknown column-name userId was given.' in .../doctrine/lib/Doctrine/DBAL/Schema/SchemaException.php:60

This may be because of several strtolower calls, e.g. in Doctrine\DBAL\Schema\Table ("$columnName = strtolower($columnName);")

@doctrinebot

Comment created by @beberlei:

Oh! :(

I'll fix it asap, that means this evening. Sorry :(

@doctrinebot

Comment created by @beberlei:

Can you try again? I implemented case handling in Schema now and added a test-case for this problem.

@doctrinebot

Comment created by @beberlei:

Fixed.

@doctrinebot

Comment created by nicokaiser:

The example given (with "userId" as column name for $id) still does not work. And there are still strtolower in Doctrine\DBAL\Schema\Table - is this intended? In my opinion the DB column names should be preserved exactly as they are written in the annotations (e.g. "userId", not "userid")...

@doctrinebot

Comment created by @beberlei:

The strtolower in Table is just for the platform independent access to column names, however for generation the Column::getName() is used. There is never a strtolower applied to this, so i am a bit puzzled to where this should happen.

I have to look at it again.

@doctrinebot

Comment created by romanb:

@Nico: Just to shed some light on this issue because I assume this sounds trivial to you at first glance. The casing is a major pain with database portability. For example, whether you query for "coLuMn" or "colUmn" or "COLUMN", Postgres will always give you lowercase in the result set. And Oracle will give you all uppercase.

PDO tried to address this with the PDO::ATTR_CASE constant that you can set LOWER or UPPER to force a unified casing in result sets.
But since we're not solely relying on PDO, we cant use that. For example, the PDO_OCI driver sucks badly and so we have a custom driver around the OCI8 extension.

Now these casing differences cause Benjamin trouble as he tries to determine differences between database schemas and things like that.

So while we would be happy with preserving the names everywhere, the databases play against us as they dont all return the names "as is".

And now we're trying hard to make this invisible for the user.

Of course you should be able to use mixed case column names and let doctrine worry about the rest.

Just wanted to give some background info.

@doctrinebot

Comment created by nicokaiser:

Ok, I understand. This sounds quite difficult... thanks for the background info, this always helps me to understand some of the Doctrine internals...

As you said, for portability reasons it is not a good idea to rely on the users' knowledge about the database (i.e. to always use columnName's with the 'right' case), because in some cases columnNames are automatically generated... And with DBMs being configurable(?) it may also be a bad idea to rely on Doctrine mapping casing behavior to DB drivers...

Yet in my project there is the simple requirement to keep the existing database model and use DDC on top of it, i.e. especially not renaming columns or changing primary keys, etc.

@doctrinebot

Comment created by romanb:

@Benjamin: Can you review my latest changeset?

Also, I discovered this:

$theJoinTable->createColumn( 
    $columnName, $class->getTypeOfColumn($joinColumn['referencedColumnName']), array('notnull' => false)
);

Doesnt that mean all foreign keys are nullable? shouldnt this be:

$theJoinTable->createColumn( 
    $columnName, $class->getTypeOfColumn($joinColumn['referencedColumnName']), array('notnull' => ! $joinColumn['nullable'])
);

?

@doctrinebot

Comment created by romanb:

Can this be considered fixed now?

@doctrinebot

Comment created by nicokaiser:

Looks good!

@doctrinebot

Issue was closed with resolution "Fixed"

@doctrinebot

Comment created by phansys:

How can I get PostgreSQL case sensitive quoted columns works?

My table structure is like this:

CREATE TABLE "Author"
(
"IdAuthor" serial NOT NULL,
"Name" character varying(100) NOT NULL DEFAULT ''::character varying,
"CreationDate" timestamp without time zone,
"ModificationDate" timestamp without time zone,
CONSTRAINT "Author_pkey" PRIMARY KEY ("IdAuthor")
)
WITH (
OIDS=FALSE
);

And in php...

<?php
// $em instanceof EntityManager
$q = $em->createQuery('select a from MyDomain\Model\Author a where a."IdAuthor" = 1');
$author = $q->getResult();

and this returns the exception:

[Syntax Error] line 0, col 56: Error: Expected =, <, <=, <>, >, >=, !=, got 'IdAuthor'

Sorry if it's a wrong place to do de question; and many thanks for your help.
Best regards.

@beberlei beberlei was assigned by doctrinebot Dec 6, 2015
@doctrinebot doctrinebot added this to the 2.0-ALPHA4 milestone Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
@doctrinebot doctrinebot added the Bug label Dec 7, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment