Mercurial > gemma

annotate schema/updates/1469/aggregate-gm-logs.sql @ 5534:cb62985ec252 aggregate-gm-import-logging

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
WIP: Started with migration to convert all gm logs to new form.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Mon, 25 Oct 2021 15:43:38 +0200
parents
children 1fba5766d820
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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;
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
9 BEGIN
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
10
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
11 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
12 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
13 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
14 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
15 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
16 );
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
17
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
18 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
19 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
20 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
21 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
22 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
23 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
24 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
25 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
26 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
27 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
28 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
29 );
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
30 last_id := -1;
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
31 FOR line IN logs LOOP
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
32 IF last_id <> line.import_id THEN
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
33 RAISE NOTICE '%', line.import_id;
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
34 last_id := line.import_id;
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
35 -- TODO: Generated log lines 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
36 TRUNCATE 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
37 END IF;
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
38 -- TODO: insert lines into filtered_logs or aggregate in 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
39 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
40
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
41 -- 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
42
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
43 -- 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
44 -- 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
45
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
46 -- 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
47
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
48 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
49 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
50 END $$;
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
51 -- 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
52
cb62985ec252 WIP: Started with migration to convert all gm logs to new form.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
53