this repo has no description
dunkirk.sh
1import { Database } from "bun:sqlite";
2
3const db = new Database("bridge.db");
4
5db.run(`
6 CREATE TABLE IF NOT EXISTS channel_mappings (
7 id INTEGER PRIMARY KEY AUTOINCREMENT,
8 slack_channel_id TEXT NOT NULL UNIQUE,
9 irc_channel TEXT NOT NULL UNIQUE,
10 created_at INTEGER DEFAULT (strftime('%s', 'now'))
11 )
12`);
13
14db.run(`
15 CREATE TABLE IF NOT EXISTS user_mappings (
16 id INTEGER PRIMARY KEY AUTOINCREMENT,
17 slack_user_id TEXT NOT NULL UNIQUE,
18 irc_nick TEXT NOT NULL UNIQUE,
19 created_at INTEGER DEFAULT (strftime('%s', 'now'))
20 )
21`);
22
23// Migration: Add unique constraints if they don't exist
24// SQLite doesn't support ALTER TABLE to add constraints, so we need to recreate the table
25function migrateSchema() {
26 // Check if irc_channel has unique constraint by examining table schema
27 const channelSchema = db
28 .query("SELECT sql FROM sqlite_master WHERE type='table' AND name='channel_mappings'")
29 .get() as { sql: string } | null;
30
31 const hasIrcChannelUnique = channelSchema?.sql?.includes("irc_channel TEXT NOT NULL UNIQUE") ?? false;
32
33 if (!hasIrcChannelUnique && channelSchema) {
34 // Check if table has any data with duplicate irc_channel values
35 const duplicates = db.query(
36 "SELECT irc_channel, COUNT(*) as count FROM channel_mappings GROUP BY irc_channel HAVING count > 1",
37 ).all();
38
39 if (duplicates.length > 0) {
40 console.warn(
41 "Warning: Found duplicate IRC channel mappings. Keeping only the most recent mapping for each IRC channel.",
42 );
43 for (const dup of duplicates as { irc_channel: string }[]) {
44 // Delete all but the most recent mapping for this IRC channel
45 db.run(
46 `DELETE FROM channel_mappings
47 WHERE irc_channel = ?
48 AND id NOT IN (
49 SELECT id FROM channel_mappings
50 WHERE irc_channel = ?
51 ORDER BY created_at DESC
52 LIMIT 1
53 )`,
54 [dup.irc_channel, dup.irc_channel],
55 );
56 }
57 }
58
59 // Recreate the table with unique constraint
60 db.run(`
61 CREATE TABLE channel_mappings_new (
62 id INTEGER PRIMARY KEY AUTOINCREMENT,
63 slack_channel_id TEXT NOT NULL UNIQUE,
64 irc_channel TEXT NOT NULL UNIQUE,
65 created_at INTEGER DEFAULT (strftime('%s', 'now'))
66 )
67 `);
68
69 db.run(
70 "INSERT INTO channel_mappings_new SELECT * FROM channel_mappings",
71 );
72 db.run("DROP TABLE channel_mappings");
73 db.run("ALTER TABLE channel_mappings_new RENAME TO channel_mappings");
74 console.log("Migrated channel_mappings table to add unique constraint on irc_channel");
75 }
76
77 // Check if irc_nick has unique constraint by examining table schema
78 const userSchema = db
79 .query("SELECT sql FROM sqlite_master WHERE type='table' AND name='user_mappings'")
80 .get() as { sql: string } | null;
81
82 const hasIrcNickUnique = userSchema?.sql?.includes("irc_nick TEXT NOT NULL UNIQUE") ?? false;
83
84 if (!hasIrcNickUnique && userSchema) {
85 // Check if table has any data with duplicate irc_nick values
86 const duplicates = db.query(
87 "SELECT irc_nick, COUNT(*) as count FROM user_mappings GROUP BY irc_nick HAVING count > 1",
88 ).all();
89
90 if (duplicates.length > 0) {
91 console.warn(
92 "Warning: Found duplicate IRC nick mappings. Keeping only the most recent mapping for each IRC nick.",
93 );
94 for (const dup of duplicates as { irc_nick: string }[]) {
95 // Delete all but the most recent mapping for this IRC nick
96 db.run(
97 `DELETE FROM user_mappings
98 WHERE irc_nick = ?
99 AND id NOT IN (
100 SELECT id FROM user_mappings
101 WHERE irc_nick = ?
102 ORDER BY created_at DESC
103 LIMIT 1
104 )`,
105 [dup.irc_nick, dup.irc_nick],
106 );
107 }
108 }
109
110 // Recreate the table with unique constraint
111 db.run(`
112 CREATE TABLE user_mappings_new (
113 id INTEGER PRIMARY KEY AUTOINCREMENT,
114 slack_user_id TEXT NOT NULL UNIQUE,
115 irc_nick TEXT NOT NULL UNIQUE,
116 created_at INTEGER DEFAULT (strftime('%s', 'now'))
117 )
118 `);
119
120 db.run("INSERT INTO user_mappings_new SELECT * FROM user_mappings");
121 db.run("DROP TABLE user_mappings");
122 db.run("ALTER TABLE user_mappings_new RENAME TO user_mappings");
123 console.log("Migrated user_mappings table to add unique constraint on irc_nick");
124 }
125}
126
127migrateSchema();
128
129db.run(`
130 CREATE TABLE IF NOT EXISTS thread_timestamps (
131 thread_ts TEXT PRIMARY KEY,
132 thread_id TEXT NOT NULL UNIQUE,
133 slack_channel_id TEXT NOT NULL,
134 last_message_time INTEGER NOT NULL
135 )
136`);
137
138db.run(`
139 CREATE INDEX IF NOT EXISTS idx_thread_id ON thread_timestamps(thread_id)
140`);
141
142export interface ChannelMapping {
143 id?: number;
144 slack_channel_id: string;
145 irc_channel: string;
146 created_at?: number;
147}
148
149export interface UserMapping {
150 id?: number;
151 slack_user_id: string;
152 irc_nick: string;
153 created_at?: number;
154}
155
156export const channelMappings = {
157 getAll(): ChannelMapping[] {
158 return db.query("SELECT * FROM channel_mappings").all() as ChannelMapping[];
159 },
160
161 getBySlackChannel(slackChannelId: string): ChannelMapping | null {
162 return db
163 .query("SELECT * FROM channel_mappings WHERE slack_channel_id = ?")
164 .get(slackChannelId) as ChannelMapping | null;
165 },
166
167 getByIrcChannel(ircChannel: string): ChannelMapping | null {
168 return db
169 .query("SELECT * FROM channel_mappings WHERE irc_channel = ?")
170 .get(ircChannel) as ChannelMapping | null;
171 },
172
173 create(slackChannelId: string, ircChannel: string): void {
174 db.run(
175 "INSERT OR REPLACE INTO channel_mappings (slack_channel_id, irc_channel) VALUES (?, ?)",
176 [slackChannelId, ircChannel],
177 );
178 },
179
180 delete(slackChannelId: string): void {
181 db.run("DELETE FROM channel_mappings WHERE slack_channel_id = ?", [
182 slackChannelId,
183 ]);
184 },
185};
186
187export const userMappings = {
188 getAll(): UserMapping[] {
189 return db.query("SELECT * FROM user_mappings").all() as UserMapping[];
190 },
191
192 getBySlackUser(slackUserId: string): UserMapping | null {
193 return db
194 .query("SELECT * FROM user_mappings WHERE slack_user_id = ?")
195 .get(slackUserId) as UserMapping | null;
196 },
197
198 getByIrcNick(ircNick: string): UserMapping | null {
199 return db
200 .query("SELECT * FROM user_mappings WHERE irc_nick = ?")
201 .get(ircNick) as UserMapping | null;
202 },
203
204 create(slackUserId: string, ircNick: string): void {
205 db.run(
206 "INSERT OR REPLACE INTO user_mappings (slack_user_id, irc_nick) VALUES (?, ?)",
207 [slackUserId, ircNick],
208 );
209 },
210
211 delete(slackUserId: string): void {
212 db.run("DELETE FROM user_mappings WHERE slack_user_id = ?", [slackUserId]);
213 },
214};
215
216export interface ThreadInfo {
217 thread_ts: string;
218 thread_id: string;
219 slack_channel_id: string;
220 last_message_time: number;
221}
222
223export const threadTimestamps = {
224 get(threadTs: string): ThreadInfo | null {
225 return db
226 .query("SELECT * FROM thread_timestamps WHERE thread_ts = ?")
227 .get(threadTs) as ThreadInfo | null;
228 },
229
230 getByThreadId(threadId: string): ThreadInfo | null {
231 return db
232 .query("SELECT * FROM thread_timestamps WHERE thread_id = ?")
233 .get(threadId) as ThreadInfo | null;
234 },
235
236 update(
237 threadTs: string,
238 threadId: string,
239 slackChannelId: string,
240 timestamp: number,
241 ): void {
242 db.run(
243 "INSERT OR REPLACE INTO thread_timestamps (thread_ts, thread_id, slack_channel_id, last_message_time) VALUES (?, ?, ?, ?)",
244 [threadTs, threadId, slackChannelId, timestamp],
245 );
246 },
247
248 cleanup(olderThan: number): void {
249 db.run("DELETE FROM thread_timestamps WHERE last_message_time < ?", [
250 olderThan,
251 ]);
252 },
253};
254
255export default db;