Update of /cvs/scoop/scoop/lib/Scoop/Stories
In directory lithium.sabren.com:/tmp/cvs-serv25165/lib/Scoop/Stories
Modified Files:
Elements.pm
Log Message:
Fix for get_sids and story_data for use with sites with archives. -j
Index: Elements.pm
===================================================================
RCS file: /cvs/scoop/scoop/lib/Scoop/Stories/Elements.pm,v
retrieving revision 1.126
retrieving revision 1.127
diff -C2 -d -r1.126 -r1.127
*** Elements.pm 6 Oct 2005 20:14:45 -0000 1.126
--- Elements.pm 15 Jan 2006 19:03:51 -0000 1.127
***************
*** 266,269 ****
--- 266,276 ----
my $archive = 0;
$archive = $S->_check_archivestatus($args->{-sid}) if exists($args->{'-sid'});
+ # need this for joining stories to users. if we're looking in the archive,
+ # the users table is in a different database, so we have to include the
+ # db
+ # name to access it
+ my $db_name;
+ $db_name = $S->{CONFIG}->{db_name} . ".users" if (lc($S->{CONFIG}->{DBType}) eq "mysql");
+ $db_name = "users" if (!(lc($S->{CONFIG}->{DBType}) eq "mysql"));
if ($type eq 'summaries') {
***************
*** 279,283 ****
my $maxstories = $S->pref('maxstories');
my $offset = (($page * $maxstories) - $maxstories) if $page;
! my $from = qq|stories s LEFT JOIN users u ON s.aid = u.uid|;
$from .= " $args->{-from}" if ($args->{-from});
--- 286,290 ----
my $maxstories = $S->pref('maxstories');
my $offset = (($page * $maxstories) - $maxstories) if $page;
! my $from = qq|stories s LEFT JOIN $db_name u ON s.aid = u.uid|;
$from .= " $args->{-from}" if ($args->{-from});
***************
*** 348,352 ****
ARCHIVE => $archive,
WHAT => qq|sid, tid, aid, u.nickname AS nick, title, dept, $date_format AS ftime, introtext, bodytext, section, displaystatus, time|,
! FROM => "stories s LEFT JOIN users u ON s.aid = u.uid",
WHERE => qq|sid = '$args->{-sid}' $displaystatus $excl_sect_sql_wAND|,
DEBUG => $DEBUG
--- 355,359 ----
ARCHIVE => $archive,
WHAT => qq|sid, tid, aid, u.nickname AS nick, title, dept, $date_format AS ftime, introtext, bodytext, section, displaystatus, time|,
! FROM => "stories s LEFT JOIN $db_name u ON s.aid = u.uid",
WHERE => qq|sid = '$args->{-sid}' $displaystatus $excl_sect_sql_wAND|,
DEBUG => $DEBUG
***************
*** 448,452 ****
DEBUG => $DEBUG,
WHAT => qq|s.sid as sid, tid, aid, u.nickname AS nick, title, dept, $date_format AS ftime, introtext, bodytext, section, displaystatus|,
! FROM => "(stories s LEFT OUTER JOIN viewed_stories v on s.sid = v.sid and v.uid = $S->{UID}) LEFT JOIN users u ON s.aid = u.uid",
WHERE => qq|(displaystatus >= 0) and (v.hide < 1 or v.hide is null) $sec_where|,
ORDER_BY => 'time desc',
--- 455,459 ----
DEBUG => $DEBUG,
WHAT => qq|s.sid as sid, tid, aid, u.nickname AS nick, title, dept, $date_format AS ftime, introtext, bodytext, section, displaystatus|,
! FROM => "(stories s LEFT OUTER JOIN viewed_stories v on s.sid = v.sid and v.uid = $S->{UID}) LEFT JOIN $db_name u ON s.aid = u.uid",
WHERE => qq|(displaystatus >= 0) and (v.hide < 1 or v.hide is null) $sec_where|,
ORDER_BY => 'time desc',
***************
*** 492,496 ****
DEBUG => $DEBUG,
WHAT => qq|sid, tid, aid, u.nickname AS nick, title, dept, $date_format AS ftime, introtext, bodytext, section, displaystatus|,
! FROM => "stories s LEFT JOIN users u ON s.aid = u.uid",
WHERE => qq|displaystatus >= 0 $sec_where|,
ORDER_BY => 'time desc',
--- 499,503 ----
DEBUG => $DEBUG,
WHAT => qq|sid, tid, aid, u.nickname AS nick, title, dept, $date_format AS ftime, introtext, bodytext, section, displaystatus|,
! FROM => "stories s LEFT JOIN $db_name u ON s.aid = u.uid",
WHERE => qq|displaystatus >= 0 $sec_where|,
ORDER_BY => 'time desc',
***************
*** 743,750 ****
}
# add WHERE info for tags
if ($params->{tag}) {
$where .= qq| AND t.tag = | . $S->dbh->quote($params->{'tag'});
! $from .= qq| LEFT JOIN story_tags t ON (s.sid = t.sid)|;
}
--- 750,758 ----
}
+ my $main_db = $S->{CONFIG}->{db_name};
# add WHERE info for tags
if ($params->{tag}) {
$where .= qq| AND t.tag = | . $S->dbh->quote($params->{'tag'});
! $from .= qq| LEFT JOIN ${main_db}.story_tags t ON (s.sid = t.sid)|;
}
***************
*** 780,789 ****
if ( ($fetched < $limit || $limit == 0) && $S->{HAVE_ARCHIVE} ) {
warn "(get_sids) didn't get all the stories... checking archive db" if $DEBUG;
!
# calculate the offset within the archive db
($rv,$sth) = $S->db_select({
DEBUG => $DEBUG,
! WHAT => 'count(sid)',
! FROM => 'stories',
WHERE => $where,
ORDER_BY => 's.time desc'
--- 788,804 ----
if ( ($fetched < $limit || $limit == 0) && $S->{HAVE_ARCHIVE} ) {
warn "(get_sids) didn't get all the stories... checking archive db" if $DEBUG;
! my $archfrom = "stories s";
! if ($params->{hotlisted}){
! $archfrom .= " LEFT JOIN ${main_db}.viewed_stories v ON
! (s.sid = v.sid AND v.uid = $q_uid)"
! }
! if ($params->{tag}){
! $archfrom .= " LEFT JOIN ${main_db}.story_tags t ON (s.sid = t.sid)";
! }
# calculate the offset within the archive db
($rv,$sth) = $S->db_select({
DEBUG => $DEBUG,
! WHAT => 'count(s.sid)',
! FROM => $archfrom,
WHERE => $where,
ORDER_BY => 's.time desc'
***************
*** 832,836 ****
warn "(story_data) starting..." if $DEBUG;
!
my $sids_to_fetch;
foreach (@$sids) {
--- 847,851 ----
warn "(story_data) starting..." if $DEBUG;
! my $main_db = $S->{CONFIG}->{db_name};
my $sids_to_fetch;
foreach (@$sids) {
***************
*** 845,853 ****
# build the SQL query for those stories not in the cache
my $date_format = $S->date_format('time');
!
my ($rv,$sth) = $S->db_select({
DEBUG => $DEBUG,
WHAT => "s.*,v.hotlisted,v.lastseen,v.highest_idx,u.nickname as nick,$date_format as ftime,count(c.cid) as comments",
! FROM => "stories s LEFT JOIN users u ON s.aid = u.uid LEFT JOIN viewed_stories v ON (s.sid = v.sid AND v.uid = $q_uid) LEFT JOIN comments c ON s.sid = c.sid",
WHERE => "s.sid IN ($sids_sql)",
GROUP_BY => 's.sid'
--- 860,868 ----
# build the SQL query for those stories not in the cache
my $date_format = $S->date_format('time');
!
my ($rv,$sth) = $S->db_select({
DEBUG => $DEBUG,
WHAT => "s.*,v.hotlisted,v.lastseen,v.highest_idx,u.nickname as nick,$date_format as ftime,count(c.cid) as comments",
! FROM => "stories s LEFT JOIN ${main_db}.users u ON s.aid = u.uid LEFT JOIN ${main_db}.viewed_stories v ON (s.sid = v.sid AND v.uid = $q_uid) LEFT JOIN comments c ON s.sid = c.sid",
WHERE => "s.sid IN ($sids_sql)",
GROUP_BY => 's.sid'
***************
*** 863,867 ****
$sids_to_fetch = [];
foreach (@$sids) {
! push @$sids_to_fetch,$_ unless ( grep { /^$_$/ } (keys %{$S->{STORY_CACHE}}) );
# checking to see if we got them all - if not, we look in the archive
}
--- 878,884 ----
$sids_to_fetch = [];
foreach (@$sids) {
! # for some reason, the way that's commented out doesn't seem
! # to be working.
! push @$sids_to_fetch,$_ unless ($S->{STORY_CACHE}->{$_}); #unless ( grep { /^$_$/ } (keys %{$S->{STORY_CACHE}}) );
# checking to see if we got them all - if not, we look in the archive
}
***************
*** 873,885 ****
# build the SQL query for those stories not in the cache
my $date_format = $S->date_format('time');
!
! my ($rv,$sth) = $S->db_select({
! DEBUG => $DEBUG,
! ARCHIVE => 1,
! WHAT => "s.*,v.hotlisted,v.lastseen,v.highest_idx,u.nickname as nick,$date_format as ftime,count(c.cid) as comments",
! FROM => "stories s LEFT JOIN users u ON s.aid = u.uid LEFT JOIN viewed_stories v ON (s.sid = v.sid AND v.uid = $q_uid) LEFT JOIN comments c ON s.sid = c.sid",
! WHERE => "s.sid IN ($sids_sql)",
! GROUP_BY => 's.sid'
! });
while ( my $story = $sth->fetchrow_hashref() ) {
--- 890,902 ----
# build the SQL query for those stories not in the cache
my $date_format = $S->date_format('time');
! my $db_name = $S->{CONFIG}->{db_name} . ".users";
! my ($rv,$sth) = $S->db_select({
! DEBUG => $DEBUG,
! ARCHIVE => 1,
! WHAT => "s.*,v.hotlisted,v.lastseen,v.highest_idx,u.nickname as nick,$date_format as ftime,count(c.cid) as comments",
! FROM => "stories s LEFT JOIN $db_name u ON s.aid = u.uid LEFT JOIN ${main_db}.viewed_stories v ON (s.sid = v.sid AND v.uid = $q_uid) LEFT JOIN comments c ON s.sid = c.sid",
! WHERE => "s.sid IN ($sids_sql)",
! GROUP_BY => 's.sid'
! });
while ( my $story = $sth->fetchrow_hashref() ) {