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

   1 package Newslash::Model::Comments;
   2 use Newslash::Model::Base -base;
   3
   4 use Data::Dumper;
   5 use Mojo::Log;
   6
   7 use bytes qw();
   8 use Digest::MD5 qw(md5_hex);
   9 use Encode;
  10
  11 sub count {
  12     my $self = shift;
  13     my $join = 'LEFT JOIN firehose ON (comments.cid = firehose.srcid AND firehose.type = "comment")';
  14     my $where = 'firehose.public != "no"';
  15     return $self->generic_count(table => "comments",
  16                                 target => "cid",
  17                                 timestamp => "date",
  18                                 join => $join,
  19                                 where => $where,
  20                                 @_);
  21 }
  22
  23 #========================================================================
  24
  25 =head2 select($query_type, $value)
  26
  27 get comments.
  28
  29 =over 4
  30
  31 =item Parameters
  32
  33 =over 4
  34
  35 =item $query_type
  36
  37 query key, one of below.
  38
  39   "stoid": story ID (int)
  40   "cid": comment ID
  41   "pid": parent comment ID
  42
  43   Warning: stoid is unique int ID for story, not strings value (/yy/mm/xxxxxx style).
  44            ... In comments table, I don't know why, use 'sid' column name for int ID value.
  45            But in this function, use "stoid" for consistency.
  46
  47 =item $value
  48
  49 value for query
  50
  51 =back
  52
  53 =item Return value
  54
  55 ARRAY of comment contents
  56
  57 =back
  58
  59 =cut
  60
  61 sub select {
  62     my $self = shift;
  63     my $params = {@_};
  64
  65     my $unique_keys = { id => "comments.cid",
  66                         comment_id => "comments.cid",
  67                         cid => "comments.cid",
  68                       };
  69     my $keys = { pid => "comments.pid",
  70                  parent_id => "comments.pid",
  71                  discussion_id => "comments.sid",
  72                  uid => "comments.uid",
  73                  user_id => "comments.uid",
  74                  points => "comments.points",
  75                  create_time => "comments.date",
  76                  update_time => "comments.last_update",
  77                };
  78     my $datetime_keys = { create_time => "comments.date",
  79                           update_time => "comments.last_update",
  80                         };
  81     my $timestamp = "comments.date";
  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     $orderby_clause = "ORDER BY comments.cid ASC" if !$orderby_clause;
  93
  94     # TODO: give reasonable LIMIT Value...
  95     $limit_clause = "LIMIT 1000" if !$limit_clause;
  96
  97     my @attrs;
  98     push @attrs, @$where_values, @$limit_values, @$orderby_values;
  99     my $dbh = $self->connect_db;
 100
 101     # `ORDER BY comments.cid ASC` is needed by comment-tree-building.
 102     # if change there, must fix other codes.
 103     my $sql = <<"EOSQL";
 104 SELECT comments.*, comment_text.*, users.nickname AS author, users.sig AS signature
 105   FROM comments
 106     LEFT JOIN comment_text ON comments.cid = comment_text.cid
 107     LEFT JOIN users ON comments.uid = users.uid
 108   $where_clause
 109   $orderby_clause
 110   $limit_clause
 111 EOSQL
 112
 113     my $sth = $dbh->prepare($sql);
 114
 115     $sth->execute(@attrs);
 116     my $comments = $sth->fetchall_arrayref(+{});
 117     if (!defined $comments) {
 118         $self->set_error($dbh->errstr, $dbh->err);
 119         $self->disconnect_db;
 120         return;
 121     }
 122     $self->disconnect_db;
 123
 124     if (!$comments) {
 125         return;
 126     }
 127
 128     my $sec_lv = $params->{security_level} || 0;
 129
 130     if ($unique) {
 131         my $c = $comments->[0];
 132         $self->_generalize($c);
 133         return $c;
 134     }
 135
 136     for my $c (@$comments) {
 137         $self->_generalize($c);
 138     }
 139     return $comments;
 140 }
 141
 142 sub build_comment_tree {
 143     my ($self, $comments)  = @_;
 144     my $root = [];
 145     my $comment_of = {};
 146
 147     # prerequisite: $comments are sorted with ascendent order by cid
 148     for my $comment (@$comments) {
 149         my $pid = $comment->{pid};
 150         my $cid = $comment->{cid};
 151         $comment_of->{$cid} = $comment;
 152         $comment->{children} = [];
 153
 154         if ($pid == 0) {
 155             push @$root, $comment;
 156             next;
 157         }
 158
 159         my $parent = $comment_of->{$pid};
 160         if ($parent) {
 161             push @{$parent->{children}}, $comment;
 162         } else {
 163             warn("(Controller::Story) invalid comment order. cid: $cid, pid: $pid");
 164         }
 165     }
 166     return $root;
 167 }
 168
 169 sub generate_signature {
 170     my ($self, $comment) = @_;
 171     return md5_hex(encode_utf8($comment));
 172 }
 173
 174 sub _generalize {
 175     my ($self, $c) = @_;
 176
 177     # NTO-nized
 178     $c->{id} = $c->{cid};
 179     $c->{comment_id} = $c->{cid};
 180     $c->{create_time} = $c->{date};
 181     $c->{update_time} = $c->{last_update};
 182     $c->{content_type} = "comment";
 183     $c->{title} = $c->{subject};
 184     $c->{introtext} = $c->{comment};
 185     $c->{intro_text} = $c->{comment};
 186     $c->{full_text} = $c->{intro_text};
 187     $c->{fulltext} = $c->{full_text};
 188     $c->{public} => "yes";
 189
 190     $c->{discussion_id} = $c->{sid};
 191
 192     $c->{parent_id} = $c->{pid};
 193 }
 194
 195
 196 #========================================================================
 197
 198 =head2 create(\%params, \%options)
 199
 200 create comment
 201
 202 see saveComment in Slass/Utility/Comments/Comments.pm too.
 203
 204 =over 4
 205
 206 =item Parameters
 207
 208 =over 4
 209
 210 =item \%options
 211
 212 comment parameters
 213
 214  * sid
 215  * pid
 216  * title
 217  * comment
 218  * tags
 219
 220 =item \%options
 221
 222 options for submission
 223
 224 =back
 225
 226 =item Return value
 227
 228 ARRAY of story contents
 229
 230 =back
 231
 232 =cut
 233
 234 sub create {
 235     my ($self, $params, $user, $extra_params, $opts) = @_;
 236     if (!$params || !$user) {
 237         $self->set_error("no params or no user given!");
 238         return;
 239     }
 240     return if $self->check_readonly;
 241
 242     $opts ||= {};
 243     $extra_params ||= {};
 244
 245     # check vital parameters are exists
 246     if (!$params->{discussion_id}
 247         || !$params->{title}
 248         || !$params->{comment}
 249         || !$user) {
 250         $self->set_error("vital parameters not given!");
 251         return;
 252     }
 253
 254     # if pid given, validate them
 255     my $subject_orig = 'yes';
 256     $params->{pid} ||= 0;
 257     if ($params->{pid}) {
 258         my $parent = $self->select(cid => $params->{pid});
 259
 260         if (!$parent) {
 261             $self->set_error("parent comment not exists", 100);
 262             return;
 263         }
 264         if ($parent->{sid} != $params->{discussion_id}) {
 265             $self->set_error("parent comment belongs another story", 101);
 266             return;
 267         }
 268         # TODO: check visibility
 269         # check title is reply?
 270         if ($params->{title} =~ /$parent->{subject}/) {
 271             $subject_orig = 'no';
 272         }
 273     }
 274     my $subject_max_length = 50;
 275     my $subject = substr $params->{title}, 0, $subject_max_length;
 276     my $signature = $self->generate_signature($params->{comment});
 277
 278     my $users = $self->new_instance_of("Newslash::Model::Users");
 279     #use Newslash::Model::Moderations;
 280     my $moderations = $self->new_instance_of("Newslash::Model::Moderations");
 281     my $user_param = $users->param->select(uid => $user->{uid});
 282
 283     my $nobonus = exists $params->{nobonus} ? $params->{nobonus} : ($user_param->{nobonus} || 0);
 284     my $scores = $moderations->score_for_comment_post($user);
 285
 286     my $sql = <<"EOSQL";
 287 INSERT INTO comments
 288     (sid, pid, date,  ipid, subnetid, subject, subject_orig, uid, points, pointsorig,
 289      pointsmax, signature, len, karma_bonus, karma, karma_abs, tweak_orig, tweak)
 290   VALUES
 291     (?,   ?,   NOW(), ?,    ?,        ?,       ?,            ?,   ?,      ?,
 292      ?,         ?,         ?,   ?,           ?,     ?,         ?,          ?);
 293 EOSQL
 294
 295     $params->{ipid} = $user->{ipid};
 296     $params->{subnetid} = $user->{subnetid};
 297     $params->{subject} = $subject;
 298     $params->{subject_orig} = $subject_orig;
 299     $params->{uid} = $user->{uid};
 300     $params->{points} = $scores->{points};
 301     $params->{pointsorig} = $scores->{points};
 302     $params->{pointsmax} = $scores->{points};
 303     $params->{len} = bytes::length $params->{comment};
 304     $params->{karma} ||= 0;
 305     $params->{karma_abs} ||= 0;
 306     $params->{karma_bonus} = $scores->{karma_bonus};
 307     $params->{tweak_orig} = $scores->{tweak};
 308     $params->{tweak} = $scores->{tweak};
 309
 310     #my $dbh = $self->connect_db({AutoCommit => 0,});
 311     my $dbh = $self->start_transaction;
 312     my $rs = $dbh->do($sql, undef,
 313                       $params->{discussion_id},
 314                       $params->{pid},
 315                       $params->{ipid},
 316                       $params->{subnetid},
 317                       $params->{subject},
 318                       $params->{subject_orig},
 319                       $params->{uid},
 320                       $params->{points}, #points
 321                       $params->{pointsorig}, #pointsorig
 322                       $params->{pointsmax}, #pointsmax
 323                       $signature, #signature,
 324                       $params->{len},
 325                       $params->{karma_bonus}, #karma_bonus
 326                       $params->{karma},
 327                       $params->{karma_abs},
 328                       $params->{tweak_orig}, #tweak_orig
 329                       $params->{tweak} #tweak
 330                      );
 331     if (!$rs) {
 332         # error occured
 333         $self->set_error("mysql_error", $dbh->{mysql_errno});
 334         #$dbh->rollback;
 335         #$dbh->disconnect;
 336         $self->rollback;
 337         return undef;
 338     }
 339     my $cid = $dbh->last_insert_id(undef, undef, undef, undef);
 340     $sql = "INSERT INTO comment_text (cid, comment) VALUE (?, ?)";
 341     $rs = $dbh->do($sql, undef, $cid, $params->{comment});
 342     if (!$rs) {
 343         # error occured
 344         $self->set_error("mysql_error", $dbh->{mysql_errno});
 345         #$dbh->rollback;
 346         #$dbh->disconnect;
 347         $self->rollback;
 348         return undef;
 349     }
 350
 351     # update discussion
 352     $sql = "UPDATE discussions SET commentcount = commentcount + 1 WHERE id = ?";
 353     $rs = $dbh->do($sql, undef, $params->{discussion_id});
 354     if (!$rs) {
 355         # error occured
 356         $self->set_error("mysql_error", $dbh->{mysql_errno});
 357         #$dbh->rollback;
 358         #$dbh->disconnect;
 359         $self->rollback;
 360         return undef;
 361     }
 362
 363     # check and undo moderation
 364     if ($user->{is_login}
 365         && !$user->{is_admin}
 366         && !$user->{author}) {
 367         my $discussions = $self->new_instance_of("Moderations");
 368         my $undo = $moderations->undo_moderation(discussion_id => $params->{discussion_id}, $user->{uid});
 369         if ($undo) {
 370             $self->{undo_moderation} = 1;
 371         }
 372     }
 373
 374     use Newslash::Model::Vars;
 375     my $vars = $self->new_instance_of("Newslash::Model::Vars");
 376     $vars->add('totalComments', 1);
 377
 378     #my $stories = $self->new_instance_of("Newslash::Model::Stories");
 379     # update below story parameter.
 380     # $stories->set_dirty(stoid => $params->{stoid});
 381     my $slash_db = $self->new_instance_of("Newslash::Model::SlashDB");
 382     $slash_db->setStory($params->{discussion_id}, { writestatus => 'dirty' });
 383
 384     # TODO:
 385     # in slash's original implement, stories.commentcount and stories.hitparade is not
 386     # updated here. they are updated by slashd.
 387
 388     if ($user->{is_login}) {
 389         my $users = $self->new_instance_of("Newslash::Model::Users");
 390         $users->info->update(uid => $user->{uid},
 391                              totalcomments => {add => 1});
 392     }
 393
 394     #$dbh->commit;
 395     #$dbh->disconnect;
 396     $self->commit;
 397
 398     # create firehose item
 399     $params->{cid} = $cid;
 400     my $firehose = $self->new_instance_of("Newslash::Model::LegacyFirehose");
 401     $firehose->createItemFromComment($cid, $params);
 402     #$self->firehose_createItemFromComment($cid, $params);
 403
 404     # saveCommentInfo is deprecated.
 405     # $slashdb->saveCommentInfo($maxCid, $submit_type);
 406
 407     # TODO: check cid is overflowed?
 408
 409     # TODO: messaging
 410     # TODO: achievements
 411
 412     return $cid;
 413 }
 414
 415 sub firehose_createItemFromComment {
 416     my($self, $cid, $comment) = @_;
 417     return if $self->check_readonly;
 418
 419     #my $comment = $self->getComment($cid);
 420     #my $text = $self->getCommentText($cid);
 421
 422     my $globjs = $self->new_instance_of("Newslash::Model::Globjs");
 423     #my $globjid = $self->getGlobjidCreate("comments", $cid);
 424     my $globjid = $globjs->getGlobjidCreate("comments", $cid);
 425
 426
 427     my $firehose = $self->new_instance_of("Newslash::Model::LegacyFirehose");
 428     # Set initial popularity scores -- we'll be forcing a quick
 429     # recalculation of them so these scores don't much matter.
 430     my ($popularity, $editorpop, $neediness);
 431     #$popularity = $self->getEntryPopularityForColorLevel(7);
 432     #$editorpop = $self->getEntryPopularityForColorLevel(7);
 433     #$neediness = $self->getEntryPopularityForColorLevel(6);
 434     $popularity = $firehose->getEntryPopularityForColorLevel(7);
 435     $editorpop = $firehose->getEntryPopularityForColorLevel(7);
 436     $neediness = $firehose->getEntryPopularityForColorLevel(6);
 437
 438     my $data = {
 439                 uid             => $comment->{uid},
 440                 public          => "yes",
 441                 title           => $comment->{subject},
 442                 introtext       => $comment->{comment},
 443                 ipid            => $comment->{ipid},
 444                 subnetid        => $comment->{subnetid},
 445                 type            => "comment",
 446                 srcid           => $comment->{cid},
 447                 popularity      => $popularity,
 448                 editorpop       => $editorpop,
 449                 globjid         => $globjid,
 450                 discussion      => $comment->{sid},
 451                 createtime      => $comment->{date},
 452                };
 453     #my $fhid = $self->createFireHose($data);
 454     my $fhid = $firehose->createFireHose($data);
 455
 456     # TODO: tags?
 457     # if (!isAnon($comment->{uid})) {
 458     #     my $constants = getCurrentStatic();
 459     #     my $tags = getObject('Slash::Tags');
 460     #     $tags->createTag({
 461     #                       uid                     => $comment->{uid},
 462     #                       name                    => $constants->{tags_upvote_tagname},
 463     #                       globjid                 => $globjid,
 464     #                       private                 => 1,
 465     #                      });
 466     # }
 467     #
 468     # my $tagboxdb = getObject('Slash::Tagbox');
 469     # if ($tagboxdb) {
 470     #     for my $tbname (qw( FireHoseScores FHEditorPop CommentScoreReason )) {
 471     #         my $tagbox = $tagboxdb->getTagboxes($tbname);
 472     #         next unless $tagbox;
 473     #         $tagbox->{object}->forceFeederRecalc($globjid);
 474     #     }
 475     # }
 476
 477     return $fhid;
 478 }
 479
 480
 481 #========================================================================
 482
 483 =head2 update
 484
 485 update comment infomation.
 486
 487 =over 4
 488
 489 =item Return value
 490
 491 HASH
 492
 493 =back
 494
 495 =cut
 496
 497 sub update {
 498     my $self = shift;
 499     return if $self->check_readonly;
 500
 501     my $params = {@_};
 502     my $cid = $params->{cid};
 503     return if !$cid;
 504
 505     my $user = $params->{user};
 506
 507     #my $columns = qw(points lastmod reason);
 508
 509     my @values;
 510     my @set_clauses;
 511
 512     if (defined $params->{points}) {
 513         if (!ref($params->{points})) {
 514             push @values, $params->{points};
 515             push @set_clauses, "points = ?";
 516
 517             # update pointsmax column
 518             push @values, $params->{points};
 519             push @set_clauses, "pointsmax = GREATEST(pointsmax, ?)";
 520         }
 521         elsif (ref($params->{points}) eq 'HASH') {
 522             my $p = $params->{points};
 523             if ($p->{add}) {
 524                 if (defined $p->{max} && defined $p->{min}) {
 525                     push @values, $p->{min};
 526                     push @values, $p->{max};
 527                     push @values, $p->{add};
 528                     push @set_clauses, "points = GREATEST(?, LEAST(?, points + ?))";
 529
 530                 # update pointsmax column
 531                     push @values, $p->{min};
 532                     push @values, $p->{max};
 533                     push @values, $p->{add};
 534                     push @set_clauses, "pointsmax = GREATEST(pointsmax, GREATEST(?, LEAST(?, points + ?)))";
 535                 }
 536                 else {
 537                     push @values, $p->{add};
 538                     push @set_clauses, "points = points + ?";
 539
 540                 # update pointsmax column
 541                     push @values, $p->{add};
 542                     push @set_clauses, "pointsmax = GREATEST(pointsmax, points + ?)";
 543                 }
 544             }
 545         }
 546     }
 547
 548     for my $column (qw(karma karma_abs tweak)) {
 549         if ($params->{$column}) {
 550             if (ref($params->{$column}) eq 'SCALAR') {
 551                 push @values, $params->{$column};
 552                 push @set_clauses, "$column = ?";
 553             }
 554             elsif (ref($params->{$column}) eq 'HASH') {
 555                 if ($params->{$column}->{add}) {
 556                     push @values, $params->{$column}->{add};
 557                     push @set_clauses, "$column = $column + ?";
 558                 }
 559             }
 560         }
 561     }
 562
 563     for my $column (qw(reason lastmod karma_bonus)) {
 564         if ($params->{$column}) {
 565             push @values, $params->{$column};
 566             push @set_clauses, "$column = ?";
 567         }
 568     }
 569
 570     return 0 if !@set_clauses;
 571     my $set_clause = join(", ", @set_clauses);
 572
 573     my $where_clause = "cid = ?";
 574     push @values, $cid;
 575
 576     if ($params->{lastmod}) {
 577         $where_clause = $where_clause . "AND lastmod != ?";
 578         push @values, $params->{lastmod};
 579     }
 580
 581     my $sql = "UPDATE comments SET $set_clause WHERE $where_clause";
 582     my $dbh = $self->connect_db;
 583     my $rs = $dbh->do($sql, undef, @values);
 584     if (!$rs) {
 585         $self->disconnect_db;
 586         return $rs;
 587     }
 588     $self->disconnect_db;
 589     return 1;
 590 }
 591
 592 # delete comment from database
 593 # this method is for test purpose only.
 594 sub hard_delete {
 595     my $self = shift;
 596     return if $self->check_readonly;
 597     my $params = {@_};
 598
 599     my $cid = $params->{cid};
 600     my $discuss_id = $params->{discussion_id};
 601     return if !$cid && !$discuss_id;
 602
 603     my $error = 0;
 604     my $sql;
 605
 606     my $dbh = $self->start_transaction;
 607
 608     if ($discuss_id) {
 609         # first, delete comment_text
 610         my $sql = "DELETE FROM comment_text WHERE cid = (SELECT cid FROM comments WHERE sid = ?)";
 611         my $rs = $dbh->do($sql, undef, $discuss_id);
 612         if (!defined $rs || $rs == 0) {
 613             Mojo::Log->new->warn("DELETE FROM comment_text failed. cid is $cid.");
 614             $error = 1;
 615         }
 616
 617         # delete comments
 618         if (!$error) {
 619             $sql = "DELETE FROM comments WHERE sid = ?";
 620             $rs = $dbh->do($sql, undef, $discuss_id);
 621             if (!defined $rs || $rs == 0) {
 622                 Mojo::Log->new->warn("DELETE FROM comments failed. cid is $cid.");
 623                 $error = 1;
 624             }
 625         }
 626         # todo: delete from firehose
 627     }
 628
 629     if ($cid) {
 630         for my $table (qw(comments comment_text)) {
 631             my $sql = "DELETE FROM $table WHERE cid = ?";
 632             my $rs = $dbh->do($sql, undef, $cid);
 633             if (!defined $rs || $rs == 0) {
 634                 Mojo::Log->new->warn("DELETE FROM $table failed. cid is $cid.");
 635                 $error = 1;
 636             }
 637         }
 638         # delete from firehose
 639         my $firehose = $self->new_instance_of("Firehose");
 640         $firehose->hard_delete("comment", $cid);
 641     }
 642
 643
 644     if ($error) {
 645         $self->rollback;
 646         return;
 647     }
 648     $self->commit;
 649     return 1;
 650
 651     # delete firehose item
 652     # delete firehose_text item
 653     # delete firehose_topics_rendererd item
 654     # delete globjs
 655     # delete stories item
 656     # delete story_param
 657     # delete story_text
 658     # delete story_topics_chosen
 659     # delete story_topics_rendered
 660     # delete tags
 661
 662     return;
 663 }
 664
 665 1;