lwb6/legacyworlds-server-data/db-structure/parts/040-functions/026-technology-dependencies.sql

-- LegacyWorlds Beta 6
-- PostgreSQL database scripts
--
-- Functions and triggers that maintain the technology
-- dependency cache and, generally speaking, the integrity
-- of the technology graph.
--
-- Copyright(C) 2004-2012, DeepClone Development
-- --------------------------------------------------------


/*
 * Dependency tree copy function
 * ------------------------------
 * 
 * This function is used when a dependency is added. It allows a dependency
 * tree to be copied as the child of another node on a different tree.
 *
 * Parameters:
 *		_reverse		Whether the tree being copied is a reverse or
 *							forward dependency tree
 *		_src			Identifier of the source tree
 *		_dest			Identifier of the destination tree
 *		_cache			Identifier of the cache entry to use as a parent
 *		_depth			Depth of the parent node
 *		_dependency		Identifier of the dependency being added
 */
DROP FUNCTION IF EXISTS defs.tdcache_copy_tree(
		BOOLEAN , INT , INT , INT , INT , INT ) CASCADE;
CREATE FUNCTION defs.tdcache_copy_tree(
			_reverse	BOOLEAN ,
			_src		INT ,
			_dest		INT ,
			_cache		INT ,
			_depth		INT ,
			_dependency	INT )
		RETURNS VOID
		LANGUAGE PLPGSQL
		STRICT VOLATILE
	AS $tdcache_copy_tree$

DECLARE
	_record		RECORD;
	_new_entry	INT;

BEGIN
	CREATE TEMPORARY TABLE tdcache_copy_ids(
		old_id	INT ,
		new_id	INT
	);

	FOR _record IN
		SELECT * FROM defs.techdep_cache
			WHERE technology_name_id = _src
				AND tdcache_reverse = _reverse
			ORDER BY tdcache_depth ASC
	LOOP
		-- Set source of copy
		IF _record.tdcache_id_copyof IS NULL THEN
			_record.technology_name_id_copyof := _src;
			_record.tdcache_id_copyof := _record.tdcache_id;
		END IF;

		IF _record.tdcache_id_parent IS NULL THEN
			-- Set parent and dependency if there is none
			_record.tdcache_id_parent := _cache;
			_record.techdep_id := _dependency;
		ELSE
			-- Remap child nodes to the tree's copy
			SELECT INTO _record.tdcache_id_parent new_id
				FROM tdcache_copy_ids
				WHERE old_id = _record.tdcache_id_parent;
		END IF;

		-- Set depth
		_record.tdcache_depth := _record.tdcache_depth + _depth;

		-- Insert new cache entry and add mapping to temporary table
		INSERT INTO defs.techdep_cache(
				technology_name_id , tdcache_reverse , tdcache_id_parent ,
				tdcache_depth , technology_name_id_copyof ,
				tdcache_id_copyof , techdep_id
			) VALUES (
				_dest , _reverse , _record.tdcache_id_parent ,
				_record.tdcache_depth , _record.technology_name_id_copyof ,
				_record.tdcache_id_copyof , _record.techdep_id
			) RETURNING tdcache_id INTO _new_entry;

		INSERT INTO tdcache_copy_ids
			VALUES ( _record.tdcache_id , _new_entry );
	END LOOP;

	DROP TABLE tdcache_copy_ids;
END;
$tdcache_copy_tree$;

REVOKE EXECUTE
	ON FUNCTION defs.tdcache_copy_tree(
			BOOLEAN , INT , INT , INT , INT , INT )
	FROM PUBLIC;



/*
 * Add a sub-tree to all copies of some node
 * ------------------------------------------
 * 
 * This function copies a source tree to all copies for a given technology and
 * direction.
 * 
 * Parameters:
 *		_reverse	Whether the tree being copied represents reverse or
 *						forward dependencies
 *		_src		The identifier of the technology whose dependency tree
 *						needs to be copied
 *		_dest		The identifier of the technology onto which the source
 *						tree is to be grafted
 *		_dependency	The identifier of the dependency because of which the
 *						copy is being carried out
 */
