Warning: Can't synchronize with repository "(default)" (Unsupported version control system "svn": No module named svn). Look in the Trac log for more information.

Ticket #279 (closed defect: fixed)

Opened 13 years ago

Last modified 11 years ago

tg-admin sql create leads to postgres sql in wrong order

Reported by: florian Owned by: anonymous
Priority: normal Milestone:
Component: SQLObject Version:
Severity: normal Keywords: postgres mysql sqlobject sql create order failed
Cc:

Description

Using the latest sqlobject 7.0 tg-admin sql sql fails. The example is TurboTunes model.py

The produced SQL output is:

CREATE TABLE album (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    artist_id INT, CONSTRAINT artist_id_exists FOREIGN KEY (artist_id) REFERENCES artist (id)
);

CREATE TABLE artist (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200)
)
CREATE TABLE artist_genre (
artist_id INT NOT NULL,
genre_id INT NOT NULL
);

CREATE TABLE genre (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200)
);

CREATE TABLE song (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    album_id INT, CONSTRAINT album_id_exists FOREIGN KEY (album_id) REFERENCES album (id)
);

Trying to run this leads to:

psql:part.sql:5: NOTICE:  CREATE TABLE will create implicit sequence "album_id_seq" for "serial" column "album.id"
psql:part.sql:5: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "album_pkey" for table "album"
psql:part.sql:5: ERROR:  relation "artist" does not exist
psql:part.sql:14: ERROR:  syntax error at or near "CREATE" at character 74
psql:part.sql:19: NOTICE:  CREATE TABLE will create implicit sequence "genre_id_seq" for "serial" column "genre.id"
psql:part.sql:19: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "genre_pkey" for table "genre"
CREATE TABLE
psql:part.sql:25: NOTICE:  CREATE TABLE will create implicit sequence "song_id_seq" for "serial" column "song.id"
psql:part.sql:25: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "song_pkey" for table "song"
psql:part.sql:25: ERROR:  relation "album" does not exist

But it should be

CREATE TABLE genre (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200)
);

CREATE TABLE artist (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200)
);

CREATE TABLE album (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    artist_id INT, CONSTRAINT artist_id_exists FOREIGN KEY (artist_id) REFERENCES artist (id)
);

CREATE TABLE artist_genre (
artist_id INT NOT NULL,
genre_id INT NOT NULL
);

CREATE TABLE song (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    album_id INT, CONSTRAINT album_id_exists FOREIGN KEY (album_id) REFERENCES album (id)
);

Which results in:

psql:part.sql:4: NOTICE:  CREATE TABLE will create implicit sequence "artist_id_seq" for "serial" column "artist.id"
psql:part.sql:4: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "artist_pkey" for table "artist"
CREATE TABLE
psql:part.sql:9: NOTICE:  CREATE TABLE will create implicit sequence "genre_id_seq" for "serial" column "genre.id"
psql:part.sql:9: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "genre_pkey" for table "genre"
CREATE TABLE
psql:part.sql:15: NOTICE:  CREATE TABLE will create implicit sequence "album_id_seq" for "serial" column "album.id"
psql:part.sql:15: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "album_pkey" for table "album"
CREATE TABLE
CREATE TABLE
psql:part.sql:27: NOTICE:  CREATE TABLE will create implicit sequence "song_id_seq" for "serial" column "song.id"
psql:part.sql:27: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "song_pkey" for table "song"
CREATE TABLE

Attachments

command.patch Download (4.5 KB) - added by yemartin 13 years ago.
A tentative patch that should fix both #279 and #882. (Update: fixed date in comment, I was 2 years early ;)

Change History

comment:1 Changed 13 years ago by florian

traceback:

