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

Имплементирани се сите случаи на употреба дефинирани во фаза 3.

Примери за индекси

Индексите се користат за забрзување на пребарувања што често се изведуваат. Во Travel Sage, се прави пребарување на активности според период, филтрирање на резервации по корисник, како и пребарување на дестинации според име. Со додавање индекси на овие колони, апликацијата работи побрзо и поефикасно.

• За побрзо пребарување на активности во одреден период

  CREATE INDEX idx_activity_dates ON activity(start_date, end_date);
  

• За побрз join и филтрирање по корисник при резервации

  CREATE INDEX idx_reservation_user ON reservation(id_user);
  

• За побрзо пребарување на дестинации по име на локација

  CREATE INDEX idx_destination_location_name ON destination(location_name);
  

• Демонстрација на користење на индекс

  EXPLAIN ANALYZE
  SELECT * FROM activity
  WHERE start_date >= '2025-01-01' AND end_date <= '2025-12-31';
  

Примери за тригери

Тригерите се користат за автоматска реакција на промена во базата. Во TravelSage, тие се применети за автоматска реакција на негативна рецензија.

1. Деактивирање дестинација со негативни рецензии

Дестинација се означува како is_flagged само ако има најмалку 5 рецензии и просечната оцена е под 3.0. Користејќи функција recalc_destination_flag и тригери што реагираат на INSERT, UPDATE и DELETE во табелата review. Предност: не се брише/сокрива содржината од корисникот, но се дава сигнал (за филтрирање или admin action) со стабилен праг.

ALTER TABLE destination
ADD COLUMN IF NOT EXISTS is_flagged BOOLEAN NOT NULL DEFAULT FALSE;

CREATE OR REPLACE FUNCTION recalc_destination_flag(p_dest INT)
RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
    avg_rating FLOAT;
    total_reviews INT;
BEGIN
    SELECT AVG(quality), COUNT(*) INTO avg_rating, total_reviews
    FROM review
    WHERE id_destination = p_dest;

    IF total_reviews >= 5 AND COALESCE(avg_rating,0) < 3 THEN
        UPDATE destination SET is_flagged = TRUE WHERE id_destination = p_dest;
    ELSE
        UPDATE destination SET is_flagged = FALSE WHERE id_destination = p_dest;
    END IF;
END;
$$;

CREATE OR REPLACE FUNCTION trg_recalc_dest_flag_trigger()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    PERFORM recalc_destination_flag(NEW.id_destination) WHEN (TG_OP = 'INSERT' OR TG_OP = 'UPDATE');
    IF TG_OP = 'DELETE' THEN
        PERFORM recalc_destination_flag(OLD.id_destination);
    END IF;
    RETURN COALESCE(NEW, OLD);
END;
$$;

DROP TRIGGER IF EXISTS trg_review_after_insert ON review;
CREATE TRIGGER trg_review_after_insert
AFTER INSERT ON review
FOR EACH ROW EXECUTE FUNCTION trg_recalc_dest_flag_trigger();

DROP TRIGGER IF EXISTS trg_review_after_update ON review;
CREATE TRIGGER trg_review_after_update
AFTER UPDATE ON review
FOR EACH ROW EXECUTE FUNCTION trg_recalc_dest_flag_trigger();

DROP TRIGGER IF EXISTS trg_review_after_delete ON review;
CREATE TRIGGER trg_review_after_delete
AFTER DELETE ON review
FOR EACH ROW EXECUTE FUNCTION trg_recalc_dest_flag_trigger();

Примери за прегледи

View се користи за да се поедностават комплексни пребарувања.

1. Во TravelSage, прикажани се активности што се „најдобра вредност за пари“, базирано на цена и просечна оцена од рецензии.

   CREATE OR REPLACE VIEW best_value_activities AS
   SELECT 
       a.id_activity,
       a.activity_name,
       a.amount,
       d.location_name,
       AVG(r.quality) AS avg_rating
   FROM activity a
   JOIN destination d ON a.id_destination = d.id_destination
   JOIN review r ON a.id_activity = r.id_destination 
   GROUP BY a.id_activity, a.activity_name, a.amount, d.location_name
   HAVING AVG(r.quality) > 4 AND a.amount < 1000;
   

2. Активности со најниски цени

Го пресметува процентот на ефтини активности по дестинација – корисно за филтрирање при буџетско патување.

CREATE OR REPLACE VIEW view_procent_cheap_destinations AS
SELECT
    d.location_name,
    COUNT(CASE WHEN a.amount < 500 THEN 1 END) * 100.0 / COUNT(*) AS procent_cheap
FROM destination d
JOIN activity a ON d.id_destination = a.id_destination
GROUP BY d.id_destination, d.location_name
ORDER BY procent_cheap DESC;

