1 package Newslash::Model::Stories;
2 use Newslash::Model::Base -base;
4 use Newslash::Model::SlashDB;
7 use DateTime::Format::MySQL;
8 use DateTime::Format::ISO8601;
12 use Newslash::Util::Formatters qw(datetime_to_string);
20 datetime => [qw(time last_update day_published
21 stuckendtime archive_last_update
23 other => [qw(uid dept hits discussion primaryskid
24 tid submitter commentcount hitparade
26 qid body_length word_count sponsor
27 stuck stuckpos fakeemail homepage
29 aliases => { user_id => "uid",
36 use constant FACULTIES => { 1000 => [qw(hits hitparade)] };
40 my $join = 'LEFT JOIN firehose ON (stories.stoid = firehose.srcid AND firehose.type = "story")';
41 my $where = 'firehose.public != "no"';
42 return $self->generic_count(table => "stories",
51 sub text { return shift->new_instance_of('::Stories::Text'); }
54 #========================================================================
56 =head2 select($query_type, $value)
68 query key, "sid" or "stoid"
78 HASH of story contents
89 my $unique_keys = { id => "stories.stoid",
90 story_id => "stories.stoid",
91 stoid => "stories.stoid",
94 my $keys = { user_id => "stories.uid",
96 topic_id => "stories.tid",
98 discussion_id => "stories.discussion",
99 commentcount => "stories.commentcount",
100 hits => "stories.hits",
101 submitter => "stories.submitter",
102 create_time => "stories.time",
103 update_time => "stories.last_update",
104 public => "firehose.public",
106 my $datetime_keys = { create_time => "stories.time",
107 update_time => "stories.last_update",
109 my $timestamp = "stories.time";
111 my ($where_clause, $where_values, $unique) = $self->build_where_clause(unique_keys => $unique_keys,
113 datetime_keys => $datetime_keys,
114 timestamp => $timestamp,
116 my ($limit_clause, $limit_values) = $self->build_limit_clause(params => $params);
117 my ($orderby_clause, $orderby_values) = $self->build_order_by_clause(keys => $keys,
120 # TODO: give reasonable LIMIT Value...
121 $limit_clause = "LIMIT 50" if !$limit_clause;
125 if ($params->{hide_future}) {
126 push @where_clauses, "stories.time <= NOW()";
129 # hide non-public story?
130 if ($params->{public_only}) {
131 push @where_clauses, "firehose.public != 'no'";
134 if (@where_clauses) {
136 $where_clause = $where_clause . " AND ";
139 $where_clause = "WHERE ";
141 $where_clause = $where_clause . join(" AND ", @where_clauses);
145 push @attrs, @$where_values, @$limit_values, @$orderby_values;
147 my $dbh = $self->connect_db;
149 SELECT stories.*, story_text.*, users.nickname as author, firehose.public,
150 discussions.type AS discussion_type, discussions.commentcount AS comment_count
152 LEFT JOIN story_text ON stories.stoid = story_text.stoid
153 LEFT JOIN users ON stories.uid = users.uid
155 ON (stories.stoid = firehose.srcid AND firehose.type = "story")
156 LEFT JOIN discussions ON firehose.discussion = discussions.id
163 #warn(Dumper(@attrs));
165 my $sth = $dbh->prepare($sql);
166 $sth->execute(@attrs);
167 my $stories = $sth->fetchall_arrayref({});
170 $self->disconnect_db();
173 if (@$stories == 0) {
174 $self->disconnect_db();
175 return $unique ? undef : [];
180 SELECT tags.*, tagnames.tagname, target.stoid
181 FROM (SELECT stories.stoid FROM stories
182 LEFT JOIN firehose ON (stories.stoid = firehose.srcid AND firehose.type = "story")
183 $where_clause $orderby_clause $limit_clause) AS target
185 ON target.stoid = globjs.target_id
187 ON globjs.globjid = tags.globjid
189 ON tags.tagnameid = tagnames.tagnameid
190 WHERE globjs.gtid = 1
193 $sth = $dbh->prepare($sql);
194 $sth->execute(@attrs);
195 my $tags_table = $sth->fetchall_arrayref({});
199 SELECT story_topics_rendered.*, story_topics_chosen.weight, topics.*
200 FROM (SELECT stories.stoid FROM stories
201 LEFT JOIN firehose ON (stories.stoid = firehose.srcid AND firehose.type = "story")
202 $where_clause $orderby_clause $limit_clause) AS target
203 LEFT JOIN story_topics_rendered
204 ON target.stoid = story_topics_rendered.stoid
205 LEFT JOIN story_topics_chosen
206 ON story_topics_rendered.stoid = story_topics_chosen.stoid
207 AND story_topics_rendered.tid = story_topics_chosen.tid
209 ON story_topics_rendered.tid = topics.tid
212 $sth = $dbh->prepare($sql);
213 $sth->execute(@attrs);
214 my $topics_table = $sth->fetchall_arrayref({});
219 FROM (SELECT stories.stoid FROM stories
220 LEFT JOIN firehose ON (stories.stoid = firehose.srcid AND firehose.type = "story")
221 $where_clause $orderby_clause $limit_clause) AS target
222 LEFT JOIN story_param
223 ON target.stoid = story_param.stoid
226 $sth = $dbh->prepare($sql);
227 $sth->execute(@attrs);
228 my $params_table = $sth->fetchall_arrayref({});
231 $self->disconnect_db();
235 for my $tag (@$tags_table) {
236 my $stoid = $tag->{stoid};
237 if (!$tags->{$stoid}) {
238 $tags->{$stoid} = [];
240 push @{$tags->{$stoid}}, $tag;
244 for my $topic (@$topics_table) {
245 my $stoid = $topic->{stoid};
246 if (!$topics->{$stoid}) {
247 $topics->{$stoid} = [];
249 push @{$topics->{$stoid}}, $topic;
253 for my $param (@$params_table) {
254 my $stoid = $param->{stoid};
255 if (!$params->{$stoid}) {
256 $params->{$stoid} = [];
258 push @{$params->{$stoid}}, $param;
261 for my $story (@$stories) {
262 my $stoid = $story->{stoid};
263 $story->{tags} = $tags->{$stoid} if $tags->{$stoid};
264 $story->{topics} = $topics->{$stoid} if $topics->{$stoid};
265 if ($params->{$stoid}) {
266 for my $param (@{$params->{$stoid}}) {
267 $story->{$param->{name}} = $param->{value};
270 $self->_generalize($story, $params);
273 return $stories->[0] if $unique;
277 sub _check_and_regularize_params {
278 my ($self, $params) = @_;
281 if (defined $params->{title}) {
282 if (length($params->{title}) > $self->{options}->{Story}->{title_max_byte}) {
283 $msg = "title too long. max: $self->{options}->{Story}->{title_max_byte} bytes";
284 $self->set_error($msg, -1);
289 $params->{commentstatus} = $params->{commentstatus} || $params->{comment_status} || "enabled";
290 if (defined $params->{commentstatus}) {
291 if (!grep /\A$params->{commentstatus}\z/, qw(disabled
298 $msg = "invalid comment_status";
299 $self->set_error($msg, -1);
304 # check timestamp. use ISO8601 style timestamp like: 2006-08-14T02:34:56-0600
305 if ($params->{time}) {
306 my $rex_timestamp = qr/
307 ^(\d+)-(\d+)-(\d+)\D+(\d+):(\d+):(\d+(?:\.\d+)?) # datetime
308 (?:Z|([+-])(\d+):(\d+))?$ # tz
310 if ($params->{time} =~ $rex_timestamp) {
311 $params->{time} = "$1-$2-$3 $4:$5:$6";
318 sub _set_tags_from_topics {
319 my ($self, $user, $stoid, $topics) = @_;
324 my $globjs = $self->new_instance_of("Newslash::Model::Globjs");
325 my $globj_id = $globjs->getGlobjidFromTargetIfExists("stories", $stoid);
328 my $tags = $self->new_instance_of("Tags");
329 for my $tid (keys %$topics) {
330 my $ret = $tags->set_tag(uid => $user->{uid} || $user->{user_id},
332 globj_id => $globj_id,
335 #warn "set_tag fault..." if !$ret
343 this implementation uses old slash's updateStory($sid, $data),
344 $sid is takable sid or stoid.
349 #my ($self, $params, $user, $extra_params, $opts) = @_;
354 my $id = $params->{stoid} || $params->{story_id} || $params->{id};
356 $self->set_error("story id not given");
361 return if !$self->_check_and_regularize_params($params);
363 my $stoid = $params->{stoid} || $params->{story_id} || $params->{id};
364 my $slash_db = Newslash::Model::SlashDB->new($self->{options});
366 my $sid = $slash_db->updateStory($stoid, $params);
369 $self->_set_tags_from_topics($params->{user}, $stoid, $params->{topics_chosen});
376 return $self->generic_update(params => $params);
380 =head2 create(\%params, $uid)
412 #my ($self, $params, $user, $extra_params, $opts) = @_;
414 return if $self->check_readonly;
417 my $user = $params->{user};
421 $msg = "no_title" if !$params->{title};
422 $msg = "no_introtext" if !$params->{introtext} || $params->{intro_text};
423 $msg = "no_topics" if !defined $params->{topics_chosen};
424 $msg = "invalid_user" if !defined $user->{uid};
426 if (length($params->{title}) > $self->{options}->{Story}->{title_max_byte}) {
427 $msg = "title too long. max: $self->{options}->{Story}->{title_max_byte} bytes";
430 $params->{commentstatus} = $params->{commentstatus} || $params->{comment_status} || "enabled";
431 if (!grep /\A$params->{commentstatus}\z/, qw(disabled enabled friends_only friends_fof_only no_foe no_foe_eof logged_in)) {
432 $msg = "invalid comment_status";
435 # check timestamp. use ISO8601 style timestamp like: 2006-08-14T02:34:56-0600
436 if ($params->{time}) {
437 my $rex_timestamp = qr/
438 ^(\d+)-(\d+)-(\d+)[^ 0-9]+(\d+):(\d+):(\d+(?:\.\d+)?) # datetime
439 (?:Z|([+-])(\d+):(\d+))?$ # tz
441 if ($params->{time} =~ $rex_timestamp) {
442 my $dt = DateTime::Format::ISO8601->parse_datetime($params->{time});
443 $params->{time} = DateTime::Format::MySQL->format_datetime($dt);
447 # check parameters finish
448 if (length($msg) > 0) {
449 $self->set_error($msg, -1);
452 $params->{neverdisplay} ||= 0;
453 $params->{submitter} ||= $user->{uid};
454 $params->{uid} = $user->{uid};
456 # createStory() deletes topics_chosen, so need to save here.
457 my $topics_chosen = $params->{topics_chosen};
459 my $slash_db = Newslash::Model::SlashDB->new($self->{options});
461 if ($params->{update}) {
462 $stoid = $params->{stoid} || $params->{story_id} || $params->{id};
463 $sid = $slash_db->updateStory($stoid, $params);
464 $self->set_error("updateStory failed");
468 ($sid, $stoid) = $slash_db->createStory($params);
471 my $globjs = $self->new_instance_of("Newslash::Model::Globjs");
472 my $globj_id = $globjs->getGlobjidFromTargetIfExists("stories", $params->{stoid});
475 my $tags = $self->new_instance_of("Tags");
476 for my $tid (keys %$topics_chosen) {
477 my $ret = $tags->set_tag(uid => $user->{uid} || $user->{user_id},
479 globj_id => $globj_id,
482 #warn "set_tag fault..." if !$ret
491 return $self->generic_insert(params => $params);
495 my ($self, @params) = @_;
496 my $params = {@params};
497 my $dt = $params->{base_datetime} || DateTime->now;
499 # create sid from timestamp
500 # my $sid_format = '%02d/%02d/%02d/%02d%0d2%02d';
501 my $sid_format = '%y/%m/%d/%H%M%S';
502 my $sid = $dt->strftime($sid_format);
504 # insert blank story with given sid
505 my $dbh = $self->connect_db;
506 my $sql = "INSERT INTO stories (sid) VALUES (?)";
508 my $n = 100; # retry 100 times
510 my $rs = $dbh->do($sql, undef, $sid);
512 $self->set_error("sid_insert_error", -1);
516 my $stoid = $dbh->last_insert_id(undef, undef, undef, undef);
517 $self->disconnect_db;
518 return ($sid, $stoid);
521 # allocate failed, so recreate sid
522 $dt->subtract( seconds => 1 );
523 $sid = $dt->strftime($sid_format);
525 $self->set_error("sid_allocate_failed", -1);
526 $self->disconnect_db;
533 my ($self, $bogus_sid) = @_;
534 # yes, this format is correct, don't change it :-)
535 my $sidformat = '%02d/%02d/%02d/%02d%0d2%02d';
536 # Create a sid based on the current time.
538 my $start_time = time;
540 # If we were called being told that there's at
541 # least one sid that is invalid (already taken),
542 # then look backwards in time until we find it,
543 # then go one second further.
547 @lt = localtime($start_time);
548 $lt[5] %= 100; $lt[4]++; # year and month
549 last if $bogus_sid eq sprintf($sidformat, @lt[reverse 0..5]);
552 # Found the bogus sid by looking
553 # backwards. Go one second further.
556 # Something's wrong. Skip ahead in
557 # time instead of back (not sure what
559 $start_time = time + 1;
562 @lt = localtime($start_time);
563 $lt[5] %= 100; $lt[4]++; # year and month
564 return sprintf($sidformat, @lt[reverse 0..5]);
575 =head2 get_related_items($stoid)
593 ARRAY of related links
599 sub get_related_items {
602 my $stoid = $params->{stoid} || $params->{story_id} || $params->{id};
605 my $dbh = $self->connect_db;
609 story_text.title as title2,
613 SELECT * FROM related_stories
615 ORDER BY ordernum ASC
617 LEFT JOIN story_text ON story_text.stoid = related.rel_stoid
618 LEFT JOIN firehose ON firehose.id = related.fhid
619 LEFT JOIN stories ON stories.stoid = related.rel_stoid
620 LEFT JOIN topics ON topics.tid = stories.tid
623 my $sth = $dbh->prepare($sql);
624 $sth->execute($stoid);
625 my $related = $sth->fetchall_arrayref({});
626 $self->disconnect_db();
628 for my $r (@$related) {
629 $r->{title} = $r->{title2} unless $r->{title};
631 $r->{type} = "story";
632 $r->{key_id} = $r->{rel_sid};
634 $r->{type} = "submission";
635 $r->{key_id} = $r->{srcid};
637 $r->{primary_topic} = {};
638 $r->{primary_topic}->{tid} = $r->{tid};
639 for my $k (qw{keyword textname series image width height
640 submittable searchable storypickable usesprite}) {
642 $r->{primary_topic}->{$k} = $r->{$k};
650 =head2 parameters($stoid)
652 get story parameters.
675 my ($self, $stoid) = @_;
677 my $dbh = $self->connect_db;
680 SELECT * FROM story_param WHERE stoid = ?
683 my $sth = $dbh->prepare($sql);
684 $sth->execute($stoid);
685 my $params = $sth->fetchall_hashref('name');
688 # get next/prev story info
689 if ($params->{next_stoid} || $params->{prev_stoid}) {
691 SELECT stories.*, story_text.* FROM stories JOIN story_text ON stories.stoid = story_text.stoid WHERE stories.stoid = ? OR stories.stoid = ?
693 $sth = $dbh->prepare($sql);
694 my $next = $params->{next_stoid}->{value} || 0;
695 my $prev = $params->{prev_stoid}->{value} || 0;
696 $sth->execute($next, $prev);
697 my $sids = $sth->fetchall_hashref('stoid');
698 $params->{next_stoid}->{story} = $sids->{$next} if $sids->{$next};
699 $params->{prev_stoid}->{story} = $sids->{$prev} if $sids->{$prev};
703 $self->disconnect_db();
707 #========================================================================
709 =head2 set_dirty($key, $id)
711 set writestatus dirty for the story.
738 my ($self, $key, $id) = @_;
739 return if $self->check_readonly;
742 if ($key eq 'stoid') {
749 my $dbh = $self->connect_db;
751 INSERT INTO story_dirty (stoid) VALUES (?)
754 my $rs = $dbh->do($sql, undef, $stoid);
762 my ($self, $story, $params) = @_;
766 $story->{id} = $story->{stoid};
767 $story->{story_id} = $story->{stoid};
768 $story->{create_time} = $story->{time};
769 $story->{update_time} = $story->{last_update};
771 for my $t (@{$story->{topics}}) {
772 if ($t->{tid} && $t->{tid} == $story->{tid}) {
773 $story->{primary_topic} = $t;
777 $story->{content_type} = "story";
778 $story->{intro_text} = $story->{introtext};
779 $story->{bodytext} ||= "";
780 $story->{body_text} = $story->{bodytext};
781 if ($story->{body_text}) {
782 $story->{full_text} = join("\n", $story->{intro_text}, $story->{body_text});
785 $story->{full_text} = $story->{intro_text};
787 $story->{fulltext} = $story->{full_text};
789 $story->{discussion_id} = $story->{discussion};
791 # no public flag given, public is 'yes'
792 $story->{public} = 'yes' if !$story->{public};
796 # delete story from database
797 # this method is for test purpose only.
800 return if $self->check_readonly;
803 my $stoid = $params->{story_id};
809 #my $dbh = $self->connect_db({AutoCommit => 0,});
810 my $dbh = $self->start_transaction;
811 for my $table (qw(stories story_param story_text story_topics_chosen story_topics_rendered)) {
812 my $sql = "DELETE FROM $table WHERE stoid = ?";
813 my $rs = $dbh->do($sql, undef, $stoid);
814 if (!defined $rs || $rs == 0) {
815 Mojo::Log->new->warn("DELETE FROM $table failed. stoid is $stoid.");
820 # delete from firehose
821 my $firehose = $self->new_instance_of("Firehose");
822 $firehose->hard_delete("story", $stoid);