SoftDeleteMixin and SoftDeleteTokenMixin handle soft deletes automaticly by maintaining the created_at, updated_at and deleted_at columns

SoftDeleteMixin

core_lib.data_layers.data.db.sqlalchemy.mixins.soft_delete_mixin.SoftDeleteMixin [source]

SoftDeleteMixin creates the following columns in a table.

  • created_at:
    • type: DateTime
    • default: datetime.utcnow
  • updated_at:
    • type: DateTime
    • default: datetime.utcnow
    • onupdate: datetime.utcnow immediately updates when the system changes the value of deleted_at.
  • deleted_at:
    • type: DateTime
    • default: None
    • When a user wishes to execute a soft deletion on a column, this field must be updated by the value datetime.utcnow.
class SoftDeleteMixin(object):
    created_at = Column(DateTime, default=datetime.utcnow)
    created_at._creation_order = 9998
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    updated_at._creation_order = 9998
    deleted_at = Column(DateTime, default=None)
    deleted_at._creation_order = 9998

SoftDeleteTokenMixin

core_lib.data_layers.data.db.sqlalchemy.mixins.soft_delete_token_mixin.SoftDeleteTokenMixin [source]

SoftDeleteTokenMixin creates the following column in a table. Which can be used for Indexing, If the user wants the deleted_at column to be indexed, it’s best to use delete_token because indexing on a DateTime column is slow.

Note: If the user does not want indexing on deleted_at, this class can be skipped and the delete_token column will not be created.

  • delete_token:
    • type: Integer
    • default: 0
    • When a user updates the deleted_at column, delete_token should also be updated with the value int(datetime.utcnow) to store the Integer value of the current timestamp which can be used for indexing.
class SoftDeleteTokenMixin(object):
    delete_token = Column(Integer, default=0)
    delete_token._creation_order = 9998

Example

from core_lib.data_layers.data.db.sqlalchemy.base import Base
from core_lib.data_layers.data.db.sqlalchemy.mixins.soft_delete_mixin import SoftDeleteMixin
from core_lib.data_layers.data.db.sqlalchemy.mixins.soft_delete_token_mixin import SoftDeleteTokenMixin

from sqlalchemy import Integer, Column, VARCHAR
from datetime import datetime

class Data(Base, SoftDeleteMixin, SoftDeleteTokenMixin):

    __tablename__ = 'user_data'

    id = Column(Integer, primary_key=True, nullable=False)
    name = Column(VARCHAR(length=255), nullable=False, default="")

  def delete_user():
    # to carry out soft delete, once the deleted_at column is set we know the value is deleted from client side.
    session.query(Data).filter(Data.id == 1).update({'deleted_at': datetime.utcnow(), 'deleted_at_token':  int(datetime.utcnow().timestamp())})