Project

General

Profile

1 6461 aaronmk
#!/bin/bash
2 4395 aaronmk
# Creates analytical DB
3
4
selfDir="$(dirname -- "$0")"
5
6 6459 aaronmk
. "$selfDir/vegbien_dest"
7
8 6055 aaronmk
exec 2>>"$selfDir/../inputs/analytical_db/logs/make_analytical_db.log.sql" >&2
9 6047 aaronmk
10 6054 aaronmk
(set -x; date)
11 6048 aaronmk
12 7618 aaronmk
"$selfDir/waitself" "$selfDir/../inputs/.TNRS/tnrs/tnrs.make"
13
14 6460 aaronmk
mk_analytical_table ()
15
{
16
    local table="$1"
17 6465 aaronmk
    local public_table="\"$public\".$table" analyt_table="analytical_db.$table"
18 6460 aaronmk
    cat <<EOF
19
-- Create $table
20 6466 aaronmk
TRUNCATE $public_table;
21
INSERT INTO $public_table SELECT * FROM ${public_table}_view;
22 7061 aaronmk
ANALYZE $public_table;
23 6460 aaronmk
24 6465 aaronmk
-- Create $analyt_table view pointing to table in $public
25
DROP VIEW IF EXISTS $analyt_table;
26
CREATE VIEW $analyt_table AS
27
SELECT * FROM $public_table;
28
GRANT SELECT ON TABLE $analyt_table TO bien_read;
29 6460 aaronmk
EOF
30
}
31
32 7345 aaronmk
((set -x; "$selfDir/psql_verbose_vegbien") <<EOF
33 7031 aaronmk
GRANT USAGE ON SCHEMA analytical_db TO bien_read;
34
GRANT USAGE ON SCHEMA analytical_db TO public_;
35
36 6053 aaronmk
TRUNCATE geoscrub_input;
37
INSERT INTO geoscrub_input SELECT * FROM geoscrub_input_view;
38
39 6042 aaronmk
SELECT make_family_higher_plant_group();
40 6085 aaronmk
TRUNCATE threatened_taxonlabel;
41
INSERT INTO threatened_taxonlabel SELECT * FROM threatened_taxonlabel_view;
42 6042 aaronmk
43 6895 aaronmk
$(mk_analytical_table taxon_trait)
44 6460 aaronmk
$(mk_analytical_table analytical_stem)
45 6868 aaronmk
$(mk_analytical_table provider_count)
46 7043 aaronmk
GRANT SELECT ON TABLE analytical_db.provider_count TO public_;
47 6460 aaronmk
$(mk_analytical_table analytical_aggregate)
48 6042 aaronmk
EOF
49 7345 aaronmk
) || exit
50 6981 aaronmk
51
"$selfDir/export_analytical_db"