Mercurial > gemma
annotate schema/updates/1469/aggregate-gm-logs.sql @ 5538:ff95d3603e4d aggregate-gm-import-logging
WIP: More aggregation.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Mon, 25 Oct 2021 23:05:45 +0200 |
parents | dc2000b807bd |
children | 4d815f295e57 |
rev | line source |
---|---|
5534
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
1 DO $$ |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
2 DECLARE |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
3 logs CURSOR FOR SELECT |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
4 lo.* FROM import.imports im JOIN import.import_logs lo |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
5 ON lo.import_id = im.id |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
6 WHERE im.kind = 'gm' |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
7 ORDER BY lo.import_id, lo.time; |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
8 last_id integer; |
5535
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
9 last_time timestamp with time zone; |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
10 curr_gid text; |
5538
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
11 value text; |
5536
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
12 joined text; |
5535
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
13 num integer; |
5534
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
14 BEGIN |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
15 |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
16 CREATE TEMP TABLE filtered_logs ( |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
17 import_id integer NOT NULL, |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
18 time timestamp with time zone NOT NULL, |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
19 kind log_type NOT NULL default 'info'::log_type, |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
20 msg text NOT NULL |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
21 ); |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
22 |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
23 CREATE TEMP TABLE agg_tracker ( |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
24 gid text NOT NULL UNIQUE, |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
25 unknown boolean NOT NULL DEFAULT false, |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
26 assume_zpg boolean NOT NULL DEFAULT false, |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
27 ign_meas_codes text ARRAY, |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
28 rescale_errors text ARRAY, |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
29 missing_values text ARRAY, |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
30 assume_cm integer NOT NULL DEFAULT 0, |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
31 bad_values integer NOT NULL DEFAULT 0, |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
32 measurements integer NOT NULL DEFAULT 0, |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
33 predictions integer NOT NULL DEFAULT 0 |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
34 ); |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
35 last_id := -1; |
5535
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
36 |
5534
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
37 FOR line IN logs LOOP |
5535
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
38 |
5534
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
39 IF last_id <> line.import_id THEN |
5536
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
40 |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
41 -- unknown |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
42 SELECT INTO joined string_agg(gid, ', ') |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
43 FROM agg_tracker |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
44 WHERE unknown; |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
45 |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
46 IF joined IS NOT NULL THEN |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
47 INSERT INTO filtered_logs VALUES ( |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
48 last_id, last_time, 'warn'::log_type, |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
49 'Cannot find following gauges: ' || joined); |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
50 END IF; |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
51 |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
52 -- assume zpg |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
53 SELECT INTO joined string_agg(gid, ', ') |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
54 FROM agg_tracker |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
55 WHERE assume_zpg; |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
56 |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
57 IF joined IS NOT NULL THEN |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
58 INSERT INTO filtered_logs VALUES ( |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
59 last_id, last_time, 'warn'::log_type, |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
60 '''Reference_code'' not specified. Assuming ''ZPG'': ' || joined); |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
61 END IF; |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
62 |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
63 -- assume cm |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
64 SELECT INTO joined string_agg( |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
65 gid || ' (' || assume_cm || ')', ', ') |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
66 FROM agg_tracker |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
67 WHERE assume_cm > 0; |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
68 |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
69 IF joined IS NOT NULL THEN |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
70 INSERT INTO filtered_logs VALUES ( |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
71 last_id, last_time, 'warn'::log_type, |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
72 '''Unit'' not specified. Assuming ''cm'': ' || joined); |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
73 END IF; |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
74 |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
75 -- bad values |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
76 SELECT INTO joined string_agg( |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
77 gid || ' (' || bad_values || ')', ', ') |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
78 FROM agg_tracker |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
79 WHERE bad_values > 0; |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
80 |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
81 IF joined IS NOT NULL THEN |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
82 INSERT INTO filtered_logs VALUES ( |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
83 last_id, last_time, 'warn'::log_type, |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
84 'Ignored measurements with value -99999: ' || joined); |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
85 END IF; |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
86 |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
87 |
5538
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
88 -- TODO: ""Missing mandatory values: " |
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
89 -- TODO: "Cannot convert units: " |
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
90 |
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
91 -- ignored measure codes |
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
92 SELECT INTO joined string_agg( |
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
93 gid || ' (' |
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
94 || array_to_string(ARRAY(SELECT DISTINCT unnest(ign_meas_codes)), '; ') |
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
95 || ')', ', ') |
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
96 FROM agg_tracker |
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
97 WHERE ign_meas_codes IS NOT NULL; |
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
98 |
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
99 IF joined IS NOT NULL THEN |
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
100 INSERT INTO filtered_logs VALUES ( |
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
101 last_id, last_time, 'warn'::log_type, |
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
102 'Ignored measure codes: ' || joined); |
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
103 END IF; |
5536
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
104 |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
105 -- predictions |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
106 SELECT INTO joined string_agg( |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
107 gid || ' (' || predictions || ')', ', ') |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
108 FROM agg_tracker |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
109 WHERE predictions > 0; |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
110 |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
111 IF joined IS NOT NULL THEN |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
112 INSERT INTO filtered_logs VALUES ( |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
113 last_id, last_time, 'info'::log_type, |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
114 'New predictions: ' || joined); |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
115 END IF; |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
116 |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
117 -- measurements |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
118 SELECT INTO joined string_agg( |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
119 gid || ' (' || measurements || ')', ', ') |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
120 FROM agg_tracker |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
121 WHERE measurements > 0; |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
122 |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
123 IF joined IS NOT NULL THEN |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
124 INSERT INTO filtered_logs VALUES ( |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
125 last_id, last_time, 'info'::log_type, |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
126 'New measurements: ' || joined); |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
127 END IF; |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
128 |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
129 -- nothing changed |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
130 SELECT INTO joined string_agg(gid, ', ') |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
131 FROM agg_tracker |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
132 WHERE measurements = 0 and predictions = 0; |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
133 |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
134 IF joined IS NOT NULL THEN |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
135 INSERT INTO filtered_logs VALUES ( |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
136 last_id, last_time, 'info'::log_type, |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
137 'No changes for: ' || joined); |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
138 END IF; |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
139 |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
140 |
dc2000b807bd
WIP: More on aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5535
diff
changeset
|
141 TRUNCATE agg_tracker; |
5534
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
142 last_id := line.import_id; |
5535
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
143 |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
144 ELSIF line.msg ~ '^Found measurements/predictions for ....................$' THEN |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
145 curr_gid := substring( |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
146 line.msg from '^Found measurements/predictions for (....................)$'); |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
147 |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
148 ELSIF line.msg ~ '^Inserted \d+ measurements for ....................$' THEN |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
149 num := substring( |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
150 line.msg from '^Inserted (\d+)')::integer; |
5538
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
151 value := substring( |
5535
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
152 line.msg from '^Inserted \d+ measurements for (....................)$'); |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
153 |
5538
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
154 INSERT INTO agg_tracker (gid, measurements) VALUES (value, num) |
5535
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
155 ON CONFLICT (gid) |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
156 DO UPDATE SET measurements = EXCLUDED.measurements + num; |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
157 |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
158 ELSIF line.msg ~ '^Ignored \d+ measurements with value -99999$' THEN |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
159 num := substring( |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
160 line.msg from '^Ignored (\d+)')::integer; |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
161 |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
162 INSERT INTO agg_tracker (gid, bad_values) VALUES (curr_gid, num) |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
163 ON CONFLICT (gid) |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
164 DO UPDATE SET bad_values = EXCLUDED.bad_values + num; |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
165 |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
166 ELSIF line.msg ~ '^Inserted \d+ predictions for ....................$' THEN |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
167 num := substring( |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
168 line.msg from '^Inserted (\d+)')::integer; |
5538
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
169 value := substring( |
5535
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
170 line.msg from '^Inserted \d+ predictions for (....................)$'); |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
171 |
5538
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
172 INSERT INTO agg_tracker (gid, predictions) VALUES (value, num) |
5535
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
173 ON CONFLICT (gid) |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
174 DO UPDATE SET predictions = EXCLUDED.predictions + num; |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
175 |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
176 ELSIF line.msg ~ '^''Reference_code'' not specified. Assuming ''ZPG''$' THEN |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
177 |
5538
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
178 INSERT INTO agg_tracker (gid, assume_zpg) VALUES (curr_gid, true) |
5535
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
179 ON CONFLICT (gid) |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
180 DO UPDATE SET assume_zpg = true; |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
181 |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
182 ELSIF line.msg ~ '^Cannot find gauge "...................." for import$' THEN |
5538
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
183 value := substring( |
5535
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
184 line.msg from '^Cannot find gauge "(....................)" for import$'); |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
185 |
5538
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
186 INSERT INTO agg_tracker (gid, unknown) VALUES (value, true) |
5535
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
187 ON CONFLICT (gid) |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
188 DO UPDATE SET unknown = true; |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
189 |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
190 ELSIF line.msg ~ '^''Unit'' not specified. Assuming ''cm''$' THEN |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
191 |
5538
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
192 INSERT INTO agg_tracker (gid, assume_cm) VALUES (curr_gid, 1) |
5535
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
193 ON CONFLICT (gid) |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
194 DO UPDATE SET assume_cm = EXCLUDED.assume_cm + 1; |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
195 |
5538
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
196 ELSIF line.msg ~ '^Ignored message with measure_code .+' THEN |
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
197 value := substring( |
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
198 line.msg from '^Ignored message with measure_code (.+)$'); |
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
199 |
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
200 INSERT INTO agg_tracker (gid, ign_meas_codes) VALUES (curr_gid, array[value]) |
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
201 ON CONFLICT (gid) |
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
202 DO UPDATE SET ign_meas_codes = |
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
203 array_cat(agg_tracker.ign_meas_codes, EXCLUDED.ign_meas_codes); |
ff95d3603e4d
WIP: More aggregation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5536
diff
changeset
|
204 |
5535
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
205 ELSIF line.msg ~ '^Importing gauge measurements took ' THEN |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
206 |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
207 -- TODO: Flush aggregation because its likely the last entry. |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
208 ELSE |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
209 -- TODO: Handle 'Missing mandatory value at %s. Ignored (bad service)' |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
210 -- TODO: Handle "unknown unit '%s'" |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
211 -- Not handled, copy through .. |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
212 INSERT INTO filtered_logs VALUES (line.import_id, line.time, line.kind, line.msg); |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
213 |
5534
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
214 END IF; |
5535
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
215 |
1fba5766d820
WIP: More on migration script.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5534
diff
changeset
|
216 last_time := line.time; |
5534
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
217 END LOOP; |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
218 |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
219 -- TODO: Handle remains from agg_tracker |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
220 |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
221 -- DELETE FROM import.import_logs WHERE import_id IN ( |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
222 -- SELECT id FROM import.imports WHERE kind = 'gm') |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
223 |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
224 -- INSERT INTO import.import_logs SELECT * FROM import.filtered_logs; |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
225 |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
226 DROP TABLE filtered_logs; |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
227 DROP TABLE agg_tracker; |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
228 END $$; |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
229 -- VACUUM FULL; |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
230 |
cb62985ec252
WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
231 |