215 lines
4.4 KiB
SQL
215 lines
4.4 KiB
SQL
-- name: CreateCustomer :exec
|
|
INSERT INTO customer (
|
|
id
|
|
,max_nkode_len
|
|
,min_nkode_len
|
|
,distinct_sets
|
|
,distinct_attributes
|
|
,lock_out
|
|
,expiration
|
|
,attribute_values
|
|
,set_values
|
|
,last_renew
|
|
,created_at
|
|
)
|
|
VALUES (?,?,?,?,?,?,?,?,?,?,?);
|
|
|
|
-- name: CreateUser :exec
|
|
INSERT INTO user (
|
|
id
|
|
,email
|
|
,renew
|
|
,refresh_token
|
|
,customer_id
|
|
,code
|
|
,mask
|
|
,attributes_per_key
|
|
,number_of_keys
|
|
,alpha_key
|
|
,set_key
|
|
,pass_key
|
|
,mask_key
|
|
,salt
|
|
,max_nkode_len
|
|
,idx_interface
|
|
,svg_id_interface
|
|
,created_at
|
|
)
|
|
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);
|
|
|
|
-- name: AddSvg :exec
|
|
INSERT INTO svg_icon (svg) VALUES (?);
|
|
|
|
-- name: UpdateUser :exec
|
|
UPDATE user
|
|
SET renew = ?
|
|
,refresh_token = ?
|
|
,code = ?
|
|
,mask = ?
|
|
,attributes_per_key = ?
|
|
,number_of_keys = ?
|
|
,alpha_key = ?
|
|
,set_key = ?
|
|
,pass_key = ?
|
|
,mask_key = ?
|
|
,salt = ?
|
|
,max_nkode_len = ?
|
|
,idx_interface = ?
|
|
,svg_id_interface = ?
|
|
WHERE email = ? AND customer_id = ?;
|
|
|
|
-- name: UpdateUserInterface :exec
|
|
UPDATE user SET idx_interface = ?, last_login = ? WHERE id = ?;
|
|
|
|
-- name: UpdateUserRefreshToken :exec
|
|
UPDATE user SET refresh_token = ? WHERE id = ?;
|
|
|
|
-- name: RenewCustomer :exec
|
|
UPDATE customer
|
|
SET attribute_values = ?, set_values = ?
|
|
WHERE id = ?;
|
|
|
|
-- name: RenewUser :exec
|
|
UPDATE user
|
|
SET alpha_key = ?, set_key = ?, renew = ?
|
|
WHERE id = ?;
|
|
|
|
-- name: RefreshUserPasscode :exec
|
|
UPDATE user
|
|
SET
|
|
renew = ?
|
|
,code = ?
|
|
,mask = ?
|
|
,alpha_key = ?
|
|
,set_key = ?
|
|
,pass_key = ?
|
|
,mask_key = ?
|
|
,salt = ?
|
|
WHERE id = ?;
|
|
|
|
-- name: GetUserRenew :many
|
|
SELECT
|
|
id
|
|
,alpha_key
|
|
,set_key
|
|
,attributes_per_key
|
|
,number_of_keys
|
|
FROM user
|
|
WHERE customer_id = ?;
|
|
|
|
-- name: GetCustomer :one
|
|
SELECT
|
|
max_nkode_len
|
|
,min_nkode_len
|
|
,distinct_sets
|
|
,distinct_attributes
|
|
,lock_out
|
|
,expiration
|
|
,attribute_values
|
|
,set_values
|
|
FROM customer
|
|
WHERE id = ?;
|
|
|
|
-- name: GetUser :one
|
|
SELECT
|
|
id
|
|
,renew
|
|
,refresh_token
|
|
,code
|
|
,mask
|
|
,attributes_per_key
|
|
,number_of_keys
|
|
,alpha_key
|
|
,set_key
|
|
,pass_key
|
|
,mask_key
|
|
,salt
|
|
,max_nkode_len
|
|
,idx_interface
|
|
,svg_id_interface
|
|
FROM user
|
|
WHERE user.email = ? AND user.customer_id = ?;
|
|
|
|
-- name: GetSvgId :one
|
|
SELECT svg
|
|
FROM svg_icon
|
|
WHERE id = ?;
|
|
|
|
-- name: GetSvgCount :one
|
|
SELECT COUNT(*) as count FROM svg_icon;
|
|
|
|
-- name: GetUserPermissions :many
|
|
SELECT permission FROM user_permission WHERE user_id = ?;
|
|
|
|
-- name: AddUserPermission :exec
|
|
INSERT INTO user_permission (user_id, permission) VALUES (?, ?);
|
|
|
|
|
|
---------- go-oidc
|
|
|
|
-- name: GetUserClients :many
|
|
SELECT *
|
|
FROM clients
|
|
WHERE owner = ?;
|
|
|
|
-- name: GetOIDCClientByID :one
|
|
SELECT *
|
|
FROM clients
|
|
WHERE id = ?;
|
|
|
|
-- name: CreateOIDCClient :exec
|
|
INSERT INTO clients (id, name, owner)
|
|
VALUES (?, ?, ?);
|
|
|
|
-- name: CreateRedirectURI :exec
|
|
INSERT INTO client_redirects (uri, client_id)
|
|
VALUES (?, ?);
|
|
|
|
-- name: DeleteRedirectURI :exec
|
|
DELETE FROM client_redirects
|
|
WHERE uri = ? AND client_id = ?;
|
|
|
|
-- name: GetClientRedirectURIs :many
|
|
SELECT *
|
|
FROM client_redirects
|
|
WHERE client_id = ?;
|
|
|
|
-- name: GetAuthorizationCode :one
|
|
SELECT *
|
|
FROM authorization_codes
|
|
WHERE code = ?;
|
|
|
|
-- name: CreateAuthorizationCode :exec
|
|
INSERT INTO authorization_codes (code, code_challenge, code_challenge_method, user_id, client_id, scope, redirect_uri, expires_at)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?);
|
|
|
|
-- name: DeleteOldAuthCodes :exec
|
|
DELETE FROM authorization_codes
|
|
WHERE expires_at < CURRENT_TIMESTAMP;
|
|
|
|
-- name: DeleteOldTokens :exec
|
|
DELETE FROM tokens
|
|
WHERE expires_at < CURRENT_TIMESTAMP;
|
|
|
|
-- name: GetTokenByValue :one
|
|
SELECT *
|
|
FROM tokens
|
|
WHERE token_value = ?;
|
|
|
|
-- name: CreateToken :exec
|
|
INSERT INTO tokens (token_type, token_value, user_id, client_id, scope, expires_at)
|
|
VALUES (?, ?, ?, ?, ?, ?);
|
|
|
|
-- name: ApproveClient :exec
|
|
INSERT INTO client_approvals (user_id, client_id)
|
|
VALUES (?, ?);
|
|
|
|
-- name: ClientApproved :one
|
|
SELECT *
|
|
FROM client_approvals
|
|
WHERE user_id = ? AND client_id = ?;
|
|
|
|
-- name: DeleteAuthCode :exec
|
|
DELETE FROM authorization_codes
|
|
WHERE code = ?;
|