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 #738 (closed defect: fixed)

Opened 13 years ago

Last modified 12 years ago

tg-admin sql create command creating malformed SQL

Reported by: adam@… Owned by: anonymous
Priority: normal Milestone:
Component: SQLObject Version: 0.9a4
Severity: normal Keywords:
Cc:

Description

I'm not sure what is causing the error, but "tg-admin sql create" is dropping some semi-colins in the table creation code, at least using the mysql driver. The relavent model.py and generated SQL code is included below.

Model.py:

from sqlobject import *
from datetime import datetime
from turbogears.database import PackageHub
from turbogears.identity.soprovider import TG_User, TG_Group, TG_Permission

hub = PackageHub("uconnballroom")
__connection__ = hub

class Person(SQLObject):
    first_name = StringCol(length=50,varchar=True,title="First name",alternateID=False,unique=False,notNone=True)
    last_name = StringCol(length=50,varchar=True,title="Last name",alternateID=False,unique=False,notNone=True)
    identity_id = IntCol(title="ID framework ID",notNone=False,default=None)
    email = StringCol(length=125,varchar=True,title="Email address",notNone=False,default=None)
    peoplesoft = IntCol(title="Peoplesoft number",notNone=False,default=None)
    phone_number = StringCol(length=15,varchar=True,title="Phone number",alternateID=False,unique=False,notNone=False,default=None)
    location = StringCol(length=80,varchar=True,title="Location",alternateID=False,unique=False,notNone=False,default=None)
    receive_news = BoolCol(title="Receive new news postings",default="False")
    allow_photo_search = BoolCol(title="Allow photos to be searched",default="True")
    role = EnumCol(enumValues=['Lead','Follow'],title="Lead or Follow preference",default="Lead")
    level = EnumCol(enumValues=['Newcomer','Bronze','Silver','Gold','Pre-Champ','Champ'],title="Prefered Level",default="Newcomer")
    newsPosts = MultipleJoin("NewsItem",joinColumn='poster_id')
    in_photos = RelatedJoin("Photo")
    memberships = MultipleJoin("Member",joinColumn='person_id')
    attendees = MultipleJoin("Attendie",joinColumn='person_id')

class NewsItem(SQLObject):
    subject = StringCol(length=150,varchar=True,title="The subject of a full news item or the line of a small news item",unique=False,notNone=True)
    full_post = StringCol(title="The full body of the news item",notNone=False,default=None)
    post_time = DateTimeCol()
    poster = ForeignKey("Person")


class Photo(SQLObject):
    time_taken = DateTimeCol(title="The time the photo was taken",alternateID=False,unique=False,notNone=True)
    title = StringCol(length=75,varchar=True,alternateID=False,unique=False,notNone=False)
    notes = StringCol(alternateID=False,unique=False,notNone=False)
    people_in_photo = RelatedJoin("Person")
    event = ForeignKey("Event")
    sets = RelatedJoin("PhotoSet")
    dance = ForeignKey("OfferedDance")


class Event(SQLObject):
    name = StringCol(length=50,varchar=True,alternateID=False,unique=False,notNone=True)
    location = StringCol(length=75,varchar=True,alternateID=False,unique=False,notNone=True)
    time = DateTimeCol(alternateID=False,unique=False,notNone=False)
    notes = StringCol(alternateID=False,unique=False,notNone=False)
    Type = EnumCol(enumValues=['Competition','Demo','Social Event'],default="Competition")
    photos = MultipleJoin("Photo",joinColumn='event_id')
    attendees = MultipleJoin("Attendie",joinColumn='event_id')
    dances = MultipleJoin("OfferedDance",joinColumn='event')

class Member(SQLObject):
    person = ForeignKey("Person")
    session = ForeignKey("Session")
    payment_method = EnumCol(enumValues=['cash','check','waved','unpaid'],default="unpaid")
    payment_amount = CurrencyCol(alternateID=False,default=0,unique=False,notNone=True)
    check_number = IntCol(alternateID=False,unique=False,notNone=False)
    on_team = BoolCol(default="False")


