Relation actions: Python and SQL

Scene 01

Two families of actions

A relation can carry two kinds of actions. The short names, on delete and on update, are Python actions. The write cycle runs them, with the hooks of the related tables. The names with the suffix underscore SQL are SQL actions. The migration writes them into the foreign key, and the database runs them. Python actions accept raise, cascade, setnull and ignore. SQL actions accept cascade, restrict, no action, setnull and setdefault, and the short forms C, R, N, SN and SD.

Example & screen notes

slide with two columns.

Left, "Python actions — run by the write cycle": - onDelete: raise, cascade, setnull, ignore - onUpdate: same values

Right, "SQL actions — written into the foreign key": - onDelete_sql, onUpdate_sql: cascade, restrict, no action, setnull, setdefault - abbreviations C, R, N, SN, SD

Scene 02

onDelete='raise'

Delete runs delete related before the DELETE statement. For every relation that points to the table, it reads the related rows with a lock. With raise, any related row stops the delete with a delete exception. In the course model, a product used by an invoice row cannot be deleted. You can add your own text after a colon, as in the second example.

Example & screen notes

course model line, then code and error.

tbl.column('product_id', size='22').relation(
    'product.id', relation_name='invoice_rows', onDelete='raise', foreign_key=True)

db.table('invc.product').delete(p['id'])

DeleteException: The record Z0G7qeKFuNPap8Nk00PAdr in table invc.product cannot be deleted:
Record referenced in table invc.invoice_row

Below, the form with a message:

.relation('state.code', relation_name='clients',
          onDelete='raise:Customers live in this state', foreign_key=True)

DeleteException: The record MA in table invc.state cannot be deleted: Customers live in this state

Scene 03

onDelete='cascade'

With cascade, each related row is deleted with the table command delete. So each child runs its own full delete cycle, with its hooks and its own relation actions. The output shows the hook of each invoice row. Its parent operation is the delete of the invoice. The children go first. Then the DELETE of the invoice runs.

Example & screen notes

course model line, then code and output.

tbl.column('invoice_id', size='22').relation(
    'invoice.id', relation_name='rows', onDelete='cascade', foreign_key=True)

class Row(SqlTable):
    def trigger_onDeleting(self, record):
        print('invoice_row trigger_onDeleting', record['id'][-4:],
              'parent', self.currentTrigger.parent.event, self.currentTrigger.parent.table)

db.table('invc.invoice').delete(inv['id'])

invoice_row trigger_onDeleting iXS1 parent delete invc.invoice
invoice_row trigger_onDeleting UYFP parent delete invc.invoice

Scene 04

setnull, ignore and the default

With setnull, each related row is updated with the update command. The foreign key column becomes None, and the old row is passed as old record. So the update hooks of the child run, as the output shows. With ignore, the cycle does nothing for that relation. Then only the foreign key in the database decides. A relation without on delete behaves as ignore.

Example & screen notes

code and output, then two bullets.

.relation('state.code', relation_name='clients', onDelete='setnull', foreign_key=True)

db.table('invc.state').delete('MA')

customer trigger_onUpdating MA -> None
[[account_name=Acme, state=None]]

ignore: no Python action; the foreign key decides
no onDelete: same as ignore

Scene 05

onUpdate and old_record

On update acts when the referenced key of a row changes. Update related runs after the UPDATE statement. It compares the referenced column in the record and in the old record. If the value did not change, there is nothing to do. If it changed, cascade updates each child to the new value, through the update command. Raise stops the update with a save exception. To compare, the cycle needs the old record. Update never reads it for you, as lesson 20 explained. So when any relation with on update points to the table, an update without old record raises a value error. This happens even when the key does not change.

Example & screen notes

code and error.

st = db.table('invc.state')
st.update({'code': 'MA', 'name': 'Mass.'})

ValueError: invc.state: onUpdate of invc.customer.state requires old_record

Bullets: - updateRelated compares the referenced column in record and old_record - unchanged → nothing to do - changed → cascade updates the children, raise stops with SaveException

Scene 06

SQL actions in the foreign key

SQL actions become clauses of the foreign key. C becomes on delete cascade, and R becomes on update restrict. Two defaults matter. Without on update SQL, the foreign key has on update cascade. Without on delete SQL, the foreign key has no on delete clause, so the database refuses to delete a referenced row. Python actions add no SQL clause. One exception: when on delete is setnull, the relation is deferred by default. The foreign key is then checked at commit.

Example & screen notes

addition to the recipe, then the constraints read from PostgreSQL.

def product_tag(pkg):
    t = pkg.table('product_tag', pkey='id')
    t.options().sys_fields()
    t.column('product_id', size='22').relation(
        'product.id', relation_name='tags', foreign_key=True,
        onDelete_sql='C', onUpdate_sql='R')
    t.column('tag')

invc_product_tag   FOREIGN KEY (product_id) REFERENCES invc.invc_product(id)
                   ON UPDATE RESTRICT ON DELETE CASCADE
invc_customer      FOREIGN KEY (customer_type_code) REFERENCES invc.invc_customer_type(code)
                   ON UPDATE CASCADE
invc_customer      FOREIGN KEY (state) REFERENCES invc.invc_state(code)
                   ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED      -- onDelete='setnull'

Bullets: - no onUpdate_sql → ON UPDATE CASCADE - no onDelete_sql → no ON DELETE clause - onDelete or onDelete_sql setnull → deferred=True by default

Scene 07

When both act

Python and SQL actions can both apply to one relation. The database action runs inside the UPDATE statement. Update related runs after it. With the default on update SQL cascade, the database has already moved the children to the new key. Update related then finds no child with the old key, and no Python hook of the children runs. To run the Python cascade with its hooks, set on update SQL to N, no action, and deferred to True. The database then checks the key at commit, and the Python cascade updates the children before that. The output shows the hook of the customer and a successful commit.

Example & screen notes

a timeline for state.update(new, old) with three boxes: UPDATE state → ON UPDATE CASCADE (database) → updateRelated (Python).

Code below:

.relation('state.code', relation_name='clients', onDelete='raise',
          onUpdate='cascade', onUpdate_sql='N', deferred=True, foreign_key=True)

customer trigger_onUpdating MA -> MASS
[[account_name=Acme, state=MASS]]
committed

Scene 08

Recap

On delete and on update are Python actions that run the hooks of the related rows. The SQL suffix names become clauses of the foreign key, with on update cascade by default. An update on a table that is the target of an on update relation needs the old record. The next lesson covers the write commands that skip all of this.

Example & screen notes

three bullets. - onDelete/onUpdate: Python, with the hooks of the related rows - onDelete_sql/onUpdate_sql: clauses of the foreign key; ON UPDATE CASCADE by default - an update on a table referenced with onUpdate needs old_record

Next: "Lesson 24 — Writes without triggers".