· 8 years ago · Nov 02, 2017, 12:34 PM
1
2-- Delete constraints
3USE nodeauth
4
5ALTER TABLE users_companies DROP CONSTRAINT fk_users_companies_users
6ALTER TABLE users_companies DROP CONSTRAINT fk_users_companies_companies
7ALTER TABLE employees DROP CONSTRAINT fk_employees_companies
8ALTER TABLE customers DROP CONSTRAINT fk_customers_companies
9ALTER TABLE jobs DROP CONSTRAINT fk_jobs_companies
10
11GO
12
13
14-- Delete Tables and procedures
15DROP TABLE IF EXISTS companies
16DROP TABLE IF EXISTS employees
17DROP TABLE IF EXISTS customers
18DROP TABLE IF EXISTS jobs
19
20DROP TABLE IF EXISTS users_companies
21
22DROP TABLE IF EXISTS users
23DROP TABLE IF EXISTS users_pending
24DROP TABLE IF EXISTS login_lockouts
25DROP TABLE IF EXISTS login_attempts
26DROP TABLE IF EXISTS sessions
27
28DROP PROCEDURE IF EXISTS users_pending_add
29DROP PROCEDURE IF EXISTS users_pending_move_to_users
30DROP PROCEDURE IF EXISTS users_update_reset_password_token
31DROP PROCEDURE IF EXISTS users_get
32DROP PROCEDURE IF EXISTS users_update
33DROP PROCEDURE IF EXISTS users_delete
34DROP PROCEDURE IF EXISTS companies_get
35DROP PROCEDURE IF EXISTS companies_update
36DROP PROCEDURE IF EXISTS login_attempts_add
37
38
39GO
40
41
42
43-- companies
44CREATE TABLE companies
45(
46 id_company INT PRIMARY KEY IDENTITY(1, 1) NOT NULL,
47 name NVARCHAR(255) NOT NULL,
48 logo NVARCHAR(2083),
49 email NVARCHAR(255),
50 phone_number NVARCHAR(45),
51 address NVARCHAR(255),
52 abn NVARCHAR(45),
53 created DateTime2 NOT NULL DEFAULT GETUTCDATE(),
54 updated DateTime2 NOT NULL DEFAULT GETUTCDATE()
55)
56GO
57
58
59-- employees of a company
60CREATE TABLE employees
61(
62 id_employee INT PRIMARY KEY IDENTITY(1, 1) NOT NULL,
63 id_company INT NOT NULL DEFAULT -1,
64 first_name NVARCHAR(45) NOT NULL,
65 last_name NVARCHAR(45) NOT NULL,
66 email NVARCHAR(255) NOT NULL,
67 type INT NOT NULL DEFAULT -1,
68 created DateTime2 NOT NULL DEFAULT GETUTCDATE(),
69 updated DateTime2 NOT NULL DEFAULT GETUTCDATE()
70)
71GO
72
73
74-- customers of a company
75CREATE TABLE customers
76(
77 id_customer INT PRIMARY KEY IDENTITY(1, 1) NOT NULL,
78 id_company INT NOT NULL DEFAULT -1,
79 first_name NVARCHAR(45) NOT NULL,
80 last_name NVARCHAR(45) NOT NULL,
81 email NVARCHAR(255) NOT NULL,
82 type INT NOT NULL DEFAULT -1,
83 created DateTime2 NOT NULL DEFAULT GETUTCDATE(),
84 updated DateTime2 NOT NULL DEFAULT GETUTCDATE()
85)
86GO
87
88
89-- company work to do
90CREATE TABLE jobs
91(
92 id_job INT PRIMARY KEY IDENTITY(1, 1) NOT NULL,
93 id_company INT NOT NULL DEFAULT -1,
94 title NVARCHAR(255) NOT NULL,
95 description NVARCHAR(2000),
96 created DateTime2 NOT NULL DEFAULT GETUTCDATE(),
97 updated DateTime2 NOT NULL DEFAULT GETUTCDATE()
98)
99GO
100
101
102-- ssers-Companies junction table
103CREATE TABLE users_companies
104(
105 id_user INT NOT NULL,
106 id_company INT NOT NULL,
107 is_owner BIT NOT NULL DEFAULT 0,
108 created DateTime2 NOT NULL DEFAULT GETUTCDATE()
109)
110GO
111
112
113-- actual users (ie. company owners/admins)
114CREATE TABLE users
115(
116 id_user INT PRIMARY KEY IDENTITY(1, 1) NOT NULL,
117 first_name NVARCHAR(45) NOT NULL,
118 last_name NVARCHAR(45) NOT NULL,
119 email NVARCHAR(255) NOT NULL UNIQUE,
120 password NVARCHAR(60) NOT NULL,
121 type INT NOT NULL DEFAULT -1,
122 jwt NVARCHAR(500),
123 reset_password_token NVARCHAR(50),
124 created DateTime2 NOT NULL DEFAULT GETUTCDATE(),
125 updated DateTime2 NOT NULL DEFAULT GETUTCDATE()
126)
127GO
128
129
130-- users that are pending email verification
131CREATE TABLE users_pending
132(
133 id_pending_user INT PRIMARY KEY IDENTITY(1, 1) NOT NULL,
134 first_name NVARCHAR(45) NOT NULL,
135 last_name NVARCHAR(45) NOT NULL,
136 email NVARCHAR(255) NOT NULL UNIQUE, -- email is here to ensure uniqueness
137 password NVARCHAR(60) NOT NULL,
138 type INT NOT NULL DEFAULT -1,
139 verification_token NVARCHAR(50), -- random alphanumeric string. Also here for resending verification email
140 created DateTime2 NOT NULL DEFAULT GETUTCDATE()
141)
142GO
143
144
145-- mssql session table
146CREATE TABLE sessions
147(
148 sid VARCHAR(255) NOT NULL PRIMARY KEY,
149 session VARCHAR(MAX) NOT NULL,
150 expires DateTime NOT NULL
151)
152GO
153
154
155-- ip addresses that attempted too many logins
156CREATE TABLE login_lockouts
157(
158 email NVARCHAR(255) NOT NULL,
159 ip_address NVARCHAR(45) NOT NULL,
160 created DateTime2 NOT NULL DEFAULT GETUTCDATE()
161)
162GO
163
164
165-- track login attempts
166CREATE TABLE login_attempts
167(
168 email NVARCHAR(255) NOT NULL,
169 ip_address NVARCHAR(45) NOT NULL,
170 created DateTime2 NOT NULL DEFAULT GETUTCDATE()
171)
172GO
173
174
175
176
177-- Foreign keys and indexes
178ALTER TABLE employees ADD CONSTRAINT fk_employees_companies FOREIGN KEY (id_company) REFERENCES companies (id_company)
179ALTER TABLE customers ADD CONSTRAINT fk_customers_companies FOREIGN KEY (id_company) REFERENCES companies (id_company)
180ALTER TABLE jobs ADD CONSTRAINT fk_jobs_companies FOREIGN KEY (id_company) REFERENCES companies (id_company)
181
182ALTER TABLE users_companies ADD CONSTRAINT fk_users_companies_users FOREIGN KEY (id_user) REFERENCES users (id_user)
183ALTER TABLE users_companies ADD CONSTRAINT fk_users_companies_companies FOREIGN KEY (id_company) REFERENCES companies (id_company)
184
185CREATE NONCLUSTERED INDEX i_users_companies ON users_companies (id_user)
186
187GO
188
189
190
191
192-- ------------------- Stored Procedures -------------------
193GO
194
195-- User create in the pending table
196CREATE PROCEDURE users_pending_add
197 @first_name NVARCHAR(45),
198 @last_name NVARCHAR(45),
199 @email NVARCHAR(255),
200 @password NVARCHAR(60),
201 @type INT,
202 @verification_token NVARCHAR(500) AS
203
204 DECLARE @userEmail NVARCHAR(255)
205
206 SELECT TOP(1) @userEmail = email FROM users WHERE email = @email
207 IF @userEmail IS NOT NULL THROW 50000, 'Account already taken', 1
208
209 SELECT TOP(1) @userEmail = email FROM users_pending WHERE email = @email
210 IF @userEmail IS NOT NULL THROW 50000, 'Account already taken', 1
211
212 SET NOCOUNT ON
213 INSERT INTO users_pending (first_name, last_name, password, email, type, verification_token)
214 VALUES (@first_name, @last_name, @password, @email, @type, @verification_token)
215GO
216
217
218-- User move from pending table to actual table
219CREATE PROCEDURE users_pending_move_to_users
220 @email NVARCHAR(255),
221 @verification_token NVARCHAR(50) AS
222
223 SET NOCOUNT ON
224
225 DECLARE @userEmail NVARCHAR(255)
226 SELECT TOP(1) @userEmail = email FROM users_pending WHERE verification_token = @verification_token
227
228 IF @userEmail IS NULL THROW 50000, 'Account not found', 1
229 IF @userEmail <> @email THROW 50000, 'Email does not match', 1
230
231 DECLARE @newCompanyId INT
232 DECLARE @newUserId INT
233
234 BEGIN TRANSACTION
235 -- create company for user
236 INSERT INTO companies (name) VALUES ('My Company')
237 SET @newCompanyId = SCOPE_IDENTITY()
238
239 -- copy user into actual table
240 INSERT INTO users (first_name, last_name, email, password, type)
241 SELECT first_name, last_name, email, password, type FROM users_pending
242 WHERE email = @userEmail
243
244 SET @newUserId = SCOPE_IDENTITY()
245
246 INSERT INTO users_companies (id_user, id_company, is_owner) VALUES (@newUserId, @newCompanyId, 1)
247
248 -- remove user from pending table
249 DELETE FROM users_pending WHERE email = @email
250 COMMIT
251GO
252
253
254-- User reset password token
255CREATE PROCEDURE users_update_reset_password_token
256 @password NVARCHAR(255),
257 @reset_password_token NVARCHAR(50) AS
258
259 UPDATE users SET password = @password, reset_password_token = NULL WHERE reset_password_token = @reset_password_token
260GO
261
262
263-- User add a new login attempt and lock out ip_address
264CREATE PROCEDURE login_attempts_add
265 @email NVARCHAR(255) ,
266 @ip_address NVARCHAR(45) AS
267
268 DECLARE @user_is_locked BIT = 0
269
270 BEGIN TRANSACTION
271 -- add new attempt
272 SET NOCOUNT ON
273 INSERT INTO login_attempts (email, ip_address) VALUES (@email, @ip_address)
274
275 -- more than 5 attempts in the last 1 minute
276 SET NOCOUNT OFF
277 IF (SELECT COUNT(*) FROM login_attempts WHERE ip_address = @ip_address AND created > DATEADD(MINUTE, -1, GETUTCDATE())) > 5
278 INSERT INTO login_lockouts VALUES (@email, @ip_address, GETUTCDATE())
279 COMMIT
280GO
281
282
283-- User get
284-- must pass in id or email
285-- tableToLookIn = pending, actual, pendingFirst, actualFirst, all = pendingFirst
286CREATE PROCEDURE users_get
287 @id_user INT = -1,
288 @email NVARCHAR(255) = '',
289 @tableToLookIn NVARCHAR(255) = 'all' AS
290
291 IF @id_user = -1 AND @email = '' THROW 50000, 'Must provide id or email', 1
292
293 SET NOCOUNT ON
294
295 IF @tableToLookIn = 'pending' AND @id_user > 0
296 SELECT * FROM users_pending WHERE id_pending_user = @id_user
297
298 ELSE IF @tableToLookIn = 'pending' AND @email <> ''
299 SELECT * FROM users_pending WHERE email = @email
300
301 ELSE IF @tableToLookIn = 'actual' AND @id_user > 0
302 SELECT * FROM users WHERE id_user = @id_user
303
304 ELSE IF @tableToLookIn = 'actual' AND @email <> ''
305 SELECT * FROM users WHERE email = @email
306
307 ELSE IF (@tableToLookIn = 'pendingFirst' OR @tableToLookIn = 'all') AND @id_user > 0
308 BEGIN
309 IF EXISTS (SELECT TOP(1) id_pending_user FROM users_pending WHERE id_pending_user = @id_user)
310 SELECT * FROM users_pending WHERE id_pending_user = @id_user
311 ELSE
312 SELECT * FROM users WHERE id_user = @id_user
313 END
314
315 ELSE IF (@tableToLookIn = 'pendingFirst' OR @tableToLookIn = 'all') AND @email <> ''
316 BEGIN
317 IF EXISTS (SELECT TOP(1) id_pending_user FROM users_pending WHERE email = @email)
318 SELECT * FROM users_pending WHERE email = @email
319 ELSE
320 SELECT * FROM users WHERE email = @email
321 END
322
323 ELSE IF @tableToLookIn = 'actualFirst' AND @id_user > 0
324 BEGIN
325 IF EXISTS (SELECT TOP(1) id_user FROM users WHERE id_user = @id_user)
326 SELECT * FROM users WHERE id_user = @id_user
327 ELSE
328 SELECT * FROM users_pending WHERE id_pending_user = @id_user
329 END
330
331 ELSE IF @tableToLookIn = 'actualFirst' AND @email <> ''
332 BEGIN
333 IF EXISTS (SELECT TOP(1) id_user FROM users WHERE email = @email)
334 SELECT * FROM users WHERE email = @email
335 ELSE
336 SELECT * FROM users_pending WHERE email = @email
337 END
338GO
339
340
341-- User update
342CREATE PROCEDURE users_update
343 @id_user INT,
344 @first_name NVARCHAR(45),
345 @last_name NVARCHAR(45) AS
346
347 UPDATE users SET first_name = @first_name, last_name = @last_name WHERE id_user = @id_user
348GO
349
350
351-- User delete
352CREATE PROCEDURE users_delete
353 @id_user INT AS
354
355 DECLARE @userIsOwner as BIT
356 DECLARE @id_company as INT
357
358 SELECT @id_company = id_company, @userIsOwner = is_owner FROM users_companies WHERE id_user = @id_user
359
360 BEGIN TRANSACTION
361 IF @userIsOwner = 1
362 BEGIN
363 DELETE FROM employees WHERE id_company = @id_company
364 DELETE FROM customers WHERE id_company = @id_company
365 DELETE FROM jobs WHERE id_company = @id_company
366 DELETE FROM users_companies WHERE id_user = @id_user
367 DELETE FROM users WHERE id_user = @id_user
368 DELETE FROM companies WHERE id_company = @id_company
369 END
370
371 IF @userIsOwner = 0
372 BEGIN
373 DELETE FROM users_companies WHERE id_user = @id_user
374 DELETE FROM users WHERE id_user = @id_user
375 END
376 COMMIT
377GO
378
379
380
381
382-- Company get
383CREATE PROCEDURE companies_get
384 @id_user INT AS
385
386 SET NOCOUNT ON
387
388 DECLARE @id_company INT
389 DECLARE @is_owner BIT
390 SELECT @id_company = id_company, @is_owner = is_owner FROM users_companies WHERE id_user = @id_user
391
392 SELECT *, is_owner = @is_owner
393 FROM companies WHERE id_company = @id_company
394GO
395
396
397-- Company update
398CREATE PROCEDURE companies_update
399 @id_company INT,
400 @name NVARCHAR(255),
401 @logo NVARCHAR(2083),
402 @email NVARCHAR(255),
403 @phone_number NVARCHAR(45),
404 @address NVARCHAR(255),
405 @abn NVARCHAR(45) AS
406
407 UPDATE companies SET
408 name = @name,
409 logo = @logo,
410 email = @email,
411 phone_number = @phone_number,
412 address = @address,
413 abn = @abn
414 WHERE id_company = @id_company
415GO