Eric Bower
·
16 Sep 24
db.go
1package db
2
3import (
4 "database/sql"
5 "database/sql/driver"
6 "encoding/json"
7 "errors"
8 "regexp"
9 "time"
10)
11
12var ErrNameTaken = errors.New("username has already been claimed")
13var ErrNameDenied = errors.New("username is on the denylist")
14var ErrNameInvalid = errors.New("username has invalid characters in it")
15var ErrPublicKeyTaken = errors.New("public key is already associated with another user")
16
17type PublicKey struct {
18 ID string `json:"id"`
19 UserID string `json:"user_id"`
20 Name string `json:"name"`
21 Key string `json:"key"`
22 CreatedAt *time.Time `json:"created_at"`
23}
24
25type User struct {
26 ID string `json:"id"`
27 Name string `json:"name"`
28 PublicKey *PublicKey `json:"public_key,omitempty"`
29 CreatedAt *time.Time `json:"created_at"`
30}
31
32type PostData struct {
33 ImgPath string `json:"img_path"`
34 LastDigest *time.Time `json:"last_digest"`
35}
36
37// Make the Attrs struct implement the driver.Valuer interface. This method
38// simply returns the JSON-encoded representation of the struct.
39func (p PostData) Value() (driver.Value, error) {
40 return json.Marshal(p)
41}
42
43// Make the Attrs struct implement the sql.Scanner interface. This method
44// simply decodes a JSON-encoded value into the struct fields.
45func (p *PostData) Scan(value interface{}) error {
46 b, ok := value.([]byte)
47 if !ok {
48 return errors.New("type assertion to []byte failed")
49 }
50
51 return json.Unmarshal(b, &p)
52}
53
54type Project struct {
55 ID string `json:"id"`
56 UserID string `json:"user_id"`
57 Name string `json:"name"`
58 ProjectDir string `json:"project_dir"`
59 Username string `json:"username"`
60 Acl ProjectAcl `json:"acl"`
61 Blocked string `json:"blocked"`
62 CreatedAt *time.Time `json:"created_at"`
63 UpdatedAt *time.Time `json:"updated_at"`
64}
65
66type ProjectAcl struct {
67 Type string `json:"type"`
68 Data []string `json:"data"`
69}
70
71// Make the Attrs struct implement the driver.Valuer interface. This method
72// simply returns the JSON-encoded representation of the struct.
73func (p ProjectAcl) Value() (driver.Value, error) {
74 return json.Marshal(p)
75}
76
77// Make the Attrs struct implement the sql.Scanner interface. This method
78// simply decodes a JSON-encoded value into the struct fields.
79func (p *ProjectAcl) Scan(value interface{}) error {
80 b, ok := value.([]byte)
81 if !ok {
82 return errors.New("type assertion to []byte failed")
83 }
84
85 return json.Unmarshal(b, &p)
86}
87
88type FeedItemData struct {
89 Title string `json:"title"`
90 Description string `json:"description"`
91 Content string `json:"content"`
92 Link string `json:"link"`
93 PublishedAt *time.Time `json:"published_at"`
94}
95
96// Make the Attrs struct implement the driver.Valuer interface. This method
97// simply returns the JSON-encoded representation of the struct.
98func (p FeedItemData) Value() (driver.Value, error) {
99 return json.Marshal(p)
100}
101
102// Make the Attrs struct implement the sql.Scanner interface. This method
103// simply decodes a JSON-encoded value into the struct fields.
104func (p *FeedItemData) Scan(value interface{}) error {
105 b, ok := value.([]byte)
106 if !ok {
107 return errors.New("type assertion to []byte failed")
108 }
109 return json.Unmarshal(b, &p)
110}
111
112type Post struct {
113 ID string `json:"id"`
114 UserID string `json:"user_id"`
115 Filename string `json:"filename"`
116 Slug string `json:"slug"`
117 Title string `json:"title"`
118 Text string `json:"text"`
119 Description string `json:"description"`
120 CreatedAt *time.Time `json:"created_at"`
121 PublishAt *time.Time `json:"publish_at"`
122 Username string `json:"username"`
123 UpdatedAt *time.Time `json:"updated_at"`
124 ExpiresAt *time.Time `json:"expires_at"`
125 Hidden bool `json:"hidden"`
126 Views int `json:"views"`
127 Space string `json:"space"`
128 Shasum string `json:"shasum"`
129 FileSize int `json:"file_size"`
130 MimeType string `json:"mime_type"`
131 Data PostData `json:"data"`
132 Tags []string `json:"tags"`
133}
134
135type Paginate[T any] struct {
136 Data []T
137 Total int
138}
139
140type Analytics struct {
141 TotalUsers int
142 UsersLastMonth int
143 TotalPosts int
144 PostsLastMonth int
145 UsersWithPost int
146}
147
148type SummaryOpts struct {
149 FkID string
150 By string
151 Interval string
152 Origin time.Time
153 Where string
154}
155
156type PostAnalytics struct {
157 ID string
158 PostID string
159 Views int
160 UpdateAt *time.Time
161}
162
163type AnalyticsVisits struct {
164 ID string
165 UserID string
166 ProjectID string
167 PostID string
168 Host string
169 Path string
170 IpAddress string
171 UserAgent string
172 Referer string
173 Status int
174}
175
176type VisitInterval struct {
177 PostID string `json:"post_id"`
178 ProjectID string `json:"project_id"`
179 Interval *time.Time `json:"interval"`
180 Visitors int `json:"visitors"`
181}
182
183type VisitUrl struct {
184 PostID string `json:"post_id"`
185 ProjectID string `json:"project_id"`
186 Url string `json:"url"`
187 Count int `json:"count"`
188}
189
190type SummaryVisits struct {
191 Intervals []*VisitInterval `json:"intervals"`
192 TopUrls []*VisitUrl `json:"top_urls"`
193 TopReferers []*VisitUrl `json:"top_referers"`
194}
195
196type Pager struct {
197 Num int
198 Page int
199}
200
201type FeedItem struct {
202 ID string
203 PostID string
204 GUID string
205 Data FeedItemData
206 CreatedAt *time.Time
207}
208
209type Token struct {
210 ID string `json:"id"`
211 UserID string `json:"user_id"`
212 Name string `json:"name"`
213 CreatedAt *time.Time `json:"created_at"`
214 ExpiresAt *time.Time `json:"expires_at"`
215}
216
217type FeatureFlag struct {
218 ID string `json:"id"`
219 UserID string `json:"user_id"`
220 PaymentHistoryID string `json:"payment_history_id"`
221 Name string `json:"name"`
222 CreatedAt *time.Time `json:"created_at"`
223 ExpiresAt *time.Time `json:"expires_at"`
224 Data FeatureFlagData `json:"data"`
225}
226
227func NewFeatureFlag(userID, name string, storageMax uint64, fileMax int64) *FeatureFlag {
228 return &FeatureFlag{
229 UserID: userID,
230 Name: name,
231 Data: FeatureFlagData{
232 StorageMax: storageMax,
233 FileMax: fileMax,
234 },
235 }
236}
237
238func (ff *FeatureFlag) FindStorageMax(defaultSize uint64) uint64 {
239 if ff.Data.StorageMax == 0 {
240 return defaultSize
241 }
242 return ff.Data.StorageMax
243}
244
245func (ff *FeatureFlag) FindFileMax(defaultSize int64) int64 {
246 if ff.Data.FileMax == 0 {
247 return defaultSize
248 }
249 return ff.Data.FileMax
250}
251
252func (ff *FeatureFlag) IsValid() bool {
253 if ff.ExpiresAt.IsZero() {
254 return false
255 }
256 return ff.ExpiresAt.After(time.Now())
257}
258
259type FeatureFlagData struct {
260 StorageMax uint64 `json:"storage_max"`
261 FileMax int64 `json:"file_max"`
262}
263
264// Make the Attrs struct implement the driver.Valuer interface. This method
265// simply returns the JSON-encoded representation of the struct.
266func (p FeatureFlagData) Value() (driver.Value, error) {
267 return json.Marshal(p)
268}
269
270// Make the Attrs struct implement the sql.Scanner interface. This method
271// simply decodes a JSON-encoded value into the struct fields.
272func (p *FeatureFlagData) Scan(value interface{}) error {
273 b, ok := value.([]byte)
274 if !ok {
275 return errors.New("type assertion to []byte failed")
276 }
277 return json.Unmarshal(b, &p)
278}
279
280type PaymentHistoryData struct {
281 Notes string `json:"notes"`
282 TxID string `json:"tx_id"`
283}
284
285// Make the Attrs struct implement the driver.Valuer interface. This method
286// simply returns the JSON-encoded representation of the struct.
287func (p PaymentHistoryData) Value() (driver.Value, error) {
288 return json.Marshal(p)
289}
290
291// Make the Attrs struct implement the sql.Scanner interface. This method
292// simply decodes a JSON-encoded value into the struct fields.
293func (p *PaymentHistoryData) Scan(value interface{}) error {
294 b, ok := value.([]byte)
295 if !ok {
296 return errors.New("type assertion to []byte failed")
297 }
298 return json.Unmarshal(b, &p)
299}
300
301type ErrMultiplePublicKeys struct{}
302
303func (m *ErrMultiplePublicKeys) Error() string {
304 return "there are multiple users with this public key, you must provide the username when using SSH: `ssh <user>@<domain>`\n"
305}
306
307var NameValidator = regexp.MustCompile("^[a-zA-Z0-9]{1,50}$")
308var DenyList = []string{
309 "admin",
310 "abuse",
311 "cgi",
312 "ops",
313 "help",
314 "spec",
315 "root",
316 "new",
317 "create",
318 "www",
319}
320
321type DB interface {
322 RegisterUser(name, pubkey, comment string) (*User, error)
323 RemoveUsers(userIDs []string) error
324 UpdatePublicKey(pubkeyID, name string) (*PublicKey, error)
325 InsertPublicKey(userID, pubkey, name string, tx *sql.Tx) error
326 FindPublicKeyForKey(pubkey string) (*PublicKey, error)
327 FindPublicKey(pubkeyID string) (*PublicKey, error)
328 FindKeysForUser(user *User) ([]*PublicKey, error)
329 RemoveKeys(pubkeyIDs []string) error
330
331 FindSiteAnalytics(space string) (*Analytics, error)
332
333 FindUsers() ([]*User, error)
334 FindUserForName(name string) (*User, error)
335 FindUserForNameAndKey(name string, pubkey string) (*User, error)
336 FindUserForKey(name string, pubkey string) (*User, error)
337 FindUser(userID string) (*User, error)
338 ValidateName(name string) (bool, error)
339 SetUserName(userID string, name string) error
340
341 FindUserForToken(token string) (*User, error)
342 FindTokensForUser(userID string) ([]*Token, error)
343 InsertToken(userID, name string) (string, error)
344 UpsertToken(userID, name string) (string, error)
345 FindTokenByName(userID, name string) (string, error)
346 RemoveToken(tokenID string) error
347
348 FindPosts() ([]*Post, error)
349 FindPost(postID string) (*Post, error)
350 FindPostsForUser(pager *Pager, userID string, space string) (*Paginate[*Post], error)
351 FindAllPostsForUser(userID string, space string) ([]*Post, error)
352 FindPostsBeforeDate(date *time.Time, space string) ([]*Post, error)
353 FindExpiredPosts(space string) ([]*Post, error)
354 FindUpdatedPostsForUser(userID string, space string) ([]*Post, error)
355 FindPostWithFilename(filename string, userID string, space string) (*Post, error)
356 FindPostWithSlug(slug string, userID string, space string) (*Post, error)
357 FindAllPosts(pager *Pager, space string) (*Paginate[*Post], error)
358 FindAllUpdatedPosts(pager *Pager, space string) (*Paginate[*Post], error)
359 InsertPost(post *Post) (*Post, error)
360 UpdatePost(post *Post) (*Post, error)
361 RemovePosts(postIDs []string) error
362
363 ReplaceTagsForPost(tags []string, postID string) error
364 FindUserPostsByTag(pager *Pager, tag, userID, space string) (*Paginate[*Post], error)
365 FindPostsByTag(pager *Pager, tag, space string) (*Paginate[*Post], error)
366 FindPopularTags(space string) ([]string, error)
367 FindTagsForPost(postID string) ([]string, error)
368
369 ReplaceAliasesForPost(aliases []string, postID string) error
370
371 InsertVisit(view *AnalyticsVisits) error
372 VisitSummary(opts *SummaryOpts) (*SummaryVisits, error)
373
374 AddPicoPlusUser(username string, paymentType, txId string) error
375 FindFeatureForUser(userID string, feature string) (*FeatureFlag, error)
376 FindFeaturesForUser(userID string) ([]*FeatureFlag, error)
377 HasFeatureForUser(userID string, feature string) bool
378 FindTotalSizeForUser(userID string) (int, error)
379 InsertFeature(userID, name string, expiresAt time.Time) (*FeatureFlag, error)
380 RemoveFeature(userID, names string) error
381
382 InsertFeedItems(postID string, items []*FeedItem) error
383 FindFeedItemsByPostID(postID string) ([]*FeedItem, error)
384
385 InsertProject(userID, name, projectDir string) (string, error)
386 UpdateProject(userID, name string) error
387 UpdateProjectAcl(userID, name string, acl ProjectAcl) error
388 LinkToProject(userID, projectID, projectDir string, commit bool) error
389 RemoveProject(projectID string) error
390 FindProjectByName(userID, name string) (*Project, error)
391 FindProjectLinks(userID, name string) ([]*Project, error)
392 FindProjectsByUser(userID string) ([]*Project, error)
393 FindProjectsByPrefix(userID, name string) ([]*Project, error)
394 FindAllProjects(page *Pager, by string) (*Paginate[*Project], error)
395
396 Close() error
397}