436 lines
No EOL
16 KiB
SQL
436 lines
No EOL
16 KiB
SQL
-- name: GetImageByID :one
|
|
SELECT id, storage_type, image_path
|
|
FROM images
|
|
WHERE id = sqlc.arg('illust_id')::bigint;
|
|
|
|
-- name: CreateImage :one
|
|
INSERT INTO images (storage_type, image_path)
|
|
VALUES ($1, $2)
|
|
RETURNING id, storage_type, image_path;
|
|
|
|
-- name: GetUserByID :one
|
|
SELECT
|
|
t.id as id,
|
|
t.avatar_id as avatar_id,
|
|
t.mail as mail,
|
|
t.nickname as nickname,
|
|
t.disp_name as disp_name,
|
|
t.user_desc as user_desc,
|
|
t.creation_date as creation_date,
|
|
i.storage_type as storage_type,
|
|
i.image_path as image_path
|
|
FROM users as t
|
|
LEFT JOIN images as i ON (t.avatar_id = i.id)
|
|
WHERE t.id = sqlc.arg('id')::bigint;
|
|
|
|
-- name: SearchUser :many
|
|
SELECT
|
|
u.id AS id,
|
|
u.avatar_id AS avatar_id,
|
|
u.mail AS mail,
|
|
u.nickname AS nickname,
|
|
u.disp_name AS disp_name,
|
|
u.user_desc AS user_desc,
|
|
u.creation_date AS creation_date,
|
|
i.storage_type AS storage_type,
|
|
i.image_path AS image_path
|
|
FROM users AS u
|
|
LEFT JOIN images AS i ON u.avatar_id = i.id
|
|
WHERE
|
|
(
|
|
sqlc.narg('word')::text IS NULL
|
|
OR (
|
|
SELECT bool_and(
|
|
u.nickname ILIKE ('%' || term || '%')
|
|
OR u.disp_name ILIKE ('%' || term || '%')
|
|
)
|
|
FROM unnest(string_to_array(trim(sqlc.narg('word')::text), ' ')) AS term
|
|
WHERE term <> ''
|
|
)
|
|
)
|
|
AND (
|
|
sqlc.narg('cursor')::int IS NULL
|
|
OR u.id > sqlc.narg('cursor')::int
|
|
)
|
|
ORDER BY u.id ASC
|
|
LIMIT COALESCE(sqlc.narg('limit')::int, 20);
|
|
|
|
-- name: GetStudioByID :one
|
|
SELECT *
|
|
FROM studios
|
|
WHERE id = sqlc.arg('studio_id')::bigint;
|
|
|
|
-- name: InsertStudio :one
|
|
INSERT INTO studios (studio_name, illust_id, studio_desc)
|
|
VALUES (
|
|
sqlc.arg('studio_name')::text,
|
|
sqlc.narg('illust_id')::bigint,
|
|
sqlc.narg('studio_desc')::text)
|
|
RETURNING id, studio_name, illust_id, studio_desc;
|
|
|
|
-- name: GetTitleTags :many
|
|
SELECT
|
|
tag_names
|
|
FROM tags as g
|
|
JOIN title_tags as t ON(t.tag_id = g.id)
|
|
WHERE t.title_id = sqlc.arg('title_id')::bigint;
|
|
|
|
-- name: InsertTitleTags :one
|
|
INSERT INTO title_tags (title_id, tag_id)
|
|
VALUES (
|
|
sqlc.arg('title_id')::bigint,
|
|
sqlc.arg('tag_id')::bigint)
|
|
RETURNING title_id, tag_id;
|
|
|
|
-- name: InsertTag :one
|
|
INSERT INTO tags (tag_names)
|
|
VALUES (
|
|
sqlc.arg('tag_names')::jsonb)
|
|
RETURNING id, tag_names;
|
|
|
|
-- name: UpdateUser :one
|
|
UPDATE users
|
|
SET
|
|
avatar_id = COALESCE(sqlc.narg('avatar_id'), avatar_id),
|
|
disp_name = COALESCE(sqlc.narg('disp_name'), disp_name),
|
|
user_desc = COALESCE(sqlc.narg('user_desc'), user_desc),
|
|
mail = COALESCE(sqlc.narg('mail'), mail)
|
|
WHERE id = sqlc.arg('user_id')
|
|
RETURNING id, avatar_id, nickname, disp_name, user_desc, creation_date, mail;
|
|
|
|
-- name: GetTitleByID :one
|
|
-- sqlc.struct: TitlesFull
|
|
SELECT
|
|
t.*,
|
|
i.storage_type as title_storage_type,
|
|
i.image_path as title_image_path,
|
|
COALESCE(
|
|
jsonb_agg(g.tag_names) FILTER (WHERE g.tag_names IS NOT NULL),
|
|
'[]'::jsonb
|
|
)::jsonb as tag_names,
|
|
s.studio_name as studio_name,
|
|
s.illust_id as studio_illust_id,
|
|
s.studio_desc as studio_desc,
|
|
si.storage_type as studio_storage_type,
|
|
si.image_path as studio_image_path
|
|
|
|
FROM titles as t
|
|
LEFT JOIN images as i ON (t.poster_id = i.id)
|
|
LEFT JOIN title_tags as tt ON (t.id = tt.title_id)
|
|
LEFT JOIN tags as g ON (tt.tag_id = g.id)
|
|
LEFT JOIN studios as s ON (t.studio_id = s.id)
|
|
LEFT JOIN images as si ON (s.illust_id = si.id)
|
|
|
|
WHERE t.id = sqlc.arg('title_id')::bigint
|
|
GROUP BY
|
|
t.id, i.id, s.id, si.id;
|
|
|
|
-- name: SearchTitles :many
|
|
SELECT
|
|
t.id as id,
|
|
t.title_names as title_names,
|
|
t.poster_id as poster_id,
|
|
t.title_status as title_status,
|
|
t.rating as rating,
|
|
t.rating_count as rating_count,
|
|
t.release_year as release_year,
|
|
t.release_season as release_season,
|
|
t.season as season,
|
|
t.episodes_aired as episodes_aired,
|
|
t.episodes_all as episodes_all,
|
|
i.storage_type as title_storage_type,
|
|
i.image_path as title_image_path,
|
|
COALESCE(
|
|
jsonb_agg(g.tag_names) FILTER (WHERE g.tag_names IS NOT NULL),
|
|
'[]'::jsonb
|
|
)::jsonb as tag_names,
|
|
s.studio_name as studio_name
|
|
|
|
FROM titles as t
|
|
LEFT JOIN images as i ON (t.poster_id = i.id)
|
|
LEFT JOIN title_tags as tt ON (t.id = tt.title_id)
|
|
LEFT JOIN tags as g ON (tt.tag_id = g.id)
|
|
LEFT JOIN studios as s ON (t.studio_id = s.id)
|
|
|
|
WHERE
|
|
CASE
|
|
WHEN sqlc.arg('forward')::boolean THEN
|
|
-- forward: greater than cursor (next page)
|
|
CASE sqlc.arg('sort_by')::text
|
|
WHEN 'year' THEN
|
|
(sqlc.narg('cursor_year')::int IS NULL) OR
|
|
(t.release_year > sqlc.narg('cursor_year')::int) OR
|
|
(t.release_year = sqlc.narg('cursor_year')::int AND t.id > sqlc.narg('cursor_id')::bigint)
|
|
|
|
WHEN 'rating' THEN
|
|
(sqlc.narg('cursor_rating')::float IS NULL) OR
|
|
(t.rating > sqlc.narg('cursor_rating')::float) OR
|
|
(t.rating = sqlc.narg('cursor_rating')::float AND t.id > sqlc.narg('cursor_id')::bigint)
|
|
|
|
WHEN 'id' THEN
|
|
(sqlc.narg('cursor_id')::bigint IS NULL) OR
|
|
(t.id > sqlc.narg('cursor_id')::bigint)
|
|
|
|
ELSE true -- fallback
|
|
END
|
|
|
|
ELSE
|
|
-- backward: less than cursor (prev page)
|
|
CASE sqlc.arg('sort_by')::text
|
|
WHEN 'year' THEN
|
|
(sqlc.narg('cursor_year')::int IS NULL) OR
|
|
(t.release_year < sqlc.narg('cursor_year')::int) OR
|
|
(t.release_year = sqlc.narg('cursor_year')::int AND t.id < sqlc.narg('cursor_id')::bigint)
|
|
|
|
WHEN 'rating' THEN
|
|
(sqlc.narg('cursor_rating')::float IS NULL) OR
|
|
(t.rating < sqlc.narg('cursor_rating')::float) OR
|
|
(t.rating = sqlc.narg('cursor_rating')::float AND t.id < sqlc.narg('cursor_id')::bigint)
|
|
|
|
WHEN 'id' THEN
|
|
(sqlc.narg('cursor_id')::bigint IS NULL) OR
|
|
(t.id < sqlc.narg('cursor_id')::bigint)
|
|
|
|
ELSE true
|
|
END
|
|
END
|
|
|
|
AND (
|
|
CASE
|
|
WHEN sqlc.narg('word')::text IS NOT NULL THEN
|
|
(
|
|
SELECT bool_and(
|
|
EXISTS (
|
|
SELECT 1
|
|
FROM jsonb_each_text(t.title_names) AS t(key, val)
|
|
WHERE val ILIKE pattern
|
|
)
|
|
)
|
|
FROM unnest(
|
|
ARRAY(
|
|
SELECT '%' || trim(w) || '%'
|
|
FROM unnest(string_to_array(sqlc.narg('word')::text, ' ')) AS w
|
|
WHERE trim(w) <> ''
|
|
)
|
|
) AS pattern
|
|
)
|
|
ELSE true
|
|
END
|
|
)
|
|
|
|
AND (
|
|
sqlc.narg('title_statuses')::title_status_t[] IS NULL
|
|
OR array_length(sqlc.narg('title_statuses')::title_status_t[], 1) IS NULL
|
|
OR array_length(sqlc.narg('title_statuses')::title_status_t[], 1) = 0
|
|
OR t.title_status = ANY(sqlc.narg('title_statuses')::title_status_t[])
|
|
)
|
|
AND (sqlc.narg('rating')::float IS NULL OR t.rating >= sqlc.narg('rating')::float)
|
|
AND (sqlc.narg('release_year')::int IS NULL OR t.release_year = sqlc.narg('release_year')::int)
|
|
AND (sqlc.narg('release_season')::release_season_t IS NULL OR t.release_season = sqlc.narg('release_season')::release_season_t)
|
|
|
|
GROUP BY
|
|
t.id, i.id, s.id
|
|
|
|
ORDER BY
|
|
CASE WHEN sqlc.arg('forward')::boolean THEN
|
|
CASE
|
|
WHEN sqlc.arg('sort_by')::text = 'id' THEN t.id
|
|
WHEN sqlc.arg('sort_by')::text = 'year' THEN t.release_year
|
|
WHEN sqlc.arg('sort_by')::text = 'rating' THEN t.rating
|
|
END
|
|
END ASC,
|
|
CASE WHEN NOT sqlc.arg('forward')::boolean THEN
|
|
CASE
|
|
WHEN sqlc.arg('sort_by')::text = 'id' THEN t.id
|
|
WHEN sqlc.arg('sort_by')::text = 'year' THEN t.release_year
|
|
WHEN sqlc.arg('sort_by')::text = 'rating' THEN t.rating
|
|
END
|
|
END DESC,
|
|
|
|
CASE WHEN sqlc.arg('sort_by')::text <> 'id' THEN t.id END ASC
|
|
|
|
LIMIT COALESCE(sqlc.narg('limit')::int, 100); -- 100 is default limit
|
|
|
|
-- name: SearchUserTitles :many
|
|
SELECT
|
|
t.id as id,
|
|
t.title_names as title_names,
|
|
t.poster_id as poster_id,
|
|
t.title_status as title_status,
|
|
t.rating as rating,
|
|
t.rating_count as rating_count,
|
|
t.release_year as release_year,
|
|
t.release_season as release_season,
|
|
t.season as season,
|
|
t.episodes_aired as episodes_aired,
|
|
t.episodes_all as episodes_all,
|
|
u.user_id as user_id,
|
|
u.status as usertitle_status,
|
|
u.rate as user_rate,
|
|
u.review_id as review_id,
|
|
u.ctime as user_ctime,
|
|
i.storage_type as title_storage_type,
|
|
i.image_path as title_image_path,
|
|
COALESCE(
|
|
jsonb_agg(g.tag_names) FILTER (WHERE g.tag_names IS NOT NULL),
|
|
'[]'::jsonb
|
|
)::jsonb as tag_names,
|
|
s.studio_name as studio_name
|
|
|
|
FROM usertitles as u
|
|
JOIN titles as t ON (u.title_id = t.id)
|
|
LEFT JOIN images as i ON (t.poster_id = i.id)
|
|
LEFT JOIN title_tags as tt ON (t.id = tt.title_id)
|
|
LEFT JOIN tags as g ON (tt.tag_id = g.id)
|
|
LEFT JOIN studios as s ON (t.studio_id = s.id)
|
|
|
|
WHERE
|
|
u.user_id = sqlc.arg('user_id')::bigint
|
|
AND
|
|
CASE
|
|
WHEN sqlc.arg('forward')::boolean THEN
|
|
-- forward: greater than cursor (next page)
|
|
CASE sqlc.arg('sort_by')::text
|
|
WHEN 'year' THEN
|
|
(sqlc.narg('cursor_year')::int IS NULL) OR
|
|
(t.release_year > sqlc.narg('cursor_year')::int) OR
|
|
(t.release_year = sqlc.narg('cursor_year')::int AND t.id > sqlc.narg('cursor_id')::bigint)
|
|
|
|
WHEN 'rating' THEN
|
|
(sqlc.narg('cursor_rating')::float IS NULL) OR
|
|
(t.rating > sqlc.narg('cursor_rating')::float) OR
|
|
(t.rating = sqlc.narg('cursor_rating')::float AND t.id > sqlc.narg('cursor_id')::bigint)
|
|
|
|
WHEN 'id' THEN
|
|
(sqlc.narg('cursor_id')::bigint IS NULL) OR
|
|
(t.id > sqlc.narg('cursor_id')::bigint)
|
|
|
|
ELSE true -- fallback
|
|
END
|
|
|
|
ELSE
|
|
-- backward: less than cursor (prev page)
|
|
CASE sqlc.arg('sort_by')::text
|
|
WHEN 'year' THEN
|
|
(sqlc.narg('cursor_year')::int IS NULL) OR
|
|
(t.release_year < sqlc.narg('cursor_year')::int) OR
|
|
(t.release_year = sqlc.narg('cursor_year')::int AND t.id < sqlc.narg('cursor_id')::bigint)
|
|
|
|
WHEN 'rating' THEN
|
|
(sqlc.narg('cursor_rating')::float IS NULL) OR
|
|
(t.rating < sqlc.narg('cursor_rating')::float) OR
|
|
(t.rating = sqlc.narg('cursor_rating')::float AND t.id < sqlc.narg('cursor_id')::bigint)
|
|
|
|
WHEN 'id' THEN
|
|
(sqlc.narg('cursor_id')::bigint IS NULL) OR
|
|
(t.id < sqlc.narg('cursor_id')::bigint)
|
|
|
|
ELSE true
|
|
END
|
|
END
|
|
|
|
AND (
|
|
CASE
|
|
WHEN sqlc.narg('word')::text IS NOT NULL THEN
|
|
(
|
|
SELECT bool_and(
|
|
EXISTS (
|
|
SELECT 1
|
|
FROM jsonb_each_text(t.title_names) AS t(key, val)
|
|
WHERE val ILIKE pattern
|
|
)
|
|
)
|
|
FROM unnest(
|
|
ARRAY(
|
|
SELECT '%' || trim(w) || '%'
|
|
FROM unnest(string_to_array(sqlc.narg('word')::text, ' ')) AS w
|
|
WHERE trim(w) <> ''
|
|
)
|
|
) AS pattern
|
|
)
|
|
ELSE true
|
|
END
|
|
)
|
|
|
|
AND (
|
|
sqlc.narg('title_statuses')::title_status_t[] IS NULL
|
|
OR array_length(sqlc.narg('title_statuses')::title_status_t[], 1) IS NULL
|
|
OR array_length(sqlc.narg('title_statuses')::title_status_t[], 1) = 0
|
|
OR t.title_status = ANY(sqlc.narg('title_statuses')::title_status_t[])
|
|
)
|
|
AND (
|
|
sqlc.narg('usertitle_statuses')::usertitle_status_t[] IS NULL
|
|
OR array_length(sqlc.narg('usertitle_statuses')::usertitle_status_t[], 1) IS NULL
|
|
OR array_length(sqlc.narg('usertitle_statuses')::usertitle_status_t[], 1) = 0
|
|
OR u.status = ANY(sqlc.narg('usertitle_statuses')::usertitle_status_t[])
|
|
)
|
|
AND (sqlc.narg('rate')::int IS NULL OR u.rate >= sqlc.narg('rate')::int)
|
|
AND (sqlc.narg('rating')::float IS NULL OR t.rating >= sqlc.narg('rating')::float)
|
|
AND (sqlc.narg('release_year')::int IS NULL OR t.release_year = sqlc.narg('release_year')::int)
|
|
AND (sqlc.narg('release_season')::release_season_t IS NULL OR t.release_season = sqlc.narg('release_season')::release_season_t)
|
|
|
|
GROUP BY
|
|
t.id, u.user_id, u.status, u.rate, u.review_id, u.ctime, i.id, s.id
|
|
|
|
ORDER BY
|
|
CASE WHEN sqlc.arg('forward')::boolean THEN
|
|
CASE
|
|
WHEN sqlc.arg('sort_by')::text = 'id' THEN t.id
|
|
WHEN sqlc.arg('sort_by')::text = 'year' THEN t.release_year
|
|
WHEN sqlc.arg('sort_by')::text = 'rating' THEN t.rating
|
|
WHEN sqlc.arg('sort_by')::text = 'rate' THEN u.rate
|
|
END
|
|
END ASC,
|
|
CASE WHEN NOT sqlc.arg('forward')::boolean THEN
|
|
CASE
|
|
WHEN sqlc.arg('sort_by')::text = 'id' THEN t.id
|
|
WHEN sqlc.arg('sort_by')::text = 'year' THEN t.release_year
|
|
WHEN sqlc.arg('sort_by')::text = 'rating' THEN t.rating
|
|
WHEN sqlc.arg('sort_by')::text = 'rate' THEN u.rate
|
|
END
|
|
END DESC,
|
|
|
|
CASE WHEN sqlc.arg('sort_by')::text <> 'id' THEN t.id END ASC
|
|
|
|
LIMIT COALESCE(sqlc.narg('limit')::int, 100); -- 100 is default limit
|
|
|
|
-- name: GetReviewByID :one
|
|
SELECT *
|
|
FROM reviews
|
|
WHERE review_id = sqlc.arg('review_id')::bigint;
|
|
|
|
-- name: InsertUserTitle :one
|
|
INSERT INTO usertitles (user_id, title_id, status, rate, review_id, ctime)
|
|
VALUES (
|
|
sqlc.arg('user_id')::bigint,
|
|
sqlc.arg('title_id')::bigint,
|
|
sqlc.arg('status')::usertitle_status_t,
|
|
sqlc.narg('rate')::int,
|
|
sqlc.narg('review_id')::bigint,
|
|
sqlc.narg('ftime')::timestamptz
|
|
)
|
|
RETURNING user_id, title_id, status, rate, review_id, ctime;
|
|
|
|
-- name: UpdateUserTitle :one
|
|
-- Fails with sql.ErrNoRows if (user_id, title_id) not found
|
|
UPDATE usertitles
|
|
SET
|
|
status = COALESCE(sqlc.narg('status')::usertitle_status_t, status),
|
|
rate = COALESCE(sqlc.narg('rate')::int, rate),
|
|
ctime = COALESCE(sqlc.narg('ftime')::timestamptz, ctime)
|
|
WHERE
|
|
user_id = sqlc.arg('user_id')
|
|
AND title_id = sqlc.arg('title_id')
|
|
RETURNING *;
|
|
|
|
-- name: DeleteUserTitle :one
|
|
DELETE FROM usertitles
|
|
WHERE user_id = sqlc.arg('user_id')
|
|
AND title_id = sqlc.arg('title_id')
|
|
RETURNING *;
|
|
|
|
-- name: GetUserTitleByID :one
|
|
SELECT
|
|
ut.*
|
|
FROM usertitles as ut
|
|
WHERE ut.title_id = sqlc.arg('title_id')::bigint AND ut.user_id = sqlc.arg('user_id')::bigint; |