CREATE DATABASE oneunified
  WITH OWNER = oneunified
       ENCODING = 'UNICODE';


-- DROP USER netdisco;

CREATE USER netdisco
  WITH SYSID 100
  ENCRYPTED PASSWORD ''
  NOCREATEDB NOCREATEUSER;
ALTER GROUP netdisco ADD USER netdisco;


-- DROP GROUP oneunified;

CREATE Group oneunified
  WITH SYSID 101
  USER oneunified;

-- DROP USER oneunified;

CREATE USER oneunified
  WITH SYSID 101
  ENCRYPTED PASSWORD 'md57b91ce5ff5c7130be2934506869a8672'
  NOCREATEDB NOCREATEUSER;
ALTER GROUP netdisco ADD USER oneunified;
ALTER GROUP oneunified ADD USER oneunified;






-- Table: activecalls

-- DROP TABLE activecalls;

CREATE TABLE activecalls
(
  device character varying(128) NOT NULL, -- Could be basic name or fqdn.
  interface character varying(50) NOT NULL, -- Extracted from string supplied in syslog.  Usually full Cisco name.
  number character varying(25) NOT NULL, -- Telephone number as supplied by gateway.
  timecreated timestamp with time zone NOT NULL, -- Time the record was created.  Assists with cleanup.
  CONSTRAINT "pkActivecallsDeviceInterface" PRIMARY KEY (device, interface)
) 
WITHOUT OIDS;
ALTER TABLE activecalls OWNER TO oneunified;
COMMENT ON TABLE activecalls IS 'This table shows active calls in Cisco h.323 based gateways.  ciscowatcher.pl feeds this table.';
COMMENT ON COLUMN activecalls.device IS 'Could be basic name or fqdn.';
COMMENT ON COLUMN activecalls.interface IS 'Extracted from string supplied in syslog.  Usually full Cisco name.';
COMMENT ON COLUMN activecalls.number IS 'Telephone number as supplied by gateway.';
COMMENT ON COLUMN activecalls.timecreated IS 'Time the record was created.  Assists with cleanup.';


-- Index: "ixActivecallsDevice"

-- DROP INDEX "ixActivecallsDevice";

CREATE INDEX "ixActivecallsDevice"
  ON activecalls
  USING btree
  (device);
COMMENT ON INDEX "ixActivecallsDevice" IS 'Allows quick generation of a device list.';

-- Constraint: "pkActivecallsDeviceInterface"

-- ALTER TABLE activecalls DROP CONSTRAINT "pkActivecallsDeviceInterface";

ALTER TABLE activecalls
  ADD CONSTRAINT "pkActivecallsDeviceInterface" PRIMARY KEY(device, interface);







-- Table: calllog

-- DROP TABLE calllog;

CREATE TABLE calllog
(
  id serial NOT NULL, -- Unique sequence numbers for records
  device character varying(128) NOT NULL,
  connectionid character varying(36) NOT NULL,
  calllegtype integer NOT NULL,
  setuptime timestamp without time zone NOT NULL,
  peeraddress character varying(20) NOT NULL,
  peersubaddress character varying(20) NOT NULL,
  disconnectcause character(2) NOT NULL,
  connecttime timestamp without time zone NOT NULL,
  disconnecttime timestamp without time zone NOT NULL,
  callorigin integer NOT NULL,
  chargedunits integer NOT NULL,
  infotype integer NOT NULL,
  transmitpackets integer NOT NULL,
  transmitbytes integer NOT NULL,
  receivepackets integer NOT NULL,
  receivebytes integer NOT NULL,
  CONSTRAINT pkid PRIMARY KEY (id)
) 
WITHOUT OIDS;
ALTER TABLE calllog OWNER TO oneunified;
COMMENT ON TABLE calllog IS 'Records details from Cisco %VOIPAAA-5-VOIP_CALL_HISTORY records';
COMMENT ON COLUMN calllog.id IS 'Unique sequence numbers for records';


