lwb6/legacyworlds-server-data/db-structure/parts/040-functions/003-updates.sql

-- LegacyWorlds Beta 6
-- PostgreSQL database scripts
--
-- Game updates - support functions for update definitions
--
-- Copyright(C) 2004-2012, DeepClone Development
-- --------------------------------------------------------


/*
 * Type that represents a field from a primary key
 * ------------------------------------------------
 * 
 * This type represents both the field itself (as a name) and its type.
 */
DROP TYPE IF EXISTS sys.pk_field_type CASCADE;
CREATE TYPE sys.pk_field_type AS (
	f_name			NAME ,
	f_type			NAME 
);


/*
 * Get a table's primary key fields and field types
 * -------------------------------------------------
 * 
 * This function will list all fields from a table's primary key along with
 * their types.
 * 
 * Parameters:
 *		_schema_name		The name of the schema the table resides in
 *		_table_name			The name of the table
 *
 * Returns:
 * 		?					A set of sys.pk_field_type records
 */
DROP FUNCTION IF EXISTS sys.get_table_pkey( NAME , NAME ) CASCADE;
CREATE FUNCTION sys.get_table_pkey( _schema_name NAME , _table_name NAME )
	RETURNS SETOF sys.pk_field_type
	LANGUAGE SQL
	STRICT IMMUTABLE
	SECURITY INVOKER
AS $get_table_pkey$

	SELECT _attr.attname AS f_name , _type.typname AS f_type

		FROM pg_namespace _schema
			INNER JOIN pg_class _table
				ON _table.relnamespace = _schema.oid
			INNER JOIN (
					SELECT indrelid , unnest( indkey ) AS indattnum
						FROM pg_index WHERE indisprimary
				) _index ON _index.indrelid = _table.oid
			INNER JOIN pg_attribute _attr
				ON _attr.attrelid = _table.oid
					AND _attr.attnum = _index.indattnum
			INNER JOIN pg_type _type
				ON _type.oid = _attr.atttypid

		WHERE _schema.nspname = $1
			AND _table.relname = $2

		ORDER BY _index.indattnum;

$get_table_pkey$;

REVOKE EXECUTE
	ON FUNCTION sys.get_table_pkey( NAME , NAME )
	FROM PUBLIC;



/*
 * Automatic update row insertion
 * -------------------------------
 * 
 * This trigger function is added to all type-specific update tables. It is
 * called before insertions, and will make sure a generic update row exists
 * for the new row.
 */
DROP FUNCTION IF EXISTS sys.tgf_speupd_before_insert( ) CASCADE;
CREATE FUNCTION sys.tgf_speupd_before_insert( )
		RETURNS TRIGGER
		LANGUAGE PLPGSQL
		STRICT VOLATILE
		SECURITY INVOKER
	AS $tgf_speupd_before_insert$

DECLARE
	_update_id	BIGINT;

BEGIN
	
	INSERT INTO sys.updates ( updtype_id , updtgt_id )
		VALUES ( NEW.updtype_id , NEW.updtgt_id )
		RETURNING update_id INTO _update_id;

	NEW.update_id := _update_id;
	RETURN NEW;
END;
$tgf_speupd_before_insert$;

REVOKE EXECUTE
	ON FUNCTION sys.tgf_speupd_before_insert( )
	FROM PUBLIC;



/*
 * Automatic update row removal
 * -----------------------------
 * 
 * This trigger function is added to all type-specific update tables. It is
 * called once a row has been deleted, and will remove the corresponding
 * generic update row.
 */
DROP FUNCTION IF EXISTS sys.tgf_speupd_after_delete( ) CASCADE;
CREATE FUNCTION sys.tgf_speupd_after_delete( )
		RETURNS TRIGGER
		LANGUAGE PLPGSQL
		STRICT VOLATILE
		SECURITY INVOKER
	AS $tgf_speupd_after_delete$
BEGIN

	DELETE FROM sys.updates
		WHERE update_id = OLD.update_id
			AND updtype_id = OLD.updtype_id
			AND updtgt_id = OLD.updtgt_id;

	RETURN OLD;
