Coverage for slidge/db/models.py: 97%

1import warnings 

2from datetime import datetime 

3from enum import IntEnum 

4from pathlib import Path 

5from typing import Any 

6 

7import sqlalchemy as sa 

8from slixmpp import JID 

9from slixmpp.types import MucAffiliation, MucRole 

10from sqlalchemy import JSON, Column, ForeignKey, Index, Table, UniqueConstraint 

11from sqlalchemy.orm import Mapped, mapped_column, relationship 

12 

13from ..core import config 

14from ..util.types import AvatarMetadata, ClientType, Hat, MucType 

15from .meta import Base, JSONSerializable, JSONSerializableTypes 

16 

17 

18class ArchivedMessageSource(IntEnum): 

19 """ 

20 Whether an archived message comes from ``LegacyMUC.backfill()`` or was received 

21 as a "live" message. 

22 """ 

23 

24 LIVE = 1 

25 BACKFILL = 2 

26 

27 

28class _JidMixin: 

29 jid_localpart: Mapped[str] 

30 

31 @property 

32 def jid(self) -> JID: 

33 return JID(f"{self.jid_localpart}@{config.JID}") 

34 

35 

36class GatewayUser(Base): 

37 """ 

38 A user, registered to the gateway component. 

39 """ 

40 

41 __tablename__ = "user_account" 

42 id: Mapped[int] = mapped_column(primary_key=True) 

43 jid: Mapped[JID] = mapped_column(unique=True) 

44 registration_date: Mapped[datetime] = mapped_column( 

45 sa.DateTime, server_default=sa.func.now() 

46 ) 

47 

48 legacy_module_data: Mapped[JSONSerializable] = mapped_column(default={}) 

49 """ 

50 Arbitrary non-relational data that legacy modules can use 

51 """ 

52 preferences: Mapped[JSONSerializable] = mapped_column(default={}) 

53 avatar_hash: Mapped[str | None] = mapped_column(default=None) 

54 """ 

55 Hash of the user's avatar, to avoid re-publishing the same avatar on the 

56 legacy network 

57 """ 

58 

59 contacts: Mapped[list["Contact"]] = relationship( 

60 back_populates="user", cascade="all, delete-orphan" 

61 ) 

62 rooms: Mapped[list["Room"]] = relationship( 

63 back_populates="user", cascade="all, delete-orphan" 

64 ) 

65 attachments: Mapped[list["Attachment"]] = relationship(cascade="all, delete-orphan") 

66 spaces: Mapped[list["Space"]] = relationship( 

67 back_populates="user", cascade="all, delete-orphan" 

68 ) 

69 

70 def __repr__(self) -> str: 

71 return f"User(id={self.id!r}, jid={self.jid!r})" 

72 

73 def get(self, field: str, default: str = "") -> JSONSerializableTypes: 

74 # """ 

75 # Get fields from the registration form (required to comply with slixmpp backend protocol) 

76 # 

77 # :param field: Name of the field 

78 # :param default: Default value to return if the field is not present 

79 # 

80 # :return: Value of the field 

81 # """ 

82 return self.legacy_module_data.get(field, default) 

83 

84 @property 

85 def registration_form(self) -> dict[str, Any]: 

86 # Kept for retrocompat, should be 

87 # FIXME: delete me 

88 warnings.warn( 

89 "GatewayUser.registration_form is deprecated.", DeprecationWarning 

90 ) 

91 return self.legacy_module_data 

92 

93 

94class Avatar(Base): 

95 """ 

96 Avatars of contacts, rooms and participants. 

97 

98 To comply with XEPs, we convert them all to PNG before storing them. 

99 """ 

100 

101 __tablename__ = "avatar" 

102 

103 id: Mapped[int] = mapped_column(primary_key=True) 

104 

105 hash: Mapped[str] = mapped_column(unique=True) 

106 height: Mapped[int] = mapped_column() 

107 width: Mapped[int] = mapped_column() 

108 

109 legacy_id: Mapped[str | None] = mapped_column(unique=True, nullable=True) 

110 

111 http_id: Mapped[str | None] = mapped_column(nullable=True) 

112 http_type: Mapped[str | None] = mapped_column(nullable=True) 

113 http_bytes: Mapped[int | None] = mapped_column(nullable=True) 

114 http_url: Mapped[str | None] = mapped_column(nullable=True) 

115 http_height: Mapped[int | None] = mapped_column(nullable=True) 

116 http_width: Mapped[int | None] = mapped_column(nullable=True) 

