Walkthrough

FAQ

• What is this?
• Why did you do this?
• What tech stack did you use?
• How can I use this?

What is this?

• These scripts use psql and bash to automate:
  • database setup
  • creating tables
  • loading data from csv
  • querying tables
  • database teardown

$ ./run.sh && cat output/queryOutput.txt | head -20
db refresh...
---------------------------------------------
DROP DATABASE
CREATE DATABASE
---------------------------------------------
creating tables...
---------------------------------------------
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
---------------------------------------------

Why did you do this?

calling psql commands by hand was taking ~1.25 hours to grade an assignment. It took ~10 hours to write this, but grading an assignment now takes ~0.33 hours

What tech stack did you use?

• mac/linux/WSL terminal environment
• psql
• PostgreSQL database
• bash

How can I use this?

• install postgres
• start postgres server
• set postgres user password to 'password'

$ sudo -u postgres psql
postgres=# ALTER USER postgres PASSWORD 'postgres';
ALTER ROLE

• check that student table names coincide with loadData

# src/getQueryOutput.sh
...
loadData() {
  psql -c "\copy <departments> from '$1/departments.csv' with (format csv, header true);" $testDbLogin
  psql -c "\copy <employees> from '$1/employees.csv' with (format csv, header true);" $testDbLogin
  psql -c "\copy <dept_emp> from '$1/dept_emp.csv' with (format csv, header true);" $testDbLogin
  psql -c "\copy <dept_manager> from '$1/dept_manager.csv' with (format csv, header true);" $testDbLogin
  psql -c "\copy <salaries> from '$1/salaries.csv' with (format csv, header true);" $testDbLogin
  psql -c "\copy <titles> from '$1/titles.csv' with (format csv, header true);" $testDbLogin
}
...

• update run.sh parameters

#!/usr/bin/env bash

./src/getQueryOutput.sh \
  sql/schema.sql `# table creation logic` \
  sql/queries.sql `# table query logic` \
  data/ `# input data location` \
  &> output/queryOutput.txt 

• call run.sh

$ ./run.sh

• check output/queryOutput.txt when finished

$ cat output/queryOutput.txt
db refresh...
---------------------------------------------
DROP DATABASE
CREATE DATABASE
---------------------------------------------
creating tables...
---------------------------------------------
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
---------------------------------------------
...