END;
$tgf_speupd_after_delete$;

REVOKE EXECUTE
	ON FUNCTION sys.tgf_speupd_after_delete( )
	FROM PUBLIC;



/*
 * Add missing update rows
 * ------------------------
 * 
 * This function adds rows missing from the update and specific update tables
 * for a given target type.
 * 
 * Parameters:
 *		_target_type	The identifier of the target type for which missing
 *							rows are to be inserted.
 */
DROP FUNCTION IF EXISTS sys.insert_missing_updates( INT ) CASCADE;
CREATE FUNCTION sys.insert_missing_updates( _target_type INT )
		RETURNS VOID
		LANGUAGE PLPGSQL
		STRICT VOLATILE
		SECURITY INVOKER
	AS $insert_missing_updates$

DECLARE
	_t_schema		TEXT;
	_t_table		TEXT;
	_field_list		TEXT;
	_query			TEXT;

BEGIN
	SELECT INTO _t_schema , _t_table updtgt_schema , updtgt_table
		FROM sys.update_targets
		WHERE updtgt_id = _target_type;
	SELECT INTO _field_list array_to_string( array_agg( f_name ) , ' , ' )
		FROM sys.get_table_pkey( _t_schema , _t_table );

	_query := 'INSERT INTO "' || _t_schema || '"."' || _t_table || '_updates" '
		|| '( updtgt_id , updtype_id , ' || _field_list || ') '
		|| 'SELECT updtgt_id , updtype_id , ' || _field_list
			|| ' FROM sys.update_types CROSS JOIN "'
		|| _t_schema || '"."' || _t_table || '" LEFT OUTER JOIN "'
		|| _t_schema || '"."' || _t_table
		|| '_updates" USING ( updtgt_id , updtype_id , ' || _field_list
		|| ') WHERE update_id IS NULL AND updtgt_id = ' || _target_type;
	EXECUTE _query;

END;
$insert_missing_updates$;

REVOKE EXECUTE
	ON FUNCTION sys.insert_missing_updates( INT )
	FROM PUBLIC;



/*
 * Trigger function that adds missing update rows
 * ----------------------------------------------
 * 
 * This function calls sys.insert_missing_updates. The main difference between
 * the original function and this one is that the former cannot be used in
 * triggers, while the latter is meant to be used as a trigger on the target
 * table.
 */
DROP FUNCTION IF EXISTS sys.tgf_insert_missing_updates( ) CASCADE;
CREATE FUNCTION sys.tgf_insert_missing_updates( )
		RETURNS TRIGGER
		LANGUAGE PLPGSQL
		STRICT VOLATILE
		SECURITY INVOKER
	AS $tgf_insert_missing_updates$
BEGIN
	IF TG_NARGS <> 1 THEN
		RAISE EXCEPTION 'This trigger function requires one argument';
	END IF;
	PERFORM sys.insert_missing_updates( TG_ARGV[ 0 ]::INT );
	RETURN NULL;
END;
$tgf_insert_missing_updates$;

REVOKE EXECUTE
	ON FUNCTION sys.tgf_insert_missing_updates( )
	FROM PUBLIC;


/*
 * Handle new update types
 * ------------------------
 * 
 * This function is triggered when a new update type is created; it will insert
 * the update rows for the new type.
 */
DROP FUNCTION IF EXISTS sys.tgf_updtype_after_insert_row( ) CASCADE;
CREATE FUNCTION sys.tgf_updtype_after_insert_row( )
		RETURNS TRIGGER
		LANGUAGE PLPGSQL
		STRICT VOLATILE
		SECURITY INVOKER
	AS $tgf_updtype_after_insert$
BEGIN
	PERFORM sys.insert_missing_updates( NEW.updtgt_id );
	RETURN NEW;
END;
$tgf_updtype_after_insert$;

REVOKE EXECUTE
	ON FUNCTION sys.tgf_updtype_after_insert_row( )
	FROM PUBLIC;

