--- a/schema/updates/1469/aggregate-gm-logs.sql	Fri Oct 29 00:07:50 2021 +0200
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,302 +0,0 @@
-DO $$
-DECLARE
-  logs NO SCROLL CURSOR FOR SELECT
-       lo.* FROM import.imports im JOIN import.import_logs lo
-       ON lo.import_id = im.id
-       WHERE im.kind = 'gm'
-       ORDER BY lo.import_id, lo.time;
-  last_id integer;
-  last_time timestamp with time zone;
-  curr_gid text;
-  value text;
-  joined text;
-  num integer;
-  line record;
-  hold record;
-  done boolean;
-BEGIN
-
-  CREATE TEMP TABLE filtered_logs (
-      import_id integer                  NOT NULL,
-      time      timestamp with time zone NOT NULL,
-      kind      log_type                 NOT NULL default 'info'::log_type,
-      msg       text                     NOT NULL
-  );
-
-  CREATE TEMP TABLE agg_tracker (
-      gid            text    NOT NULL UNIQUE,
-      unknown        boolean NOT NULL DEFAULT false,
-      assume_zpg     boolean NOT NULL DEFAULT false,
-      ign_meas_codes text ARRAY,
-      rescale_errors text ARRAY,
-      missing_values text ARRAY,
-      assume_cm      integer NOT NULL DEFAULT 0,
-      bad_values     integer NOT NULL DEFAULT 0,
-      measurements   integer NOT NULL DEFAULT 0,
-      predictions    integer NOT NULL DEFAULT 0
-  );
-
-  last_id := -1;
-  done := false;
-  hold := NULL;
-
-  OPEN logs;
-
-  LOOP
-
-    FETCH logs INTO line;
-
-    IF NOT FOUND THEN
-        done := true;
-    END IF;
-
-    -- RAISE NOTICE '%', line.msg;
-
-    IF done OR last_id <> line.import_id THEN
-
-        -- unknown
-        SELECT INTO joined string_agg(gid, ', ')
-            FROM agg_tracker
-            WHERE unknown;
-
-        IF joined IS NOT NULL THEN
-            INSERT INTO filtered_logs VALUES (
-                last_id, last_time, 'warn'::log_type,
-                'Cannot find following gauges: ' || joined);
-        END IF;
-
-        -- assume zpg
-        SELECT INTO joined string_agg(gid, ', ')
-            FROM agg_tracker
-            WHERE assume_zpg;
-
-        IF joined IS NOT NULL THEN
-            INSERT INTO filtered_logs VALUES (
-                last_id, last_time, 'warn'::log_type,
-                '''Reference_code'' not specified. Assuming ''ZPG'': ' || joined);
-        END IF;
-
-        -- assume cm
-        SELECT INTO joined string_agg(
-            gid || ' (' || assume_cm || ')', ', ')
-            FROM agg_tracker
-            WHERE assume_cm > 0;
-
-        IF joined IS NOT NULL THEN
-            INSERT INTO filtered_logs VALUES (
-                last_id, last_time, 'warn'::log_type,
-                '''Unit'' not specified. Assuming ''cm'': ' || joined);
-        END IF;
-
-        -- bad values
-        SELECT INTO joined string_agg(
-            gid || ' (' || bad_values || ')', ', ')
-            FROM agg_tracker
-            WHERE bad_values > 0;
-
-        IF joined IS NOT NULL THEN
-            INSERT INTO filtered_logs VALUES (
-                last_id, last_time, 'warn'::log_type,
-                'Ignored measurements with value -99999: ' || joined);
-        END IF;
-
-        -- missing mandatory values
-        SELECT INTO joined string_agg(
-            gid || ' ('
-                || array_to_string(ARRAY(SELECT DISTINCT unnest(missing_values)), '; ')
-                || ')', ', ')
-            FROM agg_tracker
-            WHERE missing_values IS NOT NULL;
-
-        IF joined IS NOT NULL THEN
-            INSERT INTO filtered_logs VALUES (
-                last_id, last_time, 'warn'::log_type,
-                'Missing mandatory values: ' || joined);
-        END IF;
-
-        -- convert units
-        SELECT INTO joined string_agg(
-            gid || ' ('
-                || array_to_string(ARRAY(SELECT DISTINCT unnest(rescale_errors)), '; ')
-                || ')', ', ')
-            FROM agg_tracker
-            WHERE rescale_errors IS NOT NULL;
-
-        IF joined IS NOT NULL THEN
-            INSERT INTO filtered_logs VALUES (
-                last_id, last_time, 'error'::log_type,
-                'Cannot convert units: ' || joined);
-        END IF;
-
-        -- ignored measure codes
-        SELECT INTO joined string_agg(
-            gid || ' ('
-                || array_to_string(ARRAY(SELECT DISTINCT unnest(ign_meas_codes)), '; ')
-                || ')', ', ')
-            FROM agg_tracker
-            WHERE ign_meas_codes IS NOT NULL;
-
-        IF joined IS NOT NULL THEN
-            INSERT INTO filtered_logs VALUES (
-                last_id, last_time, 'warn'::log_type,
-                'Ignored measure codes: ' || joined);
-        END IF;
-
-        -- predictions
-        SELECT INTO joined string_agg(
-            gid || ' (' || predictions || ')', ', ')
-            FROM agg_tracker
-            WHERE predictions > 0;
-
-        IF joined IS NOT NULL THEN
-            INSERT INTO filtered_logs VALUES (
-                last_id, last_time, 'info'::log_type,
-                'New predictions: ' || joined);
-        END IF;
-
-        -- measurements
-        SELECT INTO joined string_agg(
-            gid || ' (' || measurements || ')', ', ')
-            FROM agg_tracker
-            WHERE measurements > 0;
-
-        IF joined IS NOT NULL THEN
-            INSERT INTO filtered_logs VALUES (
-                last_id, last_time, 'info'::log_type,
-                'New measurements: ' || joined);
-        END IF;
-
-        -- nothing changed
-        SELECT INTO joined string_agg(gid, ', ')
-            FROM agg_tracker
-            WHERE measurements = 0 and predictions = 0;
-
-        IF joined IS NOT NULL THEN
-            INSERT INTO filtered_logs VALUES (
-                last_id, last_time, 'info'::log_type,
-                'No changes for: ' || joined);
-        END IF;
-
-        IF hold is NOT NULL THEN
-            INSERT INTO filtered_logs VALUES (hold.import_id, hold.time, hold.kind, hold.msg);
-            hold := NULL;
-        END IF;
-
-        IF done THEN
-            EXIT;
-        END IF;
-
-        -- reset aggregate table
-        TRUNCATE agg_tracker;
-        last_id := line.import_id;
-
-    ELSIF line.msg ~ '^Found measurements/predictions for ....................$' THEN
-       curr_gid := substring(
-          line.msg from '^Found measurements/predictions for (....................)$');
-
-    ELSIF line.msg ~ '^Inserted \d+ measurements for ....................$' THEN
-        num := substring(
-            line.msg from '^Inserted (\d+)')::integer;
-        value := substring(
-            line.msg from '^Inserted \d+ measurements for (....................)$');
-
-        INSERT INTO agg_tracker (gid, measurements) VALUES (value, num)
-          ON CONFLICT (gid)
-          DO UPDATE SET measurements = EXCLUDED.measurements + num;
-
-    ELSIF line.msg ~ '^Inserted \d+ predictions for ....................$' THEN
-        num := substring(
-            line.msg from '^Inserted (\d+)')::integer;
-        value := substring(
-            line.msg from '^Inserted \d+ predictions for (....................)$');
-
-        INSERT INTO agg_tracker (gid, predictions) VALUES (value, num)
-          ON CONFLICT (gid)
-          DO UPDATE SET predictions = EXCLUDED.predictions + num;
-
-    ELSIF line.msg ~ '^''Reference_code'' not specified. Assuming ''ZPG''$' THEN
-
-        INSERT INTO agg_tracker (gid, assume_zpg) VALUES (curr_gid, true)
-          ON CONFLICT (gid)
-          DO UPDATE SET assume_zpg = true;
-
-    ELSIF line.msg ~ '^Ignored \d+ measurements with value -99999$' THEN
-        num := substring(
-            line.msg from '^Ignored (\d+)')::integer;
-
-        INSERT INTO agg_tracker (gid, bad_values) VALUES (curr_gid, num)
-          ON CONFLICT (gid)
-          DO UPDATE SET bad_values = EXCLUDED.bad_values + num;
-
-    ELSIF line.msg ~ '^Cannot find gauge "...................." for import$' THEN
-        value := substring(
-            line.msg from '^Cannot find gauge "(....................)" for import$');
-
-        INSERT INTO agg_tracker (gid, unknown) VALUES (value, true)
-          ON CONFLICT (gid)
-          DO UPDATE SET unknown = true;
-
-    ELSIF line.msg ~ '^''Unit'' not specified. Assuming ''cm''$' THEN
-
-        INSERT INTO agg_tracker (gid, assume_cm) VALUES (curr_gid, 1)
-          ON CONFLICT (gid)
-          DO UPDATE SET assume_cm = EXCLUDED.assume_cm + 1;
-
-    ELSIF line.msg ~ '^Ignored message with measure_code .+' THEN
-        value := substring(
-            line.msg from '^Ignored message with measure_code (.+)$');
-
-        INSERT INTO agg_tracker (gid, ign_meas_codes) VALUES (curr_gid, array[value])
-          ON CONFLICT (gid)
-          DO UPDATE SET ign_meas_codes =
-            array_cat(agg_tracker.ign_meas_codes, EXCLUDED.ign_meas_codes);
-
-    ELSIF line.msg ~ '^Missing mandatory value at [^.]+\.' THEN
-        value := substring(
-            line.msg from '^Missing mandatory value at ([^.]+)\.');
-
-        INSERT INTO agg_tracker (gid, missing_values) VALUES (curr_gid, array[value])
-          ON CONFLICT (gid)
-          DO UPDATE SET missing_values =
-            array_cat(agg_tracker.missing_values, EXCLUDED.missing_values);
-
-    ELSIF line.msg ~ '^unknown unit ''[^'']*''' THEN
-
-        value := substring(
-            line.msg from '^unknown unit ''([^'']*)''');
-
-        INSERT INTO agg_tracker (gid, rescale_errors) VALUES (curr_gid, array[value])
-          ON CONFLICT (gid)
-          DO UPDATE SET rescale_errors =
-            array_cat(agg_tracker.rescale_errors, EXCLUDED.rescale_errors);
-
-    ELSIF line.msg ~ '^Importing gauge measurements took ' OR line.msg ~ '^error in import: ' THEN
-        -- Likely the last entry of this import.
-        hold := line;
-    ELSE
-        -- Not handled, copy through ..
-        -- RAISE NOTICE '%', line.msg;
-        INSERT INTO filtered_logs VALUES (line.import_id, line.time, line.kind, line.msg);
-
-    END IF;
-
-    last_time := line.time;
-  END LOOP;
-
-  CLOSE logs;
-
-  -- DELETE FROM import.import_logs WHERE import_id IN (
-  --  SELECT id FROM import.imports WHERE kind = 'gm')
-
-  -- INSERT INTO import.import_logs SELECT * FROM import.filtered_logs;
-
-  SELECT INTO num count(*) FROM filtered_logs;
-
-  RAISE NOTICE 'number of new gm log message entries: %', num;
-
-  DROP TABLE filtered_logs;
-  DROP TABLE agg_tracker;
-END $$;
--- VACUUM FULL;
-
-