Build and run agents you can see, understand and trust.
Python agentchatbotlarge-language-modelsllmllm-agentmcpmulti-agentmulti-modalreact-agentrealtime-agentvoice-agentvoice-assistant
# -*- coding: utf-8 -*-
# pylint: disable=too-many-public-methods,protected-access
"""Round-trip and semantic tests for :class:`AsyncSQLAlchemyStorage`.
Runs against an in-memory SQLite database via ``sqlite+aiosqlite``
so the whole suite is self-contained — no external server needed.
The intent is to exercise every ``StorageBase`` method the Redis
backend implements, mirroring the shape (though not always the
literal assertions) of the Redis backend's tests so both backends
stay behavioural equivalents.
"""
import asyncio
from contextlib import AsyncExitStack
from datetime import datetime, timedelta, timezone
from importlib import import_module
from os import environ
from pathlib import Path
from tempfile import TemporaryDirectory
from typing import Any
from unittest import TestCase
from unittest.async_case import IsolatedAsyncioTestCase
from unittest.mock import patch
from alembic import command
from alembic.config import Config
from pydantic import SecretStr
import sqlalchemy as sa
from sqlalchemy.dialects import mysql
from sqlalchemy.engine import URL
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.sql.dml import Insert
from utils import AnyString
from agentscope.app.storage._sql._mappers import _to_record
from agentscope.app.storage._sql import _storage as sql_storage
from agentscope.app.storage._sql._tables import AgentRow, SessionRow
from agentscope.app.storage import (
AgentData,
AgentRecord,
ChannelBinding,
ChannelRecord,
ChatModelConfig,
EmbeddingModelConfig,
KnowledgeBaseData,
KnowledgeBaseRecord,
KnowledgeDocumentData,
KnowledgeDocumentRecord,
MCPRecord,
RoutingConfig,
ScheduleData,
ScheduleRecord,
SessionConfig,
SessionSettings,
ChannelOrigin,
UserOrigin,
ScheduleOrigin,
SessionRecord,
SkillRecord,
SOPAgentRef,
SOPData,
SOPRecord,
SOPRunRecord,
SOPStepDataV1,
AgentVerifier,
HumanVerifier,
AsyncSQLAlchemyStorage,
TeamData,
TeamMember,
TeamRecord,
VoiceProfileData,
VoiceProfileRecord,
)
from agentscope.app.storage._sql._tables import ChannelRow
from agentscope.agent import ContextConfig, ReActConfig
from agentscope.credential import DashScopeCredential
from agentscope.mcp import HttpMCPConfig, MCPClient
from agentscope.message import AssistantMsg, UserMsg
from agentscope.sop import SOPPhase, SOPRunState, SOPStepRunState
def _channel_record(
channel_id: str,
user_id: str = "user-1",
) -> ChannelRecord:
"""Build a minimal but complete :class:`ChannelRecord`."""
return ChannelRecord(
id=channel_id,
channel_type="feishu",
user_id=user_id,
credentials={"app_id": channel_id},
routing=RoutingConfig(
bindings=[ChannelBinding(match_value="*", agent_id="agent-x")],
),
session=SessionSettings(
chat_model_config={
"type": "openai",
"credential_id": "cred-1",
"model": "gpt-4o",
"parameters": {},
},
),
)
def _agent_record(user_id: str, name: str = "agent-x") -> AgentRecord:
"""Build a minimal but complete :class:`AgentRecord`."""
return AgentRecord(
user_id=user_id,
data=AgentData(
name=name,
context_config=ContextConfig(),
react_config=ReActConfig(),
),
)
def _session_config() -> SessionConfig:
"""Build a minimal :class:`SessionConfig`."""
return SessionConfig(
workspace_id="ws-1",
name="s",
chat_model_config=ChatModelConfig(
type="openai",
credential_id="cred-1",
model="gpt-4o",
parameters={},
),
)
def _kb_record(user_id: str, name: str = "kb") -> KnowledgeBaseRecord:
"""Build a KB record with a default embedding config."""
return KnowledgeBaseRecord(
user_id=user_id,
data=KnowledgeBaseData(
name=name,
description="",
embedding_model_config=EmbeddingModelConfig(
type="openai_credential",
credential_id="cred-1",
model="text-embedding-3-small",
dimensions=8,
),
collection_name="kb-x",
),
)
def _kd_record(
user_id: str,
knowledge_base_id: str,
filename: str = "f.txt",
) -> KnowledgeDocumentRecord:
"""Build a fresh (``pending`` / unclaimed) document record."""
return KnowledgeDocumentRecord(
user_id=user_id,
knowledge_base_id=knowledge_base_id,
data=KnowledgeDocumentData(
filename=filename,
size=42,
blob_uri=f"local://{filename}",
),
)
def _schedule_record(user_id: str, agent_id: str) -> ScheduleRecord:
"""Build a schedule record."""
return ScheduleRecord(
user_id=user_id,
agent_id=agent_id,
data=ScheduleData(
name="daily",
cron_expression="0 9 * * *",
chat_model_config=ChatModelConfig(
type="openai",
credential_id="cred-1",
model="gpt-4o",
parameters={},
),
),
)
def _mcp_record(user_id: str, name: str = "deepwiki") -> MCPRecord:
"""Build an installed-MCP record wrapping a stateless HTTP MCP."""
return MCPRecord(
user_id=user_id,
client=MCPClient(
name=name,
is_stateful=False,
mcp_config=HttpMCPConfig(url="https://mcp.deepwiki.com/mcp"),
),
)
# A procedure needs at least one milestone, so the fixtures that only
# care about the record around it still carry one.
_A_STEP = SOPStepDataV1(
subject="model",
description="make the hull",
executor=SOPAgentRef(agent_id="a-1", session_key="modeller"),
)
class AsyncSQLAlchemyStorageTest(IsolatedAsyncioTestCase):
"""End-to-end tests for :class:`AsyncSQLAlchemyStorage` over
in-memory SQLite."""
async def asyncSetUp(self) -> None:
# ``:memory:`` gives a private DB per connection; using a
# shared-cache URI would too, but per-test isolation is what
# we want here.
self._stack = AsyncExitStack()
self.storage = await self._stack.enter_async_context(
AsyncSQLAlchemyStorage(
"sqlite+aiosqlite:///:memory:",
create_tables=True,
),
)
async def asyncTearDown(self) -> None:
await self._stack.aclose()
# ------------------------------------------------------------------
# Credentials
# ------------------------------------------------------------------
async def test_credentials_round_trip(self) -> None:
"""Upsert / list / get / delete + owner scoping."""
cred = DashScopeCredential(api_key=SecretStr("sk-1"))
cid = await self.storage.upsert_credential("user-1", cred)
listed = await self.storage.list_credentials("user-1")
self.assertEqual([c.id for c in listed], [cid])
self.assertEqual(listed[0].data["api_key"], "sk-1")
fetched = await self.storage.get_credential("user-1", cid)
self.assertEqual(fetched.id, cid)
# Cross-user isolation
self.assertEqual(await self.storage.list_credentials("user-2"), [])
self.assertIsNone(await self.storage.get_credential("user-2", cid))
# Delete + double-delete
self.assertTrue(await self.storage.delete_credential("user-1", cid))
self.assertFalse(await self.storage.delete_credential("user-1", cid))
async def test_upsert_credential_is_owner_scoped(self) -> None:
"""A preset id owned by another user is never read or clobbered."""
import sqlalchemy.exc
victim = DashScopeCredential(api_key=SecretStr("victim-key"))
cid = await self.storage.upsert_credential("user-1", victim)
before = await self.storage.get_credential("user-1", cid)
# Same owner + preset id → in-place update, created_at preserved.
rotated = DashScopeCredential(api_key=SecretStr("rotated"), id=cid)
self.assertEqual(
await self.storage.upsert_credential("user-1", rotated),
cid,
)
after = await self.storage.get_credential("user-1", cid)
self.assertEqual(after.data["api_key"], "rotated")
self.assertEqual(after.created_at, before.created_at)
# Attacker (user-2) presenting the victim's id must not touch the
# victim's row: the global-id INSERT collides and raises, and the
# victim's data + ownership stay intact.
attack = DashScopeCredential(api_key=SecretStr("attacker"), id=cid)
with self.assertRaises(sqlalchemy.exc.IntegrityError):
await self.storage.upsert_credential("user-2", attack)
victim_now = await self.storage.get_credential("user-1", cid)
self.assertEqual(victim_now.data["api_key"], "rotated")
self.assertIsNone(await self.storage.get_credential("user-2", cid))
self.assertEqual(await self.storage.list_credentials("user-2"), [])
# ------------------------------------------------------------------
# Agents
# ------------------------------------------------------------------
async def test_agents_round_trip_and_source_filter(self) -> None:
"""Legacy agent rows load grouped and team workers stay hidden."""
record_id = "agent-legacy"
data_id = "agent-data-legacy"
now = datetime.now()
# Insert at the row layer so AgentData cannot normalize the legacy
# payload before it reaches SQL storage.
# pylint: disable=protected-access
async with self.storage._session() as sess:
sess.add(
AgentRow(
id=record_id,
created_at=now,
updated_at=now,
user_id="user-1",
source="user",
payload={
"data": {
"id": data_id,
"name": "usr",
"system_prompt": "You are usr.",
"context_config": {"max_image_num": 3},
"react_config": {"max_iters": 7},
},
},
),
)
await sess.commit()
team_agent = _agent_record("user-1", "team-worker")
team_agent.source = "team"
await self.storage.upsert_agent("user-1", team_agent)
listed = await self.storage.list_agents("user-1")
self.assertEqual([agent.id for agent in listed], [record_id])
self.assertDictEqual(
listed[0].model_dump(mode="json", exclude_defaults=True),
{
"id": record_id,
"updated_at": now.isoformat(),
"created_at": now.isoformat(),
"user_id": "user-1",
"data": {
"id": data_id,
"name": "usr",
"system_prompt": "You are usr.",
"chat_config": {
"context_config": {"max_image_num": 3},
"react_config": {"max_iters": 7},
},
},
},
)
# But direct get works for the team-spawned worker
self.assertEqual(
(await self.storage.get_agent("user-1", team_agent.id)).id,
team_agent.id,
)
async def test_delete_agent_cascades_sessions_and_schedules(self) -> None:
"""Deleting an agent removes its sessions + schedules."""
agent = _agent_record("user-1")
await self.storage.upsert_agent("user-1", agent)
session = await self.storage.upsert_session(
user_id="user-1",
agent_id=agent.id,
config=_session_config(),
)
schedule = _schedule_record("user-1", agent.id)
await self.storage.upsert_schedule("user-1", schedule)
self.assertTrue(await self.storage.delete_agent("user-1", agent.id))
self.assertEqual(
await self.storage.list_sessions("user-1", agent.id),
[],
)
self.assertIsNone(
await self.storage.get_session(
"user-1",
agent.id,
session.id,
),
)
self.assertIsNone(
await self.storage.get_schedule("user-1", schedule.id),
)
async def test_upsert_replaces_in_place_and_keeps_created_at(self) -> None:
"""Re-upserting the same id updates via the atomic upsert path.
Exercises the ``ON CONFLICT DO UPDATE`` branch: the second
write must overwrite the mutable columns, preserve the original
``created_at``, and never create a duplicate row.
"""
agent = _agent_record("user-1", "v1")
await self.storage.upsert_agent("user-1", agent)
first = await self.storage.get_agent("user-1", agent.id)
agent.data.name = "v2"
await self.storage.upsert_agent("user-1", agent)
second = await self.storage.get_agent("user-1", agent.id)
self.assertEqual(second.data.name, "v2")
self.assertEqual(second.created_at, first.created_at)
self.assertGreaterEqual(second.updated_at, first.updated_at)
# Exactly one row — the conflict updated rather than inserted.
self.assertEqual(len(await self.storage.list_agents("user-1")), 1)
# ------------------------------------------------------------------
# Sessions
# ------------------------------------------------------------------
async def test_sessions_upsert_and_state_update(self) -> None:
"""Create + update + state-only update."""
agent = _agent_record("user-1")
await self.storage.upsert_agent("user-1", agent)
# Create
session = await self.storage.upsert_session(
user_id="user-1",
agent_id=agent.id,
config=_session_config(),
origin=ScheduleOrigin(schedule_id="sch-1"),
)
self.assertEqual(session.origin, ScheduleOrigin(schedule_id="sch-1"))
# Update (same session_id) — config swap
new_config = _session_config()
new_config.name = "renamed"
updated = await self.storage.upsert_session(
user_id="user-1",
agent_id=agent.id,
config=new_config,
session_id=session.id,
)
self.assertEqual(updated.id, session.id)
self.assertEqual(updated.config.name, "renamed")
self.assertEqual(updated.created_at, session.created_at)
# State-only update
from agentscope.state import AgentState
new_state = AgentState()
await self.storage.update_session_state(
"user-1",
agent.id,
session.id,
new_state,
)
fetched = await self.storage.get_session(
"user-1",
agent.id,
session.id,
)
self.assertEqual(fetched.state.model_dump(), new_state.model_dump())
# By-schedule listing
listed = await self.storage.list_sessions_by_schedule(
"user-1",
"sch-1",
)
self.assertEqual([s.id for s in listed], [session.id])
# set_session_team_id
await self.storage.set_session_team_id(
"user-1",
session.id,
"team-9",
)
fetched = await self.storage.get_session(
"user-1",
agent.id,
session.id,
)
self.assertEqual(fetched.team_id, "team-9")
async def test_update_session_state_missing_raises(self) -> None:
"""Updating an absent session raises :class:`KeyError`."""
from agentscope.state import AgentState
with self.assertRaises(KeyError):
await self.storage.update_session_state(
"user-1",
"agent-x",
"no-such-session",
AgentState(),
)
# ------------------------------------------------------------------
# Messages
# ------------------------------------------------------------------
async def test_messages_upsert_and_pagination(self) -> None:
"""Same-id upsert replaces; distinct ids append; pagination
yields chronological order."""
m1 = UserMsg(name="u", content="hello")
m2 = AssistantMsg(name="a", content="hi")
await self.storage.upsert_message("user-1", "sess-1", m1)
await self.storage.upsert_message("user-1", "sess-1", m2)
# Replace m1 in place (build a fresh Msg with the same id so
# Pydantic re-validates the content list rather than accepting
# a raw-string assignment on the model).
m1_updated = UserMsg(id=m1.id, name="u", content="hola")
await self.storage.upsert_message("user-1", "sess-1", m1_updated)
listed, has_more = await self.storage.list_messages(
"user-1",
"sess-1",
)
self.assertEqual([m.id for m in listed], [m1.id, m2.id])
self.assertFalse(has_more)
self.assertEqual(listed[0].content, m1_updated.content)
fetched = await self.storage.get_message("user-1", "sess-1", m2.id)
self.assertEqual(fetched.id, m2.id)
# Cursor-based pagination: the latest page of one message is the
# newest (m2), with older messages still available.
latest, has_more = await self.storage.list_messages(
"user-1",
"sess-1",
limit=1,
)
self.assertEqual([m.id for m in latest], [m2.id])
self.assertTrue(has_more)
# Walking backwards with ``before`` yields the previous page.
older, has_more = await self.storage.list_messages(
"user-1",
"sess-1",
limit=1,
before=m2.id,
)
self.assertEqual([m.id for m in older], [m1.id])
self.assertFalse(has_more)
# An unknown cursor yields an empty page.
self.assertEqual(
await self.storage.list_messages(
"user-1",
"sess-1",
before="does-not-exist",
),
([], False),
)
# The legacy ``offset`` keyword is ignored but warns.
with self.assertWarns(DeprecationWarning):
await self.storage.list_messages(
"user-1",
"sess-1",
offset=1,
)
async def test_messages_max_width_ids(self) -> None:
"""Composite key stores ids a concatenated key couldn't hold.
Two 64-char ids would be 129 chars once joined as
``session_id:msg_id`` — overflowing the old ``String(96)``
synthetic primary key. The composite ``(session_id, msg_id)``
key round-trips them without truncation.
"""
long_session = "s" * 64
long_msg_id = "m" * 64
msg = UserMsg(id=long_msg_id, name="u", content="hi")
await self.storage.upsert_message("user-1", long_session, msg)
fetched = await self.storage.get_message(
"user-1",
long_session,
long_msg_id,
)
self.assertIsNotNone(fetched)
self.assertEqual(fetched.id, long_msg_id)
listed, _has_more = await self.storage.list_messages(
"user-1",
long_session,
)
self.assertEqual([m.id for m in listed], [long_msg_id])
async def test_delete_message(self) -> None:
"""Deleting by id removes only the matching persisted message."""
first = UserMsg(name="u", content="first")
second = AssistantMsg(name="a", content="second")
await self.storage.upsert_message("user-1", "sess-1", first)
await self.storage.upsert_message("user-1", "sess-1", second)
deleted = await self.storage.delete_message(
"user-1",
"sess-1",
first.id,
)
deleted_again = await self.storage.delete_message(
"user-1",
"sess-1",
first.id,
)
messages, has_more = await self.storage.list_messages(
"user-1",
"sess-1",
)
self.assertDictEqual(
{
"deleted": deleted,
"deleted_again": deleted_again,
"messages": [message.model_dump() for message in messages],
"has_more": has_more,
},
{
"deleted": True,
"deleted_again": False,
"messages": [second.model_dump()],
"has_more": False,
},
)
# ------------------------------------------------------------------
# SOPs
# ------------------------------------------------------------------
async def test_sops_round_trip(self) -> None:
"""Upsert / get / list / delete + owner scoping."""
record = SOPRecord(
user_id="user-1",
data=SOPData(
name="ship",
description="build one",
steps=[
SOPStepDataV1(
subject="model",
description="make the hull",
executor=SOPAgentRef(
agent_id="agent-1",
session_key="modeller",
),
verifier=AgentVerifier(
agent=SOPAgentRef(
agent_id="agent-2",
session_key="reviewer",
),
criteria="Every panel has to be watertight.",
),
),
SOPStepDataV1(
subject="paint",
description="give it a livery",
executor=SOPAgentRef(
agent_id="agent-1",
session_key="modeller",
),
verifier=HumanVerifier(question="Good enough?"),
),
],
),
)
await self.storage.upsert_sop("user-1", record)
fetched = await self.storage.get_sop("user-1", record.id)
self.maxDiff = None
self.assertDictEqual(
fetched.model_dump(mode="json"),
{
"id": AnyString(),
"created_at": AnyString(),
"updated_at": AnyString(),
"user_id": "user-1",
"data": {
"name": "ship",
"description": "build one",
"steps": [
{
"version": "v1",
"subject": "model",
"description": "make the hull",
"executor": {
"agent_id": "agent-1",
"session_key": "modeller",
},
"verifier": {
"type": "agent",
"agent": {
"agent_id": "agent-2",
"session_key": "reviewer",
},
"criteria": (
"Every panel has to be watertight."
),
},
"max_attempts": 3,
},
{
"version": "v1",
"subject": "paint",
"description": "give it a livery",
"executor": {
"agent_id": "agent-1",
"session_key": "modeller",
},
"verifier": {
"type": "human",
"question": "Good enough?",
},
"max_attempts": 3,
},
],
"workspace_grain": "run",
"session_settings": {},
},
},
)
self.assertEqual(
[_.id for _ in await self.storage.list_sops("user-1")],
[record.id],
)
self.assertEqual(await self.storage.list_sops("user-2"), [])
self.assertIsNone(await self.storage.get_sop("user-2", record.id))
self.assertTrue(await self.storage.delete_sop("user-1", record.id))
self.assertFalse(await self.storage.delete_sop("user-1", record.id))
async def test_sop_runs_round_trip(self) -> None:
"""A run stores its snapshot, its sessions and the SDK's state."""
run = SOPRunRecord(
user_id="user-1",
sop_id="sop-1",
definition=SOPData(
name="ship",
steps=[
SOPStepDataV1(
subject="model",
description="make the hull",
executor=SOPAgentRef(
agent_id="agent-1",
session_key="modeller",
),
),
],
),
sessions={"modeller": "session-1"},
state=SOPRunState(steps=[SOPStepRunState()]),
)
await self.storage.upsert_sop_run("user-1", run)
fetched = await self.storage.get_sop_run("user-1", run.id)
self.maxDiff = None
self.assertDictEqual(
fetched.model_dump(mode="json"),
{
"id": AnyString(),
"created_at": AnyString(),
"updated_at": AnyString(),
"user_id": "user-1",
"sop_id": "sop-1",
"definition": {
"name": "ship",
"description": "",
"steps": [
{
"version": "v1",
"subject": "model",
"description": "make the hull",
"executor": {
"agent_id": "agent-1",
"session_key": "modeller",
},
"verifier": None,
"max_attempts": 3,
},
],
"workspace_grain": "run",
"session_settings": {},
},
"sessions": {"modeller": "session-1"},
"state": {
"id": AnyString(),
"inputs": [],
"steps": [
{
"phase": "pending",
"given": [],
"submission": None,
"verifications": [],
},
],
"created_at": AnyString(),
"phase": "pending",
},
},
)
self.assertIsNone(await self.storage.get_sop_run("user-2", run.id))
self.assertTrue(await self.storage.delete_sop_run("user-1", run.id))
self.assertFalse(await self.storage.delete_sop_run("user-1", run.id))
async def test_sop_runs_are_listed_by_procedure_and_phase(self) -> None:
"""``phase`` is a column, so the database does the filtering."""
waiting = SOPRunRecord(
user_id="user-1",
sop_id="sop-1",
definition=SOPData(name="a", steps=[_A_STEP]),
state=SOPRunState(
steps=[SOPStepRunState(phase=SOPPhase.AWAITING)],
),
)
done = SOPRunRecord(
user_id="user-1",
sop_id="sop-1",
definition=SOPData(name="a", steps=[_A_STEP]),
state=SOPRunState(
steps=[SOPStepRunState(phase=SOPPhase.COMPLETED)],
),
)
other = SOPRunRecord(
user_id="user-1",
sop_id="sop-2",
definition=SOPData(name="b", steps=[_A_STEP]),
state=SOPRunState(
steps=[SOPStepRunState(phase=SOPPhase.AWAITING)],
),
)
for record in (waiting, done, other):
await self.storage.upsert_sop_run("user-1", record)
self.assertEqual(
{_.id for _ in await self.storage.list_sop_runs("user-1")},
{waiting.id, done.id, other.id},
)
self.assertEqual(
{
_.id
for _ in await self.storage.list_sop_runs(
"user-1",
sop_id="sop-1",
)
},
{waiting.id, done.id},
)
self.assertEqual(
{
_.id
for _ in await self.storage.list_sop_runs(
"user-1",
phase=SOPPhase.AWAITING,
)
},
{waiting.id, other.id},
)
self.assertEqual(await self.storage.list_sop_runs("user-2"), [])
async def test_update_sop_run_writes_the_run_and_not_the_snapshot(
self,
) -> None:
"""The hot path rewrites state and sessions, and keeps the rest."""
run = SOPRunRecord(
user_id="user-1",
sop_id="sop-1",
definition=SOPData(name="ship", steps=[_A_STEP]),
state=SOPRunState(steps=[SOPStepRunState()]),
)
await self.storage.upsert_sop_run("user-1", run)
await self.storage.update_sop_run(
"user-1",
run.id,
SOPRunState(
id=run.state.id,
steps=[SOPStepRunState(phase=SOPPhase.AWAITING)],
),
sessions={"modeller": "session-1"},
)
updated = await self.storage.get_sop_run("user-1", run.id)
self.maxDiff = None
self.assertDictEqual(
updated.model_dump(mode="json"),
{
"id": run.id,
"created_at": AnyString(),
"updated_at": AnyString(),
"user_id": "user-1",
"sop_id": "sop-1",
"definition": {
"name": "ship",
"description": "",
"steps": [
{
"version": "v1",
"subject": "model",
"description": "make the hull",
"executor": {
"agent_id": "a-1",
"session_key": "modeller",
},
"verifier": None,
"max_attempts": 3,
},
],
"workspace_grain": "run",
"session_settings": {},
},
"sessions": {"modeller": "session-1"},
"state": {
"id": AnyString(),
"inputs": [],
"steps": [
{
"phase": "awaiting",
"given": [],
"submission": None,
"verifications": [],
},
],
"created_at": AnyString(),
"phase": "awaiting",
},
},
)
# The promoted column moved with it, so the filter still finds it.
self.assertEqual(
[
_.id
for _ in await self.storage.list_sop_runs(
"user-1",
phase=SOPPhase.AWAITING,
)
],
[run.id],
)
with self.assertRaises(KeyError):
await self.storage.update_sop_run(
"user-1",
"nope",
SOPRunState(),
)
with self.assertRaises(KeyError):
await self.storage.update_sop_run(
"user-2",
run.id,
SOPRunState(),
)
async def test_deleting_a_sop_takes_its_runs_with_it(self) -> None:
"""A run is only readable through the definition it snapshotted."""
sop = SOPRecord(
user_id="user-1",
data=SOPData(name="ship", steps=[_A_STEP]),
)
await self.storage.upsert_sop("user-1", sop)
run = SOPRunRecord(
user_id="user-1",
sop_id=sop.id,
definition=sop.data,
)
await self.storage.upsert_sop_run("user-1", run)
self.assertTrue(await self.storage.delete_sop("user-1", sop.id))
self.assertIsNone(await self.storage.get_sop_run("user-1", run.id))
self.assertEqual(await self.storage.list_sop_runs("user-1"), [])
async def test_a_step_state_subclass_survives_the_database(self) -> None:
"""What a step kept beyond the base record is stored, not trimmed."""
run = SOPRunRecord(
user_id="user-1",
sop_id="sop-1",
definition=SOPData(name="ship", steps=[_A_STEP]),
state=SOPRunState(
steps=[SOPStepRunState(phase=SOPPhase.AWAITING, call_id="c1")],
),
)
await self.storage.upsert_sop_run("user-1", run)
fetched = await self.storage.get_sop_run("user-1", run.id)
self.assertDictEqual(
fetched.state.steps[0].model_dump(mode="json"),
{
"phase": "awaiting",
"given": [],
"submission": None,
"verifications": [],
"call_id": "c1",
},
)
# ------------------------------------------------------------------
# Teams
# ------------------------------------------------------------------
async def test_teams_upsert_get_list_delete(self) -> None:
"""Team cascade: created-role member is fully deleted, invited-role
keeps their agent."""
# Two agents — one that will be "created" for the team,
# another that will be "invited".
created_agent = _agent_record("user-1", "created")
created_agent.source = "team"
invited_owner = "user-2"
invited_agent = _agent_record(invited_owner, "invited")
await self.storage.upsert_agent("user-1", created_agent)
await self.storage.upsert_agent(invited_owner, invited_agent)
# Sessions for both, plus the leader session.
leader = await self.storage.upsert_session(
user_id="user-1",
agent_id=created_agent.id,
config=_session_config(),
)
created_session = await self.storage.upsert_session(
user_id="user-1",
agent_id=created_agent.id,
config=_session_config(),
)
invited_session = await self.storage.upsert_session(
user_id="user-1",
agent_id=invited_agent.id,
config=_session_config(),
)
surviving_session = await self.storage.upsert_session(
user_id=invited_owner,
agent_id=invited_agent.id,
config=_session_config(),
)
team = TeamRecord(
user_id="user-1",
session_id=leader.id,
data=TeamData(
name="team",
members=[
TeamMember(
owner_id="user-1",
agent_id=created_agent.id,
session_id=created_session.id,
role="created",
),
TeamMember(
owner_id=invited_owner,
agent_id=invited_agent.id,
session_id=invited_session.id,
role="invited",
),
],
),
)
await self.storage.upsert_team("user-1", team)
self.assertEqual(
[t.id for t in await self.storage.list_teams("user-1")],
[team.id],
)
# Delete team.
self.assertTrue(await self.storage.delete_team("user-1", team.id))
self.assertIsNone(await self.storage.get_team("user-1", team.id))
# Created member: agent + session are gone.
self.assertIsNone(
await self.storage.get_agent("user-1", created_agent.id),
)
# Invited member: agent survives, only the invited session is gone.
self.assertIsNotNone(
await self.storage.get_agent(invited_owner, invited_agent.id),
)
self.assertIsNone(
await self.storage.get_session(
"user-1",
invited_agent.id,
invited_session.id,
),
)
self.assertIsNotNone(
await self.storage.get_session(
invited_owner,
invited_agent.id,
surviving_session.id,
),
)
# ------------------------------------------------------------------
# Knowledge base + documents + lease CAS
# ------------------------------------------------------------------
async def test_kb_and_documents_round_trip(self) -> None:
"""KB CRUD; document CRUD; cascading KB delete."""
kb = _kb_record("user-1")
await self.storage.upsert_knowledge_base("user-1", kb)
self.assertEqual(
[k.id for k in await self.storage.list_knowledge_bases("user-1")],
[kb.id],
)
doc_a = _kd_record("user-1", kb.id, "a.txt")
doc_b = _kd_record("user-1", kb.id, "b.txt")
await self.storage.upsert_knowledge_document("user-1", doc_a)
await self.storage.upsert_knowledge_document("user-1", doc_b)
listed = await self.storage.list_knowledge_documents("user-1", kb.id)
self.assertEqual(
sorted(d.id for d in listed),
sorted([doc_a.id, doc_b.id]),
)
# Cascade delete removes documents.
self.assertTrue(
await self.storage.delete_knowledge_base("user-1", kb.id),
)
self.assertEqual(
await self.storage.list_knowledge_documents("user-1", kb.id),
[],
)
async def test_document_kb_foreign_key_is_enforced(self) -> None:
"""The document→KB FK is live (PRAGMA on): cascade + rejection.
``delete_knowledge_base`` no longer deletes documents in Python
— it relies on ``ON DELETE CASCADE``. This only passes if SQLite
foreign-key enforcement is actually enabled, so the test doubles
as a guard that the ``PRAGMA foreign_keys=ON`` wiring works.
"""
from sqlalchemy.exc import IntegrityError
kb = _kb_record("user-1")
await self.storage.upsert_knowledge_base("user-1", kb)
doc = _kd_record("user-1", kb.id)
await self.storage.upsert_knowledge_document("user-1", doc)
# Native cascade: dropping the KB row removes the document row.
await self.storage.delete_knowledge_base("user-1", kb.id)
self.assertEqual(
await self.storage.list_knowledge_documents("user-1", kb.id),
[],
)
# Enforcement: a document pointing at a missing KB is rejected.
orphan = _kd_record("user-1", "no-such-kb")
with self.assertRaises(IntegrityError):
await self.storage.upsert_knowledge_document("user-1", orphan)
async def test_document_status_and_lease_cas(self) -> None:
"""``update_knowledge_document_status`` + lease CAS semantics."""
kb = _kb_record("user-1")
await self.storage.upsert_knowledge_base("user-1", kb)
doc = _kd_record("user-1", kb.id)
await self.storage.upsert_knowledge_document("user-1", doc)
# Status transition + error/chunk_count payload update
await self.storage.update_knowledge_document_status(
"user-1",
kb.id,
doc.id,
"error",
error="boom",
chunk_count=0,
)
fetched = await self.storage.get_knowledge_document(
"user-1",
kb.id,
doc.id,
)
self.assertEqual(fetched.status, "error")
self.assertEqual(fetched.data.error, "boom")
# Reset to pending for the lease dance.
await self.storage.update_knowledge_document_status(
"user-1",
kb.id,
doc.id,
"pending",
)
now = datetime.now()
ttl = timedelta(minutes=5)
# First worker wins.
self.assertTrue(
await self.storage.acquire_knowledge_document_lease(
"user-1",
kb.id,
doc.id,
"worker-A",
ttl,
now,
),
)
# Second worker loses because the lease is fresh.
self.assertFalse(
await self.storage.acquire_knowledge_document_lease(
"user-1",
kb.id,
doc.id,
"worker-B",
ttl,
now,
),
)
# Renew from the holder works, from a stranger fails.
self.assertTrue(
await self.storage.renew_knowledge_document_lease(
"user-1",
kb.id,
doc.id,
"worker-A",
ttl,
now,
),
)
self.assertFalse(
await self.storage.renew_knowledge_document_lease(
"user-1",
kb.id,
doc.id,
"worker-B",
ttl,
now,
),
)
# Release from a stranger is a no-op; from the holder clears it.
await self.storage.release_knowledge_document_lease(
"user-1",
kb.id,
doc.id,
"worker-B",
)
fetched = await self.storage.get_knowledge_document(
"user-1",
kb.id,
doc.id,
)
self.assertEqual(fetched.processing_node, "worker-A")
await self.storage.release_knowledge_document_lease(
"user-1",
kb.id,
doc.id,
"worker-A",
)
fetched = await self.storage.get_knowledge_document(
"user-1",
kb.id,
doc.id,
)
self.assertIsNone(fetched.processing_node)
self.assertIsNone(fetched.lease_expires_at)
async def test_upsert_leased_document(self) -> None:
"""A leased document read back from storage can be upserted again."""
kb = _kb_record("user-1")
await self.storage.upsert_knowledge_base("user-1", kb)
doc = _kd_record("user-1", kb.id)
await self.storage.upsert_knowledge_document("user-1", doc)
await self.storage.acquire_knowledge_document_lease(
"user-1",
kb.id,
doc.id,
"worker-A",
timedelta(minutes=5),
datetime(2026, 1, 1, 12, 0, tzinfo=timezone.utc),
)
leased = await self.storage.get_knowledge_document(
"user-1",
kb.id,
doc.id,
)
leased.data.chunk_count = 3
await self.storage.upsert_knowledge_document("user-1", leased)
fetched = await self.storage.get_knowledge_document(
"user-1",
kb.id,
doc.id,
)
self.assertDictEqual(
fetched.model_dump(mode="json"),
{
"id": doc.id,
"created_at": AnyString(),
"updated_at": AnyString(),
"user_id": "user-1",
"knowledge_base_id": kb.id,
"processing_node": "worker-A",
"status": "pending",
"lease_expires_at": "2026-01-01T12:05:00",
"data": {
"filename": "f.txt",
"size": 42,
"content_type": None,
"blob_uri": "local://f.txt",
"error": None,
"chunk_count": 3,
},
},
)
async def test_expired_lease_and_pending_sweep(self) -> None:
"""``list_..._with_expired_lease`` + ``..._pending_since`` filters."""
kb = _kb_record("user-1")
await self.storage.upsert_knowledge_base("user-1", kb)
# Doc 1: expired lease, non-terminal → should show up
d1 = _kd_record("user-1", kb.id, "d1.txt")
await self.storage.upsert_knowledge_document("user-1", d1)
now = datetime.now()
await self.storage.acquire_knowledge_document_lease(
"user-1",
kb.id,
d1.id,
"worker-A",
timedelta(seconds=1),
now - timedelta(hours=1), # ancient
)
# Doc 2: still pending, no lease → not caught by expired filter
d2 = _kd_record("user-1", kb.id, "d2.txt")
await self.storage.upsert_knowledge_document("user-1", d2)
# Doc 3: terminal → never returned
d3 = _kd_record("user-1", kb.id, "d3.txt")
await self.storage.upsert_knowledge_document("user-1", d3)
await self.storage.acquire_knowledge_document_lease(
"user-1",
kb.id,
d3.id,
"worker-B",
timedelta(seconds=1),
now - timedelta(hours=1),
)
await self.storage.update_knowledge_document_status(
"user-1",
kb.id,
d3.id,
"ready",
)
expired = (
await self.storage.list_knowledge_documents_with_expired_lease(
now,
)
)
self.assertEqual([d.id for d in expired], [d1.id])
pending = await self.storage.list_knowledge_documents_pending_since(
now + timedelta(minutes=1),
)
# d1 and d2 are both still 'pending' (acquiring a lease does
# not transition status by itself); d3 is 'ready' so excluded.
self.assertEqual(
sorted(d.id for d in pending),
sorted([d1.id, d2.id]),
)
# ------------------------------------------------------------------
# Schedules
# ------------------------------------------------------------------
async def test_schedules_round_trip(self) -> None:
"""Basic schedule CRUD + list_all across users."""
s1 = _schedule_record("user-1", "agent-1")
s2 = _schedule_record("user-2", "agent-2")
await self.storage.upsert_schedule("user-1", s1)
await self.storage.upsert_schedule("user-2", s2)
self.assertEqual(
sorted(x.id for x in await self.storage.list_schedules("user-1")),
[s1.id],
)
self.assertEqual(
sorted(x.id for x in await self.storage.list_all_schedules()),
sorted([s1.id, s2.id]),
)
self.assertTrue(
await self.storage.delete_schedule("user-1", s1.id),
)
self.assertFalse(
await self.storage.delete_schedule("user-1", s1.id),
)
# ------------------------------------------------------------------
# Installed MCPs and skills
# ------------------------------------------------------------------
async def test_mcps_round_trip(self) -> None:
"""Upsert / get / get-by-name / list / delete + owner scoping."""
record = _mcp_record("user-1")
mcp_id = await self.storage.upsert_mcp("user-1", record)
fetched = await self.storage.get_mcp("user-1", mcp_id)
self.assertEqual(fetched.client.mcp_config.type, "http_mcp")
self.assertEqual(fetched.name, "deepwiki")
by_name = await self.storage.get_mcp_by_name("user-1", "deepwiki")
self.assertEqual(by_name.id, mcp_id)
self.assertIsNone(
await self.storage.get_mcp_by_name("user-1", "nope"),
)
self.assertEqual(
[m.id for m in await self.storage.list_mcps("user-1")],
[mcp_id],
)
self.assertEqual(await self.storage.list_mcps("user-2"), [])
self.assertIsNone(await self.storage.get_mcp("user-2", mcp_id))
self.assertTrue(await self.storage.delete_mcp("user-1", mcp_id))
self.assertFalse(await self.storage.delete_mcp("user-1", mcp_id))
async def test_mcp_name_is_unique_per_user(self) -> None:
"""A second record may not claim a name, but another user may."""
await self.storage.upsert_mcp("user-1", _mcp_record("user-1"))
with self.assertRaises(ValueError):
await self.storage.upsert_mcp("user-1", _mcp_record("user-1"))
other = await self.storage.upsert_mcp("user-2", _mcp_record("user-2"))
self.assertIsNotNone(await self.storage.get_mcp("user-2", other))
async def test_mcp_rename_frees_the_old_name(self) -> None:
"""Re-upserting the same record renames rather than clashing."""
record = _mcp_record("user-1")
await self.storage.upsert_mcp("user-1", record)
record.client.name = "renamed"
await self.storage.upsert_mcp("user-1", record)
self.assertIsNone(
await self.storage.get_mcp_by_name("user-1", "deepwiki"),
)
renamed = await self.storage.get_mcp_by_name("user-1", "renamed")
self.assertEqual(renamed.id, record.id)
async def test_skills_round_trip(self) -> None:
"""Upsert / get / get-by-name / list / delete + owner scoping."""
record = SkillRecord(user_id="user-1", name="gifgrep")
skill_id = await self.storage.upsert_skill("user-1", record)
fetched = await self.storage.get_skill("user-1", skill_id)
self.assertEqual(fetched.name, "gifgrep")
by_name = await self.storage.get_skill_by_name("user-1", "gifgrep")
self.assertEqual(by_name.id, skill_id)
self.assertEqual(
[s.id for s in await self.storage.list_skills("user-1")],
[skill_id],
)
self.assertEqual(await self.storage.list_skills("user-2"), [])
self.assertIsNone(await self.storage.get_skill("user-2", skill_id))
self.assertTrue(await self.storage.delete_skill("user-1", skill_id))
self.assertFalse(await self.storage.delete_skill("user-1", skill_id))
async def test_skill_name_is_unique_per_user(self) -> None:
"""Mirrors the MCP rule; the two libraries are independent."""
await self.storage.upsert_skill(
"user-1",
SkillRecord(user_id="user-1", name="shared"),
)
with self.assertRaises(ValueError):
await self.storage.upsert_skill(
"user-1",
SkillRecord(user_id="user-1", name="shared"),
)
# The same name on the MCP side is a different table entirely.
await self.storage.upsert_mcp(
"user-1",
_mcp_record("user-1", name="shared"),
)
self.assertIsNotNone(
await self.storage.get_mcp_by_name("user-1", "shared"),
)
# ------------------------------------------------------------------
# Channels
# ------------------------------------------------------------------
async def test_channels_round_trip(self) -> None:
"""Upsert / get / list / list-all / delete + the bot-id lookup."""
record = ChannelRecord(
id="chan-1",
channel_type="feishu",
name="产品群机器人",
user_id="user-1",
credentials={"app_id": "cli-1", "app_secret": "s3cret"},
platform_config={"only_at_reply": True},
routing=RoutingConfig(
bindings=[
ChannelBinding(match_value="*", agent_id="agent-x"),
],
),
session=SessionSettings(
chat_model_config={
"type": "openai",
"credential_id": "cred-1",
"model": "gpt-4o",
"parameters": {},
},
),
)
await self.storage.upsert_channel(record, "cli-1")
fetched = await self.storage.get_channel("chan-1")
self.assertDictEqual(
fetched.model_dump(mode="json"),
{
"id": "chan-1",
"channel_type": "feishu",
"name": "产品群机器人",
"user_id": "user-1",
"enabled": True,
"credentials": {"app_id": "cli-1", "app_secret": "s3cret"},
"platform_config": {"only_at_reply": True},
"routing": {
"bindings": [
{
"match_key": "chat_id",
"match_value": "*",
"agent_id": "agent-x",
"session_scope": "per_chat",
},
],
},
"session": {
"chat_model_config": {
"type": "openai",
"credential_id": "cred-1",
"model": "gpt-4o",
"parameters": {},
},
"fallback_chat_model_config": None,
"permission_mode": "default",
},
"created_at": AnyString(),
"updated_at": AnyString(),
},
)
self.assertListEqual(
[c.id for c in await self.storage.list_channels("user-1")],
["chan-1"],
)
self.assertListEqual(await self.storage.list_channels("user-2"), [])
self.assertListEqual(
[c.id for c in await self.storage.list_all_channels()],
["chan-1"],
)
self.assertEqual(
await self.storage.get_channel_id_by_platform_bot_id("cli-1"),
"chan-1",
)
self.assertIsNone(
await self.storage.get_channel_id_by_platform_bot_id("nope"),
)
self.assertTrue(await self.storage.delete_channel("chan-1", "cli-1"))
self.assertFalse(await self.storage.delete_channel("chan-1", "cli-1"))
self.assertIsNone(await self.storage.get_channel("chan-1"))
self.assertIsNone(
await self.storage.get_channel_id_by_platform_bot_id("cli-1"),
)
async def test_platform_bot_id_is_globally_unique(self) -> None:
"""A second channel may not claim a bot already bound elsewhere,
even under a different owner.
Rejected before the write rather than by the UNIQUE constraint:
MySQL's ``ON DUPLICATE KEY UPDATE`` fires on any unique-key
conflict, so leaving it to the constraint would overwrite the
holder there while raising on SQLite and Postgres.
"""
await self.storage.upsert_channel(_channel_record("chan-1"), "cli-1")
with self.assertRaises(ValueError):
await self.storage.upsert_channel(
_channel_record("chan-2", user_id="user-2"),
"cli-1",
)
held = await self.storage.get_channel("chan-1")
self.assertEqual(held.user_id, "user-1")
self.assertIsNone(await self.storage.get_channel("chan-2"))
async def test_rebinding_a_channel_frees_the_old_bot_id(self) -> None:
"""Re-upserting the same channel under a new bot id moves the
uniqueness claim with it."""
record = _channel_record("chan-1")
await self.storage.upsert_channel(record, "cli-1")
await self.storage.upsert_channel(record, "cli-2")
self.assertIsNone(
await self.storage.get_channel_id_by_platform_bot_id("cli-1"),
)
self.assertEqual(
await self.storage.get_channel_id_by_platform_bot_id("cli-2"),
"chan-1",
)
class AsyncSQLAlchemyStorageAutoMigrateTest(IsolatedAsyncioTestCase):
"""Boot via ``auto_migrate=True`` and confirm the schema is live.
Uses a file-backed SQLite database (not ``:memory:``) so the
Alembic-driven ``upgrade head`` run inside
:meth:`AsyncSQLAlchemyStorage.__aenter__`
and the subsequent record-write use the same physical database —
``:memory:`` gives a private DB per connection, which would let
Alembic build tables that the storage's engine can't see.
"""
async def test_auto_migrate_creates_schema(self) -> None:
"""After ``auto_migrate=True`` the tables exist and CRUD works."""
import os
import tempfile
with tempfile.TemporaryDirectory() as tmp:
db_path = os.path.join(tmp, "as.db")
url = f"sqlite+aiosqlite:///{db_path}"
async with AsyncSQLAlchemyStorage(
url,
auto_migrate=True,
) as storage:
agent = _agent_record("user-1")
await storage.upsert_agent("user-1", agent)
fetched = await storage.get_agent("user-1", agent.id)
self.assertEqual(fetched.id, agent.id)
async def test_migrations_alone_create_the_channels_table(self) -> None:
"""``create_tables=False`` isolates the Alembic path, so a broken
or missing 0003 fails here instead of being masked by
``metadata.create_all``."""
import os
import tempfile
with tempfile.TemporaryDirectory() as tmp:
url = f"sqlite+aiosqlite:///{os.path.join(tmp, 'as.db')}"
async with AsyncSQLAlchemyStorage(
url,
auto_migrate=True,
create_tables=False,
) as storage:
await storage.upsert_channel(
_channel_record("chan-1"),
"cli-1",
)
self.assertEqual(
await storage.get_channel_id_by_platform_bot_id("cli-1"),
"chan-1",
)
async def test_migrations_alone_create_the_sop_tables(self) -> None:
"""Same isolation for 0004 — without ``metadata.create_all`` a
missing migration surfaces here rather than in production."""
import os
import tempfile
with tempfile.TemporaryDirectory() as tmp:
url = f"sqlite+aiosqlite:///{os.path.join(tmp, 'as.db')}"
async with AsyncSQLAlchemyStorage(
url,
auto_migrate=True,
create_tables=False,
) as storage:
sop = SOPRecord(
user_id="user-1",
data=SOPData(name="ship", steps=[_A_STEP]),
)
await storage.upsert_sop("user-1", sop)
await storage.upsert_sop_run(
"user-1",
SOPRunRecord(
user_id="user-1",
sop_id=sop.id,
definition=sop.data,
state=SOPRunState(
steps=[SOPStepRunState(phase=SOPPhase.AWAITING)],
),
),
)
self.assertEqual(
len(
await storage.list_sop_runs(
"user-1",
phase=SOPPhase.AWAITING,
),
),
1,
)
class ChannelTimestampPrecisionTest(TestCase):
"""``channels.updated_at`` is the channel's configuration version.
The client cache and the dispatcher compare it for equality, so a
MySQL ``DATETIME`` rounded to whole seconds would let two edits one
second apart share a version and leave the second one unapplied.
"""
def test_mysql_keeps_microseconds(self) -> None:
"""Both timestamps carry ``fsp=6`` on MySQL, and the migration
that creates the table agrees with the ORM metadata."""
migration = import_module(
"agentscope.app.storage._sql._alembic.versions.0003_channels",
)
dialect = mysql.dialect()
self.assertListEqual(
[
type_.compile(dialect)
for type_ in (
ChannelRow.__table__.c.created_at.type,
ChannelRow.__table__.c.updated_at.type,
migration.VERSION_TIMESTAMP,
)
],
["DATETIME(6)", "DATETIME(6)", "DATETIME(6)"],
)
class LegacyRecordShapeTest(IsolatedAsyncioTestCase):
"""The ``mode='before'`` validators absorb pre-refactor payloads.
Records written before the KB-``data`` nesting / KD-lifecycle
promotion refactor must still deserialise, so a database populated
by an older build keeps round-tripping after an upgrade.
"""
async def test_knowledge_base_flat_payload_migrates(self) -> None:
"""Flat KB fields fold into :attr:`KnowledgeBaseRecord.data`."""
record = KnowledgeBaseRecord.model_validate(
{
"user_id": "u1",
"name": "kb",
"description": "d",
"embedding_model_config": {
"type": "openai_credential",
"credential_id": "cred-1",
"model": "text-embedding-3-small",
"dimensions": 8,
},
"collection_name": "c",
},
)
self.assertEqual(record.data.name, "kb")
self.assertEqual(record.data.collection_name, "c")
async def test_knowledge_document_lifecycle_fields_lift(self) -> None:
"""Legacy in-``data`` ``status`` / ``lease_expires_at`` lift up."""
record = KnowledgeDocumentRecord.model_validate(
{
"user_id": "u1",
"knowledge_base_id": "kb1",
"data": {
"filename": "f.txt",
"size": 1,
"blob_uri": "local://f.txt",
"status": "ready",
"lease_expires_at": None,
},
},
)
self.assertEqual(record.status, "ready")
# The fields moved to the top level and no longer shadow ``data``.
self.assertNotIn("status", record.data.model_dump())
async def test_mcp_name_is_derived_not_read(self) -> None:
"""``MCPRecord.name`` comes from the client, so a payload written
before the field existed — or one carrying a stale value — still
yields the right name."""
for stored_name in (None, "stale"):
payload: dict = {
"user_id": "u1",
"client": {
"name": "deepwiki",
"is_stateful": False,
"mcp_config": {
"type": "http_mcp",
"url": "https://mcp.deepwiki.com/mcp",
},
},
}
if stored_name is not None:
payload["name"] = stored_name
self.assertEqual(
MCPRecord.model_validate(payload).name,
"deepwiki",
)
class SessionOriginLegacyTest(IsolatedAsyncioTestCase):
"""Rows written before :data:`SessionOrigin` existed still read back.
Those carry a bare ``source`` string, and — this is the part a
round-trip through the new code never exercises — their ids live in
promoted columns rather than in the payload.
"""
def _legacy_row(self, **columns: object) -> SessionRow:
"""A row in the shape the old mapper wrote."""
now = datetime.now()
return SessionRow(
id="sess-legacy",
created_at=now,
updated_at=now,
user_id="user-1",
agent_id="agent-1",
team_id=None,
payload={"config": {"name": "n", "workspace_id": "ws-1"}},
**columns,
)
async def test_legacy_schedule_row_keeps_its_schedule_id(self) -> None:
"""The id is in a column, not the payload, and must survive."""
record = _to_record(
self._legacy_row(source="schedule", source_schedule_id="sch-1"),
SessionRecord,
)
self.assertEqual(record.origin, ScheduleOrigin(schedule_id="sch-1"))
async def test_legacy_channel_row_keeps_its_ids(self) -> None:
"""Channel ids were in the payload already, flat rather than nested."""
row = self._legacy_row(source="channel", source_schedule_id=None)
row.payload = {
**row.payload,
"source_channel_id": "chan-1",
"source_chat_id": "chat-1",
"source_chat_name": "产品群",
}
self.assertEqual(
_to_record(row, SessionRecord).origin,
ChannelOrigin(
channel_id="chan-1",
chat_id="chat-1",
chat_name="产品群",
),
)
async def test_legacy_user_row_reads_as_user(self) -> None:
"""The plain case, where every id column is empty."""
record = _to_record(
self._legacy_row(source="user", source_schedule_id=None),
SessionRecord,
)
self.assertEqual(record.origin, UserOrigin())
async def test_an_origin_without_its_ids_is_not_that_origin(self) -> None:
"""A tag carries its ids, so a row missing them cannot claim one.
The old shape let ``source`` say ``channel`` while the ids were
``None``, and every caller wrote a truthiness guard because of
it. Manufacturing blank ids would walk such a row straight past
those guards — and index it under the empty string.
"""
self.assertEqual(
_to_record(
self._legacy_row(source="schedule", source_schedule_id=None),
SessionRecord,
).origin,
UserOrigin(),
)
self.assertEqual(
_to_record(
self._legacy_row(source="channel", source_schedule_id=None),
SessionRecord,
).origin,
UserOrigin(),
)
class ChannelSessionLookupTest(IsolatedAsyncioTestCase):
"""``list_sessions_by_channel`` reads both payload shapes.
The nested one is what the union writes; the flat one is every row
written before it. Matching both is what lets this ship without a
backfill, and it is dialect-sensitive JSON access, so it is worth
running rather than reasoning about.
"""
async def asyncSetUp(self) -> None:
"""Open a storage on an in-memory database."""
self._stack = AsyncExitStack()
self.storage = await self._stack.enter_async_context(
AsyncSQLAlchemyStorage(url="sqlite+aiosqlite:///:memory:"),
)
async def asyncTearDown(self) -> None:
"""Close it."""
await self._stack.aclose()
async def test_both_shapes_come_back(self) -> None:
"""One session written each way, both found by their channel."""
await self.storage.upsert_session(
user_id="user-1",
agent_id="agent-1",
config=SessionConfig(workspace_id="ws-1"),
origin=ChannelOrigin(channel_id="chan-1", chat_id="chat-new"),
)
# A row as the old code wrote it: no ``origin``, ids flat in the
# payload. Written through the row layer so the record's own
# validator cannot normalise it on the way in.
now = datetime.now()
# pylint: disable=protected-access
async with self.storage._session() as sess:
sess.add(
SessionRow(
id="sess-legacy",
created_at=now,
updated_at=now,
user_id="user-1",
agent_id="agent-1",
team_id=None,
source="channel",
source_schedule_id=None,
payload={
"config": {"workspace_id": "ws-1"},
"source_channel_id": "chan-1",
"source_chat_id": "chat-old",
},
),
)
await sess.commit()
found = await self.storage.list_sessions_by_channel("user-1", "chan-1")
self.assertListEqual(
sorted(_.origin.chat_id for _ in found),
["chat-new", "chat-old"],
)
async def test_a_legacy_call_still_builds_the_right_origin(self) -> None:
"""The flat arguments ``upsert_session`` used to take still work."""
with self.assertWarns(DeprecationWarning):
record = await self.storage.upsert_session(
user_id="user-1",
agent_id="agent-1",
config=SessionConfig(workspace_id="ws-1"),
source="channel",
source_channel_id="chan-1",
source_chat_id="chat-1",
source_chat_name="产品群",
)
self.assertEqual(
record.origin,
ChannelOrigin(
channel_id="chan-1",
chat_id="chat-1",
chat_name="产品群",
),
)
class VoiceProfileSQLTest(IsolatedAsyncioTestCase):
"""Migration-only CRUD and ownership guarantees on a real database."""
async def asyncSetUp(self) -> None:
"""Use a file database shared by Alembic and concurrent sessions."""
directory = TemporaryDirectory(prefix="agentscope-voice-%sql-")
self.addCleanup(directory.cleanup)
self.url = URL.create(
"sqlite+aiosqlite",
database=str(Path(directory.name) / "voice.db"),
).render_as_string(hide_password=False)
alembic_dir = Path(sql_storage.__file__).parent / "_alembic"
self.config = Config(str(alembic_dir / "alembic.ini"))
self.config.set_main_option("script_location", str(alembic_dir))
self.config.set_main_option(
"sqlalchemy.url",
self.url.replace("%", "%%"),
)
async def test_migrations_accept_url_from_environment(self) -> None:
"""Environment URLs with percent signs reach the intended database."""
self.config.set_main_option("sqlalchemy.url", "")
with patch.dict(environ, {"AGENTSCOPE_SQL_URL": self.url}):
await asyncio.to_thread(command.upgrade, self.config, "head")
self.assertEqual(
self.config.get_main_option("sqlalchemy.url"),
self.url,
)
async with AsyncSQLAlchemyStorage(
self.url,
create_tables=False,
) as storage:
record = VoiceProfileRecord(
user_id="owner",
data=VoiceProfileData(name="Environment URL voice"),
)
await storage.upsert_voice_profile("owner", record)
self.assertEqual(
(
await storage.get_voice_profile("owner", record.id)
).model_dump(),
record.model_dump(),
)
async def test_migrations_alone_support_voice_profile_crud(self) -> None:
"""The migration supplies the entire table, indexes and CRUD path."""
async with AsyncSQLAlchemyStorage(
self.url,
auto_migrate=True,
create_tables=False,
) as storage:
async with storage._engine.connect() as connection:
indexes = await connection.run_sync(
lambda conn: sa.inspect(conn).get_indexes(
"voice_profiles",
),
)
self.assertEqual(
{item["name"]: item["column_names"] for item in indexes},
{
"ix_voice_profiles_created_at": ["created_at"],
"ix_voice_profiles_updated_at": ["updated_at"],
"ix_voice_profiles_user_id": ["user_id"],
},
)
record = VoiceProfileRecord(
user_id="owner",
data=VoiceProfileData(name="Voice"),
)
self.assertEqual(
await storage.upsert_voice_profile("owner", record),
record.id,
)
self.assertEqual(
(
await storage.get_voice_profile("owner", record.id)
).model_dump(),
record.model_dump(),
)
self.assertEqual(
[
item.model_dump()
for item in await storage.list_voice_profiles("owner")
],
[record.model_dump()],
)
self.assertIsNone(
await storage.get_voice_profile("other", record.id),
)
self.assertEqual(await storage.list_voice_profiles("other"), [])
self.assertFalse(
await storage.delete_voice_profile("other", record.id),
)
original_created = record.created_at
record.data.name = "Updated voice"
record.created_at = datetime(2000, 1, 1)
await storage.upsert_voice_profile("owner", record)
self.assertEqual(record.created_at, original_created)
self.assertEqual(
(
await storage.get_voice_profile("owner", record.id)
).model_dump(),
record.model_dump(),
)
with self.assertRaises(PermissionError):
await storage.upsert_voice_profile(
"other",
VoiceProfileRecord(
id=record.id,
user_id="other",
data=VoiceProfileData(name="Not the owner"),
),
)
self.assertEqual(
(
await storage.get_voice_profile("owner", record.id)
).model_dump(),
record.model_dump(),
)
self.assertTrue(
await storage.delete_voice_profile("owner", record.id),
)
self.assertFalse(
await storage.delete_voice_profile("owner", record.id),
)
self.assertEqual(await storage.list_voice_profiles("owner"), [])
async def test_upgrade_from_0004_and_downgrade(self) -> None:
"""Upgrade an existing schema without touching its stored records."""
await asyncio.to_thread(command.upgrade, self.config, "0004_sops")
async with AsyncSQLAlchemyStorage(
self.url,
create_tables=False,
) as storage:
agent = _agent_record("owner")
await storage.upsert_agent("owner", agent)
before = agent.model_dump()
async with AsyncSQLAlchemyStorage(
self.url,
auto_migrate=True,
create_tables=False,
) as storage:
profile = VoiceProfileRecord(
user_id="owner",
data=VoiceProfileData(name="Migrated voice"),
)
await storage.upsert_voice_profile("owner", profile)
self.assertEqual(
(
await storage.get_voice_profile("owner", profile.id)
).model_dump(),
profile.model_dump(),
)
self.assertEqual(
(await storage.get_agent("owner", agent.id)).model_dump(),
before,
)
await asyncio.to_thread(command.downgrade, self.config, "0004_sops")
async with AsyncSQLAlchemyStorage(
self.url,
create_tables=False,
) as storage:
async with storage._engine.connect() as connection:
exists = await connection.run_sync(
lambda conn: sa.inspect(conn).has_table("voice_profiles"),
)
self.assertFalse(exists)
self.assertEqual(
(await storage.get_agent("owner", agent.id)).model_dump(),
before,
)
async def test_concurrent_voice_profile_id_collision(self) -> None:
"""Two completed prechecks cannot authorize an ownership overwrite."""
async with AsyncSQLAlchemyStorage(self.url) as storage:
for same_owner in (False, True):
with self.subTest(same_owner=same_owner):
await self._assert_concurrent_collision(
storage,
same_owner,
)
async def _assert_concurrent_collision(
self,
storage: AsyncSQLAlchemyStorage,
same_owner: bool,
) -> None:
"""Control insert ordering and verify the winning record in full."""
records = [
VoiceProfileRecord(
id="contested-profile",
user_id=owner,
data=VoiceProfileData(name=name),
)
for owner, name in (
("owner-a", "First writer"),
(
"owner-a" if same_owner else "owner-b",
"Second writer",
),
)
]
ready = asyncio.Event()
first_done = asyncio.Event()
arrivals = 0
execute = AsyncSession.execute
async def gated_execute(
session: AsyncSession,
statement: Any,
*args: Any,
**kwargs: Any,
) -> Any:
"""Hold both INSERTs until any prechecks have passed."""
nonlocal arrivals
if (
isinstance(statement, Insert)
and statement.table.name == "voice_profiles"
):
arrivals += 1
if arrivals == 2:
ready.set()
await ready.wait()
if asyncio.current_task().get_name() == "second":
await first_done.wait()
return await execute(
session,
statement,
*args,
**kwargs,
)
async def write(index: int) -> str:
"""Commit the first writer before releasing the next."""
try:
record = records[index]
return await storage.upsert_voice_profile(
record.user_id,
record,
)
finally:
if index == 0:
first_done.set()
with patch.object(
AsyncSession,
"execute",
new=gated_execute,
):
results = await asyncio.wait_for(
asyncio.gather(
asyncio.create_task(write(0), name="first"),
asyncio.create_task(write(1), name="second"),
return_exceptions=True,
),
timeout=10,
)
self.assertEqual(results[0], "contested-profile")
if same_owner:
self.assertEqual(results, ["contested-profile"] * 2)
self.assertEqual(
records[1].created_at,
records[0].created_at,
)
expected = records[1]
else:
self.assertIsInstance(results[1], PermissionError)
self.assertEqual(
str(results[1]),
"Cannot overwrite another user's voice profile.",
)
expected = records[0]
self.assertEqual(
[
item.model_dump()
for item in await storage.list_voice_profiles(
"owner-a",
)
],
[expected.model_dump()],
)
self.assertEqual(
await storage.list_voice_profiles("owner-b"),
[],
)
await storage.delete_voice_profile(
"owner-a",
"contested-profile",
)