lwb6/legacyworlds-server-data/db-structure/parts/030-data/100-universe.sql

-- LegacyWorlds Beta 6
-- PostgreSQL database scripts
--
-- Stellar systems and planets
--
-- Copyright(C) 2004-2010, DeepClone Development
-- --------------------------------------------------------


--
-- Stellar systems
--
CREATE TABLE verse.systems(
	id		SERIAL NOT NULL PRIMARY KEY ,
	x		INT NOT NULL ,
	y		INT NOT NULL
);

CREATE UNIQUE INDEX idx_systems_coordinates
	ON verse.systems( x , y );

--
-- Planets
--
CREATE TABLE verse.planets(
	name_id		INT NOT NULL PRIMARY KEY ,
	system_id	INT NOT NULL ,
	orbit		INT NOT NULL
					CHECK( orbit BETWEEN 1 AND 5 ) ,
	picture		INT NOT NULL ,
	population	REAL NOT NULL
					CHECK( population >= 0 )
);

CREATE UNIQUE INDEX idx_planets_coordinates
	ON verse.planets( system_id , orbit );
	
ALTER TABLE verse.planets
	ADD CONSTRAINT fk_planets_name
		FOREIGN KEY (name_id) REFERENCES naming.map_names ,
	ADD CONSTRAINT fk_planets_system
		FOREIGN KEY (system_id) REFERENCES verse.systems;



/*
 * Resource providers
 * -------------------
 * 
 * A resource provider allows natural resources to be extracted. Resource
 * providers are initialised by the universe generator and updated when
 * mining results are computed.
 * 
 * For now, resource providers are bound to planets. Each planet may only
 * have one provider for a given type of natural resource.
 */
CREATE TABLE verse.resource_providers(

	/* The identifier of the planet the resource provider is bound to. */
	planet_id				INT NOT NULL ,

	/* The identifier of the natural resource that can be mined from this
	 * resource provider.
	 */
	resource_name_id		INT NOT NULL ,

	/* The maximal quantity of resource units in the provider */ 
	resprov_quantity_max	DOUBLE PRECISION NOT NULL
								CHECK( resprov_quantity_max > 0 ) ,
	/* The current quantity of resources */
	resprov_quantity		DOUBLE PRECISION NOT NULL
								CHECK( resprov_quantity >= 0 ) ,

	/* The extraction difficulty, which affects the amount of work required to
	 * extract resources.
	 */
	resprov_difficulty		DOUBLE PRECISION NOT NULL
								CHECK( resprov_difficulty BETWEEN 0 AND 1 ) ,

	/* The provider's recovery rate, which determines how fast the provider's
	 * resources are regenerated.
	 */
	resprov_recovery		DOUBLE PRECISION NOT NULL
								CHECK( resprov_recovery > 0
									AND resprov_recovery <= 1 ) ,

	/* Primary key on (planet,type of resource) */
	PRIMARY KEY( planet_id , resource_name_id ) ,

	/* Make sure the quantity is always equal or smaller than the maximal
	 * quantity.
	 */
	CHECK( resprov_quantity <= resprov_quantity_max )
);

CREATE INDEX idx_resprov_resource
	ON verse.resource_providers( resource_name_id );

ALTER TABLE verse.resource_providers
	ADD CONSTRAINT fk_resprov_planet
		FOREIGN KEY ( planet_id ) REFERENCES verse.planets ,
	ADD CONSTRAINT fk_resprov_resource
		FOREIGN KEY ( resource_name_id ) REFERENCES defs.natural_resources;


--
-- Happiness
--
CREATE TABLE verse.planet_happiness(
	planet_id	INT NOT NULL PRIMARY KEY ,
	target		REAL NOT NULL
					CHECK( target BETWEEN 0.0 AND 1.0 ) ,
	current		REAL NOT NULL
					CHECK( current > 0 )
);

ALTER TABLE verse.planet_happiness
	ADD CONSTRAINT fk_planethappiness_planet
		FOREIGN KEY (planet_id) REFERENCES verse.planets;


--
-- Money
--
CREATE TABLE verse.planet_money(
	planet_id	INT NOT NULL PRIMARY KEY ,
	income		REAL NOT NULL
					CHECK( income >= 0 ) ,
	upkeep		REAL NOT NULL
					CHECK( upkeep >= 0 )
);

ALTER TABLE verse.planet_money
	ADD CONSTRAINT fk_planetmoney_planet
		FOREIGN KEY (planet_id) REFERENCES verse.planets;



/*
 * Planet resource changes
 * ------------------------
 * 
 * This table stores the results of planet resource updates. It will then be
 * used to update the owning empires' resources.
 * 
 * This table applies to both basic and natural resources. 
 */
CREATE TABLE verse.planet_resources(

	/* Identifier of the planet */
	planet_id			INT NOT NULL ,

	/* Identifier of the resource type */
	resource_name_id	INT NOT NULL ,

	/* Quantity of that resource which was somehow gained at the last game
	 * update.
	 */
	pres_income			DOUBLE PRECISION NOT NULL
							DEFAULT 0
							CHECK( pres_income >= 0 ) ,

	/* Quantity of that resource used by the planet's various buildings at the
	 * last game update.
	 */
	pres_upkeep			DOUBLE PRECISION NOT NULL
							DEFAULT 0
							CHECK( pres_upkeep >= 0 ) ,

	/* Primary key on (planet,type of resource) */
	PRIMARY KEY( planet_id , resource_name_id )
);

CREATE INDEX idx_pres_resource
	ON verse.planet_resources( resource_name_id );

ALTER TABLE verse.planet_resources
	ADD CONSTRAINT fk_pres_planet
		FOREIGN KEY ( planet_id ) REFERENCES verse.planets ,
	ADD CONSTRAINT fk_pres_resource
		FOREIGN KEY ( resource_name_id ) REFERENCES defs.resources;


--
-- Buildings
--
CREATE TABLE verse.planet_buildings(
	planet_id		INT NOT NULL ,
	building_id		INT NOT NULL ,
	amount			INT NOT NULL CHECK( amount >= 0 ) ,
	damage			REAL NOT NULL CHECK( damage >= 0 ) ,
	PRIMARY KEY( planet_id , building_id )
);

CREATE INDEX idx_planetbuildings_building
	ON verse.planet_buildings (building_id);

ALTER TABLE verse.planet_buildings
	ADD CONSTRAINT fk_planetbuildings_planet
		FOREIGN KEY (planet_id) REFERENCES verse.planets ,
	ADD CONSTRAINT fk_planetbuildings_building
		FOREIGN KEY (building_id) REFERENCES tech.buildings;