In web2py, can I specify an existing named field as the autonumber ID in a legacy database?

Question

I have dozens of tables in an existing MSSQL database all with autonumber ID primary keys, but none that are named 'id'. They are instead named PropertyID, ClientID, etc. The official documentation seems to suggest renaming each of these fields to 'id':

Legacy Databases

web2py can connect to legacy databases under some conditions:

• Each table must have a unique auto-increment integer field called "id"
• Records must be referenced exclusively using the "id" field.

If these conditions are not met, it is necessary to manually ALTER TABLE to conform them to these requirements, or they cannot be accessed by web2py.

This should not be thought of as a limitation, but rather, as one of the many ways web2py encourages you to follow good practices.

However, that would require breaking hundreds of existing queries in other applications that use this database. Surely there must be some way to specify a name for an existing autonumber field to be used instead of 'id'.

This seems to be an area where Django got it right and web2py got it horribly wrong. Or am I just missing something? Seems I was just missing something...

Answer 1

Apparently this has not made it into the current web2py book, but it looks like this has in fact been implemented. From the web2py google group: web2py and keyed tables.

NOTE: I found this by browsing New features not documented in PDF book (2 ed)

Answer 2

That statement is obsolete. There are three cases supported by web2py:

1. a table has a auto-increment field called 'id' (default)

2. a table has a auto-increment field not called 'id', define the table with

   db.define_table('name',Field('id_name','id'),...other fields...)

3. a table has a different primary key

   db.define_table('name',...fields..., primarykey=[....])

The primarykey is a list of field names.

Option 3 does not work with all supported databases but it can easily be extended. We just did not get much request for it so we do not have enough testers for all the possible options. Please move this discussion on the web2py mailing list and we'll be happy to help you more.