modeling-users

[Modeling-users] Implementing inheritance through vertical mapping

[Federico H. <fh...@vi...>]

Modeling does not implement vertical mapping yet, a feature we might[1]
be willing to contribute. However, from the looks of it, the XML schema
definition does not provide the needed information for it: the way it's
structured, it doesn't contain any provisions for telling the system
that a given class takes its data from a series of tables and not just
from one.

This can, of course, be corrected, and instantiating an object from
several tables at once was one of those things EOF did beautifully
(maybe still does). But the XML schema definition must be changed,
hopefully in a backwards-compatible way. Are there any plans about how
these changes could be implemented? We could suggest ways for doing it,
but if the issue has been discussed, we'd rather add our salt to the
discussion than make complete fools of ourselves :-)

	Fede
=20
[1] depending on priorities. We have done similar work in other
contexts, but for now we could choose to use PostgreSQL's inheritance
mechanism to implement the database, until we can spare the time to
create the vertical mapping layer.

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

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

Federico Heinz <fh...@vi...> wrote:
> Modeling does not implement vertical mapping yet, a feature we might[1]
> be willing to contribute. However, from the looks of it, the XML schema
> definition does not provide the needed information for it: the way it's
> structured, it doesn't contain any provisions for telling the system
> that a given class takes its data from a series of tables and not just
> from one.

Agreed.

> This can, of course, be corrected, and instantiating an object from
> several tables at once was one of those things EOF did beautifully
> (maybe still does).

I did not check, but I can't see a reason why it would have been
removed, even now that it's now completely rewritten in Java :)

> But the XML schema definition must be changed,
> hopefully in a backwards-compatible way. Are there any plans about how
> these changes could be implemented? We could suggest ways for doing it,
> but if the issue has been discussed, we'd rather add our salt to the
> discussion than make complete fools of ourselves :-)

I can't remember if it's been discussed here, however this is the way
I think this could be done:

 - models: add support for flattened attributes and
   relationships. Flattened properties are exactly this: take the value
   for a given field from another entity (hence potentially from another
   table). Flattened properties will be declared with a simple path,
   such as: toEmployee.toStore.name

   (BTW, backward-compatibility should, and will, be ensured)

 - make the framework correctly handle those flattened properties. As
   far as I can see, this would only impact the low-level bricks of the
   framework (esp. SQL generation for fetching and saving).

If you have different ideas, techniques, suggestions, etc., please tell;
my point of view is, for sure, greatly influenced by my remembrance of
past EOF-practices ;) and history has already shown that my eyes and
brain sometimes need to be forced to be kept opened (Mario can tell, he
who fought my initial doubts for PyModels!)

> [1] depending on priorities. We have done similar work in other
> contexts, but for now we could choose to use PostgreSQL's inheritance
> mechanism to implement the database, until we can spare the time to
> create the vertical mapping layer.

Could you possibly elaborate on that? I never played with postgresql's
inheritance support, and it's probably something that could be added in
the User's Guide until vertical mapping is supported. If you find some
time to illustrate the mechanisms, I'll be interested in learning how
they behave wrt. the framework.

-- S=E9bastien.

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

[Federico H. <fh...@vi...>]

On Thu, 2003-09-25 at 10:37, Sebastien Bigaret wrote:
> I can't remember if it's been discussed here, however this is the way
> I think this could be done:
>  - models: add support for flattened attributes and
>    relationships. Flattened properties are exactly this: take the value
>    for a given field from another entity (hence potentially from another
>    table). Flattened properties will be declared with a simple path,
>    such as: toEmployee.toStore.name
>    (BTW, backward-compatibility should, and will, be ensured)

OK, so what we're talking about here is that the XML is a description of
the *classes*, and how they map to the database, not a description of
the database itself (i.e. "entities" declared in the XML are "class"
entities, not "table" entities). No problem with that, I'm just trying
to make sure we're talking about the same things. In this way of
handling things, the XML will never reflect inheritance, and all classes
that are part of an inheritance hierarchy will have to specify the
flattening of each attribute. This makes writing the XML by hand a major
pain in the neck, though it probably doesn't matter (at least to us,
we're planning on generating this XML from our own, richer XML dstabase
schema description).

>  - make the framework correctly handle those flattened properties. As
>    far as I can see, this would only impact the low-level bricks of the
>    framework (esp. SQL generation for fetching and saving).

I agree, this should be pretty straightforward, and could be implemented
without breaking anything... people who don't use it wouldn't even
notice it's there.=20

> Could you possibly elaborate on that? I never played with postgresql's
> inheritance support, and it's probably something that could be added in
> the User's Guide until vertical mapping is supported. If you find some
> time to illustrate the mechanisms, I'll be interested in learning how
> they behave wrt. the framework.

Well, I haven't done much work with it personally either, but one of my
pals here has. The story is that you can specify inheritance in a
PostgreSQL database scheme. When you tell PostgreSQL that table Mammal
inherits from Vertebrate, a "SELECT * FROM Mammal" will include all
fields defined in Vertebrate as well. The interesting part is that the
Vertebrate fields are actually stored in their own table... think of it
as vertical mapping implemented in the DBMS. So unique indentifiers are
unique across all Vertebrates, regardless of whether they are Mammals or
not.

I have not tried this yet, but it looks to me that this could be used to
put our application (which uses vertical mapping) in such a state that
it can be used with the current framework.

	Fede

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

[Ernesto R. <er...@si...>]

----- Original Message -----=20
From: "Sebastien Bigaret" <sbi...@us...>
To: "Federico Heinz" <fh...@vi...>
Cc: "Modeling Mailing List" <mod...@li...>
Sent: Thursday, September 25, 2003 3:37 PM
Subject: [Modeling-users] Re: Implementing inheritance through vertical =
mapping
.....
>> [1] depending on priorities. We have done similar work in other
>> contexts, but for now we could choose to use PostgreSQL's inheritance
>> mechanism to implement the database, until we can spare the time to
>> create the vertical mapping layer.

>Could you possibly elaborate on that? I never played with postgresql's
>inheritance support, and it's probably something that could be added in
>the User's Guide until vertical mapping is supported. If you find some
>time to illustrate the mechanisms, I'll be interested in learning how
>they behave wrt. the framework.

Take into account that in PostgreSQL inherited tables have separate =
primary key sets and that, AFAIK, referential integrity rules in base =
tables are not checked. So the only inheritance mechanism implemented is =
that of queries. All other questions are still open and although =
possible solutions have been discussed, nothing has been done for years. =
One of the most important problems seems that an index covers only one =
table, so all DML queries (INSERT, DELETE, UPDATE) have to check the =
indexes for the complete inheritance tree, what, I think, is not =
implemented.

