lwb6/legacyworlds-server-data/db-structure/tests/admin/040-functions/026-technology-dependencies/040-techdeps-redundancy.sql

/*
 * Make sure that it is not possible to create redundant dependencies
 */
BEGIN;
	\i utils/strings.sql
	-- Make the columns we don't use in the technology definition table NULL-able
	ALTER TABLE defs.technologies
		ALTER technology_category_id DROP NOT NULL ,
		ALTER technology_discovery_id DROP NOT NULL ,
		ALTER technology_description_id DROP NOT NULL ,
		ALTER technology_price DROP NOT NULL ,
		ALTER technology_points DROP NOT NULL;

	-- Create strings to use as the technologies' names
	SELECT _create_test_strings( 3 , 'tech' );
	
	-- Insert the technologies
	INSERT INTO defs.technologies ( technology_name_id )
		VALUES ( _get_string( 'tech1' ) ) ,
				( _get_string( 'tech2' ) ) ,
				( _get_string( 'tech3' ) );

	-- ***** TESTS BEGIN HERE *****
	SELECT plan( 5 );

	/*
	 * Assuming we have tech2 -> {tech1} and tech3 -> {tech1}, it shouldn't be
	 * possible to add dependencies between tech2 and tech3.
	 */
	INSERT INTO defs.technology_dependencies ( technology_name_id , technology_name_id_depends )
		VALUES ( _get_string( 'tech2' ) , _get_string( 'tech1' ) ) ,
				( _get_string( 'tech3' ) , _get_string( 'tech1' ) ); 
	SELECT diag_test_name( 'defs.technology_dependencies - B -> A and C -> A, adding B -> C is redundant' );
	SELECT throws_ok( $$
		INSERT INTO defs.technology_dependencies ( technology_name_id , technology_name_id_depends )
			VALUES ( _get_string( 'tech2' ) , _get_string( 'tech3' ) );
	$$ , 23514 );
	SELECT diag_test_name( 'defs.technology_dependencies - B -> A and C -> A, adding C -> B is redundant' );
	SELECT throws_ok( $$
		INSERT INTO defs.technology_dependencies ( technology_name_id , technology_name_id_depends )
			VALUES ( _get_string( 'tech3' ) , _get_string( 'tech2' ) );
	$$ , 23514 );
	DELETE FROM defs.technology_dependencies;

	/*
	 * Assuming we have tech3 -> {tech2,tech1}, trying to add a dependency
	 * between tech2 and tech1 would cause a redundancy.
	 */
	INSERT INTO defs.technology_dependencies ( technology_name_id , technology_name_id_depends )
		VALUES ( _get_string( 'tech3' ) , _get_string( 'tech1' ) ) ,
				( _get_string( 'tech3' ) , _get_string( 'tech2' ) ); 
	SELECT diag_test_name( 'defs.technology_dependencies - C -> A and C -> B, adding A -> B is redundant' );
	SELECT throws_ok( $$
		INSERT INTO defs.technology_dependencies ( technology_name_id , technology_name_id_depends )
			VALUES ( _get_string( 'tech1' ) , _get_string( 'tech2' ) );
	$$ , 23514 );
	SELECT diag_test_name( 'defs.technology_dependencies - C -> A and C -> B, adding B -> A is redundant' );
	SELECT throws_ok( $$
		INSERT INTO defs.technology_dependencies ( technology_name_id , technology_name_id_depends )
			VALUES ( _get_string( 'tech2' ) , _get_string( 'tech1' ) );
	$$ , 23514 );
	DELETE FROM defs.technology_dependencies;

	/*
	 * Assuming we have tech3 -> {tech2} and tech2 -> {tech1}, trying to add a
	 * dependency from tech3 to tech1 would cause a redundancy.
	 */
	INSERT INTO defs.technology_dependencies ( technology_name_id , technology_name_id_depends )
		VALUES ( _get_string( 'tech3' ) , _get_string( 'tech2' ) ) ,
				( _get_string( 'tech2' ) , _get_string( 'tech1' ) ); 
	SELECT diag_test_name( 'defs.technology_dependencies - C -> B and B -> A, adding C -> A is redundant' );
	SELECT throws_ok( $$
		INSERT INTO defs.technology_dependencies ( technology_name_id , technology_name_id_depends )
			VALUES ( _get_string( 'tech3' ) , _get_string( 'tech1' ) );
	$$ , 23514 );
	DELETE FROM defs.technology_dependencies;

	SELECT * FROM finish( );
ROLLBACK;