source: ccsd/trunk/dbschema/ccs_graphs.schema @ 1283

-- 
-- PostgreSQL create schema for CRCnet Configuration Systems billing module
--
-- All prices in these tables should be sales tax exclusive
--
-- Author:  Chris Browning <ckb6@cs.waikato.ac.nz>
--          Matt Brown  <matt@crc.net.nz>
-- Version:  $Id$
--


CREATE TABLE rrdbot_class (
    class_id    SERIAL          PRIMARY KEY,
    class_name  varchar(64)     UNIQUE NOT NULL,
    poll        varchar(64),
    interval    integer,
    cf          varchar(8),
    archive     varchar(256),
    depends     integer         DEFAULT 0,
    ipfromoid   integer,
    skip_software_loop  integer,
    key         varchar(64)
);

CREATE TABLE rrdbot_class_parts (
    part_id     SERIAL          PRIMARY KEY,
    class_id    integer,
    name        varchar(64),
    poll        varchar(64),
    type        varchar(64),
    min         integer         DEFAULT NULL,
    max         integer         DEFAULT NULL
);

-- Graph classes that we know about
CREATE TABLE graph_group (
    group_id        SERIAL          PRIMARY KEY,
    group_name      varchar(64)
);

-- Graphs that we know about
CREATE TABLE graph_type (
    graph_id        SERIAL          PRIMARY KEY,
    group_id        integer NOT NULL REFERENCES graph_group (group_id) ON DELETE CASCADE,
    class_id        integer NOT NULL REFERENCES rrdbot_class (class_id) ON DELETE CASCADE,
    title           varchar(64),
    filename        varchar(64),
    virtical_label  varchar(64)
);


-- All the graph parts, relate to a graph by graph_id
-- ie, LINE, AREA etc
CREATE TABLE graph_parts (
    part_id         SERIAL      PRIMARY KEY,
    graph_id        integer NOT NULL REFERENCES graph_type (graph_id) ON DELETE CASCADE,
    graph_order     integer,
    type            varchar(10),
    colour          varchar(20),
    text            varchar(64),
    varname         varchar(64),
    cf              varchar(8),
    filename        varchar(128)
);



CREATE TABLE graph_time ( 
    time_id SERIAL PRIMARY KEY,
    start_time varchar(10),
    end_time varchar(10),
    name varchar(20) NOT NULL
);

-- rrdbot-script discoveries
CREATE TABLE snmp_discovery (
    host_id       integer   NOT NULL REFERENCES host (host_id) ON DELETE CASCADE,
    --name of class found
    class_id      integer   NOT NULL REFERENCES rrdbot_class (class_id) ON DELETE CASCADE,
    num           varchar(8)   NOT NULL,
    ip_address    varchar(20),
    link_id       integer,
    --Time of last discovery of this class
    timestamp       timestamp with time zone NOT NULL DEFAULT NOW()
);
CREATE TABLE snmp_instructions (
    host_id      integer   NOT NULL REFERENCES host (host_id) ON DELETE CASCADE,
    instr       integer,
    param1      varchar(64),
    param2      varchar(64),
    param3      varchar(64)
);

CREATE TABLE snmp_state (
    state      integer   NOT NULL
);

CREATE VIEW graphview as SELECT a.title, a.group_id, f.group_name, b.link_id, b.ip_address, b.num, a.graph_id, d.host_name, d.host_id, e.description from graph_type a, snmp_discovery b LEFT JOIN link e ON e.link_id=b.link_id, rrdbot_class c, host d, graph_group f WHERE c.class_id=b.class_id AND a.class_id = c.class_id AND d.host_id=b.host_id AND f.group_id=a.group_id;

CREATE VIEW mirrortmp as select a.*, b.host_id as host_id2, b.link_id as link_id2, b.host_name as host_name2, b.graph_id as graph_id2, b.ip_address as ip_address2, b.num as num2 from graphview a, graphview b where  b.host_id!=a.host_id AND b.link_id=a.link_id AND b.graph_id=a.graph_id AND a.link_id!=0;

CREATE VIEW mirrorview as SELECT a.*, b.host_id2, b.host_name2, b.ip_address2, b.num2 from graphview a LEFT JOIN mirrortmp b on a.host_id=b.host_id AND a.link_id=b.link_id AND a.graph_id=b.graph_id;

create view rrdbotlog as select a.host_id, a.class_id, c.class_name, b.host_name, a.num, a.ip_address, a.timestamp, a.link_id, d.description from snmp_discovery a LEFT JOIN link d on a.link_id=d.link_id, host b, rrdbot_class c where b.host_id=a.host_id and c.class_id=a.class_id;

-- Required values for graphs
INSERT INTO graph_time VALUES ( DEFAULT, '-1h', 'now', 'Hour');
INSERT INTO graph_time VALUES ( DEFAULT, '-1d', 'now', 'Day');
INSERT INTO graph_time VALUES ( DEFAULT, '-1w', 'now', 'Week');
INSERT INTO graph_time VALUES ( DEFAULT, '-1m', 'now', 'Month');
INSERT INTO graph_time VALUES ( DEFAULT, '-1y', 'now', 'Year');

INSERT INTO graph_time VALUES ( DEFAULT, '-2h', '-1h', 'Last Hour');
INSERT INTO graph_time VALUES ( DEFAULT, '-2d', '-1d', 'Yesturday');
INSERT INTO graph_time VALUES ( DEFAULT, '-2w', '-1w', 'Last Week');
INSERT INTO graph_time VALUES ( DEFAULT, '-2m', '-1m', 'Last Month');
INSERT INTO graph_time VALUES ( DEFAULT, '-2y', '-1y', 'Last Year');


-- Set the initial state
INSERT INTO snmp_state VALUES ( 0);
-- vim: ft=sql