duplicate records not throwing an exception

[Carl]

I have this in my model: db.py:

db.define_table('user',
SQLField('email', 'string', length=320),
SQLField('hash','string'))
db.user.email.requires=[IS_NOT_EMPTY(),IS_LOWER(),IS_NOT_IN_DB(db,
db.user.email)]

In my controller: default.py:

try:
uid = db.user.insert(email=request.vars.email,
hash=converted)
except:
etc...

I can execute the controller code again and again with the same value
in request.vars.email with success. I was expecting an exception to be
throw (which I would catch) when I tried to insert an existing email
address.

What am I missing?

Can anyone help?

[mdipierro]

This

db.user.email.requires=[IS_NOT_EMPTY(),IS_LOWER(),IS_NOT_IN_DB
(db,db.user.email)]

is only enforced at the level for form validation. If you want the
database to enforce integrity

db.define_table('user',
> SQLField('email', 'string', length=320, unique=True),

> SQLField('hash','string'))

[Carl]

thanks M / very speedy concise reply.
p 138/139 explains it all - sorry to have missed this detail.

[Carl]

db.user.insert isn't throwing an exception if a duplicate record is
inserted but instead continues to the db.commit() instruction -- when
I using appadmin I can confirm that the duplicate record has been
added.

My insert code...

try:
existingEmail = db.user.insert(email=request.vars.email,
greet=False)
db.commit()
except:
#email record already exists
### handling code

Curiously... if I go to:
.../appadmin/select/db?query=db.user.id>0 then try to add a record
with a duplicate email address the form reports "value already in
database!"

This suggests that my db.py setting is okay. What am I missing in my
code?

Carl

[mdipierro]

what's the model? is unique=True in there?

[Carl]

yes; it's:

db.define_table('user',
SQLField('email', 'string', length=320, notnull=True,
required=True, unique=True),
SQLField('greet', 'boolean'),
SQLField('day', 'string', length=2),
SQLField('month', 'string', length=9),
SQLField('year', 'string', length=4))

and then for FORMs I have:
db.user.email.requires=[IS_LOWER(), IS_EMAIL(), IS_NOT_IN_DB(db,
db.user.email)]
db.user.greet.requires=[IS_NOT_EMPTY()]

I can't find in the web2py framework code where the exception would be
thrown.
I'm using sqlite locally.

[mdipierro]

The database should throw and OperationalError at insert. Look into
sql.log and you should find the "CREATE TABLE... EMAIL UNIQUE..." I do
not know why sqlite is ignoring it.

Massimo

[Carl]

ah! sqlite had become confused such that adding 'unique=True' to my
table definition didn't update the data store.
Deleting the data store and starting again has the behaviour sorted.

thanks for your feedback.

[mdipierro]

yes sqlite does not understand migrations well. In particular it
cannot alter table and change a column type. It just stores new values
according to the new type.

Massimo

[Carl]

My target database is Google's AppEngine. Do you know if it handles
migrations smoothly?

[mdipierro]

On GAE there is no concept of alter table so you can add columns and
you can drop (they will not be dropped, just ignored). You can change
some attributes of a column (like length) since they are ignored
anyway. Some migrations will not work.

Massimo