Напреден апликативен дизајн

Напредниот апликативен дизајн има за цел да ги примени сите опишани кориснички сценарија и улоги од првите фази во финалната апликација. За таа цел создадена е нова апликација која користи модерни технологии.

За базата повторно се користи Postgres 16 базата, за backend се користи Spring Boot API напишан во Kotlin и за frontend се користи Angular 18.

Во пасусите во продолжение ќе бидат објаснети и покажани сите кориснилки сценарија имплементирани во апликацијата.

Кориснички сценарија

Регистрација на корисник

Купување на билет

Стартување на возење (патник)

Вршење на контрола

Пишување казна

Стартување на рута на автобус

Материјализирани views

Напредните извештаи од минатите фази се искористени за да се направат materialized views. Материјализираните views значително го намалуваат времето на извршување на квери поради тоа што го чуваат и резултатот. Ова е соодветно поради тоа што ваквите статистики нема потреба да бидат up to date во секој момент, доволно е да се рефрешираат на одреден период (на пример 12 часа).

Во продолжение се прикажани материјалиизираните views (за некои од нив има и графици кои може да се погледнат во амин панелот).

Средна разлика на простигнување на инстанците од постојка А до постојка Б

Идејата е да може да се има естимација на време за кое автобусот би стигнал на одредена постојка.

CREATE MATERIALIZED VIEW avg_time_diffs AS
WITH ordered_instanca AS (SELECT ilpl.instanca_na_linija_id,
                                 ilpl.postojka_na_linija_id,
                                 pnl.reden_broj,
                                 ilpl.timestamp
                          FROM instanca_na_linija_postojka_na_linija ilpl
                                   JOIN
                               postojka_na_linija pnl ON ilpl.postojka_na_linija_id = pnl.id
                          ORDER BY ilpl.instanca_na_linija_id,
                                   pnl.reden_broj),
     time_differences AS (SELECT o1.instanca_na_linija_id,
                                 o1.postojka_na_linija_id                          AS start_station_id,
                                 o1.timestamp                                      AS start_time,
                                 o2.postojka_na_linija_id                          AS end_station_id,
                                 o2.timestamp                                      AS end_time,
                                 EXTRACT(EPOCH FROM (o2.timestamp - o1.timestamp)) AS time_diff
                          FROM ordered_instanca o1
                                   JOIN
                               ordered_instanca o2 ON o1.instanca_na_linija_id = o2.instanca_na_linija_id
                          WHERE o1.reden_broj + 1 = o2.reden_broj)
SELECT
    row_number() over () as id,
    start_station_id,
       end_station_id,
       AVG(time_diff)::double precision AS avg_time_diff_seconds
FROM time_differences
GROUP BY start_station_id,
         end_station_id
ORDER BY start_station_id,
         end_station_id;

create unique index avg_time_diffs_index on avg_time_diffs (id);

Број на казни според линија

create materialized view kazna_po_linija as
select row_number() over () as id, l.id as line_id, count(l.id)
from linija l
         left join instanca_na_linija inl on l.id = inl.linija_id
         left join kontrola k on inl.id = k.instanca_na_linija_id
         left join kazna kaz on k.id = kaz.kontrola_id
where extract(year from kaz.date_created) = extract(year from now())
group by l.id
order by count(l.id) desc;

create unique index index_kazna_po_linija on kazna_po_linija(id);

Зафатеност на линиите по интервали од 1 час

create materialized view most_busy_part_of_the_day as
select row_number() over () as id, id as linija_id, interval_1h, count(id || '-' || interval_1h) broj_pati
from (select li.id,
             (to_char(extract(hour from v.start_date), '00') || ':00 -' || to_char(extract(hour from (v.start_date
                 + '1 hours'::interval))::int, '00') || ':00') as interval_1h
      from linija li
               left join instanca_na_linija inl on li.id = inl.linija_id
               left join vozenje v on inl.id = v.instanca_na_linija_id
      where v.start_date between (make_date(extract(year from (now()))::int, 1, 1)) and (make_date(extract(year from (now()))::int, 12, 31))) as hours
group by id, interval_1h
order by interval_1h desc;

create unique index index_most_busy_part_of_the_day on most_busy_part_of_the_day(id);

Commutes per line

create materialized view commutes_by_line as
select row_number() over () as id, l.id as linija_id, count(v.id)
from linija l
         left join instanca_na_linija inl on l.id = inl.linija_id
         left join vozenje v on inl.id = v.instanca_na_linija_id
where v.start_date between date_trunc('year', now()) and date_trunc('month', date_trunc('year', now())+ interval '7 months')
group by l.id;

Number of passengers per line and station

