This repository has been archived on 2025-01-04. You can view files and clone it, but cannot push or open issues or pull requests.
lwb6/legacyworlds-server-data/db-structure/parts/040-functions/172-event-storage.sql

183 lines
4.5 KiB
MySQL
Raw Permalink Normal View History

-- 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;