Emmanuel BENOîT
3a0f5bbb78
Added the necessary database code to convert the contents of event queues into actual event records. The changes include: * a new table, events.pending_events, which is automatically filled by a trigger when events are inserted into queue tables, * the game.events.batchSize constant which defines the maximal amount of events to process in a single transaction, * the events.eq_process() stored procedure, which processes the events. In addition, the "hstore" extension was added as it is the easiest way to convert events from the queues' table model to the store's meta-model.
897 lines
25 KiB
PL/PgSQL
897 lines
25 KiB
PL/PgSQL
-- LegacyWorlds Beta 6
|
|
-- PostgreSQL database scripts
|
|
--
|
|
-- Functions allowing event definitions to be added to the
|
|
-- database
|
|
--
|
|
-- Copyright(C) 2004-2012, DeepClone Development
|
|
-- --------------------------------------------------------
|
|
|
|
|
|
/*
|
|
* Return values for event definition functions
|
|
* ---------------------------------------------
|
|
*/
|
|
DROP TYPE IF EXISTS events.evdef_create_result CASCADE;
|
|
CREATE TYPE events.evdef_create_result AS ENUM(
|
|
/* The event type definition function succeeded. If it was the initial
|
|
* creation function or a field creation function, this means the creation
|
|
* can continue. If it was the finalisation function, this means the event
|
|
* definition has been added.
|
|
*/
|
|
'OK' ,
|
|
|
|
/* A bad identifier was specified. This value is returned by both the
|
|
* initial creation function and event field definition functions.
|
|
*/
|
|
'BAD_ID' ,
|
|
|
|
/* An internationalised string (either an event type's name or template)
|
|
* could not be found.
|
|
*/
|
|
'BAD_STRINGS' ,
|
|
|
|
/* Duplicate event or field identifier found. This value is returned by
|
|
* the finalisation function, with its "_fld" set to NULL if the duplicate
|
|
* identifier is the event's.
|
|
*/
|
|
'DUPLICATE' ,
|
|
|
|
/* Bad event or field specification found. This value is returned by the
|
|
* finalisation function when an event or field cannot be inserted due to
|
|
* check failures.
|
|
*/
|
|
'BAD_SPEC' ,
|
|
|
|
/* This error code is returned when trying to finalise an event after one
|
|
* of the definition calls failed.
|
|
*/
|
|
'NO_DATA'
|
|
);
|
|
|
|
|
|
|
|
/*
|
|
* Start defining a new event type
|
|
* -------------------------------
|
|
*
|
|
* This function creates a temporary table used to defgine a new type of
|
|
* event. No checks beyond the length and contents of the event type's
|
|
* identifier are made at this point.
|
|
*
|
|
* Note: the temporary table will be created in all cases, even when the
|
|
* identifier is invalid.
|
|
*
|
|
* Parameters:
|
|
* _id The identifier of the event type
|
|
* _prio The event type's default priority
|
|
* _adj Whether the player may adjust the priority for this type
|
|
* of events.
|
|
* _i18n The "base" of the I18N strings identifiers; the function
|
|
* will attempt to use "<_i18n>Name" as the name's
|
|
* identifier and "<_i18n>Template" as the template's.
|
|
*
|
|
* Returns:
|
|
* ??? An error code: OK or BAD_ID
|
|
* (see events.evdef_create_result)
|
|
*/
|
|
DROP FUNCTION IF EXISTS events.evdef_start( TEXT , INT , BOOLEAN , TEXT );
|
|
CREATE FUNCTION events.evdef_start(
|
|
_id TEXT ,
|
|
_prio INT ,
|
|
_adj BOOLEAN ,
|
|
_i18n TEXT )
|
|
RETURNS events.evdef_create_result
|
|
LANGUAGE PLPGSQL
|
|
STRICT VOLATILE SECURITY DEFINER
|
|
AS $evdef_start$
|
|
|
|
DECLARE
|
|
_name_id INT;
|
|
_template_id INT;
|
|
|
|
BEGIN
|
|
CREATE TEMPORARY TABLE evdef_temp(
|
|
evdef_id TEXT ,
|
|
evfld_id TEXT ,
|
|
evfld_typespec TEXT
|
|
) ON COMMIT DROP;
|
|
|
|
IF LENGTH( _id ) < 2 OR LENGTH( _id ) > 48
|
|
OR NOT _id ~ '^[a-z\-]+$'
|
|
THEN
|
|
RETURN 'BAD_ID';
|
|
END IF;
|
|
|
|
SELECT INTO _name_id id FROM defs.strings
|
|
WHERE name = _i18n || 'Name';
|
|
SELECT INTO _template_id id FROM defs.strings
|
|
WHERE name = _i18n || 'Template';
|
|
IF _name_id IS NULL OR _template_id IS NULL THEN
|
|
RETURN 'BAD_STRINGS';
|
|
END IF;
|
|
|
|
INSERT INTO evdef_temp( evdef_id , evfld_typespec )
|
|
VALUES( _id , _prio::TEXT || ',' || _adj::TEXT
|
|
|| ',' || _name_id || ',' || _template_id );
|
|
RETURN 'OK';
|
|
END;
|
|
$evdef_start$;
|
|
|
|
REVOKE EXECUTE
|
|
ON FUNCTION events.evdef_start( TEXT , INT , BOOLEAN , TEXT )
|
|
FROM PUBLIC;
|
|
GRANT EXECUTE
|
|
ON FUNCTION events.evdef_start( TEXT , INT , BOOLEAN , TEXT )
|
|
TO :dbuser;
|
|
|
|
|
|
|
|
/*
|
|
* Create a new field
|
|
* ------------------
|
|
*
|
|
* /!\ INTERNAL FUNCTION /!\
|
|
*
|
|
* This function adds data about a new field to the temporary event
|
|
* definition table.
|
|
*
|
|
* Parameters:
|
|
* _id The field's identifier
|
|
* _opt Whether the field is optional
|
|
* _type The field's type
|
|
* _etype The field's entity type (may be NULL)
|
|
* _is_int Whether the field is an integer (may be NULL)
|
|
* _min_val Minimal value/length (may be NULL)
|
|
* _max_val Maximal value/length (may be NULL)
|
|
*
|
|
* Returns:
|
|
* ??? An error code: OK or BAD_ID
|
|
* (see events.evdef_create_result)
|
|
*/
|
|
DROP FUNCTION IF EXISTS events.evdef_addfld_internal(
|
|
TEXT , BOOLEAN , events.field_type , events.entity_field_type ,
|
|
BOOLEAN , NUMERIC , NUMERIC ) CASCADE;
|
|
CREATE FUNCTION events.evdef_addfld_internal(
|
|
_id TEXT ,
|
|
_opt BOOLEAN ,
|
|
_type events.field_type ,
|
|
_etype events.entity_field_type ,
|
|
_is_int BOOLEAN ,
|
|
_min_val NUMERIC ,
|
|
_max_val NUMERIC
|
|
) RETURNS events.evdef_create_result
|
|
LANGUAGE PLPGSQL
|
|
CALLED ON NULL INPUT
|
|
VOLATILE SECURITY INVOKER
|
|
AS $evdef_addfld_internal$
|
|
|
|
DECLARE
|
|
_iq_string TEXT;
|
|
|
|
BEGIN
|
|
IF LENGTH( _id ) < 2 OR LENGTH( _id ) > 48
|
|
OR NOT _id ~ '^[a-z\-]+$'
|
|
THEN
|
|
DELETE FROM evdef_temp;
|
|
RETURN 'BAD_ID'::events.evdef_create_result;
|
|
END IF;
|
|
|
|
_iq_string := _opt::TEXT || ' , ''' || _type::TEXT || ''' , ';
|
|
IF _etype IS NULL
|
|
THEN
|
|
_iq_string := _iq_string || 'NULL';
|
|
ELSE
|
|
_iq_string := _iq_string || '''' || _etype::TEXT || '''';
|
|
END IF;
|
|
_iq_string := _iq_string || ' , ';
|
|
IF _is_int IS NULL
|
|
THEN
|
|
_iq_string := _iq_string || 'NULL';
|
|
ELSE
|
|
_iq_string := _iq_string || _is_int::TEXT;
|
|
END IF;
|
|
_iq_string := _iq_string || ' , ';
|
|
IF _min_val IS NULL
|
|
THEN
|
|
_iq_string := _iq_string || 'NULL';
|
|
ELSE
|
|
_iq_string := _iq_string || _min_val::TEXT;
|
|
END IF;
|
|
_iq_string := _iq_string || ' , ';
|
|
IF _max_val IS NULL
|
|
THEN
|
|
_iq_string := _iq_string || 'NULL';
|
|
ELSE
|
|
_iq_string := _iq_string || _max_val::TEXT;
|
|
END IF;
|
|
|
|
INSERT INTO evdef_temp( evdef_id , evfld_id , evfld_typespec )
|
|
SELECT evdef_id , _id , _iq_string
|
|
FROM evdef_temp
|
|
WHERE evfld_id IS NULL;
|
|
RETURN 'OK'::events.evdef_create_result;
|
|
END;
|
|
$evdef_addfld_internal$;
|
|
|
|
REVOKE EXECUTE
|
|
ON FUNCTION events.evdef_addfld_internal(
|
|
TEXT , BOOLEAN , events.field_type , events.entity_field_type ,
|
|
BOOLEAN , NUMERIC , NUMERIC )
|
|
FROM PUBLIC;
|
|
|
|
|
|
/*
|
|
* Actual field creation functions
|
|
* --------------------------------
|
|
*
|
|
* Bunch of functions that create the field records in the temporary table
|
|
* through events.evdef_addfld_internal().
|
|
*
|
|
* Note:
|
|
* These functions are not dropped manually here, as it is assumed that
|
|
* dropping events.evdef_addfld_internal() will have cascaded.
|
|
*/
|
|
|
|
-- INTEGER FIELD, NO BOUNDS
|
|
CREATE FUNCTION events.evdef_addfld_int( _id TEXT , _opt BOOLEAN )
|
|
RETURNS events.evdef_create_result
|
|
LANGUAGE SQL
|
|
STRICT VOLATILE SECURITY DEFINER
|
|
AS $$ SELECT events.evdef_addfld_internal(
|
|
$1,$2,'NUMERIC'::events.field_type,NULL,TRUE,NULL,NULL); $$;
|
|
REVOKE EXECUTE
|
|
ON FUNCTION events.evdef_addfld_int( TEXT , BOOLEAN )
|
|
FROM PUBLIC;
|
|
GRANT EXECUTE
|
|
ON FUNCTION events.evdef_addfld_int( TEXT , BOOLEAN )
|
|
TO :dbuser;
|
|
|
|
-- INTEGER FIELD W/ LOWER BOUND
|
|
CREATE FUNCTION events.evdef_addfld_int_min( _id TEXT , _opt BOOLEAN , _min INT )
|
|
RETURNS events.evdef_create_result
|
|
LANGUAGE SQL
|
|
STRICT VOLATILE SECURITY DEFINER
|
|
AS $$ SELECT events.evdef_addfld_internal(
|
|
$1,$2,'NUMERIC'::events.field_type,NULL,TRUE,$3::NUMERIC,NULL); $$;
|
|
REVOKE EXECUTE
|
|
ON FUNCTION events.evdef_addfld_int_min( TEXT , BOOLEAN , INT )
|
|
FROM PUBLIC;
|
|
GRANT EXECUTE
|
|
ON FUNCTION events.evdef_addfld_int_min( TEXT , BOOLEAN , INT )
|
|
TO :dbuser;
|
|
|
|
-- INTEGER FIELD W/ HIGHER BOUND
|
|
CREATE FUNCTION events.evdef_addfld_int_max( _id TEXT , _opt BOOLEAN , _max INT )
|
|
RETURNS events.evdef_create_result
|
|
LANGUAGE SQL
|
|
STRICT VOLATILE SECURITY DEFINER
|
|
AS $$ SELECT events.evdef_addfld_internal(
|
|
$1,$2,'NUMERIC'::events.field_type,NULL,TRUE,NULL,$3::NUMERIC); $$;
|
|
REVOKE EXECUTE
|
|
ON FUNCTION events.evdef_addfld_int_max( TEXT , BOOLEAN , INT )
|
|
FROM PUBLIC;
|
|
GRANT EXECUTE
|
|
ON FUNCTION events.evdef_addfld_int_max( TEXT , BOOLEAN , INT )
|
|
TO :dbuser;
|
|
|
|
-- INTEGER FIELD W/ LIMITED RANGE
|
|
CREATE FUNCTION events.evdef_addfld_int_range( _id TEXT , _opt BOOLEAN , _min INT , _max INT )
|
|
RETURNS events.evdef_create_result
|
|
LANGUAGE SQL
|
|
STRICT VOLATILE SECURITY DEFINER
|
|
AS $$ SELECT events.evdef_addfld_internal(
|
|
$1,$2,'NUMERIC'::events.field_type,NULL,TRUE,$3::NUMERIC,$4::NUMERIC); $$;
|
|
REVOKE EXECUTE
|
|
ON FUNCTION events.evdef_addfld_int_range( TEXT , BOOLEAN , INT , INT )
|
|
FROM PUBLIC;
|
|
GRANT EXECUTE
|
|
ON FUNCTION events.evdef_addfld_int_range( TEXT , BOOLEAN , INT , INT )
|
|
TO :dbuser;
|
|
|
|
-- REAL FIELD, NO BOUNDS
|
|
CREATE FUNCTION events.evdef_addfld_real( _id TEXT , _opt BOOLEAN )
|
|
RETURNS events.evdef_create_result
|
|
LANGUAGE SQL
|
|
STRICT VOLATILE SECURITY DEFINER
|
|
AS $$ SELECT events.evdef_addfld_internal(
|
|
$1,$2,'NUMERIC'::events.field_type,NULL,FALSE,NULL,NULL); $$;
|
|
REVOKE EXECUTE
|
|
ON FUNCTION events.evdef_addfld_real( TEXT , BOOLEAN )
|
|
FROM PUBLIC;
|
|
GRANT EXECUTE
|
|
ON FUNCTION events.evdef_addfld_real( TEXT , BOOLEAN )
|
|
TO :dbuser;
|
|
|
|
-- REAL FIELD W/ LOWER BOUND
|
|
CREATE FUNCTION events.evdef_addfld_real_min( _id TEXT , _opt BOOLEAN , _min DOUBLE PRECISION )
|
|
RETURNS events.evdef_create_result
|
|
LANGUAGE SQL
|
|
STRICT VOLATILE SECURITY DEFINER
|
|
AS $$ SELECT events.evdef_addfld_internal(
|
|
$1,$2,'NUMERIC'::events.field_type,NULL,FALSE,$3::NUMERIC,NULL); $$;
|
|
REVOKE EXECUTE
|
|
ON FUNCTION events.evdef_addfld_real_min( TEXT , BOOLEAN , DOUBLE PRECISION )
|
|
FROM PUBLIC;
|
|
GRANT EXECUTE
|
|
ON FUNCTION events.evdef_addfld_real_min( TEXT , BOOLEAN , DOUBLE PRECISION )
|
|
TO :dbuser;
|
|
|
|
-- REAL FIELD W/ HIGHER BOUND
|
|
CREATE FUNCTION events.evdef_addfld_real_max( _id TEXT , _opt BOOLEAN , _max DOUBLE PRECISION )
|
|
RETURNS events.evdef_create_result
|
|
LANGUAGE SQL
|
|
STRICT VOLATILE SECURITY DEFINER
|
|
AS $$ SELECT events.evdef_addfld_internal(
|
|
$1,$2,'NUMERIC'::events.field_type,NULL,FALSE,NULL,$3::NUMERIC); $$;
|
|
REVOKE EXECUTE
|
|
ON FUNCTION events.evdef_addfld_real_max( TEXT , BOOLEAN , DOUBLE PRECISION )
|
|
FROM PUBLIC;
|
|
GRANT EXECUTE
|
|
ON FUNCTION events.evdef_addfld_real_max( TEXT , BOOLEAN , DOUBLE PRECISION )
|
|
TO :dbuser;
|
|
|
|
-- REAL FIELD W/ LIMITED RANGE
|
|
CREATE FUNCTION events.evdef_addfld_real_range( _id TEXT , _opt BOOLEAN , _min DOUBLE PRECISION , _max DOUBLE PRECISION )
|
|
RETURNS events.evdef_create_result
|
|
LANGUAGE SQL
|
|
STRICT VOLATILE SECURITY DEFINER
|
|
AS $$ SELECT events.evdef_addfld_internal(
|
|
$1,$2,'NUMERIC'::events.field_type,NULL,FALSE,$3::NUMERIC,$4::NUMERIC); $$;
|
|
REVOKE EXECUTE
|
|
ON FUNCTION events.evdef_addfld_real_range( TEXT , BOOLEAN , DOUBLE PRECISION , DOUBLE PRECISION )
|
|
FROM PUBLIC;
|
|
GRANT EXECUTE
|
|
ON FUNCTION events.evdef_addfld_real_range( TEXT , BOOLEAN , DOUBLE PRECISION , DOUBLE PRECISION )
|
|
TO :dbuser;
|
|
|
|
-- TEXT FIELD, NO BOUNDS
|
|
CREATE FUNCTION events.evdef_addfld_text( _id TEXT , _opt BOOLEAN )
|
|
RETURNS events.evdef_create_result
|
|
LANGUAGE SQL
|
|
STRICT VOLATILE SECURITY DEFINER
|
|
AS $$ SELECT events.evdef_addfld_internal(
|
|
$1,$2,'TEXT'::events.field_type,NULL,NULL,NULL,NULL); $$;
|
|
REVOKE EXECUTE
|
|
ON FUNCTION events.evdef_addfld_text( TEXT , BOOLEAN )
|
|
FROM PUBLIC;
|
|
GRANT EXECUTE
|
|
ON FUNCTION events.evdef_addfld_text( TEXT , BOOLEAN )
|
|
TO :dbuser;
|
|
|
|
-- TEXT FIELD W/ LOWER BOUND
|
|
CREATE FUNCTION events.evdef_addfld_text_min( _id TEXT , _opt BOOLEAN , _min INT )
|
|
RETURNS events.evdef_create_result
|
|
LANGUAGE SQL
|
|
STRICT VOLATILE SECURITY DEFINER
|
|
AS $$ SELECT events.evdef_addfld_internal(
|
|
$1,$2,'TEXT'::events.field_type,NULL,NULL,$3::NUMERIC,NULL); $$;
|
|
REVOKE EXECUTE
|
|
ON FUNCTION events.evdef_addfld_text_min( TEXT , BOOLEAN , INT )
|
|
FROM PUBLIC;
|
|
GRANT EXECUTE
|
|
ON FUNCTION events.evdef_addfld_text_min( TEXT , BOOLEAN , INT )
|
|
TO :dbuser;
|
|
|
|
-- TEXT FIELD W/ HIGHER BOUND
|
|
CREATE FUNCTION events.evdef_addfld_text_max( _id TEXT , _opt BOOLEAN , _max INT )
|
|
RETURNS events.evdef_create_result
|
|
LANGUAGE SQL
|
|
STRICT VOLATILE SECURITY DEFINER
|
|
AS $$ SELECT events.evdef_addfld_internal(
|
|
$1,$2,'TEXT'::events.field_type,NULL,NULL,NULL,$3::NUMERIC); $$;
|
|
REVOKE EXECUTE
|
|
ON FUNCTION events.evdef_addfld_text_max( TEXT , BOOLEAN , INT )
|
|
FROM PUBLIC;
|
|
GRANT EXECUTE
|
|
ON FUNCTION events.evdef_addfld_text_max( TEXT , BOOLEAN , INT )
|
|
TO :dbuser;
|
|
|
|
-- TEXT FIELD W/ LIMITED RANGE
|
|
CREATE FUNCTION events.evdef_addfld_text_range( _id TEXT , _opt BOOLEAN , _min INT , _max INT )
|
|
RETURNS events.evdef_create_result
|
|
LANGUAGE SQL
|
|
STRICT VOLATILE SECURITY DEFINER
|
|
AS $$ SELECT events.evdef_addfld_internal(
|
|
$1,$2,'TEXT'::events.field_type,NULL,NULL,$3::NUMERIC,$4::NUMERIC); $$;
|
|
REVOKE EXECUTE
|
|
ON FUNCTION events.evdef_addfld_text_range( TEXT , BOOLEAN , INT , INT )
|
|
FROM PUBLIC;
|
|
GRANT EXECUTE
|
|
ON FUNCTION events.evdef_addfld_text_range( TEXT , BOOLEAN , INT , INT )
|
|
TO :dbuser;
|
|
|
|
-- BOOLEAN FIELD
|
|
CREATE FUNCTION events.evdef_addfld_bool( _id TEXT , _opt BOOLEAN )
|
|
RETURNS events.evdef_create_result
|
|
LANGUAGE SQL
|
|
STRICT VOLATILE SECURITY DEFINER
|
|
AS $$ SELECT events.evdef_addfld_internal(
|
|
$1,$2,'BOOL'::events.field_type,NULL,NULL,NULL,NULL); $$;
|
|
REVOKE EXECUTE
|
|
ON FUNCTION events.evdef_addfld_bool( TEXT , BOOLEAN )
|
|
FROM PUBLIC;
|
|
GRANT EXECUTE
|
|
ON FUNCTION events.evdef_addfld_bool( TEXT , BOOLEAN )
|
|
TO :dbuser;
|
|
|
|
-- I18N TEXT FIELD
|
|
CREATE FUNCTION events.evdef_addfld_i18n( _id TEXT , _opt BOOLEAN )
|
|
RETURNS events.evdef_create_result
|
|
LANGUAGE SQL
|
|
STRICT VOLATILE SECURITY DEFINER
|
|
AS $$ SELECT events.evdef_addfld_internal(
|
|
$1,$2,'I18N'::events.field_type,NULL,NULL,NULL,NULL); $$;
|
|
REVOKE EXECUTE
|
|
ON FUNCTION events.evdef_addfld_i18n( TEXT , BOOLEAN )
|
|
FROM PUBLIC;
|
|
GRANT EXECUTE
|
|
ON FUNCTION events.evdef_addfld_i18n( TEXT , BOOLEAN )
|
|
TO :dbuser;
|
|
|
|
-- ENTITY LINK FIELD
|
|
CREATE FUNCTION events.evdef_addfld_entity( _id TEXT , _opt BOOLEAN , _etype events.entity_field_type )
|
|
RETURNS events.evdef_create_result
|
|
LANGUAGE SQL
|
|
STRICT VOLATILE SECURITY DEFINER
|
|
AS $$ SELECT events.evdef_addfld_internal(
|
|
$1,$2,'ENTITY'::events.field_type,$3,NULL,NULL,NULL); $$;
|
|
REVOKE EXECUTE
|
|
ON FUNCTION events.evdef_addfld_entity( TEXT , BOOLEAN , events.entity_field_type )
|
|
FROM PUBLIC;
|
|
GRANT EXECUTE
|
|
ON FUNCTION events.evdef_addfld_entity( TEXT , BOOLEAN , events.entity_field_type )
|
|
TO :dbuser;
|
|
|
|
|
|
/*
|
|
* Create an event definition record
|
|
* ---------------------------------
|
|
*
|
|
* /!\ INTERNAL FUNCTION /!\
|
|
*
|
|
* This function inserts the record for an event definition into the
|
|
* appropriate table.
|
|
*
|
|
* Returns:
|
|
* ??? An error code
|
|
*/
|
|
DROP FUNCTION IF EXISTS events.evdef_create_record( );
|
|
CREATE FUNCTION events.evdef_create_record( )
|
|
RETURNS events.evdef_create_result
|
|
LANGUAGE PLPGSQL
|
|
STRICT VOLATILE SECURITY INVOKER
|
|
AS $evdef_create_record$
|
|
|
|
DECLARE
|
|
_rec RECORD;
|
|
_qstr TEXT;
|
|
|
|
BEGIN
|
|
SELECT INTO _rec *
|
|
FROM evdef_temp
|
|
WHERE evfld_id IS NULL;
|
|
IF NOT FOUND
|
|
THEN
|
|
RETURN 'NO_DATA';
|
|
END IF;
|
|
|
|
_qstr := 'INSERT INTO events.event_definitions (' ||
|
|
'evdef_id , evdef_priority , evdef_adjustable , ' ||
|
|
'evdef_name_id , evdef_template_id' ||
|
|
') VALUES ( $1 , ' || _rec.evfld_typespec || ');';
|
|
|
|
BEGIN
|
|
EXECUTE _qstr USING _rec.evdef_id;
|
|
EXCEPTION
|
|
WHEN unique_violation THEN
|
|
RETURN 'DUPLICATE';
|
|
WHEN check_violation THEN
|
|
RETURN 'BAD_SPEC';
|
|
END;
|
|
|
|
RETURN 'OK';
|
|
END;
|
|
$evdef_create_record$;
|
|
|
|
REVOKE EXECUTE
|
|
ON FUNCTION events.evdef_create_record( )
|
|
FROM PUBLIC;
|
|
|
|
|
|
/*
|
|
* Create an event field definition record
|
|
* ---------------------------------------
|
|
*
|
|
* /!\ INTERNAL FUNCTION /!\
|
|
*
|
|
* This function inserts a new event field definition record into the
|
|
* appropriate table.
|
|
*
|
|
* Parameters:
|
|
* _evdef_id The event definition's identifier
|
|
* _efdef_id The field's identifier
|
|
* _typespec The type specification from the temporary table
|
|
*
|
|
* Returns:
|
|
* ??? An error code
|
|
*/
|
|
DROP FUNCTION IF EXISTS events.evdef_create_field_record(
|
|
TEXT , TEXT , TEXT );
|
|
CREATE FUNCTION events.evdef_create_field_record(
|
|
_evdef_id TEXT ,
|
|
_efdef_id TEXT ,
|
|
_typespec TEXT )
|
|
RETURNS events.evdef_create_result
|
|
LANGUAGE PLPGSQL
|
|
STRICT VOLATILE SECURITY INVOKER
|
|
AS $evdef_create_field_record$
|
|
|
|
DECLARE
|
|
_qstr TEXT;
|
|
|
|
BEGIN
|
|
_qstr := 'INSERT INTO events.field_definitions (' ||
|
|
'evdef_id , efdef_id , efdef_optional , efdef_type , ' ||
|
|
'efdef_entity , efdef_integer , efdef_min , efdef_max ' ||
|
|
') VALUES ( $1 , $2 , ' || _typespec || ');';
|
|
BEGIN
|
|
EXECUTE _qstr USING _evdef_id , _efdef_id;
|
|
EXCEPTION
|
|
WHEN unique_violation THEN
|
|
RETURN 'DUPLICATE';
|
|
WHEN check_violation THEN
|
|
RETURN 'BAD_SPEC';
|
|
END;
|
|
RETURN 'OK';
|
|
|
|
END;
|
|
$evdef_create_field_record$;
|
|
|
|
REVOKE EXECUTE
|
|
ON FUNCTION events.evdef_create_field_record( TEXT , TEXT , TEXT )
|
|
FROM PUBLIC;
|
|
|
|
|
|
/*
|
|
* Generate the DDL for a numeric field
|
|
* ------------------------------------
|
|
*
|
|
* /!\ INTERNAL FUNCTION /!\
|
|
*
|
|
* This function generates the DDL for a numeric event field in an event queue
|
|
* table.
|
|
*
|
|
* Parameters:
|
|
* _fname The field's table name
|
|
* _opt Whether the field is optional
|
|
* _is_int Whether the field is an integer
|
|
* _min Minimal value of the field (may be NULL)
|
|
* _max Maximal value of the field (may be NULL)
|
|
*
|
|
* Returns:
|
|
* ??? The field's DDL
|
|
*/
|
|
DROP FUNCTION IF EXISTS events.efdef_ddl_numeric(
|
|
TEXT , BOOLEAN , BOOLEAN , NUMERIC , NUMERIC );
|
|
CREATE FUNCTION events.efdef_ddl_numeric(
|
|
_fname TEXT ,
|
|
_opt BOOLEAN ,
|
|
_is_int BOOLEAN ,
|
|
_min NUMERIC ,
|
|
_max NUMERIC )
|
|
RETURNS TEXT
|
|
LANGUAGE SQL
|
|
IMMUTABLE SECURITY INVOKER
|
|
CALLED ON NULL INPUT
|
|
AS $efdef_ddl_numeric$
|
|
SELECT ',' || $1 || ' '
|
|
|| ( CASE WHEN $3 THEN 'BIGINT' ELSE 'DOUBLE PRECISION' END )
|
|
|| ( CASE WHEN $2 THEN '' ELSE ' NOT NULL' END )
|
|
|| ( CASE
|
|
WHEN $4 IS NULL AND $5 IS NULL
|
|
THEN ''
|
|
ELSE
|
|
' CHECK(' || $1 || ( CASE
|
|
WHEN $5 IS NULL
|
|
THEN '>=' || $4::TEXT
|
|
WHEN $4 IS NULL
|
|
THEN '<=' || $5::TEXT
|
|
ELSE
|
|
' BETWEEN ' || $4::TEXT || ' AND ' || $5::TEXT
|
|
END ) || ')'
|
|
END );
|
|
$efdef_ddl_numeric$;
|
|
|
|
REVOKE EXECUTE
|
|
ON FUNCTION events.efdef_ddl_numeric(
|
|
TEXT , BOOLEAN , BOOLEAN , NUMERIC , NUMERIC )
|
|
FROM PUBLIC;
|
|
|
|
|
|
|
|
/*
|
|
* Generate the DDL for a text field
|
|
* ---------------------------------
|
|
*
|
|
* /!\ INTERNAL FUNCTION /!\
|
|
*
|
|
* This function generates the DDL for a text event field in an event queue
|
|
* table.
|
|
*
|
|
* Parameters:
|
|
* _fname The field's table name
|
|
* _opt Whether the field is optional
|
|
* _min Minimal length of the field (may be NULL)
|
|
* _max Maximal length of the field (may be NULL)
|
|
*
|
|
* Returns:
|
|
* ??? The field's DDL
|
|
*/
|
|
DROP FUNCTION IF EXISTS events.efdef_ddl_text(
|
|
TEXT , BOOLEAN , NUMERIC , NUMERIC );
|
|
CREATE FUNCTION events.efdef_ddl_text(
|
|
_fname TEXT ,
|
|
_opt BOOLEAN ,
|
|
_min NUMERIC ,
|
|
_max NUMERIC )
|
|
RETURNS TEXT
|
|
LANGUAGE SQL
|
|
IMMUTABLE SECURITY INVOKER
|
|
CALLED ON NULL INPUT
|
|
AS $efdef_ddl_text$
|
|
SELECT ',' || $1 || ' '
|
|
|| ( CASE WHEN $4 IS NULL THEN 'TEXT' ELSE 'VARCHAR(' || $4::TEXT || ')' END )
|
|
|| ( CASE WHEN $2 THEN '' ELSE ' NOT NULL' END )
|
|
|| ( CASE
|
|
WHEN $3 IS NULL
|
|
THEN ''
|
|
ELSE
|
|
' CHECK(LENGTH(' || $1 || ')>=' || $3::TEXT || ')'
|
|
END );
|
|
$efdef_ddl_text$;
|
|
|
|
REVOKE EXECUTE
|
|
ON FUNCTION events.efdef_ddl_text(
|
|
TEXT , BOOLEAN , NUMERIC , NUMERIC )
|
|
FROM PUBLIC;
|
|
|
|
|
|
|
|
/*
|
|
* Generate the DDL for an entity field
|
|
* ------------------------------------
|
|
*
|
|
* /!\ INTERNAL FUNCTION /!\
|
|
*
|
|
* This function generates the DDL for an entity event field in an event queue
|
|
* table.
|
|
*
|
|
* Parameters:
|
|
* _fname The field's table name
|
|
* _opt Whether the field is optional
|
|
* _etype Type of entity to reference
|
|
*
|
|
* Returns:
|
|
* ??? The field's DDL
|
|
*/
|
|
DROP FUNCTION IF EXISTS events.efdef_ddl_entity(
|
|
TEXT , BOOLEAN , events.entity_field_type );
|
|
CREATE FUNCTION events.efdef_ddl_entity(
|
|
_fname TEXT ,
|
|
_opt BOOLEAN ,
|
|
_etype events.entity_field_type )
|
|
RETURNS TEXT
|
|
LANGUAGE SQL
|
|
STRICT IMMUTABLE SECURITY INVOKER
|
|
AS $efdef_ddl_entity$
|
|
SELECT ',' || $1 || ' '
|
|
|| ( CASE
|
|
WHEN $3 IN ( 'EMP' , 'PLN', 'ALL' , 'ADM' ) THEN 'INT'
|
|
ELSE 'BIGINT'
|
|
END ) || ( CASE WHEN $2 THEN ' NOT NULL' ELSE '' END )
|
|
|| ' REFERENCES ' || ( CASE $3
|
|
WHEN 'EMP' THEN 'emp.empires(name_id)'
|
|
WHEN 'PLN' THEN 'verse.planets(name_id)'
|
|
WHEN 'FLT' THEN 'fleets.fleets(id)'
|
|
WHEN 'ALL' THEN 'emp.alliances(id)'
|
|
WHEN 'BAT' THEN 'battles.battles(id)'
|
|
WHEN 'ADM' THEN 'admin.administrators(id)'
|
|
WHEN 'BUG' THEN 'bugs.initial_report_events(event_id)'
|
|
END ) || ' ON DELETE CASCADE';
|
|
$efdef_ddl_entity$;
|
|
|
|
REVOKE EXECUTE
|
|
ON FUNCTION events.efdef_ddl_entity(
|
|
TEXT , BOOLEAN , events.entity_field_type )
|
|
FROM PUBLIC;
|
|
|
|
|
|
/*
|
|
* Trigger used to insert entries into pending_events
|
|
* --------------------------------------------------
|
|
*
|
|
* This function is used as a trigger to insert queued events into the
|
|
* events.pending_events table.
|
|
*
|
|
* Parameters:
|
|
* TG_ARGV[0] Event type identifier
|
|
*/
|
|
DROP FUNCTION IF EXISTS events.tgf_eq_insert( );
|
|
CREATE FUNCTION events.tgf_eq_insert( )
|
|
RETURNS TRIGGER
|
|
LANGUAGE PLPGSQL
|
|
STRICT VOLATILE SECURITY DEFINER
|
|
AS $tgf_eq_insert$
|
|
BEGIN
|
|
INSERT INTO events.pending_events( event_id , evdef_id )
|
|
VALUES ( NEW.event_id , TG_ARGV[0] );
|
|
RETURN NEW;
|
|
END;
|
|
$tgf_eq_insert$;
|
|
|
|
REVOKE EXECUTE
|
|
ON FUNCTION events.tgf_eq_insert( )
|
|
FROM PUBLIC;
|
|
|
|
|
|
/*
|
|
* Create the event queuing table for a new definition
|
|
* ---------------------------------------------------
|
|
*
|
|
* /!\ INTERNAL FUNCTION /!\
|
|
*
|
|
* This function generates the event queuing table from the list of fields
|
|
* that compose an event definition. The queuing table's name will always be
|
|
* "events.evq_<type identifier>"; it will contain an event identifier
|
|
* generated from the event ID sequence (events.event_id_sequence), an empire
|
|
* identifier, a timestamp, the tick identifier, and rows corresponding to the
|
|
* event definition's fields.
|
|
*
|
|
* Parameters:
|
|
* _evdef_id The event definition's identifier
|
|
*/
|
|
DROP FUNCTION IF EXISTS events.evdef_create_queue_table( TEXT );
|
|
CREATE FUNCTION _temp_init_func(_user TEXT) RETURNS VOID LANGUAGE PLPGSQL AS $init_func$
|
|
BEGIN EXECUTE $init_code$
|
|
CREATE FUNCTION events.evdef_create_queue_table( _evdef_id TEXT )
|
|
RETURNS VOID
|
|
LANGUAGE PLPGSQL
|
|
STRICT VOLATILE SECURITY INVOKER
|
|
AS $evdef_create_queue_table$
|
|
|
|
DECLARE
|
|
_tsfx TEXT;
|
|
_rec RECORD;
|
|
_qstr TEXT;
|
|
_fname TEXT;
|
|
|
|
BEGIN
|
|
_tsfx := replace( _evdef_id , '-' , '_' );
|
|
_qstr := 'CREATE TABLE events.eq_' || _tsfx || $tbl_def$ (
|
|
event_id BIGINT NOT NULL PRIMARY KEY
|
|
DEFAULT nextval('events.event_id_sequence'::regclass) ,
|
|
event_rtime TIMESTAMP WITHOUT TIME ZONE NOT NULL
|
|
DEFAULT now( ) ,
|
|
event_gtime BIGINT NOT NULL ,
|
|
empire_id INT NOT NULL REFERENCES emp.empires ( name_id )
|
|
ON DELETE CASCADE
|
|
$tbl_def$;
|
|
|
|
FOR _rec IN SELECT *
|
|
FROM events.field_definitions
|
|
WHERE evdef_id = _evdef_id
|
|
LOOP
|
|
_fname := 'ef_' || replace( _rec.efdef_id , '-' , '_' );
|
|
_qstr := _qstr || ( CASE _rec.efdef_type
|
|
WHEN 'NUMERIC' THEN
|
|
events.efdef_ddl_numeric( _fname , _rec.efdef_optional ,
|
|
_rec.efdef_integer , _rec.efdef_min , _rec.efdef_max )
|
|
WHEN 'TEXT' THEN
|
|
events.efdef_ddl_text( _fname , _rec.efdef_optional ,
|
|
_rec.efdef_min , _rec.efdef_max )
|
|
WHEN 'BOOL' THEN
|
|
',' || _fname || ' BOOLEAN' || ( CASE
|
|
WHEN NOT _rec.efdef_optional THEN ' NOT NULL'
|
|
ELSE ''
|
|
END )
|
|
WHEN 'I18N' THEN
|
|
',' || _fname || ' VARCHAR(64)' || ( CASE
|
|
WHEN NOT _rec.efdef_optional THEN ' NOT NULL'
|
|
ELSE ''
|
|
END ) || ' REFERENCES defs.strings(name)'
|
|
WHEN 'ENTITY' THEN
|
|
events.efdef_ddl_entity( _fname , _rec.efdef_optional ,
|
|
_rec.efdef_entity )
|
|
END );
|
|
END LOOP;
|
|
|
|
_qstr := _qstr || ');';
|
|
EXECUTE _qstr;
|
|
|
|
_qstr := 'GRANT INSERT ON TABLE events.eq_' || _tsfx
|
|
|| ' TO $init_code$ || $1 || $init_code$;';
|
|
EXECUTE _qstr;
|
|
|
|
_qstr := 'CREATE TRIGGER tg_eqi_' || _tsfx || ' AFTER INSERT ON events.eq_'
|
|
|| _tsfx || ' FOR EACH ROW EXECUTE PROCEDURE events.tgf_eq_insert('''
|
|
|| _evdef_id || ''');';
|
|
EXECUTE _qstr;
|
|
END;
|
|
$evdef_create_queue_table$; $init_code$ USING $1; END; $init_func$;
|
|
SELECT _temp_init_func(:dbuser_string);
|
|
DROP FUNCTION _temp_init_func(TEXT);
|
|
|
|
REVOKE EXECUTE
|
|
ON FUNCTION events.evdef_create_queue_table( TEXT )
|
|
FROM PUBLIC;
|
|
|
|
|
|
|
|
/*
|
|
* Validate and finalise an event definition
|
|
* -----------------------------------------
|
|
*
|
|
* This function inserts the contents of the temporary event definition table
|
|
* into the main table. If everything goes well, the event queuing table is
|
|
* created and the temporary table is destroyed.
|
|
*
|
|
* Returns:
|
|
* _error An error code (see events.evdef_create_result)
|
|
* _fld The name of the field which caused the error, or NULL
|
|
* if the error was table-related or if there was no
|
|
* error.
|
|
*/
|
|
DROP FUNCTION IF EXISTS events.evdef_finalise( );
|
|
CREATE FUNCTION events.evdef_finalise(
|
|
OUT _error events.evdef_create_result ,
|
|
OUT _fld TEXT )
|
|
LANGUAGE PLPGSQL
|
|
STRICT VOLATILE SECURITY DEFINER
|
|
AS $evdef_finalise$
|
|
|
|
DECLARE
|
|
_eid TEXT;
|
|
_rec RECORD;
|
|
|
|
BEGIN
|
|
-- First create the event definition record
|
|
_fld := NULL;
|
|
_error := events.evdef_create_record( );
|
|
IF _error = 'OK'
|
|
THEN
|
|
-- Then create records for all fields
|
|
FOR _rec IN SELECT * FROM evdef_temp WHERE evfld_id IS NOT NULL
|
|
LOOP
|
|
_error := events.evdef_create_field_record(
|
|
_rec.evdef_id , _rec.evfld_id , _rec.evfld_typespec );
|
|
|
|
IF _error <> 'OK'
|
|
THEN
|
|
-- Destroy the definition record on failure
|
|
_fld := _rec.evfld_id;
|
|
DELETE FROM events.event_definitions
|
|
WHERE evdef_id = _rec.evdef_id;
|
|
EXIT;
|
|
END IF;
|
|
|
|
END LOOP;
|
|
END IF;
|
|
|
|
-- If everything went well so far, create the queueing table
|
|
IF _error = 'OK'
|
|
THEN
|
|
SELECT INTO _eid evdef_id FROM evdef_temp LIMIT 1;
|
|
PERFORM events.evdef_create_queue_table( _eid );
|
|
END IF;
|
|
|
|
DROP TABLE evdef_temp;
|
|
END;
|
|
$evdef_finalise$;
|
|
|
|
REVOKE EXECUTE
|
|
ON FUNCTION events.evdef_finalise( )
|
|
FROM PUBLIC;
|
|
GRANT EXECUTE
|
|
ON FUNCTION events.evdef_finalise( )
|
|
TO :dbuser;
|