3 Commits

Author SHA1 Message Date
d6d98d20cb fix: typing shenanigans 2024-12-12 20:47:34 +07:00
476a911df9 [database] fix invalid transaction being left open 2024-11-25 20:13:51 +07:00
58a5177a30 use SQL's limit/offset pagination for nextIndex/maxCount requests (#185)
Instead of retrieving the entire list of items/characters/scores/etc. at once (and even store them in memory), use SQL's `LIMIT ... OFFSET ...` pagination so we only take what we need.

Currently only CHUNITHM uses this, but this will also affect maimai DX and O.N.G.E.K.I. once the PR is ready.

Also snuck in a fix for CHUNITHM/maimai DX's `GetUserRivalMusicApi` to respect the `userRivalMusicLevelList` sent by the client.

### How this works

Say we have a `GetUserCharacterApi` request:

```json
{
    "userId": 10000,
    "maxCount": 700,
    "nextIndex": 0
}
```

Instead of getting the entire character list from the database (which can be very large if the user force unlocked everything), add limit/offset to the query:

```python
select(character)
.where(character.c.user == user_id)
.order_by(character.c.id.asc())
.limit(max_count + 1)
.offset(next_index)
```

The query takes `maxCount + 1` items from the database to determine if there is more items than can be returned:

```python
rows = ...

if len(rows) > max_count:
    # return only max_count rows
    next_index += max_count
else:
    # return everything left
    next_index = -1
```

This has the benefit of not needing to load everything into memory (and also having to store server state, as seen in the [`SCORE_BUFFER` list](2274b42358/titles/chuni/base.py (L13)).)

Reviewed-on: Hay1tsme/artemis#185
Co-authored-by: beerpsi <beerpsi@duck.com>
Co-committed-by: beerpsi <beerpsi@duck.com>
2024-11-16 19:10:29 +00:00
2 changed files with 23 additions and 21 deletions

View File

@ -1,12 +1,11 @@
import logging import logging
import os import os
import secrets import secrets
import ssl
import string import string
import warnings import warnings
from hashlib import sha256 from hashlib import sha256
from logging.handlers import TimedRotatingFileHandler from logging.handlers import TimedRotatingFileHandler
from typing import Any, ClassVar, Optional from typing import ClassVar, Optional
import alembic.config import alembic.config
import bcrypt import bcrypt
@ -17,6 +16,7 @@ from sqlalchemy.ext.asyncio import (
AsyncSession, AsyncSession,
create_async_engine, create_async_engine,
) )
from sqlalchemy.orm import sessionmaker
from core.config import CoreConfig from core.config import CoreConfig
from core.data.schema import ArcadeData, BaseData, CardData, UserData, metadata from core.data.schema import ArcadeData, BaseData, CardData, UserData, metadata
@ -25,7 +25,7 @@ from core.utils import MISSING, Utils
class Data: class Data:
engine: ClassVar[AsyncEngine] = MISSING engine: ClassVar[AsyncEngine] = MISSING
session: ClassVar[AsyncSession] = MISSING session: ClassVar["sessionmaker[AsyncSession]"] = MISSING
user: ClassVar[UserData] = MISSING user: ClassVar[UserData] = MISSING
arcade: ClassVar[ArcadeData] = MISSING arcade: ClassVar[ArcadeData] = MISSING
card: ClassVar[CardData] = MISSING card: ClassVar[CardData] = MISSING
@ -53,7 +53,7 @@ class Data:
self.__engine = Data.engine self.__engine = Data.engine
if Data.session is MISSING: if Data.session is MISSING:
Data.session = AsyncSession(Data.engine, expire_on_commit=False) Data.session = sessionmaker(Data.engine, expire_on_commit=False, class_=AsyncSession)
if Data.user is MISSING: if Data.user is MISSING:
Data.user = UserData(self.config, self.session) Data.user = UserData(self.config, self.session)

View File

@ -9,6 +9,7 @@ from sqlalchemy.engine import Row
from sqlalchemy.engine.cursor import CursorResult from sqlalchemy.engine.cursor import CursorResult
from sqlalchemy.exc import SQLAlchemyError from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.ext.asyncio import AsyncSession from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import sessionmaker
from sqlalchemy.schema import ForeignKey from sqlalchemy.schema import ForeignKey
from sqlalchemy.sql import func, text from sqlalchemy.sql import func, text
from sqlalchemy.types import INTEGER, JSON, TEXT, TIMESTAMP, Integer, String from sqlalchemy.types import INTEGER, JSON, TEXT, TIMESTAMP, Integer, String
@ -38,7 +39,7 @@ event_log: Table = Table(
class BaseData: class BaseData:
def __init__(self, cfg: CoreConfig, conn: AsyncSession) -> None: def __init__(self, cfg: CoreConfig, conn: "sessionmaker[AsyncSession]") -> None:
self.config = cfg self.config = cfg
self.conn = conn self.conn = conn
self.logger = logging.getLogger("database") self.logger = logging.getLogger("database")
@ -46,9 +47,10 @@ class BaseData:
async def execute(self, sql: str, opts: Dict[str, Any] = {}) -> Optional[CursorResult]: async def execute(self, sql: str, opts: Dict[str, Any] = {}) -> Optional[CursorResult]:
res = None res = None
async with self.conn() as session:
try: try:
self.logger.debug(f"SQL Execute: {''.join(str(sql).splitlines())}") self.logger.debug(f"SQL Execute: {''.join(str(sql).splitlines())}")
res = await self.conn.execute(text(sql), opts) res = await session.execute(text(sql), opts)
except SQLAlchemyError as e: except SQLAlchemyError as e:
self.logger.error(f"SQLAlchemy error {e}") self.logger.error(f"SQLAlchemy error {e}")
@ -60,7 +62,7 @@ class BaseData:
except Exception: except Exception:
try: try:
res = await self.conn.execute(sql, opts) res = await session.execute(sql, opts)
except SQLAlchemyError as e: except SQLAlchemyError as e:
self.logger.error(f"SQLAlchemy error {e}") self.logger.error(f"SQLAlchemy error {e}")