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
-~----------~----~----~----~------~----~------~--~---
