ytresponse.ca/database at master · ComputerMachine/ytresponse.ca · GitHub

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
/* db layout */

CREATE DATABASE ytresponse;
CREATE USER ytresponse_user WITH PASSWORD 'she sells seashells on the seashore';

GRANT ALL PRIVILEGES ON DATABASE ytresponse TO ytresponse_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO ytresponse_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO ytresponse_user;


CREATE TABLE yt_group (
    id SERIAL PRIMARY KEY,
    permission SMALLINT,
    name VARCHAR(128),
    description VARCHAR(1028)
);
CREATE TABLE yt_user (
    id SERIAL PRIMARY KEY,
    username VARCHAR(128),
    password BYTEA,
    group_id SMALLINT REFERENCES yt_group (id),
    email VARCHAR(128),
    date TIMESTAMP WITHOUT TIME ZONE,
    ip VARCHAR(20)
);
CREATE TABLE yt_video (
    id SERIAL,
    video_id VARCHAR(11),
    video_start INT,
    video_end INT,
    autoplay SMALLINT,
    title VARCHAR(256),
    script TEXT,
    author_id SMALLINT REFERENCES yt_user (id),
    date TIMESTAMP WITHOUT TIME ZONE
);

/* Insert default groups */
/*
    permission id | authorization
    1            admin
    2            moderator
    3            regular user
    4            non validated user
    5            banned user
*/

INSERT INTO yt_group(permission, name, description) VALUES (
    1,
    'admin',
    'Elevated privileges, able to modify or delete any YT response and ban users.'
);
INSERT INTO yt_group(permission, name, description) VALUES (
    2,
    'moderator',
    'Elevated privileges, able to modify or delete any YT response and ban users.'
);
INSERT INTO yt_group(permission, name, description) VALUES (
    3,
    'user',
    'Create, modify and delete YT responses that belong to this user.'
);
INSERT INTO yt_group(permission, name, description) VALUES (
    4,
    'non-validated user',
    'This fella can''t do jack shit yet.'
);
INSERT INTO yt_group(permission, name, description) VALUES (
    5,
    'banned',
    'This fella can''t do jack shit.'
);


url routes:

1. custom url routing
localhost:3000/#{username}/#{custom_url}
localhost:3000/tazg2000/jonglaser

2. generate a unique id for every response
localhost:3000/v/#{uniqueId}