Changes between Initial Version and Version 1 of AditionalApplicationDevelopment

Timestamp:

09/15/25 13:20:45 (6 weeks ago)

Author:

221028

Comment:

--

AditionalApplicationDevelopment

@@ +1 @@
+
+== Напредни PostgreSQL компоненти
+
+** 1) Интегритетни ограничувања (CHECK/UNIQUE)**
+**1.1 Реакција: различни елементи**
+
+{{{
+ALTER TABLE reaction
+  ADD CONSTRAINT reaction_distinct_elements
+  CHECK (element1_id <> element2_id);
+}}}
+
+
+**1.2 Елементи: валидни физички вредности**
+
+{{{
+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
+  );
+
+}}}
+
+**1.3 Партиципација: без дупликати**
+
+{{{
+ALTER TABLE userparticipatesinexperiment
+  ADD CONSTRAINT uq_user_experiment UNIQUE (user_id, experiment_id);
+}}}
+
+
+**2) Временско печатење на учество**
+
+{{{
+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();
+}}}
+ 
+
+**3) Тригер: set_default_safety (на experiment)
+**
+
+{{{
+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();
+
+}}}
+
+
+**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,
+  e.time_stamp 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, e.time_stamp 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;
+}}}
+
+
+**5) Stored function: create_reaction_and_experiment_fn(...)**
+
+
+{{{
+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)
+
+}}}
+
+
+Логика (атомично во една функција):
+
+INSERT во reaction → reaction_id.
+
+Ако p_exp_result е празен → авто-генерира текст „Експеримент со X + Y…“.
+
+INSERT во experiment (time_stamp = CURRENT_TIMESTAMP, safety_warning = p_safety).
+
+Ако има p_equipment_ids → INSERT во experimentlabequipment со unnest(p_equipment_ids).
+
+Враќа {reaction_id, experiment_id}.
+
+Во апликацијата, оваа функција се повикува ако е достапна; ако не, има Python fallback со транскација (исто поведение).
+
+**6) Индекси (перформанси)**
+
+{{{
+CREATE INDEX IF NOT EXISTS idx_reaction_elements ON reaction (element1_id, element2_id);
+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);
+}}}
+
+