Non-relational databases

[Kless]

I'm more interested into a framework backed by a non relational db,
mainly MongoDB [1]. By that reason I discard Django, and to Pylons
because it hasn't a plugin system that lets reuse/share applications.

Is web2py ready to use non-relational databases?


[1] http://mongodb.org/

[mdipierro]

web2py works on GAE. We are committed to provide some support for
other non-relational databases (I do not know mongodb) but, if you
need it, we can work together to make it work.

Massimo

[dlypka]

Are there plans for web2py to handle the GAE ListProperty described in
the GAE Cookbook article "How to query by date range"
where it suggests:
For example, add a ListProperty named "date_list", and populate
it like this: date_list = [start_date, end_date].

I assume an example is:
class B(db.Model):
date_list=db.ListProperty()


http://appengine-cookbook.appspot.com/recipe/how-to-query-by-date-range/?id=ahJhcHBlbmdpbmUtY29va2Jvb2tylwELEgtSZWNpcGVJbmRleCI8YWhKaGNIQmxibWRwYm1VdFkyOXZhMkp2YjJ0eUZ3c1NDRU5oZEdWbmIzSjVJZ2xFWVhSaGMzUnZjbVVNDAsSBlJlY2lwZSI-YWhKaGNIQmxibWRwYm1VdFkyOXZhMkp2YjJ0eUZ3c1NDRU5oZEdWbmIzSjVJZ2xFWVhSaGMzUnZjbVVNMzUM

> > [1]http://mongodb.org/- Hide quoted text -
>
> - Show quoted text -

[mdipierro]

It is too much GAE specific and it would make the web2py app not
portable on relational databases.

You can use IS_IN_DB(...,multiple=True) to achieve very similar
functionality in a portable way.
there is a vimeo video about it.

Massimo

On May 3, 10:54 am, dlypka <dly...@gmail.com> wrote:
> Are there plans for web2py to handle the GAE ListProperty described in
> the GAE Cookbook article "How to query by date range"
> where it suggests:
>       For example, add a ListProperty named "date_list", and populate
> it like this: date_list = [start_date, end_date].
>
> I assume an example is:
> class B(db.Model):
>    date_list=db.ListProperty()
>

> http://appengine-cookbook.appspot.com/recipe/how-to-query-by-date-ran...

>
> On May 3, 10:47 am, mdipierro <mdipie...@cs.depaul.edu> wrote:
>
> > web2py works on GAE. We are committed to provide some support for
> > other non-relational databases (I do not know mongodb) but, if you
> > need it, we can work together to make it work.
>
> > Massimo
>
> > On May 3, 7:13 am, Kless <jonas....@googlemail.com> wrote:
>
> > > I'm more interested into a framework backed by a non relational db,
> > > mainly MongoDB [1]. By that reason I discard Django, and to Pylons
> > > because it hasn't a plugin system that lets reuse/share applications.
>
> > > Is web2py ready to use non-relational databases?
>

> > > [1]http://mongodb.org/-Hide quoted text -

[Tim Michelsen]

I'd be interested in:
* http://www.pytables.org
* http://www.gaia-gis.it/spatialite/

Regards,
Timmie

[dlypka]

When I evaluate a database framework, the first thing I look at is the
field types.
Right now I see web2py supports these types:

•db.Field(name, 'string')
•db.Field(name, 'text')
•db.Field(name, 'password')
•db.Field(name, 'blob')
•db.Field(name, 'upload')
•db.Field(name, 'boolean')
•db.Field(name, 'integer')
•db.Field(name, 'double')
•db.Field(name, 'time')
•db.Field(name, 'date')
•db.Field(name, 'datetime')
•db.Field(name, db.referenced_table) # reference field

My vote would go to extending the web2py types to include some non-
relational types and
the first one would be that GAE ListProperty type.,..
Perhaps there needs to be some type of type-extension feature. NET has
it.
So then the developers can develop and plugin their own types.
And there can be a pass-thru or 'raw' query mode which will pass a
native query down to the actual database handle.

We can't stop progress...

[Yarko Tymciurak]

the question of non-relational DBs is an interesting one, and increasingly will become so in the years to come.

I think DAL is structured to make web2py apps portable - allow you to run your design without regard to backend storage.  Clearly, that is biased towards relational databases which are prevelant today.

