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
106
107
108
109
|
create type privacy as enum ('private', 'unlisted', 'public');
create type file_type as enum ('image', 'video', 'audio', 'text', 'document', 'site', 'other');
create table users (
id integer primary key generated always as identity,
username varchar(32) not null,
unique(id, 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 integer primary key generated always as identity,
idname varchar(256),
unique(id, idname),
name varchar(256),
description text,
privacy privacy
);
create table board_ownership (
board_id integer not null,
user_id integer 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 integer primary key generated always as identity,
idname varchar(256),
unique(id, idname),
subject varchar(256),
notes text,
privacy privacy
);
create table pin_ownership (
pin_id integer not null,
user_id integer 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 integer not null,
board_id integer 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 integer primary key generated always as identity,
idname varchar(256),
unique(id, idname),
title varchar(256),
author varchar(256),
url varchar(256),
iban varchar(13),
page varchar(256),
location varchar(256)
);
create table pin_sources (
source_id integer not null,
pin_id integer 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 integer primary key generated always as identity,
idname varchar(256),
unique(id, idname),
thumbnail varchar(256),
path varchar(256) not null,
title varchar(256),
type file_type,
alt_text text,
pin_id integer 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 integer not null,
foreign key (tag) references tags(tag),
foreign key (pin_id) references pins(id),
primary key (tag, pin_id)
);
|
