MIchael,
Thanks for your help on this - I agree I want to get beyond a mess
here regardless if it is 'working' ....
Here are 3 tables that demostrate the issues (with the changes I
needed to do use_alter, modify schema.py, hardcode in schema name to
ForeignKey and tables). I want the model file below to work regardless
of the database target (so the schema cram and Oracle target need to
be separated)
The problem is that there is already a table in another schema called
'app_user' and 'facility' so check tables is finding them...
Also - you will note the ForeignKey generates an invalid ix_* index
for Oracle so I needed to modify schema.py until I get around to
setting index=False and manually creating Index'es
#!/usr/bin/env python
#
# Copyright (c) 2008 Digital Innovation, Inc. All rights reserved.
#
# The information and source code contained herein is the exclusive
property of
# Digital Innovation Inc (dicorp) and may not be disclosed,
examined, or
# reproduced in whole or in part without the explicit written
authorization from dicorp.
#
#
from datetime import datetime
from elixir import Entity, Field, OneToMany, ManyToOne, ManyToMany,
has_field, belongs_to
from elixir import options_defaults, using_options, setup_all,
using_table_options
from elixir import String, Unicode, Integer, DateTime, SmallInteger,
Boolean, Text, Binary, Float
options_defaults['table_options'] = {'schema':'cram','owner':'cram'}
from sqlalchemy import Sequence, CheckConstraint, Index,
ForeignKeyConstraint, ForeignKey, schema
class Facility(Entity):
using_options(tablename='facility')
#using_table_options(schema='cram',owner='cram')
control_number = Field(Integer,
Sequence('facility_control_number_seq'), primary_key = True,
autoincrement = True, nullable = False)
id = Field(String(20), nullable = False)
name = Field(String(50), nullable = False)
description = Field(String(255), nullable = True)
url = Field(String(255), nullable = True)
type_link = Field(Integer, CheckConstraint('type_link >= 0'),
nullable = False)
#TODO: UNIQUE Index IdUniqueKey
class PropertyItem(Entity):
using_options(tablename='property_item')
#using_table_options(schema='cram',owner='cram')
control_number = Field(Integer, Sequence('property_item_cn_seq'),
primary_key = True, autoincrement = True, nullable = False)
created_by = Field(Integer,
ForeignKey('cram.app_user.control_number', name =
'property_item_created_by_fk', use_alter = False), nullable = True,
index = True) #Link field for link CreatedBy
created_when = Field(DateTime, nullable = True)
modified_by = Field(Integer,
ForeignKey('cram.app_user.control_number', name =
'property_item_modified_by_fk', use_alter = False), nullable = True,
index = True) #Link field for link ModifiedBy
modified_when = Field(DateTime, nullable = True)
property_profile_link = Field(Integer,
ForeignKey('cram.property_profile.control_number', name =
'property_item_3_fk', use_alter = False), nullable = False, index =
True) #Link field for link PropertyProfileLink
code = Field(String(50), nullable = False)
description = Field(String(50), nullable = True)
sort_order = Field(SmallInteger, CheckConstraint('sort_order >=
0'), nullable = True)
disabled = Field(Boolean, nullable = True)
class PropertyProfile(Entity):
using_options(tablename='property_profile')
#using_table_options(schema='cram',owner='cram')
control_number = Field(Integer,
Sequence('property_profile_cn_seq'), primary_key = True, autoincrement
= True, nullable = False)
primary_facility_link = Field(Integer,
ForeignKey('cram.facility.control_number', name =
'property_profile_1_fk', use_alter = False), nullable = True, index =
True) #Link field for link PrimaryFacilityLink
created_by = Field(Integer,
ForeignKey('cram.app_user.control_number', name =
'property_profile_created_by_fk', use_alter = False), nullable = True,
index = True) #Link field for link CreatedBy
created_when = Field(DateTime, nullable = True)
modified_by = Field(Integer,
ForeignKey('cram.app_user.control_number', name =
'property_profile_3_fk', use_alter = False), nullable = True, index =
True) #Link field for link ModifiedBy
modified_when = Field(DateTime, nullable = True)
name = Field(String(50), nullable = False)
description = Field(String(255), nullable = True)
active = Field(Boolean, nullable = True)
class AppUser(Entity):
using_options(tablename='app_user')
#using_table_options(schema='cram',owner='cram')
control_number = Field(Integer,
Sequence('app_user_control_number_seq'), primary_key = True,
autoincrement = True, nullable = False)
primary_facility_link = Field(Integer,
ForeignKey('cram.facility.control_number', name = 'app_user_1_fk',
use_alter = False), nullable = True, index = True) #Link field for
link PrimaryFacilityLink
id = Field(String(20), nullable = False)
name = Field(String(50), nullable = False)
description = Field(String(255), nullable = True)
url = Field(String(255), nullable = True)
email_address = Field(String(255), nullable = True)
expiration_date = Field(DateTime, nullable = True)
password = Field(String(40), nullable = False)
disabled = Field(Boolean, nullable = True)
#TODO: UNIQUE Index IdUniqueKey
setup_all()
create_all(checkfirst=False)
On May 24, 9:57 am, Michael Bayer <[EMAIL PROTECTED]> wrote:
> On May 23, 2008, at 10:02 PM, Brandon Goldfedder wrote:
>
>
>
> > Michael,
> > Yes - that is what I am doing now :
> > ForeignKey("schemaname.tablename.colname")
>
> > and I have things working. (see example 3).
> > My problem is these steps
> > seem really ugly and a lot more work than it should be to get things
> > working portably and reliably - thus my question on if this is best
> > practice or not.
>
> no, what you had there is crazy. send an example case (just the plain
> tables and their relationships, none of the "extra" stuff you did) so
> we can fully see what you'd like to achieve.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" 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/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
