Ticket #12 Solution

[Brant Harris]

I now have a working solution to ticket #12.

I've added some code to management.py that allows one to run:
> django-admin.py transition <app>

This then compares your current db to your models in that app, and
next to each model creates a "transition file" so if your model was
called "app.py", next to it would appear "app.transition.py".

The transition file has a sort of snapshot of what it's going to do to
your database. A transition file looks something like this:
# Django transition file
from django.core.meta.transition import *

Add('Document.attachments')
Drop('app_departments_users')
Drop('app_medias')
Add('Document.rating')

django-admin transition figured out that my module "Document" has a
many to many field named "attachments" that isn't represented in the
database. It also found two tables, "app_departments_users" and
"app_medias", which are in the database but not in my models. And
finally, it realized that I had added "rating =
meta.IntegerField(default=0)" to my "Document" module.

One can edit this by hand, or if it looks good, go with it:
> django-admin.py sqlupdate <app>

Now, sqlupdate looks at the "app.transition.py" file, and executes the
changes accordingly, spitting out:
BEGIN;
DROP TABLE `app_departments_users`;
DROP TABLE `app_medias`;
ALTER TABLE `app_documents` ADD COLUMN `rating` integer NOT NULL;
CREATE TABLE app_documents_attachments (
id mediumint(9) unsigned auto_increment NOT NULL PRIMARY KEY,
document_id integer NOT NULL REFERENCES app_documents (id),
file_id integer NOT NULL REFERENCES app_files (id),
UNIQUE (document_id, file_id)
);
COMMIT;

I have only tested this with mysql, but it seems to be working well.
I'm still adding comments to the code, should I add a patch to #12 or
should I send it to someone?

Thanks

[Adrian Holovaty]

On 8/30/05, Brant Harris <deadw...@gmail.com> wrote:
> I now have a working solution to ticket #12.

Your solution is very intriguing. There's already a "django-admin.py
updatedb", which is currently commented-out in
django/core/management.py and is only about 75% done. It's similar to
what you've done -- it outputs ALTER TABLE statements -- but it's not
a two-step process that requires transition files. What was your
reasoning for using transition files as opposed to making it a
one-step thing?

Adrian

--
Adrian Holovaty
holovaty.com | djangoproject.com | chicagocrime.org

[Brant Harris]

Do you mean db_check->database_check? That seems to check permissions
and what not (which now that I think about it, I didn't really take
into account). But it doesn't seem to even be approaching the ability
to ALTER a table dependant on a new model. Did I miss something here?

The two step process allows more flexibility. For one, the python
database api doesn't really allow a satisfactory introspection of the
current database, so it's not really possible (or at least it's very
difficult) to see if, for instance, I changed Document.lead from a
CharField to a TextField. Since you don't have the original model
file to reference, you can't see if that's the case. But with using
an intermediate file, the user can specify that the field needs to be
updated.

Also, if a user renames a class from, for instance Image to Media, how
is the program supposed to know that? At best it can see if a new
class has the same fields as an older class, which transition tries to
do, but what if you also chang a field in this class?

In general, having the code guess what you want, and then allowing you
to alter it, seems best.

[Brant Harris]

Well here's the patch as it stands on my system.

[Moof]

Brant Harris wrote:
> I now have a working solution to ticket #12.
>
> I've added some code to management.py that allows one to run:
>
>>django-admin.py transition <app>

...

>>django-admin.py sqlupdate <app>
>
>
> Now, sqlupdate looks at the "app.transition.py" file, and executes the
> changes accordingly, spitting out:
> BEGIN;
> DROP TABLE `app_departments_users`;
> DROP TABLE `app_medias`;
> ALTER TABLE `app_documents` ADD COLUMN `rating` integer NOT NULL;

Erm. Can you create a NOT NULL Column without a default in an ALTER
Statement? I know you can't in MSSQL.

> CREATE TABLE app_documents_attachments (
> id mediumint(9) unsigned auto_increment NOT NULL PRIMARY KEY,
> document_id integer NOT NULL REFERENCES app_documents (id),
> file_id integer NOT NULL REFERENCES app_files (id),
> UNIQUE (document_id, file_id)
> );
> COMMIT;
>
> I have only tested this with mysql, but it seems to be working well.
> I'm still adding comments to the code, should I add a patch to #12 or
> should I send it to someone?

Not yet.

It's still missing something.

Well, two things, really. Plus the bit mentioned above, which is a
technicality, though it's an important consideration.

Let's take a use case:

You create a FooApp for django. You fiddle with the tables, and eventually
release FooApp 1.0.

You find that for the next version you want two more tables: Bar and Baz, so
you add a transition file to create Bar and Baz, and release FooApp 2.0

For the next version (3.0) you want a table Quux, and you find that rather
than having a "price" field in Bar you actully want it to be in "net", "tax"
and "commission", and not use the "price" field at all.

Question number 1: How does your transition file cope with someone who *was*
using FooApp 1.0 upgrading directly to 3.0?

Question number 2: Assuming I can, as standard *calculate* the columns
"net", "tax" and "commission" for the alreayd existing "price" column, how
do I go about doing so at database upgrade time.

My own particular take on this is up at

<http://metamoof.net/blog/2005/07/26/well_i'll_be_django'd>

but I haven't had the time to actually code up a patch, and I'm not certain
it's the correct solution anyway. I'd appreciate thoughts, though.

Moof
--
Giles Antonio Radford, alias Moof
"Too old to be a chicken and too young to be a dirty old man"
Serving up my ego over at <http://metamoof.net/>

[Brant Harris]

> Erm. Can you create a NOT NULL Column without a default in an ALTER
> Statement? I know you can't in MSSQL.

It worked fine for me in MySQL... Not sure, although a change in the
code would be trivial.

The other point you make is the sort of versioning of the transition
file. I see your point, and had thought about it while I was
designing this out. I decided to ignore versioning for about three
reasons:

For one, I decided that instead of releasing transition files, the
database managers themselves should be producing the transition files.
So each "user" of your FooApp would manage it themselves, create
their own transition file, and apply it.

Secondly, I don't know how to store what version of the model is
represented in the database. At the beginning I envisioned the
transition file to have a Version(<number>) directive that would
identify groupings of the changes that would be required to get to
that version of the model. So you could have, for instance up to
Version(5) in the transition file; then if you needed to get from
version 2 to version 5, you'd execute all the changes between the
directives after Version(2) up to Version(5).

Thirdly, this whole versioning thing required much more time. The
best thing about my solution currently is that it works. I can, at
this very moment, change my model without erasing all my data in the
app. That's usefulness.

[Brant Harris]

On 8/31/05, Brant Harris <deadw...@gmail.com> wrote:
> > Erm. Can you create a NOT NULL Column without a default in an ALTER
> > Statement? I know you can't in MSSQL.
>
> It worked fine for me in MySQL... Not sure, although a change in the
> code would be trivial.

In retrospect it does NOT work. But, again, the fix is trivial.