sqlupdate, creates sql to update the database based on an updated model file

[brantley (deadwisdom@…]

I've created a function for django-admin.py that dumps the sql that will transition an old database model (the one that is in the database currently) to a new one based an updated django model. So if you change the name of a model, or add a column, or delete a column, etc. it will output the sql to alter the database for you. And I think it's a shame that no one else has this, as it is remarkably usefull.

For instance, if I have this originally:

class Poll(meta.Model):
   question = meta.CharField(maxlength=128)
   responses = meta.IntegerField(default=0)

And I want to go to this:

class Poll(meta.Model):
   question = meta.CharField(maxlength=128)
   title = meta.CharField(maxlength=128)

It will create the sql to drop "responses" and add "title".

There are two steps in doing this. The first is to create a "transition" file. This file allows you to tweak the changes that the code thinks should be done. For instance, if you change Poll a lot, and then changed the name to Survey or something, it might think you dropped Poll and created a table called Survey. So it would create in the file:

Drop("Poll")
Add("Survey")

But you might want to change it to:

Name("app_polls", "Survey")
Change("Survey")

Which tells it that the table that was named polls, should be called "Survey" now, and also it should make all the changes to "Survey".

This all goes in a file right next to your model file. If your model file was called "model.py", the transition file would be called "model.transition.py"

The next step is to actually run the sqlupdate, that will analyze the transition file, and spew out the SQL to realize those changes.

To perform the complete steps:

1. django-admin.py transition <app_name>
2. <check the transition file to make sure it's kosher>
3. django-admin.py sqlupdate <app_name> # which won't commit the changes
4. django-admin.py sqlupdate <app_name> | mysql -p <project_name> # which will commit the changes (in mysql)

[brantley (deadwisdom@…]

Transition patch

[hugo]

I like it, especially the two-phase thingy. That's actually the first good idea I've seen on the problem of not being able to automatically recognize some transitions :-)

One thing that should go into the patch, though, would be fixing the django meta tables when a model name is changed. Otherwise you will end up with an admin that doesn't find it's tables ...

And another thought: add a way to define defaults for new columns, so that you can add them without "NOT NULL", populate them with it's default and then alter them to "SET NOT NULL", if they are null=False (the default) columns. You could use the default= setting from the model if there is one, but if there isn't, and the field is blank=False (the default), you can't just set them to NULL (because null=False) and neither to (because blank=False), but would need some thing else. So a "value callback" for field adding would be needed, where you could for example copy some other field over, or set some defined value or whatever.

How does this work cross-database? MySQL and PostgreSQL are quite different in character when it comes to the DCL, so maybe we would need some support from the database drivers to really make this usefull.

[brantley (deadwisdom@…]

Yes, Hugo. Those are exactly the weak points. I made this really just for me, and my uses. With these things not being a concern (I'm obviously using MySQL), and I really hadn't run into any problems with the meta tables, nor with the defaults. I'll look into some of this, but I'd prolly need help.

[Grimboy]

What version was this made for? It would certianly require a lot of changes to work on the current version (for a start there's next to no code in bin/django-admin). I would like this to happen as it would help to retain test data while tweeking.

[Adrian Holovaty]

Jacob is working on a new proposal. See SchemaEvolution for more.