source: trunk/dnsbl/dnsbl.sql@ 29

--
-- PostgreSQL database dump
--

SET client_encoding = 'UTF8';
SET check_function_bodies = false;

--
-- TOC entry 7 (OID 196077)
-- Name: blocks; Type: TABLE; Schema: public; Owner: dnsbl
--

CREATE TABLE blocks (
    block cidr NOT NULL,
    listme boolean DEFAULT false,
    orgid integer,
    "level" integer DEFAULT 0,
    comments text DEFAULT ''::text
);


--
-- TOC entry 8 (OID 196085)
-- Name: iplist; Type: TABLE; Schema: public; Owner: dnsbl
--

CREATE TABLE iplist (
    ip inet NOT NULL,
    count integer DEFAULT 1,
    s4list boolean DEFAULT false,
    added timestamp with time zone DEFAULT now()
);


--
-- TOC entry 9 (OID 196090)
-- Name: orgs; Type: TABLE; Schema: public; Owner: dnsbl
--

CREATE TABLE orgs (
    orgid serial NOT NULL,
    listme boolean DEFAULT false,
    orgname character varying(100) DEFAULT ''::character varying NOT NULL,
    comments text
);


--
-- TOC entry 10 (OID 196099)
-- Name: autolist; Type: TABLE; Schema: public; Owner: dnsbl
--

CREATE TABLE autolist (
    masklen smallint NOT NULL,
    ipcount bigint
);


--
-- TOC entry 11 (OID 196147)
-- Name: misc; Type: TABLE; Schema: public; Owner: dnsbl
--

CREATE TABLE misc (
    "key" character varying(30),
    value character varying(255)
);


-- for tracking IPs that get removed from listing
-- note we don't want a PK on IP as with iplist, since we may end up removing an IP more than once.  :/

CREATE TABLE waslisted (
    ip inet NOT NULL,
    count integer DEFAULT 1,
    s4list boolean DEFAULT false,
    origadded timestamp with time zone DEFAULT now(),
    delisted timestamp with time zone DEFAULT now()
);

--
-- Data for TOC entry 23 (OID 196099)
-- Name: autolist; Type: TABLE DATA; Schema: public; Owner: dnsbl
--

COPY autolist (masklen, ipcount) FROM stdin;
31      1
30      1
29      2
28      3
27      4
26      5
25      6
24      7
23      8
22      10
21      13
20      16
19      19
18      22
17      26
16      30
15      34
14      38
13      42
12      46
11      50
10      54
9       58
8       62
7       2147483648
6       2147483648
5       2147483648
4       2147483648
3       2147483648
2       2147483648
1       2147483648
0       2147483648
\.


--
-- TOC entry 12 (OID 196101)
-- Name: blocks_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
--

ALTER TABLE ONLY blocks
    ADD CONSTRAINT blocks_pkey PRIMARY KEY (block);


--
-- TOC entry 13 (OID 196103)
-- Name: iplist_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
--

ALTER TABLE ONLY iplist
    ADD CONSTRAINT iplist_pkey PRIMARY KEY (ip);


--
-- TOC entry 14 (OID 196105)
-- Name: orgs_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
--

ALTER TABLE ONLY orgs
    ADD CONSTRAINT orgs_pkey PRIMARY KEY (orgid);


--
-- TOC entry 15 (OID 196215)
-- Name: autolist_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
--

ALTER TABLE ONLY autolist
    ADD CONSTRAINT autolist_pkey PRIMARY KEY (masklen);


--
-- TOC entry 25 (OID 196107)
-- Name: blocks_orgid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dnsbl
--

ALTER TABLE ONLY blocks
    ADD CONSTRAINT blocks_orgid_fkey FOREIGN KEY (orgid) REFERENCES orgs(orgid);