117 

118 # this is only used when avatars are available as HTTP URLs and do not 

119 # have a legacy_id 

120 url: Mapped[str | None] = mapped_column(unique=True, default=None) 

121 etag: Mapped[str | None] = mapped_column(default=None) 

122 last_modified: Mapped[str | None] = mapped_column(default=None) 

123 

124 contacts: Mapped[list["Contact"]] = relationship(back_populates="avatar") 

125 rooms: Mapped[list["Room"]] = relationship(back_populates="avatar") 

126 

127 def set_http_metadata(self, meta: AvatarMetadata | None) -> None: 

128 if meta is None: 

129 return 

130 self.http_id = meta.id 

131 self.http_bytes = meta.bytes 

132 self.http_type = f"image/{meta.type}" 

133 self.http_url = meta.url 

134 self.http_height = meta.height 

135 self.http_width = meta.width 

136 

137 

138space_owner_association = Table( 

139 "space_owner_association", 

140 Base.metadata, 

141 Column("space_id", ForeignKey("space.id"), primary_key=True), 

142 Column("contact_id", ForeignKey("contact.id"), primary_key=True), 

143) 

144 

145 

146class Contact(Base, _JidMixin): 

147 """ 

148 Legacy contacts 

149 """ 

150 

151 __tablename__ = "contact" 

152 __table_args__ = ( 

153 UniqueConstraint( 

154 "user_account_id", "legacy_id", name="uq_contact_user_account_id_legacy_id" 

155 ), 

156 UniqueConstraint( 

157 "user_account_id", 

158 "jid_localpart", 

159 name="uq_contact_user_account_id_jid_localpart", 

160 ), 

161 ) 

162 

163 id: Mapped[int] = mapped_column(primary_key=True) 

164 user_account_id: Mapped[int] = mapped_column(ForeignKey("user_account.id")) 

165 user: Mapped[GatewayUser] = relationship(lazy=True, back_populates="contacts") 

166 legacy_id: Mapped[str] = mapped_column(nullable=False) 

167 

168 jid_localpart: Mapped[str] = mapped_column(nullable=False) 

169 

170 avatar_id: Mapped[int | None] = mapped_column( 

171 ForeignKey("avatar.id"), nullable=True 

172 ) 

173 avatar: Mapped[Avatar | None] = relationship(lazy=False, back_populates="contacts") 

174 

175 nick: Mapped[str | None] = mapped_column(nullable=True) 

176 

177 cached_presence: Mapped[bool] = mapped_column(default=False) 

178 last_seen: Mapped[datetime | None] = mapped_column(nullable=True) 

179 ptype: Mapped[str | None] = mapped_column(nullable=True) 

180 pstatus: Mapped[str | None] = mapped_column(nullable=True) 

181 pshow: Mapped[str | None] = mapped_column(nullable=True) 

182 caps_ver: Mapped[str | None] = mapped_column(nullable=True) 

183 

184 is_friend: Mapped[bool] = mapped_column(default=False) 

185 added_to_roster: Mapped[bool] = mapped_column(default=False) 

186 sent_order: Mapped[list["ContactSent"]] = relationship( 

187 back_populates="contact", cascade="all, delete-orphan" 

188 ) 

189 

190 extra_attributes: Mapped[JSONSerializable | None] = mapped_column( 

191 default=None, nullable=True 

192 ) 

193 updated: Mapped[bool] = mapped_column(default=False) 

194 

195 vcard: Mapped[str | None] = mapped_column() 

196 vcard_fetched: Mapped[bool] = mapped_column(default=False) 

197 

198 participants: Mapped[list["Participant"]] = relationship(back_populates="contact") 

199 

200 client_type: Mapped[ClientType] = mapped_column(nullable=False, default="pc") 

201 

202 messages: Mapped[list["DirectMessages"]] = relationship( 

203 cascade="all, delete-orphan" 

204 ) 

205 threads: Mapped[list["DirectThreads"]] = relationship(cascade="all, delete-orphan") 

206 

207 spaces_created: Mapped[list["Space"]] = relationship( 

208 back_populates="creator", 

209 cascade="all, delete-orphan", 

210 ) 

211 spaces_owned: Mapped[list["Space"]] = relationship( 

212 back_populates="owners", 

213 secondary=space_owner_association, 

214 ) 

215 

216 last_sent_msg_legacy_id: Mapped[str | None] = mapped_column() 

217 last_sent_msg_date: Mapped[datetime | None] = mapped_column() 

