Committer: gprochaev
LJSUP-7052. U trunk/bin/upgrading/update-db-general.pl U trunk/cgi-bin/LJ/Browse.pm U trunk/cgi-bin/LJ/Vertical.pm U trunk/cgi-bin/LJ/Widget/Browse.pm U trunk/cgi-bin/LJ/Widget/Search.pm U trunk/htdocs/admin/browse/load_all_structure.bml U trunk/htdocs/admin/browse/search_tags.bml
Modified: trunk/bin/upgrading/update-db-general.pl
===================================================================
--- trunk/bin/upgrading/update-db-general.pl 2010-11-22 11:41:04 UTC (rev 17742)
+++ trunk/bin/upgrading/update-db-general.pl 2010-11-23 02:27:20 UTC (rev 17743)
@@ -3154,6 +3154,18 @@
)
EOC
+register_tablecreate("vertical_keymap", <<'EOC');
+CREATE TABLE vertical_keymap (
+ journalid int(11) NOT NULL,
+ jitemid int(11) NOT NULL,
+ vert_id int(11) NOT NULL,
+ kw_id int(11) NOT NULL,
+ PRIMARY KEY (journalid,jitemid,vert_id,kw_id),
+ KEY kw_id (kw_id),
+ KEY vert_id (vert_id)
+)
+EOC
+
## --
## -- embedconten previews
## --
@@ -4308,6 +4320,12 @@
ADD remove_after INT NOT NULL");
}
+ unless (column_type("vertical2", "kw_id")) {
+ do_alter("ALTER TABLE vertical_keywords DROP PRIMARY KEY, DROP INDEX vert_id, DROP INDEX keyword, DROP journalid, DROP jitemid, DROP vert_id, DROP is_seo, ADD kw_id INT NOT NULL");
+ do_sql("TRUNCATE vertical_keywords");
+ do_alter("ALTER TABLE vertical_keywords ADD PRIMARY KEY(kw_id), ADD UNIQUE(keyword), MODIFY kw_id INT NOT NULL AUTO_INCREMENT");
+ }
+
});
register_tablecreate("eventrates", <<'EOC'); # clustered
Modified: trunk/cgi-bin/LJ/Browse.pm
===================================================================
--- trunk/cgi-bin/LJ/Browse.pm 2010-11-22 11:41:04 UTC (rev 17742)
+++ trunk/cgi-bin/LJ/Browse.pm 2010-11-23 02:27:20 UTC (rev 17743)
@@ -367,6 +367,14 @@
$path =~ s/tag\/.*$//; # remove search string
$path =~ s/\/index\.bml$//; # remove bml page
+ my $v = LJ::Vertical->load_by_url ($url);
+ if ($v) {
+ ## we are in vertical
+ my $v_path = $v->uri;
+ $path =~ s/$v_path//;
+ $path =~ s/vertical/browse/;
+ }
+
# 4 possibilities:
# /browse
# /browse/topcategory/
@@ -848,15 +856,19 @@
sub search_posts {
my $class = shift;
- my $comms = shift;
- my $limit = shift;
- my $search = shift;
+ my %args = @_;
+ my $comms = $args{'comms'};
+ my $limit = $args{'page_size'};
+ my $search = $args{'search_str'};
+ my $vertical = $args{'vertical'};
+
my @entries = ();
my $comm_list = join ",", @$comms;
my $dbh = LJ::get_db_reader();
if (defined $search) {
- my $posts = $dbh->selectall_arrayref ("SELECT journalid, jitemid FROM vertical_keywords WHERE keyword like ? AND is_seo = 0", { Slice => {} }, '%'.$search.'%');
+ my $where = $vertical ? " AND m.vert_id = " . $vertical->vert_id : "";
+ my $posts = $dbh->selectall_arrayref ("SELECT journalid, jitemid FROM vertical_keywords w, vertical_keymap m WHERE w.kw_id = m.kw_id $where AND keyword like ?", { Slice => {} }, '%'.$search.'%');
my @found_posts = ();
foreach my $post (@$posts) {
my $post_ids = $dbh->selectall_arrayref ("SELECT journalid, jitemid FROM category_recent_posts WHERE journalid IN ($comm_list) AND journalid = ? AND jitemid = ? AND is_deleted = 0 ORDER BY timecreate DESC LIMIT $limit", { Slice => {} }, $post->{journalid}, $post->{jitemid});
@@ -922,7 +934,6 @@
my $dbh = LJ::get_db_writer()
or die "unable to contact global db master to create category";
-
## Add community to category
$dbh->do("REPLACE INTO categoryjournals VALUES (?,?)", undef,
$self->catid, $uid);
Modified: trunk/cgi-bin/LJ/Vertical.pm
===================================================================
--- trunk/cgi-bin/LJ/Vertical.pm 2010-11-22 11:41:04 UTC (rev 17742)
+++ trunk/cgi-bin/LJ/Vertical.pm 2010-11-23 02:27:20 UTC (rev 17743)
@@ -350,8 +350,14 @@
my $self = shift;
my %args = @_;
- my $cats = $self->get_categories();
+ my $search = $args{'search'};
+ my $dbh = LJ::get_db_writer()
+ or die "unable to contact global db master to create vertical";
+ my $comms_search = $dbh->selectall_arrayref ("SELECT DISTINCT journalid FROM vertical_keywords w, vertical_keymap m WHERE m.jitemid <> 0 AND m.vert_id = ? AND w.kw_id = m.kw_id AND keyword like ?", { Slice => {} }, $self->vert_id, '%'.$search.'%') || [];
+
+ my $cats = $self->get_categories( $args{'category'} );
+
my $cusers = {};
foreach my $c (@$cats) {
my $cat = LJ::Browse->load_by_id ( $c->{catid} );
@@ -359,8 +365,10 @@
$cat->load_communities ( %args ) unless ($args{'is_need_child'} && $cat->{_loaded_journals});
my $comms = $cat->{communities};
- my $temp_users = LJ::load_userids(@$comms);
-
+ $comms = @$comms_search
+ ? [ grep { my $s_comm_id = $_->{journalid}; grep { $s_comm_id == $_ } @$comms } @$comms_search ]
+ : [ map { { journalid => $_ } } @$comms ];
+ my $temp_users = LJ::load_userids(map { $_->{journalid} } @$comms);
foreach my $userid (keys %$temp_users) {
$cusers->{$userid} = $temp_users->{$userid};
}
@@ -450,7 +458,7 @@
my $dbh = LJ::get_db_writer()
or die "unable to contact global db master to load vertical";
- my $sth = $dbh->prepare("SELECT * FROM vertical2");
+ my $sth = $dbh->prepare("SELECT * FROM vertical2 ORDER BY name");
$sth->execute;
die $dbh->errstr if $dbh->err;
@@ -615,11 +623,22 @@
# Loaders
#
+sub create_tag {
+ my $self = shift;
+ my $keyword = shift;
+
+ return undef unless $keyword;
+
+ my $dbh = LJ::get_db_writer();
+ my $res = $dbh->do("INSERT INTO vertical_keywords (keyword) values (?)", undef, $keyword);
+ my $kw_id = $dbh->selectrow_array("SELECT LAST_INSERT_ID()");
+ return $kw_id;
+}
+
sub save_tags {
my $self = shift;
my %args = @_;
- my $is_seo = $args{'is_seo'};
my $tags = $args{'tags'};
my $dbh = LJ::get_db_writer();
@@ -627,19 +646,21 @@
my $old_tags = $self->load_tags(%args);
if ($old_tags) {
my %new_tags = map { $_->{'tag'} => 1 } @$tags;
- my $to_del_tags = [ grep { !$new_tags{$_} } map { $_->{keyword} } @$old_tags ];
+ my $to_del_tags = [ grep { !$new_tags{$_->{keyword}} } @$old_tags ];
## Need to delete some tags?
if (@$to_del_tags) {
my @bind = map { '?' } @$to_del_tags;
- my @bind_vals = map { $_ } @$to_del_tags;
- my $del = $dbh->do("DELETE FROM vertical_keywords WHERE vert_id = ? AND keyword IN (".(join ",", @bind).") AND is_seo = ?", undef, $self->vert_id, @bind_vals, $is_seo);
+ my @bind_vals = map { $_->{kw_id} } @$to_del_tags;
+ my $del = $dbh->do("DELETE FROM vertical_keymap WHERE kw_id IN (".(join ",", @bind).")", undef, $self->vert_id, @bind_vals);
}
}
foreach my $tag (@$tags) {
- my $kw_id = $dbh->selectall_arrayref("SELECT * FROM vertical_keywords WHERE keyword = ? AND is_seo = ?", undef, $tag, $is_seo);
- next if @$kw_id;
- my $sth = $dbh->do("INSERT IGNORE INTO vertical_keywords (journalid, keyword, jitemid, vert_id, is_seo) VALUES (?, ?, ?, ?, ?)", undef , $tag->{journalid}, $tag->{tag}, $tag->{jitemid}, $self->vert_id, $is_seo);
+ my $res = $dbh->selectall_arrayref("SELECT journalid, keyword, jitemid, m.kw_id FROM vertical_keymap m, vertical_keywords w WHERE m.kw_id = w.kw_id AND w.keywords = ? AND m.vert_id = ?", undef, $tag, $self->vert_id) || [];
+ next if @$res;
+ my $kw_id = $dbh->selectrow_array("SELECT kw_id FROM vertical_keywords WHERE keyword = ?", undef, $tag->{tag});
+ $kw_id = $self->create_tag ($tag->{tag}) unless $kw_id;
+ my $sth = $dbh->do("INSERT IGNORE INTO vertical_keymap (journalid, kw_id, jitemid, vert_id) VALUES (?, ?, ?, ?)", undef , $tag->{journalid}, $kw_id, $tag->{jitemid}, $self->vert_id);
}
return 1;
@@ -649,11 +670,8 @@
my $self = shift;
my %args = @_;
- my $is_seo = $args{'is_seo'};
-
my $dbh = LJ::get_db_writer();
- my $where = $is_seo ? " AND is_seo = 1 " : "";
- my $tags = $dbh->selectall_arrayref("SELECT journalid, keyword, jitemid FROM vertical_keywords WHERE vert_id = ? $where", { Slice => {} }, $self->vert_id);
+ my $tags = $dbh->selectall_arrayref("SELECT journalid, keyword, jitemid, m.kw_id FROM vertical_keymap m, vertical_keywords w WHERE m.kw_id = w.kw_id AND m.vert_id = ?", { Slice => {} }, $self->vert_id);
return $tags ? $tags : [];
}
@@ -842,7 +860,7 @@
# now which of the objects to load did we get a memcache key for?
foreach my $obj (@to_load) {
- my $row = $memc->{"vert:$obj->{vert_id}"};
+ my $row = $memc->{"vert2:$obj->{vert_id}"};
next unless $row;
$obj->absorb_row($row);
Modified: trunk/cgi-bin/LJ/Widget/Browse.pm
===================================================================
--- trunk/cgi-bin/LJ/Widget/Browse.pm 2010-11-22 11:41:04 UTC (rev 17742)
+++ trunk/cgi-bin/LJ/Widget/Browse.pm 2010-11-23 02:27:20 UTC (rev 17743)
@@ -172,12 +172,9 @@
$search_str =~ s#/?\?.*##;
}
- if ($cat && $vertical) {
- @comms = $cat->communities( is_need_child => 1 );
+ if ($vertical) {
+ @comms = $vertical->get_communities( is_need_child => 1, category => $cat, search => $search_str );
$ad = LJ::get_ads({ location => 'bml.explore/vertical', vertical => $vertical->name, ljadwrapper => 1 });
- } elsif ($vertical) {
- @comms = $vertical->get_communities( is_need_child => 1 );
- $ad = LJ::get_ads({ location => 'bml.explore/vertical', vertical => $vertical->name, ljadwrapper => 1 });
} elsif ($cat) { # we're looking at a lower-level category
my @cat_title = split(/>/, $cat->title_html());
@@ -228,7 +225,7 @@
};
}
} else {
- my @posts = LJ::Browse->search_posts ( [ map { $_->{userid} } @comms ], $post_page_size, $search_str );
+ my @posts = LJ::Browse->search_posts ( comms => [ map { $_->{userid} } @comms ], page_size => $post_page_size, search_str => $search_str );
foreach my $entry (@posts) {
next unless $entry;
Modified: trunk/cgi-bin/LJ/Widget/Search.pm
===================================================================
--- trunk/cgi-bin/LJ/Widget/Search.pm 2010-11-22 11:41:04 UTC (rev 17742)
+++ trunk/cgi-bin/LJ/Widget/Search.pm 2010-11-23 02:27:20 UTC (rev 17743)
@@ -78,14 +78,24 @@
my $uri = BML::get_uri();
$uri =~ s#(.*?)tag/.*#$1#;
- my $vertical = LJ::Vertical->load_by_url ($uri);
+ my $vertical = $opts{'vertical'} || LJ::Vertical->load_by_url ($uri);
+ my $system = LJ::load_user('system') or die "No 'system' user in DB";
+ my $keywords = $system->prop('vertical_search');
+
+ my @words = split /\$/, $keywords;
+ @words = grep { my ($id) = $_ =~ m/(.*?)#/; $id == $vertical->vert_id } @words;
+ my ($stored_words) = $words[0] =~ m/.*?#(.*)/s;
+
$uri .= "/" if $uri !~ m#/$#; ## add end slash if not exist
+
+ my $args = BML::get_query_string();
+ $uri .= "?$args" if $args;
$template->param (
vertical_name => $vertical->name,
search_url => $uri,
view => $opts{'view'},
- tags => [ map { { tag => $_->{keyword} } } @{$vertical->load_tags (is_seo => 1)} ],
+ tags => [ map { { tag => $_ } } split /\n/m, $stored_words ],
);
return $template->output;
Modified: trunk/htdocs/admin/browse/load_all_structure.bml
===================================================================
--- trunk/htdocs/admin/browse/load_all_structure.bml 2010-11-22 11:41:04 UTC (rev 17742)
+++ trunk/htdocs/admin/browse/load_all_structure.bml 2010-11-23 02:27:20 UTC (rev 17743)
@@ -92,6 +92,7 @@
$subcat
? $subcat->add_community($comm_id, \@tags)
: $cat->add_community($comm_id, \@tags);
+
}
}
Modified: trunk/htdocs/admin/browse/search_tags.bml
===================================================================
--- trunk/htdocs/admin/browse/search_tags.bml 2010-11-22 11:41:04 UTC (rev 17742)
+++ trunk/htdocs/admin/browse/search_tags.bml 2010-11-23 02:27:20 UTC (rev 17743)
@@ -1,79 +1,111 @@
<?page
body<=
<?_code
-{
use strict;
- use vars qw(%GET %POST $title $headextra @errors @warnings);
use Class::Autouse qw( LJ::Browse );
+ use vars qw(%GET %POST $title $body);
- my $remote = LJ::get_remote();
+ my $remote = LJ::get_remote() or return "<?needlogin?>";
- return "<?needlogin?>"
- unless $remote;
+ # priv check
+ unless (LJ::check_priv($remote, 'siteadmin', 'community_directory') || $LJ::IS_DEV_SERVER) {
+ $body = "access denied";
+ return;
+ }
- return "You are not allowed to view this page"
- unless LJ::check_priv($remote, 'siteadmin', 'community_directory') || $LJ::IS_DEV_SERVER;
+ $title = "Search words in verticals";
- $title = "Recent Entries in verticals";
- my $ret = "";
-
my $vert_id = $POST{'vert_id'} ne '' ? $POST{'vert_id'} : undef;
- my $journals = $POST{'journals'};
- $ret .= "<form method='POST'>\n";
+ $body = '';
+
+ $body .= "<form method='POST'>\n";
my $form_auth = LJ::form_auth();
- $ret .= $form_auth;
+ $body .= $form_auth;
# Get the full list of categories
my @verticals = LJ::Vertical->load_all;
# Don't include the top level categories and get the unique URI for each
my @vert_ids = map { { value => $_->vert_id, text => $_->name } } @verticals;
- @vert_ids = sort { $a cmp $b } @vert_ids;
+# @vert_ids = sort { $a cmp $b } @vert_ids;
- $ret .= "<p>";
- $ret .= LJ::html_select({
+ $body .= "<p>";
+ $body .= LJ::html_select({
name => 'vert_id',
selected => $vert_id },
{ text => 'Select Vertical',
value => '' },
@vert_ids
);
- $ret .= " " . LJ::html_submit('go', 'Go') . "</p>";
+ $body .= " " . LJ::html_submit('go', 'Go') . "</p>";
+ $body .= "</form>";
- $ret .= "</form>";
-
my $vertical = undef;
- if (LJ::did_post() && $vert_id) {
- $vertical = LJ::Vertical->load_by_id($vert_id);
+ if (LJ::did_post() && $POST{'vert_id'}) {
+ $vertical = LJ::Vertical->load_by_id ($POST{'vert_id'});
}
- if (LJ::did_post() && $vertical && $POST{'save'}) {
- my $tags = $POST{'tags'};
- $vertical->save_tags( is_seo => 1, tags => [ map { { journalid => 0, jitemid => 0, tag => $_ } } split /\r\n/, $tags ] );
- }
-
if (LJ::did_post() && $vertical) {
- my $seo_tags = $vertical ? $vertical->load_tags ( is_seo => 1 ) : [];
- $seo_tags = join "\n", map { $_->{keyword} } @$seo_tags;
+ $body .= '<div style="float:left;"><form method="POST" action="">';
- $ret .= <<FORM;
-<form method="post">
-$form_auth
-<textarea cols="50" rows="20" name="tags">$seo_tags</textarea>
-<input type="hidden" name="vert_id" value="$vert_id">
-<input type="submit" name="save" value="Save search words">
-</form>
-FORM
+ my $system = LJ::load_user('system') or die "No 'system' user in DB";
+ my $keywords = $system->prop('vertical_search');
+
+ my @words = split /\$/, $keywords;
+ @words = grep { my ($id) = $_ =~ m/(.*?)#/; $id == $vert_id } @words;
+ my ($stored_words) = $words[0] =~ m/.*?#(.*)/s;
+ my $edited_words = $POST{words};
+
+ if ($POST{submit}) {
+ my @post_keywords = split /\s*\n+\s*/m, $POST{words};
+ $stored_words = join("\n", @post_keywords); # update this value, it will be used below
+ my $keywords = $system->prop('vertical_search');
+ my @words = split ":", $keywords;
+
+ my $flag = 0;
+ foreach my $v_w (@words) {
+ my ($id) = $v_w =~ m/(.*?)#/;
+ if ($id == $vert_id) {
+ $v_w = $id . "#" . $stored_words;
+ $flag = 1;
+ }
+ }
+ unless ($flag) {
+ push @words, $vert_id . "#" . $stored_words;
+ }
+
+ my $to_prop = join "\$", @words;
+ $system->set_prop('vertical_search', $to_prop);
+ }
+
+ my $words = $POST{preview} ? $edited_words : $stored_words;
+ $body .= LJ::html_textarea({ name => 'words', rows => 20, cols => 50, value => $words,
+ disabled => ($POST{edit} || $POST{preview}) ? 0 : 1 });
+
+ $body .= '<p>';
+ $body .= '<button type="submit" name="preview" value="preview">Preview widget</button> ' if $POST{edit} or $POST{preview};
+ if ($POST{edit} or $POST{preview}) {
+ $body .= '<button type="submit" name="cancel">Cancel editing</button>';
+ } else {
+ $body .= '<button type="submit" name="edit" value="edit">Edit search words</button>';
+ }
+ $body .= '</p><p><button type="submit" name="submit" value="save">Save search words</button></p>';
+ $body .= '<input type="hidden" name="vert_id" value="'.$vertical->vert_id.'">';
+ $body .= '</form>';
+ # preview
+ $body .= '</div><div style="float:right;width:650px;">';
+
+ $body .= LJ::Widget::Search->render(vertical => $vertical, type => "tags", view => 'communities');
+ $body .= '</div>';
}
+ return $body;
- return "<body>$ret</body>";
-}
_code?>
<=body
title=><?_code return $title; _code?>
head<=
-<?_code return $headextra; _code?>
-<=head
+<?_code return ''; _code?>
page?>
+