mirror of
https://github.com/BreizhHardware/Jellystat.git
synced 2026-01-18 16:27:20 +01:00
837dd18014
Added Sync feature to only sync Recently Added Items that don't exist in the database (this will not update existing data), default interval now set to 15 Minutes Renamed existing sync to Full sync (should function exactly the same), default interval now set to 1 day Reworked handling of items no longer on jellyfin. Items are no longer deleted but are now marked as archived so that we can still view their items when looking at Playback Activity. Added options to purge Archived data. This will either purge just the item (including seasons and episodes if its a show) or the item plus all related watch activity
94 lines
3.8 KiB
JavaScript
94 lines
3.8 KiB
JavaScript
exports.up = async function(knex) {
|
|
try
|
|
{
|
|
await knex.schema.raw(`
|
|
DROP FUNCTION IF EXISTS public.fs_last_user_activity(text);
|
|
|
|
CREATE OR REPLACE FUNCTION public.fs_last_user_activity(
|
|
userid text)
|
|
RETURNS TABLE("Id" text, "EpisodeId" text, "Name" text, "EpisodeName" text, "SeasonNumber" integer, "EpisodeNumber" integer, "PrimaryImageHash" text, "UserId" text, "UserName" text, archived boolean, "LastPlayed" interval)
|
|
LANGUAGE 'plpgsql'
|
|
COST 100
|
|
VOLATILE PARALLEL UNSAFE
|
|
ROWS 1000
|
|
|
|
AS $BODY$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT *
|
|
FROM (
|
|
SELECT DISTINCT ON (i."Name", e."Name")
|
|
i."Id",
|
|
a."EpisodeId",
|
|
i."Name",
|
|
e."Name" AS "EpisodeName",
|
|
CASE WHEN a."SeasonId" IS NOT NULL THEN s."IndexNumber" ELSE NULL END AS "SeasonNumber",
|
|
CASE WHEN a."SeasonId" IS NOT NULL THEN e."IndexNumber" ELSE NULL END AS "EpisodeNumber",
|
|
i."PrimaryImageHash",
|
|
a."UserId",
|
|
a."UserName",
|
|
i.archived,
|
|
(NOW() - a."ActivityDateInserted") as "LastPlayed"
|
|
FROM jf_playback_activity a
|
|
JOIN jf_library_items i ON i."Id" = a."NowPlayingItemId"
|
|
LEFT JOIN jf_library_seasons s ON s."Id" = a."SeasonId"
|
|
LEFT JOIN jf_library_episodes e ON e."EpisodeId" = a."EpisodeId"
|
|
WHERE a."UserId" = userid
|
|
) AS latest_distinct_rows
|
|
ORDER BY "LastPlayed";
|
|
END;
|
|
|
|
|
|
$BODY$;`);
|
|
|
|
}catch (error) {
|
|
console.error(error);
|
|
}
|
|
};
|
|
|
|
exports.down = async function(knex) {
|
|
try {
|
|
|
|
await knex.schema.raw(`
|
|
DROP FUNCTION IF EXISTS public.fs_last_user_activity(text);
|
|
|
|
CREATE OR REPLACE FUNCTION public.fs_last_user_activity(
|
|
userid text)
|
|
RETURNS TABLE("Id" text, "EpisodeId" text, "Name" text, "EpisodeName" text, "SeasonNumber" integer, "EpisodeNumber" integer, "PrimaryImageHash" text, "UserId" text, "UserName" text, "LastPlayed" interval)
|
|
LANGUAGE 'plpgsql'
|
|
COST 100
|
|
VOLATILE PARALLEL UNSAFE
|
|
ROWS 1000
|
|
|
|
AS $BODY$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT *
|
|
FROM (
|
|
SELECT DISTINCT ON (i."Name", e."Name")
|
|
i."Id",
|
|
a."EpisodeId",
|
|
i."Name",
|
|
e."Name" AS "EpisodeName",
|
|
CASE WHEN a."SeasonId" IS NOT NULL THEN s."IndexNumber" ELSE NULL END AS "SeasonNumber",
|
|
CASE WHEN a."SeasonId" IS NOT NULL THEN e."IndexNumber" ELSE NULL END AS "EpisodeNumber",
|
|
i."PrimaryImageHash",
|
|
a."UserId",
|
|
a."UserName",
|
|
(NOW() - a."ActivityDateInserted") as "LastPlayed"
|
|
FROM jf_playback_activity a
|
|
JOIN jf_library_items i ON i."Id" = a."NowPlayingItemId"
|
|
LEFT JOIN jf_library_seasons s ON s."Id" = a."SeasonId"
|
|
LEFT JOIN jf_library_episodes e ON e."EpisodeId" = a."EpisodeId"
|
|
WHERE a."UserId" = userid
|
|
) AS latest_distinct_rows
|
|
ORDER BY "LastPlayed";
|
|
END;
|
|
|
|
|
|
$BODY$;`);
|
|
} catch (error) {
|
|
console.error(error);
|
|
}
|
|
};
|