Re: SQL generated for ManyToManyField is incorrect with Django 1.2.1

[Gergely]

Ok, it is clear now.
SQLite unfortunatelly does not support "ALTER TABLE ADD CONSTRAINT"
command.
So the "REFERENCES" clause is not possible if the referenced table is
missing, there is no alter, and of course django will not re-arrange
my definition order.
The same applies to many-to-many relations.
At least it creates an index on the foreign key column, so query will
be faster just the constraint is missing.
I think django does the maximum it can with sqlite :)

Since I am just learning django I can live without constraints and
later I will use some "more complete" db, no frustration anymore :)

Thanks again for the informations, I hope it was usefull not just for
me.

Best regards,
Gergely

On márc. 7, 13:43, Gergely  wrote:
> Thanks a lot Tom!
>
> Now I see that not the "REFERENCES" is missing but the "ALTER" at the
> end of the script.
> And of course the reason is clear, you are right.
>
> I will test it with the latest django and sqlite and report the result
> here.
>
> Regards,
> Gergely
>
> On márc. 7, 13:33, Tom Evans  wrote:
>
>
>
>
>
>
>
> > On Mon, Mar 7, 2011 at 12:04 PM, Tom Evans  wrote:
> > > If so, then there is no difference in the SQL generated - why would there 
> > > be?
>
> > > Cheers
>
> > > Tom
>
> > Oh, I'm waay wrong:
>
> > class Series(models.Model):
> >   pass
> > class Episode(models.Model):
> >   series = models.ForeignKey('Series')
>
> > =>
>
> > BEGIN;
> > CREATE TABLE "app_series" (
> >     "id" serial NOT NULL PRIMARY KEY
> > )
> > ;
> > CREATE TABLE "app_episode" (
> >     "id" serial NOT NULL PRIMARY KEY,
> >     "series_id" integer NOT NULL REFERENCES "app_series" ("id")
> > DEFERRABLE INITIALLY DEFERRED
> > )
> > ;
> > CREATE INDEX "app_episode_series_id" ON "app_episode" ("series_id");
> > COMMIT;
>
> > where as:
>
> > class Episode(models.Model):
> >   series = models.ForeignKey('Series')
> > class Series(models.Model):
> >   pass
>
> > =>
>
> > BEGIN;
> > CREATE TABLE "app_episode" (
> >     "id" serial NOT NULL PRIMARY KEY,
> >     "series_id" integer NOT NULL
> > )
> > ;
> > CREATE TABLE "app_series" (
> >     "id" serial NOT NULL PRIMARY KEY
> > )
> > ;
> > ALTER TABLE "app_episode" ADD CONSTRAINT "series_id_refs_id_61c5b6e4"
> > FOREIGN KEY ("series_id") REFERENCES "app_series" ("id") DEFERRABLE
> > INITIALLY DEFERRED;
> > CREATE INDEX "app_episode_series_id" ON "app_episode" ("series_id");
> > COMMIT;
>
> > Django 1.2.5, using postgres_psycopg2 backend.
>
> > I can understand why it does this. The table it references needs to
> > exist before Django can create a reference to it. By declaring it in
> > one order leads Django to assume that the table does not exist yet,
> > and to defer the creation of the reference constraint pointing to it.
>
> > To have both situations producing the same SQL would require Django to
> > understand and re-order the table creation queries, and that isn't
> > necessary or worthwhile - the order that the developer chose is used
> > instead.
>
> > Cheers
>
> > Tom

-- 
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 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: SQL generated for ManyToManyField is incorrect with Django 1.2.1

[Gergely]

Thanks a lot Tom!

Now I see that not the "REFERENCES" is missing but the "ALTER" at the
end of the script.
And of course the reason is clear, you are right.

I will test it with the latest django and sqlite and report the result
here.

Regards,
Gergely

