nel.pet
1package db
2
3import (
4 "database/sql"
5 "errors"
6 "fmt"
7 "strings"
8 "time"
9
10 "tangled.org/core/appview/models"
11)
12
13func AddString(e Execer, s models.String) error {
14 _, err := e.Exec(
15 `insert into strings (
16 did,
17 rkey,
18 filename,
19 description,
20 content,
21 created,
22 edited
23 )
24 values (?, ?, ?, ?, ?, ?, null)
25 on conflict(did, rkey) do update set
26 filename = excluded.filename,
27 description = excluded.description,
28 content = excluded.content,
29 edited = case
30 when
31 strings.content != excluded.content
32 or strings.filename != excluded.filename
33 or strings.description != excluded.description then ?
34 else strings.edited
35 end`,
36 s.Did,
37 s.Rkey,
38 s.Filename,
39 s.Description,
40 s.Contents,
41 s.Created.Format(time.RFC3339),
42 time.Now().Format(time.RFC3339),
43 )
44 return err
45}
46
47func GetStrings(e Execer, limit int, filters ...filter) ([]models.String, error) {
48 var all []models.String
49
50 var conditions []string
51 var args []any
52 for _, filter := range filters {
53 conditions = append(conditions, filter.Condition())
54 args = append(args, filter.Arg()...)
55 }
56
57 whereClause := ""
58 if conditions != nil {
59 whereClause = " where " + strings.Join(conditions, " and ")
60 }
61
62 limitClause := ""
63 if limit != 0 {
64 limitClause = fmt.Sprintf(" limit %d ", limit)
65 }
66
67 query := fmt.Sprintf(`select
68 did,
69 rkey,
70 filename,
71 description,
72 content,
73 created,
74 edited
75 from strings
76 %s
77 order by created desc
78 %s`,
79 whereClause,
80 limitClause,
81 )
82
83 rows, err := e.Query(query, args...)
84
85 if err != nil {
86 return nil, err
87 }
88 defer rows.Close()
89
90 for rows.Next() {
91 var s models.String
92 var createdAt string
93 var editedAt sql.NullString
94
95 if err := rows.Scan(
96 &s.Did,
97 &s.Rkey,
98 &s.Filename,
99 &s.Description,
100 &s.Contents,
101 &createdAt,
102 &editedAt,
103 ); err != nil {
104 return nil, err
105 }
106
107 s.Created, err = time.Parse(time.RFC3339, createdAt)
108 if err != nil {
109 s.Created = time.Now()
110 }
111
112 if editedAt.Valid {
113 e, err := time.Parse(time.RFC3339, editedAt.String)
114 if err != nil {
115 e = time.Now()
116 }
117 s.Edited = &e
118 }
119
120 all = append(all, s)
121 }
122
123 if err := rows.Err(); err != nil {
124 return nil, err
125 }
126
127 return all, nil
128}
129
130func CountStrings(e Execer, filters ...filter) (int64, error) {
131 var conditions []string
132 var args []any
133 for _, filter := range filters {
134 conditions = append(conditions, filter.Condition())
135 args = append(args, filter.Arg()...)
136 }
137
138 whereClause := ""
139 if conditions != nil {
140 whereClause = " where " + strings.Join(conditions, " and ")
141 }
142
143 repoQuery := fmt.Sprintf(`select count(1) from strings %s`, whereClause)
144 var count int64
145 err := e.QueryRow(repoQuery, args...).Scan(&count)
146
147 if !errors.Is(err, sql.ErrNoRows) && err != nil {
148 return 0, err
149 }
150
151 return count, nil
152}
153
154func DeleteString(e Execer, filters ...filter) error {
155 var conditions []string
156 var args []any
157 for _, filter := range filters {
158 conditions = append(conditions, filter.Condition())
159 args = append(args, filter.Arg()...)
160 }
161
162 whereClause := ""
163 if conditions != nil {
164 whereClause = " where " + strings.Join(conditions, " and ")
165 }
166
167 query := fmt.Sprintf(`delete from strings %s`, whereClause)
168
169 _, err := e.Exec(query, args...)
170 return err
171}