Coverage for slidge/db/alembic/versions/cef02a8b1451_initial_schema.py: 59%

1"""Initial schema 

2 

3Revision ID: cef02a8b1451 

4Revises: 

5Create Date: 2025-08-28 12:48:16.890606 

6 

7""" 

8 

9from collections.abc import Sequence 

10 

11import sqlalchemy as sa 

12from alembic import op 

13 

14import slidge 

15 

16# revision identifiers, used by Alembic. 

17revision: str = "cef02a8b1451" 

18down_revision: str | None = None 

19branch_labels: str | Sequence[str] | None = None 

20depends_on: str | Sequence[str] | None = None 

21 

22 

23def upgrade() -> None: 

24 # ### commands auto generated by Alembic - please adjust! ### 

25 op.create_table( 

26 "avatar", 

27 sa.Column("id", sa.Integer(), nullable=False), 

28 sa.Column("hash", sa.String(), nullable=False), 

29 sa.Column("height", sa.Integer(), nullable=False), 

30 sa.Column("width", sa.Integer(), nullable=False), 

31 sa.Column("legacy_id", sa.String(), nullable=True), 

32 sa.Column("url", sa.String(), nullable=True), 

33 sa.Column("etag", sa.String(), nullable=True), 

34 sa.Column("last_modified", sa.String(), nullable=True), 

35 sa.PrimaryKeyConstraint("id", name=op.f("pk_avatar")), 

36 sa.UniqueConstraint("hash", name=op.f("uq_avatar_hash")), 

37 sa.UniqueConstraint("legacy_id", name=op.f("uq_avatar_legacy_id")), 

38 ) 

39 op.create_table( 

40 "bob", 

41 sa.Column("id", sa.Integer(), nullable=False), 

42 sa.Column("file_name", sa.String(), nullable=False), 

43 sa.Column("sha_1", sa.String(), nullable=False), 

44 sa.Column("sha_256", sa.String(), nullable=False), 

45 sa.Column("sha_512", sa.String(), nullable=False), 

46 sa.Column("content_type", sa.String(), nullable=False), 

47 sa.PrimaryKeyConstraint("id", name=op.f("pk_bob")), 

48 sa.UniqueConstraint("sha_1", name=op.f("uq_bob_sha_1")), 

49 sa.UniqueConstraint("sha_256", name=op.f("uq_bob_sha_256")), 

50 sa.UniqueConstraint("sha_512", name=op.f("uq_bob_sha_512")), 

51 ) 

52 op.create_table( 

53 "user_account", 

54 sa.Column("id", sa.Integer(), nullable=False), 

55 sa.Column("jid", slidge.db.meta.JIDType(), nullable=False), 

56 sa.Column( 

57 "registration_date", 

58 sa.DateTime(), 

59 server_default=sa.text("(CURRENT_TIMESTAMP)"), 

60 nullable=False, 

61 ), 

62 sa.Column( 

63 "legacy_module_data", slidge.db.meta.JSONEncodedDict(), nullable=False 

64 ), 

65 sa.Column("preferences", slidge.db.meta.JSONEncodedDict(), nullable=False), 

66 sa.Column("avatar_hash", sa.String(), nullable=True), 

67 sa.PrimaryKeyConstraint("id", name=op.f("pk_user_account")), 

68 sa.UniqueConstraint("jid", name=op.f("uq_user_account_jid")), 

69 ) 

70 op.create_table( 

71 "attachment", 

72 sa.Column("id", sa.Integer(), nullable=False), 

73 sa.Column("user_account_id", sa.Integer(), nullable=False), 

74 sa.Column("legacy_file_id", sa.String(), nullable=True), 

75 sa.Column("url", sa.String(), nullable=False), 

76 sa.Column("sims", sa.String(), nullable=True), 

77 sa.Column("sfs", sa.String(), nullable=True), 

78 sa.ForeignKeyConstraint( 

79 ["user_account_id"], 

80 ["user_account.id"], 

81 name=op.f("fk_attachment_user_account_id_user_account"), 

82 ), 

83 sa.PrimaryKeyConstraint("id", name=op.f("pk_attachment")), 

84 sa.UniqueConstraint( 

85 "user_account_id", 

86 "legacy_file_id", 

87 name="uq_attachment_user_account_id_legacy_file_id", 

88 ), 

89 ) 

90 with op.batch_alter_table("attachment", schema=None) as batch_op: 

91 batch_op.create_index( 

92 batch_op.f("ix_attachment_legacy_file_id"), ["legacy_file_id"], unique=False 

93 ) 