On márc. 7, 13:33, Tom Evans  wrote:
> On Mon, Mar 7, 2011 at 12:04 PM, Tom Evans  wrote:
> > If so, then there is no difference in the SQL generated - why would there 
> > be?
>
> > Cheers
>
> > Tom
>
> Oh, I'm waay wrong:
>
> class Series(models.Model):
>   pass
> class Episode(models.Model):
>   series = models.ForeignKey('Series')
>
> =>
>
> BEGIN;
> CREATE TABLE "app_series" (
>     "id" serial NOT NULL PRIMARY KEY
> )
> ;
> CREATE TABLE "app_episode" (
>     "id" serial NOT NULL PRIMARY KEY,
>     "series_id" integer NOT NULL REFERENCES "app_series" ("id")
> DEFERRABLE INITIALLY DEFERRED
> )
> ;
> CREATE INDEX "app_episode_series_id" ON "app_episode" ("series_id");
> COMMIT;
>
> where as:
>
> class Episode(models.Model):
>   series = models.ForeignKey('Series')
> class Series(models.Model):
>   pass
>
> =>
>
> BEGIN;
> CREATE TABLE "app_episode" (
>     "id" serial NOT NULL PRIMARY KEY,
>     "series_id" integer NOT NULL
> )
> ;
> CREATE TABLE "app_series" (
>     "id" serial NOT NULL PRIMARY KEY
> )
> ;
> ALTER TABLE "app_episode" ADD CONSTRAINT "series_id_refs_id_61c5b6e4"
> FOREIGN KEY ("series_id") REFERENCES "app_series" ("id") DEFERRABLE
> INITIALLY DEFERRED;
> CREATE INDEX "app_episode_series_id" ON "app_episode" ("series_id");
> COMMIT;
>
> Django 1.2.5, using postgres_psycopg2 backend.
>
> I can understand why it does this. The table it references needs to
> exist before Django can create a reference to it. By declaring it in
> one order leads Django to assume that the table does not exist yet,
> and to defer the creation of the reference constraint pointing to it.
>
> To have both situations producing the same SQL would require Django to
> understand and re-order the table creation queries, and that isn't
> necessary or worthwhile - the order that the developer chose is used
> instead.
>
> Cheers
>
> Tom

-- 
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 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: SQL generated for ManyToManyField is incorrect with Django 1.2.1

[Tom Evans]

On Mon, Mar 7, 2011 at 12:04 PM, Tom Evans  wrote:
> If so, then there is no difference in the SQL generated - why would there be?
>
> Cheers
>
> Tom
>

Oh, I'm waay wrong:

class Series(models.Model):
  pass
class Episode(models.Model):
  series = models.ForeignKey('Series')

=>


BEGIN;
CREATE TABLE "app_series" (
"id" serial NOT NULL PRIMARY KEY
)
;
CREATE TABLE "app_episode" (
"id" serial NOT NULL PRIMARY KEY,
"series_id" integer NOT NULL REFERENCES "app_series" ("id")
DEFERRABLE INITIALLY DEFERRED
)
;
CREATE INDEX "app_episode_series_id" ON "app_episode" ("series_id");
COMMIT;


where as:

class Episode(models.Model):
  series = models.ForeignKey('Series')
class Series(models.Model):
  pass


=>

BEGIN;
CREATE TABLE "app_episode" (
"id" serial NOT NULL PRIMARY KEY,
"series_id" integer NOT NULL
)
;
CREATE TABLE "app_series" (
"id" serial NOT NULL PRIMARY KEY
)
;
ALTER TABLE "app_episode" ADD CONSTRAINT "series_id_refs_id_61c5b6e4"
FOREIGN KEY ("series_id") REFERENCES "app_series" ("id") DEFERRABLE
INITIALLY DEFERRED;
CREATE INDEX "app_episode_series_id" ON "app_episode" ("series_id");
COMMIT;


Django 1.2.5, using postgres_psycopg2 backend.


