NotAShelf
f34c78b238
Signed-off-by: NotAShelf <raf@notashelf.dev> Change-Id: Id99798df6f7e4470caae8a193c2654aa6a6a6964
85 lines
3.3 KiB
SQL
85 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,
|
|
recipient_type TEXT NOT NULL CHECK (recipient_type IN ('public_link', 'user', 'group', 'federated')),
|
|
recipient_user_id TEXT,
|
|
recipient_group_id TEXT,
|
|
recipient_federated_handle TEXT,
|
|
recipient_federated_server TEXT,
|
|
public_token TEXT UNIQUE,
|
|
public_password_hash TEXT,
|
|
perm_view INTEGER NOT NULL DEFAULT 1,
|
|
perm_download INTEGER NOT NULL DEFAULT 0,
|
|
perm_edit INTEGER NOT NULL DEFAULT 0,
|
|
perm_delete INTEGER NOT NULL DEFAULT 0,
|
|
perm_reshare INTEGER NOT NULL DEFAULT 0,
|
|
perm_add INTEGER NOT NULL DEFAULT 0,
|
|
note TEXT,
|
|
expires_at TEXT,
|
|
access_count INTEGER NOT NULL DEFAULT 0,
|
|
last_accessed TEXT,
|
|
inherit_to_children INTEGER NOT NULL DEFAULT 1,
|
|
parent_share_id TEXT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (recipient_user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (parent_share_id) REFERENCES shares(id) ON DELETE CASCADE,
|
|
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,
|
|
actor_id TEXT,
|
|
actor_ip TEXT,
|
|
action TEXT NOT NULL,
|
|
details TEXT,
|
|
timestamp TEXT NOT NULL,
|
|
FOREIGN KEY (share_id) REFERENCES shares(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (actor_id) REFERENCES users(id) ON DELETE SET 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,
|
|
share_id TEXT NOT NULL,
|
|
notification_type TEXT NOT NULL,
|
|
is_read INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (share_id) REFERENCES shares(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX idx_share_notifications_user ON share_notifications(user_id);
|
|
CREATE INDEX idx_share_notifications_unread ON share_notifications(user_id, is_read) WHERE is_read = 0;
|
|
|
|
-- Migrate existing share_links to new shares table (if share_links exists)
|
|
INSERT OR IGNORE 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, 1, 1,
|
|
view_count, expires_at, created_at, created_at
|
|
FROM share_links
|
|
WHERE EXISTS (SELECT 1 FROM sqlite_master WHERE type='table' AND name='share_links');
|