NotAShelf
f34c78b238
Signed-off-by: NotAShelf <raf@notashelf.dev> Change-Id: Id99798df6f7e4470caae8a193c2654aa6a6a6964
83 lines
3.3 KiB
SQL
83 lines
3.3 KiB
SQL
-- V17: Enhanced Sharing System
|
|
-- Replaces simple share_links with comprehensive sharing capabilities
|
|
|
|
-- Enhanced shares table
|
|
CREATE TABLE shares (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
target_type TEXT NOT NULL CHECK (target_type IN ('media', 'collection', 'tag', 'saved_search')),
|
|
target_id TEXT NOT NULL,
|
|
owner_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
recipient_type TEXT NOT NULL CHECK (recipient_type IN ('public_link', 'user', 'group', 'federated')),
|
|
recipient_user_id TEXT REFERENCES users(id) ON DELETE CASCADE,
|
|
recipient_group_id TEXT,
|
|
recipient_federated_handle TEXT,
|
|
recipient_federated_server TEXT,
|
|
public_token TEXT UNIQUE,
|
|
public_password_hash TEXT,
|
|
perm_view BOOLEAN NOT NULL DEFAULT TRUE,
|
|
perm_download BOOLEAN NOT NULL DEFAULT FALSE,
|
|
perm_edit BOOLEAN NOT NULL DEFAULT FALSE,
|
|
perm_delete BOOLEAN NOT NULL DEFAULT FALSE,
|
|
perm_reshare BOOLEAN NOT NULL DEFAULT FALSE,
|
|
perm_add BOOLEAN NOT NULL DEFAULT FALSE,
|
|
note TEXT,
|
|
expires_at TIMESTAMPTZ,
|
|
access_count BIGINT NOT NULL DEFAULT 0,
|
|
last_accessed TIMESTAMPTZ,
|
|
inherit_to_children BOOLEAN NOT NULL DEFAULT TRUE,
|
|
parent_share_id TEXT REFERENCES shares(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMPTZ NOT NULL,
|
|
updated_at TIMESTAMPTZ NOT NULL,
|
|
UNIQUE(owner_id, target_type, target_id, recipient_type, recipient_user_id)
|
|
);
|
|
|
|
CREATE INDEX idx_shares_owner ON shares(owner_id);
|
|
CREATE INDEX idx_shares_recipient_user ON shares(recipient_user_id);
|
|
CREATE INDEX idx_shares_target ON shares(target_type, target_id);
|
|
CREATE INDEX idx_shares_token ON shares(public_token);
|
|
CREATE INDEX idx_shares_expires ON shares(expires_at);
|
|
|
|
-- Share activity log
|
|
CREATE TABLE share_activity (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
share_id TEXT NOT NULL REFERENCES shares(id) ON DELETE CASCADE,
|
|
actor_id TEXT REFERENCES users(id) ON DELETE SET NULL,
|
|
actor_ip TEXT,
|
|
action TEXT NOT NULL,
|
|
details TEXT,
|
|
timestamp TIMESTAMPTZ NOT NULL
|
|
);
|
|
|
|
CREATE INDEX idx_share_activity_share ON share_activity(share_id);
|
|
CREATE INDEX idx_share_activity_timestamp ON share_activity(timestamp);
|
|
|
|
-- Share notifications
|
|
CREATE TABLE share_notifications (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
share_id TEXT NOT NULL REFERENCES shares(id) ON DELETE CASCADE,
|
|
notification_type TEXT NOT NULL,
|
|
is_read BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at TIMESTAMPTZ NOT NULL
|
|
);
|
|
|
|
CREATE INDEX idx_share_notifications_user ON share_notifications(user_id);
|
|
CREATE INDEX idx_share_notifications_unread ON share_notifications(user_id) WHERE is_read = FALSE;
|
|
|
|
-- Migrate existing share_links to new shares table
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'share_links') THEN
|
|
INSERT INTO shares (
|
|
id, target_type, target_id, owner_id, recipient_type,
|
|
public_token, public_password_hash, perm_view, perm_download,
|
|
access_count, expires_at, created_at, updated_at
|
|
)
|
|
SELECT
|
|
id, 'media', media_id, created_by, 'public_link',
|
|
token, password_hash, TRUE, TRUE,
|
|
view_count, expires_at, created_at, created_at
|
|
FROM share_links
|
|
ON CONFLICT DO NOTHING;
|
|
END IF;
|
|
END $$;
|