More experimental stuff in trunk

[mdipierro]

Simple example:

db=DAL('sqlite://test')
db.define_table('purchase',
Field('item'),
Field('unit_price','double'),
Field('quantity','integer'))
db.purchase.insert(item='Box',unit_price=15,quantity=3)
rows=db().select(db.purchase.ALL)

class purchase_meta:
_tablename='purchase'
def __init__(self,tax):
self.tax=tax
def revenues(self):
return
self.purchase.unit_price*self.purchase.quantity*self.tax

rows.meta=purchase_meta(1.07)

for row in rows:
print row.item,
row.unit_price,'*',row.quantity,'*',row.tax,'=',row.revenues

More complex example:

db.define_table('a',Field('n','integer'))
db.define_table('b',Field('n','integer'),Field('a',db.a))
id = db.a.insert(n=4)
for i in range(3,5): db.b.insert(n=i,a=id)
rows=db(db.b.a==db.a.id).select() ### join

class products:
_tablename='c'
def n(self): return self.a.n*self.b.n

rows.meta=products()
for row in rows:
print row.a.n,'*',row.b.n,'=' row.c.n

Any suggestions on improving the syntax? Django can do the same but
only at the level of the individual table. web2py can now to it for
joins!

[mdipierro]

should this thing be called meta? interface? extension?

[Thadeus Burgess]

Not meta, too confusing with django stuff.

How about expression, makes much more sense. That is what it is actually referred to when talking about SQL. Access calls them expressions as well.

-Thadeus

[mdipierro]

we normally user expressions to refer to things like this

db(query).update(field=db.table.field+1)

How about interface?

On Oct 27, 3:05 pm, Thadeus Burgess <thade...@thadeusb.com> wrote:
> Not meta, too confusing with django stuff.
>
> How about expression, makes much more sense. That is what it is actually
> referred to when talking about SQL. Access calls them expressions as well.
>
> -Thadeus
>

[mdipierro]

I am going with "virtualfields"

[Thadeus Burgess]

Basically what we are doing is

SELECT COUNT(*) AS "Number of Orders",
SUM(quantity)AS "Total Number of Items Purchased",
AVG(quantity)AS "Average Number of Items Purchased"




FROM orders;

What is the correct terminology for AS statement? Some research suggest ALIAS is the most accurate term.

I think we should use "alias" or "aliases"

http://www.w3schools.com/sql/sql_alias.asp


-Thadeus

[mdipierro]

No no. We are not doing that.

The new virtualfields are compute by web2py, not by the database.

Massimo

On Oct 27, 3:55 pm, Thadeus Burgess <thade...@thadeusb.com> wrote:
> Basically what we are doing is
>
> SELECT COUNT(*) AS "Number of Orders",
> SUM(quantity)AS "Total Number of Items Purchased",
> AVG(quantity)AS "Average Number of Items Purchased"
>
> FROM orders;
>
> What is the correct terminology for AS statement? Some research suggest
> ALIAS is the most accurate term.
>
> I think we should use "alias" or "aliases"
>
> http://www.w3schools.com/sql/sql_alias.asp
>
> -Thadeus
>

[Thadeus Burgess]

Ok, I was about to stand my ground, however I now agree with you.

virtualfields is fine to me, as long as our DAL gets support for native SQL aggregates (liek SUM, AVG, etc).

And when the DAL gets that, it leaves open the option to assign true aliases that are tied into the SQL query.

aliases = {
   'num_items': ['count', '*'],
   'total_price': ['sum', 'item.price'],
   'average_price': ['avg', 'item.price'],
}
db(db.invoice.id == request.vars.id_invoice).select(alias=aliases)

So i'm okay with virtualfields now :P

-Thadeus

[mdipierro]

On Oct 27, 4:35 pm, Thadeus Burgess <thade...@thadeusb.com> wrote:
> Ok, I was about to stand my ground, however I now agree with you.
>
> virtualfields is fine to me, as long as our DAL gets support for native SQL
> aggregates (liek SUM, AVG, etc).

The DAL does that already. Just not all of them.
It did so for one year. It is in the manual.

for row in db(...).select(db.table.field,db.table.otherfield.sum
(),groupby=db.table.field):
print row.table.field, row._extra[db.table.otherfield.sum()]

db.table.field.sum()
db.table.field.max()
db.table.field.min()
db.table.field.count()

> And when the DAL gets that, it leaves open the option to assign true aliases
> that are tied into the SQL query.
>
> aliases = {
>    'num_items': ['count', '*'],
>    'total_price': ['sum', 'item.price'],
>    'average_price': ['avg', 'item.price'],}
>
> db(db.invoice.id == request.vars.id_invoice).select(alias=aliases)
>
> So i'm okay with virtualfields now :P
>
> -Thadeus
>

