mariadb-as-cache.sql

############################################################################################
# 1. Create the operational table (users) and the cache table (users_session_cache).
# Make sure that the cache table uses MariaDB's MEMORY storage engine.
############################################################################################

CREATE OR REPLACE TABLE users(
	id SERIAL PRIMARY KEY,
	name VARCHAR(50) NOT NULL UNIQUE,
	session_id VARCHAR(50)
) ENGINE=InnoDB;

CREATE OR REPLACE TABLE users_session_cache (
	name VARCHAR(50) NOT NULL PRIMARY KEY REFERENCES users(id),
	session_id VARCHAR(50),
	last_updated DATETIME NOT NULL
		DEFAULT CURRENT_TIMESTAMP
		ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MEMORY;


############################################################################################
# 2. Create the events that will keep the cache table up-to-date. Adjust the intervals as
# needed. Make sure to activate the event scheduler if you haven't done so already:
# 	SET GLOBAL event_scheduler = ON;
############################################################################################

CREATE OR REPLACE EVENT ev_remove_stale_user_cache_entries
    ON SCHEDULE EVERY 1 SECOND DO
		DELETE FROM users_session_cache
		WHERE NOW() > last_updated + INTERVAL 30 SECOND;

CREATE OR REPLACE EVENT ev_update_user_cache_entries
    ON SCHEDULE EVERY 1 SECOND DO
		UPDATE users_session_cache c
		JOIN users u USING (name)
		SET c.session_id = u.session_id,
			c.last_updated = NOW()
		WHERE c.session_id != u.session_id;


############################################################################################
# 3. Create the stored procedure that will be used to log in users.
# This procedure simply inserts a new user or updates an existing one with a random session
# ID.
############################################################################################

DELIMITER //
CREATE OR REPLACE PROCEDURE login(user_name VARCHAR(50))
BEGIN
	SET @session_id = LEFT(UUID(), 8);
	INSERT INTO users(name, session_id)
	VALUES (user_name, @session_id)
	ON DUPLICATE KEY UPDATE
		session_id = @session_id;
END
DELIMITER ;

############################################################################################
# 4. Create a stored function to read the session ID of a user.
# This function first checks the cache table and, if the session ID is not found, it
# retrieves it from the users table and updates the cache table.
############################################################################################

DELIMITER //
CREATE OR REPLACE FUNCTION read_session_id(user_name VARCHAR(50)) RETURNS VARCHAR(50)
BEGIN
	DECLARE sid VARCHAR(50);

	SELECT session_id INTO sid
	FROM users_session_cache
	WHERE name = user_name;

	IF sid IS NULL THEN
		SELECT session_id INTO sid
		FROM users
		WHERE name = user_name;

		IF sid IS NOT NULL THEN
			INSERT INTO users_session_cache(name, session_id, last_updated)
			VALUES (user_name, sid, NOW());
		END IF;
	END IF;

	RETURN sid;
END
DELIMITER ;


############################################################################################
# 5. Time to play! First, call the login procedure for a few users.
############################################################################################

CALL login("Maria");
CALL login("John");
CALL login("Alejandro");
CALL login("Jane");


############################################################################################
# 6. Before continuing, open a couple of extra terminals and watch the contents of the
# tables. For example, on Linux, you can use the watch command to run the queries
# constantly:
#
# watch --interval 0.3 --no-title -e "echo "users:"; mariadb -h 127.0.0.1 -P 3306 -u user
# 	-p'Password123!' -e'select * from users' --table --ssl=0 demo;"
#
# watch --interval 0.3 --no-title -e "echo "users:"; mariadb -h 127.0.0.1 -P 3306 -u user
#	-p'Password123!' -e'select * from users_session_cache' --table --ssl=0 demo;"
#
# Adjust the connection parameters and database name (demo) according to your setup.
# Then run the following queries to see how entries are added to the cache table (cache
# miss) and how reads skip the operational table (cache hit).
############################################################################################

SELECT read_session_id("Maria");		-- cache miss
SELECT read_session_id("John");			-- cache miss
SELECT read_session_id("Alejandro");	-- cache miss
SELECT read_session_id("Jane");			-- cache miss
SELECT read_session_id("Jane");			-- cache hit!


############################################################################################
# 7. See the cash update in action. Call the login function again for some of the users.
# You should see the cache table being updated automatically.
############################################################################################

CALL login("Alejandro");
CALL login("Jane");


############################################################################################
# 8. Finally, see the cache eviction in action. Wait a few seconds and see how the cache
# table automatically removes entries.
############################################################################################

-- Nothing to run here. Just wait and watch the cache table.