Database constraints or Web2py validators

[Johann Spies]

I have several tables where there should only be one possible combination of two uuid-fields per table (mostly many to many links).

I have created 'unique' constraints for those fields in Postgresql so the database would not accept any inserts or edits which violates that constraint.

I use that in combination with 'try:..  except:' to insert data.

Can DAL be used in a way for this?  As far as I understand the IS_NOT_IN_DB validators that it is working on form-level and not on database level and I am not sure whether it can be used for a combination of fields (unless I do it with a computed field - which will use up a lot of extra resources).

Regards
Johann

--
 May grace and peace be yours in abundance through the full knowledge of God and of Jesus our Lord!  His divine power has given us everything we need for life and godliness through the full knowledge of the one who called us by his own glory and excellence.
                                                    2 Pet. 1:2b,3a

[Anthony]

On Tuesday, October 11, 2011 4:04:41 AM UTC-4, spyker wrote:

I have several tables where there should only be one possible combination of two uuid-fields per table (mostly many to many links).

I have created 'unique' constraints for those fields in Postgresql so the database would not accept any inserts or edits which violates that constraint.

I use that in combination with 'try:..  except:' to insert data.

Can DAL be used in a way for this?  As far as I understand the IS_NOT_IN_DB validators that it is working on form-level and not on database level and I am not sure whether it can be used for a combination of fields (unless I do it with a computed field - which will use up a lot of extra resources).

If you only need to validate form submissions (and not other means of making inserts), then you could do something like:

db.define_table('mytable',
    Field('field1'),
    Field('field2'))

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

That will make sure field2 is unique among the set of records where field1 matches the new value of field1 being inserted (so the combination of field1 and field2 must be unique). It depends on the values being available in request.vars, so not relevant for non-form inserts (though in that case, I suppose you could create your own request.vars as a hack).

Anthony

[Anthony]

Again, if just focused on form validation, I suppose another option would be to use an onvalidation function (processed after form validation but before db insert).

Anthony

On Tuesday, October 11, 2011 7:23:33 AM UTC-4, Anthony wrote:

On Tuesday, October 11, 2011 4:04:41 AM UTC-4, spyker wrote:

I have several tables where there should only be one possible combination of two uuid-fields per table (mostly many to many links).

I have created 'unique' constraints for those fields in Postgresql so the database would not accept any inserts or edits which violates that constraint.

I use that in combination with 'try:..  except:' to insert data.

Can DAL be used in a way for this?  As far as I understand the IS_NOT_IN_DB validators that it is working on form-level and not on database level and I am not sure whether it can be used for a combination of fields (unless I do it with a computed field - which will use up a lot of extra resources).

If you only need to validate form submissions (and not other means of making inserts), then you could do something like:

db.define_table('mytable',

    Field('field1'),

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

[Johann Spies]

On 11 October 2011 13:25, Anthony <abas...@gmail.com> wrote:

Again, if just focused on form validation, I suppose another option would be to use an onvalidation function (processed after form validation but before db insert).

Anthony

On Tuesday, October 11, 2011 7:23:33 AM UTC-4, Anthony wrote:

On Tuesday, October 11, 2011 4:04:41 AM UTC-4, spyker wrote:

I have several tables where there should only be one possible combination of two uuid-fields per table (mostly many to many links)...

 

db.define_table('mytable',

    Field('field1'),

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

That will make sure field2 is unique among the set of records where field1 matches the new value of field1 being inserted (so the combination of field1 and field2 must be unique). It depends on the values being available in request.vars, so not relevant for non-form inserts (though in that case, I suppose you could create your own request.vars as a hack).

Thanks Anthony.  I will probably make use of what you suggested.  I did not know how to involve the two fields in the form-evaluation.  This will not replace the database constraint:  tackling this from both the backend and the user interface.

[Bruno Rocha]

On Tue, Oct 11, 2011 at 8:43 AM, Johann Spies <johann...@gmail.com> wrote:

Thanks Anthony.  I will probably make use of what you suggested.  I did not know how to involve the two fields in the form-evaluation.  This will not replace the database constraint:  tackling this from both the backend and the user interface.

Note that you can use db.table.validate_and_insert and db.table.validate_and_update to fire validators in pure DAL without forms.

--

Bruno Rocha

[ About me: http://zerp.ly/rochacbruno ]

[ Aprenda a programar: http://CursoDePython.com.br ]

[Johann Spies]

On 11 October 2011 13:48, Bruno Rocha <rocha...@gmail.com> wrote:

Note that you can use db.table.validate_and_insert and db.table.validate_and_update to fire validators in pure DAL without forms.

Thanks!  I did not know about that.

Regards
Johann