Committer: gprochaev
LJSUP-7052.U trunk/bin/upgrading/update-db-general.pl U trunk/bin/upgrading/update-db.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/htdocs/admin/browse/add_category.bml U trunk/htdocs/admin/browse/add_community.bml U trunk/htdocs/admin/browse/load_all_structure.bml U trunk/htdocs/admin/browse/manage_category.bml U trunk/htdocs/admin/browse/remove_category.bml U trunk/htdocs/admin/browse/remove_community.bml
Modified: trunk/bin/upgrading/update-db-general.pl =================================================================== --- trunk/bin/upgrading/update-db-general.pl 2010-11-23 04:41:29 UTC (rev 17749) +++ trunk/bin/upgrading/update-db-general.pl 2010-11-23 08:15:57 UTC (rev 17750) @@ -4328,6 +4328,9 @@ "ADD UNIQUE `url_path` (url_path, parentcatid, vert_id)"); } + if (column_null("category", "parentcatid") eq 'YES') { + do_alter("category", "ALTER TABLE category MODIFY parentcatid INT UNSIGNED NOT NULL"); + } }); Modified: trunk/bin/upgrading/update-db.pl =================================================================== --- trunk/bin/upgrading/update-db.pl 2010-11-23 04:41:29 UTC (rev 17749) +++ trunk/bin/upgrading/update-db.pl 2010-11-23 08:15:57 UTC (rev 17750) @@ -101,7 +101,7 @@ my %table_drop; # $table -> 1 my %table_status; # $table -> { SHOW TABLE STATUS ... row } my %post_create; # $table -> [ [ $action, $what ]* ] -my %coltype; # $table -> { $col -> $type } +my %coltype; # $table -> { $col -> [ $type, $null ] } my %indexname; # $table -> "INDEX"|"UNIQUE" . ":" . "col1-col2-col3" -> "PRIMARY" | index_name my @alters; my $dbh; @@ -968,7 +968,8 @@ while (my $row = $sth->fetchrow_hashref) { my $type = $row->{'Type'}; $type .= " $1" if $row->{'Extra'} =~ /(auto_increment)/i; - $coltype{$table}->{ $row->{'Field'} } = lc($type); + my $null = $row->{'Null'}; + $coltype{$table}->{ $row->{'Field'} } = [ lc($type), $null ]; } # current physical table properties @@ -1009,11 +1010,20 @@ { my ($table, $col) = @_; load_table_info($table) unless $coltype{$table}; - my $type = $coltype{$table}->{$col}; + my $type = $coltype{$table}->{$col}[0]; $type ||= ""; return $type; } +sub column_null +{ + my ($table, $col) = @_; + load_table_info($table) unless $coltype{$table}; + my $null = $coltype{$table}->{$col}[1]; + $null ||= ""; + return $null; +} + sub table_status { my ($table, $col) = @_; Modified: trunk/cgi-bin/LJ/Browse.pm =================================================================== --- trunk/cgi-bin/LJ/Browse.pm 2010-11-23 04:41:29 UTC (rev 17749) +++ trunk/cgi-bin/LJ/Browse.pm 2010-11-23 08:15:57 UTC (rev 17750) @@ -82,11 +82,11 @@ $parent = LJ::Browse->load_by_id($self->{parentcatid}); $parentcaturl = $parent->uri; } - my $existcat = LJ::Browse->load_by_url("/browse" . $parentcaturl . $self->{url_path}); + my $existcat = LJ::Browse->load_by_url("/browse" . $parentcaturl . $self->{url_path}, $vertical); croak("Category exists already") if $existcat; $dbh->do("INSERT INTO category SET url_path=?, pretty_name=?, parentcatid=?", - undef, $self->{url_path}, $self->{pretty_name}, $self->{parentcatid}); + undef, $self->{url_path}, $self->{pretty_name}, $self->{parentcatid} || 0); die $dbh->errstr if $dbh->err; my $catid = $dbh->{mysql_insertid}; @@ -211,11 +211,15 @@ my $uri = shift; my $full_uri = shift; my $parent = shift; + my $vertical = shift; return undef unless ($uri && $full_uri); $uri = "/" . $uri unless ($uri =~ /^\/.+/); $full_uri = "/" . $full_uri unless ($full_uri =~ /^\/.+/); + ## Add to memkey full_uri "vertical" if category is in a vertical + $full_uri = "/vertical" . $full_uri if $vertical; + my $c = $class->load_from_uri_cache($full_uri); return $c if $c; @@ -229,7 +233,8 @@ } # not in memcache; load from db - my $sth = $dbh->prepare("SELECT * FROM category WHERE url_path = ?" . $parent_check); + my $vertical_check = $vertical ? " AND vert_id = " . $vertical->vert_id : " AND vert_id = 0"; + my $sth = $dbh->prepare("SELECT * FROM category WHERE url_path = ?" . $parent_check . $vertical_check); $sth->execute($uri); die $dbh->errstr if $dbh->err; @@ -358,8 +363,9 @@ # given a valid URL for a category, returns the Category object associated with it # valid URLs can be the special URL defined in config or just /browse/categoryname/ sub load_by_url { - my $class = shift; - my $url = shift; + my $class = shift; + my $url = shift; + my $vertical = shift; $url =~ /^(?:$LJ::SITEROOT)?(\/.+)$/; my $path = $1; @@ -384,7 +390,9 @@ my $category; # check cache now for full URI - my $c = $class->load_from_uri_cache("/" . $p); + my $check_uri = "/" . $p; + $check_uri = "/vertical" . $check_uri if $vertical; + my $c = $class->load_from_uri_cache($check_uri); return $c if $c; if ($p) { @@ -393,7 +401,7 @@ my $partial_uri; foreach my $cat (@cats) { $partial_uri .= "/" . $cat; - $category = $class->load_by_uri($cat, $partial_uri, $parent_id); + $category = $class->load_by_uri($cat, $partial_uri, $parent_id, $vertical); return undef unless $category; $parent_id = $category->catid; } @@ -868,10 +876,23 @@ my $dbh = LJ::get_db_reader(); if (defined $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 $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}); + 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} + ); push @found_posts, @$post_ids if $post_ids; } @entries = @@ -879,7 +900,14 @@ grep { $_->{journalid} } ## remove SEO posts @found_posts; } else { - my $post_ids = $dbh->selectall_arrayref ("SELECT * FROM category_recent_posts WHERE journalid IN ($comm_list) AND is_deleted = 0 ORDER BY timecreate DESC LIMIT $limit", { Slice => {} }); + my $post_ids = $dbh->selectall_arrayref ( + "SELECT * FROM category_recent_posts + WHERE journalid IN ($comm_list) + AND is_deleted = 0 + ORDER BY timecreate DESC + LIMIT $limit", + { Slice => {} } + ); @entries = map { LJ::Entry->new ($_->{journalid}, jitemid => $_->{jitemid}) } @$post_ids; } return @entries; Modified: trunk/cgi-bin/LJ/Vertical.pm =================================================================== --- trunk/cgi-bin/LJ/Vertical.pm 2010-11-23 04:41:29 UTC (rev 17749) +++ trunk/cgi-bin/LJ/Vertical.pm 2010-11-23 08:15:57 UTC (rev 17750) @@ -354,8 +354,20 @@ 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 $comms_search = []; + if ($search) { + $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.'%' + ) || []; + } + ## Get subcategories my $cats = $self->get_categories( $args{'category'} ); @@ -410,7 +422,10 @@ my $where = $cat ? " AND parentcatid = " . $cat->catid : ""; - my $cats = $dbh->selectall_arrayref("SELECT * FROM category WHERE vert_id = ? $where", { Slice => {} }, $self->vert_id); + my $cats = $dbh->selectall_arrayref( + "SELECT * FROM category WHERE vert_id = ? $where", + { Slice => {} }, $self->vert_id + ); return $cats; } @@ -670,16 +685,37 @@ if (@$to_del_tags) { my @bind = map { '?' } @$to_del_tags; 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); + 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 $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) || []; + my $res = $dbh->selectall_arrayref( + "SELECT 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}); + 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); + 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; @@ -690,7 +726,13 @@ my %args = @_; my $dbh = LJ::get_db_writer(); - 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); + 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 : []; } @@ -702,7 +744,12 @@ my $is_random = $args{'is_random'}; my $dbh = LJ::get_db_writer(); - my $comms = $dbh->selectall_arrayref("SELECT * FROM vertical_comms WHERE vert_id = ?", { Slice => {} }, $self->vert_id); + my $comms = $dbh->selectall_arrayref( + "SELECT * + FROM vertical_comms + WHERE vert_id = ?", + { Slice => {} }, $self->vert_id + ); return [] unless $comms; my $max_num = scalar @$comms; my $count = $args{'count'}; Modified: trunk/cgi-bin/LJ/Widget/Browse.pm =================================================================== --- trunk/cgi-bin/LJ/Widget/Browse.pm 2010-11-23 04:41:29 UTC (rev 17749) +++ trunk/cgi-bin/LJ/Widget/Browse.pm 2010-11-23 08:15:57 UTC (rev 17750) @@ -100,7 +100,7 @@ $$windowtitle = $vertical ? $vertical->name : $class->ml('widget.browse.windowtitle'); - my $cat = LJ::Browse->load_by_url($uri); # Currently selected category + my $cat = LJ::Browse->load_by_url($uri, $vertical); # Currently selected category my @categories = sort { lc $a->display_name cmp lc $b->display_name } LJ::Browse->load_all($vertical); Modified: trunk/htdocs/admin/browse/add_category.bml =================================================================== --- trunk/htdocs/admin/browse/add_category.bml 2010-11-23 04:41:29 UTC (rev 17749) +++ trunk/htdocs/admin/browse/add_category.bml 2010-11-23 08:15:57 UTC (rev 17750) @@ -47,10 +47,10 @@ } # Has a valid parent category been chosen - my $pcategory = LJ::Browse->load_by_url("/browse" . $parentcaturl); + my $pcategory = LJ::Browse->load_by_url("/browse" . $parentcaturl, $vertical); # Does a category with the same URL path and parent already exist? - my $existcat = LJ::Browse->load_by_url("/browse" . $parentcaturl . $newcaturl); + my $existcat = LJ::Browse->load_by_url("/browse" . $parentcaturl . $newcaturl, $vertical); push @errors, "Category already exists" if $existcat; next if @errors; Modified: trunk/htdocs/admin/browse/add_community.bml =================================================================== --- trunk/htdocs/admin/browse/add_community.bml 2010-11-23 04:41:29 UTC (rev 17749) +++ trunk/htdocs/admin/browse/add_community.bml 2010-11-23 08:15:57 UTC (rev 17750) @@ -41,7 +41,7 @@ } # Has a valid category been chosen - my $category = LJ::Browse->load_by_url("/browse" . $caturl); + my $category = LJ::Browse->load_by_url("/browse" . $caturl, $vertical); push @errors, "Invalid category" unless $category; # Extract usernames and get userids Modified: trunk/htdocs/admin/browse/load_all_structure.bml =================================================================== --- trunk/htdocs/admin/browse/load_all_structure.bml 2010-11-23 04:41:29 UTC (rev 17749) +++ trunk/htdocs/admin/browse/load_all_structure.bml 2010-11-23 08:15:57 UTC (rev 17750) @@ -55,7 +55,7 @@ ## Category name & url my $cat_name = $columns[3]; my $cat_url = $columns[4]; - my $cat = LJ::Browse->load_by_url ("/browse" . $cat_url); + my $cat = LJ::Browse->load_by_url ("/browse" . $cat_url, $v); unless ($cat) { #unless (grep { $_->{pretty_name} eq $cat_name } @{$v->get_categories}) { @@ -70,7 +70,7 @@ my $subcat_url = $columns[6]; if ($subcat_name && $subcat_url) { - my $subcat = LJ::Browse->load_by_url ("/browse" . $cat->url_path . $subcat_url); + my $subcat = LJ::Browse->load_by_url ("/browse" . $cat->url_path . $subcat_url, $v); unless ($subcat) { # unless (grep { $_->{pretty_name} eq $subcat_name } @{$v->get_categories($cat)}) { $subcat = LJ::Browse->create ( pretty_name => $subcat_name, url_path => $subcat_url, parentcatid => $cat->catid, vertical => $cat->vertical ); Modified: trunk/htdocs/admin/browse/manage_category.bml =================================================================== --- trunk/htdocs/admin/browse/manage_category.bml 2010-11-23 04:41:29 UTC (rev 17749) +++ trunk/htdocs/admin/browse/manage_category.bml 2010-11-23 08:15:57 UTC (rev 17750) @@ -40,8 +40,8 @@ push @errors, "Invalid vertical" unless $vertical; # Has a valid category been chosen - my $category = LJ::Browse->load_by_url("/browse" . $cat_url); ## category already moved? - $category = LJ::Browse->load_by_url("/browse" . $vert_url . $cat_url) unless $category; ## no, find without vertical + my $category = LJ::Browse->load_by_url("/browse" . $cat_url, $vertical); ## category already moved? + $category = LJ::Browse->load_by_url("/browse" . $vert_url . $cat_url, $vertical) unless $category; ## no, find without vertical push @errors, "Invalid category" unless $category; next if @errors; Modified: trunk/htdocs/admin/browse/remove_category.bml =================================================================== --- trunk/htdocs/admin/browse/remove_category.bml 2010-11-23 04:41:29 UTC (rev 17749) +++ trunk/htdocs/admin/browse/remove_category.bml 2010-11-23 08:15:57 UTC (rev 17750) @@ -37,7 +37,7 @@ } # Has a valid category been chosen - my $category = LJ::Browse->load_by_url("/browse" . $caturl); + my $category = LJ::Browse->load_by_url("/browse" . $caturl, $vertical); push @errors, "Invalid category" unless $category; my @children = $category->children; Modified: trunk/htdocs/admin/browse/remove_community.bml =================================================================== --- trunk/htdocs/admin/browse/remove_community.bml 2010-11-23 04:41:29 UTC (rev 17749) +++ trunk/htdocs/admin/browse/remove_community.bml 2010-11-23 08:15:57 UTC (rev 17750) @@ -46,7 +46,7 @@ } # Has a valid category been chosen - my $category = LJ::Browse->load_by_url("/browse" . $caturl); + my $category = LJ::Browse->load_by_url("/browse" . $caturl, $vertical); push @errors, "Invalid category" unless $category; # Extract usernames and get userids @@ -113,7 +113,7 @@ # Selected a category OR tried a remove and got an error if ( LJ::did_post() && ($POST{'select'} || ($POST{'remove'} && @errors)) ) { # Has a valid category been chosen - my $category = LJ::Browse->load_by_url("/browse" . $caturl); + my $category = LJ::Browse->load_by_url("/browse" . $caturl, $vertical); push @errors, "Invalid category" unless $category; my @comms = $category->communities;