{{#!sql create materialized view number_passengers_per_line_and_station as select row_number() over () as id, p.id as station_id, l.id as line_id, count(p.id) from postojka p

join postojka_na_linija pnl on p.id = pnl.postojka_id join vozenje v on pnl.id = v.postojka_na_linija_start_id join patnik pat on v.patnik_id = pat.id join linija l on pnl.linija_id = l.id

where v.start_date between (make_date(extract(year from (now() - '1 years'::interval))::int, 4, 1)) and (make_date(extract(year from (now() - '1 years'::interval))::int, 6, 30)) group by p.id, l.id order by count(p.id) desc; }}

Ticket sales

create materialized view ticket_sales as
select row_number() over () as id, filtered.tip_id, filtered.interval_1h, filtered.broj_pati
from (select filter.tip_id, interval_1h, count(tip_id || '-' || interval_1h) broj_pati
      from (select *,
                   (to_char(extract(hour from b.datum_kupuvanje), '00') || ':00 -' ||
                    to_char(extract(hour from (b.datum_kupuvanje + '1 hours'::interval))::int, '00') ||
                    ':00') as interval_1h
            from tipbilet t
                     left join bilet b on t.id = b.tip_id
--     where  b.datum_kupuvanje between (make_date(extract(year from (now() - '1 years'::interval))::int, 1, 1)) and (make_date(extract(year from (now() - '1 years'::interval))::int, 12, 31))

           ) as filter
      group by filter.tip_id, interval_1h) filtered
where broj_pati = (select max(filtered.broj_pati)
                   from (select filter.tip_id, interval_1h, count(tip_id || '-' || interval_1h) broj_pati
                         from (select *,
                                      (to_char(extract(hour from b.datum_kupuvanje), '00') || ':00 -' ||
                                       to_char(extract(hour from (b.datum_kupuvanje + '1 hours'::interval))::int,
                                               '00') ||
                                       ':00') as interval_1h
                               from tipbilet t
                                        left join bilet b on t.id = b.tip_id
--     where  b.datum_kupuvanje between (make_date(extract(year from (now() - '1 years'::interval))::int, 1, 1)) and (make_date(extract(year from (now() - '1 years'::interval))::int, 12, 31))

                              ) as filter
                         group by filter.tip_id, interval_1h) filtered)
  and tip_id in (select id
                 from tipbilet
                 where ime in ('Hourly', 'Daily'));

Total income (from ticket sales and fines)

create materialized view total_income as
WITH kazna_sum AS (
    SELECT
        EXTRACT(YEAR FROM k.date_payed) AS year,
        SUM(k.iznos) AS sum_kz
    FROM
        kazna k
    WHERE
        k.plateno = TRUE
    GROUP BY
        EXTRACT(YEAR FROM k.date_payed)
),
     bilet_sum AS (
         SELECT
             EXTRACT(YEAR FROM b.datum_kupuvanje) AS year,
             SUM(tb.cena) AS sum_b
         FROM
             bilet b
                 JOIN tipbilet tb ON tb.id = b.tip_id
         GROUP BY
             EXTRACT(YEAR FROM b.datum_kupuvanje)
     )
SELECT
    row_number() over () as id,
    COALESCE(k.year, b.year)::text AS year,
    COALESCE(sum_kz, 0) as fines_income,
    COALESCE(sum_b, 0) as ticket_income,
    COALESCE(sum_kz, 0) + COALESCE(sum_b, 0) AS total_income
FROM
    kazna_sum k
        FULL OUTER JOIN bilet_sum b ON k.year = b.year
ORDER BY
    year desc;

Интегритет

Тригери

create or replace function check_expiry_date() returns trigger
as
    $$
    begin
        if exists(
            select 1
            from vozenje v
            where v.patnik_id = new.patnik_id and v.end_date is null
        ) then
            update vozenje set end_date = now(), status = 'FINISHED' where id in (select v.id
                                                                                  from vozenje v
                                                                                  where v.patnik_id = new.patnik_id and v.end_date is null);
        end if;

        if exists(
            select 1
            from bilet
            where id = new.bilet_id and datum_aktivacija is null
        ) then
            update bilet set datum_aktivacija = now(), status = 'ACTIVE' where id = new.bilet_id;
        end if;

        if exists(
            select 1
            from bilet b
            join tipbilet tb on b.tip_id = tb.id
            where b.id = new.bilet_id and ((b.datum_aktivacija + (tb.trajnost || ' milliseconds')::interval) < now())
        ) then
            update bilet set status = 'EXPIRED' where id = new.bilet_id;
            end if;

        if exists(
            select 1
            from bilet b
                     join tipbilet tb on b.tip_id = tb.id
            where b.id = new.bilet_id and ((b.datum_aktivacija + (tb.trajnost || ' milliseconds')::interval) < now())
        ) then
            RAISE exception 'Ticket is expired';
        end if;
        return new;
    end;
    $$ language plpgsql;

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

Трансакции

