Changed the partition by just to use the fk on the dimension tables.

[collectd_dbstore.git] / sql / metrics.sql

diff --git a/sql/metrics.sql b/sql/metrics.sql
index c43ce87..2a06268 100644 (file)
--- a/sql/metrics.sql
+++ b/sql/metrics.sql
@@ -2,35 +2,6 @@
 --  This is free software; you can redistribute it and/or modify it under
 --  the terms of the GNU General Public License as published by the Free
 --  Software Foundation; only version 2 of the License is applicable.
-drop table metrics cascade;
-drop table hostname_dimension cascade;
-drop table plugin_dimension cascade;
-drop table type_dimension cascade;
-drop type datasource_type cascade;
-
-create type datasource_type as ENUM ('GUAGE', 'COUNTER');
-
-create table metrics (id serial primary key,
-                      timestamp timestamp,
-                      measure double precision default 0,
-                      hostname_id integer not null,
-                      plugin_id integer not null,
-                      type_id integer not null
-                      );
-
-create table hostname_dimension (id serial primary key,
-                           hostname varchar(64) not null);
-
-create table plugin_dimension (id serial primary key,
-                         plugin varchar(64) not null,
-                         plugin_instance varchar(64));
-
-create table type_dimension (id serial primary key,
-                       ds_type datasource_type,
-                       type varchar(64) not null,
-                       type_name varchar(64) not null,
-                       type_instance varchar(64));
-
 create or replace function insert_metric(in_timestamp timestamp,
                                          in_measure double precision,
                                          in_hostname text,
@@ -156,17 +127,14 @@ $$ LANGUAGE plpgsql;
 --  SELECT timestamp,
 --   ((m.measure - lag(m.measure) 
 --           over(partition by m.hostname_id, 
---                             p.plugin,
---                             p.plugin_instance,
---                             t.type,
---                             t.type_instance
---                 order by timestamp, m.hostname_id, p.plugin, p.plugin_instance, t.type, t.type_instance))) AS metric,
+--                             p.plugin_id,
+--                             t.type_id,
+--                 order by timestamp, m.hostname_id, p.plugin_id, t.type_id))) AS metric,
 --  m.hostname_id, 
 --  m.plugin_id, 
 --  m.type_id
 -- FROM metrics m, plugin_dimension p, type_dimension t
 -- where m.type_id = t.id
--- and m.plugin_id = p.id
 -- and t.ds_type = 'COUNTER'
 -- UNION
 -- select timestamp, m.measure as metric,