unique=True for time on sqlite

[Paolo Valleri]

Hi all, I've tested the same example on both postgres and sqlite:

def run():

    db.define_table('user_table',

        Field('birthday', 'date', unique=True))

        

    grid = SQLFORM.grid(db.user_table.id, user_signature=False)

    return {'grid':grid}

The expected behavior is to get an error message when the I try to insert a new record with a date already present in the db.
It worked on postgres, on sqlite I got a ticket error instead saying:

UNIQUE constraint failed: user_table.birthday

is it possible to support unique=True on sqlite?

Paolo

[Anthony]

Are you saying you want an error message to appear on the form? For that, you need an IS_NOT_IN_DB validator, regardless of the database backend.

Anthony

[Paolo Valleri]

What is odd to me is that,on postgres the validator works as expected while on sqlite it doesn‘t and it is the backend itself that raises the exception.

On Jul 8, 2014 6:43 PM, "Anthony" <abas...@gmail.com> wrote:

Are you saying you want an error message to appear on the form? For that, you need an IS_NOT_IN_DB validator, regardless of the database backend.

Anthony

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/AnmLhKmS8UA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Paolo Valleri]

I found the issue. Since the format of the date field (same happens for the time field) is a text in sqlite we must postpone to the validator IS_NOT_IN_DB the validator IS_DATE. 

By setting:

db.user_table.birthday.requires=[IS_DATE(), IS_NOT_IN_DB(db,'user_table.birthday')]

everything works as expected even on sqlite. 

What do you think if we make it as default for date/time fields when the backend is sqlite?

Paolo

To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@googlegroups.com.

[Anthony]

In DAL.sqlhtml_validators, we have:

    if field.unique:
        requires.insert(0,validators.IS_NOT_IN_DB(db,field))

I wonder why IS_NOT_IN_DB is prepended rather appended to the list of validators. Given that some of the validators may actually alter the value to be inserted (e.g., CRYPT, IS_SLUG, IS_UPPER, IS_LOWER, CLEANUP), I would think we would generally want IS_NOT_IN_DB to come last so it applies to the actual value to be inserted (of course, there are cases where you might want the opposite).

Anthony

[Massimo Di Pierro]

Yes. It should be appended... I see there is a pending patch. Will apply asap.

[Anthony]

Note, the pending patch makes the change only in the case of SQLite with date and time fields, so it will need to be altered to make the change universal.

Anthony