-
Notifications
You must be signed in to change notification settings - Fork 37
/
test.sh
executable file
·141 lines (121 loc) · 4.6 KB
/
test.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
#!/bin/bash
set -o errexit
set -o pipefail
set -o nounset
readonly CWD=$(pwd)
readonly SAMPLES_DIR="$CWD/samples"
readonly DB_USER=${DB_USER:-postgres}
readonly DB_NAME="integration_test"
readonly DB_SCHEMA="public"
readonly GITHUB_SAMPLE="$SAMPLES_DIR/2015-01-01-15.json"
readonly MONTGOMERY_SALARIES_SAMPLE="$SAMPLES_DIR/employee_salaries.csv"
function download_montgomery_county_samples() {
if [ ! -f "$MONTGOMERY_SALARIES_SAMPLE" ]; then
wget -O "$MONTGOMERY_SALARIES_SAMPLE" https://data.montgomerycountymd.gov/api/views/54rh-89p8/rows.csv
fi
}
function download_github_samples() {
if [ ! -f "$GITHUB_SAMPLE" ]; then
mkdir -p $SAMPLES_DIR
cd $SAMPLES_DIR
wget http://data.githubarchive.org/2015-01-01-15.json.gz && gunzip -f 2015-01-01-15.json.gz
cd $CWD
fi
}
function recreate_db() {
psql -U ${DB_USER} -c "drop database if exists ${DB_NAME};"
psql -U ${DB_USER} -c "create database ${DB_NAME};"
}
function import_csv() {
local table=$1
local filename=$2
pgfutter --table "$table" --schema $DB_SCHEMA --db $DB_NAME --user $DB_USER csv "$filename"
if [ $? -ne 0 ]; then
echo "pgfutter could not import $filename"
exit 300
else
echo "Imported $filename into $table"
fi
}
function import_json() {
local table=$1
local filename=$2
pgfutter --table "$table" --schema $DB_SCHEMA --db $DB_NAME --user $DB_USER json "$filename"
if [ $? -ne 0 ]; then
echo "pgfutter could not import $filename"
exit 300
else
echo "Imported $filename into $table"
fi
}
function import_montgomery_county_samples() {
download_montgomery_county_samples
import_csv "employee_salaries" "$MONTGOMERY_SALARIES_SAMPLE"
}
function import_github_samples() {
download_github_samples
import_json "github_events" "$GITHUB_SAMPLE"
}
function test_json_lines_export() {
local query="SELECT e.data->'repo'->>'name' as name, json_agg(c->>'sha') as commmits FROM github_events AS e, json_array_elements(e.data->'payload'->'commits') AS c WHERE e.data->>'type' = 'PushEvent' GROUP BY e.data->'repo'->>'name'"
local filename="push_events.json"
pgclimb -d $DB_NAME -U $DB_USER -c "$query" -o "$filename" jsonlines
echo "Exported JSON lines to $filename"
}
function test_xml() {
local query="SELECT * FROM employee_salaries"
local filename="salaries.xml"
pgclimb -d $DB_NAME -U $DB_USER -c "$query" -o "$filename" xml
echo "Exported XML to $filename"
}
function test_excel_export() {
local query1="SELECT * FROM employee_salaries"
local query2="SELECT full_name FROM employee_salaries"
local filename="montgomery_positions.xlsx"
pgclimb -d $DB_NAME -U $DB_USER -c "$query1" -o "$filename" xlsx --sheet salaries
echo "Exported salaries sheet to $filename"
pgclimb -d $DB_NAME -U $DB_USER -c "$query2" -o "$filename" xlsx --sheet employees
echo "Exported Excel employees sheet to $filename"
}
function test_templates() {
local query="SELECT * FROM employee_salaries"
local template="salaries_report.tpl"
local filename="salaries_report.html"
echo -e '<!DOCTYPE html><html>' > $template
echo -e '<head><title>Montgomery County MD Employees</title></head>' >> $template
echo -e '<body>' >> $template
echo -e '<h2>Employees</h2>' >> $template
echo -e '<ul>' >> $template
echo -e '{{range .}}' >> $template
echo -e '<li>{{.full_name}}</li>' >> $template
echo -e '{{end}}' >> $template
echo -e '</ul>' >> $template
echo -e '</body>' >> $template
echo -e '</html>' >> $template
pgclimb -d $DB_NAME -U $DB_USER -c "$query" -o "$filename" template "$template"
echo "Exported template $template to $filename"
}
function test_json_doc_export {
local query="SELECT e.data FROM github_events e WHERE e.data->>'type' = 'PushEvent'"
local filename="push_event_docs.json"
pgclimb --dbname $DB_NAME --username $DB_USER --command "$query" -o "$filename" json
echo "Exported JSON to $filename"
}
function test_csv_export() {
local query="SELECT position_title, COUNT(*) AS employees, round(AVG(replace(current_annual_salary, '$', '')::numeric)) AS avg_salary FROM employee_salaries GROUP BY position_title ORDER BY 3 DESC"
local filename="montgomery_average_salaries.csv"
echo "$query" | pgclimb -d $DB_NAME -U $DB_USER -o "$filename" csv --delimiter ";" --header
echo "Exported CSV to $filename"
}
function main() {
recreate_db
import_github_samples
import_montgomery_county_samples
test_csv_export
test_json_lines_export
test_json_doc_export
test_templates
test_excel_export
test_xml
}
main