Ticket #1010 (closed defect: fixed)
Missing tables from tg-admin sql sql
Reported by: | mcfletch@… | Owned by: | anonymous |
---|---|---|---|
Priority: | normal | Milestone: | |
Component: | SQLObject | Version: | 0.9a6 |
Severity: | normal | Keywords: | |
Cc: |
Description
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://mcfletch@localhost/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);
Change History
comment:2 Changed 12 years ago by mcfletch
Similar missing-character problems on lines 1441 (join with '\n' instead of ';\n') and 1423 (same problem), which result in not having semicolons to end the statements.
comment:3 Changed 12 years ago by roger.demetrescu
Mcfletch, you're right... My fault, sorry... :(
And it seems that this problem was importing into SQLObject, as you can see at:
http://svn.colorstudy.com/SQLObject/trunk/sqlobject/main.py
I am going to reopen that SQLObject ticket.
Thanks!
comment:5 Changed 12 years ago by droggisch
- Version changed from 0.9a5 to 0.9a6
To me it seems that the template the model is generated from is faulty - it uses tg_-prefixed tables, but not in the specified intermediateTables. After adding the prefix tg_ to all relations, things worked for me.
comment:6 Changed 12 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 12 years ago by jorge.vargas
- Status changed from new to closed
- Resolution set to fixed
roger remenber to close the ticket :)
comment:9 Changed 12 years ago by roger.demetrescu
Hi Jorge ! :)
But how do we deal with external libraries ? Currently we are using (from tg-admin info):
sqlobject 0.7.1dev-r1860
And, as I wrote some comments above, It seems it is fixed in rev. 1875. Shouldn't we close the ticket only when we (TG) bumps sqlobject to at least the rev. 1875 ?
Anyway, we still have #738 opened... and it falls in the same situation:
- we need to bump sqlobject to rev. 1875.
- we need to make sure it doesn't break anything and it really fixes this "semicolons" mess.
[]s Roger
comment:10 Changed 12 years ago by elvelind
I think we should close them when they are fixed in SO. just as we close them if there is a fix in the tg trunk.
The problem is caused by a single missing character in main.py line 1392 in SQLObject 0.8dev-r1814, the line reads as:
when the line should be:
that is, the sql is not being appended to.