class Session(SQLObject):
    semester = EnumCol(enumValues=['Fall','Spring'],default="Fall")
    year = IntCol(alternateID=False,unique=False,notNone=False)
    members = MultipleJoin("Member",joinColumn='session_id')


class Attendie(SQLObject):
    person = ForeignKey("Person")
    comp_number = IntCol(alternateID=False,unique=False,notNone=False)
    needs_ride = BoolCol(default="True")
    event = ForeignKey("Event")
    car_driver = SingleJoin("Car",joinColumn='driver_id')
    car_passanger = ForeignKey("Car")
    dance_lead = MultipleJoin("Partnership",joinColumn='leader_id')
    dacne_follow = MultipleJoin("Partnership",joinColumn='follower_id')


class Car(SQLObject):
    driver = ForeignKey("Attendie")
    available_passangers = IntCol(alternateID=False,unique=False,notNone=True)
    passengers = MultipleJoin("Attendie",joinColumn='car_passanger_id')

class OfferedDance(SQLObject):
    style = EnumCol(enumValues=['Smooth','Standard','Rhythm','Latin'])
    level = EnumCol(enumValues=['Newcomer','Bronze','Silver','Gold','Pre-Champ','Champ'])
    dance = EnumCol(enumValues=['Waltz','Tango','Foxtrot','V. Waltz','Quickstep','Rumba','Cha-Cha','Swing','Mambo','Bolero','Jive','Paso-Doble','Samba'])
    people_dancing = MultipleJoin("Partnership",joinColumn='dance_id')
    event = ForeignKey('Event')

class Partnership(SQLObject):
    leader = ForeignKey("Attendie")
    follower = ForeignKey("Attendie")
    placement = IntCol(notNone=False)
    dance = ForeignKey("OfferedDance")


class PhotoSet(SQLObject):
    title = StringCol(length=40,varchar=True,alternateID=False,unique=False,notNone=False)
    summary = StringCol(alternateID=False,length="200",unique=False,notNone=False)
    date_time = DateTimeCol(alternateID=False,unique=False,notNone=False)
    photos = RelatedJoin("Photo")

The resulting SQL code:

Using database URI mysql://gears:*password removed*@localhost:3306/ballroom_gears
CREATE TABLE attendie (
    id INT PRIMARY KEY AUTO_INCREMENT,
    person_id INT,
    comp_number INT,
    needs_ride TINYINT,
    event_id INT,
    car_passanger_id INT
);

CREATE TABLE car (
    id INT PRIMARY KEY AUTO_INCREMENT,
    driver_id INT,
    available_passangers INT NOT NULL
);

CREATE TABLE event (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    location VARCHAR(75) NOT NULL,
    time DATETIME,
    notes TEXT,
    type ENUM('Competition', 'Demo', 'Social Event')
);

CREATE TABLE member (
    id INT PRIMARY KEY AUTO_INCREMENT,
    person_id INT,
    session_id INT,
    payment_method ENUM('cash', 'check', 'waved', 'unpaid'),
    payment_amount DECIMAL(10, 2) NOT NULL,
    check_number INT,
    on_team TINYINT
);

CREATE TABLE news_item (
    id INT PRIMARY KEY AUTO_INCREMENT,
    subject VARCHAR(150) NOT NULL,
    full_post TEXT,
    post_time DATETIME,
    poster_id INT
);

CREATE TABLE offered_dance (
    id INT PRIMARY KEY AUTO_INCREMENT,
    style ENUM('Smooth', 'Standard', 'Rhythm', 'Latin'),
    level ENUM('Newcomer', 'Bronze', 'Silver', 'Gold', 'Pre-Champ', 'Champ'),
    dance ENUM('Waltz', 'Tango', 'Foxtrot', 'V. Waltz', 'Quickstep', 'Rumba', 'Cha-Cha', 'Swing', 'Mambo', 'Bolero', 'Jive', 'Paso-Doble', 'Samba'),
    event_id INT
);

