wvy8pqSM

1CREATE DATABASE  IF NOT EXISTS Classifier;
2
3CREATE TABLE IF NOT EXISTS words(
4	related_word VARCHAR(30) NOT NULL,
5	word VARCHAR(30) NOT NULL,
6	relatedness TINYINT(1) NOT NULL
7) CHARSET=utf8;
8
9CREATE TABLE IF NOT EXISTS frequency (
10	related_word VARCHAR(30) NOT NULL,
11	word VARCHAR(30) NOT NULL,
12	positive INT(11),
13	negative INT(11),
14	UNIQUE KEY (related_word,word)
15) CHARSET=utf8;
16
17CREATE TABLE IF NOT EXISTS info (
18	related_word VARCHAR(30) UNIQUE NOT NULL,
19	positive INT(11) NOT NULL,
20	negative INT(11) NOT NULL,
21	total INT(11) NOT NULL
22) CHARSET=utf8;
23
24DELIMINER//
25CREATE TRIGGER ins_word BEFORE INSERT ON words
26FOR EACH ROW
27BEGIN
28	DECLARE frequency_record TINYINT(1);
29	SET @frequency_record := (SELECT COUNT(*) FROM frequency WHERE related_word=NEW.related_word AND word=NEW.word);		
30	IF frequency_record = 1 THEN
31		IF NEW.relatedness = 1 THEN
32			UPDATE frequency SET positive=positive+1 WHERE related_word=NEW.related_word AND word=NEW.word;
33		ELSEIF NEW.relatedness = 0 THEN
34			UPDATE frequency SET negative=negative+1 WHERE related_word=NEW.related_word AND word=NEW.word;
35		END IF;
36		IF NEW.relatedness = 1 THEN
37			UPDATE info SET positive=positive+1, total=total+1 WHERE related_word=NEW.related_word;
38		ELSEIF NEW.relatedness = 0 THEN
39			UPDATE info SET negative=negative+1, total=total+1 WHERE related_word=NEW.related_word;
40		END IF;
41	ELSEIF frequency_record = 0 THEN
42		IF NEW.relatedness = 1 THEN
43			INSERT INTO frequency VALUES (NEW.related_word,NEW.word,1,0);
44			INSERT INTO info VALUES (NEW.related_word,1,0,1);
45		ELSEIF NEW.relatedness = 0 THEN
46			INSERT INTO frequency VALUES (NEW.related_word,NEW.word,0,1);
47			INSERT INTO info VALUES (NEW.related_word,0,1,1);
48		END IF;
49	END IF;
50END;
51DELIMINER;