Thanks Massimilliano!
This is very interesting , i will test it out.
On 9/30/11, Massimiliano <mbelle...@gmail.com> 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
>
