Validate multiple fields

[Timothy Farrell]

It seems that we discussed this a while ago, but I can't find the discussion.

Can we have a validator based on multiple unique fields?

Basically, I would like uniqueness to be defined by multiple fields instead of just one on a validator level.  This would be to simulate a complex primary key.

Thanks,
  tim

-- 
Timothy Farrell <tfar...@swgen.com>
Computer Guy
Statewide General Insurance Agency (www.swgen.com)

[DenesL]

You can probably tailor this to your needs:

db.mytable.field2.requires=IS_EXPR('value==%s' % repr
(request.vars.field1),error_message='oops!')

or the equivalent (and more flexible):

db.mytable.field2.requires=IS_EXPR('True' ),error_message='oops!')
db.mytable.field2.requires.expressions='value==%s' % repr
(request.vars.field1)

[Timothy Farrell]

Help me understand how this works.

"value", in the expression, gets replaced with the value being validated, correct?  If so, then your suggestions check if field1 == field2.  That's not quite what I'm looking for.

What I'm looking for would be something like this:

ARE_NOT_IN_DB(db, 'table', ['field1', 'field2', 'field3], error_message='ACK!')

I think I'll have to code this up real quick.

[DenesL]

Oops, I misunderstood your goal.

Actually I wrote a ROW_NOT_IN_DB validator which did not get Massimo's
approval, but looking back it might have been because I used a bad
example to make the case.
Anyways, just coding the validator was not enough, I had to modify a
few things in gluon too.

On Dec 12, 12:19 pm, Timothy Farrell <tfarr...@swgen.com> wrote:
> Help me understand how this works.
> "value", in the expression, gets replaced with the value being validated, correct? If so, then your suggestions check if field1 == field2. That's not quite what I'm looking for.
> What I'm looking for would be something like this:ARE_NOT_IN_DB(db, 'table', ['field1', 'field2', 'field3], error_message='ACK!')
> I think I'll have to code this up real quick.

> DenesL wrote:You can probably tailor this to your needs: db.mytable.field2.requires=IS_EXPR('value==%s' % repr (request.vars.field1),error_message='oops!') or the equivalent (and more flexible): db.mytable.field2.requires=IS_EXPR('True' ),error_message='oops!') db.mytable.field2.requires.expressions='value==%s' % repr (request.vars.field1)-- Timothy Farrell<tfar...@swgen.com>Computer Guy Statewide General Insurance Agency (www.swgen.com)

[DenesL]

Oops, I misunderstood your goal.

Actually I wrote a ROW_NOT_IN_DB validator which did not get Massimo's
approval, but looking back it might have been because I used a bad
example to make the case.
Anyways, just coding the validator was not enough, I had to modify a
few things in gluon too.


On Dec 12, 12:19 pm, Timothy Farrell <tfarr...@swgen.com> wrote:

> Help me understand how this works.
> "value", in the expression, gets replaced with the value being validated, correct? If so, then your suggestions check if field1 == field2. That's not quite what I'm looking for.
> What I'm looking for would be something like this:ARE_NOT_IN_DB(db, 'table', ['field1', 'field2', 'field3], error_message='ACK!')
> I think I'll have to code this up real quick.

> DenesL wrote:You can probably tailor this to your needs: db.mytable.field2.requires=IS_EXPR('value==%s' % repr (request.vars.field1),error_message='oops!') or the equivalent (and more flexible): db.mytable.field2.requires=IS_EXPR('True' ),error_message='oops!') db.mytable.field2.requires.expressions='value==%s' % repr (request.vars.field1)-- Timothy Farrell<tfar...@swgen.com>Computer Guy Statewide General Insurance Agency (www.swgen.com)

[Timothy Farrell]

Yes, this would require changing some things in gluon.validators and gluon.html.  I'm of the mindset that this sort of validator is very important.  However, I'll have to look into it later.  Could you send me your code if you still have it?

Thanks,
  tim

[mdipierro]

you do not need this because you can already do it.

requires=IS_NOT_IN_DB(db
(db.table.field1==request.vars.field1),'table.field2')

Massimo

