south migration: renaming foreign key and m2m tables

[scha...@gmail.com]

HI All,
I'm new in south and I'm wondering how I can merge two models into one.
I have a model book which derives from the model media.
So book has a media_id_ptr (which is already a pk).

No I would like to merge all attributes from media into book and get rid of media.

I was doing the following:

1.) Added all media attributes in the book model, but with _tmp ending.
2.) Created a schemamigration (also creates a correlation table with ending _tmp)
3.) Created a datamigration and copied the attribute value into the attribute_tmp for all existing book objects.
4.) modified the book model to get rid of the _tmp ending.
5.) Created a schemamigration and exchanged (delete/add with rename column) additionally I would like to rename media_id_ptr into id and use it as primary key.

But it always fails when I would like to rename the media_id_ptr into id.
I have different tables (m2m and normal foreign keys) which point to media_id_ptr.

Therefore I have 2 question:
1.) I guess it is not that easy to reuse the former foreign key as a primary key. Is there a best practice to handle that? DO I have to remove the foreign key and add it after renaming for all tables which referenced it before?

2.) Best practice to rename the m2m_tmp table DO I have to delete the unique keys and re-add them after renaming?

Thanks for any hints how to solve this issue.

Regards
Marcel

[Michal Petrucha]

On Thu, May 12, 2016 at 11:46:35PM -0700, scha...@gmail.com wrote:
> HI All,
> I'm new in south and I'm wondering how I can merge two models into one.

Hi Marcel,

If you are using South, that would mean you're using a version of
Django that's oldee than 1.7, which means you are using a version
that hasn't received security updates for more than a year. Not good.

Second, South itself has been deprecated and replaced by the native
django.db.migrations. Learning to use South at this point is not the
best idea, because while the native migrations framework is based on
the same ideas, it works differently in some ways.

That being said, let's look at what you are doing...

> I have a model book which derives from the model media.
> So book has a media_id_ptr (which is already a pk).
>
> No I would like to merge all attributes from media into book and get rid of
> media.

I would suggest that you do this the other way around; instead of
merging Media into Book, I'd merge Book into Media, drop Book, and
rename Media to Book. Since you're using multi-table inheritance, the
child model's primary key needs to be its OneToOneField pointing to
the parent, so you'd need to alter a OneToOneField into an
auto-incrementing ID field. While it may be possible to do that, it's
not something I'd want to try myself. There are many potential
pitfalls in that, and each database appears to have a unique way of
handling auto-incrementing ID columns, each with its own set of
limitations.

> I was doing the following:
>
> 1.) Added all media attributes in the book model, but with _tmp ending.
> 2.) Created a schemamigration (also creates a correlation table with ending
> _tmp)
> 3.) Created a datamigration and copied the attribute value into the
> attribute_tmp for all existing book objects.
> 4.) modified the book model to get rid of the _tmp ending.
> 5.) Created a schemamigration and exchanged (delete/add with rename column)
> additionally I would like to rename media_id_ptr into id and use it as
> primary key.
>
> But it always fails when I would like to rename the media_id_ptr into id.
> I have different tables (m2m and normal foreign keys) which point to
> media_id_ptr.
>
> Therefore I have 2 question:
> 1.) I guess it is not that easy to reuse the former foreign key as a
> primary key. Is there a best practice to handle that? DO I have to remove
> the foreign key and add it after renaming for all tables which referenced
> it before?

If you have other tables referencing a field that you want to alter,
that can complicate things a *lot*. This depends on the database you
are using – some databases will automatically cascade certain column
alterations to any constraints that reference them, others will just
error out and leave it up to you to sort this out.

If you want your application to work on multiple databases, I'm afraid
you'll have to drop any FOREIGN KEY constraints referencing your
column, make the change, and recreate them with the new target name.

> 2.) Best practice to rename the m2m_tmp table DO I have to delete the
> unique keys and re-add them after renaming?

I don't think it's necessary to drop UNIQUE constraints in a table
before renaming it, but I may be wrong (and it might also depend on
the database you are using). Are you getting any errors when you try
to do this?

Good luck,

Michal

[scha...@gmail.com]

HI Michal

If you are using South, that would mean you're using a version of
Django that's oldee than 1.7, which means you are using a version
that hasn't received security updates for more than a year. Not good.

Second, South itself has been deprecated and replaced by the native
django.db.migrations. Learning to use South at this point is not the
best idea, because while the native migrations framework is based on
the same ideas, it works differently in some ways.

Yes I'm running on a very old Django version and because of that I would like to upgrade. I would like to do the upgrade stepwise and therefore I need to learn south first.

I would suggest that you do this the other way around; instead of
merging Media into Book, I'd merge Book into Media, drop Book, and
rename Media to Book. Since you're using multi-table inheritance, the
child model's primary key needs to be its OneToOneField pointing to
the parent, so you'd need to alter a OneToOneField into an
auto-incrementing ID field. While it may be possible to do that, it's
not something I'd want to try myself. There are many potential
pitfalls in that, and each database appears to have a unique way of
handling auto-incrementing ID columns, each with its own set of
limitations.

