Symfony2 / Doctrine2: Don't drop fulltext index on schema:update

Question

To support fulltext indexing in Symfony2 I use a MyISAM mirror table. We're regularly copying our production dataset to that table and created a SearchEntity that maps the table's structure and is associated with the real entities. So we can execute our search queries on our SearchRepository (using a custom MATCH AGAINST statement builder) and retrieve the found entities by resolving the associations.

Now, when I execute doctrine:schema:update Doctrine2 doesn't recognize the (manually added) indices on that table and wants to drop them. Unfortunately there is no advice annotation that says "but leave this index intact!".

I already tried to trick Doctrine using an @Index annotation with the same fields as in the fulltext index (prefixed ft_) and then execute some SQL manually to replace them with my FT index but that fails as well: when Doctrine initially creates the table with those dummy indices it fails because the index key length is larger than 1000 bytes (which is a hard limit for obvious reasons in MySQL)

Question is: can I advise Doctrine to leave the indices it finds on the table intact on a schema:update command? Is there a way to hack that into the framework? It's extremeley cumbersome to recreate the fulltext index after each schema update :(

SearchEntity:

/**
 * @ORM\Table(name="tmp_search2",options={"engine"="MyISAM"},
 *            uniqueConstraints={@ORM\UniqueConstraint(name="uq",columns={"language_id","product_offer_id","product_group_id","retailer_id"} )},
  *            indexes={@Index(name="price_idx", columns={"product_offer_price"}),
  *                     @Index(name="started_at_idx", columns={"product_offer_started_at"}),
  *                     @Index(name="ended_at_idx", columns={"product_offer_ended_at"}),
  *                     @Index(name="ft_products", columns={"product_name"}),
  *                     @Index(name="ft_product_group", columns={"product_group_name"}),
  *                     @Index(name="ft_product_retailer", columns={"retailer_name"})
  *            }
  * )
  * @ORM\Entity(repositoryClass="SearchRepository")
  */

class SearchEntity
{
    /**
     * This field is only here to satisfy doctrine's need for a non-composite primary key.
     * @ORM\Id
     * @ORM\Column(name="id", type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
     private $searchId;

   /**
    * @ORM\ManyToOne(targetEntity="ProductOffer")
    * @ORM\JoinColumn(name="product_offer_id", referencedColumnName="id")
    */
    private $productOffer;

   /**
     * @var integer
     *
     * @ORM\Column(name="product_offer_price", type="integer")
     */
    private $price;

The SQL to create the tmp_search indices (first drop what doctrine leaves there, then create ours)

DROP INDEX ft_products ON tmp_search2;
DROP INDEX ft_product_group ON tmp_search2;
DROP INDEX ft_product_retailer ON tmp_search2;

# import product data and then...

CREATE FULLTEXT INDEX ft_products ON tmp_search2 (product_name,product_short_text,product_long_text);
CREATE FULLTEXT INDEX ft_product_group ON tmp_search2 (product_group_name);
CREATE FULLTEXT INDEX ft_product_retailer ON tmp_search2 (retailer_name);

Answer 1

I was able to solve this using migrations then adding fake indices with the same name.

A migration added the actual fulltext index using raw SQL:

$this->addSql('ALTER TABLE content ADD FULLTEXT fulltext_content(title, description)');

Then I added the index to the Entity definition:

@ORM\Table(name="content", indexes={@ORM\Index(name="fulltext_content",columns={"title","description"})})

As long as you generate the fulltext indices first, Doctrine will no longer delete them.

Answer 2

Like someone else answered it's not a good idea to use doctrine:schema:update on production because any minor error in your code could potentially cause half your database to be dropped.

I work on a fairly large project and we use doctrine:schema:update --dump-sql to find queries that need to be executed and execute them manually.

Edit: The only other suggestion I have in addition is if you don't want to execute queries manually you could process the output of doctrine:schema:update --dump-sql filter the queries you don't want to execute and run the remaining ones on the database. Or create a command that creates your indexes after updating your schema, something like myproject:schema:createIndexes (or whatever)