CREATE TABLE partnership (
    id INT PRIMARY KEY AUTO_INCREMENT,
    leader_id INT,
    follower_id INT,
    placement INT,
    dance_id INT
);

CREATE TABLE person (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    identity_id INT,
    email VARCHAR(125),
    peoplesoft INT,
    phone_number VARCHAR(15),
    location VARCHAR(80),
    receive_news TINYINT,
    allow_photo_search TINYINT,
    role ENUM('Lead', 'Follow'),
    level ENUM('Newcomer', 'Bronze', 'Silver', 'Gold', 'Pre-Champ', 'Champ')
)
CREATE TABLE person_photo (
person_id INT NOT NULL,
photo_id INT NOT NULL
);

CREATE TABLE photo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    time_taken DATETIME NOT NULL,
    title VARCHAR(75),
    notes TEXT,
    event_id INT,
    dance_id INT
)
CREATE TABLE photo_photo_set (
photo_id INT NOT NULL,
photo_set_id INT NOT NULL
);

Change History

comment:1 Changed 13 years ago by roger.demetrescu

I can reproduce this executing "tg-admin sql sql" with a sqlite database.

I noticed that it happens with classes that use RelatedJoin?().

Try commenting "in_photos = RelatedJoin?("Photo")" from Person() class... the Person table will have a semi-colon again...

Cheers

Roger

comment:2 Changed 13 years ago by roger.demetrescu

Of course commenting "RelatedJoin?" won't solve the problem... ;)

comment:3 Changed 13 years ago by roger.demetrescu

I don't have a decent diff tool here... and the problem seems to be with SQLObject after all...

Try changing your sqlobject/main.py and tell me if it works:

change

    def createTableSQL(cls, createJoinTables=True, connection=None,
                       createIndexes=True):
        conn = connection or cls._connection
        sql = conn.createTableSQL(cls)
        if createJoinTables:
            sql += '\n' + cls.createJoinTablesSQL(connection=conn)
        if createIndexes:
            sql += '\n' + cls.createIndexesSQL(connection=conn)
        return sql
    createTableSQL = classmethod(createTableSQL)

into :

    def createTableSQL(cls, createJoinTables=True, connection=None,
                       createIndexes=True):
        conn = connection or cls._connection
        sql = conn.createTableSQL(cls)
        if createJoinTables:
            join_sql = cls.createJoinTablesSQL(connection=conn)
            if join_sql:
                sql = ';\n' + join_sql
        if createIndexes:
            index_sql = cls.createIndexesSQL(connection=conn)
            if index_sql:
                sql += ';\n' + index_sql
        return sql
    createTableSQL = classmethod(createTableSQL)

Cheers

Roger

comment:4 Changed 13 years ago by roger.demetrescu

Doing a little search in SQLObject Bug tracker, I have found this:

 http://sourceforge.net/tracker/index.php?func=detail&aid=1421647&group_id=74338&atid=540672

comment:5 Changed 13 years ago by roger.demetrescu

Good news...

People from SQLObject has applied the patch... It was commited in revision 1774.

So all we need now is to upgrade SQLObject... :)

comment:6 Changed 13 years ago by roger.demetrescu

From  http://sourceforge.net/tracker/index.php?func=detail&aid=1421647&group_id=74338&atid=540672 :

=============

Date: 2006-08-22 07:46 Sender: phd Logged In: YES user_id=4799

Fixed in thre revision 1874 in the trunk and 1875 in the 0.7-branch. Thank you!

=============

I hope it is finally fixed... :)

Can someone else confirm it ? I dont have TG/SQLObject here now.

comment:8 Changed 13 years ago by jorge.vargas

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

we are using 1860 right now so this should be fix.

Note: See TracTickets for help on using tickets.