5875
Os
ollama-space-molt-player / files
ollama-space-molt-player / packages / client / src / db.ts
5.1 KB
1import { Database } from "bun:sqlite"; 2import { Glob } from "bun"; 3import type { AccountDetails, Instance, MessageSender, StoredMessage, Username } from "./types.ts"; 4 5const MAX_MESSAGES = 5000; 6 7export class GameDatabase { 8 private db: Database; 9 private instanceId: string; 10 11 constructor(instanceId: string) { 12 this.instanceId = instanceId; 13 this.db = new Database(`memory-${instanceId}.sqlite`); 14 this.initSchema(); 15 this.initInstance(); 16 } 17 18 private initInstance(): void { 19 this.db.run("INSERT OR IGNORE INTO instances (instance_id, hint) VALUES (?, NULL)", [ 20 this.instanceId, 21 ]); 22 } 23 24 private initSchema(): void { 25 this.db.run(` 26 CREATE TABLE IF NOT EXISTS instances ( 27 instance_id TEXT PRIMARY KEY, 28 hint TEXT, 29 logged_out INTEGER NOT NULL DEFAULT 0 30 ) 31 `); 32 33 this.db.run(` 34 CREATE TABLE IF NOT EXISTS usernames ( 35 id INTEGER PRIMARY KEY AUTOINCREMENT, 36 username TEXT NOT NULL, 37 token TEXT NOT NULL, 38 prompt TEXT NOT NULL, 39 player_id TEXT NOT NULL, 40 instance_id TEXT NOT NULL, 41 last_used_on INTEGER NOT NULL, 42 FOREIGN KEY (instance_id) REFERENCES instances(instance_id) 43 ) 44 `); 45 46 this.db.run(` 47 CREATE TABLE IF NOT EXISTS messages ( 48 id INTEGER PRIMARY KEY AUTOINCREMENT, 49 tick INTEGER NOT NULL, 50 sender TEXT NOT NULL CHECK(sender IN ('server', 'client')), 51 data TEXT NOT NULL 52 ) 53 `); 54 55 this.db.run(` 56 CREATE INDEX IF NOT EXISTS idx_messages_tick 57 ON messages(tick DESC) 58 `); 59 60 this.db.run(` 61 CREATE TRIGGER IF NOT EXISTS truncate_messages 62 AFTER INSERT ON messages 63 BEGIN 64 DELETE FROM messages 65 WHERE id IN ( 66 SELECT id FROM messages 67 ORDER BY tick ASC 68 LIMIT MAX(0, (SELECT COUNT(*) FROM messages) - ${MAX_MESSAGES}) 69 ); 70 END 71 `); 72 } 73 74 saveUsername( 75 username: string, 76 token: string, 77 prompt: string, 78 playerId: string, 79 tick: number, 80 ): void { 81 this.db.run( 82 `INSERT INTO usernames (username, token, prompt, player_id, instance_id, last_used_on) 83 VALUES (?, ?, ?, ?, ?, ?)`, 84 [username, token, prompt, playerId, this.instanceId, tick], 85 ); 86 } 87 88 getUsernames(): Username[] { 89 const rows = this.db 90 .query< 91 Username, 92 [] 93 >("SELECT id, username, token, prompt, player_id, instance_id, last_used_on FROM usernames ORDER BY last_used_on DESC") 94 .all(); 95 return rows; 96 } 97 98 getActiveUsername(): Username | null { 99 const row = this.db 100 .query< 101 Username, 102 [] 103 >("SELECT id, username, token, prompt, player_id, instance_id, last_used_on FROM usernames ORDER BY last_used_on DESC LIMIT 1") 104 .get(); 105 return row || null; 106 } 107 108 updateUsernamePrompt(username: string, prompt: string): void { 109 this.db.run("UPDATE usernames SET prompt = ? WHERE username = ?", [prompt, username]); 110 } 111 112 updateUsernameLastUsed(username: string, tick: number): void { 113 this.db.run("UPDATE usernames SET last_used_on = ? WHERE username = ?", [tick, username]); 114 } 115 116 getInstance(): Instance { 117 const row = this.db 118 .query< 119 Instance, 120 [string] 121 >("SELECT instance_id, hint, logged_out FROM instances WHERE instance_id = ?") 122 .get(this.instanceId); 123 if (!row) { 124 throw new Error(`Instance ${this.instanceId} not found`); 125 } 126 return row; 127 } 128 129 isLoggedOut(): boolean { 130 const row = this.db 131 .query< 132 { logged_out: number }, 133 [string] 134 >("SELECT logged_out FROM instances WHERE instance_id = ?") 135 .get(this.instanceId); 136 return row?.logged_out === 1; 137 } 138 139 setLoggedOut(loggedOut: boolean): void { 140 this.db.run("UPDATE instances SET logged_out = ? WHERE instance_id = ?", [ 141 loggedOut ? 1 : 0, 142 this.instanceId, 143 ]); 144 } 145 146 updateHint(hint: string): void { 147 this.db.run("UPDATE instances SET hint = ? WHERE instance_id = ?", [hint, this.instanceId]); 148 } 149 150 saveMessage(tick: number, sender: MessageSender, data: object): void { 151 this.db.run("INSERT INTO messages (tick, sender, data) VALUES (?, ?, ?)", [ 152 tick, 153 sender, 154 JSON.stringify(data), 155 ]); 156 } 157 158 getMessages(limit = MAX_MESSAGES): StoredMessage[] { 159 const rows = this.db 160 .query< 161 { id: number; tick: number; sender: MessageSender; data: string }, 162 [number] 163 >("SELECT id, tick, sender, data FROM messages ORDER BY tick DESC LIMIT ?") 164 .all(limit); 165 166 return rows.map((row) => ({ 167 id: row.id, 168 tick: row.tick, 169 sender: row.sender, 170 data: row.data, 171 })); 172 } 173 174 getMessageCount(): number { 175 const result = this.db 176 .query<{ count: number }, []>("SELECT COUNT(*) as count FROM messages") 177 .get(); 178 return result?.count || 0; 179 } 180 181 clearMessages(): void { 182 this.db.run("DELETE FROM messages"); 183 } 184 185 close(): void { 186 this.db.close(); 187 } 188 189 getInstanceId(): string { 190 return this.instanceId; 191 } 192} 193 194export function generateInstanceId(): string { 195 const chars = "abcdefghijklmnopqrstuvwxyz0123456789"; 196 return Array.from({ length: 4 }, () => chars[Math.floor(Math.random() * chars.length)]).join(""); 197} 198 199export function listInstances(): string[] { 200 const glob = new Glob("memory-*.sqlite"); 201 const files = Array.from(glob.scanSync(".")); 202 return files.map((f: string) => f.slice(7, -7)); 203} 204 205export async function instanceExists(instanceId: string): Promise<boolean> { 206 const file = Bun.file(`memory-${instanceId}.sqlite`); 207 return await file.exists(); 208}