75 lines
2.5 KiB
Python
75 lines
2.5 KiB
Python
from __future__ import annotations
|
|
|
|
from datetime import datetime, timedelta
|
|
from sqlalchemy import and_, func, select, text
|
|
from sqlalchemy.ext.asyncio import AsyncSession
|
|
|
|
from app.models.audio_data import AudioData
|
|
|
|
|
|
class AudioRepository:
|
|
def __init__(self, db: AsyncSession):
|
|
self.db = db
|
|
|
|
async def latest(self, limit: int) -> list[AudioData]:
|
|
q = select(AudioData).order_by(AudioData.time.desc()).limit(limit)
|
|
res = await self.db.execute(q)
|
|
return list(res.scalars().all())
|
|
|
|
async def range(self, time_from: datetime, time_to: datetime) -> list[AudioData]:
|
|
q = (
|
|
select(AudioData)
|
|
.where(and_(AudioData.time >= time_from, AudioData.time <= time_to))
|
|
.order_by(AudioData.time.asc())
|
|
)
|
|
res = await self.db.execute(q)
|
|
return list(res.scalars().all())
|
|
|
|
async def loud_samples(
|
|
self,
|
|
threshold: float,
|
|
time_from: datetime | None,
|
|
time_to: datetime | None,
|
|
) -> list[AudioData]:
|
|
cond = [AudioData.rms_db >= threshold]
|
|
if time_from:
|
|
cond.append(AudioData.time >= time_from)
|
|
if time_to:
|
|
cond.append(AudioData.time <= time_to)
|
|
|
|
q = select(AudioData).where(and_(*cond)).order_by(AudioData.time.asc())
|
|
res = await self.db.execute(q)
|
|
return list(res.scalars().all())
|
|
|
|
async def summary_since(self, since: datetime) -> dict:
|
|
q = select(
|
|
func.avg(AudioData.rms_db).label("avg_db"),
|
|
func.max(AudioData.rms_db).label("max_db"),
|
|
func.sum(func.case((AudioData.is_silence.is_(True), 1), else_=0)).label(
|
|
"silence_count"
|
|
),
|
|
func.count().label("total_count"),
|
|
).where(AudioData.time >= since)
|
|
|
|
res = await self.db.execute(q)
|
|
row = res.one()
|
|
|
|
# dominant freq excluding silence
|
|
fq = (
|
|
select(AudioData.frequency_hz, func.count().label("cnt"))
|
|
.where(and_(AudioData.time >= since, AudioData.is_silence.is_(False)))
|
|
.group_by(AudioData.frequency_hz)
|
|
.order_by(text("cnt DESC"))
|
|
.limit(1)
|
|
)
|
|
fres = await self.db.execute(fq)
|
|
frow = fres.first()
|
|
|
|
return {
|
|
"avg_db": float(row.avg_db or 0.0),
|
|
"max_db": float(row.max_db or 0.0),
|
|
"dominant_freq": int(frow[0]) if frow else 0,
|
|
"silence_count": int(row.silence_count or 0),
|
|
"total_count": int(row.total_count or 0),
|
|
}
|