Denese. You did a fanstatic job. This was needed for some time.
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
-~----------~----~----~----~------~----~------~--~---
