1 files changed, 149 insertions, 0 deletions
diff --git a/services/docker/mail-server/relay/db.ts b/services/docker/mail-server/relay/db.ts
new file mode 100644
index 0000000..e5307be
--- /dev/null
+++ b/services/docker/mail-server/relay/db.ts
@@ -0,0 +1,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;
+  }
+}