<commit_before># go to bigdawgmiddle directory
# <NAME>
# logs=# select * from logs where logger like '%FromSciDBToPostgresImplementation%' and message like 'Migration result,%Bin%FULL' order by time desc;

# install updates
sudo apt-get update
sudo apt-get install -y libreadline-dev zlib1g-dev

initial_dir=$(pwd)
mkdir Downloads
cd Downloads
downloads_dir=$(pwd)

# Download and extract postgres
wget https://ftp.postgresql.org/pub/source/v9.4.5/postgresql-9.4.5.tar.gz
tar -xf postgresql-9.4.5.tar.gz

# get catalog resource
resources=${initial_dir}/../src/main/resources/
catalog_resource=${resources}catalog/
monitor_resource=${resources}/monitor/
schemas_resource=${resources}/schemas/
catalog_db=bigdawg_catalog
database2=mimic2_copy

# database for logs
log_db=logs

# main user
pguser=pguser

# ports for postgres
port_1=5431
port_2=5430

# bin paths
postgres1_bin=${downloads_dir}/postgres1/bin
postgres2_bin=${downloads_dir}/postgres2/bin

# tpch
scale_factor=1
#TABLES_TPCH_POSTGRES1=(region part partsupp orders)
TABLES_TPCH_POSTGRES1=(region part partsupp orders nation supplier customer lineitem) # for tests
#TABLES_TPCH_POSTGRES2=(nation supplier customer lineitem)
TABLES_TPCH_POSTGRES2=(region part partsupp orders nation supplier customer lineitem) # for tests

# auxiliary function: this compiles the code, intalls and configures the database
function setDB {
    postgres_version=$1
    port=$2
    init_dir=$(pwd)
    cd ${downloads_dir}
    cd postgresql-9.4.5

    ./configure --prefix=${downloads_dir}/${postgres_version}
    make
    make install

    postgres_data=${downloads_dir}/${postgres_version}/data
    mkdir -p ${postgres_data}

    postgres_bin=${downloads_dir}/${postgres_version}/bin
    cd ${postgres_bin}
    ./initdb -D ${postgres_data}
    cp ${postgres_data}/postgresql.conf ${postgres_data}/postgresql.conf.backup

    # dies here
    python ${initial_dir}/change_port.py -f ${postgres_data}/postgresql.conf -p ${port}
    ./pg_ctl -w start -l postgres.log -D ${postgres_data}
    cd ${init_dir}
}

# main function: download and install postgres instances (postgres1 and postgres2)
function install_postgres {
    cd ${downloads_dir}
    wget https://ftp.postgresql.org/pub/source/v9.4.5/postgresql-9.4.5.tar.gz
    tar -xf postgresql-9.4.5.tar.gz

    setDB postgres1 ${port_1}
    setDB postgres2 ${port_2}

    cd ${postgres1_bin}
    ./createuser -p ${port_1} -s -e -d postgres

    cd ${postgres2_bin}
    ./createuser -p ${port_2} -s -e -d postgres
}

# main function: this prepares the basic data in intance: postgres1
function prepare_postgres1 {
    cd ${postgres1_bin}
    ./psql -p ${port_1} -c "alter role postgres with password '<PASSWORD>'" -d template1

    # clean
    ./psql -p ${port_1} -c "drop database if exists ${log_db}" -d template1 -U postgres
    ./psql -p ${port_1} -c "drop database if exists mimic2" -d template1 -U postgres
    ./psql -p ${port_1} -c "drop database if exists test" -d template1 -U postgres

    ./psql -p ${port_1} -c "drop role if exists ${pguser}" -d template1
    ./createuser -p ${port_1} -s -e -d ${pguser}


    bash ${catalog_resource}/recreate_catalog.sh ${catalog_resource} ${downloads_dir} ${port_1} ${postgres1_bin} ${pguser} ${catalog_db}

    ./psql -p ${port_1} -c "create database mimic2 owner ${pguser}" -d template1
    ./psql -p ${port_1} -f ${initial_dir}/data/mimic2.pgd -U ${pguser} -d mimic2

    # tables for monitor
    ./psql -p ${port_1} -f ${monitor_resource}/monitor.sql -d ${catalog_db}

    # log_db
    ./psql -p ${port_1} -c "create database ${log_db} owner ${pguser}" -d template1
    ./psql -p ${port_1} -f ${initial_dir}/../src/main/resources/create_logs_table.sql -d ${log_db}

    # tests
    ./psql -p ${port_1} -c "create database test owner ${pguser}" -d template1 -U postgres
}

