#!/bin/bash
PSQL=./bin/psql
DATABASE=test
restart_cmd='./bin/postgres --config-file=postgresql.conf -D data/ '
stop_cmd='./bin/pg_ctl -D data/ stop'

echo "starting postgresql..."
${restart_cmd} &
sleep 3
echo "init database..."
$PSQL ${DATABASE} -qXc "drop table if exists violet_B;"
$PSQL ${DATABASE} -qXc "drop table if exists violet_A;"
$PSQL ${DATABASE} -qXc "create TABLE violet_B(id int primary key,col2 int, dep varchar(20), text varchar(50),school varchar(10));"
$PSQL ${DATABASE} -qXc "insert into violet_B select generate_series(1,1000) as key, (random()*(10^4))::integer, 'dep_A',random()::varchar(50),random()::varchar(10);"
$PSQL ${DATABASE} -qXc "insert into violet_B select generate_series(1001,20000) as key, (random()*(10^4))::integer, 'dep_B',random()::varchar(50),random()::varchar(10);"
$PSQL ${DATABASE} -qXc "insert into violet_B select generate_series(20001,21000) as key, (random()*(10^4))::integer, 'dep_A',random()::varchar(50),random()::varchar(10);"
$PSQL ${DATABASE} -qXc "insert into violet_B select generate_series(21001,40000) as key, (random()*(10^4))::integer, 'dep_B',random()::varchar(50),random()::varchar(10);"
$PSQL ${DATABASE} -qXc "insert into violet_B select generate_series(41001,42000) as key, (random()*(10^4))::integer, 'dep_A',random()::varchar(50),random()::varchar(10);"
$PSQL ${DATABASE} -qXc "insert into violet_B select generate_series(42001,60000) as key, (random()*(10^4))::integer, 'dep_B',random()::varchar(50),random()::varchar(10);"
$PSQL ${DATABASE} -qXc "insert into violet_B select generate_series(60001,200000) as key, (random()*(10^4))::integer, random()::varchar(20),random()::varchar(50),random()::varchar(10);"
$PSQL ${DATABASE} -qXc "create index index_dep on violet_B(dep);"
$PSQL ${DATABASE} -qXc "create TABLE violet_A(id int primary key,event_id int, phone varchar(10), name varchar(22));"
$PSQL ${DATABASE} -qXc "insert into violet_A select generate_series(1,100000) as key, (random()*(10^5))::integer,random()::varchar(10), random()::varchar(20);"
$PSQL ${DATABASE} -qXc "analyze;"
echo "testing random_page_cost 1"
$PSQL ${DATABASE} -qXc "set random_page_cost = 1;
                        select * from violet_A JOIN violet_B ON violet_B.id=violet_A.id where dep = 'dep_A';"
echo "testing random_page_cost 2"
$PSQL ${DATABASE} -qXc "set random_page_cost = 2;
                        select * from violet_A JOIN violet_B ON violet_B.id=violet_A.id where dep = 'dep_A';"
echo "testing random_page_cost 3"
$PSQL ${DATABASE} -qXc "set random_page_cost = 3;
                        select * from violet_A JOIN violet_B ON violet_B.id=violet_A.id where dep = 'dep_A';"
echo "testing random_page_cost 4"
$PSQL ${DATABASE} -qXc "set random_page_cost = 4;
                        select * from violet_A JOIN violet_B ON violet_B.id=violet_A.id where dep = 'dep_A';"

${stop_cmd}