NMS: Schema cleanup

1 files changed, 2 insertions, 230 deletions

diff --git a/nms/nms-dump.sql b/nms/nms-dump.sql
index c123554..97112b0 100644
--- a/nms/nms-dump.sql
+++ b/nms/nms-dump.sql
@@ -39,234 +39,6 @@ CREATE TYPE comment_state AS ENUM (
 
 ALTER TYPE comment_state OWNER TO nms;
 
---
--- Name: datarate; Type: TYPE; Schema: public; Owner: nms
---
-
-CREATE TYPE datarate AS (
-	switch integer,
-	ifname character varying(30),
-	ifhcinoctets double precision,
-	ifhcoutoctets double precision,
-	last_poll_time timestamp with time zone
-);
-
-
-ALTER TYPE datarate OWNER TO nms;
-
---
--- Name: operstatuses; Type: TYPE; Schema: public; Owner: postgres
---
-
-CREATE TYPE operstatuses AS (
-	switch integer,
-	ifdescr character(30),
-	ifoperstatus integer,
-	last_poll_time timestamp with time zone
-);
-
-
-ALTER TYPE operstatuses OWNER TO postgres;
-
---
--- Name: sample; Type: TYPE; Schema: public; Owner: postgres
---
-
-CREATE TYPE sample AS (
-	value bigint,
-	polled timestamp with time zone
-);
-
-
-ALTER TYPE sample OWNER TO postgres;
-
---
--- Name: sample_state; Type: TYPE; Schema: public; Owner: postgres
---
-
-CREATE TYPE sample_state AS (
-	last sample,
-	next_last sample
-);
-
-
-ALTER TYPE sample_state OWNER TO postgres;
-
---
--- Name: add_new_element(sample[], sample); Type: FUNCTION; Schema: public; Owner: postgres
---
-
-CREATE FUNCTION add_new_element(sample[], sample) RETURNS sample[]
-    LANGUAGE sql
-    AS $_$ select ('{' || $1[1] || ', ' || $2 || '}')::sample[] $_$;
-
-
-ALTER FUNCTION public.add_new_element(sample[], sample) OWNER TO postgres;
-
---
--- Name: add_new_element(sample_state, sample); Type: FUNCTION; Schema: public; Owner: postgres
---
-
-CREATE FUNCTION add_new_element(sample_state, sample) RETURNS sample_state
-    LANGUAGE sql
-    AS $_$
-        SELECT ($1.next_last, $2)::sample_state
-$_$;
-
-
-ALTER FUNCTION public.add_new_element(sample_state, sample) OWNER TO postgres;
-
---
--- Name: get_current_datarate(); Type: FUNCTION; Schema: public; Owner: nms
---
-
-CREATE FUNCTION get_current_datarate() RETURNS SETOF datarate
-    LANGUAGE sql
-    AS $$
-      SELECT switch,ifname,
-      (ifhcoutoctets[1] - ifhcoutoctets[2]) / EXTRACT(EPOCH FROM (time[1] - time[2])) AS ifhcoutoctets,
-      (ifhcinoctets[1] - ifhcinoctets[2]) / EXTRACT(EPOCH FROM (time[1] - time[2])) AS ifhcinoctets,
-      time[1] AS last_poll_time
-      FROM (
-        SELECT switch,ifname,
-        ARRAY_AGG(time) AS time,
-        ARRAY_AGG(ifhcinoctets) AS ifhcinoctets,
-        ARRAY_AGG(ifhcoutoctets) AS ifhcoutoctets
-        FROM (
-           SELECT *,rank() OVER (PARTITION BY switch,ifname ORDER BY time DESC) AS poll_num
-           FROM polls WHERE time BETWEEN (now() - interval '11 minutes') AND now()
-        ) t1
-        WHERE poll_num <= 2
-        GROUP BY switch,ifname
-      ) t2
-      WHERE
-        time[2] IS NOT NULL
-        AND ifhcinoctets[1] >= 0 AND ifhcoutoctets[1] >= 0
-        AND ifhcinoctets[2] >= 0 AND ifhcoutoctets[2] >= 0
-        AND ifhcoutoctets[1] >= ifhcoutoctets[2]
-        AND ifhcinoctets[1] >= ifhcinoctets[2];
-$$;
-
-
-ALTER FUNCTION public.get_current_datarate() OWNER TO nms;
-
---
--- Name: get_datarate(); Type: FUNCTION; Schema: public; Owner: nms
---
-
-CREATE FUNCTION get_datarate() RETURNS SETOF datarate
-    LANGUAGE plpgsql
-    AS $$
-DECLARE
-        num_entries INTEGER;
-        poll polls;
-        second_last_poll polls;
-        last_poll polls;
-        timediff float;
-        ret datarate;
-BEGIN
-        num_entries := 0;
-        last_poll.switch = -1;
-
-        FOR poll IN select * from polls where time >= now() - '15 minutes'::interval and time < now() order by switch,ifname,time LOOP
-                IF poll.switch <> last_poll.switch OR poll.ifname <> last_poll.ifname THEN
-                        IF num_entries >= 2 THEN
-                                timediff := EXTRACT(epoch from last_poll.time - second_last_poll.time);
-                                ret.switch := last_poll.switch;
-                                ret.ifname := last_poll.ifname;
-                                
-                                IF last_poll.ifhcinoctets < second_last_poll.ifhcinoctets THEN
-                                        second_last_poll.ifhcinoctets = 0;
-                                END IF;
-                                IF last_poll.ifhcoutoctets < second_last_poll.ifhcoutoctets THEN
-                                        second_last_poll.ifhcoutoctets = 0;
-                                END IF;
-
-                                ret.ifhcinoctets := (last_poll.ifhcinoctets - second_last_poll.ifhcinoctets) / timediff;
-                                ret.ifhcoutoctets := (last_poll.ifhcoutoctets - second_last_poll.ifhcoutoctets) / timediff;
-                                ret.last_poll_time := last_poll.time;
-                                return next ret;
-                        ELSIF num_entries = 1 THEN
-                                ret.switch := last_poll.switch;
-                                ret.ifname := last_poll.ifname;
-                                ret.ifhcinoctets := -1;
-                                ret.ifhcoutoctets := -1;
-                                ret.last_poll_time := last_poll.time;
-                                return next ret;
-                        END IF;
-                        num_entries := 1;
-                ELSE
-                        num_entries := num_entries + 1;
-                END IF;
-                second_last_poll.switch := last_poll.switch;
-                second_last_poll.ifname := last_poll.ifname;
-                second_last_poll.time := last_poll.time;
-                second_last_poll.ifhcinoctets := last_poll.ifhcinoctets;
-                second_last_poll.ifhcoutoctets := last_poll.ifhcoutoctets;
-                last_poll.switch := poll.switch;
-                last_poll.ifname := poll.ifname;
-                last_poll.time := poll.time;
-                last_poll.ifhcinoctets := poll.ifhcinoctets;
-                last_poll.ifhcoutoctets := poll.ifhcoutoctets;
-        END LOOP;
-       -- pah, and once more, for the last switch/ifname...
-        IF num_entries >= 2 THEN
-                timediff := EXTRACT(epoch from last_poll.time - second_last_poll.time);
-                ret.switch := last_poll.switch;
-                ret.ifname := last_poll.ifname;
-                
-                IF last_poll.ifhcinoctets < second_last_poll.ifhcinoctets THEN
-                        second_last_poll.ifhcinoctets = 0;
-                END IF;
-                IF last_poll.ifhcoutoctets < second_last_poll.ifhcoutoctets THEN
-                        second_last_poll.ifhcoutoctets = 0;
-                END IF;
-
-                ret.ifhcinoctets := (last_poll.ifhcinoctets - second_last_poll.ifhcinoctets) / timediff;
-                ret.ifhcoutoctets := (last_poll.ifhcoutoctets - second_last_poll.ifhcoutoctets) / timediff;
-		ret.last_poll_time := last_poll.time;
-                return next ret;
-        ELSIF num_entries = 1 THEN
-                ret.switch := last_poll.switch;
-                ret.ifname := last_poll.ifname;
-                ret.ifhcinoctets := -1;
-                ret.ifhcoutoctets := -1;
-		ret.last_poll_time := last_poll.time;
-                return next ret;
-        END IF;
-        
-        RETURN;
-END;
-$$;
-
-
-ALTER FUNCTION public.get_datarate() OWNER TO nms;
-
---
--- Name: sha1_hmac(bytea, bytea); Type: FUNCTION; Schema: public; Owner: postgres
---
-
-CREATE FUNCTION sha1_hmac(bytea, bytea) RETURNS text
-    LANGUAGE sql IMMUTABLE STRICT
-    AS $_$
-      SELECT encode(hmac($1, $2, 'sha1'), 'hex')
-    $_$;
-
-
-ALTER FUNCTION public.sha1_hmac(bytea, bytea) OWNER TO postgres;
-
---
--- Name: current_change(sample); Type: AGGREGATE; Schema: public; Owner: postgres
---
-
-CREATE AGGREGATE current_change(sample) (
-    SFUNC = public.add_new_element,
-    STYPE = sample_state
-);
-
-
-ALTER AGGREGATE public.current_change(sample) OWNER TO postgres;
-
 SET default_tablespace = '';
 
 SET default_with_oids = false;
@@ -402,7 +174,7 @@ CREATE TABLE snmp (
 );
 
 
-ALTER TABLE snmp OWNER TO postgres;
+ALTER TABLE snmp OWNER TO nms;
 
 --
 -- Name: snmp_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
@@ -416,7 +188,7 @@ CREATE SEQUENCE snmp_id_seq
     CACHE 1;
 
 
-ALTER TABLE snmp_id_seq OWNER TO postgres;
+ALTER TABLE snmp_id_seq OWNER TO nms;
 
 --
 -- Name: snmp_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres