130 lines
6.4 KiB
Swift
130 lines
6.4 KiB
Swift
import Vapor
|
|
import PostgresKit
|
|
import Crypto
|
|
|
|
// MARK: Defaults for Administration
|
|
extension ManagedUser {
|
|
|
|
public static func all (on connection: any SQLDatabase) async throws -> [Self] {
|
|
return try await connection.raw("""
|
|
SELECT "id",
|
|
"email",
|
|
"full_name",
|
|
"active",
|
|
ARRAY (SELECT "role_name"
|
|
FROM "user_roles"
|
|
WHERE "user_roles"."user_id" = "users"."id") AS "roles"
|
|
FROM "users"
|
|
ORDER BY "email"
|
|
""")
|
|
.all (decoding: Self.self)
|
|
}
|
|
|
|
public static func fetch (_ id: UUID, on connection: any SQLDatabase) async throws -> Self? {
|
|
return try await connection.raw("""
|
|
SELECT "id",
|
|
"email",
|
|
"full_name",
|
|
"active",
|
|
"password",
|
|
ARRAY (SELECT "role_name"
|
|
FROM "user_roles"
|
|
WHERE "user_roles"."user_id" = "users"."id") AS "roles"
|
|
FROM "users"
|
|
WHERE "id" = \(bind: id)
|
|
""")
|
|
.first (decoding: Self.self)
|
|
}
|
|
|
|
public static func create (email: String, fullname: String, roles: [String], token: String, on connection: any SQLDatabase) async throws {
|
|
if roles.isEmpty {
|
|
try await connection.raw("""
|
|
WITH created AS (
|
|
INSERT INTO "users" (
|
|
"email",
|
|
"full_name",
|
|
"active")
|
|
VALUES (\(bind: email),
|
|
\(bind: fullname),
|
|
TRUE)
|
|
RETURNING "id"
|
|
)
|
|
INSERT INTO "user_tokens" ("user_id", "token", "realm")
|
|
SELECT "id", \(bind: token), 'invite'
|
|
FROM created
|
|
""")
|
|
.run()
|
|
} else {
|
|
try await connection.raw("""
|
|
WITH "created" AS (
|
|
INSERT INTO "users" (
|
|
"email",
|
|
"full_name",
|
|
"active")
|
|
VALUES (\(bind: email),
|
|
\(bind: fullname),
|
|
TRUE)
|
|
RETURNING "id"
|
|
),
|
|
"roles" AS (
|
|
INSERT INTO "user_roles" ("user_id", "role_name")
|
|
SELECT "id", "role_name"
|
|
FROM "created"
|
|
CROSS JOIN unnest (\(bind: roles)) AS "role_name"
|
|
)
|
|
INSERT INTO "user_tokens" ("user_id", "token", "realm")
|
|
SELECT "id", \(bind: token), 'invite'
|
|
FROM "created"
|
|
""")
|
|
.run()
|
|
}
|
|
}
|
|
|
|
public static func save (id: UUID, email: String, fullname: String, roles: [String], isActive: Bool, on connection: any SQLDatabase) async throws {
|
|
try await connection.raw ("""
|
|
WITH "update_user" AS (
|
|
UPDATE "users"
|
|
SET "email" = \(bind: email),
|
|
"full_name" = \(bind: fullname),
|
|
"active" = \(bind: isActive)
|
|
WHERE "id" = \(bind: id)
|
|
),
|
|
"desired_roles" AS (
|
|
SELECT "role_name"
|
|
FROM unnest (\(bind: roles)) "role_name"
|
|
),
|
|
"current_roles" AS (
|
|
SELECT "role_name"
|
|
FROM "user_roles"
|
|
WHERE "user_id" = \(bind: id)
|
|
),
|
|
"deleting_roles" AS (
|
|
DELETE FROM "user_roles"
|
|
WHERE "user_id" = \(bind: id)
|
|
AND NOT EXISTS (SELECT *
|
|
FROM "desired_roles"
|
|
WHERE "user_roles"."role_name" = "desired_roles"."role_name")
|
|
)
|
|
INSERT INTO "user_roles" ("user_id", "role_name")
|
|
SELECT \(bind: id), "name"
|
|
FROM "roles"
|
|
WHERE EXISTS (SELECT *
|
|
FROM "desired_roles"
|
|
WHERE "role_name" = "name")
|
|
AND NOT EXISTS (SELECT *
|
|
FROM "user_roles"
|
|
WHERE "user_id" = \(bind: id)
|
|
AND "role_name" = "name")
|
|
""")
|
|
.run()
|
|
}
|
|
|
|
public static func store (token: String, userId: UUID, on connection: any SQLDatabase) async throws {
|
|
try await connection.raw("""
|
|
INSERT INTO "user_tokens" ("user_id", "token", "realm")
|
|
VALUES (\(bind: userId), \(bind: token), 'forgot')
|
|
""")
|
|
.run()
|
|
}
|
|
}
|