-- -- PostgreSQL database dump -- SET client_encoding = 'UNICODE'; SET check_function_bodies = false; SET SESSION AUTHORIZATION 'oneunified'; -- -- TOC entry 2 (OID 0) -- Name: oneunified; Type: DATABASE; Schema: -; Owner: oneunified -- CREATE DATABASE oneunified WITH TEMPLATE = template0 ENCODING = 'UNICODE'; \connect oneunified oneunified SET client_encoding = 'UNICODE'; SET check_function_bodies = false; SET SESSION AUTHORIZATION 'postgres'; -- -- TOC entry 4 (OID 2200) -- Name: public; Type: ACL; Schema: -; Owner: postgres -- REVOKE ALL ON SCHEMA public FROM PUBLIC; GRANT ALL ON SCHEMA public TO PUBLIC; SET SESSION AUTHORIZATION 'oneunified'; SET search_path = public, pg_catalog; -- -- TOC entry 5 (OID 140165) -- Name: activecalls; Type: TABLE; Schema: public; Owner: oneunified -- CREATE TABLE activecalls ( device character varying(128) NOT NULL, interface character varying(50) NOT NULL, number character varying(25) NOT NULL, timecreated timestamp with time zone NOT NULL ) WITHOUT OIDS; -- -- TOC entry 11 (OID 145047) -- Name: calllog; Type: TABLE; Schema: public; Owner: oneunified -- CREATE TABLE calllog ( id serial NOT NULL, 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 ) WITHOUT OIDS; -- -- TOC entry 14 (OID 146675) -- Name: ospfstatus; Type: TABLE; Schema: public; Owner: oneunified -- 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 DEFAULT 0 NOT NULL ) WITHOUT OIDS; -- -- TOC entry 16 (OID 146712) -- Name: counters; Type: TABLE; Schema: public; Owner: oneunified -- CREATE TABLE counters ( item character varying(30) NOT NULL, value integer DEFAULT 0 NOT NULL ) WITHOUT OIDS; -- -- TOC entry 18 (OID 146727) -- Name: interfacestatus; Type: TABLE; Schema: public; Owner: oneunified -- 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 DEFAULT 0 NOT NULL ) WITHOUT OIDS; -- -- TOC entry 20 (OID 146746) -- Name: wirelessassoc; Type: TABLE; Schema: public; Owner: oneunified -- CREATE TABLE wirelessassoc ( device character varying(128) NOT NULL, interface character varying(50) NOT NULL, mac macaddr NOT NULL, phone character varying(15), transitioned timestamp with time zone NOT NULL, status character varying(25) NOT NULL, count integer DEFAULT 0 NOT NULL ) WITHOUT OIDS; -- -- TOC entry 22 (OID 190541) -- Name: cdr; Type: TABLE; Schema: public; Owner: oneunified -- CREATE TABLE cdr ( id serial NOT NULL, device character varying(128) NOT NULL, connectionid character varying(36) NOT NULL, calllegtype1 integer, address1 character varying(20), calllegtype2 integer, address2 character varying(20), setuptime timestamp with time zone, connecttime timestamp with time zone, disconnecttime timestamp with time zone ) WITHOUT OIDS; -- -- TOC entry 25 (OID 190547) -- Name: controllerstatus; Type: TABLE; Schema: public; Owner: oneunified -- CREATE TABLE controllerstatus ( device character varying(128) NOT NULL, interface character varying(50) NOT NULL, status character varying(128) NOT NULL, count integer DEFAULT 0 NOT NULL, timeupdated timestamp with time zone NOT NULL ) WITHOUT OIDS; -- -- TOC entry 27 (OID 140169) -- Name: ixActivecallsDevice; Type: INDEX; Schema: public; Owner: oneunified -- CREATE INDEX "ixActivecallsDevice" ON activecalls USING btree (device); -- -- TOC entry 35 (OID 190546) -- Name: ix_cdr_connectionid; Type: INDEX; Schema: public; Owner: oneunified -- CREATE UNIQUE INDEX ix_cdr_connectionid ON cdr USING btree (connectionid); -- -- TOC entry 36 (OID 190559) -- Name: ix_cdr_setuptime; Type: INDEX; Schema: public; Owner: oneunified -- CREATE INDEX ix_cdr_setuptime ON cdr USING btree (setuptime); -- -- TOC entry 29 (OID 140167) -- Name: pkActivecallsDeviceInterface; Type: CONSTRAINT; Schema: public; Owner: oneunified -- ALTER TABLE ONLY activecalls ADD CONSTRAINT "pkActivecallsDeviceInterface" PRIMARY KEY (device, interface); -- -- TOC entry 30 (OID 145050) -- Name: pkid; Type: CONSTRAINT; Schema: public; Owner: oneunified -- ALTER TABLE ONLY calllog ADD CONSTRAINT pkid PRIMARY KEY (id); -- -- TOC entry 31 (OID 146681) -- Name: pkospfstatusinterface; Type: CONSTRAINT; Schema: public; Owner: oneunified -- ALTER TABLE ONLY ospfstatus ADD CONSTRAINT pkospfstatusinterface PRIMARY KEY (device, process, interface, neighbor); -- -- TOC entry 32 (OID 146715) -- Name: pkcounters; Type: CONSTRAINT; Schema: public; Owner: oneunified -- ALTER TABLE ONLY counters ADD CONSTRAINT pkcounters PRIMARY KEY (item); -- -- TOC entry 33 (OID 146730) -- Name: pkinterfacestatus; Type: CONSTRAINT; Schema: public; Owner: oneunified -- ALTER TABLE ONLY interfacestatus ADD CONSTRAINT pkinterfacestatus PRIMARY KEY (device, interface); -- -- TOC entry 34 (OID 146749) -- Name: pkwirelessassoc; Type: CONSTRAINT; Schema: public; Owner: oneunified -- ALTER TABLE ONLY wirelessassoc ADD CONSTRAINT pkwirelessassoc PRIMARY KEY (device, interface, mac); -- -- TOC entry 37 (OID 190544) -- Name: pk_cdrid; Type: CONSTRAINT; Schema: public; Owner: oneunified -- ALTER TABLE ONLY cdr ADD CONSTRAINT pk_cdrid PRIMARY KEY (id); -- -- TOC entry 38 (OID 190549) -- Name: pk_controllerstatus_devint; Type: CONSTRAINT; Schema: public; Owner: oneunified -- ALTER TABLE ONLY controllerstatus ADD CONSTRAINT pk_controllerstatus_devint PRIMARY KEY (device, interface); SET SESSION AUTHORIZATION 'postgres'; -- -- TOC entry 3 (OID 2200) -- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres -- COMMENT ON SCHEMA public IS 'Standard public schema'; SET SESSION AUTHORIZATION 'oneunified'; -- -- TOC entry 6 (OID 140165) -- Name: TABLE activecalls; Type: COMMENT; Schema: public; Owner: oneunified -- COMMENT ON TABLE activecalls IS 'This table shows active calls in Cisco h.323 based gateways. ciscowatcher.pl feeds this table.'; -- -- TOC entry 7 (OID 140165) -- Name: COLUMN activecalls.device; Type: COMMENT; Schema: public; Owner: oneunified -- COMMENT ON COLUMN activecalls.device IS 'Could be basic name or fqdn.'; -- -- TOC entry 8 (OID 140165) -- Name: COLUMN activecalls.interface; Type: COMMENT; Schema: public; Owner: oneunified -- COMMENT ON COLUMN activecalls.interface IS 'Extracted from string supplied in syslog. Usually full Cisco name.'; -- -- TOC entry 9 (OID 140165) -- Name: COLUMN activecalls.number; Type: COMMENT; Schema: public; Owner: oneunified -- COMMENT ON COLUMN activecalls.number IS 'Telephone number as supplied by gateway.'; -- -- TOC entry 10 (OID 140165) -- Name: COLUMN activecalls.timecreated; Type: COMMENT; Schema: public; Owner: oneunified -- COMMENT ON COLUMN activecalls.timecreated IS 'Time the record was created. Assists with cleanup.'; -- -- TOC entry 28 (OID 140169) -- Name: INDEX "ixActivecallsDevice"; Type: COMMENT; Schema: public; Owner: oneunified -- COMMENT ON INDEX "ixActivecallsDevice" IS 'Allows quick generation of a device list.'; -- -- TOC entry 12 (OID 145047) -- Name: TABLE calllog; Type: COMMENT; Schema: public; Owner: oneunified -- COMMENT ON TABLE calllog IS 'Records details from Cisco %VOIPAAA-5-VOIP_CALL_HISTORY records'; -- -- TOC entry 13 (OID 145047) -- Name: COLUMN calllog.id; Type: COMMENT; Schema: public; Owner: oneunified -- COMMENT ON COLUMN calllog.id IS 'Unique sequence numbers for records'; -- -- TOC entry 15 (OID 146675) -- Name: TABLE ospfstatus; Type: COMMENT; Schema: public; Owner: oneunified -- COMMENT ON TABLE ospfstatus IS 'Records information from cisco %OSPF-5-ADJCHG syslog records.'; -- -- TOC entry 17 (OID 146712) -- Name: TABLE counters; Type: COMMENT; Schema: public; Owner: oneunified -- COMMENT ON TABLE counters IS 'Repository of counters for Cricket'; -- -- TOC entry 19 (OID 146727) -- Name: TABLE interfacestatus; Type: COMMENT; Schema: public; Owner: oneunified -- COMMENT ON TABLE interfacestatus IS 'Records information from various cisco link events'; -- -- TOC entry 21 (OID 146746) -- Name: TABLE wirelessassoc; Type: COMMENT; Schema: public; Owner: oneunified -- COMMENT ON TABLE wirelessassoc IS 'Maintains a list of wireless associations'; -- -- TOC entry 23 (OID 190541) -- Name: TABLE cdr; Type: COMMENT; Schema: public; Owner: oneunified -- COMMENT ON TABLE cdr IS 'Call Detail Records using the data from calllog as a basic source.'; -- -- TOC entry 24 (OID 190541) -- Name: COLUMN cdr.id; Type: COMMENT; Schema: public; Owner: oneunified -- COMMENT ON COLUMN cdr.id IS 'Unique sequence numbers for records'; -- -- TOC entry 26 (OID 190547) -- Name: TABLE controllerstatus; Type: COMMENT; Schema: public; Owner: oneunified -- COMMENT ON TABLE controllerstatus IS 'T1/E1 Controller Status';