Add introspection for multicolumn indexes

[Jeffrey Gelens]

The current database introspection methods don't support multi column indexes.

It's difficult to add this functionality to the current get_indexes methods as it's difficult to include it the current returned data d, which is:

{u'group_id': {'primary_key': False, 'unique': False},
 u'id': {'primary_key': True, 'unique': True}}

What hvdklauw, jgelens and charstring suggest is a (*cough*) "get_real_indexes" method.

[Harro]

Also to note, the current mysql implementation isn't even correct compared to the other implementations. (dunno about oracle, don't know it).
It get's all the indexes and then loops through them and adds them to the dict. Meaning the last mention of a fieldname is used to define the indexes is has.

[Jeffrey Gelens]

As this is a non public API the result can be updated to include the multicolumn fields. South is using this introspection function too, it needs to be patched accordingly.

[Jeffrey Gelens]

Suggestion:

Old output:

{u'group_id': {'primary_key': False, 'unique': False},
 u'id': {'primary_key': True, 'unique': True}}

New output:

{
    u'table_name_pkey': {
        'primary_key': False, 
        'unique': False, 
        'fields': ('id',)
    },
    u'table_name_fieldname': {
        'primary_key': False, 
        'unique': False, 
        'fields': ('somefield',)
    },
    u'table_name_355fd33': {
        'primary_key': False, 
        'unique': False, 
        'fields': ('somefield1', 'somefield2'])  # a multicolumn index
    },

[Harro]

#5805 depends on this one

[Jeffrey Gelens]

Got this working now:

{
    u'id': {
        'primary_key': False, 
        'unique': False, 
        'fields': ('id',)
    },
    u'somefield': {
        'primary_key': False, 
        'unique': True, 
        'fields': ('somefield',)
    },
    u'somefield1': {
        'primary_key': False, 
        'unique': False, 
        'fields': ('somefield1', 'somefield2')  # a multicolumn index
    },
}

Btw, I didn't find any usage of get_indexes in south :-/

[Harro]

Quick implementation for PostgreSQL, MySQL and SQLite

[Harro]

I did note some inconsistencies between the different backends. (SQLite didn't output unique indexes)

Now they do the same, still not sure about the key values, you can't use the field name as there might be multiple.

I don't have access or knowledge of Oracle, so someone who does should fix that; also haven't written tests because django by itself doesn't support adding indexes on multiple fields (need #5805 for that, which in turn needs this to do tests)

[Dan Loewenherz]

This blocks #5805 (or at least blocks writing tests that ensure #5805 is working).

I see there's a design decision needed. IMO this is a good addition--there's a solid use case for only needing to index fields in the context of others. Some queries always reference the same fields when filtering, e.g. first_name + last_name.

[Aymeric Augustin]

What design decision is expected?

[Jeffrey Gelens]

I think the design decision was for the suggested output of get_indexes().

[Akshesh Doshi]

The get_constraints method of the introspection currently returns something similar to this for all databases.

{  
    u'introspection_article_headline_pub_date_c749bad3_idx':{  
        'index':True,
        'primary_key':False,
        'orders':['ASC', 'ASC'],
        'foreign_key':False,
        'unique':False,
        'check':False,
        'columns':[u'headline', u'pub_date']
    },
    '__primary__':{  
        'index':False,
        'primary_key':True,
        'foreign_key':False,
        'unique':False,
        'check':False,
        'columns':[u'id']
    },
    u'introspection_article_reporter_id_c7562f75':{  
        'index':True,
        'primary_key':False,
        'orders':['ASC'],
        'foreign_key':False,
        'unique':False,
        'check':False,
        'columns':[u'reporter_id']
    },
}

If I understand correctly, that means this ticket got resolved somewhere down the line.

---

I also found ​an introspection test for get_indexes method with the following docstring

"""
Test that multicolumn indexes are not included in the introspection
results.
"""

which I found something contradicting to this ticket.

[Claude Paroz]

AFAIK, the fact that get_indexes only return single column indexes was due to its "limited" utility in inspectdb which was just to determine the primary key or unique property of individual fields.
If we can make inspectdb use the get_constraints result to determine primary key and unique properties of individual fields (should be doable), we might as well deprecate and eventually remove get_indexes.

[Claude Paroz]

get_indexes has been deprecated in #27098.

As get_constraints is introspecting multicolumn indexes, we can consider this ticket resolved.

See #27060 about adding multicolumn indexes support in inspectdb.