94 batch_op.create_index(batch_op.f("ix_attachment_url"), ["url"], unique=False) 

95 

96 op.create_table( 

97 "contact", 

98 sa.Column("id", sa.Integer(), nullable=False), 

99 sa.Column("user_account_id", sa.Integer(), nullable=False), 

100 sa.Column("legacy_id", sa.String(), nullable=False), 

101 sa.Column("jid", slidge.db.meta.JIDType(), nullable=False), 

102 sa.Column("avatar_id", sa.Integer(), nullable=True), 

103 sa.Column("nick", sa.String(), nullable=True), 

104 sa.Column("cached_presence", sa.Boolean(), nullable=False), 

105 sa.Column("last_seen", sa.DateTime(), nullable=True), 

106 sa.Column("ptype", sa.String(), nullable=True), 

107 sa.Column("pstatus", sa.String(), nullable=True), 

108 sa.Column("pshow", sa.String(), nullable=True), 

109 sa.Column("caps_ver", sa.String(), nullable=True), 

110 sa.Column("is_friend", sa.Boolean(), nullable=False), 

111 sa.Column("added_to_roster", sa.Boolean(), nullable=False), 

112 sa.Column("extra_attributes", slidge.db.meta.JSONEncodedDict(), nullable=True), 

113 sa.Column("updated", sa.Boolean(), nullable=False), 

114 sa.Column("vcard", sa.String(), nullable=True), 

115 sa.Column("vcard_fetched", sa.Boolean(), nullable=False), 

116 sa.Column( 

117 "client_type", 

118 sa.Enum( 

119 "bot", 

120 "console", 

121 "game", 

122 "handheld", 

123 "pc", 

124 "phone", 

125 "sms", 

126 "tablet", 

127 "web", 

128 native_enum=False, 

129 ), 

130 nullable=False, 

131 ), 

132 sa.ForeignKeyConstraint( 

133 ["avatar_id"], ["avatar.id"], name=op.f("fk_contact_avatar_id_avatar") 

134 ), 

135 sa.ForeignKeyConstraint( 

136 ["user_account_id"], 

137 ["user_account.id"], 

138 name=op.f("fk_contact_user_account_id_user_account"), 

139 ), 

140 sa.PrimaryKeyConstraint("id", name=op.f("pk_contact")), 

141 sa.UniqueConstraint( 

142 "user_account_id", "jid", name="uq_contact_user_account_id_jid" 

143 ), 

144 sa.UniqueConstraint( 

145 "user_account_id", "legacy_id", name="uq_contact_user_account_id_legacy_id" 

146 ), 

147 ) 

148 op.create_table( 

149 "room", 

150 sa.Column("id", sa.Integer(), nullable=False), 

151 sa.Column("user_account_id", sa.Integer(), nullable=False), 

152 sa.Column("legacy_id", sa.String(), nullable=False), 

153 sa.Column("jid", slidge.db.meta.JIDType(), nullable=False), 

154 sa.Column("avatar_id", sa.Integer(), nullable=True), 

155 sa.Column("name", sa.String(), nullable=True), 

156 sa.Column("description", sa.String(), nullable=True), 

157 sa.Column("subject", sa.String(), nullable=True), 

158 sa.Column("subject_date", sa.DateTime(), nullable=True), 

159 sa.Column("subject_setter", sa.String(), nullable=True), 

160 sa.Column("n_participants", sa.Integer(), nullable=True), 

161 sa.Column( 

162 "muc_type", 

163 sa.Enum("GROUP", "CHANNEL", "CHANNEL_NON_ANONYMOUS", name="muctype"), 

164 nullable=False, 

165 ), 

166 sa.Column("user_nick", sa.String(), nullable=True), 

167 sa.Column("user_resources", sa.String(), nullable=True), 

168 sa.Column("participants_filled", sa.Boolean(), nullable=False), 

169 sa.Column("history_filled", sa.Boolean(), nullable=False), 

170 sa.Column("extra_attributes", slidge.db.meta.JSONEncodedDict(), nullable=True), 

171 sa.Column("updated", sa.Boolean(), nullable=False), 

172 sa.ForeignKeyConstraint( 

173 ["avatar_id"], ["avatar.id"], name=op.f("fk_room_avatar_id_avatar") 

174 ), 

175 sa.ForeignKeyConstraint( 

176 ["user_account_id"], 

177 ["user_account.id"], 

178 name=op.f("fk_room_user_account_id_user_account"), 

179 ), 

180 sa.PrimaryKeyConstraint("id", name=op.f("pk_room")), 

181 sa.UniqueConstraint( 

182 "user_account_id", "jid", name="uq_room_user_account_id_jid" 

183 ), 

184 sa.UniqueConstraint( 

185 "user_account_id", "legacy_id", name="uq_room_user_account_id_legacy_id" 

186 ), 

187 ) 