I can understand why it does this. The table it references needs to
exist before Django can create a reference to it. By declaring it in
one order leads Django to assume that the table does not exist yet,
and to defer the creation of the reference constraint pointing to it.

To have both situations producing the same SQL would require Django to
understand and re-order the table creation queries, and that isn't
necessary or worthwhile - the order that the developer chose is used
instead.

Cheers

Tom

-- 
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 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: SQL generated for ManyToManyField is incorrect with Django 1.2.1

[Tom Evans]

On Mon, Mar 7, 2011 at 11:08 AM, Gergely  wrote:
> And if you change the order?
> If the foreign key references a model defined later?
>
> Gergely
>

Do you mean:

  class Episode(models.Model):
series = models.ForeignKey('Series')

  class Series(models.Model):
pass


instead of:

  class Series(models.Model):
pass

  class Episode(models.Model):
series = models.ForeignKey(Series)

?

If so, then there is no difference in the SQL generated - why would there be?

Cheers

Tom

-- 
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 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: SQL generated for ManyToManyField is incorrect with Django 1.2.1

[Michal Petrucha]

> And if you change the order?
> If the foreign key references a model defined later?
Correct me if I'm wrong, but you can't do that -- python will
complain.

I can't find a better reference off the bat than
http://docs.djangoproject.com/en/1.2/ref/django-admin/#inspectdb
It says that you have to rearrange your model definitions so that they
reference existing ones. If you try to reference a model defined
later, you'll get errors while importing the models.py file.

Michal Petrucha


signature.asc
Description: Digital signature

Re: SQL generated for ManyToManyField is incorrect with Django 1.2.1

[Gergely]

And if you change the order?
If the foreign key references a model defined later?

Gergely

On márc. 7, 11:40, Tom Evans  wrote:
> On Mon, Mar 7, 2011 at 10:04 AM, Gergely  wrote:
> > Ok, lets concentrate 1 question at a time.
>
> > If you define the referenced model later and use "lazy" relationship
> > in the foreign key field (with the name of the model instead of the
> > model itself) than in the generated sql you will not find the
> > "REFERENCES" part of the foreign key definition.
> > DB engine: sqlite. Django version: 1.2.4
>
> > Example:
>
> > Model:
> > class Book(models.Model):
> > publisher = models.ForeignKey("Publisher")
>
> > class Publisher(models.Model):
>
> > Generated sql:
> > CREATE TABLE "books_book" (
> >  "id" integer NOT NULL PRIMARY KEY,
> >  "publisher_id" integer NOT NULL,
> >  )
>
> > Best regards,
> > Gergely
>
> Use a better SQL engine. This is Django 1.2.5 + postgresql:
>
> class Series(models.Model):
>   pass
>
> class Episode(models.Model):
>   series = models.ForeignKey('Series',)
>
> CREATE TABLE "media_episode" (
>     "id" serial NOT NULL PRIMARY KEY,
>     "series_id" integer NOT NULL REFERENCES "media_series" ("id")
> DEFERRABLE INITIALLY DEFERRED
> )
>
> Cheers
>
> Tom

-- 
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 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: SQL generated for ManyToManyField is incorrect with Django 1.2.1

[Tom Evans]

On Mon, Mar 7, 2011 at 10:04 AM, Gergely  wrote:
> Ok, lets concentrate 1 question at a time.
>
> If you define the referenced model later and use "lazy" relationship
> in the foreign key field (with the name of the model instead of the
> model itself) than in the generated sql you will not find the
> "REFERENCES" part of the foreign key definition.
> DB engine: sqlite. Django version: 1.2.4
>
> Example:
>
> Model:
> class Book(models.Model):
> publisher = models.ForeignKey("Publisher")
>
> class Publisher(models.Model):
>
> Generated sql:
> CREATE TABLE "books_book" (
>  "id" integer NOT NULL PRIMARY KEY,
>  "publisher_id" integer NOT NULL,
>  )
>
> Best regards,
> Gergely
>

Use a better SQL engine. This is Django 1.2.5 + postgresql:

class Series(models.Model):
  pass

class Episode(models.Model):
  series = models.ForeignKey('Series',)


CREATE TABLE "media_episode" (
"id" serial NOT NULL PRIMARY KEY,
"series_id" integer NOT NULL REFERENCES "media_series" ("id")
DEFERRABLE INITIALLY DEFERRED
)

Cheers

Tom

-- 
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 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: SQL generated for ManyToManyField is incorrect with Django 1.2.1

[Gergely]

Ok, lets concentrate 1 question at a time.

If you define the referenced model later and use "lazy" relationship
in the foreign key field (with the name of the model instead of the
model itself) than in the generated sql you will not find the
"REFERENCES" part of the foreign key definition.
DB engine: sqlite. Django version: 1.2.4

Example:

Model:
class Book(models.Model):
publisher = models.ForeignKey("Publisher")

class Publisher(models.Model):

Generated sql:
CREATE TABLE "books_book" (
  "id" integer NOT NULL PRIMARY KEY,
  "publisher_id" integer NOT NULL,
 )

Best regards,
Gergely

On márc. 6, 23:29, Ramiro Morales  wrote:
> On Sun, Mar 6, 2011 at 6:57 PM, Gergely  wrote:
> > Hello,
> > I'm trying to find an answer to an old question that is why I'm
> > forwarding this old message.
> > I tried to find the documentation of this change but couldn't.
> > Could you please help me?
> > I would like to find out:
> >  - why is only one foreign key generated in the intermediary join
> > table?
> >  - can I influence the sql generation to have both foreign keys
> > (without specifying my own join table) ?
>
> You don't show us you models and the generated SQL so
> I doubt anybody can help you here, please also include the
> information requested below for your second question.
>
>
>
>
>
>
>
>
>
>
>
> > There is a similar behaviour at simple foreign keys:
> > If you define the referenced model later and use "lazy" relationship
> > in the foreign key field (with the name of the model instead of the
> > model itself) than in the generated sql you will not find the
> > "REFERENCES" part of the foreign key definition.
> > Example:
>
> > Model:
> > class Book(models.Model):
> >    publisher = models.ForeignKey("Publisher")
>
> > class Publisher(models.Model):
>
> > Generated sql:
> > CREATE TABLE "books_book" (
> >    "id" integer NOT NULL PRIMARY KEY,
> >    "publisher_id" integer NOT NULL,
> > )
>
> What database engine are you using? What version of Django?
>
> --
> Ramiro Morales

-- 
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 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: SQL generated for ManyToManyField is incorrect with Django 1.2.1

[Ramiro Morales]

On Sun, Mar 6, 2011 at 6:57 PM, Gergely  wrote:
> Hello,
> I'm trying to find an answer to an old question that is why I'm
> forwarding this old message.
> I tried to find the documentation of this change but couldn't.
> Could you please help me?
> I would like to find out:
>  - why is only one foreign key generated in the intermediary join
> table?
>  - can I influence the sql generation to have both foreign keys
> (without specifying my own join table) ?

You don't show us you models and the generated SQL so
I doubt anybody can help you here, please also include the
information requested below for your second question.

>
> There is a similar behaviour at simple foreign keys:
> If you define the referenced model later and use "lazy" relationship
> in the foreign key field (with the name of the model instead of the
> model itself) than in the generated sql you will not find the
> "REFERENCES" part of the foreign key definition.
> Example:
>
> Model:
> class Book(models.Model):
>    publisher = models.ForeignKey("Publisher")
>
> class Publisher(models.Model):
>
> Generated sql:
> CREATE TABLE "books_book" (
>    "id" integer NOT NULL PRIMARY KEY,
>    "publisher_id" integer NOT NULL,
> )

What database engine are you using? What version of Django?

-- 
Ramiro Morales

