Hi,
I have some problems to validate in the form layer when a table has
uniqueness constraint with more than one column. The exemple following
explains the problem.
I have a table where I need to store accounting numbers. Ex.:
+==+==+==+==+
|N1 |N2 |N3 |N4 |
+==+==+==+==+
|1 |2 |3 |40 |
|1 |2 |3 |41 |
....
In the model
------------------
(with postgres or mysql)
::
db.define_table(
'account_attributes',
Field('n1', length=10, required=True, notnull=True),
Field('n2', length=30, required=True, notnull=True),
Field('n3', length=40, required=True, notnull=True),
Field('n4', length=100, required=True, notnull=True),
Field(
'account_attribute',
length=255,
notnull=True,
unique=True,
readable=False,
writable=False,
compute=lambda r: ('%s.%s.%s.%s' % (r.n1, r.n2, r.n3,
r.n4)).upper(),
),
format="%(account_attribute)s",
)
Validations in the form layer are:
::
db.account_attributes.n1.requires=IS_NOT_EMPTY()
db.account_attributes.n2.requires=IS_NOT_EMPTY()
db.account_attributes.n3.requires=IS_NOT_EMPTY()
db.account_attributes.n4.requires=IS_NOT_EMPTY()
db.account_attributes.account_attribute.requires=IS_IN_DB(
db, 'account_attributes.account_attribute', '%(n4)s'
)
In controller and view I use the Crud way, but if I try to insert the
same values without use admin interface, the application is broken
with database error:
::
IntegrityError:
ERRO: duplicar valor da chave viola a restrição de unicidade
"account_attributes_account_attribute_key"
DETAIL: Chave (account_attribute)=(1.2.3.40) já existe.
Web2Py book shows how to validate single column, and in this forum I
see solutions to validate with a column that computes the values of
other four columns as above mentioned.
I try too validates this way:
::
account_attribute_form='.'.join(
[request.vars.n1, request.vars.n2, request.vars.n3,
request.vars.n4]
).upper()
db.account_attributes.account_attribute.requires=[
IS_NOT_EMPTY(),
IS_NOT_IN_DB(
db(db.account_attributes.account_attribute==account_attribute_form),
db.account_attributes.account_attribute
)
]
Source consulted:
- http://www.web2py.com/book/default/chapter/07#Database-Validators
-
http://groups.google.com/group/web2py/browse_thread/thread/caaf68de3fbc0baf/5021f1870c9b3f15?lnk=gst&q=unique+multiple+columns#5021f1870c9b3f15
-
http://groups.google.com/group/web2py/browse_thread/thread/e5ddcfc6ca019568/a143eb919c92735b?lnk=gst&q=unique+multiple+columns#a143eb919c92735b
Obs:
In this case it is important to keep uniqueness constraint in database
layer. So I would like see your sugestions and opinions about that,
and where is my mistake?
Greetings
Lenkaster
