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;
