Re: [Modeling-users] Re: attaching information to a relation

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

On Jeudi, ao=FB 7, 2003, at 23:37, Sebastien Bigaret wrote:

>   I do not have much time by now, so this is a quick answer:
>
> Mario Ruggier <ma...@ru...> wrote:
>>> I have a question here: is it a good idea to re-use such
>>> assoc tables to relate in a many-2-many way any arbitrary
>>> pair of tables? An m2m relationship with a type seems to
>>> me a frequently occurring situation, and it seems like a lot
>>> of overhead to declare a dedicated assoc table per relation.
>>> As an example, consider:
>>>
> [model snipped]
>>> This, the same assoc table is used for the m2m relations
>>> between User<>Address and User<>Organization.
>>> Is this a good way?
>>
>> I should have played with the above case a little more, as
>> it will not work like this -- because this will attempt to create
>> two relations on the ManyToMany_Type (now TypedAssoc) table,
>> that are both named 'user'. The mdl_validate error given is:
>>
>> ValueError: Attempt to insert a relationship in entity TypedAssoc but=20=

>> a key
>> named 'user' is already registered.
>
> In fact this is not directly mdl_validate_model raising, this happens
> when loading a model. These constraints about namespaces are strictly
> enforced by Model (while ModelValidation, implementing the logic for
> mdl_validate, starts its verificatins after the model is loaded).

OK, thanks for the explanation.

>> However, if i change the name of the relation on the Assoc table
>> (e.g. following a convention such as source_target), then the
>> db is created ok. Thus the assocs will change to:
>>
>>    Association('User','TypedAssoc',
>>      relations=3D['organizations','user_organization'],
>>      delete=3D['cascade','nullify'],
>>      keys=3D['FK_Organization_id','id'],
>>    ),
>>    Association('Organization','TypedAssoc',
>>      relations=3D['users','organization_user'],
>>      delete=3D['deny','nullify'],
>>      keys=3D['FK_User_id','id'],
>>    ),
>>    Association('User','TypedAssoc',
>>      relations=3D['addresses','user_address'],
>>      delete=3D['cascade','nullify'],
>>      keys=3D['FK_Address_id','id']
>>    ),
>>    Association('Address','TypedAssoc',
>>      relations=3D['users','address_user'],
>>      delete=3D['deny','nullify'],
>>      keys=3D['FK_User_id','id']
>>    ),
>
> I don't have the time to examine your model --seems strange, and the
> delete rules too.

If you get time, ...  ;)
For the delete rules, my intention is to state that :
a) when deleting an assoc itself, just nullify the 2 relationships
between it and the User and Address rows.
b) when deleting a User, i also want to delete any assocs...
c) i only want to be able to delete an Address if no User is
using it... (several Users may be using the same address for
different reasons).

>    In fact my previous post was about something like this:
>
>      Association('TypedAssoc','User',
>        relations=3D['user','typed_assocs'],
>        delete=3D['nullify','cascade'],
>        keys=3D['FK_User_id','id']
>      ),
>      Association('TypedAssoc','Address',
>        relations=3D['address','typed_assocs'],
>        delete=3D['nullify','cascade'],
>        keys=3D['FK_Address_id','id']
>
>    i.e.  User <----->> TypedAssoc <<-----> Address

Hmmn, it seems I have switched the logic around.
In this way the "user" and "address" relations show up as constraints
on the TypedAssoc table.

But, the problem with this is that if we re-use TypedAssoc
also for a similar association between User and Organization
(using this naming strategy) then there will be the ValueError
mentioned above. Hence, using a  <source4target> naming
scheme for these relations, and adding the ones for organization,
we end up with a TypedAssoc definition of:

CREATE TABLE TypedAssoc (
   FK_USER_ID INTEGER ,
   FK_ORGANIZATION_ID INTEGER ,
   TYPE VARCHAR(30) ,
   ID INTEGER NOT NULL PRIMARY KEY,
   FK_ADDRESS_ID INTEGER ,
   CONSTRAINT address4user FOREIGN KEY (FK_USER_ID) REFERENCES=20
Address(ID),
   CONSTRAINT organization4user FOREIGN KEY (FK_ORGANIZATION_ID)=20
REFERENCES Organization(ID),
   CONSTRAINT user4address FOREIGN KEY (FK_ADDRESS_ID) REFERENCES=20
User(ID),
   CONSTRAINT user4organization FOREIGN KEY (FK_USER_ID) REFERENCES=20
User(ID))

Is this correct? This will rule out re-use of such a table...

Question: why is the order in an association important?
Or, why does switching the assoc around to this:

      Association('User','TypedAssoc',
        relations=3D['typed_assocs','user'],
        delete=3D['cascade','nullify'],
        keys=3D['id','FK_User_id']
      ),

give the following error:

ValueError: Association(User,TypedAssoc): specifies a destination=20
attribute FK_User_id for entity TypedAssoc but this entity already has=20=

a PK named id


>> However, I am surprised that in the schema dump for the
>> created sqlite database, none of these relation names
>> (user_address, etc) show up! How does he know about
>> them when re-creating the db from the schema dump
>> (which he seems to do just fine) ?
>
> The relations do not appear in the sql dump of tables --they do not
> exist, all the informations about the relationships is stored within
> foreign keys. However, when the databases support it, the framework=20
> adds
> integrity constraints to the db schema, whose names are the =
relations's
> name: (from model StoreEmployees)
>
>> mdl_generate_DB_schema.py -A -c=20
>> StoreEmployees/pymodel_StoreEmployees.py
> [...]
> ALTER TABLE ADDRESS ADD CONSTRAINT toEmployee
>   FOREIGN KEY (FK_EMPLOYEE_ID)     ^^^^^^^^^^
>   REFERENCES EMPLOYEE(ID)
>   INITIALLY DEFERRED
> [...]

OK, except that for SQLite, there's no support for ALTER ;)

> However the framework does not make any use of these constraints=20
> --since
> it already respects them at the object level. They are just put in the
> db by mdl_generated_DB_schema's '-F' option: "create foreign key
> constraints", this can be useful e.g. if you want to interact w/ the =
db
> with a raw sql adaptor without worrying about the integrity of your=20
> data
> since these constraints are also declared and enforced by the=20
> db-server.

Very good, thanks for the explanation.

mario