lwb6/legacyworlds-server-data/db-structure/parts/040-functions/172-event-storage.sql

-- LegacyWorlds Beta 6
-- PostgreSQL database scripts
--
-- Functions that extract event data from the queue tables
-- and store them in the long term storage tables.
--
-- Copyright(C) 2004-2012, DeepClone Development
-- --------------------------------------------------------


/*
 * Entity fields table
 * -------------------
 * 
 * This table, which is filled when the database is created, associates entity
 * types with fields in the events.field_values table.
 */
DROP TABLE IF EXISTS events._entity_fields;
CREATE TABLE events._entity_fields(
	efdef_entity	events.entity_field_type NOT NULL PRIMARY KEY ,
	field_name		NAME NOT NULL ,
	field_type		NAME NOT NULL
);
INSERT INTO events._entity_fields VALUES
	( 'EMP' , 'empire_id' , 'INT' ) ,
	( 'PLN' , 'planet_id' , 'INT' ) ,
	( 'FLT' , 'fleet_id' , 'BIGINT' ) ,
	( 'ALL' , 'alliance_id' , 'INT' ) ,
	( 'BAT' , 'battle_id' , 'BIGINT' ) ,
	( 'ADM' , 'admin_id' , 'INT' ) ,
	( 'BUG' , 'bug_report_id' , 'BIGINT' );



/*
 * Process a pending event
 * -----------------------
 * 
 * /!\ INTERNAL FUNCTION /!\
 * 
 * This procedure processes a single row from an event queue.
 * 
 * If the specified event identifier actually exists in the queue, it is
 * converted and stored in the main events tables, then the queue entry is
 * removed.
 * 
 * In all cases, the entry is deleted from events.pending_events.
 * 
 * Parameters:
 *		_event_id	The event's identifier
 *		_evdef_id	The event's type
 *
 * Returns:
 *		???			TRUE 
 */
DROP FUNCTION IF EXISTS events.eq_process_event( BIGINT , TEXT );
CREATE FUNCTION events.eq_process_event(
			_event_id	BIGINT ,
			_evdef_id	TEXT )
		RETURNS BOOLEAN
		LANGUAGE PLPGSQL
		STRICT VOLATILE SECURITY INVOKER
	AS $eq_process_event$

DECLARE
	_tbl	TEXT;
	_qstr	TEXT;
	_qentry	RECORD;
	_nfound	INT;
	_efdef	RECORD;

BEGIN
	
	_tbl := 'events.eq_' || replace( _evdef_id , '-' , '_' );
	_qstr := 'SELECT event_rtime , event_gtime , empire_id FROM '
		|| _tbl || ' WHERE event_id = $1';
	EXECUTE _qstr INTO _qentry USING _event_id;
	GET DIAGNOSTICS _nfound = ROW_COUNT;

	IF _nfound > 0
	THEN
	
		INSERT INTO events.events_v2 (
				event_id , evdef_id , event_rtime , event_gtime , empire_id
			) VALUES (
				_event_id , _evdef_id , _qentry.event_rtime ,
				_qentry.event_gtime , _qentry.empire_id
			);

		_qstr := format( $field_acquisition$
			SELECT efdef_id , field_name , field_type ,
					( efdef_type = 'I18N') AS i18n , _sq1.value 
				FROM events.field_definitions
					INNER JOIN (
						SELECT (each(hstore(_tbl))).*
							FROM %s _tbl
							WHERE _tbl.event_id = $1
						) _sq1 ON _sq1.key = 'ef_' || replace(efdef_id , '-','_')
					LEFT OUTER JOIN events._entity_fields
						USING ( efdef_entity )
				WHERE evdef_id = $2
		$field_acquisition$ , _tbl );

		FOR _efdef IN EXECUTE _qstr USING _event_id , _evdef_id
		LOOP
			_qstr := 'INSERT INTO events.field_values(event_id,evdef_id,efdef_id,efval_litteral'
				|| ( CASE 
					WHEN _efdef.field_name IS NOT NULL
						THEN ',' || _efdef.field_name
					WHEN _efdef.i18n
						THEN ',string_id'
					ELSE
						''
				END ) || ') ';

			IF _efdef.i18n
			THEN
				_qstr := _qstr || $i18n_query$
					SELECT $1,$2,$3,_str.id::TEXT||' '||_str.name,_str.id
						FROM defs.strings _str
						WHERE _str.name = $4
					$i18n_query$;
			ELSIF _efdef.field_name IS NOT NULL
			THEN
				_qstr := _qstr || format( 'VALUES($1,$2,$3,$4,$4::%s)' , _efdef.field_type );
			ELSE
				_qstr := _qstr || 'VALUES($1,$2,$3,$4)';
			END IF;

			EXECUTE _qstr USING _event_id , _evdef_id , _efdef.efdef_id , _efdef.value;
		END LOOP;

	END IF;

	DELETE FROM events.pending_events
		WHERE event_id = _event_id;
	RETURN TRUE;

END;
$eq_process_event$;

REVOKE EXECUTE
	ON FUNCTION events.eq_process_event( BIGINT , TEXT )
	FROM PUBLIC;



/*
 * Process pending events
 * ----------------------
 * 
 * This procedure processes events from the queues. Each time the procedure is
 * called, it will process at most "game.events.batchSize" events.
 *
 * Returns:
 *		???			TRUE if events were processed, FALSE if the queues are
 *						empty
 */
DROP FUNCTION IF EXISTS events.eq_process( );
CREATE FUNCTION events.eq_process( )
		RETURNS BOOLEAN
		LANGUAGE PLPGSQL
		STRICT VOLATILE SECURITY DEFINER
	AS $eq_process$

DECLARE
	_limit	INT;

BEGIN
	_limit := sys.get_constant( 'game.events.batchSize' );
	PERFORM events.eq_process_event( event_id , evdef_id )
		FROM events.pending_events
		LIMIT _limit;
	RETURN FOUND;
END;
$eq_process$;

REVOKE EXECUTE
	ON FUNCTION events.eq_process( )
	FROM PUBLIC;
GRANT EXECUTE
	ON FUNCTION events.eq_process( )
	TO :dbuser;