Good evening,
In a 9.5 database I would like players to rate each other and save the
reviews in the table:
CREATE TABLE words_reviews (
uid integer NOT NULL CHECK (uid <> author) REFERENCES words_users
ON DELETE CASCADE,
author integer NOT NULL REFERENCES words_users(uid) ON DELETE
CASCADE,
nice integer NOT NULL CHECK (nice = 0 OR nice = 1),
review varchar(255),
updated timestamptz NOT NULL,
PRIMARY KEY(uid, author)
);
while user names and IP addresses are saved in the other database:
CREATE TABLE words_users (
uid SERIAL PRIMARY KEY,
ip inet NOT NULL,
..........
);
However, before saving a review, I would like to delete all previous
reviews coming from the same IP in the past 24 hours:
CREATE OR REPLACE FUNCTION words_review_user(
in_uid integer, /* this user is being rated */
in_author integer, /* by the in_author user */
in_nice integer,
in_review varchar
) RETURNS void AS
$func$
DECLARE
_author_rep integer;
_author_ip integer;
BEGIN
/* find the current IP address of the author */
SELECT ip
INTO _author_ip
FROM words_users
WHERE uid = in_author;
/* try to prevent review fraud - how to improve this query please?
*/
DELETE FROM words_reviews
WHERE uid = in_uid
AND AGE(updated) < INTERVAL '1 day'
AND EXISTS (
SELECT 1
FROM words_reviews r INNER JOIN words_users u USING(uid)
WHERE u.ip = u._author_ip
AND r.author = in_author
);
UPDATE words_reviews set
author = in_author,
nice = in_nice,
review = in_review,
updated = CURRENT_TIMESTAMP
WHERE uid = in_uid AND author = in_author;
IF NOT FOUND THEN
INSERT INTO words_reviews (
author,
nice,
review,
updated
) VALUES (
in_author,
in_nice,
in_review,
CURRENT_TIMESTAMP
);
END IF;
END
$func$ LANGUAGE plpgsql;
I have the feeling that the _author_ip variable is not really necessary and
I could use some kind of "DELETE JOIN" here, but can not figure it out.
Please advise a better query if possible
Best regards
Alex
