42 lines
1.6 KiB
SQL
42 lines
1.6 KiB
SQL
|
-- +goose Up
|
||
|
|
-- +goose StatementBegin
|
||
|
|
CREATE TABLE ebook_reader_config (
|
||
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
|
|
profile_id TEXT NOT NULL,
|
||
|
|
content_id TEXT NOT NULL REFERENCES media_items(content_id) ON DELETE CASCADE,
|
||
|
|
config JSONB NOT NULL DEFAULT '{}'::jsonb,
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
PRIMARY KEY (user_id, profile_id, content_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX ebook_reader_config_profile_updated
|
||
|
|
ON ebook_reader_config (user_id, profile_id, updated_at DESC);
|
||
|
|
|
||
|
|
CREATE TABLE ebook_reader_annotations (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
|
|
profile_id TEXT NOT NULL,
|
||
|
|
content_id TEXT NOT NULL REFERENCES media_items(content_id) ON DELETE CASCADE,
|
||
|
|
kind TEXT NOT NULL CHECK (kind IN ('highlight', 'note', 'bookmark')),
|
||
|
|
cfi_range TEXT,
|
||
|
|
location TEXT,
|
||
|
|
selected_text TEXT NOT NULL DEFAULT '',
|
||
|
|
note TEXT NOT NULL DEFAULT '',
|
||
|
|
style TEXT NOT NULL DEFAULT 'highlight',
|
||
|
|
color TEXT NOT NULL DEFAULT '#facc15',
|
||
|
|
metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
CHECK ((kind = 'bookmark' AND location IS NOT NULL) OR (kind <> 'bookmark' AND cfi_range IS NOT NULL))
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX ebook_reader_annotations_book
|
||
|
|
ON ebook_reader_annotations (user_id, profile_id, content_id, updated_at DESC);
|
||
|
|
-- +goose StatementEnd
|
||
|
|
|
||
|
|
-- +goose Down
|
||
|
|
-- +goose StatementBegin
|
||
|
|
DROP TABLE IF EXISTS ebook_reader_annotations;
|
||
|
|
DROP TABLE IF EXISTS ebook_reader_config;
|
||
|
|
-- +goose StatementEnd
|