From 44ccb2f192f0a85f3ba43d0cde703fae7d1d8bfd Mon Sep 17 00:00:00 2001 From: Bob Cotton Date: Sat, 18 Apr 2009 17:08:06 -0600 Subject: [PATCH] Move the table definitions into the create_table.sql file. Seems that's where they belong --- sql/create_tables.sql | 34 ++++++++++++++++++++++++++++++++++ sql/metrics.sql | 29 ----------------------------- 2 files changed, 34 insertions(+), 29 deletions(-) diff --git a/sql/create_tables.sql b/sql/create_tables.sql index 624ea9c..100f9c3 100644 --- a/sql/create_tables.sql +++ b/sql/create_tables.sql @@ -1,3 +1,37 @@ +-- Written by Bob Cotton +-- 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)); + + select create_partition_tables('metrics', now()::timestamp, '6 months'::interval, 'month', 'YYYY_MM'); select create_partition_trigger('metrics', now()::timestamp, '6 months'::interval, 'month', 'YYYY_MM'); CREATE TRIGGER insert_metrics_trigger BEFORE INSERT ON metrics FOR EACH ROW EXECUTE PROCEDURE metrics_insert_trigger(); diff --git a/sql/metrics.sql b/sql/metrics.sql index c43ce87..c7e24ea 100644 --- 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, -- 2.11.0