[vwnc] Glorp problem

Dear Smalltalkers,

I'm working on a new version of GIPA, generating integrity preserving  
associations. This is a code generating tool that supports  
associations. Central in this approach is that associations between  
domain objects should always be bidirectional.

As an addendum I defined a Gipa-glorp integration. This addendum is a  
single class that generates from a (XML) Gipa definition a  
Glorp.DescriptionSystem. The first try was with a OneToMany  
association. This workes fine. The whole combination of Gipa and Glorp  
is specified in 23 lines of mostly code completed XML. It delivers 330  
lines of good functioning and tested Smalltalk code. Using this in  
combination with UnitOfWork results in a system that works very  
intuitive. You just have to load a single object from the database,  
and based on the interactions with this object the system loads in a  
lazy way other objects. Interacting with those objects results again  
in a lazy way, in loading other objects. Only used objects are actualy  
loaded.
In the end committing the unitOfWork results in the creation, updating  
and deletion of manipulated objects.

Now I'm trying the OneToOne Gipa association. I try two variants:  
mutual associations (if I have a partner, I am his/her partner), and  
non-mutual associations (as a person I have a single address, and on  
that address only I will live, but it is possible that somebody  
doesn't have an address and that nobody lives on a certain address).

This is quite an introduction to my actual question.
When I create the database (in testCreateDB of  
Gipa_Glorp_Test_Partner) I get the following error message:
        Cannot automatically determine join, too many constraints found.
This is in "joinFor: aMapping toTables: toTables fromConstraints:  
fromConstraints toConstraints: toConstraints" of Glorp.DescriptorSystem.
I also tried the glorp mapping without the foreign key in Address.  
Then it worked OK.  But this is not what I want, because I cannot  
start with a single address and lad from there the related person.

Does anyone have an idea whether this problem is intentional or just  
an error.
Does anyone has ideas how to change this behavior.

In order to reproduce the problem I will show the Gipa model and I  
will add as zipped file both the generated code and the SUnit TestCase  
subclass Gipa_Glorp_Test_Partner. I don't send the Gipa and Glorp  
generators.
I use a postgres database. I use the following class diagram.


This is formalized in the following Gipa definition. (This can also be  
found as a class method in Gipa_Glorp_Test_Partner.

<?xml version="1.0" encoding="UTF-8"?>
<GipaModel xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="/Users/reinier/werk/gipa/def/GIPAModel-
v1-3.xsd"
        creation="2009-03-30T22:16:00.00" package="Gipa-generated oneToMany"  
namespace="GIPA">
        <gipaClass className="Department" superType="GIPA.Gipa_Glorp_Object">
                <field name="name" type="xs:string" length="255" use="required"/>
                <field name="id" use="primary" type="xs:long" serial="true"/>
        </gipaClass>
        <gipaClass className="Person" superType="GIPA.Gipa_Glorp_Object">
                <field name="name" type="xs:string" length="255" use="required"/>
                <field name="id" use="primary" type="xs:long" serial="true"/>
        </gipaClass>
        <gipaClass className="Address" superType="GIPA.Gipa_Glorp_Object">
                <field name="name" type="xs:string" length="255" use="required"/>
                <field name="id" use="primary" type="xs:long" serial="true"/>
        </gipaClass>
        <gipaAssociation name="sub" definition="OneToMany">
                <role role="one">
                        <param name="single" value="member"/>
                        <param name="key" value="name"/>
                        <param name="create" value="true"/>
                        <class className="Department"/>
                </role>
                <role role="many">
                        <param name="single" value="department"/>
                        <class className="Person"/>
                </role>
        </gipaAssociation>
        <gipaAssociation name="partner" definition="OneToOne">
                <role role="one">
                        <param name="single" value="partner"/>
                        <class className="Person"/>
                </role>
                <role role="other">
                        <param name="single" value="partner"/>
                        <class className="Person"/>
                </role>
        </gipaAssociation>
        <gipaAssociation name="address" definition="OneToOne">
                <role role="one">
                        <param name="single" value="address"/>
                        <class className="Person"/>
                </role>
                <role role="other">
                        <param name="single" value="person"/>
                        <class className="Address"/>
                </role>
        </gipaAssociation>
</GipaModel>

The method to create the database schema is testCreateDB in  
Gipa_Glorp_Test_Partner.
I left out the problematic lines in Gipa_Glorp_Test_PartnerB.
You need to configure your database and create the namespace GIPA.


Reinier van Oosten

tel: 079-3437073
gsm: 0651335993
email: [hidden email]
skype: rvoosten




_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

Note that there is a glorp mailing list [hidden email].

This is a feature. Or rather, it's a convenience feature which is breaking down in your case, but that's because you're doing something unnecessary in the relational world. When you specify a mapping, it needs to know at least the attribute, the type of mapping, the class of object that it's to, and where it goes in the database. For a relationship, where it goes in the database mostly means what the target's table is, whether there's a link table, and what the join(s) are to get the information back. In general, you have to specify all of this.

But much of this information can be guessed or derived. Given the attribute name we can get the class of object it's related to from the classmodel. And for a relationship, given that, we know the two tables involved (if there's no link table). If there are foreign keys defined in the database, then we can see if there's either a foreign key from us to the target, or vice versa. And we can also see if there's a link table defined that has foreign keys to both. And if there's exactly one of either, then we can assume that that's the one we use. So in a remarkable number of cases, you can specify a relationship just as you have here, with just the attribute name and the fact that it's a One-To-One.

Also, the relational model differs from objects in memory in the way relationships are handled. In memory, relationships are inherently unidirectional, and if you want bidirectional relationships automatically maintained you need to do some work (as you are well aware :-). In the database, a foreign key from PERSON to ADDRESS establishes a relationship which can be traversed in either direction equally well. Having a reverse foreign key from ADDRESS to PERSON isn't necessary, and in fact it means the information is duplicated and has to be kept in sync, which relational people frown on as denormalization. So both foreign keys, as you have in this model, aren't necessary. And since you have both, this looks to Glorp as if you have two different relationships between Person and Address. It can't figure out which one to use for the mapping, and it's trying to tell you so.

It should work just fine if you delete one of those foreign keys, and it is still possible to have the bidirectional relationships in memory. It should work fine for both read and write. I have an idea that there are some circumstances under which you might need to mark one of the relationships as read-only from Glorp's point of view, but I can't recall the details right now, and I think for the basic case here it should be fine.

But note that you will also run into this error message if you start having more than one type of relationship to the same object (say a Person with both home and work addresses). In this case it will be necessary to specify the join in the mapping. e.g.
         (aDescriptor newMapping: OneToOneMapping)
                 attributeName: #address;
                 join: (Join
                          from: (myTable fieldNamed: 'home_address_id')
                          to: (myTable fieldNamed: 'id')).

If you do specify this, you can also specify a range of other possibilities, including composite keys, keys that include constant values (see e.g. the Store relationship between packages/bundles and blessings), relationships based on computed values, and so on.


At 06:29 AM 2009-10-11, reinier van oosten wrote:

Dear Smalltalkers,

I'm working on a new version of GIPA, generating integrity preserving 
associations. This is a code generating tool that supports  
associations. Central in this approach is that associations between 
domain objects should always be bidirectional.

As an addendum I defined a Gipa-glorp integration. This addendum is a 
single class that generates from a (XML) Gipa definition a 
Glorp.DescriptionSystem. The first try was with a OneToMany 
association. This workes fine. The whole combination of Gipa and Glorp 
is specified in 23 lines of mostly code completed XML. It delivers 330 
lines of good functioning and tested Smalltalk code. Using this in 
combination with UnitOfWork results in a system that works very 
intuitive. You just have to load a single object from the database, 
and based on the interactions with this object the system loads in a 
lazy way other objects. Interacting with those objects results again 
in a lazy way, in loading other objects. Only used objects are actualy 
loaded.
In the end committing the unitOfWork results in the creation, updating 
and deletion of manipulated objects.

Now I'm trying the OneToOne Gipa association. I try two variants: 
mutual associations (if I have a partner, I am his/her partner), and 
non-mutual associations (as a person I have a single address, and on 
that address only I will live, but it is possible that somebody 
doesn't have an address and that nobody lives on a certain address).

This is quite an introduction to my actual question.
When I create the database (in testCreateDB of 
Gipa_Glorp_Test_Partner) I get the following error message:
        Cannot automatically determine join, too many constraints found.
This is in "joinFor: aMapping toTables: toTables fromConstraints: 
fromConstraints toConstraints: toConstraints" of Glorp.DescriptorSystem.
I also tried the glorp mapping without the foreign key in Address. 
Then it worked OK.  But this is not what I want, because I cannot 
start with a single address and lad from there the related person.

Does anyone have an idea whether this problem is intentional or just 
an error.
Does anyone has ideas how to change this behavior.

In order to reproduce the problem I will show the Gipa model and I 
will add as zipped file both the generated code and the SUnit TestCase 
subclass Gipa_Glorp_Test_Partner. I don't send the Gipa and Glorp 
generators.
I use a postgres database. I use the following class diagram.




This is formalized in the following Gipa definition. (This can also be 
found as a class method in Gipa_Glorp_Test_Partner.

<?xml version="1.0" encoding="UTF-8"?>
<GipaModel xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance"
         xsi:noNamespaceSchemaLocation="/Users/reinier/werk/gipa/def/GIPAModel- v1-3.xsd"
         creation="2009-03-30T22:16:00.00" package="Gipa-generated oneToMany" 
namespace="GIPA">
         <gipaClass className="Department" superType="GIPA.Gipa_Glorp_Object">
                 <field name="name" type="xs:string" length="255" use="required"/>
                 <field name="id" use="primary" type="xs:long" serial="true"/>
         </gipaClass>
         <gipaClass className="Person" superType="GIPA.Gipa_Glorp_Object">
                 <field name="name" type="xs:string" length="255" use="required"/>
                 <field name="id" use="primary" type="xs:long" serial="true"/>
         </gipaClass>
         <gipaClass className="Address" superType="GIPA.Gipa_Glorp_Object">
                 <field name="name" type="xs:string" length="255" use="required"/>
                 <field name="id" use="primary" type="xs:long" serial="true"/>
         </gipaClass>
         <gipaAssociation name="sub" definition="OneToMany">
                 <role role="one">
                          <param name="single" value="member"/>
                          <param name="key" value="name"/>
                          <param name="create" value="true"/>
                          <class className="Department"/>
                 </role>
                 <role role="many">
                          <param name="single" value="department"/>
                          <class className="Person"/>
                 </role>
         </gipaAssociation>
         <gipaAssociation name="partner" definition="OneToOne">
                 <role role="one">
                          <param name="single" value="partner"/>
                          <class className="Person"/>
                 </role>
                 <role role="other">
                          <param name="single" value="partner"/>
                          <class className="Person"/>
                 </role>
         </gipaAssociation>
         <gipaAssociation name="address" definition="OneToOne">
                 <role role="one">
                          <param name="single" value="address"/>
                          <class className="Person"/>
                 </role>
                 <role role="other">
                          <param name="single" value="person"/>
                          <class className="Address"/>
                 </role>
         </gipaAssociation>
</GipaModel>

The method to create the database schema is testCreateDB in 
Gipa_Glorp_Test_Partner.
I left out the problematic lines in Gipa_Glorp_Test_PartnerB.
You need to configure your database and create the namespace GIPA.


Reinier van Oosten

tel: 079-3437073
gsm: 0651335993
email: [hidden email]
skype: rvoosten







_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

Dear Alan,
_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

The join by default is an inner join. Glorp can generate left outer joins, although I'm not quite sure what you're aiming for here.

To retrieve all addresses with the linked person, it's sufficient to do
    SELECT id, name, person_id from address
You know what the person_id is, so joining it to the Person table without getting any additional information doesn't gain you anything. You'll be left with a proxy to the Person object, and when you send it a message, it will do the appropriate select of the information from the Person table.

If you want to retrieve the person at the same time as the address, normally for performance reasons, you can write, in glorp terms
   query := Query read: Address (... where: [ ... something ...]).
   query alsoFetch: [:eachAddress | eachAddress person].
   session execute: query.

That will do the join that reads all the fields from both the address and the person table.  The distinction, which I think is what you're getting at with the join, is that if a particular address is not associated with any person, it will disappear from the table due to the inner join. If you want to bring back addresses and the corresponding person, but the person may not be there, and if so, you still want the address, then you need an outer join. So then you'd do
     query alsoFetch: [:eachAddress | eachAddress person asOuterJoin].

I don't think you want objects stored in multiple tables in this case.

At 04:07 PM 2009-10-11, reinier van oosten wrote:

Dear Alan,

Thank you for your contribution. I'm very happy to discuss this subject with you. I have to admit that I am new to Glorp. In case of my modelling with Gipa I think the combination is very promising.

I completely agree with you that the solution generated is not optimal from a relational point of view. But I try to come as close as possible to the experience I had a number of years ago using Visualworks with ObjectivityDB.

Given the example the ideal situation would be a table Person and a table Address. We could just choose one of them to have a foreign key and the other not. Let's define Person as:

CREATE TABLE person (id integer PRIMARY KEY ,
name varchar(250), address_id integer, FOREIGN KEY (address_id) REFERENCES address ON DELETE SET NULL);
CREATE TABLE address (id integer PRIMARY KEY ,  name varchar(250));

In order to select all persons and to build all Person objects we need the query

SELECT id, name, address FROM person;

In order to select all addresses to build all Address objects, with the information of the linked Person, we would need:

SELECT address.id AS id, address.name AS name, person.id AS person_id from address LEFT JOIN person ON person.address_id = address.id;

In this case, in order to create and update address records I only have to give the id and name values.

As I said I'm new to Glorp. Reading the tutorial by Roger Whitney page 44 on One Object-Multiple tables, I get the feeling that this might be the solution. However I get the feeling this is an inner join.

My question now is, is it true that Join is an inner join? Is there an implementation of Left join?

Given your example of two types of addresses, this would in Gipa be two different associations. Person would have bothe a homeAddress attribute and a workAddress attribute.


Reinier van Oosten

tel: 079-3437073
gsm: 0651335993
email: [hidden email]






On Oct 11, 2009, at 5:36 PM, Alan Knight wrote:

Note that there is a glorp mailing list [hidden email].

This is a feature. Or rather, it's a convenience feature which is breaking down in your case, but that's because you're doing something unnecessary in the relational world. When you specify a mapping, it needs to know at least the attribute, the type of mapping, the class of object that it's to, and where it goes in the database. For a relationship, where it goes in the database mostly means what the target's table is, whether there's a link table, and what the join(s) are to get the information back. In general, you have to specify all of this.

But much of this information can be guessed or derived. Given the attribute name we can get the class of object it's related to from the classmodel. And for a relationship, given that, we know the two tables involved (if there's no link table). If there are foreign keys defined in the database, then we can see if there's either a foreign key from us to the target, or vice versa. And we can also see if there's a link table defined that has foreign keys to both. And if there's exactly one of either, then we can assume that that's the one we use. So in a remarkable number of cases, you can specify a relationship just as you have here, with just the attribute name and the fact that it's a One-To-One.

Also, the relational model differs from objects in memory in the way relationships are handled. In memory, relationships are inherently unidirectional, and if you want bidirectional relationships automatically maintained you need to do some work (as you are well aware :-). In the database, a foreign key from PERSON to ADDRESS establishes a relationship which can be traversed in either direction equally well. Having a reverse foreign key from ADDRESS to PERSON isn't necessary, and in fact it means the information is duplicated and has to be kept in sync, which relational people frown on as denormalization. So both foreign keys, as you have in this model, aren't necessary. And since you have both, this looks to Glorp as if you have two different relationships between Person and Address. It can't figure out which one to use for the mapping, and it's trying to tell you so.

It should work just fine if you delete one of those foreign keys, and it is still possible to have the bidirectional relationships in memory. It should work fine for both read and write. I have an idea that there are some circumstances under which you might need to mark one of the relationships as read-only from Glorp's point of view, but I can't recall the details right now, and I think for the basic case here it should be fine.

But note that you will also run into this error message if you start having more than one type of relationship to the same object (say a Person with both home and work addresses). In this case it will be necessary to specify the join in the mapping. e.g.
         (aDescriptor newMapping: OneToOneMapping)
                 attributeName: #address;
                 join: (Join
                          from: (myTable fieldNamed: 'home_address_id')
                          to: (myTable fieldNamed: 'id')).

If you do specify this, you can also specify a range of other possibilities, including composite keys, keys that include constant values (see e.g. the Store relationship between packages/bundles and blessings), relationships based on computed values, and so on.


At 06:29 AM 2009-10-11, reinier van oosten wrote:

Dear Smalltalkers,

I'm working on a new version of GIPA, generating integrity preserving 
associations. This is a code generating tool that supports  
associations. Central in this approach is that associations between 
domain objects should always be bidirectional.

As an addendum I defined a Gipa-glorp integration. This addendum is a 
single class that generates from a (XML) Gipa definition a 
Glorp.DescriptionSystem. The first try was with a OneToMany 
association. This workes fine. The whole combination of Gipa and Glorp 
is specified in 23 lines of mostly code completed XML. It delivers 330 
lines of good functioning and tested Smalltalk code. Using this in 
combination with UnitOfWork results in a system that works very 
intuitive. You just have to load a single object from the database, 
and based on the interactions with this object the system loads in a 
lazy way other objects. Interacting with those objects results again 
in a lazy way, in loading other objects. Only used objects are actualy 
loaded.
In the end committing the unitOfWork results in the creation, updating 
and deletion of manipulated objects.

Now I'm trying the OneToOne Gipa association. I try two variants: 
mutual associations (if I have a partner, I am his/her partner), and 
non-mutual associations (as a person I have a single address, and on 
that address only I will live, but it is possible that somebody 
doesn't have an address and that nobody lives on a certain address).

This is quite an introduction to my actual question.
When I create the database (in testCreateDB of 
Gipa_Glorp_Test_Partner) I get the following error message:
        Cannot automatically determine join, too many constraints found.
This is in "joinFor: aMapping toTables: toTables fromConstraints: 
fromConstraints toConstraints: toConstraints" of Glorp.DescriptorSystem.
I also tried the glorp mapping without the foreign key in Address. 
Then it worked OK.  But this is not what I want, because I cannot 
start with a single address and lad from there the related person.

Does anyone have an idea whether this problem is intentional or just 
an error.
Does anyone has ideas how to change this behavior.

In order to reproduce the problem I will show the Gipa model and I 
will add as zipped file both the generated code and the SUnit TestCase 
subclass Gipa_Glorp_Test_Partner. I don't send the Gipa and Glorp 
generators.
I use a postgres database. I use the following class diagram.




This is formalized in the following Gipa definition. (This can also be 
found as a class method in Gipa_Glorp_Test_Partner.

<?xml version="1.0" encoding="UTF-8"?>
<GipaModel xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance"
         xsi:noNamespaceSchemaLocation="/Users/reinier/werk/gipa/def/GIPAModel- v1-3.xsd"
         creation="2009-03-30T22:16:00.00" package="Gipa-generated oneToMany" 
namespace="GIPA">
         <gipaClass className="Department" superType="GIPA.Gipa_Glorp_Object">
                 <field name="name" type="xs:string" length="255" use="required"/>
                 <field name="id" use="primary" type="xs:long" serial="true"/>
         </gipaClass>
         <gipaClass className="Person" superType="GIPA.Gipa_Glorp_Object">
                 <field name="name" type="xs:string" length="255" use="required"/>
                 <field name="id" use="primary" type="xs:long" serial="true"/>
         </gipaClass>
         <gipaClass className="Address" superType="GIPA.Gipa_Glorp_Object">
                 <field name="name" type="xs:string" length="255" use="required"/>
                 <field name="id" use="primary" type="xs:long" serial="true"/>
         </gipaClass>
         <gipaAssociation name="sub" definition="OneToMany">
                 <role role="one">
                          <param name="single" value="member"/>
                          <param name="key" value="name"/>
                          <param name="create" value="true"/>
                          <class className="Department"/>
                 </role>
                 <role role="many">
                          <param name="single" value="department"/>
                          <class className="Person"/>
                 </role>
         </gipaAssociation>
         <gipaAssociation name="partner" definition="OneToOne">
                 <role role="one">
                          <param name="single" value="partner"/>
                          <class className="Person"/>
                 </role>
                 <role role="other">
                          <param name="single" value="partner"/>
                          <class className="Person"/>
                 </role>
         </gipaAssociation>
         <gipaAssociation name="address" definition="OneToOne">
                 <role role="one">
                          <param name="single" value="address"/>
                          <class className="Person"/>
                 </role>
                 <role role="other">
                          <param name="single" value="person"/>
                          <class className="Address"/>
                 </role>
         </gipaAssociation>
</GipaModel>

The method to create the database schema is testCreateDB in 
Gipa_Glorp_Test_Partner.
I left out the problematic lines in Gipa_Glorp_Test_PartnerB.
You need to configure your database and create the namespace GIPA.


Reinier van Oosten

tel: 079-3437073
gsm: 0651335993
email: [hidden email]
skype: rvoosten







_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

--
Alan Knight [|], Engineering Manager, Cincom Smalltalk
[hidden email]
[hidden email]
http://www.cincom.com/smalltalk

_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc