1const db = getDb("questions.sqlite");
2db.table(
3 "questions",
4 `
5 create table if not exists questions (
6 qmid integer not null,
7 type integer not null,
8 text text not null,
9 primary key (qmid)
10 );
11 `,
12);
13
14export enum QuestionType {
15 normal = 0,
16 reject = 1,
17 unused = 2,
18 pending = 3,
19}
20
21export class Question {
22 /**
23 * questions are identified by a 'minute identifier'
24 * which is time / 60000 (minutes since epoch)
25 */
26 qmid!: number;
27 type!: QuestionType;
28 text!: string;
29
30 // -- instance ops --
31
32 /** "https://paperclover.net/q+a/${id}" - #YYMMDDHHMM */
33 get id(): string {
34 return formatQuestionId(this.date);
35 }
36 get date(): Date {
37 return new Date(Number(this.qmid) * 1000 * 60);
38 }
39
40 // -- static ops --
41
42 /** Create a new Question, rounding the date to the nearest available slot */
43 static create(type: QuestionType, text: string, date?: Date) {
44 const qmidWanted = Math.floor((date?.getTime() ?? Date.now()) / 1000 / 60);
45 const { qmid } = createQuery.getNonNull({
46 type,
47 text,
48 qmid: qmidWanted,
49 });
50
51 return new Date(qmid * 1000 * 60);
52 }
53 static getAll() {
54 return getAllQuery.iter();
55 }
56 static getByDate(timestamp: Date) {
57 // Older questions are inserted with second precision,
58 // but that data should always be floored.
59 const qmid = Math.floor(timestamp.getTime() / 1000 / 60);
60 return getByDateQuery.get(qmid);
61 }
62 static deleteByQmid(qmid: number) {
63 return deleteByQmidQuery.run(qmid);
64 }
65 static rejectByQmid(qmid: number) {
66 return rejectByQmidQuery.run(qmid);
67 }
68 static updateByQmid(qmid: number, text: string, type: QuestionType) {
69 return updateByQmidQuery.run({ text, type, qmid });
70 }
71 static search(ref: string) {
72 return searchQuery.array(`%@${ref}%`, `%https://paperclover.net/${ref}%`);
73 }
74}
75
76// Create a new question with a unique QMID
77const createQuery = db.prepare<
78 [{ type: QuestionType; text: string; qmid: number }],
79 { qmid: number }
80>(/* SQL */ `
81 WITH RECURSIVE
82 timestamp(value) AS (
83 SELECT $qmid
84 UNION ALL
85 SELECT value + 1
86 FROM timestamp
87 WHERE EXISTS (SELECT 1 FROM questions WHERE qmid = value)
88 )
89 INSERT INTO questions (type, text, qmid)
90 VALUES ($type, $text, (
91 SELECT value FROM timestamp
92 WHERE NOT EXISTS (SELECT 1 FROM questions WHERE qmid = value)
93 LIMIT 1
94 ))
95 RETURNING qmid
96`);
97
98const getAllQuery = db.prepare(/* SQL */ `
99 SELECT * FROM questions WHERE type = ${QuestionType.normal} ORDER BY qmid DESC
100`).as(Question);
101
102const getByDateQuery = db.prepare<[qmid: number]>(`
103 SELECT * FROM questions WHERE qmid = ? LIMIT 1
104`).as(Question);
105
106const deleteByQmidQuery = db.prepare<[qmid: number]>(`
107 DELETE FROM questions WHERE qmid = ?
108`);
109
110const rejectByQmidQuery = db.prepare<[qmid: number]>(`
111 UPDATE questions SET type = ${QuestionType.reject} WHERE qmid = ?
112`);
113
114const updateByQmidQuery = db.prepare<
115 [{ qmid: number; text: string; type: QuestionType }]
116>(`
117 UPDATE questions SET text = $text, type = $type WHERE qmid = $qmid
118`);
119
120const searchQuery = db.prepare<[string, string]>(
121 `select * from questions where type = ${QuestionType.normal} and (text like ? or text like ?)`,
122).as(Question);
123
124import { getDb } from "#sitegen/sqlite";
125import { formatQuestionId } from "../format.ts";