Ideas: More DAL syntax

[Robin B]

Here are some more backward compatible DAL syntax extensions:

This:
query = db((db.person.last_name=='Obama')&
(db.person.first_name=='Barack'))
query((db.person.party=='Democratic')&(db.person.age>35)).select()
Could also be:
query = db
(db.person.last_name=='Obama',db.person.first_name=='Barack')
query(db.person.party=='Democratic',db.person.age>35).select()
(less noise, fewer ()'s, easier to generate programmatically)

person = db.person(party='Republican')
-> returns a person with default= values populated and **attrs set
person.first_name = 'John'
response.render(person=person)
-> the record can be printed on a page without inserting it in db
person.insert()
-> actually inserts the record, returns 1 for success, 0 for failure,
updates person attrs

person = db.person[person.id]
-> shortcut syntax for db(db.person.id==person.id).select()[0]
db.person[person.id] = db.person(age=lambda attrs: attrs.age+=1)
-> update a record without having to fetch it first
del db.person[person.id]
-> delete a record without having to fetch it first

What do you think?

Robin

[mdipierro]

On Jan 22, 5:35 pm, Robin B <robi...@gmail.com> wrote:
> Here are some more backward compatible DAL syntax extensions:
>
> This:
>   query = db((db.person.last_name=='Obama')&
> (db.person.first_name=='Barack'))
>   query((db.person.party=='Democratic')&(db.person.age>35)).select()
> Could also be:
>   query = db
> (db.person.last_name=='Obama',db.person.first_name=='Barack')
>   query(db.person.party=='Democratic',db.person.age>35).select()
> (less noise, fewer ()'s, easier to generate programmatically)

If I understand, this you can do already

db(...)(...)(...).select(...)

> person = db.person(party='Republican')
> -> returns a person with default= values populated and **attrs set
> person.first_name = 'John'
> response.render(person=person)
> -> the record can be printed on a page without inserting it in db
> person.insert()
> -> actually inserts the record, returns 1 for success, 0 for failure,
> updates person attrs

Not convinced about this one.

> person = db.person[person.id]
> -> shortcut syntax for db(db.person.id==person.id).select()[0]

love this one

> db.person[person.id] = db.person(age=lambda attrs: attrs.age+=1)
> -> update a record without having to fetch it first

this depends on the one above I am not convinced about. One could do
db.person[person.id].update_record(age=...)

> del db.person[person.id]
> -> delete a record without having to fetch it first

love this one too

[Robin B]

Simple queries could be generated from lists of filters, also simple
queries require fewer ()'s:

db(*programmatic_filters).select()
db(programmatic_filters).select()
db(db.person.last_name=='Obama',db.person.first_name=='Barack').select
()

vs

db((db.person.last_name=='Obama')&
(db.person.first_name=='Barack')).select()

and

db(db.person.last_name=='Obama')(db.person.first_name=='Barack').select
()

You have to iteratively build the query from the list, it would be
easier to just pass in the list with or without a * (splat).

The real benefit is that drivers that do not allow "|" (GAE, MEMDB)
could use trivial query parsers, which would make those drivers simple
to write and maintain.

>
> > person = db.person(party='Republican')
> > -> returns a person with default= values populated and **attrs set
> > person.first_name = 'John'
> > response.render(person=person)
> > -> the record can be printed on a page without inserting it in db
> > person.insert()
> > -> actually inserts the record, returns 1 for success, 0 for failure,
> > updates person attrs
>
> Not convinced about this one.

The benefit here is that you have easy access to all the default
values without doing an insert, this become especially useful if
db.Field(default=lamda:foo(123)) becomes possible.

>
> > person = db.person[person.id]
> > -> shortcut syntax for db(db.person.id==person.id).select()[0]
>
> love this one


>
> > db.person[person.id] = db.person(age=lambda attrs: attrs.age+=1)
> > -> update a record without having to fetch it first
>
> this depends on the one above I am not convinced about. One could do
> db.person[person.id].update_record(age=...)

db.person[person.id].update_record(age=...)
-> would require 2 db requests (select,update)

db.person[person.id] = db.person(age=...)
-> would require 1 db request (update)

Since __setitem__(self,key,value) maps to self[key] = value,
if implemented properly in the driver,
this eliminates the redundant db request.

>
> > del db.person[person.id]
> > -> delete a record without having to fetch it first
>
> love this one too

DictMixin could be used to implement a lot of functionality in the
driver:
http://svn.python.org/view/python/trunk/Lib/UserDict.py?view=markup

Robin

[mdipierro]

Could you elaborate on dictmixin?

SQLStorage already extends a dict. Which methods do you think are
missing?

Massimo

[Robin B]

I was just reading UserDict and thought how getitem, setitem, delitem
could be added to db.Table for these:

person = db.person[person.id]
-> shortcut syntax for db(db.person.id==person.id).select()[0]

db.person[person.id] = db.person(age=lambda attrs: attrs.age+=1)
-> update a record without having to fetch it first

del db.person[person.id]
-> delete a record without having to fetch it first

There are more to be had:

len(db.posts) -> count()

for post in db.posts:
post.update_record(...)

Robin

[mdipierro]

I just posted this in trunk following your suggestion.

>>> db=SQLDB()
>>> db.define_table('person',SQLField('name'))
>>> db.person['name']
<gluon.sql.SQLField object at 0x1327510>
>>> db.person.insert(name='Robin')
1
>>> db.person.insert(name='Robin')
2
>>> db.person[1]
<SQLStorage {'update_record': <function <lambda> at 0x13ca930>, 'id':
1, 'name': 'Robin'}>
>>> db.person[2]=dict(name='Tim')
>>> db.person[1]=db.person[2]
>>> db.person[1]
<SQLStorage {'update_record': <function <lambda> at 0x13ca930>, 'id':
1, 'name': 'Tim'}>
>>> del db.person[1]

Beautiful, thank you!

The lambda stuff requires more thought and work.


Massimo

[Robin B]

Session could make use of the new DAL syntax, globals.py:

rows=db(table.id==record_id).select()
-> row = table[record_id]

table._db(table.id==record_id).update(**dd)
-> table[record_id] = dd

Using this syntax session db drivers only need to implement:

insert(),__getitem__,__setitem__,__delitem__

For example MEMDB would not even need a query interface.

Robin

[Robin B]

This could be another possible shortcut for insert:
db.posts[0] = attrs
-> db.posts.insert(**attrs)

then a session driver only requires:
__getitem__,__setitem__,__delitem__

Robin

[mdipierro]

can you elaborate on sesssion?

[Robin B]

Now that db.table supports getitem,setitem,delitem, session can work
by only using the new functions, then session db drivers do not need a
query interface, and can be super simple.

another shortcut:

del db.posts[0] -> drop table

I still think it is useful to get the default values:
db.posts(**attrs)

posts = db.posts
posts[0]=posts(**attrs)

Robin

[Robin B]

Sessions can use its db driver like so:

del db.sessions[0] -> clear table
del db.sessions[id] -> drop row
db.sessions[0] = db.sessions(**attrs) -> insert
db.sessions[id] -> fetch
db.sessions[id] = attrs -> update (could contain lambdas for txn)

For example, if you wanted to make an FSDB db driver to store sessions
on disk:

simply implement:
__call__,__getitem__,__setitem__,__delitem__

Robin

[Robin B]

also:

db.table.field() -> field's default value (calls lambda, returns
value, or None)

Provides some isolation to db.table.field so forms etc do not need to
know if field's default value was a lamda or a static value, it does
not care.

Robin

[mdipierro]

Why are you using db.sessions instead of db.person in the exmples
below? I feel like I am missing something.

> del db.sessions[0] -> clear table

not explicit so dangerous, imagine del db.sessions[id] and id==0 by
mistake? do you really want to clear the table?

> del db.sessions[id] -> drop row

works, in trunk

> db.sessions[0] = db.sessions(**attrs) -> insert

insert must return a id.

> db.sessions[id]  -> fetch

this works already

> db.sessions[id] = attrs -> update (could contain lambdas for txn)

this works but the lambda part is not implemented yet....

Massimo

[Robin B]

I was trying to show that Sessions does not actually use the query
interface (db(table.id==record_id).select()) in really only uses
get,set,del.

> > del db.sessions[0] -> clear table
>
> not explicit so dangerous, imagine del db.sessions[id] and id==0 by
> mistake? do you really want to clear the table?

Very true, this should not be exposed to the user. But if you think
about it the other way,
the lower level driver could implement:
del db.sessions[0]
the high level dal api would call this:
db.sessions.clear() -> del db.sessions[0]

> > del db.sessions[id] -> drop row
>
> works, in trunk
>
> > db.sessions[0] = db.sessions(**attrs) -> insert

the high level dal api would call this:
db.sessions.insert(**attrs)
the lower level driver could implement:
->rec = db.sessions[0] = db.sessions(**attrs)
->if rec and getattr(rec,'id',None):
-> return 1
->return 0


the low level table drivers (the plumbing) could expose the set:
__call__,__getitem__,__setitem__,__delitem__

the high level dal api (the porcelain) could expose the set:
insert(),clear(),...

This way you can add to the high level api without touching every
driver, since the high level api could call the low level driver the
implement the command.

Also, one could create DICTDB which is sufficient to handle sessions
and simple CRUD for tables (insert,fetch,update,delete,all), web2py
could work (sessions and CRUD) out of the box w/o having to install
sqlite, so new users do not need to install sqlite to try a sample
welcome application scaffolding. ;)

Robin

[Robin B]

DICTDB could be implemented as dicts(tables) of dicts(ids) of dicts
(attrs), so these simple databases would be pickle-able, therefore you
could theoretically store little tables inside of regular database
tables, and access them all with the same DAL syntax. I am not sure
why anyone would want to do this, but it would work.

Robin

[Robin B]

Actually storing mini tables inside of tables could be useful for
denormalized databases (GAE) so you can avoid N+1 joins.

Robin

[mdipierro]

I like these ideas. we should have an IRC meetings and discuss it
further.