Issue: column names are stripped their '_id' part
Author: maxdml
State: closed
Created: 2016-08-18 20:41:52

It seems that when a column is named, say "table_id", the generated models is always "table".

Command used: python -m pwiz -e mysql -u root database >> models.py

edit: I observe the same behavior with names such as "app-id"

e.g.:

sql schema:
DROP TABLE IF EXISTS `Application`;
/_!40101 SET @saved_cs_client     = @@character_set_client */;
/_!40101 SET character_set_client = utf8 _/;
CREATE TABLE `Application` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `app_id` char(100) NOT NULL,
  `app_name` char(100) NOT NULL,
  `app_env` text,
  `app_submit` text NOT NULL,
  `app_dag` text,
  `app_master` char(100) NOT NULL,
  `app_driver` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `app_driver` (`app_driver`),
  CONSTRAINT `Application_ibfk_1` FOREIGN KEY (`app_driver`) REFERENCES `Container` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/_!40101 SET character_set_client = @saved_cs_client */;

class Application(BaseModel):
    app_dag = TextField(null=True)
    app_driver = ForeignKeyField(db_column='app_driver', null=True, rel_model=Container, to_field='id')
    app_env = TextField(null=True)
    **app = CharField(db_column='app_id')**
    app_master = CharField()
    app_name = CharField()
    app_submit = TextField()


After having a look at the code, I found the responsible function, in reflection.py:

```
425     def make_column_name(self, column):
426         column = re.sub('_id$', '', column.lower().strip()) or column.lower()                                    
427         column = re.sub('[^\w]+', '_', column)
428         if column in RESERVED_WORDS:
429             column += '_'
430         return column
```

Any reason why '_id' would be removed? Would it be still safe to have it replace such a string by '-id' ?


It is removed because generally "_id" is database schema cruft, i.e. an indication that the column is a foreign key or something similar.


Well I actually use it to name foreign keys =) As far as I know, and at least for MySQL, the engine makes no assumption about how FK are named.


Is there any way to keep "_id" exist?

You can name your fields whatever you want, just specify the actual column name using the `column_name` parameter:

```python

class MyModel(Model):
    some_field = TextField(column_name='actual_column_name')
    another_field = IntegerField(column_name='another_field_id')
```

Nevertheless, any way to keep '_id' part? I have 150+ tables in db, I don't want to put back all removed '_id's manually. Can it be fixed without changing sources?

Just to be clear -- the "_id" bit is only stripped from foreign-key fields.

Peewee exposes foreign-keys as model instances.

Here are some sample models:

```python
class User(Model):
    username = TextField()
    class Meta:
        table_name = 'users'  # Some databases do not like table named "user".

class Tweet(Model):
    user = ForeignKeyField(User, backref='tweets')
    content = TextField()
    timestamp = TimestampField()
```

The corresponding database schema would look something like:

```sql
create table "users" ("id" integer not null primary key, "username" text not null);
create table "tweet" (
  "id" integer not null primary key,
  "user_id" integer not null,
  "content" text not null,
  "timestamp" integer not null,
  foreign key ("user_id") references "users" ("id"))
```

When interacting with our models in Python, it makes sense to call the tweet.user_id foreign key "tweet.user" since it is exposed as a user instance:

```python
# Get 20 most recent tweets and also load the associated user.
tweets = Tweet.select(Tweet, User).join(User).order_by(Tweet.timestamp.desc()).limit(20)
for tweet in tweets:
    print(tweet.user.username, '->', tweet.content)
```

Peewee also does a nice thing with foreign-keys where the actual underlying column-value (in this case the related user's id) is exposed using a special descriptor:

```python
query = Tweet.select().join(User).order_by(Tweet.timestamp.desc()).limit(20)
for tweet in query:
    print(tweet.user_id, '->', tweet.content)  # Print's the user's ID and the tweet content.
```

If the field were named "user_id" instead, my own opinion is that is would be confusing. Consider:

```python
class Tweet(Model):
    user_id = ForeignKeyField(User, column_name='user_id', backref='tweets')
    ...

query = Tweet.select(Tweet, User).join(User).order_by(Tweet.timestamp.desc()).limit(20)
for tweet in query:
    print(tweet.user_id.username, '->', tweet.content)  # Here the related user instance is called user_id?

query = Tweet.select().join(User).order_by(Tweet.timestamp.desc()).limit(20)
for tweet in query:
    print(tweet.user_id_id, '->', tweet.content)  # And the ID descriptor is called "user_id_id" :/
```

So by default, that is why pwiz strips the "_id" off of foreign-keys when generating models. Of course you are encouraged to edit the generated code, however.

Well, yes, I see, thanks for the explanation.
But I encountered an interesting behaviour. I'm developing a script for a XenForo-based forum, so I used pwiz to generate a model based on mysql schema. On my local machine it worked as expected: the script stipped out all "_id" parts. But when pwiz was launched on a client's machine, all "_id"s were preserved. So I thought, there is some option that can manipulate script's behaviour

Probably their pre-existing database schema was missing the foreign key constraints. Cuz the story with referential integrity and MySQL...

Yes, maybe, by the way. XenForo uses an interesting schema without (in some versions, as far as I know) foreign keys.
Thank you.

The MyISAM storage engine, default before 5.5.5, does not handle fk constraints.

I don't think that this is the problem, but I'll check it. Thanks again