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

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

        Hi,

  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?
>=20
> 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:
>=20
> ValueError: Attempt to insert a relationship in entity TypedAssoc but a k=
ey
> 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).

> 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:
>=20
>    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.

   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

> 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 adds
integrity constraints to the db schema, whose names are the relations's
name: (from model StoreEmployees)

> mdl_generate_DB_schema.py -A -c StoreEmployees/pymodel_StoreEmployees.py
[...]
ALTER TABLE ADDRESS ADD CONSTRAINT toEmployee=20
  FOREIGN KEY (FK_EMPLOYEE_ID)     ^^^^^^^^^^
  REFERENCES EMPLOYEE(ID)
  INITIALLY DEFERRED
[...]

However the framework does not make any use of these constraints --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 data
since these constraints are also declared and enforced by the db-server.

        Cheers,

-- S=E9bastien.