Трансакции

1. Пример при резервација на активност

Ако не успее било кој дел (резервација или ажурирање на квота), не се зачувува ништо – атомичност.

DB::transaction(function () use ($request) {
    DB::table('activity_reservation')->insert([
        'id_user' => auth()->id(),
        'id_activity' => $request->activity_id,
        'reservation_date' => now()
    ]);

    DB::table('activity')
        ->where('id_activity', $request->activity_id)
        ->decrement('amount', 1);
});

2. Внесување на нови податоци во табелите за пакети, настани и активности

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

// Внесување TravelPackage
DB::transaction(function () use ($request) {
    $validatedData = $request->validate([
        'package_name' => 'required|string|max:255',
        'price' => 'required|numeric',
        'start_date' => 'required|date_format:Y-m-d\TH:i',
        'end_date' => 'required|date_format:Y-m-d\TH:i|after_or_equal:start_date',
        'id_destination' => 'required|integer|exists:destination,id_destination'
    ]);

    TravelPackage::create($validatedData);
});

// Внесување TravelEvent
DB::transaction(function () use ($request) {
    $validatedData = $request->validate([
        'event_name' => 'required|string|max:255',
        'event_type' => 'required|string|max:255',
        'details' => 'nullable|string',
        'start_date' => 'required|date',
        'end_date' => 'required|date',
        'id_destination' => 'required|integer|exists:destination,id_destination'
    ]);

    TravelEvent::create($validatedData);
});

// Внесување TravelActivity
DB::transaction(function () use ($request) {
    $validatedData = $request->validate([
        'activity_name' => 'required|string|max:255',
        'information' => 'nullable|string|max:255',
        'category' => 'required|string|max:255',
        'amount' => 'nullable|numeric',
        'id_destination' => 'required|integer|exists:destination,id_destination'
    ]);

    TravelActivity::create($validatedData);
});

Складирани функции и процедури

Функциите и процедурите се дефинирани во базата и можат да се повикуваат од апликацијата. На тој начин се централизира логиката и се олеснува одржувањето.

1. Просечна оцена за локација (функција)

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

CREATE OR REPLACE FUNCTION avg_rating_for_location(dest_id INT)
RETURNS FLOAT AS $$
DECLARE
    result FLOAT;
BEGIN
    SELECT AVG(quality)
    INTO result
    FROM review
    WHERE id_destination = dest_id;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

2. Додади препорака (процедура)

   CREATE OR REPLACE PROCEDURE dodadi_preporaka(p_user INT, p_dest INT)
   LANGUAGE plpgsql
   AS $$
   BEGIN
       INSERT INTO destination_user(id_user, id_destination, recommendation_date)
       VALUES (p_user, p_dest, NOW());
   END;
   $$;
   

3. Комплетна резервација за пакет во трансакција

   CREATE OR REPLACE PROCEDURE create_package_reservation(
       p_user INT,
       p_package INT,
       p_discount_amount NUMERIC DEFAULT 0
   )
   LANGUAGE plpgsql
   AS $$
   DECLARE
       package_price NUMERIC(12,2);
       new_reservation INT;
   BEGIN
       SELECT price INTO package_price FROM package WHERE id_package = p_package;
   
       IF package_price IS NULL THEN
           RAISE EXCEPTION 'Package % not found', p_package;
       END IF;
   
       INSERT INTO reservation(id_user, time_point, premium_discount_applied, discount_amount, total_price)
       VALUES (p_user, now(), CASE WHEN p_discount_amount > 0 THEN TRUE ELSE FALSE END, p_discount_amount, GREATEST(package_price - p_discount_amount, 0))
       RETURNING id_reservation INTO new_reservation;
   
       INSERT INTO package_reservation(id_package, id_reservation)
       VALUES (p_package, new_reservation);
   END;
   $$;
   

Релациска алгебра

Најпопуларни дестинации по просечна оцена и број на резервации

SELECT d.location_name, AVG(r.quality) AS avg_rating, COUNT(res.id_reservation) AS total_res 
FROM destination d 
JOIN review r ON d.id_destination = r.id_destination 
JOIN reservation res ON d.id_destination = res.id_destination 
GROUP BY d.location_name 
HAVING AVG(r.quality) > 4 AND COUNT(res.id_reservation) > 20;


π_{d.location_name, avg_rating, total_res} (
γ_{d.location_name; avg_rating ← AVG(r.quality), total_res ← COUNT(res.id_reservation)} (
(destination ⨝ review ⨝ reservation)
)
σ{avg_rating > 4 ∧ total_res > 20}
)

Линк до репозиториумот

​https://develop.finki.ukim.mk/git/travel_sage.git

​https://github.com/sandrailievskaa/TravelSage