52 lines
1.6 KiB
MySQL
52 lines
1.6 KiB
MySQL
|
BEGIN;
|
||
|
|
|
||
|
|
UPDATE version SET minor = 2;
|
||
|
|
|
||
|
|
CREATE TABLE requestMetadata (
|
||
|
|
url varchar NOT NULL UNIQUE,
|
||
|
|
videoTitle varchar DEFAULT NULL,
|
||
|
|
PRIMARY KEY (url),
|
||
|
|
FOREIGN KEY (url) REFERENCES requests(url) ON DELETE CASCADE
|
||
|
|
);
|
||
|
|
|
||
|
|
DROP VIEW requests_vw;
|
||
|
|
CREATE VIEW requests_vw AS
|
||
|
|
SELECT url,COALESCE(videoTitle,url) AS title,displayName AS requester,imageUrl,state,score,reqTimestamp FROM requests
|
||
|
|
JOIN requestMetadata USING (url)
|
||
|
|
JOIN scores USING (url)
|
||
|
|
JOIN users ON requests.requester = users.userid
|
||
|
|
ORDER BY score DESC, reqTimestamp ASC;
|
||
|
|
|
||
|
|
DROP FUNCTION get_requests_voted;
|
||
|
|
CREATE FUNCTION get_requests_voted(votinguserid int)
|
||
|
|
RETURNS TABLE (
|
||
|
|
url varchar,
|
||
|
|
title varchar,
|
||
|
|
requester varchar,
|
||
|
|
imageUrl varchar,
|
||
|
|
state varchar,
|
||
|
|
score int,
|
||
|
|
reqTimestamp timestamptz,
|
||
|
|
voted bool
|
||
|
|
)
|
||
|
|
LANGUAGE SQL
|
||
|
|
AS $$
|
||
|
|
SELECT url,title,requester,imageUrl,state,score,reqTimestamp,
|
||
|
|
(CASE WHEN votes.userid IS NULL THEN FALSE ELSE TRUE END) AS voted
|
||
|
|
FROM requests_vw
|
||
|
|
LEFT JOIN votes ON (requests_vw.url = votes.requesturl AND votes.userid = votinguserid)
|
||
|
|
ORDER BY score DESC, reqTimestamp ASC;
|
||
|
|
$$;
|
||
|
|
|
||
|
|
CREATE OR REPLACE PROCEDURE add_request(url varchar,requester int)
|
||
|
|
LANGUAGE SQL
|
||
|
|
AS $$
|
||
|
|
INSERT INTO requests (url,requester) VALUES (url,requester);
|
||
|
|
INSERT INTO requestMetadata (url) VALUES (url);
|
||
|
|
INSERT INTO scores (url) VALUES (url);
|
||
|
|
INSERT INTO votes (requesturl,userid) VALUES (url,requester);
|
||
|
|
CALL update_scores();
|
||
|
|
$$;
|
||
|
|
|
||
|
|
COMMIT;
|