DROP FUNCTION IF EXISTS defs.tdcache_set_child( BOOLEAN , INT , INT , INT );
CREATE FUNCTION defs.tdcache_set_child(
			_reverse	BOOLEAN ,
			_src		INT ,
			_dest		INT ,
			_dependency	INT )
		RETURNS VOID
		LANGUAGE PLPGSQL
		STRICT VOLATILE
	AS $tdcache_set_child$

DECLARE
	_tree	INT;
	_entry	INT;
	_depth	INT;

BEGIN
	FOR _tree , _entry , _depth IN
		SELECT technology_name_id , tdcache_id , tdcache_depth + 1
			FROM defs.techdep_cache
			WHERE tdcache_reverse = _reverse
				AND technology_name_id_copyof = _dest
	LOOP
		PERFORM defs.tdcache_copy_tree( _reverse , _src , _tree , _entry , _depth , _dependency );
	END LOOP;

END;
$tdcache_set_child$;

REVOKE EXECUTE
	ON FUNCTION defs.tdcache_set_child( BOOLEAN , INT , INT , INT )
	FROM PUBLIC;



/*
 * Trigger function that handles new technology definitions
 * ---------------------------------------------------------
 * 
 * When a new technology definition is added, a pair of records needs to be
 * inserted into the dependency cache. These records correspond to the root
 * of the trees for the technology for both forward and reverse dependencies.
 */
DROP FUNCTION IF EXISTS defs.tgf_technologies_ai( ) CASCADE;
CREATE FUNCTION defs.tgf_technologies_ai( )
		RETURNS TRIGGER
		LANGUAGE PLPGSQL
		STRICT VOLATILE
		SECURITY DEFINER
	AS $tgf_technologies_ai$
BEGIN

	INSERT INTO defs.techdep_cache (
			technology_name_id , tdcache_reverse , tdcache_depth , technology_name_id_copyof
		) VALUES (
			NEW.technology_name_id , FALSE , 0 , NEW.technology_name_id
		) , (
			NEW.technology_name_id , TRUE , 0 , NEW.technology_name_id
		);
	RETURN NEW;

END;
$tgf_technologies_ai$;

REVOKE EXECUTE
	ON FUNCTION defs.tgf_technologies_ai()
	FROM PUBLIC;

CREATE TRIGGER tg_technologies_ai
	AFTER INSERT ON defs.technologies
		FOR EACH ROW EXECUTE PROCEDURE defs.tgf_technologies_ai( );



/*
 * Trigger function that verifies new dependencies
 * ------------------------------------------------
 *
 * This trigger function locks all related trees, then check that the new
 * dependency does not lead to cycles or redundant dependencies.
 */
DROP FUNCTION IF EXISTS defs.tgf_techdeps_bi( ) CASCADE;
CREATE FUNCTION defs.tgf_techdeps_bi( )
		RETURNS TRIGGER
		LANGUAGE PLPGSQL
		STRICT VOLATILE
		SECURITY DEFINER
	AS $tgf_techdeps_bi$
