56HWAaEf

1------------------------------------
2-- HelpDB SQL schema & test data. --
3------------------------------------
4
5-- Drop existing content.
6DROP TABLE IF EXISTS Quotes;
7DROP TABLE IF EXISTS Votes;
8DROP TRIGGER IF EXISTS Vote;
9DROP TRIGGER IF EXISTS Unvote;
10DROP TRIGGER IF EXISTS Remvote;
11
12-- Create both tables.
13CREATE TABLE Quotes (
14	Key   INTEGER PRIMARY KEY,
15	Body  TEXT NOT NULL,
16	Addr  TEXT NOT NULL,
17	Okay  INT  DEFAULT 0,
18	Score INT  DEFAULT 0,
19	Made  TEXT DEFAULT CURRENT_TIMESTAMP
20);
21
22CREATE TABLE Votes (
23	Key   INTEGER PRIMARY KEY,
24	Quote INT  NOT NULL,
25	Addr  TEXT NOT NULL,
26	Made  TEXT DEFAULT CURRENT_TIMESTAMP,
27	FOREIGN KEY (Quote) REFERENCES Quotes(Key)
28);
29
30-- Create triggers to handle voting logic.
31CREATE TRIGGER Vote AFTER INSERT ON Votes 
32	BEGIN UPDATE Quotes SET Score = Score + 1 WHERE Key = NEW.Quote; END;
33
34CREATE TRIGGER Unvote AFTER DELETE ON Votes
35	BEGIN UPDATE Quotes SET Score = Score - 1 WHERE Key = OLD.Quote; END;
36
37CREATE TRIGGER Remvote AFTER DELETE ON Quotes
38	BEGIN DELETE FROM Votes WHERE Quote = OLD.Key; END;
39
40-- Insert test data.
41INSERT INTO Quotes (Body, Addr, Okay) VALUES ('Know thyself.', 'test1', 1);
42INSERT INTO Quotes (Body, Addr, Okay) VALUES ('Be yourself.',  'test2', 1);
43INSERT INTO Quotes (Body, Addr, Okay) VALUES ('Things happen.','test3', 0);
44
45INSERT INTO Votes (Quote, Addr) VALUES (1, 'test1');
46INSERT INTO Votes (Quote, Addr) VALUES (1, 'test2');
47INSERT INTO Votes (Quote, Addr) VALUES (2, 'test1');