#1010: Missing tables from tg-admin sql sql
------------------------------------+---------------------------------------
Reporter: [EMAIL PROTECTED] | Owner: anonymous
Type: defect | Status: new
Priority: normal | Milestone:
Component: SQLObject | Version: 0.9a5
Severity: normal | Keywords:
------------------------------------+---------------------------------------
I'm playing around with TurboGears and have created a model that breaks
TurboGears/SQLObject's SQL generation. This is on a system with a patched
version of SQLObject wrt ticket #738
http://trac.turbogears.org/turbogears/ticket/738
The generated SQL is missing entire tables (as well as semicolons),
particularly the tg_group table is missing.
Here is the model:
{{{
from datetime import datetime
from sqlobject import *
from sqlobject.inheritance import InheritableSQLObject
from turbogears import identity
from turbogears.database import PackageHub
hub = PackageHub("betterbill")
__connection__ = hub
# class YourDataClass(SQLObject):
# pass
##class StateHistory( SQLObject ):
## """State-tracking table for all billable-recurrence items"""
## state = EnumCol(
## enumValues = [
## STATE_PENDING, # not-yet activated
## STATE_ACTIVE, # activated and live
## STATE_INACTIVE, # deactivated by user
## STATE_DISABLED, # disabled by system
## ],
## )
##
(
BILLING_MONTHLY,
BILLING_YEARLY,
BILLING_MONTHLY_ANNIVERSARY,
BILLING_YEARLY_ANNIVERSARY,
) = ('monthly','yearly','monthly-a','yearly-a')
class Node( InheritableSQLObject ):
"""Root of the object hierarchy, a hierarchic node"""
owner = ForeignKey(
'User',
notNone = True,
)
class TypeNode( Node ):
"""Nodes which define types of other nodes"""
name = UnicodeCol(
length = 64, alternateID=True, notNone=True,
)
description = UnicodeCol(
notNone=True, default=u"",
)
class PackageType( TypeNode ):
"""Definition of parameters for a type of Package (purchased
thing)"""
billingType = EnumCol(
default = BILLING_MONTHLY,
enumValues = [
BILLING_MONTHLY,
BILLING_YEARLY,
BILLING_MONTHLY_ANNIVERSARY,
BILLING_YEARLY_ANNIVERSARY,
],
notNone = True,
)
# icons, html descriptions, html summaries
class EffectType( TypeNode ):
"""Definition of parameters for a type of Effect (technical
effect)
enableFunction -- function to enable the technical effect
disableFunction -- function to disable the technical effect
viewFunction -- function to provide structured view of back-end
settings
checkFunction -- function to compare viewFunction settings to
expected and
report any errors detected
"""
enableFunction = UnicodeCol(
length = 256, alternateID=True, notNone=True,
)
disableFunction = UnicodeCol(
length = 256, alternateID=True, notNone=True,
)
viewFunction = UnicodeCol(
length = 256, alternateID=True, notNone=True,
)
checkFunction = UnicodeCol(
length = 256, alternateID=True, notNone=True,
)
class Package( Node ):
"""Billable recurring package of things purchased by the user"""
type = ForeignKey(
'PackageType',
notNone = True,
)
def state( self ):
"""Retrieve last state for a state-bearing node as a
simple string"""
for item in StateTrace.select(
StateTrace.node==self.node_id,
orderBy=StateTrace.changeDate,
).reversed()[:1]:
return item.endState.name
return None
class Effect( Node ):
"""Technical effect created by the system"""
type = ForeignKey(
'EffectType',
notNone = True,
)
class Config( Node ):
"""Non-billable configuration/settings which control effects
Configs are attached to packages and have effects attached to
them,
note that effects may attach to multiple configurations
"""
class Inventory( Node ):
"""Object in inventory which may be sold/loaned to customer"""
class Dependency( SQLObject ):
"""Dependency from one Node to another"""
parent = ForeignKey(
'Node',
notNone = True,
)
child = ForeignKey(
'Node',
notNone = True,
)
type = EnumCol(
default = 'contains',
enumValues = [
'required-by',
'contains',
],
notNone = True,
)
class State( InheritableSQLObject ):
"""Base class for descriptions of states into which nodes can
get"""
name = UnicodeCol(
length = 64, alternateID=True, notNone=True,
)
description = UnicodeCol(
notNone=True, default=u"",
)
leavePermission = ForeignKey(
# permission required to leave this state...
"Permission",
)
enterPermission = ForeignKey(
# permission required to enter this state...
"Permission",
)
leaveFunction = UnicodeCol(
# function to call whenever a package leaves this state
length = 256, alternateID=True,
)
enterFunction = UnicodeCol(
# function to call whenever a package enters this state
length = 256, alternateID=True,
)
class InventoryState( State ):
"""Descriptions of states in which inventory may be placed"""
class PackageState( State ):
"""Descriptions of states in which a package may be placed"""
billable = BoolCol(
default=True,notNone=True,
)
class StateTrace( SQLObject ):
"""Trace of state-changes for a given package (billing audit
trail)"""
node = ForeignKey(
# the node changing state...
"Node", notNone=True,
)
startState = ForeignKey(
'State',
)
endState = ForeignKey(
'State', notNone=True,
)
actor = ForeignKey(
"User", notNone=True,
)
changeDate = DateTimeCol(
default=datetime.now , notNone=True,
)
description = UnicodeCol(
notNone=False,
)
# Now the actual application objects...
class ATAInventory( Inventory ):
"""ATA to be assigned/sold/rented to the customer"""
esn = StringCol(
length=12, alternateID=True,
notNone = True,
)
mac = StringCol(
length=12, alternateID=True,
notNone = True,
)
class TelephoneNumberInventory( Inventory ):
"""Telephone number in inventory for assignment to a customer"""
number = StringCol(
length=20, alternateID=True,
notNone = True,
)
assigned = SingleJoin(
"TelephoneNumberConfig", joinColumn='inventory',
)
class TelephoneNumberConfig( Config ):
"""Configuration required to provision a telephone number"""
inventory = ForeignKey( 'TelephoneNumberInventory' )
class VoicemailConfig( Config ):
"""Configuration required to provision voicemail"""
password = StringCol(
length=6, alternateID=False,
)
class AccountConfig( Config ):
"""Configuration required to provision an account w/ access
Requires:
Identity Setup
Caller ID Setup
Diversion Setup
Access Rules Setup
"""
# Turbogears generic username/group framework follows...
class VisitIdentity(SQLObject):
visit_key = StringCol( length=40, alternateID=True,
alternateMethodName="by_visit_key" )
user_id = IntCol()
class Group(SQLObject):
"""
An ultra-simple group definition.
"""
# names like "Group", "Order" and "User" are reserved words in SQL
# so we set the name to something safe for SQL
class sqlmeta:
table="tg_group"
group_name = UnicodeCol( length=16, alternateID=True,
alternateMethodName="by_group_name" )
display_name = UnicodeCol( length=255 )
created = DateTimeCol( default=datetime.now )
# collection of all users belonging to this group
users = RelatedJoin( "User", intermediateTable="user_group",
joinColumn="group_id",
otherColumn="user_id" )
# collection of all permissions for this group
permissions = RelatedJoin( "Permission", joinColumn="group_id",
intermediateTable="group_permission",
otherColumn="permission_id" )
class User(SQLObject):
"""
Reasonably basic User definition. Probably would want additional
attributes.
"""
# names like "Group", "Order" and "User" are reserved words in SQL
# so we set the name to something safe for SQL
class sqlmeta:
table="tg_user"
user_name = UnicodeCol( length=16, alternateID=True,
alternateMethodName="by_user_name" )
email_address = UnicodeCol( length=255, alternateID=True,
alternateMethodName="by_email_address" )
display_name = UnicodeCol( length=255 )
password = UnicodeCol( length=40 )
created = DateTimeCol( default=datetime.now )
# groups this user belongs to
groups = RelatedJoin( "Group", intermediateTable="user_group",
joinColumn="user_id",
otherColumn="group_id" )
nodes = MultipleJoin(
"Node",joinColumn="owner",
)
def _get_permissions( self ):
perms = set()
for g in self.groups:
perms = perms | set(g.permissions)
return perms
def _set_password( self, cleartext_password ):
"Runs cleartext_password through the hash algorithm before
saving."
hash = identity.encrypt_password(cleartext_password)
self._SO_set_password(hash)
def set_password_raw( self, password ):
"Saves the password as-is to the database."
self._SO_set_password(password)
class Permission(SQLObject):
permission_name = UnicodeCol( length=32, alternateID=True,
alternateMethodName="by_permission_name" )
description = UnicodeCol( length=255 )
groups = RelatedJoin( "Group",
intermediateTable="group_permission",
joinColumn="permission_id",
otherColumn="group_id" )
}}}
And here is the generated SQL:
{{{
Using database URI postgres://[EMAIL PROTECTED]/betterbilling
CREATE TABLE ata_inventory (
id SERIAL PRIMARY KEY,
esn VARCHAR(12) NOT NULL UNIQUE,
mac VARCHAR(12) NOT NULL UNIQUE,
child_name VARCHAR(255)
);
CREATE TABLE account_config (
id SERIAL PRIMARY KEY,
child_name VARCHAR(255)
);
CREATE TABLE config (
id SERIAL PRIMARY KEY,
child_name VARCHAR(255)
);
CREATE TABLE dependency (
id SERIAL PRIMARY KEY,
parent_id INT NOT NULL,
child_id INT NOT NULL,
type VARCHAR(11) CHECK (type in ('required-by', 'contains')) NOT NULL
);
CREATE TABLE effect (
id SERIAL PRIMARY KEY,
type_id INT NOT NULL,
child_name VARCHAR(255)
);
CREATE TABLE effect_type (
id SERIAL PRIMARY KEY,
enable_function VARCHAR(256) NOT NULL UNIQUE,
disable_function VARCHAR(256) NOT NULL UNIQUE,
view_function VARCHAR(256) NOT NULL UNIQUE,
check_function VARCHAR(256) NOT NULL UNIQUE,
child_name VARCHAR(255)
);
;
CREATE TABLE user_group (
group_id INT NOT NULL,
user_id INT NOT NULL
)
CREATE TABLE group_permission (
group_id INT NOT NULL,
permission_id INT NOT NULL
);
CREATE TABLE inventory (
id SERIAL PRIMARY KEY,
child_name VARCHAR(255)
);
CREATE TABLE inventory_state (
id SERIAL PRIMARY KEY,
child_name VARCHAR(255)
);
CREATE TABLE node (
id SERIAL PRIMARY KEY,
owner_id INT NOT NULL,
child_name VARCHAR(255)
);
CREATE TABLE package (
id SERIAL PRIMARY KEY,
type_id INT NOT NULL,
child_name VARCHAR(255)
);
CREATE TABLE package_state (
id SERIAL PRIMARY KEY,
billable BOOL NOT NULL,
child_name VARCHAR(255)
);
CREATE TABLE package_type (
id SERIAL PRIMARY KEY,
billing_type VARCHAR(9) CHECK (billing_type in ('monthly', 'yearly',
'monthly-a', 'yearly-a')) NOT NULL,
child_name VARCHAR(255)
);
CREATE TABLE permission (
id SERIAL PRIMARY KEY,
permission_name VARCHAR(32) NOT NULL UNIQUE,
description VARCHAR(255)
);
CREATE TABLE state (
id SERIAL PRIMARY KEY,
name VARCHAR(64) NOT NULL UNIQUE,
description TEXT NOT NULL,
leave_permission_id INT,
enter_permission_id INT,
leave_function VARCHAR(256) NOT NULL UNIQUE,
enter_function VARCHAR(256) NOT NULL UNIQUE,
child_name VARCHAR(255)
);
CREATE TABLE state_trace (
id SERIAL PRIMARY KEY,
node_id INT NOT NULL,
start_state_id INT,
end_state_id INT NOT NULL,
actor_id INT NOT NULL,
change_date TIMESTAMP NOT NULL,
description TEXT
);
CREATE TABLE telephone_number_config (
id SERIAL PRIMARY KEY,
inventory_id INT,
child_name VARCHAR(255)
);
CREATE TABLE telephone_number_inventory (
id SERIAL PRIMARY KEY,
number VARCHAR(20) NOT NULL UNIQUE,
child_name VARCHAR(255)
);
CREATE TABLE type_node (
id SERIAL PRIMARY KEY,
name VARCHAR(64) NOT NULL UNIQUE,
description TEXT NOT NULL,
child_name VARCHAR(255)
);
CREATE TABLE tg_user (
id SERIAL PRIMARY KEY,
user_name VARCHAR(16) NOT NULL UNIQUE,
email_address VARCHAR(255) NOT NULL UNIQUE,
display_name VARCHAR(255),
password VARCHAR(40),
created TIMESTAMP
);
CREATE TABLE visit_identity (
id SERIAL PRIMARY KEY,
visit_key VARCHAR(40) NOT NULL UNIQUE,
user_id INT
);
CREATE TABLE voicemail_config (
id SERIAL PRIMARY KEY,
password VARCHAR(6),
child_name VARCHAR(255)
);
ALTER TABLE dependency ADD CONSTRAINT parent_id_exists FOREIGN KEY
(parent_id) REFERENCES node (id);
ALTER TABLE dependency ADD CONSTRAINT child_id_exists FOREIGN KEY
(child_id) REFERENCES node (id);
ALTER TABLE effect ADD CONSTRAINT type_id_exists FOREIGN KEY (type_id)
REFERENCES effect_type (id);
ALTER TABLE node ADD CONSTRAINT owner_id_exists FOREIGN KEY (owner_id)
REFERENCES tg_user (id);
ALTER TABLE package ADD CONSTRAINT type_id_exists FOREIGN KEY (type_id)
REFERENCES package_type (id);
ALTER TABLE state ADD CONSTRAINT leave_permission_id_exists FOREIGN KEY
(leave_permission_id) REFERENCES permission (id);
ALTER TABLE state ADD CONSTRAINT enter_permission_id_exists FOREIGN KEY
(enter_permission_id) REFERENCES permission (id);
ALTER TABLE state_trace ADD CONSTRAINT node_id_exists FOREIGN KEY
(node_id) REFERENCES node (id);
ALTER TABLE state_trace ADD CONSTRAINT start_state_id_exists FOREIGN KEY
(start_state_id) REFERENCES state (id);
ALTER TABLE state_trace ADD CONSTRAINT end_state_id_exists FOREIGN KEY
(end_state_id) REFERENCES state (id);
ALTER TABLE state_trace ADD CONSTRAINT actor_id_exists FOREIGN KEY
(actor_id) REFERENCES tg_user (id);
ALTER TABLE telephone_number_config ADD CONSTRAINT inventory_id_exists
FOREIGN KEY (inventory_id) REFERENCES telephone_number_inventory (id);
}}}
--
Ticket URL: <http://trac.turbogears.org/turbogears/ticket/1010>
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
-~----------~----~----~----~------~----~------~--~---