I agree with you, but I was trying to get rid of the parent table, because the parent table was created from a 3rd party library, which I would like to get rid of.
(it makes problems in the new Django version 1.6 and the inheritance is still not needed. It would have been better to use just one class from beginning, but then the 3rd party class could not have been used. So saving time in the past result in my conversion issue. :-)
 
Do you think it would be better to fully create a new table and merge the attributes into it?
Afterwards I would have to change all the foreign keys but your argument with converting the OneToOneField into an auto-increment seems to be a little risky.
Btw: I'm using mySQL

I don't think it's necessary to drop UNIQUE constraints in a table
before renaming it, but I may be wrong (and it might also depend on
the database you are using). Are you getting any errors when you try
to do this?

Till now I do not get to this point, but I think if I create a new table and merge everything into it, I would not have this problem, as no renaming would be required.
SO I would reduce my problems to the foreign key which would have to point to the new table.

Something like this:
1.) Create new table
2.) Create new foreign key in existing tables to the new table (nullable)
3.) Copy the values of book and media into the new table (this creates new records with different id's as in media.id)
4.) Convert the new foreign key in the existing tables
5.) Make foreign keys not nullable where appropriate.

What do you think?

Thanks
Marcel

[Michal Petrucha]

On Fri, May 13, 2016 at 01:37:43AM -0700, scha...@gmail.com wrote:
> HI Michal

>
> I would suggest that you do this the other way around; instead of
> > merging Media into Book, I'd merge Book into Media, drop Book, and
> > rename Media to Book. Since you're using multi-table inheritance, the
> > child model's primary key needs to be its OneToOneField pointing to
> > the parent, so you'd need to alter a OneToOneField into an
> > auto-incrementing ID field. While it may be possible to do that, it's
> > not something I'd want to try myself. There are many potential
> > pitfalls in that, and each database appears to have a unique way of
> > handling auto-incrementing ID columns, each with its own set of
> > limitations.
>
>
> I agree with you, but I was trying to get rid of the parent table, because
> the parent table was created from a 3rd party library, which I would like
> to get rid of.
> (it makes problems in the new Django version 1.6 and the inheritance is
> still not needed. It would have been better to use just one class from
> beginning, but then the 3rd party class could not have been used. So saving
> time in the past result in my conversion issue. :-)
>
> Do you think it would be better to fully create a new table and merge the
> attributes into it?
> Afterwards I would have to change all the foreign keys but your argument
> with converting the OneToOneField into an auto-increment seems to be a
> little risky.

That could be workable – you could perhaps even achieve this with just
a few SQL queries. First, you create new_table with all necessary
columns, then you fill it with a single INSERT INTO new_table SELECT
... FROM old_parent_table LEFT OUTER JOIN old_child_table ... (Or
maybe an inner join, if you don't care about parent entries without an
associated child.)

Yep, it does seem to me that this might be easier than the first thing
you were trying to do.

> Btw: I'm using mySQL

Ouch. You probably already know this, but mysql's lack of support for
transactional DDL means that if a migration fails halfway through,
you'll have to either restore your database from a backup, or fix it
manually...

> I don't think it's necessary to drop UNIQUE constraints in a table
> > before renaming it, but I may be wrong (and it might also depend on
> > the database you are using). Are you getting any errors when you try
> > to do this?
>
>
> Till now I do not get to this point, but I think if I create a new table
> and merge everything into it, I would not have this problem, as no renaming
> would be required.

Well, you might still want to rename the new table to have the name of
the old table (after you drop the old table). But as I said, I don't
think renaming a table should need that you also drop and recreate
UNIQUE constraints, although, obviously, I may be wrong. (I don't use
mysql...)

> SO I would reduce my problems to the foreign key which would have to point
> to the new table.
>
> Something like this:
> 1.) Create new table
> 2.) Create new foreign key in existing tables to the new table (nullable)
> 3.) Copy the values of book and media into the new table (this creates new
> records with different id's as in media.id)
> 4.) Convert the new foreign key in the existing tables
> 5.) Make foreign keys not nullable where appropriate.

I think I would do it slightly differently:

1. create the new table
2. fill the new table (for example with that INSERT INTO ... SELECT I
mentioned above) while keeping all primary key values the same
3. drop existing FOREIGN KEY constraints referencing the old table
4. drop the old tables
5. rename the new table to have the old one's name
5. recreate FOREIGN KEY constraints

I'm not sure how much South can assist you here, though. I haven't
used South in a long time. Some of it might require running raw SQL,
but at least South doesn't seem to hide intermediary M2M tables from
migrations, so dropping and recreating the FOREIGN KEY constraints
will hopefully be as easy as just altering the fields to IntegerField
and then back to ForeignKey.

Good luck,

Michal