src/newslash_web/lib/Newslash/Model/Stories.pm

   1 package Newslash::Model::Stories;
   2 use Newslash::Model::Base -base;
   3
   4 use Newslash::Model::SlashDB;
   5
   6 use DateTime::Format::MySQL;
   7
   8 use Data::Dumper;
   9 use DateTime;
  10 use Newslash::Util::Formatters qw(datetime_to_string);
  11
  12 use constant FACULTIES => { 1000 => [qw(hits hitparade)] };
  13
  14 sub count {
  15     my $self = shift;
  16     my $join = 'LEFT JOIN firehose ON (stories.stoid = firehose.srcid AND firehose.type = "story")';
  17     my $where = 'firehose.public != "no"';
  18     return $self->generic_count(table => "stories",
  19                                 target => "stoid",
  20                                 timestamp => "time",
  21                                 join => $join,
  22                                 where => $where,
  23                                 @_);
  24 }
  25
  26 #========================================================================
  27
  28 =head2 select($query_type, $value)
  29
  30 get a story.
  31
  32 =over 4
  33
  34 =item Parameters
  35
  36 =over 4
  37
  38 =item $query_type
  39
  40 query key, "sid" or "stoid"
  41
  42 =item $value
  43
  44 value for query
  45
  46 =back
  47
  48 =item Return value
  49
  50 HASH of story contents
  51
  52 =back
  53
  54 =cut
  55
  56 sub select {
  57     my $self = shift;
  58     my $params = {@_};
  59
  60
  61     my $unique_keys = { id => "stories.stoid",
  62                         story_id => "stories.stoid",
  63                         stoid => "stories.stoid",
  64                         sid => "stories.sid",
  65                       };
  66     my $keys = { user_id => "stories.uid",
  67                  uid => "stories.uid",
  68                  topic_id => "stories.tid",
  69                  tid => "stories.tid",
  70                  discussion_id => "stories.discussion",
  71                  commentcount => "stories.commentcount",
  72                  hits => "stories.hits",
  73                  submitter => "stories.submitter",
  74                  create_time => "stories.time",
  75                  update_time => "stories.last_update",
  76                  public => "firehose.public",
  77                };
  78     my $datetime_keys = { create_time => "stories.time",
  79                           update_time => "stories.last_update",
  80                         };
  81     my $timestamp = "stories.time";
  82
  83     my ($where_clause, $where_values, $unique) = $self->build_where_clause(unique_keys => $unique_keys,
  84                                                                            keys => $keys,
  85                                                                            datetime_keys => $datetime_keys,
  86                                                                            timestamp => $timestamp,
  87                                                                            params => $params);
  88     my ($limit_clause, $limit_values) = $self->build_limit_clause(params => $params);
  89     my ($orderby_clause, $orderby_values) = $self->build_order_by_clause(keys => $keys,
  90                                                                          params => $params);
  91
  92     # TODO: give reasonable LIMIT Value...
  93     $limit_clause = "LIMIT 50" if !$limit_clause;
  94
  95     # hide future story?
  96     my @where_clauses;
  97     if ($params->{hide_future}) {
  98         push @where_clauses, "stories.time <= NOW()";
  99     }
 100
 101     # hide non-public story?
 102     if ($params->{public_only}) {
 103         push @where_clauses, "firehose.public != 'no'";
 104     }
 105
 106     if (@where_clauses) {
 107         if ($where_clause) {
 108             $where_clause = $where_clause . " AND ";
 109         }
 110         else {
 111             $where_clause = "WHERE ";
 112         }
 113         $where_clause = $where_clause . join(" AND ", @where_clauses);
 114     }
 115
 116     my @attrs;
 117     push @attrs, @$where_values, @$limit_values, @$orderby_values;
 118
 119     my $dbh = $self->connect_db;
 120     my $sql = <<"EOSQL";
 121 SELECT stories.*, story_text.*, users.nickname as author, firehose.public,
 122        discussions.type AS discussion_type, discussions.commentcount AS comment_count
 123   FROM stories
 124     LEFT JOIN story_text ON stories.stoid = story_text.stoid
 125     LEFT JOIN users ON stories.uid = users.uid
 126     LEFT JOIN firehose
 127       ON (stories.stoid = firehose.srcid AND firehose.type = "story")
 128     LEFT JOIN discussions ON firehose.discussion = discussions.id
 129     $where_clause
 130     $orderby_clause
 131     $limit_clause
 132 EOSQL
 133
 134     #warn($sql);
 135     #warn(Dumper(@attrs));
 136
 137     my $sth = $dbh->prepare($sql);
 138     $sth->execute(@attrs);
 139     my $stories = $sth->fetchall_arrayref({});
 140
 141     if (!$stories) {
 142         $self->disconnect_db();
 143         return;
 144     }
 145     if (@$stories == 0) {
 146         $self->disconnect_db();
 147         return $unique ? undef : [];
 148     }
 149
 150     # get tags
 151     $sql = <<"EOSQL";
 152 SELECT tags.*, tagnames.tagname, target.stoid
 153   FROM (SELECT stories.stoid FROM stories
 154         LEFT JOIN firehose ON (stories.stoid = firehose.srcid AND firehose.type = "story")
 155         $where_clause $orderby_clause $limit_clause) AS target
 156     LEFT JOIN globjs
 157       ON target.stoid = globjs.target_id
 158     LEFT JOIN tags
 159       ON globjs.globjid = tags.globjid
 160     LEFT JOIN tagnames
 161       ON tags.tagnameid = tagnames.tagnameid
 162     WHERE globjs.gtid = 1
 163 EOSQL
 164
 165     $sth = $dbh->prepare($sql);
 166     $sth->execute(@attrs);
 167     my $tags_table = $sth->fetchall_arrayref({});
 168
 169     # get topics
 170     $sql = <<"EOSQL";
 171 SELECT story_topics_rendered.*, story_topics_chosen.weight, topics.*
 172   FROM (SELECT stories.stoid FROM stories
 173         LEFT JOIN firehose ON (stories.stoid = firehose.srcid AND firehose.type = "story")
 174         $where_clause $orderby_clause $limit_clause) AS target
 175     LEFT JOIN story_topics_rendered
 176       ON target.stoid = story_topics_rendered.stoid
 177     LEFT JOIN story_topics_chosen
 178       ON story_topics_rendered.stoid = story_topics_chosen.stoid
 179         AND story_topics_rendered.tid = story_topics_chosen.tid
 180     LEFT JOIN topics
 181       ON story_topics_rendered.tid = topics.tid
 182 EOSQL
 183
 184     $sth = $dbh->prepare($sql);
 185     $sth->execute(@attrs);
 186     my $topics_table = $sth->fetchall_arrayref({});
 187
 188     # get params
 189     $sql = <<"EOSQL";
 190 SELECT story_param.*
 191   FROM (SELECT stories.stoid FROM stories
 192         LEFT JOIN firehose ON (stories.stoid = firehose.srcid AND firehose.type = "story")
 193         $where_clause $orderby_clause $limit_clause) AS target
 194     LEFT JOIN story_param
 195       ON target.stoid = story_param.stoid
 196 EOSQL
 197
 198     $sth = $dbh->prepare($sql);
 199     $sth->execute(@attrs);
 200     my $params_table = $sth->fetchall_arrayref({});
 201
 202     # done
 203     $self->disconnect_db();
 204
 205
 206     my $tags = {};
 207     for my $tag (@$tags_table) {
 208         my $stoid = $tag->{stoid};
 209         if (!$tags->{$stoid}) {
 210             $tags->{$stoid} = [];
 211         }
 212         push @{$tags->{$stoid}}, $tag;
 213     }
 214
 215     my $topics = {};
 216     for my $topic (@$topics_table) {
 217         my $stoid = $topic->{stoid};
 218         if (!$topics->{$stoid}) {
 219             $topics->{$stoid} = [];
 220         }
 221         push @{$topics->{$stoid}}, $topic;
 222     }
 223
 224     my $params = {};
 225     for my $param (@$params_table) {
 226         my $stoid = $param->{stoid};
 227         if (!$params->{$stoid}) {
 228             $params->{$stoid} = [];
 229         }
 230         push @{$params->{$stoid}}, $param;
 231     }
 232
 233     for my $story (@$stories) {
 234         my $stoid = $story->{stoid};
 235         $story->{tags} = $tags->{$stoid} if $tags->{$stoid};
 236         $story->{topics} = $topics->{$stoid} if $topics->{$stoid};
 237         if ($params->{$stoid}) {
 238             for my $param (@{$params->{$stoid}}) {
 239                 $story->{$param->{name}} = $param->{value};
 240             }
 241         }
 242         $self->_generalize($story, $params);
 243     }
 244
 245     return $stories->[0] if $unique;
 246     return $stories;
 247 }
 248
 249 sub _check_and_regularize_params {
 250     my ($self, $params) = @_;
 251     my $msg;
 252
 253     if (defined $params->{title}) {
 254         if (length($params->{title}) > $self->{options}->{Story}->{title_max_byte}) {
 255             $msg = "title too long. max: $self->{options}->{Story}->{title_max_byte} bytes";
 256             $self->set_error($msg, -1);
 257             return;
 258         }
 259     }
 260
 261     $params->{commentstatus} = $params->{commentstatus} || $params->{comment_status} || "enabled";
 262     if (defined $params->{commentstatus}) {
 263         if (!grep /\A$params->{commentstatus}\z/, qw(disabled
 264                                                      enabled
 265                                                      friends_only
 266                                                      friends_fof_only
 267                                                      no_foe
 268                                                      no_foe_eof
 269                                                      logged_in)) {
 270             $msg = "invalid comment_status";
 271             $self->set_error($msg, -1);
 272             return;
 273         }
 274     }
 275
 276     # check timestamp. use ISO8601 style timestamp like: 2006-08-14T02:34:56-0600
 277     if ($params->{time}) {
 278         my $rex_timestamp = qr/
 279                                   ^(\d+)-(\d+)-(\d+)\D+(\d+):(\d+):(\d+(?:\.\d+)?)   # datetime
 280                                   (?:Z|([+-])(\d+):(\d+))?$                          # tz
 281                               /xi;
 282         if ($params->{time} =~ $rex_timestamp) {
 283             $params->{time} = "$1-$2-$3 $4:$5:$6";
 284         }
 285     }
 286
 287     return 1;
 288 }
 289
 290 sub _set_tags_from_topics {
 291     my ($self, $user, $stoid, $topics) = @_;
 292
 293     return if !$stoid;
 294     return if !$topics;
 295
 296     my $globjs = $self->new_instance_of("Newslash::Model::Globjs");
 297     my $globj_id = $globjs->getGlobjidFromTargetIfExists("stories", $stoid);
 298     if ($globj_id) {
 299         # set tags
 300         my $tags = $self->new_instance_of("Tags");
 301         for my $tid (keys %$topics) {
 302             my $ret = $tags->set_tag(uid => $user->{uid} || $user->{user_id},
 303                                      tagname_id => $tid,
 304                                      globj_id => $globj_id,
 305                                      private => 0,
 306                                     );
 307             #warn "set_tag fault..." if !$ret
 308         }
 309     }
 310     return $stoid;
 311 }
 312
 313 =head2 update
 314
 315 this implementation uses old slash's updateStory($sid, $data),
 316 $sid is takable sid or stoid.
 317
 318 =cut
 319
 320 sub update {
 321     #my ($self, $params, $user, $extra_params, $opts) = @_;
 322     my $self = shift;
 323     my $params = {@_};
 324
 325     # check id
 326     my $id = $params->{stoid} || $params->{story_id} || $params->{id};
 327     if (!$id) {
 328         $self->set_error("story id not given");
 329         return;
 330     }
 331
 332     # check params
 333     return if !$self->_check_and_regularize_params($params);
 334
 335     my $stoid = $params->{stoid} || $params->{story_id} || $params->{id};
 336     my $slash_db = Newslash::Model::SlashDB->new($self->{options});
 337
 338     my $sid = $slash_db->updateStory($stoid, $params);
 339     return if !$sid;
 340
 341     $self->_set_tags_from_topics($params->{user}, $stoid, $params->{topics_chosen});
 342     return $stoid;
 343
 344 }
 345
 346
 347 =head2 create(\%params, $uid)
 348
 349 create a story.
 350
 351 =over 4
 352
 353 =item Parameters
 354
 355 =over 4
 356
 357 =item \%params
 358
 359 parameters
 360
 361 $params->{fhid}
 362 $params->{subid}
 363
 364 =item $uid
 365
 366 author's uid
 367
 368 =back
 369
 370 =item Return value
 371
 372 stoid
 373
 374 =back
 375
 376 =cut
 377
 378 sub create {
 379     #my ($self, $params, $user, $extra_params, $opts) = @_;
 380     my $self = shift;
 381     return if $self->check_readonly;
 382
 383     my $params = {@_};
 384     my $user = $params->{user};
 385
 386     # check parameters
 387     my $msg = "";
 388     $msg = "no title" if !$params->{title};
 389     $msg = "no introtext" if !$params->{introtext} || $params->{intro_text};
 390     $msg = "no uid" if !$params->{uid} || $params->{user_id};
 391     $msg = "no topics" if !defined $params->{topics_chosen};
 392     $msg = "invalid user" if ref($user) ne 'HASH';
 393
 394     if (length($params->{title}) > $self->{options}->{Story}->{title_max_byte}) {
 395         $msg = "title too long. max: $self->{options}->{Story}->{title_max_byte} bytes";
 396     }
 397
 398     $params->{commentstatus} = $params->{commentstatus} || $params->{comment_status} || "enabled";
 399     if (!grep /\A$params->{commentstatus}\z/, qw(disabled enabled friends_only friends_fof_only no_foe no_foe_eof logged_in)) {
 400         $msg = "invalid comment_status";
 401     }
 402
 403     # check timestamp. use ISO8601 style timestamp like: 2006-08-14T02:34:56-0600
 404     if ($params->{time}) {
 405         my $rex_timestamp = qr/
 406                                   ^(\d+)-(\d+)-(\d+)\D+(\d+):(\d+):(\d+(?:\.\d+)?)   # datetime
 407                                   (?:Z|([+-])(\d+):(\d+))?$                          # tz
 408                               /xi;
 409         if ($params->{time} =~ $rex_timestamp) {
 410             $params->{time} = "$1-$2-$3 $4:$5:$6";
 411         }
 412     }
 413
 414     # check parameters finish
 415     if (length($msg) > 0) {
 416         $self->set_error($msg, -1);
 417         return;
 418     }
 419
 420     $params->{neverdisplay} ||= 0;
 421
 422     # createStory() deletes topics_chosen, so need to save here.
 423     my $topics_chosen = $params->{topics_chosen};
 424
 425     my $slash_db = Newslash::Model::SlashDB->new($self->{options});
 426     my ($sid, $stoid);
 427     if ($params->{update}) {
 428         $stoid = $params->{stoid} || $params->{story_id} || $params->{id};
 429         $sid = $slash_db->updateStory($stoid, $params);
 430         $self->set_error("updateStory failed");
 431         return if !$sid;
 432     }
 433     else {
 434         ($sid, $stoid) = $slash_db->createStory($params);
 435     }
 436
 437     my $globjs = $self->new_instance_of("Newslash::Model::Globjs");
 438     my $globj_id = $globjs->getGlobjidFromTargetIfExists("stories", $params->{stoid});
 439     if ($globj_id) {
 440         # set tags
 441         my $tags = $self->new_instance_of("Tags");
 442         for my $tid (keys %$topics_chosen) {
 443             my $ret = $tags->set_tag(uid => $user->{uid} || $user->{user_id},
 444                                      tagname_id => $tid,
 445                                      globj_id => $globj_id,
 446                                      private => 0,
 447                                     );
 448             #warn "set_tag fault..." if !$ret
 449         }
 450     }
 451     return $stoid;
 452 }
 453
 454 sub createSid {
 455     my ($self, $bogus_sid) = @_;
 456     # yes, this format is correct, don't change it :-)
 457     my $sidformat = '%02d/%02d/%02d/%02d%0d2%02d';
 458     # Create a sid based on the current time.
 459     my @lt;
 460     my $start_time = time;
 461     if ($bogus_sid) {
 462         # If we were called being told that there's at
 463         # least one sid that is invalid (already taken),
 464         # then look backwards in time until we find it,
 465         # then go one second further.
 466         my $loops = 1000;
 467         while (--$loops) {
 468             $start_time--;
 469             @lt = localtime($start_time);
 470             $lt[5] %= 100; $lt[4]++; # year and month
 471             last if $bogus_sid eq sprintf($sidformat, @lt[reverse 0..5]);
 472         }
 473         if ($loops) {
 474             # Found the bogus sid by looking
 475             # backwards.  Go one second further.
 476             $start_time--;
 477         } else {
 478             # Something's wrong.  Skip ahead in
 479             # time instead of back (not sure what
 480             # else to do).
 481             $start_time = time + 1;
 482         }
 483     }
 484     @lt = localtime($start_time);
 485     $lt[5] %= 100; $lt[4]++; # year and month
 486     return sprintf($sidformat, @lt[reverse 0..5]);
 487 }
 488
 489
 490 =head2 get_histories
 491
 492 =cut
 493
 494 sub get_histories {
 495 }
 496
 497 =head2 get_related_items($stoid)
 498
 499 get related links.
 500
 501 =over 4
 502
 503 =item Parameters
 504
 505 =over 4
 506
 507 =item $stoid
 508
 509 story id
 510
 511 =back
 512
 513 =item Return value
 514
 515 ARRAY of related links
 516
 517 =back
 518
 519 =cut
 520
 521 sub get_related_items {
 522     my $self = shift;
 523     my $params = {@_};
 524     my $stoid = $params->{stoid} || $params->{story_id} || $params->{id};
 525     return if !$stoid;
 526
 527     my $dbh = $self->connect_db;
 528
 529     my $sql = <<"EOSQL";
 530 SELECT related.*,
 531        story_text.title as title2,
 532        firehose.srcid,
 533        topics.*
 534   FROM (
 535     SELECT * FROM related_stories
 536       WHERE stoid = ?
 537       ORDER BY ordernum ASC
 538     ) AS related
 539   LEFT JOIN story_text ON story_text.stoid = related.rel_stoid
 540   LEFT JOIN firehose ON firehose.id = related.fhid
 541   LEFT JOIN stories ON stories.stoid = related.rel_stoid
 542   LEFT JOIN topics ON topics.tid = stories.tid
 543 EOSQL
 544
 545     my $sth = $dbh->prepare($sql);
 546     $sth->execute($stoid);
 547     my $related = $sth->fetchall_arrayref({});
 548     $self->disconnect_db();
 549
 550     for my $r (@$related) {
 551         $r->{title} = $r->{title2} unless $r->{title};
 552         if ($r->{rel_sid}) {
 553             $r->{type} = "story";
 554             $r->{key_id} = $r->{rel_sid};
 555         } else {
 556             $r->{type} = "submission";
 557             $r->{key_id} = $r->{srcid};
 558         }
 559         $r->{primary_topic} = {};
 560         $r->{primary_topic}->{tid} = $r->{tid};
 561         for my $k (qw{keyword textname series image width height
 562                       submittable searchable storypickable usesprite}) {
 563             next if !$r->{$k};
 564             $r->{primary_topic}->{$k} = $r->{$k};
 565             delete $r->{$k};
 566         }
 567     }
 568
 569     return $related;
 570 }
 571
 572 =head2 parameters($stoid)
 573
 574 get story parameters.
 575
 576 =over 4
 577
 578 =item Parameters
 579
 580 =over 4
 581
 582 =item $stoid
 583
 584 story id
 585
 586 =back
 587
 588 =item Return value
 589
 590 HASH of parameters
 591
 592 =back
 593
 594 =cut
 595
 596 sub parameters {
 597     my ($self, $stoid) = @_;
 598
 599     my $dbh = $self->connect_db;
 600
 601     my $sql = <<"EOSQL";
 602 SELECT * FROM story_param WHERE stoid = ?
 603 EOSQL
 604
 605     my $sth = $dbh->prepare($sql);
 606     $sth->execute($stoid);
 607     my $params = $sth->fetchall_hashref('name');
 608     $sth->finish;
 609
 610     # get next/prev story info
 611     if ($params->{next_stoid} || $params->{prev_stoid}) {
 612         $sql = <<"EOSQL";
 613 SELECT stories.*, story_text.* FROM stories JOIN story_text ON stories.stoid = story_text.stoid WHERE stories.stoid = ? OR stories.stoid = ?
 614 EOSQL
 615         $sth = $dbh->prepare($sql);
 616         my $next = $params->{next_stoid}->{value} || 0;
 617         my $prev = $params->{prev_stoid}->{value} || 0;
 618         $sth->execute($next, $prev);
 619         my $sids = $sth->fetchall_hashref('stoid');
 620         $params->{next_stoid}->{story} = $sids->{$next} if $sids->{$next};
 621         $params->{prev_stoid}->{story} = $sids->{$prev} if $sids->{$prev};
 622         $sth->finish;
 623     }
 624
 625     $self->disconnect_db();
 626     return $params;
 627 }
 628
 629 #========================================================================
 630
 631 =head2 set_dirty($key, $id)
 632
 633 set writestatus dirty for the story.
 634
 635 =over 4
 636
 637 =item Parameters
 638
 639 =over 4
 640
 641 =item $key
 642
 643 'stoid'
 644
 645 =item $id
 646
 647 id of the story
 648
 649 =back
 650
 651 =item Return value
 652
 653 1/0
 654
 655 =back
 656
 657 =cut
 658
 659 sub set_dirty {
 660     my ($self, $key, $id) = @_;
 661     return if $self->check_readonly;
 662
 663     my $stoid;
 664     if ($key eq 'stoid') {
 665         $stoid = $id;
 666     }
 667     else {
 668         return;
 669     }
 670
 671     my $dbh = $self->connect_db;
 672     my $sql = <<"EOSQL";
 673 INSERT INTO story_dirty (stoid) VALUES (?)
 674 EOSQL
 675
 676     my $rs = $dbh->do($sql, undef, $stoid);
 677     if (!$rs) {
 678         return;
 679     }
 680     return 1;
 681 }
 682
 683 sub _generalize {
 684     my ($self, $story, $params) = @_;
 685     $params ||= {};
 686
 687     # NTO-nized
 688     $story->{id} = $story->{stoid};
 689     $story->{story_id} = $story->{stoid};
 690     $story->{create_time} = $story->{time};
 691     $story->{update_time} = $story->{last_update};
 692
 693     for my $t (@{$story->{topics}}) {
 694         if ($t->{tid} && $t->{tid} == $story->{tid}) {
 695             $story->{primary_topic} = $t;
 696         }
 697     }
 698
 699     $story->{content_type} = "story";
 700     $story->{intro_text} = $story->{introtext};
 701     $story->{bodytext} ||= "";
 702     $story->{body_text} = $story->{bodytext};
 703     if ($story->{body_text}) {
 704         $story->{full_text} = join("\n", $story->{intro_text}, $story->{body_text});
 705     }
 706     else {
 707         $story->{full_text} = $story->{intro_text};
 708     }
 709     $story->{fulltext} = $story->{full_text};
 710
 711     $story->{discussion_id} = $story->{discussion};
 712
 713     # no public flag given, public is 'yes'
 714     $story->{public} = 'yes' if !$story->{public};
 715
 716 }
 717
 718 # delete story from database
 719 # this method is for test purpose only.
 720 sub hard_delete {
 721     my $self = shift;
 722     return if $self->check_readonly;
 723     my $params = {@_};
 724
 725     my $stoid = $params->{story_id};
 726     return if !$stoid;
 727
 728     my $error = 0;
 729     my $sql;
 730
 731     #my $dbh = $self->connect_db({AutoCommit => 0,});
 732     my $dbh = $self->start_transaction;
 733     for my $table (qw(stories story_param story_text story_topics_chosen story_topics_rendered)) {
 734         my $sql = "DELETE FROM $table WHERE stoid = ?";
 735         my $rs = $dbh->do($sql, undef, $stoid);
 736         if (!defined $rs || $rs == 0) {
 737             Mojo::Log->new->warn("DELETE FROM $table failed. stoid is $stoid.");
 738             $error = 1;
 739         }
 740     }
 741
 742     # delete from firehose
 743     my $firehose = $self->new_instance_of("Firehose");
 744     $firehose->hard_delete("story", $stoid);
 745
 746     $self->commit;
 747     return !$error;
 748
 749     # delete globjs
 750     # delete tags
 751
 752     return;
 753 }
 754
 755 1;