thats still not much of a mess, at least u have 5 tables and not 500.
see, i've never used single table inh, and i'm not sql fan at all -
thats why i made dbcook.sf.net - but maybe it looks like:
entity_table = Table('contacts', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50)),
Column('dept_data', String(50)),
Column('person_info', String(50)),
Column('type', whatevertypeitis, nullable=False)
) #i guess this can be autoloaded too
rela_table = Table('relatable', metadata,
Column('left_id', Integer, ForeignKey('contacs.id')),
Column('right_id', Integer, ForeignKey('contacts.id')),
Column('type', Integer, )
)
class Entity( object):pass
class Dept( Entity): ...
class Person( Entity): ...
class Rela( object): pass
entity_mapper = mapper( Entity, entity_table,
polymorphic_on= entity_table.c.type,
polymorphic_identity= typeidofany )
dept_mapper = mapper( Dept, inherits= entity_mapper,
polymorphic_identity= typeidofdepts )
person_mapper = mapper( Person, inherits= entity_mapper,
polymorphic_identity= typeidofppl )
so far so good. u can check if this reads things properly.
now for your m2m relation...
if u want the items split by rela.type in different properties, i.e.
type=1 is always head, type=3 is always somethingelse, then u can
probably go with implict mapping via secondary table and explicit
secondary join that spells the id2id link + the type==..
i'm not sure how that spells in plain SA, something like:
dept_mapper.add_property( 'head', relation(
Person, secondary_table=rela_table,
secondary_join = and_(
contacttbl.c.id == rela_table.c.left,
contacttbl.c.id == rela_table.c.right,
rela_table.c.type == 1 )
) )
this will leave u with all other rela.type unused/invisible - unless u
make other similar props.
otherwise u may need explicit mapping to get the relation.type...
(assoc.object), i leave that to your exercise.
plz do not expect the above to be THE solution, u may have to fix
mistakes or tweak or even abandon ... read docs on inheritance,
relations (many2many), and related.
svil
On Thursday 04 September 2008 11:57:35 Wim Verhavert wrote:
> Well let me be more concrete, I'll show you part of the mess I'm
> in:
>
> We have persons, called contacts, and departments. For some crazy
> reason the previous designers put them all in one table (called
> 'contacts' can you believe this?!). Now they share almost nothing
> but a name. There all kinds of columns (like address and such) but
> only relate to persons while there are other columns that only
> relate to departments. Now I want to clearly separate the two, but
> by inheriting them from 'entity' I somehow relate the two, as such
> this is actually a minor consern, what is bothering me is that
> departments have a 'head of department' which is a person of
> course. So the guys created a 'relation' table which maps contacts
> with other contacts by using a type indicator. So for example
> contactid 100 (which is actually a department because its typeid in
> the contacts table says so) is related to contact 235 (which is a
> person and thus the head of the department). So in the 'relations'
> table we can find something like:
>
> contactid | relation | contactid
> -----------------------------------------------
> 100 | 1 | 235
>
> Since relation 1 means 'head of ..." we can derive from this that
> contact 235 is head of department 100 (which is also a contact).
>
> I don't know for you guys, but this is a terrible design.
> So what I was looking after was to do something like this in my
> python code:
>
> class Person(object):
> def __init__(self, name):
> self.name = name
>
> class Department(object):
> def __init__(self, name, head):
> self.name = name
> self.head = head # an instance of a person
>
> Is there a way I can setup the mappers of SQLA to do this. I would
> understand if it can't, because this is a terrible design of
> course, but I'm sure you all have seen some terrible things in your
> career...
>
> Many thanks!
>
> --
> Wim
>
> On Thu, Sep 4, 2008 at 11:04 AM, <[EMAIL PROTECTED]> wrote:
> > AFAIK for the single inh. your object hierarchy makes no
> > difference - it all goes in one table, regardless if it is one
> > class of whole tree of not-realy-related-ones. what is the python
> > side of things is up to you. why is that "entity" base class
> > bothering you? declare it just inheriting object without
> > attributes, but dont use it..
> > or maybe i dont understand what u want.. wait for other replies.
> >
> > On Thursday 04 September 2008 11:22:08 Wim Verhavert wrote:
> >> Thanks for the quick answers. But I'm left with some side-effect
> >> I'm a little bit struggling with: in order for this to work
> >> myObject and myOtherObject need to inherit some base class let's
> >> say 'entity'. Now the ones who created the database clearly
> >> didn't had much experience with databases (damn MS Access for
> >> making databases that accessible!) because they simply put
> >> several unrelated objects into one table. The objects share some
> >> properties, for example 'name', but they also have other
> >> properties specific for the object (so column 'x' only has
> >> relevance for type 1 and column 'y' only for type 2 and so on).
> >> Don't tell me this is wrong, I know and I want to correct this,
> >> but I simply can't at this stage since to many apps out there
> >> depend on this structure. So actually I want myObject and
> >> myOtherObject to inherit only from 'object'. Can this be done?
> >>
> >> On Thu, Sep 4, 2008 at 9:01 AM, <[EMAIL PROTECTED]> wrote:
> >> > see (single) table inheritance and the rest,
> >> > http://www.sqlalchemy.org/docs/05/mappers.html#advdatamapping_
> >> >map per_inheritance
> >> >
> >> > On Wednesday 03 September 2008 21:59:28
> >> > [EMAIL PROTECTED]
> >
> > wrote:
> >> >> Hi all,
> >> >>
> >> >> I just started playing with SQLAlchemy today (after several
> >> >> years of plain SQL experience) and I must say I'm impressed.
> >> >> I'm reading my way through the docs now, but there is one
> >> >> thing I can't seem to find. Let me briefly explain the
> >> >> situation.
> >> >>
> >> >> I was given the task of rewriting a database which is in use
> >> >> for many years now. And since many applications depend on its
> >> >> current structure I can only make small changes at the time.
> >> >> My plan is to rewrite all the attached applications but this
> >> >> time abstracting the app's logic from the data-structure
> >> >> itself. I think that SQLAlchemy will allow me to achieve this
> >> >> task by building a library of POPO's and some mappers to the
> >> >> data-structure. In that way I can rework the database and
> >> >> only have to adapt the mappers to keep my app's running. So I
> >> >> started that and immediately stumbled upon a 'common'
> >> >> situation which I don't now how to solve in SQLA. So here
> >> >> goes:
> >> >>
> >> >> I have 1 table (mytable) which is structured somewhat like
> >> >> this: id = int (primary key)
> >> >> name = varchar()
> >> >> type = int
> >> >>
> >> >> Now all rows with a type, say 1 'constitute' a MyObject. And
> >> >> rows with type say 2 are MyOtherObject instances, and so on.
> >> >> So in my applications I want to create a class like this:
> >> >>
> >> >> class MyObject(object):
> >> >> def __init__(self, name):
> >> >> self.name = name
> >> >>
> >> >> Then I need to map this to the database. So I write a mapper
> >> >> like this:
> >> >> myobject_table = select([mytable], mytable.c.type ==
> >> >> 1).alias('somealias') (not sure if this is entirely correct.
> >> >> I'm writing this post at home and don't have access to my
> >> >> code at the office. But this is not the point so...)
> >> >> mapper(MyObject, myobject_table)
> >> >>
> >> >> So far all ok, but now when I insert new instances of type
> >> >> MyObject, the type column is not filled with value 1. The
> >> >> instance is inserted ok except for this 'hidden' column. I
> >> >> don't want to add this column to my MyObject class since I
> >> >> foresee that the structure of my DB will change and then
> >> >> there will be no more value for the type column. The column
> >> >> 'type' belongs to the internals of my data-structure and
> >> >> shouldn't be visible in my app's. In the new structure there
> >> >> will be a table just for MyObject instances.
> >> >>
> >> >> Does any guru out there knows how to solve this rather
> >> >> 'common' problem?
> >> >>
> >> >> Many thanks for reading this post!
> >> >>
> >> >> --
> >> >> Wim
>
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~----------~----~----~----~------~----~------~--~---
