[web2py] How to set multiple primary key?

[Iceberg]

Hi there,

I am fiddling multiple primary keys. Somehow I managed to find
following example inside gluon/sql.py

db.define_table('account',
Field('accnum','integer'),
Field('acctype'),
Field('accdesc'),
primarykey=['accnum','acctype'])

But running the example throws:

Traceback (most recent call last):
......
File "C:\DOWNLOAD\google_appengine\web2py\gluon\sql.py", line 2112,
in __init__
raise SyntaxError, 'invalid table "%s" attribute: %s' %
(tablename, k)
SyntaxError: invalid table "account" attribute: sequence_name

What did I do wrong?

Iceberg

[DenesL]

Multiple primary keys should work if you are defining legacy tables in
one of the supported DB engines.
Are you using MS SQL, DB2, Ingres or Informix?
Can we see the model?

[Iceberg]

I am not dealing with legacy tables, I am just starting a new db and
wondering whether I can set multiple primary key, in order to
eliminate the chance of duplicated data.

For example.

db = DAL('sqlite://storage.sqlite')
db.define_table('account',
Field('given_name'),
Field('surname'),
Field('phone_number'),
primarykey=['given_name','surname'])

[DenesL]

SQLite is not one of the supported DBs.
Keyed tables are only supported in MS SQL, DB2, Ingres and Informix.
Other DB engines (except SQLite)might be added following section E in
http://groups.google.com/group/web2py/msg/c9848792a8999c5f

[Iceberg]

Thanks for the info. Would you please comment on my following
understanding and further questions?

1. Keyed tables are only designed for dealing with legacy tables. Is
that true? And how do we define multiple primary keys in a brand new
db? Consider the address book scenario in my previous post.

2. Keyed tables are yet to be supported in some other DB engines, but
NOT the handy SQLite. Is there any special reason for that? AFAIK
SQLite contains native support for multiple primary key.

3. Neither keyword KeyedTable nor primarykey can be found in
http://www.web2py.com/book right now. Why? Denes' good work deserves
being mentioned as a dedicated section.

Thanks in advance.

Sincerely,
Iceberg

On May9, 8:55pm, DenesL <denes1...@yahoo.ca> wrote:
> SQLite is not one of the supported DBs.
> Keyed tables are only supported in MS SQL, DB2, Ingres and Informix.

> Other DB engines (except SQLite)might be added following section E inhttp://groups.google.com/group/web2py/msg/c9848792a8999c5f

[DenesL]

On May 9, 9:08 am, Iceberg <iceb...@21cn.com> wrote:
> Thanks for the info. Would you please comment on my following
> understanding and further questions?
>
> 1. Keyed tables are only designed for dealing with legacy tables. Is
> that true? And how do we define multiple primary keys in a brand new
> db? Consider the address book scenario in my previous post.

Keyed tables support is primarily for legacy tables that do not have a
field that can be used as id field. It can also be used in newly
created tables since all operations work (except update_record). Note
that references are specified as tablename.fieldname and can only
reference other keyed tables.

> 2. Keyed tables are yet to be supported in some other DB engines, but
> NOT the handy SQLite. Is there any special reason for that? AFAIK
> SQLite contains native support for multiple primary key.

I might be wrong but I think SQLite does not have FK (field level
foreign key) and TFK (table level foreign key) required for the keyed
table support in web2py.

> 3. Neither keyword KeyedTable nor primarykey can be found inhttp://www.web2py.com/bookright now. Why? Denes' good work deserves

> being mentioned as a dedicated section.

It was added after the book was made available and it would be my
fault, I either had no time or forgot about it.

[Nico de Groot]

Hi Denes,

I got the same syntax error (SyntaxError: invalid table "account"
attribute: sequence_name ) when using a legacy database (by specifying
the primarykey in 'define_table'). I tracked it down to the init
method of KeyedTable in sql.py and made an adjustment that worked -
for me at least - see below.

Nico de Groot

class KeyedTable(Table):
...
def __init__(
self,
db,
tablename,
*fields,
**args
):
...
self._trigger_name = args.get('trigger_name', None)
self._sequence_name = args.get('sequence_name', None)

for k,v in args.iteritems():
#patch NCdG
if k in ['trigger_name','sequence_name']:
continue
#/patch NCdG
if k != 'primarykey':

...

On 9 mei, 18:57, DenesL <denes1...@yahoo.ca> wrote:
> On May 9, 9:08 am, Iceberg <iceb...@21cn.com> wrote:
>
> > Thanks for the info. Would you please comment on my following
> > understanding and further questions?
>
> > 1. Keyed tables are only designed for dealing with legacy tables. Is
> > that true? And how do we define multiple primary keys in a brand new
> > db? Consider the address book scenario in my previous post.
>
> Keyed tables support is primarily for legacy tables that do not have a
> field that can be used as id field. It can also be used in newly
> created tables since all operations work (except update_record). Note
> that references are specified as tablename.fieldname and can only
> reference other keyed tables.
>
> > 2. Keyed tables are yet to be supported in some other DB engines, but
> > NOT the handy SQLite. Is there any special reason for that? AFAIK
> > SQLite contains native support for multiple primary key.
>
> I might be wrong but I think SQLite does not have FK (field level
> foreign key) and TFK (table level foreign key) required for the keyed
> table support in web2py.
>

> > 3. Neither keyword KeyedTable nor primarykey can be found inhttp://www.web2py.com/bookrightnow. Why? Denes' good work deserves

> > being mentioned as a dedicated section.
>
> It was added after the book was made available and it would be my
> fault, I either had no time or forgot about it.
>
>
>
> > Thanks in advance.
>
> > Sincerely,

> > Iceberg- Tekst uit oorspronkelijk bericht niet weergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -

[drayco]

Thank's a lot Nico de Groot.

It worked for me too even with mysql.

[dlin]

I still not understand the answer.

How could I build primary key on two fields by default sqlite?
Or, is there any better method to build 'unique key' on multiple
fields?

> > > > that true? And how do we definemultipleprimary keys in a brand new

> > > > db? Consider the address book scenario in my previous post.
>
> > > Keyed tables support is primarily for legacy tables that do not have a
> > > field that can be used as id field. It can also be used in newly
> > > created tables since all operations work (except update_record). Note
> > > that references are specified as tablename.fieldname and can only
> > > reference other keyed tables.
>
> > > > 2. Keyed tables are yet to be supported in some other DB engines, but
> > > > NOT the handy SQLite. Is there any special reason for that? AFAIK

> > > > SQLite contains native support formultipleprimary key.