[Proposal] Add Schema::hasIndex() method

[billmn]

What about add a method to the Schema class to check if an index exists?
It can be useful in migrations to avoid possible errors on adding/dropping indexes

I know a method on MySQL but I haven't experience with other DB engines.
I've found this informations to make this:

• MySQL: http://stackoverflow.com/questions/127156/how-do-i-check-if-index-exists-on-a-table-field-in-mysql
• SQL Server: http://stackoverflow.com/questions/2689766/how-do-you-check-if-a-certain-index-exists-in-a-table
• PostgreSQL: http://dba.stackexchange.com/questions/35616/create-index-if-it-does-not-exist
• SQLite: http://www.sqlite.org/pragma.html#pragma_index_info

Thoughts?

[bencorlett]

Hey mate,

It would be the first example of this sort of logic (e.g. querying the database) inside the Schema class, therefore it's probably better suited in the DB Query classes, though, it'd be the first schema-related thing there, so it's tricky.

Question, when would you not know if you had an index? If each migration's up/down methods null each other out, there shouldn't be a discrepancy, right?

[billmn]

Hey Ben,
yep ... there shouldn't be a discrepancy migration up/down ... but is possibile (a mistake, an oversight).

To avoid to stop a migration ( also avoiding all the problems that this thing can cause ) I think that a "hasIndex" or a "dropIndexIfExists" method can be helpful

[taylorotwell]

Doctrine can probably handle this. The DBAL is accessible via the DB connection.

[guiwoda]

@taylorotwell Sorry to bump this up, but it's unclear to me how you suggest to access the DBAL instance inside a migration.
I'd rather do something like this:

Schema::table('users', function(Blueprint $table){
    // ...
    if (! $table->hasIndex('remember_token'))
        $table->index('remember_token');

    // OR, altogether...
    $table->indexIfNotExists('remember_token');
});

Than something like this:

Schema::table('users', function(Blueprint $table){
    // ...
    if (! DB::connection()->getDoctrineSchemaBuilder()->hasIndex('users_remember_token_index'))
        $table->index('remember_token');
});

Where, in the second example, I'm hard-coding laravel's way of doing indexes as well as explicitly bringing up Doctrine's dependency on my code.
I feel that this kind of stuff makes Laravel what it is: a nice set of semantic Proxys over complex models / vendors.

[guiwoda]

I'll have to add to this, I really don't find the way to do it with Doctrine.
I've never used DBAL, and haven't gone too far into Laravel's code to understand when it does.

@taylorotwell as you said it's easily done with DBAL, can you post a small snippet of code of how it could be done?

UPDATE
This is how it can be done:

$conn = Schema::getConnection();
$dbSchemaManager = $conn->getDoctrineSchemaManager();
$doctrineTable = $dbSchemaManager->listTableDetails('users');

// alter table "users" add constraint users_email_unique unique ("email")
if (! $doctrineTable->hasIndex('users_email_unique'))
{
    $table->unique('email');
}

[ee0pdt]

Why is this closed? It's a perfectly valid request and necessary for avoiding errors on complicated migration sets.

But thanks @guiwoda for showing how to do this with DBAL

[mikeher]

@guiwoda Awesome, I'll try to work this into my migrations. Thanks!

I'm currently facing a problem where adding a unique composite index in a later migration, removes an index on one of the attributes in my new index, which in turn leads to an exception when rolling back, because an index is needed for the fkey to work.

My solution: To manually add the old index before deleting the unique index. However, the new index does not get deleted when migrating the new unique key once more, even though it is identical to the one originally created by the foreign() method. Because of this I am now checking whether such an index exists before adding it in my down() method.

[thiagomata]

+1

[leek]

It sucks that this was closed considering we already have hasTable(), hasColumn(), etc. This seems like a logical addition IMO.

[cyrrill]

I also would like to request the reopening this issue. Having the ability to check for an index seems like a very natural function for a Schema class.

[DennisXH]

+1 thanks @guiwoda

[martynling]

+1 thanks @guiwoda

[PiNotEqual3]

+1

+1 thanks @guiwoda