-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdb.js
147 lines (130 loc) · 4.71 KB
/
db.js
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
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
// db.js will be where we have all our functions for
// talking to the database,
// retrieve data,
// add data or
// update data
const spicedPg = require("spiced-pg");
const database = "petition";
const username = "postgres";
const password = "postgres";
// Let's create our line of communication to the database
const db = spicedPg(
process.env.DATABASE_URL ||
`postgres:${username}:${password}@localhost:5432/${database}`
);
console.log(`[db] connecting to:${database}`);
module.exports.getSignatureData = (userId) => {
const q = "SELECT * FROM signatures WHERE user_id=$1";
const params = [userId];
return db.query(q, params);
};
module.exports.getSignatureCount = () => {
const q = "SELECT COUNT(*) FROM signatures";
return db.query(q);
};
module.exports.updateSignature = (userID, signature) => {
const q = `INSERT INTO signatures (user_id, signature)
VALUES ($1, $2)`;
const params = [userID, signature];
return db.query(q, params);
};
// INSERT the user's data in users table
module.exports.updateUserData = (
firstName,
lastName,
emailAddress,
password
) => {
const q = `INSERT INTO users (first, last, email, password)
VALUES ($1, $2, $3, $4) RETURNING id`;
const params = [firstName, lastName, emailAddress, password];
return db.query(q, params);
};
module.exports.getUserData = (email) => {
const q = `SELECT users.id, users.email, users.password, signatures.user_id
FROM users
LEFT JOIN signatures
ON users.id = signatures.user_id
WHERE email=$1`;
const params = [email];
return db.query(q, params);
};
module.exports.updateUserProfile = (userAge, userCity, userURL, userID) => {
const q = `INSERT INTO user_profiles (age, city, url, user_id)
VALUES ($1, $2, $3, $4)`;
const params = [userAge, userCity, userURL, userID];
return db.query(q, params);
};
module.exports.getAllSigners = () => {
const q = `SELECT signatures.user_id, users.first, users.last, user_profiles.age, user_profiles.city, user_profiles.url
FROM signatures
JOIN users
ON signatures.user_id = users.id
LEFT JOIN user_profiles
ON signatures.user_id = user_profiles.user_id`;
return db.query(q);
};
module.exports.getAllSignersFromCity = (city) => {
const q = `SELECT signatures.user_id, users.first, users.last, user_profiles.age, user_profiles.city, user_profiles.url
FROM signatures
JOIN users
ON signatures.user_id = users.id
JOIN user_profiles
ON signatures.user_id = user_profiles.user_id
WHERE LOWER(user_profiles.city) = LOWER($1)`;
const params = [city];
return db.query(q, params);
};
module.exports.getUserProfileData = (userId) => {
const q = `SELECT users.id, users.first, users.last, users.email, user_profiles.age, user_profiles.city, user_profiles.url, user_profiles.user_id
FROM users
LEFT JOIN user_profiles
ON users.id = user_profiles.user_id
WHERE users.id = $1`;
const params = [userId];
return db.query(q, params);
};
// UPDATE users table: first, last, email
module.exports.updateUsers = (firstName, lastName, email, userId) => {
const q = `UPDATE users
SET first = $1,
last = $2,
email = $3
WHERE id = $4`;
const params = [firstName, lastName, email, userId];
return db.query(q, params);
};
module.exports.updateUsersAndPassword = (
firstName,
lastName,
email,
password,
userId
) => {
const q = `UPDATE users
SET first = $1,
last = $2,
email = $3,
password = $4
WHERE id = $5`;
const params = [firstName, lastName, email, password, userId];
return db.query(q, params);
};
module.exports.upsertUserProfiles = (userAge, userCity, userURL, userID) => {
const q = `INSERT INTO user_profiles (age, city, url, user_id)
VALUES ($1, $2, $3, $4)
ON CONFLICT (user_id)
DO UPDATE SET age = $1, city = $2, url = $3, user_id = $4`;
const params = [userAge, userCity, userURL, userID];
return db.query(q, params);
};
module.exports.deleteSignature = (userID) => {
const q = `DELETE FROM signatures WHERE user_id = $1`;
const params = [userID];
return db.query(q, params);
};
module.exports.onlyGetUserProfileData = (userId) => {
const q = `SELECT * FROM user_profiles WHERE user_id = $1`;
const params = [userId];
return db.query(q, params);
};