Traceback (most recent call last):
  File "/usr/bin/tg-admin", line 7, in ?
    sys.exit(
  File "/usr/local/lib/python2.4/site-packages/TurboGears-0.9a0dev_r391-py2.4.egg/turbogears/command/base.py", line 241, in main
    command.run()
  File "/usr/local/lib/python2.4/site-packages/TurboGears-0.9a0dev_r391-py2.4.egg/turbogears/command/base.py", line 101, in run
    command.the_runner.run(sys.argv)
  File "/usr/local/lib/python2.4/site-packages/SQLObject-0.7.0-py2.4.egg/sqlobject/manager/command.py", line 102, in run
    runner.run()
  File "/usr/local/lib/python2.4/site-packages/SQLObject-0.7.0-py2.4.egg/sqlobject/manager/command.py", line 233, in run
    self.command()
  File "/usr/local/lib/python2.4/site-packages/SQLObject-0.7.0-py2.4.egg/sqlobject/manager/command.py", line 570, in command
    soClass.createTable()
  File "/usr/local/lib/python2.4/site-packages/SQLObject-0.7.0-py2.4.egg/sqlobject/main.py", line 1308, in createTable
    conn.createTable(cls)
  File "/usr/local/lib/python2.4/site-packages/SQLObject-0.7.0-py2.4.egg/sqlobject/dbconnection.py", line 516, in createTable
    self.query(self.createTableSQL(soClass))
  File "/usr/local/lib/python2.4/site-packages/SQLObject-0.7.0-py2.4.egg/sqlobject/dbconnection.py", line 303, in query
    return self._runWithConnection(self._query, s)
  File "/usr/local/lib/python2.4/site-packages/SQLObject-0.7.0-py2.4.egg/sqlobject/dbconnection.py", line 217, in _runWithConnection
    val = meth(conn, *args)
  File "/usr/local/lib/python2.4/site-packages/SQLObject-0.7.0-py2.4.egg/sqlobject/dbconnection.py", line 300, in _query
    self._executeRetry(conn, conn.cursor(), s)
  File "/usr/local/lib/python2.4/site-packages/SQLObject-0.7.0-py2.4.egg/sqlobject/dbconnection.py", line 295, in _executeRetry
    return cursor.execute(query)
psycopg.ProgrammingError: ERROR:  relation "artist" does not exist

CREATE TABLE album (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    artist_id INT, CONSTRAINT artist_id_exists FOREIGN KEY (artist_id) REFERENCES artist (id)
)

comment:2 Changed 13 years ago by nyenyec

I experience the same problem when trying to go through the turbotunes tutorial with postgres.

comment:3 Changed 13 years ago by nyenyec <nyenyec@…>

I found a workaround based on  http://joe.sl4g.com/words/code/sqlobject_order.html

You need to add this at the beginning of your module.py file:

soClasses = ('Artist', 'Album', 'Genre', 'Song')

comment:4 Changed 13 years ago by godoy

  • Status changed from new to closed
  • Resolution set to fixed

I'm closing this as "fixed" because the soclasses approach is the easiest solution for the problem.

Another approach for this would be adding restrictions after creating the tables...

comment:5 Changed 13 years ago by jorge.vargas

  • Keywords mysql sqlobject failed added
  • Version 0.9a6 deleted
  • Milestone 0.8 deleted

just as a note

soClasses MUST be order alphabetically in a proper python way

that is prevItem = "anythin" for item in soClasses:

prevItem = item assert prevItem < item

umm that was complicated for ones words are better then python :)

for each item in the list it must be smaller ('<') then the previous one.


note on properties taken away the version specific since this is still present in sqlobject and still giving people problems

comment:6 Changed 13 years ago by yem_trac.turbogears.org@…

  • Status changed from closed to reopened
  • Resolution fixed deleted

Reopening this ticket, as soClasses is a workaround, not a fix. Furthermore, without a patch to SQLObject, the soClasses approach may still fail when doing "tg-admin sql drop".

comment:7 Changed 13 years ago by jorge.vargas

#882 is a fix to soClasses but a fix of a fix is not a good fix :)

Changed 13 years ago by yemartin

A tentative patch that should fix both #279 and #882. (Update: fixed date in comment, I was 2 years early ;)

comment:8 Changed 13 years ago by jorge.vargas

  • Status changed from reopened to closed
  • Resolution set to fixed

comment:9 Changed 13 years ago by older

Was it really fixed? I'm experiencing this now with tg1.0b1 and fresh SQLObject 0.7.1.

comment:10 Changed 12 years ago by ptwobrussell

I too am experiencing this same issue with tg1.0b1 and fresh SQLObject. I believe b1 was released prior to 9/22, so chances are this is fixed in svn

comment:11 Changed 12 years ago by jorge.vargas

  • Status changed from closed to reopened
  • Resolution fixed deleted

could you guys try the new 0.7.3 version if not lets post to SO mailing list again with this.

comment:12 Changed 12 years ago by wmark

Having applied that patch I can no longer user child/parent relations such as in:

class Category(SQLObject):
    class sqlmeta:
        table = "categories"
        idName = "category"

    category_name       = UnicodeCol(length=64, notNone=True)
    parent_category     = ForeignKey('Category', default=None)
    sub_category        = MultipleJoin('Category', joinColumn='parent_category')

... it yields in "Error 150" or

Warning: a circular reference was detected in the model. Unable to sort the classes by dependency: they will be treated in alphabetic order. This may or may not work depending on your database backend. The error was:
Found a circular reference: ... --> Category --> Category 

Removing that patch (as applied in >=SQLObject-0.7.4) everything works fine for me.

comment:13 Changed 11 years ago by faide

  • Status changed from reopened to closed
  • Resolution set to fixed

This problem was solved differently a long time ago.

Note: See TracTickets for help on using tickets.