[Thadeus Burgess]

I really need to read that thing :)

However, that syntax is chaotic. That row._extra syntax makes me cringe. If it works it works, but I think it could be **better**. Why can it not be something like.

rows = db(query).select(sum(field), avg(field)) # I just psuedocoded this. Like i usually do in emails :)

print "Sum", rows.field.sum
print "Avg", rows.field.avg

for r in rows:
   print r.table.field

-Thadeus

[mdipierro]

I do not like the _extra either and I am thinking of a way to get rid
of it but it is all but chaotic.
Consider this example


db.define_table('a',Field('b','double'))
e=(db.a.b.max()-3)*(db.a.b.min()+5)
rows = db().select(e)
row=rows.first()
print row._extra[e]

the _extra prevents conflicts with table names and field names and
allows the key to be any complex expressions. A simpler notation would
not allow that.

I am considering implementing row(e) to be the same as row._extra[e].

Massimo

On Oct 27, 4:50 pm, Thadeus Burgess <thade...@thadeusb.com> wrote:
> I really need to read that thing :)
>
> However, that syntax is chaotic. That row._extra syntax makes me cringe. If

> it works it works, but I think it could be ***better***. Why can it not be

> something like.
>
> rows = db(query).select(sum(field), avg(field)) # I just psuedocoded this.
> Like i usually do in emails :)
>
> print "Sum", rows.field.sum
> print "Avg", rows.field.avg
>
> for r in rows:
>    print r.table.field
>
> -Thadeus
>

[mdipierro]

_extra literally means anything returned by the query that is not a
table field.

[mdipierro]

In trunk now:

db.define_table('a',Field('b','double'))
e=(db.a.b.max()-3)*(db.a.b.min()+5)
rows = db().select(e)
row=rows.first()
print row._extra[e]

can now be written as:

db.define_table('a',Field('b','double'))
e=(db.a.b.max()-3)*(db.a.b.min()+5)
rows = db().select(e)
row=rows.first()

print row[e]

I am not sure I completely like the implementation of this. I need to
sleep on this. Please send me comments.

Massimo

[Thadeus Burgess]

On the same track that I was thinking. However this does not work like I would expect.

    e = (db.a.b * 10)
    f = (db.a.b.max() * db.a.b.min())
    rows = db(db.a.id > 0).select(db.a.ALL, e, f)
   
    return dict(rows=SQLTABLE(rows))

For something like the above, f would only have one row, and e would be for each row. So what would be nice to work like

print rows.f

for row in rows:
   print row.a, row.e

What about being able to give these a name?

This way, it works like the virtualfields that we're discussing. That way instead of going row[e], you can go row.revenues

However revenues is an Expression like db.a.b * 10.

So in my ealier example for the aliases would change. Make it a python dictionary of Expression objects. with the key being the fieldname.

Fields with only one row (SUM AVG etc..) would be accessible from within the rows object, fields with multiple rows, within the row object

db.define_table('a', Field('b', 'double'))

for i in range(2,6):
   db.a.insert(b=i)

expressions = {
    'taxes': db.a.b * 1.07,
    'max_min': db.a.b.min() * db.a.b.max(),
    'total': db.a.b.sum()
}

db().select(db.a.ALL, exp=expressions)

print rows.max_min
print rows.total

for row in rows:
    print "amt: ", row.b, "   || Taxes: ", row.taxes

Or to keep from clashing table namespace....

row.virtualfields.taxes


All in all, I like the implementation, but it would be nice to be able to name the Expression object (like you can the virtualfields)

-Thadeus

[mdipierro]

On Oct 27, 9:23 pm, Thadeus Burgess <thade...@thadeusb.com> wrote:
> On the same track that I was thinking. However this does not work like I
> would expect.
>
>     e = (db.a.b * 10)
>     f = (db.a.b.max() * db.a.b.min())
>     rows = db(db.a.id > 0).select(db.a.ALL, e, f)
>
>     return dict(rows=SQLTABLE(rows))
>
> For something like the above, f would only have one row, and e would be for
> each row. So what would be nice to work like

you cannot mis db.a.ALL and expressions like f without doing groupby.

> print rows.f
>
> for row in rows:
>    print row.a, row.e
>
> What about being able to give these a name?

It would be

print row.a, row[e]

The first is a table, the second is a formula

> This way, it works like the virtualfields that we're discussing. That way
> instead of going row[e], you can go row.revenues

It is more complicated than that. This would work only there were no
joins. The current approach has the advantage that does not break
joins.

