I am trying to integrate with an existing database which uses varchar
primary key fields as references instead of integers. Reading the
book, I found that I could still get this to work by setting
primarykey=['the_field'] in the define_table definition. The table I
am defining has two fields that reference another record in the same
table. In this case, it is an Employees table, using UserID (varchar)
as the primary key, and another field called Supervisor that refers to
another Employees.UserID record. In my definition, I have used
'reference Employees', but whenever I use appadmin, I get s ticket
error that 'Employees' does not exist. However, making it lowercase:
'reference employees' gets it to the next step.
However, the first time it tries to pull a record, I get an error:
invalid literal for int() with base 10: 'bob'. So I am reporting two
problems. The 'reference' argument requires a lowercase table name,
regardless of the casing of the table name, and the second problem
being that web2py still expects to find an integer ID field when using
'reference' and primarykey=[].
db.define_table('Employees',
Field('UserID', length=20),
Field('Password', length=50),
Field('IsAdmin', 'boolean'),
Field('IsManager', 'boolean'),
Field('IsEnabled', 'boolean'),
Field('IsAccounting', 'boolean'),
Field('IsMachining', 'boolean'),
Field('IsProduction', 'boolean'),
Field('IsHourly', 'boolean'),
Field('IsPartTime', 'boolean'),
Field('AllowEmployeeLogs', 'boolean'),
Field('AllowCloneDb', 'boolean'),
Field('LastName', length=20),
Field('FirstName', length=20),
Field('LegalName', length=50),
Field('AllianceID', length=10),
Field('DepartmentID', length=50),
Field('WorkEmailAddress', length=50),
Field('HomeEmailAddress', length=50),
Field('StreetAddress', length=50),
Field('City', length=50),
Field('State', length=50),
Field('ZipCode', length=10),
Field('HomePhone', length=20),
Field('WorkPhone', length=20),
Field('MobilePhone', length=20),
Field('PagerPhone', length=20),
Field('StartDate', 'datetime'),
Field('LeaveDate', 'datetime'),
Field('Picture', length=255),
Field('BarcodeID', length=12),
Field('Supervisor', 'reference employees'),
Field('AssistantTo', 'reference employees'),
Field('PartTimeHolidayHours', length=6),
Field('MachineName', length=20),
primarykey=['UserID'],
migrate=False
)
Again, for this to get to the appadmin at all without generating a
ticket, I needed to make 'employees' lowercase in the Supervisor and
AssistantTo fields, even though the table I am defining is
'Employees'.
I hope I explained this well enough. Thank you.
