Eric Bower
·
2025-07-04
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 FindPostsBeforeDate(date *time.Time, space string) ([]*Post, error)
412 FindExpiredPosts(space string) ([]*Post, error)
413 FindUpdatedPostsForUser(userID string, space string) ([]*Post, error)
414 FindPostWithFilename(filename string, userID string, space string) (*Post, error)
415 FindPostWithSlug(slug string, userID string, space string) (*Post, error)
416 FindAllPosts(pager *Pager, space string) (*Paginate[*Post], error)
417 FindAllUpdatedPosts(pager *Pager, space string) (*Paginate[*Post], error)
418 InsertPost(post *Post) (*Post, error)
419 UpdatePost(post *Post) (*Post, error)
420 RemovePosts(postIDs []string) error
421
422 ReplaceTagsForPost(tags []string, postID string) error
423 FindUserPostsByTag(pager *Pager, tag, userID, space string) (*Paginate[*Post], error)
424 FindPostsByTag(pager *Pager, tag, space string) (*Paginate[*Post], error)
425 FindPopularTags(space string) ([]string, error)
426 FindTagsForPost(postID string) ([]string, error)
427 FindTagsForUser(userID string, space string) ([]string, error)
428
429 ReplaceAliasesForPost(aliases []string, postID string) error
430
431 InsertVisit(view *AnalyticsVisits) error
432 VisitSummary(opts *SummaryOpts) (*SummaryVisits, error)
433 FindVisitSiteList(opts *SummaryOpts) ([]*VisitUrl, error)
434 VisitUrlNotFound(opts *SummaryOpts) ([]*VisitUrl, error)
435
436 AddPicoPlusUser(username, email, paymentType, txId string) error
437 FindFeature(userID string, feature string) (*FeatureFlag, error)
438 FindFeaturesForUser(userID string) ([]*FeatureFlag, error)
439 HasFeatureForUser(userID string, feature string) bool
440 FindTotalSizeForUser(userID string) (int, error)
441 InsertFeature(userID, name string, expiresAt time.Time) (*FeatureFlag, error)
442 RemoveFeature(userID, names string) error
443
444 InsertFeedItems(postID string, items []*FeedItem) error
445 FindFeedItemsByPostID(postID string) ([]*FeedItem, error)
446
447 UpsertProject(userID, name, projectDir string) (*Project, error)
448 FindProjectByName(userID, name string) (*Project, error)
449
450 FindUserStats(userID string) (*UserStats, error)
451
452 InsertTunsEventLog(log *TunsEventLog) error
453 FindTunsEventLogs(userID string) ([]*TunsEventLog, error)
454 FindTunsEventLogsByAddr(userID, addr string) ([]*TunsEventLog, error)
455
456 Close() error
457}