Thanks for the response. I will read into the docs a little bit more
and let you know what I came up with...
On Thu, Sep 4, 2008 at 11:32 AM, <[EMAIL PROTECTED]> wrote:
>
> 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 [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
-~----------~----~----~----~------~----~------~--~---
