Referential Integrity with Models concepts

Show and Tell
1

I’ve been playing with some ideas for automating referential integrity with models, and wanted to share in case someone else has a use for it, or in case it sparks some ideas.

I wrote a couple of simple functions that can be used to keep single link columns intact (either by deleting the rows they link from, or by preventing the deletion…this could be extended to setting the linked column to None as well).

def restrict_on_delete(row, table, column):
    params = {column: row}
    results = table.search(**params)
    if results:
        raise ChildExists("Child row found, cannot delete parent row")

def cascade_on_delete(row, table, column):
    params = {column: row}
    results = table.search(**params)
    for result in results:
        result.delete()

And then in a model I’d use those in the _do_delete method:

class Category(app_tables.categories.Row, buffered=True, client_updatable=True, client_deletable=True):
    def _do_delete(self, from_client):
        cascade_on_delete(self, app_tables.profile, 'category')
        # restrict_on_delete(self, app_tables.profile, 'category')
        super()._do_delete(from_client)

I’m not super happy with the fact that it’s the Category model that knows about what links to it, but that seems the most straightforward way of handling it.

ChildExists is a custom Exception I registered with Anvil, ala: Handling exception coming from the server/uplink modules - #3

There’d need to be a separate set of functions to handle multiple link columns, but the same approach could be used.

Caution! If you delete something that cascade deletes something else that’s already in a search result somewhere, you’ll get a deleted row exception if you try to save changes to it. That’s to be expected.

5 Likes
2

I’m also looking at how to tackle this. I’m coming to something very similar but I’ll be using a view rather than a search (because I already need the views anyway).

That’s why I’ve asked this one: Model Class Table

3

Using your restrict_on_delete and cascade_on_delete functions, here’s what I’ve got so far:

class Book(app_tables.book.Row, buffered=True, attrs=True, client_writable=True):

    @server_method
    @classmethod
    def get_view(cls):
        return app_tables.book.client_readable()


class Author(app_tables.author.Row, buffered=True, attrs=True, client_writable=True):
    links = [{"class": Book, "column": "author", "on_delete": restrict_on_delete}]

    @server_method
    @classmethod
    def get_view(cls):
        return app_tables.author.client_readable()

    def _do_delete(self, from_client):
        for link in self.links:
            view = link["class"].get_view()
            link["on_delete"](self, view, link["column"])
        super()._do_delete(from_client)
4

I took things a little further…

from anvil.server import portable_class, server_method
from anvil.tables import app_tables

from .exceptions import ChildExists


class LinkedClass:
    def __init__(self, klass, column, on_delete):
        self.klass = klass
        self.column = column
        delete_actions = {
            "restrict": self.restrict_on_delete,
            "cascade": self.cascade_on_delete,
        }
        self.on_delete = delete_actions[on_delete]

    def restrict_on_delete(self, row):
        params = {self.column: row}
        results = self.klass.get_view().search(**params)
        if results:
            raise ChildExists("Child row found, cannot delete parent row")

    def cascade_on_delete(self, row):
        params = {self.column: row}
        self.klass.get_view().search(**params).delete_all_rows()


@portable_class
class WithView:
    table = None

    @server_method
    @classmethod
    def get_view(cls):
        return cls.table.client_readable()


@portable_class
class WithLinks:
    links = []

    def _do_delete(self, from_client):
        for link in self.links:
            link.on_delete(self)
        super()._do_delete(from_client)


class Book(
    WithView, app_tables.book.Row, buffered=True, attrs=True, client_writable=True
):
    table = app_tables.book
    key = "isbn_13"


class Author(
    WithLinks,
    WithView,
    app_tables.author.Row,
    buffered=True,
    attrs=True,
    client_writable=True,
):
    table = app_tables.author
    key = "name"
    links = [LinkedClass(Book, column="author", on_delete="restrict")]
1 Like
5

With that approach, if you also have a _do_delete in Author (for other reasons than referential integrity, perhaps permission checks), you’d need to be sure to call the WithLinks version of it manually. Unless I’m missing something?

Not necessarily a bad thing, just observing something that I might forget to do.

6

WithLinks is deliberately first in the model definition so that, if you write a _do_delete method, calling super in there will call the WithLinks method

1 Like
7

Some time ago, I analyzed what I would need to do to implement the behavior of SQL-style Referential Integrity on top of Anvil’s then-existing features. To summarize those ideas here:

  • Virtually everything can be done if you have event-driven (before, after, instead-of) triggers.
  • Primary/unique keys need to be checked on Create and on Update.
    • Update might attempt to change an old key into an already-existing key.
  • Outbound Foreign keys need to be checked on Create and on Update.
    • Update may have created a new key (from an old one), pointing to a non-existing target.
  • Inbound Foreign keys need to be checked on Update and on Delete.
    • Update may attempt to drop an old key – with inbound references – in the process of creating a new one.
  • All of the above should cause the transaction to fail.
  • On X Restrict does not require nested transactions.
  • On X CASCADE does – or at least a way to keep an “outermost” transaction open until the last ripple-effect is completed.
  • Shared tables had better all agree on the constraints!
    • May be best to keep them in a common dependency.

For performance reasons, these checks are best done server-side only. Single- and multi-column CHECK constraints, however, can be done on both.