188 op.create_table( 

189 "contact_sent", 

190 sa.Column("id", sa.Integer(), nullable=False), 

191 sa.Column("contact_id", sa.Integer(), nullable=False), 

192 sa.Column("msg_id", sa.String(), nullable=False), 

193 sa.ForeignKeyConstraint( 

194 ["contact_id"], 

195 ["contact.id"], 

196 name=op.f("fk_contact_sent_contact_id_contact"), 

197 ), 

198 sa.PrimaryKeyConstraint("id", name=op.f("pk_contact_sent")), 

199 sa.UniqueConstraint( 

200 "contact_id", "msg_id", name="uq_contact_sent_contact_id_msg_id" 

201 ), 

202 ) 

203 op.create_table( 

204 "direct_msg", 

205 sa.Column("foreign_key", sa.Integer(), nullable=False), 

206 sa.Column("id", sa.Integer(), nullable=False), 

207 sa.Column("legacy_id", sa.String(), nullable=False), 

208 sa.Column("xmpp_id", sa.String(), nullable=False), 

209 sa.ForeignKeyConstraint( 

210 ["foreign_key"], 

211 ["contact.id"], 

212 name=op.f("fk_direct_msg_foreign_key_contact"), 

213 ), 

214 sa.PrimaryKeyConstraint("id", name=op.f("pk_direct_msg")), 

215 ) 

216 with op.batch_alter_table("direct_msg", schema=None) as batch_op: 

217 batch_op.create_index( 

218 "ix_direct_msg_legacy_id", ["legacy_id", "foreign_key"], unique=False 

219 ) 

220 

221 op.create_table( 

222 "direct_thread", 

223 sa.Column("foreign_key", sa.Integer(), nullable=False), 

224 sa.Column("id", sa.Integer(), nullable=False), 

225 sa.Column("legacy_id", sa.String(), nullable=False), 

226 sa.Column("xmpp_id", sa.String(), nullable=False), 

227 sa.ForeignKeyConstraint( 

228 ["foreign_key"], 

229 ["contact.id"], 

230 name=op.f("fk_direct_thread_foreign_key_contact"), 

231 ), 

232 sa.PrimaryKeyConstraint("id", name=op.f("pk_direct_thread")), 

233 ) 

234 with op.batch_alter_table("direct_thread", schema=None) as batch_op: 

235 batch_op.create_index( 

236 "ix_direct_direct_thread_id", ["legacy_id", "foreign_key"], unique=False 

237 ) 

238 

239 op.create_table( 

240 "group_msg", 

241 sa.Column("foreign_key", sa.Integer(), nullable=False), 

242 sa.Column("id", sa.Integer(), nullable=False), 

243 sa.Column("legacy_id", sa.String(), nullable=False), 

244 sa.Column("xmpp_id", sa.String(), nullable=False), 

245 sa.ForeignKeyConstraint( 

246 ["foreign_key"], ["room.id"], name=op.f("fk_group_msg_foreign_key_room") 

247 ), 

248 sa.PrimaryKeyConstraint("id", name=op.f("pk_group_msg")), 

249 ) 

250 with op.batch_alter_table("group_msg", schema=None) as batch_op: 

251 batch_op.create_index( 

252 "ix_group_msg_legacy_id", ["legacy_id", "foreign_key"], unique=False 

253 ) 

254 

255 op.create_table( 

256 "group_thread", 

257 sa.Column("foreign_key", sa.Integer(), nullable=False), 

258 sa.Column("id", sa.Integer(), nullable=False), 

259 sa.Column("legacy_id", sa.String(), nullable=False), 

260 sa.Column("xmpp_id", sa.String(), nullable=False), 

261 sa.ForeignKeyConstraint( 

262 ["foreign_key"], ["room.id"], name=op.f("fk_group_thread_foreign_key_room") 

263 ), 

264 sa.PrimaryKeyConstraint("id", name=op.f("pk_group_thread")), 

265 ) 

266 with op.batch_alter_table("group_thread", schema=None) as batch_op: 

267 batch_op.create_index( 

268 "ix_direct_group_thread_id", ["legacy_id", "foreign_key"], unique=False 

269 ) 