Erny

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

[Federico H. <fh...@vi...>]

On Thu, 2003-09-25 at 15:50, Ernesto Revilla wrote:
> Take into account that in PostgreSQL inherited tables have separate
> primary key sets

I understand this is not true, and this transcript seems to disprove it
(unless I failed to understand your meaning of "separate primary key
sets"):

test=3D# create table foo (id serial primary key, foo text);
NOTICE:  CREATE TABLE will create implicit sequence 'foo_id_seq' for SERIAL=
 column 'foo.id'
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' f=
or table 'foo'
CREATE
test=3D# create table foobar (bar text) inherits (foo);
CREATE
test=3D# insert into foo (foo) values ('hello');
INSERT 981730 1
test=3D# insert into foobar (foo, bar) values ('1,2', '3');
INSERT 981731 1
test=3D# select * from foo;
 id |  foo =20
----+-------
  1 | hello
  2 | 1,2
(2 rows)

test=3D# select * from foobar;
 id | foo | bar=20
----+-----+-----
  2 | 1,2 | 3
(1 row)

>  and that, AFAIK, referential integrity rules in base tables are not
> checked.

It's actually a bit worse that that: they are checked... but they don't
take inheritance into account. This means that if you declare a
relationship to foo, this won't let the foreign key point to a foobar
record:

test=3D# create table bar (id serial primary key, bar int references foo);
NOTICE:  CREATE TABLE will create implicit sequence 'bar_id_seq' for SERIAL=
 column 'bar.id'
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'bar_pkey' f=
or table 'bar'
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check=
(s)
CREATE
test=3D# insert into bar (bar) values (1);
INSERT 981752 1
test=3D# insert into bar (bar) values (2);
ERROR:  <unnamed> referential integrity violation - key referenced from bar=
 not found in foo

Which is ugly, and just may make the whole thing unusable for vertical
mapping, but I'm still looking.

	Fede

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

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

Federico Heinz <fh...@vi...> wrote:
> On Thu, 2003-09-25 at 10:37, Sebastien Bigaret wrote:
> > I can't remember if it's been discussed here, however this is the way
> > I think this could be done:
> >  - models: add support for flattened attributes and
> >    relationships. Flattened properties are exactly this: take the value
> >    for a given field from another entity (hence potentially from another
> >    table). Flattened properties will be declared with a simple path,
> >    such as: toEmployee.toStore.name
> >    (BTW, backward-compatibility should, and will, be ensured)
>=20
> OK, so what we're talking about here is that the XML is a description of
> the *classes*, and how they map to the database, not a description of
> the database itself (i.e. "entities" declared in the XML are "class"
> entities, not "table" entities). No problem with that, I'm just trying
> to make sure we're talking about the same things.=20

The XML-models or the PyModels are Entity-Relationship models, they do
represent how classes map to databases. They are more database-centered
than UML-centered, that's why, in such situations, the E-R diagram will
differ from the class diagram.

>In this way of
> handling things, the XML will never reflect inheritance, and all classes
> that are part of an inheritance hierarchy will have to specify the
> flattening of each attribute.

The XML will not directly reflect the class diagram, at least not in the
same way than UML models, however:

- the inheritance tree is modeled by the 'parent' field in Entity,

- each subclass will have their own entity and table, and these
  sub-entities will have flattened attributes and relationships pointing
  to the superclass'. Here you're right, the inherited attributes and
  relationships should be flattened (in the xml) from the parent to the
  children --but this is also the case for horizontal mapping (where
  each class gets its own table): although in this case there is no
  flattened properties, the real attributes and relationships are also
  copied.

> This makes writing the XML by hand a major
> pain in the neck, though it probably doesn't matter (at least to us,
> we're planning on generating this XML from our own, richer XML dstabase
> schema description).

XML-models have no default value, in fact they are a comprehensive
description of the models, so in general designing them by hand is a
pain --that's why the ZModeler was written, and also why PyModels are a
great win :) Note that in PyModels, properties do not need to be copied
from parent to children, this is done automatically.

  BTW: there is a reason why models requires properties to be copied: a
  string attribute in a parent can have a width of 40, while a child can
  ask a width of 50 for the same field. Since model introspection is
  something the framework does a lot, we do not want to compute
  inherited attributes if they are not overriden: that's why they are
  copied. And since xml-models are comprehensive descriptions of models,
  you also find copy of inherited attributes in entities.

    PyModels takes care of this when loaded, although they also allow
  you to override the definitions of inherited attributes, if needed.
  The best of the two worlds ;)

> >  - make the framework correctly handle those flattened properties. As
> >    far as I can see, this would only impact the low-level bricks of the
> >    framework (esp. SQL generation for fetching and saving).
>=20
> I agree, this should be pretty straightforward, and could be implemented
> without breaking anything... people who don't use it wouldn't even
> notice it's there.=20

Sure.

> > Could you possibly elaborate on that? I never played with postgresql's
> > inheritance support, and it's probably something that could be added in
> > the User's Guide until vertical mapping is supported. If you find some
> > time to illustrate the mechanisms, I'll be interested in learning how
> > they behave wrt. the framework.
>=20
> Well, I haven't done much work with it personally either, but one of my
> pals here has. The story is that you can specify inheritance in a
> PostgreSQL database scheme. When you tell PostgreSQL that table Mammal
> inherits from Vertebrate, a "SELECT * FROM Mammal" will include all
> fields defined in Vertebrate as well. The interesting part is that the
> Vertebrate fields are actually stored in their own table...=20

  You really mean "*non-inherited* fields in Vertebrate are stored in
  their own table", don't you? (my turn to ask to make sure that we're
  talking about the same thing ;)

> think of it
> as vertical mapping implemented in the DBMS. So unique indentifiers are
> unique across all Vertebrates, regardless of whether they are Mammals or
> not.
> I have not tried this yet, but it looks to me that this could be used to
> put our application (which uses vertical mapping) in such a state that
> it can be used with the current framework.

About unique IDs: the framework takes care of assigning a unique ID (per
  inheritance tree) to any object, regardless of where they are
  stored. So, if you create two objects v1(Vertebrate) and m1(Mammal),
  with Mammal inheriting from Vertebrate, it is guaranteed that
  v1.ID!=3Dm1.ID, always --even in horizontal mapping.