But non-relational data stores are increasingly becoming prevelant - big tables being the perhaps most influential example, but also Apache has interest in several:  Hadoop, couchDB..

Clearly, the playing field is still expanding in this area, and it is exciting times.

The question is not about stoping progress - the question is what path to take to lead / follow / influence progress.

Since DAL imposes little enough on you for portability (a goal), it is unique in being able to map to _some_ BigTable functionality.

The question is how to procdeed.

I think DAL should follow the objectives which Massimo set out;  extensibility may be useful for Relational use, but peripheral.  What remains interesting is central portability in the domain of relational data stores;  GAE is at the edge of that.

A perhaps more interesting suggestion is to have a DAL project which writes a non-relational data store abstraction, and begins to put the structure of things like BigTable, ... I don't know what others would fit into the "same kind of" class, or what a useful union for a non-relational DAL would be - perhaps Hadoop, CouchDB, MongoDB, etc.

But to do that would require people who are familiar with the broader uses of those other dbs, and interested in abstracting to create a new, non-relational DAL:  DALnr?.

For starters, I suggest a useful excercise might be to consider the interfaces within web2py, and how to make a DALnr that starts as SPECIFIC to one backend, to gain familiarity with it, and to work out the details of how this changes interfacing w/ web2py --- this excercise would free someone to explore the non-relational backend more thouroughly, and would second be a refactoring driver for web2py DAL integration... eventually leading to the ability to choose which KIND OF DAL your project uses.

Perhaps some unification between these two - so that applications can be portable accross both types - will emerge, and this would be useful.

But I think the first step is to cleanly, intentionally separate the two kinds of back ends and see where that leads.

What does everybody think?

- Yarko

[dlypka]

I think it is a great idea to be started the sooner, the better.

Plus, I also recommend creating a "wig2py" (widget 2 py) for GUI
Controls similar to the idea of ASP.NET controls
to solve the problem of using custom forms with CRUD. So I believe 2
critical web2py framework projects needs to be taken on ASAP.

By the way, Microsoft has recently released their 'Entity Framework'
(which I use every day at work now)
and so bascially there is a Kind of DAL choice in their architecture:
ADO.NET style
or
Entity Framework style

So if they can do it, so can web2py..

> > > Timmie- Hide quoted text -

[Yarko Tymciurak]

On Sun, May 3, 2009 at 4:11 PM, dlypka <dly...@gmail.com> wrote:

I think it is a great idea to be started the sooner, the better.

One thing at a time - there is no shortage of projects!

I think it will take someone who has a specific interest in and skill with a particular non-rel backend and who is interested in working with the community and Massimo on the DAL-like aspects.  This will benefit (I think) from a particular project, and will be (I suspect) a big project - for as one nr backend is done, adding another, absracting, and discovering the commonalities (and problems) will come up.  This might best be a Masters project perhaps.

[mdipierro]

we have widgets already. Look into gluon/sqlhtml.py for examples. The
only thing missing is a repository for third party widgets. It would
be great is somebody were to take care of that and maintain a
repository.

Massimo

[dlypka]

Even though there are existing widgets, do they handle the specific
issue
of working with the CRUD Error framework? i.e. is class INPUT
considered to be a widget?
I suspect we need a variation of CRUD that generates widgets
internally instead of INPUT instances etc,

> > > - Show quoted text -- Hide quoted text -

[Yarko Tymciurak]

BigTable is among the so-called column-oriented databases:

http://en.wikipedia.org/wiki/Column-oriented_DBMS

Check out:

http://www.hypertable.org/

http://en.wikipedia.org/wiki/BigTable

Cassandra: http://cwiki.apache.org/confluence/display/CSDR/Index

And this:

http://en.wikipedia.org/wiki/Document-oriented_database

http://couchdb.apache.org/docs/intro.html

http://labs.mudynamics.com/wp-content/uploads/2009/04/icouch.html

 

----------

Is there anyone who can summarize the strengths of the various 3 types?

The first two are obvious in some way to me:  pervasive (current) existence of SQL DBs, and need to manipulate existing data, ability to in small to medium (and large???) scale take advantage of open-source SQL servers;

