Hi guys!
I have an idea to improve DAL in scope of work with normalized DB.
As known It's a common practice to avoid NULL value by creating separate
(option) table(s) to store non required fields.
So, it would be great to have a field type like "storedin
table_name.field_name"
For example:
db.define_table('alias_opt', Field('name'), Field('alias_name', 'reference
person'))
db.define_table('person', Field('name'), Field('alias_name', 'storedin
alias_opt.alias_name'))
#INSERT:
db.person.insert(name='Alex', alias_name='Macedonian')
# means:
id=db.person.insert(name='Alex')
db.alias_opt.insert(id=id, alias_name='Macedonian')
#UPDATE:
db(db.person.id==id).update(... , alias_name=None)
# means:
# update person
...
# update option table
update_opt_args = filter_storedin_fields(update_args)
opt_rec = db.alias_opt(id)
opt_rec.update(update_opt_args)
if not any(opt_rec.values()): # - all fields of option table record is
None
del db.alias_opt(id)
else:
db.alias_opt.update_or_insert(id==id, **update_opt_args)
#DELETE:
del db.person(id) also means del db.alias_opt(id), like ondelete=
'CASCADE'
#SELECT:
rows = db(db.person).select()
# means:
rows = db(db.person).select( left=[ db.alias_opt.on( db.alias_opt.id == db.
person.id ) ] )
but only "storedin" fields should be selected from db.alis_opt
and they should be accessed by row.alias_name (not only by
row.joined_table.field_name
)
Considering, that table person could be a VIEW (i.e. JOIN is already
performed at DB level), there is no need to make join at web2py level,
it could be fixed by passing an option arg like is_view=True to
define_table()
I know, that behavior of insert/update/delete could be easy realized by
custom class based on Table
with a little hacking Field-class to intercept field type to fix it to
web2py type ( considering person.alias_name.type ==
alias_opt.alias_name.type ).
But it's hard for me to change select() behavior, because there is only
common_filter, but there isn't common_join/common_left with providing of
maping (aliasing) joined table fields to 'storedin' fields.
In fact, I dream of common_join depends on discriminator field, that will
switch tables to be joined depend on discriminator value specified in the
query (something like db.object.type_id==type_id),
if descriminator is not specified or couldn't be resolved at web2py level,
it performs left join all tables (from a list containing possible
common_join tables)
P.S. May be I try to reinvent the wheel, so feel free to shoot me!
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
