Skip to content
Find file
Fetching contributors…
Cannot retrieve contributors at this time
88 lines (73 sloc) 2.66 KB
#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;
#7
drop table reviews_old;
#8
alter table reviews
drop FOREIGN KEY `reviews_ibfk_1`,
add CONSTRAINT `reviews_user` FOREIGN KEY (`user`) REFERENCES `review_users` (`id`)
on delete cascade;
#9
alter table reviews_helpful
drop foreign key `reviews_helpful_ibfk_1`,
add CONSTRAINT `reviews_helpful_review` FOREIGN KEY (`review`) REFERENCES `reviews` (`id`)
on delete cascade;
#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;
Jump to Line
Something went wrong with that request. Please try again.