Project

General

Profile

1
#!/bin/bash
2
# Creates analytical DB
3

    
4
selfDir="$(dirname -- "$0")"
5

    
6
. "$selfDir/vegbien_dest"
7

    
8
exec 2>>"$selfDir/../inputs/analytical_db/logs/make_analytical_db.log.sql" >&2
9

    
10
(set -x; date)
11

    
12
mk_analytical_table ()
13
{
14
    local table="$1"
15
    local public_table="\"$public\".$table" analyt_table="analytical_db.$table"
16
    cat <<EOF
17
-- Create $table
18
TRUNCATE $public_table;
19
INSERT INTO $public_table SELECT * FROM ${public_table}_view;
20

    
21
-- Create $analyt_table view pointing to table in $public
22
DROP VIEW IF EXISTS $analyt_table;
23
CREATE VIEW $analyt_table AS
24
SELECT * FROM $public_table;
25
GRANT SELECT ON TABLE $analyt_table TO bien_read;
26
EOF
27
}
28

    
29
(set -x; "$selfDir/psql_verbose_vegbien") <<EOF
30
GRANT USAGE ON SCHEMA analytical_db TO bien_read;
31
GRANT USAGE ON SCHEMA analytical_db TO public_;
32

    
33
TRUNCATE geoscrub_input;
34
INSERT INTO geoscrub_input SELECT * FROM geoscrub_input_view;
35

    
36
SELECT make_family_higher_plant_group();
37
TRUNCATE threatened_taxonlabel;
38
INSERT INTO threatened_taxonlabel SELECT * FROM threatened_taxonlabel_view;
39

    
40
$(mk_analytical_table taxon_trait)
41
$(mk_analytical_table analytical_stem)
42
$(mk_analytical_table provider_count)
43
GRANT SELECT ON TABLE analytical_db.provider_count TO public_;
44
$(mk_analytical_table analytical_aggregate)
45
EOF
46

    
47
"$selfDir/export_analytical_db"
(37-37/72)