Changeset 32 for trunk/dnsbl/dnsbl.sql


Ignore:
Timestamp:
03/25/11 17:16:38 (14 years ago)
Author:
Kris Deugau
Message:

/trunk/dnsbl

Changeset to improve export speed on DNSBL database. By keeping
IP counts for each block, and directly tracking the parent of
each block and IP, an export taking ~90 seconds can be brought
down under 20.

  • updated initial tabledef SQL
  • Add addparents.sql SQL tabeldef and setparents.pl data update script
  • Tweak reporting of IP since it now requires the parent block(s) so the parent field can be filled in

Also add delist-ip script and Makefile

File:
1 edited

Legend:

Unmodified
Added
Removed

  • trunk/dnsbl/dnsbl.sql

    r29 r32  
    55SET client_encoding = 'UTF8';
    66SET check_function_bodies = false;
     7SET client_min_messages = warning;
    78
    89--
    9 -- TOC entry 7 (OID 196077)
    1010-- Name: blocks; Type: TABLE; Schema: public; Owner: dnsbl
    1111--
    … …  
    1616    orgid integer,
    1717    "level" integer DEFAULT 0,
    18     comments text DEFAULT ''::text
     18    comments text DEFAULT ''::text,
     19    parent cidr NOT NULL,
     20    ipcount integer NOT NULL
    1921);
    2022
    2123
    2224--
    23 -- TOC entry 8 (OID 196085)
    2425-- Name: iplist; Type: TABLE; Schema: public; Owner: dnsbl
    2526--
    … …  
    2930    count integer DEFAULT 1,
    3031    s4list boolean DEFAULT false,
    31     added timestamp with time zone DEFAULT now()
     32    added timestamp with time zone DEFAULT now(),
     33    parent cidr NOT NULL
    3234);
    3335
    3436
    3537--
    36 -- TOC entry 9 (OID 196090)
    3738-- Name: orgs; Type: TABLE; Schema: public; Owner: dnsbl
    3839--
    … …  
    4748
    4849--
    49 -- TOC entry 10 (OID 196099)
    5050-- Name: autolist; Type: TABLE; Schema: public; Owner: dnsbl
    5151--
    … …  
    5858
    5959--
    60 -- TOC entry 11 (OID 196147)
    6160-- Name: misc; Type: TABLE; Schema: public; Owner: dnsbl
    6261--
    … …  
    7069-- for tracking IPs that get removed from listing
    7170-- note we don't want a PK on IP as with iplist, since we may end up removing an IP more than once.  :/
     71--
     72-- Name: waslisted; Type: TABLE; Schema: public; Owner: dnsbl
     73--
    7274
    7375CREATE TABLE waslisted (
    … …  
    7981);
    8082
     83
    8184--
    82 -- Data for TOC entry 23 (OID 196099)
     85-- Name: autolist_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
     86--
     87
     88ALTER TABLE ONLY autolist
     89    ADD CONSTRAINT autolist_pkey PRIMARY KEY (masklen);
     90
     91
     92--
     93-- Name: blocks_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
     94--
     95
     96ALTER TABLE ONLY blocks
     97    ADD CONSTRAINT blocks_pkey PRIMARY KEY (block);
     98
     99
     100--
     101-- Name: iplist_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
     102--
     103
     104ALTER TABLE ONLY iplist
     105    ADD CONSTRAINT iplist_pkey PRIMARY KEY (ip);
     106
     107
     108--
     109-- Name: orgs_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
     110--
     111
     112ALTER TABLE ONLY orgs
     113    ADD CONSTRAINT orgs_pkey PRIMARY KEY (orgid);
     114
     115
     116--
     117-- Name: blocks_parent_index; Type: INDEX; Schema: public; Owner: dnsbl
     118--
     119
     120CREATE INDEX blocks_parent_index ON blocks USING btree (parent);
     121
     122
     123--
     124-- Name: ip_parent_index; Type: INDEX; Schema: public; Owner: dnsbl
     125--
     126
     127CREATE INDEX ip_parent_index ON iplist USING btree (parent);
     128
     129
     130--
     131-- Name: blocks_orgid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dnsbl
     132--
     133
     134ALTER TABLE ONLY blocks
     135    ADD CONSTRAINT blocks_orgid_fkey FOREIGN KEY (orgid) REFERENCES orgs(orgid);
     136
     137
     138--
    83139-- Name: autolist; Type: TABLE DATA; Schema: public; Owner: dnsbl
    84140--
    … …  
    1181740       2147483648
    119175\.
    120 
    121 
    122 --
    123 -- TOC entry 12 (OID 196101)
    124 -- Name: blocks_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
    125 --
    126 
    127 ALTER TABLE ONLY blocks
    128     ADD CONSTRAINT blocks_pkey PRIMARY KEY (block);
    129 
    130 
    131 --
    132 -- TOC entry 13 (OID 196103)
    133 -- Name: iplist_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
    134 --
    135 
    136 ALTER TABLE ONLY iplist
    137     ADD CONSTRAINT iplist_pkey PRIMARY KEY (ip);
    138 
    139 
    140 --
    141 -- TOC entry 14 (OID 196105)
    142 -- Name: orgs_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
    143 --
    144 
    145 ALTER TABLE ONLY orgs
    146     ADD CONSTRAINT orgs_pkey PRIMARY KEY (orgid);
    147 
    148 
    149 --
    150 -- TOC entry 15 (OID 196215)
    151 -- Name: autolist_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
    152 --
    153 
    154 ALTER TABLE ONLY autolist
    155     ADD CONSTRAINT autolist_pkey PRIMARY KEY (masklen);
    156 
    157 
    158 --
    159 -- TOC entry 25 (OID 196107)
    160 -- Name: blocks_orgid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dnsbl
    161 --
    162 
    163 ALTER TABLE ONLY blocks
    164     ADD CONSTRAINT blocks_orgid_fkey FOREIGN KEY (orgid) REFERENCES orgs(orgid);
    165 
Note: See TracChangeset for help on using the changeset viewer.