-- Create tables used by the Z3 PostgreSQL session storage.
--
-- The PostgreSQL user that the session machinery connects as needs to be
-- granted the following permissions to the user the Zope 3 session machinery
-- is configured to connect as:
--   GRANT SELECT, INSERT, UPDATE, DELETE ON SessionData TO z3session;
--   GRANT SELECT, INSERT, UPDATE, DELETE oN SessionPkgData TO z3session;
--   GRANT SELECT ON Secret TO z3session;


CREATE TABLE SessionData (
    client_id     text PRIMARY KEY,
    created       timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_accessed timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) WITHOUT OIDS;
COMMENT ON TABLE SessionData IS 'Stores session tokens (the client_id) and the last accessed timestamp. The precision of the last access time is dependant on configuration in the Z3 application servers.';

CREATE INDEX sessiondata_last_accessed_idx ON SessionData(last_accessed);

CREATE TABLE SessionPkgData (
    client_id  text NOT NULL
        REFERENCES SessionData(client_id) ON DELETE CASCADE,
    product_id text NOT NULL,
    key        text NOT NULL,
    pickle     bytea NOT NULL,
    CONSTRAINT sessionpkgdata_pkey PRIMARY KEY (client_id, product_id, key)
    ) WITHOUT OIDS;
COMMENT ON TABLE SessionPkgData IS 'Stores the actual session data as a Python pickle.';

CREATE OR REPLACE FUNCTION ensure_session_client_id(p_client_id text)
RETURNS VOID AS $$
BEGIN
    INSERT INTO SessionData (client_id) VALUES (p_client_id);
EXCEPTION WHEN unique_violation THEN
    -- Do nothing
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION set_session_pkg_data(
    p_client_id text, p_product_id text, p_key text, p_pickle bytea
    ) RETURNS VOID AS $$
BEGIN
    -- Standard upsert loop to avoid race conditions
    LOOP
        -- Attempt an UPDATE first
        UPDATE SessionPkgData SET pickle = p_pickle
        WHERE client_id = p_client_id
            AND product_id = p_product_id
            AND key = p_key;
        IF found THEN
            RETURN;
        END IF;

        -- Next try an insert
        BEGIN
            INSERT INTO SessionPkgData (client_id, product_id, key, pickle)
            VALUES (p_client_id, p_product_id, p_key, p_pickle);
            RETURN;

        -- If the INSERT fails, another connection did the INSERT before us
        -- so ignore and try update again next loop.
        EXCEPTION WHEN unique_violation THEN
            -- Do nothing
        END;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

