prevostc/custom-index-bundle-zf2

Annotation and command for control entity custom indexes

CustomIndexBundle

The CustomIndexBundle allows create index for doctrine entities using annotation with entity definition and console command., _(*1)

Installation

CustomIndexBundle requires Symfony 2.1 or higher. Now work only with PostgreSQL., _(*2)

Require the bundle in your composer.json file:, _(*3)

````json { "require": { "intaro/custom-index-bundle": "~0.1.1", } }, <sub>(*4)</sub>

Install the bundle:

$ composer update intaro/custom-index-bundle, <sub>(*5)</sub>

Register the bundle in `AppKernel`:

```php
// app/AppKernel.php

public function registerBundles()
{
    $bundles = array(
        //...

        new Intaro\CustomIndexBundle\IntaroCustomIndexBundle(),
    );

    //...
}

Usage

1) Add annotation in your entity, <sub>(*6)</sub>

<?php

namespace Acme\MyBundle\Entity;

use Doctrine\ORM\Mapping as ORM;
use Intaro\CustomIndexBundle\Annotations as CustomIndexAnnotation

/**
 * @ORM\Table(name="my_entity")
 * @ORM\Entity()
 * @CustomIndexAnnotation\CustomIndexes(indexes={
 *     @CustomIndexAnnotation\CustomIndex(columns="my_property1"),
 *     @CustomIndexAnnotation\CustomIndex(columns={"lower(my_property1)", "lower(my_property2)"})
 * })
 */
class MyEntity
{
    /**
     * @ORM\Column(type="string", length=256)
     */
    protected $myProperty1;

    /**
     * @ORM\Column(type="string", length=256)
     */
    protected $myProperty2;
}

Available CustomIndex attributes:, <sub>(*7)</sub>

• columns - array of the table columns
• name - index name (default = 'i_cindex_<md5 hash from all CustomIndex attributes>').
• unique - index is unique (default = false).
• using - corresponds to USING directive in PostgreSQL CREATE INDEX command.
• where - corresponds to WHERE directive in PostgreSQL CREATE INDEX command.

Required only columns attribute., <sub>(*8)</sub>

2) Use intaro:doctrine:index:update command for update db., <sub>(*9)</sub>

php app/console intaro:doctrine:index:update

You may use dump-sql parameter for dump sql with DROP/CRATE INDEX commands, <sub>(*10)</sub>

php app/console intaro:doctrine:index:update --dump-sql

Some annotation examples

Create index using pg_trgm extension:, <sub>(*11)</sub>

@CustomIndexAnnotation\CustomIndex(columns="lower(my_column) gist_trgm_ops", using="gist")

Create unique index using PostgreSQL functions:, <sub>(*12)</sub>

@CustomIndexAnnotation\CustomIndex(columns={"lower(my_column1)", "nullif(true, not my_column2 isnull)"}, unique=true)

Create partial index:, <sub>(*13)</sub>

@CustomIndexAnnotation\CustomIndex(columns={"site_id"}, where="product_id IS NULL")