Allow custom index

[webmasterMeyers]

trafficstars

Feature Request

Q	A
New Feature	yes
RFC	yes/no
BC Break	no

Summary

I would like to at the very least be able to use a configuration like the schema_filter to ignore my custom indexes.

Or, add an annotation or some way of allowing the use of functions like lower() on index/unique columns.

ex. @ORM\Table(uniqueConstraints={@ORM\UniqueConstraint(columns={"lower(address)", "upper(postal_code)", "lower(community)", "lower(village_town_city)", "province_id"})})

currently, if I put a line like $this->addSql('CREATE UNIQUE INDEX UNIQ_CI_CD176A9D1C132A831A55FC808BA5A708A0E0E534D45A ON person (lower(first_name), lower(middle_names), lower(last_name), gender_id, birth_date)'); in a Version1234.php migration file, the next time I run doctrine:migrations:diff I get a $this->addSql('DROP INDEX UNIQ_CI_CD176A9D1C132A831A55FC808BA5A708A0E0E534D45A');

This is VERY annoying to have to manually remove every time. And when there are 6-12 of them, you miss one or two and loose your proper unique indexing.

Thoughts?

[SenseException]

Why do you need a certain case for the unique constraint? Wouldn't this suggest to use a case insensitive column collation?

[webmasterMeyers]

Well, in my research I could not find how to do this with PostgreSQL, at least not in a way that worked as easy as indexing by lower(). In most of my cases I want to store the data as entered, but same, Same, and SaMe are all the same.

[Neirda24]

This is an old topic but I have an issue as well. Some constraints can't be defined using the DBAL alone. For example :

ALTER TABLE public.bar
    ADD CONSTRAINT some_custom_name
        EXCLUDE USING gist (
            foo_id WITH =,
            baz_id WITH =,
            TSTZRANGE(from, to) WITH &&
        )
;

I tried using a c_ prefix on CONSTRAINT name (which also create an index in this case) + the

doctrine:
    dbal:
        schema_filter: '~^(?!c_)~'

but it still shows up in the migration files... Any ideas ?

[webmasterMeyers]

In the end, my work around in symfony is to write "CI" queries, and use those to ensure uniqueness.

[serhii-nakon]

I tried in another way with PHP attributes - it works just fine (also with unique constraint instead of ORM\Index put ORM\UniqueConstraint)

Example: use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity] #[ORM\Table(name: 'table_name')] #[ORM\Index(name: 'indexer_name_1', columns: ['column_name_1'])] #[ORM\Index(name: 'indexer_name_2', columns: ['column_name_2'])] #[ORM\UniqueConstraint(name: 'unique_constraint_name_1', columns: ['column_name_1'])] #[ORM\UniqueConstraint(name: 'unique_constraint_name_2', columns: ['column_name_2'])] class ExampleEntity...

[benconda]

For people falling into this issue, made an article on how to ignore custom indexes using doctrine https://medium.com/yousign-engineering-product/ignore-custom-indexes-on-doctrine-dbal-b5131dd22071