218 

219 

220class ContactSent(Base): 

221 """ 

222 Keep track of XMPP msg ids sent by a specific contact for networks in which 

223 all messages need to be marked as read. 

224 

225 (XMPP displayed markers convey a "read up to here" semantic.) 

226 """ 

227 

228 __tablename__ = "contact_sent" 

229 __table_args__ = ( 

230 UniqueConstraint( 

231 "contact_id", "msg_id", name="uq_contact_sent_contact_id_msg_id" 

232 ), 

233 ) 

234 

235 id: Mapped[int] = mapped_column(primary_key=True) 

236 contact_id: Mapped[int] = mapped_column(ForeignKey("contact.id")) 

237 contact: Mapped[Contact] = relationship(back_populates="sent_order") 

238 msg_id: Mapped[str] = mapped_column() 

239 

240 

241class Room(Base, _JidMixin): 

242 """ 

243 Legacy room 

244 """ 

245 

246 __table_args__ = ( 

247 UniqueConstraint( 

248 "user_account_id", "legacy_id", name="uq_room_user_account_id_legacy_id" 

249 ), 

250 UniqueConstraint( 

251 "user_account_id", 

252 "jid_localpart", 

253 name="uq_room_user_account_id_jid_localpart", 

254 ), 

255 ) 

256 

257 __tablename__ = "room" 

258 id: Mapped[int] = mapped_column(primary_key=True) 

259 user_account_id: Mapped[int] = mapped_column(ForeignKey("user_account.id")) 

260 user: Mapped[GatewayUser] = relationship(lazy=True, back_populates="rooms") 

261 legacy_id: Mapped[str] = mapped_column(nullable=False) 

262 

263 jid_localpart: Mapped[str] = mapped_column(nullable=False) 

264 

265 avatar_id: Mapped[int | None] = mapped_column( 

266 ForeignKey("avatar.id"), nullable=True 

267 ) 

268 avatar: Mapped[Avatar | None] = relationship(lazy=False, back_populates="rooms") 

269 

270 name: Mapped[str | None] = mapped_column(nullable=True) 

271 description: Mapped[str | None] = mapped_column(nullable=True) 

272 subject: Mapped[str | None] = mapped_column(nullable=True) 

273 subject_date: Mapped[datetime | None] = mapped_column(nullable=True) 

274 subject_setter: Mapped[str | None] = mapped_column(nullable=True) 

275 

276 n_participants: Mapped[int | None] = mapped_column(default=None) 

277 

278 muc_type: Mapped[MucType] = mapped_column(default=MucType.CHANNEL) 

279 

280 user_nick: Mapped[str | None] = mapped_column() 

281 user_resources: Mapped[str | None] = mapped_column(nullable=True) 

282 

283 participants_filled: Mapped[bool] = mapped_column(default=False) 

284 history_filled: Mapped[bool] = mapped_column(default=False) 

285 

286 extra_attributes: Mapped[JSONSerializable | None] = mapped_column(default=None) 

287 updated: Mapped[bool] = mapped_column(default=False) 

288 

289 participants: Mapped[list["Participant"]] = relationship( 

290 back_populates="room", 

291 primaryjoin="Participant.room_id == Room.id", 

292 cascade="all, delete-orphan", 

293 ) 

294 

295 archive: Mapped[list["ArchivedMessage"]] = relationship( 

296 cascade="all, delete-orphan" 

297 ) 

298 

299 messages: Mapped[list["GroupMessages"]] = relationship(cascade="all, delete-orphan") 

300 threads: Mapped[list["GroupThreads"]] = relationship(cascade="all, delete-orphan") 

301 

302 space_id: Mapped[int | None] = mapped_column(ForeignKey("space.id"), nullable=True) 

303 space: Mapped["Space"] = relationship(back_populates="rooms", lazy=False) 

304 

305 

306class ArchivedMessage(Base): 

307 """ 

308 Messages of rooms, that we store to act as a MAM server 

309 """ 

310 

311 __tablename__ = "mam" 

312 __table_args__ = ( 

313 UniqueConstraint("room_id", "stanza_id", name="uq_mam_room_id_stanza_id"), 

314 ) 

315 

316 id: Mapped[int] = mapped_column(primary_key=True) 

317 room_id: Mapped[int] = mapped_column(ForeignKey("room.id"), nullable=False) 

318 room: Mapped[Room] = relationship(lazy=True, back_populates="archive") 

319 

320 stanza_id: Mapped[str] = mapped_column(nullable=False) 

