295 lines
9.9 KiB
SQL
295 lines
9.9 KiB
SQL
-- +goose Up
|
|
-- +goose StatementBegin
|
|
-- Upgrade collection groups from label-only buckets to opaque-id groups while
|
|
-- moving library collection placement onto the per-library membership table.
|
|
|
|
ALTER TABLE library_collection_groups
|
|
ADD COLUMN id TEXT,
|
|
ADD COLUMN name TEXT,
|
|
ADD COLUMN slug TEXT,
|
|
ADD COLUMN kind TEXT NOT NULL DEFAULT 'regular',
|
|
ADD COLUMN default_sort_mode TEXT NOT NULL DEFAULT 'manual';
|
|
|
|
-- Backfill any stale labels referenced by collections but missing a group row.
|
|
WITH stale_labels AS (
|
|
SELECT DISTINCT lcl.library_id, lc.group_label AS label
|
|
FROM library_collection_libraries lcl
|
|
JOIN library_collections lc ON lc.id = lcl.collection_id
|
|
WHERE COALESCE(lc.group_label, '') <> ''
|
|
AND NOT EXISTS (
|
|
SELECT 1
|
|
FROM library_collection_groups g
|
|
WHERE g.library_id = lcl.library_id
|
|
AND g.label = lc.group_label
|
|
)
|
|
),
|
|
numbered AS (
|
|
SELECT
|
|
s.library_id,
|
|
s.label,
|
|
ROW_NUMBER() OVER (PARTITION BY s.library_id ORDER BY s.label) - 1 AS offset
|
|
FROM stale_labels s
|
|
),
|
|
max_order AS (
|
|
SELECT library_id, COALESCE(MAX(sort_order), -1) AS max_sort_order
|
|
FROM library_collection_groups
|
|
GROUP BY library_id
|
|
)
|
|
INSERT INTO library_collection_groups (library_id, label, title, sort_order)
|
|
SELECT
|
|
n.library_id,
|
|
n.label,
|
|
n.label,
|
|
COALESCE(m.max_sort_order, -1) + 1 + n.offset
|
|
FROM numbered n
|
|
LEFT JOIN max_order m ON m.library_id = n.library_id
|
|
ON CONFLICT (library_id, label) DO NOTHING;
|
|
|
|
UPDATE library_collection_groups
|
|
SET
|
|
id = COALESCE(id, 'lcg_' || md5(library_id::text || ':' || label)),
|
|
name = COALESCE(NULLIF(name, ''), NULLIF(title, ''), label),
|
|
slug = COALESCE(
|
|
NULLIF(slug, ''),
|
|
NULLIF(regexp_replace(lower(trim(label)), '[^a-z0-9]+', '-', 'g'), ''),
|
|
'group-' || substr(md5(library_id::text || ':' || label), 1, 8)
|
|
)
|
|
WHERE id IS NULL OR name IS NULL OR slug IS NULL;
|
|
|
|
-- Seed the viewer-private user-collections placement group for every library.
|
|
INSERT INTO library_collection_groups (
|
|
library_id, label, title, sort_order, id, name, slug, kind, default_sort_mode
|
|
)
|
|
SELECT
|
|
mf.id,
|
|
'user-collections',
|
|
'My collections',
|
|
9998,
|
|
'lcg_user_' || mf.id::text,
|
|
'My collections',
|
|
'user-collections',
|
|
'user_collections',
|
|
'manual'
|
|
FROM media_folders mf
|
|
ON CONFLICT (library_id, label) DO UPDATE
|
|
SET
|
|
id = EXCLUDED.id,
|
|
name = EXCLUDED.name,
|
|
slug = EXCLUDED.slug,
|
|
kind = EXCLUDED.kind,
|
|
default_sort_mode = EXCLUDED.default_sort_mode,
|
|
updated_at = NOW();
|
|
|
|
ALTER TABLE library_collection_groups
|
|
ALTER COLUMN id SET NOT NULL,
|
|
ALTER COLUMN name SET NOT NULL,
|
|
ALTER COLUMN slug SET NOT NULL,
|
|
ADD CONSTRAINT library_collection_groups_kind_check
|
|
CHECK (kind IN ('regular', 'user_collections')),
|
|
ADD CONSTRAINT library_collection_groups_default_sort_mode_check
|
|
CHECK (default_sort_mode IN ('manual', 'name_asc', 'name_desc', 'recent', 'most_items')),
|
|
ADD CONSTRAINT library_collection_groups_id_unique UNIQUE (id),
|
|
ADD CONSTRAINT library_collection_groups_id_library_unique UNIQUE (id, library_id),
|
|
ADD CONSTRAINT library_collection_groups_library_slug_unique UNIQUE (library_id, slug);
|
|
|
|
CREATE UNIQUE INDEX idx_library_collection_groups_user_unique
|
|
ON library_collection_groups (library_id)
|
|
WHERE kind = 'user_collections';
|
|
|
|
CREATE INDEX idx_library_collection_groups_order
|
|
ON library_collection_groups (library_id, sort_order, name);
|
|
|
|
ALTER TABLE library_collection_libraries
|
|
ADD COLUMN group_id TEXT,
|
|
ADD COLUMN sort_order INTEGER NOT NULL DEFAULT 0,
|
|
ADD COLUMN created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
ADD COLUMN updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW();
|
|
|
|
UPDATE library_collection_libraries lcl
|
|
SET
|
|
group_id = (
|
|
SELECT g.id
|
|
FROM library_collection_groups g
|
|
WHERE g.library_id = lcl.library_id
|
|
AND g.label = lc.group_label
|
|
LIMIT 1
|
|
),
|
|
sort_order = lc.sort_order
|
|
FROM library_collections lc
|
|
WHERE lc.id = lcl.collection_id;
|
|
|
|
WITH ranked AS (
|
|
SELECT
|
|
collection_id,
|
|
library_id,
|
|
ROW_NUMBER() OVER (
|
|
PARTITION BY library_id, group_id
|
|
ORDER BY sort_order ASC, collection_id ASC
|
|
) - 1 AS pos
|
|
FROM library_collection_libraries
|
|
)
|
|
UPDATE library_collection_libraries lcl
|
|
SET sort_order = ranked.pos
|
|
FROM ranked
|
|
WHERE ranked.collection_id = lcl.collection_id
|
|
AND ranked.library_id = lcl.library_id;
|
|
|
|
ALTER TABLE library_collection_libraries
|
|
ADD CONSTRAINT library_collection_libraries_group_library_fkey
|
|
FOREIGN KEY (group_id, library_id)
|
|
REFERENCES library_collection_groups (id, library_id)
|
|
ON DELETE SET NULL (group_id);
|
|
|
|
CREATE INDEX idx_library_collection_libraries_group_order
|
|
ON library_collection_libraries (library_id, group_id, sort_order, collection_id);
|
|
|
|
ALTER TABLE media_folders
|
|
ADD COLUMN collection_ungrouped_sort_order INTEGER NOT NULL DEFAULT 9999;
|
|
|
|
ALTER TABLE user_collection_groups
|
|
ADD COLUMN id TEXT,
|
|
ADD COLUMN name TEXT,
|
|
ADD COLUMN slug TEXT,
|
|
ADD COLUMN default_sort_mode TEXT NOT NULL DEFAULT 'manual';
|
|
|
|
WITH stale_labels AS (
|
|
SELECT DISTINCT user_id, group_label AS label
|
|
FROM user_personal_collections
|
|
WHERE COALESCE(group_label, '') <> ''
|
|
AND NOT EXISTS (
|
|
SELECT 1
|
|
FROM user_collection_groups g
|
|
WHERE g.user_id = user_personal_collections.user_id
|
|
AND g.label = user_personal_collections.group_label
|
|
)
|
|
),
|
|
numbered AS (
|
|
SELECT
|
|
s.user_id,
|
|
s.label,
|
|
ROW_NUMBER() OVER (PARTITION BY s.user_id ORDER BY s.label) - 1 AS offset
|
|
FROM stale_labels s
|
|
),
|
|
max_order AS (
|
|
SELECT user_id, COALESCE(MAX(sort_order), -1) AS max_sort_order
|
|
FROM user_collection_groups
|
|
GROUP BY user_id
|
|
)
|
|
INSERT INTO user_collection_groups (user_id, label, title, sort_order)
|
|
SELECT
|
|
n.user_id,
|
|
n.label,
|
|
n.label,
|
|
COALESCE(m.max_sort_order, -1) + 1 + n.offset
|
|
FROM numbered n
|
|
LEFT JOIN max_order m ON m.user_id = n.user_id
|
|
ON CONFLICT (user_id, label) DO NOTHING;
|
|
|
|
UPDATE user_collection_groups
|
|
SET
|
|
id = COALESCE(id, 'ucg_' || md5(user_id::text || ':' || label)),
|
|
name = COALESCE(NULLIF(name, ''), NULLIF(title, ''), label),
|
|
slug = COALESCE(
|
|
NULLIF(slug, ''),
|
|
NULLIF(regexp_replace(lower(trim(label)), '[^a-z0-9]+', '-', 'g'), ''),
|
|
'group-' || substr(md5(user_id::text || ':' || label), 1, 8)
|
|
)
|
|
WHERE id IS NULL OR name IS NULL OR slug IS NULL;
|
|
|
|
ALTER TABLE user_collection_groups
|
|
ALTER COLUMN id SET NOT NULL,
|
|
ALTER COLUMN name SET NOT NULL,
|
|
ALTER COLUMN slug SET NOT NULL,
|
|
ADD CONSTRAINT user_collection_groups_default_sort_mode_check
|
|
CHECK (default_sort_mode IN ('manual', 'name_asc', 'name_desc', 'recent', 'most_items')),
|
|
ADD CONSTRAINT user_collection_groups_user_id_unique UNIQUE (user_id, id),
|
|
ADD CONSTRAINT user_collection_groups_user_slug_unique UNIQUE (user_id, slug);
|
|
|
|
ALTER TABLE user_personal_collections
|
|
ADD COLUMN group_id TEXT;
|
|
|
|
UPDATE user_personal_collections upc
|
|
SET group_id = g.id
|
|
FROM user_collection_groups g
|
|
WHERE g.user_id = upc.user_id
|
|
AND g.label = upc.group_label
|
|
AND COALESCE(upc.group_label, '') <> '';
|
|
|
|
ALTER TABLE user_personal_collections
|
|
ADD CONSTRAINT user_personal_collections_group_fkey
|
|
FOREIGN KEY (user_id, group_id)
|
|
REFERENCES user_collection_groups (user_id, id)
|
|
ON DELETE SET NULL (group_id);
|
|
|
|
CREATE INDEX idx_user_personal_collections_group_id_order
|
|
ON user_personal_collections (user_id, group_id, sort_order);
|
|
-- +goose StatementEnd
|
|
|
|
-- +goose Down
|
|
-- +goose StatementBegin
|
|
DROP INDEX IF EXISTS idx_user_personal_collections_group_id_order;
|
|
|
|
ALTER TABLE user_personal_collections
|
|
DROP CONSTRAINT IF EXISTS user_personal_collections_group_fkey;
|
|
|
|
UPDATE user_personal_collections upc
|
|
SET group_label = COALESCE(g.label, '')
|
|
FROM user_collection_groups g
|
|
WHERE g.user_id = upc.user_id
|
|
AND g.id = upc.group_id;
|
|
|
|
ALTER TABLE user_personal_collections
|
|
DROP COLUMN IF EXISTS group_id;
|
|
|
|
ALTER TABLE user_collection_groups
|
|
DROP CONSTRAINT IF EXISTS user_collection_groups_user_slug_unique,
|
|
DROP CONSTRAINT IF EXISTS user_collection_groups_user_id_unique,
|
|
DROP CONSTRAINT IF EXISTS user_collection_groups_default_sort_mode_check,
|
|
DROP COLUMN IF EXISTS default_sort_mode,
|
|
DROP COLUMN IF EXISTS slug,
|
|
DROP COLUMN IF EXISTS name,
|
|
DROP COLUMN IF EXISTS id;
|
|
|
|
ALTER TABLE media_folders
|
|
DROP COLUMN IF EXISTS collection_ungrouped_sort_order;
|
|
|
|
DROP INDEX IF EXISTS idx_library_collection_libraries_group_order;
|
|
|
|
ALTER TABLE library_collection_libraries
|
|
DROP CONSTRAINT IF EXISTS library_collection_libraries_group_library_fkey;
|
|
|
|
UPDATE library_collections lc
|
|
SET group_label = COALESCE(g.label, '')
|
|
FROM library_collection_libraries lcl
|
|
LEFT JOIN library_collection_groups g
|
|
ON g.id = lcl.group_id
|
|
AND g.library_id = lcl.library_id
|
|
WHERE lcl.collection_id = lc.id
|
|
AND lc.library_id = lcl.library_id;
|
|
|
|
ALTER TABLE library_collection_libraries
|
|
DROP COLUMN IF EXISTS updated_at,
|
|
DROP COLUMN IF EXISTS created_at,
|
|
DROP COLUMN IF EXISTS sort_order,
|
|
DROP COLUMN IF EXISTS group_id;
|
|
|
|
DROP INDEX IF EXISTS idx_library_collection_groups_order;
|
|
DROP INDEX IF EXISTS idx_library_collection_groups_user_unique;
|
|
|
|
DELETE FROM library_collection_groups
|
|
WHERE kind = 'user_collections'
|
|
OR label = 'user-collections';
|
|
|
|
ALTER TABLE library_collection_groups
|
|
DROP CONSTRAINT IF EXISTS library_collection_groups_library_slug_unique,
|
|
DROP CONSTRAINT IF EXISTS library_collection_groups_id_library_unique,
|
|
DROP CONSTRAINT IF EXISTS library_collection_groups_id_unique,
|
|
DROP CONSTRAINT IF EXISTS library_collection_groups_default_sort_mode_check,
|
|
DROP CONSTRAINT IF EXISTS library_collection_groups_kind_check,
|
|
DROP COLUMN IF EXISTS default_sort_mode,
|
|
DROP COLUMN IF EXISTS kind,
|
|
DROP COLUMN IF EXISTS slug,
|
|
DROP COLUMN IF EXISTS name,
|
|
DROP COLUMN IF EXISTS id;
|
|
-- +goose StatementEnd
|