Define Field as an Unsigned Integer With Web2py/Pydal

Question

Is it possible to define a web2py model with an unsigned integer Field? If so, how may it be done?

The underlying database is MySQL and the equivalent CREATE TABLE statement is below.

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
)
;

I have made three attempts to do this, unsuccessfully. The following snippets are the attempts.

db.define_table('t', Field('value', 'integer unsigned'))

db.define_table('t', Field('value', 'integer', options=['unsigned']))

db.define_table('t', Field('value', 'integer', options=['UNSIGNED']))

I have found no mention of unsigned integers in either the web2py or pydal documentation. Of course it's possible that unsigned integers just aren't supported by either web2py or pydal. I am using web2py version 2.20.4-stable+timestamp.2020.05.02.22.03.36, pydal version 20200502.2, and MySQL version 5.7.25.

Answer 1

I think you want to define a custom_qualifier on the field:

Field('value', 'integer', custom_qualifier='UNSIGNED')

It just gets appended to the field definition when migrating

This'll make the project less portable, since unsigned ints aren't a thing in PostgreSQL or MSSQL, for example.

Answer 2

I do not believe pydal explicitly supports the unsigned integer type, but everything should work if you simply specify type='integer', as long as you are not using pydal to do migrations (i.e., it will not create a new unsigned integer column for you, but it will work with existing columns).