internal/store/snippets.go

  1package store
  2
  3import (
  4	"database/sql"
  5	"errors"
  6)
  7
  8type Snippet struct {
  9	ID          int64
 10	PublicID    string
 11	OwnerID     int64
 12	OwnerName   string
 13	Description string
 14	Visibility  string // public | unlisted | private
 15	CreatedAt   string
 16	UpdatedAt   string
 17	// Files carries names and sizes. Content is filled by SnippetFiles and
 18	// SnippetFile only, so a listing does not read every body.
 19	Files []SnippetFile
 20}
 21
 22type SnippetFile struct {
 23	Name    string
 24	Size    int64
 25	Content []byte
 26}
 27
 28const snippetSelect = `
 29	SELECT s.id, s.public_id, s.owner_id, u.username, s.description, s.visibility, s.created_at, s.updated_at
 30	FROM snippets s JOIN users u ON u.id = s.owner_id`
 31
 32func scanSnippet(row interface{ Scan(...any) error }) (Snippet, error) {
 33	var sn Snippet
 34	err := row.Scan(&sn.ID, &sn.PublicID, &sn.OwnerID, &sn.OwnerName, &sn.Description, &sn.Visibility, &sn.CreatedAt, &sn.UpdatedAt)
 35	return sn, err
 36}
 37
 38// CreateSnippet inserts the snippet and its first file in one transaction.
 39// A public_id collision is ErrExists so the caller can draw another.
 40func (s *Store) CreateSnippet(ownerID int64, publicID, description, visibility, name string, content []byte) (int64, error) {
 41	tx, err := s.DB.Begin()
 42	if err != nil {
 43		return 0, err
 44	}
 45	defer tx.Rollback()
 46	res, err := tx.Exec(
 47		"INSERT INTO snippets (public_id, owner_id, description, visibility) VALUES (?, ?, ?, ?)",
 48		publicID, ownerID, description, visibility)
 49	if err != nil {
 50		if isUniqueErr(err) {
 51			return 0, ErrExists
 52		}
 53		return 0, err
 54	}
 55	id, err := res.LastInsertId()
 56	if err != nil {
 57		return 0, err
 58	}
 59	if _, err := tx.Exec("INSERT INTO snippet_files (snippet_id, name, content, size) VALUES (?, ?, ?, ?)",
 60		id, name, content, len(content)); err != nil {
 61		return 0, err
 62	}
 63	return id, tx.Commit()
 64}
 65
 66func (s *Store) SnippetByPublicID(publicID string) (Snippet, error) {
 67	sn, err := scanSnippet(s.DB.QueryRow(snippetSelect+" WHERE s.public_id = ?", publicID))
 68	if errors.Is(err, sql.ErrNoRows) {
 69		return sn, ErrNotFound
 70	}
 71	if err != nil {
 72		return sn, err
 73	}
 74	sn.Files, err = s.snippetFileNames(sn.ID)
 75	return sn, err
 76}
 77
 78func (s *Store) snippetFileNames(id int64) ([]SnippetFile, error) {
 79	rows, err := s.DB.Query("SELECT name, size FROM snippet_files WHERE snippet_id = ? ORDER BY name", id)
 80	if err != nil {
 81		return nil, err
 82	}
 83	defer rows.Close()
 84	var out []SnippetFile
 85	for rows.Next() {
 86		var f SnippetFile
 87		if err := rows.Scan(&f.Name, &f.Size); err != nil {
 88			return nil, err
 89		}
 90		out = append(out, f)
 91	}
 92	return out, rows.Err()
 93}
 94
 95// SnippetFiles returns every file with its content, by name.
 96func (s *Store) SnippetFiles(id int64) ([]SnippetFile, error) {
 97	rows, err := s.DB.Query("SELECT name, size, content FROM snippet_files WHERE snippet_id = ? ORDER BY name", id)
 98	if err != nil {
 99		return nil, err
100	}
101	defer rows.Close()
102	var out []SnippetFile
103	for rows.Next() {
104		var f SnippetFile
105		if err := rows.Scan(&f.Name, &f.Size, &f.Content); err != nil {
106			return nil, err
107		}
108		out = append(out, f)
109	}
110	return out, rows.Err()
111}
112
113func (s *Store) SnippetFile(id int64, name string) (SnippetFile, error) {
114	var f SnippetFile
115	err := s.DB.QueryRow("SELECT name, size, content FROM snippet_files WHERE snippet_id = ? AND name = ?", id, name).
116		Scan(&f.Name, &f.Size, &f.Content)
117	if errors.Is(err, sql.ErrNoRows) {
118		return f, ErrNotFound
119	}
120	return f, err
121}
122
123// ListSnippets lists an owner's snippets newest first. all=false keeps
124// public ones only. afterID is the keyset cursor: rows older than it.
125// Ids grow with creation, so ordering by id is creation order.
126func (s *Store) ListSnippets(ownerID int64, all bool, limit int, afterID int64) ([]Snippet, error) {
127	q := snippetSelect + " WHERE s.owner_id = ?"
128	args := []any{ownerID}
129	if !all {
130		q += " AND s.visibility = 'public'"
131	}
132	if afterID > 0 {
133		q += " AND s.id < ?"
134		args = append(args, afterID)
135	}
136	q += " ORDER BY s.id DESC"
137	if limit > 0 {
138		q += " LIMIT ?"
139		args = append(args, limit)
140	}
141	rows, err := s.DB.Query(q, args...)
142	if err != nil {
143		return nil, err
144	}
145	defer rows.Close()
146	var out []Snippet
147	for rows.Next() {
148		sn, err := scanSnippet(rows)
149		if err != nil {
150			return nil, err
151		}
152		out = append(out, sn)
153	}
154	if err := rows.Err(); err != nil {
155		return nil, err
156	}
157	// One query per row for the names; pages are at most 200 rows.
158	for i := range out {
159		if out[i].Files, err = s.snippetFileNames(out[i].ID); err != nil {
160			return nil, err
161		}
162	}
163	return out, nil
164}
165
166func (s *Store) CountSnippets(ownerID int64, all bool) (int, error) {
167	q := "SELECT COUNT(*) FROM snippets WHERE owner_id = ?"
168	if !all {
169		q += " AND visibility = 'public'"
170	}
171	var n int
172	err := s.DB.QueryRow(q, ownerID).Scan(&n)
173	return n, err
174}
175
176func (s *Store) UpdateSnippet(id int64, description, visibility string) error {
177	_, err := s.DB.Exec(
178		"UPDATE snippets SET description = ?, visibility = ?, updated_at = strftime('%Y-%m-%dT%H:%M:%fZ','now') WHERE id = ?",
179		description, visibility, id)
180	return err
181}
182
183func (s *Store) DeleteSnippet(id int64) error {
184	_, err := s.DB.Exec("DELETE FROM snippets WHERE id = ?", id)
185	return err
186}
187
188// SetSnippetFile adds the file or replaces one of the same name.
189func (s *Store) SetSnippetFile(id int64, name string, content []byte) error {
190	tx, err := s.DB.Begin()
191	if err != nil {
192		return err
193	}
194	defer tx.Rollback()
195	if _, err := tx.Exec(`INSERT INTO snippet_files (snippet_id, name, content, size) VALUES (?, ?, ?, ?)
196		ON CONFLICT (snippet_id, name) DO UPDATE SET content = excluded.content, size = excluded.size`,
197		id, name, content, len(content)); err != nil {
198		return err
199	}
200	if _, err := tx.Exec("UPDATE snippets SET updated_at = strftime('%Y-%m-%dT%H:%M:%fZ','now') WHERE id = ?", id); err != nil {
201		return err
202	}
203	return tx.Commit()
204}
205
206func (s *Store) RemoveSnippetFile(id int64, name string) error {
207	tx, err := s.DB.Begin()
208	if err != nil {
209		return err
210	}
211	defer tx.Rollback()
212	res, err := tx.Exec("DELETE FROM snippet_files WHERE snippet_id = ? AND name = ?", id, name)
213	if err != nil {
214		return err
215	}
216	if n, _ := res.RowsAffected(); n == 0 {
217		return ErrNotFound
218	}
219	if _, err := tx.Exec("UPDATE snippets SET updated_at = strftime('%Y-%m-%dT%H:%M:%fZ','now') WHERE id = ?", id); err != nil {
220		return err
221	}
222	return tx.Commit()
223}