Changes between Version 1 and Version 2 of AdvancedApplicationDesign_v2

Timestamp:

09/03/25 20:59:38 (6 days ago)

Author:

212012

Comment:

--

AdvancedApplicationDesign_v2

@@ -95 +95 @@
 }}}
 
-{{{#!div
-{{{#!sql
-}}}
-}}}
+= Индексирање =
+
+==== Индекси за {{{People}}} табелата ====
+{{{#!div
+{{{#!sql
+create index idx_people_embg on People(embg);
+}}}
+}}}
+
+{{{#!div
+{{{#!sql
+create index idx_people_names on People(first_name, last_name);
+}}}
+}}}
+
+{{{#!div
+{{{#!sql
+create index idx_people_nationality on People(nationality);
+}}}
+}}}
+
+==== Индекси за {{{Crime_case}}} табелата ====
+
+{{{#!div
+{{{#!sql
+create index idx_crime_case_status on Crime_case(c_status);
+}}}
+}}}
+
+{{{#!div
+{{{#!sql
+create index idx_crime_case_date on Crime_case(opening_date);
+}}}
+}}}
+
+==== Индекси за {{{Statements}}} табелата ====
+
+{{{#!div
+{{{#!sql
+create index idx_statements_date on Statements(statement_date);
+}}}
+}}}
+
+{{{#!div
+{{{#!sql
+create index idx_statements_incident on Statements(incident_timestamp);
+}}}
+}}}
+
+==== Индекси за {{{Evidence}}} табелата ====
+
+{{{#!div
+{{{#!sql
+create index idx_evidence_type on Evidence(e_type);
+}}}
+}}}
+
+{{{#!div
+{{{#!sql
+create index idx_evidence_found on Evidence(is_found);
+}}}
+}}}
+
+==== Индекси за {{{Policeman}}} табелата ====
+
+{{{#!div
+{{{#!sql
+create index idx_policeman_rank on Policeman(rank);
+}}}
+}}}
+
+= Функции и тригери =
+=== Траење на случај во денови ===
+{{{#!div
+{{{#!sql
+create or replace function calculate_case_duration(case_id bigint)
+returns integer as $$
+declare
+    opening_date date;
+    closing_date date;
+    duration integer;
+begin
+    select cc.opening_date into opening_date
+    from Crime_case as cc
+    where cc.c_id = case_id;
+    
+    select sc.date_of_closing into closing_date
+    from Solved_case as sc
+    where sc.c_id = case_id;
+    
+    if closing_date is not null then
+        duration := closing_date - opening_date;
+    else
+        duration := CURRENT_DATE - opening_date;
+    end if;
+    
+    return duration;
+end;
+$$ language plpgsql;
+}}}
+}}}
+
+=== Промена на статус на случајот кога ќе биде завршен ===
+
+{{{#!div
+{{{#!sql
+create or replace function update_case_status_on_solve()
+returns trigger as $$
+begin
+    update Crime_case
+    set c_status = 'Z'
+    where c_id = NEW.c_id and c_status = 'A';
+    
+    return new;
+end;
+$$ language plpgsql;
+
+create trigger trg_update_case_status
+    after insert on Solved_case
+    for each row
+    execute function update_case_status_on_solve();
+}}}
+}}}
+
+= Процедури и трансакции =
+=== Креирање на нов случај со иницјална изјава ===
+{{{#!div
+{{{#!sql
+create or replace procedure create_crime_case(
+    in p_case_name varchar(255),
+    in p_police_station_id bigint,
+    in p_policeman_id bigint,
+    in p_victim_id bigint,
+    in p_witness_id bigint,
+    in p_statement_description varchar(10000),
+    in p_incident_timestamp timestamp,
+    in p_incident_place varchar(4000),
+    in p_crime_types bigint[]
+)
+language plpgsql as $$
+declare
+    new_case_id bigint;
+    new_statement_id bigint;
+    crime_type_id bigint;
+begin
+    select coalesce(MAX(c_id), 0) + 1 into new_case_id from Crime_case;
+    
+    insert into Crime_case (c_id, c_name, opening_date, c_status, p_id)
+    values (new_case_id, p_case_name, CURRENT_DATE, 'A', p_police_station_id);
+    
+    select coalesce(MAX(s_id), 0) + 1 INTO new_statement_id FROM Statements;
+    
+    insert into Statements (s_id, statement_date, description, incident_timestamp, incident_place, c_id, pe_id, victim_pe_id, witness_pe_id)
+    values (new_statement_id, CURRENT_DATE, p_statement_description, p_incident_timestamp, p_incident_place, new_case_id, p_policeman_id, p_victim_id, p_witness_id);
+    
+    foreach crime_type_id IN ARRAY p_crime_types
+    loop
+        insert into Case_belongs_to_type_of_crime (c_id, t_id)
+        values (new_case_id, crime_type_id);
+    end loop;
+    
+    raise notice 'Случајот % е успешно креиран со изјава %', new_case_id, new_statement_id;
+end;
+$$;
+}}}
+}}}
+
+=== Трансфер на случај помеѓу полициски станици ===
+
+{{{#!div
+{{{#!sql
+do $$
+declare
+    v_case_id bigint := 1;
+    v_old_station bigint;
+    v_new_station bigint := 2;
+    v_new_officer bigint;
+begin
+    -- Get current station
+    select p_id into v_old_station from Crime_case where c_id = v_case_id;
+    
+    -- Get officer for new station
+    select pe_id into v_new_officer from Police_station where p_id = v_new_station;
+    
+    -- Transfer case
+    update Crime_case 
+    set p_id = v_new_station 
+    where c_id = v_case_id;
+    
+    -- Add transfer statement
+    insert into Statements (s_id, statement_date, description, incident_timestamp, 
+                          incident_place, c_id, pe_id, victim_pe_id, witness_pe_id)
+    select coalesce(MAX(s_id), 0) + 1, CURRENT_DATE, 'Случајот е префрлен од станица ' || v_old_station || ' во станица ' || v_new_station, CURRENT_TIMESTAMP, 'Префрлување на случај', v_case_id, (select pe_id from Policeman where p_id = v_new_station limit 1), 2, 3
+    from Statements;
+    
+    raise notice 'Случајот со ID % е префрлен од полициска станица % во полициска станица %', v_case_id, v_old_station, v_new_station;    
+
+exception
+    when others then
+        raise exception 'Transaction failed: %', SQLERRM;
+end;
+$$;
+}}}
+}}}