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
|
// spellchecker: words sqlocal kysely insertable updateable
import { SQLocalKysely } from "sqlocal/kysely";
import { Generated, Insertable, Kysely, Migration, Migrator } from "kysely";
import { Mail } from "./mail.ts";
export class DbError extends Error {
}
const tableNames = {
mail: {
table: "mail",
columns: {
id: "id",
messageId: "message_id",
awsMessageId: "aws_message_id",
date: "date",
raw: "raw",
},
},
} as const;
interface MailTable {
[tableNames.mail.columns.id]: Generated<number>;
[tableNames.mail.columns.messageId]: string;
[tableNames.mail.columns.awsMessageId]: string | null;
[tableNames.mail.columns.date]: number | null;
[tableNames.mail.columns.raw]: string;
}
interface Database {
[tableNames.mail.table]: MailTable;
}
const migrations: Record<string, Migration> = {
"0001-init": {
// deno-lint-ignore no-explicit-any
async up(db: Kysely<any>): Promise<void> {
const names = tableNames.mail;
await db.schema
.createTable(names.table)
.addColumn(
names.columns.id,
"integer",
(col) => col.primaryKey().autoIncrement(),
)
.addColumn(
names.columns.messageId,
"text",
(col) => col.notNull().unique(),
)
.addColumn(names.columns.awsMessageId, "text", (col) => col.unique())
.addColumn(names.columns.date, "integer")
.addColumn(names.columns.raw, "text", (col) => col.notNull())
.execute();
for (
const column of [names.columns.messageId, names.columns.awsMessageId]
) {
await db.schema
.createIndex(`${names.table}_${column}`)
.on(names.table)
.column(column)
.execute();
}
},
// deno-lint-ignore no-explicit-any
async down(db: Kysely<any>): Promise<void> {
await db.schema.dropTable(tableNames.mail.table).execute();
},
},
};
export class DbService {
private _sqlocal;
private _db;
private _migrator;
constructor(public readonly path: string) {
this._sqlocal = new SQLocalKysely({
databasePath: path,
onInit: (sql) => [
// Though this can be executed only once when database is
// created, re-calls should not affect performance.
sql`PRAGMA journal_mode=WAL;`,
],
});
const db = new Kysely<Database>({ dialect: this._sqlocal.dialect });
this._db = db;
this._migrator = new Migrator({
db,
provider: {
getMigrations(): Promise<Record<string, Migration>> {
return Promise.resolve(migrations);
},
},
});
}
async migrate(): Promise<void> {
await this._migrator.migrateToLatest();
}
async addMail(mail: Insertable<MailTable> | Mail, options?: {
allowNullAwsMessageId?: boolean;
}): Promise<void> {
if (mail instanceof Mail) {
if (mail.messageId == null) {
throw new DbError("Mail object has no message id.");
}
mail = {
message_id: mail.messageId,
aws_message_id: mail.awsMessageId,
date: mail.simpleGetDate()?.getTime(),
raw: mail.raw,
};
}
if (
mail.aws_message_id == null &&
!(options?.allowNullAwsMessageId === true)
) {
throw new DbError("Aws message id is missing but it is required.");
}
await this._db.insertInto(tableNames.mail.table).values(mail)
.executeTakeFirstOrThrow();
}
async messageIdToAws(messageId: string): Promise<string | null> {
const row = await this._db.selectFrom(tableNames.mail.table).where(
tableNames.mail.columns.messageId,
"=",
messageId,
).select(tableNames.mail.columns.awsMessageId).executeTakeFirst();
return row?.aws_message_id ?? null;
}
async messageIdFromAws(awsMessageId: string): Promise<string | null> {
const row = await this._db.selectFrom(tableNames.mail.table).where(
tableNames.mail.columns.awsMessageId,
"=",
awsMessageId,
).select(tableNames.mail.columns.messageId).executeTakeFirst();
return row?.message_id ?? null;
}
}
|