> DenesL wrote:Oops, I misunderstood your goal. Actually I wrote a ROW_NOT_IN_DB validator which did not get Massimo's approval, but looking back it might have been because I used a bad example to make the case. Anyways, just coding the validator was not enough, I had to modify a few things in gluon too. On Dec 12, 12:19 pm, Timothy Farrell<tfarr...@swgen.com>wrote:Help me understand how this works. "value", in the expression, gets replaced with the value being validated, correct? If so, then your suggestions check if field1 == field2. That's not quite what I'm looking for. What I'm looking for would be something like this:ARE_NOT_IN_DB(db, 'table', ['field1', 'field2', 'field3], error_message='ACK!') I think I'll have to code this up real quick. DenesL wrote:You can probably tailor this to your needs: db.mytable.field2.requires=IS_EXPR('value==%s' % repr (request.vars.field1),error_message='oops!') or the equivalent (and more flexible): db.mytable.field2.requires=IS_EXPR('True' ),error_message='oops!') db.mytable.field2.requires.expressions='value==%s' % repr (request.vars.field1)-- Timothy Farrell<tfar...@swgen.com>Computer Guy Statewide General Insurance Agency (www.swgen.com)-- Timothy Farrell<tfar...@swgen.com>Computer Guy Statewide General Insurance Agency (www.swgen.com)

[Timothy Farrell]

Yes, that would work if I used normal HTML forms (and I think that was what I was looking for previously), but in my case, what would be request.vars.field1 in your example is really something like simplejson.loads(request.vars.JSONdata)[2]['field1'].  And since that changes on a controller level, I can't put it in as a database validator (which I would like to do).

I could, of course, code a custom validator for each controller, but that gets around the point of having a DAL that can enforce database integrity.

[DenesL]

> Yes, this would require changing some things in gluon.validators and gluon.html.

In my case they were: validators, sql, sqlhtml & compileapp; but that
was back on rev.341

> I'm of the mindset that this sort of validator is very important.

They become important as the need arises.
My need had an alternate solution.

> Could you send me your code if you still have it?

Yup, maybe it will 'sell' better the second time around.

[mdipierro]

I do not understand. Where is your input from?

Massimo

On Dec 12, 2:42 pm, Timothy Farrell <tfarr...@swgen.com> wrote:
> Yes, that would work if I used normal HTML forms (and I think that was what I was looking for previously), but in my case, what would berequest.vars.field1in your example is really something likesimplejson.loads(request.vars.JSONdata)[2]['field1']. And since that changes on a controller level, I can't put it in as a database validator (which I would like to do).

> I could, of course, code a custom validator for each controller, but that gets around the point of having a DAL that can enforce database integrity.

> mdipierro wrote:you do not need this because you can already do it. requires=IS_NOT_IN_DB(db (db.table.field1==request.vars.field1),'table.field2') Massimo On Dec 12, 2:20 pm, Timothy Farrell<tfarr...@swgen.com>wrote:Yes, this would require changing some things in gluon.validators and gluon.html. I'm of the mindset that this sort of validator is very important. However, I'll have to look into it later. Could you send me your code if you still have it? Thanks, tim DenesL wrote:Oops, I misunderstood your goal. Actually I wrote a ROW_NOT_IN_DB validator which did not get Massimo's approval, but looking back it might have been because I used a bad example to make the case. Anyways, just coding the validator was not enough, I had to modify a few things in gluon too. On Dec 12, 12:19 pm, Timothy Farrell<tfarr...@swgen.com>wrote:Help me understand how this works. "value", in the expression, gets replaced with the value being validated, correct? If so, then your suggestions check if field1 == field2. That's not quite what I'm looking for. What I'm looking for would be something like this:ARE_NOT_IN_DB(db, 'table', ['field1', 'field2', 'field3], error_message='ACK!') I think I'll have to code this up real quick. DenesL wrote:You can probably tailor this to your needs: db.mytable.field2.requires=IS_EXPR('value==%s' % repr (request.vars.field1),error_message='oops!') or the equivalent (and more flexible): db.mytable.field2.requires=IS_EXPR('True' ),error_message='oops!') db.mytable.field2.requires.expressions='value==%s' % repr (request.vars.field1)-- Timothy Farrell<tfar...@swgen.com>Computer Guy Statewide General Insurance Agency (www.swgen.com)-- Timothy Farrell<tfar...@swgen.com>Computer Guy Statewide General Insurance Agency (www.swgen.com)-- Timothy Farrell<tfar...@swgen.com>Computer Guy Statewide General Insurance Agency (www.swgen.com)

[Timothy Farrell]

The page in question is sorta like an SQLtable, it displays a full table and allows for updates to each record without branching off to a separate page.  When the user is done making changes the wraps up the changes made and POSTs a JSON object to the web2py controller.

This is a custom built page that uses Javascript to wrap up the data and build the JSON object.  Once in the controller, the controller unpacks the JSON object and sends it to through an SQLFORM.accepts() validator (in a loop since there are multiple records).

So you see, the data being validated does not directly come from requests.vars in a form that I can put in a application-wide validator.

-tim

[mdipierro]

I still do not see why you cannot use IS_NOT_IN_DB(sqlset,....) You
must have access to the variables that you want to validate.

Massimo

On Dec 12, 3:01 pm, Timothy Farrell <tfarr...@swgen.com> wrote:
> The page in question is sorta like an SQLtable, it displays a full table and allows for updates to each record without branching off to a separate page. When the user is done making changes the wraps up the changes made and POSTs a JSON object to the web2py controller.
> This is a custom built page that uses Javascript to wrap up the data and build the JSON object. Once in the controller, the controller unpacks the JSON object and sends it to through an SQLFORM.accepts() validator (in a loop since there are multiple records).
> So you see, the data being validated does not directly come from requests.vars in a form that I can put in a application-wide validator.
> -tim

> mdipierro wrote:I do not understand. Where is your input from? Massimo On Dec 12, 2:42 pm, Timothy Farrell<tfarr...@swgen.com>wrote:Yes, that would work if I used normal HTML forms (and I think that was what I was looking for previously), but in my case, what would berequest.vars.field1in your example is really something likesimplejson.loads(request.vars.JSONdata)[2]['field1']. And since that changes on a controller level, I can't put it in as a database validator (which I would like to do). I could, of course, code a custom validator for each controller, but that gets around the point of having a DAL that can enforce database integrity. mdipierro wrote:you do not need this because you can already do it. requires=IS_NOT_IN_DB(db (db.table.field1==request.vars.field1),'table.field2') Massimo On Dec 12, 2:20 pm, Timothy Farrell<tfarr...@swgen.com>wrote:Yes, this would require changing some things in gluon.validators and gluon.html. I'm of the mindset that this sort of validator is very important. However, I'll have to look into it later. Could you send me your code if you still have it? Thanks, tim DenesL wrote:Oops, I misunderstood your goal. Actually I wrote a ROW_NOT_IN_DB validator which did not get Massimo's approval, but looking back it might have been because I used a bad example to make the case. Anyways, just coding the validator was not enough, I had to modify a few things in gluon too. On Dec 12, 12:19 pm, Timothy Farrell<tfarr...@swgen.com>wrote:Hel p me understand how this works. "value", in the expression, gets replaced with the value being validated, correct? If so, then your suggestions check if field1 == field2. That's not quite what I'm looking for. What I'm looking for would be something like this:ARE_NOT_IN_DB(db, 'table', ['field1', 'field2', 'field3], error_message='ACK!') I think I'll have to code this up real quick. DenesL wrote:You can probably tailor this to your needs: db.mytable.field2.requires=IS_EXPR('value==%s' % repr (request.vars.field1),error_message='oops!') or the equivalent (and more flexible): db.mytable.field2.requires=IS_EXPR('True' ),error_message='oops!') db.mytable.field2.requires.expressions='value==%s' % repr (request.vars.field1)-- Timothy Farrell<tfar...@swgen.com>Computer Guy Statewide General Insurance Agency (www.swgen.com)-- Timothy Farrell<tfar...@swgen.com>Computer Guy Statewide General Insurance Agency (www.swgen.com)-- Timothy Farrell<tfar...@swgen.com>Computer Guy Statewide General Insurance Agency (www.swgen.com)-- Timothy Farrell<tfar...@swgen.com>Computer Guy Statewide General Insurance Agency (www.swgen.com)

