| Version 10 (modified by , 5 weeks ago) ( diff ) |
|---|
Напреден апликативен дизајн
Напредниот апликативен дизајн има за цел да ги примени сите опишани кориснички сценарија и улоги од првите фази во финалната апликација. За таа цел создадена е нова апликација која користи модерни технологии.
За базата повторно се користи 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 со дополнителни податоци
Attachments (7)
- number-of-activities-per-hour.png (36.4 KB ) - added by 5 weeks ago.
- fines-per-line.png (25.3 KB ) - added by 5 weeks ago.
- total-income.png (21.3 KB ) - added by 5 weeks ago.
- new-model.png (242.9 KB ) - added by 5 weeks ago.
- V1__init_db.sql (10.5 KB ) - added by 5 weeks ago.
- V1.2__additional_data.sql (11.9 KB ) - added by 5 weeks ago.
- V1.1__data_init.sql (16.6 KB ) - added by 5 weeks ago.
Download all attachments as: .zip
Note:
See TracWiki
for help on using the wiki.