> However revenues is an Expression like db.a.b * 10.
>
> So in my ealier example for the aliases would change. Make it a python
> dictionary of Expression objects. with the key being the fieldname.
>
> Fields with only one row (SUM AVG etc..) would be accessible from within the
> rows object, fields with multiple rows, within the row object
>
> db.define_table('a', Field('b', 'double'))
>
> for i in range(2,6):
>    db.a.insert(b=i)
>
> expressions = {
>     'taxes': db.a.b * 1.07,
>     'max_min': db.a.b.min() * db.a.b.max(),
>     'total': db.a.b.sum()
>
> }
>
> db().select(db.a.ALL, exp=expressions)
>
> print rows.max_min
> print rows.total
>
> for row in rows:
>     print "amt: ", row.b, "   || Taxes: ", row.taxes
>

Let me think about this.

> Or to keep from clashing table namespace....
>
> row.virtualfields.taxes
>
> All in all, I like the implementation, but it would be nice to be able to
> name the Expression object (like you can the virtualfields)

Right now you can but you have to use virtualfields to name the
expressions.

>
> -Thadeus

[mdipierro]

One thing I do not like:

right now we are using a class/object to define the virtualfields but
you are suggesting a dictionary to name aggregates. Should we use a
dictionary for virtualfields too, for example?

rows.virtualfields={'total':(lambda self:
self.sale.unit_price*self.sale.quantity)}

Is this better than?

class test:
def total(self):
return self.sale.unit_price*self.sale.quantity
rows.virtualfields=test()

> ...
>
> read more »

[Thadeus Burgess]

I just finished watching your video about virtualfields.

Its a toss up between the two.

The one thing I really like about the class structure, is being able to pass defaults to it (like tax).

Another thing that I really like about classes, is that when you specify _tablename to something else, it creates a virtual table. This way a virtual table can be created, with access to all of the functions in the class.

Everything that I was worried about being able to do, can be accomplished with virtualfields in classes.

I think using a dictionary instead would be more restrictive. I really like what you settled on in the video.

The only thing that *might* come up in the future, is what if you wanted multiple classes passed to your virtual field, which may or may not ever be needed.

I can see a layout in db.py such as

db.define_table('products', Field('name'), Field('price', 'double'))
db.define_tables('sales', Field('product', db.products), Field('quantity', 'integer'))

qry_product_sales = (db.sales.id_product == db.products.id) & (db.products.id > 0)

class VF_total:
    _tablename = 'calcd'
    def __init__(self, tax): self.tax = tax

    def total_pre_tax: return (self.sales.product.price * self.sales.quantity)
    def sales_tax: return self.total - self.total_pre_tax
    def total: return self.total_pre_tax * self.tax
   
... #later in controllers

all_sales = db(qry_product_sales)

all_sales.virtualfields = VF_total(1.07)

print "Name, Price, Quantity, SubTotal, Tax, Total"
for sale in all_sales:
    print sale.sales.product.name, sale.sales.product.price, sale.sales.quantity, sale.calcd.total_pre_tax, sale.calcd.sales_tax, sale.calcd.total

### Now this is the only thing that I think is missing, aggregates on the rows object. These would be columns that don't necissarily need to be on all rows.
print "Total Gross Sales", all_sales.calcd.total.sum()
print "Average Sale", all_sales.calcd.total.avg()

-Thadeus

[mdipierro]

If I understand. You can do this already:

db.define_table('products', Field('name'), Field('price', 'double'))
db.define_tables('sales', Field('product', db.products), Field
('quantity','integer'))

qry_product_sales = (db.sales.id_product == db.products.id) &
(db.products.id > 0)

class VF_total:
_tablename = 'calcd'
def __init__(self, tax): self.tax = tax

def total_pre_tax(self): return (self.sales.product.price
*self.sales.quantity)
def sales_tax(self): return self.total() - self.total_pre_tax()
def total(self): return self.total_pre_tax() * self.tax

... #later in controllers

all_sales = db(qry_product_sales).select()

all_sales.virtualfields = VF_total(1.07)

print "Name, Price, Quantity, SubTotal, Tax, Total"
for sale in all_sales:
print sale.sales.product.name, sale.sales.product.price,
sale.sales.quantity, sale.calcd.total_pre_tax, sale.calcd.sales_tax,
sale.calcd.total

> ...
>
> read more »

[Thadeus Burgess]

Yes, but I want to be able to apply the virtualfield to every row, like how SUM or AVG would work.

In this example, I would print a spreadsheet view of all sales, and I would want at the very bottom of the report, GROSS TOTAL SALES w/ TAX.

For this to happen, I would need to SUM my virtualfield VF_total.total

-Thadeus