Mercurial > libervia-backend
comparison libervia/backend/memory/sqla_mapping.py @ 4130:02f0adc745c6
core: notifications implementation, first draft:
add a new table for notifications, and methods/bridge methods to manipulate them.
author | Goffi <goffi@goffi.org> |
---|---|
date | Mon, 16 Oct 2023 17:29:31 +0200 |
parents | 684ba556a617 |
children | 23d21daed216 |
comparison
equal
deleted
inserted
replaced
4129:51744ad00a42 | 4130:02f0adc745c6 |
---|---|
14 # GNU Affero General Public License for more details. | 14 # GNU Affero General Public License for more details. |
15 | 15 |
16 # You should have received a copy of the GNU Affero General Public License | 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/>. | 17 # along with this program. If not, see <http://www.gnu.org/licenses/>. |
18 | 18 |
19 from typing import Dict, Any | |
20 from datetime import datetime | 19 from datetime import datetime |
21 import enum | 20 import enum |
22 import json | 21 import json |
23 import pickle | 22 import pickle |
24 import time | 23 import time |
24 from typing import Any, Dict | |
25 | 25 |
26 from sqlalchemy import ( | 26 from sqlalchemy import ( |
27 Boolean, | 27 Boolean, |
28 Column, | 28 Column, |
29 DDL, | 29 DDL, |
43 from sqlalchemy.sql.functions import now | 43 from sqlalchemy.sql.functions import now |
44 from sqlalchemy.types import TypeDecorator | 44 from sqlalchemy.types import TypeDecorator |
45 from twisted.words.protocols.jabber import jid | 45 from twisted.words.protocols.jabber import jid |
46 from wokkel import generic | 46 from wokkel import generic |
47 | 47 |
48 from libervia.backend.core.constants import Const as C | |
49 | |
48 | 50 |
49 Base = declarative_base( | 51 Base = declarative_base( |
50 metadata=MetaData( | 52 metadata=MetaData( |
51 naming_convention={ | 53 naming_convention={ |
52 "ix": 'ix_%(column_0_label)s', | 54 "ix": "ix_%(column_0_label)s", |
53 "uq": "uq_%(table_name)s_%(column_0_name)s", | 55 "uq": "uq_%(table_name)s_%(column_0_name)s", |
54 "ck": "ck_%(table_name)s_%(constraint_name)s", | 56 "ck": "ck_%(table_name)s_%(constraint_name)s", |
55 "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s", | 57 "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s", |
56 "pk": "pk_%(table_name)s" | 58 "pk": "pk_%(table_name)s", |
57 } | 59 } |
58 ) | 60 ) |
59 ) | 61 ) |
60 # keys which are in message data extra but not stored in extra field this is | 62 # keys which are in message data extra but not stored in extra field this is |
61 # because those values are stored in separate fields | 63 # because those values are stored in separate fields |
62 NOT_IN_EXTRA = ('origin_id', 'stanza_id', 'received_timestamp', 'update_uid') | 64 NOT_IN_EXTRA = ("origin_id", "stanza_id", "received_timestamp", "update_uid") |
65 | |
66 | |
67 class Profiles(dict): | |
68 def __init__(self, *args, **kwargs): | |
69 super().__init__(*args, **kwargs) | |
70 self.id_to_profile = {v: k for k, v in self.items()} | |
71 | |
72 def __setitem__(self, key, value): | |
73 super().__setitem__(key, value) | |
74 self.id_to_profile[value] = key | |
75 | |
76 def __delitem__(self, key): | |
77 del self.id_to_profile[self[key]] | |
78 super().__delitem__(key) | |
79 | |
80 def update(self, *args, **kwargs): | |
81 super().update(*args, **kwargs) | |
82 self.id_to_profile = {v: k for k, v in self.items()} | |
83 | |
84 def clear(self): | |
85 super().clear() | |
86 self.id_to_profile.clear() | |
87 | |
88 | |
89 profiles = Profiles() | |
90 | |
91 | |
92 def get_profile_by_id( profile_id): | |
93 return profiles.id_to_profile.get(profile_id) | |
63 | 94 |
64 | 95 |
65 class SyncState(enum.Enum): | 96 class SyncState(enum.Enum): |
66 #: synchronisation is currently in progress | 97 #: synchronisation is currently in progress |
67 IN_PROGRESS = 1 | 98 IN_PROGRESS = 1 |
76 class SubscriptionState(enum.Enum): | 107 class SubscriptionState(enum.Enum): |
77 SUBSCRIBED = 1 | 108 SUBSCRIBED = 1 |
78 PENDING = 2 | 109 PENDING = 2 |
79 | 110 |
80 | 111 |
112 class NotificationType(enum.Enum): | |
113 chat = "chat" | |
114 blog = "blog" | |
115 calendar = "calendar" | |
116 file = "file" | |
117 call = "call" | |
118 service = "service" | |
119 other = "other" | |
120 | |
121 | |
122 class NotificationStatus(enum.Enum): | |
123 new = "new" | |
124 read = "read" | |
125 | |
126 | |
127 class NotificationPriority(enum.IntEnum): | |
128 LOW = 10 | |
129 MEDIUM = 20 | |
130 HIGH = 30 | |
131 URGENT = 40 | |
132 | |
133 | |
81 class LegacyPickle(TypeDecorator): | 134 class LegacyPickle(TypeDecorator): |
82 """Handle troubles with data pickled by former version of SàT | 135 """Handle troubles with data pickled by former version of SàT |
83 | 136 |
84 This type is temporary until we do migration to a proper data type | 137 This type is temporary until we do migration to a proper data type |
85 """ | 138 """ |
139 | |
86 # Blob is used on SQLite but gives errors when used here, while Text works fine | 140 # Blob is used on SQLite but gives errors when used here, while Text works fine |
87 impl = Text | 141 impl = Text |
88 cache_ok = True | 142 cache_ok = True |
89 | 143 |
90 def process_bind_param(self, value, dialect): | 144 def process_bind_param(self, value, dialect): |
107 except ModuleNotFoundError: | 161 except ModuleNotFoundError: |
108 # FIXME: workaround due to package renaming, need to move all pickle code to | 162 # FIXME: workaround due to package renaming, need to move all pickle code to |
109 # JSON | 163 # JSON |
110 return pickle.loads( | 164 return pickle.loads( |
111 value.replace(b"sat.plugins", b"libervia.backend.plugins"), | 165 value.replace(b"sat.plugins", b"libervia.backend.plugins"), |
112 encoding="utf-8" | 166 encoding="utf-8", |
113 ) | 167 ) |
114 | 168 |
115 | 169 |
116 class Json(TypeDecorator): | 170 class Json(TypeDecorator): |
117 """Handle JSON field in DB independant way""" | 171 """Handle JSON field in DB independant way""" |
172 | |
118 # Blob is used on SQLite but gives errors when used here, while Text works fine | 173 # Blob is used on SQLite but gives errors when used here, while Text works fine |
119 impl = Text | 174 impl = Text |
120 cache_ok = True | 175 cache_ok = True |
121 | 176 |
122 def process_bind_param(self, value, dialect): | 177 def process_bind_param(self, value, dialect): |
154 return generic.parseXml(value.encode()) | 209 return generic.parseXml(value.encode()) |
155 | 210 |
156 | 211 |
157 class JID(TypeDecorator): | 212 class JID(TypeDecorator): |
158 """Store twisted JID in text fields""" | 213 """Store twisted JID in text fields""" |
214 | |
159 impl = Text | 215 impl = Text |
160 cache_ok = True | 216 cache_ok = True |
161 | 217 |
162 def process_bind_param(self, value, dialect): | 218 def process_bind_param(self, value, dialect): |
163 if value is None: | 219 if value is None: |
191 | 247 |
192 class Component(Base): | 248 class Component(Base): |
193 __tablename__ = "components" | 249 __tablename__ = "components" |
194 | 250 |
195 profile_id = Column( | 251 profile_id = Column( |
196 ForeignKey("profiles.id", ondelete="CASCADE"), | 252 ForeignKey("profiles.id", ondelete="CASCADE"), nullable=True, primary_key=True |
197 nullable=True, | |
198 primary_key=True | |
199 ) | 253 ) |
200 entry_point = Column(Text, nullable=False) | 254 entry_point = Column(Text, nullable=False) |
201 profile = relationship("Profile") | 255 profile = relationship("Profile") |
202 | 256 |
203 | 257 |
207 UniqueConstraint("profile_id", "stanza_id", "source", "dest"), | 261 UniqueConstraint("profile_id", "stanza_id", "source", "dest"), |
208 UniqueConstraint("profile_id", "origin_id", "source", name="uq_origin_id"), | 262 UniqueConstraint("profile_id", "origin_id", "source", name="uq_origin_id"), |
209 Index("history__profile_id_timestamp", "profile_id", "timestamp"), | 263 Index("history__profile_id_timestamp", "profile_id", "timestamp"), |
210 Index( | 264 Index( |
211 "history__profile_id_received_timestamp", "profile_id", "received_timestamp" | 265 "history__profile_id_received_timestamp", "profile_id", "received_timestamp" |
212 ) | 266 ), |
213 ) | 267 ) |
214 | 268 |
215 uid = Column(Text, primary_key=True) | 269 uid = Column(Text, primary_key=True) |
216 origin_id = Column(Text) | 270 origin_id = Column(Text) |
217 stanza_id = Column(Text) | 271 stanza_id = Column(Text) |
293 if self.thread is not None: | 347 if self.thread is not None: |
294 extra["thread"] = self.thread.thread_id | 348 extra["thread"] = self.thread.thread_id |
295 if self.thread.parent_id is not None: | 349 if self.thread.parent_id is not None: |
296 extra["thread_parent"] = self.thread.parent_id | 350 extra["thread_parent"] = self.thread.parent_id |
297 | 351 |
298 | |
299 return { | 352 return { |
300 "from": f"{self.source}/{self.source_res}" if self.source_res | 353 "from": f"{self.source}/{self.source_res}" |
301 else self.source, | 354 if self.source_res |
355 else self.source, | |
302 "to": f"{self.dest}/{self.dest_res}" if self.dest_res else self.dest, | 356 "to": f"{self.dest}/{self.dest_res}" if self.dest_res else self.dest, |
303 "uid": self.uid, | 357 "uid": self.uid, |
304 "message": {m.language or '': m.message for m in self.messages}, | 358 "message": {m.language or "": m.message for m in self.messages}, |
305 "subject": {m.language or '': m.subject for m in self.subjects}, | 359 "subject": {m.language or "": m.subject for m in self.subjects}, |
306 "type": self.type, | 360 "type": self.type, |
307 "extra": extra, | 361 "extra": extra, |
308 "timestamp": self.timestamp, | 362 "timestamp": self.timestamp, |
309 } | 363 } |
310 | 364 |
311 def as_tuple(self): | 365 def as_tuple(self): |
312 d = self.serialise() | 366 d = self.serialise() |
313 return ( | 367 return ( |
314 d['uid'], d['timestamp'], d['from'], d['to'], d['message'], d['subject'], | 368 d["uid"], |
315 d['type'], d['extra'] | 369 d["timestamp"], |
370 d["from"], | |
371 d["to"], | |
372 d["message"], | |
373 d["subject"], | |
374 d["type"], | |
375 d["extra"], | |
316 ) | 376 ) |
317 | 377 |
318 @staticmethod | 378 @staticmethod |
319 def debug_collection(history_collection): | 379 def debug_collection(history_collection): |
320 for idx, history in enumerate(history_collection): | 380 for idx, history in enumerate(history_collection): |
334 print("".join(parts)) | 394 print("".join(parts)) |
335 | 395 |
336 | 396 |
337 class Message(Base): | 397 class Message(Base): |
338 __tablename__ = "message" | 398 __tablename__ = "message" |
339 __table_args__ = ( | 399 __table_args__ = (Index("message__history_uid", "history_uid"),) |
340 Index("message__history_uid", "history_uid"), | |
341 ) | |
342 | 400 |
343 id = Column( | 401 id = Column( |
344 Integer, | 402 Integer, |
345 primary_key=True, | 403 primary_key=True, |
346 ) | 404 ) |
356 s["language"] = str(self.language) | 414 s["language"] = str(self.language) |
357 return s | 415 return s |
358 | 416 |
359 def __repr__(self): | 417 def __repr__(self): |
360 lang_str = f"[{self.language}]" if self.language else "" | 418 lang_str = f"[{self.language}]" if self.language else "" |
361 msg = f"{self.message[:20]}…" if len(self.message)>20 else self.message | 419 msg = f"{self.message[:20]}…" if len(self.message) > 20 else self.message |
362 content = f"{lang_str}{msg}" | 420 content = f"{lang_str}{msg}" |
363 return f"Message<{content}>" | 421 return f"Message<{content}>" |
364 | 422 |
365 | 423 |
366 class Subject(Base): | 424 class Subject(Base): |
367 __tablename__ = "subject" | 425 __tablename__ = "subject" |
368 __table_args__ = ( | 426 __table_args__ = (Index("subject__history_uid", "history_uid"),) |
369 Index("subject__history_uid", "history_uid"), | |
370 ) | |
371 | 427 |
372 id = Column( | 428 id = Column( |
373 Integer, | 429 Integer, |
374 primary_key=True, | 430 primary_key=True, |
375 ) | 431 ) |
385 s["language"] = str(self.language) | 441 s["language"] = str(self.language) |
386 return s | 442 return s |
387 | 443 |
388 def __repr__(self): | 444 def __repr__(self): |
389 lang_str = f"[{self.language}]" if self.language else "" | 445 lang_str = f"[{self.language}]" if self.language else "" |
390 msg = f"{self.subject[:20]}…" if len(self.subject)>20 else self.subject | 446 msg = f"{self.subject[:20]}…" if len(self.subject) > 20 else self.subject |
391 content = f"{lang_str}{msg}" | 447 content = f"{lang_str}{msg}" |
392 return f"Subject<{content}>" | 448 return f"Subject<{content}>" |
393 | 449 |
394 | 450 |
395 class Thread(Base): | 451 class Thread(Base): |
396 __tablename__ = "thread" | 452 __tablename__ = "thread" |
397 __table_args__ = ( | 453 __table_args__ = (Index("thread__history_uid", "history_uid"),) |
398 Index("thread__history_uid", "history_uid"), | |
399 ) | |
400 | 454 |
401 id = Column( | 455 id = Column( |
402 Integer, | 456 Integer, |
403 primary_key=True, | 457 primary_key=True, |
404 ) | 458 ) |
410 | 464 |
411 def __repr__(self): | 465 def __repr__(self): |
412 return f"Thread<{self.thread_id} [parent: {self.parent_id}]>" | 466 return f"Thread<{self.thread_id} [parent: {self.parent_id}]>" |
413 | 467 |
414 | 468 |
469 class Notification(Base): | |
470 __tablename__ = "notifications" | |
471 __table_args__ = (Index("notifications_profile_id_status", "profile_id", "status"),) | |
472 | |
473 id = Column(Integer, primary_key=True, autoincrement=True) | |
474 timestamp = Column(Float, nullable=False, default=time.time) | |
475 expire_at = Column(Float, nullable=True) | |
476 | |
477 profile_id = Column(ForeignKey("profiles.id", ondelete="CASCADE"), index=True, nullable=True) | |
478 profile = relationship("Profile") | |
479 | |
480 type = Column(Enum(NotificationType), nullable=False) | |
481 | |
482 title = Column(Text, nullable=True) | |
483 body_plain = Column(Text, nullable=False) | |
484 body_rich = Column(Text, nullable=True) | |
485 | |
486 requires_action = Column(Boolean, default=False) | |
487 priority = Column(Integer, default=NotificationPriority.MEDIUM.value) | |
488 | |
489 extra_data = Column(JSON) | |
490 status = Column(Enum(NotificationStatus), default=NotificationStatus.new) | |
491 | |
492 def serialise(self) -> dict[str, str | float | bool | int | dict]: | |
493 """ | |
494 Serialises the Notification instance to a dictionary. | |
495 """ | |
496 result = {} | |
497 for column in self.__table__.columns: | |
498 value = getattr(self, column.name) | |
499 if value is not None: | |
500 if column.name in ("type", "status"): | |
501 result[column.name] = value.name | |
502 elif column.name == "id": | |
503 result[column.name] = str(value) | |
504 elif column.name == "profile_id": | |
505 if value is None: | |
506 result["profile"] = C.PROF_KEY_ALL | |
507 else: | |
508 result["profile"] = get_profile_by_id(value) | |
509 else: | |
510 result[column.name] = value | |
511 return result | |
512 | |
513 | |
415 class ParamGen(Base): | 514 class ParamGen(Base): |
416 __tablename__ = "param_gen" | 515 __tablename__ = "param_gen" |
417 | 516 |
418 category = Column(Text, primary_key=True) | 517 category = Column(Text, primary_key=True) |
419 name = Column(Text, primary_key=True) | 518 name = Column(Text, primary_key=True) |
423 class ParamInd(Base): | 522 class ParamInd(Base): |
424 __tablename__ = "param_ind" | 523 __tablename__ = "param_ind" |
425 | 524 |
426 category = Column(Text, primary_key=True) | 525 category = Column(Text, primary_key=True) |
427 name = Column(Text, primary_key=True) | 526 name = Column(Text, primary_key=True) |
428 profile_id = Column( | 527 profile_id = Column(ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True) |
429 ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True | |
430 ) | |
431 value = Column(Text) | 528 value = Column(Text) |
432 | 529 |
433 profile = relationship("Profile", back_populates="params") | 530 profile = relationship("Profile", back_populates="params") |
434 | 531 |
435 | 532 |
444 class PrivateInd(Base): | 541 class PrivateInd(Base): |
445 __tablename__ = "private_ind" | 542 __tablename__ = "private_ind" |
446 | 543 |
447 namespace = Column(Text, primary_key=True) | 544 namespace = Column(Text, primary_key=True) |
448 key = Column(Text, primary_key=True) | 545 key = Column(Text, primary_key=True) |
449 profile_id = Column( | 546 profile_id = Column(ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True) |
450 ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True | |
451 ) | |
452 value = Column(Text) | 547 value = Column(Text) |
453 | 548 |
454 profile = relationship("Profile", back_populates="private_data") | 549 profile = relationship("Profile", back_populates="private_data") |
455 | 550 |
456 | 551 |
465 class PrivateIndBin(Base): | 560 class PrivateIndBin(Base): |
466 __tablename__ = "private_ind_bin" | 561 __tablename__ = "private_ind_bin" |
467 | 562 |
468 namespace = Column(Text, primary_key=True) | 563 namespace = Column(Text, primary_key=True) |
469 key = Column(Text, primary_key=True) | 564 key = Column(Text, primary_key=True) |
470 profile_id = Column( | 565 profile_id = Column(ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True) |
471 ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True | |
472 ) | |
473 value = Column(LegacyPickle) | 566 value = Column(LegacyPickle) |
474 | 567 |
475 profile = relationship("Profile", back_populates="private_bin_data") | 568 profile = relationship("Profile", back_populates="private_bin_data") |
476 | 569 |
477 | 570 |
482 Index( | 575 Index( |
483 "files__profile_id_owner_media_type_media_subtype", | 576 "files__profile_id_owner_media_type_media_subtype", |
484 "profile_id", | 577 "profile_id", |
485 "owner", | 578 "owner", |
486 "media_type", | 579 "media_type", |
487 "media_subtype" | 580 "media_subtype", |
488 ) | 581 ), |
489 ) | 582 ) |
490 | 583 |
491 id = Column(Text, primary_key=True) | 584 id = Column(Text, primary_key=True) |
492 public_id = Column(Text, unique=True) | 585 public_id = Column(Text, unique=True) |
493 version = Column(Text, primary_key=True) | 586 version = Column(Text, primary_key=True) |
494 parent = Column(Text, nullable=False) | 587 parent = Column(Text, nullable=False) |
495 type = Column( | 588 type = Column( |
496 Enum( | 589 Enum("file", "directory", name="file_type", create_constraint=True), |
497 "file", "directory", | |
498 name="file_type", | |
499 create_constraint=True | |
500 ), | |
501 nullable=False, | 590 nullable=False, |
502 server_default="file", | 591 server_default="file", |
503 ) | 592 ) |
504 file_hash = Column(Text) | 593 file_hash = Column(Text) |
505 hash_algo = Column(Text) | 594 hash_algo = Column(Text) |
518 profile = relationship("Profile") | 607 profile = relationship("Profile") |
519 | 608 |
520 | 609 |
521 class PubsubNode(Base): | 610 class PubsubNode(Base): |
522 __tablename__ = "pubsub_nodes" | 611 __tablename__ = "pubsub_nodes" |
523 __table_args__ = ( | 612 __table_args__ = (UniqueConstraint("profile_id", "service", "name"),) |
524 UniqueConstraint("profile_id", "service", "name"), | |
525 ) | |
526 | 613 |
527 id = Column(Integer, primary_key=True) | 614 id = Column(Integer, primary_key=True) |
528 profile_id = Column( | 615 profile_id = Column(ForeignKey("profiles.id", ondelete="CASCADE")) |
529 ForeignKey("profiles.id", ondelete="CASCADE") | |
530 ) | |
531 service = Column(JID) | 616 service = Column(JID) |
532 name = Column(Text, nullable=False) | 617 name = Column(Text, nullable=False) |
533 subscribed = Column( | 618 subscribed = Column( |
534 Boolean(create_constraint=True, name="subscribed_bool"), nullable=False | 619 Boolean(create_constraint=True, name="subscribed_bool"), nullable=False |
535 ) | 620 ) |
538 Enum( | 623 Enum( |
539 SyncState, | 624 SyncState, |
540 name="sync_state", | 625 name="sync_state", |
541 create_constraint=True, | 626 create_constraint=True, |
542 ), | 627 ), |
543 nullable=True | 628 nullable=True, |
544 ) | 629 ) |
545 sync_state_updated = Column( | 630 sync_state_updated = Column(Float, nullable=False, default=time.time()) |
546 Float, | 631 type_ = Column(Text, name="type", nullable=True) |
547 nullable=False, | 632 subtype = Column(Text, nullable=True) |
548 default=time.time() | |
549 ) | |
550 type_ = Column( | |
551 Text, name="type", nullable=True | |
552 ) | |
553 subtype = Column( | |
554 Text, nullable=True | |
555 ) | |
556 extra = Column(JSON) | 633 extra = Column(JSON) |
557 | 634 |
558 items = relationship("PubsubItem", back_populates="node", passive_deletes=True) | 635 items = relationship("PubsubItem", back_populates="node", passive_deletes=True) |
559 subscriptions = relationship("PubsubSub", back_populates="node", passive_deletes=True) | 636 subscriptions = relationship("PubsubSub", back_populates="node", passive_deletes=True) |
560 | 637 |
565 class PubsubSub(Base): | 642 class PubsubSub(Base): |
566 """Subscriptions to pubsub nodes | 643 """Subscriptions to pubsub nodes |
567 | 644 |
568 Used by components managing a pubsub service | 645 Used by components managing a pubsub service |
569 """ | 646 """ |
647 | |
570 __tablename__ = "pubsub_subs" | 648 __tablename__ = "pubsub_subs" |
571 __table_args__ = ( | 649 __table_args__ = (UniqueConstraint("node_id", "subscriber"),) |
572 UniqueConstraint("node_id", "subscriber"), | |
573 ) | |
574 | 650 |
575 id = Column(Integer, primary_key=True) | 651 id = Column(Integer, primary_key=True) |
576 node_id = Column(ForeignKey("pubsub_nodes.id", ondelete="CASCADE"), nullable=False) | 652 node_id = Column(ForeignKey("pubsub_nodes.id", ondelete="CASCADE"), nullable=False) |
577 subscriber = Column(JID) | 653 subscriber = Column(JID) |
578 state = Column( | 654 state = Column( |
579 Enum( | 655 Enum( |
580 SubscriptionState, | 656 SubscriptionState, |
581 name="state", | 657 name="state", |
582 create_constraint=True, | 658 create_constraint=True, |
583 ), | 659 ), |
584 nullable=True | 660 nullable=True, |
585 ) | 661 ) |
586 | 662 |
587 node = relationship("PubsubNode", back_populates="subscriptions") | 663 node = relationship("PubsubNode", back_populates="subscriptions") |
588 | 664 |
589 | 665 |
590 class PubsubItem(Base): | 666 class PubsubItem(Base): |
591 __tablename__ = "pubsub_items" | 667 __tablename__ = "pubsub_items" |
592 __table_args__ = ( | 668 __table_args__ = (UniqueConstraint("node_id", "name"),) |
593 UniqueConstraint("node_id", "name"), | |
594 ) | |
595 id = Column(Integer, primary_key=True) | 669 id = Column(Integer, primary_key=True) |
596 node_id = Column(ForeignKey("pubsub_nodes.id", ondelete="CASCADE"), nullable=False) | 670 node_id = Column(ForeignKey("pubsub_nodes.id", ondelete="CASCADE"), nullable=False) |
597 name = Column(Text, nullable=False) | 671 name = Column(Text, nullable=False) |
598 data = Column(Xml, nullable=False) | 672 data = Column(Xml, nullable=False) |
599 created = Column(DateTime, nullable=False, server_default=now()) | 673 created = Column(DateTime, nullable=False, server_default=now()) |
604 | 678 |
605 | 679 |
606 ## Full-Text Search | 680 ## Full-Text Search |
607 | 681 |
608 # create | 682 # create |
683 | |
609 | 684 |
610 @event.listens_for(PubsubItem.__table__, "after_create") | 685 @event.listens_for(PubsubItem.__table__, "after_create") |
611 def fts_create(target, connection, **kw): | 686 def fts_create(target, connection, **kw): |
612 """Full-Text Search table creation""" | 687 """Full-Text Search table creation""" |
613 if connection.engine.name == "sqlite": | 688 if connection.engine.name == "sqlite": |
624 "END", | 699 "END", |
625 "CREATE TRIGGER pubsub_items_fts_sync_upd AFTER UPDATE ON pubsub_items BEGIN" | 700 "CREATE TRIGGER pubsub_items_fts_sync_upd AFTER UPDATE ON pubsub_items BEGIN" |
626 " INSERT INTO pubsub_items_fts(pubsub_items_fts, rowid, data) VALUES" | 701 " INSERT INTO pubsub_items_fts(pubsub_items_fts, rowid, data) VALUES" |
627 "('delete', old.id, old.data);" | 702 "('delete', old.id, old.data);" |
628 " INSERT INTO pubsub_items_fts(rowid, data) VALUES(new.id, new.data);" | 703 " INSERT INTO pubsub_items_fts(rowid, data) VALUES(new.id, new.data);" |
629 "END" | 704 "END", |
630 ] | 705 ] |
631 for q in queries: | 706 for q in queries: |
632 connection.execute(DDL(q)) | 707 connection.execute(DDL(q)) |
633 | 708 |
709 | |
634 # drop | 710 # drop |
711 | |
635 | 712 |
636 @event.listens_for(PubsubItem.__table__, "before_drop") | 713 @event.listens_for(PubsubItem.__table__, "before_drop") |
637 def fts_drop(target, connection, **kw): | 714 def fts_drop(target, connection, **kw): |
638 "Full-Text Search table drop" "" | 715 "Full-Text Search table drop" "" |
639 if connection.engine.name == "sqlite": | 716 if connection.engine.name == "sqlite": |