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