1package sqlite23import (4 "bytes"5 "context"6 "database/sql"7 "encoding/json"8 "fmt"9 gomail "net/mail"10 "strings"11 "time"1213 "git.lin.moe/go/mlisting/storage"14 gomsg "github.com/emersion/go-message"15 "github.com/emersion/go-message/mail"16 "github.com/google/uuid"17)1819type List struct {20 name string21 address string22 description string23 create_at time.Time24 default_perm uint2526 db *sql.DB27}2829func (l *List) Address() string {30 return l.address31}32func (l *List) Name() string {33 return l.name34}35func (l *List) Description() string {36 return l.description37}38func (l *List) DefaultPerm() uint8 {39 return uint8(l.default_perm)40}4142func (l *List) Members(ctx context.Context) ([]storage.AddressInfo, error) {43 const _sql = `SELECT address, name, create_at, permission FROM member WHERE list=?`44 var infos = []storage.AddressInfo{}45 rows, err := l.db.QueryContext(ctx, _sql, l.address)46 if err != nil {47 return nil, err48 }49 defer rows.Close()50 for rows.Next() {51 info := new(AddressInfo)52 var perm sql.NullInt6453 if err := rows.Scan(&info.address, &info.name, &info.join_at, &perm); err != nil {54 return nil, err55 }56 if perm.Valid {57 info.perm = uint(perm.Int64)58 } else {59 info.perm = uint(l.DefaultPerm())60 }61 info.db = l.db62 infos = append(infos, info)63 }64 return infos, nil65}6667func (l *List) NewMember(ctx context.Context, addr string) (storage.AddressInfo, error) {68 maddr, err := mail.ParseAddress(addr)69 if err != nil {70 return nil, err71 }72 const _sql = `INSERT INTO member (address, name, list) VALUES (?, ?, ?) RETURNING address, name, create_at`73 row := l.db.QueryRowContext(ctx, _sql, maddr.Address, maddr.Name, l.address)7475 addrinfo := new(AddressInfo)76 addrinfo.db = l.db77 addrinfo.perm = l.default_perm7879 if err := row.Scan(&addrinfo.address, &addrinfo.name, &addrinfo.join_at); err != nil {80 return nil, err81 }8283 return addrinfo, nil84}8586func (l *List) DelMember(ctx context.Context, addr string) error {87 maddr, err := mail.ParseAddress(addr)88 if err != nil {89 return err90 }91 const _sql = `DELETE FROM member WHERE list=? AND address = ? `92 _, err = l.db.ExecContext(ctx, _sql, l.address, maddr.Address)93 return err94}9596func (l *List) UpdateMember(ctx context.Context, addr string, perm uint8) (storage.AddressInfo, error) {97 maddr, err := mail.ParseAddress(addr)98 if err != nil {99 return nil, err100 }101102 const _sql = `UPDATE member SET permission=? WHERE list=? AND address=? RETURNING address, name, create_at, permission`103 info := new(AddressInfo)104 row := l.db.QueryRowContext(ctx, _sql, perm, l.address, maddr.Address)105 if err := row.Scan(&info.address, &info.name, &info.join_at, &info.perm); err != nil {106 return nil, err107 }108 info.db = l.db109 return info, nil110}111112func (l *List) GetMember(ctx context.Context, addr string) (storage.AddressInfo, error) {113 maddr, err := mail.ParseAddress(addr)114 if err != nil {115 return nil, err116 }117118 const _sql = `SELECT address, name, create_at, permission FROM member WHERE list=? AND address=?`119 info := new(AddressInfo)120 row := l.db.QueryRowContext(ctx, _sql, l.address, maddr.Address)121122 var perm sql.NullInt64123 if err := row.Scan(&info.address, &info.name, &info.join_at, &perm); err != nil {124 return nil, err125 }126 if perm.Valid {127 info.perm = uint(perm.Int64)128 } else {129 info.perm = uint(l.DefaultPerm())130 }131 info.db = l.db132133 return info, nil134}135136func (l *List) AddMessage(ctx context.Context, header gomail.Header, body []byte) (msg storage.Message, err error) {137 var (138 h = mail.HeaderFromMap(header)139 replyto string140 msgid string141 )142 entity, err := gomsg.New(gomsg.HeaderFromMap(header), bytes.NewReader(body))143 if err != nil {144 return nil, err145 }146147 msgid, err = h.MessageID()148 if err != nil {149 return150 }151 inreplyto, err := h.MsgIDList("In-Reply-To")152 if err != nil {153 return154 } else if len(inreplyto) != 0 {155 replyto = inreplyto[0]156 } else {157 replyto = ""158 }159160 rheader, err := json.Marshal(h.Map())161 if err != nil {162 return nil, err163 }164 subject, err := h.Text("Subject")165 if err != nil {166 subject = h.Get("Subject")167 }168169 text := storage.MailToText(entity)170171 var result sql.Result172 if replyto != "" {173 var (174 pID int175 mpath string176 )177 err = l.db.QueryRowContext(ctx,178 `SELECT id, mpath FROM message WHERE list=? AND message_id=?`,179 l.address, replyto).Scan(&pID, &mpath)180 if err != nil {181 return182 }183 mpath = fmt.Sprintf("%s%d/", mpath, pID)184 const _sql = `INSERT INTO message(message_id, list, header, body, text, mpath, subject) VALUES(?, ?, ?, ?, ?, ?, ?)`185 result, err = l.db.ExecContext(ctx, _sql, msgid, l.address, rheader, body, text, mpath, subject)186 if err != nil {187 return188 }189 } else {190 const _sql = `INSERT INTO message(message_id, list, header, body, text, subject) VALUES(?, ?, ?, ?, ?, ?)`191 result, err = l.db.ExecContext(ctx, _sql, msgid, l.address, rheader, body, text, subject)192 if err != nil {193 return194 }195 }196197 id, err := result.LastInsertId()198 if err != nil {199 return200 }201202 return l.messageById(ctx, id)203}204205func (l *List) messageById(ctx context.Context, id int64) (storage.Message, error) {206 var rheader []byte207 const _sql = `SELECT id, message_id, create_at, subject, header, body, text, mpath FROM message WHERE id = ?`208 msg := new(Message)209 msg.db = l.db210 if err := l.db.QueryRowContext(ctx, _sql, id).Scan(211 &msg.id, &msg.messageID,212 &msg.createAt, &msg.subject,213 &rheader, &msg.body, &msg.text,214 &msg.mpath); err != nil {215 return nil, err216 }217 hmap := make(map[string][]string)218 if err := json.Unmarshal(rheader, &hmap); err != nil {219 return nil, err220 }221 msg.header = mail.HeaderFromMap(hmap)222223 return msg, nil224}225func (l *List) Message(ctx context.Context, msgID string) (storage.Message, error) {226 var rheader []byte227 const _sql = `SELECT id, message_id, create_at, subject, header, body, text, mpath FROM message WHERE list=? AND message_id=?`228 msg := new(Message)229 msg.db = l.db230 if err := l.db.QueryRowContext(ctx, _sql,231 l.address, msgID).Scan(232 &msg.id, &msg.messageID,233 &msg.createAt, &msg.subject,234 &rheader, &msg.body, &msg.text,235 &msg.mpath); err != nil {236 return nil, err237 }238 hmap := make(map[string][]string)239 if err := json.Unmarshal(rheader, &hmap); err != nil {240 return nil, err241 }242 msg.header = mail.HeaderFromMap(hmap)243244 return msg, nil245}246247func (l *List) Messages(ctx context.Context, isThreadHead bool, search string, offset, limit uint) ([]storage.Message, int64, error) {248249 whereQ := "list=?"250 var args = []any{l.address}251 if isThreadHead {252 whereQ += " AND mpath='/'"253 }254 if search != "" {255 // sqlite3 double-quoted search string256 search = strings.ReplaceAll(search, "\"", "\"\"")257 search = "\"" + search + "\""258259 whereQ += " AND id in (select rowid from message_fts(?))"260 args = append(args, search)261 }262 whereQ += " ORDER BY create_at DESC, id DESC"263 var count int64264 err := l.db.QueryRowContext(ctx, fmt.Sprintf("SELECT count(1) FROM message WHERE %s", whereQ), args...).Scan(&count)265 if err != nil {266 return nil, 0, err267 }268269 args = append(args, limit, offset)270 rows, err := l.db.QueryContext(ctx, fmt.Sprintf(271 "SELECT id, message_id, create_at, subject, header, body, text, mpath FROM message WHERE %s LIMIT ? OFFSET ?",272 whereQ,273 ), args...)274 if err != nil {275 return nil, 0, err276 }277 defer rows.Close()278279 var result = []storage.Message{}280 for rows.Next() {281 var msg = new(Message)282 var rheader []byte283 if err := rows.Scan(&msg.id, &msg.messageID,284 &msg.createAt, &msg.subject,285 &rheader, &msg.body, &msg.text,286 &msg.mpath); err != nil {287 return nil, 0, err288 }289290 hmap := make(map[string][]string)291 if err := json.Unmarshal(rheader, &hmap); err != nil {292 return nil, 0, err293 }294 msg.header = mail.HeaderFromMap(hmap)295 msg.db = l.db296 result = append(result, msg)297 }298 return result, count, nil299}300301func (l *List) NewRequest(ctx context.Context, from string, rtype storage.RequestType, expireAt time.Time) (string, error) {302 maddr, err := mail.ParseAddress(from)303 if err != nil {304 return "", err305 }306307 token := uuid.NewString()308 const _sql = `INSERT INTO request(token, list, owner_address, request_type, expire_at) VALUES(?, ?, ?, ?, ?)`309 _, err = l.db.ExecContext(ctx, _sql, token, l.address, maddr.Address, rtype, expireAt)310 if err != nil {311 return "", err312 }313 return token, nil314}315316func (l *List) CompleteReqest(ctx context.Context, from, token string) (storage.RequestType, error) {317 var (318 expireAt time.Time319 rtype storage.RequestType320 now = time.Now()321 )322 maddr, err := mail.ParseAddress(from)323 if err != nil {324 return storage.REQUEST_INVALID, fmt.Errorf("invalid from address: %v", err)325 }326327 if err := l.db.QueryRowContext(ctx,328 `SELECT request_type, expire_at FROM request where list=? AND owner_address = ? AND token = ? AND confirmed_at IS NULL`,329 l.address, maddr.Address, token).330 Scan(&rtype, &expireAt); err != nil {331 return storage.REQUEST_INVALID, err332 }333334 if expireAt.Before(now) {335 // TODO: should return denied message336 return storage.REQUEST_INVALID, fmt.Errorf("token expired")337 }338339 l.db.ExecContext(ctx, `UPDATE request SET confirmed_at=? where token=?`, now, token)340341 return rtype, nil342}343344type AddressInfo struct {345 address, name string346 join_at time.Time347 perm uint348349 db *sql.DB350}351352func (a *AddressInfo) String() string {353 return (&mail.Address{354 Name: a.name,355 Address: a.address,356 }).String()357}358359func (a *AddressInfo) Name() string {360 return a.name361}362363func (a *AddressInfo) Address() string {364 return a.address365}366367func (a *AddressInfo) Perm() uint8 {368 return uint8(a.perm)369}370371func (a *AddressInfo) JoinedLists(ctx context.Context) ([]storage.List, error) {372 const _sql = `SELECT list.address, list.create_at, list.description, list.default_perm373 FROM list INNER JOIN member ON list.address=member.list374 WHERE member.address=?`375376 rows, err := a.db.QueryContext(ctx, _sql, a.address)377 if err != nil {378 return nil, err379 }380 defer rows.Close()381 var lists []storage.List382 for rows.Next() {383 list := new(List)384 list.db = a.db385 if err := rows.Scan(386 &list.address,387 &list.create_at,388 &list.description,389 &list.default_perm); err != nil {390 return nil, err391 }392 lists = append(lists, list)393 }394 return lists, nil395}