Changeset 5366a70920df98ecf37fed906dfe718d250c4ec1

Timestamp:

07/02/08 18:29:43 (5 years ago)

Author:

Denish Patel <denish@omniti.com>

git-committer:

Denish Patel <denish@omniti.com> 1215023383 +0000

git-parent:

[deeaea617276c3961438854251d49f293af8dd6d]

git-author:

Denish Patel <denish@omniti.com> 1215023383 +0000

Message:

Exported Latest DDL dump

Files:

• sql/reconnoiter_ddl_dump.sql (modified) (31 diffs)

sql/reconnoiter_ddl_dump.sql

@@ -23 +23 @@
-
-SET default_with_oids = false;
+
+--
+-- Name: current_node_config; Type: TABLE; Schema: stratcon; Owner: stratcon; Tablespace: 
+--
+
+CREATE TABLE current_node_config (
+    remote_address inet NOT NULL,
+    node_type text NOT NULL,
+    whence timestamp with time zone NOT NULL,
+    config xml NOT NULL
+);
+
+
+ALTER TABLE stratcon.current_node_config OWNER TO stratcon;
+
+--
+-- Name: current_node_config_changelog; Type: TABLE; Schema: stratcon; Owner: stratcon; Tablespace: 
+--
+
+CREATE TABLE current_node_config_changelog (
+    remote_address inet NOT NULL,
+    node_type text NOT NULL,
+    whence timestamp with time zone NOT NULL,
+    config xml NOT NULL
+);
+
+
+ALTER TABLE stratcon.current_node_config_changelog OWNER TO stratcon;
-
---
@@ -162 +190 @@
-    rollup_time timestamp with time zone NOT NULL,
-    count_rows integer,
-
-
-
+
-);
-
@@ -179 +205 @@
-    rollup_time timestamp with time zone NOT NULL,
-    count_rows integer,
-
-
-
+
-);
-
@@ -196 +220 @@
-    rollup_time timestamp with time zone NOT NULL,
-    count_rows integer,
-
-
-
+
-);
-
@@ -213 +235 @@
-    rollup_time timestamp with time zone NOT NULL,
-    count_rows integer,
-
-
-
+
-);
-
@@ -230 +250 @@
-    rollup_time timestamp with time zone NOT NULL,
-    count_rows integer,
-
-
-
+
-);
-
@@ -411 +429 @@
-
-  v_sql := 'select ' || v_sid || ' as sid, ' || quote_literal(in_name) || ' as name, ' ||
-
-
+
-           ' from ' ||
-           '(select ' || quote_literal(v_start_adj) || '::timestamp' ||
@@ -435 +452 @@
-        v_r_rollup_row.avg_value :=
-          (v_rollup_row.avg_value - v_l_rollup_row.avg_value) / v_interval;
-        v_r_rollup_row.min_value :=
-          (v_rollup_row.min_value - v_l_rollup_row.min_value) / v_interval;
-        v_r_rollup_row.max_value :=
-          (v_rollup_row.max_value - v_l_rollup_row.max_value) / v_interval;
-      else
-        v_r_rollup_row.count_rows = NULL;
-        v_r_rollup_row.avg_value = NULL;
-
-
+
-      end if;
-    else
@@ -594 +606 @@
-DECLARE
-v_whence timestamptz;
+v_whence_5 timestamptz;
-v_sid integer;
-v_name text;
-BEGIN
-IF TG_OP = 'INSERT' THEN
-
+
+
+
+
-     INTO v_whence;
+     
-   IF NOT FOUND THEN
-
+
+
+
+
+
+
-    END IF;
-
@@ -611 +633 @@
-
-END IF;
-
-    RETURN NULL;
-END
@@ -916 +937 @@
-  
-    FOR rec IN 
-
-
+
-         FROM stratcon.rollup_matrix_numeric_6hours
-           WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence-'12 hour'::interval
@@ -925 +945 @@
-       
-          INSERT INTO stratcon.rollup_matrix_numeric_12hours
-,min_value,max_value
-,rec.min_value,rec.max_value
+
+
-          
-     END LOOP;
@@ -941 +961 @@
-
-RETURN;
-
-EXCEPTION
-    WHEN RAISE_EXCEPTION THEN
@@ -1021 +1040 @@
- FOR rec IN 
-                SELECT sid , name,v_min_whence as rollup_time,
-
-
+
-       FROM stratcon.rollup_matrix_numeric_5m
-                      WHERE rollup_time<= v_min_whence AND rollup_time > v_min_whence -'20 minutes'::interval
@@ -1031 +1049 @@
-        
-        INSERT INTO stratcon.rollup_matrix_numeric_20m
-,min_value,max_value
-,rec.min_value,rec.max_value
+
+
-        
-   END LOOP;
@@ -1119 +1137 @@
-       INSERT INTO  stratcon.log_whence_s VALUES(date_trunc('H',v_min_whence) + (round(extract('minute' from v_min_whence)/20)*20) * '1 minute'::interval,'20 minutes');
-   END IF;
-   
- IF v_min_whence <= v_max_rollup_5 THEN
-
@@ -1129 +1146 @@
- FOR rec IN 
-                SELECT sid , name,v_min_whence as rollup_time,
-,MIN(value) as min_value ,MAX(value) as max_value
+
-                      FROM stratcon.loading_dock_metric_numeric_s
-                      WHERE WHENCE <= v_min_whence AND WHENCE > v_min_whence -'5 minutes'::interval
@@ -1138 +1155 @@
-        
-        INSERT INTO stratcon.rollup_matrix_numeric_5m
-,min_value,max_value
-,rec.min_value,rec.max_value
+
+
-        
-   END LOOP;
@@ -1233 +1250 @@
-  
-    FOR rec IN 
-
-
+
-         FROM stratcon.rollup_matrix_numeric_20m
-           WHERE date_hour(rollup_time)= v_min_whence
@@ -1241 +1257 @@
-      
-          INSERT INTO stratcon.rollup_matrix_numeric_60m
-,min_value,max_value
-,rec.min_value,rec.max_value
+
+
-          
-     END LOOP;
@@ -1260 +1276 @@
-EXCEPTION
-    WHEN RAISE_EXCEPTION THEN
-       UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_60m';
-       RAISE EXCEPTION '%', SQLERRM;
-    WHEN OTHERS THEN
@@ -1335 +1350 @@
-  
-    FOR rec IN 
-
-
+
-         FROM stratcon.rollup_matrix_numeric_60m
-           WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence-'6 hour'::interval
@@ -1344 +1358 @@
-       
-          INSERT INTO stratcon.rollup_matrix_numeric_6hours
-
-
+
+
+
-     END LOOP;
-
@@ -1361 +1376 @@
-EXCEPTION
-    WHEN RAISE_EXCEPTION THEN
-       UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_6hours'; 
-       RAISE EXCEPTION '%', SQLERRM;
-    WHEN OTHERS THEN
@@ -1373 +1387 @@
-
---
+-- Name: update_config(inet, text, timestamp with time zone, xml); Type: FUNCTION; Schema: stratcon; Owner: stratcon
+--
+
+CREATE FUNCTION update_config(v_remote_address_in inet, v_node_type_in text, v_whence_in timestamp with time zone, v_config_in xml) RETURNS void
+    AS $$
+DECLARE
+    v_config xml;
+BEGIN
+    select config into v_config from stratcon.current_node_config
+     where remote_address = v_remote_address_in
+       and node_type = v_node_type_in;
+    IF FOUND THEN
+        IF v_config::text = v_config_in::text THEN
+            RETURN;
+        END IF;
+        delete from stratcon.current_node_config
+              where _address = v_remote_address_in
+                and node_type = v_node_type_in;
+    END IF;
+    insert into stratcon.current_node_config
+                (remote_address, node_type, whence, config)
+         values (v_remote_address_in, v_node_type_in, v_whence_in, v_config_in);
+    insert into stratcon.current_node_config_changelog
+                (remote_address, node_type, whence, config)
+         values (v_remote_address_in, v_node_type_in, v_whence_in, v_config_in);
+END
+$$
+    LANGUAGE plpgsql;
+
+
+ALTER FUNCTION stratcon.update_config(v_remote_address_in inet, v_node_type_in text, v_whence_in timestamp with time zone, v_config_in xml) OWNER TO stratcon;
+
+--
--- Name: seq_sid; Type: SEQUENCE; Schema: stratcon; Owner: stratcon
---
@@ -1386 +1433 @@
-
---
+-- Name: current_node_config_changelog_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: stratcon; Tablespace: 
+--
+
+ALTER TABLE ONLY current_node_config_changelog
+    ADD CONSTRAINT current_node_config_changelog_pkey PRIMARY KEY (remote_address, node_type, whence);
+
+
+--
+-- Name: current_node_config_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: stratcon; Tablespace: 
+--
+
+ALTER TABLE ONLY current_node_config
+    ADD CONSTRAINT current_node_config_pkey PRIMARY KEY (remote_address, node_type);
+
+
+--
--- Name: loading_dock_check_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: omniti; Tablespace: 
---
@@ -1464 +1527 @@
-    ADD CONSTRAINT rollup_matrix_numeric_12hours_pkey PRIMARY KEY (rollup_time, sid, name);
-
+ALTER TABLE rollup_matrix_numeric_12hours CLUSTER ON rollup_matrix_numeric_12hours_pkey;
+
-
---
@@ -1472 +1537 @@
-    ADD CONSTRAINT rollup_matrix_numeric_20m_new_pkey PRIMARY KEY (rollup_time, sid, name);
-
+ALTER TABLE rollup_matrix_numeric_20m CLUSTER ON rollup_matrix_numeric_20m_new_pkey;
+
-
---
@@ -1480 +1547 @@
-    ADD CONSTRAINT rollup_matrix_numeric_5m_pkey PRIMARY KEY (rollup_time, sid, name);
-
+ALTER TABLE rollup_matrix_numeric_5m CLUSTER ON rollup_matrix_numeric_5m_pkey;
+
-
---
@@ -1488 +1557 @@
-    ADD CONSTRAINT rollup_matrix_numeric_60m_pkey PRIMARY KEY (rollup_time, sid, name);
-
+ALTER TABLE rollup_matrix_numeric_60m CLUSTER ON rollup_matrix_numeric_60m_pkey;
+
-
---
@@ -1495 +1566 @@
-ALTER TABLE ONLY rollup_matrix_numeric_6hours
-    ADD CONSTRAINT rollup_matrix_numeric_6hours_pkey PRIMARY KEY (rollup_time, sid, name);
+
+ALTER TABLE rollup_matrix_numeric_6hours CLUSTER ON rollup_matrix_numeric_6hours_pkey;
-
-