-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathcreatetable.sql
More file actions
46 lines (46 loc) · 1.41 KB
/
Copy pathcreatetable.sql
File metadata and controls
46 lines (46 loc) · 1.41 KB
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
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE users (
id text PRIMARY KEY,
name text NOT NULL,
email text NOT NULL,
created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_seen timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE item (
id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
title text NOT NULL,
note text,
color text DEFAULT 'default',
completed boolean NOT NULL DEFAULT false,
is_active boolean NOT NULL DEFAULT true,
position numeric,
created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
item_list_id uuid REFERENCES item_list(id)
);
CREATE TABLE item_list (
id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
name text NOT NULL,
created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
user_id text REFERENCES users(id)
);
CREATE TABLE tag (
id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
name text NOT NULL,
user_id text REFERENCES users(id),
shared_to text REFERENCES users(id),
UNIQUE (name, user_id)
);
CREATE TABLE item_list_tag (
item_list_id uuid REFERENCES item_list(id),
tag_id uuid REFERENCES tag(id),
PRIMARY KEY (item_list_id, tag_id)
);
CREATE OR REPLACE VIEW user_tag AS
SELECT t.id AS tag_id,
u.id AS user_id,
l.id AS item_list_id
FROM tag t,
users u,
item_list l
WHERE (t.name = u.email);