lwb6/legacyworlds-server-data/db-structure/parts/050-updates/000-updates-ctrl.sql

-- LegacyWorlds Beta 6
-- PostgreSQL database scripts
--
-- Game updates - control functions
--
-- Copyright(C) 2004-2010, DeepClone Development
-- --------------------------------------------------------



/*
 * Start a game update cycle
 * --------------------------
 *
 * This function prepares the execution of a cycle of game updates. It will
 * try to find the identifier of the next update, and mark all game updates
 * as requiring an update for this identifier.
 * 
 * Returns:
 *		tick_id		The identifier of the new update cycle, or NULL if an
 *						update was already in progress. 
 */
DROP FUNCTION IF EXISTS sys.start_tick( ) CASCADE;
CREATE FUNCTION sys.start_tick( OUT tick_id BIGINT )
		LANGUAGE PLPGSQL
		STRICT VOLATILE
		SECURITY DEFINER
	AS $start_tick$

DECLARE
	n_tick	BIGINT;
	c_tick	BIGINT;

BEGIN
	-- Get next / current tick
	SELECT INTO n_tick , c_tick next_tick , current_tick
		FROM sys.status
		WHERE maintenance_start IS NULL
		FOR UPDATE;
	IF NOT FOUND OR c_tick IS NOT NULL THEN
		tick_id := NULL;
		RETURN;
	END IF;

	-- Prepare game updates
	UPDATE sys.updates
		SET update_last = n_tick ,
			update_state = 'FUTURE'
		WHERE update_last < n_tick;

	-- Update system status
	UPDATE sys.status
		SET current_tick = n_tick ,
			next_tick = n_tick + 1;
	
	tick_id := n_tick;

END;
$start_tick$;

REVOKE EXECUTE
	ON FUNCTION sys.start_tick( )
	FROM PUBLIC;
GRANT EXECUTE
	ON FUNCTION sys.start_tick( )
	TO :dbuser;



--
-- Marks a tick as completed
--

CREATE OR REPLACE FUNCTION sys.end_tick( IN tick_id BIGINT )
		RETURNS VOID
		STRICT VOLATILE
		SECURITY INVOKER
	AS $$
BEGIN
	UPDATE events.events SET status = 'READY'
		WHERE status = 'TICK' AND tick = tick_id;
	UPDATE sys.status SET current_tick = NULL;
	PERFORM msgs.deliver_internal( );
END;
$$ LANGUAGE plpgsql;



/*
 * Check if a game update cycle got "stuck"
 * -----------------------------------------
 * 
 * Check sys.status for an in-progress game update identifier. If there is
 * one, check if there are further updates to execute in the cycle in
 * question.
 * 
 * Returns:
 *		tick_id		The stuck tick's identifier, or NULL if there is no stuck
 *						tick or if maintenance mode is enabled  
 */
DROP FUNCTION IF EXISTS sys.check_stuck_tick( ) CASCADE;
CREATE FUNCTION sys.check_stuck_tick( OUT tick_id BIGINT )
		LANGUAGE PLPGSQL
		STRICT VOLATILE
		SECURITY DEFINER
	AS $check_stuck_tick$

DECLARE
	c_tick	BIGINT;
	u_count	INT;

BEGIN
	-- Get next / current tick
	SELECT INTO c_tick current_tick
		FROM sys.status
		WHERE maintenance_start IS NULL
		FOR UPDATE;
	IF NOT FOUND OR c_tick IS NULL THEN
		tick_id := NULL;
		RETURN;
	END IF;
	
	-- Are there any updates left?
	SELECT INTO u_count count(*)
		FROM sys.updates
		WHERE update_state = 'FUTURE'
			AND update_last = c_tick;
	IF u_count = 0 THEN
		PERFORM sys.end_tick( c_tick );
		tick_id := NULL;
	ELSE
		tick_id := c_tick;
	END IF;

END;
$check_stuck_tick$;

REVOKE EXECUTE
	ON FUNCTION sys.check_stuck_tick( )
	FROM PUBLIC;
GRANT EXECUTE
	ON FUNCTION sys.check_stuck_tick( )
	TO :dbuser;



/*
 * Process game updates
 * ---------------------
 * 
 * This function checks for game update elements, marking some of them for
 * processing depending on their type. If there are no more updates to run,
 * end the update cycle; otherwise, depending on the type of update, process
 * the items internally or return a value that indicates they are to be
 * processed by the external Java code.
 * 
 * Parameters:
 *		_current_update		Current game update's identifier
 *
 * Returns:
 *		_has_more			TRUE if the function must be called again, FALSE
 *								otherwise
 *		_process_externally	NULL if there is no update to process or if the
 *								updates were processed in the database, or
 *								the name of the update type if external
 *								processing is needed.
 */
DROP FUNCTION IF EXISTS sys.process_updates( BIGINT ) CASCADE;
CREATE FUNCTION sys.process_updates(
			IN _current_update BIGINT ,
			OUT _has_more BOOLEAN ,
			OUT _process_externally TEXT )
		LANGUAGE PLPGSQL
		STRICT VOLATILE
		SECURITY DEFINER
	AS $process_updates$

DECLARE
	_current_type	INT;
	_batch_size		INT;
	_type_name		TEXT;
	_proc_name		NAME;

BEGIN
	-- Mark all entries that were being processed as having been processed
	UPDATE sys.updates
		SET update_state = 'PROCESSED'
		WHERE update_state = 'PROCESSING'
			AND update_last = _current_update;

	-- Find the next type of update to process and its specific parameters
	SELECT INTO _current_type , _batch_size , _type_name , _proc_name
			updtype_id , updtype_batch_size , updtype_name , updtype_proc_name
		FROM sys.update_types
			INNER JOIN sys.updates
				USING ( updtype_id , updtgt_id )
		WHERE update_state = 'FUTURE'
			AND update_last = _current_update
		ORDER BY updtype_ordering
		LIMIT 1;

	_has_more := FOUND;
	IF _has_more THEN
		-- Check batch size
		IF _batch_size IS NULL THEN
			_batch_size := sys.get_constant( 'game.batchSize' ); 
		END IF;
	
		-- Mark at most _batch_size entries of the right type as being updated
		UPDATE sys.updates
			SET update_state = 'PROCESSING'
			WHERE update_id IN (
					SELECT update_id FROM sys.updates
						WHERE updtype_id = _current_type
							AND update_state = 'FUTURE'
							AND update_last = _current_update
						LIMIT _batch_size
				);

		IF _proc_name IS NULL THEN
			-- External processing is required
			_process_externally := _type_name;
		ELSE
			-- Process updates using a stored procedure
			EXECUTE 'SELECT sys."' || _proc_name || '"( $1 )'
				USING _current_update;
		END IF;
	ELSE
		-- No updates left to run
		PERFORM sys.end_tick( _current_update );
	END IF;
END;
$process_updates$;

REVOKE EXECUTE
	ON FUNCTION sys.process_updates( BIGINT )
	FROM PUBLIC;
GRANT EXECUTE
	ON FUNCTION sys.process_updates( BIGINT )
	TO :dbuser;