I guess another alternative is to hand code it as SQL, so somewhere in
application do this:
db.sqlexecute('alter table project_details drop constraint
project_details_pkey;')
db.sqlexecute('alter table project_details add primary key (id, "Oid");')
I'm not sure where the best place to put that might be - it presumably
shouldn't be in db.py where it will get repeatedly called, but somewhere in
something like a zzz_indexing.py model file that checks first whether the
primary keys have been modified?
Cheers,
David
On Thursday, 18 August 2016 13:35:36 UTC+1, David Orme wrote:
>
> Following up - I've tried some options to set up a table creating the 'id'
> field explicitly to work around pairing the two keys.
>
> *A) Declare the field 'id' with type 'id' and set a two part primary key:*
>
> db.define_table('key_test',
> Field('id', 'id'),
> Field('oid', length=64, default=uuid.uuid4),
> primarykey = ['id', 'oid'])
>
> This doesn't work, I think because the type 'id' automatically makes it
> the primary key.
>
> *B) Declare an integer field 'id' and set a two part primary key:*
>
> db.define_table('key_test',
> Field('id', 'integer'),
> Field('oid', length=64, default=uuid.uuid4),
> primarykey = ['id', 'oid'])
>
> The table is created without any problems, but id is not a serial with an
> id value sequence. I can't immediately find a mechanism to set a field to
> have an auto increment sequence independent of using a field type of 'id'.
>
> *C) More complexity with capitalised names*
>
> As if that wasn't enough, the other application is case sensitive and uses
> Oid for the field. I can fix this using rname, but then I can't get option
> B) to work because the names aren't recognized:
>
> db.define_table('key_test2',
> Field('id', 'integer'),
> Field('oid', length=64, default=uuid.uuid4, rname='"Oid"'),
> primarykey = ['id', 'oid'])
>
> This throws the following back from the PGSQL backend: <class
> 'gluon.contrib.pg8000.ProgrammingError'> ('ERROR', '42703', 'column "oid"
> does not exist'). Which is right, because it doesn't - "Oid" does.
>
> However, this also fails
>
> db.define_table('key_test2',
> Field('id', 'integer'),
> Field('oid', length=64, default=uuid.uuid4, rname='"Oid"'),
> primarykey = ['id', '"Oid"'])
>
> The error thrown is: <type 'exceptions.SyntaxError'> primarykey must be a
> list of fields from table 'key_test2. That's also right, of course: "Oid"
> is not in db.key_test2.fields
>
> Any suggestions of an approach that might work?
>
> David
>
> On Thursday, 18 August 2016 12:13:23 UTC+1, David Orme wrote:
>>
>> Hi,
>>
>> I've got an application that shares a DB with another application running
>> a different framework. The setup is that my application declares a set of
>> tables that the other application can read from but won't write to, so I
>> haven't been exploring the mechanisms for connecting to legacy databases.
>>
>> However, the other application requires a UUID primary key called Oid, to
>> use as a foreign reference in its own tables. I can easily add that as a
>> field:
>>
>> db.define_table('project_details',
>> Field('oid', length=64, default=uuid.uuid4, rname='"Oid"'),
>> Field('project_id', 'reference project_id'),
>> Field('version', 'integer'))
>>
>> What I can't figure out how to do is add that field to the primary key.
>> For legacy tables, it seems like using this would be the approach:
>>
>> primarykey=['id','oid'],
>>
>> However, from what I can tell, once you provide primarykey, the usual
>> mechanisms for creating the default integer id primary key get disrupted.
>>
>> Any suggestions?
>>
>> Thanks,
>> David
>>
>>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