-- Constraint: pkid

-- ALTER TABLE calllog DROP CONSTRAINT pkid;

ALTER TABLE calllog
  ADD CONSTRAINT pkid PRIMARY KEY(id);


------------------------
ALTER TABLE calllog_id_seq OWNER TO oneunified;
------------------------




-- Table: counters

-- DROP TABLE counters;

CREATE TABLE counters
(
  item character varying(30) NOT NULL,
  value integer NOT NULL DEFAULT 0,
  CONSTRAINT pkcounters PRIMARY KEY (item)
) 
WITHOUT OIDS;
ALTER TABLE counters OWNER TO oneunified;
COMMENT ON TABLE counters IS 'Repository of counters for Cricket';


-- Constraint: pkcounters

-- ALTER TABLE counters DROP CONSTRAINT pkcounters;

ALTER TABLE counters
  ADD CONSTRAINT pkcounters PRIMARY KEY(item);



-- Table: interfacestatus

-- DROP TABLE interfacestatus;

CREATE TABLE interfacestatus
(
  device character varying(128) NOT NULL,
  interface character varying(50) NOT NULL,
  linkstatus character varying(25) NOT NULL,
  protocolstatus character varying(25) NOT NULL,
  transitioned timestamp with time zone NOT NULL,
  count integer NOT NULL DEFAULT 0,
  CONSTRAINT pkinterfacestatus PRIMARY KEY (device, interface)
) 
WITHOUT OIDS;
ALTER TABLE interfacestatus OWNER TO oneunified;
COMMENT ON TABLE interfacestatus IS 'Records information from various cisco link events';


-- Constraint: pkinterfacestatus

-- ALTER TABLE interfacestatus DROP CONSTRAINT pkinterfacestatus;

ALTER TABLE interfacestatus
  ADD CONSTRAINT pkinterfacestatus PRIMARY KEY(device, interface);



-- Table: ospfstatus

-- DROP TABLE ospfstatus;

CREATE TABLE ospfstatus
(
  device character varying(128) NOT NULL,
  process integer NOT NULL,
  interface character varying(50) NOT NULL,
  neighbor inet NOT NULL,
  status character varying(35) NOT NULL,
  transitioned timestamp with time zone NOT NULL,
  count integer NOT NULL DEFAULT 0,
  CONSTRAINT pkospfstatusinterface PRIMARY KEY (device, process, interface, neighbor)
) 
WITHOUT OIDS;
ALTER TABLE ospfstatus OWNER TO oneunified;
COMMENT ON TABLE ospfstatus IS 'Records information from cisco %OSPF-5-ADJCHG syslog records.';


-- Constraint: pkospfstatusinterface

-- ALTER TABLE ospfstatus DROP CONSTRAINT pkospfstatusinterface;

ALTER TABLE ospfstatus
  ADD CONSTRAINT pkospfstatusinterface PRIMARY KEY(device, process, interface, neighbor);



-- Table: wirelessassoc

-- DROP TABLE wirelessassoc;

CREATE TABLE wirelessassoc
(
  device character varying(128) NOT NULL,
  interface character varying(50) NOT NULL,
  mac macaddr NOT NULL,
  transitioned timestamp with time zone NOT NULL,
  status character varying(25) NOT NULL,
  count integer NOT NULL DEFAULT 0,
  CONSTRAINT pkwirelessassoc PRIMARY KEY (device, interface, mac)
) 
WITHOUT OIDS;
ALTER TABLE wirelessassoc OWNER TO oneunified;
COMMENT ON TABLE wirelessassoc IS 'Maintains a list of wireless associations';


-- Constraint: pkwirelessassoc

-- ALTER TABLE wirelessassoc DROP CONSTRAINT pkwirelessassoc;

ALTER TABLE wirelessassoc
  ADD CONSTRAINT pkwirelessassoc PRIMARY KEY(device, interface, mac);