Project

General

Profile

1 6461 aaronmk
#!/bin/bash
2 4395 aaronmk
# Creates analytical DB
3 7864 aaronmk
# Usage: self [log=]
4 4395 aaronmk
5
selfDir="$(dirname -- "$0")"
6
7 6459 aaronmk
. "$selfDir/vegbien_dest"
8
9 7864 aaronmk
if test -n "${log=1}"; then # $log defaults to 1
10
    exec >>"$selfDir/../inputs/analytical_db/logs/make_analytical_db.log.sql" 2>&1
11
fi
12 6047 aaronmk
13 6054 aaronmk
(set -x; date)
14 6048 aaronmk
15 7620 aaronmk
sleep 5 # wait for tnrs.make to acquire the lock if being run right after import
16 7618 aaronmk
"$selfDir/waitself" "$selfDir/../inputs/.TNRS/tnrs/tnrs.make"
17
18 9924 aaronmk
mk_table()
19 6460 aaronmk
{
20
    local table="$1"
21 9924 aaronmk
    local public_table="\"$public\".$table"
22 6460 aaronmk
    cat <<EOF
23
-- Create $table
24 6466 aaronmk
TRUNCATE $public_table;
25
INSERT INTO $public_table SELECT * FROM ${public_table}_view;
26 7061 aaronmk
ANALYZE $public_table;
27 9924 aaronmk
EOF
28
}
29 6460 aaronmk
30 9924 aaronmk
mk_analytical_table()
31
{
32
    local table="$1"
33
    local public_table="\"$public\".$table" analyt_table="analytical_db.$table"
34
    cat <<EOF
35
$(mk_table "$@")
36
37 6465 aaronmk
-- Create $analyt_table view pointing to table in $public
38
DROP VIEW IF EXISTS $analyt_table;
39
CREATE VIEW $analyt_table AS
40
SELECT * FROM $public_table;
41
GRANT SELECT ON TABLE $analyt_table TO bien_read;
42 6460 aaronmk
EOF
43
}
44
45 7345 aaronmk
((set -x; "$selfDir/psql_verbose_vegbien") <<EOF
46 7031 aaronmk
GRANT USAGE ON SCHEMA analytical_db TO bien_read;
47
GRANT USAGE ON SCHEMA analytical_db TO public_;
48
49 9925 aaronmk
$(mk_table geoscrub_input)
50 6053 aaronmk
51 9925 aaronmk
$(mk_table threatened_taxonlabel)
52 6042 aaronmk
53 6895 aaronmk
$(mk_analytical_table taxon_trait)
54 6460 aaronmk
$(mk_analytical_table analytical_stem)
55 6868 aaronmk
$(mk_analytical_table provider_count)
56 7043 aaronmk
GRANT SELECT ON TABLE analytical_db.provider_count TO public_;
57 6042 aaronmk
EOF
58 7345 aaronmk
) || exit
59 6981 aaronmk
60 10865 aaronmk
remake=1 "$selfDir"/../run export_ </dev/null
61 8320 aaronmk
#"$selfDir/export_analytical_db"