Напредни PostgreSQL компоненти

1) Интегритетни ограничувања (CHECK/UNIQUE) 1.1 Reaction: различни елементи

Цел: да не се креира реакција со ист елемент двапати.

ALTER TABLE reaction
  ADD CONSTRAINT reaction_distinct_elements
  CHECK (element1_id <> element2_id);

Каде се користи:

DatabaseManager.add_reaction(...) и update_reaction(...) (backend): пред-валидација и фаќање CheckViolation.

1.2 Elements: физички валидни вредности

Цел: основна научна валидност за елементите.

ALTER TABLE elements
  ADD CONSTRAINT elements_atomic_number_positive CHECK (atomic_number > 0);
ALTER TABLE elements
  ADD CONSTRAINT elements_mass_positive CHECK (atomic_weight > 0);
ALTER TABLE elements
  ADD CONSTRAINT elements_melting_before_boiling
  CHECK (boiling_point IS NULL OR melting_point IS NULL OR melting_point < boiling_point);

Каде се користи:

DatabaseManager.add_element(...) / update_element(...): фаќа CheckViolation и враќа јасна порака.

1.3 Партиципација: без дупликати

Цел: еден корисник да не „учествува“ двапати во истиот експеримент.

ALTER TABLE userparticipatesinexperiment
  ADD CONSTRAINT uq_user_experiment UNIQUE (user_id, experiment_id);

Каде се користи:

DatabaseManager.track_experiment_participation(...): ON CONFLICT (user_id, experiment_id) DO NOTHING.

1.4 Уникатни бизнис-клучеви (email/symbol/equipment_name)

Цел: да нема дупликат email/симбол/име на опрема.

ALTER TABLE "User"      ADD CONSTRAINT uq_user_email      UNIQUE (email);
ALTER TABLE elements    ADD CONSTRAINT uq_elements_symbol UNIQUE (symbol);
ALTER TABLE labequipment ADD CONSTRAINT uq_equipment_name UNIQUE (equipment_name);

Каде се користи:

Регистрација (DatabaseManager.register_user): фаќа UniqueViolation за email.

Додавање/уредување елемент (add_element/update_element): _norm_symbol() → UniqueViolation ако постои.

Додавање опрема (add_lab_equipment): UniqueViolation ако постои.

1.5 Уникатна тројка за Reaction (element1, element2, conditions)

Цел: иста реакција под исти услови да не се дуплира.

CREATE UNIQUE INDEX IF NOT EXISTS uq_reaction_e1_e2_cond
ON reaction (element1_id, element2_id, COALESCE(conditions, ''));

Каде се користи:

add_reaction/update_reaction: фаќа UniqueViolation и враќа None/False со лог порака „duplicate (element1, element2, conditions)“.

2) Временско печатење на учество (TIMESTAMPTZ)

Цел: да се бележи точниот момент (со временска зона) кога студентот учествувал.

ALTER TABLE userparticipatesinexperiment
  ADD COLUMN IF NOT EXISTS participation_timestamp TIMESTAMPTZ;
UPDATE userparticipatesinexperiment
   SET participation_timestamp = COALESCE(participation_timestamp, NOW());
ALTER TABLE userparticipatesinexperiment
  ALTER COLUMN participation_timestamp SET NOT NULL,
  ALTER COLUMN participation_timestamp SET DEFAULT NOW();

Каде се користи:

get_user_experiments(...), get_student_participation_experiments(...): ORDER BY participation_timestamp DESC.

Извештаи /reports/... што групираат/редат по участие.

3) Тригер: default safety_warning (на experiment)

Цел: ако UI не прати безбедносно предупредување, базата сама да пополни разумен default.

CREATE OR REPLACE FUNCTION trg_experiment_default_safety()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.safety_warning IS NULL OR NEW.safety_warning = '' THEN
    NEW.safety_warning := 'Стандардни безбедносни мерки';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS set_default_safety ON experiment;
CREATE TRIGGER set_default_safety
BEFORE INSERT ON experiment
FOR EACH ROW
EXECUTE FUNCTION trg_experiment_default_safety();

Каде се користи:

DatabaseManager.insert_experiment(...): во кодот праќаме NULL ако корисникот остави празно → тригерот пополнува.

4) Погледи (Views) 4.1 vw_students_experiments_detailed

Цел: брз извештај за студент, експеримент и време на учество.

CREATE OR REPLACE VIEW vw_students_experiments_detailed AS
SELECT 
  s.student_id,
  (u.user_name || ' ' || u.user_surname) AS full_name,
  e.experiment_id,
  e.result,
  up.participation_timestamp::timestamp AS participation_time
FROM student s
JOIN "User" u ON s.student_id = u.user_id
JOIN userparticipatesinexperiment up ON s.student_id = up.user_id
JOIN experiment e ON up.experiment_id = e.experiment_id
ORDER BY u.user_name, up.participation_timestamp DESC;

4.2 vw_user_activity_summary

Цел: консолидиран преглед на активност по корисник.

CREATE OR REPLACE VIEW vw_user_activity_summary AS
SELECT 
  u.user_id,
  (u.user_name || ' ' || u.user_surname) AS full_name,
  u.role,
  COUNT(DISTINCT uve.element_id)    AS elements_viewed,
  COUNT(DISTINCT uvl.equipment_id)  AS equipment_viewed,
  COUNT(DISTINCT upe.experiment_id) AS experiments_participated