[Timothy Farrell]

The difference is that when the validator runs, it has access to global scope variables (request, response, SQLDB), but it does not have access to my controller local variables (which is where the data I'm trying to validate resides).  Currently, validators only validate based on 1 value passed in or based on what it can access on the global scope.  I'm trying to validate based on something that is in neither in the global scope nor is it the value passed it.  It does, however, have a relationship to the value passed in.  The right way to handle this is to pass in the record being validated and the name of the field to validate to the validator so that the validator can handle inter-data relationships like this.

Does that makes sense?

-tim

[DenesL]

On Dec 12, 4:07 pm, mdipierro <mdipie...@cs.depaul.edu> wrote:
> I still do not see why you cannot use IS_NOT_IN_DB(sqlset,....) You
> must have access to the variables that you want to validate.

Correct me if I am wrong but IS_NOT_IN_DB only looks at the value of
one field.
From validators.py:
rows=self.dbset(field=value).select(limitby=(0,1))

What we are thinking is:
[field1value,field2value,field3value,...] as a row is not in the DB.

[mdipierro]

No. validators are associated to one field in the sense that only one
field will report the error.

db.define_table('t',SQLField('f1'),SQLField('f'2'))
db.t.f2.requires=IS_NOT_IN_DB(db(db.t.f1==request.vars.f1),'t.f2')
OR
db.t.f1.requires=IS_NOT_IN_DB(db(db.t.f2==request.vars.f2),'t.f1')

are equivalent and do exactly what you ask. except that the former
associates the error with the f1 value and the second to the f2 value.
They both generate the query

db((db.t.f1==request.vars.f1)&(db.t.f2==request.vars.f2)).count()

Massimo

[DenesL]

Now I am the confused one.

Help me understand using the following example. Given:

db.define_table('voter',
SQLField('lastname'),
SQLField('firstname'),
SQLField('voterid'))

What would the requires statement look like to make sure that no
combination of lastname, firstname, voterid is repeated in this
table?.

[Timothy Farrell]

Massimo,

We have a difference of design philosophy here.  Maybe we'll revisit this later.

-tim

[mdipierro]

db.voter.voterid.requires=IS_NOT_IN_DB(db
((db.voter.lastname==request.vars.lastname)&
(db.voter.firstname==request.vars.firstname)),'voter.voterid'))

[DenesL]

On Dec 15, 2:23 pm, mdipierro <mdipie...@cs.depaul.edu> wrote:
> db.voter.voterid.requires=IS_NOT_IN_DB(db
> ((db.voter.lastname==request.vars.lastname)&
> (db.voter.firstname==request.vars.firstname)),'voter.voterid'))

I did try something like that but I get:

Traceback (most recent call last):
File "D:\web2py\trunk\gluon\restricted.py", line 62, in restricted
exec ccode in environment
File "D:\web2py\trunk\applications\active/models/db.py", line 99, in
<module>
,'voter.voterid')
File "D:\web2py\trunk\gluon\sql.py", line 302, in __getattr__
def __getattr__(self, key): return self[key]
File "D:\web2py\trunk\gluon\sql.py", line 300, in __getitem__
def __getitem__(self, key): return dict.__getitem__(self,str(key))
KeyError: 'voterid'

