Changes between Initial Version and Version 1 of AdvancedApplicationDesign_v2

Timestamp:

08/19/25 12:44:03 (2 days ago)

Author:

212028

Comment:

--

AdvancedApplicationDesign_v2

@@ +1 @@
+= Материјализирани погледи и индексирање =
+
+=== 1. Број казнети според линија секоја година ===
+
+{{{#!div
+{{{#!sql
+create materialized view kazni_po_linija_godisno as
+select
+    extract(year from kz.kz_datum) as godina,
+    li.li_ime,
+    count(*) as broj_kazni
+from linija li
+join istanca_na_linija inl on inl.l_id = li.l_id
+join kontroli ko on ko.inl_id = inl.inl_id
+join kazna kz on kz.kontrola_id = ko.kontrola_id
+group by godina, li.li_ime
+order by godina, li.li_ime desc;
+
+create index idx_kazni_po_linija on kazni_po_linija_godisno(godina, li_ime);
+}}}
+}}}
+
+=== 2. Најголем број возења по интервал од 1 час според линија за секоја година ===
+
+{{{#!div
+{{{#!sql
+create materialized view maks_vozenja_po_saat_godisno as
+select
+    godina,
+    li_ime,
+    interval_1h,
+    broj_vozenja
+from (
+    select
+        extract(year from v.vozenje_start)::int as godina,
+        li.li_ime,
+        to_char(extract(hour from v.vozenje_start), '00') || ':00 - ' || to_char(extract(hour from v.vozenje_start + interval '1 hour'), '00') || ':00' as interval_1h,
+        count(*) as broj_vozenja
+    from linija li
+    join instanca_na_linija inl on inl.li_id = li.li_id
+    join vozenje v on v.inl_id = inl.inl_id
+    group by godina, li.li_ime, interval_1h
+) as q1
+where (godina, li_ime, broj_vozenja) in (
+    select
+        godina,
+        li_ime,
+        max(broj_vozenja)
+    from (
+        select
+            extract(year from v.vozenje_start)::int as godina,
+            li.li_ime,
+            to_char(extract(hour from v.vozenje_start), '00') || ':00 - ' || to_char(extract(hour from v.vozenje_start + interval '1 hour'), '00') || ':00' as interval_1h,
+            count(*) as broj_vozenja
+        from linija li
+        join instanca_na_linija inl on inl.li_id = li.li_id
+        join vozenje v on v.inl_id = inl.inl_id
+        group by godina, li.li_ime, interval_1h
+    ) as q2
+    group by godina, li_ime
+)
+order by godina, li_ime;
+
+create index idx_vozenja_po_saat on maks_vozenja_po_saat_godisno (li_ime, godina, interval_1h);
+}}}
+}}}
+
+=== 3. Број возења од линија за секоја година ===
+
+{{{#!div
+{{{#!sql
+create materialized view vozenja_po_linija_godisno as
+select
+    extract(year from v.vozenje_start) as godina,
+    li.li_ime,
+    li.li_pravec,
+    count(v.vozenje_id) as broj_vozenja
+from linija li
+join instanca_na_linija inl on inl.li_id = li.li_id
+join vozenje v on v.inl_id = inl.inl_id
+group by godina, li.li_id, li.li_ime, li.li_pravec
+order by godina, li.li_id desc;
+
+create index idx_vozenja_po_linija on vozenja_po_linija_godisno (li_ime, godina);
+}}}
+}}}
+
+=== 4. Број патници според постојка на дадена линија за секоја година ===
+
+{{{#!div
+{{{#!sql
+create materialized view patnici_po_postojka_godisno as
+select
+    extract(year from v.vozenje_start) as godina,
+    p.p_ime,
+    li.li_ime,
+    count(v.patnik_k_id) as broj_patnici
+from postojka p
+join postojka_na_linija pnl on pnl.p_id = p.p_id
+join vozenje v on v.pnl_id = pnl.pnl_id
+join patnik pa on pa.k_id = v.patnik_k_id
+join linija li on li.li_id = pnl.li_id
+group by godina, p.p_ime, li.li_ime
+order by godina, broj_patnici desc;
+
+create index idx_patnici_po_postojka on patnici_po_postojka_godisno (godina, p_ime, li_ime);
+}}}
+}}}
+
+=== 5. Часови во кои најмногу се купуваат часовни и дневни билети за секоја година ===
+
+{{{#!div
+{{{#!sql
+create materialized view maks_kupeni_bileti_po_cas_godisno as
+select
+    tb_ime,
+    godina,
+    interval_1h,
+    broj_bileti
+from (
+    select
+        tb.tb_ime,
+        extract(year from b.b_datum_na_kupuvanje)::int as godina,
+        to_char(extract(hour from b.b_datum_na_kupuvanje), '00') || ':00 - ' || to_char((extract(hour from b.b_datum_na_kupuvanje) + 1)::int, '00') || ':00' as interval_1h,
+        count(*) as broj_bileti
+    from tipbilet tb
+    join bilet b on b.tb_id = tb.tb_id
+    where tb.tb_ime in ('часовен', 'дневен')
+    group by tb.tb_ime, godina, interval_1h
+) q1
+where (q1.tb_ime, q1.godina, q1.broj_bileti) in (
+    select
+        tb_ime,
+        godina,
+        max(broj_bileti)
+    from (
+        select
+            tb.tb_ime,
+            extract(year from b.b_datum_na_kupuvanje)::int as godina,
+            to_char(extract(hour from b.b_datum_na_kupuvanje), '00') || ':00 - ' || to_char((extract(hour from b.b_datum_na_kupuvanje) + 1)::int, '00') || ':00' as interval_1h,
+            count(*) as broj_bileti
+        from tipbilet tb
+        join bilet b on b.tb_id = tb.tb_id
+        where tb.tb_ime in ('часовен', 'дневен')
+        group by tb.tb_ime, godina, interval_1h
+    ) q2
+    group by godina, tb_ime
+)
+order by q1.godina, q1.tb_ime;
+
+create index idx_bileti_kupeni_od_cas on maks_kupeni_bileti_po_cas_godisno (tb_ime, godina, interval_1h);
+}}}
+}}}
+
+=== 6. Профит од казни и билети за секоја година ===
+
+{{{#!div
+{{{#!sql
+create materialized view profit_kazni_i_bileti_godisno as
+select
+    godina,
+    sum(coalesce(kz_iznos, 0)) + sum(coalesce(tb_cena, 0)) as vkupen_promet
+from (
+    select
+        extract(year from kz.kz_datum)::int as godina,
+        kz.kz_iznos,
+        null::numeric as tb_cena
+    from kazna kz
+    where
+        kz.kz_plateno = true
+        and kz.kz_datum is not null
+        and kz.kz_datum_plateno is not null
+        and extract(year from kz.kz_datum) = extract(year from kz.kz_datum_plateno)
+    union all
+    select
+        extract(year from b.b_datum_na_kupuvanje)::int as godina,
+        null::numeric as kz_iznos,
+        tb.tb_cena
+    from bilet b
+    join tipbilet tb on tb.tb_id = b.tb_id
+) as subquery
+group by godina
+order by godina;
+
+create index idx_prihod_godisen on profit_kazni_i_bileti_godisno(godina);
+}}}
+}}}
+
+= Тригери и фукниции =
+
+=== 1. Промена на статус на билет ===
+
+{{{#!div
+{{{#!sql
+create or replace function bilet_status()
+returns trigger as
+$$
+begin
+    if new.b_status = 'expired' then
+        raise exception 'билетот е веќе истечен';
+    end if;
+
+    if new.b_status = 'inactive' and new.b_datum_aktivacija is null then
+        new.b_datum_aktivacija = now();
+        new.b_status = 'active';
+    end if;
+
+    if new.b_status = 'active' and new.b_datum_aktivacija is not null then
+        if exists (
+            select 1
+            from tipbilet tb
+            where tb.tb_id = new.tb_id
+                and (new.b_datum_aktivacija + (tb.tb_trajnost || ' minutes')::interval) < now()
+        ) then
+            new.b_status = 'expired';
+            raise exception 'билетот е истечен';
+        end if;
+    end if;
+
+    return new;
+end;
+$$ language plpgsql;
+
+create or replace trigger proverka_bilet
+before insert or update on bilet
+for each row
+execute function bilet_status();
+}}}
+}}}
+
+=== 2. Промена на статус на билет ===
+
+{{{#!div
+{{{#!sql
+create or replace function vozenje_status()
+returns trigger as $$
+begin
+    update vozenje
+    set vozenje_end = now(), vozenje_status = 'finished'
+    where patnik_k_id = new.patnik_k_id
+        and vozenje_start is not null
+        and vozenje_end is null
+        and vozenje_status = 'active'
+        and vozenje_id != new.vozenje_id;
+
+    if new.vozenje_end is not null and new.vozenje_status != 'finished' then
+        new.vozenje_status = 'finished';
+    end if;
+
+    if new.vozenje_end is null then
+        new.vozenje_status = 'active';
+    end if;
+
+    return new;
+end;
+$$ language plpgsql;
+
+create or replace trigger vozenje_proverka
+before insert or update on vozenje
+for each row
+execute function vozenje_status();
+}}}
+}}}
+
+= Процеудури и трансакции =
+
+==== 1. Регистрација на корисник ====
+
+{{{#!div
+{{{#!sql
+create or replace procedure registracija_korisnik(
+    i_k_ime varchar(4000),
+    i_k_adresa varchar(4000),
+    i_k_telefon varchar(500),
+    i_k_email varchar(500),
+    i_k_embg varchar(13),
+    i_k_lozinka varchar(500),
+    i_k_is_admin boolean,
+    i_k_uloga varchar(500),
+    i_v_plata float default null,
+    i_v_datum_vrabotuvanje date default null,
+    i_v_datum_prekin_vrabotuvanje date default null
+)
+language plpgsql
+as $$
+declare
+    nov_k_id bigint;
+begin
+    if i_k_ime is null or trim(i_k_ime) = '' then
+        raise exception 'името е задолжително';
+    end if;
+
+    if i_k_embg is null or length(i_k_embg) != 13 or i_k_embg !~ '^[0-9]{13}$' then
+        raise exception 'ембг мора да има 13 карактери';
+    end if;
+
+    if i_k_email is null or i_k_email !~ '^[a-za-z0-9._%+-]+@[a-za-z0-9.-]+\.[a-za-z]{2,}$' then
+        raise exception 'невалидна е-маил адреса';
+    end if;
+
+    if i_k_lozinka is null or trim(i_k_lozinka) = '' then
+        raise exception 'лозинка е задолжителна';
+    end if;
+
+    insert into korisnik(
+        k_ime, k_adresa, k_telefon, k_email, k_embg, k_lozinka, k_is_admin
+    ) values (
+        i_k_ime, i_k_adresa, i_k_telefon, i_k_email, i_k_embg, i_k_lozinka, i_k_is_admin
+    ) returning k_id into nov_k_id;
+
+    if i_k_uloga = 'patnik' then
+        insert into patnik(k_id) values (nov_k_id);
+    elsif i_k_uloga = 'vozac' then
+        if i_v_plata is null or i_v_datum_vrabotuvanje is null then
+            raise exception 'податоците за вработен се неопходни';
+        end if;
+        if i_v_plata <= 0 then
+            raise exception 'платата неможе да е нула или пониска вредност';
+        end if;
+        insert into vraboten(k_id, v_plata, v_datum_na_vrabotuvanje, v_datum_prekin_vrabotuvanje)
+        values (nov_k_id, i_v_plata, i_v_datum_vrabotuvanje, i_v_datum_prekin_vrabotuvanje);
+        insert into vozac(k_id) values (nov_k_id);
+    elsif i_k_uloga = 'konduktor' then
+        if i_v_plata is null or i_v_datum_vrabotuvanje is null then
+            raise exception 'податоците за вработен се неопходни';
+        end if;
+        if i_v_plata <= 0 then
+            raise exception 'платата неможе да е нула или пониска вредност';
+        end if;
+        insert into vraboten(k_id, v_plata, v_datum_na_vrabotuvanje, v_datum_prekin_vrabotuvanje)
+        values (nov_k_id, i_v_plata, i_v_datum_vrabotuvanje, i_v_datum_prekin_vrabotuvanje);
+        insert into konduktor(k_id) values (nov_k_id);
+    else
+        raise exception 'невалидна улога: %', i_k_uloga;
+    end if;
+exception
+    when unique_violation then
+        if sqlerrm ~ 'unizue_k_embg' then
+            raise exception 'ембг-то % веќе постои', i_k_embg;
+        elsif sqlerrm ~ 'unique_k_email' then
+            raise exception 'е-маил-то % веќе постои', i_k_email;
+        else
+            raise exception 'грешка при регистрација: %', sqlerrm;
+        end if;
+    when others then
+        raise exception 'грешка при регистрација: %', sqlerrm;
+end;
+$$;
+}}}
+}}}
+
+==== 2. Запишување казна ====
+
+{{{#!div
+{{{#!sql
+create or replace procedure zapisi_kazna(
+    i_kz_iznos float,
+    i_kz_plateno boolean,
+    i_kz_datum timestamp,
+    i_kz_datum_plateno timestamp default null,
+    i_kz_dokument varchar(500),
+    i_konduktor_k_id bigint,
+    i_kontrola_id bigint,
+    kz_tip varchar(500),
+    i_patnik_k_id bigint default null,
+    i_kzn_telefon varchar(500) default null,
+    i_kzn_ime varchar(4000) default null,
+    i_kzn_adresa varchar(4000) default null
+)
+language plpgsql
+as $$
+declare
+    nova_kz_id bigint;
+begin
+    if i_kz_iznos is null or i_kz_iznos <= 0 then
+        raise exception 'износот на казната мора да биде позитивен';
+    end if;
+
+    if i_kz_datum is null then
+        raise exception 'датумот на казната е задолжителен';
+    end if;
+
+    if i_kz_dokument is null or trim(i_kz_dokument) = '' then
+        raise exception 'документот за казна е задолжителен';
+    end if;
+
+    if i_konduktor_k_id is null or not exists (
+        select 1
+        from konduktor
+        where k_id = i_konduktor_k_id
+    ) then
+        raise exception 'невалиден кондуктор';
+    end if;
+
+    if i_kontrola_id is null or not exists (
+        select 1
+        from kontrola
+        where kontrola_id = i_kontrola_id
+    ) then
+        raise exception 'невалидна контрола';
+    end if;
+
+    insert into kazna(
+        kz_iznos, kz_plateno, kz_datum, kz_datum_plateno, kz_dokument, konduktor_k_id, kontrola_id
+    ) values (
+        i_kz_iznos, i_kz_plateno, i_kz_datum, i_kz_datum_plateno, i_kz_dokument, i_konduktor_k_id, i_kontrola_id
+    ) returning kz_id into nova_kz_id;
+
+    if kz_tip = 'registriran' then
+        if i_patnik_k_id is null or not exists (
+            select 1
+            from patnik
+            where k_id = i_patnik_k_id
+        ) then
+            raise exception 'грешка: не постои како регистриран патник';
+        end if;
+        insert into kaznazaregistriran(kz_id, patnik_k_id)
+        values (nova_kz_id, i_patnik_k_id);
+    elsif kz_tip = 'neregistriran' then
+        if i_kzn_ime is null or trim(i_kzn_ime) = '' then
+            raise exception 'име е задолжително за нерегистриран патник';
+        end if;
+        if i_kzn_adresa is null or trim(i_kzn_adresa) = '' then
+            raise exception 'адреса е задолжителна за нерегистриран патник';
+        end if;
+        if i_kzn_telefon is null or trim(i_kzn_telefon) = '' then
+            raise exception 'телефонот е задолжителен за нерегистриран патник';
+        end if;
+        if i_kzn_telefon !~ '^[0-9]{9,12}$' then
+            raise exception 'невалиден формат на телефонски број';
+        end if;
+        insert into kaznazaneregistriran(
+            kz_id, kzn_telefon, kzn_ime, kzn_adresa
+        ) values (
+            nova_kz_id, i_kzn_telefon, i_kzn_ime, i_kzn_adresa
+        );
+    else
+        raise exception 'патникот може да е само регистриран или нерегистриран: %', kz_tip;
+    end if;
+exception
+    when others then
+        raise exception 'грешка при запишување казна: %', sqlerrm;
+end;
+$$;
+}}}
+}}}