@Transactional
    fun refreshAverageTimeDiffs() {
        val sql = "REFRESH MATERIALIZED VIEW CONCURRENTLY avg_time_diffs;"
        jdbcTemplate.execute(sql)
    }

    @Transactional
    fun refreshCommutesByHour() {
        val sql = "refresh materialized view concurrently most_busy_part_of_the_day;"
        jdbcTemplate.execute(sql)
    }

    @Transactional
    fun refreshFinesPerLine() {
        val sql = "refresh materialized view concurrently kazna_po_linija;"
        jdbcTemplate.execute(sql)
    }
    
    @Transactional
    fun refreshTotalIncome() {
        val sql = "refresh materialized view concurrently total_income;"
        jdbcTemplate.execute(sql)
    }

@Transactional
    fun createFine(request: FineRequest): FineResponse {
        val kontrola = kontrolaRepository.findByIdOrNull(request.kontrolaId) ?: throw NotFoundException("Kontrola")
        val now = Timestamp.valueOf(LocalDateTime.now())
        val kazna =
            kaznaRepository.save(
                Kazna(
                    dateCreated = now,
                    id = 0L,
                    kondukter = authService.getConductor(),
                    datePayed =
                        if (request.plateno) {
                            now
                        } else {
                            null
                        },
                    iznos = request.iznos,
                    dokument = request.dokument,
                    kontrola = kontrola,
                    plateno = request.plateno,
                ),
            )

        if (request.patnikId != null) {
            val korisnik = korisnikRepository.findByIdOrNull(request.patnikId!!) ?: throw NotFoundException("Korisnik")
            val patnik = patnikRepository.findByKorisnik(korisnik) ?: throw NotFoundException("Patnik")
            val kzr =
                kaznaZaRegistriranRepository.save(
                    KaznaZaRegistriran(
                        patnik = patnik,
                        id = 0L,
                        kazna = kazna,
                    ),
                )
            return kzr.let { dtoMapper.toFineResponse(kazna = kazna, kzr = kzr, kzn = null) }
        }

        if (request.adresa != null && request.ime != null && request.telefon != null) {
            val kzn =
                kaznaZaNeregistriranRepository.save(
                    KaznaZaNeregistriran(
                        id = 0L,
                        kazna = kazna,
                        ime = request.ime!!,
                        telefon = request.telefon!!,
                        adresa = request.adresa!!,
                    ),
                )

            return kzn.let { dtoMapper.toFineResponse(kazna = kazna, kzn = kzn, kzr = null) }
        }
        return dtoMapper.toFineResponse(kazna = kazna, kzr = null, kzn = null)
    }

@Transactional
    fun start(request: StartCommuteRequest): CommuteResponse {
        if (!this.authService.hasAuthority(RoleEnum.ROLE_PASSENGER)) throw UnauthorizedAccessException("Unauthorised role")
        val korisnik = this.authService.getAuthenticatedUser()
        val patnik = patnikRepository.findByKorisnik(korisnik) ?: throw NotFoundException("Korisnik")

        var bilet = biletRepository.findByIdAndPatnik(request.ticketId, patnik) ?: throw NotFoundException("Bilet")

        val instancaNaLinija =
            instancaNaLinijaRepository.findByIdOrNull(request.routeInstanceId) ?: throw NotFoundException("RouteInstanceId")
        val postojka = postojkaRepository.findByIdOrNull(request.stationId) ?: throw NotFoundException("StationId")

        val postojkaNaLinijaStart =
            postojkaNaLinijaRepository.findByLinijaAndPostojkaAndPravec(instancaNaLinija.linija, postojka, instancaNaLinija.pravec)
                ?: throw NotFoundException("Station not found")

        if (bilet.status == BiletEnum.INACTIVE) {
            bilet.status = BiletEnum.ACTIVE
            bilet.datumAktivacija = Timestamp.valueOf(LocalDateTime.now())
            bilet = biletRepository.save(bilet)
        }

        val vozenje =
            Vozenje(
                id = 0L,
                startDate = Timestamp.valueOf(LocalDateTime.now()),
                endDate = null,
                status = VozenjeStatus.ACTIVE,
                patnik = patnik,
                postojkaNaLinijaStart = postojkaNaLinijaStart,
                instancaNaLinija = instancaNaLinija,
                bilet = bilet,
            )
        return vozenjeRepository.save(vozenje).let { dtoMapper.toCommuteResponse(it) }
    }

Промена на моделот на базата

Моделот на базата е малку променет за разлика од претходните фази. Изоставена е табелата Mesta, која и во нормализацијата е изоставена поради тоа што не е во трета нормална форма. Додадени се нови табели: instanca_na_linija_postojka_na_linija, pravec, linija_pravec, role, korisnik_role.

• V1__init_db.sql​ - DDL за креирање на базата
• V1.1__data_init.sql​ - DML со првични податоци
• V1.2__additional_data.sql​ - DML со дополнителни податоци