otter-space-sdk/pkg/database/migrations/001_inital_database.sql
SoXX e5b8457f42
All checks were successful
Gitea Build Check / Build (push) Successful in 2m32s
Gitea Build Check / Build (pull_request) Successful in 2m33s
feat(database): set timezone in migrations
2024-08-29 15:18:23 +02:00

116 lines
3.2 KiB
SQL

-- +migrate Up
CREATE TYPE Rating AS ENUM (
'safe',
'questionable',
'explicit',
'unknown'
);
CREATE TYPE TagType AS ENUM (
'general',
'species',
'character',
'artist',
'lore',
'meta',
'invalid',
'copyright'
);
CREATE TABLE "Post"
(
id CHAR(25) PRIMARY KEY,
rating Rating,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
deleted_at TIMESTAMP WITH TIME ZONE NULL NULL
);
CREATE TABLE "Source"
(
id CHAR(25) PRIMARY KEY,
display_name TEXT NULL,
icon TEXT NULL,
domain TEXT NOT NULL UNIQUE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
deleted_at TIMESTAMP WITH TIME ZONE NULL
);
CREATE TABLE "Tag"
(
name TEXT PRIMARY KEY,
tag_type TagType,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
deleted_at TIMESTAMP WITH TIME ZONE NULL
);
CREATE TABLE "User"
(
id TEXT PRIMARY KEY,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
deleted_at TIMESTAMP WITH TIME ZONE NULL
);
CREATE TABLE "PostReference"
(
post_id TEXT REFERENCES "Post" (id),
source_id TEXT REFERENCES "Source" (id),
url TEXT NOT NULL,
full_file_url TEXT,
preview_file_url TEXT,
sample_file_url TEXT,
source_post_id TEXT,
PRIMARY KEY (post_id, source_id, url)
);
CREATE TABLE "TagAlias"
(
name TEXT PRIMARY KEY,
tag_id TEXT REFERENCES "Tag" (name),
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE "TagGroup"
(
name TEXT PRIMARY KEY,
tag_id TEXT REFERENCES "Tag" (name),
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE "UserSource"
(
id CHAR(25) PRIMARY KEY,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
deleted_at TIMESTAMP WITH TIME ZONE NULL NULL,
user_id TEXT REFERENCES "User" (id),
source_id TEXT REFERENCES "Source" (id),
scrape_time_interval TEXT,
account_username TEXT,
account_id TEXT,
last_scrape_time TIMESTAMP WITH TIME ZONE,
account_validate BOOL DEFAULT FALSE,
account_validation_key CHAR(25),
UNIQUE (source_id, account_username, account_id)
);
CREATE TABLE "UserFavorites"
(
id CHAR(25) PRIMARY KEY,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
deleted_at TIMESTAMP WITH TIME ZONE NULL NULL,
user_id TEXT REFERENCES "User" (id),
post_id TEXT REFERENCES "Post" (id),
user_source_id CHAR(25) REFERENCES "UserSource" (id)
);
CREATE TABLE "post_tags"
(
post_id TEXT REFERENCES "Post" (id),
tag_name TEXT REFERENCES "Tag" (name),
PRIMARY KEY (post_id, tag_name)
);