321 timestamp: Mapped[datetime] = mapped_column(nullable=False) 

322 author_jid: Mapped[JID] = mapped_column(nullable=False) 

323 source: Mapped[ArchivedMessageSource] = mapped_column(nullable=False) 

324 legacy_id: Mapped[str | None] = mapped_column(nullable=True) 

325 

326 stanza: Mapped[str] = mapped_column(nullable=False) 

327 

328 displayed_by_user: Mapped[bool] = mapped_column(default=False, nullable=True) 

329 

330 

331class _LegacyToXmppIdsBase: 

332 """ 

333 XMPP-client generated IDs, and mapping to the corresponding legacy IDs. 

334 

335 A single legacy ID can map to several XMPP ids. 

336 """ 

337 

338 id: Mapped[int] = mapped_column(primary_key=True) 

339 legacy_id: Mapped[str] = mapped_column(nullable=False) 

340 xmpp_id: Mapped[str] = mapped_column(nullable=False) 

341 

342 

343class DirectMessages(_LegacyToXmppIdsBase, Base): 

344 __tablename__ = "direct_msg" 

345 __table_args__ = (Index("ix_direct_msg_legacy_id", "legacy_id", "foreign_key"),) 

346 foreign_key: Mapped[int] = mapped_column(ForeignKey("contact.id"), nullable=False) 

347 

348 

349class GroupMessages(_LegacyToXmppIdsBase, Base): 

350 __tablename__ = "group_msg" 

351 __table_args__ = (Index("ix_group_msg_legacy_id", "legacy_id", "foreign_key"),) 

352 foreign_key: Mapped[int] = mapped_column(ForeignKey("room.id"), nullable=False) 

353 

354 

355class GroupMessagesOrigin(_LegacyToXmppIdsBase, Base): 

356 """ 

357 This maps "origin ids" <message id=XXX> to legacy message IDs 

358 We need that for message corrections and retractions, which do not reference 

359 messages by their "Unique and Stable Stanza IDs (XEP-0359)" 

360 """ 

361 

362 __tablename__ = "group_msg_origin" 

363 __table_args__ = ( 

364 Index("ix_group_msg_origin_legacy_id", "legacy_id", "foreign_key"), 

365 ) 

366 foreign_key: Mapped[int] = mapped_column(ForeignKey("room.id"), nullable=False) 

367 

368 

369class DirectThreads(_LegacyToXmppIdsBase, Base): 

370 __tablename__ = "direct_thread" 

371 __table_args__ = (Index("ix_direct_direct_thread_id", "legacy_id", "foreign_key"),) 

372 foreign_key: Mapped[int] = mapped_column(ForeignKey("contact.id"), nullable=False) 

373 

374 

375class GroupThreads(_LegacyToXmppIdsBase, Base): 

376 __tablename__ = "group_thread" 

377 __table_args__ = (Index("ix_direct_group_thread_id", "legacy_id", "foreign_key"),) 

378 foreign_key: Mapped[int] = mapped_column(ForeignKey("room.id"), nullable=False) 

379 

380 

381class Attachment(Base): 

382 """ 

383 Legacy attachments 

384 """ 

385 

386 __tablename__ = "attachment" 

387 __table_args__ = ( 

388 UniqueConstraint( 

389 "user_account_id", 

390 "legacy_file_id", 

391 name="uq_attachment_user_account_id_legacy_file_id", 

392 ), 

393 ) 

394 

395 id: Mapped[int] = mapped_column(primary_key=True) 

396 user_account_id: Mapped[int] = mapped_column(ForeignKey("user_account.id")) 

397 user: Mapped[GatewayUser] = relationship(back_populates="attachments") 

398 

399 legacy_file_id: Mapped[str | None] = mapped_column(index=True, nullable=True) 

400 url: Mapped[str] = mapped_column(index=True, nullable=False) 

401 sims: Mapped[str | None] = mapped_column() 

402 sfs: Mapped[str | None] = mapped_column() 

403 

404 @property 

405 def local_path(self) -> Path: 

406 assert config.NO_UPLOAD_PATH is not None 

407 assert config.NO_UPLOAD_URL_PREFIX is not None 

408 return Path(config.NO_UPLOAD_PATH) / self.url.removeprefix( 

409 config.NO_UPLOAD_URL_PREFIX 

410 ) 

411 

412 

413class Participant(Base): 

414 __tablename__ = "participant" 

