Missing tables from tg-admin sql sql

[mcfletch@…]

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);

[mcfletch@…]

The problem is caused by a single missing character in main.py line 1392 in SQLObject 0.8dev-r1814, the line reads as:

                sql = ';\n' + join_sql

when the line should be:

                sql += ';\n' + join_sql

that is, the sql is not being appended to.

[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.

[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!

[roger.demetrescu]

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

[droggisch]

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.

[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.

[roger.demetrescu]

Ops, I meant:

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

[jorge.vargas]

roger remenber to close the ticket :)

[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

[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.