axiom-sqlitedata-ref

SQLiteData 进阶参考

概述

Point-Free 的 SQLiteData 的进阶查询模式和架构组合技巧。基于 GRDB 和 StructuredQueries 构建。

核心模式（CRUD、CloudKit 设置、@Table 基础知识）请参阅 axiom-sqlitedata discipline skill。

本参考涵盖进阶查询、架构组合、视图和自定义聚合函数。

要求 iOS 17+、Swift 6 严格并发 框架 SQLiteData 1.4+

---

列组和架构组合

SQLiteData 提供了强大的架构类型组合工具，可实现复用、更好的组织，以及单表继承模式。

列组

使用 @Selection 将相关列组合成可复用类型：

// Define a reusable column group
@Selection
struct Timestamps {
    let createdAt: Date
    let updatedAt: Date?
}

// Use in multiple tables
@Table
nonisolated struct RemindersList: Identifiable {
    let id: UUID
    var title = ""
    let timestamps: Timestamps  // Embedded column group
}

@Table
nonisolated struct Reminder: Identifiable {
    let id: UUID
    var title = ""
    var isCompleted = false
    let timestamps: Timestamps  // Same group, reused
}

重要提示： SQLite 没有列组的概念。请在 CREATE TABLE 中展开所有分组：

CREATE TABLE "remindersLists" (
    "id" TEXT PRIMARY KEY NOT NULL DEFAULT (uuid()),
    "title" TEXT NOT NULL DEFAULT '',
    "createdAt" TEXT NOT NULL,
    "updatedAt" TEXT
) STRICT

查询列组

使用点语法访问组中的字段：

// Query a field inside the group
RemindersList
    .where { $0.timestamps.createdAt <= cutoffDate }
    .fetchAll(db)

// Compare entire group (flattens to tuple in SQL)
RemindersList
    .where {
        $0.timestamps <= Timestamps(createdAt: date1, updatedAt: date2)
    }

在 @Selection 中嵌套组

在自定义查询结果中使用列组：

@Selection
struct Row {
    let reminderTitle: String
    let listTitle: String
    let timestamps: Timestamps  // Nested group
}

let results = try Reminder
    .join(RemindersList.all) { $0.remindersListID.eq($1.id) }
    .select {
        Row.Columns(
            reminderTitle: $0.title,
            listTitle: $1.title,
            timestamps: $0.timestamps  // Pass entire group
        )
    }
    .fetchAll(db)

使用枚举实现单表继承

使用 @CasePathable @Selection 枚举建模多态数据——这是类继承的值类型替代方案：

import CasePaths

@Table
nonisolated struct Attachment: Identifiable {
    let id: UUID
    let kind: Kind

    @CasePathable @Selection
    enum Kind {
        case link(URL)
        case note(String)
        case image(URL)
    }
}

注意： @CasePathable 是必需的，来自 Point-Free 的 CasePaths 库。

枚举表的 SQL 架构

将所有 case 展开为可空列：

CREATE TABLE "attachments" (
    "id" TEXT PRIMARY KEY NOT NULL DEFAULT (uuid()),
    "link" TEXT,
    "note" TEXT,
    "image" TEXT
) STRICT

查询枚举表

// Fetch all — decoding determines which case
let attachments = try Attachment.all.fetchAll(db)

// Filter by case
let images = try Attachment
    .where { $0.kind.image.isNot(nil) }
    .fetchAll(db)

插入枚举值

try Attachment.insert {
    Attachment.Draft(kind: .note("Hello world!"))
}
.execute(db)
// Inserts: (id, NULL, 'Hello world!', NULL)

更新枚举值

try Attachment.find(id).update {
    $0.kind = #bind(.link(URL(string: "https://example.com")!))
}
.execute(db)
// Sets link column, NULLs note and image columns

使用分组列的复杂枚举 case

枚举 case 可以使用嵌套的 @Selection 类型保存结构化数据：

