tableLinks.go (6664B)
1 package database 2 3 import ( 4 "dkforest/pkg/utils" 5 "fmt" 6 "github.com/google/uuid" 7 "github.com/sirupsen/logrus" 8 "html" 9 "regexp" 10 "time" 11 ) 12 13 type Link struct { 14 ID int64 15 UUID string 16 URL string 17 Title string 18 Description string 19 Shorthand *string 20 SignedCertificate string 21 OwnerUserID *UserID 22 CreatedAt time.Time 23 UpdatedAt time.Time 24 DeletedAt *time.Time 25 Mirrors []LinksMirror 26 OwnerUser *User 27 } 28 29 func (l Link) GenOwnershipCert(signerUsername Username) string { 30 return fmt.Sprintf(""+ 31 "DarkForest ownership certificate\n"+ 32 "\n"+ 33 "For the following onion address:\n"+ 34 "%s\n"+ 35 "\n"+ 36 "Signed by: @%s\n"+ 37 "Signed on: %s", 38 l.GetOnionAddr(), 39 signerUsername, 40 time.Now().UTC().Format("January 02, 2006")) 41 } 42 43 func (l Link) GetOnionAddr() string { 44 var onionV3Rgx = regexp.MustCompile(`[a-z2-7]{56}\.onion`) 45 return onionV3Rgx.FindString(l.URL) 46 } 47 48 func (l Link) DescriptionSafe() string { 49 return html.EscapeString(l.Description) 50 } 51 52 func (l *Link) Save(db *DkfDB) error { 53 return db.db.Save(l).Error 54 } 55 56 func (l *Link) DoSave(db *DkfDB) { 57 if err := l.Save(db); err != nil { 58 logrus.Error(err) 59 } 60 } 61 62 func (d *DkfDB) CreateLink(url, title, description, shorthand string) (out Link, err error) { 63 out = Link{UUID: uuid.New().String(), URL: url, Title: title, Description: description} 64 if shorthand != "" { 65 out.Shorthand = &shorthand 66 } 67 err = d.db.FirstOrCreate(&out, "url = ?", url).Error 68 return 69 } 70 71 func (d *DkfDB) DeleteLinkByID(id int64) error { 72 return d.db.Where("id = ?", id).Delete(&Link{}).Error 73 } 74 75 func (d *DkfDB) GetLinks() (out []Link, err error) { 76 err = d.db.Find(&out).Error 77 return 78 } 79 80 func (d *DkfDB) GetRecentLinks() (out []Link, err error) { 81 err = d.db.Order("id DESC").Limit(100).Find(&out).Error 82 return 83 } 84 85 func (d *DkfDB) GetLinkByShorthand(shorthand string) (out Link, err error) { 86 err = d.db.Preload("OwnerUser").First(&out, "shorthand = ?", shorthand).Error 87 return 88 } 89 90 func (d *DkfDB) GetLinkByUUID(linkUUID string) (out Link, err error) { 91 err = d.db.Preload("OwnerUser").First(&out, "uuid = ?", linkUUID).Error 92 return 93 } 94 95 func (d *DkfDB) GetLinkByID(linkID int64) (out Link, err error) { 96 err = d.db.First(&out, "id = ?", linkID).Error 97 return 98 } 99 100 type LinksCategory struct { 101 ID int64 102 Name string 103 } 104 105 func (d *DkfDB) CreateLinksCategory(category string) (out LinksCategory, err error) { 106 out = LinksCategory{Name: category} 107 err = d.db.FirstOrCreate(&out, "name = ?", category).Error 108 return 109 } 110 111 type LinksTag struct { 112 ID int64 113 Name string 114 } 115 116 func (d *DkfDB) CreateLinksTag(tag string) (out LinksTag, err error) { 117 out = LinksTag{Name: tag} 118 err = d.db.FirstOrCreate(&out, "name = ?", tag).Error 119 return 120 } 121 122 type LinksTagsLink struct { 123 LinkID int64 124 TagID int64 125 } 126 127 func (d *DkfDB) AddLinkTag(linkID, tagID int64) (err error) { 128 return d.db.Create(&LinksTagsLink{LinkID: linkID, TagID: tagID}).Error 129 } 130 131 type LinksCategoriesLink struct { 132 CategoryID int64 133 LinkID int64 134 } 135 136 func (d *DkfDB) AddLinkCategory(linkID, categoryID int64) (err error) { 137 return d.db.Create(&LinksCategoriesLink{CategoryID: categoryID, LinkID: linkID}).Error 138 } 139 140 type CategoriesResult struct { 141 Name string 142 Count int64 143 } 144 145 func (d *DkfDB) GetCategories() (out []CategoriesResult, err error) { 146 err = d.db.Raw(`SELECT 147 c.name, count(cl.link_id) as count 148 FROM links_categories_links cl 149 INNER JOIN links_categories c ON c.id = cl.category_id 150 INNER JOIN links l ON l.id = cl.link_id AND l.deleted_at IS NULL 151 GROUP BY category_id 152 ORDER BY c.name`).Scan(&out).Error 153 return 154 } 155 156 func (d *DkfDB) GetLinkCategories(linkID int64) (out []LinksCategory, err error) { 157 err = d.db.Raw(`SELECT 158 c.id, c.name 159 FROM links_categories_links cl 160 INNER JOIN links_categories c ON c.id = cl.category_id 161 WHERE cl.link_id = ? 162 ORDER BY c.name`, linkID).Scan(&out).Error 163 return 164 } 165 166 func (d *DkfDB) GetLinkTags(linkID int64) (out []LinksTag, err error) { 167 err = d.db.Raw(`SELECT 168 t.id, t.name 169 FROM links_tags_links tl 170 INNER JOIN links_tags t ON t.id = tl.tag_id 171 WHERE tl.link_id = ? 172 ORDER BY t.name`, linkID).Scan(&out).Error 173 return 174 } 175 176 // LinksCategoriesLinks many-to-many table 177 type LinksCategoriesLinks struct { 178 LinkID int64 179 CategoryID int64 180 } 181 182 func (d *DkfDB) GetTags() (out []LinksTag, err error) { 183 err = d.db.Find(&out).Error 184 return 185 } 186 187 func (d *DkfDB) GetLinksCategories() (out []LinksCategory, err error) { 188 err = d.db.Find(&out).Error 189 return 190 } 191 192 func (d *DkfDB) GetCategoriesLinks() (out []LinksCategoriesLinks, err error) { 193 err = d.db.Find(&out).Error 194 return 195 } 196 197 func (d *DkfDB) DeleteLinkCategories(linkID int64) error { 198 return d.db.Delete(&LinksCategoriesLink{}, "link_id = ?", linkID).Error 199 } 200 201 func (d *DkfDB) DeleteLinkTags(linkID int64) error { 202 return d.db.Delete(&LinksTagsLink{}, "link_id = ?", linkID).Error 203 } 204 205 type LinksMirror struct { 206 ID int64 207 LinkID int64 208 Idx int64 209 MirrorURL string 210 } 211 212 type LinksPgp struct { 213 ID int64 214 LinkID int64 215 Idx int64 216 Title string 217 Description string 218 PgpPublicKey string 219 } 220 221 func (l LinksPgp) GetKeyID() string { 222 if e := utils.GetEntityFromPKey(l.PgpPublicKey); e != nil { 223 return e.PrimaryKey.KeyIdString() 224 } 225 return "n/a" 226 } 227 228 func (l LinksPgp) GetKeyFingerprint() string { 229 out := "n/a" 230 if fingerprint := utils.GetKeyFingerprint(l.PgpPublicKey); fingerprint != "" { 231 out = fingerprint 232 } 233 return out 234 } 235 236 func (d *DkfDB) CreateLinkPgp(linkID int64, title, description, publicKey string) (out LinksPgp, err error) { 237 out = LinksPgp{ 238 LinkID: linkID, 239 Title: title, 240 Description: description, 241 PgpPublicKey: publicKey, 242 } 243 err = d.db.Create(&out).Error 244 return 245 } 246 247 func (d *DkfDB) CreateLinkMirror(linkID int64, link string) (out LinksMirror, err error) { 248 out = LinksMirror{ 249 LinkID: linkID, 250 MirrorURL: link, 251 } 252 err = d.db.Create(&out).Error 253 return 254 } 255 256 func (d *DkfDB) GetLinkPgps(linkID int64) (out []LinksPgp, err error) { 257 err = d.db.Find(&out, "link_id = ?", linkID).Error 258 return 259 } 260 261 func (d *DkfDB) GetLinkMirrors(linkID int64) (out []LinksMirror, err error) { 262 err = d.db.Find(&out, "link_id = ?", linkID).Error 263 return 264 } 265 266 func (d *DkfDB) GetLinkPgpByID(id int64) (out LinksPgp, err error) { 267 err = d.db.First(&out, "id = ?", id).Error 268 return 269 } 270 271 func (d *DkfDB) GetLinkMirrorByID(id int64) (out LinksMirror, err error) { 272 err = d.db.First(&out, "id = ?", id).Error 273 return 274 } 275 276 func (d *DkfDB) DeleteLinkPgpByID(id int64) error { 277 return d.db.Where("id = ?", id).Delete(&LinksPgp{}).Error 278 } 279 280 func (d *DkfDB) DeleteLinkMirrorByID(id int64) error { 281 return d.db.Where("id = ?", id).Delete(&LinksMirror{}).Error 282 }