Mercurial > libervia-backend

comparison sat/memory/sqla_mapping.py @ 3537:f9a5b810f14d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
core (memory/storage): backend storage is now based on SQLAlchemy
author Goffi <goffi@goffi.org>
date Thu, 03 Jun 2021 15:20:47 +0200
parents
children 84ea57a8d6b3
comparison
equal deleted inserted replaced
3536:0985c47ffd96 3537:f9a5b810f14d
1 #!/usr/bin/env python3
2
3 # Libervia: an XMPP client
4 # Copyright (C) 2009-2021 Jérôme Poisson (goffi@goffi.org)
5
6 # This program is free software: you can redistribute it and/or modify
7 # it under the terms of the GNU Affero General Public License as published by
8 # the Free Software Foundation, either version 3 of the License, or
9 # (at your option) any later version.
10
11 # This program is distributed in the hope that it will be useful,
12 # but WITHOUT ANY WARRANTY; without even the implied warranty of
13 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 # GNU Affero General Public License for more details.
15
16 # You should have received a copy of the GNU Affero General Public License
17 # along with this program. If not, see <http://www.gnu.org/licenses/>.
18
19 import pickle
20 import json
21 from sqlalchemy import (
22 Column, Integer, Text, Float, Enum, ForeignKey, UniqueConstraint, Index,
23 )
24
25 from sqlalchemy.orm import declarative_base, relationship
26 from sqlalchemy.types import TypeDecorator
27 from twisted.words.protocols.jabber import jid
28 from datetime import datetime
29
30
31 Base = declarative_base()
32 # keys which are in message data extra but not stored in extra field this is
33 # because those values are stored in separate fields
34 NOT_IN_EXTRA = ('stanza_id', 'received_timestamp', 'update_uid')
35
36
37 class LegacyPickle(TypeDecorator):
38 """Handle troubles with data pickled by former version of SàT
39
40 This type is temporary until we do migration to a proper data type
41 """
42 # Blob is used on SQLite but gives errors when used here, while Text works fine
43 impl = Text
44 cache_ok = True
45
46 def process_bind_param(self, value, dialect):
47 if value is None:
48 return None
49 return pickle.dumps(value, 0)
50
51 def process_result_value(self, value, dialect):
52 if value is None:
53 return None
54 # value types are inconsistent (probably a consequence of Python 2/3 port
55 # and/or SQLite dynamic typing)
56 try:
57 value = value.encode()
58 except AttributeError:
59 pass
60 # "utf-8" encoding is needed to handle Python 2 pickled data
61 return pickle.loads(value, encoding="utf-8")
62
63
64 class Json(TypeDecorator):
65 """Handle JSON field in DB independant way"""
66 # Blob is used on SQLite but gives errors when used here, while Text works fine
67 impl = Text
68 cache_ok = True
69
70 def process_bind_param(self, value, dialect):
71 if value is None:
72 return None
73 return json.dumps(value)
74
75 def process_result_value(self, value, dialect):
76 if value is None:
77 return None
78 return json.loads(value)
79
80
81 class JsonDefaultDict(Json):
82 """Json type which convert NULL to empty dict instead of None"""
83
84 def process_result_value(self, value, dialect):
85 if value is None:
86 return {}
87 return json.loads(value)
88
89
90 class JID(TypeDecorator):
91 """Store twisted JID in text fields"""
92 impl = Text
93 cache_ok = True
94
95 def process_bind_param(self, value, dialect):
96 if value is None:
97 return None
98 return value.full()
99
100 def process_result_value(self, value, dialect):
101 if value is None:
102 return None
103 return jid.JID(value)
104
105
106 class Profile(Base):
107 __tablename__ = "profiles"
108
109 id = Column(Integer, primary_key=True)
110 name = Column(Text, unique=True)
111
112 params = relationship("ParamInd", back_populates="profile", passive_deletes=True)
113 private_data = relationship(
114 "PrivateInd", back_populates="profile", passive_deletes=True
115 )
116 private_bin_data = relationship(
117 "PrivateIndBin", back_populates="profile", passive_deletes=True
118 )
119
120
121 class Component(Base):
122 __tablename__ = "components"
123
124 profile_id = Column(ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True)
125 entry_point = Column(Text, nullable=False)
126 profile = relationship("Profile")
127
128
129 class MessageType(Base):
130 __tablename__ = "message_types"
131
132 type = Column(Text, primary_key=True)
133
134
135 class History(Base):
136 __tablename__ = "history"
137 __table_args__ = (
138 UniqueConstraint("profile_id", "stanza_id", "source", "dest"),
139 Index("history__profile_id_timestamp", "profile_id", "timestamp"),
140 Index(
141 "history__profile_id_received_timestamp", "profile_id", "received_timestamp"
142 )
143 )
144
145 uid = Column(Text, primary_key=True)
146 stanza_id = Column(Text)
147 update_uid = Column(Text)
148 profile_id = Column(ForeignKey("profiles.id", ondelete="CASCADE"))
149 source = Column(Text)
150 dest = Column(Text)
151 source_res = Column(Text)
152 dest_res = Column(Text)
153 timestamp = Column(Float, nullable=False)
154 received_timestamp = Column(Float)
155 type = Column(ForeignKey("message_types.type"))
156 extra = Column(LegacyPickle)
157
158 profile = relationship("Profile")
159 message_type = relationship("MessageType")
160 messages = relationship("Message", backref="history", passive_deletes=True)
161 subjects = relationship("Subject", backref="history", passive_deletes=True)
162 thread = relationship(
163 "Thread", uselist=False, back_populates="history", passive_deletes=True
164 )
165
166 def __init__(self, *args, **kwargs):
167 source_jid = kwargs.pop("source_jid", None)
168 if source_jid is not None:
169 kwargs["source"] = source_jid.userhost()
170 kwargs["source_res"] = source_jid.resource
171 dest_jid = kwargs.pop("dest_jid", None)
172 if dest_jid is not None:
173 kwargs["dest"] = dest_jid.userhost()
174 kwargs["dest_res"] = dest_jid.resource
175 super().__init__(*args, **kwargs)
176
177 @property
178 def source_jid(self) -> jid.JID:
179 return jid.JID(f"{self.source}/{self.source_res or ''}")
180
181 @source_jid.setter
182 def source_jid(self, source_jid: jid.JID) -> None:
183 self.source = source_jid.userhost
184 self.source_res = source_jid.resource
185
186 @property
187 def dest_jid(self):
188 return jid.JID(f"{self.dest}/{self.dest_res or ''}")
189
190 @dest_jid.setter
191 def dest_jid(self, dest_jid: jid.JID) -> None:
192 self.dest = dest_jid.userhost
193 self.dest_res = dest_jid.resource
194
195 def __repr__(self):
196 dt = datetime.fromtimestamp(self.timestamp)
197 return f"History<{self.source_jid.full()}->{self.dest_jid.full()} [{dt}]>"
198
199 def serialise(self):
200 extra = self.extra
201 if self.stanza_id is not None:
202 extra["stanza_id"] = self.stanza_id
203 if self.update_uid is not None:
204 extra["update_uid"] = self.update_uid
205 if self.received_timestamp is not None:
206 extra["received_timestamp"] = self.received_timestamp
207 if self.thread is not None:
208 extra["thread"] = self.thread.thread_id
209 if self.thread.parent_id is not None:
210 extra["thread_parent"] = self.thread.parent_id
211
212
213 return {
214 "from": f"{self.source}/{self.source_res}" if self.source_res
215 else self.source,
216 "to": f"{self.dest}/{self.dest_res}" if self.dest_res else self.dest,
217 "uid": self.uid,
218 "message": {m.language or '': m.message for m in self.messages},
219 "subject": {m.language or '': m.subject for m in self.subjects},
220 "type": self.type,
221 "extra": extra,
222 "timestamp": self.timestamp,
223 }
224
225 def as_tuple(self):
226 d = self.serialise()
227 return (
228 d['uid'], d['timestamp'], d['from'], d['to'], d['message'], d['subject'],
229 d['type'], d['extra']
230 )
231
232 @staticmethod
233 def debug_collection(history_collection):
234 for idx, history in enumerate(history_collection):
235 history.debug_msg(idx)
236
237 def debug_msg(self, idx=None):
238 """Print messages"""
239 dt = datetime.fromtimestamp(self.timestamp)
240 if idx is not None:
241 dt = f"({idx}) {dt}"
242 parts = []
243 parts.append(f"[{dt}]<{self.source_jid.full()}->{self.dest_jid.full()}> ")
244 for message in self.messages:
245 if message.language:
246 parts.append(f"[{message.language}] ")
247 parts.append(f"{message.message}\n")
248 print("".join(parts))
249
250
251 class Message(Base):
252 __tablename__ = "message"
253
254 id = Column(Integer, primary_key=True)
255 history_uid = Column(ForeignKey("history.uid", ondelete="CASCADE"), index=True)
256 message = Column(Text)
257 language = Column(Text)
258
259 def __repr__(self):
260 lang_str = f"[{self.language}]" if self.language else ""
261 msg = f"{self.message[:20]}…" if len(self.message)>20 else self.message
262 content = f"{lang_str}{msg}"
263 return f"Message<{content}>"
264
265
266 class Subject(Base):
267 __tablename__ = "subject"
268
269 id = Column(Integer, primary_key=True)
270 history_uid = Column(ForeignKey("history.uid", ondelete="CASCADE"), index=True)
271 subject = Column(Text)
272 language = Column(Text)
273
274 def __repr__(self):
275 lang_str = f"[{self.language}]" if self.language else ""
276 msg = f"{self.subject[:20]}…" if len(self.subject)>20 else self.subject
277 content = f"{lang_str}{msg}"
278 return f"Subject<{content}>"
279
280
281 class Thread(Base):
282 __tablename__ = "thread"
283
284 id = Column(Integer, primary_key=True)
285 history_uid = Column(ForeignKey("history.uid", ondelete="CASCADE"), index=True)
286 thread_id = Column(Text)
287 parent_id = Column(Text)
288
289 history = relationship("History", uselist=False, back_populates="thread")
290
291 def __repr__(self):
292 return f"Thread<{self.thread_id} [parent: {self.parent_id}]>"
293
294
295 class ParamGen(Base):
296 __tablename__ = "param_gen"
297
298 category = Column(Text, primary_key=True, nullable=False)
299 name = Column(Text, primary_key=True, nullable=False)
300 value = Column(Text)
301
302
303 class ParamInd(Base):
304 __tablename__ = "param_ind"
305
306 category = Column(Text, primary_key=True, nullable=False)
307 name = Column(Text, primary_key=True, nullable=False)
308 profile_id = Column(
309 ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True, nullable=False
310 )
311 value = Column(Text)
312
313 profile = relationship("Profile", back_populates="params")
314
315
316 class PrivateGen(Base):
317 __tablename__ = "private_gen"
318
319 namespace = Column(Text, primary_key=True, nullable=False)
320 key = Column(Text, primary_key=True, nullable=False)
321 value = Column(Text)
322
323
324 class PrivateInd(Base):
325 __tablename__ = "private_ind"
326
327 namespace = Column(Text, primary_key=True, nullable=False)
328 key = Column(Text, primary_key=True, nullable=False)
329 profile_id = Column(
330 ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True, nullable=False
331 )
332 value = Column(Text)
333
334 profile = relationship("Profile", back_populates="private_data")
335
336
337 class PrivateGenBin(Base):
338 __tablename__ = "private_gen_bin"
339
340 namespace = Column(Text, primary_key=True, nullable=False)
341 key = Column(Text, primary_key=True, nullable=False)
342 value = Column(LegacyPickle)
343
344
345 class PrivateIndBin(Base):
346 __tablename__ = "private_ind_bin"
347
348 namespace = Column(Text, primary_key=True, nullable=False)
349 key = Column(Text, primary_key=True, nullable=False)
350 profile_id = Column(
351 ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True, nullable=False
352 )
353 value = Column(LegacyPickle)
354
355 profile = relationship("Profile", back_populates="private_bin_data")
356
357
358 class File(Base):
359 __tablename__ = "files"
360 __table_args__ = (
361 Index("files__profile_id_owner_parent", "profile_id", "owner", "parent"),
362 Index(
363 "files__profile_id_owner_media_type_media_subtype",
364 "profile_id",
365 "owner",
366 "media_type",
367 "media_subtype"
368 )
369 )
370
371 id = Column(Text, primary_key=True, nullable=False)
372 public_id = Column(Text, unique=True)
373 version = Column(Text, primary_key=True, nullable=False)
374 parent = Column(Text, nullable=False)
375 type = Column(
376 Enum("file", "directory", create_constraint=True),
377 nullable=False,
378 server_default="file",
379 # name="file_type",
380 )
381 file_hash = Column(Text)
382 hash_algo = Column(Text)
383 name = Column(Text, nullable=False)
384 size = Column(Integer)
385 namespace = Column(Text)
386 media_type = Column(Text)
387 media_subtype = Column(Text)
388 created = Column(Float, nullable=False)
389 modified = Column(Float)
390 owner = Column(JID)
391 access = Column(JsonDefaultDict)
392 extra = Column(JsonDefaultDict)
393 profile_id = Column(ForeignKey("profiles.id", ondelete="CASCADE"))
394
395 profile = relationship("Profile")