CREATE TRIGGER tg_updtype_after_insert_row
	AFTER INSERT ON sys.update_types
	FOR EACH ROW EXECUTE PROCEDURE sys.tgf_updtype_after_insert_row( );

	
/*
 * Update type definition check
 * -----------------------------
 * 
 * This trigger function is called when a new update type is added or when
 * an update type's stored procedure is updated. It makes sure that the
 * corresponding stored procedure actually exists.
 */
DROP FUNCTION IF EXISTS sys.tgf_check_update_type_proc( ) CASCADE;
CREATE FUNCTION sys.tgf_check_update_type_proc( )
		RETURNS TRIGGER
		LANGUAGE PLPGSQL
		STRICT VOLATILE
		SECURITY INVOKER
	AS $tgf_check_update_type_proc$
BEGIN

	IF NEW.updtype_proc_name IS NULL THEN
		RETURN NEW;
	END IF;
	
	PERFORM 1
		FROM pg_namespace _schema
			INNER JOIN pg_proc _proc
				ON _proc.pronamespace = _schema.oid
		WHERE _schema.nspname = 'sys'
			AND _proc.proname = NEW.updtype_proc_name;
	IF NOT FOUND THEN
		RAISE EXCEPTION 'Update type % has invalid update function sys.%' ,
			NEW.updtype_name , NEW.updtype_proc_name;
	END IF;

	RETURN NEW;
END;
$tgf_check_update_type_proc$;

REVOKE EXECUTE
	ON FUNCTION sys.tgf_check_update_type_proc( )
	FROM PUBLIC;

CREATE TRIGGER tg_check_update_type_proc_insert
	BEFORE INSERT ON sys.update_types
	FOR EACH ROW EXECUTE PROCEDURE sys.tgf_check_update_type_proc( );
CREATE TRIGGER tg_check_update_type_proc_update
	BEFORE UPDATE OF updtype_proc_name ON sys.update_types
	FOR EACH ROW EXECUTE PROCEDURE sys.tgf_check_update_type_proc( );


/*
 * Trigger that reorders the update types
 * ---------------------------------------
 * 
 * This function will update the ordering field of update types whenever rows
 * are inserted or deleted.
 */
DROP FUNCTION IF EXISTS sys.tgf_reorder_update_types( ) CASCADE;
CREATE FUNCTION sys.tgf_reorder_update_types( )
		RETURNS TRIGGER
		LANGUAGE PLPGSQL
		STRICT VOLATILE
		SECURITY INVOKER
	AS $tgf_reorder_update_types$

DECLARE
	_max_ordering	INT;

BEGIN
	
	SELECT INTO _max_ordering MAX( updtype_ordering ) FROM sys.update_types;
	IF _max_ordering IS NOT NULL THEN
		UPDATE sys.update_types
			SET updtype_ordering = updtype_ordering + _max_ordering * 2;
	END IF;

	UPDATE sys.update_types
		SET updtype_ordering = updtype_rownumber * 2
		FROM (
			SELECT updtype_id AS id,
					row_number( ) OVER (
							ORDER BY updtype_ordering
						) AS updtype_rownumber
				FROM sys.update_types
		) _row
		WHERE updtype_id = _row.id;

		RETURN NULL;
END;
$tgf_reorder_update_types$;

REVOKE EXECUTE
	ON FUNCTION sys.tgf_reorder_update_types( )
	FROM PUBLIC;

CREATE TRIGGER tg_updtype_after_insert_stmt
	AFTER INSERT ON sys.update_types
	FOR EACH STATEMENT EXECUTE PROCEDURE sys.tgf_reorder_update_types( );

CREATE TRIGGER tg_updtype_after_delete_stmt
	AFTER DELETE ON sys.update_types
	FOR EACH STATEMENT EXECUTE PROCEDURE sys.tgf_reorder_update_types( );



/*
 * Check target tables
 * --------------------
 * 
 * Before a new row is inserted into sys.update_targets, make sure
 * the target table it contains actually exists.
 */
