Thanks Massimilliano! This is very interesting , i will test it out.
On 9/30/11, Massimiliano <[email protected]> wrote: > 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 >
