1. Izveidot lietotāju

DROP PROCEDURE IF EXISTS create_user;
DELIMITER $$

CREATE PROCEDURE create_user(
    IN p_username VARCHAR(50),
    IN p_password VARCHAR(255)
)
BEGIN
    INSERT INTO users (username, password_hash, created_at)
    VALUES (p_username, SHA2(p_password,256), NOW());
END$$

DELIMITER ;

Izsaukšana DB:

CALL create_user('test', 'test');

--------------

2. Dzēst lietotāju

DROP PROCEDURE IF EXISTS delete_user;
DELIMITER $$

CREATE PROCEDURE delete_user(
    IN p_username VARCHAR(50)
)
BEGIN
    DELETE FROM users
    WHERE username = p_username;
END$$

DELIMITER ;

Izsaukšana DB:

CALL delete_user('test');

-------------

3. Modificēt lietotāju (mainīt paroli)

DROP PROCEDURE IF EXISTS update_user_password;
DELIMITER $$

CREATE PROCEDURE update_user_password(
    IN p_username VARCHAR(50),
    IN p_new_password VARCHAR(255)
)
BEGIN
    UPDATE users
    SET password_hash = SHA2(p_new_password,256)
    WHERE username = p_username;
END$$

DELIMITER ;

Izsaukšana DB:

CALL update_user_password('test', 'newpass');

---------------------------

4. Pārbaudīt lietotāja vārdu un paroli

DROP FUNCTION IF EXISTS check_user_credentials;
DELIMITER $$

CREATE FUNCTION check_user_credentials(
    p_username VARCHAR(50),
    p_password VARCHAR(255)
)
RETURNS TINYINT
DETERMINISTIC
BEGIN
    DECLARE result TINYINT;

    SELECT COUNT(*) INTO result
    FROM users
    WHERE username = p_username
      AND password_hash = SHA2(p_password,256);

    RETURN result;
END$$

DELIMITER ;

Izsaukšana DB:

SELECT check_user_credentials('test','test');

1 = pareizi, 0 = nepareizi

--------------------------

5. Pārbaudīt, vai lietotājs ir pieslēdzies

DROP FUNCTION IF EXISTS is_user_logged_in;
DELIMITER $$

CREATE FUNCTION is_user_logged_in(p_username VARCHAR(50))
RETURNS TINYINT
DETERMINISTIC
BEGIN
    DECLARE result TINYINT;

    SELECT
        CASE
            WHEN last_activity IS NOT NULL
             AND last_activity > NOW() - INTERVAL 5 MINUTE
            THEN 1
            ELSE 0
        END
    INTO result
    FROM users
    WHERE username = p_username;

    RETURN result;
END$$

DELIMITER ;

Izsaukšana DB:

SELECT is_user_logged_in('test');

1 = ir pieslēdzies, 0 = nav pieslēdzies