Hello,
I have noticed that virtual fields are painfully slow.
Here is a good way to replace them on a SQL server.
I tested it on both mysql and postgres.
suppose you have the following table:
db.define_table('numbers', Field('a', 'integer'), Field('b', 'integer'))
you can add a virtual field:
class MyVF(object):
def aplusb(self):
return self.a + self.b
db.numbers.virtualfields.append(MyVF)
If the virtual field is something more complex (eg. subqueries) and
the table has more than a few record,
you would end on your knee asking your server not to die each time you
query that table...
Here is a simple solution:
db.define_table('numbers', Field('a', 'integer'), Field('b', 'integer'))
# create the view if it does not exist yet
if not db.executesql("select * from information_schema.tables where
table_name='numbers_v' limit 1;"):
db.executesql("""create view numbers_v as
select numbers.*, numbers.a + numbers.b as aplusbs from numbers""")
# inform web2py about the view (just add the computed field the others
can be inherited)
db.define_table('numbers_v', db.numbers, Field('aplusb', 'integer'),
migrate=False)
# alias the view
numbers=db.numbers_v.with_alias('numbers')
now you can use db.numbers_v anywhere you would use db.numbers just
make use of the alias:
a_set_of_sums=db(numbers.id>0).select(numbers.aplusb)
enjoy the speed
mic