BEGIN
	-- Lock all trees
	PERFORM 1
		FROM defs.techdep_cache n1
			INNER JOIN defs.techdep_cache n2
				USING ( technology_name_id )
		WHERE n1.technology_name_id_copyof IN (
				NEW.technology_name_id , NEW.technology_name_id_depends )
		FOR UPDATE OF n2;

	-- Check for cycles
	PERFORM 1 FROM defs.techdep_cache
		WHERE technology_name_id = NEW.technology_name_id
			AND technology_name_id_copyof = NEW.technology_name_id_depends
			AND tdcache_reverse;
	IF FOUND THEN
		RAISE EXCEPTION 'Cycle detected'
			USING ERRCODE = 'check_violation';
	END IF;

	-- Check for redundant dependencies
	PERFORM  1
		FROM defs.techdep_cache n1
			INNER JOIN defs.technology_dependencies d
				ON d.technology_name_id = n1.technology_name_id_copyof
		WHERE n1.technology_name_id = NEW.technology_name_id
			AND n1.tdcache_reverse
			AND d.technology_name_id_depends = NEW.technology_name_id_depends;
	IF FOUND THEN
		RAISE EXCEPTION '% is a dependency of a technology that depends on %' ,
				NEW.technology_name_id_depends , NEW.technology_name_id
			USING ERRCODE = 'check_violation';
	END IF;

	PERFORM 1
		FROM defs.technology_dependencies d1
			INNER JOIN defs.techdep_cache n
				ON n.technology_name_id = d1.technology_name_id_depends
		WHERE d1.technology_name_id = NEW.technology_name_id
			AND n.tdcache_reverse
			AND n.technology_name_id_copyof = NEW.technology_name_id_depends;
	IF FOUND THEN
		RAISE EXCEPTION '% depends on a dependency of %' ,
				NEW.technology_name_id , NEW.technology_name_id_depends
			USING ERRCODE = 'check_violation';
	END IF;

	PERFORM 1 FROM defs.techdep_cache
		WHERE technology_name_id = NEW.technology_name_id
			AND technology_name_id_copyof = NEW.technology_name_id_depends
			AND NOT tdcache_reverse;
	IF FOUND THEN
		RAISE EXCEPTION '% is already a dependency of %' ,
				NEW.technology_name_id_depends , NEW.technology_name_id
			USING ERRCODE = 'check_violation';
	END IF;

	RETURN NEW;
END;
$tgf_techdeps_bi$;


CREATE TRIGGER tg_techdeps_bi
	BEFORE INSERT ON defs.technology_dependencies
		FOR EACH ROW EXECUTE PROCEDURE defs.tgf_techdeps_bi( );

REVOKE EXECUTE
	ON FUNCTION defs.tgf_techdeps_bi( )
	FROM PUBLIC;


/*
 * Trigger function that updates the dependency cache
 * ---------------------------------------------------
 * 
 * After a new, valid dependency has been added, trees for both forward and
 * reverse depdencies must be copied.
 */
DROP FUNCTION IF EXISTS defs.tgf_techdeps_ai( ) CASCADE;
CREATE FUNCTION defs.tgf_techdeps_ai( )
		RETURNS TRIGGER
		LANGUAGE PLPGSQL
		STRICT VOLATILE
		SECURITY DEFINER
	AS $tgf_techdeps_ai$
BEGIN

	PERFORM defs.tdcache_set_child( FALSE ,
		NEW.technology_name_id_depends , NEW.technology_name_id ,
		NEW.techdep_id );

	PERFORM defs.tdcache_set_child( TRUE ,
		NEW.technology_name_id , NEW.technology_name_id_depends ,
		NEW.techdep_id );

	RETURN NEW;
END;
$tgf_techdeps_ai$;

CREATE TRIGGER tg_techdeps_ai
	AFTER INSERT ON defs.technology_dependencies
		FOR EACH ROW EXECUTE PROCEDURE defs.tgf_techdeps_ai( );

REVOKE EXECUTE
	ON FUNCTION defs.tgf_techdeps_ai( )
	FROM PUBLIC;


/*
 * Technology dependencies view
 * -----------------------------
 * 
 * This view generates a parseable list of dependencies per technology.
 * 
 * Columns:
 *		technology_name_id		The technology's name
 *		technology_dependencies	A list of comma-separated technology name
 *									identifiers
 */
DROP VIEW IF EXISTS defs.technology_dependencies_view CASCADE;
CREATE VIEW defs.technology_dependencies_view
	AS SELECT technology_name_id ,
			array_to_string( array_agg( _name_str.name ) , ',' ) AS technology_dependencies
		FROM defs.technologies _tech
			LEFT OUTER JOIN defs.technology_dependencies
				USING ( technology_name_id )
			LEFT OUTER JOIN defs.strings _name_str
				ON _name_str.id = technology_name_id_depends
		GROUP BY technology_name_id;