After having quickly looked at the postgresql doc. for inheritance, I
can see the following immediate pb. with such an approach: you won't be
able to fetch data from a specific entity alone, because the framework
will not issue the necessary 'select ... from only...'  statement, nor
it will fetch the 'tableoid' system field --so the 'isDeep' flag of
fetch() will have no effect. This could be a problem... or not! Be also
aware that, while pg accepts multiple inheritance, the framework does
not support more than one parent for a given entity.

  I'm not aware of the issues that Ernesto has raised in his recent
  post, but for sure this needs to be checked as well. Maybe related, at
  http://www.postgresql.org/docs/7.3/interactive/ we read:

    << A limitation of the inheritance feature is that indexes
       (including unique constraints) and foreign key constraints only
       apply to single tables, not to their inheritance children. Thus,
       in the above example, specifying that another table's column
       REFERENCES cities(name) would allow the other table to contain
       city names but not capital names. This deficiency will probably
       be fixed in some future release. >>

BTW, if you can disclose, I'll be really interested in hearing the
reasons why you want vertical mapping  --esp. against the additional
overhead each fetch (where tables should be JOINed), and each
insert/update (where two INSERT/UPDATES are needed).

        Regards,

-- S=E9bastien.

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

[Mario R. <ma...@ru...>]

Hi, sorry about the late jump-in, just a few comments:

On Jeudi, sep 25, 2003, at 21:36 Europe/Zurich, Sebastien Bigaret wrote:
> Federico Heinz <fh...@vi...> wrote:
>> On Thu, 2003-09-25 at 10:37, Sebastien Bigaret wrote:

[...]

>> This makes writing the XML by hand a major
>> pain in the neck, though it probably doesn't matter (at least to us,
>> we're planning on generating this XML from our own, richer XML 
>> dstabase
>> schema description).
>
> XML-models have no default value, in fact they are a comprehensive
> description of the models, so in general designing them by hand is a
> pain --that's why the ZModeler was written, and also why PyModels are a
> great win :) Note that in PyModels, properties do not need to be copied
> from parent to children, this is done automatically.

I see the XML just as an exchange format for a model, if you need
to import, generate or export a model definition _programmatically_.
IMHO, to develop your XML by hand is silly, as it is ugly, way too
verbose, and very obscure and un-expressive -- it is very difficult
(for me ;) to visualize a model from its XML definition. So, the XML
should only be manipulated by tools -- in which case it does not
matter whether properties are flattened or not.

However, if one still wants a more human-friendly XML, what's the
problem with either assigning generic default values for xml element
attribs (e.g. parentEntity='',  isReadOnly='0', isAbstract='0', etc, if
not present), or  "inferring" the value of specific attribs from the
parent entity if one is set. Elements are "identified" by they nodename
and by the value of the name attribute. For example, the famous
Employee entity can become (leaving out PK, and Rels for simplicity) :

  <entity name='Employee'>
     <attribute width='50' precision='0' externalType='VARCHAR' 
name='lastName' scale='0' type='string'/>
     <attribute width='20' isRequired='0' precision='0' 
externalType='VARCHAR' name='firstName' scale='0' type='string' />
     <attribute ...  name='fkStoreId' ... />
   </entity>

And, if we now declare SalesClerk, that inherits from Employee, we
could get away with:

  <entity name='SalesClerk' parentEntity='Employee' >
      <attribute width='20' precision='0' externalType='VARCHAR' 
name='storeArea' scale='0' type='string' />
   </entity>