# main function: this prepares the basic data in intance: postgres2
function prepare_postgres2 {
    cd ${postgres2_bin}
    ./psql -p ${port_2} -c "alter role postgres with password '<PASSWORD>'" -d template1

    # clean the databases
    ./psql -p ${port_2} -c "drop database if exists ${database2}" -d template1 -U postgres

    ./psql -p ${port_2} -c "drop role if exists ${pguser}" -d template1
    ./createuser -p ${port_2} -s -e -d ${pguser}
    ./psql -p ${port_2} -c "alter role ${pguser} with password '<PASSWORD>'" -d template1

    ./psql -p ${port_2} -c "create database ${database2} owner ${pguser}" -d template1
    ./psql -p ${port_2} -f ${initial_dir}/data/mimic2.pgd -U ${pguser} -d ${database2}
    # ./psql -p ${port_2} -c "create database ${database2} owner ${pguser}" -d template1
    # ./psql -p ${port_2} -c "create schema mimic2v26" -d ${database2} -U pguser
    ./psql -p ${port_2} -f ${initial_dir}/../scripts/mimic2_sql/d_patients.sql -d ${database2}

    # tests
    ./psql -p ${port_2} -c "create database test owner ${pguser}" -d template1 -U postgres

    # schemas
    bash ${schemas_resource}/recreate_schemas.sh ${schemas_resource} ${downloads_dir} ${port_1} ${port_2} ${catalog_db} ${postgres1_bin} ${postgres2_bin}

}

# auxiliary function to (re)create tpch database and load the tpch arrays
function load_tables {
    # read arguments
    postgres_bin=${1}
    port=${2}
    declare -a tables=("${!3}")
    # prepare database tpch
    ${postgres_bin}/psql -p ${port} -c "drop database if exists tpch" -d template1
    ${postgres_bin}/psql -p ${port} -c "create database tpch owner ${pguser}" -d template1
    # load the tables
    for table in ${tables[*]}; do
	echo crating and loading table: ${table}
	create_table_sql=$(cat tables/${table}.sql)
	${postgres_bin}/psql -p ${port} -c "begin;${create_table_sql}; copy ${table} from '${dbgen_dir}/${table}.tbl' with (format csv, delimiter '|', freeze); commit;" -d tpch
    done;
}

