Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Configuring Index text length MySQL in Doctrine

I have a text field in my database and a index on it for the first 10 characters. How do I specify that in my Doctrine Entity?

I can't find any information about database specific options for indexes anywhere :/

This is my "partial" MySQL create statement:

 KEY `sourceaddr_index` (`sourceaddr`(10)),

And this is my @Index in doctrine:

 @ORM\Index(name="sourceaddr_index", columns={"sourceaddr"}, options={}), 

This dosnt interfere with the regular use, but I noticed the problem when deploying development to a new laptop, and creating the database based on my entities...

Any help would be appreciated :)

like image 242
Richard87 Avatar asked Sep 12 '15 14:09

Richard87


3 Answers

Possible since Doctrine 2.9, see: https://github.com/doctrine/dbal/pull/2412

@Index(name="slug", columns={"slug"}, options={"lengths": {191}})

Unfortunately, Doctrine seem to be very picky with whitespace location, so e.g. update --dump-sql yields:

 DROP INDEX slug ON wp_terms;
 CREATE INDEX slug ON wp_terms (slug(191));

and even if you execute those, they messages will stay there (tested with MariaDB 10.3.14).

like image 177
Veelkoov Avatar answered Sep 21 '22 07:09

Veelkoov


I've had very good luck naming the index in Doctrine, after manually creating it in MySQL. It's not pretty or elegant, and it's prone to cause errors moving from dev to production if you forget to recreate the index. But, Doctrine seems to understand it respect it.

In my entity, I have the following definition. Doctrine ignores the length option - it's wishful thinking on my part.

/**
  * Field
  *
  * @ORM\Table(name="field", indexes={
  *     @ORM\Index(name="field_value_bt", columns={"value"}, options={"length": 100})
  * })

And in MySQL, I execute

CREATE INDEX field_value_bt  ON field (value(100))

As far as I've seen, Doctrine just leaves the index alone so long as it's named the same.

like image 41
Michael Avatar answered Sep 19 '22 07:09

Michael


In short: you can't set this within Doctrine. Doctrine's ORM is specifically focused on cross vendor compatability and the type of index you're describing, though supported in many modern RDBMS, is somewhat outside the scope of Doctrine to handle.

Unfortunately there isn't an easy way around this if you use Doctrine's schema updater (in Symfony that would be php app/console doctrine:schema:update --force) as if you manually update the database, Doctrine will sometimes, regress that change to keep things in sync.

In instances where I've needed something like this I've just set up a fixture that sends the relevant ALTER TABLE statement via SQL. If you're going to be distributing your code (i.e. it may run on other/older databases) you can wrap the statement with a platform check to make sure.

It's not ideal but once your app/software stabilises, issues like this shouldn't happen all that often.

like image 22
John Noel Avatar answered Sep 21 '22 07:09

John Noel