BigTable - the increasing presence of Google as a desktop computer replacement - mail, documents, blogs, now application serving --- it's the big, open (in terms of data)  "database"...   Not clear what the implications / advantages (disadvantages) of using open-source (e.g. your own data pool) solutions would be here.

[mdipierro]

not sure I understand what you mean by widgets then. for me a widget
is an object that represents a field in a form. INPUT is not a widget.
widgets in web2py are

class IntegerWidget(StringWidget):
class DoubleWidget(StringWidget):
class TimeWidget(StringWidget):
class DateWidget(StringWidget):
class DatetimeWidget(StringWidget):
class TextWidget:
class BooleanWidget:
class OptionsWidget:
class MultipleOptionsWidget:
class PasswordWidget:
class UploadWidget:

and they are defined in gluon/sqlhtml and, of course, they completely
undocumented.

Massimo

[dlypka]

Upon further reading of the source code in html.py and sqlhtml.py
I see now that the widgets in sqlhtml.py are very elegantly built upon
calls to the 'HTML Helpers' which are in html.py.
It was the HTML Helpers which I was referring to as 'controls'.

NOTE however that I have been able to use an 'INPUT' HTML Helper from
within the View markup,
so it looks like the HTML helper can in some way behave like a widget:

<form action="" enctype="multipart/form-data" method="post">
<table>
<tr id="testtable_testfield1__row">
<td>
<label for="testtable_testfield1"
id="testtable_testfield1__label">Testfield1: </label>
</td>
{{if session.action == "update":}}
<td>

{{ =INPUT(_type='text', _id='testtable_testfield1',
_name='testfield1', value="abc", requires=IS_NOT_EMPTY())}}

</td>

[Timmie]

> I'd be interested in:
> *http://www.pytables.org

I asked further at the pytables mailing list:
pytables interface to sqlite?
http://thread.gmane.org/gmane.comp.python.pytables.user/1169

Please read especially the following posts:
* pytables interface to sqlite? - http://article.gmane.org/gmane.comp.python.pytables.user/1171
* pytables interface to sqlite? - http://article.gmane.org/gmane.comp.python.pytables.user/1176

I would like to have a suggestion from the ORM-savvy members (aka
Massimo).
* Is it possible to use pytables with web2py?
I used it by scripts that access stored files.

* Where would I need to start if I want to use pytables in parallel to
the SQL-based database?

Kind regards,
Timmie


> *http://www.gaia-gis.it/spatialite/
>
> Regards,
> Timmie

[dlypka]

The overview mentions C Extensions.
That would be a problem if they are core to the product.

On May 8, 4:48 am, Timmie <timmichel...@gmx-topmail.de> wrote:
> > I'd be interested in:
> > *http://www.pytables.org
>
> I asked further at the pytables mailing list:

> pytables interface to sqlite?http://thread.gmane.org/gmane.comp.python.pytables.user/1169

>
> Please read especially the following posts:

> * pytables interface to sqlite? -http://article.gmane.org/gmane.comp.python.pytables.user/1171
> * pytables interface to sqlite? -http://article.gmane.org/gmane.comp.python.pytables.user/1176

>
> I would like to have a suggestion from the ORM-savvy members (aka
> Massimo).
> * Is it possible to use pytables with web2py?
> I used it by scripts that access stored files.
>
> * Where would I need to start if I want to use pytables in parallel to
> the SQL-based database?
>
> Kind regards,
> Timmie
>
>
>
> > *http://www.gaia-gis.it/spatialite/
>
> > Regards,

[Cory Preus]

> Perhaps some unification between these two - so that applications can be
> portable accross both types - will emerge, and this would be useful.
>
> But I think the first step is to cleanly, intentionally separate the two
> kinds of back ends and see where that leads.

These two statements are key.

1. A separate DAL for GAE/BigTable is needed to harness it. web2py is
too relational specific and not portable to column oriented databases
like BigTable. After going through several data model design
iterations and getting to one that feels correct, web2py imposes the
relational model on a non-relational model, which suggests to me the
"portability" web2py provides to BigTable is in most cases, a good
data model on the wrong data store or vice versa. web2py removes
BigTable's benefit with the current DAL.

2. Patterns will emerge. When another is access layer is created for a
like persistence layer, we should start seeing patterns bubble up and
perhaps then, web2py will become a truer portable architecture.

