接下來我們要處理個人及朋友的部分，包含檢查登入、取得自己的資料，以及新增、移除查尋好友...等。都會在這次的文章中完成。
範例程式碼：https://github.com/ksw2000/ironman-2024/tree/master/golang-server/whisper
目前整個系統由各個不同的 package 組成
.
└── whipser/
├── auth/
│ └── auth.go
├── users/
│ └── users.go
├── utils/
│ └── utils.go
├── go.mod
├── go.sum
└── main.go
我們可以在 auth package 中實作一個可以判斷用戶是否已登入的機制。
func CheckLogin(db *pg.DB, token string) (*users.User, error) {
user := users.User{}
_, err := db.QueryOne(&user, `SELECT users.* FROM users, auths
WHERE users.id = auths.uid
and auths.token = ?
and auths.expired_at > ?`, token, time.Now())
if err != nil {
if err == pg.ErrNoRows {
return nil, ErrorAuthenticationFailed
}
return nil, fmt.Errorf("db.QueryOne failed: %w", err)
}
return &user, nil
}
接著我們可以實作一個取得個人資料的 API
Path: /api/v1/me
Method: GET
Header:
- Authorization
Response:
success:
- 200 OK
fail:
- 401 Unauthorized 認證失敗
- 500 Internal Server Error 伺服器端發生錯誤
content:
- error string
- uid int
- user string "帳號"
- name string
- profile string "頭貼的 url"
- email string
- public_key string
router.GET("/api/v1/me", func(c *gin.Context) {
token := c.GetHeader("Authorization")
user, err := auth.CheckLogin(db, token)
if err != nil {
if err == auth.ErrorAuthenticationFailed {
c.JSON(http.StatusUnauthorized, gin.H{
"error": "驗證失敗",
})
} else {
log.Println(err)
c.JSON(http.StatusInternalServerError, gin.H{
"error": "內部伺服器錯誤",
})
}
return
}
c.JSON(http.StatusOK, gin.H{
"error": "",
"uid": user.ID,
"user": user.UserID,
"name": user.Name,
"profile": user.Profile,
"email": user.Email,
"public_key": user.PublicKey,
})
})
驗證一下：
# 先嘗試登入
> curl -X POST http://localhost:8081/api/v1/auth/login -H "Content-Type: application/json" -d "{\"user\": \"kozue\", \"password\": \"Kozukozu0615\"}"
{"error":"","token":"34rCF++rUIgMUexEMdIyk4FNHfSu7UxJofgv/ND+fBK+MTbheIRDf5b9h3t0OeE0"}
# 嘗試取得個人資料
> curl http://localhost:8081/api/v1/me -H "Authorization: 34rCF++rUIgMUexEMdIyk4FNHfSu7UxJofgv/ND+fBK+MTbheIRDf5b9h3t0OeE0"
{"email":"kozu@example.com","error":"","name":"乙宗梢","profile":"","public_key":"example_pulic_key","uid":4,"user":"kozue"}
# 嘗試胡亂輸入 token
> curl http://localhost:8081/api/v1/me -H "Authorization: 34rCF++rUIgMUexEMdIyk4FNHfSu7U"
{"error":"驗證失敗"}
當使用者更換裝置時，我們仍然能透過資料庫輔助其解鎖加密的資料，這裡的設計是將加密過的 pirvate key 及 public key 一併傳回給使用者，
Path: /api/v1/me/key
Method: Get
Header:
- Authorization
- Pin
Response:
success:
- 200 OK
fail:
- 401 Unauthorized 驗證失敗 (Token 或 pin 碼)
- 500 Internal Server Error 伺服器端發生錯誤
content:
- error string
- public_key string
- encrypted_private_key string
要取得 private key 前，我們同時要驗證其 pin 碼是否正確，pin 碼是用來解鎖 encrypted_private_key 的關鍵之一
func CheckPin(db *pg.DB, token, pin string) (publicKey, encryptedPrivateKey string, err error) {
var u *users.User
u, err = CheckLogin(db, token)
if err != nil {
return publicKey, encryptedPrivateKey, err
}
if u == nil {
panic("user should not be nil after checkLogin")
}
salt, err := base64.StdEncoding.DecodeString(u.Salt)
if err != nil {
return publicKey, encryptedPrivateKey, fmt.Errorf("base64.StdEncoding.DecodeString failed: %w", err)
}
log.Println(pin)
hashPin := utils.HashPasswordWithSalt([]byte(pin), salt)
encodedHashPin := base64.StdEncoding.EncodeToString(hashPin[:])
if u.HashPin != encodedHashPin {
return publicKey, encryptedPrivateKey, ErrorAuthenticationFailed
}
return u.PublicKey, u.EncryptedPrivateKey, nil
}
完成 CheckPin 後我們將此邏輯註冊到 router 上
router.GET("/api/v1/me/key", func(c *gin.Context) {
token := c.GetHeader("Authorization")
pin := c.GetHeader("Pin")
publicKey, encryptedPrivateKey, err := auth.CheckPin(db, token, pin)
if err != nil {
if err == auth.ErrorAuthenticationFailed {
c.JSON(http.StatusUnauthorized, gin.H{
"error": "驗證失敗",
})
} else {
log.Println(err)
c.JSON(http.StatusInternalServerError, gin.H{
"error": "內部伺服器錯誤",
})
}
return
}
c.JSON(http.StatusOK, gin.H{
"error": "",
"public_key": publicKey,
"encrypted_private_key": encryptedPrivateKey,
})
})
接著我們嘗試使用錯的 Pin 碼
> curl http://localhost:8081/api/v1/me/key -H "Authorization: 34rCF++rUIgMUexEMdIyk4FNHfSu7UxJofgv/ND+fBK+MTbheIRDf5b9h3t0OeE0" -H "Pin: 000"
{"error":"驗證失敗"}
接著嘗試使用正確的 Pin 碼
> curl http://localhost:8081/api/v1/me/key -H "Authorization: 34rCF++rUIgMUexEMdIyk4FNHfSu7UxJofgv/ND+fBK+MTbheIRDf5b9h3t0OeE0" -H "Pin: 1020615"
{"encrypted_private_key":"example_encrypted_private_key","error":"","public_key":"example_pulic_key"}
當使用者需要取得某個用戶的資料時，可以呼叫這個 API
Path: /api/v1/users/:uid
Method: GET
Response:
success:
- 200 OK
fail:
- 404 Not Found 用戶不存在
- 500 Internal Server Error 伺服器端發生錯誤
content:
- error string
- uid int
- user string
- name string
- profile string "頭貼的 url"
- public_key string
我們可以在 users package 新增一個透過 id 查找 user 的函式
func GetUserByID(db *pg.DB, id int) (user User, err error) {
err = db.Model(&user).Where("id = ?", id).Select()
if err != nil {
if err == pg.ErrNoRows {
return user, ErrorUserNotFound
}
return user, fmt.Errorf("db.Model.Where.Select failed: %w", err)
}
return user, nil
}
完成 GetUserByID 後我們將此邏輯註冊到 router 上
router.GET("/api/v1/users/:uid", func(c *gin.Context) {
id, err := strconv.Atoi(c.Param("uid"))
if err != nil {
c.JSON(http.StatusBadRequest, gin.H{
"error": "bad request",
})
}
user, err := users.GetUserByID(db, id)
if err != nil {
if err == users.ErrorUserNotFound {
c.JSON(http.StatusNotFound, gin.H{
"error": "user not found",
})
} else {
log.Println(err)
c.JSON(http.StatusInternalServerError, gin.H{
"error": "內部伺服器錯誤",
})
}
return
}
c.JSON(http.StatusOK, gin.H{
"error": "",
"uid": user.ID,
"user": user.UserID,
"name": user.Name,
"profile": user.Profile,
"public_key": user.PublicKey,
})
})
在我的資料庫上，乙宗梢對應的 uid 是 4，所以我們嘗試取得其資料
> curl http://localhost:8081/api/v1/users/4
{"error":"","name":"乙宗梢","profile":"","public_key":"example_pulic_key","uid":4,"user":"kozue"}
若我們嘗試取得不存在的 uid 時
> curl http://localhost:8081/api/v1/users/5
{"error":"user not found"}
接著我們處理朋友的部分，首先我們先建立資料庫：
create table friends (
id serial not null primary key,
inviter integer references users(id) on delete cascade,
invitee integer references users(id) on delete cascade,
accepted boolean default false,
created_at timestamptz default now(),
updated_at timestamptz default now()
);
並在 friends packaeg 中使用 go 建立對應的 struct
.
└── whipser/
├── auth/
│ └── auth.go
├── friend/
│ └── friends.go
├── users/
│ └── users.go
├── utils/
│ └── utils.go
├── go.mod
├── go.sum
└── main.go
type Friend struct {
ID int
Inviter int
Invitee int
Accepted bool
CreatedAt time.Time
UpdatedAt time.Time
}
接著按照先前的 API 開始實作好友功能，為了測試建立朋友關係，我們當然得再新增一位乙宗梢的好朋友─藤島慈
curl -X POST http://localhost:8081/api/v1/users -H "Content-Type: application/json" -d "{\"name\":\"藤島慈\", \"user\":\"megumi\", \"password\":\"Fujishima1220\", \"email\":\"megu@example.com\", \"pin\":\"1021220\", \"public_key\":\"example_pulic_key\", \"encrypted_private_key\":\"example_encrypted_private_key\"}"
{
"name": "藤島慈",
"user": "megumi",
"password": "Fujishima1220",
"email": "megu@example.com",
"pin": "1021220",
"public_key": "example_pulic_key",
"encrypted_private_key": "example_encrypted_private_key"
}
由於我們 UI 的地方沒有讓用戶選擇是否要同意好友，因此我們這裡簡化為一律同意成為好友😂
Path: /api/v1/friends
Method: POST
Header:
- Authorization
Request:
- uid int
Response:
success:
- 201 Created
fail:
- 400 Bad Request 請求格式不正確
- 401 Unauthorized 未經授權
- 404 Not Found 用戶 uid 不存在
- 409 Conflict 已經是好友了
- 500 Internal Server Error 伺服器端發生錯誤
content:
- error string
在 frineds package 開始實作 MakeFriend 函式。新增好友時，我們要檢查 邀請者 (inviter) 和 受邀者 (invitee)不能是同一人，而且這兩個 id 也必需是確實存在的，另外，如果這兩人已經是好友了，也不可以再建立好友關係。
var (
ErrorAlreadyFriends = errors.New("already friends")
ErrorUserCannotBeFriendsWithThemSelves = errors.New("user cannot be friends with themselves")
)
func MakeFriend(db *pg.DB, inviter, invitee int) error {
if inviter == invitee {
return ErrorUserCannotBeFriendsWithThemSelves
}
var err error
var n int
tx, err := db.Begin()
if err != nil {
return fmt.Errorf("db.Begin failed: %w", err)
}
defer tx.Rollback()
// 檢查 id 是否皆存在
n, err = tx.Model((*users.User)(nil)).Where("id = ?", inviter).Count()
if err != nil {
return fmt.Errorf("tx.Model.Where(id = inviter).Count failed: %w", err)
} else if n == 0 {
return users.ErrorUserNotFound
}
n, err = tx.Model((*users.User)(nil)).Where("id = ?", invitee).Count()
if err != nil {
return fmt.Errorf("tx.Model.Where(id = invitee).Count failed: %w", err)
} else if n == 0 {
return users.ErrorUserNotFound
}
// 檢查是否已是朋友
n, err = tx.Model((*Friend)(nil)).Where(
`(inviter = ? and invitee = ?) or
(inviter = ? and invitee = ?)`, inviter, invitee, invitee, inviter).Count()
if err != nil {
return fmt.Errorf("tx.Model.Where(inviter=inviter and invitee=invitee) failed: %w", err)
} else if n > 0 {
return ErrorAlreadyFriends
}
friend := Friend{
Inviter: inviter,
Invitee: invitee,
Accepted: true,
}
if _, err = tx.Model(&friend).Insert(); err != nil {
return fmt.Errorf("tx.Model.Insert failed: %w", err)
}
if err = tx.Commit(); err != nil {
return fmt.Errorf("tx.Commit failed: %w", err)
}
return nil
}
接著，我們將此邏輯加入 router
router.POST("/api/v1/friends", func(c *gin.Context) {
req := struct {
Invitee int `json:"uid" binding:"required"`
}{}
if err := c.ShouldBindJSON(&req); err != nil {
c.JSON(http.StatusBadRequest, gin.H{"error": "bad request"})
return
}
token := c.GetHeader("Authorization")
me, err := auth.CheckLogin(db, token)
if err != nil {
if err == auth.ErrorAuthenticationFailed {
c.JSON(http.StatusUnauthorized, gin.H{
"error": "驗證失敗",
})
} else {
log.Println(err)
c.JSON(http.StatusInternalServerError, gin.H{
"error": "內部伺服器錯誤",
})
}
return
}
err = friends.MakeFriend(db, me.ID, req.Invitee)
if err != nil {
if err == friends.ErrorAlreadyFriends {
c.JSON(http.StatusConflict, gin.H{
"error": "已經是好友了",
})
} else if err == friends.ErrorUserCannotBeFriendsWithThemSelves {
c.JSON(http.StatusConflict, gin.H{
"error": "不可以和自己成為好友",
})
} else if err == users.ErrorUserNotFound {
c.JSON(http.StatusNotFound, gin.H{
"error": "用戶不存在",
})
} else {
log.Println(err)
c.JSON(http.StatusInternalServerError, gin.H{
"error": "內部伺服器錯誤",
})
}
return
}
c.JSON(http.StatusOK, gin.H{
"error": "",
})
})
建立好後我們可以試著打 API：
curl -X POST http://localhost:8081/api/v1/friends -H "Content-Type: applicaton/json" -H "Authorization: 34rCF++rUIgMUexEMdIyk4FNHfSu7UxJofgv/ND+fBK+MTbheIRDf5b9h3t0OeE0" -d "{\"uid\": 5}"
{"error":""}
查看資料庫：
Path: /api/v1/friends/:uid
Method: DELETE
Header:
- Authorization
Response:
success:
- 204 No Content 刪除成功
fail:
- 401 Unauthorized 未經授權
- 500 Internal Server Error 伺服器端發生錯誤
content:
- error string
原本有設計回傳一個 404 Not Found 好友關係不存在，但後來想想覺得其實也沒有必要，反正只要下這個 API 不管原本是不是好友，我們都會去清空資料庫
func DeleteFriend(db *pg.DB, uid1, uid2 int) error {
_, err := db.Model((*Friend)(nil)).Where(
`(inviter = ? and invitee = ?) or
(inviter = ? and invitee = ?)`, uid1, uid2, uid2, uid1).Delete()
if err != nil {
return fmt.Errorf("db.Model.Where.Delete failed: %w", err)
}
return nil
}
接著我們將該邏輯加入 router 中，記得要先檢查 Token 是否有效
router.DELETE("/api/v1/friends/:uid", func(c *gin.Context) {
id, err := strconv.Atoi(c.Param("uid"))
if err != nil {
c.JSON(http.StatusBadRequest, gin.H{
"error": "bad request",
})
}
token := c.GetHeader("Authorization")
me, err := auth.CheckLogin(db, token)
if err != nil {
if err == auth.ErrorAuthenticationFailed {
c.JSON(http.StatusUnauthorized, gin.H{
"error": "驗證失敗",
})
} else {
log.Println(err)
c.JSON(http.StatusInternalServerError, gin.H{
"error": "內部伺服器錯誤",
})
}
return
}
err = friends.DeleteFriend(db, me.ID, id)
if err != nil {
log.Println(err)
c.JSON(http.StatusInternalServerError, gin.H{
"error": "內部伺服器錯誤",
})
return
}
c.JSON(http.StatusOK, gin.H{
"error": "",
})
})
完成後，我們嘗試以乙宗梢的身份刪除藤島慈這個好友
> curl -X DELETE http://localhost:8081/api/v1/friends/5 -H "Authorization: 34rCF++rUIgMUexEMdIyk4FNHfSu7UxJofgv/ND+fBK+MTbheIRDf5b9h3t0OeE0"
{"error":""}
查看資料庫
whisper=# select * from friends;
id | inviter | invitee | accepted | created_at | updated_at
----+---------+---------+----------+------------+------------
(0 rows)
Path: /api/v1/friends/:from
Method: GET
Header:
- Authorization
Response:
success:
- 200 OK
fail:
- 400 Bad Request 請求格式不正確
- 401 Unauthorized 未經授權
- 500 Internal Server Error 伺服器端發生錯誤
content:
- error string
- next bool "-1 代表無下一筆"
- list:
- uid int
- user string
- name string
- profile string
- public_key string
- channel_id int "-1 代表不存在"
為了完成這個功能，我們可以再多新增幾位用戶
curl -X POST http://localhost:8081/api/v1/users -H "Content-Type: application/json" -d "{\"name\":\"日野下花帆\", \"user\":\"kaho_chan\", \"password\":\"Hinoshita0522\", \"email\":\"kaho@example.com\", \"pin\":\"1030522\", \"public_key\":\"example_pulic_key\", \"encrypted_private_key\":\"example_encrypted_private_key\"}"
curl -X POST http://localhost:8081/api/v1/users -H "Content-Type: application/json" -d "{\"name\":\"村野さやか\", \"user\":\"sayaka\", \"password\":\"Sayaka0113\", \"email\":\"sayaka@example.com\", \"pin\":\"20080113\", \"public_key\":\"example_pulic_key\", \"encrypted_private_key\":\"example_encrypted_private_key\"}"
curl -X POST http://localhost:8081/api/v1/users -H "Content-Type: application/json" -d "{\"name\":\"ルリ\", \"user\":\"rurino\", \"password\":\"MegumiDaisuki1220\", \"email\":\"ruri@example.com\", \"pin\":\"20070831\", \"public_key\":\"example_pulic_key\", \"encrypted_private_key\":\"example_encrypted_private_key\"}"
curl -X POST http://localhost:8081/api/v1/users -H "Content-Type: application/json" -d "{\"name\":\"夕霧綴理\", \"user\":\"tuzuri\", \"password\":\"Shiomame1117\", \"email\":\"zzr@example.com\", \"pin\":\"1021117\", \"public_key\":\"example_pulic_key\", \"encrypted_private_key\":\"example_encrypted_private_key\"}"
curl -X POST http://localhost:8081/api/v1/users -H "Content-Type: application/json" -d "{\"name\":\"百生吟子\", \"user\":\"momose_ginko\", \"password\":\"MomoseMomose1020\", \"email\":\"ginko@example.com\", \"pin\":\"1041020\", \"public_key\":\"example_pulic_key\", \"encrypted_private_key\":\"example_encrypted_private_key\"}"
curl -X POST http://localhost:8081/api/v1/users -H "Content-Type: application/json" -d "{\"name\":\"徒町小鈴\", \"user\":\"kachimachi\", \"password\":\"K7M7k7m7k7m7\", \"email\":\"k7m7@example.com\", \"pin\":\"1040228\", \"public_key\":\"example_pulic_key\", \"encrypted_private_key\":\"example_encrypted_private_key\"}"
curl -X POST http://localhost:8081/api/v1/users -H "Content-Type: application/json" -d "{\"name\":\"安養寺姫芽\", \"user\":\"hime_chan\", \"password\":\"HimeChan0924\", \"email\":\"hime@example.com\", \"pin\":\"1040924\", \"public_key\":\"example_pulic_key\", \"encrypted_private_key\":\"example_encrypted_private_key\"}"
接著查看資料庫
whisper=# select id, name from users;
id | name
----+------------
4 | 乙宗梢
5 | 藤島慈
6 | 日野下花帆
7 | 村野さやか
8 | ルリ
9 | 夕霧綴理
10 | 百生吟子
11 | 徒町小鈴
12 | 安養寺姫芽
(9 rows)
然後，我們可以再讓這些人全部和乙宗梢當好友
whisper=# select inviter, u1.name, invitee, u2.name from friends as f, users as u1, users as u2 where f.inviter=u1.id and f.invitee=u
2.id;
inviter | name | invitee | name
---------+--------+---------+------------
4 | 乙宗梢 | 5 | 藤島慈
4 | 乙宗梢 | 6 | 日野下花帆
4 | 乙宗梢 | 7 | 村野さやか
4 | 乙宗梢 | 8 | ルリ
4 | 乙宗梢 | 9 | 夕霧綴理
4 | 乙宗梢 | 10 | 百生吟子
4 | 乙宗梢 | 11 | 徒町小鈴
4 | 乙宗梢 | 12 | 安養寺姫芽
(8 rows)
仔細看一下我們的 API，會發現我們必需回傳 channel_id 代表我們和這個朋友的聊天頻道。
這時我們需要再建立一個 channels 表：
這個表，在未來還會在新增更多欄位，用來實現金鑰交換
create table channels(
id serial not null primary key,
friend_id int references friends(id) on delete cascade,
created_at timestamptz default now()
);
由於 friend_id不一定會存在於 channel 表之中，因此我們可以用 Left join 的方式將 channel_id 附加在 friends 表上
我們可以先嘗試手動新增一個聊天室，首先，先查尋 friends 表，接著我們在 channels 表新增一個 row
whisper=# select id, inviter, invitee from friends;
id | inviter | invitee
----+---------+---------
2 | 4 | 5
3 | 4 | 6
4 | 4 | 7
5 | 4 | 8
6 | 4 | 9
7 | 4 | 10
8 | 4 | 11
9 | 4 | 12
(8 rows)
whisper=# insert into channels (friend_id) values (2);
INSERT 0 1
whisper=# select * from channels;
id | friend_id | created_at
----+-----------+-------------------------------
1 | 2 | 2024-09-30 07:24:32.704242+00
(1 row)
接著我們可以先下一個 SQL 嘗試將 friends 和 users 綁定，假設我們只要查 uid=4 乙宗梢的朋友。由於乙宗梢可能是存在 inviter 也可能存在 invitee 因此我們在查找時要注意！
select f.id as friend_id,
f.inviter + f.invitee - 4 as uid,
u.name,
u.profile,
u.public_key
from friends as f, users as u
where (f.invitee=4 and u.id=f.inviter) or (f.inviter=4 and u.id=f.invitee);
whisper=# select f.id as friend_id, f.inviter + f.invitee - 4 as uid, u.name, u.profile, u.public_key from friends as f, users as u where (f.invitee=4 and u.id=f.inviter) or (f.inviter=4 and u.id=f.invitee);
friend_id | uid | name | profile | public_key
-----------+-----+------------+---------+-------------------
2 | 5 | 藤島慈 | | example_pulic_key
3 | 6 | 日野下花帆 | | example_pulic_key
4 | 7 | 村野さやか | | example_pulic_key
5 | 8 | ルリ | | example_pulic_key
6 | 9 | 夕霧綴理 | | example_pulic_key
7 | 10 | 百生吟子 | | example_pulic_key
8 | 11 | 徒町小鈴 | | example_pulic_key
9 | 12 | 安養寺姫芽 | | example_pulic_key
(8 rows)
另外我們也可以查尋藤島慈 uid = 5 的好友。可以查到乙宗梢是藤島慈的好朋友
whisper=# select f.id as friend_id, f.inviter + f.invitee - 5 as uid, u.name, u.profile, u.public_key from friends as f, users as u where (f.invitee=5 and u.id=f.inviter) or (f.inviter=5 and u.id=f.invitee);
friend_id | uid | name | profile | public_key
-----------+-----+--------+---------+-------------------
2 | 4 | 乙宗梢 | | example_pulic_key
(1 row)
完成 friends 表與 users 表的查尋後，我們還要取得 channels 表中的 channel_id
根據 API 其實我們還要取得 user_id，剛剛忘記抓了
select t1.*, channels.id as channel_id
from (
select f.id as friend_id,
f.inviter + f.invitee - 4 as uid,
u.user_id,
u.name,
u.profile,
u.public_key
from friends as f,
users as u
where (f.invitee=4 and u.id=f.inviter)
or (f.inviter=4 and u.id=f.invitee)
)t1
left join channels
on t1.friend_id = channels.friend_id;
whisper=# select t1.*, channels.id as channel_id from (select f.id as friend_id, f.inviter + f.invitee - 4 as uid, u.user_id, u.name, u.profile, u.public_key from friends as f, users as u where (f.invitee=4 and u.id=f.inviter) or (f.inviter=4 and u.id=f .invitee)) t1 left join channels on t1.friend_id = channels.friend_id;
friend_id | uid | user_id | name | profile | public_key | channel_id
-----------+-----+--------------+------------+---------+-------------------+------------
2 | 5 | megumi | 藤島慈 | | example_pulic_key | 1
5 | 8 | rurino | ルリ | | example_pulic_key |
8 | 11 | kachimachi | 徒町小鈴 | | example_pulic_key |
6 | 9 | tuzuri | 夕霧綴理 | | example_pulic_key |
4 | 7 | sayaka | 村野さやか | | example_pulic_key |
3 | 6 | kaho_chan | 日野下花帆 | | example_pulic_key |
9 | 12 | hime_chan | 安養寺姫芽 | | example_pulic_key |
7 | 10 | momose_ginko | 百生吟子 | | example_pulic_key |
(8 rows)
由上面的查尋可以看出即使 channels 表中沒有對應的 friend_id，仍會保留左邊的表，右邊填入空值 (所以叫做 left join)。此時我們可以使用 coalesce (讀音：摳鵝 less) 意思為合併，這個函式會將空值取代會後者。可以把 channels.id 換成 coalesce(channels.id, -1)
# select t1.*, COALESCE(channels.id, -1) as channel_id from (select f.id as friend_id, f.inviter + f.invitee - 4 as uid, u.user_id, u.name, u.profile, u.public_key from friends as f, users as u where (f.invitee=4 and u.id=f.inviter) or (f.inviter=4 and u.id=f.invitee)) t1 left join channels on t1.friend_id = channels.friend_id;
friend_id | uid | user_id | name | profile | public_key | channel_id
-----------+-----+--------------+------------+---------+-------------------+------------
2 | 5 | megumi | 藤島慈 | | example_pulic_key | 1
5 | 8 | rurino | ルリ | | example_pulic_key | -1
8 | 11 | kachimachi | 徒町小鈴 | | example_pulic_key | -1
6 | 9 | tuzuri | 夕霧綴理 | | example_pulic_key | -1
4 | 7 | sayaka | 村野さやか | | example_pulic_key | -1
3 | 6 | kaho_chan | 日野下花帆 | | example_pulic_key | -1
9 | 12 | hime_chan | 安養寺姫芽 | | example_pulic_key | -1
7 | 10 | momose_ginko | 百生吟子 | | example_pulic_key | -1
(8 rows)
因為這個 Query 很複雜，我們可以換一個形勢，將其包裝成 view，我們試著以朋友為中心查詢
create view view_friend_lists as
select
t1.*,
coalesce(channels.id, -1) as channel_id
from (
select f.id as friend_id,
f.inviter + f.invitee - u.id as me,
u.id as uid,
u.user_id,
u.name,
u.profile,
u.public_key
from friends as f,
users as u
where u.id = f.inviter or u.id=f.invitee
)t1
left join channels
on t1.friend_id = channels.friend_id;
whisper=# select * from view_friend_lists where me = 4;
friend_id | me | uid | user_id | name | profile | public_key | channel_id
-----------+----+-----+--------------+------------+---------+-------------------+------------
2 | 4 | 5 | megumi | 藤島慈 | | example_pulic_key | 1
3 | 4 | 6 | kaho_chan | 日野下花帆 | | example_pulic_key | 2
5 | 4 | 8 | rurino | ルリ | | example_pulic_key | -1
8 | 4 | 11 | kachimachi | 徒町小鈴 | | example_pulic_key | -1
6 | 4 | 9 | tuzuri | 夕霧綴理 | | example_pulic_key | -1
4 | 4 | 7 | sayaka | 村野さやか | | example_pulic_key | -1
9 | 4 | 12 | hime_chan | 安養寺姫芽 | | example_pulic_key | -1
7 | 4 | 10 | momose_ginko | 百生吟子 | | example_pulic_key | -1
(8 rows)
將其包裝成 view
接著我們在 Go 中建立一個可以用來接受資料庫的 struct
type ViewFriendList struct {
FriendID int `json:"-"`
Me int `json:"-"`
UID int `json:"uid"`
UserID string `json:"user"`
Name string `json:"name"`
Profile string `json:"profile"`
PublicKey string `json:"public_key"`
ChannelID int `json:"channel_id"`
}
接著我們實作 ListFriends 這個函式，因為現在還在製作階段資料庫資料不多，所以我們嘗試每次都抓取 3 筆資料。
const limit = 3;
func ListFriends(db *pg.DB, uid, cursor int) (friends []ViewFriendList, err error) {
query := db.Model(&friends).Where("me = ?", uid)
if cursor > 0 {
query = query.Where("friend_id < ?", cursor)
}
err = query.Order("friend_id DESC").Limit(limit).Select()
if err != nil && err != pg.ErrNoRows {
return friends, fmt.Errorf("db.Query failed: %w", err)
}
return friends, nil
}
並將該邏輯註冊到 router 中
router.GET("/api/v1/friends/:next", func(c *gin.Context) {
next, err := strconv.Atoi(c.Param("next"))
if err != nil || next < 0 {
c.JSON(http.StatusBadRequest, gin.H{
"error": "bad request",
})
return
}
token := c.GetHeader("Authorization")
me, err := auth.CheckLogin(db, token)
if err != nil {
if err == auth.ErrorAuthenticationFailed {
c.JSON(http.StatusUnauthorized, gin.H{
"error": "驗證失敗",
})
} else {
log.Println(err)
c.JSON(http.StatusInternalServerError, gin.H{
"error": "內部伺服器錯誤",
})
}
return
}
list, err := friends.ListFriends(db, me.ID, next)
if err != nil {
log.Println(err)
c.JSON(http.StatusInternalServerError, gin.H{
"error": "內部伺服器錯誤",
})
return
}
n := -1
if len(list) > 0 {
n = list[len(list)-1].FriendID
}
c.JSON(http.StatusOK, struct {
Error string `json:"error"`
Next int `json:"next"`
List []friends.ViewFriendList `json:"list"`
}{
Error: "",
Next: n,
List: list,
})
})
我們試著使用該 API next 設定為 0，取得最近三筆好友資料
> curl -X GET http://localhost:8081/api/v1/friends/0 -H "Content-Type: applicaton/json" -H "Authorization: 34rCF++rUIgMUexEMdIyk4FNHfSu7UxJofgv/ND+fBK+MTbheIRDf5b9h3t0OeE0"
{"error":"","next":7,"list":[{"uid":12,"user":"hime_chan","name":"安養寺姫芽","profile":"","public_key":"example_pulic_key","channel_id":-1},{"uid":11,"user":"kachimachi","name":"徒町小鈴","profile":"","public_key":"example_pulic_key","channel_id":-1},{"uid":10,"user":"momose_ginko","name":"百生吟子","profile":"","public_key":"example_pulic_key","channel_id":-1}]}
{
"error": "",
"next": 7,
"list": [
{
"uid": 12,
"user": "hime_chan",
"name": "安養寺姫芽",
"profile": "",
"public_key": "example_pulic_key",
"channel_id": -1
},
{
"uid": 11,
"user": "kachimachi",
"name": "徒町小鈴",
"profile": "",
"public_key": "example_pulic_key",
"channel_id": -1
},
{
"uid": 10,
"user": "momose_ginko",
"name": "百生吟子",
"profile": "",
"public_key": "example_pulic_key",
"channel_id": -1
}
]
}
接著往下打 next = 7 得到下一筆 next=4
> curl -X GET http://localhost:8081/api/v1/friends/7 -H "Content-Type: applicaton/json" -H "Authorization: 34rCF++rUIgMUexEMdIyk4FNHfSu7UxJofgv/ND+fBK+MTbheIRDf5b9h3t0OeE0"
{"error":"","next":4,"list":[{"uid":9,"user":"tuzuri","name":"夕霧綴理","profile":"","public_key":"example_pulic_key","channel_id":"-1"},{"uid":8,"user":"rurino","name":"ルリ","profile":"","public_key":"example_pulic_key","channel_id":"-1"},{"uid":7,"user":"sayaka","name":"村野さやか","profile":"","public_key":"example_pulic_key","channel_id":"-1"}]}
接著往下打 next = 4 得到下一筆 next=2
> curl -X GET http://localhost:8081/api/v1/friends/4 -H "Content-Type: applicaton/json" -H "Authorization: 34rCF++rUIgMUexEMdIyk4FNHfSu7UxJofgv/ND+fBK+MTbheIRDf5b9h3t0OeE0"
{"error":"","next":2,"list":[{"uid":6,"user":"kaho_chan","name":"日野下花帆","profile":"","public_key":"example_pulic_key","channel_id":2},{"uid":5,"user":"megumi","name":"藤島慈","profile":"","public_key":"example_pulic_key","channel_id":1}]}
繼續往下打 next=2 則得不到任何資料，此時 next 被設為 -1 如果繼續往下打，則會得到 bad request
> curl -X GET http://localhost:8081/api/v1/friends/2 -H "Content-Type: applicaton/json" -H "Authorization: 34rCF++rUIgMUexEMdIyk4FNHfSu7UxJofgv/ND+fBK+MTbheIRDf5b9h3t0OeE0"
{"error":"","next":-1,"list":null}
> curl -X GET http://localhost:8081/api/v1/friends/-1 -H "Content-Type: applicaton/json" -H "Authorization: 34rCF++rUIgMUexEMdIyk4FNHfSu7UxJofgv/ND+fBK+MTbheIRDf5b9h3t0OeE0"
{"error":"bad request"}
iThome鐵人賽
看影片追技術