123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181 |
- package mysql
- import (
- "context"
- "database/sql"
- "fmt"
- "strings"
- "github.com/usememos/memos/store"
- )
- func (d *DB) CreateResource(ctx context.Context, create *store.Resource) (*store.Resource, error) {
- fields := []string{"`resource_name`", "`filename`", "`blob`", "`external_link`", "`type`", "`size`", "`creator_id`", "`internal_path`", "`memo_id`"}
- placeholder := []string{"?", "?", "?", "?", "?", "?", "?", "?", "?"}
- args := []any{create.ResourceName, create.Filename, create.Blob, create.ExternalLink, create.Type, create.Size, create.CreatorID, create.InternalPath, create.MemoID}
- stmt := "INSERT INTO `resource` (" + strings.Join(fields, ", ") + ") VALUES (" + strings.Join(placeholder, ", ") + ")"
- result, err := d.db.ExecContext(ctx, stmt, args...)
- if err != nil {
- return nil, err
- }
- id, err := result.LastInsertId()
- if err != nil {
- return nil, err
- }
- id32 := int32(id)
- return d.GetResource(ctx, &store.FindResource{ID: &id32})
- }
- func (d *DB) ListResources(ctx context.Context, find *store.FindResource) ([]*store.Resource, error) {
- where, args := []string{"1 = 1"}, []any{}
- if v := find.ID; v != nil {
- where, args = append(where, "`id` = ?"), append(args, *v)
- }
- if v := find.ResourceName; v != nil {
- where, args = append(where, "`resource_name` = ?"), append(args, *v)
- }
- if v := find.CreatorID; v != nil {
- where, args = append(where, "`creator_id` = ?"), append(args, *v)
- }
- if v := find.Filename; v != nil {
- where, args = append(where, "`filename` = ?"), append(args, *v)
- }
- if v := find.MemoID; v != nil {
- where, args = append(where, "`memo_id` = ?"), append(args, *v)
- }
- if find.HasRelatedMemo {
- where = append(where, "`memo_id` IS NOT NULL")
- }
- fields := []string{"`id`", "`resource_name`", "`filename`", "`external_link`", "`type`", "`size`", "`creator_id`", "UNIX_TIMESTAMP(`created_ts`)", "UNIX_TIMESTAMP(`updated_ts`)", "`internal_path`", "`memo_id`"}
- if find.GetBlob {
- fields = append(fields, "`blob`")
- }
- query := fmt.Sprintf("SELECT %s FROM `resource` WHERE %s ORDER BY `updated_ts` DESC, `created_ts` DESC", strings.Join(fields, ", "), strings.Join(where, " AND "))
- if find.Limit != nil {
- query = fmt.Sprintf("%s LIMIT %d", query, *find.Limit)
- if find.Offset != nil {
- query = fmt.Sprintf("%s OFFSET %d", query, *find.Offset)
- }
- }
- rows, err := d.db.QueryContext(ctx, query, args...)
- if err != nil {
- return nil, err
- }
- defer rows.Close()
- list := make([]*store.Resource, 0)
- for rows.Next() {
- resource := store.Resource{}
- var memoID sql.NullInt32
- dests := []any{
- &resource.ID,
- &resource.ResourceName,
- &resource.Filename,
- &resource.ExternalLink,
- &resource.Type,
- &resource.Size,
- &resource.CreatorID,
- &resource.CreatedTs,
- &resource.UpdatedTs,
- &resource.InternalPath,
- &memoID,
- }
- if find.GetBlob {
- dests = append(dests, &resource.Blob)
- }
- if err := rows.Scan(dests...); err != nil {
- return nil, err
- }
- if memoID.Valid {
- resource.MemoID = &memoID.Int32
- }
- list = append(list, &resource)
- }
- if err := rows.Err(); err != nil {
- return nil, err
- }
- return list, nil
- }
- func (d *DB) GetResource(ctx context.Context, find *store.FindResource) (*store.Resource, error) {
- list, err := d.ListResources(ctx, find)
- if err != nil {
- return nil, err
- }
- if len(list) == 0 {
- return nil, nil
- }
- return list[0], nil
- }
- func (d *DB) UpdateResource(ctx context.Context, update *store.UpdateResource) (*store.Resource, error) {
- set, args := []string{}, []any{}
- if v := update.ResourceName; v != nil {
- set, args = append(set, "`resource_name` = ?"), append(args, *v)
- }
- if v := update.UpdatedTs; v != nil {
- set, args = append(set, "`updated_ts` = FROM_UNIXTIME(?)"), append(args, *v)
- }
- if v := update.Filename; v != nil {
- set, args = append(set, "`filename` = ?"), append(args, *v)
- }
- if v := update.InternalPath; v != nil {
- set, args = append(set, "`internal_path` = ?"), append(args, *v)
- }
- if v := update.ExternalLink; v != nil {
- set, args = append(set, "`external_link` = ?"), append(args, *v)
- }
- if v := update.MemoID; v != nil {
- set, args = append(set, "`memo_id` = ?"), append(args, *v)
- }
- if v := update.Blob; v != nil {
- set, args = append(set, "`blob` = ?"), append(args, v)
- }
- args = append(args, update.ID)
- stmt := "UPDATE `resource` SET " + strings.Join(set, ", ") + " WHERE `id` = ?"
- if _, err := d.db.ExecContext(ctx, stmt, args...); err != nil {
- return nil, err
- }
- return d.GetResource(ctx, &store.FindResource{ID: &update.ID})
- }
- func (d *DB) DeleteResource(ctx context.Context, delete *store.DeleteResource) error {
- stmt := "DELETE FROM `resource` WHERE `id` = ?"
- result, err := d.db.ExecContext(ctx, stmt, delete.ID)
- if err != nil {
- return err
- }
- if _, err := result.RowsAffected(); err != nil {
- return err
- }
- if err := d.Vacuum(ctx); err != nil {
- // Prevent linter warning.
- return err
- }
- return nil
- }
- func vacuumResource(ctx context.Context, tx *sql.Tx) error {
- stmt := "DELETE FROM `resource` WHERE `creator_id` NOT IN (SELECT `id` FROM `user`)"
- _, err := tx.ExecContext(ctx, stmt)
- if err != nil {
- return err
- }
- return nil
- }
|