THE PROBLEM QUERY
-----------------

SELECT * FROM h.inventory WHERE demand_id = 289276563 ORDER BY id;

#Note that using core.inventory (which is simpler) does not have the 30-second problem.

#In general the h namespace is intended to be a human-readable diagnostic version whereas the core namespace
#is optimised for the application






h.inventory and core.inventory
------------------------------

--
-- Inventory
--
CREATE TABLE core.inventory (
	id bigint NOT NULL DEFAULT core.new_id(),
	material_id bigint NOT NULL,
	location_id bigint NOT NULL,
	qty integer NOT NULL,
	divergence integer NOT NULL DEFAULT 0,
	ctime timestamp with time zone NOT NULL DEFAULT now(),
	actor_id bigint NULL,
	demand_id bigint NULL,
	PRIMARY KEY ( id ),
	FOREIGN KEY ( material_id ) REFERENCES core.__material_id ( id ),
	FOREIGN KEY ( location_id ) REFERENCES core.__location_id ( id ),
	FOREIGN KEY ( actor_id ) REFERENCES core.actor ( id ),
	FOREIGN KEY ( demand_id ) REFERENCES core.demand ( id )
);
CREATE INDEX inventory_material_id ON core.inventory ( material_id );
CREATE INDEX inventory_location_id ON core.inventory ( location_id );
CREATE INDEX inventory_actor_id ON core.inventory ( actor_id );
CREATE INDEX inventory_demand_id ON core.inventory ( demand_id );
CREATE OR REPLACE VIEW h.inventory AS
SELECT	core.inventory.id,
	core.inventory.material_id,
	h_material.tag AS material_tag,
	h_material.name AS material_name,
	core.inventory.location_id,
	h_location.tag AS location_tag,
	h_location.name AS location_name,
	core.inventory.qty,
	core.inventory.divergence,
	core.inventory.ctime,
	core.inventory.actor_id,
	h_actor.tag AS actor_tag,
	h_actor.name AS actor_name,
	core.inventory.demand_id,
	h_demand.target_id,
	h_demand.target_tag,
	h_demand.target_name
FROM	core.inventory
	LEFT OUTER JOIN h.material AS h_material
		ON core.inventory.material_id = h_material.id
	LEFT OUTER JOIN h.location AS h_location
		ON core.inventory.location_id = h_location.id
	LEFT OUTER JOIN h.actor AS h_actor
		ON core.inventory.actor_id = h_actor.id
	LEFT OUTER JOIN h.demand AS h_demand
		ON core.inventory.demand_id = h_demand.id;





h.material and core.material
----------------------------

--
-- Weights, dimensions, and other material data
--

--
-- Materials
--
CREATE TABLE core.material (
	LIKE core.tag
		INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES,
	mass integer NOT NULL CHECK ( mass >= 0 ),
	volume integer NOT NULL CHECK ( volume >= 0 )
) INHERITS ( core.tag );
SELECT core.inherit_unique_index ( 'material', 'tag', 'id' );
SELECT core.inherit_unique_index ( 'material', 'tag', 'tag' );
SELECT core.create_inheritable_unique_index ( 'material', 'id', 'bigint' );
COMMENT ON COLUMN core.material.mass IS
	'Mass in grams';
COMMENT ON COLUMN core.material.volume IS
	'Volume in ml';
CREATE OR REPLACE VIEW h.material AS
SELECT	core.material.id,
	core.material.tag,
	core.material.name,
	core.material.mass,
	core.material.volume
FROM	core.material;












h.location and core.location
----------------------------

--
-- Locations
--
CREATE TABLE core.location (
	LIKE core.tag
		INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES
) INHERITS ( core.tag );
SELECT core.inherit_unique_index ( 'location', 'tag', 'id' );
SELECT core.inherit_unique_index ( 'location', 'tag', 'tag' );
SELECT core.create_inheritable_unique_index ( 'location', 'id', 'bigint' );
CREATE OR REPLACE VIEW h.location AS
SELECT	core.location.id,
	core.location.tag,
	core.location.name
FROM	core.location;







h.actor and core.actor
----------------------

--
-- Actors
--
CREATE TABLE core.actor (
	LIKE core.tag
		INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES
) INHERITS ( core.tag );
SELECT core.inherit_unique_index ( 'actor', 'tag', 'id' );
SELECT core.inherit_unique_index ( 'actor', 'tag', 'tag' );
CREATE OR REPLACE VIEW h.actor AS
SELECT	core.actor.id,
	core.actor.tag,
	core.actor.name
FROM	core.actor;





h.demand and core.demand
-------------------------

--
-- Demand
--
CREATE TABLE core.demand (
	id bigint NOT NULL DEFAULT core.new_id(),
	target_id bigint NOT NULL,
	material_id bigint NOT NULL,
	qty integer NOT NULL,
	-- HACK
	benefit integer NOT NULL DEFAULT 0,
	PRIMARY KEY ( id ),
	UNIQUE ( target_id, material_id ),
	FOREIGN KEY ( target_id ) REFERENCES core.waypoint ( id ),
	FOREIGN KEY ( material_id ) REFERENCES core.__material_id ( id )
);
CREATE INDEX demand_target_id ON core.demand ( target_id );
CREATE INDEX demand_material_id ON core.demand ( material_id );
CREATE OR REPLACE VIEW h.demand AS
SELECT	core.demand.id,
	core.demand.target_id,
	h_target_waypoint.tag AS target_tag,
	h_target_waypoint.name AS target_name,
	core.demand.material_id,
	h_material.tag AS material_tag,
	h_material.name AS material_name,
	core.demand.qty,
	core.demand.benefit
FROM	core.demand
	LEFT OUTER JOIN h.waypoint AS h_target_waypoint
		ON core.demand.target_id = h_target_waypoint.id
	LEFT OUTER JOIN h.material AS h_material
		ON core.demand.material_id = h_material.id;




h.waypoint and core.waypoint
----------------------------

--
-- Waypoints
--
CREATE TABLE core.waypoint (
	LIKE core.location
		INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES,
	is_router boolean NOT NULL,
	is_target boolean NOT NULL,
	is_packer boolean NOT NULL
) INHERITS ( core.location );
SELECT core.inherit_unique_index ( 'waypoint', 'tag', 'id' );
SELECT core.inherit_unique_index ( 'waypoint', 'tag', 'tag' );
SELECT core.inherit_unique_index ( 'waypoint', 'location', 'id' );
COMMENT ON COLUMN core.waypoint.is_router IS
	'Routing decisions may be made at this waypoint';
COMMENT ON COLUMN core.waypoint.is_target IS
	'Routing may be aimed towards this waypoint';
COMMENT ON COLUMN core.waypoint.is_packer IS
	'Containerisation takes place at this waypoint';
CREATE OR REPLACE VIEW h.waypoint AS
SELECT	core.waypoint.id,
	core.waypoint.tag,
	core.waypoint.name,
	core.waypoint.is_router,
	core.waypoint.is_target,
	core.waypoint.is_packer
FROM	core.waypoint;




