Re: [web2py] Dynamic model creation

[Andrew]

Check out http://www.web2pyslices.com/slice/show/1491/plugin-lookout

I have a need to open up other databases and inspect their structures, by 
looking at the other system's catalog.  The slice above also does that.

Re: [web2py] Dynamic model creation

[pbreit]

I'm not sure it's a Web2py limitation so much as an SQL limitation. You 
actually might be able to implement dynamic schemas in Web2py I guess by 
building your define_tables() on-the-fly but it doesn't sound like the best 
idea (you'd need to be doing a lot of unpredictable migrations).

But dynamic schemas are where NoSQL DBs really shine. There's some degree 
of support for Mongo in Web2py but I think it's fairly preliminary.

You could also program directly to Mongo but you'd lose DAL features (which 
might not be too bad).


On Thursday, June 7, 2012 3:47:00 AM UTC-7, Christian wrote:
>
> Hi pbreit and thanks for the reply.
> So web2py is not the preferred way to go as DAL needs a scheme? Is there 
> no way to integrate DAL in a dynamic way?
>
> Regards,
> Christian
>
> Am Sonntag, 3. Juni 2012 schrieb pbreit :
>
>> My guess is it's NoSQL, probably Mongo which seems to be the preferred DB 
>> behind a lot of these "real-time" JavaScript frameworks.
>>
>>
>> On Sunday, June 3, 2012 2:35:34 AM UTC-7, Christian wrote:
>>>
>>> Hello group,
>>>
>>> I found a backend service, mostly for mobile apps, called parse.com. 
>>> Looking at their REST documentation (https://parse.com/docs/rest), it 
>>> looks like one can e.g. dynamically generate different kind of objects, 
>>> which get persistet (see "creating objects" in the docs to see what I 
>>> mean). These objects can get relationships to other objects and can also be 
>>> queried. So if the client posts GameScore or GameScores, both are valid 
>>> (but different) objects (in different tables?)  and get persisted,
>>> My questions: how looks sth. like this on the model side? How is this 
>>> done? How to model general tables and still make them queryable. Or does 
>>> the model generate tables dynamically? Could something like this be modeled 
>>> with DAL? 
>>> On the controller side: could such a dynamic model be made available via 
>>> sth like @request.restful?
>>>
>>> I would be happy for any hint, as I have no idea, how this is done!
>>>
>>> Thanks and regards,
>>> Christian
>>>
>>> P.S.: I also found a open source clone, which rebuilds the iOS client 
>>> library and the server with node and MongoDB, if this might be of any help. 
>>> I do not speak JS and have no clue about mongo. 
>>> https://github.com/eaigner/**DataKit
>>>
>>>

Re: [web2py] Dynamic model creation

[Christian Hoffmann]

Hi pbreit and thanks for the reply.
So web2py is not the preferred way to go as DAL needs a scheme? Is there no
way to integrate DAL in a dynamic way?

Regards,
Christian

Am Sonntag, 3. Juni 2012 schrieb pbreit :

> My guess is it's NoSQL, probably Mongo which seems to be the preferred DB
> behind a lot of these "real-time" JavaScript frameworks.
>
>
> On Sunday, June 3, 2012 2:35:34 AM UTC-7, Christian wrote:
>>
>> Hello group,
>>
>> I found a backend service, mostly for mobile apps, called parse.com.
>> Looking at their REST documentation (https://parse.com/docs/rest), it
>> looks like one can e.g. dynamically generate different kind of objects,
>> which get persistet (see "creating objects" in the docs to see what I
>> mean). These objects can get relationships to other objects and can also be
>> queried. So if the client posts GameScore or GameScores, both are valid
>> (but different) objects (in different tables?)  and get persisted,
>> My questions: how looks sth. like this on the model side? How is this
>> done? How to model general tables and still make them queryable. Or does
>> the model generate tables dynamically? Could something like this be modeled
>> with DAL?
>> On the controller side: could such a dynamic model be made available via
>> sth like @request.restful?
>>
>> I would be happy for any hint, as I have no idea, how this is done!
>>
>> Thanks and regards,
>> Christian
>>
>> P.S.: I also found a open source clone, which rebuilds the iOS client
>> library and the server with node and MongoDB, if this might be of any help.
>> I do not speak JS and have no clue about mongo.
>> https://github.com/eaigner/**DataKit 
>>
>>

Re: [web2py] Dynamic Model

[Phyo Arkar]

Thanks Massimilliano!

This is very interesting , i will test it out.

On 9/30/11, Massimiliano  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
>

Re: [web2py] Dynamic Model

[Massimiliano]

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

Re: [web2py] Dynamic Model

[Vasile Ermicioi]

I have success using the embedded (core, common + persistence jars) version
via jpype (Java API),
I will try to prototype an adapter, if I will have success I will inform you

It has all in one:

- key value store
- document database
- graph database
- object database

http://code.google.com/p/orient/wiki/JavaAPI

and embedded version doesn't seem to eat little memmory (<25 Mb python +
jvm), so it usable even on my memory limited webfaction account :) (80 Mb
for now)