Meanwhile, a separate DAL feels more correct to me. True portability
between each isn't known or clear; the architecture are not the same
and should not be treated as such.

[mdipierro]

> 1. A separate DAL for GAE/BigTable is needed to harness it. web2py is
> too relational specific and not portable to column oriented databases
> like BigTable. After going through several data model design
> iterations and getting to one that feels correct, web2py imposes the
> relational model on a non-relational model, which suggests to me the
> "portability" web2py provides to BigTable is in most cases, a good
> data model on the wrong data store or vice versa. web2py removes
> BigTable's benefit with the current DAL.

Can you make a concrete example? It will help us improve.

Massimo

[Cory Preus]

> Can you make a concrete example? It will help us improve.

The oft-cited ListProperty is a great example. Because GAE/BigTable
doesn't provide substr support, we have a couple options for a search
facility. One is to take a string, say "foobar" and tokenize it into
parts.

['fo','oo','ba','ar','foo','bar','foob','obar','fooba','oobar','foobar']

Using ListProperty, this would be a single write. Our BigTable model
looks as such:

id name tokens
1 foobar ['fo','oo','ob','ba','ar','foo','bar',...]

And a search query:

# Get all entities where numbers contains a 6.
results = db.GqlQuery("SELECT * FROM MyModel WHERE tokens = 'foo'")

GQL natively returns all entities contain an element in a list.

http://code.google.com/appengine/docs/python/datastore/entitiesandmodels.html#Lists

Conversely, using the oft cited IS_IN_DB pattern for web2py, we would
have 12 rows inserted, one for each token with a column of comma
delimited foreign key ids to each full-string entity matching the
token and a bunch of code to manage the integrity across tokens/
entities instead of what is relevant in this case: tokens *per*
entity. Duplication of a token is irrelevant.

So in this scenario, which is key for the project I'm working on with
a friend, a single SELECT using GAE's API would produce the results
needed versus a series of queries to a) SELECT the token in a tokens
table, then iterate over the elements execute SELECTs on each to get
the representation.

While the ListProperty may be unique to GAE/BigTable, omitting it
removes the ability to harness GAE's power. It is also representative
of the difference between data store paradigms, I think.

Hope this helps.

[mdipierro]

Good point. I think we can and should support listproperty. I also
think its behavior can be mimicked in relational databases. This will
probably happen within 1-2 weeks.

Massimo

On May 24, 12:50 am, Cory Preus <c...@voxmanus.com> wrote:
> > Can you make a concrete example? It will help us improve.
>
> The oft-cited ListProperty is a great example. Because GAE/BigTable
> doesn't provide substr support, we have a couple options for a search
> facility. One is to take a string, say "foobar" and tokenize it into
> parts.
>
> ['fo','oo','ba','ar','foo','bar','foob','obar','fooba','oobar','foobar']
>
> Using ListProperty, this would be a single write. Our BigTable model
> looks as such:
>
>    id name         tokens
>    1  foobar       ['fo','oo','ob','ba','ar','foo','bar',...]
>
> And a search query:
>
>    # Get all entities where numbers contains a 6.
>    results = db.GqlQuery("SELECT * FROM MyModel WHERE tokens = 'foo'")
>
> GQL natively returns all entities contain an element in a list.
>

> http://code.google.com/appengine/docs/python/datastore/entitiesandmod...

[Yarko Tymciurak]

Note that testing of appengengine specific development can / should be able to go on local machines;

I haven't tried it, but I will remind of the announcement in http://googleappengine.blogspot.com/2009/05/web2py-support-new-datastore-backend.html  of the datastore backend - claiming to be more robust than the developer SDK:
http://arachnid.github.com/bdbdatastore/

... surely worth a look, also a way to make "DALnr"  web2py apps (???)  also portable...

[Cory Preus]

Great, thanks for this.

I'm interested in seeing how you mimic ListProperty in a relational
database, Massimo. My gut says it doesn't belong, because it breaks
down normalization and/or it just seems like a proxied data type in a
relational database. Not sure how I feel about it since it seems like
it could be used in lieu of more correct normalization practice in a
relational db, much like my argument against IS_IN_DB above for GAE.
I'm Looking forward to what comes to pass. :)