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