-- Dear Mark:
-- This is to decide whether to create a single big inventory table
-- and then update as needed or to select and then either update or insert. 

-- That is, we want to test two approaches to inventory.
-- In one, we set up the contract_item_id/night pairs in advance
-- and decrement as we have sales. We have an index on contract_item_id, night
-- (By the way I don't think we need an index on id and I do think
-- we need contract_item_id,night because that's how the accesses will be done.)
-- In the other, we check whether a given contract_item_id/night pair 
-- is present. If so, we update, otherwise
-- we insert. Same indexes as in inventorybig.
-- Tim's contention is that it is possible that the second will be faster
-- because in the first case most rows are untouched.

-- inventorysmall will be a version of the table in which we 
-- have only 100,000 rows and then do upserts as needed.
-- inventorybig will have the same schema but more rows.
-- We will experiment by doing 10,000 upserts/inserts in each case.
-- Date format is just an int for convenience in generation

-- How to run this:
-- 1. The comments in the present file are callouts 
-- to Mark for how to do the loads and set up the timing harness for the tests
-- Somehow I deleted your mail on that subject. (Sorry)
-- So first fill those in.
-- 2. Bring in the files contract_item_file, inventorybig_file
-- inventorysmall_file, bigtest, smalltest
-- 3. Run the present file.





DROP TABLE contract_item;
CREATE TABLE contract_item
(
id					INTEGER NOT NULL,
available				CHAR(1) NOT NULL,
accomm_unit_def_id			INTEGER NOT NULL,
committed_stock				CHAR(1) NOT NULL
);
-- Mark: load this from the file contract_itemfile

CREATE UNIQUE INDEX contract_item1 ON contract_item(id);
CREATE INDEX contract_item2 ON contract_item(accomm_unit_def_id);



DROP TABLE inventorysmall;
CREATE TABLE inventorysmall
(
id					INTEGER NOT NULL,
available				CHAR(1) NOT NULL,	
contract_item_id			INTEGER NOT NULL,
night					INTEGER NOT NULL,
quantity				INTEGER NOT NULL
);
-- Mark: load this from inventorysmall_file

CREATE UNIQUE INDEX inventorysmall1 ON inventorysmall(id);
CREATE INDEX inventorysmall2 ON inventorysmall(contract_item_id, night);

ALTER TABLE inventorysmall
	ADD CONSTRAINT	(
			PRIMARY KEY (id)
				CONSTRAINT pkinventorysmall
			),
	ADD CONSTRAINT	(
			FOREIGN KEY (contract_item_id)
				REFERENCES contract_item(id)
				CONSTRAINT fkinventorysmall__contract_item_id
			),
	ADD CONSTRAINT	(
			CHECK (available IN ('T', 'F'))
				CONSTRAINT ckinventorysmall__available
			),
	ADD CONSTRAINT	(
			CHECK (quantity > 0)
				CONSTRAINT ckinventorysmall__quantity
			),
	ADD CONSTRAINT	(
			UNIQUE (contract_item_id, night)
				CONSTRAINT uinventorysmall__contract_item_id
			)
;



DROP TABLE inventorybig;
CREATE TABLE inventorybig
(
id					INTEGER NOT NULL,
available				CHAR(1) NOT NULL,	
contract_item_id			INTEGER NOT NULL,
night					INTEGER NOT NULL,
quantity				INTEGER NOT NULL
);
-- load this from file inventorybig_file

CREATE UNIQUE INDEX inventorybig1 ON inventorybig(id);
CREATE INDEX inventorybig2 ON inventorybig(contract_item_id, night);

ALTER TABLE inventorybig
	ADD CONSTRAINT	(
			PRIMARY KEY (id)
				CONSTRAINT pkinventorybig
			),
	ADD CONSTRAINT	(
			FOREIGN KEY (contract_item_id)
				REFERENCES contract_item(id)
				CONSTRAINT fkinventorybig__contract_item_id
			),
	ADD CONSTRAINT	(
			CHECK (available IN ('T', 'F'))
				CONSTRAINT ckinventorybig__available
			),
	ADD CONSTRAINT	(
			CHECK (quantity > 0)
				CONSTRAINT ckinventorybig__quantity
			),
	ADD CONSTRAINT	(
			UNIQUE (contract_item_id, night)
				CONSTRAINT uinventorybig__contract_item_id
			)
;



-- Mark: The tests are in two files: smalltest and bigtest
-- You need to time each of them.
-- Then run and time two tests. I suggest piping to a file.
-- smalltest > tmp
-- bigtest > tmp