path: root/sql/modules/Session.sql

CREATE OR REPLACE FUNCTION form_close(in_session_id int, in_form_id int)
RETURNS BOOL AS
$$
BEGIN
    SELECT * FROM open_forms 
    WHERE session_id = in_session_id AND id = in_form_id;

    IF FOUND THEN 
        DELETE FROM open_forms 
        WHERE session_id = in_session_id AND id = in_form_id;

        RETURN TRUE;

    ELSE RETURN FALSE;
    END IF;
END;
$$ language plpgsql;

CREATE OR REPLACE FUNCTION form_open(in_session_id int)
RETURNS INT AS
$$
BEGIN
    INSERT INTO open_forms (session_id) VALUES (in_session_id);
    RETURN currval('form_id_seq');
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION session_check(in_session_id int, in_token text) 
RETURNS session AS
$$
DECLARE out_row session%ROWTYPE;
BEGIN
    DELETE FROM session
     WHERE last_used < now() - coalesce((SELECT value FROM defaults
                                    WHERE setting_key = 'timeout')::interval,
                                '90 minutes'::interval);
        UPDATE session 
           SET last_used = now()
         WHERE session_id = in_session_id
               AND token = in_token
               AND last_used > now() - (SELECT value FROM defaults
                WHERE setting_key = 'timeout')::interval
           AND users_id = (select id from users 
            where username = SESSION_USER);
    IF FOUND THEN
        SELECT * INTO out_row FROM session WHERE session_id = in_session_id;
    ELSE
        DELETE FROM SESSION 
        WHERE users_id IN (select id from users
                        where username = SESSION_USER); 
        -- the above query also releases all discretionary locks by the
                -- session

        IF NOT FOUND THEN
            PERFORM id FROM users WHERE username = SESSION_USER;
            IF NOT FOUND THEN
                RAISE EXCEPTION 'User Not Known';
            END IF;
            
        END IF;
        INSERT INTO session(users_id, token, last_used, transaction_id)
        SELECT id, md5(random()::text), now(), 0 
          FROM users WHERE username = SESSION_USER;
        -- TODO-- remove transaction_id field from session table

        SELECT * INTO out_row FROM session 
         WHERE session_id = currval('session_session_id_seq');
    END IF;
    RETURN out_row;
END;
$$ LANGUAGE PLPGSQL;

COMMENT ON FUNCTION session_check(int, text) IS 
$$ Return code is 0 for failure, 1 for success. $$;

CREATE OR REPLACE FUNCTION unlock_all() RETURNS BOOL AS
$$
BEGIN
    UPDATE transactions SET locked_by = NULL 
    where locked_by IN 
          (select session_id from session WHERE users_id = 
                  (SELECT id FROM users WHERE username = SESSION_USER));

    RETURN FOUND;
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION unlock(in_id int) RETURNS BOOL AS $$
BEGIN
    UPDATE transactions SET locked_by = NULL WHERE id = in_id 
           AND locked_by IN (SELECT session_id FROM session WHERE users_id =
        (SELECT id FROM users WHERE username = SESSION_USER));
    RETURN FOUND;
END;
$$ LANGUAGE PLPGSQL;