88 lines
2.2 KiB
MySQL
88 lines
2.2 KiB
MySQL
|
DELIMITER $$
|
||
|
|
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `cache`.`cache_calc_start`(OUT `v_calc` INT, INOUT `v_refresh` INT, IN `v_cache_name` VARCHAR(50), IN `v_params` VARCHAR(100))
|
||
|
|
proc: BEGIN
|
||
|
|
DECLARE v_valid BOOL;
|
||
|
|
DECLARE v_lock_id VARCHAR(100);
|
||
|
|
DECLARE v_cache_id INT;
|
||
|
|
DECLARE v_expires DATETIME;
|
||
|
|
DECLARE v_clean_time DATETIME;
|
||
|
|
DECLARE vLastRefresh DATETIME;
|
||
|
|
|
||
|
|
DECLARE EXIT HANDLER FOR SQLEXCEPTION
|
||
|
|
BEGIN
|
||
|
|
IF v_lock_id IS NOT NULL THEN
|
||
|
|
DO RELEASE_LOCK(v_lock_id);
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
RESIGNAL;
|
||
|
|
END;
|
||
|
|
|
||
|
|
SET v_params = IFNULL(v_params, '');
|
||
|
|
|
||
|
|
-- Si el servidor se ha reiniciado invalida todos los calculos.
|
||
|
|
|
||
|
|
SELECT COUNT(*) > 0 INTO v_valid FROM cache_valid;
|
||
|
|
|
||
|
|
IF !v_valid
|
||
|
|
THEN
|
||
|
|
DELETE FROM cache_calc;
|
||
|
|
INSERT INTO cache_valid (valid) VALUES (TRUE);
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
-- Obtiene un bloqueo exclusivo para que no haya problemas de concurrencia.
|
||
|
|
|
||
|
|
SET v_lock_id = CONCAT_WS('/', v_cache_name, v_params);
|
||
|
|
|
||
|
|
IF !GET_LOCK(v_lock_id, 30)
|
||
|
|
THEN
|
||
|
|
SET v_calc = NULL;
|
||
|
|
SET v_refresh = FALSE;
|
||
|
|
LEAVE proc;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
-- Comprueba si el calculo solicitado existe y esta actualizado.
|
||
|
|
|
||
|
|
SELECT c.id, ca.id, ca.expires, ca.last_refresh
|
||
|
|
INTO v_cache_id, v_calc, v_expires, vLastRefresh
|
||
|
|
FROM cache c
|
||
|
|
LEFT JOIN cache_calc ca
|
||
|
|
ON ca.cache_id = c.id AND ca.params = v_params COLLATE 'utf8_general_ci'
|
||
|
|
WHERE c.name = v_cache_name COLLATE 'utf8_general_ci';
|
||
|
|
|
||
|
|
-- Si existe una calculo valido libera el bloqueo y devuelve su identificador.
|
||
|
|
|
||
|
|
IF !v_refresh AND NOW() < v_expires AND vLastRefresh >= CURDATE()
|
||
|
|
THEN
|
||
|
|
DO RELEASE_LOCK(v_lock_id);
|
||
|
|
SET v_refresh = FALSE;
|
||
|
|
LEAVE proc;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
-- Si el calculo no existe le crea una entrada en la tabla de calculos.
|
||
|
|
|
||
|
|
IF v_calc IS NULL
|
||
|
|
THEN
|
||
|
|
INSERT INTO cache_calc SET
|
||
|
|
cache_id = v_cache_id,
|
||
|
|
cacheName = v_cache_name,
|
||
|
|
params = v_params,
|
||
|
|
last_refresh = NULL,
|
||
|
|
expires = NULL,
|
||
|
|
connection_id = CONNECTION_ID();
|
||
|
|
|
||
|
|
SET v_calc = LAST_INSERT_ID();
|
||
|
|
ELSE
|
||
|
|
UPDATE cache_calc
|
||
|
|
SET
|
||
|
|
last_refresh = NULL,
|
||
|
|
expires = NULL,
|
||
|
|
connection_id = CONNECTION_ID()
|
||
|
|
WHERE id = v_calc;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
-- Si se debe recalcular mantiene el bloqueo y devuelve su identificador.
|
||
|
|
|
||
|
|
SET v_refresh = TRUE;
|
||
|
|
END$$
|
||
|
|
DELIMITER ;
|