web2py and keyed tables

[DenesL]

A) What are they?

Keyed tables are usually legacy tables that don't follow the
traditional hidden 'id' field construct of web2py tables.
Instead they define one or more fields as primary key.
Currently web2py supports keyed tables on DB2, MS SQL, Ingres (thanks
Chris Clark) and Informix (after pending patch, thanks Hans Murx).
To add support for other DB engines see at the bottom of this info.



B) How do you create keyed tables?

Using the same define_table as before but with one difference, the
table has a 'primarykey' attribute.

db.define_table('kperson',
Field('name','string'),
primarykey=['name'],
migrate=False # for legacy tables
)

db.define_table('kdog',
Field('name','string'),
Field('owner','reference kperson.name'),
primarykey=['name'],
migrate=False # for legacy tables
)

Notes:
1) primarykey is a list of the field names that make up the primary
key
2) all primarykey fields will have NOT NULL set even if not specified
3) references are to other keyed tables only
4) references must use tablename.fieldname format, as shown above
5) update_record function is not available
6) web2py rev.1354 (or higher) has appadmin support for keyed tables.



C) How to use keyed tables?

They behave almost the same way, the differences are explained below.

>>> db.kperson.insert(name='Dave')
{'name': 'Dave'}

note: insert returns the primary key of the inserted record or None if
the record was not inserted

After some more inserts:

>>> print db(db.kperson.name!='').select()
kperson.name
Anna
Bart
Cody
Dave

>>> db.kdog.insert(name='Rex',owner='Cody')
{'name': 'Rex'}

>>> db.kdog.insert(name='Fido',owner='Anna')
{'name': 'Fido'}

>>> db.kdog.insert(name='Rover',owner='Cody')
{'name': 'Rover'}

>>> print db(db.kdog.name!='').select()
kdog.name,kdog.owner
Fido,Anna
Rex,Cody
Rover,Cody

>>> print db(db.kdog.owner=='Cody').select()
kdog.name,kdog.owner
Rex,Cody
Rover,Cody

>>> db.kdog.insert(name='Spot',owner='Bart')
{'name': 'Spot'}

>>> db(db.kdog.name=='Spot').delete()
1

>>> db(db.kdog.name=='Lassie').delete()
0

note: successful operations return 1, 0 otherwise

>>> db(db.kdog.name=='Rover').update(owner='Bart')
1

>>> print db(db.kdog.name!='').select()
kdog.name,kdog.owner
Fido,Anna
Rex,Cody
Rover,Bart

>>> print db((db.kdog.owner==db.kperson.name)&(db.kdog.name.like('R%'))).select()
kdog.name,kdog.owner,kperson.name
Rex,Cody,Cody
Rover,Bart,Bart



D) About compound (multiple) keys.

Compound keys can be defined and used in regular operations by passing
a dictionary.

db.define_table('mperson',
Field('name1'),
Field('name2'),
primarykey=['name1','name2'])

>>> print db.mperson.insert(name1='Bart',name2='Simpson')
{'name2': 'Simpson', 'name1': 'Bart'}

or the equivalent
>>> k={'name1': 'Lisa', 'name2':'Simpson'}
>>> print db.mperson.insert(**k)
{'name2': 'Simpson', 'name1': 'Lisa'}

>>> print db(db.mperson.name1!='').select()
mperson.name1,mperson.name2
Bart,Simpson
Lisa,Simpson


But references are only to single key, for now.
Field('x','reference mperson.name2')

For selects you have to split the key manually.
db((db.mperson.name1=='Bart')&(db.mperson.name2=='Simpson')).select()



E) How can I add support for DB xxx?

Glad to hear that you want to help, we can use your expertise on xxx.
It is not that complicated.

Step 1:
In gluon/sql.py look for SQL_DIALECTS.
It is a dictionary of command "translations" for all supported DBs.
Now look for the key that represent the DB you want to help adding in.
We need two new entries in there, one with key 'reference FK' for
field level foreign key and another key 'reference TFK' for table
level foreign key.
Use the entries under 'mssql' as a guide.

Step 2:
In the same file look for the definition of the insert function for
keyed tables (near line 2250 in the latest trunk).
Add your DB to the list in the if statement and add an exception using
the ones already there as a reference.

Step 3:
Test, test, test...


** NOTE **: this is a work in progress.
Please report any problems to the group.
Ideas for improvement are welcome too.


Denes.

[mr.freeze]

Two words: freakin sweet. This opens up a whole new range of
applications that I can use web2py for at work. Thanks!

[mdipierro]

Denese. You did a fanstatic job. This was needed for some time.

On Nov 8, 7:38 am, DenesL <denes1...@yahoo.ca> wrote:

[Timbo]

You are my hero. If you ever make your way down to Oklahoma I'll take
you out for a steak dinner or something.

[DenesL]

@ mr.freeze: you are welcome
@ Massimo: thanks for merging it into web2py
@ Timbo: LOL, maybe someday.

[Richard]

I don't need this right now, but it's great to have this flexibility
in the future.
Thanks