415 __table_args__ = ( 

416 UniqueConstraint("room_id", "resource", name="uq_participant_room_id_resource"), 

417 UniqueConstraint( 

418 "room_id", "contact_id", name="uq_participant_room_id_contact_id" 

419 ), 

420 UniqueConstraint( 

421 "room_id", "occupant_id", name="uq_participant_room_id_occupant_id" 

422 ), 

423 ) 

424 

425 id: Mapped[int] = mapped_column(primary_key=True) 

426 

427 room_id: Mapped[int] = mapped_column(ForeignKey("room.id"), nullable=False) 

428 room: Mapped[Room] = relationship( 

429 lazy=False, back_populates="participants", primaryjoin=Room.id == room_id 

430 ) 

431 

432 contact_id: Mapped[int | None] = mapped_column( 

433 ForeignKey("contact.id"), nullable=True 

434 ) 

435 contact: Mapped[Contact | None] = relationship( 

436 lazy=False, back_populates="participants" 

437 ) 

438 

439 occupant_id: Mapped[str] = mapped_column(nullable=False) 

440 

441 is_user: Mapped[bool] = mapped_column(default=False) 

442 

443 affiliation: Mapped[MucAffiliation] = mapped_column( 

444 default="member", nullable=False 

445 ) 

446 role: Mapped[MucRole] = mapped_column(default="participant", nullable=False) 

447 

448 presence_sent: Mapped[bool] = mapped_column(default=False) 

449 

450 resource: Mapped[str] = mapped_column(nullable=False) 

451 nickname: Mapped[str] = mapped_column(nullable=False, default=None) 

452 nickname_no_illegal: Mapped[str] = mapped_column(nullable=False, default=None) 

453 

454 hats: Mapped[list[Hat]] = mapped_column(JSON, default=list) 

455 

456 extra_attributes: Mapped[JSONSerializable | None] = mapped_column(default=None) 

457 

458 def __init__(self, *args: object, **kwargs: object) -> None: 

459 super().__init__(*args, **kwargs) 

460 self.role = "participant" 

461 self.affiliation = "member" 

462 

463 

464class Bob(Base): 

465 __tablename__ = "bob" 

466 

467 id: Mapped[int] = mapped_column(primary_key=True) 

468 file_name: Mapped[str] = mapped_column(nullable=False) 

469 

470 sha_1: Mapped[str] = mapped_column(nullable=False, unique=True) 

471 sha_256: Mapped[str] = mapped_column(nullable=False, unique=True) 

472 sha_512: Mapped[str] = mapped_column(nullable=False, unique=True) 

473 

474 content_type: Mapped[str] = mapped_column(nullable=False) 

475 

476 

477class Space(Base): 

478 __tablename__ = "space" 

479 __table_args__ = ( 

480 UniqueConstraint( 

481 "user_account_id", "legacy_id", name="uq_space_user_account_id_legacy_id" 

482 ), 

483 ) 

484 

485 id: Mapped[int] = mapped_column(primary_key=True) 

486 

487 updated: Mapped[bool] = mapped_column(default=False, nullable=False) 

488 

489 user_account_id: Mapped[int] = mapped_column(ForeignKey("user_account.id")) 

490 user: Mapped[GatewayUser] = relationship(lazy=True, back_populates="spaces") 

491 

492 legacy_id: Mapped[str] = mapped_column(nullable=False) 

493 name: Mapped[str | None] = mapped_column(nullable=True) 

494 description: Mapped[str | None] = mapped_column(nullable=True) 

495 member_count: Mapped[int | None] = mapped_column(nullable=True) 

496 

497 creator_pk: Mapped[int | None] = mapped_column( 

498 ForeignKey("contact.id"), nullable=True 

499 ) 

500 creator: Mapped[Contact | None] = relationship(back_populates="spaces_created") 

501 

502 owners: Mapped[list[Contact]] = relationship( 

503 back_populates="spaces_owned", 

504 secondary=space_owner_association, 

505 ) 

506 

507 rooms: Mapped[list[Room]] = relationship(cascade="all, delete-orphan") 

508 

509 avatar_id: Mapped[int | None] = mapped_column( 

510 ForeignKey("avatar.id"), nullable=True 

511 ) 

512 avatar: Mapped[Avatar | None] = relationship(Avatar, foreign_keys=avatar_id) 

513 

514 banner_id: Mapped[int | None] = mapped_column( 

515 ForeignKey("avatar.id"), nullable=True 

516 ) 

517 banner: Mapped[Avatar | None] = relationship(Avatar, foreign_keys=banner_id)