| Version 1 (modified by , 12 days ago) ( diff ) |
|---|
Напредно програмирање на базата на податоци
Сите објекти се во скриптата advanced_db.sql. Редослед на извршување: schema_creation.sql, advanced_db.sql, data_load.sql. Скриптата може да се извршува повеќепати. Скриптата data_load.sql привремено ги исклучува тригерите, бидејќи историските податоци се внесуваат директно во нивната конечна состојба, а по внесот повторно ги вклучува.
Сите правила се тестирани: секое недозволено дејство базата го одбива со порака на македонски јазик, а прототипот од фазата P4 работи без измени со вклучени тригери.
Животен циклус на пријавата и конзистентност на статусот
Пријавата мора да минува низ точно определени статуси: поднесена → примена → во тек → решена, а од секој отворен статус може да биде одбиена. Решена или одбиена пријава е затворена и статусот не може повеќе да се менува. Првиот запис во историјата секогаш е „поднесена“ и го креира системот. Секоја следна промена ја прави работник, и не може да биде со време пред претходната промена.
Статусот се чува на две места: во reports (моменталниот статус, за брзо филтрирање) и во status_logs (целата историја). Во фазата P5 беше утврдено дека ова е намерна редундантност. Затоа базата мора да гарантира дека моменталниот статус секогаш е еднаков на последниот запис во историјата, без оглед на тоа дали апликацијата прво го менува статусот или прво внесува запис во историјата.
Имплементација
Домен:
CREATE DOMAIN report_status AS VARCHAR(20)
CHECK (VALUE IN ('submitted', 'received', 'in_progress', 'resolved', 'rejected'));
Функција за дозволените премини:
CREATE OR REPLACE FUNCTION status_transition_allowed(p_from TEXT, p_to TEXT)
RETURNS BOOLEAN LANGUAGE sql IMMUTABLE AS $$
SELECT (p_from, p_to) IN (('submitted', 'received'),
('submitted', 'rejected'),
('received', 'in_progress'),
('received', 'rejected'),
('in_progress', 'resolved'),
('in_progress', 'rejected'));
$$;
Тригери:
- reports_before_insert - новата пријава мора да има статус submitted.
- reports_before_status_update - директна промена на статусот во reports мора да е дозволен премин.
- status_logs_before_insert - првиот запис мора да биде submitted без работник; секој следен мора да е дозволен премин, да не е постар од претходниот и да го направи доделен работник.
- status_logs_after_insert - по секој нов запис во историјата, статусот во reports автоматски се усогласува.
- reports_status_consistency - одложен (DEFERRABLE INITIALLY DEFERRED) тригер-ограничување кој на крајот на трансакцијата проверува дали статусот во reports е еднаков на последниот запис во историјата. Бидејќи е одложен, апликацијата може во иста трансакција прво да го смени статусот, а потоа да внесе запис, или обратно.
CREATE OR REPLACE FUNCTION trg_status_logs_before_insert()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
v_last status_logs%ROWTYPE;
BEGIN
SELECT * INTO v_last
FROM status_logs
WHERE report_id = NEW.report_id
ORDER BY changed_at DESC, log_id DESC
LIMIT 1;
IF NOT FOUND THEN
IF NEW.status <> 'submitted' OR NEW.worker_id IS NOT NULL THEN
RAISE EXCEPTION 'Првиот запис за пријавата % мора да биде submitted, без работник',
NEW.report_id;
END IF;
RETURN NEW;
END IF;
IF NOT status_transition_allowed(v_last.status, NEW.status) THEN
RAISE EXCEPTION 'Недозволена промена на статусот на пријавата % од % во %',
NEW.report_id, v_last.status, NEW.status;
END IF;
IF NEW.changed_at < v_last.changed_at THEN
RAISE EXCEPTION 'Промената не може да биде постара од претходната (%)', v_last.changed_at;
END IF;
IF NEW.worker_id IS NULL THEN
RAISE EXCEPTION 'Промената на статусот мора да ја направи работник';
END IF;
IF NOT EXISTS (SELECT 1 FROM assignments a
WHERE a.report_id = NEW.report_id AND a.worker_id = NEW.worker_id) THEN
RAISE EXCEPTION 'Работникот % не е доделен на пријавата %', NEW.worker_id, NEW.report_id;
END IF;
RETURN NEW;
END $$;
CREATE TRIGGER status_logs_before_insert
BEFORE INSERT ON status_logs
FOR EACH ROW EXECUTE FUNCTION trg_status_logs_before_insert();
CREATE OR REPLACE FUNCTION trg_status_logs_after_insert()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
UPDATE reports
SET status = NEW.status
WHERE report_id = NEW.report_id
AND status IS DISTINCT FROM NEW.status;
RETURN NULL;
END $$;
CREATE TRIGGER status_logs_after_insert
AFTER INSERT ON status_logs
FOR EACH ROW EXECUTE FUNCTION trg_status_logs_after_insert();
CREATE OR REPLACE FUNCTION trg_reports_status_consistency()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
v_current TEXT;
v_logged TEXT;
BEGIN
SELECT status INTO v_current FROM reports WHERE report_id = NEW.report_id;
IF NOT FOUND THEN
RETURN NULL;
END IF;
SELECT status INTO v_logged
FROM status_logs
WHERE report_id = NEW.report_id
ORDER BY changed_at DESC, log_id DESC
LIMIT 1;
IF v_logged IS DISTINCT FROM v_current THEN
RAISE EXCEPTION 'Статусот на пријавата % (%) не одговара на историјата (%)',
NEW.report_id, v_current, COALESCE(v_logged, 'нема запис');
END IF;
RETURN NULL;
END $$;
CREATE CONSTRAINT TRIGGER reports_status_consistency
AFTER INSERT OR UPDATE OF status ON reports
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE FUNCTION trg_reports_status_consistency();
Складирана процедура: промената на статусот од апликацијата се сведува на еден повик, а тригерите ги проверуваат правилата и го усогласуваат статусот:
CREATE OR REPLACE PROCEDURE change_report_status(p_report_id INTEGER,
p_worker_id INTEGER,
p_new_status TEXT,
p_note TEXT DEFAULT NULL)
LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO status_logs (status, note, report_id, worker_id)
VALUES (p_new_status, p_note, p_report_id, p_worker_id);
END $$;
CALL change_report_status(4, 4, 'resolved', 'Дефектот е отстранет.');
Пример: CALL change_report_status(10, 4, 'resolved', 'test'); за пријава со статус submitted базата ја одбива со порака „Недозволена промена на статусот на пријавата 10 од submitted во resolved“. Промена само на reports.status без запис во историјата се одбива при потврдување на трансакцијата.
Непроменлива историја на статусите
Историјата на статусите е доказ за транспарентноста и одговорноста на општината кон граѓаните. Затоа веќе внесените записи не смеат да се менуваат ниту бришат, ниту од апликацијата ниту директно во базата. Единствен исклучок е бришење на целата пријава, кога нејзината историја се брише каскадно.
Имплементација
Тригер:
CREATE OR REPLACE FUNCTION trg_status_logs_immutable()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
IF TG_OP = 'DELETE'
AND NOT EXISTS (SELECT 1 FROM reports WHERE report_id = OLD.report_id) THEN
RETURN OLD; -- каскадно бришење: пријавата веќе е избришана
END IF;
RAISE EXCEPTION 'Историјата на статусите не може да се менува ни брише';
END $$;
CREATE TRIGGER status_logs_immutable
BEFORE UPDATE OR DELETE ON status_logs
FOR EACH ROW EXECUTE FUNCTION trg_status_logs_immutable();
Овластувања за дејствијата на работниците и администраторите
Работник може да го менува статусот и да пишува коментари само за пријави на кои е доделен. Затворена пријава (решена или одбиена) не прима нови коментари и не може да се доделува. Доделувањето не може да има време пред поднесувањето на пријавата. Овие правила зависат од податоци во повеќе табели, па не можат да се изразат со обични CHECK ограничувања или надворешни клучеви.
Имплементација
Тригери: проверката за доделен работник при промена на статусот е во status_logs_before_insert (погоре). Дополнително:
CREATE OR REPLACE FUNCTION trg_assignments_before_insert()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
v_report reports%ROWTYPE;
BEGIN
SELECT * INTO v_report FROM reports WHERE report_id = NEW.report_id;
IF v_report.status IN ('resolved', 'rejected') THEN
RAISE EXCEPTION 'Пријавата % е затворена (%) и не може да се доделува',
NEW.report_id, v_report.status;
END IF;
IF NEW.assigned_at < v_report.created_at THEN
RAISE EXCEPTION 'Доделувањето не може да биде пред поднесувањето на пријавата';
END IF;
RETURN NEW;
END $$;
CREATE TRIGGER assignments_before_insert
BEFORE INSERT ON assignments
FOR EACH ROW EXECUTE FUNCTION trg_assignments_before_insert();
CREATE OR REPLACE FUNCTION trg_comments_before_insert()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
v_status TEXT;
BEGIN
IF NOT EXISTS (SELECT 1 FROM assignments a
WHERE a.report_id = NEW.report_id AND a.worker_id = NEW.worker_id) THEN
RAISE EXCEPTION 'Работникот % не е доделен на пријавата %', NEW.worker_id, NEW.report_id;
END IF;
SELECT status INTO v_status FROM reports WHERE report_id = NEW.report_id;
IF v_status IN ('resolved', 'rejected') THEN
RAISE EXCEPTION 'Пријавата % е затворена и не прима нови коментари', NEW.report_id;
END IF;
RETURN NEW;
END $$;
CREATE TRIGGER comments_before_insert
BEFORE INSERT ON comments
FOR EACH ROW EXECUTE FUNCTION trg_comments_before_insert();
Откривање на слични пријави и автоматски приоритет
Кога повеќе граѓани независно пријавуваат ист проблем на иста локација, тоа е знак дека проблемот е сериозен. Ако при поднесување на нова пријава во радиус од 150 метри веќе постојат барем 2 активни пријави од истата категорија, приоритетот на новата пријава автоматски се крева на најмалку „висок“. Истата функција за пребарување ја користи и апликацијата, за да му ги прикаже на граѓанинот сличните пријави пред поднесување. Растојанието се пресметува точно во метри, со формулата на Haversine. Поднесувањето на пријава со фотографии и првиот запис во историјата е изведено во една складирана функција.
Имплементација
Функции:
CREATE OR REPLACE FUNCTION distance_m(lat1 NUMERIC, lon1 NUMERIC, lat2 NUMERIC, lon2 NUMERIC)
RETURNS DOUBLE PRECISION LANGUAGE sql IMMUTABLE AS $$
SELECT 2 * 6371000 * asin(sqrt(
power(sin(radians(lat2 - lat1) / 2), 2)
+ cos(radians(lat1)) * cos(radians(lat2))
* power(sin(radians(lon2 - lon1) / 2), 2)));
$$;
CREATE OR REPLACE FUNCTION find_similar_reports(p_category_id INTEGER,
p_latitude NUMERIC,
p_longitude NUMERIC,
p_radius_m DOUBLE PRECISION DEFAULT 150,
p_exclude_report_id INTEGER DEFAULT NULL)
RETURNS TABLE (report_id INTEGER, description TEXT, status TEXT,
created_at TIMESTAMP, distance_m DOUBLE PRECISION)
LANGUAGE sql STABLE AS $$
SELECT r.report_id, r.description, r.status::TEXT, r.created_at,
ROUND(distance_m(p_latitude, p_longitude, r.latitude, r.longitude)::NUMERIC, 1)
FROM project.reports r
WHERE r.category_id = p_category_id
AND r.status NOT IN ('resolved', 'rejected')
AND r.latitude IS NOT NULL
AND r.report_id IS DISTINCT FROM p_exclude_report_id
AND distance_m(p_latitude, p_longitude, r.latitude, r.longitude) <= p_radius_m
ORDER BY 5;
$$;
CREATE OR REPLACE FUNCTION priority_rank(p TEXT)
RETURNS INTEGER LANGUAGE sql IMMUTABLE AS $$
SELECT CASE p WHEN 'low' THEN 1 WHEN 'medium' THEN 2
WHEN 'high' THEN 3 WHEN 'urgent' THEN 4 END;
$$;
Тригер:
CREATE OR REPLACE FUNCTION trg_reports_before_insert()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
v_similar INTEGER;
BEGIN
IF NEW.status IS DISTINCT FROM 'submitted' THEN
RAISE EXCEPTION 'Новата пријава мора да има статус submitted (добиено: %)', NEW.status;
END IF;
IF NEW.latitude IS NOT NULL THEN
SELECT COUNT(*) INTO v_similar
FROM find_similar_reports(NEW.category_id, NEW.latitude, NEW.longitude, 150);
IF v_similar >= 2 AND priority_rank(NEW.priority) < priority_rank('high') THEN
NEW.priority := 'high';
END IF;
END IF;
RETURN NEW;
END $$;
CREATE TRIGGER reports_before_insert
BEFORE INSERT ON reports
FOR EACH ROW EXECUTE FUNCTION trg_reports_before_insert();
Складирана функција за поднесување:
CREATE OR REPLACE FUNCTION submit_report(p_citizen_id INTEGER,
p_category_id INTEGER,
p_description TEXT,
p_location_text TEXT,
p_latitude NUMERIC,
p_longitude NUMERIC,
p_photos TEXT[] DEFAULT '{}')
RETURNS INTEGER LANGUAGE plpgsql AS $$
DECLARE
v_report_id INTEGER;
v_created_at TIMESTAMP;
BEGIN
INSERT INTO reports (description, location_text, latitude, longitude, category_id, citizen_id)
VALUES (p_description, p_location_text, p_latitude, p_longitude, p_category_id, p_citizen_id)
RETURNING report_id, created_at INTO v_report_id, v_created_at;
INSERT INTO status_logs (status, changed_at, report_id, worker_id)
VALUES ('submitted', v_created_at, v_report_id, NULL);
INSERT INTO photos (image_url, report_id)
SELECT '/uploads/reports/' || v_report_id || '/' || f, v_report_id
FROM unnest(p_photos) AS f;
RETURN v_report_id;
END $$;
Пример: при тестирањето, три пријави за дупка на коловоз во радиус од 25 метри на ул. Македонија се поднесени со submit_report. Првите две добија приоритет „низок“, а третата автоматски доби „висок“, бидејќи во близина веќе имаше две активни пријави.
Рокови за реакција и позадинска ескалација на приоритетот
Секој приоритет има рок за реакција (SLA): итен 1 ден, висок 3 дена, среден 7 дена, низок 14 дена. Пријава која не е затворена и чиј статус не се променил подолго од рокот се смета за задоцнета. Покрај тоа, пријавите кои долго чекаат не смеат да останат со низок приоритет: секој ден автоматски се подига приоритетот според деновите од последната промена на статусот (3 или повеќе дена - најмалку среден, 7 или повеќе - најмалку висок, 14 или повеќе - итен). Приоритетот никогаш не се намалува автоматски, па повторното извршување не менува ништо.
Имплементација
Функции:
CREATE OR REPLACE FUNCTION sla_days(p TEXT)
RETURNS INTEGER LANGUAGE sql IMMUTABLE AS $$
SELECT CASE p WHEN 'urgent' THEN 1 WHEN 'high' THEN 3
WHEN 'medium' THEN 7 ELSE 14 END;
$$;
CREATE OR REPLACE FUNCTION priority_from_rank(r INTEGER)
RETURNS TEXT LANGUAGE sql IMMUTABLE AS $$
SELECT (ARRAY['low', 'medium', 'high', 'urgent'])[GREATEST(1, LEAST(4, r))];
$$;
CREATE OR REPLACE FUNCTION escalate_overdue_reports()
RETURNS TABLE (report_id INTEGER, old_priority TEXT, new_priority TEXT, days_waiting INTEGER)
LANGUAGE plpgsql AS $$
BEGIN
RETURN QUERY
WITH waiting AS (
SELECT r.report_id, r.priority::TEXT AS priority,
EXTRACT(DAY FROM now()::TIMESTAMP - MAX(l.changed_at))::INTEGER AS days_waiting
FROM project.reports r
JOIN project.status_logs l ON l.report_id = r.report_id
WHERE r.status NOT IN ('resolved', 'rejected')
GROUP BY r.report_id
),
target AS (
SELECT w.report_id, w.priority, w.days_waiting,
priority_from_rank(GREATEST(
priority_rank(w.priority),
CASE WHEN w.days_waiting >= 14 THEN 4
WHEN w.days_waiting >= 7 THEN 3
WHEN w.days_waiting >= 3 THEN 2
ELSE 1 END)) AS new_priority
FROM waiting w
),
updated AS (
UPDATE project.reports r
SET priority = t.new_priority
FROM target t
WHERE r.report_id = t.report_id
AND t.new_priority <> t.priority
RETURNING r.report_id, t.priority, t.new_priority, t.days_waiting
)
SELECT * FROM updated ORDER BY 1;
END $$;
Позадинска задача: секој ден во 06:00. Скриптата ја закажува задачата со екстензијата pg_cron, ако е инсталирана на серверот. Ако не е, задачата се закажува однадвор (на пример со cron на серверот) со командата psql -c "SELECT * FROM project.escalate_overdue_reports();".
DO $$
BEGIN
IF EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pg_cron') THEN
PERFORM cron.schedule('cityfix-escalate-overdue', '0 6 * * *',
'SELECT * FROM project.escalate_overdue_reports()');
ELSE
RAISE NOTICE 'pg_cron не е достапен: escalate_overdue_reports() закажете ја однадвор.';
END IF;
END $$;
Поглед за администраторите со доделените работници, деновите од последната промена и ознака за задоцнетост:
CREATE VIEW v_report_overview AS
WITH last_change AS (
SELECT report_id, MAX(changed_at) AS last_changed_at
FROM status_logs
GROUP BY report_id
)
SELECT r.report_id, c.name AS category, r.description, r.status, r.priority,
ci.full_name AS citizen, r.created_at, lc.last_changed_at,
(SELECT string_agg(w.full_name, ', ' ORDER BY w.full_name)
FROM assignments a JOIN workers w ON w.worker_id = a.worker_id
WHERE a.report_id = r.report_id) AS assigned_workers,
EXTRACT(DAY FROM now()::TIMESTAMP - lc.last_changed_at)::INTEGER AS days_since_change,
sla_days(r.priority) AS sla_days,
(r.status NOT IN ('resolved', 'rejected')
AND now()::TIMESTAMP - lc.last_changed_at > sla_days(r.priority) * INTERVAL '1 day') AS is_overdue
FROM reports r
JOIN categories c ON c.category_id = r.category_id
JOIN citizens ci ON ci.citizen_id = r.citizen_id
JOIN last_change lc ON lc.report_id = r.report_id;
Пример: при тестирањето функцијата го подигна приоритетот на три пријави (пријава која чека 14 дена од низок на итен, пријава која чека 8 дена од среден на висок, и пријава која чека 3 дена од низок на среден). Второто извршување веднаш потоа не промени ништо.
Приватност на граѓаните и прегледи за работата
Граѓаните ја гледаат интерактивната мапа со активните пријави, но не смеат да ги гледаат личните податоци на другите граѓани (име, е-пошта, телефон). Затоа мапата не чита директно од табелите, туку од поглед кој ги содржи само јавните податоци: активните пријави и пријавите решени во последните 30 дена. За распределба на работата, администраторите имаат поглед со оптовареноста и учинокот на секој работник.
Имплементација
Погледи:
CREATE VIEW v_public_reports AS
SELECT r.report_id, c.name AS category, r.description, r.location_text,
r.latitude, r.longitude, r.status, r.priority, r.created_at,
(SELECT COUNT(*) FROM photos p WHERE p.report_id = r.report_id) AS photo_count
FROM reports r
JOIN categories c ON c.category_id = r.category_id
WHERE r.status NOT IN ('resolved', 'rejected')
OR r.report_id IN (SELECT l.report_id FROM status_logs l
WHERE l.status = 'resolved'
AND l.changed_at >= now() - INTERVAL '30 days');
CREATE VIEW v_worker_workload AS
WITH resolved AS (
SELECT l.worker_id, l.report_id, l.changed_at AS resolved_at, r.created_at
FROM status_logs l
JOIN reports r ON r.report_id = l.report_id
WHERE l.status = 'resolved'
)
SELECT w.worker_id, w.full_name,
COUNT(DISTINCT r.report_id) FILTER (WHERE r.status NOT IN ('resolved', 'rejected'))
AS active_reports,
COUNT(DISTINCT r.report_id) FILTER (WHERE r.status NOT IN ('resolved', 'rejected')
AND r.priority IN ('high', 'urgent'))
AS active_high_priority,
(SELECT COUNT(*) FROM resolved rs
WHERE rs.worker_id = w.worker_id
AND rs.resolved_at >= now() - INTERVAL '90 days') AS resolved_last_90_days,
(SELECT ROUND(AVG(EXTRACT(EPOCH FROM rs.resolved_at - rs.created_at) / 86400)::NUMERIC, 1)
FROM resolved rs WHERE rs.worker_id = w.worker_id) AS avg_resolution_days
FROM workers w
LEFT JOIN assignments a ON a.worker_id = w.worker_id
LEFT JOIN reports r ON r.report_id = a.report_id
GROUP BY w.worker_id, w.full_name;
Сопствени домени за валидација
Е-поштата, телефонот, статусот и приоритетот се користат во повеќе табели. Наместо истото CHECK ограничување да се повторува во секоја табела (како во фазата P2), правилата се дефинирани еднаш, како домени, и сите колони ги користат. Промена на правилото се прави на едно место.
Имплементација
Домени:
CREATE DOMAIN email_address AS VARCHAR(150)
CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
CREATE DOMAIN phone_number AS VARCHAR(20)
CHECK (VALUE ~ '^\+?[0-9]{6,19}$');
CREATE DOMAIN priority_level AS VARCHAR(10)
CHECK (VALUE IN ('low', 'medium', 'high', 'urgent'));
ALTER TABLE admins ALTER COLUMN email TYPE email_address;
ALTER TABLE workers ALTER COLUMN email TYPE email_address;
ALTER TABLE citizens ALTER COLUMN email TYPE email_address;
ALTER TABLE citizens ALTER COLUMN phone TYPE phone_number;
ALTER TABLE reports ALTER COLUMN status TYPE report_status;
ALTER TABLE reports ALTER COLUMN priority TYPE priority_level;
ALTER TABLE status_logs ALTER COLUMN status TYPE report_status;
Во скриптата, домените се креираат само ако не постојат, а старите CHECK ограничувања од P2 се бришат, за скриптата да може да се извршува повеќепати.
Користење на вештачка интелигенција
Користење на вештачка интелигенција за напредното програмирање на базата
Attachments (1)
- advanced_db.sql (23.6 KB ) - added by 12 days ago.
Download all attachments as: .zip
