cpanratings.update

#1
CREATE TABLE `review_users` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `bitcard_id` char(40) default NULL,
  `username` varchar(128) default NULL,
  `name` varchar(128) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

create table reviews (
  id mediumint unsigned not null primary key auto_increment,
  user      mediumint unsigned not null,
  user_name varchar(255) not null,
  module    varchar(255) binary not null, 
  distribution varchar(255) binary not null, 
  version_reviewed varchar(255) not null,
  updated datetime not null,
  review text not null,
  rating_overall tinyint unsigned,
  rating_1       tinyint unsigned,
  rating_2       tinyint unsigned,
  rating_3       tinyint unsigned,
  rating_4       tinyint unsigned,
  KEY module_idx (module),
--  UNIQUE KEY (user_id, distribution, module),
  KEY user_id_idx (user),
  constraint foreign key (user) references review_users(id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

create table reviews_helpful (
  review    mediumint unsigned not null, 
  user      mediumint unsigned not null,
  helpful   enum('1','0') not null,
  primary key (review, user),
  constraint foreign key (review) references reviews(id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

#2
-- backup old table
create table reviews_old like reviews;
insert into reviews_old select * from reviews;
-- now data update script runs

#3
alter table review_users
  add content_suppressed tinyint unsigned not null default 0;

#4
alter table reviews
  add status enum ('active', 'deleted', 'blocked') default 'active' not null after distribution,
  add helpful_score tinyint not null default 1 after status; 

#5
alter table reviews drop user_name;

#6
alter table reviews_helpful modify helpful tinyint unsigned default 1;
update reviews_helpful set helpful = NULL where helpful=2;

#todo

# top reviewers:
#  select IF(u.name is not null, u.name,u.username), u.username  ,sum(helpful) as helpful
#  from reviews_helpful rh inner join reviews r USING(review_id)  inner join review_users u ON(u.id=r.user_id)
#  group by r.user_id order by helpful desc limit 20;

# helpfulfoo: 
#  select u.username,sum(helpful) as helpful
#   from reviews_helpful rh inner join review_users u ON(u.id=rh.user_id)
#   group by rh.user_id order by 2;