Mercurial > libervia-backend

annotate sat/memory/sqla.py @ 3753:10b71e3526bd

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
core (memory/sqla): add attribute to filter on `item_ids` in `getItems`
author Goffi <goffi@goffi.org>
date Fri, 13 May 2022 17:58:51 +0200
parents 658ddbabaf36
children af485e8afe03
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
3537
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
1 #!/usr/bin/env python3
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
2
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
3 # Libervia: an XMPP client
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
4 # Copyright (C) 2009-2021 Jérôme Poisson (goffi@goffi.org)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
5
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
6 # This program is free software: you can redistribute it and/or modify
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
7 # it under the terms of the GNU Affero General Public License as published by
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
8 # the Free Software Foundation, either version 3 of the License, or
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
9 # (at your option) any later version.
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
10
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
11 # This program is distributed in the hope that it will be useful,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
12 # but WITHOUT ANY WARRANTY; without even the implied warranty of
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
13 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
14 # GNU Affero General Public License for more details.
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
15
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
16 # You should have received a copy of the GNU Affero General Public License
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
17 # along with this program. If not, see <http://www.gnu.org/licenses/>.
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
18
3582
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
19 import sys
3537
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
20 import time
3582
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
21 import asyncio
3673
bd13391ee29e core (memory/sqla): fix `fileUpdate`
Goffi <goffi@goffi.org>
parents: 3665
diff changeset
22 import copy
3595
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
23 from datetime import datetime
3582
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
24 from asyncio.subprocess import PIPE
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
25 from pathlib import Path
3664
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
26 from typing import Union, Dict, List, Tuple, Iterable, Any, Callable, Optional
3582
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
27 from sqlalchemy.ext.asyncio import AsyncSession, AsyncEngine, create_async_engine
3537
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
28 from sqlalchemy.exc import IntegrityError, NoResultFound
3595
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
29 from sqlalchemy.orm import (
3664
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
30 sessionmaker, subqueryload, joinedload, selectinload, contains_eager
3595
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
31 )
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
32 from sqlalchemy.orm.decl_api import DeclarativeMeta
3537
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
33 from sqlalchemy.future import select
3582
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
34 from sqlalchemy.engine import Engine, Connection
3595
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
35 from sqlalchemy import update, delete, and_, or_, event, func
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
36 from sqlalchemy.sql.functions import coalesce, sum as sum_, now, count
3537
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
37 from sqlalchemy.dialects.sqlite import insert
3664
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
38 from sqlalchemy import text, literal_column, Integer
3582
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
39 from alembic import script as al_script, config as al_config
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
40 from alembic.runtime import migration as al_migration
3537
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
41 from twisted.internet import defer
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
42 from twisted.words.protocols.jabber import jid
3595
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
43 from twisted.words.xish import domish
3537
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
44 from sat.core.i18n import _
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
45 from sat.core import exceptions
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
46 from sat.core.log import getLogger
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
47 from sat.core.constants import Const as C
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
48 from sat.core.core_types import SatXMPPEntity
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
49 from sat.tools.utils import aio
3595
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
50 from sat.tools.common import uri
3582
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
51 from sat.memory import migration
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
52 from sat.memory import sqla_config
3537
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
53 from sat.memory.sqla_mapping import (
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
54 NOT_IN_EXTRA,
3595
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
55 SyncState,
3537
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
56 Base,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
57 Profile,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
58 Component,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
59 History,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
60 Message,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
61 Subject,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
62 Thread,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
63 ParamGen,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
64 ParamInd,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
65 PrivateGen,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
66 PrivateInd,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
67 PrivateGenBin,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
68 PrivateIndBin,
3595
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
69 File,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
70 PubsubNode,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
71 PubsubItem,
3537
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
72 )
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
73
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
74
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
75 log = getLogger(__name__)
3582
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
76 migration_path = Path(migration.__file__).parent
3664
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
77 #: mapping of Libervia search query operators to SQLAlchemy method name
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
78 OP_MAP = {
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
79 "==": "__eq__",
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
80 "eq": "__eq__",
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
81 "!=": "__ne__",
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
82 "ne": "__ne__",
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
83 ">": "__gt__",
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
84 "gt": "__gt__",
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
85 "<": "__le__",
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
86 "le": "__le__",
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
87 "between": "between",
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
88 "in": "in_",
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
89 "not_in": "not_in",
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
90 "overlap": "in_",
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
91 "ioverlap": "in_",
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
92 "disjoint": "in_",
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
93 "idisjoint": "in_",
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
94 "like": "like",
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
95 "ilike": "ilike",
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
96 "not_like": "notlike",
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
97 "not_ilike": "notilike",
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
98 }
3537
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
99
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
100
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
101 @event.listens_for(Engine, "connect")
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
102 def set_sqlite_pragma(dbapi_connection, connection_record):
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
103 cursor = dbapi_connection.cursor()
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
104 cursor.execute("PRAGMA foreign_keys=ON")
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
105 cursor.close()
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
106
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
107
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
108 class Storage:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
109
3582
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
110 def __init__(self):
3537
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
111 self.initialized = defer.Deferred()
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
112 # we keep cache for the profiles (key: profile name, value: profile id)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
113 # profile id to name
3744
658ddbabaf36 core (memory/sqla): new table/mapping to handle Pubsub node subscriptions:
Goffi <goffi@goffi.org>
parents: 3720
diff changeset
114 self.profiles: Dict[str, int] = {}
3537
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
115 # profile id to component entry point
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
116 self.components: Dict[int, str] = {}
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
117
3665
72b0e4053ab0 core (memory/slqa): implement `getProfileById`
Goffi <goffi@goffi.org>
parents: 3664
diff changeset
118 def getProfileById(self, profile_id):
72b0e4053ab0 core (memory/slqa): implement `getProfileById`
Goffi <goffi@goffi.org>
parents: 3664
diff changeset
119 return self.profiles.get(profile_id)
72b0e4053ab0 core (memory/slqa): implement `getProfileById`
Goffi <goffi@goffi.org>
parents: 3664
diff changeset
120
3582
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
121 async def migrateApply(self, *args: str, log_output: bool = False) -> None:
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
122 """Do a migration command
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
123
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
124 Commands are applied by running Alembic in a subprocess.
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
125 Arguments are alembic executables commands
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
126
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
127 @param log_output: manage stdout and stderr:
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
128 - if False, stdout and stderr are buffered, and logged only in case of error
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
129 - if True, stdout and stderr will be logged during the command execution
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
130 @raise exceptions.DatabaseError: something went wrong while running the
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
131 process
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
132 """
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
133 stdout, stderr = 2 * (None,) if log_output else 2 * (PIPE,)
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
134 proc = await asyncio.create_subprocess_exec(
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
135 sys.executable, "-m", "alembic", *args,
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
136 stdout=stdout, stderr=stderr, cwd=migration_path
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
137 )
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
138 log_out, log_err = await proc.communicate()
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
139 if proc.returncode != 0:
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
140 msg = _(
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
141 "Can't {operation} database (exit code {exit_code})"
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
142 ).format(
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
143 operation=args[0],
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
144 exit_code=proc.returncode
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
145 )
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
146 if log_out or log_err:
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
147 msg += f":\nstdout: {log_out.decode()}\nstderr: {log_err.decode()}"
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
148 log.error(msg)
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
149
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
150 raise exceptions.DatabaseError(msg)
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
151
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
152 async def createDB(self, engine: AsyncEngine, db_config: dict) -> None:
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
153 """Create a new database
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
154
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
155 The database is generated from SQLAlchemy model, then stamped by Alembic
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
156 """
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
157 # the dir may not exist if it's not the XDG recommended one
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
158 db_config["path"].parent.mkdir(0o700, True, True)
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
159 async with engine.begin() as conn:
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
160 await conn.run_sync(Base.metadata.create_all)
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
161
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
162 log.debug("stamping the database")
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
163 await self.migrateApply("stamp", "head")
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
164 log.debug("stamping done")
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
165
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
166 def _checkDBIsUpToDate(self, conn: Connection) -> bool:
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
167 al_ini_path = migration_path / "alembic.ini"
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
168 al_cfg = al_config.Config(al_ini_path)
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
169 directory = al_script.ScriptDirectory.from_config(al_cfg)
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
170 context = al_migration.MigrationContext.configure(conn)
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
171 return set(context.get_current_heads()) == set(directory.get_heads())
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
172
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
173 async def checkAndUpdateDB(self, engine: AsyncEngine, db_config: dict) -> None:
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
174 """Check that database is up-to-date, and update if necessary"""
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
175 async with engine.connect() as conn:
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
176 up_to_date = await conn.run_sync(self._checkDBIsUpToDate)
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
177 if up_to_date:
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
178 log.debug("Database is up-to-date")
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
179 else:
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
180 log.info("Database needs to be updated")
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
181 log.info("updating…")
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
182 await self.migrateApply("upgrade", "head", log_output=True)
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
183 log.info("Database is now up-to-date")
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
184
3537
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
185 @aio
3582
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
186 async def initialise(self) -> None:
3537
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
187 log.info(_("Connecting database"))
3583
16ade4ad63f3 core (memory/sqla_mapping): fix some technical debt:
Goffi <goffi@goffi.org>
parents: 3582
diff changeset
188
3582
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
189 db_config = sqla_config.getDbConfig()
3537
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
190 engine = create_async_engine(
3582
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
191 db_config["url"],
3595
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
192 future=True,
3537
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
193 )
3583
16ade4ad63f3 core (memory/sqla_mapping): fix some technical debt:
Goffi <goffi@goffi.org>
parents: 3582
diff changeset
194
3582
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
195 new_base = not db_config["path"].exists()
3537
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
196 if new_base:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
197 log.info(_("The database is new, creating the tables"))
3582
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
198 await self.createDB(engine, db_config)
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
199 else:
71516731d0aa core (memory/sqla): database migration using Alembic:
Goffi <goffi@goffi.org>
parents: 3537
diff changeset
200 await self.checkAndUpdateDB(engine, db_config)
3537
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
201
3583
16ade4ad63f3 core (memory/sqla_mapping): fix some technical debt:
Goffi <goffi@goffi.org>
parents: 3582
diff changeset
202 self.session = sessionmaker(
16ade4ad63f3 core (memory/sqla_mapping): fix some technical debt:
Goffi <goffi@goffi.org>
parents: 3582
diff changeset
203 engine, expire_on_commit=False, class_=AsyncSession
16ade4ad63f3 core (memory/sqla_mapping): fix some technical debt:
Goffi <goffi@goffi.org>
parents: 3582
diff changeset
204 )
16ade4ad63f3 core (memory/sqla_mapping): fix some technical debt:
Goffi <goffi@goffi.org>
parents: 3582
diff changeset
205
3537
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
206 async with self.session() as session:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
207 result = await session.execute(select(Profile))
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
208 for p in result.scalars():
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
209 self.profiles[p.name] = p.id
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
210 result = await session.execute(select(Component))
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
211 for c in result.scalars():
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
212 self.components[c.profile_id] = c.entry_point
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
213
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
214 self.initialized.callback(None)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
215
3596
2d97c695af05 core (memory/sqla): generic methods to add and delete ORM objects directly
Goffi <goffi@goffi.org>
parents: 3595
diff changeset
216 ## Generic
2d97c695af05 core (memory/sqla): generic methods to add and delete ORM objects directly
Goffi <goffi@goffi.org>
parents: 3595
diff changeset
217
2d97c695af05 core (memory/sqla): generic methods to add and delete ORM objects directly
Goffi <goffi@goffi.org>
parents: 3595
diff changeset
218 @aio
2d97c695af05 core (memory/sqla): generic methods to add and delete ORM objects directly
Goffi <goffi@goffi.org>
parents: 3595
diff changeset
219 async def add(self, db_obj: DeclarativeMeta) -> None:
2d97c695af05 core (memory/sqla): generic methods to add and delete ORM objects directly
Goffi <goffi@goffi.org>
parents: 3595
diff changeset
220 """Add an object to database"""
2d97c695af05 core (memory/sqla): generic methods to add and delete ORM objects directly
Goffi <goffi@goffi.org>
parents: 3595
diff changeset
221 async with self.session() as session:
2d97c695af05 core (memory/sqla): generic methods to add and delete ORM objects directly
Goffi <goffi@goffi.org>
parents: 3595
diff changeset
222 async with session.begin():
2d97c695af05 core (memory/sqla): generic methods to add and delete ORM objects directly
Goffi <goffi@goffi.org>
parents: 3595
diff changeset
223 session.add(db_obj)
2d97c695af05 core (memory/sqla): generic methods to add and delete ORM objects directly
Goffi <goffi@goffi.org>
parents: 3595
diff changeset
224
2d97c695af05 core (memory/sqla): generic methods to add and delete ORM objects directly
Goffi <goffi@goffi.org>
parents: 3595
diff changeset
225 @aio
2d97c695af05 core (memory/sqla): generic methods to add and delete ORM objects directly
Goffi <goffi@goffi.org>
parents: 3595
diff changeset
226 async def delete(self, db_obj: DeclarativeMeta) -> None:
2d97c695af05 core (memory/sqla): generic methods to add and delete ORM objects directly
Goffi <goffi@goffi.org>
parents: 3595
diff changeset
227 """Delete an object from database"""
2d97c695af05 core (memory/sqla): generic methods to add and delete ORM objects directly
Goffi <goffi@goffi.org>
parents: 3595
diff changeset
228 async with self.session() as session:
2d97c695af05 core (memory/sqla): generic methods to add and delete ORM objects directly
Goffi <goffi@goffi.org>
parents: 3595
diff changeset
229 async with session.begin():
2d97c695af05 core (memory/sqla): generic methods to add and delete ORM objects directly
Goffi <goffi@goffi.org>
parents: 3595
diff changeset
230 await session.delete(db_obj)
2d97c695af05 core (memory/sqla): generic methods to add and delete ORM objects directly
Goffi <goffi@goffi.org>
parents: 3595
diff changeset
231 await session.commit()
2d97c695af05 core (memory/sqla): generic methods to add and delete ORM objects directly
Goffi <goffi@goffi.org>
parents: 3595
diff changeset
232
3537
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
233 ## Profiles
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
234
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
235 def getProfilesList(self) -> List[str]:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
236 """"Return list of all registered profiles"""
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
237 return list(self.profiles.keys())
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
238
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
239 def hasProfile(self, profile_name: str) -> bool:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
240 """return True if profile_name exists
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
241
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
242 @param profile_name: name of the profile to check
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
243 """
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
244 return profile_name in self.profiles
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
245
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
246 def profileIsComponent(self, profile_name: str) -> bool:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
247 try:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
248 return self.profiles[profile_name] in self.components
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
249 except KeyError:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
250 raise exceptions.NotFound("the requested profile doesn't exists")
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
251
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
252 def getEntryPoint(self, profile_name: str) -> str:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
253 try:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
254 return self.components[self.profiles[profile_name]]
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
255 except KeyError:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
256 raise exceptions.NotFound("the requested profile doesn't exists or is not a component")
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
257
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
258 @aio
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
259 async def createProfile(self, name: str, component_ep: Optional[str] = None) -> None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
260 """Create a new profile
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
261
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
262 @param name: name of the profile
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
263 @param component: if not None, must point to a component entry point
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
264 """
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
265 async with self.session() as session:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
266 profile = Profile(name=name)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
267 async with session.begin():
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
268 session.add(profile)
3638
257135d5c5c2 core (memory/sqla): fix key/value inversion in self.profiles
Goffi <goffi@goffi.org>
parents: 3621
diff changeset
269 self.profiles[profile.name] = profile.id
3537
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
270 if component_ep is not None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
271 async with session.begin():
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
272 component = Component(profile=profile, entry_point=component_ep)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
273 session.add(component)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
274 self.components[profile.id] = component_ep
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
275 return profile
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
276
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
277 @aio
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
278 async def deleteProfile(self, name: str) -> None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
279 """Delete profile
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
280
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
281 @param name: name of the profile
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
282 """
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
283 async with self.session() as session:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
284 result = await session.execute(select(Profile).where(Profile.name == name))
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
285 profile = result.scalar()
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
286 await session.delete(profile)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
287 await session.commit()
3638
257135d5c5c2 core (memory/sqla): fix key/value inversion in self.profiles
Goffi <goffi@goffi.org>
parents: 3621
diff changeset
288 del self.profiles[profile.name]
3537
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
289 if profile.id in self.components:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
290 del self.components[profile.id]
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
291 log.info(_("Profile {name!r} deleted").format(name = name))
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
292
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
293 ## Params
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
294
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
295 @aio
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
296 async def loadGenParams(self, params_gen: dict) -> None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
297 """Load general parameters
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
298
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
299 @param params_gen: dictionary to fill
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
300 """
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
301 log.debug(_("loading general parameters from database"))
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
302 async with self.session() as session:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
303 result = await session.execute(select(ParamGen))
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
304 for p in result.scalars():
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
305 params_gen[(p.category, p.name)] = p.value
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
306
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
307 @aio
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
308 async def loadIndParams(self, params_ind: dict, profile: str) -> None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
309 """Load individual parameters
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
310
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
311 @param params_ind: dictionary to fill
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
312 @param profile: a profile which *must* exist
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
313 """
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
314 log.debug(_("loading individual parameters from database"))
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
315 async with self.session() as session:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
316 result = await session.execute(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
317 select(ParamInd).where(ParamInd.profile_id == self.profiles[profile])
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
318 )
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
319 for p in result.scalars():
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
320 params_ind[(p.category, p.name)] = p.value
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
321
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
322 @aio
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
323 async def getIndParam(self, category: str, name: str, profile: str) -> Optional[str]:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
324 """Ask database for the value of one specific individual parameter
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
325
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
326 @param category: category of the parameter
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
327 @param name: name of the parameter
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
328 @param profile: %(doc_profile)s
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
329 """
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
330 async with self.session() as session:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
331 result = await session.execute(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
332 select(ParamInd.value)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
333 .filter_by(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
334 category=category,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
335 name=name,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
336 profile_id=self.profiles[profile]
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
337 )
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
338 )
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
339 return result.scalar_one_or_none()
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
340
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
341 @aio
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
342 async def getIndParamValues(self, category: str, name: str) -> Dict[str, str]:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
343 """Ask database for the individual values of a parameter for all profiles
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
344
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
345 @param category: category of the parameter
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
346 @param name: name of the parameter
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
347 @return dict: profile => value map
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
348 """
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
349 async with self.session() as session:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
350 result = await session.execute(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
351 select(ParamInd)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
352 .filter_by(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
353 category=category,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
354 name=name
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
355 )
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
356 .options(subqueryload(ParamInd.profile))
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
357 )
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
358 return {param.profile.name: param.value for param in result.scalars()}
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
359
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
360 @aio
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
361 async def setGenParam(self, category: str, name: str, value: Optional[str]) -> None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
362 """Save the general parameters in database
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
363
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
364 @param category: category of the parameter
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
365 @param name: name of the parameter
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
366 @param value: value to set
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
367 """
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
368 async with self.session() as session:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
369 stmt = insert(ParamGen).values(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
370 category=category,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
371 name=name,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
372 value=value
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
373 ).on_conflict_do_update(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
374 index_elements=(ParamGen.category, ParamGen.name),
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
375 set_={
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
376 ParamGen.value: value
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
377 }
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
378 )
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
379 await session.execute(stmt)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
380 await session.commit()
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
381
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
382 @aio
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
383 async def setIndParam(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
384 self,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
385 category:str,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
386 name: str,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
387 value: Optional[str],
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
388 profile: str
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
389 ) -> None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
390 """Save the individual parameters in database
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
391
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
392 @param category: category of the parameter
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
393 @param name: name of the parameter
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
394 @param value: value to set
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
395 @param profile: a profile which *must* exist
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
396 """
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
397 async with self.session() as session:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
398 stmt = insert(ParamInd).values(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
399 category=category,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
400 name=name,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
401 profile_id=self.profiles[profile],
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
402 value=value
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
403 ).on_conflict_do_update(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
404 index_elements=(ParamInd.category, ParamInd.name, ParamInd.profile_id),
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
405 set_={
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
406 ParamInd.value: value
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
407 }
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
408 )
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
409 await session.execute(stmt)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
410 await session.commit()
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
411
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
412 def _jid_filter(self, jid_: jid.JID, dest: bool = False):
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
413 """Generate condition to filter on a JID, using relevant columns
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
414
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
415 @param dest: True if it's the destinee JID, otherwise it's the source one
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
416 @param jid_: JID to filter by
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
417 """
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
418 if jid_.resource:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
419 if dest:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
420 return and_(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
421 History.dest == jid_.userhost(),
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
422 History.dest_res == jid_.resource
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
423 )
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
424 else:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
425 return and_(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
426 History.source == jid_.userhost(),
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
427 History.source_res == jid_.resource
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
428 )
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
429 else:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
430 if dest:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
431 return History.dest == jid_.userhost()
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
432 else:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
433 return History.source == jid_.userhost()
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
434
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
435 @aio
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
436 async def historyGet(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
437 self,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
438 from_jid: Optional[jid.JID],
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
439 to_jid: Optional[jid.JID],
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
440 limit: Optional[int] = None,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
441 between: bool = True,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
442 filters: Optional[Dict[str, str]] = None,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
443 profile: Optional[str] = None,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
444 ) -> List[Tuple[
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
445 str, int, str, str, Dict[str, str], Dict[str, str], str, str, str]
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
446 ]:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
447 """Retrieve messages in history
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
448
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
449 @param from_jid: source JID (full, or bare for catchall)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
450 @param to_jid: dest JID (full, or bare for catchall)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
451 @param limit: maximum number of messages to get:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
452 - 0 for no message (returns the empty list)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
453 - None for unlimited
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
454 @param between: confound source and dest (ignore the direction)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
455 @param filters: pattern to filter the history results
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
456 @return: list of messages as in [messageNew], minus the profile which is already
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
457 known.
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
458 """
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
459 # we have to set a default value to profile because it's last argument
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
460 # and thus follow other keyword arguments with default values
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
461 # but None should not be used for it
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
462 assert profile is not None
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
463 if limit == 0:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
464 return []
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
465 if filters is None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
466 filters = {}
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
467
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
468 stmt = (
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
469 select(History)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
470 .filter_by(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
471 profile_id=self.profiles[profile]
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
472 )
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
473 .outerjoin(History.messages)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
474 .outerjoin(History.subjects)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
475 .outerjoin(History.thread)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
476 .options(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
477 contains_eager(History.messages),
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
478 contains_eager(History.subjects),
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
479 contains_eager(History.thread),
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
480 )
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
481 .order_by(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
482 # timestamp may be identical for 2 close messages (specially when delay is
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
483 # used) that's why we order ties by received_timestamp. We'll reverse the
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
484 # order when returning the result. We use DESC here so LIMIT keep the last
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
485 # messages
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
486 History.timestamp.desc(),
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
487 History.received_timestamp.desc()
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
488 )
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
489 )
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
490
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
491
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
492 if not from_jid and not to_jid:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
493 # no jid specified, we want all one2one communications
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
494 pass
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
495 elif between:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
496 if not from_jid or not to_jid:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
497 # we only have one jid specified, we check all messages
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
498 # from or to this jid
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
499 jid_ = from_jid or to_jid
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
500 stmt = stmt.where(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
501 or_(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
502 self._jid_filter(jid_),
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
503 self._jid_filter(jid_, dest=True)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
504 )
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
505 )
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
506 else:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
507 # we have 2 jids specified, we check all communications between
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
508 # those 2 jids
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
509 stmt = stmt.where(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
510 or_(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
511 and_(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
512 self._jid_filter(from_jid),
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
513 self._jid_filter(to_jid, dest=True),
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
514 ),
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
515 and_(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
516 self._jid_filter(to_jid),
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
517 self._jid_filter(from_jid, dest=True),
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
518 )
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
519 )
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
520 )
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
521 else:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
522 # we want one communication in specific direction (from somebody or
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
523 # to somebody).
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
524 if from_jid is not None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
525 stmt = stmt.where(self._jid_filter(from_jid))
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
526 if to_jid is not None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
527 stmt = stmt.where(self._jid_filter(to_jid, dest=True))
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
528
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
529 if filters:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
530 if 'timestamp_start' in filters:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
531 stmt = stmt.where(History.timestamp >= float(filters['timestamp_start']))
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
532 if 'before_uid' in filters:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
533 # orignially this query was using SQLITE's rowid. This has been changed
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
534 # to use coalesce(received_timestamp, timestamp) to be SQL engine independant
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
535 stmt = stmt.where(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
536 coalesce(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
537 History.received_timestamp,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
538 History.timestamp
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
539 ) < (
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
540 select(coalesce(History.received_timestamp, History.timestamp))
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
541 .filter_by(uid=filters["before_uid"])
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
542 ).scalar_subquery()
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
543 )
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
544 if 'body' in filters:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
545 # TODO: use REGEXP (function to be defined) instead of GLOB: https://www.sqlite.org/lang_expr.html
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
546 stmt = stmt.where(Message.message.like(f"%{filters['body']}%"))
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
547 if 'search' in filters:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
548 search_term = f"%{filters['search']}%"
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
549 stmt = stmt.where(or_(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
550 Message.message.like(search_term),
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
551 History.source_res.like(search_term)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
552 ))
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
553 if 'types' in filters:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
554 types = filters['types'].split()
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
555 stmt = stmt.where(History.type.in_(types))
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
556 if 'not_types' in filters:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
557 types = filters['not_types'].split()
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
558 stmt = stmt.where(History.type.not_in(types))
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
559 if 'last_stanza_id' in filters:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
560 # this request get the last message with a "stanza_id" that we
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
561 # have in history. This is mainly used to retrieve messages sent
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
562 # while we were offline, using MAM (XEP-0313).
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
563 if (filters['last_stanza_id'] is not True
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
564 or limit != 1):
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
565 raise ValueError("Unexpected values for last_stanza_id filter")
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
566 stmt = stmt.where(History.stanza_id.is_not(None))
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
567
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
568 if limit is not None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
569 stmt = stmt.limit(limit)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
570
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
571 async with self.session() as session:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
572 result = await session.execute(stmt)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
573
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
574 result = result.scalars().unique().all()
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
575 result.reverse()
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
576 return [h.as_tuple() for h in result]
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
577
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
578 @aio
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
579 async def addToHistory(self, data: dict, profile: str) -> None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
580 """Store a new message in history
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
581
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
582 @param data: message data as build by SatMessageProtocol.onMessage
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
583 """
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
584 extra = {k: v for k, v in data["extra"].items() if k not in NOT_IN_EXTRA}
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
585 messages = [Message(message=mess, language=lang)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
586 for lang, mess in data["message"].items()]
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
587 subjects = [Subject(subject=mess, language=lang)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
588 for lang, mess in data["subject"].items()]
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
589 if "thread" in data["extra"]:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
590 thread = Thread(thread_id=data["extra"]["thread"],
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
591 parent_id=data["extra"].get["thread_parent"])
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
592 else:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
593 thread = None
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
594 try:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
595 async with self.session() as session:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
596 async with session.begin():
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
597 session.add(History(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
598 uid=data["uid"],
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
599 stanza_id=data["extra"].get("stanza_id"),
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
600 update_uid=data["extra"].get("update_uid"),
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
601 profile_id=self.profiles[profile],
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
602 source_jid=data["from"],
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
603 dest_jid=data["to"],
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
604 timestamp=data["timestamp"],
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
605 received_timestamp=data.get("received_timestamp"),
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
606 type=data["type"],
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
607 extra=extra,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
608 messages=messages,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
609 subjects=subjects,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
610 thread=thread,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
611 ))
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
612 except IntegrityError as e:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
613 if "unique" in str(e.orig).lower():
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
614 log.debug(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
615 f"message {data['uid']!r} is already in history, not storing it again"
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
616 )
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
617 else:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
618 log.error(f"Can't store message {data['uid']!r} in history: {e}")
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
619 except Exception as e:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
620 log.critical(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
621 f"Can't store message, unexpected exception (uid: {data['uid']}): {e}"
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
622 )
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
623
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
624 ## Private values
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
625
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
626 def _getPrivateClass(self, binary, profile):
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
627 """Get ORM class to use for private values"""
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
628 if profile is None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
629 return PrivateGenBin if binary else PrivateGen
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
630 else:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
631 return PrivateIndBin if binary else PrivateInd
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
632
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
633
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
634 @aio
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
635 async def getPrivates(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
636 self,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
637 namespace:str,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
638 keys: Optional[Iterable[str]] = None,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
639 binary: bool = False,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
640 profile: Optional[str] = None
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
641 ) -> Dict[str, Any]:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
642 """Get private value(s) from databases
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
643
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
644 @param namespace: namespace of the values
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
645 @param keys: keys of the values to get None to get all keys/values
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
646 @param binary: True to deserialise binary values
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
647 @param profile: profile to use for individual values
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
648 None to use general values
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
649 @return: gotten keys/values
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
650 """
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
651 if keys is not None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
652 keys = list(keys)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
653 log.debug(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
654 f"getting {'general' if profile is None else 'individual'}"
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
655 f"{' binary' if binary else ''} private values from database for namespace "
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
656 f"{namespace}{f' with keys {keys!r}' if keys is not None else ''}"
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
657 )
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
658 cls = self._getPrivateClass(binary, profile)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
659 stmt = select(cls).filter_by(namespace=namespace)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
660 if keys:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
661 stmt = stmt.where(cls.key.in_(list(keys)))
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
662 if profile is not None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
663 stmt = stmt.filter_by(profile_id=self.profiles[profile])
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
664 async with self.session() as session:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
665 result = await session.execute(stmt)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
666 return {p.key: p.value for p in result.scalars()}
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
667
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
668 @aio
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
669 async def setPrivateValue(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
670 self,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
671 namespace: str,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
672 key:str,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
673 value: Any,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
674 binary: bool = False,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
675 profile: Optional[str] = None
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
676 ) -> None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
677 """Set a private value in database
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
678
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
679 @param namespace: namespace of the values
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
680 @param key: key of the value to set
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
681 @param value: value to set
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
682 @param binary: True if it's a binary values
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
683 binary values need to be serialised, used for everything but strings
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
684 @param profile: profile to use for individual value
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
685 if None, it's a general value
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
686 """
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
687 cls = self._getPrivateClass(binary, profile)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
688
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
689 values = {
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
690 "namespace": namespace,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
691 "key": key,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
692 "value": value
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
693 }
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
694 index_elements = [cls.namespace, cls.key]
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
695
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
696 if profile is not None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
697 values["profile_id"] = self.profiles[profile]
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
698 index_elements.append(cls.profile_id)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
699
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
700 async with self.session() as session:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
701 await session.execute(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
702 insert(cls).values(**values).on_conflict_do_update(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
703 index_elements=index_elements,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
704 set_={
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
705 cls.value: value
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
706 }
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
707 )
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
708 )
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
709 await session.commit()
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
710
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
711 @aio
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
712 async def delPrivateValue(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
713 self,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
714 namespace: str,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
715 key: str,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
716 binary: bool = False,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
717 profile: Optional[str] = None
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
718 ) -> None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
719 """Delete private value from database
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
720
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
721 @param category: category of the privateeter
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
722 @param key: key of the private value
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
723 @param binary: True if it's a binary values
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
724 @param profile: profile to use for individual value
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
725 if None, it's a general value
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
726 """
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
727 cls = self._getPrivateClass(binary, profile)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
728
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
729 stmt = delete(cls).filter_by(namespace=namespace, key=key)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
730
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
731 if profile is not None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
732 stmt = stmt.filter_by(profile_id=self.profiles[profile])
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
733
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
734 async with self.session() as session:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
735 await session.execute(stmt)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
736 await session.commit()
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
737
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
738 @aio
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
739 async def delPrivateNamespace(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
740 self,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
741 namespace: str,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
742 binary: bool = False,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
743 profile: Optional[str] = None
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
744 ) -> None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
745 """Delete all data from a private namespace
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
746
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
747 Be really cautious when you use this method, as all data with given namespace are
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
748 removed.
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
749 Params are the same as for delPrivateValue
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
750 """
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
751 cls = self._getPrivateClass(binary, profile)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
752
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
753 stmt = delete(cls).filter_by(namespace=namespace)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
754
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
755 if profile is not None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
756 stmt = stmt.filter_by(profile_id=self.profiles[profile])
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
757
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
758 async with self.session() as session:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
759 await session.execute(stmt)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
760 await session.commit()
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
761
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
762 ## Files
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
763
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
764 @aio
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
765 async def getFiles(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
766 self,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
767 client: Optional[SatXMPPEntity],
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
768 file_id: Optional[str] = None,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
769 version: Optional[str] = '',
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
770 parent: Optional[str] = None,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
771 type_: Optional[str] = None,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
772 file_hash: Optional[str] = None,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
773 hash_algo: Optional[str] = None,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
774 name: Optional[str] = None,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
775 namespace: Optional[str] = None,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
776 mime_type: Optional[str] = None,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
777 public_id: Optional[str] = None,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
778 owner: Optional[jid.JID] = None,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
779 access: Optional[dict] = None,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
780 projection: Optional[List[str]] = None,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
781 unique: bool = False
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
782 ) -> List[dict]:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
783 """Retrieve files with with given filters
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
784
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
785 @param file_id: id of the file
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
786 None to ignore
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
787 @param version: version of the file
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
788 None to ignore
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
789 empty string to look for current version
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
790 @param parent: id of the directory containing the files
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
791 None to ignore
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
792 empty string to look for root files/directories
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
793 @param projection: name of columns to retrieve
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
794 None to retrieve all
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
795 @param unique: if True will remove duplicates
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
796 other params are the same as for [setFile]
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
797 @return: files corresponding to filters
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
798 """
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
799 if projection is None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
800 projection = [
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
801 'id', 'version', 'parent', 'type', 'file_hash', 'hash_algo', 'name',
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
802 'size', 'namespace', 'media_type', 'media_subtype', 'public_id',
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
803 'created', 'modified', 'owner', 'access', 'extra'
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
804 ]
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
805
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
806 stmt = select(*[getattr(File, f) for f in projection])
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
807
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
808 if unique:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
809 stmt = stmt.distinct()
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
810
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
811 if client is not None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
812 stmt = stmt.filter_by(profile_id=self.profiles[client.profile])
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
813 else:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
814 if public_id is None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
815 raise exceptions.InternalError(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
816 "client can only be omitted when public_id is set"
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
817 )
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
818 if file_id is not None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
819 stmt = stmt.filter_by(id=file_id)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
820 if version is not None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
821 stmt = stmt.filter_by(version=version)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
822 if parent is not None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
823 stmt = stmt.filter_by(parent=parent)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
824 if type_ is not None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
825 stmt = stmt.filter_by(type=type_)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
826 if file_hash is not None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
827 stmt = stmt.filter_by(file_hash=file_hash)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
828 if hash_algo is not None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
829 stmt = stmt.filter_by(hash_algo=hash_algo)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
830 if name is not None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
831 stmt = stmt.filter_by(name=name)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
832 if namespace is not None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
833 stmt = stmt.filter_by(namespace=namespace)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
834 if mime_type is not None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
835 if '/' in mime_type:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
836 media_type, media_subtype = mime_type.split("/", 1)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
837 stmt = stmt.filter_by(media_type=media_type, media_subtype=media_subtype)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
838 else:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
839 stmt = stmt.filter_by(media_type=mime_type)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
840 if public_id is not None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
841 stmt = stmt.filter_by(public_id=public_id)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
842 if owner is not None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
843 stmt = stmt.filter_by(owner=owner)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
844 if access is not None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
845 raise NotImplementedError('Access check is not implemented yet')
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
846 # a JSON comparison is needed here
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
847
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
848 async with self.session() as session:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
849 result = await session.execute(stmt)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
850
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
851 return [dict(r) for r in result]
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
852
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
853 @aio
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
854 async def setFile(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
855 self,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
856 client: SatXMPPEntity,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
857 name: str,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
858 file_id: str,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
859 version: str = "",
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
860 parent: str = "",
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
861 type_: str = C.FILE_TYPE_FILE,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
862 file_hash: Optional[str] = None,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
863 hash_algo: Optional[str] = None,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
864 size: int = None,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
865 namespace: Optional[str] = None,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
866 mime_type: Optional[str] = None,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
867 public_id: Optional[str] = None,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
868 created: Optional[float] = None,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
869 modified: Optional[float] = None,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
870 owner: Optional[jid.JID] = None,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
871 access: Optional[dict] = None,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
872 extra: Optional[dict] = None
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
873 ) -> None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
874 """Set a file metadata
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
875
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
876 @param client: client owning the file
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
877 @param name: name of the file (must not contain "/")
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
878 @param file_id: unique id of the file
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
879 @param version: version of this file
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
880 @param parent: id of the directory containing this file
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
881 Empty string if it is a root file/directory
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
882 @param type_: one of:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
883 - file
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
884 - directory
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
885 @param file_hash: unique hash of the payload
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
886 @param hash_algo: algorithm used for hashing the file (usually sha-256)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
887 @param size: size in bytes
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
888 @param namespace: identifier (human readable is better) to group files
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
889 for instance, namespace could be used to group files in a specific photo album
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
890 @param mime_type: media type of the file, or None if not known/guessed
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
891 @param public_id: ID used to server the file publicly via HTTP
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
892 @param created: UNIX time of creation
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
893 @param modified: UNIX time of last modification, or None to use created date
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
894 @param owner: jid of the owner of the file (mainly useful for component)
3621
9e1a993ad1bf core (memory/sqla): minor docstring line lenght fix
Goffi <goffi@goffi.org>
parents: 3596
diff changeset
895 @param access: serialisable dictionary with access rules. See [memory.memory] for
9e1a993ad1bf core (memory/sqla): minor docstring line lenght fix
Goffi <goffi@goffi.org>
parents: 3596
diff changeset
896 details
3537
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
897 @param extra: serialisable dictionary of any extra data
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
898 will be encoded to json in database
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
899 """
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
900 if mime_type is None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
901 media_type = media_subtype = None
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
902 elif '/' in mime_type:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
903 media_type, media_subtype = mime_type.split('/', 1)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
904 else:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
905 media_type, media_subtype = mime_type, None
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
906
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
907 async with self.session() as session:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
908 async with session.begin():
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
909 session.add(File(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
910 id=file_id,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
911 version=version.strip(),
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
912 parent=parent,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
913 type=type_,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
914 file_hash=file_hash,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
915 hash_algo=hash_algo,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
916 name=name,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
917 size=size,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
918 namespace=namespace,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
919 media_type=media_type,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
920 media_subtype=media_subtype,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
921 public_id=public_id,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
922 created=time.time() if created is None else created,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
923 modified=modified,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
924 owner=owner,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
925 access=access,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
926 extra=extra,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
927 profile_id=self.profiles[client.profile]
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
928 ))
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
929
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
930 @aio
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
931 async def fileGetUsedSpace(self, client: SatXMPPEntity, owner: jid.JID) -> int:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
932 async with self.session() as session:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
933 result = await session.execute(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
934 select(sum_(File.size)).filter_by(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
935 owner=owner,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
936 type=C.FILE_TYPE_FILE,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
937 profile_id=self.profiles[client.profile]
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
938 ))
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
939 return result.scalar_one_or_none() or 0
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
940
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
941 @aio
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
942 async def fileDelete(self, file_id: str) -> None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
943 """Delete file metadata from the database
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
944
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
945 @param file_id: id of the file to delete
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
946 NOTE: file itself must still be removed, this method only handle metadata in
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
947 database
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
948 """
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
949 async with self.session() as session:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
950 await session.execute(delete(File).filter_by(id=file_id))
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
951 await session.commit()
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
952
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
953 @aio
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
954 async def fileUpdate(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
955 self,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
956 file_id: str,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
957 column: str,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
958 update_cb: Callable[[dict], None]
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
959 ) -> None:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
960 """Update a column value using a method to avoid race conditions
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
961
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
962 the older value will be retrieved from database, then update_cb will be applied to
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
963 update it, and file will be updated checking that older value has not been changed
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
964 meanwhile by an other user. If it has changed, it tries again a couple of times
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
965 before failing
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
966 @param column: column name (only "access" or "extra" are allowed)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
967 @param update_cb: method to update the value of the colum
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
968 the method will take older value as argument, and must update it in place
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
969 update_cb must not care about serialization,
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
970 it get the deserialized data (i.e. a Python object) directly
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
971 @raise exceptions.NotFound: there is not file with this id
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
972 """
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
973 if column not in ('access', 'extra'):
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
974 raise exceptions.InternalError('bad column name')
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
975 orm_col = getattr(File, column)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
976
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
977 for i in range(5):
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
978 async with self.session() as session:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
979 try:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
980 value = (await session.execute(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
981 select(orm_col).filter_by(id=file_id)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
982 )).scalar_one()
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
983 except NoResultFound:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
984 raise exceptions.NotFound
3673
bd13391ee29e core (memory/sqla): fix `fileUpdate`
Goffi <goffi@goffi.org>
parents: 3665
diff changeset
985 old_value = copy.deepcopy(value)
3537
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
986 update_cb(value)
3673
bd13391ee29e core (memory/sqla): fix `fileUpdate`
Goffi <goffi@goffi.org>
parents: 3665
diff changeset
987 stmt = update(File).filter_by(id=file_id).values({column: value})
bd13391ee29e core (memory/sqla): fix `fileUpdate`
Goffi <goffi@goffi.org>
parents: 3665
diff changeset
988 if not old_value:
3537
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
989 # because JsonDefaultDict convert NULL to an empty dict, we have to
3673
bd13391ee29e core (memory/sqla): fix `fileUpdate`
Goffi <goffi@goffi.org>
parents: 3665
diff changeset
990 # test both for empty dict and None when we have an empty dict
bd13391ee29e core (memory/sqla): fix `fileUpdate`
Goffi <goffi@goffi.org>
parents: 3665
diff changeset
991 stmt = stmt.where((orm_col == None) | (orm_col == old_value))
3537
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
992 else:
3673
bd13391ee29e core (memory/sqla): fix `fileUpdate`
Goffi <goffi@goffi.org>
parents: 3665
diff changeset
993 stmt = stmt.where(orm_col == old_value)
3537
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
994 result = await session.execute(stmt)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
995 await session.commit()
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
996
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
997 if result.rowcount == 1:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
998 break
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
999
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
1000 log.warning(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
1001 _("table not updated, probably due to race condition, trying again "
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
1002 "({tries})").format(tries=i+1)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
1003 )
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
1004
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
1005 else:
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
1006 raise exceptions.DatabaseError(
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
1007 _("Can't update file {file_id} due to race condition")
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
1008 .format(file_id=file_id)
f9a5b810f14d core (memory/storage): backend storage is now based on SQLAlchemy
Goffi <goffi@goffi.org>
parents:
diff changeset
1009 )
3595
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1010
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1011 @aio
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1012 async def getPubsubNode(
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1013 self,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1014 client: SatXMPPEntity,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1015 service: jid.JID,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1016 name: str,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1017 with_items: bool = False,
3744
658ddbabaf36 core (memory/sqla): new table/mapping to handle Pubsub node subscriptions:
Goffi <goffi@goffi.org>
parents: 3720
diff changeset
1018 with_subscriptions: bool = False,
3595
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1019 ) -> Optional[PubsubNode]:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1020 """
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1021 """
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1022 async with self.session() as session:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1023 stmt = (
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1024 select(PubsubNode)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1025 .filter_by(
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1026 service=service,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1027 name=name,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1028 profile_id=self.profiles[client.profile],
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1029 )
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1030 )
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1031 if with_items:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1032 stmt = stmt.options(
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1033 joinedload(PubsubNode.items)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1034 )
3744
658ddbabaf36 core (memory/sqla): new table/mapping to handle Pubsub node subscriptions:
Goffi <goffi@goffi.org>
parents: 3720
diff changeset
1035 if with_subscriptions:
658ddbabaf36 core (memory/sqla): new table/mapping to handle Pubsub node subscriptions:
Goffi <goffi@goffi.org>
parents: 3720
diff changeset
1036 stmt = stmt.options(
658ddbabaf36 core (memory/sqla): new table/mapping to handle Pubsub node subscriptions:
Goffi <goffi@goffi.org>
parents: 3720
diff changeset
1037 joinedload(PubsubNode.subscriptions)
658ddbabaf36 core (memory/sqla): new table/mapping to handle Pubsub node subscriptions:
Goffi <goffi@goffi.org>
parents: 3720
diff changeset
1038 )
3595
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1039 result = await session.execute(stmt)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1040 return result.unique().scalar_one_or_none()
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1041
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1042 @aio
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1043 async def setPubsubNode(
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1044 self,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1045 client: SatXMPPEntity,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1046 service: jid.JID,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1047 name: str,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1048 analyser: Optional[str] = None,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1049 type_: Optional[str] = None,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1050 subtype: Optional[str] = None,
3744
658ddbabaf36 core (memory/sqla): new table/mapping to handle Pubsub node subscriptions:
Goffi <goffi@goffi.org>
parents: 3720
diff changeset
1051 subscribed: bool = False,
3595
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1052 ) -> PubsubNode:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1053 node = PubsubNode(
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1054 profile_id=self.profiles[client.profile],
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1055 service=service,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1056 name=name,
3744
658ddbabaf36 core (memory/sqla): new table/mapping to handle Pubsub node subscriptions:
Goffi <goffi@goffi.org>
parents: 3720
diff changeset
1057 subscribed=subscribed,
3595
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1058 analyser=analyser,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1059 type_=type_,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1060 subtype=subtype,
3744
658ddbabaf36 core (memory/sqla): new table/mapping to handle Pubsub node subscriptions:
Goffi <goffi@goffi.org>
parents: 3720
diff changeset
1061 subscriptions=[],
3595
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1062 )
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1063 async with self.session() as session:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1064 async with session.begin():
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1065 session.add(node)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1066 return node
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1067
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1068 @aio
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1069 async def updatePubsubNodeSyncState(
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1070 self,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1071 node: PubsubNode,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1072 state: SyncState
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1073 ) -> None:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1074 async with self.session() as session:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1075 async with session.begin():
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1076 await session.execute(
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1077 update(PubsubNode)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1078 .filter_by(id=node.id)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1079 .values(
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1080 sync_state=state,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1081 sync_state_updated=time.time(),
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1082 )
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1083 )
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1084
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1085 @aio
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1086 async def deletePubsubNode(
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1087 self,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1088 profiles: Optional[List[str]],
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1089 services: Optional[List[jid.JID]],
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1090 names: Optional[List[str]]
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1091 ) -> None:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1092 """Delete items cached for a node
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1093
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1094 @param profiles: profile names from which nodes must be deleted.
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1095 None to remove nodes from ALL profiles
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1096 @param services: JIDs of pubsub services from which nodes must be deleted.
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1097 None to remove nodes from ALL services
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1098 @param names: names of nodes which must be deleted.
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1099 None to remove ALL nodes whatever is their names
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1100 """
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1101 stmt = delete(PubsubNode)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1102 if profiles is not None:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1103 stmt = stmt.where(
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1104 PubsubNode.profile.in_(
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1105 [self.profiles[p] for p in profiles]
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1106 )
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1107 )
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1108 if services is not None:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1109 stmt = stmt.where(PubsubNode.service.in_(services))
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1110 if names is not None:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1111 stmt = stmt.where(PubsubNode.name.in_(names))
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1112 async with self.session() as session:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1113 await session.execute(stmt)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1114 await session.commit()
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1115
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1116 @aio
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1117 async def cachePubsubItems(
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1118 self,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1119 client: SatXMPPEntity,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1120 node: PubsubNode,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1121 items: List[domish.Element],
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1122 parsed_items: Optional[List[dict]] = None,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1123 ) -> None:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1124 """Add items to database, using an upsert taking care of "updated" field"""
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1125 if parsed_items is not None and len(items) != len(parsed_items):
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1126 raise exceptions.InternalError(
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1127 "parsed_items must have the same lenght as items"
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1128 )
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1129 async with self.session() as session:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1130 async with session.begin():
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1131 for idx, item in enumerate(items):
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1132 parsed = parsed_items[idx] if parsed_items else None
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1133 stmt = insert(PubsubItem).values(
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1134 node_id = node.id,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1135 name = item["id"],
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1136 data = item,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1137 parsed = parsed,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1138 ).on_conflict_do_update(
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1139 index_elements=(PubsubItem.node_id, PubsubItem.name),
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1140 set_={
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1141 PubsubItem.data: item,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1142 PubsubItem.parsed: parsed,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1143 PubsubItem.updated: now()
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1144 }
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1145 )
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1146 await session.execute(stmt)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1147 await session.commit()
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1148
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1149 @aio
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1150 async def deletePubsubItems(
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1151 self,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1152 node: PubsubNode,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1153 items_names: Optional[List[str]] = None
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1154 ) -> None:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1155 """Delete items cached for a node
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1156
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1157 @param node: node from which items must be deleted
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1158 @param items_names: names of items to delete
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1159 if None, ALL items will be deleted
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1160 """
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1161 stmt = delete(PubsubItem)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1162 if node is not None:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1163 if isinstance(node, list):
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1164 stmt = stmt.where(PubsubItem.node_id.in_([n.id for n in node]))
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1165 else:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1166 stmt = stmt.filter_by(node_id=node.id)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1167 if items_names is not None:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1168 stmt = stmt.where(PubsubItem.name.in_(items_names))
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1169 async with self.session() as session:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1170 await session.execute(stmt)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1171 await session.commit()
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1172
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1173 @aio
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1174 async def purgePubsubItems(
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1175 self,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1176 services: Optional[List[jid.JID]] = None,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1177 names: Optional[List[str]] = None,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1178 types: Optional[List[str]] = None,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1179 subtypes: Optional[List[str]] = None,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1180 profiles: Optional[List[str]] = None,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1181 created_before: Optional[datetime] = None,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1182 updated_before: Optional[datetime] = None,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1183 ) -> None:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1184 """Delete items cached for a node
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1185
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1186 @param node: node from which items must be deleted
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1187 @param items_names: names of items to delete
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1188 if None, ALL items will be deleted
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1189 """
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1190 stmt = delete(PubsubItem)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1191 node_fields = {
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1192 "service": services,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1193 "name": names,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1194 "type_": types,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1195 "subtype": subtypes,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1196 }
3744
658ddbabaf36 core (memory/sqla): new table/mapping to handle Pubsub node subscriptions:
Goffi <goffi@goffi.org>
parents: 3720
diff changeset
1197 if profiles is not None:
658ddbabaf36 core (memory/sqla): new table/mapping to handle Pubsub node subscriptions:
Goffi <goffi@goffi.org>
parents: 3720
diff changeset
1198 node_fields["profile_id"] = [self.profiles[p] for p in profiles]
658ddbabaf36 core (memory/sqla): new table/mapping to handle Pubsub node subscriptions:
Goffi <goffi@goffi.org>
parents: 3720
diff changeset
1199
3595
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1200 if any(x is not None for x in node_fields.values()):
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1201 sub_q = select(PubsubNode.id)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1202 for col, values in node_fields.items():
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1203 if values is None:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1204 continue
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1205 sub_q = sub_q.where(getattr(PubsubNode, col).in_(values))
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1206 stmt = (
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1207 stmt
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1208 .where(PubsubItem.node_id.in_(sub_q))
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1209 .execution_options(synchronize_session=False)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1210 )
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1211
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1212 if created_before is not None:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1213 stmt = stmt.where(PubsubItem.created < created_before)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1214
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1215 if updated_before is not None:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1216 stmt = stmt.where(PubsubItem.updated < updated_before)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1217
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1218 async with self.session() as session:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1219 await session.execute(stmt)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1220 await session.commit()
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1221
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1222 @aio
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1223 async def getItems(
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1224 self,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1225 node: PubsubNode,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1226 max_items: Optional[int] = None,
3753
10b71e3526bd core (memory/sqla): add attribute to filter on `item_ids` in `getItems`
Goffi <goffi@goffi.org>
parents: 3744
diff changeset
1227 item_ids: Optional[list[str]] = None,
3595
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1228 before: Optional[str] = None,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1229 after: Optional[str] = None,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1230 from_index: Optional[int] = None,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1231 order_by: Optional[List[str]] = None,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1232 desc: bool = True,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1233 force_rsm: bool = False,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1234 ) -> Tuple[List[PubsubItem], dict]:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1235 """Get Pubsub Items from cache
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1236
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1237 @param node: retrieve items from this node (must be synchronised)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1238 @param max_items: maximum number of items to retrieve
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1239 @param before: get items which are before the item with this name in given order
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1240 empty string is not managed here, use desc order to reproduce RSM
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1241 behaviour.
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1242 @param after: get items which are after the item with this name in given order
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1243 @param from_index: get items with item index (as defined in RSM spec)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1244 starting from this number
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1245 @param order_by: sorting order of items (one of C.ORDER_BY_*)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1246 @param desc: direction or ordering
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1247 @param force_rsm: if True, force the use of RSM worklow.
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1248 RSM workflow is automatically used if any of before, after or
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1249 from_index is used, but if only RSM max_items is used, it won't be
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1250 used by default. This parameter let's use RSM workflow in this
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1251 case. Note that in addition to RSM metadata, the result will not be
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1252 the same (max_items without RSM will returns most recent items,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1253 i.e. last items in modification order, while max_items with RSM
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1254 will return the oldest ones (i.e. first items in modification
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1255 order).
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1256 to be used when max_items is used from RSM
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1257 """
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1258
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1259 metadata = {
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1260 "service": node.service,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1261 "node": node.name,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1262 "uri": uri.buildXMPPUri(
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1263 "pubsub",
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1264 path=node.service.full(),
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1265 node=node.name,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1266 ),
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1267 }
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1268 if max_items is None:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1269 max_items = 20
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1270
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1271 use_rsm = any((before, after, from_index is not None))
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1272 if force_rsm and not use_rsm:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1273 #
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1274 use_rsm = True
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1275 from_index = 0
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1276
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1277 stmt = (
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1278 select(PubsubItem)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1279 .filter_by(node_id=node.id)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1280 .limit(max_items)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1281 )
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1282
3753
10b71e3526bd core (memory/sqla): add attribute to filter on `item_ids` in `getItems`
Goffi <goffi@goffi.org>
parents: 3744
diff changeset
1283 if item_ids is not None:
10b71e3526bd core (memory/sqla): add attribute to filter on `item_ids` in `getItems`
Goffi <goffi@goffi.org>
parents: 3744
diff changeset
1284 stmt = stmt.where(PubsubItem.name.in_(item_ids))
10b71e3526bd core (memory/sqla): add attribute to filter on `item_ids` in `getItems`
Goffi <goffi@goffi.org>
parents: 3744
diff changeset
1285
3595
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1286 if not order_by:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1287 order_by = [C.ORDER_BY_MODIFICATION]
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1288
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1289 order = []
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1290 for order_type in order_by:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1291 if order_type == C.ORDER_BY_MODIFICATION:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1292 if desc:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1293 order.extend((PubsubItem.updated.desc(), PubsubItem.id.desc()))
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1294 else:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1295 order.extend((PubsubItem.updated.asc(), PubsubItem.id.asc()))
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1296 elif order_type == C.ORDER_BY_CREATION:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1297 if desc:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1298 order.append(PubsubItem.id.desc())
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1299 else:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1300 order.append(PubsubItem.id.asc())
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1301 else:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1302 raise exceptions.InternalError(f"Unknown order type {order_type!r}")
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1303
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1304 stmt = stmt.order_by(*order)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1305
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1306 if use_rsm:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1307 # CTE to have result row numbers
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1308 row_num_q = select(
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1309 PubsubItem.id,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1310 PubsubItem.name,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1311 # row_number starts from 1, but RSM index must start from 0
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1312 (func.row_number().over(order_by=order)-1).label("item_index")
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1313 ).filter_by(node_id=node.id)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1314
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1315 row_num_cte = row_num_q.cte()
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1316
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1317 if max_items > 0:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1318 # as we can't simply use PubsubItem.id when we order by modification,
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1319 # we need to use row number
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1320 item_name = before or after
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1321 row_num_limit_q = (
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1322 select(row_num_cte.c.item_index)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1323 .where(row_num_cte.c.name==item_name)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1324 ).scalar_subquery()
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1325
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1326 stmt = (
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1327 select(row_num_cte.c.item_index, PubsubItem)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1328 .join(row_num_cte, PubsubItem.id == row_num_cte.c.id)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1329 .limit(max_items)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1330 )
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1331 if before:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1332 stmt = (
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1333 stmt
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1334 .where(row_num_cte.c.item_index<row_num_limit_q)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1335 .order_by(row_num_cte.c.item_index.desc())
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1336 )
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1337 elif after:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1338 stmt = (
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1339 stmt
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1340 .where(row_num_cte.c.item_index>row_num_limit_q)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1341 .order_by(row_num_cte.c.item_index.asc())
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1342 )
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1343 else:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1344 stmt = (
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1345 stmt
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1346 .where(row_num_cte.c.item_index>=from_index)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1347 .order_by(row_num_cte.c.item_index.asc())
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1348 )
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1349 # from_index is used
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1350
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1351 async with self.session() as session:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1352 if max_items == 0:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1353 items = result = []
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1354 else:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1355 result = await session.execute(stmt)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1356 result = result.all()
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1357 if before:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1358 result.reverse()
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1359 items = [row[-1] for row in result]
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1360 rows_count = (
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1361 await session.execute(row_num_q.with_only_columns(count()))
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1362 ).scalar_one()
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1363
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1364 try:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1365 index = result[0][0]
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1366 except IndexError:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1367 index = None
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1368
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1369 try:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1370 first = result[0][1].name
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1371 except IndexError:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1372 first = None
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1373 last = None
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1374 else:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1375 last = result[-1][1].name
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1376
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1377 metadata["rsm"] = {
3719
cf930bb282ac memory (sqla): don't set keys with None as value for RSM in `getItems`
Goffi <goffi@goffi.org>
parents: 3673
diff changeset
1378 k: v for k, v in {
cf930bb282ac memory (sqla): don't set keys with None as value for RSM in `getItems`
Goffi <goffi@goffi.org>
parents: 3673
diff changeset
1379 "index": index,
cf930bb282ac memory (sqla): don't set keys with None as value for RSM in `getItems`
Goffi <goffi@goffi.org>
parents: 3673
diff changeset
1380 "count": rows_count,
cf930bb282ac memory (sqla): don't set keys with None as value for RSM in `getItems`
Goffi <goffi@goffi.org>
parents: 3673
diff changeset
1381 "first": first,
cf930bb282ac memory (sqla): don't set keys with None as value for RSM in `getItems`
Goffi <goffi@goffi.org>
parents: 3673
diff changeset
1382 "last": last,
cf930bb282ac memory (sqla): don't set keys with None as value for RSM in `getItems`
Goffi <goffi@goffi.org>
parents: 3673
diff changeset
1383 }.items() if v is not None
3595
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1384 }
3720
40a6374fcd44 memory (sqla): fix `complete` setting when `index` is `None` in `getItems`
Goffi <goffi@goffi.org>
parents: 3719
diff changeset
1385 metadata["complete"] = (index or 0) + len(result) == rows_count
3595
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1386
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1387 return items, metadata
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1388
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1389 async with self.session() as session:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1390 result = await session.execute(stmt)
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1391
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1392 result = result.scalars().all()
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1393 if desc:
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1394 result.reverse()
7510648e8e3a core (memory/sqla): methods to manipulate pubsub tables
Goffi <goffi@goffi.org>
parents: 3583
diff changeset
1395 return result, metadata
3664
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1396
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1397 def _getSqlitePath(
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1398 self,
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1399 path: List[Union[str, int]]
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1400 ) -> str:
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1401 """generate path suitable to query JSON element with SQLite"""
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1402 return f"${''.join(f'[{p}]' if isinstance(p, int) else f'.{p}' for p in path)}"
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1403
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1404 @aio
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1405 async def searchPubsubItems(
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1406 self,
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1407 query: dict,
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1408 ) -> Tuple[List[PubsubItem]]:
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1409 """Search for pubsub items in cache
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1410
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1411 @param query: search terms. Keys can be:
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1412 :fts (str):
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1413 Full-Text Search query. Currently SQLite FT5 engine is used, its query
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1414 syntax can be used, see `FTS5 Query documentation
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1415 <https://sqlite.org/fts5.html#full_text_query_syntax>`_
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1416 :profiles (list[str]):
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1417 filter on nodes linked to those profiles
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1418 :nodes (list[str]):
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1419 filter on nodes with those names
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1420 :services (list[jid.JID]):
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1421 filter on nodes from those services
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1422 :types (list[str|None]):
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1423 filter on nodes with those types. None can be used to filter on nodes with
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1424 no type set
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1425 :subtypes (list[str|None]):
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1426 filter on nodes with those subtypes. None can be used to filter on nodes with
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1427 no subtype set
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1428 :parsed (list[dict]):
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1429 Filter on a parsed data field. The dict must contain 3 keys: ``path``
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1430 which is a list of str or int giving the path to the field of interest
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1431 (str for a dict key, int for a list index), ``operator`` with indicate the
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1432 operator to use to check the condition, and ``value`` which depends of
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1433 field type and operator.
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1434
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1435 See documentation for details on operators (it's currently explained at
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1436 ``doc/libervia-cli/pubsub_cache.rst`` in ``search`` command
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1437 documentation).
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1438
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1439 :order-by (list[dict]):
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1440 Indicates how to order results. The dict can contain either a ``order``
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1441 for a well-know order or a ``path`` for a parsed data field path
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1442 (``order`` and ``path`` can't be used at the same time), an an optional
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1443 ``direction`` which can be ``asc`` or ``desc``. See documentation for
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1444 details on well-known orders (it's currently explained at
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1445 ``doc/libervia-cli/pubsub_cache.rst`` in ``search`` command
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1446 documentation).
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1447
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1448 :index (int):
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1449 starting index of items to return from the query result. It's translated
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1450 to SQL's OFFSET
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1451
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1452 :limit (int):
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1453 maximum number of items to return. It's translated to SQL's LIMIT.
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1454
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1455 @result: found items (the ``node`` attribute will be filled with suitable
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1456 PubsubNode)
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1457 """
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1458 # TODO: FTS and parsed data filters use SQLite specific syntax
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1459 # when other DB engines will be used, this will have to be adapted
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1460 stmt = select(PubsubItem)
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1461
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1462 # Full-Text Search
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1463 fts = query.get("fts")
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1464 if fts:
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1465 fts_select = text(
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1466 "SELECT rowid, rank FROM pubsub_items_fts(:fts_query)"
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1467 ).bindparams(fts_query=fts).columns(rowid=Integer).subquery()
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1468 stmt = (
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1469 stmt
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1470 .select_from(fts_select)
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1471 .outerjoin(PubsubItem, fts_select.c.rowid == PubsubItem.id)
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1472 )
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1473
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1474 # node related filters
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1475 profiles = query.get("profiles")
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1476 if (profiles
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1477 or any(query.get(k) for k in ("nodes", "services", "types", "subtypes"))
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1478 ):
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1479 stmt = stmt.join(PubsubNode).options(contains_eager(PubsubItem.node))
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1480 if profiles:
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1481 try:
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1482 stmt = stmt.where(
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1483 PubsubNode.profile_id.in_(self.profiles[p] for p in profiles)
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1484 )
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1485 except KeyError as e:
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1486 raise exceptions.ProfileUnknownError(
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1487 f"This profile doesn't exist: {e.args[0]!r}"
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1488 )
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1489 for key, attr in (
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1490 ("nodes", "name"),
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1491 ("services", "service"),
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1492 ("types", "type_"),
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1493 ("subtypes", "subtype")
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1494 ):
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1495 value = query.get(key)
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1496 if not value:
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1497 continue
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1498 if key in ("types", "subtypes") and None in value:
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1499 # NULL can't be used with SQL's IN, so we have to add a condition with
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1500 # IS NULL, and use a OR if there are other values to check
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1501 value.remove(None)
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1502 condition = getattr(PubsubNode, attr).is_(None)
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1503 if value:
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1504 condition = or_(
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1505 getattr(PubsubNode, attr).in_(value),
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1506 condition
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1507 )
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1508 else:
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1509 condition = getattr(PubsubNode, attr).in_(value)
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1510 stmt = stmt.where(condition)
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1511 else:
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1512 stmt = stmt.options(selectinload(PubsubItem.node))
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1513
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1514 # parsed data filters
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1515 parsed = query.get("parsed", [])
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1516 for filter_ in parsed:
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1517 try:
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1518 path = filter_["path"]
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1519 operator = filter_["op"]
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1520 value = filter_["value"]
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1521 except KeyError as e:
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1522 raise ValueError(
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1523 f'missing mandatory key {e.args[0]!r} in "parsed" filter'
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1524 )
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1525 try:
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1526 op_attr = OP_MAP[operator]
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1527 except KeyError:
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1528 raise ValueError(f"invalid operator: {operator!r}")
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1529 sqlite_path = self._getSqlitePath(path)
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1530 if operator in ("overlap", "ioverlap", "disjoint", "idisjoint"):
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1531 col = literal_column("json_each.value")
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1532 if operator[0] == "i":
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1533 col = func.lower(col)
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1534 value = [str(v).lower() for v in value]
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1535 condition = (
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1536 select(1)
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1537 .select_from(func.json_each(PubsubItem.parsed, sqlite_path))
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1538 .where(col.in_(value))
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1539 ).scalar_subquery()
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1540 if operator in ("disjoint", "idisjoint"):
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1541 condition = condition.is_(None)
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1542 stmt = stmt.where(condition)
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1543 elif operator == "between":
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1544 try:
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1545 left, right = value
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1546 except (ValueError, TypeError):
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1547 raise ValueError(_(
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1548 "invalid value for \"between\" filter, you must use a 2 items "
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1549 "array: {value!r}"
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1550 ).format(value=value))
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1551 col = func.json_extract(PubsubItem.parsed, sqlite_path)
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1552 stmt = stmt.where(col.between(left, right))
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1553 else:
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1554 # we use func.json_extract instead of generic JSON way because SQLAlchemy
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1555 # add a JSON_QUOTE to the value, and we want SQL value
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1556 col = func.json_extract(PubsubItem.parsed, sqlite_path)
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1557 stmt = stmt.where(getattr(col, op_attr)(value))
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1558
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1559 # order
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1560 order_by = query.get("order-by") or [{"order": "creation"}]
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1561
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1562 for order_data in order_by:
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1563 order, path = order_data.get("order"), order_data.get("path")
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1564 if order and path:
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1565 raise ValueError(_(
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1566 '"order" and "path" can\'t be used at the same time in '
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1567 '"order-by" data'
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1568 ))
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1569 if order:
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1570 if order == "creation":
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1571 col = PubsubItem.id
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1572 elif order == "modification":
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1573 col = PubsubItem.updated
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1574 elif order == "item_id":
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1575 col = PubsubItem.name
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1576 elif order == "rank":
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1577 if not fts:
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1578 raise ValueError(
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1579 "'rank' order can only be used with Full-Text Search (fts)"
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1580 )
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1581 col = literal_column("rank")
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1582 else:
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1583 raise NotImplementedError(f"Unknown {order!r} order")
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1584 else:
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1585 # we have a JSON path
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1586 # sqlite_path = self._getSqlitePath(path)
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1587 col = PubsubItem.parsed[path]
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1588 direction = order_data.get("direction", "ASC").lower()
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1589 if not direction in ("asc", "desc"):
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1590 raise ValueError(f"Invalid order-by direction: {direction!r}")
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1591 stmt = stmt.order_by(getattr(col, direction)())
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1592
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1593 # offset, limit
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1594 index = query.get("index")
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1595 if index:
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1596 stmt = stmt.offset(index)
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1597 limit = query.get("limit")
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1598 if limit:
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1599 stmt = stmt.limit(limit)
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1600
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1601 async with self.session() as session:
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1602 result = await session.execute(stmt)
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1603
9ae6ec74face memory (sqla): implement `searchPubsubItems`:
Goffi <goffi@goffi.org>
parents: 3638
diff changeset
1604 return result.scalars().all()