DROP FUNCTION IF EXISTS sys.tgf_updtgt_before_insert( ) CASCADE;
CREATE FUNCTION sys.tgf_updtgt_before_insert( )
		RETURNS TRIGGER
		LANGUAGE PLPGSQL
		STRICT VOLATILE
		SECURITY INVOKER
	AS $tgf_updtgt_before_insert$
BEGIN
	PERFORM 1
		FROM pg_class pc
			INNER JOIN pg_namespace pn
				ON pn.oid = pc.relnamespace
			INNER JOIN pg_roles pr
				ON pr.oid = pc.relowner
		WHERE pn.nspname = NEW.updtgt_schema
			AND pc.relname = NEW.updtgt_table
			AND pc.reltype <> 0 AND pc.relkind = 'r'
			AND pr.rolname <> 'postgres';
	IF NOT FOUND THEN
		RAISE EXCEPTION 'Update target table %.% not found' ,
				NEW.updtgt_schema , NEW.updtgt_table  
			USING ERRCODE = 'foreign_key_violation';
	END IF;
	
	RETURN NEW;
END;
$tgf_updtgt_before_insert$;

REVOKE EXECUTE
	ON FUNCTION sys.tgf_updtgt_before_insert( )
	FROM PUBLIC;

CREATE TRIGGER tg_updtgt_before_insert
	BEFORE INSERT ON sys.update_targets
	FOR EACH ROW EXECUTE PROCEDURE sys.tgf_updtgt_before_insert( );



/*
 * Create update list and triggers for target table
 * -------------------------------------------------
 *
 * After a new update type has been registered, a table listing updates
 * as well as a set of triggers on the target table need to be created.
 */
DROP FUNCTION IF EXISTS sys.tgf_updtgt_after_insert( ) CASCADE;
CREATE FUNCTION sys.tgf_updtgt_after_insert( )
		RETURNS TRIGGER
		LANGUAGE PLPGSQL
		STRICT VOLATILE
	AS $tgf_updtgt_after_insert$

DECLARE
	_table_name		TEXT;
	_query			TEXT;
	_field_name		NAME;
	_field_type		NAME;
	_field_list		TEXT;