-- 
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 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Fwd: SQL generated for ManyToManyField is incorrect with Django 1.2.1

[Gergely]

Hello,
I'm trying to find an answer to an old question that is why I'm
forwarding this old message.
I tried to find the documentation of this change but couldn't.
Could you please help me?
I would like to find out:
 - why is only one foreign key generated in the intermediary join
table?
 - can I influence the sql generation to have both foreign keys
(without specifying my own join table) ?

There is a similar behaviour at simple foreign keys:
If you define the referenced model later and use "lazy" relationship
in the foreign key field (with the name of the model instead of the
model itself) than in the generated sql you will not find the
"REFERENCES" part of the foreign key definition.
Example:

Model:
class Book(models.Model):
publisher = models.ForeignKey("Publisher")

class Publisher(models.Model):

Generated sql:
CREATE TABLE "books_book" (
"id" integer NOT NULL PRIMARY KEY,
"publisher_id" integer NOT NULL,
)

Sorry if these are stupid questions but I would like to understand it
completely.

Regards,
Gergely

-- Forwarded message --
From: Russell Keith-Magee 
Date: 2010 aug. 9, 01:06
Subject: SQL generated for ManyToManyField is incorrect with Django
1.2.1
To: Django users


On Mon, Aug 9, 2010 at 7:28 AM, Ersin Er  wrote:
> Hi,

> I was just trying the sample code from the Django Book 2nd Edition but
> the generated SQL script for the Books models are not correct.

> The Model code:

>http://dpaste.com/226416/

> The generated SQL script for sqlite:

>http://dpaste.com/226417/

> What's missing in the generated script is
> 'REFERENCES "books_book" ("id")'
> for the "book_id" field
> in the "books_book_authors" table. (The code listing in the book has
> it.)

> Am I missing something or Django 1.2.1 just generates incorrect
> script?

The SQL generated by Django 1.2.1 isn't incorrect - it's just
different. Django 1.2 introduced a number of internal changes to the
way many-to-many fields were represented; as a result, the output SQL
has changed slightly. The output described by the Django Book is out
of date, but not in any way that matters at a functional level -- the
public API for using m2m fields remains unchanged.

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 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: SQL generated for ManyToManyField is incorrect with Django 1.2.1

[Russell Keith-Magee]

On Mon, Aug 9, 2010 at 7:28 AM, Ersin Er  wrote:
> Hi,
>
> I was just trying the sample code from the Django Book 2nd Edition but
> the generated SQL script for the Books models are not correct.
>
> The Model code:
>
> http://dpaste.com/226416/
>
> The generated SQL script for sqlite:
>
> http://dpaste.com/226417/
>
> What's missing in the generated script is
> 'REFERENCES "books_book" ("id")'
> for the "book_id" field
> in the "books_book_authors" table. (The code listing in the book has
> it.)
>
> Am I missing something or Django 1.2.1 just generates incorrect
> script?

The SQL generated by Django 1.2.1 isn't incorrect - it's just
different. Django 1.2 introduced a number of internal changes to the
way many-to-many fields were represented; as a result, the output SQL
has changed slightly. The output described by the Django Book is out
of date, but not in any way that matters at a functional level -- the
public API for using m2m fields remains unchanged.

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-us...@googlegroups.com.
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

SQL generated for ManyToManyField is incorrect with Django 1.2.1

[Ersin Er]

Hi,

I was just trying the sample code from the Django Book 2nd Edition but
the generated SQL script for the Books models are not correct.

The Model code:

http://dpaste.com/226416/

The generated SQL script for sqlite:

http://dpaste.com/226417/

What's missing in the generated script is
'REFERENCES "books_book" ("id")'
for the "book_id" field
in the "books_book_authors" table. (The code listing in the book has
it.)

Am I missing something or Django 1.2.1 just generates incorrect
script?

Thanks.

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-us...@googlegroups.com.
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.