-- -- PostgreSQL database dump -- SET client_encoding = 'UNICODE'; SET check_function_bodies = false; SET SESSION AUTHORIZATION 'oneunified'; SET search_path = public, pg_catalog; SET SESSION AUTHORIZATION 'postgres'; SET SESSION AUTHORIZATION 'oneunified'; ALTER TABLE ONLY public."session" DROP CONSTRAINT fk_user_pkuserid; ALTER TABLE ONLY public."session" DROP CONSTRAINT pk_session_pksessionid; ALTER TABLE ONLY public."user" DROP CONSTRAINT pk_user_pkid; ALTER TABLE ONLY public.controllerstatus DROP CONSTRAINT pk_controllerstatus_devint; ALTER TABLE ONLY public.cdr DROP CONSTRAINT pk_cdrid; ALTER TABLE ONLY public.wirelessassoc DROP CONSTRAINT pkwirelessassoc; ALTER TABLE ONLY public.interfacestatus DROP CONSTRAINT pkinterfacestatus; ALTER TABLE ONLY public.counters DROP CONSTRAINT pkcounters; ALTER TABLE ONLY public.ospfstatus DROP CONSTRAINT pkospfstatusinterface; ALTER TABLE ONLY public.calllog DROP CONSTRAINT pkid; ALTER TABLE ONLY public.activecalls DROP CONSTRAINT "pkActivecallsDeviceInterface"; DROP INDEX public.ix_cdr_setuptime; DROP INDEX public.ix_cdr_connectionid; DROP INDEX public."ixActivecallsDevice"; DROP TABLE public."session"; DROP TABLE public."user"; DROP TABLE public.controllerstatus; DROP TABLE public.cdr; DROP TABLE public.wirelessassoc; DROP TABLE public.interfacestatus; DROP TABLE public.counters; DROP TABLE public.ospfstatus; DROP TABLE public.calllog; DROP TABLE public.activecalls; 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'; -- -- 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 330859) -- Name: user; Type: TABLE; Schema: public; Owner: oneunified -- CREATE TABLE "user" ( pkuserid character(36) NOT NULL, username character varying(100) NOT NULL, authentype character varying(10) NOT NULL, pwdcleartext character varying(20), pwdmd5 character varying(50) ) WITHOUT OIDS; -- -- TOC entry 31 (OID 330863) -- Name: session; Type: TABLE; Schema: public; Owner: oneunified -- CREATE TABLE "session" ( pksessionid character(36) NOT NULL, fkuserid character(36) NOT NULL, ts timestamp without time zone NOT NULL, groupname character varying(50), grouppermission boolean ) WITHOUT OIDS; -- -- TOC entry 33 (OID 140169) -- Name: ixActivecallsDevice; Type: INDEX; Schema: public; Owner: oneunified -- CREATE INDEX "ixActivecallsDevice" ON activecalls USING btree (device); -- -- TOC entry 41 (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 42 (OID 190559) -- Name: ix_cdr_setuptime; Type: INDEX; Schema: public; Owner: oneunified -- CREATE INDEX ix_cdr_setuptime ON cdr USING btree (setuptime); -- -- TOC entry 35 (OID 140167) -- Name: pkActivecallsDeviceInterface; Type: CONSTRAINT; Schema: public; Owner: oneunified -- ALTER TABLE ONLY activecalls ADD CONSTRAINT "pkActivecallsDeviceInterface" PRIMARY KEY (device, interface); -- -- TOC entry 36 (OID 145050) -- Name: pkid; Type: CONSTRAINT; Schema: public; Owner: oneunified -- ALTER TABLE ONLY calllog ADD CONSTRAINT pkid PRIMARY KEY (id); -- -- TOC entry 37 (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 38 (OID 146715) -- Name: pkcounters; Type: CONSTRAINT; Schema: public; Owner: oneunified -- ALTER TABLE ONLY counters ADD CONSTRAINT pkcounters PRIMARY KEY (item); -- -- TOC entry 39 (OID 146730) -- Name: pkinterfacestatus; Type: CONSTRAINT; Schema: public; Owner: oneunified -- ALTER TABLE ONLY interfacestatus ADD CONSTRAINT pkinterfacestatus PRIMARY KEY (device, interface); -- -- TOC entry 40 (OID 146749) -- Name: pkwirelessassoc; Type: CONSTRAINT; Schema: public; Owner: oneunified -- ALTER TABLE ONLY wirelessassoc ADD CONSTRAINT pkwirelessassoc PRIMARY KEY (device, interface, mac); -- -- TOC entry 43 (OID 190544) -- Name: pk_cdrid; Type: CONSTRAINT; Schema: public; Owner: oneunified -- ALTER TABLE ONLY cdr ADD CONSTRAINT pk_cdrid PRIMARY KEY (id); -- -- TOC entry 44 (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); -- -- TOC entry 45 (OID 330861) -- Name: pk_user_pkid; Type: CONSTRAINT; Schema: public; Owner: oneunified -- ALTER TABLE ONLY "user" ADD CONSTRAINT pk_user_pkid PRIMARY KEY (pkuserid); -- -- TOC entry 46 (OID 330865) -- Name: pk_session_pksessionid; Type: CONSTRAINT; Schema: public; Owner: oneunified -- ALTER TABLE ONLY "session" ADD CONSTRAINT pk_session_pksessionid PRIMARY KEY (pksessionid); -- -- TOC entry 47 (OID 330867) -- Name: fk_user_pkuserid; Type: FK CONSTRAINT; Schema: public; Owner: oneunified -- ALTER TABLE ONLY "session" ADD CONSTRAINT fk_user_pkuserid FOREIGN KEY (fkuserid) REFERENCES "user"(pkuserid); 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 34 (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'; -- -- TOC entry 28 (OID 330859) -- Name: TABLE "user"; Type: COMMENT; Schema: public; Owner: oneunified -- COMMENT ON TABLE "user" IS 'User list'; -- -- TOC entry 29 (OID 330859) -- Name: COLUMN "user".pkuserid; Type: COMMENT; Schema: public; Owner: oneunified -- COMMENT ON COLUMN "user".pkuserid IS 'guid of user'; -- -- TOC entry 30 (OID 330859) -- Name: COLUMN "user".authentype; Type: COMMENT; Schema: public; Owner: oneunified -- COMMENT ON COLUMN "user".authentype IS 'ntlan,basic,ldap, db'; -- -- TOC entry 32 (OID 330863) -- Name: COLUMN "session".groupname; Type: COMMENT; Schema: public; Owner: oneunified -- COMMENT ON COLUMN "session".groupname IS 'null: user authenticated; with name: group is permitted or deny''d; always at least one session entry with null entry, subsequent entries contain cached group permissions';