Re: [Modeling-users] Waht about using relations with NOT AUTONUMERIC primary keys?

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

        Hi Javier,

  The good news is: there is no problem to model your schema! Simply
  declare the two entities, Person and SisDocumentTypes, with fields
  'id' being primary keys and 'fk_idSisDocumentType' being a FK, and the
  appropriate relationship between Person and SisDocumentTypes.

The PKs are created, indeed, using a sequence to ensure that they are
unique even in a multi-threaded environment. But the PKs themselves are
*not* sequences, just plain columns, as expected:

    ID INTEGER NOT NULL;

Now, about the fact that you're initializing the SisDocumentTypes, you
have two options:

  1. use the framework to create the SisDocumentTypes, and then link the
     objects on with another (see below)

  2. dump an existing SQL table into the SisDocumentTypes table. If you
     do this, then you'll have to modify the 'pk_seq_sis_document_types'
     sequence (or table, depending on the adaptor you're using) so that
     it contains the max(SIS_DOCUMENT_TYPES.id)+1.

Solution 1. could be something like this:

a. create pymodel_Example.py::

    from Modeling.PyModel import *
=20=20=20=20
    Entity.defaults['properties'] =3D [ APrimaryKey('id', doc=3D'PK') ]
=20=20=20=20
    _connDict =3D {'database': 'example', 'host':'localhost',
                 'user':'postgres','password':''}
    model =3D Model('Example',adaptorName=3D'Postgresql', connDict=3D_connD=
ict)
    model.version=3D'0.1'
    model.entities =3D [
      Entity('Person',
             properties=3D[ AString('name', isRequired=3D1),
                          AString('documentNumber', width=3D30),
                          AForeignKey('fk_idSisDocumentType'), ],
             ),
      Entity('SisDocumentTypes',
             properties=3D[ AString('description',isRequired=3D1, width=3D3=
0) ],
             ),
      ]
    #---
    model.associations=3D[
      Association('Person', 'SisDocumentTypes',
                  relations=3D['docType', 'persons'],
                  delete=3D['nullify', 'deny'],
                  keys=3D['fk_idSisDocumentType', 'id']),
      ]
    model.build()

b. Use it! Create the doctypes first:

    >>> # Load the model and build the classes
    ... from Modeling.EditingContext import EditingContext
    >>> from Modeling import Model, ModelSet, dynamic
    >>> model=3DModel.searchModel('Example',verbose=3D1)
    >>> ModelSet.defaultModelSet().addModel(model)
    >>> dynamic.build_with_metaclass(model)
    >>> from Example import Person, SisDocumentTypes
=20=20=20=20
    >>> # create the SisDocumentTypes
    ... ec=3DEditingContext()
    >>> s1 =3D SisDocumentTypes.SisDocumentTypes()
    >>> s2 =3D SisDocumentTypes.SisDocumentTypes()
    >>> s3 =3D SisDocumentTypes.SisDocumentTypes()
    >>> ec.insert(s1); ec.insert(s2); ec.insert(s3)
    >>> s1.setDescription('DNI')
    >>> s2.setDescription('CUIT')
    >>> s3.setDescription('LE')
    >>> ec.saveChanges()

  At this point the database contains::

    example=3D# select id,description from SIS_DOCUMENT_TYPES;
     id | description=20
    ----+-------------
      1 | DNI
      2 | CUIT
      3 | LE
    (3 rows)

  The ids have been automatically created. There is no guarantee that
  DNI gets the value 1 for its id, however, buity that's probably not a
  problem, or is it? (if it is, then you want the solution 2. explained
  above)

c. Now its possible to create a person and link it to an existing
   doc.type::

    >>> ec=3DEditingContext()
    >>> p=3DPerson.Person()
    >>> ec.insert(p)
    >>> p.setName('Javier')
    >>> p.setDocumentNumber('25.948.547')
    >>> # get the appropriate doc.type
    ... DNI=3Dec.fetch('SisDocumentTypes', qualifier=3D'description=3D=3D"D=
NI"')[0]

    >>> # link them
    ... DNI.addToPersons(p)
    >>> p.setDocType(DNI)
    >>> ec.saveChanges()

  Now the db contains::

    example=3D# select id,name,fk_id_sis_document_type,document_number
    example-# from PERSON;
     id |  name  | fk_id_sis_document_type | document_number=20
    ----+--------+-------------------------+-----------------
      1 | Javier |                       1 |      25.948.547
    (1 row)

  Hopefully this makes sense and answers your question!)

-- S=E9bastien.

Javier Castro <jav...@ne...> wrote:
> Hi:
>=20
>   I has a ask for you about Modeling.
>=20
>   The thing is, i'm accostumed to work with 'system tables' in some ER
> models i work...
>=20
>   These system tables serve for this...=20
>=20
>   Let's say we have a Person, and a Person can have different document
> 'types'... then , i model this like that..
>=20
>   PERSON(id, name, fk_idSisDocumentType, documentNumber, ....)
>=20
>   SisDocumentTypes(id, description)
>=20
>   Where that 'system table' will have data like those:
>=20
>   (1, 'DNI')
>   (2, 'CUIT')
>   (3, 'LE')
>   ...
>=20
> and in person we can have:
>=20
>   (1, 'Javier', 1, '25.948.547')
>   (2, 'Alejandro', 2, '45.456')
>   (3, 'Jaime', 3, 'AC-1234')
>=20
>=20
>=20
> Well, i want to know if there is some way to model this on Modeling,
> cause i saw that each no-abstract entity on Modeling 'must have'
> APrimaryKey...
>=20
> On my example, id on SisDocumentTypes IS A PRIMARY KEY for that
> relation... and IS A FOREIGN KEY for PERSON relation... but in modeling,
> each PrimaryKey on and Entity in a PyModel, is always a SEQUENCE on the
> underlying database? How can i then model that on Modeling? I dont want
> that modeling creates an 'automumeric' key on SisDocumentTypes... cause
> these values are not inserted by the final user of the system, but yes
> by me! That relation is 'initialized' with the allowed values on
> deployment of the database, and then it can't be modified by the final
> users. Final user just can select those values from a "combobox" on
> Person's insert/update form. I hope i'm making me understood, cause
> english is not my native language. :)
>=20
> I though that i could create SisDocumentTypes like that:
>=20
> model.entities =3D [ Entity ('SisDocumentTypes',
>                         properties =3D [ AInteger('id'),
>                                         AString('description') ]
> ]
>=20
>=20
>=20
> But, then.... id is not primary key, and i can't do a simple Association
> with that relation :(
>=20
> Please, help me!!
>=20
> Javier A. Castro
> Nexion Software
> Italia 1508, 1=BA Piso, Of. 2
> CP 8332 - General Roca - R=EDo Negro
>=20
> www.nexion.com.ar