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

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;

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;

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;

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;

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;

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;

Индексирање

Прашалниците погоре служат за статистика и исвештаи преку годините.За истите за да не се извршуваат постојано резултатите од нив се складираат во материјализирани погледи.Ова го правиме за да се избегне прекумерно оптовораување на базата и исцрпување на ресурсите,имајќи во предвид дела податоците се на годишно ниво и обработката се извршива врз голем сет на податоци собрани за сите тие години.Иако веќе со ова се постигнува одредена оптимизација, за случаевите кога има потреба од обновување на податоците скалдирани во овие материјлизирани погледи може да воведеме индекси во веќе постоечките табели преку кои се доаѓа до овие материјализирани погледи за уште пооптимално извржување на прашалниците.Во прилог се индексите кои се воведуваат за секоја од табелите кој се дел од овие прашалници и причината за нивно воведување.

Индекси за Istanca na linija табелата

create index idx_instancanalinija_li_id on instancanalinija (li_id); 

Индекси за Kontroli табелата

create index idx_kontroli_inl_id on kontroli (inl_id);

Индекси за Kazna табелата

create index idx_kazna_kontrola_id on kazna (kontrola_id);

create index idx_kazna_kz_datum_year on kazna (extract(year from kz_datum));

Индекси за Vozenje табелата

create index idx_vozenje_inl_id on vozenje(inl_id);

create index idx_vozenje_hour on vozenje(extract(year from vozenje_start));

Тригери и фукниции

1. Проверка на билет и ажурирање на статус на возење

create or replace function proverka_bilet_azuriranje_vozenja() 
returns trigger as $$
begin
    if exists (
        select 1
        from vozenje v
        where v.patnik_id = new.patnik_id
        and v.status = 'active'
        and v.vozenje_id != new.vozenje_id
    ) then
        update vozenje
        set end_date = new.start_date,
            status = 'finished'
        where patnik_id = new.patnik_id
        and status = 'active'
        and vozenje_id != new.vozenje_id;
    end if;

    if exists (
        select 1
        from bilet b
        join tipbilet tb on b.tb_id = tb.tb_id
        where b.b_id = new.b_id
        and b.status = 'active'
        and (b.datum_na_kupuvanje + (tb.tb_trajnost || ' seconds')::interval) < now()
    ) then
        update bilet
        set status = 'expired'
        where b_id = new.b_id;

        raise exception 'ticket % is expired', new.b_id;
    end if;

    return new;
end;
$$ language plpgsql;

create or replace trigger menagiranje_vozenja
before insert on vozenje
for each row
execute function proverka_bilet_azuriranje_vozenja();

create or replace function update_vozenje_on_line_instance_change() returns trigger as $$
begin
    -- update vozenje if inl_datum_end changed
    if new.inl_datum_end is distinct from old.inl_datum_end then
        update vozenje
        set end_date = new.inl_datum_end,
            status = 'finished'
        where inl_id = new.inl_id
        and status = 'active';
    end if;

    return new;
end;
$$ language plpgsql;

create or replace trigger update_vozenje_za_inl
after update of inl_datum_end on instancanalinija
for each row
execute function update_vozenje_on_line_instance_change();

Процеудури и трансакции

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