# main function
function load_tpch {<commit_msg>setup script runs for new vagrant installs<commit_after># go to bigdawgmiddle directory
# <NAME>
# logs=# select * from logs where logger like '%FromSciDBToPostgresImplementation%' and message like 'Migration result,%Bin%FULL' order by time desc;

# install updates
sudo apt-get update
sudo apt-get install -y libreadline-dev zlib1g-dev

initial_dir=$(pwd)

# use this for vagrant
ln -s ~/Downloads Downloads

# OR use this for a non-vagrant setup
mkdir Downloads

cd Downloads
downloads_dir=$(pwd)

# Download and extract postgres
wget https://ftp.postgresql.org/pub/source/v9.4.5/postgresql-9.4.5.tar.gz
tar -xf postgresql-9.4.5.tar.gz

# get catalog resource
resources=${initial_dir}/../src/main/resources/
catalog_resource=${resources}catalog/
monitor_resource=${resources}/monitor/
schemas_resource=${resources}/schemas/
catalog_db=bigdawg_catalog
database2=mimic2_copy

# database for logs
log_db=logs

# main user
pguser=pguser

# ports for postgres
port_1=5431
port_2=5430

# bin paths
postgres1_bin=${downloads_dir}/postgres1/bin
postgres2_bin=${downloads_dir}/postgres2/bin

# tpch
scale_factor=1
#TABLES_TPCH_POSTGRES1=(region part partsupp orders)
TABLES_TPCH_POSTGRES1=(region part partsupp orders nation supplier customer lineitem) # for tests
#TABLES_TPCH_POSTGRES2=(nation supplier customer lineitem)
TABLES_TPCH_POSTGRES2=(region part partsupp orders nation supplier customer lineitem) # for tests

# auxiliary function: this compiles the code, intalls and configures the database
function setDB {
    postgres_version=$1
    port=$2
    init_dir=$(pwd)
    cd ${downloads_dir}
    cd postgresql-9.4.5

    ./configure --prefix=${downloads_dir}/${postgres_version}
    make
    make install

    postgres_data=${downloads_dir}/${postgres_version}/data
    mkdir -p ${postgres_data}

    postgres_bin=${downloads_dir}/${postgres_version}/bin
    cd ${postgres_bin}
    ./initdb -D ${postgres_data}
    cp ${postgres_data}/postgresql.conf ${postgres_data}/postgresql.conf.backup

    python ${initial_dir}/change_port.py -f ${postgres_data}/postgresql.conf -p ${port}
    ./pg_ctl -w start -l postgres.log -D ${postgres_data}
    cd ${init_dir}
}

# main function: download and install postgres instances (postgres1 and postgres2)
function install_postgres {
    cd ${downloads_dir}
    wget https://ftp.postgresql.org/pub/source/v9.4.5/postgresql-9.4.5.tar.gz
    tar -xf postgresql-9.4.5.tar.gz

    setDB postgres1 ${port_1}
    setDB postgres2 ${port_2}

    cd ${postgres1_bin}
    ./createuser -p ${port_1} -s -e -d postgres

    cd ${postgres2_bin}
    ./createuser -p ${port_2} -s -e -d postgres
}

# main function: this prepares the basic data in intance: postgres1
function prepare_postgres1 {
    cd ${postgres1_bin}
    ./psql -p ${port_1} -c "alter role postgres with password '<PASSWORD>'" -d template1

    # clean
    ./psql -p ${port_1} -c "drop database if exists ${log_db}" -d template1 -U postgres
    ./psql -p ${port_1} -c "drop database if exists mimic2" -d template1 -U postgres
    ./psql -p ${port_1} -c "drop database if exists test" -d template1 -U postgres

    ./psql -p ${port_1} -c "drop role if exists ${pguser}" -d template1
    ./createuser -p ${port_1} -s -e -d ${pguser}


    bash ${catalog_resource}/recreate_catalog.sh ${catalog_resource} ${downloads_dir} ${port_1} ${postgres1_bin} ${pguser} ${catalog_db}

    ./psql -p ${port_1} -c "create database mimic2 owner ${pguser}" -d template1
    ./psql -p ${port_1} -f ${initial_dir}/data/mimic2.pgd -U ${pguser} -d mimic2

    # tables for monitor
    ./psql -p ${port_1} -f ${monitor_resource}/monitor.sql -d ${catalog_db}

    # log_db
    ./psql -p ${port_1} -c "create database ${log_db} owner ${pguser}" -d template1
    ./psql -p ${port_1} -f ${initial_dir}/../src/main/resources/create_logs_table.sql -d ${log_db}

    # tests
    ./psql -p ${port_1} -c "create database test owner ${pguser}" -d template1 -U postgres
}

# main function: this prepares the basic data in intance: postgres2
function prepare_postgres2 {
    cd ${postgres2_bin}
    ./psql -p ${port_2} -c "alter role postgres with password '<PASSWORD>'" -d template1

    # clean the databases
    ./psql -p ${port_2} -c "drop database if exists ${database2}" -d template1 -U postgres

    ./psql -p ${port_2} -c "drop role if exists ${pguser}" -d template1
    ./createuser -p ${port_2} -s -e -d ${pguser}
    ./psql -p ${port_2} -c "alter role ${pguser} with password '<PASSWORD>'" -d template1

    ./psql -p ${port_2} -c "create database ${database2} owner ${pguser}" -d template1

    # May return an error about there not being a root role. It's not an issue, so ignore
    ./psql -p ${port_2} -f ${initial_dir}/data/mimic2.pgd -U ${pguser} -d ${database2}

    # ./psql -p ${port_2} -c "create database ${database2} owner ${pguser}" -d template1
    # ./psql -p ${port_2} -c "create schema mimic2v26" -d ${database2} -U pguser

    # d_patients.sql is a legacy sample that's not even supposed to run
    # ./psql -p ${port_2} -f ${initial_dir}/../scripts/mimic2_sql/d_patients.sql -d ${database2}

    # tests
    ./psql -p ${port_2} -c "create database test owner ${pguser}" -d template1 -U postgres

    # schemas
    bash ${schemas_resource}/recreate_schemas.sh ${schemas_resource} ${downloads_dir} ${port_1} ${port_2} ${catalog_db} ${postgres1_bin} ${postgres2_bin}

}

# auxiliary function to (re)create tpch database and load the tpch arrays
function load_tables {
    # read arguments
    postgres_bin=${1}
    port=${2}
    declare -a tables=("${!3}")
    # prepare database tpch
    ${postgres_bin}/psql -p ${port} -c "drop database if exists tpch" -d template1
    ${postgres_bin}/psql -p ${port} -c "create database tpch owner ${pguser}" -d template1
    # load the tables
    for table in ${tables[*]}; do
	echo crating and loading table: ${table}
	create_table_sql=$(cat tables/${table}.sql)
	${postgres_bin}/psql -p ${port} -c "begin;${create_table_sql}; copy ${table} from '${dbgen_dir}/${table}.tbl' with (format csv, delimiter '|', freeze); commit;" -d tpch
    done;
}

# main function
function load_tpch {
{'fraction_non_alphanumeric': 0.1391355335902161, 'fraction_numerical': 0.020941531244764618, 'mean_word_length': 3.7283168316831685, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 11, 'https://': 4, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 30, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '76094', 'n_tokens_mistral': 4282, 'n_tokens_neox': 4187, 'n_words': 1217}