I'm very interested on dynamic model and I'm investigating a little bit.
I've had some results with this model.
The user can define compound models. Types are ancestor types (string,
integer etc) useful for representation
db.define_table(
'types',
Field('type', 'string'),
format='%(type)s'
)
db.define_table(
'models',
Field('name', 'string'),
format='%(name)s'
)
db.define_table(
'models_tree',
Field('parent', 'reference models'),
Field('child', 'reference models'),
Field('display_order', 'integer')
)
db.define_table(
'fields',
Field('name', 'string'),
Field('label', 'string'),
Field('model', 'reference models'),
Field('type', 'reference types'),
Field('display_order', 'integer'),
format='%(name)s'
)
#db.fields.type.requires=IS_IN_DB(db, db.types)
db.define_table(
'rows',
Field('model', 'reference models'),
Field('note', 'text')
)
db.define_table(
'meta',
Field('field', 'reference fields'),
Field('row', 'reference rows'),
Field('value', 'text')
)
db.meta.field.requires=IS_IN_DB(db, db.fields)
db.meta.row.requires=IS_IN_DB(db, db.rows)
def get_models():
query = (db.fields.type == db.types.id) & (db.fields.model ==
db.models.id)
return db(query).select()
models = lambda : get_models()
def get_tree():
parents = db.models.with_alias('parents')
childs = db.models.with_alias('childs')
query = ((db.models_tree.parent == db.models.id) & (db.models_tree.child
== childs.id))
sql = """
SELECT
parents.name,
childs.name
FROM
models as parents,
models as childs,
models_tree
WHERE
models_tree.parent = parents.id AND
models_tree.child = childs.id;
"""
return db.executesql(sql)
def get_fields_from_parent_model(parent_id):
query = db.models_tree.parent == parent_id
models = db(query).select(orderby=db.models_tree.display_order)
fields = []
if models:
for m in models:
fields += get_fields_from_parent_model(m.child)
else:
fields = [r for r in
db(db.fields.model==parent_id).select(orderby=db.fields.display_order)]
return fields
tree = lambda : get_tree()
def build_query(model_id):
fields = get_fields_from_parent_model(model_id)
_select_tpl = "\t%(name)s.value as %(name)s"
_from_tpl = "\t\tJOIN meta %(name)s ON (%(name)s.row = rows.id AND
%(name)s.field = %(id)s)"
_select = ["rows.id as id"]
_from = [" FROM", "\tmodels, ", "\trows"]
for f in fields:
f.name = f.name.replace(" ", "_")
_select.append( _select_tpl % f)
_from.append( _from_tpl % f)
select = 'SELECT '
select += ", ".join(_select)
frm = "\n".join(_from)
#where = " WHERE rows.id = %s;" % row_id
where = " WHERE rows.model = models.id AND models.id = %s" % model_id
sql = select + frm + where
print sql
rows = db.executesql( sql )
return rows
