Project

General

Profile

1
#!/bin/bash
2
# Creates analytical DB
3
# Usage: self [log=]
4

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

    
7
. "$selfDir/vegbien_dest"
8

    
9
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

    
13
(set -x; date)
14

    
15
sleep 5 # wait for tnrs.make to acquire the lock if being run right after import
16
"$selfDir/waitself" "$selfDir/../inputs/.TNRS/tnrs/tnrs.make"
17

    
18
mk_table()
19
{
20
    local table="$1"
21
    local public_table="\"$public\".$table"
22
    cat <<EOF
23
-- Create $table
24
TRUNCATE $public_table;
25
INSERT INTO $public_table SELECT * FROM ${public_table}_view;
26
ANALYZE $public_table;
27
EOF
28
}
29

    
30
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
-- 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
EOF
43
}
44

    
45
((set -x; "$selfDir/psql_verbose_vegbien") <<EOF
46
GRANT USAGE ON SCHEMA analytical_db TO bien_read;
47
GRANT USAGE ON SCHEMA analytical_db TO public_;
48

    
49
$(mk_table geoscrub_input)
50

    
51
SELECT make_family_higher_plant_group();
52
$(mk_table threatened_taxonlabel)
53

    
54
$(mk_analytical_table taxon_trait)
55
$(mk_analytical_table analytical_stem)
56
$(mk_analytical_table provider_count)
57
GRANT SELECT ON TABLE analytical_db.provider_count TO public_;
58
EOF
59
) || exit
60

    
61
remake=1 "$selfDir"/../run export_
62
#"$selfDir/export_analytical_db"
(40-40/83)