54 lines
2.3 KiB
JavaScript
54 lines
2.3 KiB
JavaScript
'use strict';
|
||
const assert = require('node:assert');
|
||
const fs = require('node:fs');
|
||
const os = require('node:os');
|
||
const path = require('node:path');
|
||
|
||
const Database = require('better-sqlite3');
|
||
|
||
const tmp = fs.mkdtempSync(path.join(os.tmpdir(), 'clip-join-'));
|
||
const db = new Database(path.join(tmp, 'h.db'));
|
||
db.exec(`
|
||
CREATE TABLE clips (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
type TEXT, text TEXT, image BLOB, preview TEXT,
|
||
top_at REAL, created_at REAL NOT NULL
|
||
);
|
||
CREATE TABLE favorites (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
source_clip_id INTEGER UNIQUE, favorited_at REAL NOT NULL
|
||
);
|
||
`);
|
||
const now = Date.now()/1000;
|
||
// created_at 必须互不相同，否则 ORDER BY created_at DESC 的次序未定义，断言就是碰运气
|
||
db.prepare('INSERT INTO clips(type,text,preview,top_at,created_at) VALUES(?,?,?,?,?)').run('text','a','a',null,now - 2);
|
||
db.prepare('INSERT INTO clips(type,text,preview,top_at,created_at) VALUES(?,?,?,?,?)').run('text','b','b',null,now - 1);
|
||
db.prepare('INSERT INTO clips(type,text,preview,top_at,created_at) VALUES(?,?,?,?,?)').run('text','c','c',null,now);
|
||
const favInfo = db.prepare('INSERT INTO favorites(source_clip_id, favorited_at) VALUES(?,?)').run(2, now);
|
||
const favIdOfClip2 = favInfo.lastInsertRowid; // = favorites.id，不是 clip.id
|
||
|
||
let passed = 0;
|
||
function check(name, fn) {
|
||
try { fn(); console.log(' ok -', name); passed++; }
|
||
catch (e) { console.error(' FAIL -', name, e.message); process.exitCode = 1; }
|
||
}
|
||
|
||
check('LEFT JOIN returns one row per clip with fav_id when matched', () => {
|
||
const rows = db.prepare(`
|
||
SELECT c.id, c.text, f.id AS fav_id
|
||
FROM clips c
|
||
LEFT JOIN favorites f ON f.source_clip_id = c.id
|
||
ORDER BY c.created_at DESC
|
||
`).all();
|
||
assert.strictEqual(rows.length, 3, 'JOIN 不应放大行数');
|
||
// created_at DESC → c(3), b(2), a(1)；被收藏的是 clip 2 = 中间那条
|
||
assert.deepStrictEqual(rows.map(r => r.id), [3, 2, 1]);
|
||
assert.strictEqual(rows[0].fav_id, null);
|
||
// fav_id 是 favorites 表的主键，不是 source_clip_id —— 这两个值以前被搞混了
|
||
assert.strictEqual(rows[1].fav_id, favIdOfClip2);
|
||
assert.notStrictEqual(rows[1].fav_id, rows[1].id, 'favId 与 clipId 是不同的命名空间');
|
||
assert.strictEqual(rows[2].fav_id, null);
|
||
});
|
||
|
||
db.close();
|