基于Perl 6与DBIish的预算应用data access layer面向对象设计咨询

嘿，我来帮你捋捋Perl 6里面向对象风格的Data Access Layer（DAL）怎么设计，顺便聊聊值不值得投入的问题～

如果你的预算APP只是个人自用的小工具，初期确实可以不用太纠结“规范”——直接写SQL操作就能满足需求，快速实现核心功能。但如果有以下情况，投入时间做规范的DAL绝对值得：

• 你打算长期维护这个APP，比如后续加新功能（多用户、预算目标、跨设备同步）
• 想让代码更易读、易调试，避免把业务逻辑和SQL语句混在一起
• 希望学习Perl 6的面向对象设计思想，积累工程化经验

我们可以把DAL拆成数据库连接类、实体模型类、数据访问仓库类三个核心部分，再配合业务逻辑层实现报表功能，下面一步步来：

1. 数据库连接类：封装连接与初始化 #

这个类负责管理SQLite连接，用单例模式避免重复创建连接，同时初始化必要的数据库表：

use DBIish;

class DB::Connection {
    has $.handle is rw;
    has static $!instance;

    # 获取单例实例，默认连接到budget.db
    method instance(Str :$db-path = 'budget.db') {
        unless $!instance.defined {
            $!instance = self.new;
            $!instance.connect($db-path);
        }
        $!instance;
    }

    # 建立数据库连接
    method connect(Str $db-path) {
        $.handle = DBIish.connect('SQLite', database => $db-path);
        # 初始化表（如果不存在）
        self._initialize_tables;
    }

    # 初始化交易和分类表
    method _initialize_tables {
        $.handle.execute(q:to/INIT_SQL/);
            CREATE TABLE IF NOT EXISTS transactions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                amount REAL NOT NULL,
                category TEXT NOT NULL,
                transaction_date DATE NOT NULL,
                description TEXT
            );
            CREATE TABLE IF NOT EXISTS categories (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT UNIQUE NOT NULL,
                parent_id INTEGER REFERENCES categories(id)
            );
        INIT_SQL
    }

    # 断开连接
    method disconnect {
        $.handle.disconnect if $.handle.defined;
    }
}

2. 实体模型类：封装数据与验证 #

对应数据库表的实体类，负责封装数据和基础验证逻辑，符合单一职责原则：

class Transaction {
    has Int $.id is rw;
    has Num $.amount is required;
    has Str $.category is required;
    has Date $.transaction-date is required;
    has Str $.description is rw;

    # 验证数据合法性
    method validate {
        die "金额不能为0" if $.amount == 0;
        die "交易日期不能是未来日期" if $.transaction-date > Date.today;
    }
}

class Category {
    has Int $.id is rw;
    has Str $.name is required;
    has Int $.parent-id is rw;

    method validate {
        die "分类名称不能为空" if $.name.trim eq '';
    }
}

3. 数据访问仓库类：专注CRUD操作 #

每个实体对应一个仓库类，负责所有和该实体相关的数据库操作，业务逻辑层只需要调用这些方法，不用关心SQL细节：

class TransactionRepository {
    has DB::Connection $.db;

    method new {
        self.bless(:db(DB::Connection.instance));
    }

    # 创建交易记录
    method create(Transaction $tx) {
        $tx.validate;
        my $stmt = $.db.handle.prepare(q:to/INSERT_SQL/);
            INSERT INTO transactions (amount, category, transaction_date, description)
            VALUES (?, ?, ?, ?)
        INSERT_SQL
        $stmt.execute($tx.amount, $tx.category, $tx.transaction-date.Str, $tx.description);
        $tx.id = $.db.handle.last-insert-rowid.Int;
        return $tx;
    }

    # 根据ID获取交易记录
    method get-by-id(Int $id) {
        my $stmt = $.db.handle.prepare('SELECT * FROM transactions WHERE id = ?');
        my $row = $stmt.execute($id).hash;
        return unless $row;
        Transaction.new(
            id => $row<id>.Int,
            amount => $row<amount>.Num,
            category => $row<category>,
            transaction-date => Date.new($row<transaction_date>),
            description => $row<description>
        );
    }

    # 根据分类和日期范围查询交易
    method get-by-category(Str $category, Date :$start-date, Date :$end-date) {
        my $sql = 'SELECT * FROM transactions WHERE category = ?';
        my @params = $category;
        if $start-date.defined {
            $sql ~= ' AND transaction_date >= ?';
            @params.push($start-date.Str);
        }
        if $end-date.defined {
            $sql ~= ' AND transaction_date <= ?';
            @params.push($end-date.Str);
        }
        my $stmt = $.db.handle.prepare($sql);
        my @transactions;
        for $stmt.execute(@params) -> $row {
            @transactions.push(Transaction.new(
                id => $row<id>.Int,
                amount => $row<amount>.Num,
                category => $row<category>,
                transaction-date => Date.new($row<transaction_date>),
                description => $row<description>
            ));
        }
        return @transactions;
    }

    # 你还可以扩展update、delete、按日期范围查询等方法
}

4. 业务逻辑层：实现报表功能 #

把报表生成等业务逻辑放在单独的类里，调用仓库类的方法获取数据，彻底分离业务和数据访问：

class BudgetService {
    has TransactionRepository $.tx-repo = TransactionRepository.new;

    # 生成月度支出汇总报表
    method monthly-spending-summary(Date $month) {
        my $start-date = Date.new($month.year, $month.month, 1);
        my $end-date = $start-date.later(:month(1)).earlier(:day(1));
        # 查询该月所有交易记录
        my @txs = $.tx-repo.get-by-category(*, :$start-date, :$end-date);
        my %summary;
        for @txs -> $tx {
            # 假设正数为支出，负数为收入
            if $tx.amount > 0 {
                %summary{$tx.category} += $tx.amount;
            }
        }
        return %summary;
    }

    # 生成分类支出趋势报表（最近N个月）
    method category-spending-trend(Str $category, Int $months = 6) {
        my %trend;
        my $current-date = Date.today;
        for ^$months -> $i {
            my $month = $current-date.earlier(:month($i));
            my $start-date = Date.new($month.year, $month.month, 1);
            my $end-date = $start-date.later(:month(1)).earlier(:day(1));
            my @txs = $.tx-repo.get-by-category($category, :$start-date, :$end-date);
            my $total = [+] @txs.grep(*.amount > 0).map(*.amount);
            %trend{$month.Str} = $total;
        }
        return %trend;
    }
}

• 解耦：业务逻辑不依赖具体数据库，以后想换PostgreSQL或者其他数据库，只需要修改仓库类
• 可维护：每个类职责明确，出问题时能快速定位到数据层、业务层还是实体层
• 可测试：可以给仓库类写Mock对象，不用连接真实数据库就能测试业务逻辑
• 扩展性：后续加新功能（比如用户管理、预算目标），只需要新增对应的实体和仓库类

内容的提问来源于stack exchange，提问作者Jonathan Dewein