Welcome | Get started | Dive into Lino | Contribute | Reference

Watching database changes

This tutorial explains how to use the lino.modlib.changes plugin for logging changes to individual rows of database tables and implementing a kind of audit trail.

This tutorial is a tested document and part of the Lino test suite. It uses the lino_book.projects.watch sample application:

>>> from lino import startup
>>> startup('lino_book.projects.watch.settings')

To enable database change watching, you add lino.modlib.changes to your get_installed_apps and then register "change watchers" for every type of change you want to watch.

You will also want to make your changes visible for users by adding the changes.ChangesByMaster slave table to some of your detail layouts.

The example in this tutorial uses the lino_xl.lib.contacts module. It also adds a model Entry as an example of a watched model. Imagine some journal entry to be audited.

The "master" of a change watcher is the object to which every change should be attributed. In this example the master is Partner: every change to Entry, Partner or Company will be logged and attributed to their Partner record.

We define our own subclass of Site for this tutorial (which is the recommended way except for very simple examples). Here is the settings.py file:

from lino.projects.std.settings import *


class Site(Site):

    demo_fixtures = "std demo demo2"
    languages = 'en'
    # default_user = "robin"
    user_types_module = 'lino_xl.lib.xl.user_types'

    def get_installed_apps(self):

        yield super(Site, self).get_installed_apps()
        yield 'lino_xl.lib.contacts'
        #~ yield 'lino_xl.lib.notes'
        # yield 'lino.modlib.changes'
        yield 'lino.modlib.users'

        yield 'lino_book.projects.watch.entries'


SITE = Site(globals())
# SITE.user_types_module = None

DEBUG = True

We need to redefine the default list of user types by overriding Site.setup_choicelists() because contacts adds a user group "office", required to see most commands.

Here is our models.py module which defines the Entry model and some few startup event listeners:

from django.db import models
from lino.api import dd
from django.utils.translation import gettext_lazy as _

from lino.modlib.users.mixins import My, UserAuthored


class Entry(UserAuthored):

    class Meta:
        verbose_name = _("Entry")
        verbose_name_plural = _("Entries")

    subject = models.CharField(_("Subject"), blank=True, max_length=200)
    body = dd.RichTextField(_("Body"), blank=True)
    company = dd.ForeignKey('contacts.Company')


class Entries(dd.Table):
    model = Entry

    detail_layout = """
    id user
    company
    subject
    body
    """

    insert_layout = """
    company
    subject
    """


class EntriesByCompany(Entries):
    master_key = 'company'


class MyEntries(My, Entries):
    pass


@dd.receiver(dd.post_startup)
def my_change_watchers(sender, **kw):
    """
    This site watches the changes to Partner, Company and Entry
    """
    self = sender
    
    from lino.utils.watch import watch_changes as wc
    
    # In our example we want to collect changes to Company and Entry
    # objects to their respective Partner.

    wc(self.models.contacts.Partner)
    wc(self.models.contacts.Company, master_key='partner_ptr')
    wc(self.models.entries.Entry, master_key='company__partner_ptr')

    # add two application-specific panels, one to Partners, one to
    # Companies:
    
    self.models.contacts.Companies.add_detail_tab(
        'changes', 'changes.ChangesByMaster')
    self.models.contacts.Companies.add_detail_tab(
        'entries', 'entries.EntriesByCompany')

You can play with this application by cloning the latest development version of Lino, then doing:

$ go watch
$ python manage.py prep
$ python manage.py runserver

TODO: write a demo fixture which reproduces what we are doing in the temporary database during djangotests.

>>> from lino.api.doctest import *
>>> rt.show('changes.Changes')
... 
==== ============= ===================== ===================== =============================================================
 ID   Change Type   Master                Object                Changes
---- ------------- --------------------- --------------------- -------------------------------------------------------------
 1    Create        `My pub <Detail>`__   `My pub <Detail>`__   Company(id=181,language='en',name='My pub',partner_ptr=181)
==== ============= ===================== ===================== =============================================================
>>> rt.show('gfks.BrokenGFKs')
... 
===================== ================= =============================================================== ========
 Database model        Database object   Message                                                         Action
--------------------- ----------------- --------------------------------------------------------------- --------
 `Change <Detail>`__   `#1 <Detail>`__   Invalid primary key 181 for contacts.Company in `object_id`     clear
 `Change <Detail>`__   `#2 <Detail>`__   Invalid primary key 181 for contacts.Company in `object_id`     clear
 `Change <Detail>`__   `#3 <Detail>`__   Invalid primary key 1 for watch_tutorial.Entry in `object_id`   clear
 `Change <Detail>`__   `#4 <Detail>`__   Invalid primary key 1 for watch_tutorial.Entry in `object_id`   clear
 `Change <Detail>`__   `#5 <Detail>`__   Invalid primary key 181 for contacts.Company in `object_id`     clear
 `Change <Detail>`__   `#1 <Detail>`__   Invalid primary key 181 for contacts.Partner in `master_id`     clear
 `Change <Detail>`__   `#2 <Detail>`__   Invalid primary key 181 for contacts.Partner in `master_id`     clear
 `Change <Detail>`__   `#3 <Detail>`__   Invalid primary key 181 for contacts.Partner in `master_id`     clear
 `Change <Detail>`__   `#4 <Detail>`__   Invalid primary key 181 for contacts.Partner in `master_id`     clear
 `Change <Detail>`__   `#5 <Detail>`__   Invalid primary key 181 for contacts.Partner in `master_id`     clear
===================== ================= =============================================================== ========

There open questions regarding these change records:

  • Do we really never want to remove them? Do we really want a nullable master field? Should this option be configurable?

  • How to tell lino.modlib.gfks.models.BrokenGFKs to differentiate them from ?

  • Should get_broken_generic_related suggest to "clear" nullable GFK fields?