Re: [web2py] Dynamic Model

[Vasile Ermicioi]

>
>  doesn't seem to much  memmory
>
>
>

Re: [web2py] Dynamic Model

[TheSweetlink]

I have recently found OrientDB http://orientechnologies.com

It is a document-graph db that will allow you to evolve your schema
over time.  In fact you can have no schema, mixed-schema, or full
schema.  There is SQL syntax that is both familiar and powerful,
especially when combined with Gremlin the graph traversal language.

OrientDB is VERY FAST in my initial testing.  Massimo might write an
adapter in the future to make it more integrated with web2py but in
the meantime I have had great success using the requests python
library to access OrientDB via its RESTful API.

The community behind OrientDB has been as helpful to me as the web2py
community has been which is monumentally helpful to say the least.

Best of luck,
David

Phyo Arkar wrote:
> Couch ? Mongo?
> I really have to try them , not started yet.
>
> On Thu, Sep 29, 2011 at 4:28 AM, pbreit  wrote:
>
> > NoSQL

Re: [web2py] Dynamic Model

[Phyo Arkar]

Couch ? Mongo?
I really have to try them , not started yet.

On Thu, Sep 29, 2011 at 4:28 AM, pbreit  wrote:

> NoSQL

Re: [web2py] Dynamic Model

[pbreit]

NoSQL

Re: [web2py] Dynamic Model

[Phyo Arkar]

Thank you very much for that technique. I will check it.

Yeah i also thought about writing schema_0-1-2-3-4.py file for each of the
table for each users.
But Letting python write its own Python code , wont it introduce security
concern :| ?

On Thu, Sep 29, 2011 at 4:11 AM, Ismael Serratos wrote:

> Hi Phyo!!! I needed almost the same, and I found the EAV Modeling technique
> (http://en.wikipedia.org/wiki/Entity-attribute-value_model) useful, but
> due the complexity of my project I write the model as a file, I mean open
> the *.py, write, append.
>
> On Wed, Sep 28, 2011 at 4:31 PM, Phyo Arkar wrote:
>
>> Here is what i was asked for, weird that i haven't see in any application
>> yet.
>>
>> A Dynamic Model : A dynamic table schema.
>>
>> They want a feature to add a column dynamically , for example a table do
>> not have comment field. they want a button to add a new field on the fly and
>> add the data into it.
>>
>> so when he modify a table by adding new field , That new Table model need
>> to store somewhere. Should i keep it inside DB ?
>> its gonna be weird that when his table gonna load , it will look up into
>> db for table defination and load it..
>>
>> What you guys think?
>>
>> Thanks in advance
>>
>> Phyo.
>>
>
>

Re: [web2py] Dynamic Model

[Ismael Serratos]

Hi Phyo!!! I needed almost the same, and I found the EAV Modeling technique
(http://en.wikipedia.org/wiki/Entity-attribute-value_model) useful, but due
the complexity of my project I write the model as a file, I mean open the
*.py, write, append.

On Wed, Sep 28, 2011 at 4:31 PM, Phyo Arkar wrote:

> Here is what i was asked for, weird that i haven't see in any application
> yet.
>
> A Dynamic Model : A dynamic table schema.
>
> They want a feature to add a column dynamically , for example a table do
> not have comment field. they want a button to add a new field on the fly and
> add the data into it.
>
> so when he modify a table by adding new field , That new Table model need
> to store somewhere. Should i keep it inside DB ?
> its gonna be weird that when his table gonna load , it will look up into db
> for table defination and load it..
>
> What you guys think?
>
> Thanks in advance
>
> Phyo.
>