Материјализирани погледи и индексирање

1. Број казнети според линија секоја година

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 час според линија за секоја година

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. Број возења од линија за секоја година

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. Број патници според постојка на дадена линија за секоја година

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. Часови во кои најмногу се купуваат часовни и дневни билети за секоја година

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. Профит од казни и билети за секоја година

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. Промена на статус на билет

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. Промена на статус на билет

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. Регистрација на корисник

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. Запишување казна

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;
$$;