Issue: Index name is differed from table_name when defining in Model class
Author: imckl
State: closed
Created: 2018-06-28 02:42:41

Just noticed index name is differed from table_name when defining in Model class, when using Meta.table_name.
This concerns on naming conventions.

Example code:

    # class name meets PEP8
    class ExampleTableWithSuffix(ContextModel):
        column_pk = PrimaryKeyField()
        column_a = IntegerField()

        class Meta:
            # table name meets PostgreSQL naming convention 
            table_name = 'example_table_with_suffix'
            indexes = (
                (("column_a", ), False),
            )

And debug output:

    [2018-06-28 10:22:47,596][DEBUG] - peewee: ('CREATE TABLE IF NOT EXISTS "example_table_with_suffix" ("column_pk" SERIAL NOT NULL PRIMARY KEY, "column_a" INTEGER NOT NULL)', [])
    [2018-06-28 10:22:49,080][DEBUG] - peewee: ('CREATE INDEX IF NOT EXISTS "exampletablewithsuffix_column_a" ON "example_table_with_suffix" ("column_a")', [])

It seems use class name to generate index name instead of table_name, and this should break common SQL naming convention. 
Perhaps naming it is more of a subjective issue, but keeping name to be consistency would be better if had choice, I think.

Thanks for the report, I suppose it would have been better to use the table name rather than the model name when declaring names for indices. Unfortunately, such a change would break backwards-compatibility, so I'm not too excited about changing this when there's a workaround that will allow you to control the index name.

You can:

```python

index = ExampleTableWithSuffix.index(
    ExampleTableWithSuffix.column_a, 
    name='example_table_with_suffix_column_a')
ExampleTableWithSuffix.add_index(index)
```

For more info:

* http://docs.peewee-orm.com/en/latest/peewee/api.html#Model.index
* http://docs.peewee-orm.com/en/latest/peewee/api.html#Model.add_index

--------------------------------

Going forward I think it would be nice to follow more closely the sql naming conventions. So that mixed-case class names get underscores, rather than just being smashed into one word lower-case. I'll need to think about how to introduce such a change w/o impacting backwards-compatibility.

I've been working on a new branch that improves the table name generation (and fixes the index name generation so it uses the table name in all cases): https://github.com/coleifer/peewee/tree/feature/improved-table-names

The legacy table-name behavior can be preserved by specifying `legacy_table_names = True` in the model Meta options, but the index-naming change would take effect regardless.

Such a change could be pretty disruptive to Peewee users so I'd like to think about how best to handle getting it deployed, because I do think it's a worthwhile improvement.

Example class name -> table name with new code:

* User -> user
* Tweet -> tweet
* ActivityLog -> activity_log
* UserProfile -> user_profile
* APIRequest -> api_request
* WebHTTPRequest -> web_http_request
* getHTTPResponse -> get_http_response
* FooBar -> foo_bar
* foo__Bar -> foo_bar

I've applied this patch by hand and it works fine. 
Only with some edge-cases(numeric letters in table name) not covered by the regex expression, by explicitly setting table_name that suits my need. 

## About backwards-compatibility:
Of course this change could be really disruptive to users, so i'm thinking about how to reduce backwards-incompatibility range.
Just assuming our final goal is to completely meet SQL(PostgreSQL) naming conventions, and Peewee could achieves it step-by-step.
### keep naming consistency
1. In my opinion, keeps naming consistency is a subset of SQL naming conventions. My initial intention to submit this issue is that index name(maybe some other also) generated by Peewee is out of naming consistency scope. And this patch fixes that hopefully. If we tend to apply naming consistency subset first, it produces less backwards-incompatibility overhead.
2. For backwards-compatibility, we can just simply create new indexes on the fly, and provide a pwiz-like options to delete old-generated indexes. Old index will keep by default and show should-be-delete warning in case of using indexes explicitly already.
3. I know duplicate indexes should have an impact on database efficiency. But if the Peewee user cares about efficiency, he/she should do the work themselves with instructions/tools provided by Peewee. For those who doesn't care about, just to leave it alone.
### follow basic SQL naming conventions by default
1. I really like the idea auto convert class/table name to SQL naming conventions by default. But, unfortunately, this is the most disruptive part :(
My suggestion is still use legacy name by default, with deprecation warning in current major release. Finally Peewee will change legacy name to false in next major release
2. For migration, we can provide something like - piwz --format=v3 old.db new_db_schema.py - that is handy for user to convert and migrate data.
2. A future base model like __future__ in python?

Generally, backwards-compatibility gets more important depending on user-base, and not fully obeying naming conventions would not make code unworkable, however not vice versa in this situation. 
Naming itself still more of a subjective issue. If Peewee should concern more about backwards-compatibility due to huge user-base, just do what backwards-compatibility matters, it's fine :) We can still see tons of code base using Python 2.x after 10 years of Python 3 release, don't we?

Hope my suggestions above could be helpful.


> Only with some edge-cases(numeric letters in table name) not covered by the regex expression, by explicitly setting table_name that suits my need.

Could you give examples? I thought it was working OK for numbers, too, e.g. Foo2Bar2Baz -> foo2_bar2_baz.

> My suggestion is still use legacy name by default, with deprecation warning in current major release. Finally Peewee will change legacy name to false in next major release

Yes, I agree this change should coincide with a new major release, and users should be given plenty of warning.

Thanks for sharing your thoughts.

I've merged this functionality into `master`. It is disabled by default, but can be enabled by specifying `legacy_table_names = False` in your model's `Meta` options.

Documentation: http://docs.peewee-orm.com/en/latest/peewee/models.html#table-names

Thanks!

My website's url happens to be started with a numeric letter. This could be hard to determine whether it is tail or not, so just ignore this case :)
Example:
    Site0myhostnameTable - start with numeric but not tail.
    output: site0_myhostname_table
    desired: site_0myhostname_table

Ehm, I was hoping there is database-wide option for non-legacy table names. Instead, I need to disable it in each model, which is inconsistent.

Most people use a base-class for their models, so as not to have to specify the database on each class. You could specify the `legacy_table_names` value in the base model as well.