软删除（Soft Delete）的实现与恢复逻辑

ALTER TABLE users ADD COLUMN is_deleted BOOLEAN DEFAULT FALSE;

-- 软删除操作
UPDATE users SET is_deleted = TRUE WHERE id = 1;

-- 查询未删除的数据
SELECT * FROM users WHERE is_deleted = FALSE;

from sqlalchemy import Column, Boolean, DateTime
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    is_deleted = Column(Boolean, default=False)
    deleted_at = Column(DateTime, default=None)

    @classmethod
    def get_active_users(cls, session):
        return session.query(cls).filter_by(is_deleted=False).all()

# 使用示例
active_users = User.get_active_users(session)

-- 恢复数据
UPDATE users SET is_deleted = FALSE, deleted_at = NULL WHERE id = 1;

class UserService:
    def restore_user(self, user_id):
        user = session.query(User).filter_by(id=user_id, is_deleted=True).first()
        if user:
            user.is_deleted = False
            user.deleted_at = None
            # 恢复与用户相关的其他记录
            OrderService.restore_user_orders(user_id)
            session.commit()
            return True
        return False

class OrderService:
    @staticmethod
    def restore_user_orders(user_id):
        orders = session.query(Order).filter_by(user_id=user_id, is_deleted=True).all()
        for order in orders:
            order.is_deleted = False
            order.deleted_at = None
        session.commit()