🧠 Ćwiczenia z SQL - Programowanie i Optymalizacja Baz Danych

START TRANSACTION;
-- Użyj konkretnego ID do testu, np. ID=1 (Kowalski, 90 pkt)
UPDATE gracze
SET punkty = punkty + 10
WHERE id = 1 AND punkty < 100;

-- Sprawdzenie, czy aktualizacja się powiodła
SELECT punkty FROM gracze WHERE id = 1; 

COMMIT; -- Zatwierdzenie zmian
-- ROLLBACK; -- Alternatywnie, wycofanie zmian

DELIMITER //
CREATE PROCEDURE aktualizuj_ranking()
BEGIN
    DECLARE koniec INT DEFAULT 0;
    DECLARE id_gracza INT;
    
    -- Definicja Kursora
    DECLARE cur CURSOR FOR SELECT id FROM gracze ORDER BY punkty DESC;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET koniec = 1;

    SET @ranking = 1;
    OPEN cur;
    petla: LOOP
        FETCH cur INTO id_gracza;
        IF koniec THEN
            LEAVE petla;
        END IF;
        
        -- Aktualizacja rankingu
        UPDATE gracze SET ranking = @ranking WHERE id = id_gracza;
        SET @ranking = @ranking + 1;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;
-- Wywołanie: CALL aktualizuj_ranking();

DELIMITER //
CREATE TRIGGER blokada_meczy
BEFORE INSERT ON mecze
FOR EACH ROW
BEGIN
    DECLARE rozegrane INT;
    
    SELECT COUNT(*) INTO rozegrane 
    FROM mecze 
    WHERE id_gracza = NEW.id_gracza;
    
    IF rozegrane >= 100 THEN
        -- Wygenerowanie błędu, który przerwie INSERT
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Gracz osiągnął limit 100 meczów.';
    END IF;
END //
DELIMITER ;

DELIMITER //
CREATE FUNCTION liczba_meczy(gracz_id INT) 
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE liczba INT;
    SELECT COUNT(*) INTO liczba FROM mecze WHERE id_gracza = gracz_id;
    RETURN liczba;
END //
DELIMITER ;
-- Wywołanie: SELECT liczba_meczy(1);

CREATE INDEX idx_punkty_desc ON gracze(punkty DESC);

-- 1. Usuń rekordy zależne
DELETE FROM mecze WHERE id_gracza = 1; 

-- 2. Usuń gracza
DELETE FROM gracze WHERE id = 1;

-- UWAGA: Aby usunięcie rekordów zależnych było automatyczne, 
-- należy dodać ON DELETE CASCADE do definicji klucza obcego przy tworzeniu tabeli.

<?php
// Zakładamy, że połączenie PDO jest już skonfigurowane
$pdo = new PDO('mysql:host=localhost;dbname=esport', 'root', '');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$czas = $_GET['czas'] ?? 30; // Domyślnie 30 dni, ale można podmienić

$stmt = $pdo->prepare("
    SELECT nazwa, punkty, data_ostatniego_punkty
    FROM gracze
    WHERE data_ostatniego_punkty >= DATE_SUB(NOW(), INTERVAL :czas DAY)
    ORDER BY punkty DESC
");

// Parametryzacja zapytania - BEZPIECZEŃSTWO
$stmt->bindParam(':czas', $czas, PDO::PARAM_INT);
$stmt->execute();

echo '<h2>Raport Rankingowy (Aktywni)';
echo '<table>';
echo '<tr><th>Nazwa<th>Punkty<th>Ostatni Punkt';
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo '<tr>';
    echo '<td>' . htmlspecialchars($row['nazwa']) . '';
    echo '<td>' . htmlspecialchars($row['punkty']) . '';
    echo '<td>' . htmlspecialchars($row['data_ostatniego_punkty']) . '';
    echo '</tr>';
}
echo '</table>';
?>

<?php
// Zabezpieczony kod logowania z Prepared Statements
$pdo = new PDO('mysql:host=localhost;dbname=esport', 'root', '');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$username = $_POST['username'] ?? '';
$password = $_POST['password'] ?? '';

// Użycie placeholderów (?) lub nazwanego placeholdera (:nazwa)
$stmt = $pdo->prepare('SELECT id FROM gracze WHERE username = :username AND password = :password');

// Właściwe zabezpieczenie: Wartości są przekazywane ODDZIELNIE od zapytania.
$stmt->bindParam(':username', $username);
$stmt->bindParam(':password', $password);
$stmt->execute();

if ($stmt->rowCount() > 0) {
    echo 'Zalogowano pomyślnie!';
} else {
    echo 'Błędne dane logowania.';
}
?>

SELECT g.nazwa, COUNT(m.id) AS liczba_meczy
FROM gracze g
LEFT JOIN mecze m ON g.id = m.id_gracza
GROUP BY g.nazwa;