BEGIN
	_table_name := '"' || NEW.updtgt_schema || '"."' || NEW.updtgt_table || '_updates"';
	_query := 'CREATE TABLE ' || _table_name || $_table_base_fields$(
			update_id		BIGINT NOT NULL ,
			updtype_id		INT NOT NULL ,
			updtgt_id		INT NOT NULL
		$_table_base_fields$;

	-- List target table's primary key fields
	_field_list := '';
	FOR _field_name , _field_type IN
			SELECT * FROM sys.get_table_pkey( NEW.updtgt_schema , NEW.updtgt_table )
	LOOP
		_query := _query || ', "' || _field_name || '" "' || _field_type
				|| '" NOT NULL';
		IF _field_list <> '' THEN
			_field_list := _field_list || ' , ';
		END IF;
		_field_list := _field_list || '"' || _field_name || '"';
	END LOOP;
	
	_query := _query ||
		' , PRIMARY KEY ( updtgt_id , updtype_id , update_id , '
		|| _field_list || ' ) , CHECK ( updtgt_id = ' || NEW.updtgt_id || ' ) )';
	EXECUTE _query;

	-- Add foreign keys
	_query := 'ALTER TABLE ' || _table_name
		|| ' ADD CONSTRAINT fk_upd_' || NEW.updtgt_table || '_update '
			|| 'FOREIGN KEY ( update_id , updtype_id , updtgt_id ) REFERENCES sys.updates '
				|| 'ON DELETE CASCADE , '
		|| ' ADD CONSTRAINT fk_upd_' || NEW.updtgt_table || '_target '
			|| 'FOREIGN KEY ( ' || _field_list
				|| ' ) REFERENCES "' || NEW.updtgt_schema || '"."' || NEW.updtgt_table
				|| '" ON DELETE CASCADE';
	EXECUTE _query;

	-- Create automatic update creation/deletion triggers
	_query := 'CREATE TRIGGER tg_speupd_before_insert BEFORE INSERT ON ' || _table_name
		|| ' FOR EACH ROW EXECUTE PROCEDURE sys.tgf_speupd_before_insert( )';
	EXECUTE _query;
	_query := 'CREATE TRIGGER tg_speupd_after_delete AFTER DELETE ON ' || _table_name
		|| ' FOR EACH ROW EXECUTE PROCEDURE sys.tgf_speupd_after_delete( )';
	EXECUTE _query;
	
	-- Create triggers that will insert/delete update rows when new items are added/removed
	_query := 'CREATE TRIGGER tg_speupd_after_insert AFTER INSERT ON "'
		||	NEW.updtgt_schema || '"."' || NEW.updtgt_table
		|| '" FOR EACH STATEMENT EXECUTE PROCEDURE sys.tgf_insert_missing_updates( '
		|| NEW.updtgt_id || ' )';
	EXECUTE _query;

	RETURN NEW;
END;
$tgf_updtgt_after_insert$;

REVOKE EXECUTE
	ON FUNCTION sys.tgf_updtgt_after_insert( )
	FROM PUBLIC;

CREATE TRIGGER tg_updtgt_after_insert
	AFTER INSERT ON sys.update_targets
	FOR EACH ROW EXECUTE PROCEDURE sys.tgf_updtgt_after_insert( );


/*
 * Trigger function that deletes specific update tables
 * -----------------------------------------------------
 *
 * This trigger function is called after a row has been deleted from the
 * update target definitions table. It will destroy the corresponding table.
 */
DROP FUNCTION IF EXISTS sys.tgf_updtgt_after_delete( );
CREATE FUNCTION sys.tgf_updtgt_after_delete( )
		RETURNS TRIGGER
		LANGUAGE PLPGSQL
		STRICT VOLATILE
		SECURITY INVOKER
	AS $tgf_updtgt_after_delete$
DECLARE
	_query	TEXT;
BEGIN
	_query := 'DROP TABLE "' || OLD.updtgt_schema || '"."' || OLD.updtgt_table || '_updates"';
	EXECUTE _query;
	RETURN NULL;
END;
$tgf_updtgt_after_delete$;

REVOKE EXECUTE
	ON FUNCTION sys.tgf_updtgt_after_delete( )
	FROM PUBLIC;

CREATE TRIGGER tg_updtgt_after_delete
	AFTER DELETE ON sys.update_targets
	FOR EACH ROW EXECUTE PROCEDURE sys.tgf_updtgt_after_delete( );



/*
 * Stored update type registration
 * --------------------------------
 *
 * This function can be called to register an update type that uses a stored
 * procedure. The new update type is added at the end of the list of updates.
 * 
 * Since this function is meant to be used from the SQL definition code only,
 * it does not handle errors and will raise exceptions when something goes
 * wrong.
 * 
 * Parameters:
 *		_target			The name of the target for this update type
 *		_name			The name of the update type
 *		_description	The update type's description
 *		_proc			The name of the stored procedure
 */
DROP FUNCTION IF EXISTS sys.register_update_type( TEXT , TEXT , TEXT , NAME );
CREATE FUNCTION sys.register_update_type( _target TEXT , _name TEXT , _description TEXT , _proc NAME )
		RETURNS VOID
		LANGUAGE PLPGSQL
		STRICT VOLATILE
		SECURITY INVOKER
	AS $register_update_type$

DECLARE
	_target_id		INT;
	_max_ordering	INT;

BEGIN
	SELECT INTO _target_id updtgt_id
		FROM sys.update_targets
		WHERE updtgt_name = _target;

	SELECT INTO _max_ordering MAX( updtype_ordering )
		FROM sys.update_types;
	IF _max_ordering IS NULL THEN
		_max_ordering := 1;
	END IF;

	INSERT INTO sys.update_types(
			updtgt_id , updtype_name , updtype_ordering ,
			updtype_description , updtype_proc_name
		) VALUES (
			_target_id , _name , _max_ordering + 1 ,
			_description , _proc
		);
END;
$register_update_type$;


REVOKE EXECUTE
	ON FUNCTION sys.register_update_type( TEXT , TEXT , TEXT , NAME )
	FROM PUBLIC;