Re: Support for db declaration of "on cascade delete"?
Thanks - I'll try this out on a fresh DB. You're right that I'm running sqlall on an already populated database. I'll post back if it looks like there was a real bug here. Thanks again for all of your help, Greg Russell Keith-Magee wrote: > On 10/5/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: >>> However, you shouldn't be having any problems with foreign key >>> constraints. The model creation process should be able to identify and >>> avoid any problems with forward declared foreign key constraints. >> This is what I would guess, but is it possible that "sqlall" produces >> different output than is actually used by "syncdb" for single >> applications? > > Certainly possible at the moment (hence my refactoring attempts). For > example, up until recently, syncdb didn't produce indicies for tables. > If you look at the internals, syncdb doesn't use sqlall to add new > tables - it duplicates sqlall logic because of some deficiencies in > the existing install/sqlall implementation. However, syncdb/sqlall do > share some sub-components - m2m table generation, table > back-references, etc. > > As for your sqlall output - I suspect these results are a red herring > - is this the output you get after running sqlall on a clean databse, > or after you have run syncdb? > > If some (or all) of the tables already exist (e.g., you run syncdb, > then run sqlall), sqlall could be getting confused because it can't > differentiate between tables that already exist, and tables it thinks > it needs to create. > > If you have a completely clean database, and then run sqlall, the > models won't necessarily be created in the order you expect (because > of dictionary ordering), but the constraints should be added correctly > (inline if the table has already been created, post declared as an > ALTER if they haven't). > > However, if sqlall is producing this output on a clean database, then > you may have found a nasty bug. > >> Any help would be appreciated here - it would take some work for me to >> automate the process of generating custom SQL handlers for each of my >> models, but it would be very clean for me to add a step in our dev process >> to modify the output of "sqlall" and just use that. > > It's probably not as messy as you think. If hand cranking SQL > modifiers for each model is too much work, here's another approach - > you could add a handler that listens for the post_syncdb signal, and > emits alter statements programatically for each foreign key it finds > in the model. > > Look for any instance of 'management.py' in the django sources for > examples of what you can do on a post_syncdb trigger. As an example, > the automated addition of the superuser and the addition of > permissions are both stimulated by post_syncdb handler. > > Yours, > Russ Magee %-) > > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-users -~--~~~~--~~--~--~---
Re: Support for db declaration of "on cascade delete"?
On 10/5/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > However, you shouldn't be having any problems with foreign key > > constraints. The model creation process should be able to identify and > > avoid any problems with forward declared foreign key constraints. > > This is what I would guess, but is it possible that "sqlall" produces > different output than is actually used by "syncdb" for single > applications? Certainly possible at the moment (hence my refactoring attempts). For example, up until recently, syncdb didn't produce indicies for tables. If you look at the internals, syncdb doesn't use sqlall to add new tables - it duplicates sqlall logic because of some deficiencies in the existing install/sqlall implementation. However, syncdb/sqlall do share some sub-components - m2m table generation, table back-references, etc. As for your sqlall output - I suspect these results are a red herring - is this the output you get after running sqlall on a clean databse, or after you have run syncdb? If some (or all) of the tables already exist (e.g., you run syncdb, then run sqlall), sqlall could be getting confused because it can't differentiate between tables that already exist, and tables it thinks it needs to create. If you have a completely clean database, and then run sqlall, the models won't necessarily be created in the order you expect (because of dictionary ordering), but the constraints should be added correctly (inline if the table has already been created, post declared as an ALTER if they haven't). However, if sqlall is producing this output on a clean database, then you may have found a nasty bug. > Any help would be appreciated here - it would take some work for me to > automate the process of generating custom SQL handlers for each of my > models, but it would be very clean for me to add a step in our dev process > to modify the output of "sqlall" and just use that. It's probably not as messy as you think. If hand cranking SQL modifiers for each model is too much work, here's another approach - you could add a handler that listens for the post_syncdb signal, and emits alter statements programatically for each foreign key it finds in the model. Look for any instance of 'management.py' in the django sources for examples of what you can do on a post_syncdb trigger. As an example, the automated addition of the superuser and the addition of permissions are both stimulated by post_syncdb handler. Yours, Russ Magee %-) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-users -~--~~~~--~~--~--~---
Re: Support for db declaration of "on cascade delete"?
Thanks for your response, Russell. To follow up on this part of the thread: Russell Keith-Magee wrote: >>> I don't believe you can. However, you can use sql or sqlall [1] to >>> output the table creation sql to a file for editing. For example: >>> >>> manage.py sqlall myapp > myapp.sql >> I was looking at this, but: Does anyone know why "manage.py sqlall" >> rearranges the order of the table-creates from what's in the models? >> >> I can't find the reason for the new ordering; it looks kind of random, >> and it causes the table-creates to fail because of foreign key constraints. > > It isn't random - it's hashtable order. There's no particular reason > for it, other than the fact that we need to have a dictionary to store > the models. > > However, you shouldn't be having any problems with foreign key > constraints. The model creation process should be able to identify and > avoid any problems with forward declared foreign key constraints. This is what I would guess, but is it possible that "sqlall" produces different output than is actually used by "syncdb" for single applications? "syncdb" on my models.py executes correctly, but "sqlall" produces output that violates foreign key constraints right away with no obvious ordering (some tables are created before the tables that they reference, some are created after). My backend is MySQL 5.0.22 using InnoDB. As a contrived example, I hacked down my models.py file to produce the following test models.py. In this little model, User is referred to by Computer, and Device refers to both Computer and DeviceName. You'll see in the sqlall result that follows that Device is created first, with a reference to DeviceName. The Computer reference is correctly created after the Computer table is created. Any help would be appreciated here - it would take some work for me to automate the process of generating custom SQL handlers for each of my models, but it would be very clean for me to add a step in our dev process to modify the output of "sqlall" and just use that. Thanks, Greg models.py from django.db import models class User(models.Model): id = models.BigIntegerField(primary_key=True) class Admin: pass class Meta: db_table='user' class Computer(models.Model): id = models.CharField(primary_key=True, maxlength=16) user = models.ForeignKey(User) class Admin: pass class Meta: db_table='computer' class DeviceName(models.Model): device_type = models.IntegerField() name_index = models.IntegerField() name = models.TextField() class Admin: pass class Meta: db_table='device_name' class Device(models.Model): name = models.ForeignKey(DeviceName) computer = models.ForeignKey(Computer) class Admin: pass class Meta: db_table='device' class DeviceRenderState(models.Model): device = models.ForeignKey(Device) creator = models.ForeignKey(User) class Admin: pass class Meta: db_table='device_render_state' sqlall result-- BEGIN; CREATE TABLE `device` ( `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `name_id` integer NOT NULL REFERENCES `device_name` (`id`), `computer_id` varchar(16) NOT NULL, CONSTRAINT FOREIGN KEY (`name_id`) REFERENCES `device_name` (`id`) ); CREATE TABLE `device_name` ( `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `device_type` integer NOT NULL, `name_index` integer NOT NULL, `name` longtext NOT NULL ); CREATE TABLE `computer` ( `id` varchar(16) NOT NULL PRIMARY KEY, `user_id` bigint NOT NULL REFERENCES `user` (`id`), CONSTRAINT FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ); ALTER TABLE `device` ADD CONSTRAINT computer_id_referencing_computer_id FOREIGN KEY (`computer_id`) REFERENCES `computer` (`id`); CREATE TABLE `user` ( `id` bigint NOT NULL PRIMARY KEY ); CREATE TABLE `device_render_state` ( `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `device_id` integer NOT NULL REFERENCES `device` (`id`), `creator_id` bigint NOT NULL REFERENCES `user` (`id`), CONSTRAINT FOREIGN KEY (`device_id`) REFERENCES `device` (`id`), CONSTRAINT FOREIGN KEY (`creator_id`) REFERENCES `user` (`id`) ); CREATE INDEX device_name_id ON `device` (`name_id`); CREATE INDEX device_computer_id ON `device` (`computer_id`); CREATE INDEX computer_user_id ON `computer` (`user_id`); CREATE INDEX device_render_state_device_id ON `device_render_state` (`device_id` ); CREATE INDEX device_render_state_creator_id ON `device_render_state` (`creator_i d`); COMMIT; > > That said, I am aware (and am working on fixing) a slight problem with > install/sqlall dealing with multiple applications; specificially if > you
Re: Support for db declaration of "on cascade delete"?
On 10/5/06, Greg Plesur <[EMAIL PROTECTED]> wrote: > > Waylan Limberg wrote: > >> Do any of you know if there's a way to tell Django to specify "on > > cascade delete" for foreign key references, when it creates DB tables? Django doesn't use ON DELETE CASCADE in its table definitions, due to some database backends not fully supporting the operation. To compensate, when you delete an object through the Django ORM, it calculates the dependent objects, and deletes them as well. However, if you _really_ want to have ON DELETE CASCADE in your model, your best bet is to use the custom SQL handlers. If you create an 'sql' directory in your models directory, and put a file called 'mymodel.sql' in that directory (where mymodel is the name of your model), the contents of that file will be executed on the database when syncdb installs the model. If you put some ALTER statements in the file, you can add the cascade directive, or any other table modifications/triggers you want to the table after it has been created. > > I don't believe you can. However, you can use sql or sqlall [1] to > > output the table creation sql to a file for editing. For example: > > > > manage.py sqlall myapp > myapp.sql > > I was looking at this, but: Does anyone know why "manage.py sqlall" > rearranges the order of the table-creates from what's in the models? > > I can't find the reason for the new ordering; it looks kind of random, > and it causes the table-creates to fail because of foreign key constraints. It isn't random - it's hashtable order. There's no particular reason for it, other than the fact that we need to have a dictionary to store the models. However, you shouldn't be having any problems with foreign key constraints. The model creation process should be able to identify and avoid any problems with forward declared foreign key constraints. That said, I am aware (and am working on fixing) a slight problem with install/sqlall dealing with multiple applications; specificially if you try to run: manage.py sqlall app1 app2 or manage.py install app1 app2 and app1 has a dependency on app2, sqlall reports problems with model creation. manage.py syncdb doesn't have this problem. Is this the problem you are experiencing? If not, can you provide more details (backend, models etc) Yours, Russ Magee %-) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-users -~--~~~~--~~--~--~---
Re: Support for db declaration of "on cascade delete"?
Waylan Limberg wrote: >> Do any of you know if there's a way to tell Django to specify "on > cascade delete" for foreign key references, when it creates DB tables? > > I don't believe you can. However, you can use sql or sqlall [1] to > output the table creation sql to a file for editing. For example: > > manage.py sqlall myapp > myapp.sql I was looking at this, but: Does anyone know why "manage.py sqlall" rearranges the order of the table-creates from what's in the models? I can't find the reason for the new ordering; it looks kind of random, and it causes the table-creates to fail because of foreign key constraints. > > Then open myapp.sql in your favorite editor and alter the sql as you > see fit. After saving, feed that file into your db. > > [1]: > http://www.djangoproject.com/documentation/django_admin/#sql-appname-appname >> I'm developing an application that is mostly using Django to touch my >> DB, but that defines some SQL stored-procedures for manipulation of some >> of its data by other clients. I'd like the db-level "on cascade delete" >> defined for these cases. >> >> Thanks, >> Greg >> >> > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-users -~--~~~~--~~--~--~---
Re: Support for db declaration of "on cascade delete"?
On 10/4/06, Greg Plesur <[EMAIL PROTECTED]> wrote: > > All, > > Do any of you know if there's a way to tell Django to specify "on cascade delete" for foreign key references, when it creates DB tables? I don't believe you can. However, you can use sql or sqlall [1] to output the table creation sql to a file for editing. For example: manage.py sqlall myapp > myapp.sql Then open myapp.sql in your favorite editor and alter the sql as you see fit. After saving, feed that file into your db. [1]: http://www.djangoproject.com/documentation/django_admin/#sql-appname-appname > > I'm developing an application that is mostly using Django to touch my > DB, but that defines some SQL stored-procedures for manipulation of some > of its data by other clients. I'd like the db-level "on cascade delete" > defined for these cases. > > Thanks, > Greg > > > > > -- Waylan Limberg [EMAIL PROTECTED] --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-users -~--~~~~--~~--~--~---
Support for db declaration of "on cascade delete"?
All, Do any of you know if there's a way to tell Django to specify "on cascade delete" for foreign key references, when it creates DB tables? I'm developing an application that is mostly using Django to touch my DB, but that defines some SQL stored-procedures for manipulation of some of its data by other clients. I'd like the db-level "on cascade delete" defined for these cases. Thanks, Greg --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-users -~--~~~~--~~--~--~---
