<?php
ob_implicit_flush();

$conn = pg_connect("host=localhost dbname=test user=postgres");
$address_count = 5000;
$content_count = 200;
$historical_content = 50;

pg_query("DROP TABLE bigboy");
pg_query("DROP TABLE address");
pg_query("DROP SEQUENCE seq_bigboy");
pg_query("DROP SEQUENCE seq_address");

pg_query("CREATE TABLE bigboy(bigboy_id NUMERIC(11) NOT NULL PRIMARY KEY, 
				address_id NUMERIC(11) NOT NULL, 
				content_id NUMERIC(11), 
				creation_date TIMESTAMP WITH TIME ZONE NOT NULL)");
pg_query("CREATE UNIQUE INDEX address_content_unq ON bigboy(address_id, content_id)");
pg_query("CREATE INDEX bigboy_address_idx ON bigboy(address_id)");
pg_query("CREATE INDEX bigboy_content_id ON bigboy(content_id)");
pg_query("CREATE INDEX bigboy_creation_date_idx ON bigboy(creation_date)");
pg_query("CREATE TABLE address(address_id NUMERIC(11) NOT NULL PRIMARY KEY, blah VARCHAR(50) NOT NULL)");
pg_query("CREATE SEQUENCE seq_bigboy");
pg_query("CREATE SEQUENCE seq_address");


echo ("Inserting rows into address table...\n");

for($i=0; $i<$address_count; $i++)
{
	pg_query("INSERT INTO address VALUES(NEXTVAL('seq_address'),'myaddress$i@blah.com')");
	if($i % 500 == 499)
		echo ("Inserted " . ($i + 1) . " rows\n");
}

echo ("Done populating address table...\n");

for($i=0; $i<$content_count; $i++)
{
	$result = pg_query("VACUUM VERBOSE ANALYZE bigboy");
	$result = pg_query("SELECT relpages FROM pg_class WHERE relname = 'bigboy_pkey'");
	$row = pg_fetch_array($result);
	$bigboy_pkey_pages = $row['relpages'];

	$result = pg_query("SELECT relpages FROM pg_class WHERE relname = 'address_content_unq'");
	$row = pg_fetch_array($result);
	$address_content_unq_pages = $row['relpages'];

	$result = pg_query("SELECT relpages FROM pg_class WHERE relname = 'bigboy_address_idx'");
	$row = pg_fetch_array($result);
	$bigboy_address_idx_pages = $row['relpages'];

	if($i % 25 == 0)
		echo "Iteration | bigboy_pkey | address_content_unq | bigboy_address_idx\n";

	echo "$i | $bigboy_pkey_pages | $address_content_unq_pages | $bigboy_address_idx_pages\n";

	pg_query("INSERT INTO bigboy
		SELECT NEXTVAL('seq_bigboy'), address_id, $i, CURRENT_TIMESTAMP
		FROM address");

	//After we've populated the table with 250,000 rows, start chopping off the oldest
	//batch with each iteration
	if($i >= $historical_content)
		pg_query("DELETE FROM bigboy WHERE content_id = " . ($i - $historical_content));

}

	
?>