28 lines
1.1 KiB
PL/PgSQL
28 lines
1.1 KiB
PL/PgSQL
-- this was patched on the server without a proper migration, so the migration script here takes care to make sure it can run 2x
|
|
ALTER TABLE users DROP COLUMN IF EXISTS online;
|
|
DROP TRIGGER IF EXISTS connection_up_down ON connections;
|
|
|
|
ALTER TABLE users ADD COLUMN online BOOL DEFAULT FALSE NOT NULL;
|
|
|
|
CREATE OR REPLACE FUNCTION manage_user_online() RETURNS TRIGGER
|
|
STRICT VOLATILE AS $$
|
|
BEGIN
|
|
IF (TG_OP = 'DELETE') THEN
|
|
UPDATE users set ONLINE = (SELECT COUNT(conn.id) FROM connections conn WHERE conn.user_id = OLD.user_id) > 0 WHERE users.id = OLD.user_id;
|
|
RETURN NULL;
|
|
ELSIF (TG_OP = 'INSERT') THEN
|
|
UPDATE users set ONLINE = (SELECT COUNT(conn.id) FROM connections conn WHERE conn.user_id = NEW.user_id) > 0 WHERE users.id = NEW.user_id;
|
|
RETURN NULL;
|
|
END IF;
|
|
RETURN NULL;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
|
|
DELETE FROM connections;
|
|
|
|
CREATE TRIGGER connection_up_down AFTER INSERT OR DELETE
|
|
ON connections FOR EACH ROW EXECUTE PROCEDURE manage_user_online(user_id);
|
|
|
|
-- ONE time bootstrap the new online field
|
|
-- UPDATE users set online = (SELECT COUNT(conn.id) FROM connections conn WHERE conn.user_id = users.id) > 0; |