[DenesL]

Wait.. it works on the home PC...
I will double check.

[DenesL]

Mistyped the requires...
Thank you Massimo.

[billf]

I followed this thread with interest. Strictly speaking, does one
also need to add a unique index to the table, e.g.

db.executesql('CREATE UNIQUE INDEX IF NOT EXISTS person_voterid ON
voter (lastname,firstname,voterid))

I understand that the syntax can vary between databases.

How difficult would it be to support the creation of indices in the
DAL? I think I'll start another thread :-)

[mdipierro]

The problem is that some dbs do not support the "IF IT EXISTS" part.
This means we would need a lot of additional web2py structure to
handle it. It is going to add a lot of complexity and it may not be as
flexible as raw SQL. The question is, it is worth it?

Massimo

[tknack]

Hi Massimo,

Yes, it works ! But when I try to use IS_NOT_IN_DB with IS_IN_DB, I
can't obtain a drop down list, even if I change the order of the
lines. Here's my code :

db.define_table('zonedef',
SQLField('nom'))

db.zonedef.nom.requires=[IS_NOT_EMPTY(error_message='can't be
empty!'),IS_NOT_IN_DB(db,'zonedef.nom')]

db.zonedef.represent=lambda zonedef:
A(zonedef.nom,_href=t2.action('update',zonedef.id))


db.define_table('u_name',
SQLField('nom'),
SQLField('description',length=128))

db.define_table('u',
SQLField('unit_id',db.u_name),
SQLField('zd_id',db.zonedef),
SQLField('nb_u','double'))

db.u.zd_id.requires=IS_IN_DB(db,'zonedef.id','%(nom)s')
db.u.unit_id.requires=IS_IN_DB(db,'u_name.id','%(nom)s : (description)
s')

db.u.zd_id.requires=IS_NOT_IN_DB(db
(db.u.unit_id==request.vars.unit_id),'u.zd_id')

Can you help me to fix it, Massimo ?

[mdipierro]

for now use this. This is a case I never should about before.

class MY_ODD_VALIDATOR(IS_IN_DB):
def __init__(self,*a,**b): IS_IN_DB.__init__(self,*a,**b)
def __call__(self,value):
e=IS_IN_DB.__call__(self,value)
if e[1]: return e
e=IS_NOT_IN_DB(db
(db.u.unit_id==request.vars.unit_id),'u.zd_id')(value)
return e

db.u.zd_id.requires=MY_ODD_VALIDATOR(db,'zonedef.id','%(nom)s')

[tknack]

Massimo,

here's what I wrote in gluon/validator.py :

class IS_NOT_IN_DB_WITH_DROP(IS_IN_DB):

def __init__(self,*a,**b):
IS_IN_DB.__init__(self,*a,**b)
def __call__(self,value):
e=IS_IN_DB.__call__(self,value)
if e[1]:return e

e=IS_NOT_IN_DB(db
(db.u.unit_id==request.vars.unit_id),'u.zd_id')(value)
return e

and in my db.py:

db.u.zd_id.requires=IS_NOT_IN_DB_WITH_DROP(db,'zonedef.id','%(nom)s')

and I've got an error :

File "gluon/validators.py", line 723, in __call__
e=IS_NOT_IN_DB(db
(db.u_implantee.unit_id==request.vars.unit_id),'u_implantee.zd_id')
(value)
NameError: global name 'db' is not defined

It seems it's normal, because code in validator.py may be generic one.

So,

Is it correct to put my validator code in validator.py ?

Maybe you wanted I put it somewhere else ?

Please, forgive my lack of experience

T

[Fran]

On Jan 8, 9:03 am, tknack <tknac...@gmail.com> wrote:
> Is it correct to put my validator code in validator.py ?

I put my custom validators into a separate validators.py in my modules
folder & then import to the model like so:
from applications.myapplication.modules.validators import *

F

[tknack]

Thanks Fran... now I think it's more clear in my head...

again, I post my code with the right names...

here's my applications.sefom.modules.validators.py (yes , my
application name is 'sefom') :


from gluon.validators import *

class IS_NOT_IN_DB_DDOWN_UIM(object):

def __init__(self,*a,**b):
IS_IN_DB.__init__(self,*a,**b)
def __call__(self,value):
e=IS_IN_DB.__call__(self,value)
if e[1]:return e
e=IS_NOT_IN_DB(db

(db.u_implantee.unit_id==request.vars.unit_id),'u_implantee.zd_id')
(value)
return e


here's the code I put in my model db.py:


from applications.sefom.modules.validators import *

db.define_table('zonedef',
SQLField('nom'))

db.zonedef.nom.requires=[IS_NOT_EMPTY(error_message='ne peut être
vide!'),IS_NOT_IN_DB(db,'zonedef.nom')]

db.define_table('u_nommee',

SQLField('nom'),
SQLField('description',length=128))

# table des unités
db.define_table('u_implantee',
SQLField('unit_id',db.u_nommee),
SQLField('zd_id',db.zonedef"),

SQLField('nb_u','double'))

db.u_implantee.zd_id.requires=IS_IN_DB(db,'zonedef.id','%(nom)s')
db.u_implantee.unit_id.requires=IS_IN_DB(db,'u_nommee.id','%(nom)s : %
(description)s')

db.u_implantee.zd_id.requires=IS_NOT_IN_DB_DDOWN_UIM(db,'zonedef.id','%
(nom)s')


and here's the error I get :

File "applications/sefom/modules/validators.py", line 27, in __init__
IS_IN_DB.__init__(self,*a,**b)
TypeError: unbound method __init__() must be called with IS_IN_DB
instance as first argument (got IS_NOT_IN_DB_DDOWN_UIM instance
instead)

I'm using postgreSQL and I created a multi-column constraint on
(zd_id,unit_id) for 'u_implantee' table. So the duet can't be present
twice.

Is there another solution ?

Thanks a lot for your help.

[mdipierro]

should be

class IS_NOT_IN_DB_DDOWN_UIM(IS_IN_DB):

def __init__(self,*a,**b):
IS_IN_DB.__init__(self,*a,**b)
def __call__(self,value):
e=IS_IN_DB.__call__(self,value)
if e[1]:return e
e=IS_NOT_IN_DB(db
(db.u_implantee.unit_id==request.vars.unit_id),'u_implantee.zd_id')
(value)
return e

Massimo

[tknack]

Thanks Massimo, I made a mistake...

But now, how can I solve that error :

File "applications/sefom/modules/validators.py", line 31, in
__call__

e=IS_NOT_IN_DB(db
(db.u_implantee.unit_id==request.vars.unit_id),'u_implantee.zd_id')
(value)

NameError: global name 'db' is not defined

do I import db.py in validators.py ?

T.

[mdipierro]

You should not put the new validator in validators.py. Just put it in
the model that needs it.

Massimo

[tknack]

Thanks a lot Massimo, it works !

Now, I want to translate the error message in French and when I put
it, like the others validators, here :

db.u_implantee.zd_id.requires=IS_NOT_IN_DB_DDOWN_UIM
(db,'zonedef.id','%
(nom)s',error_message=('Cette valeur a deja ete saisie !'))

the error stays in English.....

forgiveness for my first steps

[mdipierro]

class IS_NOT_IN_DB_DDOWN_UIM(IS_IN_DB):
def __init__(self,*a,**b):
IS_IN_DB.__init__(self,*a,**b)
def __call__(self,value):
e=IS_IN_DB.__call__(self,value)
if e[1]:return e
e=IS_NOT_IN_DB(db

(db.u_implantee.unit_id==request.vars.unit_id),'u_implantee.zd_id',error_message=self.error_message)
(value)
return e

and use with

....=IS_NOT_IN_DB_DDOWN_UIM(....,error_message=T("the error message")

[tknack]

Thank you, Massimo, it works fine....

But, if I want to put these codes in the right files, which files
should I use and which imports should I do ?

have you as a project to expand the list of validators or to make the
DAL able to handle multi-column constraints ?

T.

[mdipierro]

If the are app specific I would put them in a model file.

I was not planning to extend the number of built-in validators but you
raised a good issue. I think we need one more or one more parameter
for IS_IN_DB.

[tknack]

On 9 jan, 15:07, mdipierro <mdipie...@cs.depaul.edu> wrote:
> If the are app specific I would put them in a model file.
>
> I was not planning to extend the number of built-in validators but you
> raised a good issue. I think we need one more or one more parameter
> for IS_IN_DB.
>

Yes, I think it's a great idea !

There's one more problem I want to solve, but I don't know where's the
topic for that !

I want to offer a connection for users that allows everyone to see
only pages that are dedicated, and which presents only data that are
associated with a user/password I defined (perhaps with T2) ?

I advise that you make