1 package Newslash::Model::Stories;
2 use Newslash::Model::Base -base;
4 use Newslash::Model::SlashDB;
6 use DateTime::Format::MySQL;
10 use Newslash::Util::Formatters qw(datetime_to_string);
13 sub _calculate_time_range {
14 my ($self, $params) = @_;
15 my $year = $params->{year};
16 my $month = $params->{month};
17 my $day = $params->{day};
18 my $offset_sec = $params->{offset_sec};
20 my $dt_from = DateTime->new(year => $year,
23 my $dt_to = DateTime->new(year => $year,
27 $dt_from->add(seconds => -$offset_sec);
28 $dt_to->add(seconds => -$offset_sec);
30 $dt_to->add(days => 1);
31 return (DateTime::Format::MySQL->format_datetime($dt_from),
32 DateTime::Format::MySQL->format_datetime($dt_to));
33 #return ($dt_from->strftime('%F'), $dt_to->strftime('%F'));
41 return if !$params->{year};
42 $target = "month" if !$params->{day};
43 $target = "year" if !$params->{month};
45 my ($year, $month, $day) = ($params->{year}, $params->{month}, $params->{day});
46 $year = 1 if (!$year || $year !~ m/^[0-9]{4}$/);
47 $month = 1 if (!$month || $month !~ m/^(1[0-2]|0?[0-9])$/);
48 $day = 1 if (!$day || $day !~ m/^(3[0-1]|[1-2][0-9]|0?[0-9])$/);
50 my $offset = $params->{offset_sec} || 0;
51 $offset = 0 if $offset !~ m/^[+-]?[0-9]+$/;
53 my $dt = DateTime->new(year => $year,
56 $dt->add(seconds => -$offset);
57 my $dt_string = DateTime::Format::MySQL->format_datetime($dt);
59 # create end of term datetime
60 # why use "DATE_ADD(?, INTERVAL 1 MONTH)" ? bacause, this function add simply 30 days...
61 my $dt_end = DateTime->new(year => $year,
64 if ($target eq "month") {
65 $dt_end->add(months => 1);
67 elsif ($target eq "year") {
68 $dt_end->add(years => 1);
70 $dt->add(seconds => -$offset);
71 my $dt_end_string = DateTime::Format::MySQL->format_datetime($dt_end);
73 # we must consider timezone offset, so use relative day/month.
77 if ($target eq "day") {
78 # `stories` table not contain display/non-display flag,
81 SELECT COUNT(stories.stoid) AS count FROM stories
83 ON (stories.stoid = firehose.srcid AND firehose.type = "story")
84 WHERE firehose.public != "no"
86 AND stories.time < DATE_ADD(?, INTERVAL 1 DAY)
88 push @attrs, $dt_string, $dt_string;
90 elsif ($target eq "month") {
92 SELECT TIMESTAMPDIFF(DAY, ?, stories.time) AS day,
93 COUNT(stories.stoid) AS count
96 ON (stories.stoid = firehose.srcid AND firehose.type = "story")
97 WHERE firehose.public != "no"
100 GROUP BY TIMESTAMPDIFF(DAY, ?, stories.time)
103 push @attrs, $dt_string, $dt_string, $dt_end_string, $dt_string;
105 elsif ($target eq "year") {
107 SELECT TIMESTAMPDIFF(MONTH, ?, stories.time) AS month,
108 COUNT(stories.stoid) AS count
111 ON (stories.stoid = firehose.srcid AND firehose.type = "story")
112 WHERE firehose.public != "no"
113 AND stories.time >= ?
115 GROUP BY TIMESTAMPDIFF(MONTH, ?, stories.time)
118 push @attrs, $dt_string, $dt_string, $dt_end_string, $dt_string;
120 my $dbh = $self->connect_db;
121 my $sth = $dbh->prepare($sql);
122 $sth->execute(@attrs);
123 my $rs = $sth->fetchall_arrayref({});
126 $self->disconnect_db();
129 $self->disconnect_db();
133 #warn $dt_end_string;
137 if ($target eq "day") {
138 return $rs->[0]->{count};
140 elsif ($target eq "month") {
143 elsif ($target eq "year") {
150 for my $counts (@$rs) {
151 # day / month is differential from base datetime, so add 1
152 $hash->{$counts->{$key} + 1} = $counts->{count};
160 my $limit = $options->{limit} || 10;
161 my $show_future = $options->{show_future} || 0;
162 my $show_nonpublic = $options->{show_nonpublic} || 0;
163 return $self->select(order_by => {time => 'desc'},
165 show_nonpublic => $show_nonpublic,
166 show_future => $show_future
170 #========================================================================
172 =head2 select($query_type, $value)
184 query key, "sid" or "stoid"
194 HASH of story contents
205 my $return_single = 0;
208 for my $k (qw(sid stoid)) {
211 $value = $params->{$k};
215 for my $k (qw(submitter)) {
218 $value = $params->{$k};
226 if ($query_type && $value) {
227 push @where_clauses, "stories.$query_type = ?";
228 push @query_param, $value;
233 if (!$params->{show_future}) {
234 push @where_clauses, "stories.time <= NOW()";
237 # show non-public story?
238 if (!$params->{show_nonpublic}) {
239 push @where_clauses, "firehose.public != 'no'";
243 if ($params->{year} && $params->{month} && $params->{day}) {
244 my ($time_from, $time_to) = $self->_calculate_time_range($params);
245 push @where_clauses, "stories.time > ? AND stories.time < ?";
246 push @query_param, $time_from, $time_to;
250 if ($params->{until}) {
251 push @where_clauses, "stories.time <= ?";
252 push @query_param, $params->{until};
255 if ($params->{since}) {
256 push @where_clauses, "stories.time >= ?";
257 push @query_param, $params->{since};
262 my @units = qw(YEAR MONTH WEEK DAY HOUR MINUTE);
264 for my $term (qw(years months weeks days hours minutes)) {
265 $unit = shift @units;
266 if (defined $params->{$term}) {
267 $date_limit = $params->{$term};
271 if (length $date_limit) {
272 push @where_clauses, "stories.time > NOW() - INTERVAL ? $unit";
273 push @query_param, $date_limit;
276 # build ORDER BY clause
277 # my $order_clause = "";
278 # my @safe_params = qw(commentcount hits time);
279 # if (defined $params->{order_by}) {
280 # # check order_by's value
281 # my $k = $params->{order_by};
282 # if (grep {$_ eq $k} @safe_params) {
283 # my $order = "DESC";
284 # if (defined $params->{order} && $params->{order} eq "ASC") {
287 # $order_clause = "ORDER BY $k $order";
290 my ($order_clause, $order_values) = $self->build_order_by_clause(keys => [qw(commentcount hits time)], params => $params);
293 my $limit_clause = "";
294 if (defined $params->{limit}) {
295 $limit_clause = "LIMIT ?";
296 push @query_param, $params->{limit};
300 my $where_clause = "";
301 if (@where_clauses) {
302 $where_clause = "WHERE " . join("\n AND ", @where_clauses) . "\n";
304 my $dbh = $self->connect_db;
306 SELECT stories.*, story_text.*, users.nickname as author, firehose.public
308 LEFT JOIN story_text ON stories.stoid = story_text.stoid
309 LEFT JOIN users ON stories.uid = users.uid
311 ON (stories.stoid = firehose.srcid AND firehose.type = "story")
318 #warn(Dumper(@query_param));
320 my $sth = $dbh->prepare($sql);
321 $sth->execute(@query_param);
322 my $stories = $sth->fetchall_arrayref({});
325 $self->disconnect_db();
328 if (@$stories == 0) {
329 $self->disconnect_db();
330 return $return_single ? undef : [];
335 SELECT tags.*, tagnames.tagname, target.stoid
336 FROM (SELECT stories.stoid FROM stories
337 LEFT JOIN firehose ON (stories.stoid = firehose.srcid AND firehose.type = "story")
338 $where_clause $order_clause $limit_clause) AS target
340 ON target.stoid = globjs.target_id
342 ON globjs.globjid = tags.globjid
344 ON tags.tagnameid = tagnames.tagnameid
345 WHERE globjs.gtid = 1
348 $sth = $dbh->prepare($sql);
349 $sth->execute(@query_param);
350 my $tags_table = $sth->fetchall_arrayref({});
354 SELECT story_topics_rendered.*, story_topics_chosen.weight, topics.*
355 FROM (SELECT stories.stoid FROM stories
356 LEFT JOIN firehose ON (stories.stoid = firehose.srcid AND firehose.type = "story")
357 $where_clause $order_clause $limit_clause) AS target
358 LEFT JOIN story_topics_rendered
359 ON target.stoid = story_topics_rendered.stoid
360 LEFT JOIN story_topics_chosen
361 ON story_topics_rendered.stoid = story_topics_chosen.stoid
362 AND story_topics_rendered.tid = story_topics_chosen.tid
364 ON story_topics_rendered.tid = topics.tid
367 $sth = $dbh->prepare($sql);
368 $sth->execute(@query_param);
369 my $topics_table = $sth->fetchall_arrayref({});
370 $self->disconnect_db();
374 for my $tag (@$tags_table) {
375 my $stoid = $tag->{stoid};
376 if (!$tags->{$stoid}) {
377 $tags->{$stoid} = [];
379 push @{$tags->{$stoid}}, $tag;
383 for my $topic (@$topics_table) {
384 my $stoid = $topic->{stoid};
385 if (!$topics->{$stoid}) {
386 $topics->{$stoid} = [];
388 push @{$topics->{$stoid}}, $topic;
391 for my $story (@$stories) {
392 my $stoid = $story->{stoid};
393 $story->{tags} = $tags->{$stoid} if $tags->{$stoid};
394 $story->{topics} = $topics->{$stoid} if $topics->{$stoid};
395 $self->_generalize($story, $params);
398 return $stories->[0] if $return_single;
404 this implementation uses old slash's updateStory($sid, $data),
405 $sid is takable sid or stoid.
410 my ($self, $params, $user, $extra_params, $opts) = @_;
413 if (!$params->{stoid}) {
414 $self->set_error("stoid not given");
417 return $self->create($params, $user, $extra_params, $opts);
421 =head2 create(\%params, $uid)
453 my ($self, $params, $user, $extra_params, $opts) = @_;
454 return if $self->check_readonly;
459 $msg = "no title" if !$params->{title};
460 $msg = "no introtext" if !$params->{introtext};
461 $msg = "no uid" if !$params->{uid};
462 $msg = "no topics" if !defined $params->{topics_chosen};
463 $msg = "invalid user" if ref($user) ne 'HASH';
465 if (length($params->{title}) > $self->{options}->{Story}->{title_max_byte}) {
466 $msg = "title too long. max: $self->{options}->{Story}->{title_max_byte} bytes";
469 $params->{commentstatus} ||= "enabled";
470 if (!grep /\A$params->{commentstatus}\z/, qw(disabled enabled friends_only friends_fof_only no_foe no_foe_eof logged_in)) {
471 $msg = "invalid commentstatus";
474 # check timestamp. use ISO8601 style timestamp like: 2006-08-14T02:34:56-0600
475 if ($params->{time}) {
476 my $rex_timestamp = qr/
477 ^(\d+)-(\d+)-(\d+)\D+(\d+):(\d+):(\d+(?:\.\d+)?) # datetime
478 (?:Z|([+-])(\d+):(\d+))?$ # tz
480 if ($params->{time} =~ $rex_timestamp) {
481 $params->{time} = "$1-$2-$3 $4:$5:$6";
485 # check parameters finish
486 if (length($msg) > 0) {
487 $self->set_error($msg, -1);
491 $params->{neverdisplay} ||= 0;
493 # createStory deletes topics_chosen, so save before.
494 my $topics_chosen = $params->{topics_chosen};
496 my $slash_db = Newslash::Model::SlashDB->new($self->{options});
498 if ($opts->{update}) {
499 my $rs = $slash_db->updateStory($params->{stoid}, $params);
503 $sid = $params->{sid};
504 $stoid = $params->{stoid};
507 ($sid, $stoid) = $slash_db->createStory($params);
510 my $globjs = $self->new_instance_of("Newslash::Model::Globjs");
511 my $globj_id = $globjs->getGlobjidFromTargetIfExists("stories", $params->{stoid});
514 use Newslash::Model::Tags;
515 my $tags = $self->new_instance_of("Newslash::Model::Tags");
516 for my $tid (keys %$topics_chosen) {
517 my $ret = $tags->set_tag(uid => $user->{uid},
519 globj_id => $globj_id,
522 #warn "set_tag fault..." if !$ret
530 my ($self, $bogus_sid) = @_;
531 # yes, this format is correct, don't change it :-)
532 my $sidformat = '%02d/%02d/%02d/%02d%0d2%02d';
533 # Create a sid based on the current time.
535 my $start_time = time;
537 # If we were called being told that there's at
538 # least one sid that is invalid (already taken),
539 # then look backwards in time until we find it,
540 # then go one second further.
544 @lt = localtime($start_time);
545 $lt[5] %= 100; $lt[4]++; # year and month
546 last if $bogus_sid eq sprintf($sidformat, @lt[reverse 0..5]);
549 # Found the bogus sid by looking
550 # backwards. Go one second further.
553 # Something's wrong. Skip ahead in
554 # time instead of back (not sure what
556 $start_time = time + 1;
559 @lt = localtime($start_time);
560 $lt[5] %= 100; $lt[4]++; # year and month
561 return sprintf($sidformat, @lt[reverse 0..5]);
572 =head2 get_related_items($stoid)
590 ARRAY of related links
596 sub get_related_items {
599 my $stoid = $params->{stoid};
602 my $dbh = $self->connect_db;
606 story_text.title as title2,
610 SELECT * FROM related_stories
612 ORDER BY ordernum ASC
614 LEFT JOIN story_text ON story_text.stoid = related.rel_stoid
615 LEFT JOIN firehose ON firehose.id = related.fhid
616 LEFT JOIN stories ON stories.stoid = related.rel_stoid
617 LEFT JOIN topics ON topics.tid = stories.tid
620 my $sth = $dbh->prepare($sql);
621 $sth->execute($stoid);
622 my $related = $sth->fetchall_arrayref({});
623 $self->disconnect_db();
625 for my $r (@$related) {
626 $r->{title} = $r->{title2} unless $r->{title};
628 $r->{type} = "story";
629 $r->{key_id} = $r->{rel_sid};
631 $r->{type} = "submission";
632 $r->{key_id} = $r->{srcid};
634 $r->{primary_topic} = {};
635 $r->{primary_topic}->{tid} = $r->{tid};
636 for my $k (qw{keyword textname series image width height
637 submittable searchable storypickable usesprite}) {
639 $r->{primary_topic}->{$k} = $r->{$k};
647 =head2 parameters($stoid)
649 get story parameters.
672 my ($self, $stoid) = @_;
674 my $dbh = $self->connect_db;
677 SELECT * FROM story_param WHERE stoid = ?
680 my $sth = $dbh->prepare($sql);
681 $sth->execute($stoid);
682 my $params = $sth->fetchall_hashref('name');
685 # get next/prev story info
686 if ($params->{next_stoid} || $params->{prev_stoid}) {
688 SELECT stories.*, story_text.* FROM stories JOIN story_text ON stories.stoid = story_text.stoid WHERE stories.stoid = ? OR stories.stoid = ?
690 $sth = $dbh->prepare($sql);
691 my $next = $params->{next_stoid}->{value} || 0;
692 my $prev = $params->{prev_stoid}->{value} || 0;
693 $sth->execute($next, $prev);
694 my $sids = $sth->fetchall_hashref('stoid');
695 $params->{next_stoid}->{story} = $sids->{$next} if $sids->{$next};
696 $params->{prev_stoid}->{story} = $sids->{$prev} if $sids->{$prev};
700 $self->disconnect_db();
704 #========================================================================
706 =head2 set_dirty($key, $id)
708 set writestatus dirty for the story.
735 my ($self, $key, $id) = @_;
736 return if $self->check_readonly;
739 if ($key eq 'stoid') {
746 my $dbh = $self->connect_db;
748 INSERT INTO story_dirty (stoid) VALUES (?)
751 my $rs = $dbh->do($sql, undef, $stoid);
759 my ($self, $story, $params) = @_;
762 $story->{content_type} = "story";
765 for my $t (@{$story->{topics}}) {
766 if ($t->{tid} && $t->{tid} == $story->{tid}) {
767 $story->{primary_topic} = $t;
769 #if ($t->{weight} && $t->{weight} > $max_weight) {
770 # $max_weight = $t->{weight};
774 $story->{time_string} = datetime_to_string($story->{time});
775 $story->{createtime} = $story->{time};
776 $story->{discussion_id} = $story->{discussion};
777 $story->{id} = $story->{stoid};
779 # no public flag given, public is 'yes'
780 $story->{public} = 'yes' if !$story->{public};
782 # convert timestamp format
783 if (lc($params->{datetime_format}) eq "javascript") {
784 for my $k (qw{time day_published archive_last_update stuckendtime}) {
785 my $dt = DateTime::Format::MySQL->parse_datetime($story->{$k});
786 $story->{$k} = $dt->strftime('%FT%T');
791 # delete story from database
792 # this method is for test purpose only.
795 return if $self->check_readonly;
798 my $stoid = $params->{stoid};
804 my $dbh = $self->connect_db({AutoCommit => 0,});
805 for my $table (qw(stories story_param story_text story_topics_chosen story_topics_rendered)) {
806 my $sql = "DELETE FROM $table WHERE stoid = ?";
807 my $rs = $dbh->do($sql, undef, $stoid);
808 if (!defined $rs || $rs == 0) {
809 Mojo::Log->new->warn("DELETE FROM $table failed. stoid is $stoid.");
814 $self->disconnect_db;
818 # delete firehose item
819 # delete firehose_text item
820 # delete firehose_topics_rendererd item