1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
|
create type privacy as enum ('private', 'unlisted', 'public');
create type file_type as enum ('image', 'video', 'audio', 'text', 'document', 'site', 'other');
create extension if not exists "uuid-ossp";
create table users (
id uuid primary key default gen_random_uuid(),
username varchar(32) not null,
unique(username),
password varchar(128) not null,
email varchar(128),
bio text,
site varchar(128),
privacy privacy not null default 'public',
admin boolean not null default true
);
-- create table sessions (
-- id varchar(128) primary key,
-- expires timestamp with time zone,
-- user_id integer not null,
-- foreign key (user_id) references users(id)
-- );
create table boards (
id uuid primary key default gen_random_uuid(),
idname varchar(256),
name varchar(256),
description text,
privacy privacy
);
create table board_ownership (
board_id uuid not null,
user_id uuid not null,
foreign key (board_id) references boards(id),
foreign key (user_id) references users(id),
primary key (board_id, user_id)
);
create table pins (
id uuid primary key default gen_random_uuid(),
subject varchar(256),
notes text,
privacy privacy
);
create table pin_ownership (
pin_id uuid not null,
user_id uuid not null,
foreign key (pin_id) references pins(id),
foreign key (user_id) references users(id),
primary key (pin_id, user_id)
);
create table pins_boards (
pin_id uuid not null,
board_id uuid not null,
foreign key (pin_id) references pins(id),
foreign key (board_id) references boards(id),
primary key (pin_id, board_id)
);
create table sources (
id uuid primary key default gen_random_uuid(),
idname varchar(256),
title varchar(256),
author varchar(256),
url varchar(256),
iban varchar(13),
page varchar(256),
location varchar(256)
);
create table pin_sources (
source_id uuid not null,
pin_id uuid not null,
foreign key (source_id) references sources(id),
foreign key (pin_id) references pins(id),
primary key (source_id, pin_id)
);
create table files (
id uuid primary key default gen_random_uuid(),
idname varchar(256),
thumbnail varchar(256),
path varchar(256) not null,
title varchar(256),
type file_type,
alt_text text,
pin_id uuid not null,
foreign key (pin_id) references pins(id)
);
create table tags (
tag varchar(128) not null primary key
);
create table tags_pins (
tag varchar(128) not null,
pin_id uuid not null,
foreign key (tag) references tags(tag),
foreign key (pin_id) references pins(id),
primary key (tag, pin_id)
);
|
