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

Sebastien Bigaret

2004-08-01 10:14:01 UTC

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 *

Entity.defaults['properties'] = [ APrimaryKey('id', doc='PK') ]

_connDict = {'database': 'example', 'host':'localhost',
'user':'postgres','password':''}
model = Model('Example',adaptorName='Postgresql', connDict=_connDict)
model.version='0.1'
model.entities = [
Entity('Person',
properties=[ AString('name', isRequired=1),
AString('documentNumber', width=30),
AForeignKey('fk_idSisDocumentType'), ],
),
Entity('SisDocumentTypes',
properties=[ AString('description',isRequired=1, width=30) ],
),
]
#---
model.associations=[
Association('Person', 'SisDocumentTypes',
relations=['docType', 'persons'],
delete=['nullify', 'deny'],
keys=['fk_idSisDocumentType', 'id']),
]
model.build()

Post by Javier Castro

# Load the model and build the classes

... from Modeling.EditingContext import EditingContext

Post by Javier Castro

from Modeling import Model, ModelSet, dynamic
model=Model.searchModel('Example',verbose=1)
ModelSet.defaultModelSet().addModel(model)
dynamic.build_with_metaclass(model)
from Example import Person, SisDocumentTypes
# create the SisDocumentTypes

... ec=EditingContext()

Post by Javier Castro

s1 = SisDocumentTypes.SisDocumentTypes()
s2 = SisDocumentTypes.SisDocumentTypes()
s3 = 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=# select id,description from SIS_DOCUMENT_TYPES;
id | description
----+-------------
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

Post by Javier Castro

ec=EditingContext()
p=Person.Person()
ec.insert(p)
p.setName('Javier')
p.setDocumentNumber('25.948.547')
# get the appropriate doc.type

... DNI=ec.fetch('SisDocumentTypes', qualifier='description=="DNI"')[0]

Post by Javier Castro

# link them

... DNI.addToPersons(p)

Post by Javier Castro

p.setDocType(DNI)
ec.saveChanges()

Now the db contains::

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

Hopefully this makes sense and answers your question!)

-- Sébastien.

Post by Javier Castro
I has a ask for you about Modeling.
The thing is, i'm accostumed to work with 'system tables' in some ER
models i work...
These system tables serve for this...
Let's say we have a Person, and a Person can have different document
'types'... then , i model this like that..
PERSON(id, name, fk_idSisDocumentType, documentNumber, ....)
SisDocumentTypes(id, description)
(1, 'DNI')
(2, 'CUIT')
(3, 'LE')
...
(1, 'Javier', 1, '25.948.547')
(2, 'Alejandro', 2, '45.456')
(3, 'Jaime', 3, 'AC-1234')
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...
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. :)
model.entities = [ Entity ('SisDocumentTypes',
properties = [ AInteger('id'),
AString('description') ]
]
But, then.... id is not primary key, and i can't do a simple Association
with that relation :(
Please, help me!!
Javier A. Castro
Nexion Software
Italia 1508, 1º Piso, Of. 2
CP 8332 - General Roca - Río Negro
www.nexion.com.ar