Support for callbacks at Table level for create update delete

[sarvesh4396]

Hey, is there support for callbacks at Table level for these crud.
Similar is implemented in rails active record.
Or are there any custom implementation regarding the same. Where I can make a base table inheriting piccolo table and implement the same.

[sinisaos]

@sarvesh4396 At the ORM level, there are callback method, but only for read operations (select and objects queries). Here is possible workaround example. If you use PiccoloCRUD for web api, there is something similar called hooks. At this point we only have pre-event hooks. For post-event hooks there was a PR for it but it never took off. Hope this helps.

[sarvesh4396]

@sinisaos, yeah there are callbacks and hooks for api. I have used them.
Now I want to create a meta table by default which keeps track of number of rows added updated deleted. And calculate the size on runtime.
Is there a way i can do these operations loke by overriding update delete methods?

[sinisaos]

@sarvesh4396 I think the best way is to try to override the save, update and delete methods like @dantownsend did in this example. You can create a table with the data you want to monitor (something like AuditLog from here) that you can populate in the async def run method in example. I haven't tried it, but it should work because every time you make crud operations on your table, your monitor table will be filled with the data you want to track. Sorry if this doesn't help.

[sarvesh4396]

Thanks @sinisaos , I could do it that way.
Or something like in #373. I'll try it soon.

[sinisaos]

@sarvesh4396 I tried some code based on this example and it works. Something like this:

import asyncio
import time
import typing as t
import uuid
from enum import Enum

import uvicorn
from fastapi import FastAPI
from piccolo.apps.user.tables import BaseUser
from piccolo.columns import Real, Text, Timestamp, Varchar
from piccolo.table import Table
from piccolo_admin.endpoints import create_admin
from piccolo_api.session_auth.tables import SessionsBase
from starlette.routing import Mount

from piccolo_conf import DB


class MonitorLog(Table, db=DB):
    class ActionType(str, Enum):
        """An enumeration of MonitorLog table actions type."""

        creating = "creating"
        updating = "updating"
        deleting = "deleting"

    action_time = Timestamp()
    action_type = Varchar(choices=ActionType)
    table_name = Varchar()
    change_message = Text()
    execution_time = Real()

    @classmethod
    async def record_save_action(
        cls,
        table: t.Type[Table],
        execution_time: float,
        new_row_id=t.Union[str, uuid.UUID, int],
    ):
        result = cls(
            action_type=cls.ActionType.creating,
            table_name=table._meta.tablename.title(),
            change_message=f"Create row {new_row_id} in "
            f"{table._meta.tablename.title()} table",
            execution_time=execution_time,
        )
        await result.save().run()

    @classmethod
    async def record_patch_action(
        cls,
        table: t.Type[Table],
        execution_time: float,
        row_id: t.Union[str, uuid.UUID, int],
    ):
        result = cls(
            action_type=cls.ActionType.updating,
            table_name=table._meta.tablename.title(),
            change_message=f"Update row "
            f"{row_id} in {table._meta.tablename.title()} table",
            execution_time=execution_time,
        )
        await result.save().run()

    @classmethod
    async def record_delete_action(
        cls,
        table: t.Type[Table],
        execution_time: float,
        row_id: t.Union[str, uuid.UUID, int],
    ):
        result = cls(
            action_type=cls.ActionType.deleting,
            table_name=table._meta.tablename.title(),
            change_message=f"Delete row {row_id} "
            f"in {table._meta.tablename.title()} table",
            execution_time=execution_time,
        )
        await result.save().run()


class Manager(Table, db=DB):
    name = Varchar(length=100)

    # override save method for create and update objects
    def save(self, columns=None):
        pk = self.id
        save_ = super().save

        class Save:
            async def run(self, *args, **kwargs):
                # create
                if columns is None:
                    start_time = time.time()
                    new_row = await save_(columns=columns).run(*args, **kwargs)
                    end_time = time.time() - start_time

                    await MonitorLog.record_save_action(
                        Manager,
                        new_row_id=new_row[0]["id"],
                        execution_time=end_time,
                    )
                # update
                else:
                    start_time = time.time()
                    await save_(columns=columns).run(*args, **kwargs)
                    end_time = time.time() - start_time

                    await MonitorLog.record_patch_action(
                        Manager,
                        row_id=pk,
                        execution_time=end_time,
                    )

            def __await__(self):
                return self.run().__await__()

        return Save()

    # override remove method for delete objects
    def remove(self):
        pk = self.id
        remove_ = super().remove

        class Remove:
            async def run(self, *args, **kwargs):
                start_time = time.time()
                await remove_().run(*args, **kwargs)
                end_time = time.time() - start_time

                await MonitorLog.record_delete_action(
                    Manager,
                    row_id=pk,
                    execution_time=end_time,
                )

            def __await__(self):
                return self.run().__await__()

        return Remove()


# FastAPI app instantiation and mounting admin
app = FastAPI(
    routes=[
        Mount(
            "/admin/",
            create_admin(
                tables=[Manager, MonitorLog],
            ),
        )
    ],
)


