Two words: freakin sweet. This opens up a whole new range of applications that I can use web2py for at work. Thanks!
On Nov 8, 7:38 am, DenesL <[email protected]> wrote: > 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. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "web2py-users" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/web2py?hl=en -~----------~----~----~----~------~----~------~--~---
