mirror of
https://github.com/stashapp/stash-box.git
synced 2026-02-10 15:12:33 -06:00
5efb00c542
* Prevent submission of fingerprints with 0s duration * Escape email address in activation email link * Sort edit comments by timestamp * Use local timezone for edit/editnote timestamps * Order user list ascending * Disable submit buttons while edits are submitting. * Fix performer/scene update triggers to concatenate and update correctly * Add scene duration to scene page and search card * Allow unsetting studio parent id * Fix performer ordering by scene_count * Add performer order by created_at * Fix updating of column names with reserved names * Allow users to cancel own edits * Fix edit diff of partial performer birthdates * Fix birthdate showing up in edit diffs when being removed * Add career start ordering for performers * Support searching by tag name with uuid * Change edit diff checkboxes to icons for visibility * Add markdown to edit comments * Add scene filtering by fingerprint * Switch markdown library to react-markdown * Prevent generation of image uuids that start with ad * Fix timestamp timezone * Restore #60 Co-authored-by: peolic <66393006+peolic@users.noreply.github.com> * Fix breasttype issues for male performers * Fix breasttype issue for performers with null breasttype * Add shortcut for changing performers in scenes * Linting * Coalesce breast type to Unknown rather than NA * Fix breasttype diff logic * Fix text truncation of performer name in scene form Co-authored-by: peolic <66393006+peolic@users.noreply.github.com>
50 lines
1.8 KiB
PL/PgSQL
50 lines
1.8 KiB
PL/PgSQL
TRUNCATE TABLE scene_search;
|
|
|
|
INSERT INTO scene_search
|
|
SELECT
|
|
S.id as scene_id,
|
|
REGEXP_REPLACE(S.title, '[^a-zA-Z0-9 ]+', '', 'g') AS scene_title,
|
|
S.date::TEXT AS scene_date,
|
|
T.name || ' ' || REGEXP_REPLACE(T.name, '[^a-zA-Z0-9]', '', 'g') || ' ' || CASE WHEN TP.name IS NOT NULL THEN (TP.name || ' ' || REGEXP_REPLACE(TP.name, '[^a-zA-Z0-9]', '', 'g') ) ELSE '' END AS studio_name,
|
|
ARRAY_TO_STRING(ARRAY_CAT(ARRAY_AGG(P.name), ARRAY_AGG(PS.as)), ' ', '') AS performer_names
|
|
FROM scenes S
|
|
LEFT JOIN scene_performers PS ON PS.scene_id = S.id
|
|
LEFT JOIN performers P ON PS.performer_id = P.id
|
|
LEFT JOIN studios T ON T.id = S.studio_id
|
|
LEFT JOIN studios TP ON T.parent_studio_id = TP.id
|
|
GROUP BY S.id, S.title, T.name, TP.name;
|
|
|
|
CREATE OR REPLACE FUNCTION update_performers() RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
IF (NEW.name != OLD.name) THEN
|
|
UPDATE scene_search SET performer_names = SUBQUERY.performer_names
|
|
FROM (
|
|
SELECT S.id as scene_id, ARRAY_TO_STRING(ARRAY_CAT(ARRAY_AGG(P.name), ARRAY_AGG(PPS.as)), ' ', '') AS performer_names
|
|
FROM scene_performers PS
|
|
JOIN scenes S ON PS.scene_id = S.id
|
|
LEFT JOIN scene_performers PPS ON S.id = PPS.scene_id
|
|
LEFT JOIN performers P ON PPS.performer_id = P.id
|
|
WHERE PS.performer_id = NEW.id
|
|
GROUP BY S.id
|
|
) SUBQUERY
|
|
WHERE scene_search.scene_id = SUBQUERY.scene_id;
|
|
END IF;
|
|
RETURN NULL;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE OR REPLACE FUNCTION update_scene_performers() RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
UPDATE scene_search SET performer_names = SUBQUERY.performer_names
|
|
FROM (
|
|
SELECT PS.scene_id as scene_id, ARRAY_TO_STRING(ARRAY_CAT(ARRAY_AGG(P.name), ARRAY_AGG(PS.as)), ' ', '') AS performer_names
|
|
FROM scene_performers PS
|
|
LEFT JOIN performers P ON PS.performer_id = P.id
|
|
WHERE PS.scene_id = NEW.scene_id
|
|
GROUP BY PS.scene_id
|
|
) SUBQUERY
|
|
WHERE scene_search.scene_id = COALESCE(NEW.scene_id, OLD.scene_id);
|
|
RETURN NULL;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|