FROM "User" u
LEFT JOIN userviewselement           uve ON u.user_id = uve.user_id
LEFT JOIN userviewslabequipment      uvl ON u.user_id = uvl.user_id
LEFT JOIN userparticipatesinexperiment upe ON u.user_id = upe.user_id
GROUP BY u.user_id, full_name, u.role
ORDER BY full_name;

4.3 vw_students_experiments_for_teacher

Цел: на наставник да му се прикажат експериментите на неговите студенти.

CREATE OR REPLACE VIEW vw_students_experiments_for_teacher AS
SELECT 
  (u.user_name || ' ' || u.user_surname) AS student_name,
  s.student_id,
  e.experiment_id,
  e.result,
  e.time_stamp,
  r.product,
  el1.symbol AS element1_symbol,
  el2.symbol AS element2_symbol,
  up.participation_timestamp AS participation_date,
  s.teacher_id
FROM student s
JOIN "User" u ON s.student_id = u.user_id
JOIN userparticipatesinexperiment up ON s.student_id = up.user_id
JOIN experiment e ON up.experiment_id = e.experiment_id
JOIN reaction  r ON e.reaction_id   = r.reaction_id
JOIN elements el1 ON r.element1_id = el1.element_id
JOIN elements el2 ON r.element2_id = el2.element_id
ORDER BY up.participation_timestamp DESC, student_name;

Каде се користи:

Backend методи: vw_students_experiments_detailed(), vw_students_experiments_for_teacher(teacher_id), get_user_activity_summary().

5) Складирана функција: create_reaction_and_experiment_fn(...)

Цел: атомично креирање Reaction → Experiment → (N:M) Equipment во една транскација на ниво на база. Код (скратено):

CREATE OR REPLACE FUNCTION create_reaction_and_experiment_fn(
  p_teacher_id INT, p_element1_id INT, p_element2_id INT,
  p_product TEXT, p_conditions TEXT, p_exp_result TEXT,
  p_safety TEXT, p_equipment_ids INT[]
) RETURNS TABLE(reaction_id INT, experiment_id INT)
LANGUAGE plpgsql AS $$
DECLARE v_reaction_id INT; v_experiment_id INT; v_result TEXT;
BEGIN
  INSERT INTO reaction(...) RETURNING reaction_id INTO v_reaction_id;

  IF p_exp_result IS NULL OR p_exp_result = '' THEN
    -- авто-опис: „Експеримент со X и Y под услови...“
    SELECT 'Експеримент со ' || e1.symbol || ' и ' || e2.symbol ||
           CASE WHEN p_conditions IS NULL OR p_conditions = '' THEN
                ' под услови: стандардни. ' ELSE ' под услови: ' || p_conditions || '. ' END ||
           'Очекуван производ: ' || COALESCE(p_product, 'непознат') || '.'
    INTO v_result
    FROM elements e1, elements e2
    WHERE e1.element_id = p_element1_id AND e2.element_id = p_element2_id;
  ELSE v_result := p_exp_result; END IF;

  INSERT INTO experiment(...) VALUES (...) RETURNING experiment_id INTO v_experiment_id;

  IF p_equipment_ids IS NOT NULL AND array_length(p_equipment_ids,1) > 0 THEN
    INSERT INTO experimentlabequipment(experiment_id, equipment_id)
    SELECT v_experiment_id, unnest(p_equipment_ids) ON CONFLICT DO NOTHING;
  END IF;

  RETURN QUERY SELECT v_reaction_id, v_experiment_id;
END; $$;

Каде се користи:

DatabaseManager.create_reaction_and_experiment(...): прво повикува DB-функција; ако не постои → Python fallback со транскација.

Route /reactions/add: UI формата креира Reaction + Experiment + Equipment во еден чекор.

6) Индекси (перформанси)

Цел: побрзи пребарувања и сортирања.

-- филтрирање по E1/E2:
CREATE INDEX IF NOT EXISTS idx_reaction_elements   ON reaction (element1_id, element2_id);
-- експерименти по реакција (JOIN/филтер):
CREATE INDEX IF NOT EXISTS idx_exp_reaction        ON experiment (reaction_id);
-- учества по корисник/експеримент:
CREATE INDEX IF NOT EXISTS idx_up_user             ON userparticipatesinexperiment (user_id);
CREATE INDEX IF NOT EXISTS idx_up_experiment       ON userparticipatesinexperiment (experiment_id);
-- реални ORDER BY по време:
CREATE INDEX IF NOT EXISTS idx_up_user_time        ON userparticipatesinexperiment (user_id, participation_timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_exp_reaction_time   ON experiment (reaction_id, time_stamp DESC);

Каде се користат:

get_experiments_by_reaction(reaction_id) → idx_exp_reaction(_time)

get_user_experiments(user_id) и get_student_participation_experiments(student_id) → idx_up_user(_time)

get_reaction_by_symbols(sym1,sym2) → elements(symbol) + idx_reaction_elements (бидејќи симболите се нормализирани во кодот, индексот е употреблив).