/
user.sql
executable file
·199 lines (144 loc) · 3.26 KB
/
user.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
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
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
-- User
-- get all entries
CREATE PROCEDURE getAll(
IN start INT,
IN limit INT,
IN status INT,
IN search CHAR,
IN email CHAR,
IN phone_number CHAR,
-- return array of user
OUT fetch_all,
-- return user count for count query
OUT fetch_one
)
BEGIN
SELECT * FROM `user` WHERE 1 = 1
@IF isset(:status) AND !empty(:status)
THEN
AND user.status = :status
END @IF
@IF isset(:email) AND !empty(:email)
THEN
AND user.email = :email
END @IF
@IF isset(:phone_number) AND !empty(:phone_number)
THEN
AND user.phone_number = :phone_number
END @IF
-- search
@IF isset(:search) AND !empty(:search)
THEN
AND user.username LIKE CONCAT('%',:search,'%') OR
user.first_name LIKE CONCAT('%',:search,'%') OR
user.last_name LIKE CONCAT('%',:search,'%')
END @IF
-- limit
@IF isset(:limit)
THEN
@SQL_LIMIT(:start, :limit)
END @IF;
-- SELECT FOUND_ROWS() as count;
SELECT count(*) FROM (
@SQL_COUNT(user.user_id, user) -- this takes previous query removes limit and replaces select columns with parameter user_id
) as count;
END
-- get user information
CREATE PROCEDURE get(
IN user CHAR,
IN email CHAR,
IN user_id INT,
IN status INT,
OUT fetch_row,
)
BEGIN
SELECT * FROM user AS _ WHERE 1 = 1
@IF isset(:username)
THEN
AND _.username = :username
END @IF
@IF isset(:email)
THEN
AND _.email = :email
END @IF
@IF isset(:user_id)
THEN
AND _.user_id = :user_id
END @IF
@IF isset(:status)
THEN
AND _.status = :status
END @IF
@IF isset(:token)
THEN
AND _.token = :token
END @IF
LIMIT 1;
END
-- Add new user
CREATE PROCEDURE add(
IN user ARRAY,
OUT insert_id
)
BEGIN
-- allow only table fields and set defaults for missing values
@FILTER(:user, user)
INSERT INTO user
( @KEYS(:user) )
VALUES ( :user );
END
-- Edit comment
CREATE PROCEDURE edit(
IN username CHAR,
IN email CHAR,
IN user_id INT,
IN user ARRAY,
OUT affected_rows
)
BEGIN
-- allow only table fields and set defaults for missing values
@FILTER(:user, user)
UPDATE user
SET @LIST(:user)
WHERE
@IF isset(:email)
THEN
email = :email
END @IF
@IF isset(:user_id)
THEN
user_id = :user_id
END @IF
@IF isset(:username)
THEN
username = :username
END @IF
END
-- delete user
PROCEDURE delete(
IN user_id ARRAY,
OUT affected_rows
)
BEGIN
DELETE FROM user WHERE user_id IN (:user_id);
END
-- Update role
CREATE PROCEDURE setRole(
IN user_id INT,
IN role CHAR,
IN role_id INT
OUT insert_id
)
BEGIN
UPDATE user
SET
@IF isset(:role_id)
THEN
role_id = :role_id
END @IF
@IF isset(:role)
THEN
role_id = (SELECT role_id FROM roles WHERE name = :role)
END @IF
WHERE user_id = :user_id
END