source: trunk/dnsbl/dnsbl.sql@ 69

--
-- PostgreSQL database dump
--

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

--
-- 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,
    parent cidr NOT NULL,
    ipcount integer NOT NULL
);


--
-- 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(),
    parent cidr NOT NULL
);


--
-- 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
);


--
-- Name: autolist; Type: TABLE; Schema: public; Owner: dnsbl
--

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


--
-- 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.  :/
--
-- Name: waslisted; Type: TABLE; Schema: public; Owner: dnsbl
--

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()
);


--
-- Name: autolist_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
--

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


--
-- Name: blocks_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
--

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


--
-- Name: iplist_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
--

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


--
-- Name: orgs_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
--

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


--
-- Name: blocks_parent_index; Type: INDEX; Schema: public; Owner: dnsbl
--

CREATE INDEX blocks_parent_index ON blocks USING btree (parent);


--
-- Name: ip_parent_index; Type: INDEX; Schema: public; Owner: dnsbl
--

CREATE INDEX ip_parent_index ON iplist USING btree (parent);


--
-- 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);


--
-- 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
\.