Application Development
Scope
This phase concerns itself with implementing the use cases defined in P3 of the project, found at: https://develop.finki.ukim.mk/projects/routemk/wiki/ApplicationDesign
Source
The source code for the application can be found at the following link https://github.com/KikoTheFinker/RouteMK, and includes setup through:
- Docker (recommended, especially for the DB)
- Native (no containers)
Transactions
Transactions are used from Spring Boot specifically in these files:
Indexes
To optimize query performance and reduce full table scans as data volume grows, the following indexes were added based on real usage patterns in analytical queries, views, and joins.
- Most queries join trip and route using route_id, for example in company_performance_view, top_selling_routes_view, ticket statistics, and weighted usage reports. Without this index, every join would trigger a sequential scan of the trip table. With the index, the database can directly locate all trips for a given route.
CREATE INDEX idx_trip_route_id ON trip(route_id);
- These two indexes accelerate lookups and joins involving route endpoints. They support queries that find trips between specific locations, which for our application is especially iseful to detect subroutes, one of the most important features. They're also useful in displaying routes with origin - destination metadata.
CREATE INDEX idx_route_from_location_id ON route(from_location_id); CREATE INDEX idx_route_to_location_id ON route(to_location_id);
- Filtering by trip status (e.g., 'COMPLETED', 'NOT_STARTED') is common in ticket statistics and reporting queries. This index ensures that the database can filter relevant trips quickly instead of scanning the full trip table. It's especially important in analytical use cases and dashboards.
CREATE INDEX idx_trip_status ON trip(status);
- Views such as company_performance_view and top_selling_routes_view frequently join route and transport_organizer through transport_organizer_id. This index speeds up company-based route lookups, revenue calculations and per-organizer reporting. So, this index is useful for when we're calculating company metrics/revenue by organizer.
CREATE INDEX idx_route_transport_organizer_id ON route(transport_organizer_id);
Triggers
1. Trigger to update the number of free seats based on ticket insertion/deletion
This trigger automatically adjusts the number of available seats in a trip whenever a ticket is inserted or deleted. On insert, it decreases the free_seats count by one, and on delete, it increases it by one. This ensures that trip capacity stays accurate without requiring manual updates.
CREATE OR REPLACE FUNCTION update_free_seats()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
UPDATE trip
SET free_seats = free_seats - 1
WHERE trip_id = NEW.trip_id;
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
UPDATE trip
SET free_seats = free_seats + 1
WHERE trip_id = OLD.trip_id;
RETURN OLD;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER ticket_insert_update_seats
AFTER INSERT ON ticket
FOR EACH ROW
EXECUTE FUNCTION update_free_seats();
CREATE TRIGGER ticket_delete_update_seats
AFTER DELETE ON ticket
FOR EACH ROW
EXECUTE FUNCTION update_free_seats();
2. Ticket discount and total price triggers
These two triggers work together in a way that the discount triggers first calculates and applies the reduced ticket price before the ticket is officially inserted into the database. Once the discounted ticket is saved, the payment triggers recalculate the total payment by summing all ticket prices linked to the same payment record, ensuring the final total reflects the applied discount.
CREATE OR REPLACE FUNCTION apply_ticket_discount()
RETURNS TRIGGER AS $$
BEGIN
UPDATE ticket
SET price = tr.base_price - (tr.base_price * NEW.discount / 100)
FROM trip tr
WHERE ticket.ticket_id = NEW.ticket_id AND ticket.trip_id = tr.trip_id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER apply_student_ticket_discount
BEFORE INSERT ON student_ticket
FOR EACH ROW EXECUTE FUNCTION apply_ticket_discount();
CREATE TRIGGER apply_child_ticket_discount
BEFORE INSERT ON child_ticket
FOR EACH ROW EXECUTE FUNCTION apply_ticket_discount();
-- UPDATE TOTAL PAYMENT TOTAL PRICE (TRIGGER AFTER CHILD/STUDENT DISCOUNT IS APPLIED)
CREATE OR REPLACE FUNCTION update_payment_total()
RETURNS TRIGGER AS $$
BEGIN UPDATE payment
SET total_price = ( SELECT COALESCE(SUM(price), 0)
FROM ticket
WHERE payment_id = NEW.payment_id)
WHERE payment_id = NEW.payment_id;
RETURN NEW; END; $$
LANGUAGE plpgsql;
CREATE TRIGGER trg_update_payment_total_insert
AFTER INSERT ON ticket
FOR EACH ROW EXECUTE FUNCTION update_payment_total();
CREATE TRIGGER trg_update_payment_total_update
AFTER UPDATE OF price, payment_id ON ticket
FOR EACH ROW EXECUTE FUNCTION update_payment_total();
CREATE TRIGGER trg_update_payment_total_delete
AFTER DELETE ON ticket
FOR EACH ROW EXECUTE FUNCTION update_payment_total();
3. Trigger to automatically set trip status
This trigger assigns the default status 'NOT_STARTED' to any new trip that doesn't explicitly define a status. It guarantees consistent initialization of trip state at creation time. This prevents null or undefined statuses in downstream queries and logic.
CREATE OR REPLACE FUNCTION set_default_trip_status()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.status IS NULL THEN
NEW.status = 'NOT_STARTED';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_set_default_trip_status
BEFORE INSERT ON trip
FOR EACH ROW EXECUTE FUNCTION set_default_trip_status();
4. Trigger to update trip status
This function adjusts a trip's status based on available seats and trip date. It sets the trip to 'FULL' when no seats remain or resets it to 'NOT_STARTED' if seats reopen. If the trip date is in the past, it marks the trip as 'COMPLETED' to reflect its final state.
CREATE OR REPLACE FUNCTION update_trip_status()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.free_seats = 0 THEN
NEW.status = 'FULL';
ELSIF NEW.free_seats > 0 AND OLD.status = 'FULL' THEN
NEW.status = 'NOT_STARTED';
END IF;
IF NEW.date < CURRENT_DATE THEN
NEW.status = 'COMPLETED';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_update_trip_status
BEFORE UPDATE OF free_seats, date ON trip
FOR EACH ROW
EXECUTE FUNCTION update_trip_status();
5. Trigger to automatically set the ticket purchase time and date
This trigger ensures that every ticket has a purchase date and time set automatically. If either field is missing at insertion, it fills them with the current date and time. It prevents incomplete timestamps and simplifies data entry.
CREATE OR REPLACE FUNCTION set_purchase_timestamp()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.date_purchased IS NULL THEN
NEW.date_purchased = CURRENT_DATE;
END IF;
IF NEW.time_purchased IS NULL THEN
NEW.time_purchased = CURRENT_TIME;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_set_purchase_timestamp
BEFORE INSERT ON ticket
FOR EACH ROW EXECUTE FUNCTION set_purchase_timestamp();
