1package storage 2 3import ( 4 "encoding/json" 5 "fmt" 6 "log" 7 "strings" 8 "time" 9 10 "github.com/nkanaev/yarr/src/content/htmlutil" 11) 12 13type ItemStatus int 14 15const ( 16 UNREAD ItemStatus = 0 17 READ ItemStatus = 1 18 STARRED ItemStatus = 2 19) 20 21var StatusRepresentations = map[ItemStatus]string{ 22 UNREAD: "unread", 23 READ: "read", 24 STARRED: "starred", 25} 26 27var StatusValues = map[string]ItemStatus{ 28 "unread": UNREAD, 29 "read": READ, 30 "starred": STARRED, 31} 32 33func (s ItemStatus) MarshalJSON() ([]byte, error) { 34 return json.Marshal(StatusRepresentations[s]) 35} 36 37func (s *ItemStatus) UnmarshalJSON(b []byte) error { 38 var str string 39 if err := json.Unmarshal(b, &str); err != nil { 40 return err 41 } 42 *s = StatusValues[str] 43 return nil 44} 45 46type Item struct { 47 Id int64 `json:"id"` 48 GUID string `json:"guid"` 49 FeedId int64 `json:"feed_id"` 50 Title string `json:"title"` 51 Link string `json:"link"` 52 Content string `json:"content,omitempty"` 53 Date time.Time `json:"date"` 54 Status ItemStatus `json:"status"` 55 ImageURL *string `json:"image"` 56 AudioURL *string `json:"podcast_url"` 57} 58 59type ItemFilter struct { 60 FolderID *int64 61 FeedID *int64 62 Status *ItemStatus 63 Search *string 64 After *int64 65} 66 67type MarkFilter struct { 68 FolderID *int64 69 FeedID *int64 70} 71 72func (s *Storage) CreateItems(items []Item) bool { 73 tx, err := s.db.Begin() 74 if err != nil { 75 log.Print(err) 76 return false 77 } 78 79 now := time.Now() 80 81 for _, item := range items { 82 _, err = tx.Exec(` 83 insert into items ( 84 guid, feed_id, title, link, date, 85 content, image, podcast_url, 86 date_arrived, status 87 ) 88 values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 89 on conflict (feed_id, guid) do nothing`, 90 item.GUID, item.FeedId, item.Title, item.Link, item.Date, 91 item.Content, item.ImageURL, item.AudioURL, 92 now, UNREAD, 93 ) 94 if err != nil { 95 log.Print(err) 96 if err = tx.Rollback(); err != nil { 97 log.Print(err) 98 return false 99 } 100 return false 101 } 102 } 103 if err = tx.Commit(); err != nil { 104 log.Print(err) 105 return false 106 } 107 return true 108} 109 110func listQueryPredicate(filter ItemFilter, newestFirst bool) (string, []interface{}) { 111 cond := make([]string, 0) 112 args := make([]interface{}, 0) 113 if filter.FolderID != nil { 114 cond = append(cond, "i.feed_id in (select id from feeds where folder_id = ?)") 115 args = append(args, *filter.FolderID) 116 } 117 if filter.FeedID != nil { 118 cond = append(cond, "i.feed_id = ?") 119 args = append(args, *filter.FeedID) 120 } 121 if filter.Status != nil { 122 cond = append(cond, "i.status = ?") 123 args = append(args, *filter.Status) 124 } 125 if filter.Search != nil { 126 words := strings.Fields(*filter.Search) 127 terms := make([]string, len(words)) 128 for idx, word := range words { 129 terms[idx] = word + "*" 130 } 131 132 cond = append(cond, "i.search_rowid in (select rowid from search where search match ?)") 133 args = append(args, strings.Join(terms, " ")) 134 } 135 if filter.After != nil { 136 compare := ">" 137 if newestFirst { 138 compare = "<" 139 } 140 cond = append(cond, fmt.Sprintf("(i.date, i.id) %s (select date, id from items where id = ?)", compare)) 141 args = append(args, *filter.After) 142 } 143 144 predicate := "1" 145 if len(cond) > 0 { 146 predicate = strings.Join(cond, " and ") 147 } 148 149 return predicate, args 150} 151 152func (s *Storage) ListItems(filter ItemFilter, limit int, newestFirst bool) []Item { 153 predicate, args := listQueryPredicate(filter, newestFirst) 154 result := make([]Item, 0, 0) 155 156 order := "date desc, id desc" 157 if !newestFirst { 158 order = "date asc, id asc" 159 } 160 161 query := fmt.Sprintf(` 162 select 163 i.id, i.guid, i.feed_id, 164 i.title, i.link, i.date, 165 i.status, i.image, i.podcast_url 166 from items i 167 where %s 168 order by %s 169 limit %d 170 `, predicate, order, limit) 171 rows, err := s.db.Query(query, args...) 172 if err != nil { 173 log.Print(err) 174 return result 175 } 176 for rows.Next() { 177 var x Item 178 err = rows.Scan( 179 &x.Id, &x.GUID, &x.FeedId, 180 &x.Title, &x.Link, &x.Date, 181 &x.Status, &x.ImageURL, &x.AudioURL, 182 ) 183 if err != nil { 184 log.Print(err) 185 return result 186 } 187 result = append(result, x) 188 } 189 return result 190} 191 192func (s *Storage) GetItem(id int64) *Item { 193 i := &Item{} 194 err := s.db.QueryRow(` 195 select 196 i.id, i.guid, i.feed_id, i.title, i.link, i.content, 197 i.date, i.status, i.image, i.podcast_url 198 from items i 199 where i.id = ? 200 `, id).Scan( 201 &i.Id, &i.GUID, &i.FeedId, &i.Title, &i.Link, &i.Content, 202 &i.Date, &i.Status, &i.ImageURL, &i.AudioURL, 203 ) 204 if err != nil { 205 log.Print(err) 206 return nil 207 } 208 return i 209} 210 211func (s *Storage) UpdateItemStatus(item_id int64, status ItemStatus) bool { 212 _, err := s.db.Exec(`update items set status = ? where id = ?`, status, item_id) 213 return err == nil 214} 215 216func (s *Storage) MarkItemsRead(filter MarkFilter) bool { 217 predicate, args := listQueryPredicate(ItemFilter{FolderID: filter.FolderID, FeedID: filter.FeedID}, false) 218 query := fmt.Sprintf(` 219 update items as i set status = %d 220 where %s and i.status != %d 221 `, READ, predicate, STARRED) 222 _, err := s.db.Exec(query, args...) 223 if err != nil { 224 log.Print(err) 225 } 226 return err == nil 227} 228 229type FeedStat struct { 230 FeedId int64 `json:"feed_id"` 231 UnreadCount int64 `json:"unread"` 232 StarredCount int64 `json:"starred"` 233} 234 235func (s *Storage) FeedStats() []FeedStat { 236 result := make([]FeedStat, 0) 237 rows, err := s.db.Query(fmt.Sprintf(` 238 select 239 feed_id, 240 sum(case status when %d then 1 else 0 end), 241 sum(case status when %d then 1 else 0 end) 242 from items 243 group by feed_id 244 `, UNREAD, STARRED)) 245 if err != nil { 246 log.Print(err) 247 return result 248 } 249 for rows.Next() { 250 stat := FeedStat{} 251 rows.Scan(&stat.FeedId, &stat.UnreadCount, &stat.StarredCount) 252 result = append(result, stat) 253 } 254 return result 255} 256 257func (s *Storage) SyncSearch() { 258 rows, err := s.db.Query(` 259 select id, title, content 260 from items 261 where search_rowid is null; 262 `) 263 if err != nil { 264 log.Print(err) 265 return 266 } 267 268 items := make([]Item, 0) 269 for rows.Next() { 270 var item Item 271 rows.Scan(&item.Id, &item.Title, &item.Content) 272 items = append(items, item) 273 } 274 275 for _, item := range items { 276 result, err := s.db.Exec(` 277 insert into search (title, description, content) values (?, "", ?)`, 278 item.Title, htmlutil.ExtractText(item.Content), 279 ) 280 if err != nil { 281 log.Print(err) 282 return 283 } 284 if numrows, err := result.RowsAffected(); err == nil && numrows == 1 { 285 if rowId, err := result.LastInsertId(); err == nil { 286 s.db.Exec( 287 `update items set search_rowid = ? where id = ?`, 288 rowId, item.Id, 289 ) 290 } 291 } 292 } 293} 294 295func (s *Storage) DeleteOldItems() { 296 rows, err := s.db.Query(fmt.Sprintf(` 297 select feed_id, count(*) as num_items 298 from items 299 where status != %d 300 group by feed_id 301 having num_items > 50 302 `, STARRED)) 303 304 if err != nil { 305 log.Print(err) 306 return 307 } 308 309 feedIds := make([]int64, 0) 310 for rows.Next() { 311 var id int64 312 rows.Scan(&id, nil) 313 feedIds = append(feedIds, id) 314 } 315 316 for _, feedId := range feedIds { 317 result, err := s.db.Exec(` 318 delete from items where feed_id = ? and status != ? and date_arrived < ?`, 319 feedId, 320 STARRED, 321 time.Now().Add(-time.Hour*24*90), // 90 days 322 ) 323 if err != nil { 324 log.Print(err) 325 return 326 } 327 num, err := result.RowsAffected() 328 if err != nil { 329 log.Print(err) 330 return 331 } 332 if num > 0 { 333 log.Printf("Deleted %d old items (%d)", num, feedId) 334 } 335 } 336} 337