3

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...

skaffman
  • 398,947
  • 96
  • 818
  • 769
mwolfe02
  • 23,787
  • 9
  • 91
  • 161
  • They haven't got it "horribly wrong" and no, I don't think you're missing anything. They've made a determination to have a particular architectural requirement that in turn places constraints on where web2py may be used - if you can't work with that then basically tough (which is not altogether unreasonable). OTOH the *comments* made in its defence on the referenced page are dumb, worse than dumb - a joy of theory over practice and arrogant in a very negative sense. Of course the source is available so you could always hack... but I suspect that's not really an option – Murph Jul 12 '10 at 18:29
  • I used the term "horribly wrong" because massimo's comment on that page made it sound as though web2py had made a conscious decision to not support a fairly common use case. Apparently, enough other web2py users needed the feature that it was added to trunk as of version 1.72.1. In the link I referenced in my answer below, Massimo even says, "This was needed for some time." I appreciate the time and effort he's put into the framework, but his responses to justified criticisms are every bit as acerbic as his responses to the inane criticisms of those who've never used web2py. – mwolfe02 Jul 12 '10 at 19:24
  • I apologize if I sounded acerbic. That was not my intention. When I said "that statement is obsolete" I did not mean yours, but I meant my statement which you quoted correctly. Sometimes I am cryptic and concise because of lack of time. Criticism help us make web2py better and/or clarify misunderstandings, so it is welcome. :-) – mdipierro Jul 18 '10 at 10:45
  • Glad to hear it. If possible, you might consider editing the FAQ page I linked to and quoted in my question. A google search for "web2py legacy database" returns that page as its first result. And since anyone who's done any amount of research into using web2py realizes that you are the creator, your comments carry the weight of official status. Since the support for legacy databases has vastly improved since that statement and v2 of the web2py book, I think it would benefit the community as a whole if that information is more widely available. Hopefully this question on SO helps with that. – mwolfe02 Jul 19 '10 at 12:58

2 Answers2

4

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.

mdipierro
  • 4,239
  • 1
  • 19
  • 14
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)

mwolfe02
  • 23,787
  • 9
  • 91
  • 161