South and MultiDB

[Andrew Godwin]

Greetings, South users. I'd like to ask you all a question.

Obviously, Django 1.2's biggest feature is MultiDB support. South 0.7 is
very close to release, and currently we've only implemented a single
MultiDB feature - a new --database argument, that allows you to run
South on a different database entirely. It's the same as changing your
settings - it looks in the new database for history info, and everything
goes there.

What's up for discussion is "better" MultiDB integration. Specifically,
the scenario where you have some models running from one database, some
from another, and you'd like South to know where to put them.

Django does this with the "allow_syncdb" method on database routers - it
asks when the models are created which databases they should go on.
There are a few proposals of how South should handle this:

- A new allow_migrate method on database routers. All database methods
use this to determine which databases to execute on (it receives the
table name). It's flexible, and can be changed in the future, but
doesn't allow for different tables with the same name on different
databases, and won't work well with db.execute().

- Having schemamigration use allow_syncdb to work out what databases
are allowed when you create the migration, and then freeze these into
the migration code itself (so db.create_table becomes
dbs['users_db'].create_table, etc.). This uses existing idoms, and
requires little user effort, but is very fragile, and will break as soon
as you rename a database alias.

- Adding the possibility of having multiple migration sets per app, so
you can have one set of migrations for your two master databases, and a
second set for your metadata storage databases (say). This would mean
adding a --migset (name TBC) option to migrate, and then you could use
that and --database in tandem. It's clean, but it means more work to
make the second migration set.

- Not doing anything at all, and making sure there's enough hooks for
people to do their own stuff; this is the reasoning behind some of the
Django MultiDB stuff, with the angle that people who are using multiple
databases with split models likely know what they are doing and are able
to spend a bit more time. It's not so good in terms of, well, getting
stuff done fast.

There's the options. Any feedback at all is welcomed, especially if
you're starting to build stuff on top of MultiDB (yes, all three of
you). I'm personally swaying towards the second or third options, but
I'm not very strongly attached to them.

Andrew

[Craig Kimerer]

On Tue, Feb 23, 2010 at 4:02 PM, Andrew Godwin <and...@aeracode.org> wrote:

Greetings, South users. I'd like to ask you all a question.

Obviously, Django 1.2's biggest feature is MultiDB support. South 0.7 is very close to release, and currently we've only implemented a single MultiDB feature - a new --database argument, that allows you to run South on a different database entirely. It's the same as changing your settings - it looks in the new database for history info, and everything goes there.

What's up for discussion is "better" MultiDB integration. Specifically, the scenario where you have some models running from one database, some from another, and you'd like South to know where to put them.

Django does this with the "allow_syncdb" method on database routers - it asks when the models are created which databases they should go on. There are a few proposals of how South should handle this:

 - A new allow_migrate method on database routers. All database methods use this to determine which databases to execute on (it receives the table name). It's flexible, and can be changed in the future, but doesn't allow for different tables with the same name on different databases, and won't work well with db.execute().

Why not pass app_label / table name.  That should be enough to allow different tables with the same name on different DBs.  It would be even better if we could give the frozen ORM name, unfortunately the frozen ORM name is not backwards compatible because anyone using south before the orm freezing was in place would be out of luck on those migrations. The ORM thing would also affect anyone who has ever changed the name of their model (you would have to keep a list of all model names that ever existed).  One point of risk here is long table names that get truncated could be confusing to new users, but if they are using multi-db it probably isn't a point of concern.

 

 - Having schemamigration use allow_syncdb to work out what databases are allowed when you create the migration, and then freeze these into the migration code itself (so db.create_table becomes dbs['users_db'].create_table, etc.). This uses existing idoms, and requires little user effort, but is very fragile, and will break as soon as you rename a database alias.

Personally, I would rather just continue with the re-assigning of `db` when you run migrate.  This avoids having issues like freezing old alias names, and you solve for free the ability of adding models to new shards as you bring them up.  They may both be broken solutions, but this way seems less broken to me.

 - Adding the possibility of having multiple migration sets per app, so you can have one set of migrations for your two master databases, and a second set for your metadata storage databases (say). This would mean adding a --migset (name TBC) option to migrate, and then you could use that and --database in tandem. It's clean, but it means more work to make the second migration set.

I'm not convinced this is more work to make a second migration set.  I only see two times where it's more work than currently creating a migration.  The first is when you've already been using multi-db stuff (very small number), creating multiple models at once that need to go on different databases, e.g. --initial. 

 - Not doing anything at all, and making sure there's enough hooks for people to do their own stuff; this is the reasoning behind some of the Django MultiDB stuff, with the angle that people who are using multiple databases with split models likely know what they are doing and are able to spend a bit more time. It's not so good in terms of, well, getting stuff done fast.

There's the options. Any feedback at all is welcomed, especially if you're starting to build stuff on top of MultiDB (yes, all three of you). I'm personally swaying towards the second or third options, but I'm not very strongly attached to them.

Oh come on, there has to be at least 5 of us :)

 

Andrew

I like the #2 option the best assuming its with the south.db.db override per migration, otherwise I would prefer #3.  However with #3 how would you handle tests, specifically making the tables go on the correct databases?

Craig

[Andrew Godwin]

On 24/02/10 02:05, Craig Kimerer wrote:

Why not pass app_label / table name.  That should be enough to allow different tables with the same name on different DBs.  It would be even better if we could give the frozen ORM name, unfortunately the frozen ORM name is not backwards compatible because anyone using south before the orm freezing was in place would be out of luck on those migrations. The ORM thing would also affect anyone who has ever changed the name of their model (you would have to keep a list of all model names that ever existed).  One point of risk here is long table names that get truncated could be confusing to new users, but if they are using multi-db it probably isn't a point of concern.

Yes, it's not possible to reliably turn table names into models, so I won't be able to do that. Even if I pass the app_label, I'm not sure that's enough for things like db.execute or data migrations (would need to also try and use allow_syncdb for the models stuff, and... ick)

Personally, I would rather just continue with the re-assigning of `db` when you run migrate.  This avoids having issues like freezing old alias names, and you solve for free the ability of adding models to new shards as you bring them up.  They may both be broken solutions, but this way seems less broken to me.

Well, you still have to store the alias somewhere, and if you need to put the table in two different databases in the same migration, you'll need to use it twice - aliases are probably going to appear in the same migration the same number of times, no matter what

I'm not convinced this is more work to make a second migration set.  I only see two times where it's more work than currently creating a migration.  The first is when you've already been using multi-db stuff (very small number), creating multiple models at once that need to go on different databases, e.g. --initial. 

I've been thinking more about this, and TBH I can probably automate the creation of other migration sets if I only detect changes for a certain database alias, and then automatically apply sets to certain aliases (basically, have a set -> db alias mapping in settings).

I'm definitely settling on this option. It makes multi-db a clean break, and allows any operations on the other databases, pretty seamlessly.

I like the #2 option the best assuming its with the south.db.db override per migration, otherwise I would prefer #3.  However with #3 how would you handle tests, specifically making the tables go on the correct databases?

I've not seen how tests work yet, but as I said above, I'd probably require people to define which aliases get which sets, and then it can be automated (there's also no need for another option).

Andrew