You are my hero. If you ever make your way down to Oklahoma I'll take
you out for a steak dinner or something.
On Nov 8, 8:46 am, mdipierro <mdipie...@cs.depaul.edu> wrote:
> Denese. You did a fanstatic job. This was needed for some time.
>
> On Nov 8, 7:38 am, DenesL <denes1...@yahoo.ca> 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 web2py@googlegroups.com
To unsubscribe from this group, send email to
web2py+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en
-~----------~----~----~----~------~----~------~--~---
