mirror of https://github.com/rocboss/paopao-ce
You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
74 lines
2.4 KiB
74 lines
2.4 KiB
--------------------------------------------------------------------------------
|
|
-- contact_manager sql dml
|
|
--------------------------------------------------------------------------------
|
|
|
|
-- name: CreateContact :exec
|
|
INSERT INTO p_contact (user_id, friend_id, status, created_on) VALUES ($1, $2, $3, $4);
|
|
|
|
-- name: UpsertContact :one
|
|
INSERT INTO p_contact (user_id, friend_id, status, created_on)
|
|
VALUES ($1, $2, $3, $4)
|
|
ON CONFLICT ON CONSTRAINT idx_contact_user_friend
|
|
DO UPDATE SET is_del=0
|
|
RETURNING id, status;
|
|
|
|
-- name: FreshContactStatus :exec
|
|
UPDATE p_contact SET status=$1, modified_on=$2, is_del=0 WHERE id=$3;
|
|
|
|
-- name: CreateMessage :one
|
|
INSERT INTO p_message (sender_user_id, receiver_user_id, type, brief, content, reply_id, created_on)
|
|
VALUES ($1, $2, $3, $4, $5, $6, $7)
|
|
RETURNING id;
|
|
|
|
-- name: AddFriendMsgsUpdate :exec
|
|
UPDATE p_message
|
|
SET reply_id=$1, modified_on=$2
|
|
WHERE ((sender_user_id = $3 AND receiver_user_id = $4) OR
|
|
(sender_user_id = $4 AND receiver_user_id = $3)) AND
|
|
type = $5 AND reply_id = $6;
|
|
|
|
-- name: RejectFriendMsgsUpdate :exec
|
|
UPDATE p_message
|
|
SET reply_id=$1, modified_on=$2
|
|
WHERE sender_user_id=$3 AND receiver_user_id=$4 AND type=$5 AND reply_id=$6;
|
|
|
|
-- name: DeleteFriend :exec
|
|
UPDATE p_contact SET status=4, is_del=1, deleted_on=$1 WHERE id=$2;
|
|
|
|
-- name: DeleteFriendByIds :exec
|
|
UPDATE p_contact SET status=4, is_del=1, deleted_on=$1 WHERE id=ANY(@ids::BIGINT[]);
|
|
|
|
-- name: ListFriend :many
|
|
SELECT c.friend_id user_id,
|
|
u.username username,
|
|
u.nickname nickname,
|
|
u.avatar avatar,
|
|
u.phone phone
|
|
FROM p_contact c
|
|
JOIN p_user u
|
|
ON c.friend_id=u.id
|
|
WHERE c.user_id=$1 AND c.status=2 AND c.is_del=0
|
|
ORDER BY u.nickname ASC
|
|
LIMIT $2 OFFSET $3;
|
|
|
|
-- name: CountFriendsById :one
|
|
SELECT count(*) FROM p_contact WHERE user_id=$1 AND status=2 AND is_del=0;
|
|
|
|
-- name: GetContacts :many
|
|
SELECT id, user_id, friend_id, group_id, remark, status, is_top, is_black, notice_enable, is_del
|
|
FROM p_contact
|
|
WHERE (user_id=$1 AND friend_id=$2) OR (user_id=$2 AND friend_id=$1);
|
|
|
|
-- name: GetUserFriend :one
|
|
SELECT id, user_id, friend_id, group_id, remark, status, is_top, is_black, notice_enable, is_del
|
|
FROM p_contact
|
|
WHERE user_id=$1 AND friend_id=$2 AND is_del=0;
|
|
|
|
-- name: GetContact :one
|
|
SELECT id, user_id, friend_id, group_id, remark, status, is_top, is_black, notice_enable, is_del
|
|
FROM p_contact
|
|
WHERE user_id=$1 AND friend_id=$2;
|
|
|
|
-- name: IsFriend :one
|
|
SELECT true FROM p_contact WHERE user_id=$1 AND friend_id=$2 AND is_del=0 AND status=2;
|