48 lines
1.4 KiB
SQL
48 lines
1.4 KiB
SQL
create extension if not exists "uuid-ossp";
|
|
|
|
create table artists (
|
|
artist_id integer primary key generated always as identity,
|
|
handle varchar(128) not null unique,
|
|
name varchar(128),
|
|
bio text,
|
|
site varchar(256)
|
|
);
|
|
|
|
create table artworks (
|
|
artwork_id integer primary key generated always as identity,
|
|
title varchar(256),
|
|
description text,
|
|
url_source varchar(256),
|
|
created_at timestamp not null default current_timestamp,
|
|
artist_id integer not null,
|
|
comment_number integer not null default 0,
|
|
foreign key (artist_id) references artists(artist_id)
|
|
);
|
|
|
|
create table comments (
|
|
comment_id integer unique not null,
|
|
text text not null,
|
|
artwork_id integer not null,
|
|
created_at timestamp not null default current_timestamp,
|
|
primary key (comment_id, artwork_id),
|
|
foreign key (artwork_id) references artworks(artwork_id)
|
|
);
|
|
|
|
create table comment_relations (
|
|
artwork_id integer,
|
|
foreign key (artwork_id) references artworks(artwork_id),
|
|
in_reply_to_id integer,
|
|
foreign key (in_reply_to_id) references comments(comment_id),
|
|
comment_id integer,
|
|
foreign key (comment_id) references comments(comment_id),
|
|
primary key (artwork_id, in_reply_to_id, comment_id)
|
|
);
|
|
|
|
create table artwork_files (
|
|
file_id uuid primary key default gen_random_uuid(),
|
|
alt_text text,
|
|
extension varchar(16),
|
|
artwork_id integer,
|
|
foreign key (artwork_id) references artworks(artwork_id)
|
|
);
|