-
Notifications
You must be signed in to change notification settings - Fork 0
/
database.sql
62 lines (51 loc) · 2.82 KB
/
database.sql
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
CREATE DATABASE password_manager;
CREATE extension IF NOT EXISTS "uuid-ossp";
CREATE TABLE users(
user_id uuid DEFAULT
uuid_generate_v4(),
user_name VARCHAR(255) NOT NULL,
user_email VARCHAR(255) NOT NULL UNIQUE,
user_password VARCHAR(255) NOT NULL,
PRIMARY KEY (user_id)
);
CREATE TABLE passwords(
password_id SERIAL,
user_id UUID,
site_name VARCHAR(255) NOT NULL,
site_email VARCHAR(255) NOT NULL,
site_password VARCHAR(255) NOT NULL,
site_iv VARCHAR(255) NOT NULL,
PRIMARY KEY (password_id),
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
-- ADDED new Column refresh_token
ALTER TABLE users ADD refresh_token VARCHAR(255)[];
-- Tests
-- insert fake users
INSERT INTO users (user_name, user_email, user_password) VALUES ('ALEK', '[email protected]', 'password');
-- insert dummy passwords vault info:
INSERT INTO passwords (user_id, site_name, site_email, site_password, site_iv) VALUES ('fake-user-id-1', 'instagram.com', '[email protected]', 'encypted-password', 'encrypted-iv') RETURNING *;
-- Modify 'refresh_token' array
UPDATE users SET refresh_token='{jwt-token}' WHERE user_email='[email protected]'; -- Add refresh_token to database
UPDATE users SET refresh_token='{}' WHERE refresh_token=$1; -- Empty out the 'refresh_token' array for logoutAllController.js
-- Get all* user's info of all users by a matching KEY user_id:
SELECT * FROM users LEFT JOIN passwords ON users.user_id = passwords.user_id ORDER BY password_id ASC;
-- Get all* user's info of a particuler user by user_id:
SELECT * FROM users LEFT JOIN passwords ON users.user_id = passwords.user_id WHERE users.user_id='fake-user-id-1' ORDER BY password_id ASC;
-- *Either all or particular data based on needs
-- REMINDER COMMANDS:
-- \l -- all databases
-- \c password_manager -- connect to database
-- \x off -- Expanded display ON or OFF
-- \dt
-- \d+ users
-- \d+ passwords
-- CREATE extension IF NOT EXISTS "uuid-ossp"; -- install uuid inside postgres database
-- -- MIGHT ADD:
-- ON DELETE CASCADE -- is required to the FOREGIN KEY -> would allow to delete the referencing rows ('passwords' table) if I were to `DELETE FROM users WHERE user_id=$1`,
-- otherwise will fail if there is any data in `passwords` table by the matching 'user_id'.
-- Use-case scenario: `Delete account` feature -> if user wants to delete their account -> their referencing 'passwords (table) vault' should also be removed,
-- unlike my current setup won't allow to remove 'users' table's row(s) if there is 1 or more rows in the 'passwords' table (if isn't empty).
-- Modify (add) constraints without dropping table 'passwords'
-- for when table 'passwords' contains data & we want to retain the data + also add new constraints:
-- ALTER TABLE passwords DROP CONSTRAINT passwords_user_id_fkey, ADD CONSTRAINT passwords_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE;