yuming
30a40252c6
原计划是部署后上服务器手工执行一条 SQL 补额度，漏执行的后果是所有 存量用户的提醒立刻大面积降级。改成 PRAGMA user_version 驱动的一次性 自动迁移，部署即执行、不可能漏。 幂等性两层保证：user_version 版本号 + INSERT OR IGNORE 只补尚无记录的 openid，已有记录一律不覆盖。整段包在事务里。 Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
113 lines
4.1 KiB
JavaScript
113 lines
4.1 KiB
JavaScript
const test = require('node:test')
|
|
const assert = require('node:assert')
|
|
const { execFileSync } = require('node:child_process')
|
|
const Database = require('better-sqlite3')
|
|
const fs = require('node:fs')
|
|
const os = require('node:os')
|
|
const path = require('node:path')
|
|
|
|
// db.js 的迁移是在 require 时执行一次的（模块单例），所以没法在同一个进程里反复触发。
|
|
// 这里用子进程来「启动一次后端的数据层」，父进程只用 better-sqlite3 直接开同一个文件做
|
|
// 前置造数和结果断言。全程使用系统临时目录，绝不碰 server/data/birthday.db。
|
|
const DB_JS = path.join(__dirname, '..', 'src', 'db.js')
|
|
|
|
function bootDb(dbPath) {
|
|
execFileSync(process.execPath, ['-e', 'require(process.env.MIGRATION_ENTRY)'], {
|
|
env: { ...process.env, MIGRATION_ENTRY: DB_JS, DB_PATH: dbPath },
|
|
stdio: 'ignore'
|
|
})
|
|
}
|
|
|
|
function tempDbPath() {
|
|
const dir = fs.mkdtempSync(path.join(os.tmpdir(), 'bday-mig-'))
|
|
return path.join(dir, 'mig.db')
|
|
}
|
|
|
|
// 造一个「老库」：表结构齐全、有纪念日数据，但 subscribe_quota 为空、user_version 还是 0。
|
|
// 这正是本次改造上线时线上生产库的样子。
|
|
function makeLegacyDb(rows) {
|
|
const dbPath = tempDbPath()
|
|
bootDb(dbPath) // 先借 db.js 建出完整表结构
|
|
const raw = new Database(dbPath)
|
|
const insert = raw.prepare(`
|
|
INSERT INTO anniversaries (id, openid, personName, type, solarMonth, solarDay, remindEnabled, remindDays)
|
|
VALUES (?, ?, ?, 'birthday', 1, 1, 1, 3)
|
|
`)
|
|
for (const [id, openid] of rows) insert.run(id, openid, '某人')
|
|
raw.prepare('DELETE FROM subscribe_quota').run()
|
|
raw.pragma('user_version = 0')
|
|
raw.close()
|
|
return dbPath
|
|
}
|
|
|
|
function readQuota(dbPath) {
|
|
const raw = new Database(dbPath, { readonly: true })
|
|
const list = raw.prepare('SELECT * FROM subscribe_quota ORDER BY openid').all()
|
|
const version = raw.pragma('user_version', { simple: true })
|
|
raw.close()
|
|
return { list, version }
|
|
}
|
|
|
|
test('迁移 v1：按纪念日条数给存量用户补初始余额', () => {
|
|
const dbPath = makeLegacyDb([
|
|
['a1', 'uOld1'], ['a2', 'uOld1'], ['a3', 'uOld1'], // 3 条
|
|
['b1', 'uOld2'] // 1 条
|
|
])
|
|
|
|
bootDb(dbPath)
|
|
|
|
const { list, version } = readQuota(dbPath)
|
|
assert.strictEqual(version, 1, 'user_version 应被推进到 1')
|
|
assert.deepStrictEqual(
|
|
list.map(r => [r.openid, r.balance, r.grantedTotal, r.sentTotal]),
|
|
[['uOld1', 3, 3, 0], ['uOld2', 1, 1, 0]],
|
|
'每个存量用户的初始余额应等于其纪念日条数'
|
|
)
|
|
})
|
|
|
|
test('迁移 v1：重复启动不会重复累加（幂等）', () => {
|
|
const dbPath = makeLegacyDb([['a1', 'uIdem'], ['a2', 'uIdem']])
|
|
|
|
bootDb(dbPath) // 第一次：补 2
|
|
bootDb(dbPath) // 第二次：user_version 已是 1，应直接跳过
|
|
bootDb(dbPath) // 第三次：同上
|
|
|
|
const { list } = readQuota(dbPath)
|
|
assert.strictEqual(list.length, 1)
|
|
assert.strictEqual(list[0].balance, 2, '反复启动后余额仍应是 2，不能累加成 4 或 6')
|
|
assert.strictEqual(list[0].grantedTotal, 2)
|
|
})
|
|
|
|
test('迁移 v1：已有额度记录的用户不被覆盖', () => {
|
|
const dbPath = makeLegacyDb([['a1', 'uHas'], ['a2', 'uHas'], ['c1', 'uNone']])
|
|
|
|
// 模拟「已经用新版小程序补过额度」的用户：subscribe_quota 里已有一行
|
|
const raw = new Database(dbPath)
|
|
raw.prepare(`
|
|
INSERT INTO subscribe_quota (openid, balance, grantedTotal, sentTotal, updateTime)
|
|
VALUES ('uHas', 7, 9, 2, 0)
|
|
`).run()
|
|
raw.close()
|
|
|
|
bootDb(dbPath)
|
|
|
|
const { list } = readQuota(dbPath)
|
|
const has = list.find(r => r.openid === 'uHas')
|
|
const none = list.find(r => r.openid === 'uNone')
|
|
assert.deepStrictEqual(
|
|
[has.balance, has.grantedTotal, has.sentTotal], [7, 9, 2],
|
|
'已有记录必须原样保留，不能被按纪念日条数估算出来的值覆盖'
|
|
)
|
|
assert.strictEqual(none.balance, 1, '尚无记录的用户照常补')
|
|
})
|
|
|
|
test('迁移 v1：全新空库也能正常推进版本号', () => {
|
|
const dbPath = tempDbPath()
|
|
|
|
bootDb(dbPath)
|
|
|
|
const { list, version } = readQuota(dbPath)
|
|
assert.strictEqual(version, 1)
|
|
assert.strictEqual(list.length, 0, '没有纪念日就没有要补的用户')
|
|
})
|