Project

General

Profile

1 6461 aaronmk
#!/bin/bash
2 4395 aaronmk
# Creates analytical DB
3
4
selfDir="$(dirname -- "$0")"
5
6 6459 aaronmk
prefix=
7
. "$selfDir/vegbien_dest"
8
9 6055 aaronmk
exec 2>>"$selfDir/../inputs/analytical_db/logs/make_analytical_db.log.sql" >&2
10 6047 aaronmk
11 6054 aaronmk
(set -x; date)
12 6048 aaronmk
13 6460 aaronmk
mk_analytical_table ()
14
{
15
    local table="$1"
16 6465 aaronmk
    local public_table="\"$public\".$table" analyt_table="analytical_db.$table"
17 6460 aaronmk
    cat <<EOF
18
-- Create $table
19 6466 aaronmk
TRUNCATE $public_table;
20
INSERT INTO $public_table SELECT * FROM ${public_table}_view;
21 6460 aaronmk
22 6465 aaronmk
-- Create $analyt_table view pointing to table in $public
23
DROP VIEW IF EXISTS $analyt_table;
24
CREATE VIEW $analyt_table AS
25
SELECT * FROM $public_table;
26
GRANT SELECT ON TABLE $analyt_table TO bien_read;
27 6460 aaronmk
EOF
28
}
29
30 6462 aaronmk
(set -x; "$selfDir/psql_verbose_vegbien") <<EOF
31 6053 aaronmk
TRUNCATE geoscrub_input;
32
INSERT INTO geoscrub_input SELECT * FROM geoscrub_input_view;
33
34 6042 aaronmk
SELECT make_family_higher_plant_group();
35 6085 aaronmk
TRUNCATE threatened_taxonlabel;
36
INSERT INTO threatened_taxonlabel SELECT * FROM threatened_taxonlabel_view;
37 6042 aaronmk
38 6460 aaronmk
$(mk_analytical_table analytical_stem)
39
$(mk_analytical_table analytical_aggregate)
40 6042 aaronmk
EOF