Mercurial > libervia-backend

comparison sat/memory/sqla.py @ 3798:b5013bada4b6

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
core (memory/sqla): generic `get` + `session_add` in `delete`: new generic `get` allow to retrieve an ORM object with given column and value, and optionally join load attributes. In `delete`, it is now possible to specify instances to add to session, this allow, to update them in the same transaction. It is also now possible to specify a list of object to delete them all in the same transaction. rel 367
author Goffi <goffi@goffi.org>
date Fri, 17 Jun 2022 14:15:23 +0200
parents cc653b2685f0
children 1a10b8b4f169
comparison
equal deleted inserted replaced
3797:cc653b2685f0 3798:b5013bada4b6
28 from sqlalchemy.exc import IntegrityError, NoResultFound 28 from sqlalchemy.exc import IntegrityError, NoResultFound
29 from sqlalchemy.orm import ( 29 from sqlalchemy.orm import (
30 sessionmaker, subqueryload, joinedload, selectinload, contains_eager 30 sessionmaker, subqueryload, joinedload, selectinload, contains_eager
31 ) 31 )
32 from sqlalchemy.orm.decl_api import DeclarativeMeta 32 from sqlalchemy.orm.decl_api import DeclarativeMeta
33 from sqlalchemy.orm.attributes import Mapped
33 from sqlalchemy.future import select 34 from sqlalchemy.future import select
34 from sqlalchemy.engine import Engine, Connection 35 from sqlalchemy.engine import Engine, Connection
35 from sqlalchemy import update, delete, and_, or_, event, func 36 from sqlalchemy import update, delete, and_, or_, event, func
36 from sqlalchemy.sql.functions import coalesce, sum as sum_, now, count 37 from sqlalchemy.sql.functions import coalesce, sum as sum_, now, count
37 from sqlalchemy.dialects.sqlite import insert 38 from sqlalchemy.dialects.sqlite import insert
214 self.initialized.callback(None) 215 self.initialized.callback(None)
215 216
216 ## Generic 217 ## Generic
217 218
218 @aio 219 @aio
220 async def get(
221 self,
222 client: SatXMPPEntity,
223 db_cls: DeclarativeMeta,
224 db_id_col: Mapped,
225 id_value: Any,
226 joined_loads=None
227 ) -> Optional[DeclarativeMeta]:
228 stmt = select(db_cls).where(db_id_col==id_value)
229 if client is not None:
230 stmt = stmt.filter_by(profile_id=self.profiles[client.profile])
231 if joined_loads is not None:
232 for joined_load in joined_loads:
233 stmt = stmt.options(joinedload(joined_load))
234 async with self.session() as session:
235 result = await session.execute(stmt)
236 if joined_loads is not None:
237 result = result.unique()
238 return result.scalar_one_or_none()
239
240 @aio
219 async def add(self, db_obj: DeclarativeMeta) -> None: 241 async def add(self, db_obj: DeclarativeMeta) -> None:
220 """Add an object to database""" 242 """Add an object to database"""
221 async with self.session() as session: 243 async with self.session() as session:
222 async with session.begin(): 244 async with session.begin():
223 session.add(db_obj) 245 session.add(db_obj)
224 246
225 @aio 247 @aio
226 async def delete(self, db_obj: DeclarativeMeta) -> None: 248 async def delete(
227 """Delete an object from database""" 249 self,
250 db_obj: Union[DeclarativeMeta, List[DeclarativeMeta]],
251 session_add: Optional[List[DeclarativeMeta]]
252 ) -> None:
253 """Delete an object from database
254
255 @param db_obj: object to delete or list of objects to delete
256 @param session_add: other objects to add to session.
257 This is useful when parents of deleted objects needs to be updated too, or if
258 other objects needs to be updated in the same transaction.
259 """
260 if not db_obj:
261 return
262 if not isinstance(db_obj, list):
263 db_obj = [db_obj]
228 async with self.session() as session: 264 async with self.session() as session:
229 async with session.begin(): 265 async with session.begin():
230 await session.delete(db_obj) 266 if session_add is not None:
267 for obj in session_add:
268 session.add(obj)
269 for obj in db_obj:
270 await session.delete(obj)
231 await session.commit() 271 await session.commit()
232 272
233 ## Profiles 273 ## Profiles
234 274
235 def getProfilesList(self) -> List[str]: 275 def getProfilesList(self) -> List[str]: