200 lines
8.2 KiB
PL/PgSQL
200 lines
8.2 KiB
PL/PgSQL
-- Users known by the application. "Nickname" is a misnomer. Nom de
|
|
-- user was too pretentious. It's how the customer wishes to be
|
|
-- addressed, but "address" would be confusing.
|
|
|
|
-- requires extensions citex, btree_gist
|
|
|
|
--DROP VIEW IF EXISTS staff_members;
|
|
--DROP VIEW IF EXISTS client_members;
|
|
--DROP VIEW IF EXISTS usernames;
|
|
|
|
DROP VIEW IF EXISTS staff_appointments;
|
|
DROP TABLE IF EXISTS appointments;
|
|
DROP TABLE IF EXISTS officehours;
|
|
DROP VIEW IF EXISTS staff_client_relationships;
|
|
DROP TABLE IF EXISTS relationship;
|
|
DROP VIEW IF EXISTS staff_members;
|
|
DROP TABLE IF EXISTS staff;
|
|
DROP VIEW IF EXISTS client_members;
|
|
DROP TABLE IF EXISTS clients;
|
|
DROP VIEW IF EXISTS usernames;
|
|
DROP TABLE IF EXISTS users CASCADE;
|
|
|
|
-- DROP VIEW IF EXISTS relationships;
|
|
|
|
CREATE TABLE users (
|
|
id SERIAL PRIMARY KEY UNIQUE,
|
|
email CITEXT UNIQUE NOT NULL,
|
|
nickname TEXT NOT NULL
|
|
);
|
|
|
|
-- Users who are currently staff
|
|
|
|
CREATE TABLE staff (
|
|
staff_id INTEGER UNIQUE REFERENCES users(id) ON DELETE CASCADE,
|
|
active BOOLEAN
|
|
);
|
|
|
|
-- Users who are currently clients
|
|
|
|
CREATE TABLE clients (
|
|
client_id INTEGER UNIQUE REFERENCES users(id) ON DELETE CASCADE,
|
|
active BOOLEAN
|
|
);
|
|
|
|
-- This is interesting, because we've basically created a M:1
|
|
-- relationship of staff and clients, but a 1:1 relationship of
|
|
-- clients to staff. That satisfies the current assignment, mostly.
|
|
-- An appointment can then be made my a client, and there's only one
|
|
-- staff person who it could apply to, so the query is straightforward
|
|
-- then.
|
|
--
|
|
-- If we wanted a client to have more than one coach, we'd have to
|
|
-- remove the UNIQUE constraint and let there be more than one
|
|
-- client-staff relationships.
|
|
|
|
CREATE TABLE relationship (
|
|
id SERIAL PRIMARY KEY UNIQUE,
|
|
client_id INTEGER UNIQUE NOT NULL REFERENCES clients(client_id) ON DELETE CASCADE,
|
|
staff_id INTEGER NOT NULL REFERENCES staff(staff_id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- Staff members may create office hours, but to prevent confusion we
|
|
-- should not allow a single staff-member's officehour entries to
|
|
-- overlap. The && is the "overlaps" (has points in common) operator
|
|
-- for ranges.
|
|
|
|
CREATE TABLE officehours (
|
|
id SERIAL PRIMARY KEY UNIQUE,
|
|
staff_id INTEGER NOT NULL REFERENCES staff(staff_id) ON DELETE CASCADE,
|
|
during TSRANGE,
|
|
EXCLUDE USING gist (staff_id WITH =, during WITH &&)
|
|
);
|
|
|
|
-- This one is tricky. We're basically saying that the client has an
|
|
-- appointment, and that's all we care about. BUT the INSERT must
|
|
-- find the staff member with whom this client has a relationship, and
|
|
-- assert that the staff member has office hours at that time, and
|
|
-- that no other client off that staff member has already chosen an
|
|
-- overlapping appointment.
|
|
|
|
CREATE TABLE appointments (
|
|
id SERIAL PRIMARY KEY UNIQUE,
|
|
during TSRANGE,
|
|
client_id INTEGER NOT NULL REFERENCES clients(client_id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE VIEW usernames AS SELECT id, nickname FROM users;
|
|
|
|
CREATE VIEW staff_members AS
|
|
(SELECT users.nickname AS staff_name, users.id AS staff_id
|
|
FROM users WHERE users.id IN (SELECT staff_id FROM staff WHERE active=true));
|
|
|
|
CREATE VIEW client_members AS
|
|
(SELECT users.nickname AS client_name, users.id AS client_id
|
|
FROM users WHERE users.id IN (SELECT client_id FROM clients WHERE active=true));
|
|
|
|
CREATE VIEW staff_client_relationships AS
|
|
(SELECT staff_name, staff_members.staff_id AS staff_id,
|
|
client_name, client_members.client_id AS client_id FROM relationship
|
|
INNER JOIN staff_members ON relationship.staff_id = staff_members.staff_id
|
|
INNER JOIN client_members ON relationship.client_id = client_members.client_id);
|
|
|
|
CREATE VIEW staff_appointments AS
|
|
(SELECT staff_name,
|
|
staff_client_relationships.staff_id AS staff_id,
|
|
client_name,
|
|
staff_client_relationships.client_id AS client_id,
|
|
appointments.id AS appointment_id,
|
|
during
|
|
FROM appointments INNER JOIN staff_client_relationships
|
|
ON appointments.client_id = staff_client_relationships.client_id);
|
|
|
|
DROP FUNCTION IF EXISTS add_appointment(INT, TSRANGE);
|
|
CREATE OR REPLACE FUNCTION add_appointment(q_client_id INT, q_timerange TSRANGE) RETURNS VOID as $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT officehours.id FROM officehours INNER JOIN relationship
|
|
ON relationship.staff_id = officehours.staff_id
|
|
WHERE relationship.client_id = q_client_id
|
|
AND (q_timerange <@ officehours.during)) THEN
|
|
RAISE EXCEPTION 'There are no office hours for your coach at that time.';
|
|
END IF;
|
|
IF EXISTS (SELECT appointments.id FROM appointments WHERE client_id IN (
|
|
SELECT client_id FROM relationship WHERE staff_id IN (
|
|
SELECT staff_id FROM relationship WHERE client_id = q_client_id))
|
|
AND (q_timerange && appointments.during)) THEN
|
|
RAISE EXCEPTION 'That appointment slot is already taken.';
|
|
END IF;
|
|
INSERT INTO appointments (client_id, during) SELECT q_client_id, q_timerange;
|
|
END
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Some basic testing starts below:
|
|
|
|
-- INSERT INTO users (nickname, email) VALUES ('Alice', 'alice@example.com');
|
|
-- INSERT INTO users (nickname, email) VALUES ('Bob', 'bob@example.com');
|
|
-- INSERT INTO users (nickname, email) VALUES ('Carol', 'carol@example.com');
|
|
-- INSERT INTO users (nickname, email) VALUES ('Doug', 'doug@example.com');
|
|
|
|
-- SELECT * from usernames;
|
|
|
|
-- INSERT INTO users (nickname, email) VALUES ('Ellen', 'ellen@example.com');
|
|
-- INSERT INTO users (nickname, email) VALUES ('Fred', 'fred@example.com');
|
|
|
|
-- SELECT * from usernames;
|
|
|
|
-- INSERT INTO staff (staff_id) SELECT id from users where email IN ('alice@example.com', 'bob@example.com');
|
|
-- INSERT INTO clients (client_id) SELECT id from users where email NOT IN ('alice@example.com', 'bob@example.com');
|
|
-- UPDATE staff SET active = True;
|
|
-- UPDATE clients SET active = True;
|
|
|
|
-- INSERT INTO relationship (staff_id, client_id) VALUES (1, 3);
|
|
-- INSERT INTO relationship (staff_id, client_id) VALUES (1, 4);
|
|
-- INSERT INTO relationship (staff_id, client_id) VALUES (2, 5);
|
|
-- INSERT INTO relationship (staff_id, client_id) VALUES (2, 6);
|
|
|
|
-- Note the plural:
|
|
|
|
-- WITH staff_members AS (SELECT users.nickname AS staff_name, users.id AS staff_id FROM users WHERE users.id IN (SELECT staff_id FROM staff WHERE active=true))
|
|
|
|
|
|
-- SELECT staff_name from staff_members;
|
|
-- SELECT client_name from client_members;
|
|
|
|
-- INSERT INTO officehours (staff_id, during) VALUES (1, '[2015-02-07 10:00, 2015-02-07 17:00)');
|
|
-- INSERT INTO officehours (staff_id, during) VALUES (2, '[2015-02-07 12:00, 2015-02-07 15:00)');
|
|
|
|
-- Insert an appointment with a client
|
|
-- WHERE the client's staffer has those office hours (DOES OVERLAP)
|
|
|
|
-- SELECT * FROM officehours INNER JOIN relationship
|
|
-- ON relationship.staff_id = officehours.staff_id
|
|
-- WHERE relationship.client_id = 3;
|
|
|
|
|
|
|
|
|
|
-- SELECT add_appointment(3, '[2015-02-07 10:00, 2015-02-07 10:30)');
|
|
-- SELECT add_appointment(4, '[2015-02-07 10:30, 2015-02-07 11:00)');
|
|
-- SELECT add_appointment(5, '[2015-02-07 13:00, 2015-02-07 13:30)');
|
|
-- SELECT add_appointment(6, '[2015-02-07 13:30, 2015-02-07 14:00)');
|
|
|
|
-- INSERT INTO appointments (client_id, during)
|
|
-- SELECT 3, '[2015-02-07 10:00, 2015-02-07 10:30)'
|
|
-- WHERE EXISTS (SELECT * FROM officehours INNER JOIN relationship
|
|
-- ON relationship.staff_id = officehours.staff_id
|
|
-- WHERE relationship.client_id = 3
|
|
-- AND (officehours.during && '[2015-02-07 10:00, 2015-02-07 10:30)'))
|
|
-- AND NOT EXISTS (SELECT * from appointments INNER JOIN
|
|
--
|
|
-- SELECT * FROM officehours INNER JOIN relationship
|
|
-- ON relationship.staff_id = officehours.staff_id
|
|
-- WHERE relationship.client_id = 3
|
|
-- AND (officehours.during && '[2015-02-07 10:00, 2015-02-07 10:30)');
|
|
--
|
|
|
|
-- SELECT client_id, during FROM appointments WHERE client_id IN (
|
|
-- SELECT client_id FROM relationship WHERE staff_id IN (
|
|
-- SELECT staff_id FROM relationship WHERE client_id = 3));
|
|
--
|