-
Notifications
You must be signed in to change notification settings - Fork 0
/
create-sql-query.sh
executable file
·102 lines (95 loc) · 2.1 KB
/
create-sql-query.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
#!/bin/bash
TODAY=$(date '+%Y-%m-%d')
function usage() {
echo "Usage: ./create-sql-query.sh [OPTIONS] FILENAME"
echo
echo " Create the SQL query to dump data in the same form as a cleaned CSV."
echo
echo "Options:"
echo
echo -e " -d=DATE, --date=DATE\t\tUse given date (default: $TODAY)"
echo -e " --known-before\t\tReturn only rows that are known before the given date"
echo -e " -t=TABLE, --table=TABLE\tUse this SQL table name (default: rki_csv)"
echo -e " -h, --help\t\t\tShow this message and exit"
exit
}
while [[ $# -gt 0 ]]; do
key="$1"
case $key in
-d=* | --date=*)
DATE="${key#*=}"
shift
;;
--known-before)
KNOWN_BEFORE=true
shift
;;
-t=* | --table=*)
TABLE_NAME="${key#*=}"
shift
;;
-h | --help)
usage
shift
;;
*)
break
;;
esac
done
if [[ $# -lt 1 ]]; then
usage
fi
FILENAME=$1
REF_DATE=${DATE:-$TODAY}
TABLE=${TABLE_NAME:-'rki_csv'}
REF_DATE_COMPARISON="$REF_DATE"
if [[ $KNOWN_BEFORE ]]; then
REF_DATE_COMPARISON=$(date -I -d "$REF_DATE - 1 day")
fi
cat << ENDMYSQL
SELECT
'IdBundesland',
'IdLandkreis',
'Meldedatum',
'Altersgruppe',
'Geschlecht',
'NeuerFall',
'NeuerTodesfall',
'NeuGenesen',
'AnzahlFall',
'AnzahlTodesfall',
'AnzahlGenesen',
'Refdatum',
'IstErkrankungsbeginn',
'Altersgruppe2',
'GueltigAb',
'GueltigBis',
'DFID'
UNION ALL
SELECT
IdBundesland,
IdLandkreis,
Meldedatum,
Altersgruppe,
Geschlecht,
IF(NeuerFall = 1 AND GueltigAb < "$REF_DATE", 0, NeuerFall),
IF(NeuerTodesfall = 1 AND GueltigAb < "$REF_DATE", 0, NeuerTodesfall),
IF(NeuGenesen = 1 AND GueltigAb < "$REF_DATE", 0, NeuGenesen),
AnzahlFall,
AnzahlTodesfall,
AnzahlGenesen,
Refdatum,
IstErkrankungsbeginn,
Altersgruppe2,
"$REF_DATE",
NULL,
DFID
FROM $TABLE
WHERE
GueltigAb <= "$REF_DATE_COMPARISON" AND
(GueltigBis IS NULL OR "$REF_DATE_COMPARISON" <= GueltigBis)
INTO OUTFILE '$FILENAME'
CHARACTER SET UTF8 FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
ENDMYSQL