--=================================================================
CREATE TABLE cfg_item (
	--= item context
	user CURRENT_USER,
	machine VARCHAR(40) NOT NULL DEFAULT 'default',
	cookie VARCHAR(40) DEFAULT 'default',

	--= item definition
	name VARCHAR(20) NOT NULL DEFAULT 'must set this !',
	value TEXT NOT NULL DEFAULT 'must set this !',
	type varchar(20) NOT NULL DEFAULT 'string',
);

COMMENT ON TABLE cfg_item
 IS 'all configuration options are stored in this table';

-- the following three items establish the logical/business context of the option
COMMENT ON COLUMN cfg_item.user
 IS 'the database level user this option belongs to; this is the "role" of the user from the perspective of the database; can be "default" at the application level to indicate that it we do not care';
COMMENT ON COLUMN cfg_item.machine
 IS 'the logical workplace this option pertains to; can be a hostname but should be a logical rather than a physical identifier, machines get moved, workplaces do not; kind of like a "role" for the machine; associate this with a physical machine through a local config file or environment variable; can be "default" if we do not care';
COMMENT ON COLUMN cfg_item.cookie
 IS 'an arbitrary, opaque entity the client code can use to associate this config item with even finer grained context; could be the pertinent patient ID for patient specific options; can default to "default"';

-- item definition
COMMENT ON COLUMN cfg_item.name
 IS 'the name of the option; this MUST be set to something meaningful';
COMMENT ON COLUMN cfg_item.value
 IS 'the value of the option converted to string; this MUST be set to something meaningful';
COMMENT ON COLUMN cfg_item.type
 IS 'type of the value; this is particularly ugly, we should use a better scheme here';

--!!!! should we use a smarter scheme for the value<->type problem ?

--========
CREATE TABLE cfg_desc (
	item_id INTEGER REFERENCES cfg_item,
	group VARCHAR(20) DEFAULT 'default',
	description TEXT NOT NULL
);

COMMENT ON TABLE cfg_desc
 IS 'arbitrary descriptions related to particular config items; this is primarily intended for config management clients';

COMMENT ON COLUMN cfg_desc.item_id
 IS 'the OID of the item this description belongs to'
COMMENT ON COLUMN cfg_desc.group
 IS 'just for logical grouping of options according to task sets to facilitate better config management';
COMMENT ON COLUMN cfg_desc.description
 IS 'now, this is what this table is all about, arbitrary information related to the described config item'
--=================================================================