270 

271 op.create_table( 

272 "mam", 

273 sa.Column("id", sa.Integer(), nullable=False), 

274 sa.Column("room_id", sa.Integer(), nullable=False), 

275 sa.Column("stanza_id", sa.String(), nullable=False), 

276 sa.Column("timestamp", sa.DateTime(), nullable=False), 

277 sa.Column("author_jid", slidge.db.meta.JIDType(), nullable=False), 

278 sa.Column( 

279 "source", 

280 sa.Enum("LIVE", "BACKFILL", name="archivedmessagesource"), 

281 nullable=False, 

282 ), 

283 sa.Column("legacy_id", sa.String(), nullable=True), 

284 sa.Column("stanza", sa.String(), nullable=False), 

285 sa.ForeignKeyConstraint( 

286 ["room_id"], ["room.id"], name=op.f("fk_mam_room_id_room") 

287 ), 

288 sa.PrimaryKeyConstraint("id", name=op.f("pk_mam")), 

289 sa.UniqueConstraint("room_id", "stanza_id", name="uq_mam_room_id_stanza_id"), 

290 ) 

291 op.create_table( 

292 "participant", 

293 sa.Column("id", sa.Integer(), nullable=False), 

294 sa.Column("room_id", sa.Integer(), nullable=False), 

295 sa.Column("contact_id", sa.Integer(), nullable=True), 

296 sa.Column("is_user", sa.Boolean(), nullable=False), 

297 sa.Column( 

298 "affiliation", 

299 sa.Enum("outcast", "member", "admin", "owner", "none", native_enum=False), 

300 nullable=False, 

301 ), 

302 sa.Column( 

303 "role", 

304 sa.Enum("moderator", "participant", "visitor", "none", native_enum=False), 

305 nullable=False, 

306 ), 

307 sa.Column("presence_sent", sa.Boolean(), nullable=False), 

308 sa.Column("resource", sa.String(), nullable=False), 

309 sa.Column("nickname", sa.String(), nullable=False), 

310 sa.Column("nickname_no_illegal", sa.String(), nullable=False), 

311 sa.Column("hats", sa.JSON(), nullable=False), 

312 sa.Column("extra_attributes", slidge.db.meta.JSONEncodedDict(), nullable=True), 

313 sa.ForeignKeyConstraint( 

314 ["contact_id"], 

315 ["contact.id"], 

316 name=op.f("fk_participant_contact_id_contact"), 

317 ), 

318 sa.ForeignKeyConstraint( 

319 ["room_id"], ["room.id"], name=op.f("fk_participant_room_id_room") 

320 ), 

321 sa.PrimaryKeyConstraint("id", name=op.f("pk_participant")), 

322 sa.UniqueConstraint( 

323 "room_id", "contact_id", name="uq_participant_room_id_contact_id" 

324 ), 

325 sa.UniqueConstraint( 

326 "room_id", "resource", name="uq_participant_room_id_resource" 

327 ), 

328 ) 

329 # ### end Alembic commands ### 

330 

331 

332def downgrade() -> None: 

333 # ### commands auto generated by Alembic - please adjust! ### 

334 op.drop_table("participant") 

335 op.drop_table("mam") 

336 with op.batch_alter_table("group_thread", schema=None) as batch_op: 

337 batch_op.drop_index("ix_direct_group_thread_id") 

338 

339 op.drop_table("group_thread") 

340 with op.batch_alter_table("group_msg", schema=None) as batch_op: 

341 batch_op.drop_index("ix_group_msg_legacy_id") 

342 

343 op.drop_table("group_msg") 

344 with op.batch_alter_table("direct_thread", schema=None) as batch_op: 

345 batch_op.drop_index("ix_direct_direct_thread_id") 

346 

347 op.drop_table("direct_thread") 

348 with op.batch_alter_table("direct_msg", schema=None) as batch_op: 

349 batch_op.drop_index("ix_direct_msg_legacy_id") 

350 

351 op.drop_table("direct_msg") 

352 op.drop_table("contact_sent") 

353 op.drop_table("room") 

354 op.drop_table("contact") 

355 with op.batch_alter_table("attachment", schema=None) as batch_op: 

356 batch_op.drop_index(batch_op.f("ix_attachment_url")) 

357 batch_op.drop_index(batch_op.f("ix_attachment_legacy_file_id")) 

358 

359 op.drop_table("attachment") 

360 op.drop_table("user_account") 

361 op.drop_table("bob") 

362 op.drop_table("avatar") 

363 # ### end Alembic commands ###