Noss/DB.pm

filter: Statementstmt 79.6% 199 / 250 Branchbran 59.0% 78 / 132 Conditioncond 41.0% 16 / 39 MC/DCmcdc 12.5% 3 / 24 Subroutinesub 93.7% 30 / 32 Podpod 100% 14 / 14 totaltotal 69.2% 340 / 491 CC 83 SCAR 29.7
CC
83
Cov
70%
skip
56.0
look
39.6
first_unread
16.9
CRAP 19.6
« Noss/FeedReader/Atom.pm ↑ Summary Noss/TextToHtml.pm »
1 package WWW::Noss::DB;
2 2 ▶use 5.016;
Subroutine BEGIN: called CC=1 SCAR=0.0
3 2 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
4 2 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
5 2 our $VERSION = '2.05';
6  
7 2 ▶use List::Util qw(all any max none);
Subroutine BEGIN: called CC=1 SCAR=0.0
8  
9 2 ▶use DBD::SQLite;
Subroutine BEGIN: called CC=1 SCAR=0.0
10 2 ▶use DBI;
Subroutine BEGIN: called CC=1 SCAR=0.0
11 2 ▶use JSON;
Subroutine BEGIN: called CC=1 SCAR=0.0
12  
13 2 ▶use WWW::Noss::FeedReader qw(read_feed);
Subroutine BEGIN: called CC=1 SCAR=0.0
14 2 ▶use WWW::Noss::Util qw(resolve_url);
Subroutine BEGIN: called CC=1 SCAR=0.0
15  
16 2 my %DAY_MAP = (
17 0 => 'Sunday',
18 1 => 'Monday',
19 2 => 'Tuesday',
20 3 => 'Wednesday',
21 4 => 'Thursday',
22 5 => 'Friday',
23 6 => 'Saturday',
24 );
25  
26 2 ▶sub _initialize {
Subroutine _initialize: called CC=3 SCAR=11.3
27  
28 2 my ($self) = @_;
29  
30 2 $self->{ DB } = DBI->connect(
31 "dbi:SQLite:dbname=$self->{ Path }", '', '',
32 {
33 RaiseError => 1,
34 AutoInactiveDestroy => 1,
35 }
36 );
37  
38 2 $self->{ DB }->do('PRAGMA journal_mode = WAL');
39 2 $self->{ DB }->do('PRAGMA cache_size = 10000');
40  
41 $self->{ DB }->do(
42 2 q{
43 CREATE TABLE IF NOT EXISTS feeds (
44 nossname TEXT NOT NULL UNIQUE,
45 nosslink TEXT NOT NULL,
46 title TEXT NOT NULL,
47 link TEXT,
48 description TEXT,
49 updated INTEGER,
50 author TEXT,
51 category TEXT,
52 generator TEXT,
53 image TEXT,
54 rights TEXT,
55 skiphours TEXT,
56 skipdays TEXT
57 );
58 }
59 );
60  
61 $self->{ DB }->do(
62 2 q{
63 CREATE INDEX IF NOT EXISTS
64 idx_feeds_nossname
65 ON
66 feeds(nossname);
67 }
68 );
69  
70 $self->{ DB }->do(
71 2 q{
72 CREATE TABLE IF NOT EXISTS posts (
73 nossid INTEGER NOT NULL,
74 status TEXT NOT NULL,
75 feed TEXT NOT NULL,
76 title TEXT,
77 link TEXT,
78 author TEXT,
79 category TEXT,
80 summary TEXT,
81 published INTEGER,
82 updated INTEGER,
83 uid TEXT,
84 nossuid TEXT NOT NULL,
85 displaytitle TEXT
86 );
87 }
88 );
89  
90 26 my @post_cols = map { $_->[0] } @{
91 2 $self->{ DB }->selectall_arrayref(
92 2 q{
93 SELECT
94 name
95 FROM pragma_table_info('posts');
96 }
97 )
98 };
99  
100 # TODO: Someday, we should be able to get rid of this
101 # Pre-1.06 databases do not have nossuid, add it.
102 2 ▶ if (none { $_ eq 'nossuid' } @post_cols) {
Subroutine __ANON__: called CC=1 SCAR=0.2
Branch: 1 branch
BranchTrueFalseTotal
if (&none(sub { $_ eq "nossuid"; } , @post_cols)) 0 2 2
103 $self->{ DB }->do(
104 0 q{
105 ALTER TABLE
106 posts
107 ADD COLUMN
108 nossuid TEXT NOT NULL DEFAULT ';;;;';
109 }
110 );
111 $self->{ DB }->do(
112 0 q{
113 UPDATE
114 posts
115 SET
116 nossuid = (
117 ifnull(uid, '') || ';' ||
118 ifnull(feed, '') || ';' ||
119 ifnull(title, '') || ';' ||
120 ifnull(link, '') || ';' ||
121 ifnull(published, '')
122 );
123 }
124 ); # no concat_ws() in the sqlite version we support :-(
125 }
126  
127 # Pre-1.09 databases do not have display title, add it.
128 2 ▶ if (none { $_ eq 'displaytitle' } @post_cols) {
Subroutine __ANON__: called CC=1 SCAR=0.2
Branch: 1 branch
BranchTrueFalseTotal
if (&none(sub { $_ eq "displaytitle"; } , @post_cols)) 0 2 2
129 $self->{ DB }->do(
130 0 q{
131 ALTER TABLE
132 posts
133 ADD COLUMN
134 displaytitle TEXT;
135 }
136 );
137 $self->{ DB }->do(
138 0 q{
139 UPDATE
140 posts
141 SET
142 displaytitle = title;
143 }
144 );
145 }
146  
147 $self->{ DB }->do(
148 2 q{
149 CREATE INDEX IF NOT EXISTS
150 idx_posts_uid
151 ON
152 posts(uid);
153 }
154 );
155  
156 $self->{ DB }->do(
157 2 q{
158 CREATE INDEX IF NOT EXISTS
159 idx_posts_feedid
160 ON
161 posts(feed, nossid);
162 }
163 );
164  
165 $self->{ DB }->do(
166 2 q{
167 CREATE UNIQUE INDEX IF NOT EXISTS
168 idx_posts_nossuid
169 ON
170 posts(nossuid);
171 }
172 );
173  
174 2 return 1;
175  
176 }
177  
178 2 ▶sub new {
Subroutine new: called CC=1 SCAR=0.0
Pod documented
179  
180 2 my ($class, $file) = @_;
181  
182 2 my $self = {
183 Path => undef,
184 DB => undef,
185 };
186  
187 2 bless $self, $class;
188  
189 2 $self->{ Path } = $file;
190  
191 2 $self->_initialize;
192  
193 2 return $self;
194  
195 }
196  
197 2 ▶sub has_feed {
Subroutine has_feed: called CC=2 SCAR=7.0
Pod documented
198  
199 2 my ($self, $feed) = @_;
200  
201 my $name =
202 2 ▶ eval { $feed->isa('WWW::Noss::FeedConfig') }
Branch: 1 branch
BranchTrueFalseTotal
eval { do { $feed->isa('WWW::Noss::FeedConfig') } } ? : 0 2 2
203 ? $feed->name
204 : $feed;
205  
206 my $row = $self->{ DB }->selectrow_arrayref(
207 2 q{
208 SELECT
209 rowid
210 FROM
211 feeds
212 WHERE
213 nossname = ?;
214 },
215 undef,
216 $name
217 );
218  
219 2 return defined $row;
220  
221 }
222  
223 2 ▶sub load_feed {
Subroutine load_feed: called CC=7 SCAR=19.6
Pod documented
224  
225 2 my ($self, $feed) = @_;
226  
227 2 my ($feedref, $postref) = read_feed($feed);
228  
229 $self->{ DB }->do(
230 q{
231 INSERT INTO feeds(
232 nossname,
233 nosslink,
234 title,
235 link,
236 description,
237 updated,
238 author,
239 category,
240 generator,
241 image,
242 rights,
243 skiphours,
244 skipdays
245 )
246 VALUES(
247 ?,
248 ?,
249 ?,
250 ?,
251 ?,
252 (0 + ?),
253 ?,
254 ?,
255 ?,
256 ?,
257 ?,
258 ?,
259 ?
260 )
261 ON CONFLICT (nossname) DO
262 UPDATE
263 SET
264 nossname = excluded.nossname,
265 nosslink = excluded.nosslink,
266 title = excluded.title,
267 link = excluded.link,
268 description = excluded.description,
269 updated = excluded.updated,
270 author = excluded.author,
271 category = excluded.category,
272 generator = excluded.generator,
273 image = excluded.image,
274 rights = excluded.rights,
275 skiphours = excluded.skiphours,
276 skipdays = excluded.skipdays;
277 },
278 undef,
279 $feedref->{ nossname },
280 $feedref->{ nosslink },
281 $feedref->{ title },
282 $feedref->{ link },
283 $feedref->{ description },
284 $feedref->{ updated },
285 $feedref->{ author },
286 (defined $feedref->{ category } ? encode_json($feedref->{ category }) : undef),
287 $feedref->{ generator },
288 $feedref->{ image },
289 $feedref->{ rights },
290 (defined $feedref->{ skiphours } ? encode_json($feedref->{ skiphours }) : undef),
291 2 ▶ (defined $feedref->{ skipdays } ? encode_json($feedref->{ skipdays }) : undef),
Branch: 3 branches
BranchTrueFalseTotal
defined $feedref->{'category'} ? : 2 0 2
defined $feedref->{'skiphours'} ? : 1 1 2
defined $feedref->{'skipdays'} ? : 1 1 2
292 );
293  
294 my $upsert_post = $self->{ DB }->prepare(
295 2 q{
296 INSERT INTO posts(
297 nossid,
298 status,
299 feed,
300 title,
301 link,
302 author,
303 category,
304 summary,
305 published,
306 updated,
307 uid,
308 nossuid,
309 displaytitle
310 )
311 VALUES (
312 (0 + ?),
313 ?,
314 ?,
315 ?,
316 ?,
317 ?,
318 ?,
319 ?,
320 (0 + ?),
321 (0 + ?),
322 ?,
323 ?,
324 ?
325 )
326 ON CONFLICT (nossuid) DO
327 UPDATE
328 SET
329 nossid = excluded.nossid,
330 author = excluded.author,
331 category = excluded.category,
332 summary = excluded.summary,
333 updated = excluded.updated,
334 displaytitle = excluded.displaytitle;
335 },
336 );
337  
338 2 my $new = 0;
339 2 my @ok;
340  
341 $self->{ DB }->sqlite_update_hook(
342 20 ▶ sub {
Subroutine __ANON__: called CC=2 SCAR=6.9
343 20 ▶ $new++ if $_[0] == DBD::SQLite::INSERT;
Branch: 1 branch
BranchTrueFalseTotal
if $_[0] == &DBD::SQLite::INSERT() 9 11 20
344 20 push @ok, $_[3];
345 }
346 2 );
347  
348 2 for my $e (@$postref) {
349 $upsert_post->execute(
350 $e->{ nossid },
351 ($feed->autoread ? 'read' : 'unread'),
352 $e->{ feed },
353 $e->{ title },
354 $e->{ link },
355 $e->{ author },
356 (defined $e->{ category } ? encode_json($e->{ category }) : undef),
357 $e->{ summary },
358 $e->{ published },
359 $e->{ updated },
360 $e->{ uid },
361 $e->{ nossuid },
362 $e->{ displaytitle },
363 8 ▶ );
Branch: 2 branches
BranchTrueFalseTotal
$feed->autoread ? : 0 8 8
defined $e->{'category'} ? : 8 0 8
364 }
365  
366 2 my $ok_set = sprintf "(%s)", join ",", @ok;
367  
368 $self->{ DB }->do(
369 2 qq{
370 DELETE FROM
371 posts
372 WHERE
373 feed = ? AND
374 rowid NOT IN $ok_set;
375 },
376 undef,
377 $feed->name,
378 );
379  
380 2 return $new;
381  
382 }
383  
384 1 ▶sub feed {
Subroutine feed: called CC=10 SCAR=27.7
Pod documented
385  
386 1 my ($self, $feed, %param) = @_;
387  
388 my $name =
389 1 ▶ eval { $feed->isa('WWW::Noss::FeedConfig') }
Branch: 1 branch
BranchTrueFalseTotal
eval { do { $feed->isa('WWW::Noss::FeedConfig') } } ? : 0 1 1
390 ? $feed->name
391 : $feed;
392  
393 my $row = $self->{ DB }->selectrow_hashref(
394 1 q{
395 SELECT
396 nossname,
397 nosslink,
398 title,
399 link,
400 description,
401 updated,
402 author,
403 category,
404 generator,
405 image,
406 rights,
407 skiphours,
408 skipdays
409 FROM
410 feeds
411 WHERE
412 nossname = ?;
413 },
414 undef,
415 $name
416 );
417  
418 1 ▶ return undef unless defined $row;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $row 0 1 1
419  
420 $row->{ category } =
421 defined $row->{ category }
422 ? decode_json($row->{ category })
423 1 ▶ : [];
Branch: 1 branch
BranchTrueFalseTotal
defined $row->{'category'} ? : 1 0 1
424 $row->{ skiphours } =
425 defined $row->{ skiphours }
426 ? decode_json($row->{ skiphours })
427 1 ▶ : [];
Branch: 1 branch
BranchTrueFalseTotal
defined $row->{'skiphours'} ? : 0 1 1
428 $row->{ skipdays } =
429 defined $row->{ skipdays }
430 ? decode_json($row->{ skipdays })
431 1 ▶ : [];
Branch: 1 branch
BranchTrueFalseTotal
defined $row->{'skipdays'} ? : 0 1 1
432 1 ▶ if (defined $row->{ link }) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $row->{"link"}) 1 0 1
433 1 $row->{ link } = resolve_url($row->{ link }, $row->{ nosslink });
434 }
435  
436 1 ▶ if ($param{ post_info }) {
Branch: 1 branch
BranchTrueFalseTotal
if ($param{"post_info"}) 1 0 1
437  
438 my $posts = $self->{ DB }->selectall_arrayref(
439 1 q{
440 SELECT
441 status,
442 updated,
443 published
444 FROM
445 posts
446 WHERE
447 feed = ?;
448 },
449 undef,
450 $name
451 );
452  
453 $row->{ updated } //=
454 0 (max grep { defined } map { $_->[1] } @$posts) //
455 0 ▶ (max grep { defined } map { $_->[2] } @$posts);
Condition16%1/6
$row->{"updated"} //= &max(grep({defined $_;} map({$_->[1];} @$posts))) // &max(grep({defined $_;} map({$_->[2];} @$posts)))
l!l&&r!l&&!r
100
&max(grep({defined $_;} map({$_->[1];} @$posts))) // &max(grep({defined $_;} map({$_->[2];} @$posts)))
l!l&&r!l&&!r
000
MC/DC0%0/3
$row->{"updated"} //= &max(grep({defined $_;} map({$_->[1];} @$posts))) // &max(grep({defined $_;} map({$_->[2];} @$posts)))
$row->{"updated"} &max(grep({defined $_;} map({$_->[1];} @$posts))) &max(grep({defined $_;} map({$_->[2];} @$posts)))
Truth table0%0/4
A //= B // C
A= $row->{"updated"}B= &max(grep({defined $_;} map({$_->[1];} @$posts)))C= &max(grep({defined $_;} map({$_->[2];} @$posts)))
ABCresult
1XX1
01X1
0011
0000
456  
457 1 $row->{ posts } = scalar @$posts;
458 1 $row->{ unread } = scalar grep { $_->[0] eq 'unread' } @$posts;
459  
460 }
461  
462 1 return $row;
463  
464 }
465  
466 2 ▶sub feeds {
Subroutine feeds: called CC=6 SCAR=18.0
Pod documented
467  
468 2 my ($self) = @_;
469  
470 my $feeds = $self->{ DB }->selectall_arrayref(
471 2 q{
472 SELECT
473 nossname,
474 nosslink,
475 title,
476 link,
477 description,
478 updated,
479 author,
480 category,
481 generator,
482 image,
483 rights,
484 skiphours,
485 skipdays
486 FROM
487 feeds;
488 },
489 { Slice => {} },
490 );
491  
492 2 for my $f (@$feeds) {
493 $f->{ category } =
494 defined $f->{ category }
495 ? decode_json($f->{ category })
496 4 ▶ : [];
Branch: 1 branch
BranchTrueFalseTotal
defined $f->{'category'} ? : 4 0 4
497 $f->{ skiphours } =
498 defined $f->{ skiphours }
499 ? decode_json($f->{ skiphours })
500 4 ▶ : [];
Branch: 1 branch
BranchTrueFalseTotal
defined $f->{'skiphours'} ? : 2 2 4
501 $f->{ skipdays } =
502 defined $f->{ skipdays }
503 ? decode_json($f->{ skipdays })
504 4 ▶ : [];
Branch: 1 branch
BranchTrueFalseTotal
defined $f->{'skipdays'} ? : 2 2 4
505 4 ▶ if (defined $f->{ link }) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $f->{"link"}) 4 0 4
506 4 $f->{ link } = resolve_url($f->{ link }, $f->{ nosslink });
507 }
508 }
509  
510 2 return @$feeds;
511  
512 }
513  
514 0 ▶sub del_feeds {
Subroutine del_feeds: not called CC=1 SCAR=6.9
Pod documented
515  
516 0 my ($self, @feeds) = @_;
517  
518 my $feed_set =
519 sprintf '(%s)',
520 join ',',
521 0 map { $self->{ DB }->quote($_) }
522 @feeds;
523  
524 $self->{ DB }->do(
525 0 qq{
526 DELETE FROM
527 feeds
528 WHERE
529 nossname IN $feed_set;
530 }
531 );
532  
533 $self->{ DB }->do(
534 0 qq{
535 DELETE FROM
536 posts
537 WHERE
538 feed IN $feed_set;
539 }
540 );
541  
542 0 return 1;
543  
544 }
545  
546 10 ▶sub post {
Subroutine post: called CC=9 SCAR=27.2
Pod documented
547  
548 10 my ($self, $feed, $post) = @_;
549  
550 my $name =
551 10 ▶ eval { $feed->isa('WWW::Noss::FeedConfig') }
Branch: 1 branch
BranchTrueFalseTotal
eval { do { $feed->isa('WWW::Noss::FeedConfig') } } ? : 0 10 10
552 ? $feed->name
553 : $feed;
554  
555 10 my $postref;
556  
557 10 ▶ if ($post < 0) {
Branch: 1 branch
BranchTrueFalseTotal
if ($post < 0) { } 1 9 10
558 1 my $off = -$post - 1;
559 $postref = $self->{ DB }->selectrow_hashref(
560 1 qq{
561 SELECT
562 nossid,
563 status,
564 feed,
565 title,
566 link,
567 author,
568 category,
569 summary,
570 published,
571 updated,
572 uid,
573 nossuid,
574 displaytitle
575 FROM
576 posts
577 WHERE
578 feed = ?
579 ORDER BY
580 nossid DESC
581 LIMIT 1 OFFSET $off;
582 },
583 undef,
584 $feed
585 );
586 } else {
587 $postref = $self->{ DB }->selectrow_hashref(
588 9 q{
589 SELECT
590 nossid,
591 status,
592 feed,
593 title,
594 link,
595 author,
596 category,
597 summary,
598 published,
599 updated,
600 uid,
601 nossuid,
602 displaytitle
603 FROM
604 posts
605 WHERE
606 feed = ? AND
607 nossid = (0 + ?);
608 },
609 undef,
610 $feed,
611 $post
612 );
613 }
614  
615 10 ▶ return undef unless defined $postref;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $postref 0 10 10
616  
617 # TODO: Move DB -> Perl logic to seperate subroutine?
618 $postref->{ category } =
619 defined $postref->{ category }
620 ? decode_json($postref->{ category })
621 10 ▶ : [];
Branch: 1 branch
BranchTrueFalseTotal
defined $postref->{'category'} ? : 10 0 10
622 # Convert '/foo' and '//foo' relative links
623 10 ▶ if (defined $postref->{ link } and $postref->{ link } =~ /^\//) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $postref->{"link"} and $postref->{"link"} =~ m[^/]u) 0 10 10
Condition33%1/3
defined $postref->{"link"} and $postref->{"link"} =~ m[^/]u
!ll&&!rl&&r
0100
MC/DC0%0/2
defined $postref->{"link"} and $postref->{"link"} =~ m[^/]u
defined $postref->{"link"} $postref->{"link"} =~ m[^/]u
Truth table33%1/3
A and B
A= defined $postref->{"link"}B= $postref->{"link"} =~ m[^/]u
ABresult
0X0
100
111
624 0 my $feed_info = $self->feed($feed);
625 my $conf = resolve_url(
626 $postref->{ link },
627 $feed_info->{ link } // $feed_info->{ nosslink }
628 0 ▶ );
Condition0%0/3
$feed_info->{"link"} // $feed_info->{"nosslink"}
l!l&&r!l&&!r
000
MC/DC0%0/2
$feed_info->{"link"} // $feed_info->{"nosslink"}
$feed_info->{"link"} $feed_info->{"nosslink"}
Truth table0%0/3
A // B
A= $feed_info->{"link"}B= $feed_info->{"nosslink"}
ABresult
1X1
011
000
629 0 ▶ $postref->{ link } = $conf if defined $conf;
Branch: 1 branch
BranchTrueFalseTotal
if defined $conf 0 0 0
630 }
631  
632 10 return $postref;
633  
634 }
635  
636 1 ▶sub first_unread {
Subroutine first_unread: called CC=8 SCAR=28.3
Pod documented
637  
638 1 my ($self, $feed) = @_;
639  
640 my $name =
641 1 ▶ eval { $feed->isa('WWW::Noss::FeedConfig') }
Branch: 1 branch
BranchTrueFalseTotal
eval { do { $feed->isa('WWW::Noss::FeedConfig') } } ? : 0 1 1
642 ? $feed->name
643 : $feed;
644  
645 my $postref = $self->{ DB }->selectrow_hashref(
646 1 q{
647 SELECT
648 nossid,
649 status,
650 feed,
651 title,
652 link,
653 author,
654 category,
655 summary,
656 published,
657 updated,
658 uid,
659 nossuid,
660 displaytitle
661 FROM
662 posts
663 WHERE
664 feed = ? AND
665 status = 'unread'
666 ORDER BY
667 nossid DESC;
668 },
669 undef,
670 $feed,
671 );
672  
673 1 ▶ return undef unless defined $postref;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $postref 0 1 1
674  
675 $postref->{ category } =
676 defined $postref->{ category }
677 ? decode_json($postref->{ category })
678 1 ▶ : [];
Branch: 1 branch
BranchTrueFalseTotal
defined $postref->{'category'} ? : 1 0 1
679 # Convert '/foo' and '//foo' relative links
680 1 ▶ if (defined $postref->{ link } and $postref->{ link } =~ /^\//) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $postref->{"link"} and $postref->{"link"} =~ m[^/]u) 0 1 1
Condition33%1/3
defined $postref->{"link"} and $postref->{"link"} =~ m[^/]u
!ll&&!rl&&r
010
MC/DC0%0/2
defined $postref->{"link"} and $postref->{"link"} =~ m[^/]u
defined $postref->{"link"} $postref->{"link"} =~ m[^/]u
Truth table33%1/3
A and B
A= defined $postref->{"link"}B= $postref->{"link"} =~ m[^/]u
ABresult
0X0
100
111
681 0 my $feed_info = $self->feed($feed);
682 my $conf = resolve_url(
683 $postref->{ link },
684 $feed_info->{ link } // $feed_info->{ nosslink }
685 0 ▶ );
Condition0%0/3
$feed_info->{"link"} // $feed_info->{"nosslink"}
l!l&&r!l&&!r
000
MC/DC0%0/2
$feed_info->{"link"} // $feed_info->{"nosslink"}
$feed_info->{"link"} $feed_info->{"nosslink"}
Truth table0%0/3
A // B
A= $feed_info->{"link"}B= $feed_info->{"nosslink"}
ABresult
1X1
011
000
686 0 ▶ $postref->{ link } = $conf if defined $conf;
Branch: 1 branch
BranchTrueFalseTotal
if defined $conf 0 0 0
687 }
688  
689 1 return $postref;
690  
691 }
692  
693 2 ▶sub largest_id {
Subroutine largest_id: called CC=3 SCAR=11.0
Pod documented
694  
695 2 my ($self, @feeds) = @_;
696  
697 2 my $where = '';
698  
699 2 ▶ if (@feeds) {
Branch: 1 branch
BranchTrueFalseTotal
if (@feeds) 1 1 2
700 $where = sprintf
701 "WHERE feed IN (%s)",
702 1 join(',', map { $self->{ DB }->quote($_) } @feeds);
703 }
704  
705 my $row = $self->{ DB }->selectrow_arrayref(
706 2 qq{
707 SELECT
708 nossid
709 FROM
710 posts
711 $where
712 ORDER BY
713 nossid DESC
714 LIMIT 1;
715 },
716 );
717  
718 2 ▶ return defined $row ? $row->[0] : undef;
Branch: 1 branch
BranchTrueFalseTotal
defined $row ? : 2 0 2
719  
720 }
721  
722 13 ▶sub look {
Subroutine look: called CC=31 SCAR=36.8
Pod documented
723  
724 13 my ($self, %param) = @_;
725  
726 13 my @posts;
727  
728 13 my $title = $param{ title };
729 my @feeds =
730 ref $param{ feeds } eq 'ARRAY'
731 13 ▶ ? @{ $param{ feeds } }
Branch: 1 branch
BranchTrueFalseTotal
ref $param{'feeds'} eq 'ARRAY' ? : 1 12 13
732 : ();
733 13 my $status = $param{ status };
734 my @tags =
735 ref $param{ tags } eq 'ARRAY'
736 13 ▶ ? @{ $param{ tags } }
Branch: 1 branch
BranchTrueFalseTotal
ref $param{'tags'} eq 'ARRAY' ? : 1 12 13
737 : ();
738 my @content =
739 ref $param{ content } eq 'ARRAY'
740 13 ▶ ? @{ $param{ content } }
Branch: 1 branch
BranchTrueFalseTotal
ref $param{'content'} eq 'ARRAY' ? : 1 12 13
741 : ();
742 13 ▶ my $order = $param{ order } // 'feed';
Condition100%2/2
$param{"order"} // "feed"
l!l
310
MC/DC100%1/1
$param{"order"} // "feed"
$param{"order"}
Truth table100%2/2
A
A= $param{"order"}
Aresult
11
00
743 13 ▶ my $reverse = $param{ reverse } // 0;
Condition100%2/2
$param{"reverse"} // 0
l!l
112
MC/DC100%1/1
$param{"reverse"} // 0
$param{"reverse"}
Truth table100%2/2
A
A= $param{"reverse"}
Aresult
11
00
744 13 ▶ my $limit = $param{ limit } // 0;
Condition100%2/2
$param{"limit"} // 0
l!l
112
MC/DC100%1/1
$param{"limit"} // 0
$param{"limit"}
Truth table100%2/2
A
A= $param{"limit"}
Aresult
11
00
745 74 ▶ my $callback = $param{ callback } // sub {
Subroutine __ANON__: called CC=1 SCAR=0.0
746 74 push @posts, $_[0];
747 13 ▶ };
Condition66%2/3
$param{"callback"} // sub { push @posts, $_[0]; }
l!l&&r!l&&!r
1120
MC/DC0%0/2
$param{"callback"} // sub { push @posts, $_[0]; }
$param{"callback"} sub { push @posts, $_[0]; }
Truth table66%2/3
A // B
A= $param{"callback"}B= sub { push @posts, $_[0]; }
ABresult
1X1
011
000
748  
749 13 ▶ if (defined $status and $status !~ /^(un)?read$/) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $status and not $status =~ /^(un)?read$/u) 0 13 13
Condition66%2/3
defined $status and not $status =~ /^(un)?read$/u
!ll&&!rl&&r
1210
MC/DC0%0/2
defined $status and not $status =~ /^(un)?read$/u
defined $status not $status =~ /^(un)?read$/u
Truth table66%2/3
A and not(B)
A= defined $statusB= not $status =~ /^(un)?read$/u
ABresult
0X0
100
111
750 0 die "status must be 'read' or 'unread'";
751 }
752  
753 13 ▶ unless (ref $callback eq 'CODE') {
Branch: 1 branch
BranchTrueFalseTotal
unless (ref $callback eq "CODE") 0 13 13
754 0 die "callback must be a code ref";
755 }
756  
757 13 my @wheres;
758  
759 13 ▶ if (defined $title) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $title) 1 12 13
760 1 push @wheres, 'displaytitle REGEXP ' . $self->{ DB }->quote($title);
761 }
762  
763 13 ▶ if (@feeds) {
Branch: 1 branch
BranchTrueFalseTotal
if (@feeds) 1 12 13
764 my $feed_set =
765 sprintf '(%s)',
766 join ',',
767 1 map { $self->{ DB }->quote($_) }
768 @feeds;
769 1 push @wheres, "feed IN $feed_set";
770 }
771  
772 13 ▶ if (defined $status) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $status) 1 12 13
773 1 push @wheres, 'status = ' . $self->{ DB }->quote($status);
774 }
775  
776 13 ▶ if (@content) {
Branch: 1 branch
BranchTrueFalseTotal
if (@content) 1 12 13
777 1 push @wheres, map { 'summary REGEXP ' . $self->{ DB }->quote($_) } @content;
778 }
779  
780 13 ▶ my $where = @wheres ? 'WHERE ' . join ' AND ', @wheres : '';
Branch: 1 branch
BranchTrueFalseTotal
@wheres ? : 4 9 13
781  
782 13 ▶ my ($asc, $desc, $first, $last) =
Branch: 1 branch
BranchTrueFalseTotal
$reverse ? : 1 12 13
783 $reverse
784 ? ('DESC', 'ASC' , 'LAST', 'FIRST')
785 : ('ASC', 'DESC', 'FIRST', 'LAST');
786  
787 13 my $order_clause;
788 my $rev_order_clause; # For sorting by date + limit
789  
790 13 ▶ if ($order eq 'feed') {
Branch: 3 branches
BranchTrueFalseTotal
if ($order eq 'feed') { } 11 2 13
elsif ($order eq 'title') { } 1 1 2
elsif ($order eq 'date') { } 1 0 1
791 11 $order_clause = qq{
792 feed COLLATE NOCASE $asc,
793 nossid $asc
794 };
795 } elsif ($order eq 'title') {
796 1 $order_clause = qq{
797 displaytitle COLLATE NOCASE $asc NULLS $last,
798 feed COLLATE NOCASE $asc,
799 nossid $asc
800 };
801 } elsif ($order eq 'date') {
802 1 $order_clause = qq{
803  
804 CASE
805 WHEN updated IS NOT NULL THEN updated
806 ELSE published
807 END $asc NULLS $first,
808 feed COLLATE NOCASE $asc,
809 nossid $asc
810 };
811 1 $rev_order_clause = qq{
812 CASE
813 WHEN updated IS NOT NULL THEN updated
814 ELSE published
815 END $desc NULLS $last,
816 feed COLLATE NOCASE $desc,
817 nossid $desc
818 };
819 } else {
820 0 die "Cannot order posts by '$order'";
821 }
822  
823 13 ▶ my $limit_clause = $limit > 0 ? "LIMIT $limit" : '';
Branch: 1 branch
BranchTrueFalseTotal
$limit > 0 ? : 1 12 13
824  
825 13 my $sth;
826 13 ▶ if ($order eq 'date' and $limit > 0) {
Branch: 1 branch
BranchTrueFalseTotal
if ($order eq 'date' and $limit > 0) { } 0 13 13
Condition66%2/3
$order eq "date" and $limit > 0
!ll&&!rl&&r
1210
MC/DC0%0/2
$order eq "date" and $limit > 0
$order eq "date" $limit > 0
Truth table66%2/3
A and B
A= $order eq "date"B= $limit > 0
ABresult
0X0
100
111
827 # First get last (or first when --reverse) posts, then sort them in
828 # display order.
829 $sth = $self->{ DB }->prepare(
830 0 qq{
831 SELECT
832 *
833 FROM (
834 SELECT
835 nossid,
836 status,
837 feed,
838 title,
839 link,
840 author,
841 category,
842 summary,
843 published,
844 updated,
845 uid,
846 nossuid,
847 displaytitle
848 FROM
849 posts
850 $where
851 ORDER BY
852 $rev_order_clause
853 $limit_clause
854 )
855 ORDER BY
856 $order_clause
857 }
858 );
859 } else {
860 $sth = $self->{ DB }->prepare(
861 13 qq{
862 SELECT
863 nossid,
864 status,
865 feed,
866 title,
867 link,
868 author,
869 category,
870 summary,
871 published,
872 updated,
873 uid,
874 nossuid,
875 displaytitle
876 FROM
877 posts
878 $where
879 ORDER BY
880 $order_clause
881 $limit_clause;
882 }
883 );
884 }
885  
886 13 $sth->execute;
887  
888 13 my $n = 0;
889  
890 13 my %cache;
891  
892 13 ▶ while (my $p = $sth->fetchrow_hashref) {
Branch: 1 branch
BranchTrueFalseTotal
if (my $p = $sth->fetchrow_hashref) 82 13 95
893 $p->{ category } =
894 defined $p->{ category }
895 ? decode_json($p->{ category })
896 82 ▶ : [];
Branch: 1 branch
BranchTrueFalseTotal
defined $p->{'category'} ? : 82 0 82
897 82 ▶ if (defined $p->{ link } and $p->{ link } =~ /^\//) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $p->{"link"} and $p->{"link"} =~ m[^/]u) 0 82 82
Condition33%1/3
defined $p->{"link"} and $p->{"link"} =~ m[^/]u
!ll&&!rl&&r
0820
MC/DC0%0/2
defined $p->{"link"} and $p->{"link"} =~ m[^/]u
defined $p->{"link"} $p->{"link"} =~ m[^/]u
Truth table33%1/3
A and B
A= defined $p->{"link"}B= $p->{"link"} =~ m[^/]u
ABresult
0X0
100
111
898 0 my $feed_info;
899 0 ▶ if (exists $cache{ $p->{ feed } }) {
Branch: 1 branch
BranchTrueFalseTotal
if (exists $cache{$p->{'feed'}}) { } 0 0 0
900 0 $feed_info = $cache{ $p->{ feed } };
901 } else {
902 0 $feed_info = $self->feed($p->{ feed });
903 0 $cache{ $p->{ feed } } = $feed_info;
904 }
905 my $conv = resolve_url(
906 $p->{ link },
907 $feed_info->{ link } // $feed_info->{ nosslink }
908 0 ▶ );
Condition0%0/3
$feed_info->{"link"} // $feed_info->{"nosslink"}
l!l&&r!l&&!r
000
MC/DC0%0/2
$feed_info->{"link"} // $feed_info->{"nosslink"}
$feed_info->{"link"} $feed_info->{"nosslink"}
Truth table0%0/3
A // B
A= $feed_info->{"link"}B= $feed_info->{"nosslink"}
ABresult
1X1
011
000
909 }
910 24 ▶ next unless all {
Subroutine __ANON__: called CC=1 SCAR=0.0
911 24 my $t = $_;
912 24 ▶ any { $_ =~ $t } @{ $p->{ category } };
Subroutine __ANON__: called CC=1 SCAR=0.0
913 82 ▶ } @tags;
Branch: 1 branch
BranchTrueFalseTotal
unless &all(sub { my $t = $_; &any(sub { $_ =~ /$t/u; } , @{$$p{"category"};}); } , @tags) 0 82 82
914 82 $callback->($p);
915 82 $n++;
916 }
917  
918 13 ▶ return defined $param{ callback } ? $n : @posts;
Branch: 1 branch
BranchTrueFalseTotal
defined $param{'callback'} ? : 1 12 13
919  
920 }
921  
922 3 ▶sub mark {
Subroutine mark: called CC=6 SCAR=20.1
Pod documented
923  
924 3 my ($self, $mark, $feed, @post) = @_;
925  
926 my $name =
927 3 ▶ eval { $feed->isa('WWW::Noss::FeedConfig') }
Branch: 1 branch
BranchTrueFalseTotal
eval { do { $feed->isa('WWW::Noss::FeedConfig') } } ? : 0 3 3
928 ? $feed->name
929 : $feed;
930  
931 3 ▶ unless ($mark =~ /^(un)?read$/) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($mark =~ /^(un)?read$/u) 0 3 3
932 0 die "Posts can only be marked as either 'read' or 'unread'";
933 }
934  
935 3 my @wheres = ("feed = " . $self->{ DB }->quote($feed));
936  
937 3 ▶ if (any { $_ < 0 } @post) {
Subroutine __ANON__: called CC=1 SCAR=0.0
Branch: 2 branches
BranchTrueFalseTotal
if (&any(sub { $_ < 0; } , @post)) { } 0 3 3
elsif (@post > 0) { } 1 2 3
938 0 my $largest = $self->largest_id($feed);
939 push @wheres,
940 sprintf "nossid IN (%s)",
941 join ',',
942 0 ▶ map { $_ < 0 ? $largest + $_ + 1 : $_ }
Branch: 1 branch
BranchTrueFalseTotal
$_ < 0 ? : 0 0 0
943 @post;
944 } elsif (@post > 0) {
945 1 push @wheres, sprintf "nossid IN (%s)", join ',', @post;
946 }
947  
948 3 push @wheres, "status != '$mark'";
949  
950 3 my $where = join ' AND ', @wheres;
951  
952 my $num = $self->{ DB }->do(
953 3 qq{
954 UPDATE
955 posts
956 SET
957 status = ?
958 WHERE
959 $where;
960 },
961 undef,
962 $mark
963 );
964  
965 3 return $num;
966  
967 }
968  
969 0 ▶sub skip {
Subroutine skip: not called CC=7 SCAR=40.3
Pod documented
970  
971 0 my ($self, $feed) = @_;
972  
973 my $name =
974 0 ▶ eval { $feed->isa('WWW::Noss::FeedConfig') }
Branch: 1 branch
BranchTrueFalseTotal
eval { do { $feed->isa('WWW::Noss::FeedConfig') } } ? : 0 0 0
975 ? $feed->name
976 : $feed;
977  
978 my $row = $self->{ DB }->selectrow_hashref(
979 0 q{
980 SELECT
981 skiphours,
982 skipdays
983 FROM
984 feeds
985 WHERE
986 nossname = ?;
987 },
988 undef,
989 $name
990 );
991  
992 0 ▶ return undef unless defined $row;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $row 0 0 0
993  
994 0 my ($hour, $day) = (gmtime)[2, 6];
995  
996 my @skip_hours =
997 defined $row->{ skiphours }
998 0 ▶ ? @{ decode_json($row->{ skiphours }) }
Branch: 1 branch
BranchTrueFalseTotal
defined $row->{'skiphours'} ? : 0 0 0
999 : ();
1000 my @skip_days =
1001 defined $row->{ skipdays }
1002 0 ▶ ? @{ decode_json($row->{ skipdays }) }
Branch: 1 branch
BranchTrueFalseTotal
defined $row->{'skipdays'} ? : 0 0 0
1003 : ();
1004  
1005 0 ▶ if (grep { $hour eq $_ } @skip_hours) {
Branch: 1 branch
BranchTrueFalseTotal
if (grep {$hour eq $_;} @skip_hours) 0 0 0
1006 0 return 1;
1007 }
1008  
1009 0 ▶ if (grep { $DAY_MAP{ $day } eq $_ } @skip_days) {
Branch: 1 branch
BranchTrueFalseTotal
if (grep {$DAY_MAP{$day} eq $_;} @skip_days) 0 0 0
1010 0 return 1;
1011 }
1012  
1013 0 return 0;
1014  
1015 }
1016  
1017 1 ▶sub vacuum {
Subroutine vacuum: called CC=1 SCAR=0.0
Pod documented
1018  
1019 1 my ($self) = @_;
1020  
1021 1 $self->{ DB }->do(q{ VACUUM; });
1022  
1023 1 return 1;
1024  
1025 }
1026  
1027 4 ▶sub finish {
Subroutine finish: called CC=1 SCAR=0.0
Pod documented
1028  
1029 4 my ($self) = @_;
1030  
1031 4 $self->{ DB }->disconnect;
1032  
1033 }
1034  
1035 2 ▶DESTROY {
Subroutine DESTROY: called CC=1 SCAR=0.0
1036  
1037 2 my ($self) = @_;
1038  
1039 2 $self->finish;
1040  
1041 }
1042  
1043 2 1;
1044  
1045 =head1 NAME
1046  
1047 WWW::Noss::DB - noss SQLite database interface
1048  
1049 =head1 USAGE
1050  
1051 use WWW::Noss::DB;
1052  
1053 my $db = WWW::Noss::DB->new('path/to/database');
1054  
1055 =head1 DESCRIPTION
1056  
1057 B<WWW::Noss::DB> is a module that provides an object-oriented interface to
1058 L<noss>'s SQLite feed database. This is a private module, please consult the
1059 L<noss> manual for user documentation.
1060  
1061 =head1 METHODS
1062  
1063 =over 4
1064  
1065 =item $db = WWW::Noss::DB->new($file)
1066  
1067 Loads a L<noss> database from C<$file> or initializes it if ones does not
1068 exist, then returns a blessed B<WWW::Noss::DB> object.
1069  
1070 =item $bool = $db->has_feed($feed)
1071  
1072 Returns true if C<$db> has the feed C<$feed>.
1073  
1074 =item $new = $db->load_feed($feed_conf)
1075  
1076 Loads the L<WWW::Noss::FeedConfig> object C<$feed_conf> into the database.
1077 Returns the number of new posts loaded if successful, dies on failure.
1078  
1079 =item \%feed = $db->feed($feed, [ %param ])
1080  
1081 Returns a hash ref of information about the feed C<$feed>. C<$feed> can either
1082 be the name of a feed or a L<WWW::Noss::FeedConfig> object. C<%param> is an
1083 optional hash of additional parameters.
1084  
1085 C<\%feed> will look something like this:
1086  
1087 {
1088 nossname => ...,
1089 nosslink => ...,
1090 title => ...,
1091 link => ...,
1092 description => ...,
1093 updated => ...,
1094 author => ...,
1095 category => [ ... ],
1096 generator => ...,
1097 image => ...,
1098 rights => ...,
1099 skiphours => [ ... ],
1100 skipdays => [ ... ],
1101 posts => ..., # only with post_info set
1102 unread => ..., # only with post_info set
1103 }
1104  
1105 The following is a list of valid fields for C<%param>:
1106  
1107 =over 4
1108  
1109 =item post_info
1110  
1111 Boolean determining whether to also retrieve the total number of posts and
1112 number of unread posts. This causes C<feed()> to be slower. Defaults to false.
1113  
1114 =back
1115  
1116 =item @feeds = $db->feeds()
1117  
1118 Returns an array of feed hash refs of each feed loaded in the database. The
1119 hash refs follow the same format as the one returned by the C<feed()> method,
1120 minus the C<posts> and C<unread> fields.
1121  
1122 =item $rt = $db->del_feeds(@feeds)
1123  
1124 Deletes the feeds C<@feeds> from the database. Returns C<1> on success.
1125  
1126 =item \%post = $db->post($feed, $post)
1127  
1128 Returns the hash ref C<\%post> representing post number C<$post> in feed
1129 C<$feed>. C<$feed> can be a feed name or a L<WWW::Noss::FeedConfig> object.
1130 If C<$post> is negative, returns the nth post from the end of the feed.
1131  
1132 C<\%post> will look something like this:
1133  
1134 {
1135 nossid => ...,
1136 status => ...,
1137 feed => ...,
1138 title => ...,
1139 link => ...,
1140 author => ...,
1141 category => [ ... ],
1142 summary => ...,
1143 published => ...,
1144 updated => ...,
1145 uid => ...,
1146 nossuid => ...,
1147 displaytitle => ...,
1148 }
1149  
1150 Returns C<undef> if no matching post exists.
1151  
1152 =item \%post = $db->first_unread($feed)
1153  
1154 Returns the first unread post in C<$feed>. C<$feed> can be a feed name or a
1155 L<WWW::Noss::FeedConfig> object. C<\%post> follows the same format as the one
1156 returned by C<post()>. Returns C<undef> if no unread post exists.
1157  
1158 =item $id = $db->largest_id([ @feeds ])
1159  
1160 Returns the largest ID in the specified feeds. If feeds is not provided, all
1161 feeds are searched.
1162  
1163 =item @posts = $db->look([ %param ])
1164  
1165 Returns a list of posts matching the parameters specified in C<%param>. If no
1166 parameters are provided, returns a list of every post in the database.
1167  
1168 The following are a list of valid fields to C<%param>:
1169  
1170 =over 4
1171  
1172 =item title
1173  
1174 Only return posts whose titles match the given regex.
1175  
1176 =item feeds
1177  
1178 Only return posts that are in the feeds of the given array ref.
1179  
1180 =item status
1181  
1182 Only return posts that are of the given status. Can either be C<'read'> or
1183 C<'unread'>.
1184  
1185 =item tags
1186  
1187 Only return posts containing the tags specified by the given array ref.
1188  
1189 =item content
1190  
1191 Only return posts whose content match all regexes in the given array ref.
1192  
1193 =item order
1194  
1195 How C<look()> should order the returned posts. The following are valid values:
1196  
1197 =over 4
1198  
1199 =item feed
1200  
1201 Order by feed alphabetically.
1202  
1203 =item title
1204  
1205 Order by post title alphabetically.
1206  
1207 =item date
1208  
1209 Order by post date.
1210  
1211 =back
1212  
1213 =item reverse
1214  
1215 Return the post list in reverse order.
1216  
1217 =item limit
1218  
1219 Limit the number of posts that are selected. If equal to or less than C<0>,
1220 there is no limit. Default is C<0> (no limit).
1221  
1222 =item callback
1223  
1224 Subroutine reference to a callback to call on each post reference. The post
1225 reference is available via the C<@_> array. When this option is set,
1226 C<look> will return the number of posts processed instead of the post list.
1227  
1228 =back
1229  
1230 =item $num = $db->mark($mark, $feed, @post)
1231  
1232 Mark the posts C<@post> in feed C<$feed> as C<$mark>. Returns the number of posts
1233 updated. C<$feed> can be either a feed name or L<WWW::Noss::FeedConfig> object.
1234 C<$mark> can either be C<'read'> or C<'unread'>. C<@post> is a list of post IDs
1235 to update. If C<@post> is empty, all posts in C<$feed> are updated.
1236  
1237 =item $bool = $db->skip($feed)
1238  
1239 Check whether you are supposed to skip updating C<$feed> right now. C<$feed>
1240 can either be a feed name or L<WWW::Noss::FeedConfig> object. C<undef> is
1241 returned if C<$feed> does not exist.
1242  
1243 =item $db->vacuum()
1244  
1245 Runs the C<VACUUM> L<sqlite3(1)> command on the database, which frees up any
1246 unused space within the database and reduces its total size.
1247  
1248 =item $db->finish()
1249  
1250 Closes the connection to the local database. Is automatically called when a
1251 B<WWW::Noss::DB> object is destroyed.
1252  
1253 =back
1254  
1255 =head1 AUTHOR
1256  
1257 Written by Samuel Young, E<lt>samyoung@posteo.comE<gt>.
1258  
1259 This project's source can be found on its
1260 L<Codeberg page|https://codeberg.org/1-1sam/noss.git>. Comments and pull
1261 requests are welcome!
1262  
1263 =head1 COPYRIGHT
1264  
1265 Copyright (C) 2025-2026 Samuel Young
1266  
1267 This program is free software: you can redistribute it and/or modify
1268 it under the terms of the GNU General Public License as published by
1269 the Free Software Foundation, either version 3 of the License, or
1270 (at your option) any later version.
1271  
1272 =head1 SEE ALSO
1273  
1274 L<WWW::Noss::FeedConfig>, L<noss>, L<sqlite3(1)>
1275  
1276 =cut
1277  
1278 # vim: expandtab shiftwidth=4
« Noss/FeedReader/Atom.pm ↑ Summary Noss/TextToHtml.pm »