123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283 |
- package store
- import (
- "context"
- "database/sql"
- "fmt"
- "strings"
- )
- type Resource struct {
- ID int
- // Standard fields
- CreatorID int
- CreatedTs int64
- UpdatedTs int64
- // Domain specific fields
- Filename string
- Blob []byte
- InternalPath string
- ExternalLink string
- Type string
- Size int64
- LinkedMemoAmount int
- }
- type FindResource struct {
- GetBlob bool
- ID *int
- CreatorID *int
- Filename *string
- MemoID *int
- Limit *int
- Offset *int
- }
- type UpdateResource struct {
- ID int
- UpdatedTs *int64
- Filename *string
- }
- type DeleteResource struct {
- ID int
- }
- func (s *Store) CreateResource(ctx context.Context, create *Resource) (*Resource, error) {
- tx, err := s.db.BeginTx(ctx, nil)
- if err != nil {
- return nil, err
- }
- defer tx.Rollback()
- if err := tx.QueryRowContext(ctx, `
- INSERT INTO resource (
- filename,
- blob,
- external_link,
- type,
- size,
- creator_id,
- internal_path
- )
- VALUES (?, ?, ?, ?, ?, ?, ?)
- RETURNING id, created_ts, updated_ts
- `,
- create.Filename, create.Blob, create.ExternalLink, create.Type, create.Size, create.CreatorID, create.InternalPath,
- ).Scan(&create.ID, &create.CreatedTs, &create.UpdatedTs); err != nil {
- return nil, err
- }
- if err := tx.Commit(); err != nil {
- return nil, err
- }
- resource := create
- return resource, nil
- }
- func (s *Store) ListResources(ctx context.Context, find *FindResource) ([]*Resource, error) {
- tx, err := s.db.BeginTx(ctx, nil)
- if err != nil {
- return nil, err
- }
- defer tx.Rollback()
- resources, err := listResources(ctx, tx, find)
- if err != nil {
- return nil, err
- }
- if err := tx.Commit(); err != nil {
- return nil, err
- }
- return resources, nil
- }
- func (s *Store) GetResource(ctx context.Context, find *FindResource) (*Resource, error) {
- tx, err := s.db.BeginTx(ctx, nil)
- if err != nil {
- return nil, err
- }
- defer tx.Rollback()
- resources, err := listResources(ctx, tx, find)
- if err != nil {
- return nil, err
- }
- if len(resources) == 0 {
- return nil, nil
- }
- if err := tx.Commit(); err != nil {
- return nil, err
- }
- return resources[0], nil
- }
- func (s *Store) UpdateResource(ctx context.Context, update *UpdateResource) (*Resource, error) {
- tx, err := s.db.BeginTx(ctx, nil)
- if err != nil {
- return nil, err
- }
- defer tx.Rollback()
- set, args := []string{}, []any{}
- if v := update.UpdatedTs; v != nil {
- set, args = append(set, "updated_ts = ?"), append(args, *v)
- }
- if v := update.Filename; v != nil {
- set, args = append(set, "filename = ?"), append(args, *v)
- }
- args = append(args, update.ID)
- fields := []string{"id", "filename", "external_link", "type", "size", "creator_id", "created_ts", "updated_ts", "internal_path"}
- query := `
- UPDATE resource
- SET ` + strings.Join(set, ", ") + `
- WHERE id = ?
- RETURNING ` + strings.Join(fields, ", ")
- resource := Resource{}
- dests := []any{
- &resource.ID,
- &resource.Filename,
- &resource.ExternalLink,
- &resource.Type,
- &resource.Size,
- &resource.CreatorID,
- &resource.CreatedTs,
- &resource.UpdatedTs,
- &resource.InternalPath,
- }
- if err := tx.QueryRowContext(ctx, query, args...).Scan(dests...); err != nil {
- return nil, err
- }
- if err := tx.Commit(); err != nil {
- return nil, err
- }
- return &resource, nil
- }
- func (s *Store) DeleteResource(ctx context.Context, delete *DeleteResource) error {
- tx, err := s.db.BeginTx(ctx, nil)
- if err != nil {
- return err
- }
- defer tx.Rollback()
- if _, err := tx.ExecContext(ctx, `
- DELETE FROM resource
- WHERE id = ?
- `, delete.ID); err != nil {
- return err
- }
- if err := tx.Commit(); err != nil {
- // Prevent linter warning.
- return err
- }
- return nil
- }
- func listResources(ctx context.Context, tx *sql.Tx, find *FindResource) ([]*Resource, error) {
- where, args := []string{"1 = 1"}, []any{}
- if v := find.ID; v != nil {
- where, args = append(where, "resource.id = ?"), append(args, *v)
- }
- if v := find.CreatorID; v != nil {
- where, args = append(where, "resource.creator_id = ?"), append(args, *v)
- }
- if v := find.Filename; v != nil {
- where, args = append(where, "resource.filename = ?"), append(args, *v)
- }
- if v := find.MemoID; v != nil {
- where, args = append(where, "resource.id in (SELECT resource_id FROM memo_resource WHERE memo_id = ?)"), append(args, *v)
- }
- fields := []string{"resource.id", "resource.filename", "resource.external_link", "resource.type", "resource.size", "resource.creator_id", "resource.created_ts", "resource.updated_ts", "internal_path"}
- if find.GetBlob {
- fields = append(fields, "resource.blob")
- }
- query := fmt.Sprintf(`
- SELECT
- COUNT(DISTINCT memo_resource.memo_id) AS linked_memo_amount,
- %s
- FROM resource
- LEFT JOIN memo_resource ON resource.id = memo_resource.resource_id
- WHERE %s
- GROUP BY resource.id
- ORDER BY resource.id 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 := tx.QueryContext(ctx, query, args...)
- if err != nil {
- return nil, err
- }
- defer rows.Close()
- list := make([]*Resource, 0)
- for rows.Next() {
- resource := Resource{}
- dests := []any{
- &resource.LinkedMemoAmount,
- &resource.ID,
- &resource.Filename,
- &resource.ExternalLink,
- &resource.Type,
- &resource.Size,
- &resource.CreatorID,
- &resource.CreatedTs,
- &resource.UpdatedTs,
- &resource.InternalPath,
- }
- if find.GetBlob {
- dests = append(dests, &resource.Blob)
- }
- if err := rows.Scan(dests...); err != nil {
- return nil, err
- }
- list = append(list, &resource)
- }
- if err := rows.Err(); err != nil {
- return nil, err
- }
- return list, 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
- }
|