The default values are more "limited" than in PyModels, as they apply
to nodeType[@name]/@attrib, and to nodeType[@name]/* (children).
It is then a matter of pre-processing to generate from the above the
same identical full XML for these 2 entities as in the current
StoreEmp example, thus no runtime code would need to be touched
in any way. The pre-processing could be done also as an XSL
transformation or so, or with any old dom manipulation technique.
Also, no backwards compatibility issues, as if something is specified
explicitly, then it is not affected by this mechanism.

But, I question whether doing all this is worth it... in the end it will
still always be way more cumbersome than PyModels (also in
performance!).

>   BTW: there is a reason why models requires properties to be copied: a
>   string attribute in a parent can have a width of 40, while a child 
> can
>   ask a width of 50 for the same field. Since model introspection is
>   something the framework does a lot, we do not want to compute
>   inherited attributes if they are not overriden: that's why they are
>   copied. And since xml-models are comprehensive descriptions of 
> models,
>   you also find copy of inherited attributes in entities.
>
>     PyModels takes care of this when loaded, although they also allow
>   you to override the definitions of inherited attributes, if needed.
>   The best of the two worlds ;)

Hey, and it can also generate the xml so trivially ;)

mario

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

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

        Hi Mario,

Mario Ruggier <ma...@ru...> wrote:
> Hi, sorry about the late jump-in, just a few comments:

No problem :)

> I see the XML just as an exchange format for a model, if you need
> to import, generate or export a model definition _programmatically_.
> IMHO, to develop your XML by hand is silly, as it is ugly, way too
> verbose, and very obscure and un-expressive -- it is very difficult
> (for me ;) to visualize a model from its XML definition. So, the XML
> should only be manipulated by tools -- in which case it does not
> matter whether properties are flattened or not.

As far as flattened properties are concerned, implementing them will add
new declaration(s) in either the xml-model or the pymodel, in a
completely backward compatible way.

  And yes, now that we have pymodels, we can say that designing a
  xml-model by hand is unappropriate :)

> However, if one still wants a more human-friendly XML, what's the
> problem with either assigning generic default values for xml element
> attribs (e.g. parentEntity=3D'',  isReadOnly=3D'0', isAbstract=3D'0', etc=
, if
> not present), or  "inferring" the value of specific attribs from the
> parent entity if one is set. Elements are "identified" by they nodename
> and by the value of the name attribute. For example, the famous
> Employee entity can become (leaving out PK, and Rels for simplicity) :
[snipped example]
> The default values are more "limited" than in PyModels, as they apply
> to nodeType[@name]/@attrib, and to nodeType[@name]/* (children).
> It is then a matter of pre-processing to generate from the above the
> same identical full XML for these 2 entities as in the current
> StoreEmp example, thus no runtime code would need to be touched
> in any way. The pre-processing could be done also as an XSL
> transformation or so, or with any old dom manipulation technique.
> Also, no backwards compatibility issues, as if something is specified
> explicitly, then it is not affected by this mechanism.
>=20
> But, I question whether doing all this is worth it... in the end it will
> still always be way more cumbersome than PyModels (also in
> performance!).

I really do not feel like going that way; xml-models are a complete dump
on models in memory at runtime, they are advertized as-is, and I have
the strong feeling that this should remain untouched.

  First, because xml has no declared explicit way of specifying
defaults, except those declared in the classes Model, Entity, etc., but
that's not explicitely supported, nor recommended, nor checked, and
changing the defaults in these classes could possibly slightly change
the semantics of some fields in the xml --while on the other hand the
pymodels have an explicit mechanism for notifying changes between
version, that's the model's attribute 'version'. Second, because
introducing such a feature (lighter xml for inheritance), apart from
representing a real work, will probably not be used at all: as you
noted, even with this, all in all they would remain far more cumbersome
than pymodels.

> Hey, and it can also generate the xml so trivially ;)

  One more reason, I'd say ;)

  May I repeat it again, all this does *not* mean that xml-models are
  about to be deprecated. People are using it in various ways, including
  auto-generating them from other xml-sources. It just means that we
  won't probably change the way it is now: an explicit, comprehensive
  serialization of models used at runtime.


-- S=E9bastien.

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

[Yannick G. <ygi...@yg...>]

=2D----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On September 27, 2003 05:37, Mario Ruggier wrote:
> I see the XML just as an exchange format for a model, if you need
> to import, generate or export a model definition _programmatically_.
> IMHO, to develop your XML by hand is silly, as it is ugly, way too
> verbose, and very obscure and un-expressive -- it is very difficult
> (for me ;) to visualize a model from its XML definition. So, the XML
> should only be manipulated by tools -- in which case it does not
> matter whether properties are flattened or not.

As I said some time ago, we use Datadesigner [1] to make our XML model and
than we pass it to a DOM transformation tool to have a Modeling
friendly document.

XML is really easy to morph !

[1] : http://www.danny.cz/datadesigner.en.html

=2D --=20
Yannick Gingras
Coder for OBB : Observingly Blighted B.th.u.
http://OpenBeatBox.org

=2D----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQE/dYmKrhy5Fqn/MRARAl4LAJ9votpKlqT4RO60sXV3tuy9Gh+kdQCfWz8p
eD/mKUkOth7p9SaXppCvAd8=3D
=3DFYDg
=2D----END PGP SIGNATURE-----

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

[SoaF-BSD <so...@la...>]

On Saturday 27 September 2003 14:58, Yannick Gingras wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On September 27, 2003 05:37, Mario Ruggier wrote:
> > I see the XML just as an exchange format for a model, if you need
> > to import, generate or export a model definition _programmatically_.
> > IMHO, to develop your XML by hand is silly, as it is ugly, way too
> > verbose, and very obscure and un-expressive -- it is very difficult
> > (for me ;) to visualize a model from its XML definition. So, the XML
> > should only be manipulated by tools -- in which case it does not
> > matter whether properties are flattened or not.
>
> As I said some time ago, we use Datadesigner [1] to make our XML model and
> than we pass it to a DOM transformation tool to have a Modeling
> friendly document.

Perhaps you can provide this tool ? add this to the CVS .. this could be 
great. no ? 


Bye Bye 

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

[Yannick G. <yan...@sa...>]

=2D----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On September 29, 2003 06:20 pm, SoaF-BSD wrote:
> > As I said some time ago, we use Datadesigner [1] to make our XML model
> > and than we pass it to a DOM transformation tool to have a Modeling
> > friendly document.
>
> Perhaps you can provide this tool ? add this to the CVS .. this could be
> great. no ?

Sure !

I already posted it some time ago but it didn't seems to generate a
lot of interests.

Since it's more a convenient internal tool for us than a generic model
converter it's a bit tainted by our usage of the framework : public
keys are public (we serialize them to cross a XML-RPC bridge), only
to-many relations and their inverse are merged and a few points like
that.  It might be better to swith to a XSLT transform instead.
Anyway, if it can help someone, I'm willing to make it more generic.

#!/usr/bin/python

#   Copyright (C) 2003 Savoir-faire Linux <in...@sa...>
#      by Yannick Gingras <yan...@sa...>

#   This is dd2pm.

#   dd2pm is free software; you can redistribute it and/or modify
#   it under the terms of the GNU General Public License as published by
#   the Free Software Foundation; either version 2 of the License, or
#   (at your option) any later version.

#   dd2pm is distributed in the hope that it will be useful,
#   but WITHOUT ANY WARRANTY; without even the implied warranty of
#   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#   GNU General Public License for more details.

#   You should have received a copy of the GNU General Public License
#   along with Open Beat Box; if not, write to the Free Software
#   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  U=
SA

"""dd2pm.py : Datadesigner to Python Modeling convertion tool. """

from xml.dom.minidom import parseString
from xml.dom.ext     import PrettyPrint
from xml             import xpath
from xml.xpath       import Evaluate
import xml
import sys

DOC_SKEL =3D """<?xml version=3D'1.0' encoding=3D'iso-8859-1'?> <model name=
=3D'' />"""

DEF_DB =3D "MySQL"

DEF_NEEDED_ENTITIES =3D []

DEF_CONNECTION =3D {}

DD_PM_TYPE_MAP =3D {"bool":    "int",
                  "integer": "int",
                  "int":     "int",
                  "varchar": "string",
                  "numeric": "string",
                  "real":    "float",
                  "float":   "float",
                  "time":    "DateTime",
                  "char":    "string",
                  "text":    "string",
                  "date":    "DateTime"}

DD_PM_EXT_TYPE_MAP =3D {"tinyint": "int",
                      "smallint": "int",
                      "decimal": "numeric"}

def getSourceDom(filename):
    return parseString(open(filename).read())


def createDoc():
    return parseString(DOC_SKEL)


class ModelingDDImporter:
    """Convert a Data Designer shema to PyModeling Model"""
    def __init__(self, srcDoc, targetDoc, onlyNeededEnts=3D0):
        """both srcDoc and targetDoc are DOM compliant instances"""
        self.srcDoc =3D srcDoc
        self.destDoc =3D targetDoc
        self.onlyNeededEnts =3D onlyNeededEnts

    def startConvertion(self):
        self.model =3D self.destDoc.getElementsByTagName("model")[0]
        self.project =3D self.srcDoc.getElementsByTagName("project")[0]
        self.addModelDefAttrs()
        self.convPrjAttrs()
        self.extractPrjEntities()
        self.mutateEntities()
        self.extractRelations()
        self.mutateRelations()

    def addModelDefAttrs(self):
        self.model.setAttribute("adaptorName", DEF_DB)
        self.model.setAttribute("connectionDictionary", str(DEF_CONNECTION))

    def convPrjAttrs(self):
        name =3D self.project.getAttribute("name")
       =20
        self.model.setAttribute("name", name)
        self.model.setAttribute("packageName", name)

    def extractPrjEntities(self):
        entitiesNode =3D self.srcDoc.getElementsByTagName("entities")[0]
        self.prjEntities =3D entitiesNode.getElementsByTagName("entity")

    def extractRelations(self):
        relationsNode =3D self.srcDoc.getElementsByTagName("relations")[0]
        self.relations =3D relationsNode.getElementsByTagName("relation")

    def mutateEntities(self):
        for entity in self.prjEntities:
            needed =3D 1
            if self.onlyNeededEnts:
                if entity.getAttribute("name") not in DEF_NEEDED_ENTITIES:
                    needed =3D 0

            if needed:
                newEntity =3D self.destDoc.createElement('entity')
                self.model.appendChild(newEntity)
                self.mutateEntity(entity, newEntity)

    def mutateRelations(self):
        for relation in self.relations:
            needed =3D 1
            if self.onlyNeededEnts:
                parent =3D Evaluate("./parent/text()", relation)[0].nodeVal=
ue
                if parent not in DEF_NEEDED_ENTITIES:
                    needed =3D 0

            if needed:
                newRelation =3D self.destDoc.createElement('relation')
                self.mutateRelation(relation, newRelation)

    def getModelEntityWithName(self, name):
        modelEntities =3D self.model.getElementsByTagName("entity")
       =20
        for modelEntity in modelEntities:
            if modelEntity.getAttribute("name") =3D=3D name:
                return modelEntity
           =20
        return None
       =20
    def mutateEntity(self, sourceEntity, destEntity):
        # mutate names=20
        mapping =3D {"name": ["moduleName", "className", "name"],
                   "pname": ["externalName"]}
        for mapEntry in mapping.items():
            val =3D sourceEntity.getAttribute(mapEntry[0])
            for attrName in mapEntry[1]:
                destEntity.setAttribute(attrName, val)

        # set defaults
        defaults =3D self.makeDefaults(isReadOnly =3D'0',
                                     isAbstract =3D '0',
                                     typeName =3D '',
                                     parentEntity =3D '')
        for attrName, val in defaults.items():
            destEntity.setAttribute(attrName, val)

        # mutate misc attrs
        self.mutateEntAttrs(sourceEntity, destEntity)

    def mutateEntAttrs(self, sourceEntity, destEntity):
        entAttrsNode =3D sourceEntity.getElementsByTagName("attributes")[0]
        entAttrs =3D entAttrsNode.getElementsByTagName("attribute")

        for entAttr in entAttrs:
            newEntAttr =3D self.destDoc.createElement("attribute")
            destEntity.appendChild(newEntAttr)
            self.mutateEntAttr(entAttr, newEntAttr)

    def mutateEntAttr(self, sourceEntAttr, destEntAttr):
        # mutate name
        name =3D sourceEntAttr.getAttribute("name")
        destEntAttr.setAttribute("name", name)
        pname =3D sourceEntAttr.getAttribute("pname")
        destEntAttr.setAttribute("columnName", pname)

        # set defaults
        defaults =3D self.makeDefaults( isClassProperty =3D '1',
                                      width =3D '0',
                                      isRequired =3D '0',
                                      precision =3D '0',
                                      defaultValue =3D 'None',
                                      scale =3D '0',
                                      displayLabel =3D "")
        for attrName, val in defaults.items():
            destEntAttr.setAttribute(attrName, val)

        # mutate real values
        mapping =3D {"type": "externalType",
                   "length": "width"}
        for node in sourceEntAttr.childNodes:
            if node.nodeType =3D=3D node.ELEMENT_NODE:
                tagName =3D node.tagName
                try:
                    value =3D node.firstChild.nodeValue
                except AttributeError:
                    value =3D ""
                self.mutateEntAttrType(tagName,
                                       value,
                                       sourceEntAttr,
                                       destEntAttr)

    def strToBool(self, str):
        if str =3D=3D "true":
            return 1
        elif str =3D=3D "false":
            return 0
        else:
            raise Exception("String '%s' cannot be converted to bool" % str)

    def mutateEntAttrType(self, tagName, value, sourceEntAttr, destEntAttr):
        if tagName =3D=3D "type":
            if DD_PM_EXT_TYPE_MAP.has_key(value):
                value =3D DD_PM_EXT_TYPE_MAP[value]
            destEntAttr.setAttribute("externalType", value)
            destEntAttr.setAttribute("type", DD_PM_TYPE_MAP[value])
        elif tagName =3D=3D "nullable":
            destEntAttr.setAttribute("isRequired",
                                     str(not self.strToBool(value)))
        elif tagName =3D=3D "unique":
            pass
        elif tagName =3D=3D "primarykey":
            if self.strToBool(value):
                newElem =3D self.destDoc.createElement("primaryKey")
                newElem.setAttribute("attributeName",
                                     destEntAttr.getAttribute("name"))
                destEntAttr.parentNode.appendChild(newElem)
                destEntAttr.setAttribute("isRequired", '1')
                destEntAttr.setAttribute("defaultValue", '0')
                destEntAttr.setAttribute("isClassProperty", '1')
        elif tagName =3D=3D "foreignkey":
            if self.strToBool(value):
                destEntAttr.setAttribute("isClassProperty", '0')
        elif tagName =3D=3D "isarray":
            pass
        elif tagName =3D=3D "imported":
            pass
        elif tagName =3D=3D "description":
            pass
        elif tagName =3D=3D "default":
            destEntAttr.setAttribute("defaultValue", value)
        elif tagName =3D=3D "length":
            # tricky, converted to width only if there is no precision
            if sourceEntAttr.getElementsByTagName("decimals"):
                # use precision
                destEntAttr.setAttribute("precision", value)
            else:
                # use width
                destEntAttr.setAttribute("width", value)
        elif tagName =3D=3D "decimals":
            destEntAttr.setAttribute("scale", value)
        else:
            raise Exception("Unknowed node type : %s" % tagName)

    def mutateRelation(self, sourceRelation, destRelation):
        # set defaults
        defaults =3D self.makeDefaults(deleteRule=3D'nullify',
                                     isClassProperty=3D'1',
                                     multiplicityUpperBound=3D'-1',
                                     multiplicityLowerBound=3D'0',
                                     displayLabel=3D'',
                                     joinSemantic=3D'0')
        for attrName, value in defaults.items():
            destRelation.setAttribute(attrName, value)

        type =3D xpath.Evaluate("./type/text()", sourceRelation)[0].nodeVal=
ue
        if type =3D=3D "ident":
            pass # keep the defaults
        elif type =3D=3D "inform":
            # reverse relation of a to-many
            destRelation.setAttribute("multiplicityUpperBound", "1")
            destRelation.setAttribute("multiplicityLowerBound", "0")
        else:
            raise Exception("Can't handle relation type : '%s'" % type)
       =20
        # append relation to master entity
        parentElem =3D sourceRelation.getElementsByTagName("parent")[0]
        fromEntity =3D parentElem.firstChild.nodeValue
        self.getModelEntityWithName(fromEntity).appendChild(destRelation)

        # set the receiver
        childElem =3D sourceRelation.getElementsByTagName("child")[0]
        toEntity =3D childElem.firstChild.nodeValue
        destRelation.setAttribute("destinationEntity", toEntity)

        destRelation.setAttribute("name", sourceRelation.getAttribute("name=
"))

        relAttrsNode =3D sourceRelation.getElementsByTagName("relationattrs=
")[0]
        bindingNode =3D relAttrsNode.getElementsByTagName("attributebind")[=
0]

        fromAttr =3D bindingNode.getAttribute("parent")
        toAttr =3D bindingNode.getAttribute("child")

        joinElem =3D self.destDoc.createElement("join")
        joinElem.setAttribute("sourceAttribute", fromAttr)
        joinElem.setAttribute("destinationAttribute", toAttr)

        destRelation.appendChild(joinElem)       =20

       =20
    def makeDefaults(self, **kwargs):
        return kwargs

if __name__ =3D=3D '__main__':

    if len(sys.argv) not in [3, 4]:
        print """usage : %s [-o] <SOURCE> <DESTINATION>
        where <SOURCE> is the path of the source XML model
        and   <DESTINATION> is where to save the PyModeling XML model

        Options :
        -o : only convert needed entities""" % (sys.argv[0])
        sys.exit(1)

    onlyNeededEnts =3D "-o" in sys.argv
   =20
    srcDoc =3D getSourceDom(sys.argv[-2])
    newDoc =3D createDoc()

    importer =3D ModelingDDImporter(srcDoc, newDoc, onlyNeededEnts)
    importer.startConvertion()

    PrettyPrint(newDoc, stream=3Dopen(sys.argv[-1], "w"))
#  The END !


=2D --=20
Yannick Gingras
Byte Gardener, Savoir-faire Linux inc.
http://www.savoirfairelinux.com/

=2D----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQE/euJzrhy5Fqn/MRARAt/jAJ0eABfJZURbCLulDyX5oFRaR7DQvgCggzaz
mNqOp1fTT8LkjVcH+KEnqIQ=3D
=3Ds43u
=2D----END PGP SIGNATURE-----

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

[Federico H. <fh...@vi...>]

On Thu, 2003-09-25 at 16:36, Sebastien Bigaret wrote:
> The XML-models or the PyModels are Entity-Relationship models, they do
> represent how classes map to databases. They are more database-centered
> than UML-centered, that's why, in such situations, the E-R diagram will
> differ from the class diagram.

Right. What I meant is that the XML constructs a flat view of the tables
making up the object.

>   BTW: there is a reason why models requires properties to be copied: a
>   string attribute in a parent can have a width of 40, while a child can
>   ask a width of 50 for the same field.

I got that from the docs... not sure about how good this may be for the
app. Of course, when each class maps to a different table this is
probably needed, particularly if it's a legacy database...

>   You really mean "*non-inherited* fields in Vertebrate are stored in
>   their own table", don't you? (my turn to ask to make sure that we're
>   talking about the same thing ;)

Ermmm... in my example, Vertebrate is the superclass, so all of its
fields are non-inherited.

I sent an example earlier. I understand that what happens is that all
Vertebrate fields live in the Vertebrates table, and the Mammals table
contains just the non-inherited fields. However, a select on Mammals
will also return inherited fields as if they were there.

This is my understanding of what is going on... I haven't looked at the
innards of PostgreSQL to find out.

>   About unique IDs: the framework takes care of assigning a unique ID (pe=
r
>   inheritance tree) to any object, regardless of where they are
>   stored. So, if you create two objects v1(Vertebrate) and m1(Mammal),
>   with Mammal inheriting from Vertebrate, it is guaranteed that
>   v1.ID!=3Dm1.ID, always --even in horizontal mapping.

So we don't use the automatic unique id generator in the DBMS... Doesn't
this break horribly in a multiple-address-space scenario?

> you won't be
> able to fetch data from a specific entity alone, because the framework
> will not issue the necessary 'select ... from only...'  statement, nor
> it will fetch the 'tableoid' system field --so the 'isDeep' flag of
> fetch() will have no effect. This could be a problem... or not!

Indeed, this is something we'll have to look into.

> Be also
> aware that, while pg accepts multiple inheritance, the framework does
> not support more than one parent for a given entity.

Multiple inheritance is an abomination :-)

> BTW, if you can disclose, I'll be really interested in hearing the
> reasons why you want vertical mapping  --esp. against the additional
> overhead each fetch (where tables should be JOINed), and each
> insert/update (where two INSERT/UPDATES are needed).

Well, there were two important problems without vertical mapping:
      * unique ids for sibling classes is a bitch of a problem when you
        have multiple address spaces
      * we often have relations that are "to instances of any subclass
        of foo", which are pretty easy to implement in vertical mapping,
        but were near to impossible in the framework we're trying to
        break from.
As a matter of fact, I look forward to finding out how you solved the
second problem in Modeling...

	Fede

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

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

Federico Heinz <fh...@vi...> wrote:
> On Thu, 2003-09-25 at 16:36, Sebastien Bigaret wrote:
> > The XML-models or the PyModels are Entity-Relationship models, they do
> > represent how classes map to databases. They are more database-centered
> > than UML-centered, that's why, in such situations, the E-R diagram will
> > differ from the class diagram.
>=20
> Right. What I meant is that the XML constructs a flat view of the tables
> making up the object.

Indeed.

> >   BTW: there is a reason why models requires properties to be copied: a
> >   string attribute in a parent can have a width of 40, while a child can
> >   ask a width of 50 for the same field.
>=20
> I got that from the docs... not sure about how good this may be for the
> app. Of course, when each class maps to a different table this is
> probably needed, particularly if it's a legacy database...

That was the exact reason why this was made possible!

[my silly comment snipped]
> I sent an example earlier. I understand that what happens is that all
> Vertebrate fields live in the Vertebrates table, and the Mammals table
> contains just the non-inherited fields. However, a select on Mammals
> will also return inherited fields as if they were there.
> This is my understanding of what is going on... I haven't looked at the
> innards of PostgreSQL to find out.

Dunno either how postgresql achieves this. BTW and just in case, this
may be the place to recall that the framework automatically and
transparently handles fetching objects on an inheritance tree, whatever
the underlying db schema.

(this is the 'isDeep' parameter of fetch(), see
 http://modeling.sf.net/UserGuide/ec-fetch-inheritance.html)

> >   About unique IDs: the framework takes care of assigning a unique ID (=
per
> >   inheritance tree) to any object, regardless of where they are
> >   stored. So, if you create two objects v1(Vertebrate) and m1(Mammal),
> >   with Mammal inheriting from Vertebrate, it is guaranteed that
> >   v1.ID!=3Dm1.ID, always --even in horizontal mapping.
>=20
> So we don't use the automatic unique id generator in the DBMS... Doesn't
> this break horribly in a multiple-address-space scenario?

Not at all! For postgresql e.g., we use dedicated sequences, one for
each inheritance tree; when generating the db-schema, you'll see this:

    [...]
    CREATE SEQUENCE PK_SEQ_BOOK START 1
    CREATE SEQUENCE PK_SEQ_WRITER START 1
    [...]

All sequences names PK_SEQ_... are used by the pk generation mechanism.

MySQL does not support sequences so we use a different mechanism (a
dedicated table containing only one row, plus specific SQL statements to
safely increment the next PK value).

In any case, it is a strong requirement for the framework's pk-
generation process to be safe in multi-threaded (same adress space) or
in multi-processes (different address spaces) environments.

[...]
> > Be also
> > aware that, while pg accepts multiple inheritance, the framework does
> > not support more than one parent for a given entity.
>=20
> Multiple inheritance is an abomination :-)

I'm glad you said it!

> > BTW, if you can disclose, I'll be really interested in hearing the
> > reasons why you want vertical mapping  --esp. against the additional
> > overhead each fetch (where tables should be JOINed), and each
> > insert/update (where two INSERT/UPDATES are needed).
>=20
> Well, there were two important problems without vertical mapping:
>       * unique ids for sibling classes is a bitch of a problem when you
>         have multiple address spaces

As stated above, the framework does take care of this.

>       * we often have relations that are "to instances of any subclass
>         of foo", which are pretty easy to implement in vertical mapping,
>         but were near to impossible in the framework we're trying to
>         break from.
> As a matter of fact, I look forward to finding out how you solved the
> second problem in Modeling...

Well, since the siblings get different ids, resolving FKs "simply" means
deep-fetching the inheritance tree to find the related row. So, when an
object has a relationship pointing to an entity, it can actually refer
to this entity or any of its sub-entities --with nothing more than a
classic foreign key.  And of course, this is why the framework takes
great care of assigning a different id to the siblings.

  If you're interested in looking at the code, the methods responsible
  for this are:

    - for to-one relationships:
          AccessFaultHandler.completeInitializationOfObject()
          [in module FaultHandler]
=20=20=20=20
    - for to-many relationships:
          DatabaseContext.objectsForSourceGlobalID()
          [in module DatabaseContext]
      triggered by:
          AccessArrayFaultHandler.completeInitializationOfObject()
          [in module FaultHandler]

  And last, willRead() in DatabaseObject (superclass for CustomObject)
  is the one responsible for asking the fault handler to complete the
  initialization of the object.


    Will this allow a second look at horizontal mapping ? ;)

-- S=E9bastien.

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

[Federico H. <fh...@vi...>]

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;

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.

> Well, since the siblings get different ids, resolving FKs "simply" means
> deep-fetching the inheritance tree to find the related row.

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

But things may look clearer tomorrow. Sleep is a marvelous thing, when
you can get some :-)

>     Will this allow a second look at horizontal mapping ? ;)

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.

	Fede

Re: [Modeling-users] Re: Implementing inheritance through verticalmapping

[Federico H. <fh...@vi...>]

On Thu, 2003-09-25 at 17:54, Ernesto Revilla wrote:
> For serial, the trick is that the two tables use the same sequence
> generator, so the problem does not appear.

I stand corrected. As a matter of fact, it seems PostgreSQL implements
inheritance in a way that seems completely backwards (and different from
what I thought was happening). Basically, it seems to be doing
horizontal mapping, with selects on the superclass being mapped to a
series of selects on every (grand)*child. One would think that
inheritance could be implemented in a much smarter way from the inside
of the DBMS...

> All this may not be that important if only modeling is used to
> manipulate data as it may take care of it.

That's right.

Thanks for the clarification!

	Fede

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.

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

[Federico H. <fh...@vi...>]

On Fri, 2003-09-26 at 19:05, Sebastien Bigaret wrote:
>   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.

I know... this can be a bummer. In other instances when I had to solve
this problem, I added to the driver a function that would return the PK
assigned to the last insert (most DBMSs offer this one way or another,
so a function in the driver can be used to unify the API). I would then
assign a temp primary key to any new objects, and use it as a foreign
key. Then, when the final PK is assigned, I'd go back and change the
bogus foreign keys. It gets the job done, and it doesn't rely on
additional tables on mysql and friends. On the other hand, it does
require quite a bit of internal bookkeeping, so it may well not be worth
the trouble.

> 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, [...] 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 :)

Doh! And I was looking up at you for enlightenment! :-)

Anyway, this may be a very good chance to compare horiz- vs
vert-mapping, and see how they fare, don't you think?

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

Indeed. But of course, when you have a few dozen hundred clients doing
queries, things may look different. This is something we will have to
find out sooner or later :-)

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

There's not much to optimize, actually. All you can do is cache the join
sentence needed to fetch a given class, and create it carefully so you
don't fetch unnecessary stuff nor check for redundant conditions, all of
which is pretty straightforward...

	Fede

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

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

        Hi,

  Sorry that I announced the plan for earlier, but I couldn't find the
  time for it. Looks like I never learn from those time constraints :/

> > because I considered a bit slower than horizontal: with 'nb' classes and
> > subclasses, [...] 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 :)
>=20
> Doh! And I was looking up at you for enlightenment! :-)

  Yeah, well, you know, if I was a SQL guru I probably wouldn't have
  need the framework so badly ;)

Okay, back to it: here is the 1st draft of a plan to support vertical
mapping.

Prerequisite (at least for me:)
------------

  * define a sample model, one root entity A, two sub-entities B and
    C, and at least one sub-entity D for B (we need a sub-sub-entity to
    make sure that the support for vertical mapping will support an
    inheritance tree of any depth). Given this sample model, define:

    - the derived db-schema,

    - SQL statements for selecting, inserting, updating and deleting all
      those 4 entities.

    Note: we cannot simply reuse as-is one of the test models
          AuthorBooks or StoreEmployees, since they involve only one
          level of inheritance, at most.

Code
----

This is what come to my mind by now. We need to:

  * have PyModels (and xml, but we can start w/o xml) support flattened
    attributes and relationships. A flattened property is basically a
    path/dotted notation, such as 'toPerson.name' (stored in an
    attribute 'definition' of Attribute and Relationship). In the core,
    both Attribute and FlattenedRelationship are almost ready to store
    this in models at runtime (I say almost, because it's never been
    used so it is possible that bugs remain in them) --see methods
    isFlattened(), definition(). I've a look at them and it seems that
    the xml support is already almost (if not fully) done.

       ...Re-reading this, I realize that these 2 classes need to be
       refactored. The framework will need to know flattened attributes' and
       relationships' properties from the parent (such as a string's
       width, a relationship's cardinality, etc.), and this is not
       supported at all. That's not that bad, both classes need a bit of
       refactor anyway (yes, they'ere rather old, and pretty ugly if you
       look at their code).

    BTW this is how /I/ think the model would be designed:

      - define root entity A,

      - define sub-entity B w/ parent=3DA,

      - in sub-entity B, define a to-one rel. 'toA' to the parent; this
        relationship will join B's PK to A's PK (and will probably not
        be a class property),

      - in sub-entity B, flatten every properties of A
        (def.=3D'toA.<attrName>' or 'toA.<relName>')

    --> this is for a single level of inheritance. Now it's not clear to
        me at all how this should be done when 2+ levels of inheritance
        are involved. Should a sub-entity flatten properties from its
        direct parent only, or should it flatten its parents
        non-flattened properties, then its parent's parent non-flattened
        properties, etc. ? That will probably be clearer when we'll have
        examples on how this is best done at the SQL level.

    We also need to make sure that defining a PyModel w/ vertical
    mapping will be painless. That must not be that difficult, in fact
    it should be sufficient to i. detect that a sub-entity has a
    different externalName() (db-table's name) than its parent,
    ii. automatically append the necessary items in the sub-entity
    (toOne rel. to parent, flattened properties).

  * Have SQLExpression.py detect and generate SQL statements for
    vertical mapping. This is the biggest part of it, in fact, and
    probably not the most trivial --the main problem is in fact that
    SQLExpression's internals are not very well documented, I've a few
    notes on paper that I should probably put somewhere. I don't not
    have them at hand though, still need to look for them --or rewrite
    them.

  * Write tests for selects, inserts, updates and deletes,

  * Release the code!


-- S=E9bastien.

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

[Federico H. <fh...@vi...>]

Sébastien,

On Sat, 2003-10-04 at 11:35, Sebastien Bigaret wrote:
> Prerequisite (at least for me:)
>   * define a sample model, one root entity A, two sub-entities B and
>     C, and at least one sub-entity D for B (we need a sub-sub-entity to
>     make sure that the support for vertical mapping will support an
>     inheritance tree of any depth). Given this sample model, define:
>     - the derived db-schema,
>     - SQL statements for selecting, inserting, updating and deleting all
>       those 4 entities.

Well, I've done these, please find them attached. I extended the
StoreEmployee model to add a special kind of SalesClerk, the
AreaSupervisor who gets a commission on the area's sales. I'm providing
the pymodel, the SQL schema as Modiling creates it today, the vertical
mapping schema, and the examples. As you might expect, there are no big
surprises in any of these...

>   * have PyModels (and xml, but we can start w/o xml) support flattened
>     attributes and relationships.

I think xml the important part would be to get the back-end to accept
dotted-path notation, then pymodel and XML would be just a matter of
them allowing the notation.

>     BTW this is how /I/ think the model would be designed:
>       - define root entity A,
>       - define sub-entity B w/ parent=A,
>       - in sub-entity B, define a to-one rel. 'toA' to the parent; this
>         relationship will join B's PK to A's PK (and will probably not
>         be a class property),
>       - in sub-entity B, flatten every properties of A
>         (def.='toA.<attrName>' or 'toA.<relName>')

I think this relationship should be implicit, i.e. if the user has
already stated that B is a subclass of A, no explicit declaration of the
relationship should be required.

>     --> this is for a single level of inheritance. Now it's not clear to
>         me at all how this should be done when 2+ levels of inheritance
>         are involved. Should a sub-entity flatten properties from its
>         direct parent only, or should it flatten its parents
>         non-flattened properties, then its parent's parent non-flattened
>         properties, etc. ? That will probably be clearer when we'll have
>         examples on how this is best done at the SQL level.

In keeping with the spirit of the current XML schema, it seems to me
that it would be best to flatten every attribute in every class. In such
a case, AreaSupervisor's the store_area attribute would be flattened as
"toSales_Clerk.store_area" and the first_name would be flattened as
"toSales_Clerk.toEmployee.first_name". Actually, if we didn't have
multiple nuisance (sorry: inheritance), we could automatically call this
relationship "super".

>     We also need to make sure that defining a PyModel w/ vertical
>     mapping will be painless. That must not be that difficult, in fact
>     it should be sufficient to i. detect that a sub-entity has a
>     different externalName() (db-table's name) than its parent,
>     ii. automatically append the necessary items in the sub-entity
>     (toOne rel. to parent, flattened properties).

Hmmm... not sure about this one... currently, sub-entities have
different externalName()s than their parents (after all, they are
different tables!). I can't quite make up my mind whether the choice
between vertical and horizontal mapping should be a global option of the
model, or decided on an entity-by-entity basis (thus allowing for both
kinds of mapping to coexist in the same schema).

	Fede

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

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

        Hi,

Federico Heinz <fh...@vi...> wrote:
> On Sat, 2003-10-04 at 11:35, Sebastien Bigaret wrote:
> > Prerequisite (at least for me:)
> >   * define a sample model, one root entity A, two sub-entities B and
> >     C, and at least one sub-entity D for B (we need a sub-sub-entity to
> >     make sure that the support for vertical mapping will support an
> >     inheritance tree of any depth). Given this sample model, define:
> >     - the derived db-schema,
> >     - SQL statements for selecting, inserting, updating and deleting all
> >       those 4 entities.
>=20
> Well, I've done these, please find them attached. I extended the
> StoreEmployee model to add a special kind of SalesClerk, the
> AreaSupervisor who gets a commission on the area's sales. I'm providing
> the pymodel, the SQL schema as Modiling creates it today, the vertical
> mapping schema, and the examples. As you might expect, there are no big
> surprises in any of these...

Thanks a lot!  Unfortunately I probably won't be able to study this in
detail in the next two or three weeks; I'm currently fighting against a
medical problem, nothing serious, but kind of tiring and I won't
probably find the energy to concentrate on that until the surgery... but
if I do I'll drop a line or two here of course ;)

  Sorry for the delay, and again, thanks for the examples.

-- S=E9bastien.