Press n or j to go to the next uncovered block, b, p or k for the previous block.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 | 2x 2x 24x 24x 24x 24x 2x 24x 24x 24x 24x 24x 49x 10x 39x 39x 39x 39x 5x 5x 5x 5x 5x 2x 2x 2x 3x 3x 3x 37x 37x 37x 37x 34x 37x 14x 14x 14x 14x 14x 28x 14x 14x 24x 24x 24x 49x 49x 68x 68x 68x 68x 68x 68x 68x 65x 65x 65x 65x 65x 65x 65x 65x 65x 65x 44x 44x 65x 65x 2x 24x 72x 24x 2x 24x 24x 24x 2x 2x 2x 2x 24x 24x 24x 24x 24x 65x 7x 7x 7x 7x 7x 7x 24x 24x 24x 24x 24x 2x 24x 24x 24x 24x 24x 24x 24x | import { AsyncLocalStorage } from 'node:async_hooks'
import { existsSync } from 'node:fs'
import { copyFile, mkdir, open, readFile, rename } from 'node:fs/promises'
import { createRequire } from 'node:module'
import { dirname } from 'node:path'
import initSqlJs, { type Database, type SqlValue } from 'sql.js'
import { sqliteSchema } from './schema'
type SqlParams = SqlValue[]
export const CURRENT_SCHEMA_VERSION = 2
type TransactionCallback<T> = () => Promise<T> | T
export interface SqliteAppDatabase {
run(sql: string, params?: SqlParams): Promise<number>
get<T extends Record<string, unknown>>(sql: string, params?: SqlParams): T | null
all<T extends Record<string, unknown>>(sql: string, params?: SqlParams): T[]
transaction<T>(callback: TransactionCallback<T>): Promise<T>
flush(): Promise<void>
close(): void
}
const loadSqlJs = async () => {
const require = createRequire(import.meta.url)
const wasmFile = require.resolve('sql.js/dist/sql-wasm.wasm')
const wasmDir = dirname(wasmFile)
return initSqlJs({
locateFile: (file) => `${wasmDir}/${file}`
})
}
class SqlJsAppDatabase implements SqliteAppDatabase {
private readonly transactionStorage = new AsyncLocalStorage<true>()
private writeTail: Promise<void> = Promise.resolve()
constructor(
private readonly filePath: string,
private readonly database: Database,
private shouldRotateBackup: boolean
) {}
async run(sql: string, params: SqlParams = []): Promise<number> {
if (this.transactionStorage.getStore()) {
return this.runImmediate(sql, params)
}
return this.enqueueWrite(async () => {
const changedRows = this.runImmediate(sql, params)
await this.flushAtomic()
return changedRows
})
}
async transaction<T>(callback: TransactionCallback<T>): Promise<T> {
Iif (this.transactionStorage.getStore()) {
return callback()
}
return this.enqueueWrite(async () => {
this.database.run('BEGIN')
try {
const result = await this.transactionStorage.run(true, callback)
this.database.run('COMMIT')
await this.flushAtomic()
return result
} catch (error) {
try {
this.database.run('ROLLBACK')
} catch {
// Preserve the original transaction error if rollback itself fails.
}
throw error
}
})
}
get<T extends Record<string, unknown>>(sql: string, params: SqlParams = []): T | null {
const statement = this.database.prepare(sql)
try {
statement.bind(params)
if (!statement.step()) return null
return statement.getAsObject() as T
} finally {
statement.free()
}
}
all<T extends Record<string, unknown>>(sql: string, params: SqlParams = []): T[] {
const statement = this.database.prepare(sql)
const rows: T[] = []
try {
statement.bind(params)
while (statement.step()) {
rows.push(statement.getAsObject() as T)
}
return rows
} finally {
statement.free()
}
}
async flush(): Promise<void> {
Iif (this.transactionStorage.getStore()) {
await this.flushAtomic()
return
}
await this.enqueueWrite(() => this.flushAtomic())
}
close(): void {
this.database.close()
}
private runImmediate(sql: string, params: SqlParams): number {
this.database.run(sql, params)
return this.database.getRowsModified()
}
private async enqueueWrite<T>(operation: () => Promise<T>): Promise<T> {
const previous = this.writeTail
let release!: () => void
this.writeTail = new Promise<void>((resolve) => {
release = resolve
})
await previous
try {
return await operation()
} finally {
release()
}
}
private async flushAtomic(): Promise<void> {
await mkdir(dirname(this.filePath), { recursive: true })
const tempPath = `${this.filePath}.tmp`
const backupPath = `${this.filePath}.bak`
const backupTempPath = `${backupPath}.tmp`
const tempFile = await open(tempPath, 'w')
try {
await tempFile.writeFile(Buffer.from(this.database.export()))
await tempFile.sync()
} finally {
await tempFile.close()
}
if (this.shouldRotateBackup && existsSync(this.filePath)) {
await copyFile(this.filePath, backupTempPath)
await rename(backupTempPath, backupPath)
}
await rename(tempPath, this.filePath)
this.shouldRotateBackup = true
}
}
const ensureTaskSortOrderColumn = (database: Database): void => {
const tableInfo = database.exec('PRAGMA table_info(tasks)')
const hasSortOrder = tableInfo[0]?.values.some((row) => row[1] === 'sort_order') ?? false
Iif (!hasSortOrder) {
database.run('ALTER TABLE tasks ADD COLUMN sort_order INTEGER NOT NULL DEFAULT 0')
}
}
const backfillActiveTaskSortOrder = (database: Database): void => {
const result = database.exec(
`SELECT id
FROM tasks
WHERE completed_at IS NULL AND sort_order <= 0
ORDER BY created_at ASC`
)
const rows = result[0]?.values ?? []
for (let index = 0; index < rows.length; index += 1) {
const id = rows[index]?.[0]
Iif (typeof id !== 'string') continue
database.run('UPDATE tasks SET sort_order = ? WHERE id = ?', [index + 1, id])
}
}
export const createSqliteAppDatabase = async (filePath: string): Promise<SqliteAppDatabase> => {
const SQL = await loadSqlJs()
const candidates = [
{ path: `${filePath}.tmp`, isPrimary: false },
{ path: filePath, isPrimary: true },
{ path: `${filePath}.bak`, isPrimary: false }
]
let database: Database | null = null
let loadedFromPrimary = false
for (const candidate of candidates) {
if (!existsSync(candidate.path)) continue
try {
const candidateDatabase = new SQL.Database(await readFile(candidate.path))
candidateDatabase.exec('PRAGMA schema_version')
database = candidateDatabase
loadedFromPrimary = candidate.isPrimary
break
} catch {
// Try the next recovery candidate when a file is incomplete or corrupt.
}
}
database ??= new SQL.Database()
migrateDatabase(database)
const appDatabase = new SqlJsAppDatabase(filePath, database, loadedFromPrimary)
await appDatabase.flush()
return appDatabase
}
const migrateDatabase = (database: Database): void => {
const versionRow = database.exec('PRAGMA user_version')[0]?.values[0]?.[0]
const version = typeof versionRow === 'number' ? versionRow : Number(versionRow ?? 0)
Iif (!Number.isInteger(version) || version < 0 || version > CURRENT_SCHEMA_VERSION) {
throw new Error(`Unsupported SQLite schema version: ${String(versionRow)}`)
}
database.run(sqliteSchema)
ensureTaskSortOrderColumn(database)
backfillActiveTaskSortOrder(database)
database.run(`PRAGMA user_version = ${CURRENT_SCHEMA_VERSION}`)
}
|