In the following link(1) bellow, Massimo explains how validation
mechanism works:
....
db.table.field2.requires=IS_NOT_IN_DB(db
(db.table.field1==request.vars.field1),db.table.field2)
[1]
http://groups.google.com/group/web2py/browse_thread/thread/e5ddcfc6ca019568/a143eb919c92735b?lnk=gst&q=unique+multiple+columns#a143eb919c92735b
I didn't noticed that the correct field to validate is "n4"
db.account_attributes.n4requires=IS_IN_DB(
db, 'account_attributes.account_attribute', '%(n4)s'
)
Instead "account_attribute" field:
db.account_attributes.account_attribbute(
db, 'account_attributes.account_attribute', '%(n4)s'
)
After edit this part of code, the validation of form level worked
correctly.
It would be good in Web2py book an exemple that shows a case to
validate uniqueness constraint of multiple fields.
[2] http://www.web2py.com/book/default/chapter/07#Database-Validators
Until to the next challenge! ;-)
Greetings.
Lenkaster
On 26 maio, 16:27, Lenkaster <[email protected]> wrote:
> 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/caaf68de3f...
>
> -http://groups.google.com/group/web2py/browse_thread/thread/e5ddcfc6ca...
>
> 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?
>
> GreetingsLenkaster
