63 lines
1.8 KiB
JavaScript
63 lines
1.8 KiB
JavaScript
const config = require('config');
|
|
|
|
exports.up = async function up(knex) {
|
|
await knex.raw(`
|
|
CREATE MATERIALIZED VIEW actors_meta AS (
|
|
SELECT
|
|
actors.id as actor_id,
|
|
COUNT(DISTINCT stashes_actors)::integer as stashed,
|
|
COUNT(DISTINCT releases_actors)::integer as scenes,
|
|
row_to_json(avatars) as avatar
|
|
FROM actors
|
|
LEFT JOIN stashes_actors ON stashes_actors.actor_id = actors.id
|
|
LEFT JOIN releases_actors ON releases_actors.actor_id = actors.id
|
|
LEFT JOIN media AS avatars ON avatars.id = actors.avatar_media_id
|
|
GROUP BY
|
|
actors.id,
|
|
avatars.id
|
|
);
|
|
|
|
CREATE MATERIALIZED VIEW scenes_meta AS (
|
|
SELECT
|
|
releases.id as scene_id,
|
|
COUNT(DISTINCT stashes_scenes)::integer as stashed
|
|
FROM releases
|
|
LEFT JOIN stashes_scenes ON stashes_scenes.scene_id = releases.id
|
|
GROUP BY releases.id
|
|
);
|
|
|
|
CREATE MATERIALIZED VIEW movies_meta AS (
|
|
SELECT
|
|
movie_id,
|
|
stashed,
|
|
stashed_scenes,
|
|
stashed + stashed_scenes as stashed_total
|
|
FROM (
|
|
SELECT
|
|
movies.id as movie_id,
|
|
COUNT(DISTINCT stashes_movies)::integer as stashed,
|
|
COUNT(DISTINCT stashes_scenes)::integer as stashed_scenes
|
|
FROM movies
|
|
LEFT JOIN stashes_movies ON stashes_movies.movie_id = movies.id
|
|
LEFT JOIN movies_scenes ON movies_scenes.movie_id = movies.id
|
|
LEFT JOIN stashes_scenes ON stashes_scenes.scene_id = movies_scenes.scene_id
|
|
GROUP BY movies.id
|
|
) AS meta
|
|
);
|
|
|
|
GRANT ALL ON actors_meta TO :visitor;
|
|
GRANT ALL ON scenes_meta TO :visitor;
|
|
GRANT ALL ON movies_meta TO :visitor;
|
|
`, {
|
|
visitor: knex.raw(config.database.query.user),
|
|
});
|
|
};
|
|
|
|
exports.down = async function down(knex) {
|
|
await knex.raw(`
|
|
DROP MATERIALIZED VIEW IF EXISTS actors_meta;
|
|
DROP MATERIALIZED VIEW IF EXISTS scenes_meta;
|
|
DROP MATERIALIZED VIEW IF EXISTS movies_meta;
|
|
`);
|
|
};
|