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

Имплементирани се сите случаи на употреба дефинирани во фаза 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();

2. Тежинска просечна оцена, каде што помал број рецензии имаат помала тежина - без деактивирање, само со можност за admin да ги филтрира.

   CREATE OR REPLACE FUNCTION recalc_destination_flag_weighted(p_dest INT)
   RETURNS VOID LANGUAGE plpgsql AS $$
   DECLARE
       avg_rating FLOAT;
       total_reviews INT;
       weight_rating FLOAT;
   BEGIN
       SELECT AVG(quality), COUNT(*) INTO avg_rating, total_reviews
       FROM review
       WHERE id_destination = p_dest;
   
       IF total_reviews < 5 THEN
           weight_rating := avg_rating * (total_reviews::FLOAT / 5);
       ELSE
           weight_rating := avg_rating;
       END IF;
   
       IF total_reviews >= 1 AND COALESCE(weight_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;
   $$;
   

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

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);
});

3. Се вметнуваат:

• TravelPackage
• TravelActivity за секоја активност
• activity_reservation за секоја активност
• Reservation за целиот пакет
• Се намалува amount за секоја активност

  DB::transaction(function () use ($request) {
      $packageData = $request->validate([
          'package_name' => 'required|string|max:255',
          'price' => 'required|numeric',
          'start_date' => 'required|date',
          'end_date' => 'required|date|after_or_equal:start_date',
          'id_destination' => 'required|integer|exists:destination,id_destination'
      ]);
  
      $package = TravelPackage::create($packageData);
  
      foreach ($request->activities as $activityData) {
          $validatedActivity = validator($activityData, [
              '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'
          ])->validate();
  
          $activity = TravelActivity::create($validatedActivity);
  
          DB::table('activity_reservation')->insert([
              'id_user' => auth()->id(),
              'id_activity' => $activity->id_activity,
              'reservation_date' => now()
          ]);
  
          DB::table('activity')->where('id_activity', $activity->id_activity)->decrement('amount', 1);
  
          DB::table('package_activity')->insert([
              'id_package' => $package->id_package,
              'id_activity' => $activity->id_activity
          ]);
      }
  
      Reservation::create([
          'id_user' => auth()->id(),
          'id_package' => $package->id_package,
          'time_point' => now()
      ]);
  });
  

4. Пакет со повеќе активности

   DB::transaction(function () use ($request) {
       $package = TravelPackage::create([...]);
       foreach ($request->activities as $activity) {
           $act = TravelActivity::create([...]);
           DB::table('activity_reservation')->insert([
               'id_user' => auth()->id(),
               'id_activity' => $act->id_activity,
               'reservation_date' => now()
           ]);
           DB::table('activity')->where('id_activity', $act->id_activity)->decrement('amount', 1);
       }
       Reservation::create([
           'id_user' => auth()->id(),
           'id_package' => $package->id_package,
           'time_point' => now()
       ]);
   });
   

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

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

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

4. Композитна метрика за дестинација: просечна оцена, процент на ефтини активности, број на резервации. Се користи за рангирање на дестинации.

   CREATE OR REPLACE FUNCTION destination_score(p_dest INT)
   RETURNS FLOAT LANGUAGE plpgsql AS $$
   DECLARE
       avg_rating FLOAT;
       cheap_percent FLOAT;
       total_reservations INT;
       score FLOAT;
   BEGIN
       SELECT AVG(quality) INTO avg_rating FROM review WHERE id_destination = p_dest;
       SELECT COUNT(*) * 100.0 / NULLIF((SELECT COUNT(*) FROM activity WHERE id_destination = p_dest),0)
           INTO cheap_percent FROM activity WHERE id_destination = p_dest AND amount < 500;
       SELECT COUNT(*) INTO total_reservations FROM reservation WHERE id_destination = p_dest;
   
       score := COALESCE(avg_rating,0)*0.5 + COALESCE(cheap_percent,0)*0.3 + COALESCE(total_reservations,0)*0.2;
       RETURN score;
   END;
   $$;
   

5. Селектира дестинации по score, користејќи ја функцијата destination_score(id_destination) и потоа ги сортира дестинациите од највисок score кон најнизок. Со LIMIT 5 се избираат само топ 5 дестинации со највисок score.

   CREATE OR REPLACE PROCEDURE recommend_top_destinations(p_user INT)
   LANGUAGE plpgsql AS $$
   DECLARE
       dest RECORD;
   BEGIN
       FOR dest IN
           SELECT id_destination
           FROM destination
           ORDER BY destination_score(id_destination) DESC
           LIMIT 5
       LOOP
           INSERT INTO destination_user(id_user, id_destination, recommendation_date)
           VALUES (p_user, dest.id_destination, NOW());
       END LOOP;
   END;
   $$;
   
   

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

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

   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}
   )
   

Прикажување на дестинации кои имаат:

• висока просечна оцена (avg_rating > 4),
• голем процент на ефтини активности (cheap_percent > 50%),
• доволен број на резервации (total_res > 20).

SQL концепт

• AVG(r.quality) → просечна оцена по дестинација
• COUNT(CASE WHEN a.amount < 500 THEN 1 END) * 100 / COUNT(*) → процент на ефтини активности
• COUNT(res.id_reservation) → број на резервации
• Филтрирање со HAVING за условите за висока оценка, ефтини активности и број на резервации

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

πd.location_name,avg_rating,cheap_percent, 
total_res​( σavg_rating>4∧cheap_percent>50∧total_res>20 (γd.location_name;avg_rating←AVG(r.quality),
cheap_percent←COUNT(activity)COUNT(σa.amount<500​(activity))​
∗100,total_res←COUNT(res.id_reservation)​(destination⋈activity⋈review⋈reservation)))

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

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

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