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 Newslash::Model::Stories::Text;
   7 use Newslash::Model::Stories::RelatedStories;
   8
   9 use DateTime;
  10 use DateTime::Format::MySQL;
  11 use DateTime::Format::ISO8601;
  12 use List::Util q(any);
  13
  14 use Data::Dumper;
  15 use DateTime;
  16
  17
  18 sub key_definition {
  19     return {
  20             table => "stories",
  21             primary => "stoid",
  22             unique => [qw(sid)],
  23             datetime => [qw(time last_update day_published
  24                             stuckendtime archive_last_update
  25                           )],
  26             other => [qw(uid dept hits discussion primaryskid
  27                          tid submitter commentcount hitparade
  28                          is_archived in_trash
  29                          qid body_length word_count sponsor
  30                          stuck stuckpos fakeemail homepage
  31                        )],
  32             aliases => { user_id => "uid",
  33                          id => "stoid",
  34                          create_time => time,
  35                        }
  36            };
  37 }
  38
  39 use constant FACULTIES => { 1000 => [qw(hits hitparade)] };
  40
  41 sub count_by_author {
  42     my $self = shift;
  43     my $sql = <<"EOSQL";
  44 SELECT uid, COUNT(stoid) AS count
  45   FROM stories
  46   WHERE stories.time <= NOW()
  47   GROUP BY uid;
  48 EOSQL
  49
  50     my $dbh = $self->connect_db;
  51     my $sth = $dbh->prepare($sql);
  52     $sth->execute();
  53     my $rs = $sth->fetchall_hashref("uid");
  54     if (!defined $rs) {
  55         $self->set_error("select failed", $dbh->errstr, $dbh->err);
  56         $self->disconnect_db;
  57         return;
  58     }
  59     $self->disconnect_db;
  60     return $rs;
  61 }
  62
  63 sub count {
  64     my $self = shift;
  65     my $join = 'LEFT JOIN firehose ON (stories.stoid = firehose.srcid AND firehose.type = "story")';
  66     my $where = 'firehose.public != "no"';
  67     return $self->generic_count(table => "stories",
  68                                 target => "stoid",
  69                                 timestamp => "time",
  70                                 join => $join,
  71                                 where => $where,
  72                                 @_);
  73 }
  74
  75 ##### sub models
  76 sub text            { return shift->_get_model('::Stories::Text'); }
  77 sub related_stories { return shift->_get_model('::Stories::RelatedStories'); }
  78
  79 sub _get_model {
  80     my ($self, $class) = @_;
  81     $self->{sub_models} ||= {};
  82     if (!$self->{sub_models}->{$class}) {
  83         $self->{sub_models}->{$class} = $self->new_instance_of($class);
  84     }
  85     else {
  86         if ($self->transaction_mode && !$self->{sub_models}->{$class}->transaction_mode) {
  87             $self->{sub_models}->{$class}->use_transaction($self->{_tr_dbh});
  88         }
  89     }
  90     return $self->{sub_models}->{$class};
  91 }
  92
  93 #========================================================================
  94
  95 =head2 select($query_type, $value)
  96
  97 get a story.
  98
  99 =over 4
 100
 101 =item Parameters
 102
 103 =over 4
 104
 105 =item $query_type
 106
 107 query key, "sid" or "stoid"
 108
 109 =item $value
 110
 111 value for query
 112
 113 =back
 114
 115 =item Return value
 116
 117 HASH of story contents
 118
 119 =back
 120
 121 =cut
 122
 123 sub select {
 124     my $self = shift;
 125     my $params = {@_};
 126
 127
 128     my $unique_keys = { id => "stories.stoid",
 129                         story_id => "stories.stoid",
 130                         stoid => "stories.stoid",
 131                         sid => "stories.sid",
 132                       };
 133     my $keys = { user_id => "stories.uid",
 134                  uid => "stories.uid",
 135                  topic_id => "stories.tid",
 136                  tid => "stories.tid",
 137                  discussion_id => "stories.discussion",
 138                  commentcount => "stories.commentcount",
 139                  hits => "stories.hits",
 140                  submitter => "stories.submitter",
 141                  create_time => "stories.time",
 142                  update_time => "stories.last_update",
 143                  public => "firehose.public",
 144                };
 145     my $datetime_keys = { create_time => "stories.time",
 146                           update_time => "stories.last_update",
 147                         };
 148     my $timestamp = "stories.time";
 149
 150     my ($where_clause, $where_values, $unique) = $self->build_where_clause(unique_keys => $unique_keys,
 151                                                                            keys => $keys,
 152                                                                            datetime_keys => $datetime_keys,
 153                                                                            timestamp => $timestamp,
 154                                                                            params => $params);
 155     my ($limit_clause, $limit_values) = $self->build_limit_clause(params => $params);
 156     my ($orderby_clause, $orderby_values) = $self->build_order_by_clause(keys => $keys,
 157                                                                          params => $params);
 158
 159     # TODO: give reasonable LIMIT Value...
 160     $limit_clause = "LIMIT 50" if !$limit_clause;
 161
 162     # hide future story?
 163     my @where_clauses;
 164     if ($params->{hide_future}) {
 165         push @where_clauses, "stories.time <= NOW()";
 166     }
 167
 168     # hide non-public story?
 169     if ($params->{public_only}) {
 170         push @where_clauses, "firehose.public != 'no'";
 171     }
 172
 173     # exclude "preview" item
 174     push @where_clauses, "globj_types.maintable != 'preview'";
 175
 176     if (@where_clauses) {
 177         if ($where_clause) {
 178             $where_clause = $where_clause . " AND ";
 179         }
 180         else {
 181             $where_clause = "WHERE ";
 182         }
 183         $where_clause = $where_clause . join(" AND ", @where_clauses);
 184     }
 185
 186     my @attrs;
 187     push @attrs, @$where_values, @$limit_values, @$orderby_values;
 188
 189     my $dbh = $self->connect_db;
 190     my $sql = <<"EOSQL";
 191 SELECT stories.*,
 192        story_text.*,
 193        users.nickname as author,
 194        firehose.public,
 195        firehose.popularity,
 196        firehose.editorpop,
 197        firehose.neediness,
 198        firehose.activity,
 199        firehose.globjid,
 200        firehose.id AS fhid,
 201        discussions.type AS discussion_type,
 202        discussions.commentcount AS comment_count
 203   FROM stories
 204     LEFT JOIN story_text
 205       ON stories.stoid = story_text.stoid
 206     LEFT JOIN users
 207       ON stories.uid = users.uid
 208     LEFT JOIN firehose
 209       ON (stories.stoid = firehose.srcid AND firehose.type = "story")
 210     LEFT JOIN discussions
 211       ON firehose.discussion = discussions.id
 212     LEFT JOIN globjs
 213       ON firehose.globjid = globjs.globjid
 214     LEFT JOIN globj_types
 215       ON globjs.gtid = globj_types.gtid
 216     $where_clause
 217     $orderby_clause
 218     $limit_clause
 219 EOSQL
 220
 221     $self->_last_query($sql, \@attrs);
 222
 223     my $sth = $dbh->prepare($sql);
 224     $sth->execute(@attrs);
 225     my $stories = $sth->fetchall_arrayref({});
 226
 227     if (!$stories) {
 228         $self->disconnect_db();
 229         return;
 230     }
 231     if (@$stories == 0) {
 232         $self->disconnect_db();
 233         return $unique ? undef : [];
 234     }
 235
 236     # get tags
 237     $sql = <<"EOSQL";
 238 SELECT tags.*, tagnames.tagname, target.stoid
 239   FROM (SELECT stories.stoid FROM stories
 240         LEFT JOIN firehose
 241           ON (stories.stoid = firehose.srcid AND firehose.type = "story")
 242         LEFT JOIN globjs
 243           ON firehose.globjid = globjs.globjid
 244         LEFT JOIN globj_types
 245           ON globjs.gtid = globj_types.gtid
 246         $where_clause $orderby_clause $limit_clause) AS target
 247     LEFT JOIN globjs
 248       ON target.stoid = globjs.target_id
 249     LEFT JOIN globj_types
 250       ON globjs.gtid = globj_types.gtid
 251     LEFT JOIN tags
 252       ON globjs.globjid = tags.globjid
 253     LEFT JOIN tagnames
 254       ON tags.tagnameid = tagnames.tagnameid
 255     WHERE globjs.gtid = 1
 256 EOSQL
 257
 258     $sth = $dbh->prepare($sql);
 259     $sth->execute(@attrs);
 260     my $tags_table = $sth->fetchall_arrayref({});
 261
 262     # get topics
 263     $sql = <<"EOSQL";
 264 SELECT story_topics_rendered.*, story_topics_chosen.weight, topics.*
 265   FROM (SELECT stories.stoid FROM stories
 266         LEFT JOIN firehose
 267           ON (stories.stoid = firehose.srcid AND firehose.type = "story")
 268         LEFT JOIN globjs
 269           ON firehose.globjid = globjs.globjid
 270         LEFT JOIN globj_types
 271           ON globjs.gtid = globj_types.gtid
 272         $where_clause $orderby_clause $limit_clause) AS target
 273     LEFT JOIN story_topics_rendered
 274       ON target.stoid = story_topics_rendered.stoid
 275     LEFT JOIN story_topics_chosen
 276       ON story_topics_rendered.stoid = story_topics_chosen.stoid
 277         AND story_topics_rendered.tid = story_topics_chosen.tid
 278     LEFT JOIN topics
 279       ON story_topics_rendered.tid = topics.tid
 280 EOSQL
 281
 282     $sth = $dbh->prepare($sql);
 283     $sth->execute(@attrs);
 284     my $topics_table = $sth->fetchall_arrayref({});
 285
 286     # get params
 287     $sql = <<"EOSQL";
 288 SELECT story_param.*
 289   FROM (SELECT stories.stoid FROM stories
 290         LEFT JOIN firehose
 291           ON (stories.stoid = firehose.srcid AND firehose.type = "story")
 292         LEFT JOIN globjs
 293           ON firehose.globjid = globjs.globjid
 294         LEFT JOIN globj_types
 295           ON globjs.gtid = globj_types.gtid
 296         $where_clause $orderby_clause $limit_clause) AS target
 297     LEFT JOIN story_param
 298       ON target.stoid = story_param.stoid
 299 EOSQL
 300
 301     $sth = $dbh->prepare($sql);
 302     $sth->execute(@attrs);
 303     my $params_table = $sth->fetchall_arrayref({});
 304
 305     # done
 306     $self->disconnect_db();
 307
 308
 309     my $tags = {};
 310     for my $tag (@$tags_table) {
 311         my $stoid = $tag->{stoid};
 312         if (!$tags->{$stoid}) {
 313             $tags->{$stoid} = [];
 314         }
 315         push @{$tags->{$stoid}}, $tag;
 316     }
 317
 318     my $topics = {};
 319     for my $topic (@$topics_table) {
 320         my $stoid = $topic->{stoid};
 321         if (!$topics->{$stoid}) {
 322             $topics->{$stoid} = [];
 323         }
 324         push @{$topics->{$stoid}}, $topic;
 325     }
 326
 327     my $params = {};
 328     for my $param (@$params_table) {
 329         my $stoid = $param->{stoid};
 330         if (!$params->{$stoid}) {
 331             $params->{$stoid} = [];
 332         }
 333         push @{$params->{$stoid}}, $param;
 334     }
 335
 336     for my $story (@$stories) {
 337         my $stoid = $story->{stoid};
 338         $story->{tags} = $tags->{$stoid} || [];
 339         $story->{topics} = $topics->{$stoid} || [];
 340         if ($params->{$stoid}) {
 341             for my $param (@{$params->{$stoid}}) {
 342                 $story->{$param->{name}} = $param->{value};
 343             }
 344         }
 345         $self->_generalize($story, $params);
 346     }
 347
 348     return $stories->[0] if $unique;
 349     return $stories;
 350 }
 351
 352 sub _check_and_regularize_params {
 353     my ($self, $params) = @_;
 354     my $msg;
 355
 356     if (defined $params->{title}) {
 357         if (length($params->{title}) > $self->{options}->{Story}->{title_max_byte}) {
 358             $msg = "title too long. max: $self->{options}->{Story}->{title_max_byte} bytes";
 359             $self->set_error($msg, -1);
 360             return;
 361         }
 362     }
 363
 364     $params->{commentstatus} = $params->{commentstatus} || $params->{comment_status} || "enabled";
 365     if (defined $params->{commentstatus}) {
 366         if (!grep /\A$params->{commentstatus}\z/, qw(disabled
 367                                                      enabled
 368                                                      friends_only
 369                                                      friends_fof_only
 370                                                      no_foe
 371                                                      no_foe_eof
 372                                                      logged_in)) {
 373             $msg = "invalid comment_status";
 374             $self->set_error($msg, -1);
 375             return;
 376         }
 377     }
 378
 379     # check timestamp. use ISO8601 style timestamp like: 2006-08-14T02:34:56-0600
 380     if ($params->{time}) {
 381         my $rex_timestamp = qr/
 382                                   ^(\d+)-(\d+)-(\d+)\D+(\d+):(\d+):(\d+(?:\.\d+)?)   # datetime
 383                                   (?:Z|([+-])(\d+):(\d+))?$                          # tz
 384                               /xi;
 385         if ($params->{time} =~ $rex_timestamp) {
 386             $params->{time} = "$1-$2-$3 $4:$5:$6";
 387         }
 388     }
 389
 390     return 1;
 391 }
 392
 393 sub _set_tags_from_topics {
 394     my ($self, $user, $stoid, $topics) = @_;
 395
 396     return if !$stoid;
 397     return if !$topics;
 398
 399     my $globjs = $self->new_instance_of("Newslash::Model::Globjs");
 400     my $globj_id = $globjs->getGlobjidFromTargetIfExists("stories", $stoid);
 401     if ($globj_id) {
 402         # set tags
 403         my $tags = $self->new_instance_of("Tags");
 404         for my $tid (keys %$topics) {
 405             my $ret = $tags->set_tag(uid => $user->{uid} || $user->{user_id},
 406                                      tagname_id => $tid,
 407                                      globj_id => $globj_id,
 408                                      private => 0,
 409                                     );
 410             #warn "set_tag fault..." if !$ret
 411         }
 412     }
 413     return $stoid;
 414 }
 415
 416 =head2 update
 417
 418 this implementation uses old slash's updateStory($sid, $data),
 419 $sid is takable sid or stoid.
 420
 421 =cut
 422
 423 sub update {
 424     #my ($self, $params, $user, $extra_params, $opts) = @_;
 425     my $self = shift;
 426     my $params = {@_};
 427     my $user = $params->{user};
 428
 429     # check id
 430     my $story;
 431     my $stoid = $params->{stoid} || $params->{story_id} || $params->{id};
 432     my $sid = $params->{sid};
 433     if (!$story) {
 434         if ($stoid) {
 435             $story = $self->select(stoid => $stoid);
 436         }
 437         elsif ($sid) {
 438             $story = $self->select(sid => $sid);
 439         }
 440     }
 441     if ($story) {
 442         $sid = $story->{sid};
 443         $stoid = $story->{stoid};
 444     }
 445
 446     if (!$story) {
 447         $self->last_error("no story given");
 448         return;
 449     }
 450
 451     # check params
 452     return if !$self->_check_and_regularize_params($params);
 453
 454     my $slash_db = Newslash::Model::SlashDB->new($self->{options});
 455
 456     my $add_related = $params->{add_related};
 457     delete $params->{add_related} if $add_related;
 458     my $add_tags = $params->{add_tags};
 459     delete $params->{add_tags} if $add_tags;
 460
 461     $stoid = $slash_db->updateStory($stoid, $params);
 462     return if !$stoid;
 463
 464     $self->_set_tags_from_topics($params->{user}, $stoid, $params->{topics_chosen});
 465
 466     # set tags
 467     if ($add_tags) {
 468         my $tags = $self->new_instance_of("Tags");
 469         my $globjs = $self->new_instance_of("Newslash::Model::Globjs");
 470         my $globj_id = $globjs->getGlobjidFromTargetIfExists("stories", $params->{stoid});
 471         for my $tag (@$add_tags) {
 472             my $rs = $tags->add(uid => $user->{uid} || $user->{user_id},
 473                                 globj_id => $globj_id,
 474                                 name => $tag);
 475             if (!defined $rs) {
 476                 $self->logger->warn("Stories::update: tag $tag set failed: " . $tags->last_error);
 477             }
 478         }
 479     }
 480
 481     # insert related story
 482     if ($add_related) {
 483         for my $related_sid (@$add_related) {
 484             my $rs = $self->add_related_story(sid => $sid,
 485                                               related_sid => $related_sid);
 486             if (!defined $rs) {
 487                 $self->logger->warn("Stories::update: insert related story $related_sid to $sid failed: " . $self->last_error);
 488             }
 489
 490             $rs = $self->add_related_story(sid => $related_sid,
 491                                            related_sid => $sid);
 492             if (!defined $rs) {
 493                 $self->logger->warn("Stories::update: insert related story $sid to $related_sid failed: " . $self->last_error);
 494             }
 495         }
 496     }
 497
 498     return wantarray ? ($sid, $stoid) : $stoid;
 499 }
 500
 501 sub update2 {
 502     my $self = shift;
 503     my $params = {@_};
 504     return $self->generic_update(params => $params);
 505 }
 506
 507
 508 =head2 create(\%params, $uid)
 509
 510 create a story.
 511
 512 =over 4
 513
 514 =item Parameters
 515
 516 =over 4
 517
 518 =item \%params
 519
 520 parameters
 521
 522 $params->{fhid}
 523 $params->{subid}
 524
 525 =item $uid
 526
 527 author's uid
 528
 529 =back
 530
 531 =item Return value
 532
 533 stoid
 534
 535 =back
 536
 537 =cut
 538
 539 sub create {
 540     #my ($self, $params, $user, $extra_params, $opts) = @_;
 541     my $self = shift;
 542     return if $self->check_readonly;
 543
 544     my $params = {@_};
 545     my $user = $params->{user};
 546
 547     # check parameters
 548     my $msg = "";
 549     $msg = "no_title" if !$params->{title};
 550     $msg = "no_introtext" if !$params->{introtext} && !$params->{intro_text};
 551     $msg = "no_topics" if !defined $params->{topics_chosen};
 552     $msg = "invalid_user" if !defined $user->{uid};
 553
 554     if (length($params->{title}) > $self->{options}->{Story}->{title_max_byte}) {
 555         $msg = "title too long. max: $self->{options}->{Story}->{title_max_byte} bytes";
 556     }
 557
 558     $params->{commentstatus} = $params->{commentstatus} || $params->{comment_status} || "enabled";
 559     if (!grep /\A$params->{commentstatus}\z/, qw(disabled enabled friends_only friends_fof_only no_foe no_foe_eof logged_in)) {
 560         $msg = "invalid comment_status";
 561     }
 562
 563     # check timestamp. use ISO8601 style timestamp like: 2006-08-14T02:34:56-0600
 564     if ($params->{time}) {
 565         my $rex_timestamp = qr/
 566                                   ^(\d+)-(\d+)-(\d+)[^ 0-9]+(\d+):(\d+):(\d+(?:\.\d+)?)   # datetime
 567                                   (?:Z|([+-])(\d+):(\d+))?$                          # tz
 568                               /xi;
 569         if ($params->{time} =~ $rex_timestamp) {
 570             my $dt = DateTime::Format::ISO8601->parse_datetime($params->{time});
 571             $params->{time} = DateTime::Format::MySQL->format_datetime($dt);
 572         }
 573     }
 574
 575     # check parameters finish
 576     if (length($msg) > 0) {
 577         $self->set_error($msg, -1);
 578         return;
 579     }
 580     $params->{neverdisplay} ||= 0;
 581     $params->{uid} = $user->{uid};
 582
 583     # check submission
 584     if ($params->{submission_id}) {
 585         my $submission = $self->new_instance_of("Submissions")->select(id => $params->{submission_id});
 586         if ($submission) {
 587             $params->{submitter} = $submission->{uid};
 588             $params->{fhid} = $submission->{fhid};
 589         }
 590         else {
 591             $self->logger->error("Stories::create - invalid submission_id: $params->{submission_id}");
 592         }
 593     }
 594     if ($params->{journal_id}) {
 595         my $journal = $self->new_instance_if("Journals")->select(id => $params->{journal_id});
 596         if ($journal) {
 597             $params->{submitter} = $journal->{uid};
 598             $params->{fhid} = $journal->{fhid};
 599         }
 600         else {
 601             $self->logger->error("Stories::create - invalid journal_id: $params->{journal_id}");
 602         }
 603     }
 604     $params->{submitter} ||= $user->{uid};
 605
 606     # createStory() deletes topics_chosen, so need to save here.
 607     my $topics_chosen = $params->{topics_chosen};
 608
 609     my $add_related = $params->{add_related};
 610     delete $params->{add_related} if $add_related;
 611     my $add_tags = $params->{add_tags};
 612     delete $params->{add_tags} if $add_tags;
 613
 614     my $slash_db = Newslash::Model::SlashDB->new($self->{options});
 615     my ($sid, $stoid);
 616     if ($params->{update}) {
 617         $stoid = $params->{stoid} || $params->{story_id} || $params->{id};
 618         $sid = $slash_db->updateStory($stoid, $params);
 619         $self->set_error("updateStory failed");
 620         return if !$sid;
 621     }
 622     else {
 623         ($sid, $stoid) = $slash_db->createStory($params);
 624     }
 625
 626     my $globjs = $self->new_instance_of("Newslash::Model::Globjs");
 627     my $globj_id = $globjs->getGlobjidFromTargetIfExists("stories", $params->{stoid});
 628
 629     # set topics
 630     my $tags = $self->new_instance_of("Tags");
 631     if ($globj_id) {
 632         for my $tid (keys %$topics_chosen) {
 633             my $rs = $tags->add(uid => $user->{uid} || $user->{user_id},
 634                                 tagname_id => $tid,
 635                                 globj_id => $globj_id,
 636                                 private => 0,
 637                                );
 638             if (!defined $rs) {
 639                 $self->logger->warn("Stories::create: tag $tid set failed: " . $tags->last_error);
 640             }
 641         }
 642     }
 643
 644     # set tags
 645     if ($add_tags) {
 646         for my $tag (@$add_tags) {
 647             my $rs = $tags->add(uid => $user->{uid} || $user->{user_id},
 648                                 globj_id => $globj_id,
 649                                 name => $tag);
 650             if (!defined $rs) {
 651                 $self->logger->warn("Stories::create: tag $tag set failed: " . $tags->last_error);
 652             }
 653         }
 654     }
 655
 656     # insert related story
 657     if ($add_related) {
 658         for my $related_sid (@$add_related) {
 659             my $rs = $self->add_related_story(sid => $sid,
 660                                               related_sid => $related_sid);
 661             if (!defined $rs) {
 662                 $self->logger->warn("insert related story failed: " . $self->last_error);
 663             }
 664
 665             $rs = $self->add_related_story(sid => $related_sid,
 666                                            related_sid => $sid);
 667             if (!defined $rs) {
 668                 $self->logger->warn("Stories::create: insert related story failed: " . $self->last_error);
 669             }
 670         }
 671     }
 672
 673     # add firehose item to related_story
 674     if ($params->{submission_id}) {
 675         my $submissions = $self->new_instance_of("Submissions");
 676         my $submission = $submissions->select(id => $params->{submission_id});
 677         if ($submission) {
 678             my $rs = $self->add_related_firehose(sid => $sid,
 679                                                  fhid => $submission->{fhid});
 680             if (!defined $rs) {
 681                 $self->logger->error("Stories::create - insert related firehose failed: " . $self->last_error);
 682             }
 683
 684             # change submission status
 685             if (!$submissions->update(id => $submission->{subid}, del => 2)) {
 686                 $self->logger->error("Stories::create - cannot change submission status");
 687             }
 688         }
 689         else {
 690             $self->logger->error("Stories::create - invalid submission_id: $params->{submission_id}");
 691         }
 692     }
 693
 694
 695
 696     return wantarray ? ($sid, $stoid) : $stoid;
 697 }
 698
 699 sub create2 {
 700     my $self = shift;
 701     my $params = {@_};
 702     return $self->generic_insert(params => $params);
 703 }
 704
 705 sub allocate_sid {
 706     my ($self, @params) = @_;
 707     my $params = {@params};
 708     my $dt = $params->{base_datetime} || DateTime->now;
 709
 710     # create sid from timestamp
 711     # my $sid_format = '%02d/%02d/%02d/%02d%0d2%02d';
 712     my $sid_format = '%y/%m/%d/%H%M%S';
 713     my $sid = $dt->strftime($sid_format);
 714
 715     # insert blank story with given sid
 716     my $dbh = $self->connect_db;
 717     my $sql = "INSERT INTO stories (sid) VALUES (?)";
 718
 719     my $n = 100; # retry 100 times
 720     while (--$n) {
 721         my $rs = $dbh->do($sql, undef, $sid);
 722         if (!defined $rs) {
 723             $self->set_error("sid_insert_error", -1);
 724             return;
 725         }
 726         if ($rs) {
 727             my $stoid = $dbh->last_insert_id(undef, undef, undef, undef);
 728             $self->disconnect_db;
 729             return ($sid, $stoid);
 730         }
 731
 732         # allocate failed, so recreate sid
 733         $dt->subtract( seconds => 1 );
 734         $sid = $dt->strftime($sid_format);
 735     }
 736     $self->set_error("sid_allocate_failed", -1);
 737     $self->disconnect_db;
 738     return;
 739 }
 740
 741
 742 # Legacy API
 743 sub createSid {
 744     my ($self, $bogus_sid) = @_;
 745     # yes, this format is correct, don't change it :-)
 746     my $sidformat = '%02d/%02d/%02d/%02d%0d2%02d';
 747     # Create a sid based on the current time.
 748     my @lt;
 749     my $start_time = time;
 750     if ($bogus_sid) {
 751         # If we were called being told that there's at
 752         # least one sid that is invalid (already taken),
 753         # then look backwards in time until we find it,
 754         # then go one second further.
 755         my $loops = 1000;
 756         while (--$loops) {
 757             $start_time--;
 758             @lt = localtime($start_time);
 759             $lt[5] %= 100; $lt[4]++; # year and month
 760             last if $bogus_sid eq sprintf($sidformat, @lt[reverse 0..5]);
 761         }
 762         if ($loops) {
 763             # Found the bogus sid by looking
 764             # backwards.  Go one second further.
 765             $start_time--;
 766         } else {
 767             # Something's wrong.  Skip ahead in
 768             # time instead of back (not sure what
 769             # else to do).
 770             $start_time = time + 1;
 771         }
 772     }
 773     @lt = localtime($start_time);
 774     $lt[5] %= 100; $lt[4]++; # year and month
 775     return sprintf($sidformat, @lt[reverse 0..5]);
 776 }
 777
 778
 779 =head2 get_histories
 780
 781 =cut
 782
 783 sub get_histories {
 784 }
 785
 786 =head2 get_related_items($stoid)
 787
 788 get related links.
 789
 790 =over 4
 791
 792 =item Parameters
 793
 794 =over 4
 795
 796 =item $stoid
 797
 798 story id
 799
 800 =back
 801
 802 =item Return value
 803
 804 ARRAY of related links
 805
 806 =back
 807
 808 =cut
 809
 810 sub get_related_items {
 811     my $self = shift;
 812     my $params = {@_};
 813     my $stoid = $params->{stoid} || $params->{story_id} || $params->{id};
 814     return if !$stoid;
 815
 816     my $dbh = $self->connect_db;
 817
 818     my $sql = <<"EOSQL";
 819 SELECT related.*,
 820        story_text.title as title2,
 821        firehose.*,
 822        stories.*,
 823        topics.*
 824   FROM (
 825     SELECT * FROM related_stories
 826       WHERE stoid = ?
 827       ORDER BY ordernum ASC
 828     ) AS related
 829   LEFT JOIN story_text ON story_text.stoid = related.rel_stoid
 830   LEFT JOIN firehose ON firehose.id = related.fhid
 831   LEFT JOIN stories ON stories.sid = related.rel_sid
 832   LEFT JOIN topics ON topics.tid = stories.tid
 833 EOSQL
 834
 835     my $sth = $dbh->prepare($sql);
 836     $sth->execute($stoid);
 837     my $related = $sth->fetchall_arrayref({});
 838     $self->disconnect_db();
 839
 840     for my $r (@$related) {
 841         $r->{create_time} = $r->{time};
 842         $r->{title} = $r->{title2} unless $r->{title};
 843         if ($r->{rel_sid}) {
 844             $r->{type} = "story";
 845             $r->{key_id} = $r->{rel_sid};
 846         } else {
 847             $r->{type} = "submission";
 848             $r->{key_id} = $r->{srcid};
 849         }
 850         $r->{primary_topic} = {};
 851         $r->{primary_topic}->{tid} = $r->{tid};
 852         for my $k (qw{keyword textname series image width height
 853                       submittable searchable storypickable usesprite}) {
 854             next if !$r->{$k};
 855             $r->{primary_topic}->{$k} = $r->{$k};
 856             delete $r->{$k};
 857         }
 858     }
 859
 860     return $related;
 861 }
 862
 863 sub add_related_story {
 864     my $self = shift;
 865     my $args = {@_};
 866
 867     my $story = $args->{story};
 868     if (!$story) {
 869         if ($args->{stoid}) {
 870             $story = $self->select(stoid => $args->{stoid});
 871         } elsif ($args->{sid}) {
 872             $story = $self->select(sid => $args->{sid});
 873         }
 874     }
 875
 876     if (!$story || !$story->{stoid} || !$story->{sid}) {
 877         $self->last_error("cannot get target story");
 878         return;
 879     }
 880
 881     my $related = $args->{related};
 882     if (!$related) {
 883         if ($args->{related_stoid}) {
 884             $related = $self->select(stoid => $args->{related_stoid});
 885         } elsif ($args->{related_sid}) {
 886             $related = $self->select(sid => $args->{related_sid});
 887         }
 888     }
 889
 890     if (!$related || !$related->{stoid} || !$related->{sid} ) {
 891         $self->last_error("cannot get related story");
 892         return;
 893     }
 894
 895     # get related item count
 896     my $dbh = $self->start_transaction;
 897     my $sql = "SELECT rel_stoid FROM related_stories WHERE stoid = ?";
 898     my $stoids = $dbh->do($sql, undef, $story->{stoid});
 899     $stoids = [] if ref($stoids) ne "ARRAY";
 900
 901     # check if related story is already set
 902     if (any { $_ eq $related->{stoid} } @$stoids) {
 903         $self->commit;
 904         return 0;
 905     }
 906
 907     # insert
 908     my $count = @$stoids;
 909     my $rs = $self->related_stories->insert(stoid => $story->{stoid},
 910                                             rel_stoid => $related->{stoid},
 911                                             rel_sid => $related->{sid},
 912                                             ordernum => $count + 1);
 913
 914     if (!defined $rs) {
 915         $self->last_error("insert failed: " . $self->related_stories->last_error);
 916         $self->rollback;
 917         return;
 918     }
 919     $self->commit;
 920     return 1;
 921 }
 922
 923 sub add_related_firehose {
 924     my $self = shift;
 925     my $args = {@_};
 926
 927     my $story = $args->{story};
 928     if (!$story) {
 929         if ($args->{stoid}) {
 930             $story = $self->select(stoid => $args->{stoid});
 931         } elsif ($args->{sid}) {
 932             $story = $self->select(sid => $args->{sid});
 933         }
 934     }
 935
 936     if (!$story || !$story->{stoid} || !$story->{sid}) {
 937         $self->last_error("cannot get target story");
 938         return;
 939     }
 940
 941     my $fh_item = $args->{firehose_item};
 942     if (!$fh_item) {
 943         if ($args->{fhid}) {
 944             $fh_item = $self->new_instance_of("Firehose")->select(id => $args->{fhid});
 945         }
 946     }
 947
 948     if (!$fh_item) {
 949         $self->last_error("cannot get related firehose item");
 950         return;
 951     }
 952
 953     # get related item count
 954     my $dbh = $self->start_transaction;
 955     my $sql = "SELECT rel_stoid FROM related_stories WHERE stoid = ?";
 956     my $stoids = $dbh->do($sql, undef, $story->{stoid});
 957     $stoids = [] if ref($stoids) ne "ARRAY";
 958
 959     # insert
 960     my $count = @$stoids;
 961     my $rs = $self->related_stories->insert(stoid => $story->{stoid},
 962                                             rel_stoid => 0,
 963                                             rel_sid => 0,
 964                                             title => "Firehose: $fh_item->{title}",
 965                                             fhid => $fh_item->{id},
 966                                             ordernum => $count + 1);
 967
 968     if (!defined $rs) {
 969         $self->last_error("insert failed: " . $self->related_stories->last_error);
 970         $self->rollback;
 971         return;
 972     }
 973     $self->commit;
 974     return 1;
 975 }
 976
 977
 978 =head2 parameters($stoid)
 979
 980 get story parameters.
 981
 982 =over 4
 983
 984 =item Parameters
 985
 986 =over 4
 987
 988 =item $stoid
 989
 990 story id
 991
 992 =back
 993
 994 =item Return value
 995
 996 HASH of parameters
 997
 998 =back
 999
