82 lines
2.5 KiB
PL/PgSQL
82 lines
2.5 KiB
PL/PgSQL
BEGIN TRANSACTION;
|
|
|
|
DROP TYPE IF EXISTS catalog_grid_result CASCADE;
|
|
DROP FUNCTION IF EXISTS fetch_catalog;
|
|
|
|
-- OLD: 121ms
|
|
CREATE TYPE catalog_grid_result AS
|
|
(
|
|
-- post_count bigint,
|
|
estimated_post_count bigint,
|
|
post_id bigint,
|
|
board_post_id bigint,
|
|
creation_time timestamptz,
|
|
bump_time timestamptz,
|
|
body text,
|
|
subject text,
|
|
thread_id bigint,
|
|
embed text,
|
|
board_thread_id bigint,
|
|
pathpart text,
|
|
site_name text,
|
|
file_mimetype text,
|
|
file_illegal boolean,
|
|
-- file_resolution dimension,
|
|
file_name text,
|
|
file_extension text,
|
|
file_thumb_extension text
|
|
);
|
|
|
|
|
|
CREATE OR REPLACE FUNCTION fetch_catalog(max_time timestamptz, max_row_read int DEFAULT 10000)
|
|
RETURNS SETOF catalog_grid_result AS $$
|
|
WITH
|
|
top AS
|
|
(
|
|
SELECT * FROM fetch_top_threads(max_time, max_row_read) AS top
|
|
),
|
|
tall_posts AS
|
|
(
|
|
SELECT
|
|
top.post_count AS estimated_post_count,
|
|
posts.post_id,
|
|
posts.board_post_id,
|
|
posts.creation_time,
|
|
top.bump_time,
|
|
posts.body,
|
|
posts.subject,
|
|
posts.thread_id,
|
|
posts.embed
|
|
FROM top
|
|
JOIN posts ON top.thread_id = posts.thread_id AND posts.local_idx = 1
|
|
WHERE creation_time < max_time
|
|
)
|
|
SELECT
|
|
-- post_counts.post_count,
|
|
tall_posts.*,
|
|
threads.board_thread_id, -- this should be part of the url path when creating links, not thread_id (that's internal)
|
|
boards.pathpart,
|
|
sites."name",
|
|
-- sites.site_id,
|
|
attachments.mimetype AS file_mimetype,
|
|
attachments.illegal AS file_illegal,
|
|
-- attachments.resolution AS file_resolution,
|
|
attachments.board_filename AS file_name,
|
|
attachments.file_extension,
|
|
attachments.thumb_extension AS file_thumb_extension
|
|
FROM tall_posts
|
|
JOIN threads ON tall_posts.thread_id = threads.thread_id
|
|
JOIN boards ON threads.board_id = boards.board_id
|
|
JOIN sites ON sites.site_id = boards.site_id
|
|
LEFT OUTER JOIN attachments ON attachments.post_id = tall_posts.post_id AND attachments.attachment_idx = 1
|
|
ORDER BY bump_time DESC;
|
|
$$ LANGUAGE sql;
|
|
|
|
|
|
REVOKE EXECUTE ON FUNCTION fetch_catalog FROM PUBLIC;
|
|
GRANT EXECUTE ON FUNCTION fetch_catalog TO chan_archive_anon;
|
|
GRANT EXECUTE ON FUNCTION fetch_catalog TO chan_archiver;
|
|
|
|
-- ROLLBACK;
|
|
COMMIT;
|