62 lines
2.2 KiB
JavaScript
62 lines
2.2 KiB
JavaScript
import { getPool } from "../../db/index.js";
|
|
|
|
// Usage counts are per-day; storage totals are running cumulatives.
|
|
export async function getSeries(userId, { from, to }) {
|
|
const client = await getPool();
|
|
try {
|
|
const res = await client.query(
|
|
`SELECT
|
|
to_char(d, 'YYYY-MM-DD') AS date,
|
|
(
|
|
SELECT COUNT(*)::int FROM usage u
|
|
JOIN usage_url uu ON uu.id = u.usage_url_id
|
|
WHERE u.user_id = $1 AND uu.module = 'core'
|
|
AND u.created_ts >= d AND u.created_ts < d + interval '1 day'
|
|
) AS core,
|
|
(
|
|
SELECT COUNT(*)::int FROM usage u
|
|
JOIN usage_url uu ON uu.id = u.usage_url_id
|
|
WHERE u.user_id = $1 AND uu.module = 'archive'
|
|
AND u.created_ts >= d AND u.created_ts < d + interval '1 day'
|
|
) AS audit,
|
|
(
|
|
SELECT COALESCE(SUM(pg_column_size(ed.data)), 0)::bigint
|
|
FROM event e
|
|
JOIN event_data ed ON ed.event_id = e.id
|
|
WHERE e.user_id = $1 AND e.created_as_ts < d + interval '1 day'
|
|
) AS core_disk,
|
|
(
|
|
(
|
|
SELECT COALESCE(SUM(pg_column_size(a.digest)), 0)::bigint
|
|
FROM audit a
|
|
JOIN event e ON e.event_id_uuid = a.event_id AND e.user_id = $1
|
|
WHERE a.created_ts < d + interval '1 day'
|
|
)
|
|
+
|
|
(
|
|
SELECT COALESCE(SUM(pg_column_size(s.jws)), 0)::bigint
|
|
FROM audit_signature s
|
|
JOIN audit a ON a.audit_id = s.audit_id
|
|
JOIN event e ON e.event_id_uuid = a.event_id AND e.user_id = $1
|
|
WHERE a.created_ts < d + interval '1 day'
|
|
)
|
|
) AS audit_disk
|
|
FROM generate_series($2::date, $3::date, interval '1 day') AS d
|
|
ORDER BY d`,
|
|
[userId, from, to],
|
|
);
|
|
|
|
const usage = [];
|
|
const storage = [];
|
|
for (const r of res.rows) {
|
|
const core = r.core, audit = r.audit;
|
|
usage.push({ date: r.date, core, audit, total: core + audit });
|
|
const coreDisk = Number(r.core_disk), auditDisk = Number(r.audit_disk);
|
|
storage.push({ date: r.date, coreDisk, auditDisk, totalDisk: coreDisk + auditDisk });
|
|
}
|
|
return { usage, storage };
|
|
} finally {
|
|
await client.release();
|
|
}
|
|
}
|