@Table
nonisolated struct Attachment: Identifiable {
    let id: UUID
    let kind: Kind

    @CasePathable @Selection
    enum Kind {
        case link(URL)
        case note(String)
        case image(Attachment.Image)  // Fully qualify nested types
    }

    @Selection
    struct Image {
        var caption = ""
        var url: URL
    }
}

SQL 架构会展开所有嵌套字段：

CREATE TABLE "attachments" (
    "id" TEXT PRIMARY KEY NOT NULL DEFAULT (uuid()),
    "link" TEXT,
    "note" TEXT,
    "caption" TEXT,
    "url" TEXT
) STRICT

将行传递给数据库函数

借助列组，@DatabaseFunction 可以接收整行表数据：

@DatabaseFunction
func isPastDue(reminder: Reminder) -> Bool {
    !reminder.isCompleted && reminder.dueDate < Date()
}

// Use in queries — columns are flattened/reconstituted automatically
let pastDue = try Reminder
    .where { $isPastDue(reminder: $0) }
    .fetchAll(db)

列组与 SwiftData 继承的比较

方式	SQLiteData	SwiftData
类型	值类型（枚举/结构体）	引用类型（类）
完备性	编译器强制检查 switch	运行时类型检查
冗长程度	简洁的枚举 case	冗长的类层次结构
继承	通过枚举实现单表继承	@Model 类继承
可复用列	@Selection 分组	手动重复

SwiftData 对应写法（更冗长）：

@Model class Attachment { var isActive: Bool }
@Model class Link: Attachment { var url: URL }
@Model class Note: Attachment { var note: String }
@Model class Image: Attachment { var url: URL }
// Each needs explicit init calling super.init

---

查询组合

将可复用作用域构建为静态属性/方法：

extension Item {
    static let active = Item.where { !$0.isArchived && !$0.isDeleted }
    static let inStock = Item.where(\.isInStock)

    static func createdAfter(_ date: Date) -> Where<Item> {
        Item.where { $0.createdAt > date }
    }
}

// Chain scopes
let results = try Item.active.inStock.order(by: \.title).fetchAll(db)

// Use as base for @FetchAll
@FetchAll(Item.active) var items

扩展 Where<Item> 以添加可组合筛选条件：

extension Where<Item> {
    func matching(_ search: String) -> Where<Item> {
        self.where { $0.title.contains(search) || $0.notes.contains(search) }
    }
}
let results = try Item.inStock.matching(searchText).fetchAll(db)

---

使用 @Fetch 自定义获取请求

当需要在单个读取事务中获取多项数据时，使用 @Fetch（单表查询请使用 @FetchAll/@FetchOne）：

struct DashboardRequest: FetchKeyRequest {
    struct Value: Sendable {
        let totalItems: Int
        let activeItems: [Item]
        let categories: [Category]
    }

    func fetch(_ db: Database) throws -> Value {
        try Value(
            totalItems: Item.count().fetchOne(db) ?? 0,
            activeItems: Item.where { !$0.isArchived }.order(by: \.updatedAt.desc()).limit(10).fetchAll(db),
            categories: Category.order(by: \.name).fetchAll(db)
        )
    }
}

@Fetch(DashboardRequest()) var dashboard

使用 .load() 动态加载：

@Fetch var results = SearchRequest.Value()

.task(id: query) {
    try? await $results.load(SearchRequest(query: query), animation: .default)
}

主要优势：原子读取、自动观察、类型安全的结果。

---

进阶查询模式

字符串函数

