yangxiangyuan
401 lines
17 KiB
JavaScript
401 lines
17 KiB
JavaScript
const Database = require('better-sqlite3')
|
|
const fs = require('fs')
|
|
const path = require('path')
|
|
const crypto = require('crypto')
|
|
|
|
const dataDir = path.join(process.cwd(), 'data')
|
|
if (!fs.existsSync(dataDir)) fs.mkdirSync(dataDir, { recursive: true })
|
|
const dbPath = path.join(dataDir, 'cloud_notes.db')
|
|
const db = new Database(dbPath)
|
|
|
|
db.exec(`CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT NOT NULL,
|
|
salt TEXT NOT NULL,
|
|
created_at TEXT DEFAULT (datetime('now'))
|
|
);`)
|
|
|
|
db.exec(`CREATE TABLE IF NOT EXISTS notes (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
title TEXT NOT NULL,
|
|
html TEXT NOT NULL,
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
updated_at TEXT DEFAULT (datetime('now'))
|
|
);`)
|
|
|
|
// ensure optional columns
|
|
try {
|
|
const cols = db.prepare(`PRAGMA table_info(notes)`).all().map(c => c.name)
|
|
if (!cols.includes('starred')) db.exec(`ALTER TABLE notes ADD COLUMN starred INTEGER DEFAULT 0`)
|
|
if (!cols.includes('pinned')) db.exec(`ALTER TABLE notes ADD COLUMN pinned INTEGER DEFAULT 0`)
|
|
if (!cols.includes('deleted_at')) db.exec(`ALTER TABLE notes ADD COLUMN deleted_at TEXT`)
|
|
} catch {}
|
|
db.exec(`CREATE TABLE IF NOT EXISTS note_versions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
note_id INTEGER NOT NULL,
|
|
title TEXT NOT NULL,
|
|
html TEXT NOT NULL,
|
|
saved_at TEXT DEFAULT (datetime('now'))
|
|
);`)
|
|
|
|
db.exec(`CREATE TABLE IF NOT EXISTS attachments (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
note_id INTEGER NOT NULL,
|
|
filename TEXT NOT NULL,
|
|
file_path TEXT NOT NULL,
|
|
file_size INTEGER,
|
|
created_at TEXT DEFAULT (datetime('now'))
|
|
);`)
|
|
|
|
db.exec(`CREATE TABLE IF NOT EXISTS tags (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
name TEXT NOT NULL,
|
|
color TEXT,
|
|
sort_index INTEGER DEFAULT 0,
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
updated_at TEXT DEFAULT (datetime('now'))
|
|
);`)
|
|
|
|
db.exec(`CREATE TABLE IF NOT EXISTS note_tags (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
note_id INTEGER NOT NULL,
|
|
tag_id INTEGER NOT NULL,
|
|
created_at TEXT DEFAULT (datetime('now'))
|
|
);`)
|
|
|
|
db.exec(`CREATE TABLE IF NOT EXISTS settings (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
key TEXT NOT NULL UNIQUE,
|
|
value TEXT,
|
|
updated_at TEXT DEFAULT (datetime('now'))
|
|
);`)
|
|
|
|
const getUserCount = db.prepare(`SELECT COUNT(1) AS c FROM users`)
|
|
const findUser = db.prepare(`SELECT * FROM users WHERE username=?`)
|
|
const createUser = db.prepare(`INSERT INTO users (username, password_hash, salt) VALUES (?, ?, ?)`)
|
|
const hashPassword = (pwd, saltHex) => {
|
|
const salt = Buffer.from(saltHex, 'hex')
|
|
const key = crypto.scryptSync(String(pwd || ''), salt, 32)
|
|
return key.toString('hex')
|
|
}
|
|
|
|
const ensureAdmin = () => {
|
|
const c = getUserCount.get().c
|
|
if (c > 0) return
|
|
const salt = crypto.randomBytes(16).toString('hex')
|
|
const defaultPass = process.env.CLOUD_NOTES_DEFAULT_PASSWORD || process.env.LOCK_DEFAULT_PASSWORD || ''
|
|
const hash = hashPassword(defaultPass, salt)
|
|
createUser.run('admin_cn', hash, salt)
|
|
}
|
|
ensureAdmin()
|
|
|
|
const login = (username, password) => {
|
|
const u = findUser.get(username)
|
|
if (!u) {
|
|
const exists = getUserCount.get().c > 0
|
|
if (exists) return null
|
|
const salt = crypto.randomBytes(16).toString('hex')
|
|
const hash = hashPassword(password, salt)
|
|
createUser.run(username, hash, salt)
|
|
return findUser.get(username)
|
|
}
|
|
const ok = u.password_hash === hashPassword(password, u.salt)
|
|
return ok ? u : null
|
|
}
|
|
|
|
const insertNote = db.prepare(`INSERT INTO notes (user_id, title, html) VALUES (@user_id, @title, @html)`)
|
|
const updateNoteStmt = db.prepare(`UPDATE notes SET title=@title, html=@html, updated_at=datetime('now') WHERE id=@id`)
|
|
const insertVersion = db.prepare(`INSERT INTO note_versions (note_id, title, html) VALUES (@note_id, @title, @html)`)
|
|
const listVersionsStmt = db.prepare(`SELECT id, note_id, title, html, datetime(saved_at, 'localtime') AS saved_at FROM note_versions WHERE note_id=? ORDER BY id DESC LIMIT 50`)
|
|
const latestVersionStmt = db.prepare(`SELECT id, note_id, title, html, datetime(saved_at, 'localtime') AS saved_at FROM note_versions WHERE note_id=? ORDER BY id DESC LIMIT 1`)
|
|
const createAttachmentStmt = db.prepare(`INSERT INTO attachments (note_id, filename, file_path, file_size) VALUES (@note_id, @filename, @file_path, @file_size)`)
|
|
const listAttachmentsStmt = db.prepare(`SELECT id, note_id, filename, file_path, file_size, datetime(created_at, 'localtime') AS created_at FROM attachments WHERE note_id=? ORDER BY id ASC`)
|
|
const getAttachmentByIdStmt = db.prepare(`SELECT id, file_path FROM attachments WHERE id=?`)
|
|
const deleteAttachmentStmt = db.prepare(`DELETE FROM attachments WHERE id=?`)
|
|
const countAttachmentByPathStmt = db.prepare(`SELECT COUNT(1) AS c FROM attachments WHERE file_path=?`)
|
|
|
|
const listNotesStmt = db.prepare(`SELECT id, title, starred, pinned, datetime(updated_at, 'localtime') AS updated_at, datetime(created_at, 'localtime') AS created_at, LENGTH(html) AS size_bytes FROM notes WHERE user_id=? AND deleted_at IS NULL ORDER BY pinned DESC, updated_at DESC`)
|
|
const getNoteStmt = db.prepare(`SELECT id, user_id, title, html, starred, pinned, datetime(updated_at, 'localtime') AS updated_at, datetime(created_at, 'localtime') AS created_at, datetime(deleted_at, 'localtime') AS deleted_at, LENGTH(html) AS size_bytes FROM notes WHERE id=?`)
|
|
const searchNotesStmt = db.prepare(`SELECT id, user_id, title, html, datetime(created_at, 'localtime') AS created_at, datetime(updated_at, 'localtime') AS updated_at, starred, pinned, LENGTH(html) AS size_bytes FROM notes WHERE user_id=? AND deleted_at IS NULL AND (title LIKE ? OR html LIKE ?) ORDER BY updated_at DESC`)
|
|
const listTrashNotesStmt = db.prepare(`SELECT id, title, starred, pinned, datetime(updated_at, 'localtime') AS updated_at, datetime(created_at, 'localtime') AS created_at, datetime(deleted_at, 'localtime') AS deleted_at, LENGTH(html) AS size_bytes FROM notes WHERE user_id=? AND deleted_at IS NOT NULL ORDER BY deleted_at DESC, updated_at DESC`)
|
|
const softDeleteNoteStmt = db.prepare(`UPDATE notes SET deleted_at=datetime('now') WHERE id=@id`)
|
|
const restoreNoteStmt = db.prepare(`UPDATE notes SET deleted_at=NULL WHERE id=@id`)
|
|
const hardDeleteNoteStmt = db.prepare(`DELETE FROM notes WHERE id=@id`)
|
|
const setStarStmt = db.prepare(`UPDATE notes SET starred=@starred WHERE id=@id`)
|
|
const setPinStmt = db.prepare(`UPDATE notes SET pinned=@pinned WHERE id=@id`)
|
|
const listTagsStmt = db.prepare(`SELECT id, user_id, name, color, sort_index, datetime(created_at, 'localtime') AS created_at, datetime(updated_at, 'localtime') AS updated_at FROM tags WHERE user_id=? ORDER BY sort_index ASC, id ASC`)
|
|
const insertTagStmt = db.prepare(`INSERT INTO tags (user_id, name, color, sort_index) VALUES (@user_id, @name, @color, @sort_index)`)
|
|
const updateTagStmt = db.prepare(`UPDATE tags SET name=@name, color=@color, sort_index=@sort_index, updated_at=datetime('now') WHERE id=@id AND user_id=@user_id`)
|
|
const deleteTagStmt = db.prepare(`DELETE FROM tags WHERE id=? AND user_id=?`)
|
|
const deleteNoteTagsByTagStmt = db.prepare(`DELETE FROM note_tags WHERE tag_id=?`)
|
|
const listNoteTagsStmt = db.prepare(`SELECT tag_id FROM note_tags WHERE note_id=?`)
|
|
const deleteNoteTagsByNoteStmt = db.prepare(`DELETE FROM note_tags WHERE note_id=?`)
|
|
const insertNoteTagStmt = db.prepare(`INSERT INTO note_tags (note_id, tag_id) VALUES (?, ?)`)
|
|
const listNotesByTagStmt = db.prepare(`SELECT n.id, n.title, n.starred, n.pinned, datetime(n.updated_at, 'localtime') AS updated_at, datetime(n.created_at, 'localtime') AS created_at, LENGTH(n.html) AS size_bytes FROM notes n JOIN note_tags t ON n.id=t.note_id WHERE n.user_id=? AND n.deleted_at IS NULL AND t.tag_id=? ORDER BY n.pinned DESC, n.updated_at DESC`)
|
|
const tagStatsStmt = db.prepare(`SELECT t.id, t.user_id, t.name, t.color, t.sort_index, COUNT(nt.note_id) AS note_count FROM tags t LEFT JOIN note_tags nt ON t.id=nt.tag_id LEFT JOIN notes n ON nt.note_id=n.id AND n.deleted_at IS NULL WHERE t.user_id=? GROUP BY t.id, t.user_id, t.name, t.color, t.sort_index ORDER BY note_count DESC, t.sort_index ASC, t.id ASC`)
|
|
const untaggedCountStmt = db.prepare(`SELECT COUNT(1) AS c FROM notes n WHERE n.user_id=? AND n.deleted_at IS NULL AND NOT EXISTS (SELECT 1 FROM note_tags nt WHERE nt.note_id=n.id)`)
|
|
const recentAttachmentsByUserStmt = db.prepare(`SELECT a.id, a.note_id, a.filename, a.file_path, a.file_size, datetime(a.created_at, 'localtime') AS created_at, n.title AS note_title FROM attachments a JOIN notes n ON a.note_id=n.id WHERE n.user_id=? ORDER BY a.created_at DESC LIMIT 10`)
|
|
|
|
const getSettingStmt = db.prepare(`SELECT value FROM settings WHERE key=?`)
|
|
const upsertSettingStmt = db.prepare(`INSERT INTO settings (key, value) VALUES (@key, @value) ON CONFLICT(key) DO UPDATE SET value=@value, updated_at=datetime('now')`)
|
|
|
|
const defaultTinyToolbarSettings = () => ({
|
|
undo_redo: true,
|
|
blocks_fontsize: true,
|
|
inline_format: true,
|
|
align: true,
|
|
indent: true,
|
|
lists: true,
|
|
table: true,
|
|
media: true,
|
|
insert_misc: true,
|
|
code: true,
|
|
page_tools: true,
|
|
search_visual: true,
|
|
preview_fullscreen_print: true,
|
|
cn_tools: true,
|
|
menubar: true,
|
|
upgrade_button: true
|
|
})
|
|
|
|
const normalizeTinyToolbarSettings = raw => {
|
|
const def = defaultTinyToolbarSettings()
|
|
const out = {}
|
|
Object.keys(def).forEach(k => {
|
|
out[k] = raw && Object.prototype.hasOwnProperty.call(raw, k) ? !!raw[k] : def[k]
|
|
})
|
|
return out
|
|
}
|
|
|
|
const getTinyToolbarSettings = () => {
|
|
const row = getSettingStmt.get('tiny_toolbar')
|
|
if (!row || typeof row.value !== 'string') return defaultTinyToolbarSettings()
|
|
try {
|
|
const raw = JSON.parse(row.value || '{}')
|
|
return normalizeTinyToolbarSettings(raw)
|
|
} catch {
|
|
return defaultTinyToolbarSettings()
|
|
}
|
|
}
|
|
|
|
const saveTinyToolbarSettings = settings => {
|
|
const norm = normalizeTinyToolbarSettings(settings || {})
|
|
const value = JSON.stringify(norm)
|
|
upsertSettingStmt.run({ key: 'tiny_toolbar', value })
|
|
return norm
|
|
}
|
|
|
|
const saveNote = payload => {
|
|
const id = parseInt(payload.id || '0', 10)
|
|
const user_id = parseInt(payload.user_id || '1', 10)
|
|
const title = String(payload.title || '未命名')
|
|
const html = String(payload.html || '')
|
|
const baseVersionId = parseInt(payload.base_version_id || '0', 10)
|
|
const force = !!payload.force
|
|
let noteId = 0
|
|
let versionId = 0
|
|
if (!id) {
|
|
const info = insertNote.run({ user_id, title, html })
|
|
const vinfo = insertVersion.run({ note_id: info.lastInsertRowid, title, html })
|
|
noteId = info.lastInsertRowid
|
|
versionId = vinfo.lastInsertRowid
|
|
} else {
|
|
const latest = latestVersionStmt.get(id)
|
|
const latestId = latest && latest.id ? latest.id : 0
|
|
if (baseVersionId && latestId && baseVersionId !== latestId && !force) {
|
|
return { conflict: true, latest }
|
|
}
|
|
updateNoteStmt.run({ id, title, html })
|
|
const vinfo = insertVersion.run({ note_id: id, title, html })
|
|
noteId = id
|
|
versionId = vinfo.lastInsertRowid
|
|
}
|
|
const tagIds = Array.isArray(payload.tag_ids) ? payload.tag_ids.map(x => parseInt(x, 10)).filter(x => x > 0) : []
|
|
deleteNoteTagsByNoteStmt.run(noteId)
|
|
const seen = new Set()
|
|
tagIds.forEach(tid => {
|
|
if (tid && !seen.has(tid)) {
|
|
seen.add(tid)
|
|
insertNoteTagStmt.run(noteId, tid)
|
|
}
|
|
})
|
|
return { note_id: noteId, version_id: versionId, conflict: false }
|
|
}
|
|
|
|
const listVersions = noteId => listVersionsStmt.all(noteId)
|
|
const createAttachment = payload => {
|
|
const info = createAttachmentStmt.run(payload)
|
|
return info.lastInsertRowid
|
|
}
|
|
const listAttachments = noteId => listAttachmentsStmt.all(noteId)
|
|
const deleteAttachment = id => deleteAttachmentStmt.run(parseInt(id, 10))
|
|
const deleteAttachmentWithFile = id => {
|
|
const aid = parseInt(id, 10)
|
|
if (!aid) return
|
|
const row = getAttachmentByIdStmt.get(aid)
|
|
if (row && row.file_path) {
|
|
try {
|
|
const refs = countAttachmentByPathStmt.get(row.file_path)
|
|
const c = refs && typeof refs.c === 'number' ? refs.c : 0
|
|
if (c <= 1) {
|
|
const rel = String(row.file_path || '').replace(/^[\\/]+/, '')
|
|
if (rel) {
|
|
const full = path.join(process.cwd(), rel)
|
|
if (fs.existsSync(full)) fs.unlinkSync(full)
|
|
}
|
|
}
|
|
} catch {}
|
|
}
|
|
deleteAttachmentStmt.run(aid)
|
|
}
|
|
const cloneAttachments = (srcNoteId, destNoteId) => {
|
|
const srcId = parseInt(srcNoteId, 10)
|
|
const dstId = parseInt(destNoteId, 10)
|
|
if (!srcId || !dstId || srcId === dstId) return
|
|
const atts = listAttachmentsStmt.all(srcId)
|
|
atts.forEach(a => {
|
|
createAttachmentStmt.run({
|
|
note_id: dstId,
|
|
filename: a.filename,
|
|
file_path: a.file_path,
|
|
file_size: a.file_size
|
|
})
|
|
})
|
|
}
|
|
|
|
const listNotes = userId => listNotesStmt.all(userId)
|
|
const getNote = id => {
|
|
const row = getNoteStmt.get(id)
|
|
if (!row) return null
|
|
const rel = listNoteTagsStmt.all(id)
|
|
row.tag_ids = rel.map(r => r.tag_id)
|
|
const latest = latestVersionStmt.get(id)
|
|
row.version_id = latest && latest.id ? latest.id : 0
|
|
row.version_saved_at = latest && latest.saved_at ? latest.saved_at : ''
|
|
return row
|
|
}
|
|
const searchNotes = (userId, keyword) => {
|
|
const kw = String(keyword || '').trim()
|
|
if (!kw) return []
|
|
const pat = `%${kw}%`
|
|
return searchNotesStmt.all(userId, pat, pat)
|
|
}
|
|
const setStar = payload => setStarStmt.run({ id: parseInt(payload.id,10), starred: payload.starred ? 1 : 0 })
|
|
const setPin = payload => setPinStmt.run({ id: parseInt(payload.id,10), pinned: payload.pinned ? 1 : 0 })
|
|
const listTrashNotes = userId => listTrashNotesStmt.all(userId)
|
|
const softDeleteNote = id => softDeleteNoteStmt.run({ id: parseInt(id, 10) })
|
|
const restoreNote = id => restoreNoteStmt.run({ id: parseInt(id, 10) })
|
|
const listTags = userId => listTagsStmt.all(userId)
|
|
const saveTags = (userId, tags) => {
|
|
const uid = parseInt(userId || '1', 10)
|
|
const existing = listTagsStmt.all(uid)
|
|
const existingById = new Map()
|
|
existing.forEach(t => { existingById.set(t.id, t) })
|
|
const tx = db.transaction(() => {
|
|
const keep = new Set()
|
|
const list = Array.isArray(tags) ? tags : []
|
|
list.forEach((t, idx) => {
|
|
const id = parseInt(t.id || '0', 10)
|
|
const name = String(t.name || '').trim()
|
|
const color = String(t.color || '').trim()
|
|
const sort_index = Number.isFinite(t.sort_index) ? t.sort_index : idx
|
|
if (!name) return
|
|
if (id && existingById.has(id)) {
|
|
updateTagStmt.run({ id, user_id: uid, name, color, sort_index })
|
|
keep.add(id)
|
|
} else {
|
|
const info = insertTagStmt.run({ user_id: uid, name, color, sort_index })
|
|
keep.add(info.lastInsertRowid)
|
|
}
|
|
})
|
|
existing.forEach(t => {
|
|
if (!keep.has(t.id)) {
|
|
deleteNoteTagsByTagStmt.run(t.id)
|
|
deleteTagStmt.run(t.id, uid)
|
|
}
|
|
})
|
|
})
|
|
tx()
|
|
}
|
|
const listNotesByTag = (userId, tagId) => {
|
|
const uid = parseInt(userId || '1', 10)
|
|
const tid = parseInt(tagId || '0', 10)
|
|
if (!tid) return []
|
|
return listNotesByTagStmt.all(uid, tid)
|
|
}
|
|
const dashboard = userId => {
|
|
const uid = parseInt(userId || '1', 10)
|
|
const notes = listNotesStmt.all(uid)
|
|
const now = new Date()
|
|
const todayStr = now.toISOString().slice(0, 10)
|
|
const weekAgoMs = now.getTime() - 6 * 24 * 3600 * 1000
|
|
let todayCount = 0
|
|
let weekCount = 0
|
|
let starredCount = 0
|
|
let pinnedCount = 0
|
|
let lastNoteId = 0
|
|
let lastNoteUpdated = 0
|
|
notes.forEach(n => {
|
|
const updated = String(n.updated_at || n.created_at || '')
|
|
if (updated.startsWith(todayStr)) todayCount++
|
|
const t = Date.parse(updated.replace(' ', 'T'))
|
|
if (!Number.isNaN(t) && t >= weekAgoMs) weekCount++
|
|
if (n.starred) starredCount++
|
|
if (n.pinned) pinnedCount++
|
|
if (!Number.isNaN(t) && t > lastNoteUpdated) {
|
|
lastNoteUpdated = t
|
|
lastNoteId = n.id
|
|
}
|
|
})
|
|
const recentNotes = notes.slice(0, 6)
|
|
const pinned = notes.filter(n => n.pinned).slice(0, 6)
|
|
const starred = notes.filter(n => n.starred).slice(0, 6)
|
|
const tagStats = tagStatsStmt.all(uid)
|
|
const hotTags = tagStats.slice(0, 8)
|
|
const untaggedRow = untaggedCountStmt.get(uid)
|
|
const untaggedCount = untaggedRow && typeof untaggedRow.c === 'number' ? untaggedRow.c : 0
|
|
const recentAttachments = recentAttachmentsByUserStmt.all(uid)
|
|
return {
|
|
overview: {
|
|
total_notes: notes.length,
|
|
today_count: todayCount,
|
|
week_count: weekCount,
|
|
starred_count: starredCount,
|
|
pinned_count: pinnedCount
|
|
},
|
|
recent_notes: recentNotes,
|
|
pinned_notes: pinned,
|
|
starred_notes: starred,
|
|
hot_tags: hotTags,
|
|
untagged_count: untaggedCount,
|
|
recent_attachments: recentAttachments,
|
|
last_note_id: lastNoteId
|
|
}
|
|
}
|
|
const hardDeleteNote = id => {
|
|
const nid = parseInt(id, 10)
|
|
if (!nid) return
|
|
const atts = listAttachmentsStmt.all(nid)
|
|
atts.forEach(a => {
|
|
try {
|
|
const rel = String(a.file_path || '').replace(/^[\\/]+/, '')
|
|
if (rel) {
|
|
const refs = countAttachmentByPathStmt.get(a.file_path)
|
|
const c = refs && typeof refs.c === 'number' ? refs.c : 0
|
|
if (c <= 1) {
|
|
const full = path.join(process.cwd(), rel)
|
|
if (fs.existsSync(full)) fs.unlinkSync(full)
|
|
}
|
|
}
|
|
} catch {}
|
|
deleteAttachmentStmt.run(parseInt(a.id, 10))
|
|
})
|
|
hardDeleteNoteStmt.run({ id: nid })
|
|
deleteNoteTagsByNoteStmt.run(nid)
|
|
}
|
|
|
|
module.exports = { login, saveNote, listVersions, createAttachment, listAttachments, deleteAttachment, deleteAttachmentWithFile, listNotes, getNote, searchNotes, setStar, setPin, listTrashNotes, softDeleteNote, restoreNote, hardDeleteNote, listTags, saveTags, listNotesByTag, dashboard, cloneAttachments, getTinyToolbarSettings, saveTinyToolbarSettings }
|