Scene 01
A table class
Each table of the model has a table object. db dot table returns it. By default its class is SqlTable. The table argument x_table_class replaces that class with a subclass of SqlTable. A class that does not derive from SqlTable raises TypeError. The methods of this lesson live in such a class. The compiler calls them while it compiles a query. The examples add a class Invoice to the invoice table of the course model, through a layer. Lesson 11 explains layers.
Example & screen notes
Slide title "x_table_class". Code:
from asqueel import SqlTable
class Invoice(SqlTable):
...
invoice = invc.table('invoice', pkey='id', x_table_class=Invoice)
type(db.table('invc.invoice')).__name__ # 'Invoice'
Scene 02
sql_formula=True
When sql_formula is True, the formula text comes from a method. Its name is sql_formula underscore and the column name. It belongs to the table class of the table that owns the formula. The method receives attr, a copy of the column attributes. It returns the SQL expression as a string. It can also add var arguments to attr. Here it reads a discount rate from the environment and binds it as rate. Lesson 17 covers the environment. Without the rate, the discount is zero. With tempEnv set to ten, the totals are ten per cent lower. The method runs at every compilation. A missing method raises UnsupportedFeatureError. An empty result raises ValueError.
Example & screen notes
Slide title "sql_formula_
class Invoice(SqlTable):
def sql_formula_discounted(self, attr):
attr['var_rate'] = self.db.currentEnv.get('discount_rate', 0)
return '$total * (1 - :rate / 100.0)'
invoice.formulaColumn('discounted', sql_formula=True, dtype='N')
with db.tempEnv(discount_rate=10):
rows = db.table('invc.invoice').query(
columns='$inv_number, $total, $discounted', order_by='$inv_number').fetch()
| inv_number | total | discounted |
|---|---|---|
| A-001 | 760.00 | 684.00 |
| A-002 | 20.00 | 18.00 |
Scene 03
formulaColumn_<x>()
A table class can also declare formula columns from Python. Every method whose name starts with formulaColumn underscore is called without arguments. It returns a dictionary, or a list of dictionaries. Each dictionary has a name and the arguments of a formulaColumn. The recipe declares nothing for these columns. A query reads them like any other formula. The results are kept in the current environment, so a tempEnv computes them again. A name equal to a declared column raises ValueError.
Example & screen notes
Slide title "Formula columns from a method". Code:
class Invoice(SqlTable):
def formulaColumn_totals(self):
return [dict(name='total_with_vat', sql_formula='$total + $vat_total', dtype='N'),
dict(name='vat_share', sql_formula='$vat_total / NULLIF($gross_total, 0)', dtype='N')]
db.table('invc.invoice').query(columns='$inv_number, $total_with_vat, $vat_share')
( "t0"."total" + "t0"."vat_total" ) AS "total_with_vat",
( "t0"."vat_total" / NULLIF("t0"."gross_total", 0) ) AS "vat_share"
Scene 04
Sub-selects from a method
select, exists and every select underscore name accept a string instead of a dictionary. The string is the suffix of a method called subquery underscore and the suffix. The method returns the dictionary of the sub-select. The method is looked up on the root table of the query, not on the table that owns the formula. A query on invoices finds it. A query on invoice rows that reads the formula through at invoice_id looks for the method on the row table. It does not find it and raises UnsupportedFeatureError. A sql_formula method, instead, belongs to the table that owns the formula.
Example & screen notes
Slide title "select='
class Invoice(SqlTable):
def subquery_cheapest_row(self):
return dict(table='invc.invoice_row', columns='MIN($unit_price)',
where='$invoice_id=#THIS.id')
invoice.formulaColumn('cheapest_row', select='cheapest_row', dtype='N')
Table:
| Query | Result |
|---|---|
invc.invoice: $inv_number, $cheapest_row |
A-001 80.00, A-002 20.00, A-003 None |
invc.invoice_row: $id, @invoice_id.cheapest_row |
UnsupportedFeatureError: invc.invoice_row: missing table method subquery_cheapest_row() |
Scene 05
#ENV inside a formula
hash ENV reads the environment while the formula is compiled. With the key user in the environment, its value becomes a quoted SQL literal. With a second argument, that key is tried next. When neither key exists, the compiler calls a method env underscore and the name. The method returns SQL text, inserted as it is. Here env_office returns a quoted string. Without user in the environment and without env_user, compilation raises ValueError. The value is written into the SQL text. A compiled query keeps the value of its first compilation. hash ENV works only inside sql_formula. Lesson 15 covers parameters read at every execution.
Example & screen notes
Slide title "#ENV(name[,fallback])". Code:
class Invoice(SqlTable):
def env_office(self):
return "'Head office'"
invoice.formulaColumn('issued_by', sql_formula='#ENV(user)', dtype='T')
invoice.formulaColumn('office', sql_formula='#ENV(office)', dtype='T')
with db.tempEnv(user='alice'):
q = db.table('invc.invoice').query(columns='$inv_number, $issued_by, $office')
( 'alice' ) AS "issued_by",
( 'Head office' ) AS "office"
Scene 06
Recap
A table class carries methods that the compiler calls. They supply formula text, whole formula columns and sub-select dictionaries. hash ENV inlines an environment value or the result of an env method. The next lesson covers sub-query columns and items read from XML documents.
Example & screen notes
Three bullets:
x_table_classattaches aSqlTablesubclass; its methods run during compilationsql_formula_<name>(attr)andformulaColumn_<x>()supply formulas;subquery_<name>()supplies sub-selects, looked up on the root table of the query#ENV(name[,fallback])inlines an environment value or the result ofenv_<name>()