#738: tg-admin sql create command creating malformed SQL
---------------------------------+------------------------------------------
Reporter: [EMAIL PROTECTED] | Owner: anonymous
Type: defect | Status: new
Priority: normal | Milestone:
Component: SQLObject | Version: 0.9a4
Severity: normal | Keywords:
---------------------------------+------------------------------------------
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
[EMAIL PROTECTED]: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
);
}}}
--
Ticket URL: <http://trac.turbogears.org/turbogears/ticket/738>
TurboGears <http://www.turbogears.org/>
TurboGears front-to-back web development
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"TurboGears Tickets" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/turbogears-tickets
-~----------~----~----~----~------~----~------~--~---
