Re: [Modeling-users] Re: Implementing inheritance through vertical mapping

[Sebastien B. <sbi...@us...>]

        Hi,

Federico Heinz <fh...@vi...> wrote:
> On Thu, 2003-09-25 at 19:18, Sebastien Bigaret wrote:
> > Not at all! For postgresql e.g., we use dedicated sequences, one for
> > each inheritance tree;
>=20
> Trying to recall why we didn't go for this approach, I vagely remember
> that we discarded it in search of database independence... (the
> exploration of PostgreSQL's inheritance is just meant as a temporary
> hack, DBMS independence is important to us). But we might reconsider
> this.

If you want DBMS independence, I must be even more explicit about it then.

  The framework needs to be in control of the mechanism generating the
  primary keys --it needs to know the PK in advance so that
  relationships (FK) and other changes can be changed in a single
  roundtrip to the database.

  For postgres and oracle we use sequences; with mysql and sqlite we use
a dedicated table, with one column 'id' and one row containing the value
for the next available pk. All are named after all the root entities in
the model: PK_SEQ_<rootEntityName>. When a database is migrated, this
sequence/table can be rebuild with max(pk)+1 for all tables of the root
entity and its subentities.

  (I should probably add a little note about this in the User's Guide,
  after all the python code is commented, but not the db schema)

> > Well, since the siblings get different ids, resolving FKs "simply" means
> > deep-fetching the inheritance tree to find the related row.
>=20
> I checked the code... and it looks suspiciously simple. You're hiding
> something from me! :-) I guess there's some cool magic hidden in there,
> my guess is that objectsForSourceGlobalID has some clever tricks up its
> sleeve. It's late now, I'll look into it tomorrow. Right now, my
> sleep-deprived brain can't imagine any other ways of doing it than
> trying all tables in sequence until you find one that has the record
> you're looking for, or keeping a table that tracks what table got
> assigned which key, all of which doesn't sound more efficient than
> joins...
>=20
> But things may look clearer tomorrow. Sleep is a marvelous thing, when
> you can get some :-)

Seems like you were not dreaming yet ;) Yes, that's exactly the way this
is done by now: if you have three entities, you'll get three fetch. For
example, for SalesClerk and Executive inheriting from Employee, when a
fault is cleared 3 fetches are done:

SELECT t0.id, t0.last_name,t0.first_name,t0.fk_store_id
        FROM EMPLOYEE t0
        WHERE (t0.id =3D 1);
SELECT t0.id,t0.store_area,t0.last_name,t0.first_name,t0.fk_store_id
        FROM SALES_CLERK t0
        WHERE (t0.id =3D 1);
SELECT t0.id,t0.office_location,t0.last_name,t0.first_name,t0.fk_store_id
        FROM EXECUTIVE t0
        WHERE (t0.id =3D 1);

  We've discussed other details with Ernesto at:
https://sf.net/mailarchive/forum.php?thread_id=3D2563498&forum_id=3D10674


> >     Will this allow a second look at horizontal mapping ? ;)
>=20
> You seem to be advocating it very much... Any particular reason? We've
> been doing the vertical mapping thing with databases that are
> representative of our target, running on slow machines, and the DB never
> was the bottleneck.

I'm not really advocating it, it's just that I never played with it
because I considered a bit slower than horizontal: with 'nb' classes and
subclasses,

  - a fetch needs 'nb' fetches+join --horizontal: nb fetches, single
    table: 1 fetch,

  - an update can need 2 query for vertical, vs 1 for horizontal and
    single.

And that's basically why I never tried it --but my opinion has no real
weight here, remember I'm really not good at sql: that was one of the
motivation for writing the code :)  You're perfectly right when you say
that the db will not be the bottleneck, the time taken by the framework
to build and manage the object graph will be greater anyhow.

  BTW if you have precise SQL examples on how you optimize the fetching
  and updating of objects in a vertical mapping that could come in
  handy!

-- S=E9bastien.