File size: 6,461 Bytes
a571bd0 | 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 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 | from __future__ import annotations
import sqlite3
from dataclasses import dataclass
from datetime import datetime, timezone
UTC = timezone.utc
from pathlib import Path
from typing import Literal
MessageRole = Literal["user", "assistant"]
@dataclass(frozen=True)
class ChatSettings:
chat_id: int
model: str
custom_soul: str | None
@dataclass(frozen=True)
class MessageRecord:
role: MessageRole
user_id: int | None
user_name: str | None
text: str
created_at: str
class SQLiteStorage:
def __init__(self, path: Path) -> None:
self.path = path
def initialize(self) -> None:
self.path.parent.mkdir(parents=True, exist_ok=True)
with self._connect() as connection:
connection.executescript(
"""
CREATE TABLE IF NOT EXISTS chat_settings (
chat_id INTEGER PRIMARY KEY,
model TEXT NOT NULL,
custom_soul TEXT,
updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS messages (
id INTEGER PRIMARY KEY AUTOINCREMENT,
chat_id INTEGER NOT NULL,
telegram_message_id INTEGER,
role TEXT NOT NULL CHECK (role IN ('user', 'assistant')),
user_id INTEGER,
user_name TEXT,
text TEXT NOT NULL,
created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_messages_chat_id_id
ON messages(chat_id, id);
"""
)
def get_chat_settings(self, chat_id: int, default_model: str) -> ChatSettings:
with self._connect() as connection:
row = connection.execute(
"SELECT chat_id, model, custom_soul FROM chat_settings WHERE chat_id = ?",
(chat_id,),
).fetchone()
if row is None:
now = self._now()
connection.execute(
"""
INSERT INTO chat_settings(chat_id, model, custom_soul, updated_at)
VALUES (?, ?, NULL, ?)
""",
(chat_id, default_model, now),
)
return ChatSettings(chat_id=chat_id, model=default_model, custom_soul=None)
return ChatSettings(
chat_id=int(row["chat_id"]),
model=str(row["model"]),
custom_soul=row["custom_soul"],
)
def set_model(self, chat_id: int, model: str, default_model: str) -> None:
settings = self.get_chat_settings(chat_id, default_model)
with self._connect() as connection:
connection.execute(
"""
UPDATE chat_settings
SET model = ?, custom_soul = ?, updated_at = ?
WHERE chat_id = ?
""",
(model, settings.custom_soul, self._now(), chat_id),
)
def set_custom_soul(self, chat_id: int, soul: str, default_model: str) -> None:
settings = self.get_chat_settings(chat_id, default_model)
with self._connect() as connection:
connection.execute(
"""
UPDATE chat_settings
SET model = ?, custom_soul = ?, updated_at = ?
WHERE chat_id = ?
""",
(settings.model, soul, self._now(), chat_id),
)
def reset_custom_soul(self, chat_id: int, default_model: str) -> None:
settings = self.get_chat_settings(chat_id, default_model)
with self._connect() as connection:
connection.execute(
"""
UPDATE chat_settings
SET model = ?, custom_soul = NULL, updated_at = ?
WHERE chat_id = ?
""",
(settings.model, self._now(), chat_id),
)
def append_message(
self,
chat_id: int,
telegram_message_id: int | None,
role: MessageRole,
user_id: int | None,
user_name: str | None,
text: str,
) -> None:
with self._connect() as connection:
connection.execute(
"""
INSERT INTO messages(
chat_id,
telegram_message_id,
role,
user_id,
user_name,
text,
created_at
)
VALUES (?, ?, ?, ?, ?, ?, ?)
""",
(chat_id, telegram_message_id, role, user_id, user_name, text, self._now()),
)
def get_recent_messages(self, chat_id: int, limit: int) -> list[MessageRecord]:
with self._connect() as connection:
rows = connection.execute(
"""
SELECT role, user_id, user_name, text, created_at
FROM messages
WHERE chat_id = ?
ORDER BY id DESC
LIMIT ?
""",
(chat_id, limit),
).fetchall()
records = [self._row_to_message(row) for row in rows]
records.reverse()
return records
def clear_messages(self, chat_id: int) -> None:
with self._connect() as connection:
connection.execute("DELETE FROM messages WHERE chat_id = ?", (chat_id,))
def _connect(self) -> sqlite3.Connection:
connection = sqlite3.connect(self.path)
connection.row_factory = sqlite3.Row
return connection
@staticmethod
def _row_to_message(row: sqlite3.Row) -> MessageRecord:
role = str(row["role"])
if role not in ("user", "assistant"):
raise RuntimeError(f"Unknown message role in database: {role}")
user_id = row["user_id"]
return MessageRecord(
role=role,
user_id=None if user_id is None else int(user_id),
user_name=row["user_name"],
text=str(row["text"]),
created_at=str(row["created_at"]),
)
@staticmethod
def _now() -> str:
return datetime.now(UTC).isoformat()
|