函数	用法	SQL
upper() / lower()	$0.title.upper()	UPPER/LOWER
trim() / ltrim() / rtrim()	$0.title.trim()	TRIM
substr(start, len)	$0.title.substr(0, 3)	SUBSTR
replace(old, new)	$0.title.replace("old", "new")	REPLACE
length()	$0.title.length()	LENGTH
instr(search)	$0.title.instr("search") > 0	INSTR
like(pattern)	$0.title.like("%phone%")	LIKE
hasPrefix / hasSuffix / contains	$0.title.contains("Max")	Swift 风格
collate(.nocase)	$0.title.collate(.nocase).eq(#bind("X"))	COLLATE

空值处理

// Coalesce — first non-null value
let name = try User.select { $0.nickname ?? $0.firstName ?? "Anonymous" }.fetchAll(db)

// Null checks
let withDue = try Reminder.where { $0.dueDate.isNot(nil) }.fetchAll(db)
let noDue = try Reminder.where { $0.dueDate.is(nil) }.fetchAll(db)

// Null-safe ordering
let sorted = try Item.order { $0.priority.desc(nulls: .last) }.fetchAll(db)

范围和集合成员关系

// IN (set or subquery)
let selected = try Item.where { $0.id.in(selectedIds) }.fetchAll(db)
let inActive = try Item.where { $0.categoryID.in(
    Category.where(\.isActive).select(\.id)
)}.fetchAll(db)

// NOT IN
let excluded = try Item.where { !$0.id.in(excludedIds) }.fetchAll(db)

// BETWEEN (or Swift range syntax)
let midRange = try Item.where { $0.price.between(10, and: 100) }.fetchAll(db)

分页

// Offset-based
let items = try Item.order(by: \.createdAt).limit(20, offset: page * 20).fetchAll(db)

// Cursor-based (more efficient for deep pages)
let items = try Item.where { $0.id > lastSeenId }.order(by: \.id).limit(20).fetchAll(db)

去重结果

let categories = try Item.select(\.category).distinct().fetchAll(db)

---

RETURNING 子句

获取 INSERT、UPDATE 或 DELETE 操作生成的值：

// Insert and get auto-generated ID
let newId = try Item.insert { Item.Draft(title: "New Item") }
    .returning(\.id).fetchOne(db)

// Update and return new values
let updates = try Item.find(id).update { $0.count += 1 }
    .returning { ($0.id, $0.count) }.fetchOne(db)

// Capture deleted records before removal
let deleted = try Item.where { $0.isArchived }.delete()
    .returning(Item.self).fetchAll(db)

使用 RETURNING 可避免为自动生成的 ID 再执行一次查询、审计删除操作或验证更新。

---

联接

连接类型

// INNER JOIN — only matching rows
let items = try Item.join(Category.all) { $0.categoryID.eq($1.id) }.fetchAll(db)

// LEFT JOIN — all from left, matching from right (nullable)
let items = try Item.leftJoin(Category.all) { $0.categoryID.eq($1.id) }
    .select { ($0, $1) }  // (Item, Category?)
    .fetchAll(db)

此外还支持：.rightJoin()（保留右表中的所有行）和 .fullJoin()（保留两表中的所有行）。

多表连接可以自然地串联：

extension Reminder {
    static let withTags = group(by: \.id)
        .leftJoin(ReminderTag.all) { $0.id.eq($1.reminderID) }
        .leftJoin(Tag.all) { $1.tagID.eq($2.primaryKey) }
}

使用 TableAlias 进行自连接

struct ManagerAlias: TableAlias { typealias Table = Employee }

let employeesWithManagers = try Employee
    .leftJoin(Employee.all.as(ManagerAlias.self)) { $0.managerID.eq($1.id) }
    .select { (employeeName: $0.name, managerName: $1.name) }
    .fetchAll(db)

---

CASE 表达式

// Simple case — map values
let labels = try Item.select {
    Case($0.priority).when(1, then: "Low").when(2, then: "Medium")
        .when(3, then: "High").else("Unknown")
}.fetchAll(db)

// Searched case — boolean conditions
let status = try Order.select {
    Case().when($0.shippedAt.isNot(nil), then: "Shipped")
        .when($0.paidAt.isNot(nil), then: "Paid").else("Unknown")
}.fetchAll(db)

// Case in updates (toggle pattern)
try Reminder.find(id).update {
    $0.status = Case($0.status)
        .when(#bind(.incomplete), then: #bind(.completing))
        .when(#bind(.completing), then: #bind(.completed))
        .else(#bind(.incomplete))
}.execute(db)

---

公用表表达式（CTE）

非递归 CTE

// Single CTE
let expensiveItems = try With {
    Item.where { $0.price > 1000 }
} query: { expensive in
    expensive.order(by: \.price).limit(10)
}.fetchAll(db)

// Multiple CTEs
let report = try With {
    Customer.where { $0.totalSpent > 10000 }
} with: {
    Order.where { $0.createdAt > lastMonth }
} query: { highValue, recentOrders in
    highValue.join(recentOrders) { $0.id.eq($1.customerID) }
        .select { ($0.name, $1.total) }
}.fetchAll(db)

使用 CTE 将复杂查询拆分为易读的部分、复用子查询，或改进查询计划。

递归 CTE

查询层级数据（树、组织架构图、串联评论）：

@Table
nonisolated struct Category: Identifiable {
    let id: UUID
    var name = ""
    var parentID: UUID?  // Self-referential
}

// Get all descendants of a root category
let allDescendants = try With {
    Category.where { $0.id.eq(#bind(rootCategoryId)) }  // Base case
} recursiveUnion: { cte in
    Category.all.join(cte) { $0.parentID.eq($1.id) }.select { $0 }  // Recursive case
} query: { cte in
    cte.order(by: \.name)
}.fetchAll(db)

反转连接条件（$0.id.eq($1.parentID)），即可向上而不是向下遍历树。

---

全文搜索（FTS5）

FTS5 基础

@Table
struct ReminderText: FTS5 {
    let rowid: Int
    let title: String
    let notes: String
    let tags: String
}

// Create FTS table in migration
try #sql(
    """
    CREATE VIRTUAL TABLE "reminderTexts" USING fts5(
        "title", "notes", "tags",
        tokenize = 'trigram'
    )
    """
)
.execute(db)

FTS5 高级功能

// Highlight search terms
let results = try ItemText.where { $0.match(query) }
    .select { ($0.rowid, $0.title.highlight("<b>", "</b>")) }.fetchAll(db)

// Snippets with context
let snippets = try ItemText.where { $0.match(query) }
    .select { $0.description.snippet("<b>", "</b>", "...", 64) }.fetchAll(db)

// BM25 relevance ranking
let ranked = try ItemText.where { $0.match(query) }
    .order { $0.bm25().desc() }.fetchAll(db)

---

聚合

字符串和 JSON 聚合

// groupConcat — comma-separated tags per item
let itemsWithTags = try Item.group(by: \.id)
    .leftJoin(ItemTag.all) { $0.id.eq($1.itemID) }
    .leftJoin(Tag.all) { $1.tagID.eq($2.id) }
    .select { ($0.title, $2.name.groupConcat(separator: ", ")) }
    .fetchAll(db)
// ("iPhone", "electronics, mobile, apple")

// jsonGroupArray — aggregate into JSON array
let itemsJson = try Store.group(by: \.id)
    .leftJoin(Item.all) { $0.id.eq($1.storeID) }
    .select { ($0.name, $1.title.jsonGroupArray()) }
    .fetchAll(db)

选项：.groupConcat(distinct: true)、.groupConcat(order: { $0.asc() })、.jsonGroupArray(filter: $1.isActive)、jsonObject("key", $0.value)。

条件聚合

所有聚合函数都接受 filter: 参数：

let stats = try Item.select {
    Stats.Columns(
        total: $0.count(),
        activeCount: $0.count(filter: $0.isActive),
        avgActivePrice: $0.price.avg(filter: $0.isActive),
        totalRevenue: $0.revenue.sum(filter: $0.status.eq(#bind(.completed)))
    )
}.fetchOne(db)

HAVING 子句

.where() 在分组前筛选行；.having() 在聚合后筛选分组：

let frequentCustomers = try Customer.group(by: \.id)
    .leftJoin(Order.all) { $0.id.eq($1.customerID) }
    .having { $1.count() > 5 }
    .select { ($0.name, $1.count()) }
    .fetchAll(db)

---

使用 #sql 宏创建架构

#sql 宏支持用于创建架构和迁移的类型安全原始 SQL。

CREATE TABLE

migrator.registerMigration("Create initial tables") { db in
    try #sql("""
        CREATE TABLE "items" (
            "id" TEXT PRIMARY KEY NOT NULL DEFAULT (uuid()),
            "title" TEXT NOT NULL DEFAULT '',
            "isInStock" INTEGER NOT NULL DEFAULT 1,
            "price" REAL NOT NULL DEFAULT 0.0,
            "createdAt" TEXT NOT NULL DEFAULT (datetime('now'))
        ) STRICT
        """).execute(db)
}

参数插值

• \(value) → 自动转义（可安全用于用户输入）
• \(raw: value) → 按字面插入（仅用于你可控的标识符）
• 切勿使用 \(raw: userInput)——这会导致 SQL 注入漏洞

其他 DDL

// CREATE INDEX (with optional WHERE for partial indexes)
try #sql("""CREATE INDEX "idx_items_search" ON "items" ("title") WHERE "isArchived" = 0""").execute(db)

// CREATE TRIGGER
try #sql("""
    CREATE TRIGGER "update_timestamp" AFTER UPDATE ON "items"
    BEGIN UPDATE "items" SET "updatedAt" = datetime('now') WHERE "id" = NEW."id"; END
    """).execute(db)

// ALTER TABLE
try #sql("""ALTER TABLE "items" ADD COLUMN "notes" TEXT NOT NULL DEFAULT ''""").execute(db)

使用 #sql 编写 DDL（CREATE、ALTER、索引、触发器）。常规 CRUD 操作请使用查询构建器。

外键关系

migrator.registerMigration("Create tables with foreign keys") { db in
    try #sql("""
        CREATE TABLE "itemCategories" (
            "itemID" TEXT NOT NULL REFERENCES "items"("id") ON DELETE CASCADE,
            "categoryID" TEXT NOT NULL REFERENCES "categories"("id") ON DELETE CASCADE,
            PRIMARY KEY ("itemID", "categoryID")
        ) STRICT
        """).execute(db)
}

重要：启用外键约束——SQLite 默认禁用此功能：

var configuration = Configuration()
configuration.prepareDatabase { db in
    try db.execute(sql: "PRAGMA foreign_keys = ON")
}

如果没有 PRAGMA foreign_keys = ON，REFERENCES 和 ON DELETE CASCADE 会被静默忽略。

批量操作的事务上下文

将批量操作包装在显式事务中，以保证原子性并提升性能：

try database.write { db in
    // All operations share one transaction
    for item in items {
        try Item.insert { Item.Draft(title: item.title) }.execute(db)
    }
}
// Commits once on success, rolls back entirely on failure

database.write { } 代码块本身就是一个事务。对于以读取为主的批量分析，请使用 database.read { }，它能提供一致的快照。

---

数据库视图

使用 @Selection 获取自定义查询结果

@Selection 会生成 .Columns 类型，以便在编译时验证查询结果：

@Selection
struct ReminderWithList: Identifiable {
    var id: Reminder.ID { reminder.id }
    let reminder: Reminder
    let remindersList: RemindersList
}

@FetchAll(
    Reminder.join(RemindersList.all) { $0.remindersListID.eq($1.id) }
        .select { ReminderWithList.Columns(reminder: $0, remindersList: $1) }
)
var reminders: [ReminderWithList]

它也适用于聚合查询——请参阅上文的“条件聚合”部分。

临时视图

对于可复用的复杂查询，可组合使用 @Table @Selection 和 createTemporaryView：

@Table @Selection
private struct ReminderWithList {
    let reminderTitle: String
    let remindersListTitle: String
}

try database.write { db in
    try ReminderWithList.createTemporaryView(
        as: Reminder.join(RemindersList.all) { $0.remindersListID.eq($1.id) }
            .select { ReminderWithList.Columns(reminderTitle: $0.title, remindersListTitle: $1.title) }
    ).execute(db)
}

// Query like a table — join complexity hidden
let results = try ReminderWithList.order { ($0.remindersListTitle, $0.reminderTitle) }.fetchAll(db)

临时视图的生命周期与连接一致。对于持久视图，请在迁移中使用 #sql("CREATE VIEW ...")。

若要使视图可写，可添加 createTemporaryTrigger(insteadOf: .insert { ... })，将操作转发到底层表。

---

自定义聚合函数

使用 @DatabaseFunction 在 Swift 中编写复杂聚合，避免编写曲折的 SQL 子查询：

// 1. Define — takes Sequence<T?>, returns aggregate result
@DatabaseFunction
func mode(priority priorities: some Sequence<Reminder.Priority?>) -> Reminder.Priority? {
    var occurrences: [Reminder.Priority: Int] = [:]
    for priority in priorities {
        guard let priority else { continue }
        occurrences[priority, default: 0] += 1
    }
    return occurrences.max { $0.value < $1.value }?.key
}

// 2. Register
configuration.prepareDatabase { db in db.add(function: $mode) }

// 3. Use in queries
let results = try RemindersList.group(by: \.id)
    .leftJoin(Reminder.all) { $0.id.eq($1.remindersListID) }
    .select { ($0.title, $mode(priority: $1.priority)) }
    .fetchAll(db)

常见用途：众数、中位数、加权平均、自定义筛选。函数在 Swift 中运行（而非 SQLite 的 C 引擎中），因此尽可能使用内置聚合函数（count、sum、avg、min、max）。

---

批量 Upsert 性能

对于高吞吐量同步（50K+ 条记录），请使用缓存语句，而非类型安全 API：

func batchUpsert(_ items: [Item], in db: Database) throws {
    let statement = try db.cachedStatement(sql: """
        INSERT INTO items (id, name, libraryID, remoteID, updatedAt)
        VALUES (?, ?, ?, ?, ?)
        ON CONFLICT(libraryID, remoteID) DO UPDATE SET
            name = excluded.name, updatedAt = excluded.updatedAt
        WHERE excluded.updatedAt >= items.updatedAt
        """)
    for item in items {
        try statement.execute(arguments: [item.id, item.name, item.libraryID, item.remoteID, item.updatedAt])
    }
}

若要获得更高吞吐量，可构建多行 VALUES 子句。在运行时查询变量上限：sqlite3_limit(db.sqliteConnection, SQLITE_LIMIT_VARIABLE_NUMBER, -1)（iOS 14+ 为 32,766，iOS 13 为 999）。

模式	吞吐量	权衡
类型安全 upsert	~1K 行/秒	开发体验最佳，具备编译时检查
缓存语句	~10K 行/秒	平衡性好
多行 VALUES	~50K 行/秒	最复杂

---

其他高级模式

数据库触发器

try database.write { db in
    try Reminder.createTemporaryTrigger(
        after: .insert { new in
            Reminder
                .find(new.id)
                .update {
                    $0.position = Reminder.select { ($0.position.max() ?? -1) + 1 }
                }
        }
    )
    .execute(db)
}

自定义更新逻辑

extension Updates<Reminder> {
    mutating func toggleStatus() {
        self.status = Case(self.status)
            .when(#bind(.incomplete), then: #bind(.completing))
            .else(#bind(.incomplete))
    }
}

// Usage
try Reminder.find(reminder.id).update { $0.toggleStatus() }.execute(db)

枚举支持

enum Priority: Int, QueryBindable {
    case low = 1
    case medium = 2
    case high = 3
}

enum Status: Int, QueryBindable {
    case incomplete = 0
    case completing = 1
    case completed = 2
}

@Table
nonisolated struct Reminder: Identifiable {
    let id: UUID
    var priority: Priority?
    var status: Status = .incomplete
}

复合查询

// UNION (deduplicated), UNION ALL (keep duplicates)
let all = try Customer.select(\.email).union(Supplier.select(\.email)).fetchAll(db)

// INTERSECT (in both), EXCEPT (in first but not second)
let shared = try Customer.select(\.email).intersect(Supplier.select(\.email)).fetchAll(db)

---

资源

GitHub：pointfreeco/sqlite-data、pointfreeco/swift-structured-queries、groue/GRDB.swift

技能：axiom-sqlitedata、axiom-sqlitedata-migration、axiom-database-migration、axiom-grdb

---

目标平台： iOS 17+、Swift 6 框架： SQLiteData 1.4+ 历史记录： 查看 git log 了解更改