async def main():
    # Tables creating
    await BaseUser.create_table(if_not_exists=True)
    await SessionsBase.create_table(if_not_exists=True)
    await Manager.create_table(if_not_exists=True)
    await MonitorLog.create_table(if_not_exists=True)

    # Creating admin user
    if not await BaseUser.exists().where(BaseUser.email == "admin@test.com"):
        user = BaseUser(
            username="piccolo",
            password="piccolo123",
            email="admin@test.com",
            admin=True,
            active=True,
            superuser=True,
        )
        await user.save()

    # create managers
    for i in range(1, 11):
        manager = Manager(name=f"Manager {i}")
        await manager.save()

    # update manager 3
    manager = await Manager.objects().where(Manager.id == 3).first()
    manager.name = "Manager 3333333"
    await manager.save(columns=[Manager.name])

    # update manager 5
    manager = await Manager.objects().where(Manager.id == 5).first()
    manager.name = "Manager 5555555"
    await manager.save(columns=[Manager.name])

    # delete manager 10
    manager = await Manager.objects().where(Manager.id == 10).first()
    await manager.remove()

    # delete manager 9
    manager = await Manager.objects().where(Manager.id == 9).first()
    await manager.remove()

    print(await MonitorLog.select())


if __name__ == "__main__":
    asyncio.run(main())

    uvicorn.run(app, host="127.0.0.1", port=8000)

I hope you find it useful.

[sarvesh4396]

@sinisaos , It works I have tried to make some changes to retain the history and a custom base table for this.

import typing as t
import uuid
from enum import Enum

from piccolo.columns import  Timestamp, Varchar, Integer, JSON
from piccolo.table import Table
from piccolo.querystring import Unquoted
from piccolo_conf import DB


class ActionType(str, Enum):
    CREATE = "CREATE"
    UPDATE = "UPDATE"
    DELETE = "DELETE"


class Monitor(Table, db=DB):
    action_time = Timestamp()
    action_type = Varchar(choices=ActionType)
    table_name = Varchar()
    row_id = Integer()
    history = JSON()

    @classmethod
    async def get_first(cls, table, row_id):
        return (await table.objects().where(table.id == row_id).first()).to_dict()

    @classmethod
    async def record_save_action(
        cls,
        table: t.Type[Table],
        row_id=t.Union[str, uuid.UUID, int],
    ):
        row = await cls.get_first(table, row_id)
        result = cls(
            action_type=ActionType.CREATE,
            table_name=table._meta.tablename.title(),
            row_id=row_id,
            history=row,
        )
        await result.save().run()

    @classmethod
    async def record_patch_action(
        cls,
        table: t.Type[Table],
        row_id=t.Union[str, uuid.UUID, int],
    ):
        row = await cls.get_first(table, row_id)
        result = cls(
            action_type=ActionType.UPDATE,
            table_name=table._meta.tablename.title(),
            row_id=row_id,
            history=row,
        )
        await result.save().run()

    @classmethod
    async def record_delete_action(
        cls,
        table: t.Type[Table],
        row_id=t.Union[str, uuid.UUID, int],
    ):
        result = cls(
            action_type=ActionType.DELETE,
            table_name=table._meta.tablename.title(),
            row_id=row_id,
        )
        await result.save().run()


class BaseTable(Table, db=DB):
    __monitor__ = False

    # override save method for create and update objects
    def save(self, columns=None):
        table = self.__class__
        pk = self.id

        save_ = super().save
        monitor = self.__monitor__

        class Save:
            async def run(self, *args, **kwargs):
                saved = await save_(columns=columns).run(*args, **kwargs)
                print(columns)

                if monitor:

                    if isinstance(pk, Unquoted):  # create null

                        await Monitor.record_save_action(table, saved[0]["id"])
                    else:
                        await Monitor.record_patch_action(table, pk)

            def __await__(self):
                return self.run().__await__()

        return Save()

    # override remove method for delete objects
    def remove(self):
        pk = self.id
        table = self.__class__
        monitor = self.__monitor__

        remove_ = super().remove

        class Remove:
            async def run(self, *args, **kwargs):

                await remove_().run(*args, **kwargs)

                if monitor:
                    await Monitor.record_delete_action(
                        table,
                        row_id=pk,
                    )

            def __await__(self):
                return self.run().__await__()

        return Remove()


class Manager(BaseTable, db=DB):
    name = Varchar(length=100)
    __monitor__ = True


await Manager.create_table(if_not_exists=True)
await Monitor.create_table(if_not_exists=True)

await Manager(name="Sarvesh").save()

managers = await Manager.objects()
managers[0].name = "Updated Name"
await managers[0].save()
await managers[0].remove()


Monitor.select().run_sync()

Monitor will keep all records of actions with history.
Try to run it in notebook.

[sinisaos]

@sarvesh4396 I tried your changes and they work great. I'm glad you found my example useful and successfully modified it to suit your needs. Cheers.