1000 =cut
1001
1002 sub parameters {
1003     my ($self, $stoid) = @_;
1004
1005     my $dbh = $self->connect_db;
1006
1007     my $sql = <<"EOSQL";
1008 SELECT * FROM story_param WHERE stoid = ?
1009 EOSQL
1010
1011     my $sth = $dbh->prepare($sql);
1012     $sth->execute($stoid);
1013     my $params = $sth->fetchall_hashref('name');
1014     $sth->finish;
1015
1016     # get next/prev story info
1017     if ($params->{next_stoid} || $params->{prev_stoid}) {
1018         $sql = <<"EOSQL";
1019 SELECT stories.*, story_text.* FROM stories JOIN story_text ON stories.stoid = story_text.stoid WHERE stories.stoid = ? OR stories.stoid = ?
1020 EOSQL
1021         $sth = $dbh->prepare($sql);
1022         my $next = $params->{next_stoid}->{value} || 0;
1023         my $prev = $params->{prev_stoid}->{value} || 0;
1024         $sth->execute($next, $prev);
1025         my $sids = $sth->fetchall_hashref('stoid');
1026         $params->{next_stoid}->{story} = $sids->{$next} if $sids->{$next};
1027         $params->{prev_stoid}->{story} = $sids->{$prev} if $sids->{$prev};
1028         $sth->finish;
1029     }
1030
1031     $self->disconnect_db();
1032     return $params;
1033 }
1034
1035 #========================================================================
1036
1037 =head2 set_dirty($key, $id)
1038
1039 set writestatus dirty for the story.
1040
1041 =over 4
1042
1043 =item Parameters
1044
1045 =over 4
1046
1047 =item $key
1048
1049 'stoid'
1050
1051 =item $id
1052
1053 id of the story
1054
1055 =back
1056
1057 =item Return value
1058
1059 1/0
1060
1061 =back
1062
1063 =cut
1064
1065 sub set_dirty {
1066     my ($self, $key, $id) = @_;
1067     return if $self->check_readonly;
1068
1069     my $stoid;
1070     if ($key eq 'stoid') {
1071         $stoid = $id;
1072     }
1073     else {
1074         return;
1075     }
1076
1077     my $dbh = $self->connect_db;
1078     my $sql = <<"EOSQL";
1079 INSERT INTO story_dirty (stoid) VALUES (?)
1080 EOSQL
1081
1082     my $rs = $dbh->do($sql, undef, $stoid);
1083     if (!$rs) {
1084         return;
1085     }
1086     return 1;
1087 }
1088
1089 sub _generalize {
1090     my ($self, $story, $params) = @_;
1091     $params ||= {};
1092
1093     # NTO-nized
1094     $story->{id} = $story->{stoid};
1095     $story->{story_id} = $story->{stoid};
1096     $story->{create_time} = $story->{time};
1097     $story->{update_time} = $story->{last_update};
1098
1099     for my $t (@{$story->{topics}}) {
1100         if ($t->{tid} && $t->{tid} == $story->{tid}) {
1101             $story->{primary_topic} = $t;
1102         }
1103     }
1104
1105     $story->{content_type} = "story";
1106     $story->{intro_text} = $story->{introtext};
1107     $story->{bodytext} ||= "";
1108     $story->{body_text} = $story->{bodytext};
1109     if ($story->{body_text}) {
1110         $story->{full_text} = join("\n", $story->{intro_text}, $story->{body_text});
1111     }
1112     else {
1113         $story->{full_text} = $story->{intro_text};
1114     }
1115     $story->{fulltext} = $story->{full_text};
1116
1117     $story->{discussion_id} = $story->{discussion};
1118
1119     # no public flag given, public is 'yes'
1120     $story->{public} = 'yes' if !$story->{public};
1121
1122 }
1123
1124 # delete story from database
1125 # this method is for test purpose only.
1126 sub hard_delete {
1127     my $self = shift;
1128     return if $self->check_readonly;
1129     my $params = {@_};
1130
1131     my $stoid = $params->{story_id};
1132     return if !$stoid;
1133
1134     my $error = 0;
1135     my $sql;
1136
1137     #my $dbh = $self->connect_db({AutoCommit => 0,});
1138     my $dbh = $self->start_transaction;
1139     for my $table (qw(stories story_param story_text story_topics_chosen story_topics_rendered)) {
1140         my $sql = "DELETE FROM $table WHERE stoid = ?";
1141         my $rs = $dbh->do($sql, undef, $stoid);
1142         if (!defined $rs || $rs == 0) {
1143             Mojo::Log->new->warn("DELETE FROM $table failed. stoid is $stoid.");
1144             $error = 1;
1145         }
1146     }
1147
1148     # delete from firehose
1149     my $firehose = $self->new_instance_of("Firehose");
1150     $firehose->hard_delete("story", $stoid);
1151
1152     $self->commit;
1153     return !$error;
1154
1155     # delete globjs
1156     # delete tags
1157
1158     return;
1159 }
1160
1161 1;